Multi-Accounts

§0Principles

§1Progress / Tracker

Live snapshot — phase status and the bar below are computed from GitHub PR state at build time (2026-06-11). 7/15 phases done · 47%.

§2aThe mental-model shift

Today the system conflates two things into one identifier (user_id):

• Identity — who is making a request (a human, a session, an actor for audit).
• Ownership — whose money the request moves (balances, positions, margin, fills, P&L, statements).

The new model needs to split these. Every concept in the system is one of:

The single hardest discipline: every money-touching API call must take an explicit account_id, and the gateway must authorize (user_id, account_id, action) against account_permissions. No more silent default.

UserId::as_default_account_id() (forward: user → their default account) and UserId::from_default_account_id() (reverse: account → user) are the inventory of every place that conflation still lives — ~210 call sites. The two are not symmetric: the forward resolution is legitimate and permanent (a request that omits account_id resolves to the user's default account — the one whose id equals the user's; §3.5), so as_default_account_id is never deleted. The reverse is the real smell — code deriving "the user behind this account," which only the 1:1 era made free. Step 10 (§1) is therefore per-service account-keying, not a function deletion.

§2bConcepts that need rethinking

Secretly account-level, masquerading as user-level today

• users.is_close_only, users.is_frozen, users.ep3_username, users.ep3_account, users.maker_fee, users.taker_fee — schema already has the TODO. These move to trading_accounts exclusively.
• API keys — currently per-user with implicit access to "the user's account". Need to become per-(user, accounts).
• TigerBeetle ledger IDs — UserId::derive_tb_account_id(ledger) constructs the TB ledger account id from user_id bit pattern. The ledger of record is bound to user, not account. This must become AccountId::derive_tb_account_id(ledger).
• Risk snapshots — risk:{user_id} in Redis, but the value already carries account_id. The key is the thing that's wrong. user_id should be deprecated and then dropped.
• Drop-copy / position / fill / balance streams — currently filtered by user_id; should be filtered by the set of account_ids the user can read.
• Loss limits, LTV monitors, deposit addresses, leaderboard rows — composite-PK'd on user_id but semantically account-level.

Genuinely user-level — stay that way

• users, password_reset_codes, user_risk_profiles, onboarding_applications, documents_meta, KYC, MFA, session tokens, audit "who did this".
• Notifications addressed to a human (email/push), not to an account.

Has to be invented (in protocol, not just DB)

• Every money-touching request carries an explicit account_id (or account_ids: Vec<AccountId> for reads/subscriptions); there is no session-level "active account" state. Today there's no protocol-level way to say "I'm Andrew, acting on behalf of ACME-1" — the request body has to grow that field. WhoAmIResponse.accounts is multi-aware but the rest of the surface isn't. Sticky session state is rejected on purpose: it makes "I traded in the wrong account" reconstructable only from logs+state-at-the-time instead of just the request body, and it adds an invalidation axis (permission revocation, account close) that explicit parameters don't have.

§2cDatabase primary keys

Two stores to migrate: Postgres (transactional state of record) and ClickHouse (append-only time-series for orders, fills, positions, risk snapshots). They have very different rekey economics — Postgres PKs are an integrity constraint, ClickHouse ORDER BY is the on-disk layout — so they need separate plans.

2c.1 · Postgres

The schema is in surprisingly good shape — trading_accounts and account_permissions already exist. The work splits into additive schema, PK rewrites, and one cleanup pass.

Class 0: additive columns (do these first; unblock the rest):

• trading_accounts.ubo_user_id CHAR(16) REFERENCES users(id) — the Ultimate Beneficial Owner. See §3.3. Nullable: system accounts (e.g., AX_LENDING_USER_ID) have no beneficial owner and should be NULL; customer-owned accounts have a UBO. Backfill: 1:1 from the user that created the account, NULL for system accounts.

No users.primary_account_id column. The "user's default account" is derived, not stored: it's the account whose id shares the user's ULID bit pattern (the TigerBeetle-preservation invariant from below). Zero-cost to compute, stable for the life of the user, and doesn't require a new NOT NULL column. See §3.5.

Class A: drop user_id, key on account_id (simple, mechanical):

• current_balances — PK user_id → account_id (A-2946 already added the account_id column NOT NULL with a 1:1 backfill; only the keying remains)
• monthly_loss_limits — FK user_id → account_id (needs additive column add + backfill first)
• loan_to_value_monitors — PK user_id → account_id (needs additive column add + backfill first)

Class B: composite PKs that need user_id swapped for account_id (data migration):

• blockchain_deposit_addresses — (user_id, blockchain, asset) → (account_id, blockchain, asset). Deposit addresses fund accounts.
• leaderboard — (metric, cadence, period_start, user_id) → (..., account_id). Ranks trading entities, not humans.

Class C: shed duplicated columns once trading_accounts is canonical (cleanup, off the critical path):

• users: drop ep3_username, ep3_account, is_close_only, is_frozen, maker_fee, taker_fee. (Schema already TODOs this at db/postgres/1.sql:30.) Pure hygiene — does not block anything; can ship before, after, or in parallel with any rekey.

No change (identity tables): users, api_keys (see §2f — different reason), user_risk_profiles, password_reset_codes, onboarding_applications, etc.

2c.2 · ClickHouse

The A-2946 backfill (db/clickhouse/a-2946-account-id-backfill.sql) has already added account_id columns to every relevant table and backfilled them 1:1 from user_id. The materialized views (risk_snapshots_1h_mv, risk_snapshots_1d_mv) were updated atomically via MODIFY QUERY to carry the column through. So columns are in place; what remains is the rekey — that script's preamble explicitly defers it ("the follow-up rekey is a separate migration"). That's the work below.

Why this is meaningfully different from Postgres: MergeTree sort/primary keys are the physical part layout. You cannot ALTER … MODIFY ORDER BY to move user_id out of the sort key — you must build a new table with the new ORDER BY, INSERT SELECT everything across, swap names, drop the old. For multi-billion-row tables (order_log, trades, positions, transactions) this is a real operation: hours of I/O, dual disk-space, and a brief writer pause at swap time.

Tables where user_id is already only a column (no rekey, just stop writing it after Postgres cutover):

• historical_orders — sort key (completion_time_ns, order_id).
• order_log — sort key (event_timestamp_ns, order_id).
• trades — sort key (symbol, timestamp_ns, trade_id); both maker/taker carry both ids.

For these, the column stays for one release as a denormalized convenience, then we either drop user_id outright or keep it as an "actor of record" stamp (cheap, useful for audit). Either way: no on-disk rebuild.

The three rekeyed tables below (positions, transactions, risk_snapshots_1d) likewise keep their user_id column through the 1:1 era as denormalized actor-of-record data — only the sort key flips. That has a consequence for readers: once the sort key leads with account_id, any query that still filters/LIMIT 1 BYs on user_id loses the leading-column prune and full-scans. So reader-query rekey is coupled to each storage rekey, not deferrable — every WHERE user_id/LIMIT 1 BY user_id against an account_id-leading table (positions, risk_snapshots, risk_snapshots_1h, risk_snapshots_1d) must move to account_id in the same step that flips the key. (transactions is the exception: its key leads with timestamp, not account_id, so its user_id reads are perf-neutral and only need migrating for the semantic/cleanup reasons below.) The eventual user_id-column drop for all of these — rekeyed and column-only alike — is its own terminal step (§1 step 13), with one exception: risk_snapshots/_1h/_1d have their user_id drop pulled forward into the risk-engine slice of step 10. Those tables already lead their sort key with account_id and the api-gateway reader already GROUP BY account_id, so dropping the column (and rebuilding the 3 MVs) is free to do alongside risk-engine's internal rekey. Every other CH user_id column stays for the step-13 sweep.

Tables that need a true rekey (user_id is in the sort/primary key):

Rekey playbook (per table, applied uniformly):

1. CREATE TABLE … _new with the new ORDER BY / PRIMARY KEY.
2. For MV-fed tables (risk_snapshots_1h, _1d): drop the MV, recreate it pointing at _new. For source-of-truth tables fed by service writes (positions, transactions, risk_snapshots): the writer must dual-write or be paused.
3. INSERT INTO …_new SELECT * FROM … in time-bucketed batches (by partition where partitioned, by month otherwise). Verify row counts match per partition.
4. RENAME TABLE … TO …_old, …_new TO … (atomic in a single statement).
5. Optionally DROP TABLE …_old after a soak.

For TTL'd tables (risk_snapshots, risk_snapshots_1h) there's a cheaper path: stop writing on the old key, start writing on the new key in a new table from cutover, let the TTL drain the old one. Skips the INSERT SELECT entirely. Worth using.

Invariants that change meaning, not just column names:

• positions.sequence_number — doc says "globally unique per user, i.e., not tracked per symbol." Once accounts can be multi-user, this needs to be per account, not per user. Where the writer generates it (trade-engine2 / settlement-engine) must reseed per account.
• transactions.sequence_number + transactions.event_id — the doc identifies (user_id, sequence_number) as the primary unique key with event_id as backup. The new primary unique key is (account_id, sequence_number). The backup event_id semantics don't change but the uniqueness contract migrates.
• risk_snapshots rows currently fan out one-per-user; in the multi-account world they fan out one-per-account. A user's multi-account "risk view" is composed client-side (one query per chosen account) — the server emits one row per account and never fans out over a permission set (§2e, no implicit fan-out). The producer (risk-engine buying_power_updater.rs) already loops on account_id.

Out of scope: funding_rates, candles_*, bbo_candles_*, market_snapshots, benchmark_prices, index_prices, invariants_log — none reference user/account. monitor.* schema is service-owned and unaffected.

Analytics DB (db/clickhouse/analytics.sql) is symbol/event-keyed throughout — no user/account in any table — so no rekey, no column adds.

§2dRedis shape changes

The Redis surface is narrow — almost all of it is in rs/sdk-internal/src/redis/redis_keys.rs:

Nothing scans these with wildcards, and the value (RedisUserRiskSnapshot) already carries both ids, so the cutover is a single producer/consumer swap plus a backfill. No Lua/MULTI dependencies cross the user/account boundary.

The Redis pubsub channel risk_snapshot_updated is global, so it doesn't shift. But any future "subscribe to a user's risk" should be expressed as "subscribe to an account's risk" — a multi-account user's view is composed client-side, one subscription per chosen account, never a server-side fan-out over a permission set (§2e).

§2eProtocol & request shape

The SDK already half-supports this. Most message types (Fill, Position, Balance, UserRiskSnapshot, AdminTrade) carry both user_id and account_id. The shape is mostly right — the enforcement is what's wrong — with one exception: user_id on the trade/fill types (Fill, AdminTrade) is scheduled for removal — account_id becomes the sole identity (see the outbound note at the end of this section and §1 step 8b). The gateway guard in api-gateway/src/utils.rs:106 actively rejected any client-supplied account_id that didn't equal the user's default. That guard inverted (step 6, merged):

• Before: "account_id must equal user's default."
• After: "account_id must exist in account_permissions for this user with can_<action> true."

Request-shape decisions, each made explicitly:

1. Optional account_id, defaults to the user's default account. Keeps every existing SDK call working unchanged (the default account is user_id's bit pattern, so this is a no-op until users actually have a second account). This default is permanent — there is no primary_account_id and no mutable "make this my default" knob; every user has exactly one default account (id == user id) forever, used or not. The foot-gun concern — "I misclicked and traded the wrong account" — is real but lives at the client layer: the CLI/GUI for multi-account users should refuse to send without an explicit choice. The server accepts the omission and resolves it deterministically via as_default_account_id. One nuance to document on every endpoint: the default is fixed by the bit pattern, so adding a second account to a user never changes which account their bare-account_id-less calls land on (still the default account).
   No implicit fan-out (decided). Omission resolves to the single default account — never a server-side aggregate over the user's readable-account set. A money read keys on exactly one account_id: the explicit one (authorized via authorize) or the default. Reading N accounts is N explicit calls (or a future account_ids-plural endpoint — a deliberately later decision). Rationale: server fan-out makes a bare read's cost unbounded in the user's grant set (multi-account scan plus per-row account_permissions resolution) and turns a cheap keyed lookup into an expensive union. Account is a top-level context, surfaced as an account selector in the GUI header bar: the active account is chosen explicitly and sent on every money read; "switch account" is a selector change, not a server-inferred set. The consumer composes multi-account views client-side.
2. JWT claims: add nothing. Keep JWT identity-scoped. Account context is per-request, not per-session — a single session can act on multiple accounts (treasurer pattern).
3. Subscriptions/streams: each stream scopes to a single account_id (optional; omission resolves to the user's default account), supplied by the connection out-of-band — e.g. WsQueryParams.account_id (rs/sdk protocol/order_gateway.rs), already shipped (#2224). The server enforces the scope server-side (rejecting an account the user cannot read); never trust a client-supplied filter. There is no account_ids: Vec<AccountId> server-side fan-out — a multi-account view is N connections, one per account, composed client-side (the same no-fan-out rule as item 1). There is no plural account_ids subscription.
4. WhoAmIResponse.accounts becomes the real list (from account_permissions), not the synthetic 1:1.
5. Cancel/replace by cid need an optional account_id; by oid they don't. A server order id (oid) is globally unique, so CancelOrderRequest/ReplaceOrderRequest referencing one need no account. But client_order_id (cid) is namespace-scoped — the open-orders index is (UserId, ClientOrderId) today and becomes (AccountId, ClientOrderId) — so resolving a cid requires knowing which account's namespace. The WS path gets this from the connection (WsQueryParams.account_id); the REST /cancel-order and /replace-order paths have no connection scope, so the request grows an optional account_id (omission → default account). Additive and non-breaking — shipped in step 8 (#2264), not the breaking bump.

For events flowing out (drop-copy, fills, position deltas), the payload key is account_id — the owner whose position moved. A user_id may also ride along, but its meaning must be pinned: it is the actor of record (who placed the order), which under multi-account is not the owner and not the UBO (a proxy/algo trades an account it doesn't own). The trap to avoid: once the EP3 seam is account-only (§2a, §1 step 10e), the actor is no longer recoverable from the participant, so it must be sourced from order correlation (order_id → order_log, which holds the authenticating user) — never from the participant parse, and never silently backfilled from trading_accounts.ubo_user_id (that would reinterpret "the trader" as "the legal owner" exactly when they diverge).

The split is by what the object is about, not blanket removal:

Why OrderDetails keeps it but Fill/Position/ledger/risk don't: an order is the act of one authenticating session, so "actor of record" is a real, single-valued property of that object. A money/position/ledger/risk row is a fact about the account; its actor (if ever needed) is recovered by joining order_id → order_log (which holds the authenticating user), and the legal owner by joining account → trading_accounts.ubo_user_id. Stamping a bare user_id on those rows just re-creates the conflation. (Rejected for the dropped types: keeping user_id silently meaning UBO — actively wrong under proxy trading; or actor-of-record — needless surface area now that account_id is the key and the actor is recoverable via order correlation.)

The internal ChTradeRow.maker_user_id/taker_user_id stamps stop being derived from the participant in step 10e; the public field removals/rename above ride the single 1:1-break bump in §1 step 8b.

§2fAuthorization & API keys

account_permissions already has the right granularity: can_list, can_read, can_set_limits, can_reduce_or_close, can_trade. Two design choices to lock in:

Centralize the check. One function — authorize(user_id, account_id, Action::Trade) — called at the gateway boundary on every money-touching route. Replace the contents of validate_requested_account_id with a real ACL lookup. The as_default_account_id call sites become the test set: every one of them is a place where a permission check should now run. (Shipped in step 6 — #2180. One operational lesson already learned: enforcement is only as good as the permission rows; accounts seeded before account_permissions existed had none and 403'd until the #2353 backfill.)

API keys are the hardest sub-design. Today api_keys has user_id only. Two viable shapes:

• (a) Key belongs to a user, inherits the user's permissions at request time. Simple, but a leaked key is as powerful as the user's full account set, and revoking access to one account doesn't revoke the key.
• (b) Key belongs to a (user, account_id, scoped_perms) tuple. A user can mint N keys, each with a subset of their permissions on one account. Recommended — matches how every other exchange API does this, and matches a "read-only key for the accountant, trade-only key for the bot" workflow that customers will want immediately.

Recommendation: (b). Schema change: api_keys gets account_id (NOT NULL FK) and per-key permission flags mirroring account_permissions, with a constraint that key perms ⊆ user perms at issuance time. In flight as #2227 (step 9, draft).

§2gSequencing summary & off-critical-path work

Steps 1–6 are the "make it work for 1:1 in the new shape" phase and shipped safely. Step 6 is the watershed — once the gateway authorizes against account_permissions instead of "must equal default," multi-account is a config-flip per customer rather than a code change. The public-SDK breaking bump (step 8b) is gated to that watershed, not to the end: it must land before the first customer's second account exists, because the dropped user_id fields stay value-correct only while 1:1 holds — once it breaks they are ambiguous. That is the whole "ship the public API early and once" discipline: the additive surface (step 8) went out first, the single breaking bump (8b) at the 1:1 break, then the internal plumbing (steps 10–13) moves underneath on our own timeline. Steps 4 and 5 (ClickHouse) ran in parallel with the Postgres work but had to complete before step 10 (the service account-keying), since the writers stamping account_id into CH rows still used the 1:1 derivation. Step 13 (internal ClickHouse user_id column drops, no public surface) is strictly last. Step 10 does not delete as_default_account_id: the forward default-account resolver is permanent (§3.5). What step 10 retires is the reverse derivation (from_default_account_id) and user-keyed service state; step 10b additionally moves close-only/freeze off the user.

Off the critical path (ship anytime)

• Drop the duplicate users columns (ep3_*, maker_fee, taker_fee — pure cleanup; is_close_only/is_frozen are retired by step 10b, not here). Does not block any step above.
• Admin GUI concept-shift (see §3.7). Earliest start: as soon as step 1 lands (it has). Run as a parallel PR series.

§3Design Questions

1. Cross-account margin / netting — if one user can act on several accounts, are those accounts margined jointly?
   • Not yet answered Leaning no — independent per account (a joint legal entity gets fuzzy). Needs to become a stated invariant: it decides whether current_balances keys on account_id alone (independent) or needs a margin_group_id (joint).
2. Account lifecycle — who creates accounts beyond onboarding, and is there a public-SDK account-creation message?
   • Not yet answered Onboarding still creates (user, account) atomically; no admin-creates / self-serve flow and no public-SDK account-creation message yet. The surface lands in step 11.
3. First-class owner — should trading_accounts carry an explicit owner column?
   • Answered — affirmative Add ubo_user_id CHAR(16) REFERENCES users(id) (nullable) — the Ultimate Beneficial Owner (KYC/tax/compliance counterparty), distinct from the symmetric, operational account_permissions. Nullable for system accounts (AX_LENDING_USER_ID); backfilled 1:1 from the creating user for customer accounts. A stepping-stone to a future first-class customer entity, at which point ubo_user_id migrates to customer_id and is dropped. UBO is single-valued; joint ownership is a future customer-entity concern.
4. Audit shape — should audit rows carry account_id (and the authorizing permission)?
   • Not yet answered Minimum viable: every audit row gains account_id. Richer: also record the account_permission row that authorized the action, so revoking a permission yields a clean "after this point, X could no longer do Y" story. Not yet committed to either.
5. Default account for legacy clients — does an omitted account_id resolve to a single, derived default account (no stored primary_account_id)?
   • Answered — affirmative account_id stays optional and resolves to the user's default account even after they have several (§2e). The default is derived, not stored — the account whose id shares the user's ULID bit pattern (already preserved for TigerBeetle history, §2c.1). No users.primary_account_id, ever; the forward resolver as_default_account_id is therefore permanent (§1 step 10, concern g). A mutable "active account" preference, if ever wanted, is a client/session concern — never a stored column that changes ownership math.
6. Frozen / close-only semantics — is the truth at the account level (dropping the user-level columns)?
   • Answered — affirmative Per-account is the truth; per-user is a derived "all my accounts are frozen" view. Move enforcement to trading_accounts.is_close_only/is_frozen and drop the users columns (§1 step 10b).
7. Admin GUI concept-shift — should money-bearing admin workflows flip from user-centric to account-centric?
   • Answered — affirmative Money operations flip to account-centric ("freeze ACME-1"); identity operations stay user-centric (MFA, password, KYC). ubo_user_id makes the admin GUI navigable ("show me Andrew's accounts" → WHERE ubo_user_id = Andrew; audit lines carry both). The trader GUI gets an account selector in the header — what makes no-implicit-fan-out ergonomic (§2e) — shown only when permissions authorize more than the default account. Its own workstream: a parallel PR series, earliest start once step 1 lands (it has).