database schema adjustments to server/realm/scene

db/schema/tables/080_audit.sql

@@ -0,0 +1,186 @@
+-- Chattyness Audit Schema Tables
+-- PostgreSQL 18
+--
+-- Audit trails and activity logging
+-- Load via: psql -f schema/tables/080_audit.sql
+
+\set ON_ERROR_STOP on
+
+BEGIN;
+
+-- =============================================================================
+-- Audit Event Types
+-- =============================================================================
+
+CREATE TYPE audit.event_category AS ENUM (
+    'auth',        -- Login, logout, password changes
+    'account',     -- Profile updates, settings changes
+    'realm',       -- Realm creation, modification, deletion
+    'scene',       -- Scene creation, modification
+    'moderation',  -- Moderation actions
+    'prop',        -- Prop transfers, creation
+    'admin'        -- Administrative actions
+);
+
+-- =============================================================================
+-- Audit Log
+-- =============================================================================
+-- Immutable log of significant events for compliance and debugging.
+-- =============================================================================
+
+CREATE TABLE audit.events (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+
+    -- Event classification
+    category audit.event_category NOT NULL,
+    action TEXT NOT NULL,  -- Specific action (e.g., 'login', 'create_realm', 'ban_user')
+
+    -- Actor
+    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
+    session_id UUID,  -- Auth session ID (not FK to allow historical queries)
+    ip_address INET,
+    user_agent TEXT,
+
+    -- Target (what was affected)
+    target_type TEXT,  -- 'user', 'realm', 'scene', 'prop', etc.
+    target_id UUID,
+
+    -- Context
+    realm_id UUID REFERENCES realm.realms(id) ON DELETE SET NULL,
+
+    -- Event data
+    details JSONB NOT NULL DEFAULT '{}',
+
+    -- Outcome
+    success BOOLEAN NOT NULL DEFAULT true,
+    error_message TEXT,
+
+    -- Immutable timestamp
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
+);
+
+COMMENT ON TABLE audit.events IS 'Immutable audit log of significant system events';
+COMMENT ON COLUMN audit.events.details IS 'JSON payload with event-specific data';
+
+CREATE INDEX idx_audit_events_time ON audit.events (created_at DESC);
+CREATE INDEX idx_audit_events_user ON audit.events (user_id, created_at DESC)
+    WHERE user_id IS NOT NULL;
+CREATE INDEX idx_audit_events_category ON audit.events (category, created_at DESC);
+CREATE INDEX idx_audit_events_target ON audit.events (target_type, target_id, created_at DESC)
+    WHERE target_id IS NOT NULL;
+CREATE INDEX idx_audit_events_realm ON audit.events (realm_id, created_at DESC)
+    WHERE realm_id IS NOT NULL;
+CREATE INDEX idx_audit_events_ip ON audit.events (ip_address, created_at DESC)
+    WHERE ip_address IS NOT NULL;
+
+-- =============================================================================
+-- Login History
+-- =============================================================================
+-- Dedicated table for login attempts (success and failure).
+-- =============================================================================
+
+CREATE TABLE audit.login_history (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+
+    -- User (may be NULL for failed attempts with unknown username)
+    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
+    attempted_username TEXT,
+
+    -- Attempt details
+    success BOOLEAN NOT NULL,
+    failure_reason TEXT,
+    auth_provider auth.auth_provider,
+
+    -- Client info
+    ip_address INET NOT NULL,
+    user_agent TEXT,
+
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
+);
+
+COMMENT ON TABLE audit.login_history IS 'History of login attempts for security monitoring';
+
+CREATE INDEX idx_audit_login_history_user ON audit.login_history (user_id, created_at DESC)
+    WHERE user_id IS NOT NULL;
+CREATE INDEX idx_audit_login_history_ip ON audit.login_history (ip_address, created_at DESC);
+CREATE INDEX idx_audit_login_history_failed ON audit.login_history (ip_address, created_at DESC)
+    WHERE success = false;
+
+-- =============================================================================
+-- Data Export Requests (GDPR compliance)
+-- =============================================================================
+
+CREATE TYPE audit.export_status AS ENUM (
+    'pending',
+    'processing',
+    'completed',
+    'failed',
+    'expired'
+);
+
+CREATE TABLE audit.data_exports (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+
+    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
+
+    -- Request details
+    status audit.export_status NOT NULL DEFAULT 'pending',
+    requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+
+    -- Processing
+    started_at TIMESTAMPTZ,
+    completed_at TIMESTAMPTZ,
+    error_message TEXT,
+
+    -- Result
+    download_path public.asset_path,
+    download_expires_at TIMESTAMPTZ,
+    downloaded_at TIMESTAMPTZ
+);
+
+COMMENT ON TABLE audit.data_exports IS 'User data export requests (GDPR compliance)';
+
+CREATE INDEX idx_audit_data_exports_user ON audit.data_exports (user_id);
+CREATE INDEX idx_audit_data_exports_status ON audit.data_exports (status)
+    WHERE status IN ('pending', 'processing');
+
+-- =============================================================================
+-- Account Deletion Requests (GDPR compliance)
+-- =============================================================================
+
+CREATE TYPE audit.deletion_status AS ENUM (
+    'pending',
+    'processing',
+    'completed',
+    'cancelled'
+);
+
+CREATE TABLE audit.deletion_requests (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+
+    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
+
+    -- Request details
+    status audit.deletion_status NOT NULL DEFAULT 'pending',
+    reason TEXT,
+    requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+
+    -- Grace period (user can cancel during this time)
+    scheduled_for TIMESTAMPTZ NOT NULL,
+    cancelled_at TIMESTAMPTZ,
+
+    -- Processing
+    completed_at TIMESTAMPTZ,
+
+    -- Confirmation
+    confirmed_at TIMESTAMPTZ,
+    confirmation_token_hash TEXT
+);
+
+COMMENT ON TABLE audit.deletion_requests IS 'Account deletion requests with grace period';
+
+CREATE INDEX idx_audit_deletion_requests_user ON audit.deletion_requests (user_id);
+CREATE INDEX idx_audit_deletion_requests_pending ON audit.deletion_requests (scheduled_for)
+    WHERE status = 'pending';
+
+COMMIT;