Data Model Reference

This page covers the full relational schema, the Neo4j property graph, the Weaviate vector schema, and the trend path tables. Use the entity-relationship diagrams as the authoritative reference for table structure and foreign-key relationships.

---

1. Core Pipeline — PostgreSQL Schema

The six tables below form the backbone of the ingestion and governance pipeline.

erDiagram
    source_artifacts {
        string artifact_id PK
        string source_system
        string source_type
        string source_ref UK
        datetime received_at
        string legal_basis
        string customer_scope
        string processing_run_id
    }

    transcript_segments {
        string segment_id PK
        string artifact_id FK
        int sequence
        text text
        string start_offset
        string end_offset
        string speaker
        string privacy_class
    }

    candidate_questions {
        string candidate_id PK
        text question_text
        float confidence
        string flow_type
        string source_segment_id
        string artifact_id FK
        string processing_run_id
    }

    candidate_answers {
        string candidate_id PK
        text answer_text
        string source_segment_id
        string question_candidate_id FK
        string artifact_id FK
        float confidence
        string processing_run_id
    }

    classification_records {
        string record_id PK
        string candidate_id FK
        text answer_text
        string primary_horn_type
        float primary_confidence
        json alternative_types
        string assertion_subtype
        json assertion_grammar
        boolean requires_review
        string model_version
        string processing_run_id
        datetime classified_at
    }

    approved_answers {
        string answer_id PK
        string candidate_id
        string horn_type
        string assertion_subtype
        text answer_text
        json assertion_grammar
        string status
        int version
        string owner_role
        string validation_record_id
        datetime created_at
        date review_due
        string supersedes_id
        string customer_scope
        json conflict_record_ids
    }

    source_artifacts ||--o{ transcript_segments : "artifact_id"
    source_artifacts ||--o{ candidate_questions : "artifact_id"
    source_artifacts ||--o{ candidate_answers : "artifact_id"
    candidate_questions ||--o{ candidate_answers : "question_candidate_id"
    candidate_questions ||--o{ classification_records : "candidate_id"

---

2. Validation and Registry Tables

erDiagram
    validation_assignments {
        string assignment_id PK
        string candidate_id
        string horn_type
        string current_state
        string assigned_to
        datetime created_at
        datetime updated_at
        json record_ids
        json conflict_flag_ids
    }

    validation_records {
        string record_id PK
        string assignment_id FK
        string candidate_id
        string decision
        string actor
        datetime timestamp
        text rationale
        text revised_text
        string prior_record_id
        json conflict_flags
    }

    conflict_flags {
        string flag_id PK
        string assignment_id_a FK
        string assignment_id_b
        string conflict_type
        text description
        datetime detected_at
    }

    evidence_links {
        string link_id PK
        string answer_id FK
        text source_ref
        datetime created_at
    }

    version_chains {
        string chain_id PK
        string answer_id FK
        string supersedes_id
        datetime created_at
    }

    registry_conflict_records {
        string conflict_id PK
        string answer_id_a FK
        string answer_id_b
        string conflict_type
        text description
        datetime detected_at
    }

    validation_assignments ||--o{ validation_records : "assignment_id"
    validation_assignments ||--o{ conflict_flags : "assignment_id_a"
    approved_answers ||--o{ evidence_links : "answer_id"
    approved_answers ||--o{ version_chains : "answer_id"
    approved_answers ||--o{ registry_conflict_records : "answer_id_a"

---

3. Generation Tables (Flow B)

erDiagram
    work_items {
        string work_item_id PK
        string candidate_id FK
        text question_text
        string source_segment_id
        string processing_run_id
        string state
        string jira_issue_key
        datetime created_at
        datetime updated_at
        text escalation_reason
        string generated_candidate_id
    }

    generated_candidates {
        string generated_id PK
        string work_item_id FK
        string question_candidate_id
        string answer_candidate_id
        text generated_text
        json citations
        string model_version
        int prompt_tokens
        int completion_tokens
        datetime generated_at
        string origin
    }

    candidate_questions ||--o{ work_items : "candidate_id"
    work_items ||--o{ generated_candidates : "work_item_id"

---

4. Trend Path Tables

The eight trend path tables extend the core schema. They are populated during intake and are query-ready immediately — no validation or registry step is required.

erDiagram
    transcript_lines {
        string line_id PK
        string artifact_id FK
        string segment_id FK
        int sequence
        int source_index
        string start_offset
        string end_offset
        string speaker
        text text
    }

    raw_mentions {
        string mention_id PK
        string line_id FK
        string artifact_id FK
        string segment_id
        text mention_text
        text normalized_text
        int start_char
        int end_char
        string mention_type
        string privacy_class
        string extractor_version
        string processing_run_id
    }

    line_sentiments {
        string line_id PK_FK
        float compound
        float pos
        float neu
        float neg
        string sentiment_label
        string scorer_version
        string processing_run_id
    }

    question_mention_links {
        string link_id PK
        string question_candidate_id FK
        string mention_id FK
        string link_basis
        int distance_lines
        string processing_run_id
    }

    canonical_entities {
        string canonical_entity_id PK
        text label
        text normalized_label
        string status
        string created_by
        datetime created_at
        string resolution_mode
    }

    mention_resolutions {
        string resolution_id PK
        string mention_id FK
        string canonical_entity_id FK
        float match_score
        string resolution_mode
        string review_state
        string actor
        datetime resolved_at
    }

    mention_clusters {
        string cluster_id PK
        text cluster_key
        int frequency
        datetime first_seen_at
        datetime last_seen_at
        string status
        string suggested_entity_id
    }

    canonical_entity_taxonomies {
        string assignment_id PK
        string canonical_entity_id FK
        string taxonomy_type
        string taxonomy_value
        string assigned_by
        datetime assigned_at
    }

    transcript_segments ||--o{ transcript_lines : "segment_id"
    source_artifacts ||--o{ transcript_lines : "artifact_id"
    transcript_lines ||--o{ raw_mentions : "line_id"
    transcript_lines ||--|| line_sentiments : "line_id"
    candidate_questions ||--o{ question_mention_links : "question_candidate_id"
    raw_mentions ||--o{ question_mention_links : "mention_id"
    raw_mentions ||--o{ mention_resolutions : "mention_id"
    canonical_entities ||--o{ mention_resolutions : "canonical_entity_id"
    canonical_entities ||--o{ canonical_entity_taxonomies : "canonical_entity_id"

---

5. Neo4j Property Graph

The graph is a derived projection updated by the raw_to_knowledge_graph Celery worker. It supports contradiction traversal, supersession chains, and trend analytics via Cypher.

graph LR
    subgraph knowledge_grp["Knowledge Graph (Milestone 7)"]
        ANS[Answer\nanswer_id\nhorn_type · status\nsubject · predicate\nobject_value · scope]
        QST[Question\nquestion_id\nquestion_text]
        EVI[Evidence\nevidence_id\nsource_ref]
        CON[Concept\nlabel]
    end

    subgraph trend_grp["Trend Graph (Trend Path)"]
        ART[Artifact\nartifact_id]
        SEG2[TranscriptSegment\nsegment_id]
        LNE[TranscriptLine\nline_id · text\nspeaker · sequence]
        MNT[RawMention\nmention_id\nnormalized_text\nmention_type]
        ENT[CanonicalEntity\ncanonical_entity_id\nlabel · status]
        TAX[TaxonomyTerm\ntype · value]
        SPK[Speaker\nname]
        CSC[CustomerScope\nname]
        TBK[TimeBucket\nweek: YYYY-Www]
    end

    QST -->|ANSWERED_BY| ANS
    ANS -->|SUPPORTED_BY| EVI
    ANS -->|ABOUT| CON
    ANS -->|SUPERSEDES| ANS
    ANS ---|CONFLICTS_WITH| ANS

    ART -->|IN_SCOPE| CSC
    SEG2 -->|HAS_LINE| LNE
    LNE -->|HAS_MENTION| MNT
    MNT -->|RESOLVES_TO| ENT
    ENT -->|IN_TAXONOMY| TAX
    LNE -->|SPOKEN_BY| SPK
    LNE -->|IN_WEEK| TBK
    QST -->|MENTIONS| ENT

Node properties:

Node	Key properties
Answer	answer_id, horn_type, subject, predicate, object_value, scope, status, version
Question	question_id, question_text, source_session_id
Evidence	evidence_id, source_ref, last_verified
Concept	label (unique key)
TranscriptLine	line_id, artifact_id, segment_id, sequence, speaker, text
RawMention	mention_id, normalized_text, mention_type
CanonicalEntity	canonical_entity_id, label, normalized_label, status
TaxonomyTerm	type + value (composite key)
Speaker	name
CustomerScope	name
TimeBucket	week (ISO 8601 week string, e.g. 2026-W23)

---

6. Migration Sequence

Alembic migrations are applied in order. Each revision builds on the previous.

graph LR
    M0([None]) --> M1["001\ninitial_schema\nsource_artifacts\ntranscript_segments\ncandidate_questions\ncandidate_answers"]
    M1 --> M2["002\nclassification_schema\nclassification_records"]
    M2 --> M3["003\nvalidation_schema\nvalidation_assignments\nvalidation_records\nconflict_flags"]
    M3 --> M4["004\nregistry_schema\napproved_answers\nevidence_links\nversion_chains\nregistry_conflict_records"]
    M4 --> M5["005\ngeneration_schema\nwork_items\ngenerated_candidates"]
    M5 --> M6["006\ntrend_schema\ntranscript_lines\nraw_mentions\nline_sentiments\nquestion_mention_links\ncanonical_entities\nmention_resolutions\nmention_clusters\ncanonical_entity_taxonomies"]

---

7. Weaviate Schema

Weaviate holds vector embeddings of approved answers for semantic retrieval and known-answer detection.

Collection: ApprovedAnswer
  Properties:
    answer_id     (text, tokenize: field)
    answer_text   (text, tokenize: word)    ← vectorized
    horn_type     (text, tokenize: field)
    status        (text, tokenize: field)
    customer_scope (text, tokenize: field)
    version       (int)
  Vectorizer: text2vec-openai (or text2vec-transformers)
  Distance metric: cosine
  Known-answer threshold: ≥ 0.92

Weaviate is write-only from the raw_to_knowledge_graph worker; the API reads it for search queries. It is never the source of truth — always verify against PostgreSQL approved_answers.