Skip to content

05. Data Model & Storage

Persistence map

Store Role
PostgreSQL Documents, jobs, stages, chunks, artifacts, audit events, factsheets
MinIO (S3 API) Raw PDF objects (documents/{uuid}.pdf style keys)
Redis Celery broker/result backend only
Qdrant / Meilisearch Optional compose services; no app write/read paths

Schema is created at API startup with Base.metadata.create_all (main.py). Alembic is a dependency but no migration package is present.

Entity-relationship diagram

%%{init: {
  "theme": "base",
  "themeVariables": {
    "primaryColor": "#1e293b",
    "primaryTextColor": "#cad6eaff",
    "primaryBorderColor": "#38bdf8",
    "lineColor": "#64748b",
    "attributeBackgroundColor": "#1e293b",
    "attributeTextColor": "#f8fafc"
  },
  "themeCSS": ".er.attributeBoxOdd { fill: #1e293b !important; } .er.attributeBoxEven { fill: #0f172a !important; } .row-rect-odd { fill: #1e293b !important; } .row-rect-even { fill: #0f172a !important; } .row-rect-odd path { fill: #1e293b !important; } .row-rect-even path { fill: #0f172a !important; }"
}}%%
erDiagram
  Document ||--o{ DocumentJob : has
  Document ||--o{ Artifact : has
  DocumentJob ||--o{ JobStage : has
  DocumentJob ||--o{ JobChunk : has
  DocumentJob ||--o{ Artifact : produces
  Document {
    int id PK
    string filename
    string file_path
    string status
    int page_count
  }
  DocumentJob {
    int id PK
    int document_id FK
    string status
    string current_stage
  }
  JobStage {
    int id PK
    int job_id FK
    string stage_name
    string status
    int attempt_count
  }
  JobChunk {
    int id PK
    int job_id FK
    string stage_name
    int chunk_index
    string status
  }
  Artifact {
    int id PK
    int document_id FK
    int job_id FK
    string stage_name
    string artifact_type
    json content
    string s3_path
  }
  Factsheet {
    int id PK
    string task_id
    string status
    json doc_ids
    json prefilled
    json result
  }
  AuditEvent {
    int id PK
    string action
    string actor
    string target_type
  }

Factsheet references documents by JSON doc_ids (not a FK table). AuditEvent exists but log_audit_event is not called from routers (Confirmed).

Status enums

Enum Values
DocumentStatus UPLOADED, PROCESSING, COMPLETED, FAILED, PARTIAL
JobStatus PENDING, RUNNING, PARTIAL, FAILED, COMPLETED
StageName OCR, EXTRACTION, MERGE
ChunkKind PAGE_RANGE, TEXT_SPAN
ChunkStatus PENDING, RUNNING, COMPLETED, FAILED

Artifact lifecycle

graph TD
  classDef default fill:#1e293b,stroke:#38bdf8,stroke-width:2px,color:#f8fafc
  classDef highlight fill:#065f46,stroke:#34d399,stroke-width:2px,color:#f0fdf4

  PDF["PDF in MinIO"]:::highlight
  OCR["OCR chunk artifacts<br/>markdown"]
  EXT["Extraction chunk artifacts<br/>JSON"]
  MER["Merged extraction artifact"]
  FS["Factsheet.result JSON"]:::highlight

  PDF --> OCR --> EXT --> MER --> FS

  linkStyle default stroke:#64748b,stroke-width:2px

Artifacts may store inline content JSON and optional s3_path. Factsheet output is stored on the Factsheet row, not as a separate artifact type.

Consistency and idempotency

  • Unique constraints on (job_id, stage_name) and (job_id, stage_name, chunk_index) prevent duplicate stage/chunk rows
  • Re-run endpoints intentionally re-process stages; callers must accept overwrite semantics
  • Document checksum column exists; upload idempotency by checksum is not a hard API guarantee from inspection alone (Unknown without deeper ingestion service read)

Data-model interview Q&A

Q: Why model chunks in the database?
A: So partial OCR/extraction failures are visible, retryable, and resumable without redoing completed page ranges.

Q: Where does search fit?
A: Settings and compose-full provision Meilisearch/Qdrant for UC3, but this checkout has no index writers or query APIs yet.