1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
-- Migration 001: Initial OAuth2 STS Schema
-- Created: 2024-01-01
-- Description: Create core OAuth2 tables with proper foreign keys and indexes
-- OAuth Clients table
CREATE TABLE IF NOT EXISTS oauth_clients (
id INTEGER PRIMARY KEY AUTOINCREMENT,
client_id TEXT NOT NULL UNIQUE,
client_secret_hash TEXT NOT NULL,
client_name TEXT NOT NULL,
redirect_uris TEXT NOT NULL, -- JSON array
scopes TEXT NOT NULL, -- space-separated
grant_types TEXT NOT NULL, -- space-separated
response_types TEXT NOT NULL, -- space-separated
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT 1
);
-- Authorization Codes table
CREATE TABLE IF NOT EXISTS auth_codes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code TEXT NOT NULL UNIQUE,
client_id TEXT NOT NULL,
user_id TEXT NOT NULL,
redirect_uri TEXT NOT NULL,
scope TEXT,
expires_at TEXT NOT NULL,
created_at TEXT NOT NULL,
is_used BOOLEAN NOT NULL DEFAULT 0,
code_challenge TEXT,
code_challenge_method TEXT,
FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id)
);
-- Access Tokens table
CREATE TABLE IF NOT EXISTS access_tokens (
id INTEGER PRIMARY KEY AUTOINCREMENT,
token_id TEXT NOT NULL UNIQUE,
client_id TEXT NOT NULL,
user_id TEXT NOT NULL,
scope TEXT,
expires_at TEXT NOT NULL,
created_at TEXT NOT NULL,
is_revoked BOOLEAN NOT NULL DEFAULT 0,
token_hash TEXT NOT NULL,
FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id)
);
-- Refresh Tokens table
CREATE TABLE IF NOT EXISTS refresh_tokens (
id INTEGER PRIMARY KEY AUTOINCREMENT,
token_id TEXT NOT NULL UNIQUE,
access_token_id INTEGER NOT NULL,
client_id TEXT NOT NULL,
user_id TEXT NOT NULL,
scope TEXT,
expires_at TEXT NOT NULL,
created_at TEXT NOT NULL,
is_revoked BOOLEAN NOT NULL DEFAULT 0,
token_hash TEXT NOT NULL,
FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id),
FOREIGN KEY (access_token_id) REFERENCES access_tokens (id)
);
-- RSA Keys table
CREATE TABLE IF NOT EXISTS rsa_keys (
id INTEGER PRIMARY KEY AUTOINCREMENT,
kid TEXT NOT NULL UNIQUE,
private_key_pem TEXT NOT NULL,
public_key_pem TEXT NOT NULL,
created_at TEXT NOT NULL,
is_current BOOLEAN NOT NULL DEFAULT 0
);
-- Audit Log table
CREATE TABLE IF NOT EXISTS audit_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
event_type TEXT NOT NULL,
client_id TEXT,
user_id TEXT,
ip_address TEXT,
user_agent TEXT,
details TEXT, -- JSON
created_at TEXT NOT NULL,
success BOOLEAN NOT NULL
);
-- Rate Limiting table
CREATE TABLE IF NOT EXISTS rate_limits (
id INTEGER PRIMARY KEY AUTOINCREMENT,
identifier TEXT NOT NULL, -- client_id or IP
endpoint TEXT NOT NULL,
count INTEGER NOT NULL DEFAULT 1,
window_start TEXT NOT NULL,
created_at TEXT NOT NULL,
UNIQUE (identifier, endpoint, window_start)
);
-- Performance indexes
CREATE INDEX IF NOT EXISTS idx_auth_codes_client_id ON auth_codes (client_id);
CREATE INDEX IF NOT EXISTS idx_auth_codes_expires_at ON auth_codes (expires_at);
CREATE INDEX IF NOT EXISTS idx_access_tokens_client_id ON access_tokens (client_id);
CREATE INDEX IF NOT EXISTS idx_access_tokens_expires_at ON access_tokens (expires_at);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_client_id ON refresh_tokens (client_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs (created_at);
CREATE INDEX IF NOT EXISTS idx_rate_limits_identifier ON rate_limits (identifier, endpoint);
|
