docs/schema.sql

-- CHAMP pediatric chatbot — PostgreSQL schema
--
-- Draft, 2026-08-05. Implements §6.3 of INFRASTRUCTURE_PLAN.md.
-- Nothing here is deployed. Target: PostgreSQL 15+ (uses gen_random_uuid()
-- from core, and btree_gist for the temporal exclusion constraints).
--
-- Choices this file had to make that the prose left open — react to these:
--   * Native ENUMs for closed technical sets; a lookup table for occupation,
--     which is expected to change (see role_options).
--   * valid_to IS NULL means "current row"; overlap is prevented by exclusion
--     constraints rather than by application code.
--   * Message content columns are named *_tokenized so that code touching raw
--     user text is obvious at a glance.
--   * The vault's primary key is (conversation_id, surrogate), which makes the
--     fail-closed rule structural: a surrogate cannot be resolved without
--     knowing which conversation it belongs to. A second unique constraint,
--     on (conversation_id, value_hmac), makes the mapping bijective within a
--     conversation — one real value in, one surrogate out, both directions.
--   * ON DELETE CASCADE from users almost everywhere, because erasure is a
--     first-class requirement. The exceptions are marked and need legal input.

CREATE EXTENSION IF NOT EXISTS btree_gist;

-- ============================================================================
-- Enumerated types
-- ============================================================================

CREATE TYPE account_type_t     AS ENUM ('guest', 'registered');
CREATE TYPE auth_provider_t    AS ENUM ('firebase', 'whatsapp');
CREATE TYPE platform_t         AS ENUM ('web', 'mobile', 'whatsapp');
CREATE TYPE message_role_t      AS ENUM ('user', 'assistant');
CREATE TYPE run_outcome_t       AS ENUM ('passed', 'fallback_refusal', 'error');
CREATE TYPE impact_source_t     AS ENUM ('inference', 'infrastructure');

-- Execution status of one pipeline step. Deliberately NOT the same thing as a
-- judge verdict: a judge that returns FAIL executed perfectly and is 'ok' — the
-- verdict lives in the step's payload. 'error'/'timeout' mean the step itself
-- did not complete.
CREATE TYPE step_status_t       AS ENUM ('ok', 'error', 'timeout');
-- NB: where inference ran is the `inference_providers` lookup table, not an
-- enum — self-hosted and the serverless providers are one axis, and the list
-- of providers changes without warning.
-- NB: consent purposes are a lookup TABLE, not an enum — see consent_purposes
-- below. More purposes are expected, and adding one should not need a migration.
CREATE TYPE consent_action_t   AS ENUM ('granted', 'withdrawn');
CREATE TYPE consent_source_t   AS ENUM ('signup', 'settings', 'admin', 'import');

-- Mirrors ProfileBase.age_group. Brackets are a survey instrument: changing
-- them invalidates cohort comparisons, so add new values rather than editing
-- old ones (ALTER TYPE ... ADD VALUE).
CREATE TYPE age_group_t AS ENUM
    ('0-18', '18-24', '25-34', '35-44', '45-54', '55-64', '65+');

-- ProfileBase currently allows only 'M'/'F'. That is narrow for a research
-- instrument and widening it later is a data migration; ADD VALUE is the
-- intended escape hatch.
CREATE TYPE gender_t AS ENUM ('M', 'F');

-- OPEN ITEM (§6.3.9): this list is incomplete and Malik will supply the full
-- set once the redactor's entity taxonomy is settled. Known gap already: a city
-- ('Paris') has no category — 'address' is a different granularity and would
-- draw from a different surrogate pool.
CREATE TYPE pii_entity_t AS ENUM
    ('person_name', 'phone', 'email', 'address', 'date', 'health_id', 'other');

-- ============================================================================
-- Languages
-- ============================================================================

-- A lookup table rather than CHECK (lang IN ('en','fr')). The app is bilingual
-- today, but a third language should be an INSERT, not a migration touching
-- every constraint that happens to mention a language — and there are several
-- (users.locale, messages.lang, consent_notice_texts.lang).
CREATE TABLE languages (
    lang_code  text PRIMARY KEY,          -- ISO 639-1
    is_active  boolean NOT NULL DEFAULT true
);

INSERT INTO languages (lang_code) VALUES ('en'), ('fr');

-- ============================================================================
-- Identity
-- ============================================================================

-- No lifecycle status column: account lifecycle is not specified yet, so row
-- absence means deleted. Add states (suspended, pending, …) when a requirement
-- for them actually appears, rather than guessing at them now.
CREATE TABLE users (
    user_id       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    account_type  account_type_t   NOT NULL,
    locale        text             NOT NULL DEFAULT 'en'
                      REFERENCES languages(lang_code),

    -- Reserved pending the username decision (§6.3.2). Nullable so it costs
    -- nothing today; the unique index below is the part that is expensive to
    -- add retroactively. Firebase cannot enforce this — it has no username
    -- concept — so uniqueness has to live here.
    username      text,

    created_at    timestamptz NOT NULL DEFAULT now(),

    -- Firebase Auth tracks last sign-in too. This is the app-side copy, so
    -- "who has not come back in six months" is a query rather than a call out
    -- to Firebase for every user. NULL until the first login after signup.
    last_login_at timestamptz
);

-- Case-insensitive uniqueness without requiring the citext extension.
CREATE UNIQUE INDEX users_username_lower_uniq
    ON users (lower(username)) WHERE username IS NOT NULL;

COMMENT ON TABLE users IS
    'Opaque internal identity. Holds no direct identifiers: email, phone and '
    'credentials live only in Firebase Auth.';

-- One user may hold several identity bindings: a Firebase uid (including an
-- anonymous one for guests) and, if WhatsApp ships, an E.164 phone number.
-- user_id deliberately does NOT require a firebase_uid.
CREATE TABLE auth_identities (
    auth_identity_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id          uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    provider         auth_provider_t NOT NULL,
    provider_uid     text NOT NULL,
    linked_at        timestamptz NOT NULL DEFAULT now(),

    UNIQUE (provider, provider_uid)
);

CREATE INDEX auth_identities_user_idx ON auth_identities (user_id);

COMMENT ON COLUMN auth_identities.provider_uid IS
    'Firebase uid, or E.164 phone for WhatsApp. Guest upgrade via Firebase '
    'account linking preserves the uid, so the row and the user_id both survive '
    'the upgrade — never mint a new user_id for an upgrading guest.';

-- ============================================================================
-- Consent — append-only event log (§6.3.3)
-- ============================================================================

-- Purposes as data, not as a type: the list is expected to grow, and adding a
-- purpose should be an INSERT rather than a migration. is_active retires a
-- purpose without invalidating the historical events that reference it.
CREATE TABLE consent_purposes (
    purpose_code  text PRIMARY KEY,
    is_active     boolean NOT NULL DEFAULT true
);

INSERT INTO consent_purposes (purpose_code) VALUES
    -- Use of the service, and retention of conversations so that users can see
    -- their own history when they log back in.
    ('service_and_storage'),
    -- Use of conversations to improve the system and the model, for research.
    ('research_improvement');

COMMENT ON TABLE consent_purposes IS
    'Consent taxonomy as of 2026-08-06. Notice text and display labels live '
    'outside the database (client i18n / the consent notice itself); this table '
    'holds only the codes that consent_events references.';

-- The notice a person was actually shown. Same reasoning as `prompts` storing
-- the full template rather than a hash, only with more at stake: if someone
-- disputes what they agreed to, the text they saw is the evidence, and a bare
-- version string in consent_events proves nothing on its own.
--
-- A version is published once; its wording exists per language, so the text
-- lives in a child table rather than repeating published_at per translation.
CREATE TABLE consent_notices (
    notice_version text PRIMARY KEY,
    published_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE consent_notice_texts (
    notice_version text NOT NULL REFERENCES consent_notices(notice_version),
    lang           text NOT NULL REFERENCES languages(lang_code),
    body           text NOT NULL,

    PRIMARY KEY (notice_version, lang)
);

COMMENT ON TABLE consent_notice_texts IS
    'What the person read, in the language they read it in. Not client i18n: '
    'display labels belong in the client, but the consent notice is the '
    'agreement itself and has to be reproducible years later.';

CREATE TABLE consent_events (
    consent_event_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

    -- OPEN ITEM — see "can a user actually be deleted?" in §6.3.9 of
    -- INFRASTRUCTURE_PLAN.md. RESTRICT means deleting a user with any consent
    -- event fails, i.e. no user can currently be hard-deleted. Deliberate, to
    -- force the decision: erasure wants the record gone, accountability wants
    -- proof that consent was obtained and its withdrawal honoured. Resolve with
    -- legal, then adjust this and the trigger below together.
    user_id        uuid NOT NULL REFERENCES users(user_id) ON DELETE RESTRICT,

    purpose_code   text NOT NULL REFERENCES consent_purposes(purpose_code),
    action         consent_action_t  NOT NULL,

    -- Which wording was in force. A foreign key, not free text: an unregistered
    -- version would otherwise be indistinguishable from a typo.
    notice_version text NOT NULL REFERENCES consent_notices(notice_version),
    occurred_at    timestamptz       NOT NULL DEFAULT now(),
    source         consent_source_t  NOT NULL
);

CREATE INDEX consent_events_lookup_idx
    ON consent_events (user_id, purpose_code, occurred_at DESC);

-- Append-only, enforced in the database rather than by convention.
--
-- OPEN ITEM: this fires on DELETE as well as UPDATE, and cascading deletes fire
-- child triggers — so it blocks every deletion path out of `users`, whatever the
-- foreign key above is set to. It needs narrowing (probably to UPDATE only)
-- before erasure can work. Left as-is on purpose so it is resolved together with
-- the foreign key, not separately. See §6.3.9 of INFRASTRUCTURE_PLAN.md.
CREATE FUNCTION consent_events_append_only() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'consent_events is append-only (attempted %)', TG_OP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER consent_events_no_mutation
    BEFORE UPDATE OR DELETE ON consent_events
    FOR EACH ROW EXECUTE FUNCTION consent_events_append_only();

-- Current consent state is derived, never stored.
CREATE VIEW user_consent_current AS
SELECT DISTINCT ON (user_id, purpose_code)
       user_id,
       purpose_code,
       action,
       notice_version,
       occurred_at
FROM   consent_events
ORDER  BY user_id, purpose_code, occurred_at DESC, consent_event_id DESC;

COMMENT ON VIEW user_consent_current IS
    'Latest event per (user, purpose). action = ''granted'' means currently '
    'consented. Absence of a row means never asked.';

-- ============================================================================
-- Demographics — temporal, not snapshotted (§6.3.4)
-- ============================================================================

-- These attributes are collected under consent and must be erasable
-- independently of the account. valid_to IS NULL = the current row.
CREATE TABLE user_demographics_history (
    user_id    uuid        NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    valid_from timestamptz NOT NULL DEFAULT now(),
    valid_to   timestamptz,
    age_group  age_group_t,
    gender     gender_t,

    -- The exclusion constraint below already rules out duplicates, so this is
    -- about identity rather than uniqueness: logical replication needs a replica
    -- identity, and migration tooling expects a declared key.
    PRIMARY KEY (user_id, valid_from),

    CONSTRAINT demographics_interval_valid
        CHECK (valid_to IS NULL OR valid_to > valid_from),

    -- No overlapping intervals per user. This also implies at most one
    -- current row, since two open intervals would both extend to infinity.
    CONSTRAINT demographics_no_overlap EXCLUDE USING gist (
        user_id WITH =,
        tstzrange(valid_from, COALESCE(valid_to, 'infinity')) WITH &&
    )
);

-- Redundant with the exclusion constraint for correctness, but makes the
-- "current demographics" lookup a single index hit.
CREATE UNIQUE INDEX demographics_current_idx
    ON user_demographics_history (user_id) WHERE valid_to IS NULL;

-- Occupation. ProfileBase.roles is a SET (1..5 selections), so this is a
-- junction, not a column. A lookup table rather than an ENUM because the
-- option list is research-driven and expected to change; is_active lets an
-- option be retired without breaking history.
CREATE TABLE role_options (
    role_code     text PRIMARY KEY,
    is_active     boolean NOT NULL DEFAULT true
);

INSERT INTO role_options (role_code) VALUES
    ('patient'), ('clinician'), ('computer-scientist'),
    ('researcher'), ('other');

COMMENT ON TABLE role_options IS
    'The user-facing occupation the person selects for themselves: clinician, '
    'researcher, computer scientist, patient, other. Matches ProfileBase.roles. '
    'Display labels live in the client (translations.tsx), not here — the app is '
    'bilingual and i18n is already client-side.';

CREATE TABLE user_role_history (
    user_id    uuid        NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    role_code  text        NOT NULL REFERENCES role_options(role_code),
    valid_from timestamptz NOT NULL DEFAULT now(),
    valid_to   timestamptz,

    -- role_code is part of the key: occupation is multi-valued, so one user can
    -- hold several roles over the same interval.
    PRIMARY KEY (user_id, role_code, valid_from),

    CONSTRAINT user_role_interval_valid
        CHECK (valid_to IS NULL OR valid_to > valid_from),

    CONSTRAINT user_role_no_overlap EXCLUDE USING gist (
        user_id   WITH =,
        role_code WITH =,
        tstzrange(valid_from, COALESCE(valid_to, 'infinity')) WITH &&
    )
);

CREATE INDEX user_role_current_idx
    ON user_role_history (user_id) WHERE valid_to IS NULL;

-- The point-in-time join lives in message_demographics, defined after messages.

-- ============================================================================
-- What produced an answer — reference tables (§6.3.6)
-- ============================================================================
--
-- All lookup tables rather than enums: every one of these lists changes on a
-- different clock from the code, and none should need a migration to extend.
--
-- ⚠️ A foreign key requires the referenced row to exist at write time. If the
-- app writes an answer citing a wiki build or prompt version nobody registered,
-- the INSERT fails and the message is lost to protect its own metadata — a bad
-- trade. Register builds and prompt versions at application startup (the image
-- already builds the wiki) so the reference always resolves.

-- Which model generated the answer. model_code carries the version, since that
-- is how models are actually identified in the wild.
CREATE TABLE models (
    model_code   text PRIMARY KEY,
    is_active    boolean NOT NULL DEFAULT true
);

INSERT INTO models (model_code) VALUES
    ('gemma-4-26b-e4b'),
    ('gpt-oss-20b'),
    ('gpt-5-mini-2025-08-07'),
    ('gpt-5.3-chat-latest'),
    ('gemini-3-flash-preview');

-- Where inference ran. Replaces the old inference_backend enum: self-hosting
-- and the serverless providers are the same axis, so one table covers both.
CREATE TABLE inference_providers (
    provider_code  text PRIMARY KEY,
    is_self_hosted boolean NOT NULL DEFAULT false,
    is_active      boolean NOT NULL DEFAULT true
);

INSERT INTO inference_providers (provider_code, is_self_hosted) VALUES
    ('self_hosted',  true),
    ('vertex',       false),
    ('groq',         false),
    ('scaleway',     false),
    ('huggingface',  false),
    ('openai',       false),
    ('google',       false);

COMMENT ON COLUMN inference_providers.is_self_hosted IS
    'Lets impact and cost queries split metered from estimated without '
    'hardcoding provider names.';

-- Which skill answered. Mirrors the directories under agent/skills/; the list
-- grows every time a variant is trialled.
CREATE TABLE skill_variants (
    skill_variant text PRIMARY KEY,
    is_active     boolean NOT NULL DEFAULT true
);

INSERT INTO skill_variants (skill_variant) VALUES
    ('pediatry_wiki'),
    ('pediatry_wiki_reject_ledger'),
    ('pediatry_wiki_reject_ledger_split_index'),
    ('pediatry_wiki_self_judge'),
    ('pediatry_wiki_no_critic'),
    ('pediatry_wiki_short'),
    ('pediatry_wiki_verbatim'),
    ('pediatry'),
    ('champ');

-- Which build of the knowledge base was in play. Hash is the identity; name is
-- what a human says out loud; git_sha ties it back to the source revision.
CREATE TABLE wiki_builds (
    wiki_version  text PRIMARY KEY,       -- content hash of the built wiki
    name          text,                   -- hand-assigned label
    git_sha       text,
    article_count int,
    built_at      timestamptz NOT NULL DEFAULT now()
);

-- Prompt templates, content-addressed. Stored per template rather than per
-- "prompt set", because a pipeline uses several at once: the main agent, the
-- wiki subagent, the judge and the attribution check each get their own.
--
-- The full text is stored, not just a hash — the point is to be able to read
-- what the judge was actually told months later, not merely to prove a hash
-- matches something nobody kept. The table stays small: an unchanged template
-- is one row, and editing it creates a new one.
--
-- NOTE this holds the TEMPLATE, not the rendered prompt actually sent (template
-- + category index + recalled articles + history).
--
-- OPEN ITEM: whether to persist the rendered prompt as well. The ingredients to
-- reconstruct it are all recorded — but reconstruction depends on the assembly
-- logic as it was at the time (history truncation, document blocks, language
-- correction), and that rots. See "store the rendered prompt, or reconstruct
-- it?" in §6.3.9 of INFRASTRUCTURE_PLAN.md. Either choice fits this schema:
-- payload_object_key already exists for spilling large content, and a
-- rendered_prompt_sha256 column would be a one-line addition.
CREATE TABLE prompts (
    prompt_id   text PRIMARY KEY,          -- content hash of the template text
    name        text,                      -- e.g. GENERATE_ANSWER_PROMPT_SHORT
    text        text NOT NULL,
    git_sha     text,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- ============================================================================
-- Conversations and messages (§6.3.6)
-- ============================================================================

CREATE TABLE conversations (
    conversation_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id          uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    -- No session_id: it was the key into the in-RAM session store, and once
    -- conversations are persisted the conversation_id is the identifier the
    -- client holds. Nothing else referenced it.
    -- No platform column here: a conversation can start on mobile and continue
    -- on desktop, so the client belongs to the message, not the thread.
    started_at       timestamptz NOT NULL DEFAULT now(),
    last_activity_at timestamptz NOT NULL DEFAULT now(),

    -- Retention clock. Set at creation from policy: short for guests, longer
    -- for registered users who consented to transcript storage (§6.3.8).
    expires_at       timestamptz
);

CREATE INDEX conversations_user_recent_idx
    ON conversations (user_id, last_activity_at DESC);
CREATE INDEX conversations_expiry_idx
    ON conversations (expires_at) WHERE expires_at IS NOT NULL;

CREATE TABLE messages (
    message_id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id uuid NOT NULL
                        REFERENCES conversations(conversation_id) ON DELETE CASCADE,
    seq             int  NOT NULL,
    role            message_role_t NOT NULL,

    -- PII-replaced text. Restore through the vault for display; never render
    -- this column directly to a user.
    content_tokenized text NOT NULL,

    lang            text NOT NULL REFERENCES languages(lang_code),

    -- The client this message came from. On assistant messages, the client of the
    -- request that produced it — so the column is meaningful on every row and
    -- a device switch mid-conversation is recorded rather than flattened.
    platform        platform_t NOT NULL,

    created_at      timestamptz NOT NULL DEFAULT now(),

    UNIQUE (conversation_id, seq)
);

CREATE INDEX messages_conversation_idx ON messages (conversation_id, seq);
CREATE INDEX messages_created_idx      ON messages (created_at);

COMMENT ON TABLE messages IS
    'One row per message, not per exchange: a row is either a user message or '
    'an assistant message. How an assistant message was produced lives in '
    'pipeline_runs and pipeline_steps, because that belongs to the run rather '
    'than to the message. '
    'Messages are IMMUTABLE once sent — nothing in a conversation is ever '
    'edited or removed by a user. Several decisions rest on this: attachments '
    'need no validity interval, seq is stable, and a transcript read months '
    'later is the transcript that existed at the time. Erasure is the only '
    'thing that removes rows, and it removes them entirely.';

-- ============================================================================
-- Pipeline runs and steps (§6.3.6)
-- ============================================================================
--
-- An assistant answer is not an atomic thing: it is the output of a *run* —
-- an ordered sequence of steps, some of them LLM calls (draft, judge, redraft,
-- attribution, translation), some deterministic (PII redaction, language
-- detection, tool execution).
--
-- The run, not the message, is the unit that owns how an answer was produced.
-- That is why there is no assistant_message_details table: everything that
-- would have gone in it is a property of the run, and a run can exist with no
-- message at all when a request dies before replying.

CREATE TABLE step_types (
    step_type    text PRIMARY KEY,
    is_llm_call  boolean NOT NULL,   -- so queries need not hardcode which burn tokens
    is_active    boolean NOT NULL DEFAULT true
);

-- No ordering column here on purpose: the order steps actually ran in is
-- pipeline_steps.seq, per run. Steps repeat, get skipped, and run out of any
-- nominal order, so a fixed sequence on the type would be wrong as often as not.
INSERT INTO step_types (step_type, is_llm_call) VALUES
    ('triage',          true),
    ('clarify',         true),
    ('tool_call',       false),   -- e.g. recall_topic; execution, not inference
    ('draft',           true),
    ('judge',           true),
    ('redraft',         true),
    ('attribution',     true),
    ('translation',     true),
    ('language_check',  false),
    ('pii_redaction',   false),
    ('pii_restoration', false);

COMMENT ON TABLE step_types IS
    'Deterministic steps are recorded too, not just LLM calls: the absence of a '
    'pii_redaction step is itself evidence, and being able to show that '
    'redaction ran on a given answer is worth more than the rows cost.';

-- One row per request that entered the pipeline.
CREATE TABLE pipeline_runs (
    run_id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),

    -- The user message that triggered this run. Where several user messages
    -- arrive before a reply (WhatsApp), this is the last of them.
    trigger_message_id uuid NOT NULL
                          REFERENCES messages(message_id) ON DELETE CASCADE,

    -- The assistant message produced, if any. NULL when the run died before
    -- replying — which is the reason this table exists.
    assistant_message_id uuid UNIQUE
                          REFERENCES messages(message_id) ON DELETE SET NULL,

    -- Configuration in force for this run. Prompts are NOT here: each step uses
    -- its own template, so prompt_id lives on pipeline_steps.
    skill_variant    text REFERENCES skill_variants(skill_variant),
    wiki_version     text REFERENCES wiki_builds(wiki_version),

    outcome          run_outcome_t,

    started_at       timestamptz NOT NULL DEFAULT now(),
    ended_at         timestamptz,

    -- Covers this run's steps: operational diagnostics should be able to
    -- expire before the conversation itself does.
    diagnostics_expires_at timestamptz
);

CREATE INDEX runs_trigger_idx   ON pipeline_runs (trigger_message_id);
CREATE INDEX runs_started_idx   ON pipeline_runs (started_at);
CREATE INDEX runs_skill_idx     ON pipeline_runs (skill_variant, started_at);
-- Finding runs that shipped ungrounded content or failed, without reading payloads.
CREATE INDEX runs_outcome_idx
    ON pipeline_runs (outcome, started_at) WHERE outcome <> 'passed';
CREATE INDEX runs_diagnostics_expiry_idx
    ON pipeline_runs (diagnostics_expires_at) WHERE diagnostics_expires_at IS NOT NULL;

COMMENT ON COLUMN pipeline_runs.outcome IS
    'Disposition of the whole run. Note judge_rounds is deliberately NOT stored '
    'here — it is COUNT(*) over steps of type judge, and a stored counter that '
    'can disagree with the steps is worse than a query.';

-- One row per step. Model and provider live HERE, not on the message: the judge
-- may run on different weights than the drafter, and during a backend cutover
-- one step can be self-hosted while the next hits Vertex.
CREATE TABLE pipeline_steps (
    step_id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    run_id           uuid NOT NULL REFERENCES pipeline_runs(run_id) ON DELETE CASCADE,
    seq              int  NOT NULL,

    -- Self-reference for nesting: a tool call issued inside a draft step is a
    -- child of it, not a sibling.
    parent_step_id   uuid REFERENCES pipeline_steps(step_id) ON DELETE CASCADE,

    step_type        text NOT NULL REFERENCES step_types(step_type),

    -- NULL for deterministic steps.
    model_code       text REFERENCES models(model_code),
    provider_code    text REFERENCES inference_providers(provider_code),

    -- The prompt TEMPLATE this step used. Per step, not per run: the judge and
    -- the drafter are told different things.
    prompt_id        text REFERENCES prompts(prompt_id),

    prompt_tokens     integer,
    completion_tokens integer,

    -- Timestamps rather than a duration: they let you see gaps and overlap,
    -- which a single latency_ms hides.
    started_at       timestamptz NOT NULL DEFAULT now(),
    ended_at         timestamptz,

    status           step_status_t NOT NULL DEFAULT 'ok',
    error_detail     text,

    -- Per step, not per answer: the judge's reasoning is the judge's. Tokenised
    -- like message content — reasoning quotes the user, and tool arguments are
    -- derived from user text, so both carry PII.
    reasoning_tokenized text,

    -- Step-specific content: tool name/arguments/result, judge verdict and its
    -- claims array, the draft text. Inline when small, object storage when large.
    payload_tokenized   jsonb,
    payload_object_key  text,

    UNIQUE (run_id, seq),

    CONSTRAINT steps_payload_single_location
        CHECK (NOT (payload_tokenized IS NOT NULL AND payload_object_key IS NOT NULL)),

    -- Deterministic steps consume no tokens and have no model.
    CONSTRAINT steps_tokens_need_model
        CHECK (model_code IS NOT NULL
               OR (prompt_tokens IS NULL AND completion_tokens IS NULL)),

    CONSTRAINT steps_tokens_non_negative
        CHECK (COALESCE(prompt_tokens, 0) >= 0
               AND COALESCE(completion_tokens, 0) >= 0)
);

CREATE INDEX steps_run_idx      ON pipeline_steps (run_id, seq);
CREATE INDEX steps_type_idx     ON pipeline_steps (step_type, started_at);
CREATE INDEX steps_prompt_idx   ON pipeline_steps (prompt_id);
CREATE INDEX steps_model_idx    ON pipeline_steps (model_code);
CREATE INDEX steps_provider_idx ON pipeline_steps (provider_code);
CREATE INDEX steps_parent_idx   ON pipeline_steps (parent_step_id);
CREATE INDEX steps_failed_idx   ON pipeline_steps (status, started_at) WHERE status <> 'ok';

CREATE TABLE message_feedback (
    feedback_id       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    message_id        uuid NOT NULL REFERENCES messages(message_id) ON DELETE CASCADE,
    rating            smallint CHECK (rating BETWEEN 1 AND 5),
    comment_tokenized text,
    created_at        timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT feedback_not_empty
        CHECK (rating IS NOT NULL OR comment_tokenized IS NOT NULL)
);

CREATE INDEX message_feedback_message_idx ON message_feedback (message_id);

-- An upload is not a message. It arrives *with* one, as part of that user turn,
-- and inherits the immutability of messages: a document that was part of the
-- conversation stays part of it. There is therefore no deletion, and no validity
-- interval to record.
--
-- conversation_id is deliberately absent — it is reachable through messages, and
-- carrying both would duplicate the link.
CREATE TABLE attachments (
    attachment_id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    message_id      uuid NOT NULL REFERENCES messages(message_id) ON DELETE CASCADE,

    -- The original bytes are NOT retained (decided 2026-08-06). These describe
    -- what was uploaded, so the client can show the user which file they sent
    -- and research can characterise the corpus, without keeping the file.
    filename        text NOT NULL,
    mime_type       text NOT NULL,
    size_bytes      bigint NOT NULL CHECK (size_bytes > 0),

    -- What was extracted from the file, redacted through the same vault as
    -- message content. NOT NULL because extraction failure rejects the upload:
    -- an attachment row exists only if there was text to store. This is the only
    -- copy of the document's content and the only form of it a model ever sees.
    text_tokenized  text NOT NULL,

    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX attachments_message_idx ON attachments (message_id);

-- An empty user message is legitimate ONLY when a file came with it: attaching
-- a photo and typing nothing is a real turn, typing nothing at all is not.
--
-- This cannot be a CHECK — a CHECK constraint cannot see another table — so it
-- is a constraint trigger, deferred to commit time because the attachment row
-- is inserted after the message it belongs to.
CREATE FUNCTION messages_empty_needs_attachment() RETURNS trigger AS $$
BEGIN
    IF NEW.content_tokenized = '' AND NOT EXISTS (
           SELECT 1 FROM attachments WHERE message_id = NEW.message_id
       ) THEN
        RAISE EXCEPTION 'message % has no text and no attachment', NEW.message_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER messages_empty_needs_attachment_check
    AFTER INSERT OR UPDATE ON messages
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE FUNCTION messages_empty_needs_attachment();

COMMENT ON TABLE attachments IS
    'Extracted text only — the uploaded file is discarded after extraction, so '
    'there is no object storage here and no bytes for erasure to sweep. Kept '
    'out of messages.content_tokenized on purpose: OCR output is not something '
    'the user wrote, and inlining it would make message length and token counts '
    'meaningless and stop the UI distinguishing a file from typed text.';

-- ----------------------------------------------------------------------------
-- Point-in-time demographics, so analysis code does not have to write the
-- temporal join correctly every time:
--     SELECT * FROM message_demographics WHERE message_id = ...
-- Returns NULLs where the demographic was never collected, or was erased after
-- consent withdrawal.
-- ----------------------------------------------------------------------------
CREATE VIEW message_demographics AS
SELECT m.message_id,
       c.user_id,
       d.age_group,
       d.gender
FROM   messages m
JOIN   conversations c USING (conversation_id)
LEFT   JOIN user_demographics_history d
       ON  d.user_id = c.user_id
       AND m.created_at >= d.valid_from
       AND (d.valid_to IS NULL OR m.created_at < d.valid_to);

-- Occupation is multi-valued, so it is a separate point-in-time view rather
-- than more columns on the one above.
CREATE VIEW message_roles AS
SELECT m.message_id,
       c.user_id,
       r.role_code
FROM   messages m
JOIN   conversations c USING (conversation_id)
JOIN   user_role_history r
       ON  r.user_id = c.user_id
       AND m.created_at >= r.valid_from
       AND (r.valid_to IS NULL OR m.created_at < r.valid_to);

-- ============================================================================
-- Environmental impact (§6.3.7)
-- ============================================================================

-- Attached to a STEP, not to a message. Each LLM call has its own model, its
-- own provider and its own completion count, which is exactly what the impact
-- coefficients need; one figure per message was only ever an approximation and
-- breaks outright once the judge runs on different weights than the drafter.
-- Roll up to message, conversation or user by joining (see user_impact).
CREATE TABLE environmental_impact (
    impact_id     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    source_type   impact_source_t NOT NULL,

    -- NULL for infrastructure events (idle node hours, accumulated server
    -- emissions), which belong to no step — and NULL after erasure, see below.
    step_id       uuid REFERENCES pipeline_steps(step_id) ON DELETE SET NULL,

    provider_code text REFERENCES inference_providers(provider_code),
    node_id       text,
    region        text,

    energy_kwh    numeric,
    gwp_kgco2eq   numeric,
    water_l       numeric,
    adpe_kgsbeq   numeric,
    pe_mj         numeric,

    -- true  = metered on our own GPU (codecarbon / nvidia-smi)
    -- false = estimated from token counts (ecologits)
    -- Never sum across this boundary without saying which is which.
    measured      boolean NOT NULL,

    created_at    timestamptz NOT NULL DEFAULT now(),

    -- A negative footprint is a bug in the collector, not a datum. Cheaper to
    -- reject at the boundary than to find it later inside a summed total.
    CONSTRAINT impact_non_negative CHECK (
        COALESCE(energy_kwh,  0) >= 0 AND
        COALESCE(gwp_kgco2eq, 0) >= 0 AND
        COALESCE(water_l,     0) >= 0 AND
        COALESCE(adpe_kgsbeq, 0) >= 0 AND
        COALESCE(pe_mj,       0) >= 0
    )
);

CREATE INDEX impact_provider_idx ON environmental_impact (provider_code, created_at);
CREATE INDEX impact_step_idx     ON environmental_impact (step_id);

COMMENT ON COLUMN environmental_impact.step_id IS
    'ON DELETE SET NULL rather than CASCADE, deliberately: erasing a user must '
    'remove the attribution but should not silently reduce total reported '
    'emissions. Erased rows survive as unattributed aggregate. This is also why '
    'there is no CHECK tying source_type to step_id — an inference row can '
    'legitimately end up with a NULL step.';

COMMENT ON COLUMN environmental_impact.measured IS
    'Metered and estimated figures are not comparable and must never be summed '
    'without the distinction. Estimates also depend on which token count the '
    'coefficients were derived for — with per-step prompt/completion counts '
    'stored separately, an estimate can be recomputed later; a single summed '
    'total could not be.';

-- Per-user rollup, so the carbon-footprint screen does not hand-write the join
-- through steps → runs → messages → conversations every time. Unattributed
-- rows (infrastructure, or post-erasure) simply do not appear here.
CREATE VIEW user_impact AS
SELECT c.user_id,
       i.*
FROM   environmental_impact i
JOIN   pipeline_steps  s ON s.step_id = i.step_id
JOIN   pipeline_runs   r ON r.run_id  = s.run_id
JOIN   messages        m ON m.message_id = r.trigger_message_id
JOIN   conversations   c ON c.conversation_id = m.conversation_id;

-- ============================================================================
-- PII vault — separate schema, separate key (§6.3.5)
-- ============================================================================

CREATE SCHEMA pii;

-- One data key per conversation. The key is generated locally, used to encrypt
-- every vault row in that conversation, then itself encrypted ("wrapped") by
-- the KMS key named in kms_key_id. Only the wrapped form is written down.
--
-- Why here and not a key_id column on every token row: that column repeated
-- the same value for every row in a conversation.
--
-- OPEN ITEM (§6.3.9): this table exists to support *partial* erasure — keeping
-- the redacted transcripts as research material while destroying the mapping
-- back to real values. Whether that is ever wanted is undecided. If erasure is
-- always full erasure (delete the user, cascade everything), drop this table and
-- point token_map at conversations directly. Note the shredding is a live-data
-- operation only: the wrapped key sits in the same database as the ciphertext,
-- so a point-in-time restore brings back both.
CREATE TABLE pii.conversation_keys (
    conversation_id uuid PRIMARY KEY
                        REFERENCES public.conversations(conversation_id) ON DELETE CASCADE,
    wrapped_dek     bytea NOT NULL,
    kms_key_id      text  NOT NULL,   -- names the wrapping key, for rotation
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE pii.token_map (
    -- Parented by the key row, not by conversations directly, so that ciphertext
    -- cannot exist without a recorded key to decrypt it. Erasure still cascades
    -- from users and conversations, one level further up.
    conversation_id uuid NOT NULL
                        REFERENCES pii.conversation_keys(conversation_id) ON DELETE CASCADE,

    -- The text that literally appears in the stored message in place of the real
    -- value — a plausible substitute chosen by the redactor ('Paris' -> 'Marseille'),
    -- NOT a hash. Restoration scans model output for this exact string, so it has
    -- to be what was actually substituted in.
    surrogate       text NOT NULL,

    -- Deterministic keyed hash of the normalised real value, salted per
    -- conversation. Never rendered, and not a substitute for ciphertext — this is
    -- the write-path index. It answers "did I already assign a surrogate to
    -- 'Paris' in this conversation?" without decrypting anything, and it is what
    -- the UNIQUE below is able to constrain (ciphertext cannot be: encryption is
    -- randomised, so the same value encrypts to different bytes every time).
    value_hmac      bytea NOT NULL,

    entity_type     pii_entity_t NOT NULL,

    -- The real value, encrypted under this conversation's data key.
    ciphertext      bytea NOT NULL,

    created_at      timestamptz NOT NULL DEFAULT now(),

    -- Restore direction (surrogate -> real). Composite, so a surrogate cannot be
    -- resolved without knowing its conversation: fail-closed is structural rather
    -- than something the restore path has to remember. Also guarantees one
    -- surrogate never resolves to two different real values.
    PRIMARY KEY (conversation_id, surrogate),

    -- Redact direction (real -> surrogate). Without this, the same real value
    -- could be given two different surrogates in one conversation, which reads to
    -- the model as two different cities. Together with the PK, the mapping is
    -- bijective within a conversation.
    --
    -- OPEN ITEM (§6.3.9): the team may keep surrogates consistent by some other
    -- mechanism than this write-path lookup. If so, value_hmac and this
    -- constraint are the parts that change — the restore direction above holds
    -- either way.
    UNIQUE (conversation_id, value_hmac)
);

COMMENT ON SCHEMA pii IS
    'Reversible de-identification map, kept in its own schema and encrypted '
    'under its own key. Surrogates are scoped to one conversation, so the same '
    'real value yields unrelated surrogates in different conversations and two '
    'conversations cannot be correlated by comparing them.';

COMMENT ON TABLE pii.token_map IS
    'Restoration is a lookup, not a decryption of the surrogate: the surrogate '
    'carries no relationship to the value it replaced. Losing this table makes '
    'the transcripts permanently unresolvable, which is a deliberate erasure '
    'option (§6.3.5) and not only a failure mode.';

-- Point-in-time recovery must be coordinated with the transcript store:
-- restoring one to a different point than the other yields dangling tokens
-- and unrenderable history.

-- ============================================================================
-- Not modelled here
-- ============================================================================
--
--  * Retention periods per record family — accounts, transcripts, traces,
--    vault entries, impact rows. They differ, and none are decided.
--  * The erasure procedure itself. Cascades cover the relational side; spilled
--    step payloads in S3/GCS (pipeline_steps.payload_object_key) and any
--    exported research datasets need an explicit sweep. Attachments no longer
--    do — nothing about them lives outside Postgres.
--  * Whether real name is collected at all (§6.3.2).
--  * Anything the training app needs — out of scope for this discussion.