-- MCP server: OAuth 2.1 clients, authorization codes, refresh tokens, agent identity and audit trail. -- Issued access tokens are ordinary fine-grained rows in access_tokens, so every permission check -- (scopedRepoPermission) applies to agents exactly as it does to personal tokens. CREATE TABLE oauth_clients ( id TEXT PRIMARY KEY, name TEXT NOT NULL, redirect_uris TEXT NOT NULL, -- 'dcr' for RFC 7591 registrations, 'cimd' for Client ID Metadata Documents (client_id is a URL). kind TEXT NOT NULL CHECK (kind IN ('dcr', 'cimd')), created_at INTEGER NOT NULL, last_used_at INTEGER ); CREATE TABLE oauth_codes ( code_hash TEXT PRIMARY KEY, client_id TEXT NOT NULL, user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, redirect_uri TEXT NOT NULL, code_challenge TEXT NOT NULL, resource TEXT NOT NULL, -- JSON: { permissions, allRepos, repoIds, agent } grant_json TEXT NOT NULL, expires_at INTEGER NOT NULL ); -- One OAuth grant is one access_tokens row for its whole life: refreshing rotates that row's secret in -- place. Rotated refresh tokens are kept (used_at set) so replaying one revokes the grant. CREATE TABLE oauth_refresh_tokens ( token_hash TEXT PRIMARY KEY, client_id TEXT NOT NULL, user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, access_token_id TEXT NOT NULL REFERENCES access_tokens(id) ON DELETE CASCADE, resource TEXT NOT NULL, grant_json TEXT NOT NULL, expires_at INTEGER NOT NULL, used_at INTEGER, created_at INTEGER NOT NULL ); CREATE INDEX oauth_refresh_tokens_user ON oauth_refresh_tokens(user_id); CREATE INDEX oauth_refresh_tokens_access ON oauth_refresh_tokens(access_token_id); -- Which agent a token belongs to and which human authorized it; drives commit/PR attribution. -- parent_token_id marks tokens delegated through create_repo_token. CREATE TABLE mcp_token_agents ( token_id TEXT PRIMARY KEY REFERENCES access_tokens(id) ON DELETE CASCADE, agent_name TEXT NOT NULL, client_id TEXT, authorized_by TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, parent_token_id TEXT, created_at INTEGER NOT NULL ); CREATE INDEX mcp_token_agents_parent ON mcp_token_agents(parent_token_id); -- However a token is deleted (Settings, the REST API, a password change, OAuth revocation), its OAuth -- refresh tokens and every token it 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); END; CREATE TABLE mcp_audit ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, token_id TEXT, agent_name TEXT, repo_id TEXT, tool TEXT NOT NULL, arguments TEXT NOT NULL, outcome TEXT NOT NULL CHECK (outcome IN ('ok', 'denied', 'error')), detail TEXT, created_at INTEGER NOT NULL ); CREATE INDEX mcp_audit_user ON mcp_audit(user_id, created_at DESC); CREATE INDEX mcp_audit_repo ON mcp_audit(repo_id, created_at DESC);