ERP Revenue Sync

Revenue for a fictional food manufacturer lives in two systems that disagree. ERPNext has a REST API; another business unit only has a CSV export. They spell customers differently, format dates differently and sometimes disagree on totals, so finance rebuilds the number by hand and still can't say which version is true. This project merges both into one canonical model and treats every figure as a claim it has to prove: bad records are held back with a reason, and the stored totals are checked against the source every day.

Technical Decisions

One Canonical Model, One Adapter per Source

Each source gets an adapter that maps its format into a single canonical invoice; nothing downstream knows which ERP a record came from. Adding a third system means writing one adapter, not touching validation, storage or the dashboard.

A Cursor That Cannot Skip an Invoice

ERPNext is polled incrementally on a (modified, name) cursor, so invoices that share a timestamp at a page boundary are neither skipped nor repeated, and every poll re-reads a 65-minute overlap to catch transactions that commit late. Deletions come from ERPNext's own Deleted Document stream and become tombstones, so a deleted invoice stops counting instead of lingering in the totals.

Bad Records Wait; They Are Never Summed

A record that breaks a business rule goes to quarantine with a reason code — total mismatch, conflicting duplicate, invalid date, missing field, unsupported currency, unknown customer — and is never half-loaded. Customer names are resolved through a versioned mapping file changed only by commit; changing it re-validates every record held for an unknown customer. Nobody edits financial data in the dashboard: fixes happen in the source or in the mapping, so the stored number never drifts from the systems it came from.

A Queue That Admits It Delivers Twice

Changes travel through a Postgres queue (pgmq) drained by a scheduled worker. Queues deliver at least once, and the same invoice can arrive by webhook and by polling, so instead of chasing exactly-once delivery every write is an idempotent upsert: processing an invoice twice gives the same result as processing it once. Messages that keep failing land in a dead-letter queue with their last error, visible on the health page.

Proving the Total Every Day

A daily reconciliation compares what is stored with what ERPNext reports, document by document, and classifies every difference instead of just flagging a mismatch. In the published snapshot the result is ERPNext $462,664.00 against $462,664.00 stored, with zero unexplained differences. Half a month is marked as partial, so October never passes for a whole month, and a query that would return a truncated result fails closed rather than summing part of the data.

Spec-Driven, With Agents Held to the Spec

The repository carries the full chain: a constitution of principles, a spec with requirements, business rules, edge cases and acceptance criteria, a plan, small tasks, and an analysis that maps every acceptance criterion to the tests that cover it. AI agents implemented against that spec, and every critical piece — the incremental sync, the idempotent consumer, validation and quarantine — went through adversarial review before it merged. More than 1,200 tests cover the core, the API routes and the SQL against a local Supabase stack.

A Public Demo That Cannot Touch the Database

The live site is a static sample: the figures were exported once from the running pipeline through an allowlist that copies no error text, internal ids or raw customer strings, and a leak check refuses to write the file if anything looks like a host, address or token. With one flag the same codebase serves only that page and answers 404 to every data route before a database client exists. Clone the repository and the full signed-in dashboard is still there.