mirror of
https://github.com/Twigpine/openlaunch.git
synced 2026-11-11 05:01:34 -10:00
Kevin CodexandClaude Opus 5.5
01a908d81a
From a strict review of af4e6e5..133fabd (#133, #136): - Community pairs are never stored (usdLedger.capturable). A pair priced from an outside pool can be pushed for a minute, and a captured price stayed in the totals for good. A suspended or delisted pair must show no USD, which a stored value defeated. Their trades keep usd NULL and count at the pair's price now, which follows its listing status. The backfill (apply) clears pair values captured since #133 and drops their ledger rows. - A launch row whose volume is partly unpriced while its quote has no price right now shows its quote amount (launchVolumeUsd: null) instead of a smaller dollar figure that looked complete. - The receipt path (applyLaunchTx, the user is waiting) no longer captures live prices: a capture could wait on Coinbase and Chainlink. Those trades take the hour's price the poller captured, or the heal pass prices them. - Heal pass (usdLedger.healUnpricedUsd), every 5 minutes per chain after a sync: - prices the last 3 days' trades and quote-side fees with no USD whose hour now has a price, and adds them to the launch totals in the same statement - skips pairs and re-tests usd IS NULL inside the UPDATE, so two machines change each row and each total once - Backfill apply is ONE transaction under the rebuild-launch-totals locks: pair cleanup, pricing and the totals recompute commit together. No reader sees rows priced but not yet summed, and a failure changes nothing. The dry run counts pairs as unpriced and stays write-free. - The lists probe one launch at a time (LATERAL u on the new partial index bb_launch_swaps_usd_missing_token_idx) instead of aggregating every unpriced trade per request. The totals' unpriced-fee read gets bb_launch_fee_events_usd_missing_idx. - scripts/rebuild-launch-totals.sql recomputes the USD totals too and reports USD drift before and after. - Wording: the tooltip and the partial note describe what has no recorded value. The backfill retries network errors and timeouts, not only 429/5xx. Deferred, not risky: the pairs pages and the token fee panel still use today's price; the volume sort mixes units during an ETH price outage (pre-existing); per-hour source labels; poolMaps / quoteUsdNow duplication. Verified on a throwaway Postgres 16 with the real migration (3 partial indexes built): - two concurrent heal passes priced the missed trade and fee once (AETH $2,000 → $6,200, not $10,400; fee $21); a rerun and a throttled run changed nothing; the pair stayed NULL - the dry run reported pairs as unpriced and changed nothing - apply (1 BEGIN / 1 COMMIT) cleared the old pair value and its ledger rows and left AETH at $6,200 - the repair script ran with 0 USD drift - the real reads: AETH $6,200, the pair row null (quote amount), sort ok, totals $6,200 and partial tsc and lint clean; npm test 1410 (1399 pass, 11 skipped, 0 fail). Co-Authored-By: Claude Opus 5.5 <[email protected]>
27 lines
2.9 KiB
SQL
27 lines
2.9 KiB
SQL
-- Indexes on the big, busy tables, built by scripts/migrate.mjs AFTER the schema transaction commits, one at a time
|
|
-- with CREATE INDEX CONCURRENTLY: reads and writes on the table never wait for the build. (Inside the schema
|
|
-- transaction a build would hold the exclusive lock an ALTER TABLE on that table already took, and every query on
|
|
-- it would queue behind the whole build.) One statement per entry, each `CREATE INDEX CONCURRENTLY IF NOT EXISTS`;
|
|
-- an index a failed build left invalid is dropped and built again on the next deploy. Only for indexes whose columns
|
|
-- schema.sql already has.
|
|
|
|
-- swap attribution (2026-10-07): the unchecked set, every swap until the history drain reaches it, then only the
|
|
-- newest few (the partial index stays small); EntryPoint calls whose receipt is not read yet
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS bb_launch_swaps_unattributed_idx ON bb_launch_swaps (chain_id, block_number DESC) WHERE trader_via IS NULL;
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS bb_launch_swaps_receipt_pending_idx ON bb_launch_swaps (chain_id, block_number DESC) WHERE trader_via = 'receipt_pending';
|
|
-- one wallet's trades (profile pages, /me, posting eligibility, points) without scanning every swap
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS bb_launch_swaps_trader_idx ON bb_launch_swaps (trader, block_number DESC);
|
|
-- points (2026-10-09): every balance change of a season's buyers and launchers, read by wallet in both directions, up
|
|
-- to the season's last block; and transfers out of a launcher or a fee recipient (who got the token from them)
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS bb_token_transfers_to_idx ON bb_token_transfers (chain_id, token, to_addr, block_number);
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS bb_token_transfers_from_idx ON bb_token_transfers (chain_id, token, from_addr, block_number);
|
|
-- boosts (2026-10-15): a boost names its launch by position id; the lane joins on it
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS bb_launches_chain_token_id_idx ON bb_launches (chain_id, token_id);
|
|
-- trade-time USD (2026-11-10): the trades still without a USD value (every one until the first backfill, then only
|
|
-- those whose hour had no price yet), so a repeat backfill and the "partial" check never scan every swap
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS bb_launch_swaps_usd_missing_idx ON bb_launch_swaps (chain_id, block_time) WHERE usd IS NULL;
|
|
-- trade-time USD review (2026-11-11): the lists' per-launch probe of trades with no USD value (queries.SELECT's LATERAL u), and
|
|
-- the site totals' read of quote-side fees with no USD value (getLaunchTotals): neither scans its whole table on a page view
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS bb_launch_swaps_usd_missing_token_idx ON bb_launch_swaps (chain_id, token) WHERE usd IS NULL;
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS bb_launch_fee_events_usd_missing_idx ON bb_launch_fee_events (chain_id, token) WHERE usd IS NULL AND kind IN ('collected', 'burned');
|