Data Model

Drizzle schema lives in lib/db/schema/ — a directory of per-domain files (users.ts, accounts.ts, payouts.ts, auth.ts, cms.ts, faq.ts, halts.ts, merchant.ts, mocks.ts, notifications.ts, platform-fees.ts, provider-webhooks.ts, reconciliation.ts, audit.ts, enums.ts, relations.ts, re-exported from index.ts). It is not a single schema.ts.

Migrations 0000_baseline through 0042_payee_wallet_whitelist (43 files) represent the complete history. Add migrations only — never edit existing ones, and append to meta/_journal.json.

Tables

33 tables are defined today. Core:

Table	Purpose	Notable columns
users	Both individuals and merchants. Merchant fields are nullable.	role, company_name, blocked_at, webhook_url, webhook_secret, per-op fee rates (invoice_pct, payout_pct, api_pct, receive_pct, sell_pct, convert_pct, invoice_open_fee_eur_minor), feature flags (orders_enabled, payouts_enabled, invoices_enabled), auth columns (password_hash, totp_secret, totp_enabled, totp_recovery_code_hash, session_version), KYC/KYB columns. No tier column — see 03-Domains#Fees.
wallets	Per-(user, asset) deposit address. Has a public_slug for pay-to.	unique (user_id, asset), fireblocks_vault_id
transactions	Canonical ops log surfaced in /my/wallet.	intent_id (FK), type enum: buy/sell/convert/send/receive/deposit/payout/fee
ledger_entries	Double-entry-style debits & credits. idempotency_key is the dedup boundary.	partial unique index per op kind
observed_deposits	Staging area for inbound chain events (formerly mock_chain_txs).	observed_deposit_status, to_address
courses	Cached oracle rates per asset.	unique asset
payment_intents	Unified source of truth for checkout requests.	merges invoices, api_orders, payment_orders. kind (invoice | order | pay_to), external_id, deposit_address, fireblocks_vault_id, status
invoice_line_items	Line items per invoice.	linked via intent_id
invoice_rate_history	Frozen oracle rates per invoice.
payment_email_confirmations	Pay-to email handshake tokens (1 h TTL).
api_keys	Integrator credentials.	secret_hash, revoked_at, expires_at
webhook_events	Outbox for merchant callbacks.	intent_id (FK), status, next_attempt_at
provider_webhook_events	Durable inbound provider-callback inbox (Fireblocks, KYC, Mercuryo).	dedupe + replay
payout_batches	Payout batches.	merchant_batch_id, max_risk_score, status, total_platform_fees_minor
payout_rows	Recipients per batch.	risk_score numeric(3,1), status payout_row_status, fee_asset
aml_records	Cached AML risk scores.	composite PK (wallet_address, network, currency), checked_at for freshness
platform_fees	Replaces fee_tiers. One named global knob per row.	key, value_eur_minor | value_pct, label, updated_by
payees / payee_wallets	Saved counterparties + their whitelisted destination wallets.	wallet_whitelist_status (draft/pending/active)
audit_log	Append-only event log (enforced by 0012_audit_log_append_only).	actor_type, action, metadata
notifications	In-app notifications, streamed over SSE.	read_at
reconciliation_runs	Ledger-vs-custody reconciliation results.	drift per asset
outbound_halts	Per-asset outbound circuit breaker.	partial unique index on active (cleared_at IS NULL)
kyc_records	KYC/KYB submissions + review state.	Sumsub applicant metadata
cms_pages / faq_articles	Editable public pages + FAQ.	slug, body_md, published, faq_audience
rate_limits	DB-backed rate-limit + login-lockout windows.	key
auth_magic_link_challenges, auth_passkey_challenges, passkeys, auth_known_devices, auth_session_revocation_tokens	Auth challenge, credential, device and revocation state.
mock_emails	Mocked outbound mail; surfaces at /admin/emails.	metadata.kind filter

Enums

asset, faq_audience, invoice_status, kyc_status, ledger_entry_type, observed_deposit_status, order_status, payee_type, payment_intent_kind, payment_intent_status, payment_order_status, payout_row_status, payout_status, transaction_status, transaction_type, user_role, wallet_whitelist_status, webhook_status.

Extend a Postgres enum via migration, then mirror it in lib/db/schema/enums.ts.

Enum	Values
payment_intent_kind	invoice, order, pay_to
payment_intent_status	draft, pending, detected, confirmed, underpaid, overpaid, due, expired, void
payout_status	queued, validated, executing, completed, partial_success, failed
payout_row_status	ready, invalid_address, invalid_amount, blocked, insufficient_balance, pending, broadcast, confirmed, failed
transaction_type	buy, sell, convert, send, receive, deposit, payout, fee
wallet_whitelist_status	draft, pending, active

Migration timeline

43 migrations, 0000 – 0042. Grouped by theme rather than listed one-by-one (run ls lib/db/migrations for the authoritative list):

Range	Theme
0000	0000_baseline — squashed base schema (supersedes the old 0000_init…0016 history)
0001–0007	Invoice open-fee + reconcile, order renames, draft invoices without a wallet, payout queued status, order idempotency, provider webhook events
0008–0009, 0015–0017, 0029, 0043	Auth rework: drop legacy federated-auth columns, magic links, app magic-link tokens, password + MFA + recovery, MFA sync fix, device detection
0010–0014, 0018–0024	Fee, audit-log append-only, CMS/FAQ seed, order expiry index, and assorted hardening
0025–0028	API docs, notifications, user KYC details, Sumsub metadata
0030–0033	Mercuryo, API-key expiry, rate_limits, payout row fee asset
0034–0036	Reconciliation runs, pending reconciliation, TRES reconciliation
0037–0039	Fireblocks vault ids on wallets and payment_intents, wallets.user_id restrict
0040–0042	Outbound halts, amountless pay-to intents, payee wallet whitelist

Drawbacks

• See 08-Risks and Drawbacks#Data model.

Open Questions

• 09-Open Questions#PII storage