Model many-to-many token transfers
A swap or batch transfer moves several tokens between several addresses. Cramming them into one transaction row makes per-holder balances and per-token supply queries wrong.
Procedure
- Model the event, not the transaction:
transfers(log_index, tx_hash, token, from_addr, to_addr, amount numeric).
- Keep
amount as a numeric of the token's raw units; a single bigint fails for 18-decimal tokens near their max:CREATE TABLE transfers (
block_number bigint, block_hash bytea, log_index int, tx_hash bytea,
token bytea, from_addr bytea, to_addr bytea,
amount numeric(78,0), PRIMARY KEY (block_hash, tx_hash, log_index)
);
- Link to the parent by
tx_hash, but do not merge transfers into it; a batch holds N transfers per 1 tx.
- Reconstruct a holder balance with a windowed sum, not a stored running total:
SELECT addr, sum(delta) FROM (SELECT to_addr AS addr, amount AS delta FROM transfers WHERE token=$T UNION ALL SELECT from_addr, -amount FROM transfers WHERE token=$T) s GROUP BY 1;
- Use the zero address for mints and burns consistently so supply = sum(mints) - sum(burns).
- Index
(token, to_addr) and (token, from_addr) for holder queries.
Pitfalls
- One row per transaction loses the intermediate parties of a multi-hop swap; downstream balance math is then wrong.
float/double for amounts loses precision on large 18-decimal values; use numeric(78,0).
- Counting a transfer to the zero address as normal inflates holder counts.
- The same log_index under two block hashes (reorg) must stay distinct; key on the hash.
Verification
psql -c "SELECT token, sum(CASE WHEN to_addr='\x0000000000000000000000000000000000000000' THEN amount ELSE 0 END) - sum(CASE WHEN from_addr='\x0000000000000000000000000000000000000000' THEN amount ELSE 0 END) AS supply FROM transfers GROUP BY token;"
# compare each row against totalSupply() for that token via cast call
Report transfer row counts per transaction and per-token supply cross-checked against totalSupply().
1---2name: model-many-to-many-token-transfers3description: Use when indexing token flows where one transaction moves many assets between many holders. Normalises transfers into rows of sender, receiver, token and amount instead of one fat row per transaction.4metadata:5 source: https://skillmd.com/skills/jiayaoqijia/model-many-to-many-token-transfers6---78# Model many-to-many token transfers910A swap or batch transfer moves several tokens between several addresses. Cramming them into one transaction row makes per-holder balances and per-token supply queries wrong.1112## Procedure13141. Model the event, not the transaction: `transfers(log_index, tx_hash, token, from_addr, to_addr, amount numeric)`.152. Keep `amount` as a numeric of the token's raw units; a single bigint fails for 18-decimal tokens near their max:16 ```sql17 CREATE TABLE transfers (18 block_number bigint, block_hash bytea, log_index int, tx_hash bytea,19 token bytea, from_addr bytea, to_addr bytea,20 amount numeric(78,0), PRIMARY KEY (block_hash, tx_hash, log_index)21 );22 ```233. Link to the parent by `tx_hash`, but do not merge transfers into it; a batch holds N transfers per 1 tx.244. Reconstruct a holder balance with a windowed sum, not a stored running total:25 `SELECT addr, sum(delta) FROM (SELECT to_addr AS addr, amount AS delta FROM transfers WHERE token=$T UNION ALL SELECT from_addr, -amount FROM transfers WHERE token=$T) s GROUP BY 1;`265. Use the zero address for mints and burns consistently so supply = sum(mints) - sum(burns).276. Index `(token, to_addr)` and `(token, from_addr)` for holder queries.2829## Pitfalls3031- One row per transaction loses the intermediate parties of a multi-hop swap; downstream balance math is then wrong.32- `float`/`double` for amounts loses precision on large 18-decimal values; use `numeric(78,0)`.33- Counting a transfer to the zero address as normal inflates holder counts.34- The same log_index under two block hashes (reorg) must stay distinct; key on the hash.3536## Verification3738 psql -c "SELECT token, sum(CASE WHEN to_addr='\x0000000000000000000000000000000000000000' THEN amount ELSE 0 END) - sum(CASE WHEN from_addr='\x0000000000000000000000000000000000000000' THEN amount ELSE 0 END) AS supply FROM transfers GROUP BY token;"39 # compare each row against totalSupply() for that token via cast call4041Report transfer row counts per transaction and per-token supply cross-checked against `totalSupply()`.