Repo Pilot
Payment-event ingestion and reconciliation that survives duplicates
A FastAPI service that ingests payment lifecycle events, keeps every transaction's current status, and reports reconciliation summaries and discrepancies computed in SQL. Duplicate and out-of-order events don't corrupt state.
- 11,000+ seeded events
- 500 planted duplicates
- 3 discrepancy checks
- 2 queries per detail view
§1
The problem
Payment partners send webhooks at least once and not always in order. Reconciliation has to count each event exactly once, keep the right final status, and flag transactions whose history doesn't add up.
§2
My role
Sole developer: data model, ingestion, reconciliation queries, migrations, the data generator, tests and the small dashboard pages.
§3
Architecture
§4
Key decisions
D/01
event_id is the primary key
Deduplication happens in the database. A duplicate gets 200 with the stored event rather than 409, because webhook senders treat errors as a reason to retry. A racing insert hits IntegrityError and rolls back.
D/02
Late events can't rewind status
A transaction's status only changes when the event's timestamp is at or after its current updated_at.
D/03
Aggregate in SQL
The summary is one GROUP BY over date, merchant and status. No Python loops over rows.
D/04
No N+1 queries
The detail view uses joinedload for the merchant and selectinload for the events: two queries in total, however long the history.
D/05
Easy to run, real in production
SQLite when DATABASE_URL is unset, PostgreSQL in Docker, Alembic migrations for both, and a composite (status, merchant_id) index.
§5
Challenges
- Finding discrepancies in SQL: settled transactions with a payment_failed event (an EXISTS subquery), payments stuck in initiated or processed for over 48 hours, and transactions with more than one terminal event.
- Timezone-aware timestamps are normalised to naive UTC so comparisons behave the same in SQLite and PostgreSQL.
- A generator writes 11,000+ events, including failures, discrepancies and 500 deliberate duplicates, and a seeder replays them through the API to check idempotency end to end.
§6
Stack
- Python
- FastAPI
- SQLAlchemy
- Alembic
- PostgreSQL
- SQLite
- pytest
- Docker
Next case study
ClaimSense
An LLM reads the insurance claim; plain code decides where it goes