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.

157 KB
Raw Blame
-- ─────────────────────────────────────────────────────────────────────────────
-- three.ws — Postgres schema (Neon)
-- Idempotent migrations. Apply with:  psql "$DATABASE_URL" -f api/_lib/schema.sql
-- ─────────────────────────────────────────────────────────────────────────────

create extension if not exists "pgcrypto";
create extension if not exists "citext";

-- ── users ───────────────────────────────────────────────────────────────────
create table if not exists users (
    id              uuid primary key default gen_random_uuid(),
    email           citext not null unique,
    password_hash   text,                       -- null = oauth-only or wallet-only account
    display_name    text,
    avatar_url      text,
    plan            text not null default 'free' check (plan in ('free','pro','team','enterprise')),
    email_verified  boolean not null default false,
    wallet_address  text,                       -- lowercased 0x… for wallet login
    created_at      timestamptz not null default now(),
    updated_at      timestamptz not null default now(),
    deleted_at      timestamptz
);

-- Additive migration for deployments that pre-date the wallet_address column.
alter table users add column if not exists wallet_address text;
alter table users add column if not exists is_admin boolean not null default false;
alter table users add column if not exists username text;
alter table users add column if not exists privy_did text;
create unique index if not exists users_wallet_unique on users(wallet_address) where wallet_address is not null;
create unique index if not exists users_username_unique on users(lower(username)) where username is not null;
create unique index if not exists users_privy_did_unique on users(privy_did) where privy_did is not null;
-- Case-insensitive username lookup on the login/signup hot path (auth/[action].js).
create index if not exists users_display_name_lower on users(lower(display_name)) where deleted_at is null;

-- Public social-profile fields. Surfaced on /u/<username> and editable by the
-- owner via PATCH /api/auth/profile. All nullable — a profile with none set
-- still renders cleanly from the user's avatars/agents/coins.
alter table users add column if not exists bio text;
alter table users add column if not exists website text;
alter table users add column if not exists location text;
alter table users add column if not exists banner_url text;

-- Account tier ("mode"): the coarse membership mode shown on a member's card.
-- NULL = the default 'user' tier. Granted modes are assigned by an admin
-- (api/admin/user/[id].js); 'holder' is derived live from on-chain $THREE and is
-- never stored here. See api/_lib/account-tier.js for the resolver + perk curve.
alter table users add column if not exists account_tier text
    check (account_tier is null or account_tier in ('beta', 'pro', 'three-dimensional'));

-- Onboarding guided-tour state (api/_lib/migrations/20260712030000_onboarding_tour_state.sql).
-- seen_at = offered/started at least once (auto-start suppressed after this);
-- completed_at = finished the onboarding track end to end.
alter table users add column if not exists onboarding_tour_seen_at timestamptz;
alter table users add column if not exists onboarding_tour_completed_at timestamptz;

-- Terms of Service clickwrap state (api/_lib/migrations/20260716100000_users_tos_acceptance.sql).
-- Stamped at signup and re-stamped on later sign-ins; the append-only
-- evidentiary record is the audit_log 'tos-accept' row. See api/_lib/legal.js.
alter table users add column if not exists tos_accepted_version int;
alter table users add column if not exists tos_accepted_at timestamptz;

-- Real-funds agreement signatures (api/_lib/migrations/20260917180000_legal_signatures.sql).
-- Append-only; user_id has no FK so the record outlives account deletion.
-- See api/_lib/real-funds-agreement.js.
create table if not exists legal_signatures (
	id              uuid        primary key default gen_random_uuid(),
	user_id         uuid,
	bundle_version  int         not null,
	documents       jsonb       not null,
	signature_name  text        not null,
	attestations    jsonb       not null,
	context         text,
	path            text,
	ip              text,
	user_agent      text,
	created_at      timestamptz not null default now()
);
create index if not exists legal_signatures_user_idx
	on legal_signatures (user_id, bundle_version desc, created_at desc)
	where user_id is not null;

-- ── user_follows — the social graph ──────────────────────────────────────────
-- A directed follow edge: follower_id follows following_id. Composite PK makes
-- a follow idempotent (one edge per pair) and the toggle a single upsert/delete.
-- The check blocks self-follows at the storage layer. Two covering indexes back
-- the hot reads: "who follows X" (follower list + count) and "who X follows"
-- (following list + count, and the feed fan-out join).
create table if not exists user_follows (
    follower_id   uuid not null references users(id) on delete cascade,
    following_id  uuid not null references users(id) on delete cascade,
    created_at    timestamptz not null default now(),
    primary key (follower_id, following_id),
    check (follower_id <> following_id)
);
create index if not exists user_follows_following on user_follows(following_id, created_at desc);
create index if not exists user_follows_follower  on user_follows(follower_id, created_at desc);

-- SAML SSO subject link (set when a user signs in via an enterprise IdP).
alter table users add column if not exists saml_name_id text;
alter table users add column if not exists saml_issuer text;
-- A SAML subject is unique per (IdP issuer, NameID). Partial unique so the same
-- NameID from two different IdPs never collides and non-SSO users (null
-- saml_name_id) are unconstrained.
create unique index if not exists users_saml_subject_unique
    on users(saml_issuer, saml_name_id) where saml_name_id is not null;

-- ── saml_request_ids — SAML SSO InResponseTo replay protection ──────────────
-- The /api/auth/saml/login lambda records each AuthnRequest ID here; the
-- /api/auth/saml/acs lambda (a separate serverless instance) confirms the IdP's
-- response echoes one we issued, then deletes it. Lives in Postgres rather than
-- process memory so it survives across instances. Rows are swept on insert and
-- removed on successful validation; abandoned logins expire by created_at.
create table if not exists saml_request_ids (
    request_id text primary key,
    value      text not null,
    created_at timestamptz not null default now()
);
create index if not exists saml_request_ids_created on saml_request_ids(created_at);

-- ── user_subdomains — tracks `<label>.threews.sol` SNS claims ───────────────
-- Populated by /api/threews/subdomain POST; read by /api/threews/* + a few
-- on-ramp endpoints (u-og, x402/pay-by-name). Without it every /threews/claim
-- availability check 500s with "relation does not exist".
create table if not exists user_subdomains (
    id              uuid primary key default gen_random_uuid(),
    user_id         uuid not null references users(id) on delete cascade,
    label           text not null,
    parent          text not null,
    owner_wallet    text not null,
    url_record      text,
    signature       text,
    created_at      timestamptz not null default now()
);

create unique index if not exists user_subdomains_label_parent
    on user_subdomains(label, parent);
create index if not exists user_subdomains_user
    on user_subdomains(user_id, created_at desc);

-- ── token_metadata — server cache of Solana token info (symbol/name/logo) ──
-- Resolved once via Helius DAS, then served from Postgres on every subsequent
-- portfolio load. Biggest Helius-credit saver in the system.
create table if not exists token_metadata (
    mint           text primary key,
    chain          text not null default 'solana',
    symbol         text,
    name           text,
    logo           text,
    decimals       smallint,
    source         text,
    refreshed_at   timestamptz not null default now(),
    created_at     timestamptz not null default now()
);
create index if not exists token_metadata_chain on token_metadata(chain);
create index if not exists token_metadata_refreshed on token_metadata(refreshed_at);

-- ── avatars (GLBs stored in R2) ─────────────────────────────────────────────
create table if not exists avatars (
    id              uuid primary key default gen_random_uuid(),
    owner_id        uuid not null references users(id) on delete cascade,
    slug            text not null,              -- short, URL-safe handle
    name            text not null,
    description     text,
    storage_key     text not null,              -- R2 object key
    size_bytes      bigint not null,
    content_type    text not null default 'model/gltf-binary',
    source          text not null default 'upload' check (source in ('upload','avaturn','import')),
    source_meta     jsonb not null default '{}'::jsonb,
    thumbnail_key   text,
    visibility      text not null default 'public' check (visibility in ('private','unlisted','public')),
    tags            text[] not null default '{}',
    checksum_sha256 text,
    version         int not null default 1,
    created_at      timestamptz not null default now(),
    updated_at      timestamptz not null default now(),
    deleted_at      timestamptz,
    unique (owner_id, slug)
);

create index if not exists avatars_owner_idx on avatars(owner_id) where deleted_at is null;
create index if not exists avatars_public_idx on avatars(visibility, created_at desc) where visibility = 'public' and deleted_at is null;
create index if not exists avatars_tags_idx on avatars using gin(tags);

-- Additive migrations for avatars columns added after initial deployment.
alter table avatars add column if not exists storage_mode jsonb;
alter table avatars add column if not exists parent_avatar_id uuid references avatars(id) on delete set null;
-- Vision-generated accessibility description of the thumbnail (T4.1). Null = not
-- generated yet; the gallery falls back to the avatar name.
alter table avatars add column if not exists alt_text text;

-- ── mocap_clips (recorded face / pose / hand motion clips) ──────────────────
-- See api/_lib/migrations/2026-05-24-mocap-clips.sql for the full schema +
-- rationale. Mirrored here so a clean schema.sql apply provisions the table.
create table if not exists mocap_clips (
    id              uuid primary key default gen_random_uuid(),
    owner_id        uuid not null references users(id) on delete cascade,
    avatar_id       uuid references avatars(id) on delete set null,
    slug            text not null,
    name            text not null,
    description     text,
    kind            text not null default 'face' check (kind in ('face','pose','hand','composite','vmc')),
    format          text not null default 'three.ws.face-mocap.v1',
    duration_ms     int not null default 0,
    frame_count     int not null default 0,
    frames          jsonb,
    storage_key     text,
    thumbnail_key   text,
    tags            text[] not null default '{}',
    visibility      text not null default 'private' check (visibility in ('private','unlisted','public')),
    price_amount    numeric(30,9),
    price_currency  text,
    play_count      bigint not null default 0,
    created_at      timestamptz not null default now(),
    updated_at      timestamptz not null default now(),
    deleted_at      timestamptz,
    unique (owner_id, slug)
);
create index if not exists mocap_clips_owner_idx on mocap_clips(owner_id, created_at desc) where deleted_at is null;
create index if not exists mocap_clips_public_idx on mocap_clips(visibility, created_at desc) where visibility = 'public' and deleted_at is null;
create index if not exists mocap_clips_kind_idx on mocap_clips(kind, created_at desc) where deleted_at is null;
create index if not exists mocap_clips_tags_idx on mocap_clips using gin(tags);

-- ── animation_clips (keyframe animations authored in the Animation Studio) ──
-- See api/_lib/migrations/2026-05-31-animation-clips.sql for the full schema +
-- rationale. Mirrored here so a clean schema.sql apply provisions the table.
create table if not exists animation_clips (
    id              uuid primary key default gen_random_uuid(),
    owner_id        uuid not null references users(id) on delete cascade,
    avatar_id       uuid references avatars(id) on delete set null,
    slug            text not null,
    name            text not null,
    description     text,
    kind            text not null default 'animation' check (kind in ('animation','loop','sequence')),
    format          text not null default 'three.ws.animation.v1',
    duration_ms     int not null default 0,
    frame_count     int not null default 0,
    fps             int,
    loop            boolean not null default true,
    clip            jsonb,
    storage_key     text,
    editor_doc      jsonb,
    thumbnail_key   text,
    tags            text[] not null default '{}',
    visibility      text not null default 'private' check (visibility in ('private','unlisted','public')),
    price_amount    numeric(30,9),
    price_currency  text,
    artifact_key        text,
    artifact_bytes      bigint,
    artifact_mime       text,
    creator_payto_base  text,
    creator_payto_solana text,
    creator_payto_bsc   text,
    listed          boolean not null default false,
    play_count      bigint not null default 0,
    purchase_count  bigint not null default 0,
    created_at      timestamptz not null default now(),
    updated_at      timestamptz not null default now(),
    deleted_at      timestamptz,
    unique (owner_id, slug)
);
create index if not exists animation_clips_owner_idx on animation_clips(owner_id, created_at desc) where deleted_at is null;
create index if not exists animation_clips_public_idx on animation_clips(visibility, created_at desc) where visibility = 'public' and deleted_at is null;
create index if not exists animation_clips_kind_idx on animation_clips(kind, created_at desc) where deleted_at is null;
create index if not exists animation_clips_listed_idx on animation_clips(listed, created_at desc) where listed = true and deleted_at is null;
create index if not exists animation_clips_tags_idx on animation_clips using gin(tags);

-- ── OAuth 2.1 clients (for MCP & third-party apps) ──────────────────────────
-- Supports RFC 7591 dynamic client registration.
create table if not exists oauth_clients (
    id                      uuid primary key default gen_random_uuid(),
    client_id               text not null unique,
    client_secret_hash      text,                            -- null = public client
    client_type             text not null check (client_type in ('public','confidential')),
    name                    text not null,
    logo_uri                text,
    client_uri              text,
    redirect_uris           text[] not null,
    grant_types             text[] not null default '{authorization_code,refresh_token}',
    response_types          text[] not null default '{code}',
    token_endpoint_auth     text not null default 'none',    -- 'none' | 'client_secret_basic' | 'client_secret_post'
    scope                   text not null default 'avatars:read',
    software_id             text,
    software_version        text,
    registered_by_user_id   uuid references users(id) on delete set null,
    dynamically_registered  boolean not null default false,
    created_at              timestamptz not null default now(),
    updated_at              timestamptz not null default now()
);

-- ── OAuth authorization codes (short-lived, PKCE) ───────────────────────────
create table if not exists oauth_auth_codes (
    code                text primary key,                    -- opaque, hashed-at-rest not needed (short TTL)
    client_id           text not null references oauth_clients(client_id) on delete cascade,
    user_id             uuid not null references users(id) on delete cascade,
    redirect_uri        text not null,
    scope               text not null,
    resource            text,                                -- RFC 8707 resource indicator
    code_challenge      text not null,
    code_challenge_method text not null default 'S256',
    expires_at          timestamptz not null,
    consumed_at         timestamptz,
    created_at          timestamptz not null default now()
);

create index if not exists oauth_auth_codes_expiry on oauth_auth_codes(expires_at);

-- ── OAuth refresh tokens ────────────────────────────────────────────────────
-- Access tokens are JWTs (stateless); refresh tokens are opaque + stored.
create table if not exists oauth_refresh_tokens (
    id              uuid primary key default gen_random_uuid(),
    token_hash      text not null unique,                    -- sha256 of the secret
    client_id       text not null references oauth_clients(client_id) on delete cascade,
    user_id         uuid not null references users(id) on delete cascade,
    scope           text not null,
    resource        text,
    expires_at      timestamptz not null,
    revoked_at      timestamptz,
    replaced_by     uuid references oauth_refresh_tokens(id),
    created_at      timestamptz not null default now(),
    last_used_at    timestamptz
);

create index if not exists oauth_refresh_user on oauth_refresh_tokens(user_id) where revoked_at is null;
create index if not exists oauth_refresh_user_client on oauth_refresh_tokens(user_id, client_id);
create index if not exists oauth_refresh_expiry on oauth_refresh_tokens(expires_at);

-- Revoking a connected app records a cutoff so its in-flight access tokens die
-- on the next request (api/_lib/auth.js revokeClientGrant / authenticateBearer).
create table if not exists oauth_client_revocations (
    user_id    uuid        not null references users(id) on delete cascade,
    client_id  text        not null references oauth_clients(client_id) on delete cascade,
    revoked_at timestamptz not null default now(),
    primary key (user_id, client_id)
);

-- ── Developer API keys (for server-to-server MCP usage) ─────────────────────
create table if not exists api_keys (
    id              uuid primary key default gen_random_uuid(),
    user_id         uuid not null references users(id) on delete cascade,
    name            text not null,
    prefix          text not null,                           -- first 8 chars, for display
    token_hash      text not null unique,                    -- sha256(rest)
    scope           text not null default 'avatars:read avatars:write',
    last_used_at    timestamptz,
    expires_at      timestamptz,
    revoked_at      timestamptz,
    created_at      timestamptz not null default now(),
    preset          text check (preset is null or preset = 'connector')
);

create index if not exists api_keys_user on api_keys(user_id) where revoked_at is null;

-- ── SIWE (Sign-In with Ethereum) ────────────────────────────────────────────
-- Short-lived nonces issued per client; burned on verify to prevent replay.
create table if not exists siwe_nonces (
    nonce        text primary key,
    address      text,                       -- lowercased, set on verify attempt (audit only)
    issued_at    timestamptz not null default now(),
    expires_at   timestamptz not null,
    consumed_at  timestamptz
);

create index if not exists siwe_nonces_expiry on siwe_nonces(expires_at);

-- ── SIWS (Sign-In with Solana) ───────────────────────────────────────────────
-- Parallel to siwe_nonces; same burn-on-verify replay protection.
create table if not exists siws_nonces (
    nonce        text primary key,
    address      text,                       -- base58, set on verify attempt (audit only)
    issued_at    timestamptz not null default now(),
    expires_at   timestamptz not null,
    consumed_at  timestamptz
);

create index if not exists siws_nonces_expiry on siws_nonces(expires_at);

-- Link addresses to users. A user may have multiple wallets across chains; address is unique.
-- chain_type: 'evm' for 0x-prefixed hex addresses, 'solana' for base58 addresses.
create table if not exists user_wallets (
    id           uuid primary key default gen_random_uuid(),
    user_id      uuid not null references users(id) on delete cascade,
    address      text not null unique,       -- lowercased 0x-prefixed (EVM) or base58 (Solana)
    chain_type   text not null default 'evm' check (chain_type in ('evm', 'solana')),
    chain_id     int,                        -- EVM chain ID; null for Solana
    is_primary   boolean not null default false,
    created_at   timestamptz not null default now(),
    last_used_at timestamptz
);

create index if not exists user_wallets_user on user_wallets(user_id);

-- Additive migration for deployments that pre-date chain_type.
alter table user_wallets add column if not exists chain_type text not null default 'evm'
    check (chain_type in ('evm', 'solana'));

-- Linked Solana wallets that hold a Seeker Genesis Token (soulbound Token-2022
-- token minted per Solana Seeker phone). Written by /api/seeker/verify, read by
-- /api/seeker/status and the "Seeker verified" badge.
create table if not exists seeker_genesis_verifications (
    user_id         uuid not null references users(id) on delete cascade,
    wallet_address  text not null,
    token_mint      text not null,
    verified_at     timestamptz not null default now(),
    last_checked_at timestamptz not null default now(),
    primary key (user_id, wallet_address)
);

create index if not exists seeker_genesis_verifications_wallet
    on seeker_genesis_verifications(wallet_address);

-- ── Sessions (browser cookie auth) ──────────────────────────────────────────
create table if not exists sessions (
    id              uuid primary key default gen_random_uuid(),
    user_id         uuid not null references users(id) on delete cascade,
    token_hash      text not null unique,
    user_agent      text,
    ip              inet,
    expires_at      timestamptz not null,
    revoked_at      timestamptz,
    created_at      timestamptz not null default now(),
    last_seen_at    timestamptz not null default now()
);

create index if not exists sessions_user on sessions(user_id) where revoked_at is null;
create index if not exists sessions_expiry on sessions(expires_at);

-- ── Usage events (for quotas, analytics, billing) ───────────────────────────
create table if not exists usage_events (
    id              bigserial primary key,
    user_id         uuid references users(id) on delete set null,
    api_key_id      uuid references api_keys(id) on delete set null,
    client_id       text references oauth_clients(client_id) on delete set null,
    avatar_id       uuid references avatars(id) on delete set null,
    kind            text not null,                           -- 'tool_call' | 'avatar_fetch' | 'upload' | 'render'
    tool            text,                                    -- MCP tool name if applicable
    status          text not null default 'ok',              -- 'ok' | 'error' | 'rate_limited'
    bytes           bigint,
    latency_ms      int,
    meta            jsonb not null default '{}'::jsonb,
    created_at      timestamptz not null default now()
);

create index if not exists usage_events_user_time on usage_events(user_id, created_at desc);
create index if not exists usage_events_kind_time on usage_events(kind, created_at desc);

-- ── Plan quotas (soft reference; actual limits enforced in code) ────────────
create table if not exists plan_quotas (
    plan                text primary key,
    max_avatars         int not null,
    max_bytes_per_avatar bigint not null,
    max_total_bytes     bigint not null,
    mcp_calls_per_day   int not null,
    updated_at          timestamptz not null default now()
);

insert into plan_quotas (plan, max_avatars, max_bytes_per_avatar, max_total_bytes, mcp_calls_per_day) values
    ('free',         10,   26214400,       262144000,          1000),
    ('pro',          500,  52428800,       26843545600,        50000),
    ('team',         5000, 104857600,      536870912000,       500000),
    ('enterprise',   100000, 524288000,    10995116277760,     10000000)
on conflict (plan) do update set
    max_avatars = excluded.max_avatars,
    max_bytes_per_avatar = excluded.max_bytes_per_avatar,
    max_total_bytes = excluded.max_total_bytes,
    mcp_calls_per_day = excluded.mcp_calls_per_day,
    updated_at = now();

-- ── updated_at triggers ─────────────────────────────────────────────────────
create or replace function set_updated_at() returns trigger as $$
begin
    new.updated_at = now();
    return new;
end;
$$ language plpgsql;

do $$ begin
    create trigger users_set_updated_at before update on users
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

do $$ begin
    create trigger avatars_set_updated_at before update on avatars
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

do $$ begin
    create trigger oauth_clients_set_updated_at before update on oauth_clients
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

-- ── agent_identities — every agent gets a body, a place, an identity ─────────
CREATE TABLE IF NOT EXISTS agent_identities (
	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id uuid NOT NULL REFERENCES users(id),
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz,
	deleted_at timestamptz,
	name text NOT NULL CHECK (length(name) > 0),
	description text,
	persona_prompt text,
	home_url text,
	avatar_url text,
	profile_image_url text,
	is_public boolean NOT NULL DEFAULT true,
	is_template boolean NOT NULL DEFAULT false
);

create index if not exists agent_identities_user
    on agent_identities(user_id) where deleted_at is null;

-- Additive migrations for agent_identities columns added after initial deployment.
alter table agent_identities add column if not exists wallet_address   text;
-- Custodial wallet keys, on-chain identity, persona, payments, and the unified
-- meta.onchain block all live in `meta`; `skills` is the agent's text[] skill
-- list (read/written as a raw Postgres array — never jsonb — by api/agents.js
-- and queried with `skills @> '{…}'::text[]` in api/agents/public.js). Both
-- columns are written on EVERY create path (api/agents.js:154,281) and read by
-- the wallet/onchain provisioning, the task-01 backfill, and the custody
-- migration, yet the original CREATE TABLE above never declared them — a DB
-- bootstrapped purely from this file 500s on the first wallet write without
-- these two lines. Declare them idempotently here so a fresh apply is correct.
alter table agent_identities add column if not exists meta             jsonb not null default '{}'::jsonb;
alter table agent_identities add column if not exists skills           text[] not null default '{}'::text[];

-- Wallet-address index: created AFTER the column is guaranteed above so a fresh
-- apply never references a not-yet-added column (the original ordering indexed
-- wallet_address before its ADD and broke a clean-room bootstrap).
create index if not exists agent_identities_wallet
    on agent_identities(wallet_address) where wallet_address is not null;
alter table agent_identities add column if not exists chain_id         int;
alter table agent_identities add column if not exists erc8004_agent_id bigint;
alter table agent_identities add column if not exists erc8004_registry text;
alter table agent_identities add column if not exists registration_cid text;
alter table agent_identities add column if not exists home_url         text;
alter table agent_identities add column if not exists embed_policy     jsonb;
alter table agent_identities add column if not exists voice_provider   text default 'browser';
alter table agent_identities add column if not exists voice_id         text;
alter table agent_identities add column if not exists voice_cloned_at  timestamptz;
alter table agent_identities add column if not exists voice_model      text;
alter table agent_identities add column if not exists voice_settings   jsonb;
alter table agent_identities add column if not exists farcaster_fid        integer;
alter table agent_identities add column if not exists farcaster_fname      text;
alter table agent_identities add column if not exists farcaster_seeded_at  timestamptz;
alter table agent_identities add column if not exists persona_prompt        text;
alter table agent_identities add column if not exists persona_prompt_hash   text;
alter table agent_identities add column if not exists persona_prompt_sig    text;
alter table agent_identities add column if not exists persona_tone_tags     jsonb not null default '[]'::jsonb;
alter table agent_identities add column if not exists persona_extracted_at  timestamptz;
-- Brain Studio: structured, editable personality. `persona_traits` is the source
-- of truth for the slider dimensions + vocabulary + base persona; it compiles
-- (src/agents/persona-compile.js) into the signed `persona_prompt` above.
-- `persona_updated_at` tracks the last Brain Studio save (distinct from the
-- one-time extraction interview in `persona_extracted_at`).
alter table agent_identities add column if not exists persona_traits        jsonb not null default '{}'::jsonb;
alter table agent_identities add column if not exists persona_updated_at    timestamptz;
-- is_public arrived via inline CREATE on fresh DBs but never as an additive
-- migration, so pre-existing deployments are missing the column entirely —
-- that 500s /api/avatars/:id/agents. Add it and backfill to the new default.
alter table agent_identities add column if not exists is_public boolean not null default true;
alter table agent_identities alter column is_public set default true;
update agent_identities set is_public = true
 where is_public = false and deleted_at is null;

-- Per-agent Metaplex Core skill collection: master identifier for the agent's
-- "skill ownership" NFTs. Populated by scripts/create-agent-collection.mjs.
-- See api/_lib/migrations/20260617120000_agent_skill_collection.sql.
alter table agent_identities add column if not exists skill_collection_mint       text;
alter table agent_identities add column if not exists skill_collection_network    text;
alter table agent_identities add column if not exists skill_collection_uri        text;
alter table agent_identities add column if not exists skill_collection_tx         text;
alter table agent_identities add column if not exists skill_collection_created_at timestamptz;
create unique index if not exists agent_identities_skill_collection_mint
    on agent_identities (skill_collection_mint)
    where skill_collection_mint is not null;

-- ── agent_memories — the agent's persistent context ──────────────────────────
create table if not exists agent_memories (
    id          uuid primary key default gen_random_uuid(),
    agent_id    uuid not null references agent_identities(id) on delete cascade,
    type        text not null check (type in ('user','feedback','project','reference')),
    content     text not null,
    tags        text[] not null default '{}',
    context     jsonb not null default '{}'::jsonb,
    salience    real not null default 0.5,
    created_at  timestamptz not null default now(),
    updated_at  timestamptz not null default now(),
    expires_at  timestamptz
);

create index if not exists agent_memories_agent_type
    on agent_memories(agent_id, type, created_at desc)
    where expires_at is null;

-- Memories are owner-only by default. `is_public` is an explicit opt-in that
-- lets an owner surface a memory on their public profile (/u/<username>).
-- Default false so nothing is ever exposed without a deliberate toggle.
alter table agent_memories add column if not exists is_public boolean not null default false;

-- Partial index for the public-profile read path: public, non-expired memories
-- for a given agent, newest first.
create index if not exists agent_memories_public
    on agent_memories(agent_id, created_at desc)
    where is_public = true and expires_at is null;

-- Memory Studio (P2): tiered model (working/recall/archival) + recall bookkeeping
-- + lazy embedding/entity cursors. See migrations/20260619120000_memory_studio.sql.
alter table agent_memories add column if not exists tier text not null default 'recall'
    check (tier in ('working', 'recall', 'archival'));
alter table agent_memories add column if not exists embedding jsonb;
alter table agent_memories add column if not exists embedder text;
alter table agent_memories add column if not exists pinned boolean not null default false;
alter table agent_memories add column if not exists last_accessed_at timestamptz;
alter table agent_memories add column if not exists access_count integer not null default 0;
alter table agent_memories add column if not exists entities_extracted boolean not null default false;

create index if not exists agent_memories_working
    on agent_memories(agent_id, salience desc)
    where tier = 'working' and expires_at is null;
create index if not exists agent_memories_needs_embed
    on agent_memories(agent_id, created_at desc)
    where embedding is null and expires_at is null;
create index if not exists agent_memories_needs_entities
    on agent_memories(agent_id, created_at desc)
    where entities_extracted = false and expires_at is null;

do $$ begin
    create trigger agent_memories_set_updated_at before update on agent_memories
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

-- ── agent_reflections / _reflection_runs — Reflection & Dreams (Living Agents) ─
-- The agent consolidates raw memories + actions into higher-order "dreams" the
-- owner reviews. agent_reflections holds the candidate insights (provenance via
-- source_memory_ids is mandatory); agent_reflection_runs logs every pass — even
-- skipped ones — so the per-agent daily cap + debounce never silently truncate.
-- See migrations/20260623210000_agent_reflections.sql.
create table if not exists agent_reflections (
    id                 uuid primary key default gen_random_uuid(),
    agent_id           uuid not null references agent_identities(id) on delete cascade,
    status             text not null default 'pending'
                           check (status in ('pending', 'accepted', 'rejected')),
    kind               text not null default 'insight'
                           check (kind in ('insight', 'belief', 'question', 'prune')),
    statement          text not null,
    rationale          text,
    confidence         real not null default 0.5 check (confidence >= 0 and confidence <= 1),
    source_memory_ids  uuid[] not null default '{}',
    proposed_type      text check (proposed_type in ('user', 'feedback', 'project', 'reference')),
    proposed_salience  real not null default 0.7 check (proposed_salience >= 0 and proposed_salience <= 1),
    proposed_action    jsonb,
    question           text,
    answer             text,
    run_id             uuid,
    accepted_memory_id uuid,
    created_at         timestamptz not null default now(),
    reviewed_at        timestamptz
);
create index if not exists agent_reflections_agent_status
    on agent_reflections(agent_id, status, created_at desc);
create index if not exists agent_reflections_run
    on agent_reflections(run_id)
    where run_id is not null;

create table if not exists agent_reflection_runs (
    id             uuid primary key default gen_random_uuid(),
    agent_id       uuid not null references agent_identities(id) on delete cascade,
    trigger        text not null check (trigger in ('cron', 'on-demand', 'manual')),
    status         text not null check (status in ('ok', 'skipped', 'error')),
    reason         text,
    dreams_created integer not null default 0,
    candidates     integer not null default 0,
    model          text,
    input_tokens   integer,
    output_tokens  integer,
    created_at     timestamptz not null default now()
);
create index if not exists agent_reflection_runs_agent_time
    on agent_reflection_runs(agent_id, created_at desc);

-- ── agent_autopilot_proposals — Memory-grounded Autopilot (Living Agents) ─────
-- Explainable autonomy: the agent proposes and (within owner-granted scope) takes
-- REAL actions, each traceable to the memory/reflection that motivated it. Scope
-- lives on agent_identities.meta.autopilot; this is the proposal queue. Execution
-- records a signed agent_actions row linked via executed_action_id. Provenance
-- (source_memory_ids and/or source_reflection_id) is mandatory.
-- See migrations/20260623230000_autopilot_proposals.sql.
create table if not exists agent_autopilot_proposals (
    id                    uuid primary key default gen_random_uuid(),
    agent_id              uuid not null references agent_identities(id) on delete cascade,
    user_id               uuid not null references users(id) on delete cascade,
    kind                  text not null
                              check (kind in ('create_alert', 'briefing', 'wallet_transfer')),
    title                 text not null,
    rationale             text not null,
    params                jsonb not null default '{}'::jsonb,
    source_memory_ids     uuid[] not null default '{}',
    source_reflection_id  uuid references agent_reflections(id) on delete set null,
    confidence            real not null default 0.6 check (confidence >= 0 and confidence <= 1),
    requires_confirmation boolean not null default true,
    status                text not null default 'pending'
                              check (status in ('pending', 'executed', 'dismissed', 'undone', 'failed')),
    executed_action_id    bigint,
    result                jsonb not null default '{}'::jsonb,
    created_at            timestamptz not null default now(),
    decided_at            timestamptz,
    executed_at           timestamptz,
    constraint agent_autopilot_proposals_has_source
        check (array_length(source_memory_ids, 1) > 0 or source_reflection_id is not null)
);
create index if not exists agent_autopilot_proposals_agent_status
    on agent_autopilot_proposals(agent_id, status, created_at desc);
create index if not exists agent_autopilot_proposals_user
    on agent_autopilot_proposals(user_id, created_at desc);
create unique index if not exists agent_autopilot_proposals_reflection_pending
    on agent_autopilot_proposals(source_reflection_id)
    where source_reflection_id is not null and status = 'pending';

-- ── agent_memory_entities / _entity_links — temporal knowledge graph (P2) ────
-- Nodes are the entities (mints, tickers, wallets, people, strategies, topics)
-- the agent's memories mention; edges are derived at read time from co-occurrence
-- within the same memory. See migrations/20260619120000_memory_studio.sql.
create table if not exists agent_memory_entities (
    id            uuid primary key default gen_random_uuid(),
    agent_id      uuid not null references agent_identities(id) on delete cascade,
    kind          text not null,
    label         text not null,
    normalized    text not null,
    salience      real not null default 0.5,
    mention_count integer not null default 0,
    first_seen_at timestamptz not null default now(),
    last_seen_at  timestamptz not null default now(),
    meta          jsonb not null default '{}'::jsonb,
    unique (agent_id, kind, normalized)
);
create index if not exists agent_memory_entities_agent
    on agent_memory_entities(agent_id, last_seen_at desc);
create index if not exists agent_memory_entities_kind
    on agent_memory_entities(agent_id, kind, mention_count desc);

create table if not exists agent_memory_entity_links (
    entity_id   uuid not null references agent_memory_entities(id) on delete cascade,
    memory_id   uuid not null references agent_memories(id) on delete cascade,
    created_at  timestamptz not null default now(),
    primary key (entity_id, memory_id)
);
create index if not exists agent_memory_entity_links_memory
    on agent_memory_entity_links(memory_id);

-- ── agent_memory_pins — IPFS CIDs an agent has pinned ────────────────────────
-- Lets the read proxy (GET /api/agents/:id/memory/:cid) confirm the requested
-- CID belongs to this agent instead of proxying any caller-supplied CID.
create table if not exists agent_memory_pins (
    agent_id    uuid not null references agent_identities(id) on delete cascade,
    cid         text not null,
    filename    text not null,
    bytes       integer not null default 0,
    created_at  timestamptz not null default now(),
    primary key (agent_id, cid)
);

create index if not exists agent_memory_pins_agent
    on agent_memory_pins(agent_id, created_at desc);

-- ── agent_actions — append-only signed history ───────────────────────────────
create table if not exists agent_actions (
    id             bigserial primary key,
    agent_id       uuid not null references agent_identities(id) on delete cascade,
    type           text not null,
    payload        jsonb not null default '{}'::jsonb,
    source_skill   text,
    signature      text,
    signer_address text,
    created_at     timestamptz not null default now()
);

create index if not exists agent_actions_agent_time
    on agent_actions(agent_id, created_at desc);
create index if not exists agent_actions_type_time
    on agent_actions(type, created_at desc);

do $$ begin
    create trigger agent_identities_set_updated_at before update on agent_identities
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

-- ── widgets — saved configurations of avatars rendered in a widget runtime ──
create table if not exists widgets (
    id              text primary key,                -- 'wdgt_' + 12 base64url chars
    user_id         uuid not null references users(id) on delete cascade,
    avatar_id       uuid references avatars(id) on delete set null,
    type            text not null check (type in ('turntable','animation-gallery','talking-agent','passport','hotspot-tour','pumpfun-feed','kol-trades','live-trades-canvas','bonding-curve','walking-avatar')),
    name            text not null,
    config          jsonb not null default '{}'::jsonb,
    is_public       boolean not null default true,
    view_count      bigint not null default 0,
    created_at      timestamptz not null default now(),
    updated_at      timestamptz not null default now(),
    deleted_at      timestamptz
);

create index if not exists widgets_user_idx
    on widgets(user_id) where deleted_at is null;
create index if not exists widgets_type_idx
    on widgets(type) where deleted_at is null;

do $$ begin
    create trigger widgets_set_updated_at before update on widgets
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

-- ── widget_views — anonymous load events for widget owner analytics ─────────
-- No IPs, no UAs, no cookies. country from x-vercel-ip-country edge header.
create table if not exists widget_views (
    id            bigserial primary key,
    widget_id     text not null references widgets(id) on delete cascade,
    country       text,
    referer_host  text,
    created_at    timestamptz not null default now()
);

create index if not exists widget_views_widget_time
    on widget_views(widget_id, created_at desc);

-- ── widget_chat_threads — one row per (widget, visitor, page-load) ──────────
-- visitor_id is a cookieless opaque UUID minted client-side in localStorage;
-- thread_id is per page-load in sessionStorage so each "conversation start"
-- gets its own bucket. Lets the creator see conversations grouped by visit.
create table if not exists widget_chat_threads (
    id              text primary key,                -- 'wct_' + 12 base64url chars
    widget_id       text not null references widgets(id) on delete cascade,
    visitor_id      text not null,
    referer_host    text,
    country         text,
    user_agent_hash text,
    message_count   integer not null default 0,
    started_at      timestamptz not null default now(),
    last_message_at timestamptz not null default now()
);

create index if not exists widget_chat_threads_widget_time
    on widget_chat_threads(widget_id, last_message_at desc);
create index if not exists widget_chat_threads_visitor
    on widget_chat_threads(widget_id, visitor_id, started_at desc);

-- ── widget_chat_messages — append-only log of visitor + assistant turns ─────
-- content is the redacted form — email/phone/card patterns are scrubbed at
-- write time so the creator can review without storing PII.
create table if not exists widget_chat_messages (
    id           bigserial primary key,
    thread_id    text not null references widget_chat_threads(id) on delete cascade,
    widget_id    text not null references widgets(id) on delete cascade,
    role         text not null check (role in ('user', 'assistant')),
    content      text not null,
    actions      jsonb,
    provider     text,
    model        text,
    redacted     boolean not null default false,
    created_at   timestamptz not null default now()
);

create index if not exists widget_chat_messages_thread_time
    on widget_chat_messages(thread_id, created_at);
create index if not exists widget_chat_messages_widget_time
    on widget_chat_messages(widget_id, created_at desc);

-- ── widget_knowledge_docs — uploaded docs + URLs that ground the agent ─────
create table if not exists widget_knowledge_docs (
    id           text primary key,                   -- 'wkd_' + 12 base64url chars
    widget_id    text not null references widgets(id) on delete cascade,
    user_id      uuid not null references users(id) on delete cascade,
    title        text not null,
    source_type  text not null check (source_type in ('url', 'text', 'pdf', 'markdown')),
    source_url   text,
    byte_size    integer not null default 0,
    chunk_count  integer not null default 0,
    token_count  integer not null default 0,
    status       text not null default 'ready' check (status in ('queued', 'processing', 'ready', 'failed')),
    error        text,
    source_text  text,                                   -- held server-side until QStash worker consumes it
    embedder     text,                                   -- vector space tag ('<model>@<dim>'); null = legacy text-embedding-3-small@256
    created_at   timestamptz not null default now(),
    updated_at   timestamptz not null default now()
);

alter table widget_knowledge_docs add column if not exists embedder text;

create index if not exists widget_knowledge_docs_widget
    on widget_knowledge_docs(widget_id, created_at desc);
create index if not exists widget_knowledge_docs_user
    on widget_knowledge_docs(user_id, created_at desc);

do $$ begin
    create trigger widget_knowledge_docs_set_updated_at before update on widget_knowledge_docs
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

-- ── widget_knowledge_chunks — 512/100-overlap chunks with tagged embeds ─────
-- Embedding is stored as a JSONB float array, produced by the embedder named
-- in `embedder` ('<model>@<dim>', e.g. 'nvidia/nv-embedqa-e5-v5@1024' on the
-- free NIM lane or 'text-embedding-3-small@256' on OpenAI; null = legacy
-- OpenAI rows). Vectors from different embedders are different spaces — query
-- time embeds the search string with the SAME tag and never compares across
-- tags (api/_lib/embeddings.js scoreRowsBySpace). Retrieval scores via cosine
-- similarity in JS at query time; switch to pgvector if a single widget grows
-- past several thousand chunks.
create table if not exists widget_knowledge_chunks (
    id            bigserial primary key,
    doc_id        text not null references widget_knowledge_docs(id) on delete cascade,
    widget_id     text not null references widgets(id) on delete cascade,
    chunk_index   integer not null,
    content       text not null,
    embedding     jsonb not null,
    token_count   integer not null default 0,
    embedder      text,
    created_at    timestamptz not null default now()
);

alter table widget_knowledge_chunks add column if not exists embedder text;

create index if not exists widget_knowledge_chunks_widget
    on widget_knowledge_chunks(widget_id);
create index if not exists widget_knowledge_chunks_doc
    on widget_knowledge_chunks(doc_id, chunk_index);

-- ── erc8004_agents_index — crawled directory of every on-chain agent ───────
-- Populated by api/cron/erc8004-crawl.js from Etherscan V2 getLogs across every
-- chain in REGISTRY_DEPLOYMENTS. Metadata fields are lazily filled from each
-- agent's agentURI JSON. `has_3d` = true when services[name=avatar] is set.
create table if not exists erc8004_agents_index (
    chain_id           integer     not null,
    agent_id           text        not null,                -- uint256 as decimal string
    owner              text        not null,                -- 0x-lowercase
    registry           text        not null,                -- 0x-lowercase contract addr
    agent_uri          text,
    name               text,
    description        text,
    image              text,                                -- 2D thumbnail URL
    glb_url            text,                                -- services[name=avatar] endpoint
    services           jsonb       not null default '[]'::jsonb,
    x402_support       boolean     not null default false,
    has_3d             boolean     not null default false,
    active             boolean     not null default true,
    registered_block   bigint,
    registered_tx      text,
    registered_at      timestamptz,
    last_metadata_at   timestamptz,
    metadata_error     text,
    last_seen_at       timestamptz not null default now(),
    primary key (chain_id, agent_id)
);

create index if not exists erc8004_agents_has3d_time
    on erc8004_agents_index(has_3d, registered_at desc) where active;
create index if not exists erc8004_agents_chain_time
    on erc8004_agents_index(chain_id, registered_at desc) where active;
create index if not exists erc8004_agents_owner
    on erc8004_agents_index(owner) where active;
create index if not exists erc8004_agents_metadata_stale
    on erc8004_agents_index(last_metadata_at nulls first);

-- head_block / blocks_behind exist because `updated_at` alone proves the cron
-- ran, not that the index caught up: five of the configured chains produce
-- blocks faster than a fixed chunk can consume them, so their backlog grows
-- every tick while the cursor looks fresh. chunk_size adapts per chain (grows
-- while behind, halves on an RPC range rejection) so each provider is used to
-- its real limit. See migration 20260813210000_erc8004_crawl_head_tracking.sql.
create table if not exists erc8004_crawl_cursor (
    chain_id       integer primary key,
    last_block     bigint  not null default 0,
    updated_at     timestamptz not null default now(),
    head_block     bigint,
    blocks_behind  bigint  not null default 0,
    chunk_size     integer not null default 1000,
    last_error     text
);

create index if not exists erc8004_crawl_cursor_behind
    on erc8004_crawl_cursor(blocks_behind desc);

-- ── solana_agents_index — crawled directory of every on-chain Solana agent ──
-- External agents (NOT three.ws's own agent_identities) from two registries,
-- folded into one table via `source`:
--   'metaplex' → Metaplex Agent Registry (1DREGFgysWYxLnRnKQnwrxnJQeSMk2HmGaC6whw2B2p)
--   'agenc'    → AgenC coordination protocol (Tetsuo Corp)
-- Populated by api/cron/[name].js → handleSolanaAgentsCrawl. Served by
-- /api/explore?source=agents next to erc8004_agents_index (EVM) so /agents lists
-- the whole ecosystem. Mirrors api/_lib/migrations/20260628140000_solana_agents_index.sql.
create table if not exists solana_agents_index (
    source           text        not null,
    ref              text        not null,
    network          text        not null default 'mainnet',
    owner            text,
    asset            text,
    agent_id         text,
    name             text,
    description      text,
    image            text,
    glb_url          text,
    metadata_uri     text,
    endpoint         text,
    capabilities     text,
    reputation       integer,
    status           text,
    has_3d           boolean     not null default false,
    x402_support     boolean     not null default false,
    active           boolean     not null default true,
    registered_at    timestamptz,
    last_metadata_at timestamptz,
    metadata_error   text,
    last_seen_at     timestamptz not null default now(),
    primary key (source, ref)
);
create index if not exists solana_agents_active_time
    on solana_agents_index(registered_at desc) where active;
create index if not exists solana_agents_source_time
    on solana_agents_index(source, registered_at desc) where active;
create index if not exists solana_agents_owner
    on solana_agents_index(owner) where active;
create index if not exists solana_agents_asset
    on solana_agents_index(asset) where asset is not null;
create index if not exists solana_agents_metadata_stale
    on solana_agents_index(last_metadata_at nulls first);

-- ── Solana on-chain attestations (ERC-8004 analog, no deployed program) ─────
-- Each row is one signed SPL Memo tx referencing an agent's Metaplex Core
-- asset pubkey. Schemas: threews.{feedback,validation,task,accept,revoke,dispute}.v1
create table if not exists solana_attestations (
    signature        text        primary key,
    network          text        not null,                      -- 'mainnet' | 'devnet'
    slot             bigint      not null,
    block_time       timestamptz,
    agent_asset      text        not null,                      -- referenced agent pubkey
    attester         text        not null,                      -- fee payer / signer
    kind             text        not null,                      -- e.g. threews.feedback.v1
    payload          jsonb       not null,
    task_id          text,                                      -- denormalized for fast joins
    target_signature text,                                      -- for revoke/dispute → original
    verified         boolean     not null default false,        -- payload schema-valid + task linkage if required
    revoked          boolean     not null default false,
    disputed         boolean     not null default false,
    indexed_at       timestamptz not null default now()
);
create index if not exists solana_att_agent_kind_time on solana_attestations(agent_asset, kind, slot desc);
create index if not exists solana_att_attester       on solana_attestations(attester);
create index if not exists solana_att_task_id        on solana_attestations(task_id) where task_id is not null;
create index if not exists solana_att_target         on solana_attestations(target_signature) where target_signature is not null;

create table if not exists solana_attestations_cursor (
    agent_asset       text primary key,
    network           text not null,
    last_signature    text,
    last_indexed_at   timestamptz not null default now()
);

-- ── agent_onchain_events — cross-chain agent lifecycle index (Solana first) ──
-- One row per indexed on-chain event, every chain in one table. Written by the
-- erc8004-crawl (EVM registry logs) and solana-attestations-crawl (Solana
-- account signatures) crons; read by /api/agents/onchain-history and the index
-- lag monitor on /status. `chain_id` is 0 on Solana so the uniqueness index
-- never sees a NULL. occurred_at is the ABSOLUTE on-chain timestamp; indexed_at
-- is ingestion time. Mirrors api/_lib/migrations/20260811090000_agent_onchain_events.sql.
create table if not exists agent_onchain_events (
    id            bigserial   primary key,
    chain         text        not null,
    chain_id      integer     not null default 0,
    network       text        not null default 'mainnet',
    agent_ref     text        not null,
    event_class   text        not null,
    event_name    text        not null,
    tx            text        not null,
    log_index     integer     not null default 0,
    block_number  bigint,
    occurred_at   timestamptz not null,
    actor         text,
    counterparty  text,
    payload       jsonb       not null default '{}'::jsonb,
    indexed_at    timestamptz not null default now()
);
create unique index if not exists agent_onchain_events_uniq
    on agent_onchain_events(chain, chain_id, tx, log_index);
create index if not exists agent_onchain_events_agent_time
    on agent_onchain_events(agent_ref, occurred_at desc);
create index if not exists agent_onchain_events_class_time
    on agent_onchain_events(event_class, occurred_at desc);
create index if not exists agent_onchain_events_chain_indexed
    on agent_onchain_events(chain, indexed_at desc);

create table if not exists agent_event_cursor (
    chain           text        not null,
    chain_id        integer     not null default 0,
    agent_ref       text        not null,
    network         text        not null default 'mainnet',
    last_tx         text,
    last_slot       bigint,
    last_event_at   timestamptz,
    last_indexed_at timestamptz not null default now(),
    scanned         integer     not null default 0,
    error           text,
    primary key (chain, chain_id, agent_ref)
);
create index if not exists agent_event_cursor_stale
    on agent_event_cursor(last_indexed_at nulls first);

-- ── SAS credentials (credentialed attestations issued by three.ws authority) ──
-- Permissionless attestations live in solana_attestations (memo-based);
-- this table is the credentialed counterpart for things only we can issue
-- (verified-client, audited-validation, etc.). Used to weight reputation.
create table if not exists solana_credentials (
    attestation_pda   text        primary key,
    network           text        not null,
    schema_pda        text        not null,
    credential_pda    text        not null,
    kind              text        not null,                -- e.g. threews.verified-client.v1
    subject           text        not null,                -- nonce; wallet or agent asset pubkey
    issuer_signature  text        not null,                -- tx sig of issuance
    data              jsonb       not null default '{}'::jsonb,
    expiry            timestamptz,
    closed            boolean     not null default false,
    closed_at         timestamptz,
    issued_at         timestamptz not null default now()
);
create index if not exists solana_creds_subject_kind on solana_credentials(subject, kind) where closed = false;
create index if not exists solana_creds_kind         on solana_credentials(kind, issued_at desc);

-- Additive migrations for usage_events.
alter table usage_events add column if not exists agent_id uuid references agent_identities(id) on delete set null;
create index if not exists usage_events_agent_time on usage_events(agent_id, created_at desc) where agent_id is not null;

-- LLM cost accounting (see migrations/20260608010000_usage_events_llm_cost.sql).
-- cost_micro_usd is micro-USD ($0.000001) as bigint so spend sums never drift.
alter table usage_events add column if not exists provider       text;
alter table usage_events add column if not exists model          text;
alter table usage_events add column if not exists input_tokens   int;
alter table usage_events add column if not exists output_tokens  int;
alter table usage_events add column if not exists cost_micro_usd bigint;
create index if not exists usage_events_llm_time on usage_events(created_at desc) where kind = 'llm';
create index if not exists usage_events_llm_provider_time on usage_events(provider, created_at desc) where kind = 'llm' and provider is not null;

-- Usage metering ledger (see migrations/20260623170000_usage_metering.sql).
-- Money is stored in USDC atomics (6 decimals) as bigint so statement sums never
-- drift. Metered rows carry kind='metered' and an idempotency_key so a retried
-- settlement meters EXACTLY once.
alter table usage_events add column if not exists meter_action       text;
alter table usage_events add column if not exists units              int;
alter table usage_events add column if not exists price_usdc_atomics bigint;
alter table usage_events add column if not exists fee_usdc_atomics   bigint;
alter table usage_events add column if not exists discount_bps       int;
alter table usage_events add column if not exists settlement_ref     text;
alter table usage_events add column if not exists settlement_kind    text;
alter table usage_events add column if not exists idempotency_key    text;
create unique index if not exists usage_events_idem on usage_events(idempotency_key) where idempotency_key is not null;
create index if not exists usage_events_settlement on usage_events(settlement_ref) where settlement_ref is not null;
create index if not exists usage_events_metered_user_time on usage_events(user_id, created_at desc) where kind = 'metered';

-- ── agent_registrations_pending — transient prep records for 2-step registration ─────
create table if not exists agent_registrations_pending (
	id              uuid primary key default gen_random_uuid(),
	user_id         uuid not null references users(id) on delete cascade,
	cid             text,                                  -- IPFS CID (null when stored via the R2 fallback)
	metadata_uri    text not null,                         -- ipfs://CID
	payload         jsonb not null,                        -- registration JSON
	created_at      timestamptz not null default now(),
	expires_at      timestamptz not null
);

create index if not exists agent_registrations_pending_user_expiry
	on agent_registrations_pending(user_id, expires_at);

-- ── agent_delegations — ERC-7710 signed delegation envelopes ────────────────
create table if not exists agent_delegations (
    id                  uuid primary key default gen_random_uuid(),
    agent_id            uuid not null references agent_identities(id) on delete cascade,
    chain_id            integer not null,
    delegator_address   text not null,
    delegate_address    text not null,
    delegation_hash     text not null unique,
    delegation_json     jsonb not null,
    scope               jsonb not null,
    status              text not null default 'active',
    expires_at          timestamptz not null,
    created_at          timestamptz not null default now(),
    revoked_at          timestamptz,
    tx_hash_revoke      text,
    last_redeemed_at    timestamptz,
    redemption_count    integer not null default 0,

    constraint agent_delegations_status_check
        check (status in ('active', 'revoked', 'expired')),
    constraint agent_delegations_chain_id_check
        check (chain_id > 0),
    constraint agent_delegations_delegator_address_check
        check (length(delegator_address) = 42 and delegator_address like '0x%'),
    constraint agent_delegations_delegate_address_check
        check (length(delegate_address) = 42 and delegate_address like '0x%')
);

create index if not exists idx_delegations_agent on agent_delegations(agent_id);
create index if not exists idx_delegations_status on agent_delegations(status) where status = 'active';
create index if not exists idx_delegations_delegator on agent_delegations(delegator_address);

-- ── agent_subscriptions — recurring on-chain payment schedules ──────────────
create table if not exists agent_subscriptions (
    id                  uuid primary key default gen_random_uuid(),
    user_id             uuid not null references users(id) on delete cascade,
    agent_id            uuid not null references agent_identities(id) on delete cascade,
    delegation_id       uuid not null references agent_delegations(id) on delete cascade,
    period_seconds      integer not null,
    amount_per_period   text not null,
    next_charge_at      timestamptz not null,
    last_charge_at      timestamptz,
    status              text not null default 'active',
    last_error          text,
    created_at          timestamptz not null default now(),
    canceled_at         timestamptz,

    constraint agent_subscriptions_status_check
        check (status in ('active', 'canceled', 'paused')),
    constraint agent_subscriptions_period_seconds_check
        check (period_seconds > 0)
);

create index if not exists idx_subscriptions_due on agent_subscriptions(next_charge_at) where status = 'active';
create index if not exists idx_subscriptions_user on agent_subscriptions(user_id);
create index if not exists idx_subscriptions_agent on agent_subscriptions(agent_id);

-- Lifecycle columns (migration 20260813191500_recurring_payment_lifecycle.sql).
-- A retryable failure bumps consecutive_failures and leaves the schedule active
-- so the next tick retries; the cron pauses it once the bound is hit. paused_at
-- / resumed_at make an owner's pause and resume auditable.
alter table agent_subscriptions add column if not exists consecutive_failures integer not null default 0;
alter table agent_subscriptions add column if not exists last_error_code      text;
alter table agent_subscriptions add column if not exists last_tx_hash         text;
alter table agent_subscriptions add column if not exists paused_at            timestamptz;
alter table agent_subscriptions add column if not exists resumed_at           timestamptz;

-- ── subscription_charges: per-tick charge attempt log ──────────────────────
-- The subscription mirror of dca_executions: one row per attempt, so a creator
-- can see what a schedule actually paid and every failure keeps the code the
-- cron classified it under.
create table if not exists subscription_charges (
    id                  uuid primary key default gen_random_uuid(),
    subscription_id     uuid not null references agent_subscriptions(id) on delete cascade,
    agent_id            uuid not null references agent_identities(id) on delete cascade,
    payer_user_id       uuid references users(id) on delete set null,
    chain_id            integer,
    amount              text not null,
    tx_hash             text,
    status              text not null default 'pending',
    code                text,
    outcome             text,
    error               text,
    period_start_at     timestamptz,
    charged_at          timestamptz not null default now(),

    constraint subscription_charges_status_check
        check (status in ('success', 'failed', 'aborted', 'unknown')),
    constraint subscription_charges_outcome_check
        check (outcome is null or outcome in ('charged', 'fatal', 'retryable', 'ambiguous', 'skipped'))
);

create index if not exists idx_subscription_charges_subscription
    on subscription_charges(subscription_id, charged_at desc);
create index if not exists idx_subscription_charges_agent
    on subscription_charges(agent_id, charged_at desc);
create unique index if not exists uq_subscription_charges_period
    on subscription_charges(subscription_id, period_start_at)
    where status = 'success' and period_start_at is not null;

-- ── indexer_state — block cursor for the index-delegations cron ──────────────
create table if not exists indexer_state (
    contract           text    not null,
    chain_id           int     not null,
    last_indexed_block bigint  not null default 0,
    updated_at         timestamptz not null default now(),
    primary key (contract, chain_id)
);

-- ── dca_strategies — DCA schedule configs ───────────────────────────────────
create table if not exists dca_strategies (
    id                      uuid primary key default gen_random_uuid(),
    agent_id                uuid not null,
    delegation_id           uuid not null,
    chain_id                integer not null default 84532,
    token_in                text not null,
    token_out               text not null,
    token_out_symbol        text not null default 'WETH',
    amount_per_execution    text not null,
    period_seconds          integer not null,
    slippage_bps            integer not null default 50,
    status                  text not null default 'active',
    next_execution_at       timestamptz not null,
    last_execution_at       timestamptz,
    created_at              timestamptz not null default now(),
    cancelled_at            timestamptz,

    constraint dca_strategies_status_check
        check (status in ('active', 'paused', 'expired', 'cancelled')),
    constraint dca_strategies_chain_id_check
        check (chain_id > 0),
    constraint dca_strategies_slippage_check
        check (slippage_bps between 1 and 500),
    constraint dca_strategies_period_check
        check (period_seconds in (86400, 604800))
);

create index if not exists idx_dca_strategies_agent on dca_strategies(agent_id);
create index if not exists idx_dca_strategies_next_exec on dca_strategies(next_execution_at) where status = 'active';

-- Lifecycle columns (migration 20260813191500_recurring_payment_lifecycle.sql):
-- same contract as agent_subscriptions above.
alter table dca_strategies add column if not exists consecutive_failures integer not null default 0;
alter table dca_strategies add column if not exists last_error           text;
alter table dca_strategies add column if not exists last_error_code      text;
alter table dca_strategies add column if not exists paused_at            timestamptz;
alter table dca_strategies add column if not exists resumed_at           timestamptz;

-- ── dca_executions — per-cron swap attempt log ───────────────────────────────
create table if not exists dca_executions (
    id                      uuid primary key default gen_random_uuid(),
    strategy_id             uuid not null references dca_strategies(id) on delete cascade,
    chain_id                integer not null,
    tx_hash                 text,
    amount_in               text not null,
    quote_amount_out        text,
    amount_out              text,
    slippage_bps_used       integer,
    quote_divergence_bps    integer,
    status                  text not null default 'pending',
    error                   text,
    executed_at             timestamptz not null default now(),

    constraint dca_executions_status_check
        check (status in ('pending', 'success', 'failed', 'aborted'))
);

create index if not exists idx_dca_executions_strategy on dca_executions(strategy_id);

-- ── subscriptions — platform plan subscriptions paid on-chain ────────────────
-- chain_type: 'evm' | 'solana'. One active row per user (upserted on payment).
-- EVM payments use USDC on any supported chain; Solana payments use SPL USDC.
create table if not exists subscriptions (
    id              uuid primary key default gen_random_uuid(),
    user_id         uuid not null references users(id) on delete cascade,
    plan            text not null check (plan in ('pro', 'team', 'enterprise')),
    chain_type      text not null check (chain_type in ('evm', 'solana')),
    chain_id        integer,                -- EVM chain ID; null for Solana
    token_address   text,                  -- EVM: USDC contract; Solana: USDC mint address
    tx_hash         text,                  -- most recent payment tx hash / signature
    amount_usd      numeric(12,2),         -- USD value at time of payment
    status          text not null default 'active' check (status in ('active','expired','cancelled')),
    active_until    timestamptz not null,
    created_at      timestamptz not null default now(),
    updated_at      timestamptz not null default now(),
    cancelled_at    timestamptz
);

create unique index if not exists subscriptions_user_active
    on subscriptions(user_id) where status = 'active';
create index if not exists subscriptions_expiry
    on subscriptions(active_until) where status = 'active';

do $$ begin
    create trigger subscriptions_set_updated_at before update on subscriptions
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

-- ── plan_payment_intents — tracks checkout sessions before on-chain confirmation ──
-- Created when user initiates checkout; confirmed when tx lands on-chain.
create table if not exists plan_payment_intents (
    id              uuid primary key default gen_random_uuid(),
    user_id         uuid not null references users(id) on delete cascade,
    plan            text not null check (plan in ('pro', 'team', 'enterprise')),
    chain_type      text not null check (chain_type in ('evm', 'solana')),
    chain_id        integer,
    amount_usdc     numeric(12,6) not null, -- USD value charged (equals the USDC amount on USDC intents)
    asset           text not null default 'USDC' check (asset in ('USDC', 'SOL', 'THREE')),
    amount_asset    numeric(30,9),          -- exact on-chain amount of `asset` expected, human units (null on legacy USDC rows)
    asset_price_usd numeric(18,9),          -- USD price of one unit of `asset` at quote time (null for USDC)
    recipient       text not null,          -- address/pubkey that should receive payment
    nonce           text not null unique,   -- random, prevents replay
    memo            text,                   -- Solana Pay memo / EVM calldata hint
    status          text not null default 'pending' check (status in ('pending','confirmed','expired','failed')),
    tx_hash         text,
    created_at      timestamptz not null default now(),
    expires_at      timestamptz not null,
    confirmed_at    timestamptz
);

create index if not exists payment_intents_user on plan_payment_intents(user_id);
create index if not exists payment_intents_expiry on plan_payment_intents(expires_at) where status = 'pending';
create index if not exists payment_intents_nonce on plan_payment_intents(nonce);

-- ── email_verifications — short-lived numeric codes for email verification ──
-- Code is stored hashed. Latest unconsumed row per user is the active one.
create table if not exists email_verifications (
    id           uuid primary key default gen_random_uuid(),
    user_id      uuid not null references users(id) on delete cascade,
    code_hash    text not null,             -- sha256 of the 6-digit code
    expires_at   timestamptz not null,
    consumed_at  timestamptz,
    attempts     int not null default 0,
    created_at   timestamptz not null default now()
);

create index if not exists email_verifications_user on email_verifications(user_id) where consumed_at is null;
create index if not exists email_verifications_expiry on email_verifications(expires_at);

-- ── password_resets — single-use tokens for password reset flow ─────────────
-- Token is stored hashed; raw value is delivered via email link only.
create table if not exists password_resets (
    id           uuid primary key default gen_random_uuid(),
    user_id      uuid not null references users(id) on delete cascade,
    token_hash   text not null unique,       -- sha256 of the random token
    expires_at   timestamptz not null,
    consumed_at  timestamptz,
    created_at   timestamptz not null default now()
);

create index if not exists password_resets_user on password_resets(user_id) where consumed_at is null;
create index if not exists password_resets_expiry on password_resets(expires_at);

-- ── user_prefs — per-user UI preferences (dashboard layout, filters, etc.) ──
create table if not exists user_prefs (
    user_id     uuid primary key references users(id) on delete cascade,
    prefs       jsonb not null default '{}'::jsonb,
    updated_at  timestamptz not null default now()
);

-- ── pumpfun_signals — off-chain pump.fun activity signals attached to a wallet ─
-- Not an on-chain attestation — these are crawled from the upstream
-- pumpfun-claims-bot enrichment pipeline. Used to weight Solana reputation
-- and surface trust signals on the agent passport.
create table if not exists pumpfun_signals (
    id                bigserial primary key,
    wallet            text,                          -- claimer / creator / buyer base58 pubkey (null for mint-attributed signals)
    agent_asset       text,                          -- linked Solana agent if known
    kind              text        not null,         -- 'first_claim' | 'fake_claim' | 'graduation' | 'influencer' | 'new_account' | 'whale_buy' | 'launch'
    weight            real        not null default 0,-- reputation impact, -1..1
    payload           jsonb       not null default '{}'::jsonb,
    tx_signature      text,
    seen_at           timestamptz not null default now(),
    -- One row per (transaction, signal kind): a single tx can legitimately
    -- carry several signal kinds (e.g. first_claim + influencer + new_account).
    constraint pumpfun_signals_tx_kind_key unique (tx_signature, kind)
);
create index if not exists pumpfun_signals_wallet  on pumpfun_signals(wallet, seen_at desc);
create index if not exists pumpfun_signals_agent   on pumpfun_signals(agent_asset, seen_at desc) where agent_asset is not null;
create index if not exists pumpfun_signals_kind    on pumpfun_signals(kind, seen_at desc);
create index if not exists pumpfun_signals_seen_at on pumpfun_signals(seen_at desc);

-- Per-source crawl cursor for the pumpfun-signals cron. Keeps the sweep from
-- re-evaluating the whole recent-events window every run; lives in Postgres to
-- avoid spending Upstash write quota (tasks/redis-burn-rate-reduction.md).
create table if not exists pumpfun_signals_cursor (
    source         text        primary key,         -- 'claims' | 'whales' | 'mints' | 'graduations'
    last_seen_ms   bigint      not null default 0,
    last_signature text,
    updated_at     timestamptz not null default now()
);

-- ── marketplace_skills — community skill registry ────────────────────────────
-- Two flavors:
--   • tool skills    — schema_json: jsonb array matching the ToolPack schema used in chat/src/tools.js.
--                      Each element: { clientDefinition: {...}, type, function: { name, description, parameters } }
--   • content skills — content: markdown knowledge injected into the system prompt (no tool schema).
-- Every row must have at least one of `schema_json` or `content` (enforced by check constraint).
create table if not exists marketplace_skills (
    id            uuid primary key default gen_random_uuid(),
    author_id     uuid references users(id) on delete set null,
    name          text not null,
    slug          text not null unique,
    description   text not null,
    category      text not null default 'general',
    schema_json   jsonb,
    content       text,
    tags          text[] not null default '{}',
    is_public     boolean not null default true,
    install_count integer not null default 0,
    created_at    timestamptz not null default now(),
    updated_at    timestamptz not null default now(),
    constraint marketplace_skills_has_payload check (schema_json is not null or content is not null)
);

create index if not exists marketplace_skills_category_idx on marketplace_skills(category);
create index if not exists marketplace_skills_author_idx   on marketplace_skills(author_id);
create index if not exists marketplace_skills_popular_idx  on marketplace_skills(install_count desc);
create index if not exists marketplace_skills_new_idx      on marketplace_skills(created_at desc);

do $$ begin
    create trigger marketplace_skills_set_updated_at before update on marketplace_skills
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

-- ── skill_installs — tracks which users have installed which skills ───────────
create table if not exists skill_installs (
    id           uuid primary key default gen_random_uuid(),
    user_id      uuid references users(id) on delete cascade,
    skill_id     uuid references marketplace_skills(id) on delete cascade,
    installed_at timestamptz not null default now(),
    unique (user_id, skill_id)
);

create index if not exists skill_installs_user_idx on skill_installs(user_id);

-- ── skill_ratings ────────────────────────────────────────────────────────────
create table if not exists skill_ratings (
    id         uuid primary key default gen_random_uuid(),
    user_id    uuid references users(id) on delete cascade,
    skill_id   uuid references marketplace_skills(id) on delete cascade,
    rating     smallint not null check (rating between 1 and 5),
    created_at timestamptz not null default now(),
    unique (user_id, skill_id)
);

-- ── agent_payments — autonomous agent-to-agent / agent-to-skill payment ledger ─
-- Inserted by skill-runtime after each paid skill call. status transitions:
--   pending → confirmed (on-chain tx broadcast succeeded)
--   pending → failed    (insufficient balance or tx error)
create table if not exists agent_payments (
    id              uuid        primary key default gen_random_uuid(),
    payer_agent_id  uuid        not null references agent_identities(id),
    payee_agent_id  uuid        references agent_identities(id),   -- null when paying a skill author
    skill_id        uuid        references marketplace_skills(id),
    amount_wei      numeric(40) not null,
    chain_id        integer     not null,
    tx_hash         text,
    memo            text,
    status          text        not null default 'pending',
    created_at      timestamptz not null default now(),
    constraint agent_payments_status_check
        check (status in ('pending', 'confirmed', 'failed'))
);

create index if not exists agent_payments_payer_time
    on agent_payments(payer_agent_id, created_at desc);
create index if not exists agent_payments_payee_time
    on agent_payments(payee_agent_id, created_at desc) where payee_agent_id is not null;
create index if not exists agent_payments_status
    on agent_payments(status, created_at desc);

-- Additive migration: royalty pricing on skills.
alter table marketplace_skills add column if not exists price_per_call_usd numeric(10,6) not null default 0;
alter table marketplace_skills add column if not exists rails text[] not null default '{x402,a2a}';

-- ── royalty_ledger — per-call micro-payment records ──────────────────────────
create table if not exists royalty_ledger (
    id             uuid        primary key default gen_random_uuid(),
    skill_id       uuid        not null references marketplace_skills(id) on delete cascade,
    -- nullable: /api/x402/skill-call callers are paying wallets, not agents
    agent_id       uuid        references agent_identities(id) on delete cascade,
    author_user_id uuid        not null references users(id) on delete cascade,
    price_usd      numeric(10,6) not null,
    status         text        not null default 'pending',
    settled_at     timestamptz,
    tx_hash        text,
    -- settlement provenance (x402 rail): which network, which paying wallet,
    -- which lane accrued the row, and the platform's cut in USD
    network          text,
    payer            text,
    source           text        not null default 'skill-runtime',
    platform_fee_usd numeric(10,6),
    created_at     timestamptz not null default now(),
    constraint royalty_ledger_status_check check (status in ('pending', 'settling', 'settled', 'failed'))
);

create index if not exists royalty_ledger_author_idx on royalty_ledger(author_user_id, created_at desc);
create index if not exists royalty_ledger_agent_idx  on royalty_ledger(agent_id, created_at desc);

-- ── subscription_plans — creator monetization plans ───────────────────────────
-- Distinct from the `subscriptions` table (platform billing). These are
-- creator-to-fan recurring payment plans.
create table if not exists subscription_plans (
    id           uuid        primary key default gen_random_uuid(),
    creator_id   uuid        not null references users(id) on delete cascade,
    agent_id     uuid        references agent_identities(id) on delete set null,
    name         text        not null,
    price_usd    numeric(8,2) not null check (price_usd >= 0.99),
    interval     text        not null default 'monthly' check (interval in ('weekly','monthly')),
    perks        text[],
    included_skills text[]   not null default '{}',
    active       boolean     not null default true,
    created_at   timestamptz not null default now()
);

create index if not exists subscription_plans_creator_idx
    on subscription_plans(creator_id) where active = true;
create index if not exists subscription_plans_agent_idx
    on subscription_plans(agent_id) where agent_id is not null and active = true;

-- ── creator_subscriptions — fan subscriptions to creator plans ────────────────
create table if not exists creator_subscriptions (
    id                   uuid        primary key default gen_random_uuid(),
    plan_id              uuid        not null references subscription_plans(id),
    subscriber_user_id   uuid        not null references users(id) on delete cascade,
    status               text        not null default 'active' check (status in ('active','paused','cancelled','past_due')),
    current_period_start timestamptz not null default now(),
    current_period_end   timestamptz not null,
    payment_method       text        not null default 'x402',
    wallet_address       text,
    created_at           timestamptz not null default now(),
    cancelled_at         timestamptz,
    unique(plan_id, subscriber_user_id)
);

create index if not exists creator_subscriptions_subscriber_idx
    on creator_subscriptions(subscriber_user_id) where status = 'active';
create index if not exists creator_subscriptions_plan_idx
    on creator_subscriptions(plan_id);
create index if not exists creator_subscriptions_due_idx
    on creator_subscriptions(current_period_end) where status = 'active';

-- ── subscription_payments — per-period payment records ────────────────────────
create table if not exists subscription_payments (
    id              uuid        primary key default gen_random_uuid(),
    subscription_id uuid        not null references creator_subscriptions(id),
    amount_usd      numeric(8,2) not null,
    status          text        not null default 'pending' check (status in ('pending','succeeded','failed')),
    tx_hash         text,
    paid_at         timestamptz,
    period_end      timestamptz,
    created_at      timestamptz not null default now()
);

create index if not exists subscription_payments_subscription_idx
    on subscription_payments(subscription_id);

-- At most one pending charge per (subscription, billing period) — see
-- migration 20260621122000_subscription-payment-idempotency.sql.
create unique index if not exists subscription_payments_one_pending_per_period
    on subscription_payments (subscription_id, period_end)
    where status = 'pending' and period_end is not null;

-- ── social_connections ────────────────────────────────────────────────────────
create table if not exists social_connections (
    id           uuid        primary key default gen_random_uuid(),
    user_id      uuid        not null references users(id) on delete cascade,
    provider     text        not null,
    provider_uid text        not null,
    username     text        not null,
    access_token text        not null,
    scopes       text        not null,
    connected_at timestamptz not null default now(),
    unique(user_id, provider)
);

create index if not exists social_connections_user_idx on social_connections(user_id);

-- Additive migrations for social_connections added after initial deployment.
alter table social_connections add column if not exists refresh_token    text;
alter table social_connections add column if not exists expires_at       timestamptz;
alter table social_connections add column if not exists raw_data         jsonb not null default '{}'::jsonb;
alter table social_connections add column if not exists disconnected_at  timestamptz;
alter table social_connections add column if not exists updated_at       timestamptz not null default now();
-- provider_uid holds the provider's user ID (e.g. Twitter numeric ID)

-- Additive migrations for agent_identities — X social seeding
alter table agent_identities add column if not exists x_username   text;
alter table agent_identities add column if not exists x_seeded_at  timestamptz;

-- ── scene_gates ───────────────────────────────────────────────────────────────
-- Token-gated scene shares. Visitors must prove wallet ownership before loading.
create table if not exists scene_gates (
    id           text primary key,
    user_id      uuid references users(id),
    scene_ref    text not null,
    chain        text not null check (chain in ('solana','evm')),
    kind         text not null check (kind in ('spl','collection','erc20','erc721')),
    address      text not null,
    min_balance  numeric not null default 1,
    created_at   timestamptz not null default now()
);

-- ── gate_nonces ───────────────────────────────────────────────────────────────
-- One-time nonces for gate-check wallet signature verification.
create table if not exists gate_nonces (
    nonce       text primary key,
    gate_id     text not null references scene_gates(id) on delete cascade,
    address     text not null,
    expires_at  timestamptz not null,
    consumed_at timestamptz
);

create index if not exists gate_nonces_expiry  on gate_nonces(expires_at);
create index if not exists gate_nonces_gate_id on gate_nonces(gate_id);

-- ── plugins — LobeHub/pai-chat compatible plugin marketplace ─────────────────
-- manifest_json matches ToolManifest from pai-chat:
--   { identifier, meta:{title,...}, api[], systemRole?, type?, settings?, ... }
create table if not exists plugins (
    id            uuid         primary key default gen_random_uuid(),
    author_id     uuid         references users(id) on delete set null,
    identifier    text         not null,
    manifest_url  text,
    manifest_json jsonb        not null,
    name          text         not null,
    description   text         not null default '',
    category      text         not null default 'tools',
    tags          text[]       not null default '{}',
    is_public     boolean      not null default true,
    install_count integer      not null default 0,
    avg_rating    numeric(3,2) not null default 0,
    rating_count  integer      not null default 0,
    deleted_at    timestamptz,
    created_at    timestamptz  not null default now(),
    updated_at    timestamptz  not null default now(),
    unique (identifier, author_id)
);

create index if not exists plugins_category_idx   on plugins(category);
create index if not exists plugins_author_idx     on plugins(author_id);
create index if not exists plugins_popular_idx    on plugins(install_count desc);
create index if not exists plugins_new_idx        on plugins(created_at desc);
create index if not exists plugins_identifier_idx on plugins(identifier);

do $$ begin
    create trigger plugins_set_updated_at before update on plugins
        for each row execute function set_updated_at();
exception when duplicate_object then null; end $$;

-- ── pumpfun_graduations — persisted pump.fun → AMM migration events ──────────
-- See api/_lib/migrations/2026-05-04-pumpfun-graduations.sql for the full schema.
create table if not exists pumpfun_graduations (
    tx_signature        text        primary key,
    mint                text        not null,
    name                text,
    symbol              text,
    creator             text,
    pool                text,
    raydium_pool        text,
    pump_swap_pool      text,
    market_cap_usd      double precision,
    market_cap_usd_initial double precision,
    ath_market_cap      double precision,
    amount_sol          double precision,
    amount_usd          double precision,
    sol_price           double precision,
    image_uri           text,
    description         text,
    twitter             text,
    telegram            text,
    website             text,
    creator_launches    integer,
    creator_graduated   integer,
    payload             jsonb       not null default '{}'::jsonb,
    seen_at             timestamptz not null default now()
);
create index if not exists pumpfun_graduations_seen_at on pumpfun_graduations(seen_at desc);
create index if not exists pumpfun_graduations_mint on pumpfun_graduations(mint);
create index if not exists pumpfun_graduations_creator on pumpfun_graduations(creator) where creator is not null;

-- ── agent_skill_prices ───────────────────────────────────────────────────────
-- Per-skill price set by the agent owner. Authoritative source of truth for
-- "is this skill paid, and how much?". See migration 2026-04-30-agent-monetization.sql.
CREATE TABLE IF NOT EXISTS agent_skill_prices (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agent_id          UUID NOT NULL REFERENCES agent_identities(id) ON DELETE CASCADE,
    skill             TEXT NOT NULL,
    currency_mint     TEXT NOT NULL,
    chain             TEXT NOT NULL DEFAULT 'solana',
    amount            BIGINT NOT NULL,
    is_active         BOOLEAN NOT NULL DEFAULT true,
    mint_decimals     SMALLINT NOT NULL DEFAULT 6,                 -- A2 cached mint decimals
    trial_uses        INTEGER  NOT NULL DEFAULT 0,                 -- C2 trial allowance per buyer
    time_pass_hours   INTEGER,                                     -- C3 time-pass duration
    time_pass_amount  BIGINT,                                      -- C3 time-pass price
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (agent_id, skill)
);

CREATE INDEX IF NOT EXISTS agent_skill_prices_agent_id ON agent_skill_prices(agent_id);

-- ── skill_purchases ─────────────────────────────────────────────────────────
-- Per-buyer skill ownership ledger. Pending row is created when buyer initiates
-- the Solana Pay flow; status flips to 'confirmed' once on-chain transfer is
-- verified by /api/marketplace/purchase/:reference/confirm or by the
-- /api/webhooks/solana-pay endpoint. See migration 2026-05-10-skill-purchases.sql.
CREATE TABLE IF NOT EXISTS skill_purchases (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id          UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    agent_id         UUID NOT NULL REFERENCES agent_identities(id) ON DELETE CASCADE,
    skill            TEXT NOT NULL,
    status           TEXT NOT NULL DEFAULT 'pending'
                       CHECK (status IN ('pending', 'confirmed', 'failed', 'expired', 'tipped', 'trial')),
    reference        TEXT NOT NULL UNIQUE,
    tx_signature     TEXT UNIQUE,
    amount           BIGINT NOT NULL,
    currency_mint    TEXT NOT NULL,
    chain            TEXT NOT NULL DEFAULT 'solana',
    kind             TEXT NOT NULL DEFAULT 'purchase'
                       CHECK (kind IN ('purchase', 'trial', 'time_pass')),
    expires_at       TIMESTAMPTZ,                                 -- A3 pending TTL
    valid_until      TIMESTAMPTZ,                                 -- C3 time-bounded access (time_pass)
    trial_remaining  INTEGER,                                     -- C2 trial counter
    tipped_amount    BIGINT,                                      -- A6 mismatch-as-tip
    platform_fee_amount BIGINT NOT NULL DEFAULT 0,                -- platform fee split off on-chain (atomic units)
    platform_fee_wallet TEXT,                                     -- treasury wallet the fee leg pays
    referrer_user_id UUID REFERENCES users(id),                   -- C6 referral attribution
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    confirmed_at     TIMESTAMPTZ
);

-- 'confirmed' or 'trial' both count as active ownership for uniqueness.
CREATE UNIQUE INDEX IF NOT EXISTS skill_purchases_one_active_per_user
    ON skill_purchases (user_id, agent_id, skill)
    WHERE status IN ('confirmed', 'trial');

CREATE INDEX IF NOT EXISTS skill_purchases_user_agent       ON skill_purchases (user_id, agent_id);
CREATE INDEX IF NOT EXISTS skill_purchases_agent            ON skill_purchases (agent_id);
CREATE INDEX IF NOT EXISTS skill_purchases_status_created   ON skill_purchases (status, created_at DESC);
CREATE INDEX IF NOT EXISTS skill_purchases_expires_at
    ON skill_purchases (expires_at)
    WHERE status = 'pending' AND expires_at IS NOT NULL;

-- Skill-ownership NFT minted to the buyer after confirmation (perpetual on-chain
-- receipt + license). One NFT per confirmed purchase. See migration
-- 20260617130000_skill_nft_mints.sql and api/skills/mint.js.
ALTER TABLE skill_purchases ADD COLUMN IF NOT EXISTS skill_nft_mint      TEXT;
ALTER TABLE skill_purchases ADD COLUMN IF NOT EXISTS skill_nft_signature TEXT;
ALTER TABLE skill_purchases ADD COLUMN IF NOT EXISTS skill_nft_network   TEXT;
ALTER TABLE skill_purchases ADD COLUMN IF NOT EXISTS skill_nft_minted_at TIMESTAMPTZ;
CREATE UNIQUE INDEX IF NOT EXISTS skill_purchases_skill_nft_mint
    ON skill_purchases (skill_nft_mint)
    WHERE skill_nft_mint IS NOT NULL;

-- ── purchase_receipts ────────────────────────────────────────────────────────
-- Append-only signed receipts for confirmed skill purchases. See
-- api/_lib/purchase-confirm.js and migration 2026-05-10-monetization-v2.sql.
CREATE TABLE IF NOT EXISTS purchase_receipts (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_id   UUID NOT NULL REFERENCES skill_purchases(id) ON DELETE CASCADE,
    receipt_json  JSONB NOT NULL,
    signature     TEXT NOT NULL,                                  -- HMAC-SHA256 over canonical receipt_json
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (purchase_id)
);

CREATE INDEX IF NOT EXISTS purchase_receipts_created_at
    ON purchase_receipts (created_at DESC);

-- ── purchase_events ──────────────────────────────────────────────────────────
-- Funnel telemetry for the purchase lifecycle.
CREATE TABLE IF NOT EXISTS purchase_events (
    id          BIGSERIAL PRIMARY KEY,
    purchase_id UUID REFERENCES skill_purchases(id) ON DELETE CASCADE,
    event       TEXT NOT NULL,
    payload     JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS purchase_events_purchase
    ON purchase_events (purchase_id, created_at DESC);

CREATE INDEX IF NOT EXISTS purchase_events_event_time
    ON purchase_events (event, created_at DESC);

-- ── csrf_tokens ──────────────────────────────────────────────────────────────
-- Lightweight double-submit cookie tokens (A5). See api/_lib/csrf.js. Expired
-- rows are pruned by the cron job in api/cron/[name].js.
CREATE TABLE IF NOT EXISTS csrf_tokens (
    token       TEXT PRIMARY KEY,
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at  TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS csrf_tokens_user
    ON csrf_tokens (user_id, expires_at DESC);

CREATE INDEX IF NOT EXISTS csrf_tokens_expires
    ON csrf_tokens (expires_at);

-- ── agent_payment_intents ────────────────────────────────────────────────────
-- Generic payment intent (subscriptions, one-shot, x402 invocations). Skill
-- purchases get a synthetic intent with id 'sp_<skill_purchase_id>' on confirm
-- so agent_revenue_events.intent_id FK can point at it.
CREATE TABLE IF NOT EXISTS agent_payment_intents (
    id             TEXT PRIMARY KEY,
    payer_user_id  UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    agent_id       UUID NOT NULL REFERENCES agent_identities(id) ON DELETE CASCADE,
    currency_mint  TEXT NOT NULL,
    amount         TEXT NOT NULL,
    memo           TEXT,
    start_time     TIMESTAMPTZ,
    end_time       TIMESTAMPTZ,
    status         TEXT NOT NULL DEFAULT 'pending',
    cluster        TEXT,
    tx_signature   TEXT,
    paid_at        TIMESTAMPTZ,
    payload        JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at     TIMESTAMPTZ NOT NULL
);

-- ── paid_assets — "buy once, re-download forever" 3D-asset catalog ──────────
-- Mirror of api/_lib/migrations/2026-05-21-paid-assets.sql. Backs the
-- /api/x402/asset-download endpoint: creators upload a GLB/avatar/accessory
-- to R2, price it in USDC atomics. Per-row payout overrides let creators
-- receive USDC directly to their own wallet; NULL falls back to env X402_PAY_TO_*.
CREATE TABLE IF NOT EXISTS paid_assets (
    id                   uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    slug                 text        NOT NULL UNIQUE,
    title                text        NOT NULL,
    description          text        NOT NULL,
    mime_type            text        NOT NULL,
    size_bytes           bigint      NOT NULL,
    r2_key               text        NOT NULL,
    price_atomics        text        NOT NULL,
    creator_payto_base   text,
    creator_payto_solana text,
    creator_payto_bsc    text,
    created_at           timestamptz NOT NULL DEFAULT now(),
    glb_sha256           text
);
CREATE INDEX IF NOT EXISTS paid_assets_slug_idx ON paid_assets (slug);

-- Avatar thumbnail regeneration freshness tracking + queue.
-- Mirror of api/_lib/migrations/2026-06-27-avatar-thumbnail-regen.sql. The
-- autonomous x402 loop pays asset-download for the stalest listing and queues a
-- re-render; the drainer cron (api/cron/avatar-thumbnail-render.js) renders the
-- current GLB to a fresh PNG and writes it back so listings show current state.
ALTER TABLE paid_assets ADD COLUMN IF NOT EXISTS thumbnail_r2_key       text;
ALTER TABLE paid_assets ADD COLUMN IF NOT EXISTS thumbnail_generated_at timestamptz;
ALTER TABLE paid_assets ADD COLUMN IF NOT EXISTS source_updated_at      timestamptz;
ALTER TABLE paid_assets ADD COLUMN IF NOT EXISTS avatar_id              uuid REFERENCES avatars(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS avatar_thumbnail_regen_jobs (
    id                 bigserial   PRIMARY KEY,
    asset_id           uuid        REFERENCES paid_assets(id) ON DELETE CASCADE,
    asset_slug         text        NOT NULL,
    avatar_id          uuid        REFERENCES avatars(id) ON DELETE SET NULL,
    r2_key             text        NOT NULL,
    run_id             uuid,
    x402_tx_signature  text,
    amount_atomic      bigint      NOT NULL DEFAULT 0,
    status             text        NOT NULL DEFAULT 'queued'
                                   CHECK (status IN ('queued','rendering','done','failed')),
    thumbnail_r2_key   text,
    width              int         NOT NULL DEFAULT 768,
    height             int         NOT NULL DEFAULT 768,
    attempts           int         NOT NULL DEFAULT 0,
    error              text,
    reason             text,
    created_at         timestamptz NOT NULL DEFAULT now(),
    updated_at         timestamptz NOT NULL DEFAULT now(),
    rendered_at        timestamptz
);
CREATE INDEX IF NOT EXISTS avatar_thumbnail_regen_jobs_status_idx
    ON avatar_thumbnail_regen_jobs (status, created_at);
CREATE UNIQUE INDEX IF NOT EXISTS avatar_thumbnail_regen_jobs_open_uniq
    ON avatar_thumbnail_regen_jobs (asset_slug)
    WHERE status IN ('queued','rendering');

-- ── siwx_payments + siwx_nonces — Sign-In-With-X (CAIP-122) payment history ─
-- Mirror of api/_lib/migrations/2026-05-21-siwx.sql. A wallet that paid for a
-- resource can re-access it by signing CAIP-122 instead of re-paying. Stored
-- addresses follow CAIP-122 payload format exactly (lowercase hex EVM,
-- base58 Solana); siwx-storage.js normalizes before SELECT/INSERT.
CREATE TABLE IF NOT EXISTS siwx_payments (
    resource     text        NOT NULL,
    address      text        NOT NULL,
    network      text        NOT NULL,
    paid_at      timestamptz NOT NULL DEFAULT now(),
    expires_at   timestamptz,
    last_used_at timestamptz,
    use_count    integer     NOT NULL DEFAULT 0,
    PRIMARY KEY (resource, address)
);
CREATE INDEX IF NOT EXISTS siwx_payments_expires_idx
    ON siwx_payments (expires_at) WHERE expires_at IS NOT NULL;

CREATE TABLE IF NOT EXISTS siwx_nonces (
    nonce    text        PRIMARY KEY,
    resource text        NOT NULL,
    address  text        NOT NULL,
    used_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS siwx_nonces_used_at_idx ON siwx_nonces (used_at);

-- ── x402_subscriptions + x402_access_log — subscription keys + bypass audit ─
-- Mirror of api/_lib/migrations/2026-05-21-x402-subscriptions.sql. The
-- installAccessControl() onProtectedRequest hook short-circuits the 402
-- challenge when a request carries INTERNAL_API_KEY, a subscription key, or
-- an OAuth bearer with the required scope. Every bypass and abort writes
-- to x402_access_log so audit dashboards can reconstruct who used what.
CREATE TABLE IF NOT EXISTS x402_subscriptions (
    id                    text        PRIMARY KEY,
    name                  text        NOT NULL,
    key_hash              text        NOT NULL UNIQUE,
    key_prefix            text        NOT NULL,
    rate_limit_per_minute integer     NOT NULL DEFAULT 60,
    expires_at            timestamptz,
    revoked_at            timestamptz,
    meta                  jsonb,
    created_by            uuid        REFERENCES users(id) ON DELETE SET NULL,
    created_at            timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS x402_subscriptions_prefix_idx ON x402_subscriptions(key_prefix);
CREATE INDEX IF NOT EXISTS x402_subscriptions_active_idx ON x402_subscriptions(revoked_at, expires_at);

CREATE TABLE IF NOT EXISTS x402_access_log (
    id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    caller_id   text        NOT NULL,
    route       text        NOT NULL,
    reason      text        NOT NULL,
    granted     boolean     NOT NULL,
    meta        jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS x402_access_log_caller_idx ON x402_access_log(caller_id, created_at desc);
CREATE INDEX IF NOT EXISTS x402_access_log_route_idx ON x402_access_log(route, created_at desc);
CREATE INDEX IF NOT EXISTS x402_access_log_created_idx ON x402_access_log(created_at desc);

-- ── club_tips — Pole Club live-tip ledger ───────────────────────────────────
-- Mirror of api/_lib/migrations/2026-05-22-club-tips.sql. Every settled
-- /api/x402/dance-tip payment writes one row here; /api/club/tips reads it
-- for the page boot, /api/club/tips/stream tails for SSE. paid_at/paid_tx
-- are reserved for the dancer-payouts sweep.
CREATE TABLE IF NOT EXISTS club_tips (
    id              uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    ticket_id       text        NOT NULL UNIQUE,
    dancer          text        NOT NULL,
    dance           text        NOT NULL,
    clip            text,
    label           text,
    payer           text,
    network         text,
    amount_atomics  numeric,
    asset           text,
    started_at      timestamptz NOT NULL,
    ends_at         timestamptz NOT NULL,
    paid_at         timestamptz,
    paid_tx         text,
    created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS club_tips_created_at_desc ON club_tips (created_at desc);
CREATE INDEX IF NOT EXISTS club_tips_dancer_created ON club_tips (dancer, created_at desc);

-- ── club_dancer_wallets + club_payouts — Pole Club payout sweep ─────────────
-- Mirror of api/_lib/migrations/2026-05-23-club-dancer-wallets.sql. Each row
-- carries display metadata + two destination addresses (EVM Base 8453, Solana
-- mainnet) the cron sweep deposits accumulated tips into. /api/cron/club-payouts
-- is idempotent — re-running only sweeps tips with paid_at IS NULL.
CREATE TABLE IF NOT EXISTS club_dancer_wallets (
    dancer          text         PRIMARY KEY,
    display_name    text         NOT NULL,
    bio             text,
    evm_address     text,
    solana_address  text,
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now()
);
-- Partial index for the cron filter (unpaid tips grouped by dancer/network/asset).
CREATE INDEX IF NOT EXISTS club_tips_unpaid_by_dancer_net
    ON club_tips (dancer, network, asset)
    WHERE paid_at IS NULL;

CREATE TABLE IF NOT EXISTS club_payouts (
    id                uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    dancer            text        NOT NULL REFERENCES club_dancer_wallets(dancer),
    network           text        NOT NULL,
    asset             text        NOT NULL,
    amount_atomics    numeric     NOT NULL,
    tx                text        NOT NULL,
    swept_tip_count   integer     NOT NULL,
    created_at        timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS club_payouts_dancer_created
    ON club_payouts (dancer, created_at desc);

-- Seed the four built-in stage slots with display names hard-coded in
-- /api/x402/dance-tip and src/club.js. Addresses stay NULL until an admin
-- sets them; the cron logs + skips (dancer, network) pairs where missing.
INSERT INTO club_dancer_wallets (dancer, display_name) VALUES
    ('1', 'Nyx'),
    ('2', 'Ari'),
    ('3', 'Sable'),
    ('4', 'Vesper')
ON CONFLICT (dancer) DO UPDATE
    SET display_name = excluded.display_name,
        updated_at   = now();

-- ── x402_receipts — durable log of x402 offer-receipt artifacts ─────────────
-- Mirror of api/_lib/migrations/2026-05-24-x402-receipts.sql. Every successful
-- paid /api/x402/* call writes one row: the signed receipt we returned in the
-- SettlementResponse, the resource it covered, the payer wallet, and the chain.
-- Buyers query their own receipts via /api/x402/my-receipts (buyer-signed gate).
CREATE TABLE IF NOT EXISTS x402_receipts (
    id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    payer        text        NOT NULL,
    network      text        NOT NULL,
    resource_url text        NOT NULL,
    format       text        NOT NULL,
    receipt      jsonb       NOT NULL,
    transaction  text,
    issued_at    timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE x402_receipts ADD COLUMN IF NOT EXISTS rail text NOT NULL DEFAULT 'x402';
ALTER TABLE x402_receipts ADD COLUMN IF NOT EXISTS skill_id uuid;
ALTER TABLE x402_receipts ADD COLUMN IF NOT EXISTS author_atomics numeric;
ALTER TABLE x402_receipts ADD COLUMN IF NOT EXISTS platform_atomics numeric;
CREATE INDEX IF NOT EXISTS x402_receipts_payer_issued
    ON x402_receipts (payer, issued_at desc);
CREATE INDEX IF NOT EXISTS x402_receipts_resource_issued
    ON x402_receipts (resource_url, issued_at desc);

-- ── bsc_consumed_tx — anti-replay set for x402 direct payments on BSC ───────
-- Mirror of api/_lib/migrations/2026-05-25-bsc-consumed-tx.sql. Each
-- successfully verified BSC payment tx is recorded here so a replay across
-- Vercel cold starts or function replicas can't unlock the same resource
-- twice. The on-chain Payment(payer, amount, ref) event is uniquely keyed by
-- tx_hash; the table's PRIMARY KEY enforces single-consumption.
CREATE TABLE IF NOT EXISTS bsc_consumed_tx (
    tx_hash      text        PRIMARY KEY,
    ref          text,
    payer        text,
    amount       numeric,
    pay_to       text,
    consumed_at  timestamptz NOT NULL DEFAULT now()
);

-- Garbage-collect old rows. The Payment event is public on-chain so eventual
-- cleanup of records older than 30 days is safe; auditing can re-derive from
-- BSC archive nodes if needed.
CREATE INDEX IF NOT EXISTS bsc_consumed_tx_consumed_at
    ON bsc_consumed_tx (consumed_at);

-- ── agent_reviews — marketplace ratings ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS agent_reviews (
    id           uuid        NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    agent_id     uuid        NOT NULL REFERENCES agent_identities(id) ON DELETE CASCADE,
    user_id      uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    rating       int         NOT NULL CHECK (rating BETWEEN 1 AND 5),
    review       text,
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now(),
    UNIQUE (agent_id, user_id)
);
CREATE INDEX IF NOT EXISTS agent_reviews_agent_id ON agent_reviews(agent_id);
CREATE INDEX IF NOT EXISTS agent_reviews_user_id  ON agent_reviews(user_id);

-- ── x_triggers / x_scheduled_posts / x_pending_reviews — social automation ──
CREATE TABLE IF NOT EXISTS x_triggers (
    id             uuid        NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id        uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    agent_id       uuid        REFERENCES agent_identities(id) ON DELETE SET NULL,
    kind           text        NOT NULL,
    config         jsonb       NOT NULL DEFAULT '{}',
    auto_publish   boolean     NOT NULL DEFAULT false,
    enabled        boolean     NOT NULL DEFAULT true,
    last_fired_at  timestamptz,
    last_state     jsonb,
    created_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS x_triggers_user_enabled ON x_triggers(user_id) WHERE enabled;
CREATE INDEX IF NOT EXISTS x_triggers_agent_enabled ON x_triggers(agent_id) WHERE enabled;

-- Shape matches what /api/cron/run-x-scheduled-posts and /api/x/schedule read and
-- write (posted_at, tweet_id, error, attempts), not the vestigial published_at
-- column this file used to declare. See
-- api/_lib/migrations/20260814060000_x_social_publish_state.sql for why the two
-- generations diverged and how an already-provisioned database is reconciled.
CREATE TABLE IF NOT EXISTS x_scheduled_posts (
    id                uuid        NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id           uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    agent_id          text,
    text              text        NOT NULL,
    thread_parts      jsonb,
    reply_to_tweet_id text,
    scheduled_at      timestamptz NOT NULL,
    posted_at         timestamptz,
    tweet_id          text,
    error             text,
    attempts          int         NOT NULL DEFAULT 0,
    created_at        timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS x_scheduled_due_idx
    ON x_scheduled_posts(scheduled_at) WHERE posted_at IS NULL AND error IS NULL;
CREATE INDEX IF NOT EXISTS x_scheduled_user_idx
    ON x_scheduled_posts(user_id, scheduled_at DESC);

-- ── walk metrics / events / achievements ────────────────────────────────────
-- See api/_lib/migrations/20260621140000_walk_metrics.sql for the full rationale.
-- Mirrored here so a clean schema.sql apply provisions the walk leaderboard +
-- per-creator analytics pipeline. Ingest: POST /api/walk/metrics. Reads:
-- GET /api/walk/leaderboard and GET /api/walk/analytics.
create table if not exists walk_metrics (
    id              bigserial primary key,
    user_id         uuid references users(id) on delete cascade,
    anon_id         text,
    avatar_id       uuid references avatars(id) on delete set null,
    day             date not null,
    env_id          text,
    embed_origin    text,
    site_hostname   text,
    distance_meters double precision not null default 0,
    duration_sec    double precision not null default 0,
    sessions        integer not null default 0,
    updated_at      timestamptz not null default now(),
    created_at      timestamptz not null default now(),
    constraint walk_metrics_walker_present check (user_id is not null or anon_id is not null)
);
create unique index if not exists walk_metrics_rollup_uniq
    on walk_metrics (
        coalesce(user_id::text, ''),
        coalesce(anon_id, ''),
        day,
        coalesce(env_id, ''),
        coalesce(embed_origin, ''),
        coalesce(avatar_id::text, '')
    );
create index if not exists walk_metrics_user_day on walk_metrics (user_id, day) where user_id is not null;
create index if not exists walk_metrics_anon_day on walk_metrics (anon_id, day) where anon_id is not null;
create index if not exists walk_metrics_avatar_day on walk_metrics (avatar_id, day) where avatar_id is not null;
create index if not exists walk_metrics_origin on walk_metrics (avatar_id, embed_origin) where avatar_id is not null;

create table if not exists walk_events (
    id              bigserial primary key,
    user_id         uuid references users(id) on delete set null,
    anon_id         text,
    avatar_id       uuid references avatars(id) on delete set null,
    event_name      text not null,
    value           double precision,
    embed_origin    text,
    created_at      timestamptz not null default now()
);
create index if not exists walk_events_avatar_time on walk_events (avatar_id, created_at desc) where avatar_id is not null;
create index if not exists walk_events_avatar_name on walk_events (avatar_id, event_name) where avatar_id is not null;

create table if not exists walk_achievements (
    id              bigserial primary key,
    user_id         uuid references users(id) on delete cascade,
    anon_id         text,
    code            text not null,
    unlocked_at     timestamptz not null default now(),
    constraint walk_achievements_walker_present check (user_id is not null or anon_id is not null)
);
create unique index if not exists walk_achievements_uniq
    on walk_achievements (
        coalesce(user_id::text, ''),
        coalesce(anon_id, ''),
        code
    );
create index if not exists walk_achievements_user on walk_achievements (user_id) where user_id is not null;

-- ── walk programmatic-control sessions + command queue ───────────────────────
-- See api/_lib/migrations/20260621160000_walk_control.sql for the full rationale.
-- Mirrored here so a clean schema.sql apply provisions the walk control API.
-- Endpoint: api/walk/control/[action].js. The walk client opts in via
-- /walk?control=<sessionId>&ck=<controlToken>.
create table if not exists walk_control_sessions (
    id              uuid primary key default gen_random_uuid(),
    owner_id        uuid not null references users(id) on delete cascade,
    avatar_id       uuid references avatars(id) on delete set null,
    token_hash      text not null,
    label           text,
    env_id          text,
    pos_x           double precision,
    pos_z           double precision,
    facing          double precision,
    motion          text,
    current_env     text,
    client_seen_at  timestamptz,
    created_at      timestamptz not null default now(),
    expires_at      timestamptz not null
);
create index if not exists walk_control_sessions_owner
    on walk_control_sessions (owner_id, created_at desc);
create index if not exists walk_control_sessions_token
    on walk_control_sessions (token_hash);
create index if not exists walk_control_sessions_expires
    on walk_control_sessions (expires_at);

create table if not exists walk_control_commands (
    id              bigserial primary key,
    session_id      uuid not null references walk_control_sessions(id) on delete cascade,
    seq             bigint not null,
    kind            text not null check (kind in ('move','gesture','say','env')),
    payload         jsonb not null default '{}'::jsonb,
    dedup_key       text,
    created_at      timestamptz not null default now(),
    delivered_at    timestamptz
);
create index if not exists walk_control_commands_drain
    on walk_control_commands (session_id, seq)
    where delivered_at is null;
create unique index if not exists walk_control_commands_dedup
    on walk_control_commands (session_id, kind, dedup_key)
    where dedup_key is not null;

-- Shape matches what /api/x/reviews reads and writes (status + resolved_at), not
-- the vestigial approved/reviewed_at pair this file used to declare. See
-- api/_lib/migrations/20260814060000_x_social_publish_state.sql for why the two
-- generations diverged and how an already-provisioned database is reconciled.
CREATE TABLE IF NOT EXISTS x_pending_reviews (
    id           uuid        NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id      uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    trigger_id   uuid        REFERENCES x_triggers(id) ON DELETE SET NULL,
    agent_id     text,
    text         text        NOT NULL,
    thread_parts jsonb,
    status       text        NOT NULL DEFAULT 'pending'
                             CHECK (status IN ('pending', 'approved', 'rejected')),
    resolved_at  timestamptz,
    created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS x_pending_reviews_user_pending_idx
    ON x_pending_reviews(user_id, created_at DESC) WHERE status = 'pending';

-- ── club_tips — backfill amount_atomics ──────────────────────────────────────
ALTER TABLE club_tips ADD COLUMN IF NOT EXISTS amount_atomics numeric;

-- ── PWA & notifications (task 39) ────────────────────────────────────────────
-- Web Push endpoints, the unified preference center, the sent→opened→returned
-- funnel, and the double opt-in newsletter list. Full rationale lives in the
-- dated migration 20260628000000_push_and_notification_prefs.sql; mirrored here
-- so a clean schema.sql apply provisions the tables.
create table if not exists push_subscriptions (
    id           uuid primary key default gen_random_uuid(),
    user_id      uuid not null references users(id) on delete cascade,
    endpoint     text not null,
    p256dh       text not null,
    auth         text not null,
    user_agent   text,
    created_at   timestamptz not null default now(),
    last_seen_at timestamptz not null default now()
);
create unique index if not exists push_subscriptions_endpoint
    on push_subscriptions (endpoint);
create index if not exists push_subscriptions_user
    on push_subscriptions (user_id, created_at desc);

create table if not exists notification_preferences (
    user_id    uuid primary key references users(id) on delete cascade,
    prefs      jsonb not null default '{}'::jsonb,
    updated_at timestamptz not null default now()
);

create table if not exists notification_events (
    id              bigint generated always as identity primary key,
    notification_id uuid references user_notifications(id) on delete cascade,
    user_id         uuid not null references users(id) on delete cascade,
    channel         text not null check (channel in ('in_app','push','email','telegram')),
    event           text not null check (event in ('sent','opened','returned')),
    meta            jsonb not null default '{}'::jsonb,
    created_at      timestamptz not null default now()
);
create index if not exists notification_events_notif
    on notification_events (notification_id);
create index if not exists notification_events_funnel
    on notification_events (event, channel, created_at desc);
create unique index if not exists notification_events_once
    on notification_events (notification_id, channel, event)
    where notification_id is not null and event in ('opened','returned');

create table if not exists newsletter_subscribers (
    id            uuid primary key default gen_random_uuid(),
    email         citext not null unique,
    status        text not null default 'pending'
                  check (status in ('pending','confirmed','unsubscribed')),
    confirm_token text not null,
    locale        text,
    source        text,
    created_at    timestamptz not null default now(),
    confirmed_at  timestamptz,
    unsubbed_at   timestamptz
);
create index if not exists newsletter_subscribers_token
    on newsletter_subscribers (confirm_token);

-- ── genome_breedings — Agent Genome: provable breeding events + lineage ──────
-- Two agents breed into a child that provably inherits a recombination of both
-- parents' brain, voice, body, and skill alleles. Each row records the breeding
-- seed + derived genome so the descent is re-derivable and non-forgeable
-- (api/_lib/genome.js deriveGenome/verifyGenome). The child gets a fresh distinct
-- wallet (fork's ownership invariant, extended to two parents); the parents'
-- rows are never mutated by a breed. `breeding_key` is the idempotency key:
-- replaying the same key returns the same child instead of minting twins.
create table if not exists genome_breedings (
    id                 uuid primary key default gen_random_uuid(),
    breeding_key       text not null unique,
    parent_a_agent_id  uuid not null references agent_identities(id) on delete cascade,
    parent_b_agent_id  uuid not null references agent_identities(id) on delete cascade,
    child_agent_id     uuid references agent_identities(id) on delete set null,
    seed               text not null,
    genome             jsonb not null,
    genome_hash        text not null,
    generation         integer not null default 1,
    pedigree_tier      text not null default 'common',
    bred_by            uuid not null references users(id),
    stud_fee_lamports  bigint not null default 0,
    stud_fee_signature text,
    consent_owner      uuid references users(id),
    status             text not null default 'born'
                       check (status in ('pending','born','failed')),
    created_at         timestamptz not null default now()
);
create index if not exists genome_breedings_parent_a
    on genome_breedings (parent_a_agent_id, created_at desc);
create index if not exists genome_breedings_parent_b
    on genome_breedings (parent_b_agent_id, created_at desc);
create index if not exists genome_breedings_child
    on genome_breedings (child_agent_id) where child_agent_id is not null;
create index if not exists genome_breedings_bred_by
    on genome_breedings (bred_by, created_at desc);

-- ── Portable & Verifiable Brain (Living Agents · Task 06) ──────────────────────
-- See api/_lib/migrations/20260623230000_brain_ownership.sql for the full rationale.
-- Per-memory authorship (signed by the agent's EVM wallet), integrity hash, and
-- storage provenance; per-agent default storage mode; and the brain-anchor
-- history (content-addressed milestones recorded on the ERC-8004 registry).
alter table agent_memories add column if not exists content_hash   text;
alter table agent_memories add column if not exists signature       text;
alter table agent_memories add column if not exists signer_address  text;
alter table agent_memories add column if not exists signed_at       timestamptz;
alter table agent_memories add column if not exists storage_mode    text;
alter table agent_memories add column if not exists ipfs_cid        text;
alter table agent_memories drop constraint if exists agent_memories_storage_mode_chk;
alter table agent_memories add constraint agent_memories_storage_mode_chk
    check (storage_mode is null or storage_mode in ('local','ipfs','encrypted-ipfs','none'));

alter table agent_identities add column if not exists memory_storage_mode text not null default 'local';
alter table agent_identities drop constraint if exists agent_identities_memory_storage_mode_chk;
alter table agent_identities add constraint agent_identities_memory_storage_mode_chk
    check (memory_storage_mode in ('local','ipfs','encrypted-ipfs','none'));

create table if not exists agent_brain_anchors (
    id                  uuid primary key default gen_random_uuid(),
    agent_id            uuid not null references agent_identities(id) on delete cascade,
    brain_hash          text not null,
    kind                text not null default 'threews.brain-anchor.v1',
    status              text not null default 'pending'
                            check (status in ('pending','anchored','failed')),
    proof_uri           text,
    proof_hash          text,
    tx_hash             text,
    chain_id            integer,
    erc8004_agent_id    bigint,
    memory_count        integer not null default 0,
    public_count        integer not null default 0,
    persona_prompt_hash text,
    signer_address      text,
    signature           text,
    error_code          text,
    error_detail        text,
    created_at          timestamptz not null default now(),
    anchored_at         timestamptz
);
create index if not exists agent_brain_anchors_agent
    on agent_brain_anchors(agent_id, created_at desc);
create index if not exists agent_brain_anchors_hash
    on agent_brain_anchors(agent_id, brain_hash);
create index if not exists agent_brain_anchors_status
    on agent_brain_anchors(agent_id, status, created_at desc);

-- ── user_streaks / user_badges — cross-surface leaderboard streaks + badges ──
-- See api/_lib/migrations/20260712020000_leaderboard_streaks_badges.sql for the
-- full rationale.
create table if not exists user_streaks (
    user_id         uuid primary key references users(id) on delete cascade,
    current_streak  integer not null default 0,
    longest_streak  integer not null default 0,
    last_active_day date,
    updated_at      timestamptz not null default now(),
    created_at      timestamptz not null default now()
);

create table if not exists user_badges (
    id          bigserial primary key,
    user_id     uuid not null references users(id) on delete cascade,
    code        text not null,
    context     jsonb,
    unlocked_at timestamptz not null default now()
);
create unique index if not exists user_badges_uniq on user_badges (user_id, code);
create index if not exists user_badges_user on user_badges (user_id, unlocked_at desc);

-- ── Farcaster memory seeding consent ────────────────────────────────────────
-- Mirrors api/_lib/migrations/20260811130000_farcaster_memory_consent.sql. The
-- challenge table holds the one-time nonce plus the exact text the wallet signs
-- (stored server-side so verification never trusts a client copy); the consent
-- table is the durable, revocable grant. Every seeded memory row carries its
-- consent id in context->>'consent_id' so a revoke deletes exactly that grant.
create table if not exists farcaster_seed_challenges (
    nonce       text primary key,
    user_id     uuid not null references users(id) on delete cascade,
    agent_id    uuid not null references agent_identities(id) on delete cascade,
    fid         integer not null,
    fname       text,
    message     text not null,
    cast_limit  int not null,
    created_at  timestamptz not null default now(),
    expires_at  timestamptz not null,
    consumed_at timestamptz
);
create index if not exists farcaster_seed_challenges_agent
    on farcaster_seed_challenges (agent_id) where consumed_at is null;
create index if not exists farcaster_seed_challenges_expiry
    on farcaster_seed_challenges (expires_at);

create table if not exists farcaster_memory_consents (
    id              uuid primary key default gen_random_uuid(),
    user_id         uuid not null references users(id) on delete cascade,
    agent_id        uuid not null references agent_identities(id) on delete cascade,
    fid             integer not null,
    fname           text,
    scope           text not null,
    proof_chain     text not null check (proof_chain in ('solana', 'ethereum')),
    proof_address   text not null,
    proof_signature text not null,
    proof_message   text not null,
    source_lane     text,
    granted_at      timestamptz not null default now(),
    revoked_at      timestamptz,
    last_seeded_at  timestamptz,
    memories_seeded int not null default 0,
    casts_ingested  int not null default 0
);
create unique index if not exists farcaster_memory_consents_active_agent
    on farcaster_memory_consents (agent_id) where revoked_at is null;
create index if not exists farcaster_memory_consents_user
    on farcaster_memory_consents (user_id);
create index if not exists farcaster_memory_consents_fid
    on farcaster_memory_consents (fid) where revoked_at is null;
create index if not exists agent_memories_farcaster_consent
    on agent_memories ((context ->> 'consent_id'))
    where context ->> 'source' = 'farcaster_seed';

-- ── x_memory_consents ─────────────────────────────────────────────────────────
-- Consent-gated X memory seeding. See
-- api/_lib/migrations/20260811140000_x_memory_consent.sql for the rationale and
-- api/_lib/x-memory-seed.js for the disclosure this grant is recorded against.
create table if not exists x_memory_consents (
    id              uuid primary key default gen_random_uuid(),
    user_id         uuid not null references users(id) on delete cascade,
    agent_id        uuid not null references agent_identities(id) on delete cascade,
    x_user_id       text not null,
    username        text not null,
    scope_version   text not null,
    disclosure      jsonb not null default '{}'::jsonb,
    granted_scopes  text,
    granted_at      timestamptz not null default now(),
    revoked_at      timestamptz,
    revoked_reason  text,
    last_seeded_at  timestamptz,
    memories_seeded int not null default 0,
    posts_read      int not null default 0
);
create unique index if not exists x_memory_consents_active_agent
    on x_memory_consents (agent_id) where revoked_at is null;
create index if not exists x_memory_consents_user
    on x_memory_consents (user_id);
create index if not exists x_memory_consents_x_user
    on x_memory_consents (x_user_id) where revoked_at is null;
create index if not exists agent_memories_x_seed_consent
    on agent_memories ((context ->> 'consent_id'))
    where context ->> 'source' = 'x_seed';

-- The X OAuth callback never supplied social_connections.scopes, which is NOT
-- NULL with no default, so every X connect died before writing a row.
alter table social_connections alter column scopes set default '';

-- ── Agents v1 REST API (migration 20260922130000_agents_v1_api.sql) ──────────
-- ── lifecycle ────────────────────────────────────────────────────────────────
-- `running` lets scheduled automations, wallet intents and runs execute.
-- `stopped` pauses all of them without deleting anything. The wallet-intent
-- execution context (api/_lib/wallet-intents.js buildExecContext), the
-- automation engine and the run driver all read this column.
ALTER TABLE agent_identities ADD COLUMN IF NOT EXISTS status text NOT NULL DEFAULT 'running';
ALTER TABLE agent_identities ADD COLUMN IF NOT EXISTS status_changed_at timestamptz;
DO $$ BEGIN
	ALTER TABLE agent_identities ADD CONSTRAINT agent_identities_status_check CHECK (status IN ('running', 'stopped'));
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ── runs ─────────────────────────────────────────────────────────────────────
-- A run is a goal the agent pursues through the server-side tool loop, one
-- checkpointed step at a time. `checkpoint` holds the AgentRuntime state and
-- next context between steps so any instance can resume it; `lease_until`
-- keeps two drivers (the SSE stream and the cron) from stepping it at once.
CREATE TABLE IF NOT EXISTS agent_runs (
	id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	agent_id             uuid NOT NULL REFERENCES agent_identities(id) ON DELETE CASCADE,
	user_id              uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	goal                 text NOT NULL,
	status               text NOT NULL DEFAULT 'queued'
		CHECK (status IN ('scheduled', 'queued', 'running', 'paused', 'completed', 'failed', 'cancelled', 'budget_exhausted')),
	model                text,
	temperature          real,
	tools_allowed        text[],
	max_steps            int NOT NULL DEFAULT 12 CHECK (max_steps BETWEEN 1 AND 60),
	step_count           int NOT NULL DEFAULT 0,
	budget_credits_usd   numeric(20, 6) NOT NULL DEFAULT 0,
	budget_usd           numeric(20, 6) NOT NULL DEFAULT 0,
	spent_credits_usd    numeric(20, 6) NOT NULL DEFAULT 0,
	spent_usd            numeric(20, 6) NOT NULL DEFAULT 0,
	schedule_cron        text,
	scheduled_for        timestamptz,
	checkpoint           jsonb,
	result               text,
	error                text,
	source               text NOT NULL DEFAULT 'api',
	automation_id        uuid,
	trigger_key          text,
	lease_owner          text,
	lease_until          timestamptz,
	cancel_requested_at  timestamptz,
	created_at           timestamptz NOT NULL DEFAULT now(),
	updated_at           timestamptz NOT NULL DEFAULT now(),
	started_at           timestamptz,
	finished_at          timestamptz
);
CREATE INDEX IF NOT EXISTS agent_runs_agent_idx ON agent_runs (agent_id, created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS agent_runs_user_idx ON agent_runs (user_id, created_at DESC);
-- One run per automation fire: a retried sweep can never start the same run twice.
CREATE UNIQUE INDEX IF NOT EXISTS agent_runs_trigger_uidx ON agent_runs (automation_id, trigger_key)
	WHERE automation_id IS NOT NULL AND trigger_key IS NOT NULL;
CREATE INDEX IF NOT EXISTS agent_runs_due_idx ON agent_runs (status, scheduled_for)
	WHERE status IN ('scheduled', 'queued', 'running');

CREATE TABLE IF NOT EXISTS agent_run_steps (
	id              bigserial PRIMARY KEY,
	run_id          uuid NOT NULL REFERENCES agent_runs(id) ON DELETE CASCADE,
	seq             int NOT NULL,
	kind            text NOT NULL
		CHECK (kind IN ('status', 'model_call', 'tool_call', 'tool_result', 'tool_blocked', 'final', 'error')),
	provider        text,
	model           text,
	tool_name       text,
	input           jsonb,
	output          jsonb,
	input_tokens    int,
	output_tokens   int,
	cost_micro_usd  bigint,
	latency_ms      int,
	created_at      timestamptz NOT NULL DEFAULT now(),
	UNIQUE (run_id, seq)
);

-- ── chat history ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS agent_messages (
	id              bigserial PRIMARY KEY,
	agent_id        uuid NOT NULL REFERENCES agent_identities(id) ON DELETE CASCADE,
	user_id         uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	role            text NOT NULL CHECK (role IN ('user', 'assistant')),
	content         text NOT NULL,
	model           text,
	provider        text,
	input_tokens    int,
	output_tokens   int,
	cost_micro_usd  bigint,
	charged_usd     numeric(20, 6) NOT NULL DEFAULT 0,
	free_tier       boolean NOT NULL DEFAULT false,
	tool_calls      jsonb NOT NULL DEFAULT '[]'::jsonb,
	signatures      text[] NOT NULL DEFAULT '{}',
	created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS agent_messages_thread_idx ON agent_messages (agent_id, user_id, id DESC);
CREATE INDEX IF NOT EXISTS agent_messages_user_day_idx ON agent_messages (user_id, created_at) WHERE role = 'user';

-- ── automations ──────────────────────────────────────────────────────────────
-- One trigger, one action. Spend and notify actions execute through a backing
-- wallet intent (trigger_type 'on_automation', never swept on its own) so they
-- inherit the intent engine's caps, spend policy and custody ledger. The
-- `agent_prompt` action starts an agent run.
CREATE TABLE IF NOT EXISTS agent_automations (
	id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	agent_id        uuid NOT NULL REFERENCES agent_identities(id) ON DELETE CASCADE,
	user_id         uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	title           text,
	trigger_type    text NOT NULL
		CHECK (trigger_type IN ('price_threshold', 'schedule', 'balance_below', 'tip_received', 'launch_matching', 'graduation', 'whale_buy')),
	trigger_config  jsonb NOT NULL DEFAULT '{}'::jsonb,
	action_type     text NOT NULL CHECK (action_type IN ('agent_prompt', 'swap', 'transfer', 'notify')),
	action_config   jsonb NOT NULL DEFAULT '{}'::jsonb,
	trigger_once    boolean NOT NULL DEFAULT false,
	enabled         boolean NOT NULL DEFAULT true,
	intent_id       uuid,
	state           jsonb NOT NULL DEFAULT '{}'::jsonb,
	fire_count      int NOT NULL DEFAULT 0,
	last_fired_at   timestamptz,
	last_checked_at timestamptz,
	last_status     text,
	last_note       text,
	source          text NOT NULL DEFAULT 'api',
	created_at      timestamptz NOT NULL DEFAULT now(),
	updated_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS agent_automations_agent_idx ON agent_automations (agent_id, created_at DESC);
CREATE INDEX IF NOT EXISTS agent_automations_live_idx ON agent_automations (trigger_type) WHERE enabled = true;

-- ── link codes (agents-v1 legacy shape) ────────────────────────────────────
-- This is the ORIGINAL account_link_codes from the 2026-09-22 agents-v1
-- feature (agent_id/scopes/redeemed_at/link_id). A fresh bootstrap creates it
-- under this name so migration 20261024193900_fix_account_link_codes_collision.sql
-- has something to rename, exactly mirroring what happened in production: the
-- 2026-10-11 account-linking feature's own `account_link_codes` (status/
-- device_kind/claim_secret_hash/...) is the table that migration actually
-- creates and that api/_lib/account-link/link-codes.js reads and writes today.
CREATE TABLE IF NOT EXISTS account_link_codes (
	id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	code_hash    text NOT NULL UNIQUE,
	user_id      uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	agent_id     uuid REFERENCES agent_identities(id) ON DELETE SET NULL,
	label        text,
	scopes       text NOT NULL,
	created_at   timestamptz NOT NULL DEFAULT now(),
	expires_at   timestamptz NOT NULL,
	redeemed_at  timestamptz,
	link_id      uuid
);
CREATE INDEX IF NOT EXISTS account_link_codes_user_idx ON account_link_codes (user_id, created_at DESC);

-- A redeemed code becomes a link: which device or gateway identity is paired,
-- and the API key it was issued (revoking the link revokes the key).
CREATE TABLE IF NOT EXISTS account_links (
	id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id      uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	agent_id     uuid REFERENCES agent_identities(id) ON DELETE SET NULL,
	kind         text NOT NULL,
	external_id  text,
	label        text,
	api_key_id   uuid REFERENCES api_keys(id) ON DELETE SET NULL,
	created_at   timestamptz NOT NULL DEFAULT now(),
	last_seen_at timestamptz,
	revoked_at   timestamptz
);
CREATE INDEX IF NOT EXISTS account_links_user_idx ON account_links (user_id) WHERE revoked_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS account_links_external_uidx ON account_links (kind, external_id)
	WHERE revoked_at IS NULL AND external_id IS NOT NULL;

-- ── CLI device links ─────────────────────────────────────────────────────────
-- `three-ws login --device` (api/cli/[action].js, /cli/authorize). The CLI keeps
-- the device code; only its sha256 is stored. An approved row mints one API key
-- on the next poll and becomes `consumed`, so the key is delivered exactly once.
-- Full rationale: api/_lib/migrations/20260922130000_cli_link_requests.sql.
create table if not exists cli_link_requests (
	id                uuid primary key default gen_random_uuid(),
	device_code_hash  text not null unique,
	user_code         text not null unique,
	client_name       text not null,
	hostname          text,
	requested_scope   text not null,
	granted_scope     text,
	user_id           uuid references users(id) on delete cascade,
	status            text not null default 'pending'
	                  check (status in ('pending', 'approved', 'denied', 'consumed')),
	api_key_id        uuid references api_keys(id) on delete set null,
	created_ip        text,
	created_at        timestamptz not null default now(),
	expires_at        timestamptz not null,
	decided_at        timestamptz,
	consumed_at       timestamptz,
	last_polled_at    timestamptz
);
create index if not exists cli_link_requests_expires_idx on cli_link_requests (expires_at);
create index if not exists cli_link_requests_user_idx on cli_link_requests (user_id) where user_id is not null;

-- Whole-agent marketplace: listings, escrowed bids, settlement, custody rotation
-- (migrations/20260922140000_agent_marketplace.sql; docs/agent-marketplace.md).
create table if not exists agent_listings (
    id                    uuid primary key default gen_random_uuid(),
    agent_id              uuid not null references agent_identities(id),
    seller_user_id        uuid not null references users(id),
    status                text not null default 'active',
    -- Buy-now price in USDC atomics (6dp). Null means auction only.
    ask_usdc_atomics      bigint,
    -- Optional buy-now price in $THREE atomics.
    ask_three_atomics     numeric,
    -- Floor for bids, USDC atomics.
    min_bid_usdc_atomics  bigint not null,
    expires_at            timestamptz not null,
    -- true: the agent wallet's balance goes to the buyer with the agent.
    -- false: the balance is swept to the seller's payout address first.
    include_balance       boolean not null default false,
    -- true: memories, chat and activity history transfer. false: they are
    -- detached from the agent before the buyer takes ownership.
    include_history       boolean not null default true,
    -- Seller's Solana address for sale proceeds and (when excluded) the balance.
    payout_address        text not null,
    escrow_address        text not null,
    note                  text,
    -- What the listing advertised at creation: skills, persona summary, wallet
    -- balance, so a buyer can compare it with what actually transferred.
    snapshot              jsonb not null default '{}'::jsonb,
    sold_bid_id           uuid,
    -- Set once the escrow token accounts are closed and their rent returned.
    escrow_closed_at      timestamptz,
    closed_at             timestamptz,
    created_at            timestamptz not null default now(),
    updated_at            timestamptz not null default now()
);

do $$
begin
    if not exists (select 1 from pg_constraint where conname = 'agent_listings_status_chk') then
        alter table agent_listings add constraint agent_listings_status_chk
            check (status in ('active', 'settling', 'sold', 'delisted', 'expired'));
    end if;
    if not exists (select 1 from pg_constraint where conname = 'agent_listings_price_chk') then
        alter table agent_listings add constraint agent_listings_price_chk
            check (min_bid_usdc_atomics > 0
                   and (ask_usdc_atomics is null or ask_usdc_atomics >= min_bid_usdc_atomics)
                   and (ask_three_atomics is null or ask_three_atomics > 0));
    end if;
end $$;

-- One live listing per agent: a second would let two buyers win the same agent.
create unique index if not exists agent_listings_one_live
    on agent_listings (agent_id)
    where status in ('active', 'settling');
create index if not exists agent_listings_browse
    on agent_listings (status, created_at desc);
create index if not exists agent_listings_seller
    on agent_listings (seller_user_id, created_at desc);
create index if not exists agent_listings_expiry
    on agent_listings (expires_at)
    where status = 'active';

create table if not exists agent_listing_bids (
    id                  uuid primary key default gen_random_uuid(),
    listing_id          uuid not null references agent_listings(id),
    bidder_user_id      uuid not null references users(id),
    kind                text not null default 'bid',          -- bid | buy_now
    currency            text not null default 'USDC',         -- USDC | THREE
    amount_atomics      numeric not null,
    status              text not null default 'awaiting_funds',
    -- agent_wallet: escrowed by the platform from one of the bidder's agents.
    -- connected_wallet: the bidder signed the escrow transfer in their wallet.
    funding_source      text not null,
    funding_agent_id    uuid references agent_identities(id),
    -- Where the escrowed funds came from, and so where a refund goes.
    funding_address     text not null,
    -- Solana Pay reference key riding the escrow transfer (connected wallet).
    escrow_reference    text,
    escrow_signature    text,
    funded_at           timestamptz,
    -- Unfunded bids lapse; a signed-but-unsent wallet transaction expires with
    -- its blockhash long before this.
    fund_by             timestamptz,
    decided_at          timestamptz,
    refund_status       text,
    refund_signature    text,
    refund_error        text,
    refund_attempts     integer not null default 0,
    -- Exactly-once record of the refund transaction ({signature,
    -- lastValidBlockHeight}), written before broadcast (chain.js sendLeg).
    refund_leg          jsonb,
    -- Lease: an inline refund and the sweep cron never send the same refund.
    refund_locked_until timestamptz,
    created_at          timestamptz not null default now(),
    updated_at          timestamptz not null default now()
);

do $$
begin
    if not exists (select 1 from pg_constraint where conname = 'agent_listing_bids_status_chk') then
        alter table agent_listing_bids add constraint agent_listing_bids_status_chk
            check (status in ('awaiting_funds', 'open', 'accepted', 'rejected', 'withdrawn', 'expired'));
    end if;
    if not exists (select 1 from pg_constraint where conname = 'agent_listing_bids_refund_chk') then
        alter table agent_listing_bids add constraint agent_listing_bids_refund_chk
            check (refund_status is null or refund_status in ('pending', 'sent', 'failed'));
    end if;
    if not exists (select 1 from pg_constraint where conname = 'agent_listing_bids_shape_chk') then
        alter table agent_listing_bids add constraint agent_listing_bids_shape_chk
            check (amount_atomics > 0
                   and kind in ('bid', 'buy_now')
                   and currency in ('USDC', 'THREE')
                   and funding_source in ('agent_wallet', 'connected_wallet'));
    end if;
end $$;

-- An escrow transfer funds exactly one bid.
create unique index if not exists agent_listing_bids_escrow_signature_key
    on agent_listing_bids (escrow_signature) where escrow_signature is not null;
create unique index if not exists agent_listing_bids_escrow_reference_key
    on agent_listing_bids (escrow_reference) where escrow_reference is not null;
create index if not exists agent_listing_bids_listing
    on agent_listing_bids (listing_id, status, amount_atomics desc);
create index if not exists agent_listing_bids_bidder
    on agent_listing_bids (bidder_user_id, created_at desc);
create index if not exists agent_listing_bids_refund_due
    on agent_listing_bids (updated_at) where refund_status in ('pending', 'failed');

create table if not exists agent_transfers (
    id                   uuid primary key default gen_random_uuid(),
    listing_id           uuid not null unique references agent_listings(id),
    bid_id               uuid not null unique references agent_listing_bids(id),
    agent_id             uuid not null references agent_identities(id),
    buyer_user_id        uuid not null references users(id),
    seller_user_id       uuid not null references users(id),
    currency             text not null,
    amount_atomics       numeric not null,
    fee_atomics          numeric not null,
    seller_net_atomics   numeric not null,
    -- The state machine's resume point (see settlement.js STEPS).
    step                 text not null default 'pay_seller',
    status               text not null default 'in_progress',   -- in_progress | completed | failed
    payout_signature     text,
    fee_signature        text,
    old_wallet_address   text,
    new_wallet_address   text,
    -- The custody rotation record: every step, its time, and its signatures.
    -- The pending new key sits here encrypted (secret-box) only between key
    -- generation and the ownership swap, then is cleared.
    rotation             jsonb not null default '{}'::jsonb,
    -- Exactly-once records of every on-chain leg, keyed by leg id.
    legs                 jsonb not null default '{}'::jsonb,
    attempts             integer not null default 0,
    last_error           text,
    -- Lease so two workers never drive the same transfer at once.
    locked_until         timestamptz,
    completed_at         timestamptz,
    created_at           timestamptz not null default now(),
    updated_at           timestamptz not null default now()
);

create index if not exists agent_transfers_open
    on agent_transfers (updated_at) where status <> 'completed';
create index if not exists agent_transfers_buyer
    on agent_transfers (buyer_user_id, created_at desc);
create index if not exists agent_transfers_seller
    on agent_transfers (seller_user_id, created_at desc);

create table if not exists agent_marketplace_history (
    id              bigserial primary key,
    agent_id        uuid not null,
    listing_id      uuid,
    bid_id          uuid,
    transfer_id     uuid,
    actor_user_id   uuid,
    event           text not null,
    currency        text,
    amount_atomics  numeric,
    signature       text,
    meta            jsonb not null default '{}'::jsonb,
    created_at      timestamptz not null default now()
);

create index if not exists agent_marketplace_history_agent
    on agent_marketplace_history (agent_id, created_at desc);
create index if not exists agent_marketplace_history_listing
    on agent_marketplace_history (listing_id, created_at desc);

-- Preview records for the financial MCP tools: each commit must cite a preview
-- the same user produced for the same action and arguments in the last ten
-- minutes, so a model can never skip showing the user what it is about to do.
create table if not exists agent_market_previews (
    id            text primary key,
    user_id       uuid not null,
    action        text not null,
    params_hash   text not null,
    payload       jsonb not null,
    consumed_at   timestamptz,
    created_at    timestamptz not null default now()
);

create index if not exists agent_market_previews_user
    on agent_market_previews (user_id, created_at desc);

-- Revenue follows the owner who earned it. Earnings balances join revenue to
-- the agent's CURRENT owner, so without this a sale would hand the seller's
-- unwithdrawn earnings to the buyer. Settlement stamps every pre-sale row with
-- the seller; rows written after the sale stay null and follow the new owner.
alter table agent_revenue_events add column if not exists owner_user_id uuid;