Three schemas, two clients
core— tenant identity and everything mode-agnostic: who the merchant is, their team, their KYC, their branding, their customers’ phone identities. A business is the same business whether it’s testing or live.sandboxandlive— the business data, duplicated per mode. A plan, a subscription, an invoice exists in exactly one mode. There is nomodecolumn. Test data lives in a different schema than live data, full stop.
database.core for the core schema, and database.data(mode) which returns a client pointed at sandbox or live. A controller that holds a data('test') client physically cannot read live rows — the connection’s search_path doesn’t include the live schema.
This is isolation by construction, not by convention. The classic bug — forgetting
WHERE mode = 'live' in one query out of three hundred — is impossible here, because the column doesn’t exist. The schema boundary is the filter.The core schema: who you are
CustomerIdentity is deliberately not scoped to a tenant. It is the cross-merchant wallet — keyed by phone, verified over WhatsApp, holding payment methods reusable everywhere. See identity →
The data schema: the money
Every table below exists once insandbox and once in live. Every row carries a tenantId so a single schema holds all tenants, partitioned by composite index.
A few choices worth calling out:
Money is integer minor units
Money is integer minor units
Every amount is an
Int of kobo — ₦5,000.00 is 500000. No floats anywhere near money. Currency is a sibling field, default NGN, with per-plan FX supported.The invoice idempotency key prevents double-billing
The invoice idempotency key prevents double-billing
An invoice’s
idempotencyKey is renewal_{subscriptionId}_{periodStart}, unique per (tenantId, idempotencyKey). The renewal scanner can fire twice for the same period and the second insert is rejected by the database, not by application logic.The dunning schedule is one-to-one with the invoice
The dunning schedule is one-to-one with the invoice
A failing invoice gets exactly one
DunningSchedule carrying the whole recovery state — attempts made, the chosen rail, whether to be payday-aware, the last action taken. It is the recovery state machine’s memory.Events are the spine
Events are the spine
Every meaningful change writes a row to
events. The webhook system consumes that table; the dashboard activity feed reads it; analytics aggregate it. One append-only log, many readers. See webhooks →Indexing for the hot paths
Reads that happen on every request or every scan are indexed to match:| Query | Index |
|---|---|
| Cursor pagination of any list | (tenantId, createdAt, id) compound |
| Renewal scan: due subscriptions | (status, currentPeriodEnd) |
| Dunning scan: retries due now | (state, nextAttemptAt) |
| Portal: a phone’s subscriptions across merchants | (phone) on customers |
| Checkout: reuse a customer by contact | (tenantId, email) |
| A customer’s transactions | (customerId) on payment_attempts |
[createdAt desc, id desc]), never OFFSET — so page 500 costs the same as page 1.
Next: the request lifecycle — how a raw HTTP request becomes a tenant-scoped, authenticated, idempotent operation.