Data model: money you can trust and ids you can sort

Every app I'd built before Fable let the database pick its own primary keys and stored money in whatever numeric column was handy. Fable couldn't afford either shortcut. It's a group-expenses app: the entire product is a promise that "you owe Priya ₹1,433.34" is exactly right, forever, no matter how many phones added expenses offline and retried on a flaky train-station connection. That promise fails in two boring, specific ways if you model the data casually — floating-point money and server-assigned ids — so those are the two decisions this chapter is built around. Everything else in the schema is downstream of getting them right.

The problem, stated precisely

Open a JavaScript console and type the most famous bug in computing:

0.1 + 0.2
// 0.30000000000000004

That isn't a JavaScript quirk; it's IEEE 754 double-precision doing exactly what the standard says. A double stores numbers in binary, and 0.1 has no finite binary representation any more than 1/3 has a finite decimal one. Now imagine that error compounding across a trip's worth of expenses, three-way splits, and settlements. A ledger that's off by a hundred-thousandth of a rupee is a ledger nobody trusts — and "the app says I owe a different number than you do" is the one bug that ends a friendship and uninstalls the app.

The second problem is quieter. If the database assigns ids (SERIAL, auto-increment), then a row doesn't have an id until the server has written it. But Fable is optimistic and offline-tolerant: the moment you tap "Add expense," the card has to appear in the chat, get referenced by a message, and be retry-safe — all before the server has confirmed anything. A client that can't name a row until the server does can't do any of that.

Options considered

For money, three candidates:

For ids, also three:

Decision and why

Money is paisa-as-bigint: every amount is an integer count of the currency's minor unit, stored in a BigInt column, and never — anywhere in the stack — a float. Ids are ULIDs, stored as varchar(26) and generated wherever the row is first conceived, which for money-shaped writes is the client.

The money decision is enforced by a tiny package, @hisaab/money, that is the only place in the codebase that knows money is a bigint. It exports a branded type:

export type Minor<C extends CurrencyCode = CurrencyCode> = bigint & {
  readonly [minorBrand]: C;
};

const a: Minor<'INR'> = minor(240000n, 'INR');   // ₹2,400.00
const b: Minor<'USD'> = minor(500n,    'USD');   // $5.00
add(a, b);                                        // ❌ compile error

The brand does real work: it's a phantom type that makes add(inr, usd) a type error, so you cannot accidentally add two currencies. That matters because of the second half of the money model — currency lives on the group, not the expense. groups.currency_code is a char(3) defaulting to 'INR', immutable once any expense exists. Notice what's absent: there is no currency column on expenses or settlements. Mixed-currency groups aren't forbidden by a validation rule you could forget to run — they're structurally impossible, because there is nowhere to put a second currency. That's the strongest kind of invariant: one the schema can't express the violation of.

The ULID decision pays off twice. First, sortability buys index locality — the argument from the B-tree indexes lesson cashed in, though it's worth attributing the benefit to the right index. The ULID's time-ordering is a write-path win, and it belongs to the primary key: consecutive inserts land at the right edge of the PK B-tree instead of splitting random pages all over it, which is exactly what UUIDv4 gets wrong. The hot read — "the latest 50 messages in this group" — is served by a different structure, the composite secondary index (group_id, created_at DESC): narrow to the group on the leftmost column, then walk the leaf entries in stored order, no sort. (Expenses get the analogous (group_id, spent_at DESC) — and spent_at is user-settable backdating, so that index's order deliberately isn't assumed to match insert order.) Sortable PKs make the writes cheap; the composite indexes make the reads cheap. Second, client-generatable ids are the foundation of idempotency — which is the next section.

How it works

The ledger is four tables, and the shape is the whole point:

• expenses — one row per expense. amount_minor bigint, a split_mode (equal | exact | percent | shares), and the raw split_input as JSON so a split can be re-derived exactly.
• expense_splits — who owes what: (expense_id, user_id) primary key, owed_paisa bigint.
• expense_payers — who paid what: same shape, paid_paisa bigint. Two tables, not one column, because an expense can have multiple payers.
• settlements — money actually moving between two people, as a small state machine (requested → marked_paid → confirmed, plus disputed/cancelled).

A one-payer, four-person dinner is one expenses row, four expense_splits rows, and one expense_payers row. A person's net position in a group is a pure projection over these:

net[user] = Σ paid − Σ owed
          − Σ settlements(payer=user, confirmed)
          + Σ settlements(recipient=user, confirmed)

Balances are never stored as truth. There's a group_balance_cache table for fast reads, but it's explicitly a cache: recomputable from the ledger at any time, and a nightly job rebuilds it from scratch and asserts zero drift. Storing balances as state is exactly how Splitwise-likes drift over years; the ledger is the source of truth and the balance is always a function of it.

The ledger is the heart, but it's 5 of 37 tables (expense_attachments — receipt photos — rides along with the four above). The rest group into ownership domains, mirroring how the schema file itself is sectioned:

The organizing rule: everything hangs off a group. Messages, expenses, settlements, stash items, moments, even location points carry a group_id, and access control is one question — "is this user an active member of this group?" — answered by the group_members table on every request.

The splits-sum-to-total invariant — for every expense, Σ owed = Σ paid = amount_minor — is guarded in three layers, and it's worth being precise about which layer does what, because "the database checks it" is the tempting-but-wrong answer. Postgres CHECK constraints are per-row; they can enforce amount_minor > 0 and owed_paisa >= 0 (and they do), but they can't express a sum across rows without a trigger. So the real enforcement is:

1. By construction. The @hisaab/money split functions can't produce a non-summing result. splitEqual(₹100, 3) returns [3334, 3333, 3333] — the leftover paisa is handed out by the largest-remainder method (the same rule banks use), so no paisa is ever created or lost. Percentages are taken in integer basis points (3333 bp, not 33.33%) because integers are the only honest way to make weights sum to exactly 100%.
2. Defensively, at runtime. The proportional splitter recomputes the sum and throws "internal invariant failed" if it ever doesn't match — a check that should never fire, positioned so that if it ever does, it does so loudly instead of writing a bad ledger row.
3. As a periodic assertion. The nightly balance-cache rebuild is a full re-derivation that would surface any drift that slipped through.

Every one of these writes rides a pooled database connection — opening a fresh Postgres connection per expense would cost more than the write itself.

Idempotency: how a retried payment is made safe

Here's where the client-generated id earns its keep. Every money-shaped write carries a client-minted ULID as an Idempotency-Key. On settlements that key is a first-class column with a unique constraint:

idempotencyKey String? @map("idempotency_key") @db.VarChar(26)

@@unique([groupId, payerUserId, idempotencyKey], map: "uq_settlements_idem")

The flow: the phone generates the ULID before sending. The train-station connection drops; the phone has no idea whether the write landed, so it retries with the same key. If the first attempt did land, the second INSERT collides with uq_settlements_idem and loses — the duplicate is rejected by the database, not by application logic that might race with itself. The write applies exactly once. This is the database's uniqueness guarantee and the concurrency discipline from the isolation-levels lesson doing the same job from two directions: correct math under retries, correct math under concurrency.

Two subtleties the schema encodes deliberately. The column is nullable, and Postgres treats NULLs as distinct in a unique index — so legacy or key-less rows don't collide with each other. And chat messages solve the identical "did my send land?" problem with a parallel mechanism: a client_dedup_key with @@unique([group_id, sender_user_id, client_dedup_key]). Same idea, different table.

What broke in production

The schema you read above isn't the schema I designed. TDD-002 specified 33 tables; the shipped database has 37, and the gaps are the honest part of the story.

The other three new tables — change_phone_challenges, settlement_reminders, and the polls trio — are features that simply hadn't been imagined when the data model was written. That's normal. A schema is a living thing; the discipline isn't "predict every table," it's "never let a later table violate the two invariants above." None of the four did.

The real war story is a column, not a table.

A second, smaller trap taught the same lesson from the constraint side: devices.push_token looks like it wants a plain unique index, but declaring one breaks reinstalling the app on the same phone — the old device row is soft-revoked (revoked_at set), not deleted, so a full unique index sees two rows with the same token. It had to become a partial unique index (WHERE revoked_at IS NULL). Unique constraints and soft deletes fight; you resolve it with partial indexes, and you only learn that the day a reinstall throws a 500.

Interview takeaway

If a system-design interview touches money or ids — and "design Splitwise / a payments ledger / a wallet" always does — here is what this chapter teaches you to say:

• "I'd store money as integer minor units, never a float." Then show 0.1 + 0.2 and say the word IEEE 754. Add: "and I'd wrap it in a currency-branded type so the compiler stops me adding two currencies." That one sentence signals you've been bitten before.
• "Currency belongs on the account/group, and I'd leave it off the line items so mixed currencies are structurally impossible." Interviewers love an invariant enforced by the absence of a column rather than by a validation rule.
• "I'd use a sortable, client-generatable id — a ULID — not auto-increment and not UUIDv4." Then give both reasons: sortable means index locality on the write path (link it to B-trees), and client-generatable means the id can double as an idempotency key.
• "Every money-shaped write carries a client-generated idempotency key with a unique constraint, so a retry over a flaky network applies exactly once." This is the single highest-signal thing you can say about correctness under real networks — the retry loses to the database, not to a race in your code.
• "Balances are a projection over an append-only ledger, cached but always recomputable, with a nightly drift assertion." Never store the derived number as truth.

And the meta-point, which is what the whole Fable, Deconstructed series is about: I got some of this wrong. The auth-refresh scan shipped and had to be fixed with a lookup column; a table I specified never got built; four I didn't specify did. A schema is not a monument. What has to stay constant is the small set of invariants — exact money, sortable ids, ledger-as-truth — that every later migration is required to preserve. The infra those tables run on has its own evolution story, told in the infra-evolution chapter.