Repository Public
1import postgres from 'postgres';2import { appendAudit, verifyAuditChain, type AuditRecord } from '@spw/core';3import { config } from './config.ts';45export const sql = postgres(config.databaseUrl, { max: 8, idle_timeout: 30, onnotice: () => {} });67const SCHEMA = `8create table if not exists credentials (9 id text primary key, public_key text not null, counter bigint not null default 0, transports jsonb not null default '[]',10 label text not null default '', device_type text not null default '', backed_up boolean not null default false,11 created_at timestamptz not null default now(), last_used_at timestamptz);12create table if not exists pending_credentials (13 id text primary key, public_key text not null, counter bigint not null, transports jsonb not null, label text not null,14 device_type text not null, backed_up boolean not null, expires_at timestamptz not null);15create table if not exists sessions (16 id_hash text primary key, created_at timestamptz not null default now(), last_seen timestamptz not null default now(),17 expires_at timestamptz not null, ua text not null default '', ip text not null default '', revoked boolean not null default false);18create table if not exists challenges (19 id uuid primary key, kind text not null, challenge text not null, expires_at timestamptz not null);20create table if not exists proposals (21 id uuid primary key, idempotency_key text unique not null, state text not null, version int not null default 0,22 network text not null, asset text not null, symbol text not null, decimals int not null, amount numeric(78,0) not null check (amount > 0),23 from_addr text not null, to_addr text not null, note text not null default '',24 fee_estimate numeric(78,0), max_fee numeric(78,0), warnings jsonb not null default '[]',25 intent jsonb, intent_hash text, raw text, txid text, block_ref text, confirmations int not null default 0, error text,26 created_at timestamptz not null default now(), updated_at timestamptz not null default now(),27 expires_at timestamptz, broadcast_at timestamptz, last_broadcast_at timestamptz, finalized_at timestamptz);28alter table proposals add column if not exists memo text;29alter table proposals add column if not exists aux jsonb;30alter table proposals add column if not exists kind text not null default 'transfer';31alter table proposals add column if not exists swap jsonb;32alter table proposals add column if not exists pre_raw text;33alter table proposals add column if not exists pre_txid text;34alter table proposals add column if not exists pre_state text;35alter table proposals add column if not exists out_amount numeric(78,0);36alter table proposals add column if not exists bridge jsonb;37alter table proposals add column if not exists xswap jsonb;38create unique index if not exists proposals_txid on proposals (network, txid) where txid is not null;39create table if not exists deposits (40 network text not null, txid text not null, idx int not null, asset text not null, amount numeric(78,0) not null,41 from_addr text not null default '', block bigint not null, block_time timestamptz, seen_at timestamptz not null default now(),42 primary key (network, txid, idx, asset));43create table if not exists scan_cursor (network text primary key, cursor bigint not null, finalized bigint not null default 0);44create table if not exists address_book (45 id uuid primary key, network text not null, address text not null, label text not null, created_at timestamptz not null default now(),46 unique (network, address));47create table if not exists swap_quotes (48 id bigserial primary key, at timestamptz not null default now(), search_id uuid not null, provider text not null,49 from_key text not null, to_key text not null, amount_in numeric(78,0) not null, amount_out numeric(78,0), min_out numeric(78,0),50 ms int not null, ok boolean not null, code text);51create index if not exists swap_quotes_at on swap_quotes (at);52create table if not exists agents (53 id uuid primary key, name text not null, mission text not null default '', state text not null default 'CREATED', mode text not null default 'RESEARCH',54 network text not null default 'base', universe jsonb not null, interval text not null default '1h', risk jsonb not null,55 vault_index int unique not null, vault_address text not null, capital_usdc numeric(78,0) not null default 0,56 strategy_version_id uuid, mandate_id uuid, mandate jsonb, last_bar timestamptz, stop_reason text,57 created_at timestamptz not null default now(), updated_at timestamptz not null default now());58-- V5: agents on every execution-enabled network; cash leg and the registry asset chosen for each market symbol59alter table agents add column if not exists cash text not null default 'USDC';60alter table agents add column if not exists assets jsonb not null default '{}';61create table if not exists strategy_versions (62 id uuid primary key, agent_id uuid not null references agents(id), name text not null, version text not null, code_sha256 text not null, path text not null,63 meta jsonb not null default '{}', status text not null default 'draft', report jsonb, notes text not null default '',64 created_at timestamptz not null default now(), approved_at timestamptz, unique (agent_id, code_sha256));65create table if not exists agent_orders (66 id uuid primary key, agent_id uuid not null references agents(id), ts timestamptz not null default now(), mode text not null,67 from_asset text not null, to_asset text not null, from_symbol text not null, to_symbol text not null,68 amount_in numeric(78,0) not null, expected_out numeric(78,0), min_out numeric(78,0), received numeric(78,0),69 state text not null, reason text, txid text, pre_txid text, raw text, pre_raw text, intent jsonb, aux jsonb, bar timestamptz, updated_at timestamptz not null default now());70create index if not exists agent_orders_agent on agent_orders (agent_id, ts desc);71create table if not exists agent_equity (72 agent_id uuid not null references agents(id), ts timestamptz not null, mode text not null, equity_usd double precision not null, positions jsonb not null, primary key (agent_id, ts, mode));73create table if not exists agent_paper (agent_id uuid not null references agents(id), symbol text not null, units double precision not null, primary key (agent_id, symbol));74create table if not exists research_log (75 id bigserial primary key, agent_id uuid references agents(id), ts timestamptz not null default now(), kind text not null, title text not null, body text not null default '', data jsonb);76create table if not exists settings (key text primary key, value jsonb not null);77create table if not exists candles (78 source text not null, symbol text not null, interval text not null, ts timestamptz not null,79 open double precision not null, high double precision not null, low double precision not null, close double precision not null,80 volume double precision not null, primary key (source, symbol, interval, ts));81create index if not exists candles_series on candles (symbol, interval, ts);82create table if not exists market_quality (83 symbol text not null, interval text not null, checked_at timestamptz not null, last_ts timestamptz, fresh boolean not null,84 gaps int not null, max_deviation_bps double precision, status text not null, detail jsonb not null default '{}',85 primary key (symbol, interval));86create table if not exists audit_events (87 seq bigint primary key, ts text not null, type text not null, actor text not null, data jsonb not null, prev text not null, hash text not null);88create or replace function spw_audit_immutable() returns trigger language plpgsql as $$89begin raise exception 'audit_events is append-only'; end $$;90drop trigger if exists audit_no_update on audit_events;91create trigger audit_no_update before update or delete or truncate on audit_events for each statement execute function spw_audit_immutable();92-- derivatives, wave 2 (read-only and paper). Every price, size and amount is numeric (exact) or a decimal string inside jsonb.93create table if not exists deriv_paper_accounts (94 id uuid primary key, venue text not null, network text not null, label text not null, initial_collateral numeric not null,95 state jsonb not null, policy jsonb not null, meta jsonb not null default '{}',96 created_at timestamptz not null default now(), updated_at timestamptz not null default now());97create table if not exists deriv_orders (98 account_id uuid not null references deriv_paper_accounts(id) on delete cascade, client_id text not null, venue_order_id text,99 instrument text not null, side text not null, type text not null, size numeric not null, price numeric, trigger_price numeric,100 reduce_only boolean not null, tif text not null, status text not null, filled numeric not null default 0, avg_fill_price numeric,101 reject_reason text, origin text not null default 'owner', first_result jsonb,102 created_at timestamptz not null, updated_at timestamptz not null, primary key (account_id, client_id));103create index if not exists deriv_orders_recent on deriv_orders (account_id, created_at desc);104create table if not exists deriv_fills (105 account_id uuid not null references deriv_paper_accounts(id) on delete cascade, fill_id text not null, instrument text not null,106 order_id text, client_id text, side text not null, price numeric not null, size numeric not null, fee numeric not null, fee_asset text not null,107 closed_pnl numeric, liquidation boolean not null default false, ts timestamptz not null, primary key (account_id, fill_id));108create index if not exists deriv_fills_recent on deriv_fills (account_id, ts desc);109create table if not exists deriv_funding (110 account_id uuid not null references deriv_paper_accounts(id) on delete cascade, instrument text not null, ts timestamptz not null,111 amount numeric not null, rate numeric not null, size numeric not null, primary key (account_id, instrument, ts));112create table if not exists deriv_risk_events (113 id bigserial primary key, account_id uuid not null references deriv_paper_accounts(id) on delete cascade, ts timestamptz not null,114 kind text not null, code text, message text not null, data jsonb not null default '{}', dedupe text, unique (account_id, dedupe));115create index if not exists deriv_risk_events_recent on deriv_risk_events (account_id, ts desc);116create table if not exists deriv_equity (117 account_id uuid not null references deriv_paper_accounts(id) on delete cascade, ts timestamptz not null,118 equity numeric not null, collateral numeric not null, margin_used numeric not null, unrealized_pnl numeric not null, primary key (account_id, ts));119-- derivatives, controlled live (venue accounts). A venue account starts READ_ONLY; only the owner's passkey (through the signer) changes that.120create table if not exists deriv_accounts (121 id uuid primary key, venue text not null, network text not null, account text not null, agent_address text not null, agent_name text not null default '',122 label text not null default '', mode text not null default 'READ_ONLY', policy jsonb not null, pending jsonb,123 authorization_id uuid, authorization_hash text, venue_authorization jsonb, halt text, divergence text, meta jsonb not null default '{}',124 last_nonce bigint not null default 0, authorized_at timestamptz, revoked_at timestamptz,125 created_at timestamptz not null default now(), updated_at timestamptz not null default now(), unique (venue, network, account));126-- orders, fills, funding, risk events and equity of venue accounts live in the same tables as the paper ones, keyed by the account127-- id (a uuid of either table): the foreign keys to deriv_paper_accounts are dropped, nothing else changes for paper accounts.128alter table deriv_orders drop constraint if exists deriv_orders_account_id_fkey;129alter table deriv_fills drop constraint if exists deriv_fills_account_id_fkey;130alter table deriv_funding drop constraint if exists deriv_funding_account_id_fkey;131alter table deriv_risk_events drop constraint if exists deriv_risk_events_account_id_fkey;132alter table deriv_equity drop constraint if exists deriv_equity_account_id_fkey;133-- live order state machine: proposed → risk_checked → authorized → signed → submitted → open|filled|partially_filled|cancelled|rejected, plus unknown134alter table deriv_orders add column if not exists state text;135alter table deriv_orders add column if not exists final boolean not null default false;136alter table deriv_orders add column if not exists cloid text;137alter table deriv_orders add column if not exists request jsonb;138alter table deriv_orders add column if not exists order_hash text;139alter table deriv_orders add column if not exists nonce bigint;140alter table deriv_orders add column if not exists expires_at timestamptz;141-- json, not jsonb: the venue hashes an action with its keys in order, and jsonb does not keep the order of keys142alter table deriv_orders add column if not exists action json;143alter table deriv_orders add column if not exists sign_request json;144alter table deriv_orders add column if not exists signature jsonb;145alter table deriv_orders add column if not exists venue_answer jsonb;146alter table deriv_orders add column if not exists submitted_at timestamptz;147create index if not exists deriv_orders_live on deriv_orders (account_id) where state is not null and not final;148-- leverage changes and cancels sent to a venue (one row per signed action)149create table if not exists deriv_actions (150 id uuid primary key, account_id uuid not null, kind text not null, state text not null, request jsonb not null, action_hash text,151 nonce bigint not null, expires_at timestamptz not null, action json not null, sign_request json not null, signature jsonb, venue_answer jsonb,152 error text, created_at timestamptz not null default now(), updated_at timestamptz not null default now());153create index if not exists deriv_actions_recent on deriv_actions (account_id, created_at desc);154-- >>> v6 perp agents (paper only): additive. An agent of kind 'perp' owns one derivatives paper account; it has no vault (a155-- negative vault_index keeps the unique column, the signer is never asked for an address) and no mandate.156alter table agents add column if not exists kind text not null default 'spot';157alter table agents add column if not exists deriv_account_id uuid;158alter table agents add column if not exists perp jsonb not null default '{}';159-- the venue's own closed bars and hourly funding settlements (exact decimals), so that a signal does not refetch its lookback160create table if not exists perp_candles (161 venue text not null, symbol text not null, interval text not null, ts timestamptz not null,162 open numeric not null, high numeric not null, low numeric not null, close numeric not null, volume numeric not null,163 primary key (venue, symbol, interval, ts));164create table if not exists perp_funding (165 venue text not null, symbol text not null, ts timestamptz not null, time_ms bigint not null, rate numeric not null, premium numeric,166 primary key (venue, symbol, ts));167-- one decision per agent and closed bar (the signal log): the row exists before any order of that bar is sent168create table if not exists perp_agent_signals (169 agent_id uuid not null references agents(id), bar timestamptz not null, status text not null, reason text not null default '',170 strategy_sha256 text not null, weights jsonb, scaled jsonb, scale text, equity numeric, targets jsonb, orders jsonb,171 created_at timestamptz not null default now(), updated_at timestamptz not null default now(), primary key (agent_id, bar));172-- <<< v6 perp agents173-- >>> v7 perp agents LIVE: the venue account an agent trades under a derivative mandate; one agent per venue account174alter table agents add column if not exists live_account_id uuid;175create unique index if not exists agents_live_account on agents (live_account_id) where live_account_id is not null;176-- <<< v7 perp agents LIVE177-- derivative mandates (MANDATE_LIVE): the canonical mandate the owner approved with his passkey; the signer holds its own copy and enforces it178create table if not exists deriv_mandates (179 id uuid primary key, account_id uuid not null, authorization_id uuid not null, state text not null, mandate jsonb not null, hash text not null, ack jsonb not null,180 prepared_at timestamptz not null, approved_at timestamptz, revoked_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now());181create index if not exists deriv_mandates_account on deriv_mandates (account_id, approved_at desc);182-- an order placed under a mandate names it and the agent that asked for it; every proposal keeps the estimate it was shown with183alter table deriv_orders add column if not exists mandate_id uuid;184alter table deriv_orders add column if not exists agent jsonb;185alter table deriv_orders add column if not exists estimate jsonb;186-- collateral of venue accounts (v6): deposits (a wallet proposal + the credit read from the venue's ledger) and withdrawals (withdraw3 + arrival on the wallet)187create table if not exists deriv_collateral (188 id uuid primary key, account_id uuid not null, kind text not null, state text not null, final boolean not null default false, venue_network text not null,189 amount numeric not null check (amount > 0), fee numeric, chain_network text, proposal_id uuid, txid text, op_hash text, request jsonb not null default '{}',190 action json, signature jsonb, venue_answer jsonb, nonce bigint, balance_before jsonb, balance_after jsonb, ledger jsonb, arrival jsonb, error text, attempts int not null default 0,191 expires_at timestamptz, sent_at timestamptz, venue_at timestamptz, done_at timestamptz, checked_at timestamptz,192 created_at timestamptz not null default now(), updated_at timestamptz not null default now());193create index if not exists deriv_collateral_open on deriv_collateral (account_id) where not final;194create index if not exists deriv_collateral_recent on deriv_collateral (account_id, created_at desc);195-- >>> v8 GMX v2 trading: one row per order action of the wallet on GMX (increase, decrease, cancel). The on-chain transaction is an ordinary proposal (kind 'gmx'); this row follows the ORDER by its key196create table if not exists deriv_gmx_ops (197 id uuid primary key, idempotency_key text unique, kind text not null, state text not null, final boolean not null default false, account text not null,198 market text, is_long boolean, collateral_token text, amount text, size_usd text, acceptable_price text, slippage_bps int, execution_fee text not null default '0', max_fee text not null default '0',199 needs_approval boolean not null default false, order_key text, target_op uuid, proposal_id uuid, op_hash text, txid text, pre_txid text, block_number bigint,200 request jsonb not null default '{}', estimate jsonb, outcome jsonb, error text, attempts int not null default 0,201 expires_at timestamptz, sent_at timestamptz, order_created_at timestamptz, done_at timestamptz, checked_at timestamptz,202 created_at timestamptz not null default now(), updated_at timestamptz not null default now());203create index if not exists deriv_gmx_ops_open on deriv_gmx_ops (created_at) where not final;204create index if not exists deriv_gmx_ops_recent on deriv_gmx_ops (created_at desc);205-- <<< v8 GMX v2 trading206-- >>> v7 any-to-any plans: a plan is a list of legs; each leg is an ordinary proposal created when the previous one has settled207create table if not exists plans (208 id uuid primary key, state text not null, current int not null default 0, legs jsonb not null, meta jsonb not null,209 created_at timestamptz not null default now(), updated_at timestamptz not null default now());210create index if not exists plans_open on plans (state) where state in ('active', 'paused');211-- <<< v7 any-to-any plans212-- v8 hardening: tracked agent-vault withdrawals (agents/withdrawals.ts)213create table if not exists agent_withdrawals (214 id uuid primary key, agent_id uuid not null references agents(id), network text not null, asset text not null, symbol text not null, decimals int not null,215 amount numeric(78,0) not null, from_addr text not null, to_addr text not null, nonce int not null, state text not null, raw text, txid text, error text,216 idempotency_key text, arrival jsonb, created_at timestamptz not null default now(), updated_at timestamptz not null default now(),217 broadcast_at timestamptz, last_broadcast_at timestamptz, confirmed_at timestamptz);218create unique index if not exists agent_withdrawals_one_open on agent_withdrawals (agent_id, network, asset) where state in ('requested', 'signed', 'broadcast');219create unique index if not exists agent_withdrawals_idem on agent_withdrawals (agent_id, idempotency_key) where idempotency_key is not null;220create index if not exists agent_withdrawals_agent on agent_withdrawals (agent_id, created_at desc);221`;222223export async function migrate(): Promise<void> { await sql.unsafe(SCHEMA); }224225let auditLock: Promise<unknown> = Promise.resolve();226/** Append to the tamper-evident hash chain. Never pass secrets or assertion payloads here. */227export function audit(type: string, actor: string, data: Record<string, unknown> = {}): Promise<void> {228 const run = async () => {229 await sql.begin(async (tx) => {230 await tx`select pg_advisory_xact_lock(7707001)`;231 const [last] = await tx<AuditRecord[]>`select seq::int as seq, ts, type, actor, data, prev, hash from audit_events order by seq desc limit 1`;232 const rec = appendAudit(last ?? null, type, actor, data);233 await tx`insert into audit_events (seq, ts, type, actor, data, prev, hash) values (${rec.seq}, ${rec.ts}, ${rec.type}, ${rec.actor}, ${tx.json(rec.data as never)}, ${rec.prev}, ${rec.hash})`;234 });235 };236 const p = auditLock.then(run, run);237 auditLock = p.catch(() => {});238 return p;239}240241export async function auditList(limit: number): Promise<{ events: AuditRecord[]; intact: boolean; brokenAt: number | null; total: number }> {242 const all = await sql<AuditRecord[]>`select seq::int as seq, ts, type, actor, data, prev, hash from audit_events order by seq asc`;243 const brokenAt = verifyAuditChain(all);244 return { events: all.slice(-limit).reverse(), intact: brokenAt === null, brokenAt, total: all.length };245}246247export async function getSetting<T>(key: string, dflt: T): Promise<T> {248 const [r] = await sql<{ value: T }[]>`select value from settings where key = ${key}`;249 return r ? r.value : dflt;250}251export async function setSetting(key: string, value: unknown): Promise<void> {252 await sql`insert into settings (key, value) values (${key}, ${sql.json(value as never)}) on conflict (key) do update set value = excluded.value`;253}254