Data Model

users (Supabase Auth)
  └── exchange_credentials      (encrypted API keys per user)
  └── skills
        └── skill_versions      (immutable history)
              └── deployments
                    ├── agent_state            (1:1)
                    ├── agent_commands         (1:N)
                    ├── agent_logs             (1:N)
                    ├── agent_conversations    (1:N per deployer)
                    │     └── agent_messages   (1:N)
                    └── decision_snapshots     (1:N)

  └── sim_runs
        └── decision_snapshots  (1:N; same table as live, distinguished by sim_run_id)

(Data plane, not user-scoped)
bars
news (+ embeddings)
funding_rates

create table skills (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid references auth.users(id) on delete cascade,
  name            text not null,
  slug            text not null,                  -- url-friendly, unique per user
  description     text,
  latest_version  int not null default 1,
  is_archived     boolean not null default false,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now(),
  unique (user_id, slug)
);

create table skill_versions (
  skill_id        uuid references skills(id) on delete cascade,
  version         int not null,
  -- The full skill artifact (zod-validated on insert)
  payload         jsonb not null,
  -- Derived: hash of payload for content-addressing
  payload_hash    text not null,
  notes           text,                            -- author's change notes
  created_at      timestamptz not null default now(),
  primary key (skill_id, version)
);

create index on skill_versions (payload_hash);

create table deployments (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid references auth.users(id) on delete cascade,
  skill_id        uuid references skills(id),
  skill_version   int not null,
  broker_kind     text not null check (broker_kind in ('paper', 'hyperliquid-mainnet')),
  status          text not null default 'provisioning'
                  check (status in ('provisioning', 'running', 'paused', 'halted', 'stopping', 'stopped', 'error')),
  fly_machine_id  text,                            -- nullable while provisioning
  fly_region      text,
  error_text      text,
  started_at      timestamptz,
  stopped_at      timestamptz,
  halt_reason     text,                            -- e.g. 'daily_loss_limit_exceeded'
  created_at      timestamptz not null default now(),
  foreign key (skill_id, skill_version) references skill_versions(skill_id, version)
);

create index on deployments (user_id, status);
create index on deployments (skill_id, skill_version);

create table agent_state (
  deployment_id     uuid primary key references deployments(id) on delete cascade,
  status            text not null,                 -- mirrors deployments.status
  equity_usd        numeric,
  day_start_equity  numeric,                       -- for daily loss halt math
  positions         jsonb not null default '[]',   -- [{symbol, size, entry, unrealizedPnl, leverage}]
  open_orders       jsonb not null default '[]',
  last_tick_at      timestamptz,
  last_action       jsonb,
  last_reasoning    text,
  updated_at        timestamptz not null default now()
);

create table agent_commands (
  id              uuid primary key default gen_random_uuid(),
  deployment_id   uuid references deployments(id) on delete cascade,
  user_id         uuid references auth.users(id),  -- who issued
  kind            text not null check (kind in ('stop','pause','resume','flatten','kill','snapshot','clear_halt')),
  payload         jsonb,
  requested_at    timestamptz not null default now(),
  acked_at        timestamptz,
  result          jsonb
);

create index on agent_commands (deployment_id, requested_at desc);

-- trigger to NOTIFY the runner on insert
create function notify_agent_command() returns trigger as $$
begin
  perform pg_notify('agent_commands_' || new.deployment_id::text, new.id::text);
  return new;
end;
$$ language plpgsql;

create trigger agent_commands_notify
  after insert on agent_commands
  for each row execute function notify_agent_command();

create table agent_logs (
  id              bigserial primary key,
  deployment_id   uuid references deployments(id) on delete cascade,
  ts              timestamptz not null default now(),
  level           text not null check (level in ('debug','info','warn','error')),
  event           text not null,                   -- 'tick_started','tick_failed','order_filled', ...
  message         text,
  data            jsonb
);

create index on agent_logs (deployment_id, ts desc);
create index on agent_logs (deployment_id, level, ts desc);

create table decision_snapshots (
  id                uuid primary key default gen_random_uuid(),
  deployment_id     uuid references deployments(id) on delete cascade,
  sim_run_id        uuid references sim_runs(id) on delete cascade,
  -- Exactly one of (deployment_id, sim_run_id) must be set
  check ((deployment_id is null) <> (sim_run_id is null)),

  tick_at           timestamptz not null,
  context_json      jsonb not null,                -- bars, news, portfolio, funding sent to model
  steps_json        jsonb not null,                -- model's tool-call sequence
  final_text        text,                          -- model's final reasoning paragraph
  proposed_action   jsonb,                         -- ProposedAction or null
  engine_rule       text,                          -- e.g. 'R3_POSITION_CAP' if rejected
  engine_result     jsonb not null,                -- {kind, orderId?, fill?, rejectDetail?}
  prompt_tokens     int,
  completion_tokens int,
  cost_usd          numeric,
  created_at        timestamptz not null default now()
);

create index on decision_snapshots (deployment_id, tick_at desc);
create index on decision_snapshots (sim_run_id, tick_at);

create table agent_conversations (
  id              uuid primary key default gen_random_uuid(),
  deployment_id   uuid references deployments(id) on delete cascade,
  user_id         uuid references auth.users(id),
  created_at      timestamptz not null default now(),
  last_message_at timestamptz not null default now(),
  unique (deployment_id, user_id)                  -- one chat per deployer per deployment
);

create table agent_messages (
  id               bigserial primary key,
  conversation_id  uuid references agent_conversations(id) on delete cascade,
  role             text not null check (role in ('user','assistant','tool')),
  content          text,
  tool_calls       jsonb,
  tool_call_id     text,
  usage            jsonb,
  created_at       timestamptz not null default now()
);

create index on agent_messages (conversation_id, created_at);

create table sim_runs (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid references auth.users(id) on delete cascade,
  skill_id        uuid references skills(id),
  skill_version   int not null,
  symbol          text not null,
  bars_interval   text not null,
  range_from      timestamptz not null,
  range_to        timestamptz not null,
  status          text not null default 'queued'
                  check (status in ('queued','loading','running','computing_metrics','complete','error','cancelled')),
  progress_pct    int default 0,
  error_text      text,
  metrics_json    jsonb,
  cost_usd        numeric,
  started_at      timestamptz,
  finished_at     timestamptz,
  created_at      timestamptz not null default now(),
  foreign key (skill_id, skill_version) references skill_versions(skill_id, version)
);

create table exchange_credentials (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid references auth.users(id) on delete cascade,
  exchange        text not null check (exchange in ('hyperliquid')),
  network         text not null check (network in ('mainnet')),
  label           text,                            -- user-friendly name
  -- The key material: AES-256-GCM encrypted, key in Supabase Vault
  ciphertext      bytea not null,
  nonce           bytea not null,
  -- Public address for display only
  wallet_address  text,
  created_at      timestamptz not null default now(),
  last_used_at    timestamptz
);

create index on exchange_credentials (user_id, exchange, network);

create table bars (
  symbol          text not null,
  interval        text not null,                   -- '1m'|'5m'|'15m'|'1h'|'4h'|'1d'
  ts              timestamptz not null,
  open            numeric not null,
  high            numeric not null,
  low             numeric not null,
  close           numeric not null,
  volume          numeric not null,
  primary key (symbol, interval, ts)
);

create index on bars (symbol, interval, ts desc);

create table news (
  id              uuid primary key default gen_random_uuid(),
  source          text not null,                   -- 'cryptopanic', 'tiingo', ...
  external_id     text not null,                   -- source-specific id for dedup
  ts              timestamptz not null,
  title           text not null,
  url             text,
  body            text,                            -- full article if available
  symbols         text[] not null default '{}',    -- tagged symbols
  categories      text[],                          -- 'regulation','hack','exchange', ...
  sentiment       numeric,                         -- -1.0 to 1.0 (provider-derived if available)
  raw             jsonb,
  embedding       vector(1536),                    -- pgvector
  created_at      timestamptz not null default now(),
  unique (source, external_id)
);

create index on news (ts desc);
create index on news using gin (symbols);
create index on news using ivfflat (embedding vector_cosine_ops) with (lists = 100);

create table funding_rates (
  symbol          text not null,
  ts              timestamptz not null,
  rate            numeric not null,
  primary key (symbol, ts)
);

create table open_interest (
  symbol          text not null,
  ts              timestamptz not null,
  oi_usd          numeric not null,           -- total notional OI in USD
  long_oi_usd     numeric,                    -- nullable; not provided by Hyperliquid
  short_oi_usd    numeric,                    -- nullable; not provided by Hyperliquid
  primary key (symbol, ts)
);

create index on open_interest (symbol, ts desc);