mirror of
https://github.com/Twigpine/openlaunch.git
synced 2026-11-09 05:20:09 -10:00
Kevin Codex
3d8ce76840
- Metadata ownership is per (chain, launcher, meta_key), the thing the public URI is keyed by: the first registration under a key fixes its content; later rows under the same key must match exactly (the salt search) or get 409. Serialized with a transaction-scoped advisory lock so two concurrent first writes cannot both win (tested: 10/10 races → one 200, one 409). Reads prefer the launched row, then the FIRST registration, never a newer one. - fly-secrets.sh reads every key independently from the secrets file (no RPC derivation), fails on missing required keys, supports --dry-run; verified against mocked files. - rebuild-launch-totals.sql / rebuild-holders.sql run in one transaction holding SHARE locks on the raw event tables and EXCLUSIVE on the target table, so ingestion waits instead of racing (tested: an ingestion started mid-rebuild blocked 3s, then applied on top; totals consistent).
46 lines
3.7 KiB
PL/PgSQL
46 lines
3.7 KiB
PL/PgSQL
-- Rebuild bb_token_holders from bb_token_transfers (the deduped source of truth) and refresh the
|
|
-- per-launch holders counters. Idempotent; safe to run any time (a few thousand rows).
|
|
-- Usage: fly ssh console -a basebid-db -C "sh -c 'PGPASSWORD=$OPERATOR_PASSWORD psql -h localhost -p 5433 -U postgres -d basebid -f /tmp/rebuild-holders.sql'"
|
|
BEGIN;
|
|
-- serialize against the indexer: transfers cannot be ingested while balances are recomputed
|
|
LOCK TABLE bb_token_transfers IN SHARE MODE;
|
|
LOCK TABLE bb_token_holders IN EXCLUSIVE MODE;
|
|
LOCK TABLE bb_launches IN EXCLUSIVE MODE;
|
|
WITH legs AS (
|
|
SELECT chain_id, token, from_addr AS holder, -value AS d, block_number FROM bb_token_transfers WHERE from_addr <> '0x0000000000000000000000000000000000000000'
|
|
UNION ALL
|
|
SELECT chain_id, token, to_addr, value, block_number FROM bb_token_transfers WHERE to_addr <> '0x0000000000000000000000000000000000000000'
|
|
), agg AS (
|
|
SELECT chain_id, token, holder, sum(d) AS balance, min(block_number) AS first_block, max(block_number) AS last_block FROM legs GROUP BY 1, 2, 3
|
|
)
|
|
INSERT INTO bb_token_holders (chain_id, token, holder, balance, first_block, last_block)
|
|
SELECT chain_id, token, holder, balance, first_block, last_block FROM agg
|
|
ON CONFLICT (chain_id, token, holder) DO UPDATE
|
|
SET balance = EXCLUDED.balance, first_block = LEAST(bb_token_holders.first_block, EXCLUDED.first_block), last_block = GREATEST(bb_token_holders.last_block, EXCLUDED.last_block);
|
|
-- rows with no transfer legs left (should not exist) → zero them rather than delete, keeps history
|
|
UPDATE bb_token_holders h SET balance = 0
|
|
WHERE NOT EXISTS (SELECT 1 FROM bb_token_transfers t WHERE t.chain_id = h.chain_id AND t.token = h.token AND (t.from_addr = h.holder OR t.to_addr = h.holder));
|
|
-- holders counter: wallets with balance > 0, excluding protocol + burn addresses on either chain
|
|
UPDATE bb_launches l SET holders = COALESCE(c.n, 0)
|
|
FROM (SELECT l2.chain_id, l2.token,
|
|
(SELECT count(*) FROM bb_token_holders h WHERE h.chain_id = l2.chain_id AND h.token = l2.token AND h.balance > 0
|
|
AND h.holder NOT IN ('0x0000000000000000000000000000000000000000',
|
|
'0x000000000000000000000000000000000000dead',
|
|
'0x000000000022d473030f116ddee9f6b43ac78ba3',
|
|
'0x498581ff718922c3f8e6a244956af099b2652b2b',
|
|
'0x58daec3116aae6d93017baaea7749052e8a04fa7',
|
|
'0x6ff5693b99212da76ad316178a184ab56d299b43',
|
|
'0x7c5f5a4bbd8fd63184577525326123b519429bdc',
|
|
'0x815542e8b392389a1389e22e588e4b62a67ade72',
|
|
'0x8366a39cc670b4001a1121b8f6a443a643e40951',
|
|
'0x8876789976decbfcbbbe364623c63652db8c0904',
|
|
'0xcd1680d26922fcd9cabfbb8a56ba40c333fd842a')) AS n
|
|
FROM bb_launches l2) c
|
|
WHERE l.chain_id = c.chain_id AND l.token = c.token;
|
|
COMMIT;
|
|
-- check: every launch's balances + burns to zero must equal its supply
|
|
WITH sums AS (SELECT chain_id, token, sum(balance) held FROM bb_token_holders GROUP BY 1, 2),
|
|
burned AS (SELECT chain_id, token, COALESCE(sum(value), 0) b FROM bb_token_transfers WHERE to_addr = '0x0000000000000000000000000000000000000000' GROUP BY 1, 2)
|
|
SELECT count(*) FILTER (WHERE l.supply = s.held + COALESCE(b.b, 0)) AS ok, count(*) FILTER (WHERE l.supply <> s.held + COALESCE(b.b, 0)) AS mismatch
|
|
FROM bb_launches l LEFT JOIN sums s ON s.chain_id = l.chain_id AND s.token = l.token LEFT JOIN burned b ON b.chain_id = l.chain_id AND b.token = l.token;
|