app/worker/migrations/0002_unify_agents.sql

 -- Collapses the accounts/agents two-tier hierarchy into one peer

 -- table, per governance.md: "One noun covers every participant: an

 -- Agent is an authenticatable identity, human or synthetic... There's

 -- no capability a synthetic Agent has that a human Agent couldn't

 -- exercise too." The previous schema special-cased "accounts" (OAuth-

 -- login humans, implicitly superior, able to override) vs "agents"

 -- (bot sub-identities, owned by an account) -- a real distinction in

 -- kind that shouldn't exist. Now there is exactly one identity table;

 -- "logged in via OAuth" and "authenticates with a bearer token" are

 -- just two kinds of credential the SAME kind of entity can hold, not

 -- two different entity types. `created_by` is provenance only (who

 -- spun up this identity, for /profile display) -- never an authority

 -- relationship; all real authority flows through repo-level Role

 -- grants (roles.json on each repo's zz-meta branch), uniformly, for

 -- every Agent. Dropping and recreating rather than migrating data:

 -- every row here is this session's own test/verification data.

 DROP TABLE IF EXISTS sessions;

 DROP TABLE IF EXISTS agent_tokens;

 DROP TABLE IF EXISTS account_identities;

 DROP TABLE IF EXISTS repos;

 DROP TABLE IF EXISTS agents;

 DROP TABLE IF EXISTS accounts;

 CREATE TABLE agents (

  id TEXT PRIMARY KEY,

  display_name TEXT NOT NULL,

  email TEXT,

  created_at TEXT NOT NULL,

  created_by TEXT REFERENCES agents(id) -- provenance only, nullable (a human's first login has no creator)

 );

 CREATE TABLE agent_identities (

  id TEXT PRIMARY KEY,

  agent_id TEXT NOT NULL REFERENCES agents(id),

  provider TEXT NOT NULL CHECK (provider IN ('cloudflare', 'google')),

  provider_subject TEXT NOT NULL,

  access_token_enc TEXT,

  refresh_token_enc TEXT,

  token_expires_at TEXT,

  created_at TEXT NOT NULL,

  UNIQUE (provider, provider_subject)

 );

 CREATE INDEX idx_agent_identities_agent ON agent_identities(agent_id);

 CREATE TABLE agent_tokens (

  id TEXT PRIMARY KEY,

  agent_id TEXT NOT NULL REFERENCES agents(id),

  token_hash TEXT NOT NULL UNIQUE,

  created_at TEXT NOT NULL,

  revoked_at TEXT,

  last_used_at TEXT

 );

 CREATE INDEX idx_agent_tokens_agent ON agent_tokens(agent_id);

 CREATE TABLE sessions (

  id TEXT PRIMARY KEY,

  agent_id TEXT NOT NULL REFERENCES agents(id),

  created_at TEXT NOT NULL,

  expires_at TEXT NOT NULL

 );

 CREATE INDEX idx_sessions_agent ON sessions(agent_id);

 CREATE TABLE repos (

  id TEXT PRIMARY KEY,

  slug TEXT NOT NULL UNIQUE, -- "<owner-segment>/<repo-name>"

  artifacts_name TEXT NOT NULL UNIQUE,

  description TEXT,

  owner_id TEXT NOT NULL REFERENCES agents(id), -- single FK; no more owner_type split

  created_at TEXT NOT NULL,

  updated_at TEXT NOT NULL

 );

 CREATE INDEX idx_repos_updated ON repos(updated_at DESC);

 CREATE INDEX idx_repos_owner ON repos(owner_id);

 -- `zz login`'s browser<->CLI bridge: the CLI generates `nonce`, opens

 -- a browser to it, polls until the OAuth callback fills in agent_id/

 -- token, then the row is consumed (never reused). See accountsDb.ts.

 DROP TABLE IF EXISTS cli_login_sessions;

 CREATE TABLE cli_login_sessions (

  nonce TEXT PRIMARY KEY,

  agent_id TEXT REFERENCES agents(id),

  token TEXT,

  created_at TEXT NOT NULL,

  consumed INTEGER NOT NULL DEFAULT 0

 );