paliad/internal/db/migrations/058_paliadin_poc.up.sql

-- t-paliad-146: Paliadin PoC — paliad.paliadin_turns.
--
-- Design: docs/design-paliadin-2026-05-07.md §0.5.6 (PoC variant).
--
-- Paliadin is the in-app conversational AI assistant. Phase 0 PoC runs on
-- m's laptop only (PALIADIN_ENABLED=false on prod default), backed by a
-- long-lived `claude` process inside a tmux session — not the Anthropic
-- Messages API. The PoC's load-bearing artefact is monitoring: every
-- turn writes a row here so m can decide via /admin/paliadin whether the
-- feature earns a production v1 build.
--
-- The PoC variant of this table stores the FULL prompt + response (no
-- redaction) because m is the only user, m is m's own compliance officer,
-- and the whole point is to read what was asked later. Production v1
-- swaps to hash-only storage; that's a separate migration.
--
-- Sections:
--   1. CREATE paliad.paliadin_turns (with RLS).
--   2. Indexes.

-- ============================================================================
-- 1. paliad.paliadin_turns
-- ============================================================================

CREATE TABLE paliad.paliadin_turns (
    turn_id            uuid          PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Who asked. FK to paliad.users (not auth.users) so deleting an auth
    -- row leaves the audit trail intact via paliad.users.
    user_id            uuid          NOT NULL REFERENCES paliad.users(id),

    -- Browser session ID (opaque). Lets us group turns into "a single
    -- conversation" without storing the full thread server-side.
    session_id         text          NOT NULL,

    started_at         timestamptz   NOT NULL DEFAULT now(),
    finished_at        timestamptz,                            -- NULL until end-of-turn
    duration_ms        int,                                    -- finished_at - started_at

    -- The user's prompt, verbatim. PoC scope only — production v1 stores
    -- a redacted hash instead. See docs/design-paliadin-2026-05-07.md §3.3.
    user_message       text          NOT NULL,

    -- Claude's response, verbatim, with the [paliadin-meta] trailer
    -- already stripped. The trailer's parsed fields land in `used_tools`,
    -- `rows_seen`, `chip_count`, `classifier_tag` below.
    response           text,

    -- Approximate token count (server-side word_count * 1.3). Claude Code
    -- via tmux doesn't expose Anthropic's usage block, so this is a
    -- coarse heuristic for the dashboard cost trend — not a billing
    -- number.
    response_tokens    int,

    -- Tool names Claude used during this turn, parsed from the
    -- [paliadin-meta] trailer block ("used_tools: search_my_deadlines,
    -- lookup_court"). Empty array means Claude didn't use any tool —
    -- the load-bearing dashboard signal: high tool-use rate justifies
    -- the data-grounding pitch in §8.1.
    used_tools         text[]        NOT NULL DEFAULT '{}'::text[],

    -- Row counts parallel to used_tools (e.g. "rows_seen: 3, 1" → {3, 1}).
    -- Helps spot "tool ran but returned nothing" patterns.
    rows_seen          int[]         NOT NULL DEFAULT '{}'::int[],

    -- Number of action chips Claude embedded in the response.
    chip_count         int           NOT NULL DEFAULT 0,

    -- True if the user closed the SSE stream before Claude finished.
    abandoned          boolean       NOT NULL DEFAULT false,

    -- Which paliad page m was on when he asked. Empty when invoked from
    -- /paliadin directly.
    page_origin        text,

    -- Error code, NULL on success. Possible values:
    --   tmux_unresponsive — couldn't write to the pane / pane died
    --   pane_died         — tmux window closed mid-turn
    --   user_aborted      — abandoned=true synonym, kept for query clarity
    --   timeout           — Claude didn't write the response file in time
    --   prompt_disabled   — PALIADIN_ENABLED=false at request time
    error_code         text,

    -- Coarse self-classification by Claude itself in the [paliadin-meta]
    -- trailer ("data" / "concept" / "navigation" / "meta" / "other").
    -- Drives the use-case-shape histogram on /admin/paliadin.
    classifier_tag     text
);

-- ============================================================================
-- 2. Indexes
-- ============================================================================

-- Per-user timeline (the "my recent paliadin turns" query). Most rows for
-- the PoC will share user_id=m, so this index is mostly useful as a sort
-- helper.
CREATE INDEX paliadin_turns_user_started_idx
    ON paliad.paliadin_turns(user_id, started_at DESC);

-- Global timeline for /admin/paliadin dashboard. Keeps the dashboard
-- queries (top-N recent turns, daily counts) on an index scan even as
-- the table grows.
CREATE INDEX paliadin_turns_started_idx
    ON paliad.paliadin_turns(started_at DESC);

-- Histogram queries on classifier_tag. Tiny table at PoC scale; the
-- index pays for itself once we have weeks of data.
CREATE INDEX paliadin_turns_classifier_idx
    ON paliad.paliadin_turns(classifier_tag, started_at DESC)
    WHERE classifier_tag IS NOT NULL;

-- ============================================================================
-- 3. RLS
-- ============================================================================

ALTER TABLE paliad.paliadin_turns ENABLE ROW LEVEL SECURITY;

-- A user sees their own turns; global_admin sees all rows. The /admin/
-- paliadin dashboard runs under m (global_admin) and so sees the full
-- log. Other users would only see their own — though in PoC scope
-- there's only m, the policy is the production-shape from day one.
CREATE POLICY paliadin_turns_select
    ON paliad.paliadin_turns FOR SELECT
    USING (
      user_id = auth.uid()
      OR EXISTS (SELECT 1 FROM paliad.users u
                  WHERE u.id = auth.uid() AND u.global_role = 'global_admin')
    );

-- Service-role (paliad backend) writes. Direct-auth INSERT is blocked.
-- Paliad runs with the service role today so the policy is inert in
-- practice; we still enable RLS so future direct-auth callers are gated.
CREATE POLICY paliadin_turns_insert_admin_only
    ON paliad.paliadin_turns FOR INSERT
    WITH CHECK (false);

CREATE POLICY paliadin_turns_update_admin_only
    ON paliad.paliadin_turns FOR UPDATE
    USING (false);

COMMENT ON TABLE paliad.paliadin_turns IS
    'Per-turn audit log for Paliadin (in-app AI). PoC variant stores full prompt + response — production v1 will swap to hash-only. Powers /admin/paliadin dashboard. Design: docs/design-paliadin-2026-05-07.md §0.5.6.';