ProofedMesh/db/trust/migrations/0004_approval_sessions.up.sql

-- Migration: 0004_approval_sessions
-- Content-gebundene Approvals gemäß docs/architecture/v1.1.md §4.3 und
-- Phase-1-Spezifikation [DEV-608](/DEV/issues/DEV-608).
--
-- Drei Tabellen, strikt in der Reihenfolge der Session → Preview → Confirm
-- Pipeline des approval-service:
--
--   1. `approval_sessions` — eine durch die Pocket-ID-Session gestützte
--      Approval-Session; sie bindet (session_id, user_sub) an ein Ablauf-
--      datum und wird vom Approval-UI pro Browser-Login einmal erzeugt.
--   2. `approval_previews` — ein serverseitig gerendertes Freigabe-Objekt;
--      Content-Hash (`H_content`), Salt und Challenge-Nonce werden gleich
--      bei der Vorschau festgenagelt, damit das Confirm-Step eine *andere*
--      Payload nicht nachträglich unterschieben kann.
--   3. `approvals` — die bestätigte, DSSE-signierte Approval. `tlog_status`
--      spiegelt den TLog-Einwurf; `attestation-pending` bedeutet die Row
--      ist persistiert, der TLog-Submit aber noch nicht quittiert und wird
--      vom attest-reconciler bzw. einem späteren Worker nachgezogen.
--
-- Design-Entscheidungen:
--   * Alle `*_id`-Spalten sind TEXT (opaque IDs; Service-generiert, nicht
--     UUID gezwungen) — bleibt kompatibel mit dem bestehenden Stil der
--     Trust-DB.
--   * `content_canonical` wird in Preview mitgespeichert, damit Confirm
--     den Hash deterministisch nachrechnen und das signierte Statement
--     ohne erneutes Rendern aufbauen kann.
--   * `tlog_status` ist ein Enum via CHECK statt pg-native ENUM, um die
--     Migration ohne CREATE TYPE drop-safe zu halten.

CREATE TABLE IF NOT EXISTS approval_sessions (
    session_id    TEXT PRIMARY KEY
                     CHECK (length(session_id) BETWEEN 16 AND 128),
    user_sub      TEXT NOT NULL
                     CHECK (length(user_sub) BETWEEN 1 AND 256),
    active_until  TIMESTAMPTZ NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_approval_sessions_user
    ON approval_sessions (user_sub);

CREATE INDEX idx_approval_sessions_active_until
    ON approval_sessions (active_until);

CREATE TABLE IF NOT EXISTS approval_previews (
    preview_id          TEXT PRIMARY KEY
                           CHECK (length(preview_id) BETWEEN 16 AND 128),
    session_id          TEXT NOT NULL
                           REFERENCES approval_sessions(session_id)
                           ON DELETE CASCADE,
    content_hash        TEXT NOT NULL
                           CHECK (content_hash ~ '^sha256:[0-9a-f]{64}$'),
    content_canonical   BYTEA NOT NULL,
    salt                BYTEA NOT NULL
                           CHECK (length(salt) BETWEEN 16 AND 64),
    challenge_nonce     BYTEA NOT NULL
                           CHECK (length(challenge_nonce) BETWEEN 16 AND 64),
    verification_digits TEXT NOT NULL
                           CHECK (verification_digits ~ '^[0-9]{4,12}$'),
    expires_at          TIMESTAMPTZ NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    consumed_at         TIMESTAMPTZ
);

CREATE INDEX idx_approval_previews_session
    ON approval_previews (session_id);

CREATE INDEX idx_approval_previews_expires
    ON approval_previews (expires_at)
    WHERE consumed_at IS NULL;

CREATE TABLE IF NOT EXISTS approvals (
    approval_id      TEXT PRIMARY KEY
                        CHECK (length(approval_id) BETWEEN 16 AND 128),
    preview_id       TEXT NOT NULL
                        REFERENCES approval_previews(preview_id),
    session_id       TEXT NOT NULL
                        REFERENCES approval_sessions(session_id),
    user_sub         TEXT NOT NULL,
    content_hash     TEXT NOT NULL
                        CHECK (content_hash ~ '^sha256:[0-9a-f]{64}$'),
    envelope_json    JSONB NOT NULL,
    attestation_ref  TEXT NOT NULL
                        CHECK (attestation_ref ~ '^sha256:[0-9a-f]{64}$'),
    tlog_status      TEXT NOT NULL DEFAULT 'attestation-pending'
                        CHECK (tlog_status IN ('attestation-pending',
                                               'logged',
                                               'failed')),
    log_index        BIGINT,
    tlog_error       TEXT,
    confirmed_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    logged_at        TIMESTAMPTZ,
    CONSTRAINT approvals_log_consistency
        CHECK ((tlog_status = 'logged') = (log_index IS NOT NULL))
);

CREATE INDEX idx_approvals_tlog_pending
    ON approvals (confirmed_at)
    WHERE tlog_status = 'attestation-pending';

CREATE INDEX idx_approvals_user
    ON approvals (user_sub, confirmed_at);