diff options
Diffstat (limited to 'migrations/20241201000000_initial_schema.sql')
| -rw-r--r-- | migrations/20241201000000_initial_schema.sql | 107 |
1 files changed, 107 insertions, 0 deletions
diff --git a/migrations/20241201000000_initial_schema.sql b/migrations/20241201000000_initial_schema.sql new file mode 100644 index 0000000..8796157 --- /dev/null +++ b/migrations/20241201000000_initial_schema.sql @@ -0,0 +1,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);
\ No newline at end of file |