broad-announce/migrations/009_auth.up.sql

-- 009_auth.up.sql
-- M13a: Multi-tenant auth schema for authd (port 8804).
--
-- Tenancy model (per M13 survey, decision 1.1 = C, decision 2.1 = C hybrid):
--   - One Postgres DB for everything (M11 already uses it for DLQ, deliveries).
--   - Schema 'auth' is the namespace for authd tables.
--   - A 'tenant' is a customer company. Each tenant has a 'tenant_slug'
--     used in routes (e.g. /v1/tenants/acme-001/sources) and as a
--     partition key for downstream queries.
--   - A 'user' belongs to ONE tenant (super-admins have tenant_id=NULL
--     and role='super_admin'; tenant-admins have role='tenant_admin').
--   - 'global_id' on users is for SSO future (v2). For now it's
--     just a stable opaque id we generate server-side.
--
-- Authentication (per survey 2.2 = A in-house JWT, 2.4 = B magic-link):
--   - 'magic_links' stores the one-time tokens emailed to invited
--     users. After use they're marked 'consumed_at'.
--   - 'refresh_tokens' stores the long-lived tokens (7d TTL by default)
--     that authd issues alongside the short-lived (15m) access JWT.
--     The refresh token is a 256-bit random string, NOT a JWT — the
--     server-side row is the source of truth (decision: refresh in
--     server-side Postgres, rotated on use).
--   - 'sessions' is the audit trail of every login/logout, used for
--     security investigations ("who was logged in when X happened").
--
-- Audit (per survey: every service writes its own audit_log row):
--   - 'audit_log' is shared across services. action is namespaced
--     as 'auth.login', 'auth.logout', 'auth.invite', 'cert.issue',
--     'cert.revoke', etc.
--   - actor_user_id is nullable for system actions (cron, service tokens).
--   - payload is jsonb for service-specific structured data.
--
-- Surfaces: authd (CRUD users/magic_links/sessions/refresh_tokens + reads
-- audit_log), admind (reads audit_log for the admin UI), and a
-- 'verify_session' SQL function used by ingestd/admind to validate
-- a presented access JWT against the refresh-token table (so we can
-- revoke a session by deleting its row).

CREATE SCHEMA IF NOT EXISTS auth;
SET search_path TO auth, public;

-- pgcrypto provides gen_random_bytes() and digest() (used by the
-- refresh-token functions below for secure random + sha256).
-- gen_random_uuid() comes from pgcrypto on older Postgres and is
-- built-in on 13+, but the extension is harmless to enable.
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ---------------------------------------------------------------------------
-- Tenants
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tenants (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug            TEXT NOT NULL UNIQUE
                    CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,62}[a-z0-9]$'),
    display_name    TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'suspended', 'archived')),
    contact_email   TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    archived_at     TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS tenants_status_idx ON tenants(status) WHERE status != 'archived';

-- ---------------------------------------------------------------------------
-- Users
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- global_id is for SSO migration (v2). Until then, the per-tenant
    -- (id, tenant_id) pair is the unique key.
    global_id       UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    tenant_id       UUID REFERENCES tenants(id) ON DELETE CASCADE,
    email           TEXT NOT NULL,
    role            TEXT NOT NULL
                    CHECK (role IN ('super_admin', 'tenant_admin', 'viewer')),
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'active', 'disabled')),
    display_name    TEXT,
    -- bcrypt of the password. NULL until the magic link is consumed.
    password_hash   TEXT,
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    disabled_at     TIMESTAMPTZ
);

-- Email is unique per tenant (or unique globally for super_admins).
-- Partial unique index for super_admins (tenant_id IS NULL).
CREATE UNIQUE INDEX IF NOT EXISTS users_email_global_uniq
    ON users(email) WHERE tenant_id IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS users_email_per_tenant_uniq
    ON users(email, tenant_id) WHERE tenant_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS users_tenant_idx ON users(tenant_id) WHERE tenant_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS users_status_idx ON users(status) WHERE status != 'active';

-- ---------------------------------------------------------------------------
-- Magic links (invite + password-set)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS magic_links (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- token_hash is sha256(token). The plaintext token is emailed and
    -- never stored. Lookup is by hash.
    token_hash      BYTEA NOT NULL UNIQUE,
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    purpose         TEXT NOT NULL
                    CHECK (purpose IN ('invite', 'password_reset', 'mfa_reset')),
    expires_at      TIMESTAMPTZ NOT NULL,
    consumed_at     TIMESTAMPTZ,
    -- ip that consumed it (audit trail)
    consumed_ip     INET,
    -- user-agent that consumed it (audit trail)
    consumed_ua     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS magic_links_user_idx ON magic_links(user_id);
CREATE INDEX IF NOT EXISTS magic_links_unconsumed_idx
    ON magic_links(expires_at) WHERE consumed_at IS NULL;

-- ---------------------------------------------------------------------------
-- Refresh tokens (server-side session table)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- token_hash is sha256(token). The plaintext is in the httpOnly
    -- cookie / mobile secure storage. Token is a 32-byte random hex string.
    token_hash      BYTEA NOT NULL UNIQUE,
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    -- jti of the access JWT that this refresh was issued with. Used
    -- to correlate logs and to make rotation auditable.
    access_jti      TEXT NOT NULL,
    -- The previous token's hash (if rotated). Lets us detect a stolen
    -- token being used after rotation.
    parent_hash     BYTEA,
    expires_at      TIMESTAMPTZ NOT NULL,
    revoked_at      TIMESTAMPTZ,
    -- The 'family' id groups all rotations of one login. If a non-head
    -- token in a family is used, the family is killed (re-use detection).
    family_id       UUID NOT NULL DEFAULT gen_random_uuid(),
    created_ip      INET,
    created_ua      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS refresh_tokens_user_idx ON refresh_tokens(user_id);
CREATE INDEX IF NOT EXISTS refresh_tokens_family_idx ON refresh_tokens(family_id);
CREATE INDEX IF NOT EXISTS refresh_tokens_active_idx
    ON refresh_tokens(expires_at) WHERE revoked_at IS NULL;

-- ---------------------------------------------------------------------------
-- Sessions (read-only audit view of refresh_tokens, denormalized for
-- fast "what devices is this user logged in from" queries).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    refresh_id      UUID NOT NULL REFERENCES refresh_tokens(id) ON DELETE CASCADE,
    ip              INET,
    user_agent      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    revoked_at      TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS sessions_user_idx ON sessions(user_id);
CREATE INDEX IF NOT EXISTS sessions_active_idx
    ON sessions(user_id) WHERE revoked_at IS NULL;

-- ---------------------------------------------------------------------------
-- Audit log
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id              BIGSERIAL PRIMARY KEY,
    occurred_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- 'auth.login', 'auth.logout', 'auth.invite', 'auth.magic_consume',
    -- 'cert.issue', 'cert.revoke', 'tenant.create', 'user.disable', etc.
    action          TEXT NOT NULL,
    actor_user_id   UUID REFERENCES users(id) ON DELETE SET NULL,
    actor_ip        INET,
    actor_ua        TEXT,
    -- The target of the action. For 'auth.invite' it's the invited
    -- user. For 'cert.revoke' it's the source/cert id. Free-form uuid.
    target_id       UUID,
    -- Tenant scope (NULL for super-admin global actions).
    tenant_id       UUID REFERENCES tenants(id) ON DELETE SET NULL,
    -- Action-specific structured data. Example for 'auth.login':
    --   {"email": "...", "success": true, "failure_reason": null}
    payload         JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS audit_log_action_time_idx
    ON audit_log(action, occurred_at DESC);
CREATE INDEX IF NOT EXISTS audit_log_actor_idx
    ON audit_log(actor_user_id, occurred_at DESC)
    WHERE actor_user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS audit_log_tenant_idx
    ON audit_log(tenant_id, occurred_at DESC)
    WHERE tenant_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS audit_log_target_idx
    ON audit_log(target_id) WHERE target_id IS NOT NULL;

-- ---------------------------------------------------------------------------
-- SQL functions
-- ---------------------------------------------------------------------------

-- generate_magic_link_token returns a 32-byte cryptographically random
-- token, hex-encoded. Callers hash it before storing.
CREATE OR REPLACE FUNCTION generate_magic_link_token()
RETURNS TEXT
LANGUAGE plpgsql
SET search_path = auth, public
AS $$
DECLARE
    raw BYTEA;
BEGIN
    raw := gen_random_bytes(32);
    RETURN encode(raw, 'hex');
END;
$$;

-- issue_refresh_token: create a new refresh token row + the matching
-- session row in one transaction. Returns the plaintext token (caller
-- hashes it for the response and stores only the hash).
CREATE OR REPLACE FUNCTION issue_refresh_token(
    p_user_id    UUID,
    p_access_jti TEXT,
    p_ttl_seconds INTEGER,
    p_ip         INET,
    p_ua         TEXT
)
RETURNS TABLE (id UUID, token TEXT, family_id UUID, expires_at TIMESTAMPTZ)
LANGUAGE plpgsql
SET search_path = auth, public
AS $$
DECLARE
    v_raw        BYTEA := gen_random_bytes(32);
    v_token      TEXT  := encode(v_raw, 'hex');
    v_hash       BYTEA := digest(v_raw, 'sha256');
    v_new_id     UUID;
    v_family     UUID := gen_random_uuid();
    v_expires    TIMESTAMPTZ := NOW() + (p_ttl_seconds || ' seconds')::INTERVAL;
BEGIN
    INSERT INTO auth.refresh_tokens
        (token_hash, user_id, access_jti, family_id, expires_at, created_ip, created_ua)
    VALUES
        (v_hash, p_user_id, p_access_jti, v_family, v_expires, p_ip, p_ua)
    RETURNING auth.refresh_tokens.id INTO v_new_id;

    INSERT INTO auth.sessions
        (user_id, refresh_id, ip, user_agent)
    VALUES
        (p_user_id, v_new_id, p_ip, p_ua);

    RETURN QUERY SELECT v_new_id, v_token, v_family, v_expires;
END;
$$;

-- rotate_refresh_token: consume a refresh token and issue a new one
-- in the same family. If the presented token was already revoked OR
-- the family has a re-use signal, the whole family is killed.
CREATE OR REPLACE FUNCTION rotate_refresh_token(
    p_presented_token TEXT,
    p_new_access_jti  TEXT,
    p_ttl_seconds     INTEGER,
    p_ip              INET,
    p_ua              TEXT
)
RETURNS TABLE (id UUID, token TEXT, family_id UUID, expires_at TIMESTAMPTZ, killed_family BOOLEAN)
LANGUAGE plpgsql
SET search_path = auth, public
AS $$
DECLARE
    v_presented_hash BYTEA := digest(decode(p_presented_token, 'hex'), 'sha256');
    v_old            RECORD;
    v_raw            BYTEA;
    v_token          TEXT;
    v_hash           BYTEA;
    v_new_id         UUID;
    v_expires        TIMESTAMPTZ;
    v_killed         BOOLEAN := FALSE;
BEGIN
    SELECT rt.id, rt.user_id, rt.family_id, rt.expires_at, rt.revoked_at, rt.parent_hash
      INTO v_old
      FROM auth.refresh_tokens rt
     WHERE rt.token_hash = v_presented_hash;

    IF NOT FOUND THEN
        -- Unknown token — likely forgeries or already cleaned up.
        RAISE EXCEPTION 'unknown_refresh_token' USING ERRCODE = '22023';
    END IF;

    IF v_old.expires_at < NOW() THEN
        RAISE EXCEPTION 'expired_refresh_token' USING ERRCODE = '22023';
    END IF;

    -- Re-use detection: if this token is already revoked OR has a
    -- child (parent_hash set), someone is replaying a stolen token.
    IF v_old.revoked_at IS NOT NULL OR v_old.parent_hash IS NOT NULL THEN
        -- Kill the whole family. Every token in it is suspect.
        UPDATE auth.refresh_tokens
           SET revoked_at = NOW()
         WHERE auth.refresh_tokens.family_id = v_old.family_id
           AND auth.refresh_tokens.revoked_at IS NULL;
        UPDATE auth.sessions
           SET revoked_at = NOW()
         WHERE auth.sessions.user_id = v_old.user_id
           AND auth.sessions.refresh_id IN (
               SELECT auth.refresh_tokens.id
                 FROM auth.refresh_tokens
                WHERE auth.refresh_tokens.family_id = v_old.family_id
           )
           AND auth.sessions.revoked_at IS NULL;
        v_killed := TRUE;
        RAISE EXCEPTION 'refresh_token_reuse' USING ERRCODE = '22023';
    END IF;

    -- Revoke the old, issue the new
    UPDATE auth.refresh_tokens
       SET revoked_at = NOW()
     WHERE auth.refresh_tokens.id = v_old.id;

    v_raw     := gen_random_bytes(32);
    v_token   := encode(v_raw, 'hex');
    v_hash    := digest(v_raw, 'sha256');
    v_expires := NOW() + (p_ttl_seconds || ' seconds')::INTERVAL;

    INSERT INTO auth.refresh_tokens
        (token_hash, user_id, access_jti, family_id, parent_hash, expires_at, created_ip, created_ua)
    VALUES
        (v_hash, v_old.user_id, p_new_access_jti, v_old.family_id, v_presented_hash, v_expires, p_ip, p_ua)
    RETURNING auth.refresh_tokens.id INTO v_new_id;

    UPDATE auth.sessions
       SET last_seen_at = NOW(),
           refresh_id   = v_new_id,
           ip           = p_ip,
           user_agent   = p_ua
     WHERE refresh_id = v_old.id;

    RETURN QUERY SELECT v_new_id, v_token, v_old.family_id, v_expires, v_killed;
END;
$$;

-- revoke_refresh_token: revoke a single token by plaintext.
CREATE OR REPLACE FUNCTION revoke_refresh_token(p_token TEXT)
RETURNS BOOLEAN
LANGUAGE plpgsql
SET search_path = auth, public
AS $$
DECLARE
    v_hash BYTEA := digest(decode(p_token, 'hex'), 'sha256');
    v_id   UUID;
BEGIN
    UPDATE auth.refresh_tokens
       SET revoked_at = NOW()
     WHERE token_hash = v_hash
       AND revoked_at IS NULL
    RETURNING auth.refresh_tokens.id INTO v_id;

    IF v_id IS NULL THEN
        RETURN FALSE;
    END IF;

    UPDATE auth.sessions
       SET revoked_at = NOW()
     WHERE refresh_id = v_id
       AND revoked_at IS NULL;

    RETURN TRUE;
END;
$$;

-- ---------------------------------------------------------------------------
-- updated_at triggers
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION auth_set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS tenants_set_updated_at ON tenants;
CREATE TRIGGER tenants_set_updated_at
    BEFORE UPDATE ON tenants
    FOR EACH ROW EXECUTE FUNCTION auth_set_updated_at();

DROP TRIGGER IF EXISTS users_set_updated_at ON users;
CREATE TRIGGER users_set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION auth_set_updated_at();