Store raw logs before decoding
Decoding during ingestion is a one-way door: if the ABI was wrong the original bytes are gone. Persist the untouched log first, decode into derived tables second.
Procedure
- Ingest the raw log verbatim into
raw_logs, keyed so re-ingestion is a no-op:CREATE TABLE raw_logs ( block_hash bytea NOT NULL, block_number bigint NOT NULL, tx_hash bytea NOT NULL, log_index int NOT NULL, address bytea NOT NULL, topics bytea[] NOT NULL, data bytea NOT NULL, removed boolean NOT NULL DEFAULT false, PRIMARY KEY (block_hash, tx_hash, log_index) ); - Insert with
ON CONFLICT DO NOTHINGso replaying a block adds nothing. - Decode in a second pass from
raw_logsinto typed tables (transfers,swaps). A decoder bug then means re-running the second pass, not an archive hit. - Record the decoder used per row:
decoder_version smallint NOT NULL. - Check the raw row count for a block equals the node's
eth_getLogscount before decoding. - Keep the
removedflag as the node passed it; a reorg marks rows rather than silently dropping them.
Pitfalls
- Decoding inside the fetch loop and discarding the bytes makes an ABI fix a full historical refetch of hours of archive RPC.
- Storing only decoded columns means a newly wanted field cannot be recovered for past blocks without archive access you may not have.
topicsis an array; flattening it to one column loses the indexed-parameter positions you need to decode.- Ignoring
removedmakes reorg-dropped logs look like valid events.
Verification
psql -c "SELECT count(*) FROM raw_logs WHERE block_number=$N;"
# expect it to equal the eth_getLogs length for block $N
Report raw rows ingested per block and that decode runs as a separate, re-runnable pass keyed on raw_logs.