debtflow

Schema-validated XML reporting pipeline for regulated debt portfolios.

Turn periodic Excel portfolio snapshots into structurally valid XML submissions for credit reporting bureaus. Built for compliance environments where every file must pass XSD validation before it touches disk.

---

What it does

Financial institutions, debt servicers, and credit originators are required to submit credit data to reporting bureaus on a regular basis — weekly or monthly portfolio snapshots in a strictly defined XML format, validated against versioned XSD schemas.

debtflow automates the full path:

Excel snapshot → PostgreSQL → Diff engine → Payment engine → XML builder → XSD validator → File

Each step is a discrete, testable module. No file is written unless it passes schema validation. Invalid output is logged with the precise XSD error path — never silently dropped.

---

Pipeline

┌─────────────────────────────────────────────────────────────┐
│                        debtflow pipeline                    │
└─────────────────────────────────────────────────────────────┘

  Excel file (.xlsx)
       │
       ▼
  ┌──────────┐    normalise columns, restore leading zeros,
  │ Importer │    batch insert 5k rows at a time
  └──────────┘
       │
       ▼
  ┌──────────────┐
  │  PostgreSQL  │  versioned snapshots — one row per (debt × export_date)
  └──────────────┘
       │
       ▼
  ┌─────────────┐   compare two latest snapshots per debt,
  │ Diff engine │   detect credit events (payment / closure)
  └─────────────┘
       │
       ▼
  ┌────────────────┐  cascading allocation: fees → principal → penalties
  │ Payment engine │  Decimal arithmetic throughout — no float
  └────────────────┘
       │
       ▼
  ┌─────────────┐  single builder, bureau switched by parameter
  │ XML builder │  strict block ordering enforced by construction
  └─────────────┘
       │
       ▼
  ┌───────────────┐  file written only on clean validation
  │ XSD validator │  error logged with XPath to failing node
  └───────────────┘
       │
       ▼
  output/YYYYMMDD_<entity>_<bureau>.xml

---

Key design decisions

Snapshot versioning, not upsert. Each import creates a new version row (debt_id × export_date). The diff engine compares the two most recent snapshots per debt. Upserting would destroy the history needed for event detection.

Single XML builder, parameterised by bureau. One module handles all bureau targets, switched by bureau_type. Differences between bureaus — schema version, envelope structure, optional blocks — are handled with conditional generation. No duplicated builder per bureau.

XSD validation gates the write. validator.save_xml() wraps xmlschema. If validation raises, the file is not written. The pipeline logs the failure with the debt identifier, event type, and the exact XSD error path. No silent failures.

Decimal everywhere. All financial arithmetic uses Python's Decimal with explicit ROUND_HALF_UP. Float is prohibited in the payment engine and XML serialisation. Amounts are serialised as exact fixed-point strings matching the target XSD pattern type — not a numeric type.

Config-driven bureau routing. Bureau-specific parameters (schema version, XSD path, envelope structure, reference codes) live in YAML. Adding a new bureau or updating a schema version is a config change — not a code change.

---

Stack

Layer	Technology
Language	Python 3.12
Database	PostgreSQL 15
ORM + migrations	SQLAlchemy 2.x + Alembic
Excel parsing	pandas + openpyxl
XML generation	lxml
XSD validation	xmlschema
Data validation	Pydantic v2
API	FastAPI
CLI	Click
Scheduler	APScheduler
Infrastructure	Docker + docker compose

---

Quick start

Prerequisites: Docker, docker compose, Python 3.12+

git clone https://github.com/lokyfour/debtflow.git
cd debtflow

# copy and fill in your config
cp config/config.example.yaml config/config.yaml

# place your XSD schema files
# see schemas/README.md for the expected structure

# start the database
docker compose up -d db

# apply migrations
alembic upgrade head

# run the full pipeline (dry run — no file written)
python -m src.interfaces.cli run \
  --pair entity_1:primary_bureau \
  --file path/to/snapshot.xlsx \
  --dry-run

# run for real
python -m src.interfaces.cli run \
  --pair entity_1:primary_bureau \
  --file path/to/snapshot.xlsx

---

Configuration

bureaus:
  primary_bureau:
    schema_version: "YOUR_SCHEMA_VERSION"
    xsd_path: "schemas/primary_bureau/"
    envelope:
      source_id: true
      doc_number_format: "composite"
    optional_blocks:
      cost_of_credit: false

  secondary_bureau:
    schema_version: "YOUR_SCHEMA_VERSION"
    xsd_path: "schemas/secondary_bureau/"
    envelope:
      source_id: false
      doc_number_format: "short"
    optional_blocks:
      cost_of_credit: true

entities:
  entity_1:
    name: "Reporting Entity Name"
    lei: "YOUR_LEI_CODE"           # Legal Entity Identifier — ISO 17442
    registration_id: "YOUR_REG_ID"
    role: "servicer"               # creditor | servicer | originator

  entity_2:
    name: "Second Reporting Entity"
    lei: "YOUR_LEI_CODE"
    registration_id: "YOUR_REG_ID"
    role: "originator"

pairs:
  - entity: entity_1
    bureau: primary_bureau
    source_id: "YOUR_SOURCE_ID"
  - entity: entity_1
    bureau: secondary_bureau
    source_id: "YOUR_SOURCE_ID"
  - entity: entity_2
    bureau: primary_bureau
    source_id: "YOUR_SOURCE_ID"

Each pair (entity × bureau) is an independent routing unit. The system processes all configured pairs per run, producing one XML file per pair.

lei — Legal Entity Identifier, ISO 17442 standard. Used across AnaCredit (ECB), SEPA, EMIR, and most European regulatory reporting frameworks as the canonical entity identifier.

---

Project structure

debtflow/
├── src/
│   ├── storage/           # SQLAlchemy models, session factory
│   ├── processing/        # importer, diff engine, payment engine
│   ├── builder/           # xml_builder, xsd validator
│   └── interfaces/        # pipeline orchestrator, FastAPI, CLI
├── config/
│   └── config.example.yaml
├── schemas/
│   ├── primary_bureau/    # place XSD files here
│   ├── secondary_bureau/  # place XSD files here
│   └── README.md
├── alembic/
│   └── versions/
├── tests/
│   └── fixtures/          # synthetic data only
├── docker-compose.yml
├── Dockerfile
└── pyproject.toml

---

Applying to your bureau

The pipeline is format-agnostic at the builder level. To target a new bureau:

1. Add a bureau profile to config.yaml with the schema version and XSD path
2. Place the bureau's XSD files in schemas/<bureau_name>/
3. Add bureau-specific envelope fields as conditional blocks in xml_builder.py
4. Register the new pairs in config.yaml

No other code changes required.

---

Use cases

The pattern implemented here applies to any regulated environment that requires:

• Periodic portfolio snapshots submitted as schema-validated XML
• Diff-based event detection between submission cycles
• Multi-entity routing to multiple receiving bureaus
• Immutable audit trail of what was submitted and when

Relevant European frameworks: AnaCredit (ECB granular credit data), SEPA payment reporting, ESMA securitisation disclosure, national credit reference agency submissions (BKR, SCHUFA, BIK, and others).

---

Status

Production-tested against a portfolio of 320 000 debt records across multiple legal entities and two bureau targets. Both bureau schemas validated clean end-to-end.

---

License

MIT