mirror of
https://github.com/Twigpine/openlaunch.git
synced 2026-11-11 05:01:34 -10:00
Kevin Codex
12ba7c3f63
- bb_launch_swaps.via_app: true when the swap's transaction carries the app's ERC-8021 builder suffix. That means a wallet's own Universal Router call, or the smart-wallet operation the swap is credited to. Set on insert, and again when attribution or the trader heal re-credits a swap. NULL means unknown, which never counts. Only the tag result is cached, never the input. applyLaunchTx reads the transaction for its input; if that read fails, the swap is still credited from the receipt, with the tag unknown. - Checked on real Base trades from the public feed: router trades from the app are tagged; smart-wallet trades routed through 0x's AllowanceHolder (another front end) are not. - bb_seasons.rules_version: a season keeps the version it started with (score.rulesFor), and new seasons start on LATEST_RULES_VERSION. Every existing season is version 1. scoreSeason takes its rules as an argument. The admin panel and /api/points show the version.
660 lines
40 KiB
SQL
660 lines
40 KiB
SQL
-- basebid.lol · free token launchpad on Base.
|
||
--
|
||
-- Applied by scripts/migrate.mjs (Fly release_command) against the DEDICATED
|
||
-- basebid Postgres on EVERY deploy, inside one transaction — so everything here
|
||
-- must be idempotent: CREATE … IF NOT EXISTS, ALTER TABLE … ADD COLUMN IF NOT
|
||
-- EXISTS. Tables keep the historical `bb_` prefix. The app connects as the
|
||
-- database owner over Fly's private network (no RLS / roles).
|
||
--
|
||
-- Source of truth is the chain (LaunchFactory / LaunchLocker / PoolManager
|
||
-- events); these tables are an index of it plus off-chain metadata and the
|
||
-- indexer cursor. Amounts are wei as numeric(78,0). Addresses LOWERCASE hex.
|
||
--
|
||
-- NOTE: the pre-2026-09 stake-to-rank tables (bb_entries, bb_bids, …) are no
|
||
-- longer created here and are intentionally NOT dropped: they hold the
|
||
-- history of the old board. Drop them by hand once the escrow is wound down.
|
||
|
||
CREATE TABLE IF NOT EXISTS bb_migrations (
|
||
id bigserial PRIMARY KEY,
|
||
schema_sha256 text NOT NULL,
|
||
applied_at timestamptz NOT NULL DEFAULT now(),
|
||
applied_by text
|
||
);
|
||
|
||
-- ── launchpad ────────────────────────────────────────────────────────────────
|
||
-- Index of the fee-free LaunchFactory / LaunchLocker (contracts/docs/LAUNCHPAD.md)
|
||
-- plus PoolManager Swap events for the pools it created. Amounts are wei as
|
||
-- numeric(78,0) (exact; never through a double). Addresses / ids LOWERCASE hex.
|
||
|
||
CREATE TABLE IF NOT EXISTS bb_launches (
|
||
token text PRIMARY KEY,
|
||
token_id bigint NOT NULL,
|
||
launcher text NOT NULL,
|
||
quote text NOT NULL, -- 0x000…000 = native ETH
|
||
pool_id text NOT NULL UNIQUE,
|
||
start_tick integer NOT NULL,
|
||
lp_fee integer NOT NULL, -- pips (10000 = 1%)
|
||
supply numeric(78,0) NOT NULL,
|
||
metadata_uri text NOT NULL DEFAULT '',
|
||
name text NOT NULL,
|
||
symbol text NOT NULL,
|
||
block_number bigint NOT NULL,
|
||
block_time timestamptz NOT NULL,
|
||
tx_hash text NOT NULL,
|
||
log_index integer NOT NULL,
|
||
-- rolling state maintained by swap / fee ingestion
|
||
sqrt_price_x96 numeric(78,0),
|
||
tick integer,
|
||
last_swap_block bigint NOT NULL DEFAULT 0,
|
||
last_swap_log integer NOT NULL DEFAULT 0,
|
||
volume_quote numeric(78,0) NOT NULL DEFAULT 0, -- |quote| traded, buys + sells
|
||
buys integer NOT NULL DEFAULT 0,
|
||
sells integer NOT NULL DEFAULT 0,
|
||
last_trade_at timestamptz,
|
||
fees_quote_collected numeric(78,0) NOT NULL DEFAULT 0,
|
||
fees_token_collected numeric(78,0) NOT NULL DEFAULT 0,
|
||
fees_quote_burned numeric(78,0) NOT NULL DEFAULT 0,
|
||
fees_token_burned numeric(78,0) NOT NULL DEFAULT 0,
|
||
UNIQUE (tx_hash, log_index)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_launches_time_idx ON bb_launches (block_number DESC, log_index DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_launches_volume_idx ON bb_launches (volume_quote DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_launches_launcher_idx ON bb_launches (launcher);
|
||
|
||
CREATE TABLE IF NOT EXISTS bb_launch_swaps (
|
||
tx_hash text NOT NULL,
|
||
log_index integer NOT NULL,
|
||
token text NOT NULL REFERENCES bb_launches(token),
|
||
pool_id text NOT NULL,
|
||
trader text, -- tx.from (router is the event sender)
|
||
amount0 numeric(78,0) NOT NULL, -- signed, swapper's perspective (quote)
|
||
amount1 numeric(78,0) NOT NULL, -- signed (token)
|
||
sqrt_price_x96 numeric(78,0) NOT NULL,
|
||
tick integer NOT NULL,
|
||
is_buy boolean NOT NULL,
|
||
block_number bigint NOT NULL,
|
||
block_time timestamptz NOT NULL,
|
||
PRIMARY KEY (tx_hash, log_index)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_launch_swaps_token_idx ON bb_launch_swaps (token, block_number DESC, log_index DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_launch_swaps_time_idx ON bb_launch_swaps (block_number DESC, log_index DESC);
|
||
|
||
CREATE TABLE IF NOT EXISTS bb_launch_fee_events (
|
||
tx_hash text NOT NULL,
|
||
log_index integer NOT NULL,
|
||
kind text NOT NULL CHECK (kind IN ('collected','burned','paid','credited','claimed')),
|
||
token_id bigint, -- null for credited/claimed
|
||
token text,
|
||
currency text,
|
||
account text,
|
||
quote_amount numeric(78,0),
|
||
token_amount numeric(78,0),
|
||
amount numeric(78,0),
|
||
block_number bigint NOT NULL,
|
||
block_time timestamptz NOT NULL,
|
||
PRIMARY KEY (tx_hash, log_index)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_launch_fee_events_token_idx ON bb_launch_fee_events (token, block_number DESC);
|
||
|
||
-- Off-chain metadata, written BEFORE the launch tx against the predicted token
|
||
-- address (the on-chain metadataURI points at /api/launch/meta/<token>).
|
||
CREATE TABLE IF NOT EXISTS bb_launch_meta (
|
||
token text PRIMARY KEY,
|
||
launcher text NOT NULL,
|
||
name text NOT NULL,
|
||
symbol text NOT NULL,
|
||
description text,
|
||
image_url text,
|
||
website text,
|
||
x_handle text,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS bb_launch_sync_state (
|
||
id integer PRIMARY KEY DEFAULT 1 CHECK (id = 1),
|
||
cursor_block bigint NOT NULL DEFAULT 0, -- last block fully scanned
|
||
head_block bigint,
|
||
last_run_at timestamptz,
|
||
last_error text
|
||
);
|
||
INSERT INTO bb_launch_sync_state (id) VALUES (1) ON CONFLICT DO NOTHING;
|
||
ALTER TABLE bb_launches ADD COLUMN IF NOT EXISTS recipients jsonb NOT NULL DEFAULT '[]'::jsonb; -- [{payout,bps}] from LaunchLocker.recipientsOf
|
||
ALTER TABLE bb_launch_fee_events DROP CONSTRAINT IF EXISTS bb_launch_fee_events_kind_check;
|
||
ALTER TABLE bb_launch_fee_events ADD CONSTRAINT bb_launch_fee_events_kind_check CHECK (kind IN ('collected','burned','paid','credited','claimed'));
|
||
|
||
-- ── site pulse: all-time visits + who is online now (cookie-free, no PII) ────
|
||
-- visitor_hash = sha256(ip | user-agent | daily salt): rotates daily, cannot be reversed.
|
||
CREATE TABLE IF NOT EXISTS bb_site_counters (
|
||
id integer PRIMARY KEY DEFAULT 1 CHECK (id = 1),
|
||
visits bigint NOT NULL DEFAULT 0 -- sessions: one per visitor per 30 min
|
||
);
|
||
CREATE TABLE IF NOT EXISTS bb_presence (
|
||
visitor_hash text PRIMARY KEY,
|
||
first_seen timestamptz NOT NULL DEFAULT now(),
|
||
last_seen timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_presence_last_seen_idx ON bb_presence (last_seen DESC);
|
||
-- Seed the counter ONCE from the pre-pivot analytics (bb_daily_site rollups + un-rolled
|
||
-- bb_views), if those tables exist, so the all-time number carries over.
|
||
DO $$
|
||
DECLARE seeded bigint := 0;
|
||
BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM bb_site_counters WHERE id = 1) THEN
|
||
IF to_regclass('bb_daily_site') IS NOT NULL THEN
|
||
SELECT COALESCE(sum(views), 0) INTO seeded FROM bb_daily_site;
|
||
IF to_regclass('bb_views') IS NOT NULL THEN
|
||
seeded := seeded + (SELECT count(*) FROM bb_views v WHERE v.kind = 'human'
|
||
AND v.day > COALESCE((SELECT max(day) FROM bb_daily_site), '1970-01-01'::date));
|
||
END IF;
|
||
END IF;
|
||
INSERT INTO bb_site_counters (id, visits) VALUES (1, seeded);
|
||
END IF;
|
||
END $$;
|
||
CREATE INDEX IF NOT EXISTS bb_launch_swaps_token_time_idx ON bb_launch_swaps (token, block_time DESC); -- rolling 1h/24h stats
|
||
|
||
-- ── multi-chain (2026-09-06): Base 8453 + Robinhood Chain 4663 ──────────────
|
||
-- Rows are keyed by (chain_id, token). Existing rows are Base. Idempotent.
|
||
ALTER TABLE bb_launches ADD COLUMN IF NOT EXISTS chain_id integer NOT NULL DEFAULT 8453;
|
||
ALTER TABLE bb_launch_swaps ADD COLUMN IF NOT EXISTS chain_id integer NOT NULL DEFAULT 8453;
|
||
ALTER TABLE bb_launch_fee_events ADD COLUMN IF NOT EXISTS chain_id integer NOT NULL DEFAULT 8453;
|
||
ALTER TABLE bb_launch_meta ADD COLUMN IF NOT EXISTS chain_id integer NOT NULL DEFAULT 8453;
|
||
DO $$
|
||
BEGIN
|
||
IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'bb_launch_swaps_token_fkey') THEN
|
||
ALTER TABLE bb_launch_swaps DROP CONSTRAINT bb_launch_swaps_token_fkey;
|
||
END IF;
|
||
IF EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid WHERE t.relname = 'bb_launches' AND c.contype = 'p' AND array_length(c.conkey, 1) = 1) THEN
|
||
ALTER TABLE bb_launches DROP CONSTRAINT bb_launches_pkey;
|
||
ALTER TABLE bb_launches ADD PRIMARY KEY (chain_id, token);
|
||
END IF;
|
||
IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'bb_launches_pool_id_key') THEN
|
||
ALTER TABLE bb_launches DROP CONSTRAINT bb_launches_pool_id_key;
|
||
END IF;
|
||
IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'bb_launches_tx_hash_log_index_key') THEN
|
||
ALTER TABLE bb_launches DROP CONSTRAINT bb_launches_tx_hash_log_index_key;
|
||
END IF;
|
||
IF EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid WHERE t.relname = 'bb_launch_swaps' AND c.contype = 'p' AND array_length(c.conkey, 1) = 2) THEN
|
||
ALTER TABLE bb_launch_swaps DROP CONSTRAINT bb_launch_swaps_pkey;
|
||
ALTER TABLE bb_launch_swaps ADD PRIMARY KEY (chain_id, tx_hash, log_index);
|
||
END IF;
|
||
IF EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid WHERE t.relname = 'bb_launch_fee_events' AND c.contype = 'p' AND array_length(c.conkey, 1) = 2) THEN
|
||
ALTER TABLE bb_launch_fee_events DROP CONSTRAINT bb_launch_fee_events_pkey;
|
||
ALTER TABLE bb_launch_fee_events ADD PRIMARY KEY (chain_id, tx_hash, log_index);
|
||
END IF;
|
||
IF EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid WHERE t.relname = 'bb_launch_meta' AND c.contype = 'p' AND array_length(c.conkey, 1) = 1) THEN
|
||
ALTER TABLE bb_launch_meta DROP CONSTRAINT bb_launch_meta_pkey;
|
||
ALTER TABLE bb_launch_meta ADD PRIMARY KEY (chain_id, token);
|
||
END IF;
|
||
END $$;
|
||
CREATE UNIQUE INDEX IF NOT EXISTS bb_launches_chain_pool_idx ON bb_launches (chain_id, pool_id);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS bb_launches_chain_tx_idx ON bb_launches (chain_id, tx_hash, log_index);
|
||
CREATE INDEX IF NOT EXISTS bb_launches_chain_time_idx ON bb_launches (chain_id, block_number DESC, log_index DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_launch_swaps_chain_token_idx ON bb_launch_swaps (chain_id, token, block_number DESC, log_index DESC);
|
||
|
||
-- one indexer cursor per chain (replaces the single-row bb_launch_sync_state)
|
||
CREATE TABLE IF NOT EXISTS bb_launch_sync_cursor (
|
||
chain_id integer PRIMARY KEY,
|
||
cursor_block bigint NOT NULL DEFAULT 0,
|
||
head_block bigint,
|
||
last_run_at timestamptz,
|
||
last_error text
|
||
);
|
||
INSERT INTO bb_launch_sync_cursor (chain_id, cursor_block, head_block, last_run_at, last_error)
|
||
SELECT 8453, cursor_block, head_block, last_run_at, last_error FROM bb_launch_sync_state WHERE id = 1
|
||
ON CONFLICT (chain_id) DO NOTHING;
|
||
INSERT INTO bb_launch_sync_cursor (chain_id) VALUES (4663) ON CONFLICT DO NOTHING;
|
||
|
||
-- ── signed metadata edits ────────────────────────────────────────────────────
|
||
-- Single-use nonces for the /me "edit token details" flow (see src/lib/launchpad/editAuth.ts).
|
||
CREATE TABLE IF NOT EXISTS bb_edit_nonces (
|
||
nonce text PRIMARY KEY, -- 32 hex chars
|
||
chain_id integer NOT NULL,
|
||
token text NOT NULL,
|
||
wallet text NOT NULL,
|
||
expires_at timestamptz NOT NULL,
|
||
used_at timestamptz
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_edit_nonces_expires_idx ON bb_edit_nonces (expires_at);
|
||
ALTER TABLE bb_launch_meta ADD COLUMN IF NOT EXISTS updated_at timestamptz;
|
||
|
||
-- ── posts (comments on token pages + the human feed) ─────────────────────────
|
||
-- Every row was written from a wallet-signed message (see src/lib/launchpad/posts.ts).
|
||
CREATE TABLE IF NOT EXISTS bb_posts (
|
||
id bigserial PRIMARY KEY,
|
||
chain_id integer NOT NULL,
|
||
token text NOT NULL,
|
||
wallet text NOT NULL,
|
||
parent_id bigint REFERENCES bb_posts(id) ON DELETE CASCADE,
|
||
body text NOT NULL,
|
||
tag text, -- creator | whale | holder | null (at post time)
|
||
signature text NOT NULL, -- audit trail
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
hidden boolean NOT NULL DEFAULT false,
|
||
hidden_by text, -- 'auto' | admin wallet
|
||
reports integer NOT NULL DEFAULT 0
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_posts_token_idx ON bb_posts (chain_id, token, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_posts_time_idx ON bb_posts (created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_posts_wallet_idx ON bb_posts (wallet, created_at DESC);
|
||
CREATE TABLE IF NOT EXISTS bb_post_reports (
|
||
post_id bigint NOT NULL REFERENCES bb_posts(id) ON DELETE CASCADE,
|
||
wallet text NOT NULL,
|
||
reason text NOT NULL,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
PRIMARY KEY (post_id, wallet)
|
||
);
|
||
CREATE TABLE IF NOT EXISTS bb_token_settings (
|
||
chain_id integer NOT NULL,
|
||
token text NOT NULL,
|
||
comments_muted boolean NOT NULL DEFAULT false,
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
PRIMARY KEY (chain_id, token)
|
||
);
|
||
-- single-use nonces for post / report / mod signatures (client-generated, server-consumed)
|
||
CREATE TABLE IF NOT EXISTS bb_post_nonces (
|
||
nonce text PRIMARY KEY,
|
||
wallet text NOT NULL,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
-- ── holders (token Transfer events → balances; trust panel) ─────────────────
|
||
-- Every Transfer of a launched token, deduped by (chain, tx, log) so re-scans and backfills are idempotent.
|
||
CREATE TABLE IF NOT EXISTS bb_token_transfers (
|
||
chain_id integer NOT NULL,
|
||
tx_hash text NOT NULL,
|
||
log_index integer NOT NULL,
|
||
token text NOT NULL,
|
||
from_addr text NOT NULL,
|
||
to_addr text NOT NULL,
|
||
value numeric(78,0) NOT NULL,
|
||
block_number bigint NOT NULL,
|
||
block_time timestamptz,
|
||
PRIMARY KEY (chain_id, tx_hash, log_index)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_token_transfers_token_idx ON bb_token_transfers (chain_id, token, block_number DESC);
|
||
-- Running balance per wallet per token (the pool / locker / zero / dead addresses are stored too and tagged in queries).
|
||
CREATE TABLE IF NOT EXISTS bb_token_holders (
|
||
chain_id integer NOT NULL,
|
||
token text NOT NULL,
|
||
holder text NOT NULL,
|
||
balance numeric(78,0) NOT NULL DEFAULT 0,
|
||
first_block bigint NOT NULL,
|
||
last_block bigint NOT NULL,
|
||
PRIMARY KEY (chain_id, token, holder)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_token_holders_top_idx ON bb_token_holders (chain_id, token, balance DESC);
|
||
-- Per-launch counter (wallets with balance > 0, excluding the pool / zero / dead) + backfill marker
|
||
-- (NULL = history not yet scanned; the loop backfills from the launch block, then keeps up per chunk).
|
||
ALTER TABLE bb_launches ADD COLUMN IF NOT EXISTS holders integer NOT NULL DEFAULT 0;
|
||
ALTER TABLE bb_launches ADD COLUMN IF NOT EXISTS holders_synced_block bigint;
|
||
CREATE INDEX IF NOT EXISTS bb_launches_holders_idx ON bb_launches (holders DESC);
|
||
|
||
-- feed: newest swaps across all tokens without scanning the table
|
||
CREATE INDEX IF NOT EXISTS bb_launch_swaps_bt_idx ON bb_launch_swaps (block_time DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_launches_bt_idx ON bb_launches (block_time DESC);
|
||
|
||
-- ── metadata key (stable URI across the CREATE2 salt search) ────────────────
|
||
ALTER TABLE bb_launch_meta ADD COLUMN IF NOT EXISTS meta_key text;
|
||
CREATE INDEX IF NOT EXISTS bb_launch_meta_key_idx ON bb_launch_meta (launcher, meta_key);
|
||
|
||
-- ── chain_id has no default (2026-09-14) ───────────────────────────────────
|
||
-- The multi-chain ALTERs above backfilled pre-existing rows as Base (8453); from here every insert
|
||
-- must name its chain, so a row written without one fails loudly instead of being filed under Base.
|
||
ALTER TABLE bb_launches ALTER COLUMN chain_id DROP DEFAULT;
|
||
ALTER TABLE bb_launch_swaps ALTER COLUMN chain_id DROP DEFAULT;
|
||
ALTER TABLE bb_launch_fee_events ALTER COLUMN chain_id DROP DEFAULT;
|
||
ALTER TABLE bb_launch_meta ALTER COLUMN chain_id DROP DEFAULT;
|
||
|
||
-- ── unlisted quote tokens (2026-09-25) ─────────────────────────────────────
|
||
-- The factory takes any ERC-20 as the quote. For a quote no list knows, the indexer reads the token's own
|
||
-- symbol / name / decimals once and keeps them here (lib/launchpad/unlisted-quote.ts decides what is shown).
|
||
-- A NULL field is one the contract did not answer; checked_at spaces out the retries. Addresses LOWERCASE hex.
|
||
CREATE TABLE IF NOT EXISTS bb_quote_tokens (
|
||
chain_id integer NOT NULL,
|
||
address text NOT NULL,
|
||
symbol text,
|
||
name text,
|
||
decimals integer,
|
||
checked_at timestamptz NOT NULL DEFAULT now(),
|
||
PRIMARY KEY (chain_id, address)
|
||
);
|
||
|
||
-- ── token banners (2026-10-05) ─────────────────────────────────────────────
|
||
-- An optional wide image the creator uploads beside the logo. Same rules as image_url (https only; uploads are
|
||
-- re-encoded server-side, here to a 1500×500 WebP). NULL means no banner: the market cards draw one from the logo.
|
||
ALTER TABLE bb_launch_meta ADD COLUMN IF NOT EXISTS banner_url text;
|
||
|
||
-- ── profiles (2026-10-07) ──────────────────────────────────────────────────
|
||
-- Optional public profile per wallet: a unique username shown wherever the wallet appears (trades, holders, posts,
|
||
-- "launched by"). Every write is a wallet signature (src/lib/profiles). The X tick comes only from a public post
|
||
-- that carries a one-time code bound to this wallet and the claimed handle (lib/profiles/xpost.ts); the claimed
|
||
-- handle is never shown until it is verified. No email, no IP, no off-site identity beyond the public X account.
|
||
CREATE TABLE IF NOT EXISTS bb_profiles (
|
||
wallet text PRIMARY KEY, -- lowercase hex
|
||
username text NOT NULL, -- lowercase [a-z0-9_]{3,20}
|
||
display_name text NOT NULL,
|
||
bio text,
|
||
avatar_key text, -- t/<hex>.webp in our image store, served same-origin
|
||
x_handle text, -- claimed handle (lowercase); public only once verified
|
||
x_user_id text, -- X account id ('h:<handle>' when only the handle was readable)
|
||
x_post_id text,
|
||
x_verified_at timestamptz,
|
||
x_account_created timestamptz,
|
||
x_followers integer,
|
||
x_status text NOT NULL DEFAULT 'none' CHECK (x_status IN ('none','verified','post_missing','pending_review')),
|
||
x_checked_at timestamptz,
|
||
hidden boolean NOT NULL DEFAULT false, -- admin: the wallet shows as a plain address again
|
||
points_flag text, -- admin: 'excluded' keeps the wallet off any points board
|
||
points_flag_reason text,
|
||
username_changed_at timestamptz,
|
||
deleted_at timestamptz, -- deleted by its owner: the row stays (flags, created_at and the rename clock survive a re-create)
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS bb_profiles_username_uq ON bb_profiles (username);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS bb_profiles_x_user_uq ON bb_profiles (x_user_id) WHERE x_user_id IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS bb_profiles_x_review_idx ON bb_profiles (x_status, updated_at DESC) WHERE x_status <> 'none';
|
||
-- A released username (rename or delete) stays reserved for its previous wallet for 30 days.
|
||
CREATE TABLE IF NOT EXISTS bb_username_holds (
|
||
username text PRIMARY KEY,
|
||
wallet text NOT NULL,
|
||
released_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
-- single-use nonces for profile / profile-moderation signatures (client-generated, server-consumed)
|
||
CREATE TABLE IF NOT EXISTS bb_profile_nonces (
|
||
nonce text PRIMARY KEY,
|
||
wallet text NOT NULL,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
-- One-time X verification codes: bound to the wallet AND the handle the wallet signed for, so a copied code is
|
||
-- useless from any other account. The code goes into a public post, so verifying also needs a private key that was
|
||
-- returned only to the signer (kept here as a hash). review: NULL until a post is submitted while every lookup is down.
|
||
CREATE TABLE IF NOT EXISTS bb_x_codes (
|
||
code text PRIMARY KEY, -- OL-XXXXXXXX
|
||
wallet text NOT NULL,
|
||
x_handle text NOT NULL, -- lowercase
|
||
issued_at timestamptz NOT NULL DEFAULT now(),
|
||
expires_at timestamptz NOT NULL,
|
||
used_at timestamptz,
|
||
post_id text,
|
||
submitted_at timestamptz,
|
||
review text CHECK (review IN ('pending','approved','rejected')),
|
||
secret_hash text -- sha256 of the private verify key only the signer was given
|
||
);
|
||
ALTER TABLE bb_x_codes ADD COLUMN IF NOT EXISTS secret_hash text;
|
||
CREATE INDEX IF NOT EXISTS bb_x_codes_wallet_idx ON bb_x_codes (wallet, issued_at DESC);
|
||
|
||
-- ── swap attribution (2026-10-07) ───────────────────────────────────────────
|
||
-- trader is tx.from, except with proof another account authorized the call (lib/launchpad/attribution.ts): an ERC-4337
|
||
-- EntryPoint transaction credits the sender of the user operation whose execution contains the swap ('userop').
|
||
-- tx_from keeps the sender; trader_via NULL = not checked yet, 'receipt_pending' = an EntryPoint call whose receipt is not
|
||
-- read yet (retried), 'unread' = the evidence could not be read (sender kept). Both open states are settled from the chain.
|
||
ALTER TABLE bb_launch_swaps ADD COLUMN IF NOT EXISTS trader_via text;
|
||
ALTER TABLE bb_launch_swaps ADD COLUMN IF NOT EXISTS tx_from text;
|
||
-- its indexes (the unchecked set, receipt-pending calls, one wallet's trades) are in db/concurrent-indexes.sql: built
|
||
-- concurrently after this transaction, so no query on swaps waits for them
|
||
|
||
-- ── points, Season 1 (2026-10-07) ──────────────────────────────────────────
|
||
-- Reputation points for launching and trading, recomputed from scratch every hour from the chain index
|
||
-- (lib/points/score.ts). No cash value, not a token. An admin starts a season (a shadow run only admins see, to tune the
|
||
-- rules on real data); publishing starts the season's clock for everyone; an ended season keeps its final standings.
|
||
CREATE TABLE IF NOT EXISTS bb_seasons (
|
||
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
slug text NOT NULL, -- s1, s2…
|
||
name text NOT NULL,
|
||
starts_at timestamptz NOT NULL,
|
||
ends_at timestamptz NOT NULL,
|
||
public boolean NOT NULL DEFAULT false,
|
||
computed_at timestamptz, -- last successful compute (also with zero rows)
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS bb_seasons_slug_uq ON bb_seasons (slug);
|
||
-- One row per wallet with points in a season (eligible or not: an unverified wallet sees what it would unlock).
|
||
CREATE TABLE IF NOT EXISTS bb_points (
|
||
season_id integer NOT NULL,
|
||
wallet text NOT NULL,
|
||
creator integer NOT NULL DEFAULT 0,
|
||
scout integer NOT NULL DEFAULT 0,
|
||
total integer NOT NULL DEFAULT 0,
|
||
eligible boolean NOT NULL DEFAULT false,
|
||
rank_creator integer, -- among eligible wallets only
|
||
rank_scout integer,
|
||
breakdown jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
computed_at timestamptz NOT NULL DEFAULT now(),
|
||
PRIMARY KEY (season_id, wallet)
|
||
);
|
||
-- added after the table was first written (a database built from the first draft lacks them)
|
||
ALTER TABLE bb_seasons ADD COLUMN IF NOT EXISTS computed_at timestamptz;
|
||
ALTER TABLE bb_seasons ADD COLUMN IF NOT EXISTS computed_until timestamptz; -- the data cut-off of the last compute
|
||
ALTER TABLE bb_seasons ADD COLUMN IF NOT EXISTS published_at timestamptz; -- first publish: the season's clock starts here
|
||
CREATE INDEX IF NOT EXISTS bb_points_creator_idx ON bb_points (season_id, rank_creator) WHERE rank_creator IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS bb_points_scout_idx ON bb_points (season_id, rank_scout) WHERE rank_scout IS NOT NULL;
|
||
|
||
-- ── boosts (TWIG sent to 0x…dEaD for a place in the Boosted lane; lib/launchpad/boost.ts) ──────────────────────────
|
||
-- Raw facts from TWIG Transfer logs to 0x…dEaD on Base whose amount names a launch in its last 12 digits: the tag is
|
||
-- that launch's bb_launches.token_id (its position id). The launch is joined at read time, so a boost indexed before
|
||
-- its launch still counts once the launch is in. Never read by the Live sort, the Trending board or ranking.ts.
|
||
CREATE TABLE IF NOT EXISTS bb_boosts (
|
||
chain_id integer NOT NULL,
|
||
tx_hash text NOT NULL,
|
||
log_index integer NOT NULL,
|
||
booster text NOT NULL, -- the Transfer's sender (a smart wallet itself, never its bundler)
|
||
amount numeric(78,0) NOT NULL, -- raw TWIG, the tag included
|
||
tag bigint NOT NULL, -- amount mod 10^12
|
||
block_number bigint NOT NULL,
|
||
block_time timestamptz NOT NULL,
|
||
PRIMARY KEY (chain_id, tx_hash, log_index)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_boosts_time_idx ON bb_boosts (chain_id, block_time DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_boosts_tag_idx ON bb_boosts (chain_id, tag);
|
||
ALTER TABLE bb_token_settings ADD COLUMN IF NOT EXISTS boost_hidden boolean NOT NULL DEFAULT false; -- admin: kept out of the Boosted lane
|
||
|
||
-- ── Boosted lane views + clicks (2026-10-16): counted only while the token is in the lane ──
|
||
-- Shown as each lane card's click count and on /admin; never read by the ranking (boost-ui.test.ts). visitor =
|
||
-- sha256(address hash | token), cut short. The address hash (the client address, an IPv6 one by its /64, with the
|
||
-- daily presence salt, no user agent) rotates every UTC day and cannot be reversed, and mixing in the token means one
|
||
-- address's clicks on two tokens cannot be linked. One row per visitor, token and 30-minute slot: one address counts
|
||
-- once per card every 30 minutes, whatever user agent it sends.
|
||
CREATE TABLE IF NOT EXISTS bb_boost_clicks (
|
||
chain_id integer NOT NULL,
|
||
token text NOT NULL,
|
||
visitor text NOT NULL,
|
||
slot timestamptz NOT NULL, -- the 30-minute slot the click fell in
|
||
place integer NOT NULL CHECK (place >= 1), -- the token's place in the lane then, from the server's lane
|
||
source text NOT NULL CHECK (source IN ('home', 'base')),
|
||
clicked_at timestamptz NOT NULL DEFAULT now(),
|
||
PRIMARY KEY (chain_id, token, visitor, slot)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_boost_clicks_time_idx ON bb_boost_clicks (chain_id, clicked_at DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_boost_clicks_token_time_idx ON bb_boost_clicks (chain_id, token, clicked_at DESC); -- each card's 24 h count
|
||
-- One count per token and hour: each page load that brings the lane on screen adds 1 to every token in it.
|
||
CREATE TABLE IF NOT EXISTS bb_boost_views (
|
||
chain_id integer NOT NULL,
|
||
token text NOT NULL,
|
||
hour_at timestamptz NOT NULL,
|
||
views integer NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (chain_id, token, hour_at)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_boost_views_time_idx ON bb_boost_views (chain_id, hour_at DESC);
|
||
|
||
-- ── community pairs (2026-10-16; lib/launchpad/pairs.ts, reports/Community pairs plan.md) ──────────────────────────
|
||
-- An app-level list of ERC-20s that launches may be paired with and that the site prices and badges. The contracts are
|
||
-- untouched and still accept any ERC-20: a token missing here simply shows as an unlisted pair. A listing names the
|
||
-- pool its USD price is read from (`price_kind`: 'launch' = an openlaunch pool indexed here, 'univ3' = a Uniswap v3 /
|
||
-- Aerodrome Slipstream pool with an on-chain oracle, 'univ4' = a hookless Uniswap v4 pool, averaged from our own
|
||
-- samples) and the core quote on its other side. Status: listed → suspended (by the hourly monitor after 24 h of
|
||
-- failing checks, at once when the contract's code changes, or by an admin) → delisted (14 days suspended, or an admin).
|
||
CREATE TABLE IF NOT EXISTS bb_pairs (
|
||
chain_id integer NOT NULL,
|
||
address text NOT NULL, -- lowercase
|
||
symbol text NOT NULL, -- as shown (cleaned); unique among listed pairs per chain
|
||
name text NOT NULL,
|
||
decimals integer NOT NULL CHECK (decimals BETWEEN 6 AND 18),
|
||
logo_url text, -- our own image host, or a path on this site
|
||
website text,
|
||
x_handle text,
|
||
description text,
|
||
status text NOT NULL DEFAULT 'listed' CHECK (status IN ('listed', 'suspended', 'delisted')),
|
||
status_by text NOT NULL DEFAULT 'admin' CHECK (status_by IN ('admin', 'monitor')),
|
||
status_reason text,
|
||
status_at timestamptz NOT NULL DEFAULT now(),
|
||
price_kind text NOT NULL CHECK (price_kind IN ('launch', 'univ3', 'univ4')),
|
||
price_pool text NOT NULL, -- launch: the launched token; univ3: the pool; univ4: the pool id
|
||
price_quote text NOT NULL, -- the core quote on the pool's other side (lowercase)
|
||
price_hook text, -- univ4: the pool's hook as read from its key, when it has one (named by the admin)
|
||
code_hash text, -- keccak256 of the runtime code when listed; a change suspends
|
||
failing_since timestamptz, -- first failing monitor run since the last healthy one
|
||
checked_at timestamptz,
|
||
listed_at timestamptz NOT NULL DEFAULT now(),
|
||
PRIMARY KEY (chain_id, address)
|
||
);
|
||
-- One listed pair per symbol per chain (the badge is the symbol): a second one is refused, whatever path writes it.
|
||
CREATE UNIQUE INDEX IF NOT EXISTS bb_pairs_listed_symbol_idx ON bb_pairs (chain_id, upper(symbol)) WHERE status = 'listed';
|
||
-- The public record of every status change, shown on the pair page.
|
||
CREATE TABLE IF NOT EXISTS bb_pair_events (
|
||
id bigserial PRIMARY KEY,
|
||
chain_id integer NOT NULL,
|
||
address text NOT NULL,
|
||
status text NOT NULL,
|
||
reason text,
|
||
actor text NOT NULL, -- an admin wallet, or 'monitor'
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_pair_events_pair_idx ON bb_pair_events (chain_id, address, created_at DESC);
|
||
-- Minute samples of a v4 price pool (quote units per pair token), for its 30-minute average: v4 keeps no oracle.
|
||
CREATE TABLE IF NOT EXISTS bb_pair_prices (
|
||
chain_id integer NOT NULL,
|
||
address text NOT NULL,
|
||
t timestamptz NOT NULL, -- truncated to the minute: two machines write one row
|
||
price double precision NOT NULL,
|
||
PRIMARY KEY (chain_id, address, t)
|
||
);
|
||
-- MUSEWORLD was an "official" quote in code until this list existed; it is the first community pair, under the same rules.
|
||
INSERT INTO bb_pairs (chain_id, address, symbol, name, decimals, logo_url, website, x_handle, description, status, price_kind, price_pool, price_quote)
|
||
VALUES (8453, '0x882c8e35504d58979b1280fe76d6553f717029f8', 'MUSEWORLD', 'Museworld', 18, '/museworld-mark.png', 'https://museworld.lol', 'museworldhq',
|
||
'Museworld''s token. Museworld''s AI agents launch their tokens paired with it.', 'listed', 'launch',
|
||
'0x882c8e35504d58979b1280fe76d6553f717029f8', '0x5f980dcfc4c0fa3911554cf5ab288ed0eb13dba3')
|
||
ON CONFLICT (chain_id, address) DO NOTHING;
|
||
INSERT INTO bb_pair_events (chain_id, address, status, reason, actor)
|
||
SELECT 8453, '0x882c8e35504d58979b1280fe76d6553f717029f8', 'listed', 'Moved from an official quote to the community pairs list.', 'migration'
|
||
WHERE NOT EXISTS (SELECT 1 FROM bb_pair_events WHERE chain_id = 8453 AND address = '0x882c8e35504d58979b1280fe76d6553f717029f8');
|
||
|
||
-- ── community pair applications (2026-10-16; lib/launchpad/pairApply.ts) ─────────────────────────────────────────────
|
||
-- A community applies for its token after the free pre-check passes; the fee ($200 of TWIG, PAIR_LISTING_USD) is sent to
|
||
-- 0x…dEaD naming the application in its last digits (Boost tag kind 3) and pays for up to 3 reviews within 60 days.
|
||
CREATE TABLE IF NOT EXISTS bb_pair_applications (
|
||
id bigserial PRIMARY KEY,
|
||
chain_id integer NOT NULL,
|
||
address text NOT NULL, -- the token (lowercase)
|
||
applicant text NOT NULL, -- the wallet that signed (a verified X profile)
|
||
code text NOT NULL, -- for the proof post, e.g. OL-12-K7QX
|
||
symbol text NOT NULL,
|
||
name text NOT NULL,
|
||
decimals integer NOT NULL,
|
||
price_kind text NOT NULL,
|
||
price_pool text NOT NULL,
|
||
price_quote text NOT NULL,
|
||
price_hook text,
|
||
logo_url text,
|
||
website text,
|
||
x_handle text,
|
||
description text,
|
||
proof_url text,
|
||
precheck jsonb NOT NULL, -- the pre-check as it stood when the application was made
|
||
status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'paid', 'changes', 'approved', 'rejected', 'expired')),
|
||
fee_usd double precision NOT NULL,
|
||
paid_usd double precision NOT NULL DEFAULT 0,
|
||
paid_at timestamptz,
|
||
quote_twig numeric(78,0), -- the locked quote: whole TWIG, its USD price, until when
|
||
quote_price_usd double precision,
|
||
quote_at timestamptz, -- when the lock started: a burn sent before it is not at its price
|
||
quote_until timestamptz,
|
||
reviews_used integer NOT NULL DEFAULT 0,
|
||
reason text, -- the last decision's reason, shown to the applicant
|
||
decided_by text,
|
||
decided_at timestamptz,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_pair_applications_status_idx ON bb_pair_applications (status, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS bb_pair_applications_applicant_idx ON bb_pair_applications (applicant);
|
||
-- One application in progress per token.
|
||
CREATE UNIQUE INDEX IF NOT EXISTS bb_pair_applications_open_idx ON bb_pair_applications (chain_id, address) WHERE status IN ('open', 'paid', 'changes');
|
||
-- TWIG sent to 0x…dEaD for an application (raw facts from the Transfer log), each valued in USD when indexed.
|
||
CREATE TABLE IF NOT EXISTS bb_pair_burns (
|
||
chain_id integer NOT NULL,
|
||
tx_hash text NOT NULL,
|
||
log_index integer NOT NULL,
|
||
application_id bigint NOT NULL,
|
||
sender text NOT NULL,
|
||
twig numeric(78,0) NOT NULL, -- whole TWIG
|
||
usd double precision, -- null until a price was there to value it
|
||
priced_by text CHECK (priced_by IN ('quote', 'live')),
|
||
block_number bigint NOT NULL,
|
||
block_time timestamptz NOT NULL,
|
||
PRIMARY KEY (chain_id, tx_hash, log_index)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_pair_burns_app_idx ON bb_pair_burns (application_id);
|
||
-- The end of the latest fee quote, kept when a landed burn spends the quote (that clears quote_until): another transfer
|
||
-- sent under it may still be indexing, so a fee waiver waits for it as well.
|
||
ALTER TABLE bb_pair_applications ADD COLUMN IF NOT EXISTS last_quote_until timestamptz;
|
||
-- Backfill: a live quote's own end. Where a burn already spent the quote (that cleared quote_until) its end is unknown,
|
||
-- but every such quote began before this runs, so it ends by now + 30 minutes: an application a waiver could still
|
||
-- apply to waits that long. Later quotes record last_quote_until themselves.
|
||
UPDATE bb_pair_applications SET last_quote_until = quote_until WHERE last_quote_until IS NULL AND quote_until IS NOT NULL;
|
||
UPDATE bb_pair_applications a SET last_quote_until = now() + interval '30 minutes'
|
||
WHERE a.last_quote_until IS NULL AND a.status IN ('open', 'changes') AND EXISTS (SELECT 1 FROM bb_pair_burns b WHERE b.application_id = a.id);
|
||
|
||
-- ── admin sign-in (lib/admin) ───────────────────────────────────────────────
|
||
-- Server-issued nonces for Sign-In with Ethereum messages: 5 minutes, single use.
|
||
CREATE TABLE IF NOT EXISTS bb_admin_nonces (
|
||
nonce text PRIMARY KEY,
|
||
expires_at timestamptz NOT NULL,
|
||
used_at timestamptz,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
-- Admin sessions: 8 hours from sign-in. Only the SHA-256 of the cookie's token is kept, never the token.
|
||
CREATE TABLE IF NOT EXISTS bb_admin_sessions (
|
||
id bigserial PRIMARY KEY,
|
||
token_hash text NOT NULL UNIQUE,
|
||
wallet text NOT NULL,
|
||
device text NOT NULL DEFAULT '', -- "Chrome · macOS": no user agent, no IP
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
expires_at timestamptz NOT NULL,
|
||
last_seen_at timestamptz NOT NULL DEFAULT now(),
|
||
revoked_at timestamptz
|
||
);
|
||
CREATE INDEX IF NOT EXISTS bb_admin_sessions_wallet_idx ON bb_admin_sessions (wallet, created_at DESC);
|
||
|
||
-- ── trade-time USD (2026-11-10, lib/launchpad/usd-history.ts) ───────────────
|
||
-- Every trade's and fee's USD value at the time it happened, instead of its quote volume at today's price. One USD price
|
||
-- per quote per UTC hour: the live price the indexer saw during that hour (source 'live', updated until the hour ends,
|
||
-- never replaced afterwards), or a historical close filled in by scripts/usd-backfill.ts ('coinbase', 'geckoterminal',
|
||
-- 'fixed'). `decimals` is the quote's, so a raw amount converts without a second lookup.
|
||
CREATE TABLE IF NOT EXISTS bb_quote_usd_hourly (
|
||
chain_id integer NOT NULL,
|
||
quote text NOT NULL, -- lowercase; 0x000… = the chain's native asset
|
||
hour timestamptz NOT NULL, -- start of the UTC hour
|
||
usd double precision NOT NULL CHECK (usd > 0), -- per whole quote unit
|
||
decimals integer NOT NULL CHECK (decimals BETWEEN 0 AND 36),
|
||
source text NOT NULL,
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
PRIMARY KEY (chain_id, quote, hour)
|
||
);
|
||
-- the value at the trade's own hour, fixed once written; NULL = no price for that hour yet (the backfill fills it)
|
||
ALTER TABLE bb_launch_swaps ADD COLUMN IF NOT EXISTS usd double precision;
|
||
-- quote side only, as the USD fee totals always were: 'collected' (quote_amount) and a 'burned' row in the launch's quote
|
||
ALTER TABLE bb_launch_fee_events ADD COLUMN IF NOT EXISTS usd double precision;
|
||
-- each launch's sums of those values, added to in the event's own transaction and recomputed by the backfill
|
||
ALTER TABLE bb_launches ADD COLUMN IF NOT EXISTS volume_usd double precision NOT NULL DEFAULT 0;
|
||
ALTER TABLE bb_launches ADD COLUMN IF NOT EXISTS fees_collected_usd double precision NOT NULL DEFAULT 0;
|
||
ALTER TABLE bb_launches ADD COLUMN IF NOT EXISTS fees_burned_usd double precision NOT NULL DEFAULT 0;
|
||
|
||
-- ── in-app trades and rules versions (2026-11-11, lib/launchpad/appTag.ts, lib/points/score.ts) ───────────────
|
||
-- A swap made in the app: its transaction carries the app's ERC-8021 builder suffix (a wallet's own router call), or
|
||
-- the smart wallet's user operation it is credited to does. NULL = not known (from before this column, or the
|
||
-- transaction's input could not be read); only true counts. A label for a small capped points bonus, never proof.
|
||
ALTER TABLE bb_launch_swaps ADD COLUMN IF NOT EXISTS via_app boolean;
|
||
-- the scoring rules a season runs on (score.rulesFor): a season keeps the version it was started with, so a rule
|
||
-- added later never changes a season already running. Every season before this column scores on version 1
|
||
ALTER TABLE bb_seasons ADD COLUMN IF NOT EXISTS rules_version integer NOT NULL DEFAULT 1;
|