Design a Personal-Finance Data Pipeline (Mint-Style)

This is lesson 10 of System Design Case Studies, Part 2. It reuses ideas from earlier lessons: queues and idempotent writes from System Design Lesson 34 (idempotency) and Lesson 37 (delivery guarantees), and the "count per category" job is a smaller, exact cousin of Lesson 5 (trending and top-K). Ranking things per category, with exact numbers, comes back in Lesson 9 (sales ranking by category). The previous lesson in the series is Lesson 8, the stock exchange matching engine, where money also has to be exact.

The classic version of this question is in the system-design-primer: solutions/system_design/mint. I use its numbers and its parts (accounts API, queue, extraction service, category service, budget service, notifications), then add the parts an interviewer now expects: aggregators, dedup, idempotency, and security.

Words used in this lesson

• Bank aggregator is a company that connects to many banks for you and gives you one API for balances and transactions. Plaid, Yodlee and TrueLayer are examples. You do not talk to each bank yourself.
• Access token is a secret string the aggregator gives you after linking. It lets your server ask for that user's data. It is not the bank password.
• Transaction is one line on a statement: a date, an amount, a description.
• Pending / posted: a pending transaction is a hold that may still change. A posted one is final.
• Idempotent means doing the same operation twice has the same result as doing it once.
• Dedup (de-duplication) is detecting rows that are the same real-world event and keeping one.
• Cursor is a bookmark the aggregator gives you ("you have read up to here"). Next time you ask for "what is new since this cursor".
• Minor units are the smallest money unit as a whole number (cents). 1599 means 15.99. Never use floating point for money.
• Reconciliation is checking that two sources agree (our sum of transactions against the bank's balance).
• Envelope encryption is encrypting data with a data key, and keeping that key encrypted with a master key held in a key service.
• PII is personally identifiable information: names, account numbers, addresses.

1. Requirements

Functional (close to the primer's list)

• The user links a financial account.
• The service extracts transactions: on first link, on manual refresh, and daily (or on a webhook) for active users.
• Each transaction gets a category. The user can override it, and the override sticks.
• The service shows monthly spending by category.
• The user sets a budget per category. The service sends a notification when spending approaches (say 80%) or passes (100%) the limit.

Non-functional

• Correct money: no double-counted transaction, no lost one, and a way to prove it (reconciliation).
• Safe: the app must never hold a bank password, and a database leak must not reveal usable credentials.
• Available for reads. Writes may lag a few minutes. The primer says budget notifications do not need to be instant.
• Tolerates a flaky upstream. Banks and aggregators go down, time out and return partial data.

Out of scope here: investment accounts, money movement, and legal rules (consent, data rights). A real product must plan for them.

2. Back-of-envelope numbers

These are the primer's assumptions, not facts about any company.

Users                     10 millionLinked accounts           30 millionTransactions per month    5 billionAverage write rate        5e9 / 2.5e6 s per month = 2,000 transactions/sReads                     500 million per month   = 200 reads/s       (10:1 write to read)Row size                  ~50 bytes (user_id 8, created_at 5, seller 32, amount 5)New data                  5e9 x 50 B = 250 GB/month, ~9 TB in 3 yearsSellers                   50,000 -> a seller-to-category map under 12 MB

Things this arithmetic tells us:

• It is write heavy: 2,000 writes/s on average, more at peak (many users sync after payday and on the first of the month). One SQL primary will struggle. See Lesson 11 for the stages of scaling a database, and System Design Lesson 25 (sharding).
• Reads are tiny (200/s). A cache and a few replicas are enough.
• 250 GB a month is small for object storage and a warehouse. Keep recent months in the database and send old months to cheaper storage.
• A 12 MB seller map fits in every worker, so rules-first categorisation is cheap.
• The real limit is not our hardware. It is the aggregator's rate limits and each bank's slowness. If a bank answers in 5 seconds and you have 30 million accounts to refresh daily, you need many workers waiting in parallel. The workers are mostly idle, waiting on the network.

3. API

# 1. Start linking (our server asks the aggregator for a short-lived link token)POST /v1/links                       -> { "linkToken": "link-xyz", "expiresIn": 1800 }#    The browser opens the aggregator's own screen with that token. The user signs in THERE.# 2. Finish linking (the aggregator gives the browser a one-time public token)POST /v1/links/complete   { "publicToken": "public-abc" }#    Our server swaps it for a long-lived access token and stores it encrypted.-> 201 { "accountIds": ["acc_1", "acc_2"] }# 3. ReadGET  /v1/transactions?account=acc_1&from=2026-10-01&cursor=...      -> paged listGET  /v1/spending?month=2026-10                                      -> { "groceries": 8823, ... }# 4. Corrections and budgetsPUT  /v1/transactions/{id}/category   { "category": "treats", "applyToMerchant": true }PUT  /v1/budgets/2026-10              { "groceries": 10000, "gas": 5000 }# 5. Refresh (async)POST /v1/accounts/{id}/refresh        -> 202 Accepted# 6. Aggregator -> us (webhook)POST /hooks/aggregator    { "type": "TRANSACTIONS_UPDATED", "itemId": "item_9" }

Notes:

• Money is an integer in minor units, with a currency code. Refresh answers 202 and queues a job.
• The password never touches our servers. Step 1 and 2 are the whole trick, and I explain why in section 6.6.
• The webhook must be verified (a signature check, as the vendor documents) and must be safe to receive twice.

4. Data model

The primer uses accounts, transactions and monthly_spending tables. I keep those ideas and fix three things: no stored password, a unique key for idempotency, and a place for raw data.

users(id, email, created_at)institution_links(                        -- one per "login at a bank"  id, user_id, provider, provider_item_id,  access_token_enc,                       -- ciphertext only (section 6.6)  sync_cursor,                            -- the bookmark from the aggregator  status, last_synced_at, last_error)accounts(id, link_id, provider_account_id, name, type, currency,         opening_balance_minor, last_balance_minor, last_balance_at)transactions(  id,                         -- our internal id  account_id,  provider_txn_id,            -- the aggregator's id  pending_txn_id NULL,        -- the pending row this one replaced  posted_date, amount_minor, currency,  description_raw, merchant,  -- raw text and cleaned merchant name  category, category_source,  -- 'user' | 'rule' | 'model' | 'none'  is_pending, created_at, updated_at,  UNIQUE (account_id, provider_txn_id))      -- <- the idempotency key  INDEX (account_id, posted_date)category_overrides(user_id, merchant, category, PRIMARY KEY (user_id, merchant))budgets(user_id, month, category, limit_minor, PRIMARY KEY (user_id, month, category))monthly_spending(user_id, month, category, spent_minor, PRIMARY KEY (user_id, month, category))alerts_sent(user_id, month, category, level, sent_at, PRIMARY KEY (user_id, month, category, level))raw_payloads  (object store: one file per fetched page, for replay and audits)

Points worth saying aloud:

• UNIQUE (account_id, provider_txn_id) is the single most important line. The database refuses a second copy, whatever the code does.
• alerts_sent has a primary key on (user, month, category, level). Inserting it is how we make sure a notification goes out once.
• Raw payloads go to object storage (see System Design Lesson 17). If the categoriser has a bug, you replay the raw data instead of calling the bank again.
• The primer stores account_password_hash in the accounts table. Do not do that. A hash cannot be used to log in to a bank, so it is useless to you, and storing a bank login at all is the thing to avoid. See section 6.6.
• monthly_spending is a derived table. You can always rebuild it from transactions.

5. High-level architecture

 Browser ---> Web/API ---> Accounts API ---(link token, swap)---> Aggregator <---> Banks                               |                                      |                               | refresh request                      | webhook "new data"                               v                                      v                      Queue: sync-jobs  <------------------- Webhook receiver                               |                  +------------+------------+                  v            v            v             Sync worker  Sync worker  Sync worker      (many, mostly waiting on the network)                  | 1. load cursor + decrypt token                  | 2. fetch a page   -> save raw page to Object Store                  | 3. upsert rows (idempotent), categorise                  | 4. save new cursor                  v            SQL: transactions  --->  Spending job  --->  monthly_spending                                          |                                          v                                   Budget check ---> Queue: notifications ---> push / email            Cache (Redis): recent transactions, monthly totals     Warehouse: history for analytics

This is the primer's picture plus the aggregator on the left and the webhook path. Everything between the bank and our tables is asynchronous. The user never waits for a bank.

6. Deep dives

6.1 Talking to aggregators

Banks do not offer one standard API. An aggregator hides that. Our flow has two kinds of sync:

• Pull: a scheduled job asks "anything new for this link since my cursor?". The primer refreshes daily for users active in the last 30 days.
• Push: the aggregator calls our webhook when it has new data. We turn the webhook into a job in the same queue. We still run a slow daily pull as a safety net, because webhooks can be lost.

Rules for a good client of a flaky upstream:

6.2 Idempotent ingestion and dedup

Duplicates come from four places:

1. Retries. A worker fetches page 2, crashes before saving the cursor, and fetches page 2 again.
2. Overlap. An aggregator re-sends rows near the cursor edge (a safe habit on their side).
3. Pending to posted. A coffee shop hold is p5 for 2,500. Two days later the final row is t5 for 2,750 (tip added), with a new id. If you keep both, the user is charged 52.50 in your app.
4. The same event from two sources (a manual CSV import plus the aggregator). That one needs a fingerprint, below.

My design has three layers:

1. Upsert by (account_id, provider_txn_id). Insert if new, update if changed, skip if the same. The database unique index is the final guard.
2. Apply the page, then move the cursor. If we crash in between, the next run repeats the page, and layer 1 makes that harmless. This is "at-least-once delivery plus idempotent writes", which gives the effect of exactly once (see System Design Lesson 37).
3. Pending replacement. When a row says "I replace pending id p5", delete the pending row and remember p5 as replaced, so a replayed page cannot bring it back.

Here is the core loop I ran (pipeline.ts, shortened):

export function ingestAccount(store: Store, provider: Provider, accountId: string): IngestStats {  const s = { seen: 0, inserted: 0, updated: 0, skipped: 0, replacedPending: 0 };  let cursor = store.cursors.get(accountId) ?? null;  for (;;) {    const page = provider.fetchPage(accountId, cursor);    for (const raw of page.txns) {                       // apply the whole page ...      s.seen++;      if (raw.pendingId) {        store.replaced.add(store.key({ accountId, id: raw.pendingId }));        if (store.txns.delete(store.key({ accountId, id: raw.pendingId }))) s.replacedPending++;      }      if (store.replaced.has(store.key(raw))) { s.skipped++; continue; }   // a replayed pending row      const k = store.key(raw), old = store.txns.get(k);      const merchant = normaliseMerchant(raw.description);      const row = { ...raw, merchant, ...categorise(merchant, store.userOverrides) };      if (!old) { store.txns.set(k, row); s.inserted++; }      else if (changed(old, row)) { store.txns.set(k, row); s.updated++; }      else s.skipped++;    }    if (page.nextCursor === null) break;    cursor = page.nextCursor;    store.cursors.set(accountId, cursor);               // ... THEN move the cursor  }  return s;}

In SQL the same idea is one statement per row: INSERT ... ON CONFLICT (account_id, provider_txn_id) DO UPDATE SET amount_minor = EXCLUDED.amount_minor, ... (PostgreSQL syntax; MySQL uses ON DUPLICATE KEY UPDATE). Do a whole page inside one transaction, and save the cursor in the same transaction. Then the rows and the bookmark move together, and a crash cannot leave them out of step.

I ran it on the fake bank (3 rows per page, 9 rows including one exact duplicate and one pending-to-posted change):

run 1: { seen: 9, inserted: 8, updated: 0, skipped: 1, replacedPending: 1 } provider calls: 3run 2 (cursor at end): { seen: 3, inserted: 0, updated: 0, skipped: 3, replacedPending: 0 }run 3 (cursor lost, full replay): { seen: 9, inserted: 0, updated: 0, skipped: 9, replacedPending: 0 }rows stored: 7

Read it:

• Run 1: 9 rows seen, 8 inserted (the duplicate t3 was skipped), and the pending row p5 was inserted then replaced. Final: 7 rows, which is right.
• Run 2 re-reads only the last page (the cursor bookmark was the start of page 3) and changes nothing.
• Run 3 pretends we lost the cursor and replays everything. Zero changes. That is idempotency.

Fingerprint fallback (not in my demo). If an id is not stable, build a key from (account, date, amount, normalised description) and add a counter for same-day twins (two 3.50 coffees on one day are real). Fingerprints cause false merges, so use them only for sources without ids, and keep the raw rows so a merge can be undone.

6.3 Categorisation: rules first, a model later

The primer's category service is a seller-to-category dictionary seeded with popular sellers, plus a crowd-sourced override map for sellers not yet in it. That is a rules-first design. I like it because it is cheap, fast, explainable ("this was groceries because the merchant is Whole Foods") and easy to fix.

Order of decision in my code:

1. User override for this merchant (category_overrides). It always wins. The primer says "no automatic re-categorisation", which means: do not change a row the user already touched.
2. Rules: a merchant pattern table (regular expressions on the cleaned name).
3. Otherwise uncategorised, with category_source = 'none', so a later step can fill it.

Before matching, clean the description. Raw text looks like POS DEBIT WHOLE FOODS #1042:

export function normaliseMerchant(desc: string): string {  return desc.toUpperCase()    .replace(/\b(POS|DEBIT|CARD|PURCHASE|ACH)\b/g, " ")    .replace(/[#*]\s*\d+/g, " ")        // store numbers like #1042    .replace(/\b\d{3,}\b/g, " ")         // long digit runs    .replace(/[^A-Z& ]/g, " ")    .replace(/\s+/g, " ").trim();}

Result on my sample (real output, from demo.ts):

  t1  2026-10-01  -120000 LANDLORD RENT OCT    housing (rule)  t2  2026-10-02    -5423 WHOLE FOODS          groceries (rule)  t3  2026-10-03    -4010 SHELL OIL            gas (rule)  t4  2026-10-04    -1599 NETFLIX COM          subscriptions (rule)  t5  2026-10-06    -2750 BLUE BOTTLE COFFEE   uncategorised (none)  t6  2026-10-07   300000 PAYROLL ACME CORP    uncategorised (none)  t7  2026-10-08    -3400 WHOLE FOODS          groceries (rule)

WHOLE FOODS #1042 and WHOLE FOODS 1042 both reach the same merchant name, so one rule covers both. Two rows stayed uncategorised, and that is the honest answer for a merchant we do not know. Showing "uncategorised" is better than a wrong guess.

Where machine learning fits. A model (for example a text classifier on the merchant string, amount and day of week) is useful for the long tail: merchants no rule covers. How I would add it:

• Run it only when rules say nothing, and store category_source = 'model' with a confidence score. Below a threshold, leave it uncategorised.
• Train on rows where category_source = 'user', because those are corrections by humans. Be careful: a few users' odd labels should not rewrite a merchant for everyone. Use a vote across many users (the primer suggests tracking the most common override per seller).
• Keep rules as the override layer, so a wrong model result can be fixed with one new rule and no retraining.
• Measure with a labelled sample: accuracy by category, and the share of rows left uncategorised. I did not build or run a model in this lesson.

6.4 Budgets and alerts

The primer suggests a default budget template by income tier and storing only the user's overrides (so we do not store 100 million budget rows). That is a good saving: the table only holds what users changed.

Spending per category is a sum over negative amounts in the month. The alert rule is: for each budget category, if spent >= limit x threshold and we have not sent this level this month, send it and record it. In my demo, the budget is groceries 100.00, gas 50.00, restaurants 80.00:

spend Oct: { housing: 120000, groceries: 8823, gas: 4010, subscriptions: 1599, uncategorised: 2750 }alerts 1: [ 'groceries at 80% (8823/10000)', 'gas at 80% (4010/5000)' ]alerts 2: []

The second call sends nothing, because the first call recorded both alerts. In SQL: INSERT INTO alerts_sent ... ON CONFLICT DO NOTHING, and send the notification only if a row was actually inserted. The "insert, then send" order can lose a notification if we crash in between; "send, then insert" can send twice. For a budget warning, a rare duplicate is better than a missed one, so I would send first, then insert, and make the push message carry a key so the notification service can drop repeats.

Maintaining totals: update them incrementally (fast, but a bug drifts silently) or recompute the touched (user, month) from transactions (slower, self-healing). I would recompute after each sync and run a nightly full check.

Edge cases to name: refunds, transfers between the user's own accounts (not spending), split transactions, and time zones (use the bank's local date).

6.5 Batch versus stream

My answer: both. The unit of work is the same ("sync this link"), so one worker does it. A scheduler (batch) enqueues links that have not synced for N hours, spread over the day with jitter so we do not hit the aggregator all at once. The webhook (stream) enqueues a link right away. Monthly reports, category backfills and the reconciliation check are batch jobs that read the SQL tables or the warehouse.

Ordering: two sync jobs for the same link must not run at the same time, or both would race on the cursor. Either key the queue by link_id (one consumer per key, see System Design Lesson 36 on Kafka) or take a short lock row per link.

Queue basics and dead-letter queues are in Lesson 18 and Lesson 38. A sync that fails 5 times goes to a dead-letter queue and sets the link status to error, and an engineer or the user is told.

6.6 PII and security

This is the part an interviewer for a finance product will push on. Short rules:

1. Never store a bank username and password. The user signs in on the aggregator's screen. Our app gets only a token. If our database leaks, no one gets bank logins, because we never had them. This also keeps us out of the business of handling bank credentials, which carries heavy obligations. (The primer's account_login and account_password_hash columns are the thing to avoid.)
2. Tokenised access. The access token is long-lived and powerful for that one link, so it is a secret. We keep it encrypted, not in plain text, and scope it: it can only read data for that item, not move money (check the scopes your aggregator offers).
3. Encrypt at rest and in transit. TLS everywhere (DevOps Lesson 4). Disk or database encryption as a baseline. Then field-level encryption for the access token, and consider it for account numbers.
4. Envelope encryption. Each token is encrypted with a data key. The data key is wrapped by a master key that lives in a key management service and never leaves it. Rotating the master key means re-wrapping data keys, not re-encrypting all data. See System Design Lesson 51 (encryption and secrets).
5. Least access. Sync workers can decrypt tokens. The web tier and analytics cannot. Analysts see a masked or tokenised copy. Use separate IAM roles (DevOps Lesson 5) and a private network (DevOps Lesson 6).
6. Minimise and expire. Store the last 4 digits of an account number, not all of it. Delete data when the user unlinks (and revoke the token at the aggregator). Keep raw payloads for a limited time.
7. No secrets or PII in logs. Log ids, not descriptions or amounts tied to names. This is the most common leak.

I ran a small sealing function with Node's built-in crypto (AES-256-GCM, a random 12-byte nonce, and the user and item ids as "additional authenticated data"):

export function seal(token: string, key: Buffer, aad: string): string {  const iv = randomBytes(12);  const c = createCipheriv("aes-256-gcm", key, iv); c.setAAD(Buffer.from(aad));  const ct = Buffer.concat([c.update(token, "utf8"), c.final()]);  return Buffer.concat([iv, c.getAuthTag(), ct]).toString("base64");}

The test shows three things: the token round-trips; the blob does not contain the plain text; and opening it with a different aad (user:8 instead of user:7) throws. That last point means a stolen blob copied onto another user's row does not decrypt. In my test the key is random bytes held in memory. In production the key comes from a key service, not from the code or the database.

6.7 Reconciliation

How do you know the numbers are right? Compare two independent facts. The bank says "the balance is B". We know "opening balance O plus the sum of all posted transactions S". If O + S = B, we have everything. If not, something is missing or doubled.

reconcile: { ours: 262818, theirs: 262818, diff: 0, ok: true }reconcile after losing t4: { ours: 264417, theirs: 262818, diff: 1599, ok: false }

I deleted the 15.99 subscription row and the check reported a difference of exactly 1599, the size of the missing row. A difference equal to one amount usually points to one missing or doubled transaction. Use it like this:

• Run after every sync for the touched account, and nightly for all accounts.
• Compare posted rows only. Pending rows are not in the bank's ledger balance (some aggregators give an "available" balance that does include holds, so compare like with like).
• On a mismatch: mark the account needs_review, show the user "some data may be missing" instead of wrong totals, and trigger a backfill from the raw payloads or a re-sync of the date range.
• Track the mismatch rate per bank. A bank with a high rate has a data problem to report to the aggregator.

6.8 Scaling the pipeline

• Database: 2,000 writes/s on average. Partition transactions by user_id (all of one user's rows together, so totals stay on one shard). See System Design Lesson 25 and Lesson 26. Keep the last few months in SQL and move older rows to a warehouse or object storage, as the primer suggests.

7. Failure modes and trade-offs

Trade-offs to say aloud

• Rules vs model: rules are explainable and cheap, a model covers the long tail. Rules first, a model for the rest, user corrections as the final word.
• Batch vs stream: batch is simple and spiky, stream is fresh and needs queue discipline. Use both with one worker.
• Send-then-record vs record-then-send for alerts: a rare duplicate against a rare miss.

8. How to say it in an interview (60 seconds)

"The user links a bank through an aggregator's screen, so we never see a password. We get a token and store it encrypted with an envelope key. Syncing is a queue of 'sync this link' jobs, started by a daily scheduler and by webhooks. A worker loads the cursor, fetches a page, saves the raw page, upserts the rows with a unique key on account and provider transaction id, and only then saves the new cursor. So a crash or a replay changes nothing. In my test, replaying all nine rows with a lost cursor made zero changes. A pending row that later posts with a new id is replaced, with a tombstone so a replay cannot bring it back. Each row gets a category from the user's override first, then rules on the cleaned merchant name, then optionally a model for the long tail. Monthly totals feed budget alerts, recorded in an alerts table so each level goes out once. Reconciliation compares opening balance plus posted rows with the bank's balance; when I removed one 15.99 row, it reported a difference of exactly 1599. Writes are about 2,000 per second, so I shard transactions by user and cache reads."

Run it yourself

Put pipeline.ts, fake.ts, demo.ts and pipeline.test.ts in one folder. Run node demo.ts and node --test pipeline.test.ts. They use only Node's standard library and import each other with the .ts extension, which Node 22's type stripping needs. Result of my last test run:

ok 1 - normalise strips noiseok 2 - ingest twice is idempotentok 3 - pending is replaced by postedok 4 - rules first, user override wins, unknown stays uncategorisedok 5 - alerts fire once per level per monthok 6 - reconcile detects a missing transactionok 7 - sealed token round-trips and is bound to its context# tests 7   # pass 7   # fail 0

The code uses in-memory maps instead of a database, so it does not show transactions, locks or concurrent workers. Treat it as a model of the logic, not as production code.

Try it

1. Two coffee purchases of 3.50 happen on the same day at the same shop. A fingerprint of (account, date, amount, merchant) gives the same value for both. What goes wrong, and what is the fix? Answer: they would be merged into one, so spending is under-counted. Use the provider id when there is one. If you must fingerprint, add a counter per identical fingerprint per day. 2. Your sync job saves the new cursor first and then writes the rows. It crashes in between. What is lost? Answer: the rows of that page are never fetched again, because the cursor already moved past them. Always apply the rows, then move the cursor (best in one database transaction).

Interview questions

Key takeaways

• It is a pipeline problem: link, fetch, upsert, categorise, total, alert, reconcile. Do the arithmetic first: about 2,000 writes/s and 250 GB a month.
• Never store a bank password. The aggregator's link screen gives you a token, and you keep it encrypted with an envelope key.
• Idempotency comes from a unique key, from apply rows then move cursor, and from a tombstone for replaced pending rows. My own first version failed the replay test without the tombstone.
• Categorise rules first, with the user's override as the final word. Use a model only for the long tail, with a stored source and confidence.
• Alerts need their own table so each level is sent once.
• Reconciliation (opening balance plus posted rows against the bank balance) is how you find a missing row. In my test it reported exactly 1599.
• Use both batch and stream with one worker, and never run two jobs for one link at once.

What's next

Lesson 11: From One User to Millions: Scaling a Web App on AWS. This lesson showed a pipeline with queues, workers, a cache and replicas. Lesson 11 shows the order in which you add each of those parts as users grow, what breaks at each stage, and which number tells you it is time.