How I'd take a card at a coffee shop · haily.yevklnekqt.blog

If you’ve seen a payment system drawn as a website checkout, it usually looks like this: the merchant creates a payment, the customer confirms later, a worker talks to the card network, and once a day you settle. That is a real product. It is not a coffee shop.

At a register, someone taps a card for a latte. They might add a tip thirty seconds later. The approval has to come back while they are still at the counter. At 10pm the store sends that day’s charges to the banks that actually pay them — not one Visa file.

This note walks through that system in the order I’d teach it. We start with one latte so the words mean something. Then we look at the records — they live in a relational database — the API, and the states. After that: why the tap stays live, how we keep the card number off our servers, how retries don’t double-charge, how the ledger works, how the 10pm files actually get built, and how we tell the rest of the store without blocking the receipt.

1. One latte, start to finish

Oakland 12 rings up $4.75. Follow the money.

The customer taps. Our server asks the customer’s bank: “set aside $4.75.” The bank says yes. That is authorize — a hold. Nothing has moved yet. The card still has the money; it just can’t spend those $4.75 twice.
The tip screen: +$1.00. We tell the bank: “actually take $5.75.” That is capture. The register prints a receipt. The customer leaves.
At 10pm a job collects every captured row for the day, grouped by the receiving bank — the bank that pays the store. It writes small files and uploads them.
The bank says “got the file.” Hours later it sends a result file: this line posted, that line rejected. Money in the store’s account is a third step, often the next day.

If you remember only the sequence: hold → capture → file per bank → result later. Capture is when the register is done with the customer. Settlement is tonight’s paperwork. Deposit is later still. Mixing those three up is how you end up marking a payment “settled” because an upload succeeded.

2. What to build

Before we draw boxes, lock the product. Otherwise you accidentally design Stripe.

We’re building a till:

Tap at the counter: authorize fast, then capture
A tip or a cancel after the tap, so the final amount can differ from the hold
A receipt in well under a second
A 10pm job that builds one set of files per receiving bank
Refunds only after capture (or after settle)
A way to retry hold, capture, and the nightly job without charging twice

Wallets, peer-to-peer, subscriptions, and a full merchant dashboard can wait. They don’t change the till.

Four questions hide most of the rest of the design. Answer them before you add a queue.

Hold then capture, or one charge?
Two steps. Hotels hold at check-in and capture at checkout. Amazon authorizes at order and captures at ship. A coffee shop holds at tap and captures after the tip.

One charge call (auth+capture together).

Pro: one network round trip, one state (captured or failed).
Why: fewer attempts to reconcile. A website with a known total likes this.
Con: you cannot change the amount after the bank said yes.
Why: the issuer locked $4.75. A $1 tip is a second charge or a guess before they tap.
Con: a guessed tip that they skip is money you have to refund.
Why: capture already moved it. Refund is another attempt, another dispute surface.

Hold, then capture. This is the one I’d take for a till.

Pro: the tip screen is a second amount on the same payment.
Why: capture can be $5.75 after a $4.75 hold (within the network’s rules). One intent, two attempts.
Con: two calls, two timeouts, two idempotency keys.
Why: that is real. It is still cheaper than a second charge or a refund for a skipped tip.
Con: a hold that never captures must expire or cancel.
Why: you left money locked. That is a sweeper, not a reason to merge the calls.

Who is “the bank”?
Three companies, and people mash them together:

Issuer — the customer’s bank (Chase, if they tapped a Chase card)
Network — Visa / Mastercard. They move the message
Acquirer (receiving bank) — who the store settled with. That’s who sends the nightly report

Not storing the card number does not mean you don’t know where the payment went. When you sent the tap, you picked an acquirer. Save that as acquirer_id. PCI is “don’t keep the number.” Routing is a column you wrote on the way out.

When does money actually move?
Authorize = lock. Capture = “yes, take it” — the register can treat this as done. Settle = the acquirer filed it. Deposit = later.

What can be slow?
Not the tap. Settlement, emails to the back office, reconciling the result file: those can wait.

Security, retries, durability, and high request rates all matter. For a coffee chain, the numbers that actually bite are: authorize in well under a second, never double-charge on retry, and get every captured row into the right bank file at 10pm. Only add a box if it helps one of those.

3. Two records: the intent, and each attempt

The first data-model mistake is one row with a status. It looks clean. Then the first authorize times out, you retry, and you can no longer tell “we asked Chase twice” from “we asked once.” Disputes need that history.

So you keep two records.

A payment is the business intent: “Oakland 12 wants $5.75 from this tap.” One row. It has a status: created, authorized, captured, settled, failed, refunded.

An attempt is one try to talk to the bank: this authorize, that capture, a refund, a settlement submit. One payment can have several attempts — the first authorize timed out, the second succeeded, later a refund.

Merchant     id, name, api_key, status

Payment      the intent
  payment_id
  merchant_id
  amount_authorized, amount_captured   -- cents, not 4.75
  currency
  state
  acquirer_id                          -- which receiving bank
  payment_method_token                 -- not the card number
  batch_part_id                        -- null until the 10pm job claims it

Attempt      one call to a bank
  attempt_id
  payment_id
  type          authorize | capture | refund | settle
  status        initiated | success | failed | retrying
  provider_ref  what the bank returned
  idempotency_key
  processed_at

Never put card number, expiry, or CVC on Payment. We’ll come back to why.

These rows live in a relational database (Postgres is the one I’d pick). That is not a default box on a template. Payments need:

One transaction, several writes. Move the payment to captured and write two ledger rows that sum to zero. If those can commit separately, the books already lie. A relational store gives you BEGIN … COMMIT across tables.
Keys the database enforces. Unique on (merchant_id, idempotency_key) so two machines cannot insert the same retry. Foreign key from Attempt.payment_id to Payment, so you cannot record a bank call for a payment that does not exist.
Legal states in the schema. A check constraint or an enum so created → settled is impossible even if the handler has a bug.
Reads that join. Support asks “show me this latte and every time we talked to the bank.” That is Payment plus its Attempt rows, not a scavenger hunt across logs.

A document store or a wide-column store can hold JSON. They make the next sentence hard: “those four writes either all happened or none did.” Redis can cache the idempotency lookup. It is not the source of truth for money. If Redis restarts, the unique row in Postgres is still there.

I would not start on a ledger in Kafka, or on a database that only offers eventual consistency for the tap. Eventual is for the receipt email.

Relational (Postgres) vs document / wide-column vs “the log is the ledger.”

Relational — Pro: one BEGIN covers payment + attempts + two ledger rows; unique keys and check constraints. Con: you will shard later by merchant_id.
Why pro: money is several rows that must not diverge. Why con: one instance will not hold a global chain forever — that is a scale pass, not a reason to skip ACID on day one.
Document store — Pro: dump a payment blob. Con: “update status and append two ledger lines atomically” becomes application luck.
Why: most document DBs do not give you multi-document transactions as the default mental model. A crash between two writes is a dispute.
Kafka as the ledger — Pro: you never lose the stream of events. Con: the register cannot wait for a consumer to say “authorized.”
Why: a log is at-least-once and async. The person is at the counter. Use the log after capture, not instead of Postgres on the tap.

4. The API

The card reader talks to us; we talk to a bank. Two calls do almost all of the product:

POST /v1/holds
POST /v1/holds/{hold_id}/capture

POST /v1/holds
{
  "merchant_id": "store_oakland_12",
  "amount": 475,
  "currency": "USD",
  "payment_method": "tok_…",
  "idempotency_key": "term-88-20260825-104411-01"
}

→ 201
{
  "payment_id": "pay_01",
  "hold_id": "hold_01",
  "state": "authorized",
  "amount": 475,
  "expires_at": "2026-08-25T18:44:11Z"
}

POST /v1/holds/hold_01/capture
{
  "amount": 575,
  "idempotency_key": "term-88-20260825-104411-01-cap"
}

→ 200
{
  "payment_id": "pay_01",
  "state": "captured",
  "amount_authorized": 475,
  "amount_captured": 575
}

Amounts are integer cents, so you never fight floating point. Same idempotency_key means the same payment — if the reader retries, you return the saved result. A new key is a new charge, on purpose.

A website often splits “create payment” (intent, no money) and “confirm” (customer pays). At a register those collapse: the tap is the confirm. You still keep hold and capture as two calls because of the tip.

The rest of the surface is small:

GET  /v1/payments/{payment_id}
POST /v1/payments/{payment_id}/refunds    {amount, idempotency_key}
GET  /v1/merchants/{id}/payments?state=&from=&to=
POST /internal/settlement/run             {business_date, idempotency_key}

GET returns the payment plus its attempts, so support can see the trail. Refunds only from captured or settled — enforced in the database, not only in the handler.

5. States

Think of a payment as sitting in one named place, with only some moves allowed. You should not be able to settle something that was never authorized. Put that in the database, not only in application code. Application code gets retried. The WHERE clause does not.

Happy path: created → authorized → captured → settled.

At the store	State
Card tapped, bank said yes	`authorized` (hold)
Tip entered	`captured`
10pm file received by that bank	still not `settled` — wait for their result
Their result says posted	`settled`
Nobody captured in time	`expired`
Bank said no	`failed`

Refunds from captured or settled only. A hold that wasn’t captured is cancelled or expires — don’t call that a refund. The customer never lost the money; you just released the lock.

Attempts have their own life: initiated → success, or failed then retrying if the error looks temporary (timeout), or stay failed if it’s permanent (stolen card). Don’t retry a decline in a loop. The issuer already said no.

If you merge authorize and capture, you have to guess the tip or run a second $1 charge. Two calls is the product, not extra ceremony.

6. The tap (keep it live)

Left to right, for a register:

The reader says pay → POST /holds.
We write a payment row (created, then authorized once the bank answers) and a ledger line. If the bank never comes back, the row still exists and says where it stopped.
The gateway asks the bank to authorize. (The sketch says “charge”; label that arrow auth. We have not captured yet.)
Approve comes back. Receipt prints. Customer puts the card away.

Some designs put step 3 on a queue: the API returns, a worker calls the bank later.

Authorize on a queue.

Pro: a slow bank does not hold HTTP threads. The website can show “processing.”
Why: the customer already left the button. Decoupling helps that product.
Con: at a counter the person is waiting on your queue depth, not on the bank.
Why: you added a hop. P99 is now broker + worker + bank. The receipt is late.
Con: “approved” can sit in a partition while they put the card away.
Why: at-least-once delivery is not a yes/no in 300ms.

Call the bank in the request. This is the one I’d take for a till. Queues are for 10pm and for “tell the store’s other software.”

Pro: one round trip. The reader blocks on the issuer, which is the real latency.
Why: no extra hop you control.
Con: a stuck bank occupies a request worker.
Why: timeouts and a pool cap are the fix — fail the tap — not a lie via Kafka.

  Card reader                   Our payment service           Banks
  (wait for yes/no)             (orchestrator)
       |                              |
       |  hold / capture              |  auth / capture
       |  + idempotency key           |
       +----------------------------->+------------------>  Acquirer A
                                      |                    Acquirer B
                                      |  record every step
                                      v
                                Relational DB (Postgres, keyed by store)
                                Ledger (two rows, sum 0)

  Later: 10pm files, webhooks to the store's back office

A relational database is enough. You want one ACID transaction for “move to captured and write two ledger rows.” If those can diverge, your books are already lying. Postgres (or MySQL, same idea) is that box. I would not put the tap’s source of truth in a queue or a cache.

7. Security

Two ways a naive design blows up: the card number lands on your servers, or one internal box can impersonate another.

The card number should never touch us

If the full card number, expiry, or CVC hits your API, logs, queue, or database, you fall under PCI DSS level 1: audits, key management, facility rules, a lot of cost. A misconfigured log that prints a PAN is a breach, not a bug.

Store the PAN yourself (encrypt it, “we’re careful”).

Pro: you can retry a bank without the terminal. Switching processors looks easy.
Why: you have the number.
Con: you are now a card vault. PCI level 1 is the job.
Why: the number on your disk is the thing thieves want. Encryption does not remove the audit; it is the audit.
Con: a debug log, a support export, a replica snapshot — any of them is a breach.
Why: PAN wants to leak. Tokens do not spend.

Tokenization (certified iframe / terminal → vault → pm_abc123). This is the one I’d take.

Pro: a leak of our database is not a leak of card numbers.
Why: we never saw the PAN. We store a stand-in the vault will accept.
Pro: PCI scope shrinks to “we handle tokens.”
Why: the certified widget is on the processor’s path, not ours.
Con: you cannot authorize if the vault is down, and switching processors means re-tokenizing.
Why: the number lives in their house. That is the fee you pay to not be a bank.

Tokenization is the fix: replace the number with a stand-in that is useless to a thief.

Customer enters the card in a certified place — hosted fields on the web, a certified terminal in a shop.
That widget talks to a vault (the processor). Our backend is not on that path.
Vault returns pm_abc123 (a token).
Browser or terminal sends only the token to us.
We store the token. We send the token when we authorize.

A leak of our database is not a leak of card numbers. Log the token, never the payload.

On a coffee shop the PAN stays in the terminal / bank tunnel — same idea, different box. We still save last4, brand, and acquirer_id.

“We don’t store cards, so we don’t know the bank” mixes those up. PCI = don’t keep the number. Routing = a column we wrote when we sent the tap.

Schema: drop any card_number / cvc field. Add payment_method_token.

One compromised worker should not pretend to be another

If auth workers, the payment API, and the 10pm job talk with no identity checks, one hacked box can issue refunds or settle by impersonating a friend. That’s lateral movement.

Mutual TLS (mTLS) is both sides of an internal call showing a certificate, not just the server. Each service (payment API, authorize worker, batch job) has its own cert from an internal CA. The payment database only accepts writes from services that are allowed to write. A random box presenting “I’m the settler” gets refused.

You don’t need to draw a full mesh on day one. You do need: internal calls are authenticated, the batch job cannot hit POST /refunds, and we don’t put raw cards on the wire inside our network either.

8. Exactly once

In payments, doing the happy path twice is the bug. Networks drop. Readers retry. Workers crash after the bank said yes. The 10pm job gets rerun. If you only handle the clean path, you double-charge.

What breaks without extra machinery:

A retry authorizes the same card twice
A worker charges the bank, then crashes before saving the row (customer held, we have no record)
The 10pm job is rerun and settles the same payment twice
Two workers update the same row and the states fight

No key: reader posts $50, network drops, reader retries, you charge twice.

With key abc123: you store abc123 → payment_id and the response. Same key comes back → return the saved body. Do not call the bank again.

Four things, used together:

1. Idempotency keys on every money call.
Hold, capture, refund, and the 10pm batch. Unique in Postgres on (merchant_id, idempotency_key). Redis can cache lookups; the unique row is what stops two machines racing. Stripe’s Idempotency-Key header is this idea. Also send our key to the bank if they support it, so they dedupe too.

2. Transactional outbox — don’t split “save” and “call the world”.
The nightmare: bank said yes, process died, no local row.

The pattern: in one database transaction, write the payment change and a row in an outbox table (“please authorize pay_01”, or “please settle this batch”). Commit. A worker reads the outbox and does the external call. If the worker dies, the row is still there. That’s at-least-once delivery; the idempotency key makes the extra delivery safe.

Outbox
  event_id
  type          authorize | settle | webhook
  payload
  status        pending | sent | failed
  created_at

Do not put the tap on a queue or outbox so the HTTP request returns before the bank answers. At a register the person is waiting. Call the bank in the request. Still write created + the key first, then call, then write the result. If we die in the middle, a retry with the same key sees created and asks the bank again with the same key (or an inquire). It does not open a second hold.

Do use the outbox for webhooks and for “build tonight’s file” — work that can wait.

3. Legal state moves in the database.
UPDATE … SET state = 'captured' WHERE state = 'authorized'. Two workers: one gets zero rows. A version column (optimistic locking) is the same idea. You cannot jump created → settled.

4. A batch token on the nightly run.
Each run has a batch_id / part id. Payments already tagged are skipped. Otherwise a crashed 10pm job settles the same latte twice.

Schema adds: payment.batch_part_id, attempt.idempotency_key, the Outbox table. Keep the response body next to the key so a retry doesn’t rebuild slightly different JSON.

9. Two ledger rows that sum to zero

Every money event writes two lines that add to zero. Authorize $4.75:

debit customer_hold 475
credit merchant_receivable 475

A refund reverses the pair. If they don’t sum to zero, something is already wrong — you don’t wait until month-end. Matching a bank file is then comparing two lists. Append only; don’t edit a running total in place.

Payment row + two ledger rows: same database commit. If the payment says captured and the ledger doesn’t, you will spend the next morning arguing with a spreadsheet.

10. 10pm files per receiving bank

A common sketch is: cron at midnight, find captured payments, send a batch, mark settled. That’s the right shape. It usually skips how big the file is, how you restart a worker, and that “the bank” is each acquirer.

Visa will sell you a network report. You still want Chase’s file, Adyen’s file, … — different formats, different cut-offs. The store settled with those banks, not with Visa as a pile of money.

Authorize is not settlement. The tap is a fast request/response. Settlement is almost never a 10,000-request-per-second HTTP API. If it were, you would not pack the day into one upload. What the receiving bank actually gives you is a file drop (SFTP, S3, a batch window): header, detail lines, trailer with counts and totals. They ack receipt (“we got it”). That ack is not the result. They process later, on their clock. A result file comes back — that is the asynchronous answer. Deposit is later still.

Don’t make one 40GB file

If the chain were running at 10,000 taps/sec all day: 10,000 × 86,400 seconds = 864 million rows. Ten banks, even split: 86.4 million each — not 86,400. 86,400 is the number of seconds in a day. At 500 bytes a line, ~43 GB per bank.

Uploads are usually capped at hundreds of MB. A 40GB put that dies at 97% starts over. Split on row count: 100,000 rows ≈ 50 MB → hundreds of part files per bank per day. Header can still say batch_id, part 17 of 864. Same logical batch, many objects.

Receipt ack ≠ result. Don’t flip every payment to settled just because SFTP succeeded.

Don’t use a change feed for the cutoff

CDC (every database write becomes an event) as the 10pm selector.

Pro: you already have a stream for webhooks and search. One pipe.
Why: the diary of writes exists. Reusing it looks cheaper than a second query.
Con: cutoff is a frozen set, not “events we happened to see.” Feeds lag; shards lag differently.
Why: a capture at 9:59:50 can land in the stream at 10:00:20. A capture at 10:00:01 can appear first. You double-send Tuesday or drop a row into Wednesday’s file.
Con: S3 is an object, not a log you append line by line.
Why: stream-into-S3 means local part files, a worker that can die mid-part, and the nasty case object landed, database write did not. Orphan file; next run does not know those rows were already sent.

Scan the primary at 10pm.

Pro: no replica lag. The WHERE captured_at < cutoff set is exact.
Why: you are reading the same machine that took the tap.
Con: 86 million rows of sequential read sit on the till’s database.
Why: settlement is a huge scan. Authorize is a 300ms point write. Mixing them makes the receipt wait on WAL and buffer cache from the night job.

Read replicas after cutoff, SQL snapshot. This is the one I’d take. Direct reads. Shard by merchant_id. After 10pm the day’s captured rows do not move.

Pro: the set is defined by columns (state, captured_at, batch_part_id), not by consumer offset.
Why: ORDER BY payment_id always rebuilds the same part if you restart. That is a file, not a stream.
Pro: the tap’s primary stays for holds and captures.
Why: the heavy read is on a follower. Authorize P99 does not share the scan.
Con: a replica can be behind.
Why: if you query at 10:00:00 on a replica that is 30 seconds late, you miss rows that already committed on the primary. Wait until replay is past cutoff (or start the job at 10:05 once lag is known). Lag is a delay; CDC lag is a wrong set.

Sort by payment_id and you always get the same order, so a restart rebuilds the same part.

WHERE state = 'captured'
  AND captured_at >= day_start AND captured_at < cutoff
  AND batch_part_id IS NULL
ORDER BY payment_id
LIMIT 100000

Same sort order → same file bytes if you restart. Object key: s3://settle/{acquirer}/{date}/shard_{n}/part_{k}.csv.

SettlementPart
  acquirer_id, business_date, shard_id, part_seq
  first_payment_id, last_payment_id
  row_count, amount_cents
  s3_uri
  state     writing | uploaded | submitted | ack_receipt | result_applied
  idempotency_key

Worker, one shard, one bank:

Select the next 100k ids from the replica. Empty → done.
Write the file locally (trailer = count + sum). Crash here: throw away the local file. Nothing is claimed yet.
Upload to a deterministic object key. Crash after S3, before the database: retry the same key (overwrite is fine). You do not invent a second file for the same rows.
One transaction on the primary: save the part row (s3_uri, first_payment_id, last_payment_id); UPDATE payments SET state = 'settling', batch_part_id = $part WHERE id IN (…) AND state = 'captured'. The AND state = 'captured' stops a double claim. If this transaction fails, the object may already exist — next run uses the same key and claims again.
A submitter sends uploaded parts to the bank. Crash after send, before we record their receipt: submitter retries the same file id. Their side must treat that file id as idempotent too.
Receipt → ack_receipt. Not settled yet.

Restart cursor: last committed last_payment_id, then payment_id > that. Uncommitted work is still captured and will be picked again. That’s why we sort.

Recon: store mismatches, don’t UPDATE 86 million rows at once

When the result file lands, most lines match.

One UPDATE payments SET state = 'settled' WHERE batch_part_id = … for the whole file.

Pro: one statement. Looks like the happy path.
Why: every row in the part posted, so flip them all.
Con: 86 million rows in one transaction is a WAL and lock storm.
Why: Postgres has to write every row to the log and hold locks. A crash restarts the whole statement. The tap on that shard waits.
Con: twelve rejects block or wrongly settle with the rest.
Why: the file is not all-or-nothing. Mixing “posted” and “rejected” in one UPDATE is how you lie.

Exception table + chunked updates. This is the one I’d take. You do not need a second copy of every payment. You need an exception table for the rest.

SettlementException
  acquirer_id, business_date, part_id
  payment_id          -- null if they have a line we don't
  provider_ref
  kind                missing_theirs | extra_theirs | amount_mismatch | rejected
  our_cents, their_cents
  status              open | resolved

If the trailer matches, mark the part result_applied, then move the matching payments to settled in chunks of 1,000–10,000 ids on that store’s shard, with a cursor. Crash, continue. An 86-million-row UPDATE on one machine is a WAL and lock storm, not a shortcut.

Twelve rejected lines: those twelve stay settling (or go back to captured with a reason) and get exception rows. The other 99,988 can settle. Don’t block the whole file.

11. Webhooks

The till already knows the latte happened. The store’s other software — inventory, payroll, a tablet in the back — shouldn’t poll “is it captured yet?” every few seconds. We POST them when something important happens.

Do not wait for their server before you print the receipt. If inventory is down, the latte still happened.

Their software polls GET /payments every few seconds.

Pro: you do not run a dispatcher. Their outage is their problem.
Why: no HMAC, no retry schedule, no dead-letter queue on your side.
Con: 10,000 stores × a poll is empty work, and they still learn late.
Why: most GETs return “nothing new.” Capture already happened at the counter; they are reconstructing it from a pull.

POST them from our request, wait for 200, then print.

Pro: inventory is never behind the receipt.
Why: you serialized two companies in one HTTP call.
Con: their timeout is now the customer’s timeout.
Why: the till should not care if payroll is down. That is the whole point of an outbox.

Outbox row in the same commit as captured, dispatcher later. This is the one I’d take.

Pro: the receipt does not wait. The event cannot vanish if we crash.
Why: BEGIN writes payment + ledger + webhook_events. Commit. A worker POSTs. At-least-once; their idempotency key makes the extra POST safe.
Con: they can be seconds behind.
Why: that is allowed. The customer already left. Back office is not the tap.
Con: you now operate retries, HMAC, a dead-letter queue.
Why: cheaper than blocking the register, and cheaper than 10,000 polls.

How: in the same database transaction that sets captured (or settled), insert a webhook_events row. That’s the durable trigger — the outbox idea again. A dispatcher (a separate service) reads new rows, or a queue, and HTTP POSTs the store.

WebhookEvent
  event_id
  merchant_id
  type              payment.authorized | payment.captured | payment.settled
  payload           payment_id, amount, status, …
  status            pending | delivered | failed
  attempts
  last_attempt_at
  created_at

MerchantWebhookConfig
  merchant_id
  url
  signing_secret

To actually ship this:

Exponential backoff: 1 min → 5 min → 30 min → 2 hr, then a dead-letter queue a human can see
Track status, attempt count, last attempt
Sign the body with HMAC and the shared secret so they can tell it’s us, not a spoof
Put an idempotency key in the payload so they can ignore our retries (we deliver at-least-once)
Dispatcher never sits on the tap path

If the bank is down during the tap: fail fast, retry only timeouts, optional backup acquirer as a new hold (new key), not a silent second charge. That’s the register, not the webhook. Don’t mix those two failure modes.

12. If this gets bigger

Shard key is the id you split data on. Pick it for the queries you actually run.

payment_id.

Pro: writes spread evenly. No hot store.
Why: ids are random-ish. Each insert hits a different machine.
Con: Oakland 12’s day and Chase’s 10pm file touch every shard.
Why: “all captured rows for this store” and “all rows for this acquirer” become scatter-gather. Settlement is the workload that hurts.

merchant_id / store_id. This is the one I’d take first.

Pro: one store’s taps, tips, and refunds live together. Statements are local.
Why: the product query is “what did Oakland 12 do today,” not “payment 7f3a.”
Con: a huge franchise under one id becomes a hot shard.
Why: the key is the tenant. Split a hot tenant later; don’t pick a key that makes every night job global.

acquirer_id.

Pro: 10pm grouping is already local.
Why: the file is WHERE acquirer_id = Chase.
Con: one store with two banks spans machines. Support by store is scatter-gather.
Why: the till is store-shaped. Only pick this if settlement is the main workflow they will grade you on.

Shard by store, run the 10pm job per shard, then stitch parts per bank if they want one logical batch.

Ten times the taps: more API machines, a connection pool in front of Postgres (PgBouncer is a waiting room so you don’t open 10,000 database sockets), cache the idempotency lookup if it’s hot (cache is a hint; the unique row is the lock). Authorize stays live. If the bank is rate-limiting, the reader says busy — don’t lie with a queue.

Global: authorize near the store. Keep settlement files in-region (residency rules). Same reason the card number never arrives.

Recap

Start from the counter. The tap’s source of truth is a relational database: one transaction for the payment, the attempts, and two ledger rows. Hold is live and safe to retry. Capture is live and safe to retry. The card number never sat on our server. Settle is tonight, grouped by the bank that pays us. Recon is comparing two lists. Webhooks leave after the receipt is already printed.

If a box doesn’t help one of those, it doesn’t belong on the till.