One reference view over every CFX bank account: all four
*_bank_account_view arms, UNION ALL-ed, unfiltered.
CFX bank accounts exist in several unrelated representations — one table
with typed columns (virtual_account), one JSONB column
family (bank's four rail columns), and one implicit set that exists only as
strings on bank_transaction rows. Each has a different key shape and a
different notion of ownership, so answering "what accounts do we have, who
owns them, and what is in them" meant querying several tables, unpacking one
JSONB family by hand, and getting the ownership semantics right in each.
Nothing anywhere computed a balance.
MANUAL is the one table this view introduces
(bankAccountTable) and the primary enumeration: the others
cover CFX's own side of the perimeter, because that is all
bank_transaction ever recorded, so the counterparty accounts CFX actually
trades with appear in none of them. Registering an account there demotes the
two inferred arms — BANK_INSTRUCTION and OBSERVED — to backstop for it,
via the anti-join ladder in § 4.7.
There is no DEPOSIT_MEMO arm. A deposit memo is a customer's slice of a
shared FBO, and unlike every arm here its balance cannot be measured — only
allocated. Money arrives naming the memo, but nothing records which memo a
withdrawal drained: a withdrawal transfers to a CFX-owned ledger account and
is redeemed from there, so the memo link is gone by the time fiat leaves. Nor
is the shortfall small — on prod the memo arm showed $333.5M of credits
against $0 of debits, and the one account audited had moved $65.0M out
on-chain of which the transfer tables recorded $21.8M. Every arm below
reports a figure derived from bank_transaction alone and is checkable
against it; a memo row would have been the only one that is not, so it was
removed rather than left looking equally authoritative. The deposit_bank_memo
table and its services are untouched — the memo is simply not an account.
See docs/proposals/BANK_ACCOUNT_VIEW.md § 4.6.
There is still no producer-written balance cache behind this view: the five
original sources stay authoritative, and the bank_account table is
authoritative only for rows no other source has. Dropping provider-sourced
balances is what keeps the rest of it a pure view — reconciling Victor's /
RouteFusion's / metcap's authoritative figure against the implied one is the
obvious follow-on (§ 4.1, § 4.2).
Arm pruning is the reason source is a literal in all four arms. A view
takes no parameters, so every query plans every arm — unless the planner can
constant-fold the predicate. source IN (…) folds to false in the arms that
cannot match and they leave the plan entirely; the same filter written
against any CASE-derived column would plan all four. That is what lets the
whole union live here instead of being reassembled per request.
Declaration order is the UNION order. It carries no meaning beyond that —
every arm projects the same thirty-five columns and callers sort — but the
COLUMN order within each arm is load-bearing, because UNION ALL matches by
position and most of the contract is text. See
bankAccountViewColumns and
test/bankAccountViewContract.integration.test.ts.
securityInvoker is required, not cosmetic. Every arm is itself a
securityInvoker view over RLS-enabled tables; without it here the union
would run with the owner's row visibility and silently bypass the querying
role's policies.
One reference view over every CFX bank account: all four
*_bank_account_viewarms,UNION ALL-ed, unfiltered.CFX bank accounts exist in several unrelated representations — one table with typed columns (
virtual_account), one JSONB column family (bank's four rail columns), and one implicit set that exists only as strings onbank_transactionrows. Each has a different key shape and a different notion of ownership, so answering "what accounts do we have, who owns them, and what is in them" meant querying several tables, unpacking one JSONB family by hand, and getting the ownership semantics right in each. Nothing anywhere computed a balance.MANUALis the one table this view introduces (bankAccountTable) and the primary enumeration: the others cover CFX's own side of the perimeter, because that is allbank_transactionever recorded, so the counterparty accounts CFX actually trades with appear in none of them. Registering an account there demotes the two inferred arms —BANK_INSTRUCTIONandOBSERVED— to backstop for it, via the anti-join ladder in § 4.7.There is no
DEPOSIT_MEMOarm. A deposit memo is a customer's slice of a shared FBO, and unlike every arm here its balance cannot be measured — only allocated. Money arrives naming the memo, but nothing records which memo a withdrawal drained: a withdrawal transfers to a CFX-owned ledger account and is redeemed from there, so the memo link is gone by the time fiat leaves. Nor is the shortfall small — on prod the memo arm showed $333.5M of credits against $0 of debits, and the one account audited had moved $65.0M out on-chain of which the transfer tables recorded $21.8M. Every arm below reports a figure derived frombank_transactionalone and is checkable against it; a memo row would have been the only one that is not, so it was removed rather than left looking equally authoritative. Thedeposit_bank_memotable and its services are untouched — the memo is simply not an account. See docs/proposals/BANK_ACCOUNT_VIEW.md § 4.6.There is still no producer-written balance cache behind this view: the five original sources stay authoritative, and the
bank_accounttable is authoritative only for rows no other source has. Dropping provider-sourced balances is what keeps the rest of it a pure view — reconciling Victor's / RouteFusion's / metcap's authoritative figure against the implied one is the obvious follow-on (§ 4.1, § 4.2).Arm pruning is the reason
sourceis a literal in all four arms. A view takes no parameters, so every query plans every arm — unless the planner can constant-fold the predicate.source IN (…)folds to false in the arms that cannot match and they leave the plan entirely; the same filter written against any CASE-derived column would plan all four. That is what lets the whole union live here instead of being reassembled per request.Declaration order is the UNION order. It carries no meaning beyond that — every arm projects the same thirty-five columns and callers sort — but the COLUMN order within each arm is load-bearing, because
UNION ALLmatches by position and most of the contract istext. See bankAccountViewColumns andtest/bankAccountViewContract.integration.test.ts.securityInvokeris required, not cosmetic. Every arm is itself asecurityInvokerview over RLS-enabled tables; without it here the union would run with the owner's row visibility and silently bypass the querying role's policies.See docs/proposals/BANK_ACCOUNT_VIEW.md.