data-model.md

Data model

Fourteen tables in PostgreSQL, plus the vector and pg_trgm extensions. The full definitions are in services/contextforge/core/models.py and the migrations in infra/alembic/versions/; this document is about why the schema is shaped this way and which parts of it are load-bearing.

The shape

users ─┬─ reading_preferences   (1:1, the accessibility settings that follow a reader)
       │
       ├─ documents ─┬─ document_chunks   (the retrieval unit: text + vector + offsets)
       │             ├─ document_assets   (tables and figures pulled out during parsing)
       │             └─ jobs              (one row per ingestion attempt)
       │
       ├─ conversations ── messages ── citations ──► document_chunks
       │
       ├─ summaries        (cached, per document/page/section/reading level)
       └─ quizzes ── quiz_questions

evaluation_runs ── evaluation_results     (benchmark history, no user)

documents

The lifecycle row. status moves uploading → queued → parsing → chunking → embedding → indexing → ready, or to failed with error_code and error_message that are safe to show a user.

It also records how it was processed: parser, parser_version, chunker, embedding_model, embedding_dim, used_ocr. Without those, a corpus is a set of vectors whose provenance is unknown, and "re-embed everything produced by the old model" is not a query anyone can write. With them it is one WHERE clause.

checksum_sha256 is indexed, which is how re-uploading the same bytes is detected and reported as a duplicate instead of ingested twice.

Two composite indexes cover the two ways documents are actually listed: (user_id, created_at) for the library and (user_id, status) for "what is still processing". A trigram index on title backs fuzzy title search.

document_chunks

The most important table in the system, and the one whose columns do the most work.

Column	Why it exists
content	the passage text, verbatim
embedding vector(384)	the searchable representation
embedding_model	which space this vector lives in
char_start, char_end	offsets into the document's canonical text
page_start, page_end	what a citation shows a human
section_path text[]	the heading trail, for scoping and for legible citations
chunk_type	prose, table, code, formula — atomic types are never split
token_count	context budgeting without re-tokenising
content_hash	detect unchanged chunks across a re-chunk
content_tsv	generated tsvector, heading weighted above body

char_start/char_end are an invariant, not a hint. For every chunk, content == document_text[char_start:char_end] holds exactly. That is what lets a citation report document coordinates and lets the reader highlight by slicing. Chunk overlap is implemented by moving a chunk's start offset backwards rather than by prepending a copy of the previous chunk's text, precisely to keep this true.

embedding_model is a correctness column, not metadata. Vectors from two models share a column but not a space, and comparing them produces confident nonsense rather than an error. Retrieval filters on the active model, so a half-re-embedded corpus returns fewer results rather than wrong ones.

Indexes:

• ix_chunks_embedding_hnsw — HNSW, vector_cosine_ops, m=16, ef_construction=64. Which index is installed is an operational choice, changed with contextforge db index rather than a migration; see ADR-002.
• ix_chunks_content_tsv — GIN over the generated column, for the keyword arm.
• ix_chunks_document_pages — page-scoped fetches, for the reader.
• uq_chunk_document_index — one row per (document_id, chunk_index). This is the constraint that makes re-ingestion safe to attempt rather than merely intended to be.

conversations, messages, citations

conversations.document_ids is a UUID[] with a GIN index, holding the true scope of the conversation: one document, several, or empty for the whole library. It is queried with the containment operator @>, not with = ANY(...), because only containment can use the GIN index — the difference is an index scan versus a sequential scan of every conversation in the table, and there is a migration test that reads the query plan to keep it that way.

conversations.document_id still exists alongside it as a denormalised single-document reference, with ON DELETE SET NULL. Deleting one document out of a five-document conversation should leave the conversation readable with that source gone — not cascade the whole thing away, which is what the original ON DELETE CASCADE did.

messages.turn_index is unique per conversation. Ordering by timestamp looks fine until two messages land in the same millisecond and a conversation renders with the answer above the question.

citations carries the marker, the chunk it points at, and quote_char_start / quote_char_end in document coordinates. Storing chunk-relative offsets would have been simpler and would break the moment two adjacent chunks are merged into one retrieved block.

jobs

One row per ingestion attempt: type, state, attempts, progress, stage, error, timings. Progress is written in its own short transaction so that polling sees movement while the long ingestion transaction is still open.

This is what makes a failure legible after the fact. "The upload didn't work" becomes "the third attempt failed at the parsing stage with DOCUMENT_ENCRYPTED after 1.2 seconds".

summaries and quizzes

Cached generation output, keyed by scope (document, page or section) and reading level, so the same request does not pay for the same model call twice. Summaries carry their citations as JSONB rather than rows: they are written and read as one unit and never queried by citation.

evaluation_runs and evaluation_results

Benchmark history in the database, with the git commit, config and environment that produced it. They belong to no user. The harness writes JSON to evals/results/ for the committed record; these tables are for querying across runs — "when did nDCG@5 drop" is a question about a series, not a file.

Delete behaviour

Relationship	On delete	Why
user → documents, preferences	CASCADE	deleting an account must remove the data
document → chunks, assets, jobs	CASCADE	they are meaningless without it
conversation → messages → citations	CASCADE	ditto
citation → chunk	CASCADE	a citation to a deleted chunk cannot be rendered
conversation → document	SET NULL	the conversation outlives one of its sources

Deleting a document also removes its objects from storage. A database row and a 40 MB blob that outlives it is a bill nobody notices.

Migrations

Five revisions. Each one is a decision that was wrong before and is right now:

Rev	What it did
0001	extensions, all core tables, the generated content_tsv, the GIN and HNSW indexes
0002	citations.quote_char_start / quote_char_end — citations gained exact offsets
0003	conversations.document_ids with its GIN index, backfilled; summaries.citations
0004	NOT NULL on JSONB and array columns that models already treated as required
0005	messages.turn_index with a unique constraint; conversations.document_id → SET NULL

Two properties are tested rather than assumed: upgrading from empty reaches head, and alembic check reports no drift between the models and the migrations. The second is the one that fails the moment somebody adds a field to a model and forgets the revision — a disagreement that is invisible to any test that builds its schema with create_all, which is why the test suite migrates instead. Downgrade to base is also exercised, because a rollback is what turns a bad deployment into a five-minute incident rather than a restore from backup.