Crediting On-Chain Deposits Without a Balance Column

A custodial wallet has to answer one question honestly: how much of this user's money is spendable right now. The obvious implementation increments a balance column when a transaction appears in a block, and that column is wrong the first time the scanner restarts mid-batch, the node replays a range you already processed, or the chain reorganizes underneath you.

I have built custodial wallet services for BTC and ETH and an on-chain payment system on top of them, and the mistake I see repeated most often is treating the block scanner as a writer of balances. It is not. It is a reader of an external system that can retract what it told you five minutes ago.

The constraint that makes this hard

There is no transaction that spans your database and a blockchain node. You cannot commit "I saw this deposit" and "I credited this user" atomically with the chain's own view of history, because the chain's view is not yours to commit. Three things follow from that, and every design decision below is downstream of them:

• Delivery is at-least-once. Your scanner will process the same block twice.
• History is mutable near the tip. A block you accepted can stop existing.
• The node is not authoritative about your ledger. It is authoritative about itself, and it can be replaced, resynced, or briefly behind.

So I stopped storing conclusions and started storing observations.

Store what the chain said, not what you concluded

Two tables. One records blocks and their lineage, the other records the deposit outputs found inside them. Nothing here is a status, and nothing is an aggregate.

create table chain_block (
    chain       text        not null,
    height      bigint      not null,
    block_hash  text        not null,
    parent_hash text        not null,
    seen_at     timestamptz not null default now(),
    orphaned_at timestamptz,
    primary key (chain, block_hash)
);

create index on chain_block (chain, height) where orphaned_at is null;

create table deposit_event (
    id           bigserial primary key,
    chain        text not null,
    block_hash   text not null,
    tx_hash      text not null,
    output_index int  not null,
    address      text not null,
    amount       numeric(38, 0) not null,
    unique (chain, tx_hash, output_index, block_hash),
    foreign key (chain, block_hash) references chain_block (chain, block_hash)
);

Two details carry most of the weight.

The amount is an integer in the smallest unit of the chain. Satoshis, wei. No floats, no decimal degrees of freedom, no unit conversion anywhere except the presentation layer. This is boring and it removes an entire category of incident.

The uniqueness key includes block_hash. That looks redundant until a fork puts the same transaction in two competing blocks. Both observations are real; exactly one of them will survive. If you make (tx_hash, output_index) unique you are asserting the chain has one history, which is precisely the assumption that breaks.

Ingestion is then a single transaction per block, and every statement in it is idempotent:

def ingest_block(conn, chain, block, watched):
    with conn, conn.cursor() as cur:
        cur.execute(
            '''
            insert into chain_block (chain, height, block_hash, parent_hash)
            values (%s, %s, %s, %s)
            on conflict (chain, block_hash) do nothing
            ''',
            (chain, block.height, block.hash, block.parent_hash),
        )
        for out in block.outputs_to(watched):
            cur.execute(
                '''
                insert into deposit_event
                    (chain, block_hash, tx_hash, output_index, address, amount)
                values (%s, %s, %s, %s, %s, %s)
                on conflict do nothing
                ''',
                (chain, block.hash, out.tx_hash, out.index, out.address, out.amount),
            )

Replaying a range costs CPU and nothing else. That property is what lets you recover from a bad deploy by rewinding the scanner cursor instead of writing a repair script under pressure.

Confirmations are a query, not a state machine

The common design gives each deposit a status column and a job that promotes pending to confirmed once enough blocks pile up. Now you own a scheduler, a retry policy, and a class of rows that get stuck in the wrong state when the job dies between two updates.

A deposit does not become confirmed. It is confirmed, because the tip moved. That is a read:

select w.account_id,
       sum(d.amount) as confirmed_amount
from deposit_event d
join chain_block b using (chain, block_hash)
join wallet_address w on w.chain = d.chain and w.address = d.address
where d.chain = %(chain)s
  and b.orphaned_at is null
  and b.height <= %(tip_height)s - %(min_confirmations)s
group by w.account_id;

I take tip_height from the highest non-orphaned block in my own table, not from the node. If the node is swapped or resyncs and briefly reports a lower tip, my projection stays consistent with the data I actually hold, and it advances again when ingestion advances.

The spendable balance is still a durable ledger posting — you cannot let a user's available funds silently shift because a query changed shape. So the projection feeds an append-only ledger, once, keyed on the money rather than on the block:

POST_DEPOSITS = '''
insert into ledger_entry (account_id, direction, amount, idempotency_key)
select w.account_id,
       'credit',
       d.amount,
       'deposit:' || d.chain || ':' || d.tx_hash || ':' || d.output_index
from deposit_event d
join chain_block b using (chain, block_hash)
join wallet_address w on w.chain = d.chain and w.address = d.address
where d.chain = %(chain)s
  and b.orphaned_at is null
  and b.height <= %(tip_height)s - %(min_confirmations)s
on conflict (idempotency_key) do nothing
'''

The idempotency key deliberately omits the block hash. If a shallow reorg moves the same transaction into a different block, it is the same money arriving once, and it must be credited once. The observation is block-scoped; the posting is not.

Reorgs: invalidate, never delete

When the chain rewinds, the temptation is to delete the rows you no longer believe in. Don't. Deleting destroys the evidence you will want during the incident review, and it turns a fork into a distributed-delete problem across every table that referenced those rows.

Instead, walk back from your tip, compare hashes with the node, and mark the divergent blocks orphaned:

def reconcile_tip(conn, chain, rpc):
    with conn, conn.cursor() as cur:
        cur.execute(
            '''
            select height, block_hash from chain_block
            where chain = %s and orphaned_at is null
            order by height desc
            limit 200
            ''',
            (chain,),
        )
        fork_height = None
        for height, block_hash in cur.fetchall():
            if rpc.block_hash_at(height) == block_hash:
                break
            cur.execute(
                '''
                update chain_block set orphaned_at = now()
                where chain = %s and block_hash = %s and orphaned_at is null
                ''',
                (chain, block_hash),
            )
            fork_height = height
        return fork_height

The scanner then re-ingests from the fork point. Every deposit event attached to an orphaned block drops out of the projection on the next read, because the join filters on orphaned_at is null. There are no compensating updates to write and no rows to remember to fix. The correction is structural.

The part you cannot solve in code

If a reorg goes deeper than your confirmation threshold, the money was already posted and the user may already have spent it. No amount of schema design prevents that; you chose a risk when you picked the threshold.

What you can do is make the threshold a function of value rather than a constant, so that small deposits stay fast and large ones wait:

And when the deep reorg does happen, the reversal is a negative ledger entry with a human decision attached, not an automatic correction. Automatic debits against user balances driven by external state are how you turn a chain event into a support catastrophe. This is the same rule I apply to fiat: automation may propose, but taking money back is a reviewed action.

What I would do differently

I would separate address ownership from ingestion earlier. In an early version the scanner resolved address -> account inline while parsing blocks, which meant a deposit to an address that had just been reassigned could be attributed against a stale mapping. Ownership should be its own versioned table with validity ranges, and attribution should be a join at projection time, not a lookup at parse time.

I would also persist the raw block payload for the confirmation window instead of only the outputs I cared about. It costs storage that you delete a day later, and it means that when you discover a parsing bug, you can re-derive events from bytes you already have rather than asking the node to serve a range it may have pruned.

The general shape has held up across everything I have built with money in it, on-chain or not: record observations immutably, derive state by query, and make every write idempotent on a key that describes the money rather than the delivery. Reorgs, retries and replays stop being incidents and become inputs the system was already designed to accept.

Originally published on polycratia.com — where I write about payment systems, crypto rails and marketplace backends.