T16 · Databases and Onchain Data

A Real-World Problem

You create a table with three columns:

create table balances (
  address text primary key,
  token   text,
  amount  numeric
);

An indexer reads each transfer event and runs an update. The numbers look right on day one.

On day three, operations reports that an address is missing a deposit.

You open the row: address, token, amount. Then you realize:

You have no way to explain where that amount came from.

It is the result of hundreds of additions and subtractions. You cannot identify which transaction was missed, which block it belonged to, whether you missed an incoming transfer or counted an outgoing one twice, or whether the error began today or three days ago.

All you can do is clear the database and replay from the beginning. It matches afterward, but you still do not know why it failed, so you cannot know whether it will happen again.

Three days later, it does.

This is a data-model problem, not just a bug. The table discarded the chain’s most valuable information from day one.

Thought Experiment

Consider two styles of accounting.

First: record only the balance. A sheet says “Cash: 8,500.” Spend 300; cross it out and write 8,200. Receive 1,000; replace it with 9,200.

Current values are quick to read, but discrepancies are impossible to investigate. A month later, 200 is missing. The only clue is the current number, which explains nothing.

Second: record transactions and calculate the balance. Each entry has a date, counterparty, amount, and receipt number. The balance is a conclusion derived from the entries, not a number continually overwritten.

Queries may be slower, but there is a decisive advantage: every number can be traced back to a receipt. If accounts disagree, inspect transactions individually, recompute a range, or reverse one invalid receipt.

Recall T1: a chain is the opposite of the first ledger. It records transactions, from which state is derived. Anyone can verify the same history independently precisely because the entries are retained.

That makes the problem stark:

The chain paid a high price to preserve a transaction history, and your database compressed it into one number.

You lost both traceability and the chain’s central verifiability.

There is another case a balance-only ledger cannot handle: onchain “receipts” can be withdrawn by a reorg. Transactions recorded yesterday may disappear from canonical history. With only a number, you cannot tell which changes to reverse.

Your Decision

Design storage for a protocol’s transfer events. Consider four approaches:

What Happens

Four questions distinguish the designs:

The first three ask the same underlying question: did you preserve the provenance of each row?

Put this rule on your schema-review checklist:

Every database row must answer: which transaction produced it, at which block height, and is that block still on the canonical chain?

The third is easiest to miss. tx_hash and block_number answer the first two, but the third also needs the block hash. Without it, you cannot know which branch a row belongs to after a reorg.

Build a Model

Separate three kinds of table by responsibility:

Fact Table: The Primary Key Is an Onchain Coordinate

-- One row = one onchain log. Append only; never update.
create table transfer_events (
  chain_id      integer       not null,
  block_number  bigint        not null,
  log_index     integer       not null,
  block_hash    bytea         not null,      -- Used to detect reorgs
  block_time    timestamptz   not null,      -- From block header, not now()
  tx_hash       bytea         not null,
  tx_index      integer       not null,
  contract      bytea         not null,
  from_addr     bytea         not null,
  to_addr       bytea         not null,
  amount        numeric(78,0) not null,      -- Holds a 256-bit integer
  primary key (chain_id, block_number, log_index)
);

create index on transfer_events (chain_id, contract, from_addr, block_number);
create index on transfer_events (chain_id, contract, to_addr,   block_number);

Six decisions in this DDL address six incident classes:

The primary key is an onchain coordinate, not an auto-increment ID. Chain, block, and log position uniquely identify an event. Repeated ingestion is rejected automatically: this is the basis of idempotent ingestion. Auto-increment duplicates rows when you replay.

chain_id belongs in the key. Block heights overlap between chains. Without this column, adding a chain causes quiet collisions and strange symptoms.

For amount, use numeric(78,0). Onchain quantities can be 256-bit unsigned integers. bigint is too small and float loses precision. Storing token quantities in floating point is a severe correctness error. Seventy-eight decimal digits cover the range.

Store block_hash. It identifies which branch your row came from. Without it, a reorg can leave permanently dirty data that you cannot recognize.

Take block_time from the block header, not now(). Ingestion time is not event time. Backfilling a year of history would otherwise give all rows today’s timestamp.

Normalize addresses and hashes. bytea is compact and avoids casing errors. If you use text, normalize all addresses to lowercase or the same address may become two rows and joins may silently omit data.

Checkpoints and Blocks

-- One row per chain: indexing progress.
create table indexer_checkpoints (
  chain_id          integer     primary key,
  last_block_number bigint      not null,
  last_block_hash   bytea       not null,
  updated_at        timestamptz not null default now()
);

-- Keep a recent chain of block hashes for tracing reorgs.
create table blocks (
  chain_id     integer     not null,
  block_number bigint      not null,
  block_hash   bytea       not null,
  parent_hash  bytea       not null,       -- Validates continuity
  block_time   timestamptz not null,
  primary key (chain_id, block_number)
);

The blocks table is essential. Without it, you know only “I indexed through block 1000,” not what block 1000 looked like when you saw it. You cannot detect a reorg. T17 uses this table extensively.

Idempotent Ingestion in One Statement

insert into transfer_events
  (chain_id, block_number, log_index, block_hash, block_time,
   tx_hash, tx_index, contract, from_addr, to_addr, amount)
values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
on conflict (chain_id, block_number, log_index) do nothing;

That is enough. Replay the same block range a hundred times and the row count stays fixed. Idempotency comes from the primary key, not an application check asking whether you processed the log.

Derived Table: The Easy Place to Get It Wrong

create table token_balances (
  chain_id         integer       not null,
  contract         bytea         not null,
  holder           bytea         not null,
  balance          numeric(78,0) not null,
  updated_at_block bigint        not null,
  primary key (chain_id, contract, holder)
);

How should you update it?

A natural implementation inserts the event and adds its amount to the recipient. Replay doubles balances: the event insert is blocked by do nothing, but the balance update runs again.

Tie the actions together in the same statement so balances change only when the event was actually inserted for the first time:

with inserted as (
  insert into transfer_events
    (chain_id, block_number, log_index, block_hash, block_time,
     tx_hash, tx_index, contract, from_addr, to_addr, amount)
  values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
  on conflict (chain_id, block_number, log_index) do nothing
  returning chain_id, block_number, contract, from_addr, to_addr, amount
),
deltas as (
  select chain_id, contract, to_addr   as holder,  amount as delta, block_number from inserted
  union all
  select chain_id, contract, from_addr as holder, -amount as delta, block_number from inserted
)
insert into token_balances as b (chain_id, contract, holder, balance, updated_at_block)
select chain_id, contract, holder, delta, block_number from deltas
on conflict (chain_id, contract, holder) do update
set balance          = b.balance + excluded.balance,
    updated_at_block = greatest(b.updated_at_block, excluded.updated_at_block);

Read this SQL closely. returning is the key: on conflict, do nothing returns no rows; then deltas is empty and balances do not move.

Remember:

Put “is this the first time we have seen the log?” and “should the projection change?” in the same transaction and statement.

Separate them and replay, retry, and concurrency can each break consistency.

Deleting After a Reorg

Delete facts above the surviving height:

begin;
delete from transfer_events where chain_id = $1 and block_number > $2;
delete from blocks           where chain_id = $1 and block_number > $2;
update indexer_checkpoints
  set last_block_number = $2,
      last_block_hash   = (select block_hash from blocks where chain_id = $1 and block_number = $2)
  where chain_id = $1;
commit;

But the derived balance table does not fix itself. Deleting events does not reverse increments already applied.

Two approaches:

• Recalculate: reset balances of affected holders and aggregate again from facts. Slower but simple and reliable.
• Record provenance of each projection change: maintain a row connecting every change to its event, then apply reverse changes by height. Faster but doubles storage and complexity.

Choose recalculation first. Correctness matters more, and a reorg usually touches only a few head blocks and a bounded set of addresses.

What Belongs in Redis?

One sentence: you must be able to rebuild anything in Redis from Postgres.

Appropriate: query cache, deduplication window, rate-limit counters, leaderboards, latest height. Inappropriate: the only copy of a fact about what happened onchain.

The issue is not that Redis is unreliable. Facts should have one authoritative copy. Two copies eventually diverge, likely when you can least afford it.

What It Is Called

Hands-on

select count(*) from transfer_events;

delete from transfer_events where chain_id = $1 and block_number > $2;  -- $2 = N - 5
delete from blocks           where chain_id = $1 and block_number > $2;

select md5(string_agg(t.tx_hash::text || t.amount::text, '|'
              order by t.chain_id, t.block_number, t.log_index))
from transfer_events t
where t.chain_id = $1 and t.block_number <= $2;

AI Lab

Step one:
Design PostgreSQL tables for one ERC-20 token's Transfer events. Requirements:
query history and current balance by address, support multiple chains, and scale to
hundreds of millions of events. Provide complete DDL and ingestion SQL.

Step two:
A reorg replaces the last five indexed blocks with five different blocks. Three
previous transfer events disappear and two new ones appear. Using your schema and
write logic, explain step by step:
1. How do I detect the reorg?
2. Which rows do I delete, and with what SQL?
3. What happens to the balance table? Is it still correct?
4. After reindexing, how do I prove balances match canonical history?

Real-World Case

Change One Variable

Takeaway Questions

Chapter Check