chattyness/db/schema/tables/080_audit.sql

-- 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;