gitfor/platform

-- security_auth: 2026-10 audit fixes for authentication and identity (AUTH-A1, AUTH-A12).

-- AUTH-A1: tokens minted by another token (REST POST /user/tokens, deploy tokens) record the minting
-- token so revoking the parent revokes them, and so the child's expiry can be capped at the parent's.
ALTER TABLE access_tokens ADD COLUMN parent_token_id TEXT;

CREATE INDEX access_tokens_parent ON access_tokens(parent_token_id);

ALTER TABLE deploy_tokens ADD COLUMN parent_token_id TEXT;

CREATE INDEX deploy_tokens_parent ON deploy_tokens(parent_token_id);

DROP TRIGGER access_tokens_revoke_mcp;

-- However a token is deleted (Settings, the REST API, a password change, OAuth revocation), its OAuth
-- refresh tokens and every token it minted or delegated go with it.
CREATE TRIGGER access_tokens_revoke_mcp BEFORE DELETE ON access_tokens
BEGIN
  DELETE FROM oauth_refresh_tokens WHERE access_token_id = OLD.id;
  DELETE FROM access_tokens WHERE id IN (SELECT token_id FROM mcp_token_agents WHERE parent_token_id = OLD.id);
  DELETE FROM access_tokens WHERE parent_token_id = OLD.id;
  DELETE FROM deploy_tokens WHERE parent_token_id = OLD.id;
END;

-- AUTH-A12: browser sessions live in D1 (not KV) so they can be listed and revoked individually and
-- carry a sliding idle timeout. The secret is never stored; token_hash is its SHA-256.
CREATE TABLE sessions (
  token_hash TEXT PRIMARY KEY,
  id TEXT NOT NULL UNIQUE,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  ip TEXT,
  user_agent TEXT,
  created_at INTEGER NOT NULL,
  last_seen_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL
);

CREATE INDEX sessions_user ON sessions(user_id);

CREATE INDEX sessions_expiry ON sessions(expires_at);