Architecture
The mirror is deliberately simple. It consumes the legacy system's existing export and publishes a public read-only copy. No writes ever flow back to Sapiens/AdBench. The authoritative system of record remains at the Commissioner of Political Practices.
Data flow
The ingest worker is idempotent. Re-running over the same export produces no duplicates thanks to stable primary keys andON CONFLICT DO UPDATEsemantics.
Design principles
Read-only. Always.
The mirror never writes back to Sapiens. It is a downstream replica. If an ingest bug corrupts data, we re-run from the last export and the authoritative record is unaffected.
Schema-on-read-friendly
Tables mimic the legacy schema (ALL_CAPS, flat, denormalized). This makes diffing and auditing against the export trivial and lets us port the ingest to different schemas as the modernization lands.
Idempotent ingest
Primary keys from the legacy system become our PKs. Every line item has a stable natural key so re-ingesting the same export is a no-op.
Continuity over parity
We don't try to match every feature of the legacy UI. We match the read-access surface: search, browse, export. Admin workflows stay in Sapiens.
Auditable freshness
Every ingest writes a row to INGEST_JOB with timestamps, source file, row counts. The admin page surfaces this so anyone can see when the mirror last synced.
Cheap to run
Neon Postgres + Vercel static + a small cron. Low five figures annually. No license, no per-seat, no forklift migration.
Legacy schema mapping
| Export file | Mirror table | Primary key | Notes |
|---|---|---|---|
| LOBBYIST_REG.csv | LOBBYIST_REG | LOBBYIST_ID | Registered lobbyist roster per session year. |
| PRINCIPAL_REG.csv | PRINCIPAL_REG | PRINCIPAL_ID | Principal (employer) registrations. |
| LOBBYIST_REPORT.csv | LOBBYIST_REPORT | REPORT_ID | Periodic disclosure reports (monthly, session, year-end). |
| PAYMENT_LINE.csv | PAYMENT_LINE | NATURAL_KEY (REPORT_ID + PAYMENT_TYPE) | Payment categories per report (lobbyist, travel, advertising, …). |
| CONTRIBUTIONS.xml | CONTRIBUTION | NATURAL_KEY (state + donor + recipient) | Donor → recipient rollups. |
| EXPENDITURES.xml | EXPENDITURE | NATURAL_KEY (state + payee + category) | Payee and category-level spend. |
Production path
In production this mirror runs on Neon Postgres (branchable, serverless, with point-in-time recovery) and deploys to Vercel. The SQLite database backing this prototype is a stand-in and ships withTODO(prod)markers throughout the codebase pointing to the Neon swap. The schema is identical; the only code change is the client library.