Replacing a structurally-limited email-parsing data source with a real bank-data integration (cursor-paginated idempotent transaction sync, atomic multi-table writes through Postgres functions, and a net-worth engine), built sandbox-first so it stands as a complete demo even if production keys are never flipped.
The challenge
I built a single-user personal-finance dashboard whose "real bank data" ambition rested entirely on parsing bank-alert emails. Proving that path end-to-end (a real credit-card alert producing the first real transaction) exposed the structural ceiling of email parsing as a finance-data source:
- Alerts fire on transactions only: never balance changes.
- Investments and loans send no transaction alerts at all.
- Reward and cash-back tracking can't be reconstructed from alerts.
- Coverage needs a per-card parser, and multiple cards at one bank share identical subject lines, so a parser mis-routes.
The actual goal (a full dashboard with transactions, balances, investments, loans, net worth, and projections) was something email parsing structurally could not reach. The answer was a real bank-data integration (Plaid), covering a handful of institutions, without opening any public attack surface.
Approach
I built the whole thing sandbox-first, cron-driven, and atomic at the database layer.
- Sandbox-first. Build the entire app against Plaid's sandbox (indefinitely free). If production is never flipped, the sandbox build is still a complete, demonstrable environment.
- Cron, not webhooks. A nightly poll of a cursor-based sync endpoint never misses anything because the cursor advances atomically, and the app never needs to expose an inbound endpoint of its own.
- Read-only products only (transactions, balances, investments, liabilities), never transfer or payment-initiation.
- Atomic writes at the database layer. Because the app writes through PostgREST over HTTP (which can't do multi-statement transactions), any multi-table write is wrapped in a single Postgres function so it's atomic and can't leave orphan rows.
Delivered in milestones: a Plaid client with a read-only health check; the Link UI plus atomic account persistence; cursor-paginated nightly transaction sync with idempotency and a systemd timer; balances / holdings / liabilities via an "update mode"; and a net-worth engine with dedicated pages and a categorization-precedence chain.
Some execution highlights:
- Idempotency proven, not assumed. The first sync inserted 49 transaction rows; an immediate re-run returned 0 added / 0 modified / 0 removed with row count unchanged. The balances/holdings sync went further with a deliberate split-counter response: the first run inserted 13 holdings, the second returned 0 inserted / 13 updated. An aggregate count would have hidden the failure mode where a broken upsert key silently re-inserts duplicates. The split counters make it visible.
- A silent-corruption trap, caught at sign-off. The foreign key from accounts to items was
ON DELETE SET NULL, not CASCADE. I corrected the cleanup to a specific two-step delete order (accounts first, then the item). The reversed order produces no error at all, just silently orphaned rows. That's the dangerous kind of bug. - "Update mode" to add products without losing data. Adding investments and liabilities to an already-linked item used Plaid's update mode rather than a re-link, because update mode preserves the access token and item ID, so the existing 12 accounts and 49 transactions survived instead of being cascaded away.
- Verified external error codes against live docs: didn't trust the model. An error-code matcher was narrowed after checking Plaid's documentation: one referenced code doesn't exist, and two others were different failure classes than assumed.
- Provenance-based categorization precedence (manual > AI-gateway > Plaid's own categories): a manual edit pins the "manual" source, and the sync function's update clause omits the category column so manual edits automatically survive re-syncs. Verified live by editing a row and re-triggering a sync.
Results
- A complete sandbox bank-data integration: one linked institution, 12 accounts across five types, 49 transactions, 36+ balance snapshots, 13 holdings across seven security types, 2 loan liabilities. A nightly sync timer completes a full three-step run in ~38 seconds.
- All acceptance criteria green, the final milestone 16/16, with the net-worth math verified figure-by-figure.
- Idempotency and manual-edit survival both proven live: the two properties that make an unattended nightly sync trustworthy.
- Honestly scoped: the integration is sandbox-only. Production was never flipped; promotion stayed deferred pending a few days of observed sync behavior.
What this demonstrates
- Picking the right integration primitive matters more than effort. Email-alert parsing had a hard structural ceiling; recognizing that early redirected the entire project onto a foundation that could actually reach the goal.
- Idempotency you can prove beats idempotency you assume. Cursor pagination with atomic cursor advance gives idempotency for free, and split inserted-vs-updated counters turn a silent-duplicate failure mode into something you can see.
- Database-layer correctness under an HTTP data API. Wrapping multi-table writes in Postgres functions (one implicit transaction) and knowing foreign-key delete semantics is the difference between clean cleanup and silent data corruption.
- Skepticism toward generated assumptions. Verifying external-API error codes against live docs and grepping the codebase before trusting a task prompt each caught real errors before they shipped.