100 lines
4.8 KiB
SQL
100 lines
4.8 KiB
SQL
-- SportPath phone authentication and per-device session records.
|
|
-- Apply once to a backed-up database after reviewing the live schema.
|
|
-- This migration keeps existing HMAC credentials nullable during the transition.
|
|
|
|
ALTER TABLE users
|
|
MODIFY username VARCHAR(50) NULL,
|
|
MODIFY email VARCHAR(100) NULL,
|
|
MODIFY password_hash VARCHAR(255) NULL,
|
|
MODIFY api_key VARCHAR(64) NULL,
|
|
MODIFY api_secret VARCHAR(64) NULL,
|
|
ADD COLUMN phone_e164 VARCHAR(16) NULL AFTER uuid,
|
|
ADD COLUMN phone_verified_at DATETIME NULL AFTER phone_e164,
|
|
ADD COLUMN account_role ENUM('member', 'owner', 'content_manager', 'support') NOT NULL DEFAULT 'member',
|
|
ADD UNIQUE KEY uq_users_phone_e164 (phone_e164);
|
|
|
|
CREATE TABLE app_settings (
|
|
setting_key VARCHAR(100) PRIMARY KEY,
|
|
setting_value JSON NOT NULL,
|
|
is_public BOOLEAN NOT NULL DEFAULT FALSE,
|
|
revision BIGINT UNSIGNED NOT NULL DEFAULT 1,
|
|
updated_by INT NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_settings_public (is_public, setting_key),
|
|
CONSTRAINT fk_settings_editor FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE app_setting_audit (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
setting_key VARCHAR(100) NOT NULL,
|
|
previous_value JSON NULL,
|
|
new_value JSON NOT NULL,
|
|
actor_user_id INT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_settings_audit_key_time (setting_key, created_at),
|
|
INDEX idx_settings_audit_actor_time (actor_user_id, created_at),
|
|
CONSTRAINT fk_settings_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE otp_challenges (
|
|
challenge_uuid CHAR(36) PRIMARY KEY,
|
|
phone_e164 VARCHAR(16) NOT NULL,
|
|
purpose ENUM('register', 'login', 'change_phone') NOT NULL,
|
|
code_digest CHAR(64) NOT NULL COMMENT 'HMAC-SHA256 with a server-only OTP pepper',
|
|
attempt_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
|
max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 5,
|
|
request_ip_digest CHAR(64) NULL COMMENT 'Keyed digest; raw IP is not persisted here',
|
|
device_uuid CHAR(36) NULL,
|
|
expires_at DATETIME NOT NULL,
|
|
consumed_at DATETIME NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_otp_phone_created (phone_e164, created_at),
|
|
INDEX idx_otp_expiry (expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE auth_rate_limit_buckets (
|
|
bucket_digest CHAR(64) PRIMARY KEY COMMENT 'HMAC digest of a phone, IP, or device bucket',
|
|
bucket_type ENUM('phone', 'ip', 'device') NOT NULL,
|
|
window_started_at DATETIME NOT NULL,
|
|
request_count INT UNSIGNED NOT NULL DEFAULT 0,
|
|
blocked_until DATETIME NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_rate_bucket_expiry (updated_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE user_devices (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
device_uuid CHAR(36) NOT NULL COMMENT 'Random app-install ID; not a hardware serial',
|
|
platform ENUM('ios', 'android', 'web') NOT NULL,
|
|
public_key TEXT NULL COMMENT 'Public half of the device key; private key stays on device',
|
|
key_fingerprint CHAR(64) NULL,
|
|
key_algorithm VARCHAR(32) NULL,
|
|
display_name VARCHAR(80) NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_seen_at DATETIME NULL,
|
|
revoked_at DATETIME NULL,
|
|
UNIQUE KEY uq_device_user_uuid (user_id, device_uuid),
|
|
UNIQUE KEY uq_device_key_fingerprint (key_fingerprint),
|
|
INDEX idx_devices_user_active (user_id, revoked_at),
|
|
CONSTRAINT fk_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE auth_sessions (
|
|
session_uuid CHAR(36) PRIMARY KEY,
|
|
family_uuid CHAR(36) NOT NULL COMMENT 'Allows revoking a rotated refresh-token family',
|
|
user_id INT NOT NULL,
|
|
device_uuid CHAR(36) NULL,
|
|
refresh_token_digest CHAR(64) NOT NULL UNIQUE,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_used_at DATETIME NULL,
|
|
expires_at DATETIME NOT NULL,
|
|
revoked_at DATETIME NULL,
|
|
replaced_by CHAR(36) NULL,
|
|
INDEX idx_sessions_user_active (user_id, revoked_at, expires_at),
|
|
INDEX idx_sessions_family (family_uuid),
|
|
CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_sessions_device FOREIGN KEY (user_id, device_uuid)
|
|
REFERENCES user_devices(user_id, device_uuid) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|