Database schema

dispatch_ai

PostgreSQL

Dispatch AI persistence: conversations and the retrieval index.

erDiagram
  conversations {
    varchar id PK
    varchar shipment_id
    conversation_status status
    int token_budget
    int tokens_used
    timestamptz opened_at
    timestamptz resolved_at
  }
  eval_runs {
    bigserial id PK
    varchar prompt_key FK
    int version
    varchar suite
    real score
    timestamptz ran_at
  }
  knowledge_chunks {
    bigserial id PK
    varchar source_id FK
    int seq
    text content
    vector_1024 embedding
  }
  knowledge_sources {
    varchar id PK
    varchar kind
    text uri
    source_status status
    timestamptz ingested_at
  }
  messages {
    bigserial id PK
    varchar conversation_id FK
    message_role role
    text content
    int tokens
    timestamptz sent_at
  }
  prompt_versions {
    bigserial id PK
    varchar prompt_key FK
    int version
    text body
    timestamptz created_at
  }
  prompts {
    varchar key PK
    int active_version
  }
  suggestions {
    bigserial id PK
    bigint message_id FK
    suggestion_kind kind
    jsonb payload
    real confidence
    bool accepted
    timestamptz created_at
  }
  prompts ||--o{ eval_runs : "prompt_key"
  knowledge_sources ||--o{ knowledge_chunks : "source_id"
  conversations ||--o{ messages : "conversation_id"
  prompts ||--o{ prompt_versions : "prompt_key"
  messages ||--o{ suggestions : "message_id"

Project dispatch_ai PostgreSQL

Dispatch AI persistence: conversations and the retrieval index. Lives in the platform Postgres cluster (see platform ADR-003); the embeddings column needs the pgvector extension (ADR-004 here).

conversations A chat session. Escalated conversations keep history but reject new assistant turns.
messages One turn. Tool turns store the retrieval trace as JSON in content.
suggestions Every suggestion the assistant made, kept for the eval harness (ADR-008).
knowledge_sources A document registered for retrieval.
knowledge_chunks The retrieval unit. Re-chunking a source replaces its rows wholesale.
prompts The prompt registry (ADR-007): key + pointer to the active version.
prompt_versions
eval_runs Nightly eval results per prompt version (ADR-008).
conversation_status enum open | resolved | escalated
message_role enum user | assistant | tool
suggestion_kind enum reroute | reply_draft
source_status enum pending | chunked | embedded | failed
conversation_state group conversations, messages, suggestions
retrieval_index group knowledge_sources, knowledge_chunks
prompt_registry group prompts, prompt_versions, eval_runs