# Schema diagram Visual companion to [`schema.sql`](schema.sql), which implements §6.3 of [`INFRASTRUCTURE_PLAN.md`](INFRASTRUCTURE_PLAN.md). Draft — nothing is deployed. ## How to work on this **`schema.sql` is the source of truth. This file is the picture of it.** They live in the same directory and should change in the same commit — a diagram that has drifted from the DDL is worse than no diagram, because people trust it. The diagrams below are [Mermaid](https://mermaid.js.org/syntax/entityRelationshipDiagram.html) `erDiagram` blocks. They render natively in GitHub and GitLab, in VS Code (Markdown preview, with the Mermaid extension), and in most Markdown viewers — so a teammate reads them by opening this file, with nothing to install and no account to create. To change the model: edit `schema.sql`, edit the matching block here, open one PR. Reviewers see the DDL diff and the shape change side by side. *Alternatives considered.* [dbdiagram.io](https://dbdiagram.io) (DBML) gives nicer layout and drag-to-arrange, and would be worth it if the auto-layout below stops being readable — but it is a third-party service and the diagram drifts from `schema.sql` unless someone syncs it by hand. Tools that generate an ERD from a live database (SchemaSpy, the pgAdmin ERD tool) are the right answer *later*, once there is a database to point them at; they can't help while the schema is still a text file. --- ## Overview Keys only; attributes are in the detail diagrams below. The reference tables (`step_types`, `models`, `inference_providers`, `skill_variants`, `wiki_builds`, `prompts`) are omitted here for readability and shown in *Pipeline runs and steps*, as is `languages`, which `users.locale`, `messages.lang` and `consent_notice_texts.lang` all point at. ```mermaid erDiagram users ||--o{ auth_identities : "authenticates via" users ||--o{ consent_events : "grants / withdraws" consent_purposes ||--o{ consent_events : "categorises" consent_notices ||--o{ consent_events : "wording in force" consent_notices ||--o{ consent_notice_texts : "translated as" users ||--o{ user_demographics_history : "over time" users ||--o{ user_role_history : "over time" role_options ||--o{ user_role_history : "option for" users ||--o{ conversations : "holds" conversations ||--o{ messages : "contains" messages ||--o{ pipeline_runs : "triggers" pipeline_runs ||--o{ pipeline_steps : "consists of" messages ||--o{ message_feedback : "rated by" messages ||--o{ attachments : "carries" pipeline_steps ||--o{ environmental_impact : "costs" conversations ||--o| pii_conversation_keys : "one data key each" pii_conversation_keys ||--o{ pii_token_map : "encrypts" users { uuid user_id PK } auth_identities { uuid auth_identity_id PK uuid user_id FK } consent_purposes { text purpose_code PK } consent_notices { text notice_version PK } consent_notice_texts { text notice_version PK text lang PK } consent_events { uuid consent_event_id PK uuid user_id FK text purpose_code FK text notice_version FK } user_demographics_history { uuid user_id PK timestamptz valid_from PK } role_options { text role_code PK } user_role_history { uuid user_id PK text role_code PK timestamptz valid_from PK } conversations { uuid conversation_id PK uuid user_id FK } messages { uuid message_id PK uuid conversation_id FK } pipeline_runs { uuid run_id PK uuid trigger_message_id FK uuid assistant_message_id FK } pipeline_steps { uuid step_id PK uuid run_id FK } message_feedback { uuid feedback_id PK uuid message_id FK } attachments { uuid attachment_id PK uuid message_id FK } environmental_impact { uuid impact_id PK uuid step_id FK } pii_conversation_keys { uuid conversation_id PK } pii_token_map { uuid conversation_id PK text surrogate PK } ``` `pii_token_map` and `pii_conversation_keys` are `pii.token_map` and `pii.conversation_keys` — kept in a separate schema and encrypted under their own key (§6.3.5). --- ## Identity, consent and demographics The pseudonymisation boundary: no direct identifiers live here. Email, phone and credentials are in Firebase Auth; `auth_identities` is the only link to it. ```mermaid erDiagram users ||--o{ auth_identities : "authenticates via" users ||--o{ consent_events : "grants / withdraws" consent_purposes ||--o{ consent_events : "categorises" users ||--o{ user_demographics_history : "over time" users ||--o{ user_role_history : "over time" role_options ||--o{ user_role_history : "option for" consent_notices ||--o{ consent_events : "wording in force" consent_notices ||--o{ consent_notice_texts : "translated as" languages ||--o{ consent_notice_texts : "written in" languages ||--o{ users : "preferred by" languages { text lang_code PK "ISO 639-1; en, fr" boolean is_active } users { uuid user_id PK "opaque; no direct identifiers" account_type_t account_type "guest or registered" text locale FK "languages.lang_code" text username UK "reserved, undecided" timestamptz created_at timestamptz last_login_at "app-side copy; Firebase tracks it too" } auth_identities { uuid auth_identity_id PK uuid user_id FK auth_provider_t provider "firebase or whatsapp" text provider_uid UK "firebase uid (incl. anonymous) or E.164" timestamptz linked_at } consent_purposes { text purpose_code PK "service_and_storage or research_improvement" boolean is_active } consent_notices { text notice_version PK timestamptz published_at } consent_notice_texts { text notice_version PK "also FK to consent_notices" text lang PK "also FK to languages" text body "the wording the person actually read" } consent_events { uuid consent_event_id PK uuid user_id FK "ON DELETE RESTRICT - needs legal input" text purpose_code FK text notice_version FK "re-consent when the notice changes" consent_action_t action "granted or withdrawn" timestamptz occurred_at consent_source_t source } user_demographics_history { uuid user_id PK "also FK to users" timestamptz valid_from PK timestamptz valid_to "NULL = current" age_group_t age_group "consent-conditional" gender_t gender "consent-conditional" } role_options { text role_code PK "user-selected occupation" boolean is_active } user_role_history { uuid user_id PK "also FK to users" text role_code PK "also FK to role_options" timestamptz valid_from PK timestamptz valid_to "NULL = current" } ``` Four things the diagram can't show: - **`consent_events` is append-only** — enforced by a trigger. Current state is the `user_consent_current` view, never a stored column. - **The notice text is stored, not just its version.** `notice_version` is a foreign key rather than free text, and `consent_notice_texts` holds the wording per language. Same reasoning as `prompts` keeping full template text, with more at stake: if someone disputes what they agreed to, a bare version string proves nothing. - **The `*_history` tables use exclusion constraints** so intervals cannot overlap per user. That is what makes `valid_to IS NULL` safe as "current". - **`role_options` is the user's own occupation** — clinician, researcher, computer scientist, patient, other — selected by the user and used as a research variable. It matches `ProfileBase.roles`, and it is multi-valued, which is why it is a junction table rather than a column. --- ## Conversations, messages and feedback A row in `messages` is *either* a user message or an assistant message, and holds only what every message has. **How an answer was produced belongs to the run, not the message** — see the next section. **Messages are immutable once sent.** Nothing in a conversation is ever edited or removed by a user, and several decisions rest on it: attachments need no validity interval, `seq` is stable, and a transcript read months later is the one that existed at the time. Erasure is the only thing that removes rows, and it removes them outright. ```mermaid erDiagram users ||--o{ conversations : "holds" conversations ||--o{ messages : "contains" messages ||--o{ message_feedback : "rated by" messages ||--o{ attachments : "carries" conversations { uuid conversation_id PK uuid user_id FK timestamptz started_at timestamptz last_activity_at timestamptz expires_at "retention; short for guests" } messages { uuid message_id PK uuid conversation_id FK int seq UK "unique per conversation" message_role_t role "user or assistant" text content_tokenized "PII replaced; empty only if a file came with it" text lang FK "languages.lang_code" platform_t platform "web or mobile or whatsapp" timestamptz created_at } message_feedback { uuid feedback_id PK uuid message_id FK smallint rating text comment_tokenized timestamptz created_at } attachments { uuid attachment_id PK uuid message_id FK "NOT NULL — an upload always arrives with a message" text filename text mime_type bigint size_bytes "of the original, which is not kept" text text_tokenized "extracted text, redacted; the only copy" timestamptz created_at } ``` **An upload is not a message, and does not float free of one.** It arrives *with* a user turn and inherits the immutability of messages — nothing in a sent conversation is ever edited or removed, so an attachment has no deletion and needs no validity interval. That is also why `message_id` is `NOT NULL` and `conversation_id` is absent: the conversation is reachable through the message, and storing both would duplicate the link. **The file itself is not retained** — only the text extracted from it, redacted through the same vault as message content. So there is no object storage in this design and nothing outside Postgres for erasure to sweep. The extracted text is kept out of `messages.content_tokenized` deliberately: OCR output is not something the user wrote, and inlining it would make message length and token counts meaningless. --- ## Pipeline runs and steps An assistant answer is the output of a **run**: an ordered sequence of steps, some LLM calls (draft, judge, redraft, attribution, translation), some deterministic (PII redaction, language detection, tool execution). Deterministic steps are recorded too — the *absence* of a `pii_redaction` step is itself evidence. Two consequences worth reading off the diagram: - **`model_code` and `provider_code` sit on the step, not the answer.** 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. One model per answer cannot express either. - **A run can exist with no message.** That is the whole reason it is a separate table: requests that die before replying are exactly the ones worth investigating. ```mermaid erDiagram messages ||--o{ pipeline_runs : "triggers" pipeline_runs |o--o| messages : "produces" pipeline_runs ||--o{ pipeline_steps : "consists of" pipeline_steps |o--o{ pipeline_steps : "nests" step_types ||--o{ pipeline_steps : "classifies" models ||--o{ pipeline_steps : "generated by" inference_providers ||--o{ pipeline_steps : "served by" skill_variants ||--o{ pipeline_runs : "answered by" wiki_builds ||--o{ pipeline_runs : "grounded on" prompts ||--o{ pipeline_steps : "prompted by" pipeline_runs { uuid run_id PK uuid trigger_message_id FK "the user message" uuid assistant_message_id FK "NULL if it died before replying" text skill_variant FK text wiki_version FK "audit trail" run_outcome_t outcome "passed, fallback_refusal, error" timestamptz started_at timestamptz ended_at timestamptz diagnostics_expires_at "own retention clock" } pipeline_steps { uuid step_id PK uuid run_id FK int seq UK "unique per run" uuid parent_step_id FK "tool call inside a draft" text step_type FK text model_code FK "NULL if deterministic" text provider_code FK "NULL if deterministic" text prompt_id FK "the template this step used" int prompt_tokens int completion_tokens timestamptz started_at timestamptz ended_at step_status_t status "ok, error, timeout" text error_detail text reasoning_tokenized "this step's reasoning" jsonb payload_tokenized "verdict, claims, tool args and result" text payload_object_key "when too large to inline" } step_types { text step_type PK "draft, judge, attribution, pii_redaction, ..." boolean is_llm_call boolean is_active } models { text model_code PK "gemma-4-26b-e4b, gpt-oss-20b, ..." boolean is_active } inference_providers { text provider_code PK "self_hosted, vertex, groq, scaleway, ..." boolean is_self_hosted boolean is_active } skill_variants { text skill_variant PK "mirrors agent/skills/" boolean is_active } wiki_builds { text wiki_version PK "content hash" text name "hand-assigned label" text git_sha int article_count timestamptz built_at } prompts { text prompt_id PK "content hash of the template" text name "GENERATE_ANSWER_PROMPT_SHORT, ..." text text "the template itself, stored in full" text git_sha timestamptz created_at } ``` **`judge_rounds` is not stored.** It is `COUNT(*)` over steps of type `judge`; a counter that can disagree with the steps it counts is worse than a query. **`status` is not a judge verdict.** A judge returning FAIL executed perfectly — that is `status = 'ok'` with a FAIL in `payload_tokenized`. `error` and `timeout` mean the step itself did not complete. The reference tables exist for **auditability** rather than for the product: together they let you reconstruct, months later, which model on which provider ran which skill against which knowledge base and prompt set. All are lookup tables rather than enums, because each list changes on its own clock. `prompts` stores each **template** in full — `prompt_id` is a hash of the text, so an unchanged template is one row and editing it creates a new one. It is per *step*, not per run: the judge and the drafter are told different things. Storing the text rather than only a hash is the point — you can read what the judge was told, not merely verify a hash against something nobody kept. ⚠️ **Open question:** whether to also store the **rendered** prompt actually sent (template plus injected index, recalled articles and history) rather than reconstructing it. See §6.3.9 of the plan — the schema accommodates either. ⚠️ Foreign keys to `wiki_builds` and `prompts` mean the referenced row must exist before a run or step can be written. Register both at application startup, or an unregistered build turns a lost piece of metadata into a lost run. --- ## Environmental impact and the PII vault ```mermaid erDiagram pipeline_steps ||--o{ environmental_impact : "costs" inference_providers ||--o{ environmental_impact : "incurred on" conversations ||--o| pii_conversation_keys : "one data key each" pii_conversation_keys ||--o{ pii_token_map : "encrypts" environmental_impact { uuid impact_id PK impact_source_t source_type "inference or infrastructure" uuid step_id FK "NULL for infrastructure, or after erasure" text provider_code FK text node_id text region numeric energy_kwh numeric gwp_kgco2eq numeric water_l numeric adpe_kgsbeq numeric pe_mj boolean measured "true = metered GPU, false = ecologits estimate" timestamptz created_at } pii_conversation_keys { uuid conversation_id PK bytea wrapped_dek "data key, encrypted by the KMS key" text kms_key_id "names the wrapping key, for rotation" timestamptz created_at } pii_token_map { uuid conversation_id PK "composite PK makes fail-closed structural" text surrogate PK "the substitute that appears in the text" bytea value_hmac UK "keyed hash of the real value; write-path index" pii_entity_t entity_type bytea ciphertext "the real value, never plaintext" timestamptz created_at } ``` Five rules encoded in these tables rather than in application code: - **Impact attaches to a step, not a message.** Each LLM call has its own model, provider and completion count — which is what the coefficients actually need. One figure per message was an approximation, and it breaks outright once the judge runs on different weights than the drafter. - **`measured`** separates metered GPU energy from ecologits token estimates. The two are not comparable and must never be summed without the distinction. - **The vault's composite primary key** means a surrogate cannot be resolved without knowing its conversation. Fail-closed is structural, not something the restore path has to remember. - **The vault is constrained in both directions.** The primary key covers restoration — one surrogate can never resolve to two real values. The unique constraint on `value_hmac` covers redaction — one real value can never be given two surrogates inside a conversation, which would read to the model as two different people or places. The pair makes the mapping bijective per conversation, which is the property restoration actually depends on. - **`value_hmac` exists because ciphertext cannot be constrained.** Encryption is randomised, so the same value encrypts to different bytes every time and a unique index over it would never fire. The keyed hash is deterministic, so it can carry the constraint and serve as the write-path lookup without anything being decrypted. `step_id` is `ON DELETE SET NULL` rather than `CASCADE` on purpose: erasing a user removes the attribution but must not silently reduce total reported emissions, so erased rows survive as unattributed aggregate. --- ## Views Not shown in the diagrams — they are derived, and drawing them would imply storage. | View | Purpose | |---|---| | `user_consent_current` | Latest event per (user, purpose). Absence of a row means never asked | | `message_demographics` | Point-in-time age group and gender for a message, via the temporal join | | `message_roles` | Point-in-time occupation for a message; multi-valued, so separate from the above | | `user_impact` | Per-user environmental rollup, joining impact through steps → runs → messages → conversations | `message_demographics` and `message_roles` exist so analysis code never has to write the temporal join by hand — getting it wrong silently rewrites history with today's demographics. `user_impact` exists for the same reason: a four-table join is a thing people get wrong or skip.