50 lines
1.6 KiB
SQL
50 lines
1.6 KiB
SQL
CREATE TABLE oauth_clients (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
redirect_uris_json TEXT NOT NULL CHECK (json_valid(redirect_uris_json)),
|
|
created_at_utc TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE oauth_authorization_codes (
|
|
code_hash TEXT PRIMARY KEY,
|
|
client_id TEXT NOT NULL REFERENCES oauth_clients(id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
redirect_uri TEXT NOT NULL,
|
|
scope TEXT NOT NULL,
|
|
resource TEXT NOT NULL,
|
|
code_challenge TEXT NOT NULL,
|
|
created_at_utc TEXT NOT NULL,
|
|
expires_at_utc TEXT NOT NULL,
|
|
consumed_at_utc TEXT
|
|
);
|
|
|
|
CREATE INDEX oauth_authorization_codes_expiry_idx
|
|
ON oauth_authorization_codes(expires_at_utc);
|
|
|
|
CREATE TABLE oauth_access_tokens (
|
|
token_hash TEXT PRIMARY KEY,
|
|
client_id TEXT NOT NULL REFERENCES oauth_clients(id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
scope TEXT NOT NULL,
|
|
resource TEXT NOT NULL,
|
|
created_at_utc TEXT NOT NULL,
|
|
expires_at_utc TEXT NOT NULL,
|
|
last_used_at_utc TEXT,
|
|
request_window_started_at_utc TEXT NOT NULL,
|
|
request_count INTEGER NOT NULL DEFAULT 0 CHECK (request_count >= 0),
|
|
revoked_at_utc TEXT
|
|
);
|
|
|
|
CREATE INDEX oauth_access_tokens_user_idx ON oauth_access_tokens(user_id);
|
|
CREATE INDEX oauth_access_tokens_expiry_idx ON oauth_access_tokens(expires_at_utc);
|
|
|
|
CREATE TABLE oauth_rate_limit_attempts (
|
|
id TEXT PRIMARY KEY,
|
|
kind TEXT NOT NULL CHECK (kind IN ('registration', 'token')),
|
|
address_hash TEXT NOT NULL,
|
|
attempted_at_utc TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX oauth_rate_limit_attempts_lookup_idx
|
|
ON oauth_rate_limit_attempts(kind, address_hash, attempted_at_utc);
|