Radix-Skills/skills/data-science/erp-data-reconciliation/references/reconciliation-webapp-architecture.md

Reconciliation Webapp Architecture (Django)

Reference architecture for building a webapp on top of a reconciliation engine. Based on Coop-Uniconta project (Radix), May 2026.

Stack

• Backend: Django 4.2 + Django REST Framework
• Database: MariaDB 11.4
• Cache/Queue: Redis + Celery (for background matching)
• Frontend: Django Templates + vanilla JS (no React needed for internal tools)
• Deployment: Docker Compose (web, db, redis)
• Auth: Django built-in + admin, later MS Entra/OIDC

Project Structure

backend/
  config/
    settings/base.py      # Shared config
    settings/local.py     # Dev overrides (DEBUG=True, admin enabled)
    urls.py               # URL routing
    wsgi.py / asgi.py     # Entry points
    celery.py             # Celery app
  apps/
    core/                 # Models: Transaction, MatchResult, InvoiceStatus, AuditLog, BonusRule
      models.py
      admin.py
      matching.py         # 5-phase match engine
    import_/              # Excel parsers
      parsers.py          # Coop + Uniconta import with preview
    reconciliation/       # Views: dashboard, import, matching, reports
      views.py
      templates/
  templates/
    base.html             # Dark-themed layout (sidebar nav + main content)
  static/
    css/app.css           # Dark theme: --bg-primary: #0f172a, --accent: #3b82f6
  manage.py
  entrypoint.sh           # wait-for-db + migrate + runserver
  Dockerfile              # python:3.12-slim
  requirements.txt

Data Model

AccountingYear (year, start_date, end_date)
SourceFile (source, file_name, file_path, row_count, parsed_count, error_count, accounting_year)
Transaction (source_file, source, year, month, date, amount, abs_amount,
             doc_type, reference, faktura, text, text_normalized,
             konteringstype, bilag, doc_number,
             match_status, match_type, match_score, match_group,
             original_data JSON)
MatchResult (group_id, match_type, score, status,
             transaction_a, transaction_b, additional_b_ids JSON,
             amount_diff, date_diff_days, explanation, comment,
             approved_by, approved_at)
InvoiceStatus (faktura_number, accounting_year, invoice_total, paid_total, remaining,
               status, invoice_date, first_invoice_text,
               coop_payment_count, uniconta_payment_count, invoice_count)
AuditLog (action, user, match_result, details JSON)
BonusRule (name, year, period_start, period_end, conditions JSON, percentage, min_amount)
BonusCalculation (rule, transaction, calculated_amount, status)

Key Design Decisions

1. Store original_data as JSON — Always preserve raw Excel row data for traceability.
2. Separate amount and abs_amount — amount keeps original sign for net calculations; abs_amount for matching.
3. MatchResult captures both sides — Primary match (transaction_a → transaction_b) + optional additional_b_ids for many-to-one.
4. InvoiceStatus is computed, not stored per-transaction — Recalculated after each matching run.
5. AuditLog for every manual action — Match approval, rejection, bonus adjustment.

Match Engine Integration

The match engine runs as a Celery task triggered from the web UI:

# apps/core/matching.py
AMT_TOL = Decimal("5.00")
DATE_TOL_REF = 21
DATE_TOL_GEN = 21

def run_matching(accounting_year=None):
    # Phase 1: Reference match (RE↔Faktura, RG↔Kreditnota)
    # Phase 2: ZV↔Faktura
    # Phase 3: RE↔Betaling without reference
    # Phase 4: General abs(amount)+date
    # Phase 5: Many-to-one (sum of 2-3 Uni = 1 Coop)
    # Bulk create MatchResult, bulk update Transaction statuses

UI Pages

Page	Purpose
Dashboard	Year selector, stat cards, invoice status summary, monthly overview
Import	Upload Excel, show preview, map columns if auto-detection fails
Reconciliation List	Filterable table of all transactions, paginated (50/page)
Transaction Detail	Raw data, normalized data, potential matches, match history
Faktura Status	All invoices with BETALT/DELVIST/UBETALT, filterable
Reports	By type, by month, match summary, export to Excel
Run Match	Trigger background matching for selected year

Dark Theme CSS Variables

:root {
  --bg-primary: #0f172a;
  --bg-secondary: #1e293b;
  --bg-tertiary: #334155;
  --text-primary: #f8fafc;
  --text-secondary: #94a3b8;
  --accent: #3b82f6;
  --success: #22c55e;
  --warning: #f59e0b;
  --danger: #ef4444;
  --border: #334155;
  --radius: 8px;
}

Inspired by Radix-ERP visual style: sidebar navigation, card-based stats, data tables with badges, filter bars above tables.

Docker Compose

services:
  web:
    build: ./backend
    command: ["web-dev"]
    ports: ["8000:8000"]
    depends_on:
      db: {condition: service_healthy}
    env_file: [.env]
  db:
    image: mariadb:11.4
    environment:
      MARIADB_DATABASE: ${DB_NAME}
      MARIADB_USER: ${DB_USER}
      MARIADB_PASSWORD: ${DB_PASSWORD}
      MARIADB_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
    healthcheck:
      test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
  redis:
    image: redis:7-alpine

Deployment Notes

• python manage.py migrate runs on container startup via entrypoint.sh
• python manage.py createsuperuser for first admin login
• Uploads stored in MEDIA_ROOT (mounted volume in production)
• Static files collected via collectstatic for production (nginx)

Next Steps for a New Reconciliation Project

1. Copy project structure from template
2. Adapt parsers for the specific Excel formats
3. Run analysis script to determine match strategy
4. Configure AMT_TOL and DATE_TOL based on data quality
5. Build import UI with column mapping fallback
6. Implement match engine phases iteratively
7. Add manual match/approve/reject with audit log
8. Add reports and bonus calculation framework