schema.sql

-- afx402 schema (Supabase Postgres)
-- Notes:
-- - Wallets are identities. No user accounts required.
-- - x402-style flows are recorded as calls + balances.

create table if not exists afx_agents (
  id text primary key,
  owner_wallet text,
  name text not null,
  tagline text not null,
  handler_key text not null,
  price_wei text not null,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists afx_calls (
  id bigserial primary key,
  agent_id text not null references afx_agents(id) on delete cascade,
  payer_wallet text not null,
  amount_wei text not null,
  payment_mode text not null check (payment_mode in ('prepaid','per_call','subscription','flash')),
  tx_hash text,
  created_at timestamptz not null default now()
);

create index if not exists afx_calls_payer_created_idx on afx_calls (payer_wallet, created_at desc);

create table if not exists afx_balances (
  wallet text primary key,
  balance_wei text not null default '0',
  updated_at timestamptz not null default now()
);

create table if not exists afx_policies (
  wallet text primary key,
  daily_cap_wei text not null default '0',
  allow_agent_ids text[] not null default '{}'::text[],
  policy_json jsonb,
  policy_hash text,
  updated_at timestamptz not null default now()
);

create table if not exists afx_nonces (
  wallet text not null,
  nonce text not null,
  issued_at timestamptz not null,
  used boolean not null default false,
  used_at timestamptz,
  primary key (wallet, nonce)
);

create table if not exists afx_faucet (
  id bigserial primary key,
  wallet text not null,
  amount_wei text not null,
  created_at timestamptz not null default now()
);

create table if not exists afx_subscriptions (
  id bigserial primary key,
  wallet text not null,
  agent_id text not null references afx_agents(id) on delete cascade,
  period_seconds integer not null,
  price_per_period_wei text not null,
  credits_per_period integer not null default 0,
  next_renew_at timestamptz not null,
  status text not null default 'active' check (status in ('active','paused','canceled')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (wallet, agent_id)
);

create table if not exists afx_subscription_credits (
  wallet text not null,
  agent_id text not null references afx_agents(id) on delete cascade,
  remaining integer not null default 0,
  updated_at timestamptz not null default now(),
  primary key (wallet, agent_id)
);

create table if not exists afx_renewals (
  id bigserial primary key,
  wallet text not null,
  agent_id text not null references afx_agents(id) on delete cascade,
  amount_wei text not null,
  created_at timestamptz not null default now()
);

-- Flash/optimistic payment promises (EIP-712 signatures verified instantly, settled later)
create table if not exists afx_payment_promises (
  id bigserial primary key,
  payer_wallet text not null,
  agent_id text not null,
  amount_wei text not null,
  nonce text not null,
  expires_at timestamptz not null,
  signature text not null,
  chain_id integer not null,
  verifying_contract text not null,
  status text not null default 'pending' check (status in ('pending','settled','void')),
  settled_at timestamptz,
  created_at timestamptz not null default now(),
  unique (payer_wallet, nonce)
);

create index if not exists afx_payment_promises_status_idx on afx_payment_promises (status, created_at asc);

-- If your DB was created before 'flash' existed, expand the check constraint safely.
do $$
begin
  if exists (
    select 1
    from pg_constraint
    where conname = 'afx_calls_payment_mode_check'
  ) then
    alter table afx_calls drop constraint afx_calls_payment_mode_check;
    alter table afx_calls add constraint afx_calls_payment_mode_check
      check (payment_mode in ('prepaid','per_call','subscription','flash','agent_to_agent'));
  end if;
end $$;

-- Agent wallets for agent-to-agent payments
create table if not exists afx_agent_wallets (
  agent_id text primary key references afx_agents(id) on delete cascade,
  owner_wallet text not null,
  daily_budget_wei text not null default '1000000000000000000',
  spent_today_wei text not null default '0',
  auto_topup_threshold_wei text not null default '100000000000000',
  auto_topup_amount_wei text not null default '500000000000000',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- Agent events for logging agent activities
create table if not exists afx_agent_events (
  id bigserial primary key,
  agent_id text not null references afx_agents(id) on delete cascade,
  event_type text not null check (event_type in ('auto_topup','outbound_call','inbound_call','failover','circuit_open','circuit_close')),
  target_agent_id text,
  amount_wei text,
  from_wallet text,
  metadata jsonb,
  created_at timestamptz not null default now()
);

create index if not exists afx_agent_events_agent_created_idx on afx_agent_events (agent_id, created_at desc);

-- Atomic debit function to prevent race conditions
create or replace function debit_balance_atomic(p_wallet text, p_amount text)
returns jsonb language plpgsql as $$
declare
  v_current text;
  v_new text;
begin
  -- Lock the row and get current balance
  select balance_wei into v_current
  from afx_balances
  where wallet = lower(p_wallet)
  for update;

  if v_current is null then
    return jsonb_build_object('success', false, 'error', 'no_balance');
  end if;

  -- Check if balance is sufficient (compare as numeric)
  if v_current::numeric < p_amount::numeric then
    return jsonb_build_object('success', false, 'error', 'insufficient_prepaid');
  end if;

  -- Debit
  v_new := (v_current::numeric - p_amount::numeric)::text;
  update afx_balances
  set balance_wei = v_new, updated_at = now()
  where wallet = lower(p_wallet);

  return jsonb_build_object('success', true, 'new_balance', v_new);
end;
$$;

-- Circuit breaker state (optional - can use in-memory in simple deployments)
create table if not exists afx_circuit_states (
  agent_id text primary key references afx_agents(id) on delete cascade,
  state text not null default 'closed' check (state in ('closed','open','half-open')),
  failures integer not null default 0,
  successes integer not null default 0,
  last_failure timestamptz,
  last_success timestamptz,
  last_state_change timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- Seed built-in agents if absent
insert into afx_agents (id, owner_wallet, name, tagline, handler_key, price_wei)
values
  -- AI-Powered Agents
  ('research', null, 'Research Agent', 'AI-powered research assistant. Analyzes topics and provides comprehensive summaries.', 'research', '500000000000000'),
  ('summarizer', null, 'Summarizer Agent', 'Condenses long text into key points using AI.', 'summarize', '300000000000000'),
  ('code-reviewer', null, 'Code Reviewer', 'AI code review: finds bugs, security issues, and suggests improvements.', 'code_review', '400000000000000'),
  ('translator', null, 'Translator Agent', 'Translates text between languages using AI.', 'translate', '300000000000000'),
  ('sentiment', null, 'Sentiment Analyzer', 'Analyzes sentiment and emotions in text.', 'sentiment', '250000000000000'),
  ('data-extractor', null, 'Data Extractor', 'Extracts structured data from unstructured text using AI.', 'data_extract', '350000000000000'),
  -- Utility Agents
  ('echo', null, 'Echo Agent', 'Returns your input. Good for testing x402 flows.', 'echo', '100000000000000'),
  ('budget-auditor', null, 'Budget Auditor', 'Explains how your policy would gate spend.', 'budget_audit', '200000000000000'),
  ('json-linter', null, 'JSON Linter', 'Validates and pretty-prints JSON payloads.', 'json_lint', '100000000000000'),
  ('sha256', null, 'SHA-256 Hasher', 'Returns a SHA-256 digest of your input string.', 'sha256', '100000000000000'),
  ('keywords', null, 'Keyword Extractor', 'Extracts a short list of unique keywords from text.', 'keywords', '150000000000000'),
  ('text-cleaner', null, 'Text Cleaner', 'Normalizes whitespace and trims noisy input.', 'text_clean', '100000000000000')
on conflict (id) do nothing;