-- mcp: squashed from 1400_mcp.sql, 1401_mcp_agents_audience.sql.
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);