09 — Diagrams

Last verified: 18/05/2026 (S242 — Wave 3 / Phase 0 close prep; final WP4 architecture sub-doc) Scope: ERDs (content_items + q_a_pairs + q_a_extractions; source_documents + cocoindex source-key; workspaces + reserved seats; citations polymorphic FK); sequence diagrams (ingest flow with Cloud Run sidecar; Q&A round-trip; bid feedback 3-UC composite; auto-RLS event trigger). Visual companion to the eight prose sub-docs of the WP4 architecture-split family. Status: [CURRENT-CANONICAL] — final WP4 sub-doc; closes the 9-way split of docs/plans/phase-0-investigation/0.9-intended-architecture.md. Layer: 3 — depends on 02-data-flow.md, 04-workspace-types.md, 05-qa-flow.md, 06-mcp-tooling.md, 08-new-features.md per docs/specs/core-docs-pathway-assessment/INV-architecture-split-readiness.md §4. Renders schemas and flows established in those sub-docs; introduces no new schema or flow content. Companion sub-docs: 01-vision.md, 02-data-flow.md, 03-tech-stack.md, 04-workspace-types.md, 05-qa-flow.md, 06-mcp-tooling.md (Theme F gated), 07-collapse-list.md, 08-new-features.md.

1. Mission

This sub-doc is the visual-substrate companion to the eight prose sub-docs of the WP4 architecture-split family. It renders the entity-relationship diagrams (ERDs) and sequence diagrams that the prose sub-docs reference but defer here per the per-sub-doc forward-reference policy (docs/specs/core-docs-pathway-assessment/architecture-sub-doc-construction-guide.md §6.3). Every diagram in this doc is a visual rendering of content owned by another sub-doc — schemas anchored in 04-workspace-types.md, flow stages anchored in 02-data-flow.md, Q&A round-trip anchored in 05-qa-flow.md, bid-feedback 3-UC anchored in 08-new-features.md, auto-RLS event trigger anchored in docs/specs/rls-pattern/PRODUCT.md + TECH.md.

Three scope boundaries apply throughout:

No new schema or flow content. Per docs/specs/core-docs-pathway-assessment/architecture-sub-doc-readiness-audit.md Risk 5, every entity, column, edge, and sequence step in this sub-doc must trace to a citation in the prose sub-docs or schema-source files (supabase/types/database.types.ts, docs/reference/SCHEMA-QUICK-REFERENCE.md). No “schema changes in the diagram” that aren’t in the prose.
Mermaid syntax for renderability. All diagrams are mermaid blocks (erDiagram + sequenceDiagram). They render inline in markdown viewers (GitHub, Cursor, Claude Code preview) without asset commits. Where mermaid cannot capture a nuance (polymorphic FK shape; nullable FK with two-edge RLS routing) it is annotated alongside.
Migration-state qualifier per ERD. Schemas marked RATIFIED-S235, migration STILL-OPEN per Q-OQR1-16 in 04-workspace-types.md §8 are rendered as they will exist post-migration. Each ERD carries a banner identifying the migration state at draft time, so a reader cannot mistake the diagram for a live-schema snapshot. The live-schema state (pre-migration) is captured in supabase/types/database.types.ts; the post-migration state rendered here is the canonical target.

Construction-guide discipline: this sub-doc applies the construction-guide pattern (docs/specs/core-docs-pathway-assessment/architecture-sub-doc-construction-guide.md §2.3) — section template adapts because the primary content is diagrams, not prose. §3 onwards is diagram sub-sections. §6 anti-patterns is thin (no diagrams that contradict the prose). §7 source-doc-status follows the standard pattern. Numbered D-N diagram invariants capture the load-bearing claims that the diagrams visualise — these mirror the P-N / S-N / T-N pattern used elsewhere in the architecture family.

2. Diagram catalogue

The diagrams are grouped by kind. Each row identifies the owning prose section and the migration-state qualifier.

§	Diagram	Kind	Owning prose section	Migration state
3.1	`content_items` + `q_a_pairs` + `q_a_extractions`	ERD	`04-workspace-types.md` §5; `05-qa-flow.md` §2 + §3	`q_a_pairs` + `q_a_extractions`: RATIFIED-S235, migration STILL-OPEN per Q-OQR1-16; `content_items`: live schema
3.2	`source_documents` + cocoindex source-key + content-hash	ERD	`02-data-flow.md` §2; `04-workspace-types.md` §9	`source_documents.workspace_id` NULLABLE: RATIFIED-S235, migration STILL-OPEN per Q-OQR1-16 item 9; existing columns: live schema
3.3	`workspaces` + `application_types` + per-app satellites	ERD	`04-workspace-types.md` §2 + §3 + §4	`application_types` + `workspaces.application_type_id` FK: RATIFIED-S235, migration STILL-OPEN per Q-OQR1-16 items 1 + 2 + 4
3.4	`citations` polymorphic + `question_matches` discriminator	ERD	`05-qa-flow.md` §6 + §7	Polymorphic `citations.citing_entity` enum + separate `embedding_score` / `fulltext_score`: RATIFIED-S236 per N8 + N9; migration STILL-OPEN
4.1	Ingest flow with Cloud Run sidecar	sequence	`02-data-flow.md` §3 + §4 + §5	flow shape ratified; auto-RLS interaction RATIFIED-S236
4.2	Q&A round-trip (search + verbatim fetch)	sequence	`05-qa-flow.md` §6 + §7; `06-mcp-tooling.md` §4	two-step retrieval pattern ratified per S16 §6.1
4.3	Bid-feedback 3-UC composite (UC5 + UC6 + UC8)	sequence	`08-new-features.md` §7; `05-qa-flow.md` §8 + §9	UC5 + UC6 + UC8 RATIFIED-S229 per `0.9-edit-flow-investigation.md`
4.4	Auto-RLS event trigger on `CREATE TABLE`	sequence	`docs/specs/rls-pattern/PRODUCT.md` P-1 + `TECH.md` T-1	RATIFIED-S236; migration draft APPLY-GATED-ON-LIAM-REVIEW

3. Entity-relationship diagrams

3.1 ERD — `content_items` + `q_a_pairs` + `q_a_extractions`

Owning prose: 04-workspace-types.md §5 (q_a_pairs corpus-level cardinality); 05-qa-flow.md §2 (shape-critical columns) + §3 (q_a_extractions two-tier model).

Migration state: content_items columns reflect live schema per supabase/types/database.types.ts, except where annotated (Q-OQR1-16) for post-migration shape — specifically op_id lands per N7 RESOLVED-S236 in the Q-OQR1-16 combined-PR migration (see 02-data-flow.md §5.3). q_a_pairs + q_a_extractions are RATIFIED-S235, migration STILL-OPEN per Q-OQR1-16 item 10 (0.9-decision-graph.md §11.3) — they do not exist in live schema yet; the diagram renders the post-migration target shape.

D-1. content_items.content_text_hash is GENERATED ALWAYS per CLAUDE.md Gotcha — explicit insert or update is rejected. PG auto-computes via md5(normalised content). The ERD marks the column with GENERATED to signal “do not write”.

D-2. q_a_pairs carries no direct workspace_id FK per Q-OQR1-06 (00-synthesis-v2.md §3.6; 04-workspace-types.md §5.1). Workspace relevance is computed via scope_tag overlap with anti_scope_tag exclusion at read time, not via FK partition. The ERD must NOT render a workspace_id column on q_a_pairs. Any diagram showing one renders the pre-Q-OQR1-06 superseded framing per 04-workspace-types.md §11 and 05-qa-flow.md §11 anti-patterns.

D-3. q_a_pairs.source_workspace_id is NULLABLE per Q-OQR1-07. It is a provenance-audit column only, populated at UC5 promotion time when origin_kind='derived_from_bid_response'. It is NOT a workspace-scoping signal. The ERD marks the column nullable and annotates “provenance-audit only” alongside.

D-4. q_a_extractions is the derived cache per S16 §6 two-tier model (“one golden record, many extractions”). promoted_to_pair_id (when set) is the lineage edge to a q_a_pairs row. invalidated_at is set by post-flow trigger when source changes — the old extraction row is preserved for audit comparison; the new extraction row lands as a separate row.

1
erDiagram
2
    content_items ||--o{ q_a_extractions : "produces (via cocoindex ExtractByLlm)"
3
    content_items ||--o{ content_history : "trigger-driven version snapshot"
4
    q_a_pairs ||--o{ q_a_extractions : "promoted_to_pair_id (when extraction promoted)"
5
    q_a_pairs ||--o{ q_a_pair_history : "trigger-driven version snapshot (05-qa-flow.md §3.3)"
6
    q_a_pairs ||--o| q_a_pairs : "superseded_by (UC8 merge lineage)"
7

8
    content_items {
9
        uuid id PK
10
        text content_type "discriminator"
11
        text content "canonical body"
12
        text content_text_hash "GENERATED ALWAYS"
13
        text title
14
        text brief "summary layer"
15
        text detail "detail layer"
16
        vector_1024 embedding "text-embedding-3-large"
17
        text freshness "fresh/aging/stale/expired (governance)"
18
        text dedup_status
19
        uuid content_owner_id "FK auth.users (SET NULL)"
20
        uuid parent_id "FK content_items (lineage)"
21
        text ingest_source
22
        uuid op_id "cocoindex per-flow run id (Q-OQR1-16)"
23
        timestamptz created_at
24
    }
25

26
    q_a_pairs {
27
        uuid id PK
28
        text question_text
29
        text_array alternate_question_phrasings "Shape D context variants"
30
        text answer_standard "NOT NULL"
31
        text answer_advanced "tiered nullable"
32
        text_array scope_tag "workspace-relevance filter"
33
        text_array anti_scope_tag "explicit exclusion"
34
        uuid source_workspace_id "NULLABLE provenance-audit"
35
        text origin_kind "extracted_from_corpus/curated_explicit/derived_from_bid_response/imported_legacy"
36
        uuid source_content_item_id "FK content_items (lineage)"
37
        vector_1024 question_embedding "embedded over question_text+alternates"
38
        text publication_status "draft/in_review/published/archived"
39
        uuid superseded_by "self-FK UC8 merge"
40
        timestamptz valid_from
41
        timestamptz valid_to "NULL = currently valid"
42
    }
43

44
    q_a_extractions {
45
        uuid id PK
46
        uuid source_content_item_id "FK content_items"
47
        text extractor_kind "prior_bid_response/llm_extraction/yaml_frontmatter_v1/markdown_heading_v1"
48
        text extracted_question
49
        text extracted_answer
50
        uuid promoted_to_pair_id "FK q_a_pairs (NULL until UC5 promotion)"
51
        timestamptz invalidated_at "set by post-flow trigger"
52
        timestamptz created_at
53
    }
54

55
    content_history {
56
        uuid id PK
57
        uuid content_item_id "FK content_items (SET NULL)"
58
        int version "monotonic per item"
59
        text content "snapshot"
60
        text change_type "edit/classify/import/archive/delete"
61
        timestamptz created_at
62
    }
63

64
    q_a_pair_history {
65
        uuid id PK
66
        uuid q_a_pair_id "FK q_a_pairs"
67
        int version
68
        text question_text "snapshot"
69
        text answer_standard "snapshot"
70
        timestamptz valid_from "from source row"
71
        timestamptz valid_to
72
        timestamptz created_at
73
    }

Notes on rendering choices:

vector_1024 renders mermaid-safely; the actual Postgres type is vector(1024) per CLAUDE.md “Supabase & Schema”.
text_array similarly renders mermaid-safely for text[].
parent_id self-FK on content_items is rendered without the loop arrow (mermaid renders self-FKs awkwardly); it is captured in the column note.
q_a_extractions.invalidated_at semantics (post-flow trigger sets when source changes) are owned by 05-qa-flow.md §3.4; the ERD shows the column and references back.

3.2 ERD — `source_documents` + cocoindex source-key + content-hash

Owning prose: 02-data-flow.md §2 (source-binding model — cocoindex source-key + content-hash idempotency); 04-workspace-types.md §9 (source_documents.workspace_id nullable migration).

Migration state: source_documents.workspace_id becomes NULLABLE per Q-OQR1-09 (00-synthesis-v2.md §3.15; 04-workspace-types.md §9.1) — RATIFIED-S235, migration STILL-OPEN per Q-OQR1-16 item 9. The diagram renders the post-migration target shape. Current live-schema row already has workspace_id: string | null per supabase/types/database.types.ts — the migration formalises the nullable cardinality with the corresponding RLS-routing change. pipeline_runs columns and source_documents other columns reflect live schema, except where annotated (Q-OQR1-16) for post-migration shape — specifically content_items.op_id lands per N7 RESOLVED-S236 in the Q-OQR1-16 combined-PR migration (see 02-data-flow.md §5.3).

D-5. Cocoindex’s source-binding identity is the (connector, original_path) pair, where original_path is stored in source_documents.storage_path per 02-data-flow.md §2.2. The content-hash (stored in source_documents.content_hash) is the idempotency guard — if the hash matches the stored hash for that source-key, the cocoindex pipeline short-circuits via @coco.fn(memo=True).

D-6. Memoisation discipline: inner-tier cocoindex functions MUST consume content_text: str, NOT FileLike, per 05-qa-flow.md §5.2 layered fn-shape requirement (S9 spike RESOLVED-PARTIAL-S235; COCO.9 + COCO.10 CLOSED-CONDITIONAL). If inner fn receives FileLike, memo keys on whole-file fingerprint and any byte change invalidates — defeating the idempotency guarantee. This invariant lives at the cocoindex flow level, not in the schema, but it binds to this ERD because source_documents.content_hash is the substrate the discipline guards.

D-7. source_documents.workspace_id NULL with content-item-to-workspace edge per 02-data-flow.md §2.2 + 04-workspace-types.md §9.1. Admin-shared binaries (e.g. a Phew bid library DOCX used across multiple procurement workspaces) bind without a workspace assignment; RLS for the nullable case routes through content_items.source_document_id → content_item_workspaces.workspace_id.

1
erDiagram
2
    source_documents ||--o{ content_items : "source_document_id (FK)"
3
    content_items ||--o{ content_item_workspaces : "many-to-many junction"
4
    workspaces ||--o{ content_item_workspaces : "many-to-many junction"
5
    source_documents ||--o| pipeline_runs : "pipeline_run_id (NULL allowed)"
6

7
    source_documents {
8
        uuid id PK
9
        text storage_path "cocoindex source-key (connector://path)"
10
        text content_hash "idempotency guard (D-5)"
11
        text filename
12
        text original_filename
13
        text mime_type
14
        int file_size
15
        text extracted_text "Docling/pullmd markdown output"
16
        jsonb extraction_metadata
17
        uuid workspace_id "NULLABLE (Q-OQR1-09; D-7)"
18
        uuid pipeline_run_id "FK pipeline_runs (NULL allowed)"
19
        uuid uploaded_by "FK auth.users (SET NULL)"
20
        text status
21
        int version
22
        timestamptz created_at
23
    }
24

25
    content_items {
26
        uuid id PK
27
        uuid source_document_id "FK source_documents (NULL for non-binary sources)"
28
        text content
29
        text content_type
30
        text ingest_source
31
        uuid op_id "cocoindex per-flow run id"
32
    }
33

34
    content_item_workspaces {
35
        uuid id PK
36
        uuid content_item_id "FK content_items"
37
        uuid workspace_id "FK workspaces"
38
        timestamptz assigned_at
39
    }
40

41
    workspaces {
42
        uuid id PK
43
        text name
44
        uuid application_type_id "FK application_types (Q-OQR1-16 item 2)"
45
    }
46

47
    pipeline_runs {
48
        uuid id PK
49
        text pipeline_name
50
        int items_processed
51
        text_array items_created "array of created content_item ids (per recordPipelineRun)"
52
        int items_updated
53
        int items_skipped
54
        text error_message
55
        jsonb progress
56
        timestamptz created_at
57
        timestamptz completed_at
58
    }

Notes on rendering choices:

source_documents.workspace_id nullable is the load-bearing change; the diagram marks it explicitly. The RLS-routing-via-content-items semantics live in docs/specs/rls-pattern/ (forward-ref per 02-data-flow.md §6) and are NOT rendered as edges in the ERD — they are runtime RLS behaviour, not schema.
pipeline_runs is the KH-side rollup retained per N6 RESOLVED (02-data-flow.md §7.2). Cocoindex’s per-flow ledger is internal to cocoindex’s LMDB ops-DB; pipeline_runs insulates the KH UI from cocoindex schema changes (TS-facing cocoindex API STILL-OPEN per 00-synthesis-v2.md §5.2 row 5).
op_id on content_items is the column added per N7 hybrid op_id pattern (02-data-flow.md §5.3 + §5.1). RATIFIED-S236, column-level schema STILL-OPEN pending the Q-OQR1-16 combined-PR migration.

3.3 ERD — `workspaces` + `application_types` + per-application-type satellites

Owning prose: 04-workspace-types.md §2 (workspaces table shape) + §3 (application_types instance table) + §4 (per-application-type satellite pattern + reserved seats).

Migration state: application_types instance table + workspaces.application_type_id FK + 6 satellite reserved seats land in the Q-OQR1-16 combined PR per 04-workspace-types.md §8 items 1 + 2 + 4 (RATIFIED-S235, migration STILL-OPEN). The current live schema has workspaces.type text column with a CHECK constraint — that column drops in the combined migration; the FK to application_types takes its place per 04-workspace-types.md §2.2. The reserved seats (5 non-procurement satellites) ship upfront per S240 ratification (docs/specs/reserved-workspace-seats/PRODUCT.md S-1 through S-8).

D-8. application_types is an instance table with provenance enum (core / client / recommended) per Q-OQR1-01 Option (c) hybrid (04-workspace-types.md §3.1). Six core-provenance rows ship as seed data per Q-OQR1-03 (04-workspace-types.md §3.2): procurement, intelligence, sales_proposal, product_guide, competitor_research, training_onboarding. Client-extended application types use client provenance; platform-recommended-but-not-default extensions use recommended.

D-9. Each application_types row has a corresponding satellite table named <application_type_key>_workspaces (singular noun matching the key) per 04-workspace-types.md §4.1. Cardinality is 1:1 with workspaces (each workspace bound to application_type='procurement' has exactly one row in procurement_workspaces, FK to workspaces.id). Satellites use Shape B typed columns over JSONB per 00-synthesis-v2.md §3.9 platform standard.

D-10. Reserved-seat shape per docs/specs/reserved-workspace-seats/PRODUCT.md S-1 through S-8: 5 non-procurement satellite tables (intelligence_workspaces, sales_proposal_workspaces, product_guide_workspaces, competitor_research_workspaces, training_onboarding_workspaces) are created upfront with the minimal shape — primary key + FK to workspaces.id + RLS enabled via auto-RLS event trigger. Per-application-type column lists land per feature spec via ALTER TABLE ... ADD COLUMN at each application’s build cycle. procurement_workspaces carries the 6 ratified columns (buyer / deadline / submission_date / outcome / outcome_recorded_at / outcome_recorded_by per OQ-Q38-E + 04-workspace-types.md §4.2).

1
erDiagram
2
    application_types ||--o{ workspaces : "application_type_id (FK)"
3
    workspaces ||--|| procurement_workspaces : "1:1 satellite"
4
    workspaces ||--|| intelligence_workspaces : "1:1 satellite (reserved seat)"
5
    workspaces ||--|| sales_proposal_workspaces : "1:1 satellite (reserved seat)"
6
    workspaces ||--|| product_guide_workspaces : "1:1 satellite (reserved seat)"
7
    workspaces ||--|| competitor_research_workspaces : "1:1 satellite (reserved seat)"
8
    workspaces ||--|| training_onboarding_workspaces : "1:1 satellite (reserved seat)"
9

10
    application_types {
11
        uuid id PK
12
        text key "procurement/intelligence/sales_proposal/product_guide/competitor_research/training_onboarding (6 core)"
13
        text label
14
        text icon
15
        text route
16
        text state_machine_name
17
        text default_colour
18
        text default_icon
19
        text provenance "core/client/recommended (D-8)"
20
        boolean client_extensible
21
        boolean active
22
    }
23

24
    workspaces {
25
        uuid id PK
26
        text name
27
        text description
28
        uuid application_type_id "FK application_types (Q-OQR1-16 item 2 — replaces workspaces.type)"
29
        text status
30
        text colour "per-instance user pick"
31
        text icon "per-instance user pick"
32
        boolean is_archived
33
        timestamptz created_at
34
    }
35

36
    procurement_workspaces {
37
        uuid id PK
38
        uuid workspace_id "FK workspaces (1:1)"
39
        text buyer "promoted from former bid_workspaces.domain_metadata JSONB"
40
        date deadline
41
        date submission_date
42
        text outcome
43
        timestamptz outcome_recorded_at
44
        uuid outcome_recorded_by "FK auth.users"
45
    }
46

47
    intelligence_workspaces {
48
        uuid id PK
49
        uuid workspace_id "FK workspaces (1:1) — minimal seat (D-10)"
50
    }
51

52
    sales_proposal_workspaces {
53
        uuid id PK
54
        uuid workspace_id "FK workspaces (1:1) — minimal seat (D-10)"
55
    }
56

57
    product_guide_workspaces {
58
        uuid id PK
59
        uuid workspace_id "FK workspaces (1:1) — minimal seat (D-10)"
60
    }
61

62
    competitor_research_workspaces {
63
        uuid id PK
64
        uuid workspace_id "FK workspaces (1:1) — minimal seat (D-10)"
65
    }
66

67
    training_onboarding_workspaces {
68
        uuid id PK
69
        uuid workspace_id "FK workspaces (1:1) — minimal seat (D-10)"
70
    }

Notes on rendering choices:

The pre-migration workspaces.type text column is NOT rendered (it drops in the combined PR per 04-workspace-types.md §2.2 + §6 kb_section retire). Diagrams showing workspaces.type render the superseded framing per 04-workspace-types.md §11 anti-patterns.
The 5 reserved-seat satellites carry only PK + FK columns in the diagram — the per-app column lists land per feature spec. This matches the upfront-seats S240 ratification (docs/specs/reserved-workspace-seats/PRODUCT.md S-7).
application_types.client_extensible and application_types.active are config columns described at the pattern level in 04-workspace-types.md §3.1; the exact column-level shape lands in the migration draft.
workspaces.colour uses UK English in the column name per the project convention; the live schema has color (American spelling) — the rename is not in Q-OQR1-16 scope and is captured as a future cosmetic-only change.

3.4 ERD — `citations` polymorphic + `question_matches` discriminator

Owning prose: 05-qa-flow.md §6 (citations polymorphic shape — N8 RESOLVED-S234) + §7 (question_matches with question_kind discriminator + separate scoring columns — N9 RESOLVED-S236).

Migration state: Polymorphic citations.citing_entity enum + separate embedding_score / fulltext_score columns on question_matches are RATIFIED-S236 per N8 + N9 (00-synthesis-v2.md §5.1); migration STILL-OPEN per Q-OQR1-16. The table bid_question_matches is renamed to question_matches per Q-OQR1-02 (04-workspace-types.md §7.2 row 4); the table is never built under the old name — it lands with the new name in the combined PR.

D-11. citations.citing_entity is a closed-list polymorphic enum per N8 RESOLVED-S234 (05-qa-flow.md §6.1): bid_response, sales_proposal_response, competitor_research_finding, training_unit, mcp_search_response. The citing_entity_id UUID is a polymorphic reference — indexed per enum value rather than a single typed FK. Adding a new application type (e.g. audit_response) adds a new enum value, not a new column. The polymorphic shape allows the citations table to serve all current and future application types without schema change.

D-12. question_matches.question_kind aligns with the form_types vocabulary per Q-OQR1-02 + N9 (05-qa-flow.md §7.2). For v1 procurement workspaces, the value space is the procurement subset of docs/ontology/26-form-type.md baseline_values (source-of-truth). The renamed table generalises the pre-Q-OQR1-02 bid_question_matches to cross-form-type matching within the procurement umbrella.

D-13. question_matches carries SEPARATE embedding_score NUMERIC(5,4) and fulltext_score NUMERIC(5,4) columns per N9 RESOLVED-S236 (00-synthesis-v2.md §5.1; WP8 ruling 14/05/2026). A single blended match_score column is [RATIFIED-RENAME] per 05-qa-flow.md §11 anti-patterns — blended single score hides per-method tunability signal. The blend function and UI score-presentation policy are tech-spec scope per 05-qa-flow.md §7.3 gap flag (deferred to docs/specs/procurement-question-matching/TECH.md at procurement build cycle start).

D-14. Citations are version-on-cite per §6.0.3 of 0.9-edit-flow-investigation.md (05-qa-flow.md §6.3). When a bid response ships, it captures the q_a_pair version used at ship time; the q_a_pair can continue to evolve; the shipped citation resolves to the version snapshot via q_a_pair_history. This is the load-bearing design that allows Q&A pairs to improve over time without retroactively altering shipped artefacts. The ERD edge from citations to q_a_pairs is rendered as a many-to-one FK; the version-on-cite behaviour is runtime, not schema.

1
erDiagram
2
    citations }o--|| q_a_pairs : "cited_q_a_pair_id (FK)"
3
    citations ||--o| bid_responses : "citing_entity_id when citing_entity='bid_response'"
4
    citations ||--o| q_a_pairs : "version-on-cite via q_a_pair_history (D-14)"
5
    question_matches }o--|| q_a_pairs : "q_a_pair_id (FK)"
6
    question_matches }o--|| bid_questions : "form_question_id when question_kind in form_types"
7
    bid_questions }o--|| workspaces : "project_id (legacy; rename to workspace_id per Q-OQR1-16 item 5)"
8
    bid_responses }o--|| bid_questions : "question_id (FK)"
9

10
    citations {
11
        uuid id PK
12
        text citing_entity "ENUM: bid_response/sales_proposal_response/competitor_research_finding/training_unit/mcp_search_response (D-11)"
13
        uuid citing_entity_id "polymorphic FK per enum value"
14
        uuid cited_q_a_pair_id "FK q_a_pairs"
15
        int cited_q_a_pair_version "version-on-cite snapshot (D-14)"
16
        text citation_kind
17
        timestamptz created_at
18
    }
19

20
    question_matches {
21
        uuid id PK
22
        text question_kind "form_type discriminator per docs/ontology/26-form-type.md baseline_values (D-12)"
23
        uuid form_question_id "polymorphic per question_kind"
24
        uuid q_a_pair_id "FK q_a_pairs (corpus-level)"
25
        numeric_5_4 embedding_score "separate column (D-13)"
26
        numeric_5_4 fulltext_score "separate column (D-13)"
27
        timestamptz matched_at
28
    }
29

30
    q_a_pairs {
31
        uuid id PK
32
        text_array scope_tag "workspace-relevance filter"
33
        text_array anti_scope_tag
34
    }
35

36
    bid_questions {
37
        uuid id PK
38
        uuid project_id "FK workspaces (renames to workspace_id per Q-OQR1-16 item 5)"
39
        text question_text
40
    }
41

42
    bid_responses {
43
        uuid id PK
44
        uuid question_id "FK bid_questions"
45
        text response_text
46
        text review_status
47
    }

Notes on rendering choices:

citations.citing_entity_id is a polymorphic UUID — it references bid_responses when citing_entity='bid_response', sales_proposal_response_id when the value is 'sales_proposal_response', and so on. Mermaid cannot render a polymorphic FK natively; the diagram annotates the contract at citing_entity_id and renders the dominant edge (to bid_responses) explicitly. The other 4 enum values follow the same pattern when their respective application types ship.
question_matches.form_question_id follows the same polymorphic-FK pattern keyed on question_kind. For v1, bid_questions is the only form-question table; future form-types add their own tables, and form_question_id references the table aligned with question_kind value.
The bid_questions.project_id → workspace_id rename is companion cleanup per Q-OQR1-16 item 5 (04-workspace-types.md §7.4): 2 DB columns + a 44-file code-file sweep via ts-morph + ast-grep. The diagram renders the pre-rename column name to match the live schema; the rename is in scope for the combined PR.
q_a_pair_history is rendered in ERD 3.1; the version-on-cite mechanic (D-14) reaches across both ERDs — citations.cited_q_a_pair_version snapshots the version, recoverable via q_a_pair_history.version lookup at any future read time.

4. Sequence diagrams

4.1 Sequence — Ingest flow with Cloud Run sidecar

Owning prose: 02-data-flow.md §3 (cocoindex flow stages) + §4 (Cloud Run sidecar topology) + §5 (audit_log + op_id propagation).

D-15. Cocoindex flow stages run in order per 02-data-flow.md §3.1: source walk → binary conversion (Docling for PDF/DOCX/XLSX; pullmd for HTML) → LLM extraction (ExtractByLlm) → embedding (LiteLLMEmbedder("openai/text-embedding-3-large")) → entity resolution → Postgres UPSERT. The sidecar runs Docling + pullmd + cocoindex engine; Vercel function bundle stays under the 250 MB limit by delegating binary parsing to the sidecar (Docling’s 1.8 GB footprint mandates this per 02-data-flow.md §4.1).

D-16. op_id propagates from cocoindex’s per-flow run identifier into Postgres target columns per N7 RESOLVED-S236 (02-data-flow.md §5.1). Every content_items row, every q_a_extractions row, every source_documents state update written by a given cocoindex run carries that run’s op_id. The audit_log trigger fires independently on AFTER INSERT/UPDATE/DELETE and writes its own row with the table name, row id, operation type, and invoking role — providing audit cohesion across cocoindex and non-cocoindex writes.

D-17. Re-classification trigger is governed by edit_intent per 02-data-flow.md §8.2: cosmetic skips re-run; data and structural trigger re-run. Cocoindex @coco.fn(memo=True) short-circuits at the classification stage for cosmetic edits after the content-hash check.

1
sequenceDiagram
2
    autonumber
3
    participant FS as Source folder (LocalFS/SharePoint)
4
    participant CR as Cloud Run sidecar
5
    participant CI as cocoindex engine
6
    participant DL as Docling (binary parser)
7
    participant PM as pullmd (HTML adapter)
8
    participant LLM as Anthropic ExtractByLlm
9
    participant EM as LiteLLM embedder
10
    participant PG as Supabase Postgres
11
    participant AL as audit_log trigger
12

13
    FS->>CI: localfs.walk_dir(live=True) emits file-change event
14
    activate CI
15
    Note over CI: op_id generated for this flow run (D-16)
16
    CI->>CR: files_transform invokes per-MIME @coco.fn wrapper
17
    activate CR
18

19
    alt MIME = PDF / DOCX / XLSX
20
        CR->>DL: parse binary → GFM markdown
21
        DL-->>CR: content_text: str
22
    else MIME = HTML / URL
23
        CR->>PM: pullmd extract → GFM markdown
24
        PM-->>CR: content_text: str + share_id
25
    else MIME = markdown
26
        CR-->>CR: pass-through (no conversion)
27
    end
28

29
    CR-->>CI: content_text: str (D-15 inner-fn signature)
30
    deactivate CR
31

32
    Note over CI: @coco.fn(memo=True) check — content-hash match short-circuits (D-17)
33

34
    alt content-hash unchanged AND edit_intent='cosmetic'
35
        CI-->>PG: skip LLM extraction + embedding (memo hit)
36
    else content-hash changed OR edit_intent in (data,structural)
37
        CI->>LLM: ExtractByLlm with typed output_type
38
        LLM-->>CI: classification + Q&A extractions + entities
39
        CI->>EM: LiteLLMEmbedder("openai/text-embedding-3-large")
40
        EM-->>CI: vector(1024) embedding
41
    end
42

43
    CI->>PG: postgres.mount_table_target UPSERT content_items (carries op_id)
44
    activate PG
45
    PG->>AL: AFTER INSERT/UPDATE trigger fires
46
    AL-->>PG: audit_log row written (table_name + row_id + op_type + invoking_role)
47
    PG-->>CI: success
48
    deactivate PG
49

50
    opt q_a_extraction emitted
51
        CI->>PG: UPSERT q_a_extractions (carries op_id, source_content_item_id)
52
        PG->>AL: trigger fires for q_a_extractions
53
    end
54

55
    opt source_documents state change
56
        CI->>PG: UPDATE source_documents (carries op_id, status)
57
        PG->>AL: trigger fires for source_documents
58
    end
59

60
    deactivate CI
61
    Note over PG,AL: audit_log carries trigger-driven cohesion;<br/>op_id columns carry cocoindex per-flow correlation

Notes on rendering choices:

The alt block on MIME branching reflects the per-MIME viewer-composition pattern per 02-data-flow.md §4.2 + 03-tech-stack.md §9.5.
The alt block on memo hit reflects the edit_intent-driven re-classification policy per 02-data-flow.md §8.2. The cosmetic skip relies on both (a) content-hash unchanged AND (b) edit_intent being cosmetic — the diagram captures the dual condition.
pipeline_runs insert is not rendered in this sequence; it is the KH-side rollup written via recordPipelineRun() per 02-data-flow.md §7.2 + CLAUDE.md “Cron pipeline_runs inserts” gotcha. The rollup is asynchronous to the cocoindex flow and not part of the per-row write path.
RAISE LOG from the auto-RLS event trigger is not rendered here (it fires only on CREATE TABLE, not on table writes); see §4.4 for that sequence.

4.2 Sequence — Q&A round-trip (search + verbatim fetch)

Owning prose: 05-qa-flow.md §6 (citations polymorphic) + §7 (question_matches discriminator + separate scoring); 06-mcp-tooling.md §4 (Theme F mempalace direct pattern) — applies to the MCP-server-to-Claude transport tier.

D-18. Two-step retrieval pattern per S16 §6.1 (05-qa-flow.md §7.2) + AI-consumer-first principle (01-vision.md §2.1): a fast ranking pass using embedding_score + fulltext_score returns a preview list (list/preview tool); the caller then fetches verbatim answer text for the top-N candidates (get/verbatim tool). The two-step pattern keeps the MCP response compact at the list step and saves token budget for the verbatim fetch step.

D-19. Workspace relevance is computed at query time via scope_tag overlap per Q-OQR1-06 (05-qa-flow.md §1.1). The q_a_search RPC applies WHERE q_a_pairs.scope_tag && workspaces.scope_tag AND NOT (q_a_pairs.anti_scope_tag && workspaces.scope_tag) before scoring — this is the substrate that makes the corpus-level shape (no workspace_id FK on q_a_pairs) safe for cross-application-type Q&A.

D-20. embedding_score and fulltext_score ship as separate response columns per N9 RESOLVED-S236 (05-qa-flow.md §7.3) — the blend function and UI score-presentation policy are tech-spec scope per the §7.3 gap flag. The architecture sub-doc carries the schema shape (two separate columns); feature spec owns the runtime combination policy.

1
sequenceDiagram
2
    autonumber
3
    participant U as Claude client (Word/Desktop/Code)
4
    participant MCP as KH MCP server (Vercel)
5
    participant RPC as q_a_search RPC (Postgres)
6
    participant QAP as q_a_pairs (corpus-level)
7
    participant QM as question_matches
8
    participant WS as workspaces (scope_tag)
9

10
    U->>MCP: q_a_search(query, workspace_id)
11
    activate MCP
12
    Note over MCP: build embedding via text-embedding-3-large<br/>then call q_a_search RPC
13

14
    MCP->>RPC: q_a_search(query_embedding, fulltext_terms, workspace_id)
15
    activate RPC
16

17
    RPC->>WS: SELECT scope_tag, anti_scope_tag<br/>WHERE id = workspace_id
18
    WS-->>RPC: workspace scope tags
19

20
    RPC->>QAP: scope_tag overlap filter (D-19)
21
    Note over RPC,QAP: WHERE scope_tag && ws.scope_tag<br/>AND NOT (anti_scope_tag && ws.scope_tag)
22
    QAP-->>RPC: candidate q_a_pairs (corpus-filtered by scope_tag)
23

24
    RPC-->>RPC: score candidates:<br/>embedding_score = cosine(query_embedding, question_embedding)<br/>fulltext_score = ts_rank(question_text, query_terms)
25
    Note over RPC: SEPARATE columns per N9 (D-20)
26

27
    opt question_matches cache hit
28
        RPC->>QM: read cached match if recent
29
        QM-->>RPC: cached (embedding_score, fulltext_score)
30
    end
31

32
    RPC-->>MCP: top-N preview list:<br/>[{q_a_pair_id, question_text, embedding_score, fulltext_score, brief_answer}]
33
    deactivate RPC
34

35
    MCP-->>U: q_a_search response (preview list — list/preview step, D-18)
36
    deactivate MCP
37

38
    Note over U: User/Claude picks top candidate(s)<br/>and requests verbatim fetch
39

40
    U->>MCP: q_a_get(q_a_pair_id) for top candidate
41
    activate MCP
42

43
    MCP->>QAP: SELECT answer_standard, answer_advanced, citations<br/>WHERE id = q_a_pair_id
44
    QAP-->>MCP: verbatim answer + citation lineage
45

46
    MCP-->>U: q_a_get response (verbatim answer — get/verbatim step, D-18)
47
    deactivate MCP
48

49
    Note over MCP,U: Two-step pattern keeps preview compact<br/>and verbatim payload precise per request

Notes on rendering choices:

The diagram covers the read tier. Q&A write-back (UC6) is rendered in §4.3 as part of the bid-feedback 3-UC composite.
question_matches is shown as a cached-match read in the opt block. The cache is populated by background extraction per 05-qa-flow.md §3; live q_a_search calls compute scores at query time and may or may not write back to question_matches depending on the runtime caching policy (feature spec scope per 05-qa-flow.md §7.3).
MCP-server transport is the KH MCP server per 06-mcp-tooling.md. The mempalace direct pattern (per 06-mcp-tooling.md §4) does not interact with Q&A search — mempalace is the memory subsystem; Q&A search is a separate KH MCP tool. The diagram does not render mempalace interactions for the Q&A flow.
Auth context (RLS + role checks) is implicit — the RPC runs with the caller’s auth context per getAuthorisedClient() discriminated union (CLAUDE.md Data & Architecture gotcha); the diagram does not render the auth check explicitly to keep the Q&A flow legible.

4.3 Sequence — Bid-feedback 3-UC composite (UC5 + UC6 + UC8)

Owning prose: 08-new-features.md §7 (bid-feedback loop 3-UC composite); 05-qa-flow.md §8 (UC6 Q&A revise) + §9 (UC5 bid response → Q&A promotion); 08-new-features.md §3 (UC8 dedup with temporal).

D-21. Bid-feedback loop composes three S229-RATIFIED UCs per 08-new-features.md §7.1: UC5 (bid response → Q&A pair promotion) + UC6 (Q&A pair revise — user-direct + AI-suggest sub-variants) + UC8 (cross-workspace dedup + merge). The composition is the v1 user-data-quality flywheel: bid responses produce Q&A pairs, Q&A pairs get refined, and the resulting corpus is deduplicated cross-workspace.

D-22. Lost-bid promotion default is skip per N10 RESOLVED-S234 (08-new-features.md §7.2). A bid response from a lost bid does NOT auto-promote to a Q&A pair; the user can override per-response with explicit promotion. Won-bid responses default to promote. This prevents corpus pollution with answers the buyer rejected.

D-23. Version-on-cite carry-through per §6.0.3 of 0.9-edit-flow-investigation.md (05-qa-flow.md §6.3): a UC5 promotion captures the bid-response version at promotion time; the resulting q_a_pair evolves under UC6; shipped citations bind to the version snapshot via q_a_pair_history. UC6 revisions move publication_status from published back to in_review per 05-qa-flow.md §8.3.

D-24. UC8 dedup runs on cocoindex entity_resolution selective adoption per 08-new-features.md §3.3 (organisations, people, products/services in scope; free-text concepts deferred to v2). User-driven merge / supersede / proceed-anyway resolution per cross-UC §6.0.5 downstream-impact UI pattern. superseded_by self-FK on q_a_pairs (ERD 3.1) captures the merge lineage.

1
sequenceDiagram
2
    autonumber
3
    participant U as User (curator/reviewer)
4
    participant KH as KH server (Next.js)
5
    participant PG as Supabase Postgres
6
    participant CI as cocoindex (entity_resolution)
7
    participant LLM as Anthropic (AI-suggest)
8

9
    Note over U,LLM: UC5 — Bid response → Q&A promotion
10

11
    U->>KH: Promote bid response to Q&A pair
12
    activate KH
13

14
    alt bid outcome = lost (D-22)
15
        Note over KH: default skip; user must explicitly override
16
        opt explicit-override toggle on
17
            KH->>KH: proceed with promotion
18
        end
19
    else bid outcome = won
20
        KH->>KH: default promote
21
    end
22

23
    KH->>PG: SELECT close-match q_a_pairs<br/>WHERE cosine(question_embedding, source_q_embedding) >= 0.85
24
    PG-->>KH: candidate close matches (if any)
25

26
    alt close match found
27
        KH-->>U: Surface options:<br/>supersede / merge (→UC6) / proceed-anyway
28
        U->>KH: pick option
29
    else no close match
30
        KH->>KH: proceed with new pair
31
    end
32

33
    KH->>PG: INSERT q_a_pairs (origin_kind='derived_from_bid_response',<br/>source_workspace_id=<procurement_ws_id>,<br/>publication_status='draft',<br/>valid_from=now())
34
    PG->>PG: trigger writes q_a_pair_history row v1 (D-23)
35
    PG-->>KH: q_a_pair_id
36

37
    KH-->>U: Q&A pair drafted; routed to review queue
38
    deactivate KH
39

40
    Note over U,LLM: UC6 — Q&A pair revise (user-direct OR AI-suggest)
41

42
    alt User-direct revision
43
        U->>KH: Open Q&A pair in Tiptap+Yjs ContentEditor
44
        activate KH
45
        U->>KH: Edit question/answer + state intent from closed vocab<br/>(typo/factual-update/regulatory-change/etc.)
46
        U->>KH: Commit
47
        KH->>PG: UPDATE q_a_pairs (valid_to=now() for prior row;<br/>INSERT new q_a_pair_history row v2)
48
        PG->>PG: publication_status moves published→in_review (D-23)
49
        PG-->>KH: success
50
        deactivate KH
51
    else AI-suggest revision (Candidate B)
52
        Note over KH: source-doc change detected via cocoindex source-binding
53
        KH->>LLM: propose revision draft (source-doc delta as context)
54
        LLM-->>KH: revision draft
55
        KH-->>U: Surface AI-suggested revision for review
56
        U->>KH: edit + commit (falls back to user-direct flow above)
57
    end
58

59
    Note over U,LLM: UC8 — Cross-workspace dedup + merge (D-24)
60

61
    CI->>PG: entity_resolution scan over q_a_pairs corpus
62
    activate CI
63
    Note over CI: scope: organisations, people, products/services<br/>(free-text concepts deferred to v2)
64
    CI->>PG: detect duplicate cluster (q_a_pair_a, q_a_pair_b)
65
    deactivate CI
66

67
    PG-->>U: Surface dedup candidate (downstream-impact UI:<br/>count + paginated affected-record list per §6.0.5)
68

69
    alt User picks merge
70
        U->>KH: Merge q_a_pair_b into q_a_pair_a
71
        activate KH
72
        KH->>PG: UPDATE q_a_pairs SET superseded_by=a_id<br/>WHERE id=b_id
73
        KH->>PG: re-anchor citations via hybrid policy (§6.0.2):<br/>verbatim match → re-anchor; else mark-as-paraphrased
74
        PG->>PG: q_a_pair_history rows updated; citations bound to versions
75
        PG-->>KH: success
76
        deactivate KH
77
    else User picks proceed-anyway
78
        Note over U,KH: corpus carries both records; no schema change
79
    end
80

81
    Note over U,PG: Composition (D-21):<br/>UC5 produces → UC6 refines → UC8 deduplicates<br/>= v1 user-data-quality flywheel

Notes on rendering choices:

The three UCs are rendered sequentially in the diagram for clarity, but in production they operate at different cadences: UC5 fires on user-initiated promotion at bid-close time; UC6 fires on user-initiated revision OR on source-doc-change-driven AI-suggest; UC8 fires on cocoindex scheduled entity_resolution scan.
The alt block in UC8 (merge vs proceed-anyway) renders the user-driven resolution per 08-new-features.md §3.1. Auto-merge is NOT a v1 behaviour — every UC8 resolution is user-clicked per 0.9-edit-flow-investigation.md §6.8 RATIFIED-S229.
audit_log entries fire on every governed-table write per the trigger pattern (D-16); they are not rendered as separate steps here to keep the bid-feedback composition legible. Every INSERT/UPDATE on q_a_pairs in this sequence has a corresponding audit_log row written by the same AFTER INSERT/UPDATE trigger described in §4.1.
The multi-Q&A revision atomicity pattern per 05-qa-flow.md §8.4 (shared-op-ID multi-Q&A transactional revision) is NOT separately rendered — it applies to UC6 when a regulatory update touches many Q&A pairs at once; the per-op rollback affordance reverses all touched pairs in one operation.

4.4 Sequence — Auto-RLS event trigger on `CREATE TABLE`

Owning prose: docs/specs/rls-pattern/PRODUCT.md P-1 (RLS auto-enabled on new public tables) + TECH.md T-1 (rls_auto_enable() event trigger pattern). Migration draft: supabase/migrations/20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql — APPLY GATED ON LIAM REVIEW.

D-25. The ensure_rls event trigger fires on ddl_command_end for command tags CREATE TABLE / CREATE TABLE AS / SELECT INTO per docs/specs/rls-pattern/TECH.md T-1. The trigger function rls_auto_enable() iterates DDL commands and, for objects in the public schema (skipping pg_catalog / information_schema / pg_toast* / pg_temp*), executes ALTER TABLE ... ENABLE ROW LEVEL SECURITY. This guarantees every new public-schema table has RLS enabled without per-migration developer action.

D-26. P-5 observability is [DEFERRED-v1.1] per Liam S240 ratification (docs/specs/rls-pattern/PRODUCT.md P-5). The v1 observability hook is RAISE LOG only — no audit_log row insert; no separate fan-out helper. Postgres logs flow to the Cloud Run sidecar log ingest and into the KH observability stack via standard log shipping. Option (a) audit_log insert and option (b) fan-out helper remain v1.1 candidates if operational signal surfaces a need.

D-27. P-4 per-function anon REVOKE-EXECUTE is orthogonal to P-1 — the auto-RLS event trigger covers tables only, not functions per docs/specs/rls-pattern/PRODUCT.md P-4. Every new PL/pgSQL function in public.* requires an explicit REVOKE EXECUTE ON FUNCTION public.foo() FROM anon in its migration. CI guard migration-revoke-guard.yml enforces. This invariant is not rendered in the sequence below (the trigger fires on table DDL, not function DDL) but is recorded for completeness.

1
sequenceDiagram
2
    autonumber
3
    participant DEV as Developer
4
    participant MIG as Migration runner (Supabase CLI)
5
    participant PG as PostgreSQL engine
6
    participant ET as ensure_rls event trigger
7
    participant FN as rls_auto_enable() fn
8
    participant PGC as pg_class catalog
9
    participant LOG as Postgres log (RAISE LOG)
10
    participant CR as Cloud Run sidecar log ingest
11

12
    DEV->>MIG: supabase db push (migration with CREATE TABLE)
13
    activate MIG
14

15
    MIG->>PG: Execute migration SQL
16
    activate PG
17

18
    PG->>PG: CREATE TABLE public.my_new_table (...)
19
    Note over PG: relrowsecurity = false (default at table creation)
20

21
    PG->>ET: ddl_command_end fires (tag=CREATE TABLE) (D-25)
22
    activate ET
23

24
    ET->>FN: invoke rls_auto_enable()
25
    activate FN
26

27
    FN->>FN: iterate pg_event_trigger_ddl_commands()
28

29
    alt object schema = public AND not in skip-list
30
        FN->>PG: ALTER TABLE public.my_new_table ENABLE ROW LEVEL SECURITY
31
        PG-->>FN: success
32
        FN->>LOG: RAISE LOG 'rls_auto_enable: enabled RLS on public.my_new_table' (D-26)
33
    else object schema in (pg_catalog, information_schema, pg_toast*, pg_temp*)
34
        Note over FN: skip — not a user table
35
    end
36

37
    deactivate FN
38
    deactivate ET
39

40
    PG->>PGC: post-trigger state: relrowsecurity = true
41
    Note over PG,PGC: table now has RLS enabled
42

43
    Note over PG: Per P-2 — explicit grants required<br/>for Data API exposure;<br/>SELECT grant_standard_public_table_access('public.my_new_table'::regclass)
44

45
    PG-->>MIG: migration applied successfully
46
    deactivate PG
47

48
    MIG-->>DEV: success
49
    deactivate MIG
50

51
    LOG->>CR: log line shipped via standard Postgres log forwarding (D-26)
52
    Note over CR: v1 observability is structured-logging only;<br/>audit_log insert + fan-out helper are v1.1 candidates
53

54
    Note over DEV,CR: Verification:<br/>SELECT pg_class.relrowsecurity = true<br/>FROM pg_class WHERE relname='my_new_table'

Notes on rendering choices:

The alt block on schema-in-skip-list reflects the safety filter in rls_auto_enable() per docs/specs/rls-pattern/TECH.md T-1. The skip-list prevents the trigger from attempting to enable RLS on system catalogues (where it would either fail or be inappropriate).
The grant_standard_public_table_access(target_table regclass) helper is rendered as a Note over PG callout rather than a separate step — the helper is invoked explicitly by the developer in the migration (per docs/specs/rls-pattern/TECH.md T-2) and is not auto-fired by the event trigger. P-1 (auto-RLS) and P-2 (grants) are separate invariants; the combined migration pairs them but they remain logically distinct.
The verification step at the bottom (pg_class.relrowsecurity = true) is the per-table assertion that the auto-RLS pattern is working — it appears in the migration’s post-apply checklist per docs/specs/rls-pattern/TECH.md T-3.
For the Q-OQR1-16 combined PR (per 04-workspace-types.md §8), the trigger fires repeatedly — once per new table created by the migration: application_types, procurement_workspaces, the 5 reserved-seat satellites per docs/specs/reserved-workspace-seats/PRODUCT.md, and any new vocabulary tables. Each CREATE TABLE invocation walks through the sequence above independently.

5. Cross-doc references

5.1 Schemas anchored in `04-workspace-types.md`

This sub-doc renders the following schema artefacts; column-level detail lives in the cited 04-workspace-types.md sections:

ERD section	Anchored prose
3.1 q_a_pairs corpus-level shape	`04-workspace-types.md` §5
3.1 q_a_extractions two-tier model	`05-qa-flow.md` §3
3.2 source_documents nullable workspace_id	`04-workspace-types.md` §9.1
3.3 application_types instance table	`04-workspace-types.md` §3
3.3 reserved-seat satellites	`04-workspace-types.md` §4.2; `docs/specs/reserved-workspace-seats/{PRODUCT,TECH}.md`
3.4 citations polymorphic	`05-qa-flow.md` §6
3.4 question_matches discriminator + separate scoring	`05-qa-flow.md` §7

5.2 Flows anchored in `02-data-flow.md`

Sequence section	Anchored prose
4.1 cocoindex flow stages	`02-data-flow.md` §3.1
4.1 Cloud Run sidecar topology	`02-data-flow.md` §4.1
4.1 audit_log + op_id hybrid	`02-data-flow.md` §5.1 + §5.3
4.1 edit_intent re-classification policy	`02-data-flow.md` §8.2

5.3 Q&A round-trip anchored in `05-qa-flow.md`

Sequence section	Anchored prose
4.2 two-step retrieval (list/preview → get/verbatim)	`05-qa-flow.md` §7.2
4.2 scope_tag overlap workspace-relevance	`05-qa-flow.md` §1.1 + §2.2
4.2 separate embedding_score + fulltext_score	`05-qa-flow.md` §7.3

5.4 Bid-feedback 3-UC anchored in `08-new-features.md`

Sequence section	Anchored prose
4.3 UC5 promotion	`05-qa-flow.md` §9
4.3 UC6 revise (user-direct + AI-suggest)	`05-qa-flow.md` §8
4.3 UC8 dedup with temporal	`08-new-features.md` §3
4.3 lost-bid default skip (N10)	`08-new-features.md` §7.2
4.3 version-on-cite	`05-qa-flow.md` §6.3

5.5 Auto-RLS anchored in `docs/specs/rls-pattern/`

Sequence section	Anchored prose
4.4 ensure_rls event trigger	`docs/specs/rls-pattern/TECH.md` T-1
4.4 RAISE LOG observability (v1)	`docs/specs/rls-pattern/PRODUCT.md` P-5 + `TECH.md` T-5
4.4 grant_standard_public_table_access helper	`docs/specs/rls-pattern/TECH.md` T-2
4.4 per-function anon REVOKE-EXECUTE (orthogonal)	`docs/specs/rls-pattern/PRODUCT.md` P-4 + `TECH.md` T-4

6. Anti-patterns to avoid

Per construction guide §6 — diagrams that contradict the prose. This sub-doc’s §6 is thin by design (the prose sub-docs carry the load-bearing anti-pattern tables in their own §6 + §10 + §11 sections; the diagrams render only ratified state).

Diagram anti-pattern	Why rejected	Citation
Render `q_a_pairs.workspace_id` FK column in any ERD	Superseded by corpus-level + scope_tag-driven relevance per Q-OQR1-06; the column does not exist in the post-migration shape	`04-workspace-types.md` §11; `05-qa-flow.md` §11
Render `idx_q_a_pairs_workspace` (workspace-partition index) on `q_a_pairs`	DO-NOT-BUILD per Q-OQR1-06 — workspace relevance computed at read time via scope_tag GIN index	`04-workspace-types.md` §11; `05-qa-flow.md` §11
Render `workspaces.type` text column with CHECK constraint	Drops in combined PR per Q-OQR1-16 item 2; replaced by `workspaces.application_type_id` FK	`04-workspace-types.md` §11
Render `bid_workspaces` (or `bid_question_matches`) under the old name	Pre-Q-OQR1-02 framing; tables ship under new names (`procurement_workspaces`, `question_matches`)	`04-workspace-types.md` §7; `05-qa-flow.md` §11
Render `kb_section` value in any workspace-discriminator diagram	Added in error; zero prod rows; retires in Q-OQR1-16 combined PR	`04-workspace-types.md` §6 + §11
Render `pipeline_failures` as a target table for any ingest sequence	DO-NOT-BUILD per COCO.7 — cocoindex retry/back-off/DLQ subsumes; `pipeline_runs` handles rollup	`02-data-flow.md` §7.3 + §10.4
Render `citations.bid_response_id NOT NULL` as the sole citer column	Superseded by `citing_entity` polymorphic enum (N8 RESOLVED-S234); hard-coded bid_response FK cannot serve cross-application-type citation	`05-qa-flow.md` §11
Render a single blended `match_score` column on `question_matches`	Separate `embedding_score` + `fulltext_score` columns per N9 RESOLVED-S236	`05-qa-flow.md` §11
Render the ingest sequence with binary parsing inside the Vercel function	Docling’s 1.8 GB footprint exceeds Vercel’s 250 MB function-bundle limit; Cloud Run sidecar is mandatory	`02-data-flow.md` §4.1 + §10.5
Render `audit_log` writes as app-stamped (without trigger)	Trigger-driven coverage is N7 RESOLVED-S236; app-stamped audit creates silent gaps in new write paths	`02-data-flow.md` §5.2 + §10.5
Render `digests` as a current table name	Renamed to `change_reports` per Q-OQR1-16 item 7; user-facing label has been “Change Reports” already	`08-new-features.md` §4

7. Source-doc supersession

This sub-doc is the ninth and final of nine sub-docs that supersede docs/plans/phase-0-investigation/0.9-intended-architecture.md (2015 lines, S229). The source doc’s §15 carries three illustrative ERD sketches that predate the S233-S237 ratifications — those sketches are NOT carried forward verbatim; the ERDs in §3 of this sub-doc redraw from the current state per 04-workspace-types.md + 05-qa-flow.md + 02-data-flow.md schemas. Full audit trail of the 10 superseded items + ratifying doc per row: docs/specs/core-docs-pathway-assessment/INV-architecture-split-readiness.md §2. Canonical-state sources downstream: 00-synthesis-v2.md §3 + §5; 0.9-decision-graph.md §11; the 8 prose sub-docs (01-vision.md through 08-new-features.md) for the per-section content this sub-doc visualises.

Per docs/specs/core-docs-pathway-assessment/architecture-sub-doc-construction-guide.md §10.4, the final cross-doc audit gate is the closure point at which 0.9-intended-architecture.md archives to .planning/.archive/.specs/0.9-intended-architecture.md per CLAUDE.md “Historical planning”. The archive is a Wave 4 action; this sub-doc’s landing closes the 9-sub-doc family but does not itself execute the archive.

7.1 Heritage docs feeding this sub-doc

Per construction guide §4.1 — three-tier status taxonomy. The heritage table is thinner here than for other sub-docs: the diagrams render from the prose sub-docs (which carry their own heritage tables), not from external heritage docs.

Doc	Date	Status	Useful for
`docs/plans/phase-0-investigation/0.9-intended-architecture.md` §15 (illustrative diagrams)	10/05/2026 (S229 — pre-Q-OQR1 framing)	`[FULLY-SUPERSEDED]` — pre-S235 ERD sketches predate the application_types instance table, the q_a_pairs corpus-level shape, the polymorphic citations enum, and the question_matches generalisation. Preserved in `.planning/.archive/.specs/` post-Wave 4 audit for audit trail only.	Pre-supersession ERD sketches as a record of the framing shift — cite only for the audit-trail surface, NOT as ratification source for any diagram in this sub-doc.
`supabase/types/database.types.ts`	(auto-generated; refreshed per `supabase gen types typescript` per CLAUDE.md)	`[CURRENT-CANONICAL]` for the live-schema column lists of `content_items`, `source_documents`, `workspaces`, `content_history`, `content_item_workspaces`, `pipeline_runs`, `bid_questions`, `bid_responses`.	Ground-truth column lists for ERDs 3.1, 3.2, 3.3, 3.4 — every column rendered in those ERDs that corresponds to a live-schema column was cross-checked against this file at draft time.
`docs/reference/SCHEMA-QUICK-REFERENCE.md`	(refreshed per `bun run stats` + schema migrations)	`[CURRENT-CANONICAL]` for the live-schema RPC / function / trigger inventory and the per-table column descriptions.	Authoritative description of `content_history` shape (§10) used as the ERD 3.1 cross-check; authoritative description of `content_citations` shape (§13) — note that `content_citations` and `citations` in this sub-doc’s ERD 3.4 represent the post-migration shape per N8 RESOLVED-S234 (separate evolution from the live `content_citations` table).
`docs/specs/rls-pattern/PRODUCT.md` + `TECH.md`	14/05/2026 (S239 NEW; S240 P-5/T-5 edits)	`[CURRENT-CANONICAL]` for the auto-RLS event trigger pattern + P-1..P-5 invariants.	Substrate for sequence diagram §4.4 (auto-RLS event trigger). All claims in the diagram trace to P-1 + P-5 + T-1 + T-5.
`docs/specs/reserved-workspace-seats/PRODUCT.md` + `TECH.md`	15/05/2026 (S240 NEW)	`[CURRENT-CANONICAL]` for the 5 reserved-satellite-seat pattern (S-1 through S-8).	Substrate for ERD 3.3 reserved-seat rendering. The 5 satellite tables shown with minimal PK + FK shape trace to S-7 (no per-app columns at v1).

7.2 Cross-sub-doc consistency verification

Per docs/specs/core-docs-pathway-assessment/architecture-sub-doc-readiness-audit.md Risk 5 (“Diagram drift in 09-diagrams.md”): “no schema appears in a diagram that doesn’t appear in the corresponding prose sub-doc.”

Verification pass at draft time (S242):

Diagram entity / step	Prose sub-doc anchor	Verified
ERD 3.1 — `content_items` columns	`04-workspace-types.md` §5; `supabase/types/database.types.ts` live schema	Cross-checked column list; `content_text_hash` GENERATED marked per CLAUDE.md gotcha
ERD 3.1 — `q_a_pairs` columns	`04-workspace-types.md` §5; `05-qa-flow.md` §2.1 shape-critical columns	All columns trace to §2.1 table; no fabricated columns
ERD 3.1 — `q_a_extractions` columns	`05-qa-flow.md` §3.1 + §3.2 extractor_kind enum	All columns trace to §3 prose; `invalidated_at` semantics from §3.4
ERD 3.1 — `q_a_pair_history` columns	`05-qa-flow.md` §3.3 history mirror	Trigger-driven shape mirrors `content_history` per §3.3
ERD 3.2 — `source_documents` nullable workspace_id	`02-data-flow.md` §2.2; `04-workspace-types.md` §9.1	Q-OQR1-09 ratification cited; RLS-routing semantics in linked spec
ERD 3.3 — `application_types` shape + 6 baseline rows	`04-workspace-types.md` §3.1 + §3.2	6 `core`-provenance rows; reserved-seat naming per §4.2
ERD 3.3 — 5 reserved-seat satellites with minimal shape	`docs/specs/reserved-workspace-seats/PRODUCT.md` S-1..S-8	Each seat carries PK + FK only; per-app columns deferred to feature spec
ERD 3.4 — `citations.citing_entity` polymorphic enum	`05-qa-flow.md` §6.1 — 5 values	All 5 enum values rendered; N8 RESOLVED-S234 cited
ERD 3.4 — `question_matches` separate scoring columns	`05-qa-flow.md` §7.3 — N9 RESOLVED-S236	Both columns rendered as `NUMERIC(5,4)`; blend function deferred to feature spec
Sequence 4.1 — cocoindex flow stages	`02-data-flow.md` §3.1 — 6 stages	All 6 stages rendered in order
Sequence 4.1 — Cloud Run sidecar topology	`02-data-flow.md` §4.1 + §4.2	Docling + pullmd + cocoindex engine; per-MIME branching per §4.2
Sequence 4.1 — op_id propagation	`02-data-flow.md` §5.1 + §5.3 — N7 hybrid	op_id on content_items + q_a_extractions + source_documents writes
Sequence 4.1 — edit_intent re-classification	`02-data-flow.md` §8.2 — cosmetic / data / structural	Memo hit on cosmetic; trigger re-run on data + structural
Sequence 4.2 — two-step retrieval	`05-qa-flow.md` §7.2 — S16 §6.1	list/preview + get/verbatim split
Sequence 4.2 — scope_tag overlap filter	`05-qa-flow.md` §1.1 — Q-OQR1-06	Read-time GIN-index overlap; no workspace_id FK consulted
Sequence 4.3 — UC5 promotion	`05-qa-flow.md` §9 — RATIFIED S229	KH-DB-only operation; lineage to source bid response
Sequence 4.3 — UC6 revise sub-variants	`05-qa-flow.md` §8.1 — user-direct + AI-suggest	Both sub-variants rendered; Candidate B as Anthropic API call
Sequence 4.3 — lost-bid default skip	`08-new-features.md` §7.2 — N10 RESOLVED-S234	Default skip; explicit override option rendered
Sequence 4.3 — UC8 entity_resolution scope	`08-new-features.md` §3.3 — selective adoption	Organisations / people / products in scope; free-text deferred to v2
Sequence 4.4 — ensure_rls event trigger	`docs/specs/rls-pattern/TECH.md` T-1	Tag filter (CREATE TABLE / CREATE TABLE AS / SELECT INTO); public schema filter
Sequence 4.4 — RAISE LOG observability	`docs/specs/rls-pattern/PRODUCT.md` P-5 — DEFERRED-v1.1	Option (c) ratified S240; audit_log + fan-out are v1.1 candidates

All entities and steps trace to a prose sub-doc anchor or schema source. No fabricated content detected at draft time.

8. Outstanding judgment calls

Per construction guide §2.1 §8 + §5.3 gap-flag format. The following are diagram-shape sub-decisions that were not pre-ratified; each is recorded as a judgement call so the cross-doc audit Wave 4 can review.

8.1 Polymorphic FK rendering (ERD 3.4)

Judgement call: Mermaid cannot render polymorphic FKs natively. The diagram annotates the citing_entity_id and form_question_id columns with “polymorphic FK per enum value” notes, and renders the dominant edge (to bid_responses / bid_questions) explicitly. The other 4 enum values are described inline.

Source-doc check: 05-qa-flow.md §6.1 (N8 polymorphic enum) + §7.2 (question_kind discriminator) describe the contract at the column level but do not dictate a specific ERD-rendering convention.

Category: None — this is a rendering choice, not a fabricated claim. Recorded for Wave 4 audit consideration: if a different polymorphic-FK ERD convention is preferred, the column annotations can be rewritten without changing the ratified schema.

8.2 RLS-routing-via-content-items edge on `source_documents.workspace_id` NULL (ERD 3.2)

Judgement call: The diagram marks source_documents.workspace_id as nullable and renders the FK to workspaces directly. The RLS-routing-via-content_items.source_document_id → content_item_workspaces.workspace_id path for the NULL case is described in the §3.2 prose alongside the ERD but not rendered as an edge — runtime RLS behaviour is not schema, and the linked docs/specs/rls-pattern/ spec is the authoritative source.

Source-doc check: 02-data-flow.md §2.2 + 04-workspace-types.md §9.1 describe the routing but do not specify ERD rendering.

Category: None — rendering choice. The semantics are correctly described in the prose alongside the ERD.

8.3 `application_types.client_extensible` + `application_types.active` column-level shape

Judgement call: The diagram renders client_extensible boolean and active boolean as columns on application_types. These are described at the pattern level in 04-workspace-types.md §3.1 (“rich config columns”) but the exact column-level shape (boolean vs enum vs JSONB metadata) is migration-draft scope per 04-workspace-types.md §3.1 (“Detailed columns are not enumerated in this sub-doc — the table is created in the Q-OQR1-16 combined-PR migration”).

Source-doc check: 04-workspace-types.md §3.1 — schema SQL belongs in the migration + the feature spec, not in this architecture record.

Category: investigation needed (low-priority). Suggested prerequisite work: confirm the exact column shape when the combined-PR migration draft lands. If application_types.client_extensible ships as a different shape (e.g. enum rather than boolean), update ERD 3.3 to match. Recorded for Wave 4 audit + post-migration verification.

8.4 q_a_search RPC vs application-tier scoring

Judgement call: Sequence 4.2 renders the scoring step inside the Postgres RPC (embedding_score = cosine(...) and fulltext_score = ts_rank(...) computed at the RPC layer). The choice between Postgres-tier scoring and application-tier scoring is feature-spec scope per 05-qa-flow.md §7.3 gap flag (deferred to docs/specs/procurement-question-matching/TECH.md).

Source-doc check: 05-qa-flow.md §7.2 (two-step retrieval) + §7.3 (separate scoring columns) describe the schema shape but not the tier at which scoring is computed.

Category: tech spec needed. Suggested prerequisite work: the procurement-question-matching TECH.md feature spec owns the per-method weight configuration, blend function, AND the tier choice (RPC vs application). The diagram renders the RPC-tier option for visual clarity; if the feature spec ratifies the application-tier choice, sequence 4.2 needs revision. Recorded for Wave 4 audit + procurement-build-cycle revision.

8.5 audit_log row writes not rendered in sequence 4.3

Judgement call: Sequence 4.3 (bid-feedback 3-UC) does not render the audit_log row writes that fire on every q_a_pairs INSERT/UPDATE. The omission keeps the bid-feedback composition legible; the audit_log behaviour is rendered in sequence 4.1 and applies identically here.

Source-doc check: 02-data-flow.md §5.1 — trigger-driven audit_log fires on every governed-table write regardless of call site.

Category: None — rendering choice for legibility. The behaviour is correctly described in the diagram’s closing note. Recorded for Wave 4 audit consideration: if explicit audit_log steps are preferred, they can be added to sequence 4.3 without changing the ratified pattern.

End of sub-doc. This closes the 9-way split of docs/plans/phase-0-investigation/0.9-intended-architecture.md. Wave 4 cross-doc audit (per docs/specs/core-docs-pathway-assessment/architecture-sub-doc-construction-guide.md §10.4) gates the predecessor archive to .planning/.archive/.specs/0.9-intended-architecture.md.

09 — Diagrams

09 — Diagrams

1. Mission

2. Diagram catalogue

3. Entity-relationship diagrams

3.1 ERD — content_items + q_a_pairs + q_a_extractions

3.2 ERD — source_documents + cocoindex source-key + content-hash

3.3 ERD — workspaces + application_types + per-application-type satellites

3.4 ERD — citations polymorphic + question_matches discriminator

4. Sequence diagrams

4.1 Sequence — Ingest flow with Cloud Run sidecar

4.2 Sequence — Q&A round-trip (search + verbatim fetch)

4.3 Sequence — Bid-feedback 3-UC composite (UC5 + UC6 + UC8)

4.4 Sequence — Auto-RLS event trigger on CREATE TABLE

5. Cross-doc references

5.1 Schemas anchored in 04-workspace-types.md

5.2 Flows anchored in 02-data-flow.md

5.3 Q&A round-trip anchored in 05-qa-flow.md

5.4 Bid-feedback 3-UC anchored in 08-new-features.md

5.5 Auto-RLS anchored in docs/specs/rls-pattern/

6. Anti-patterns to avoid

7. Source-doc supersession

7.1 Heritage docs feeding this sub-doc

7.2 Cross-sub-doc consistency verification

8. Outstanding judgment calls

8.1 Polymorphic FK rendering (ERD 3.4)

8.2 RLS-routing-via-content-items edge on source_documents.workspace_id NULL (ERD 3.2)

8.3 application_types.client_extensible + application_types.active column-level shape

8.4 q_a_search RPC vs application-tier scoring

8.5 audit_log row writes not rendered in sequence 4.3

3.1 ERD — `content_items` + `q_a_pairs` + `q_a_extractions`

3.2 ERD — `source_documents` + cocoindex source-key + content-hash

3.3 ERD — `workspaces` + `application_types` + per-application-type satellites

3.4 ERD — `citations` polymorphic + `question_matches` discriminator

4.4 Sequence — Auto-RLS event trigger on `CREATE TABLE`

5.1 Schemas anchored in `04-workspace-types.md`

5.2 Flows anchored in `02-data-flow.md`

5.3 Q&A round-trip anchored in `05-qa-flow.md`

5.4 Bid-feedback 3-UC anchored in `08-new-features.md`

5.5 Auto-RLS anchored in `docs/specs/rls-pattern/`

8.2 RLS-routing-via-content-items edge on `source_documents.workspace_id` NULL (ERD 3.2)

8.3 `application_types.client_extensible` + `application_types.active` column-level shape