Ledger-first: a data architecture for a crypto custodian

TL;DR

• Put one append-only, double-entry ledger table at the centre of the warehouse. Every balance, valuation and revenue number is a deterministic function of it, so when two numbers disagree exactly one is a bug.
• Three rules keep the table trustworthy: native integer units, deterministic keys hashed from source references, and corrections booked as reversals instead of updates.
• Reconciliation becomes tests that run on every build: journals balance, balances roll forward, the ledger ties to the chain, and closed periods stay frozen.
• Valuation is a join with provenance: which price, which source, which cutoff. An unpriced balance is NULL and a failing test, never a silent zero.
• Start with the ledger table, the sign convention and four invariants against a week of real data. Dashboards are views that come after.

Ask three teams at a crypto custodian for last month’s assets under custody and you will get three numbers. Operations sums wallet balances from a chain indexer. Finance pulls a trial balance out of the ERP. Someone in growth has a dashboard that adds deposits and subtracts withdrawals since launch. Each number is defensible on its own terms, none of them agree, and the month-end meeting is spent arguing about whose spreadsheet is wrong.

I don’t think better process fixes this. Structure does. The pattern I keep coming back to is what I call ledger-first. One append-only, double-entry fact table is the system of record inside the warehouse: rows are only ever added, never changed, and every movement is booked as debits and credits that cancel out. Every balance, valuation and revenue metric is then a deterministic function of that table. If two numbers disagree, exactly one of them is a bug, and the lineage tells you which.

Accountants have worked this way for five hundred years. The change is putting the ledger at the center of the warehouse instead of leaving it to the ERP at month-end.

The layers

sources            chain indexers · internal transfer service · ERP general ledger · price feeds · contracts
   │
staging            1:1 typed copies, native units, source keys preserved
   │
canonical ledger   fct_ledger_entry   (immutable, double-entry, one row per leg)
   │
state              fct_balance_daily  (roll-forward snapshot per account × asset × day)
   │
valuation          fct_balance_valued (× price at the reporting cutoff, with price provenance)
   │
metrics            assets under custody · fee accruals · revenue · flows   (semantic layer)

Staging does no interpretation. It types columns, keeps source units and preserves every source key, so any number walks back to a transaction hash or an ERP document.

The canonical ledger is the only layer allowed to decide what happened. Turning a withdrawal request plus an on-chain transfer plus a network fee into one balanced journal is business logic, and it lives in exactly one place.

Everything below that line is arithmetic: balances are cumulative sums, valuations are balances times prices, metrics are aggregations defined once. If a dashboard needs logic that isn’t a view over those layers, that logic is missing from the ledger.

The ERP shows up as a source for non-custody journals like vendor bills, but it is not upstream of custody balances. It receives summarized journals generated from the ledger, one line per account class per day, and the two are reconciled. The warehouse holds every leg; the general ledger holds what auditors need at the level they need it.

The core table

-- One row per leg. A journal is balanced when its legs sum to zero per asset.
create table fct_ledger_entry (
  entry_id       string     not null,  -- hash(source_system, source_ref, leg_no)
  journal_id     string     not null,  -- groups the legs of one economic event
  account_id     string     not null,  -- wallet, client sub-account, house account, suspense
  asset_id       string     not null,
  amount_native  bignumeric not null,  -- signed, in the asset's smallest unit; debit > 0
  event_type     string     not null,  -- deposit, withdrawal, transfer, reward, fee, adjustment
  effective_at   timestamp  not null,  -- when it happened economically
  recorded_at    timestamp  not null,  -- when we learned about it
  source_system  string     not null,
  source_ref     string     not null,  -- tx hash + log index, ERP doc id, ...
  leg_no         int64      not null   -- position of this leg within the source event
);

Three rules make this table trustworthy.

Native units, always. Amounts are integers in the asset’s base unit: satoshis, wei, lamports. Decimals live on dim_asset, and conversion to display units happens at the edge. BIGNUMERIC holds about 38 integer digits, which covers any realistic custody balance, but an ERC-20 amount is a uint256 and spam tokens with absurd supplies do exist. I keep the raw string in staging and fail the load loudly on overflow rather than truncating.

Deterministic keys. entry_id is a hash of source system, source reference and leg number. Re-ingesting a day of chain data produces the same keys, so loads are idempotent (running the same load twice gives the same result) and duplicates are detectable.

No updates, only reversals. A correction is a new journal that reverses the wrong one and books the right one. History is never rewritten. That is also why the table carries two timestamps: effective_at says when something happened, recorded_at says when we knew. Keep both from day one, because retrofitting the second clock later is miserable.

The sign convention is plain double-entry: debits positive, credits negative. Wallet accounts are debit-normal (what we hold). Client accounts are credit-normal (what we owe). The presentation layer flips signs for humans. The ledger never does.

One day at a toy custodian

Here is a single day of BTC activity at the custodian I’ll use as a running example. Two clients, A and B, share one omnibus wallet, W1.

Journal	Event	Account	BTC	amount_native (sats)
J1	deposit	wallet:W1	+2.0000	+200,000,000
J1	deposit	client:A	−2.0000	−200,000,000
J2	deposit	wallet:W1	+1.5000	+150,000,000
J2	deposit	client:B	−1.5000	−150,000,000
J3	custody fee	client:A	+0.0010	+100,000
J3	custody fee	house:fee_revenue	−0.0010	−100,000
J4	withdrawal	client:B	+0.5000	+50,000,000
J4	withdrawal	house:network_fees	+0.0002	+20,000
J4	withdrawal	wallet:W1	−0.5002	−50,020,000

Every journal sums to zero. At close, W1 holds +2.9998, clients A and B are owed 1.9990 and 1.0000, fee revenue is −0.0010 and network fees are +0.0002. Those sum to zero too, by construction.

Now look at what falls out for free. Client obligations total 2.9990 BTC. The wallet holds 2.9998. The 0.0008 BTC difference is exactly the house’s own position: fees earned minus network fees paid. If the chain indexer says W1 holds 2.9998, the books tie to the blockchain. If it says 2.9990, something left the wallet that the ledger doesn’t know about, and that is the single most important alert a custodian can have.

Assets under custody at a toy price of $40,000 is 2.9990 × 40,000 = $119,960. It’s the client accounts, not the wallet balance, which already includes the house’s own coins.

Invariants are tests

With the ledger at the center, reconciliation stops being a monthly spreadsheet and becomes assertions that run on every build.

Invariant	What the test checks
Every journal balances per asset	sum(amount_native) = 0 grouped by journal_id, asset_id
Balances roll forward	close(t) = close(t−1) + net_flows(t) for every account × asset × day
Ledger matches the chain	per wallet account, abs(ledger − on_chain) <= tolerance(asset) at each snapshot
Closed periods are frozen	no entries with effective_at in a closed period and recorded_at after close, unless tagged as an adjustment

The first two as dbt singular tests, where any returned row is a failure:

-- tests/assert_journals_balance.sql
select journal_id, asset_id, sum(amount_native) as imbalance_native
from {{ ref('fct_ledger_entry') }}
group by journal_id, asset_id
having sum(amount_native) != 0

-- tests/assert_balances_roll_forward.sql
with flows as (
  select account_id, asset_id, date(effective_at) as balance_date,
         sum(amount_native) as net_native
  from {{ ref('fct_ledger_entry') }}
  group by 1, 2, 3
),
rolled as (
  select
    b.account_id, b.asset_id, b.balance_date, b.close_native,
    lag(b.close_native) over (
      partition by b.account_id, b.asset_id order by b.balance_date
    ) as prev_close_native,
    coalesce(f.net_native, 0) as net_native
  from {{ ref('fct_balance_daily') }} as b  -- gap-free: one row per account × asset × day
  left join flows as f using (account_id, asset_id, balance_date)
)
select *
from rolled
where close_native != coalesce(prev_close_native, 0) + net_native

The roll-forward test looks tautological, since balances come from the same ledger. It isn’t. fct_balance_daily is almost always incremental, carrying yesterday’s close forward, because rebuilding years of daily balances every run is expensive. The moment a late entry arrives with an effective_at three days ago, the incremental model is stale and this test catches it. The fix is to rebuild from the minimum affected effective_at, not to loosen the test.

Note that date(effective_at) is a UTC day. If your reporting day closes in New York, pass the time zone explicitly, everywhere. Balances on UTC days and valuations on Eastern cutoffs will disagree every night.

Valuation is a join with provenance

A valued balance is quantity times price, and the price needs as much lineage as the quantity: which cutoff, which source wins, and what happens when there’s no print at all.

-- fct_balance_valued: price each asset once per day, then join to balances
with asset_days as (
  select distinct asset_id, balance_date,
         timestamp(date_add(balance_date, interval 1 day)) as cutoff_at
  from {{ ref('fct_balance_daily') }}
),
ranked as (
  select
    d.asset_id, d.balance_date, p.price_usd, p.price_source, p.price_at,
    row_number() over (
      partition by d.asset_id, d.balance_date
      order by p.source_rank, p.price_at desc
    ) as rn
  from asset_days as d
  left join {{ ref('stg_price_observation') }} as p
    on  p.asset_id = d.asset_id
    and p.price_at <= d.cutoff_at
    and p.price_at >  timestamp_sub(d.cutoff_at, interval 15 minute)
)
select
  b.account_id, b.asset_id, b.balance_date,
  b.close_native / a.unit_scale               as close_units,
  r.price_usd, r.price_source, r.price_at,
  b.close_native / a.unit_scale * r.price_usd as close_usd  -- NULL when unpriced
from {{ ref('fct_balance_daily') }} as b
join {{ ref('dim_asset') }} as a using (asset_id)
join ranked as r
  on r.asset_id = b.asset_id and r.balance_date = b.balance_date and r.rn = 1

unit_scale is 10^decimals stored as BIGNUMERIC on dim_asset, so the division never touches a float. An unpriced balance produces a NULL value and a failing test, never a silent zero. Illiquid assets need either a longer window or a manual mark with an approver, and the price_source column is how an auditor tells them apart.

Balances are also semi-additive. They sum across accounts but not across time. “Assets under custody for Q4” is the average of daily closes, or the quarter-end close, and the semantic layer should make the wrong aggregation impossible to request.

The domain traps

Omnibus vs. segregated wallets. One on-chain address can hold coins for many beneficial owners, and one client can have coins spread across many addresses. The fix is to keep wallets and clients as separate accounts on opposite sides of the ledger, as in the example. The client relationship to a wallet is a dimension, not a property of the balance.

Rewards and accruals. Staking rewards accrue before they are distributed. The ledger needs both events: an accrual that debits a rewards receivable and credits the client, then a settlement that moves the receivable into the wallet when the tokens land on-chain. The receivable should drain to near zero every epoch. A receivable that grows is a reward you promised and never received.

Chain weirdness. Reorgs, where the network replaces its most recent blocks, mean a deposit can disappear, so I book only past a finality threshold per chain, or book provisionally and reverse. Fee-on-transfer tokens deliver less than was sent, so the legs record what arrived. Unsolicited dust and airdrops go to a suspense account, a holding account for items nobody has classified yet, until someone decides who owns them.

What goes wrong in practice

Floats sneak in. A CSV export, a JSON parser or a BI tool silently converts wei into a double, and 2^53 is about 0.009 ETH in wei. Any amount above that loses precision. Every boundary where amounts cross a system needs a test that round-trips the integer.

Keys stop being deterministic. Someone adds the ingestion timestamp to the hash “for uniqueness,” and the next backfill doubles every deposit. The journal balance test still passes, because both copies balance. Only a uniqueness test on (source_system, source_ref, leg_no) catches it.

Someone runs an UPDATE, always well-intentioned, always at month-end. Only the pipeline’s service account can write the ledger; manual corrections go through a reviewed seed of adjustment journals.

Suspense becomes a landfill. Alert on the age of the oldest open item, not just the balance.

The ERP and the ledger drift. Someone books a manual journal directly in the ERP and it never comes back to the warehouse. Reconciling the ledger-derived trial balance against the ERP trial balance, by account class and day, is the only defense.

Where I’d start

Not with dashboards. I’d start with fct_ledger_entry, the sign convention and the four invariants running in CI against a week of real data. The first breaks usually show up within an hour, and each one is a conversation about what actually happened, which finance needed to have anyway.

Once the ledger holds, the rest is views. Assets under custody, fee revenue, net flows and client statements become different questions asked of the same rows. When two of them disagree, I don’t need a meeting. I need a diff.