77 lines · 3.0 KB
-- 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);