Skip to content

Open-source platform for 3D AI agents. Turn text or a photo into a rigged, animated GLB avatar, give it an LLM brain, memory and a wallet, and embed it anywhere with one web component. Remote MCP server, x402 payments, Three.js, browser-native.

5.9 KB
Raw Blame
-- Migration: trader duels, a points-only skill-prediction game on head-to-head
-- trader matchups (roadmap 917 item 12.6).
-- Apply: psql "$DATABASE_URL" -f api/_lib/migrations/20260930150000_trader_duels.sql
-- Idempotent.
--
-- Free to play. Points have no cash value and cannot be bought, sold,
-- transferred or redeemed: there is no deposit, no escrow, no payout and no
-- token of any kind anywhere in these tables. Every user gets a free daily
-- allowance, stakes it on a call, and wins points plus XP (credited into
-- trading_quest_completions under code 'duel:<market id>') for a correct one.
--
--   duel_markets        one head-to-head over a fixed UTC window, generated by
--                       /api/cron/duels-tick from the rivalry engine, open for
--                       calls until window_start, resolved after window_end from
--                       the realized P&L ledger (agent_sniper_positions plus
--                       agent_strategy_positions) through computeTraderMetrics,
--                       the same math as /trader/:id.
--   duel_picks          one call per user per market (unique), locked at
--                       window_start by the insert itself.
--   duel_wallets        the spendable points balance. The check constraint is
--                       what makes an overdraft impossible, even under a race.
--   duel_points_ledger  every grant, stake, payout and refund, insert-only.
--                       One grant per user per UTC day and one of each kind per
--                       market are enforced by unique indexes, so a replayed
--                       settlement can never pay twice.
--
-- api/_lib/trader-duels.js owns every read and write.

begin;

create table if not exists duel_markets (
    id              uuid primary key default gen_random_uuid(),
    network         text not null default 'mainnet' check (network in ('mainnet','devnet')),
    window_kind     text not null check (window_kind in ('day','week')),
    window_start    timestamptz not null,
    window_end      timestamptz not null,
    agent_a         uuid references agent_identities(id) on delete set null,
    agent_b         uuid references agent_identities(id) on delete set null,
    agent_a_name    text not null,
    agent_b_name    text not null,
    context         jsonb,                                   -- the rivalry that produced it, as it stood at creation
    status          text not null default 'open' check (status in ('open','resolved','void')),
    winner          text check (winner is null or winner in ('a','b')),
    void_reason     text check (void_reason is null or void_reason in ('tie','no_trades','trader_unavailable')),
    result          jsonb,                                   -- each side's closed trades, wins and realized P&L in the window
    resolved_at     timestamptz,
    created_at      timestamptz not null default now(),
    check (window_end > window_start),
    check (agent_a is distinct from agent_b)
);

-- One market per pair per window, whichever side the generator lists first.
create unique index if not exists duel_markets_pair_window
    on duel_markets (network, window_kind, window_start, least(agent_a, agent_b), greatest(agent_a, agent_b));
create index if not exists duel_markets_status_end on duel_markets (status, window_end);
create index if not exists duel_markets_start on duel_markets (network, window_start desc);
create index if not exists duel_markets_agent_a on duel_markets (agent_a, window_start desc);
create index if not exists duel_markets_agent_b on duel_markets (agent_b, window_start desc);

create table if not exists duel_picks (
    id           bigserial primary key,
    market_id    uuid not null references duel_markets(id) on delete cascade,
    user_id      uuid not null references users(id) on delete cascade,
    side         text not null check (side in ('a','b')),
    stake        integer not null check (stake > 0),
    status       text not null default 'open' check (status in ('open','won','lost','refunded')),
    payout       integer not null default 0 check (payout >= 0),
    created_at   timestamptz not null default now(),
    settled_at   timestamptz
);
create unique index if not exists duel_picks_one_per_user on duel_picks (market_id, user_id);
create index if not exists duel_picks_user on duel_picks (user_id, created_at desc);
create index if not exists duel_picks_open on duel_picks (market_id) where status = 'open';

create table if not exists duel_wallets (
    user_id     uuid primary key references users(id) on delete cascade,
    balance     integer not null default 0 check (balance >= 0),
    updated_at  timestamptz not null default now()
);

create table if not exists duel_points_ledger (
    id          bigserial primary key,
    user_id     uuid not null references users(id) on delete cascade,
    kind        text not null check (kind in ('grant','stake','payout','refund')),
    amount      integer not null,
    market_id   uuid references duel_markets(id) on delete set null,
    day         date,                                        -- grant: the UTC day it was granted for
    created_at  timestamptz not null default now(),
    check ((kind = 'grant') = (day is not null))
);
create unique index if not exists duel_points_ledger_grant_day
    on duel_points_ledger (user_id, day)
    where kind = 'grant';
create unique index if not exists duel_points_ledger_market_kind
    on duel_points_ledger (user_id, market_id, kind)
    where market_id is not null;
create index if not exists duel_points_ledger_user on duel_points_ledger (user_id, created_at desc);

comment on table duel_markets is
    'Trader duels: head-to-head skill predictions over a fixed UTC window, resolved from the realized P&L ledger. Points only.';
comment on table duel_wallets is
    'Free duel points. No cash value; cannot be bought, sold, transferred or redeemed.';
comment on table duel_points_ledger is
    'Insert-only history of duel points: daily grants, stakes, payouts and refunds.';

commit;