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