Sign in

pulls/pr_01m47d24b0e6n91zwymwxg0vpx

public

pr_01m47d24b0e6n91zwymwxg0vpx/services/identity/migrations/0009_oauth.sql

31 lines1,066 bytesCodeBlame
1-- OAuth 2.1 sign-in for applications, such as MCP clients.
2-- Clients are not stored: a client id carries its own name and redirect
3-- addresses, so registering one writes nothing.
4
5CREATE TABLE oauth_codes (
6 -- SHA-256 of the authorization code.
7 id TEXT PRIMARY KEY,
8 user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
9 client_id TEXT NOT NULL,
10 client_name TEXT NOT NULL,
11 redirect_uri TEXT NOT NULL,
12 -- PKCE, method S256.
13 code_challenge TEXT NOT NULL,
14 expires_at TEXT NOT NULL
15);
16
17-- One per application a person has signed in to.
18CREATE TABLE oauth_grants (
19 id TEXT PRIMARY KEY,
20 user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
21 client_id TEXT NOT NULL,
22 client_name TEXT NOT NULL,
23 -- SHA-256 of the current refresh token; replaced on every use.
24 refresh_hash TEXT UNIQUE,
25 -- The access token issued with it, revoked when the grant is refreshed.
26 access_token_id TEXT,
27 created_at TEXT NOT NULL,
28 last_used_at TEXT NOT NULL,
29 expires_at TEXT
30);
31CREATE INDEX oauth_grants_by_user ON oauth_grants (user_id);