Data model

Schema truth lives in the Alembic migrations under backend/alembic/versions/. This page describes what those tables hold and why. The HTTP surface is documented separately in the API reference.

users
 ├── refresh_tokens
 └── projects
      ├── sessions ──┬── agent_logs
      │              ├── chat_messages
      │              ├── audit_log
      │              └── memory_chunks
      ├── chat_threads ── chat_messages
      └── (corpus: a SQLite file per project, outside Postgres)

users

preferences is JSON rather than a column per setting, so a new knob is not a migration. NULL means "use the default" — the same convention model_routing uses.

sessions

One research run.

Indexes: user_id, project_id, status.

Two nullable columns carry the same rule, and it is the rule this codebase is built around: citation_resolution_rate and elapsed_seconds are NULL when unmeasured. 0.0 in the first would mean "every marker points at nothing", which is the opposite finding. Storing the two as one value is the bug the project exists to refuse.

agent_logs

The durable event stream, and the source the SSE endpoint replays from.

Events are written here first and published to Redis second. That ordering is what makes the live feed lossless.

audit_log

The compliance trail. One row per human decision.

draft_hash is what lets a bundle prove an approval applies to that report. For plan_approved it hashes the approved tasks and outline instead, so the design decision is carried in the same chain.

The row is written before the resume is queued, so the record of what a human chose survives a worker that dies mid-run.

Semantics

A bundle's approval_chain is derived entirely from these rows, and verify_bundle's approval_chain check is what decides whether an artifact is trustworthy — so a reader verifying a bundle is, in the end, trusting the rules below. They are pinned by backend/tests/security/test_audit_log_semantics.py, which fails if this section stops describing the code.

Writers — four, and no others. submit_plan (plan_approved) and approve_or_rework (approved / rework_requested), once on each host: app/api/v1/research.py for the server and desktop/sidecar.py for the desktop. The graph never writes one. A pipeline state change is not a decision, and a chain carrying entries no human authored would describe something other than what it claims to.

draft_hash holds two different hashed objects. At the report gate it is sha256(draft_report) — the link that ties an approval to the exact text approved, and the one the verifier checks. At the design gate the same column holds sha256(json.dumps({"tasks", "outline"}, sort_keys=True)), hashing a plan. No shipped tool verifies that second value. This overload is intentional for now: splitting the column is a schema change, and carrying both decisions in one ordered chain is what lets a bundle show the whole history. Anyone reading only the verifier would reasonably assume one meaning, which is why it is written down here.

Ordering is meaning. Both hosts read the chain ORDER BY id ASC, never by timestamp — a rework followed by an approval is a different history from the reverse, and two decisions inside the same second must not be free to swap.

Append-only in practice, not by constraint. Nothing in the codebase updates or deletes an audit_log row; rows cascade-delete with their session, which is the one legitimate removal, since the decision is about that session and does not outlive it. There is no database constraint enforcing append-only — the guarantee is the absence of a writer, and that absence is what the test checks.

Transactionality, and the gap in it. The row is committed in the same transaction as the session's status change, before the resume is dispatched. What is not handled: if the dispatch fails after that commit, the decision is recorded and the work never happens, and nothing reconciles the two. The bias is deliberate — a decision a human made is evidence and is recorded when made — but the reconciliation gap is real and undocumented elsewhere.

No writer catches exceptions around the audit write, so a failure to record a decision fails the request rather than silently proceeding without a record.

rework_count on sessions is a second, redundant record of the same events, used for the rework cap. It can disagree with the chain and nothing checks that it doesn't.

projects

UNIQUE (user_id, lower(name)) — "Thesis" and "thesis" as separate projects is a UI trap, not a feature.

chat_threads and chat_messages

CHECK ((session_id IS NOT NULL) <> (thread_id IS NOT NULL)) — a message belongs to a report or a thread, never both and never neither. Two nullable parents would permit a row that appears in no history at all, whose first symptom is a user's question vanishing.

memory_chunks

Embedded slices of approved reports. Requires pgvector.

Indexes: project_id, source_session_id, UNIQUE (source_session_id, chunk_index), and HNSW on embedding with vector_cosine_ops — matching the operator retrieval uses.

Rows are written from exactly one place: the COMPLETED transition, reachable only through the human approval gate. The unique index makes re-ingestion idempotent rather than duplicating the corpus.

Postgres must be a pgvector image, not stock. Migration 0006 enables the extension and 0007 creates the vector column, so a stock image fails alembic upgrade head outright.

refresh_tokens

Corpus storage

Uploaded documents do not live in Postgres. Each project gets a SQLite file under the configured CORPUS_DIR, holding the document bytes, extracted text spans, and their embeddings.

That keeps the corpus portable and makes the desktop build's story identical: from 3.1 the desktop also keeps one file per project, corpus/corpus_<project_id>.sqlite in its data folder. Releases before 3.1 kept one corpus.sqlite for the whole app; the first 3.1 launch moves it, whole, into a project of its own (desktop/corpus_layout.py), keeping a backup. Which layout is in force is the corpus_layout row of the desktop-only app_meta table.

Inside a store, every schema change goes through CorpusStore._migrate, which only adds: existing rows are never rewritten, so a 3.0.x app can still open a 3.1 store and read the documents it knew (G-3).

A selected-files run never reads this live. research_runs.corpus_selection (migration 0028) freezes the request and the exact document versions it resolved to, so a later upload, rename or delete cannot change what a finished run was allowed to read. research_runs.offline (0029) records the Offline guarantee the run asked for, and research_runs.grounding_report (0030) records its claim-by-claim check (RFC V3.1 §9.6). All three are nullable: runs created before them read their project's whole corpus, and say so.

CORPUS_DIR defaults to the relative path data/corpus, resolved against the backend package root rather than the process working directory — otherwise running from the repository root and from backend/ would create two different corpus roots, and a document uploaded through one would simply not be there for the other.

LangGraph checkpoints

Owned by langgraph-checkpoint-postgres, keyed by thread_id (the session id), in tables this application does not define and never hand-edits.

They are dropped explicitly when a session is deleted. Cascades cannot reach them, and without that step "delete" would leave the full agent state — including fetched page content — behind.

Migration policy

• Alembic is the only schema writer. No create_all in application code. It once masked an empty migration, and the whole class of "works locally, missing in production" follows from that.
• Autogenerate runs against a scratch database built from alembic upgrade head only.
• compare_type and compare_server_default are on; every model module is imported in env.py.
• Every migration has a real downgrade() unless it is named in one of two registries in tests/task/test_migration_downgrade_policy.py, each entry with its reason: IRREVERSIBLE_BY_DESIGN (the downgrade declines on every database — dropping the vector extension would take every vector column with it; PostgreSQL cannot drop an enum value) and DATA_DEPENDENT_DOWNGRADE (the downgrade is real and correctly refuses a database holding rows the older schema cannot express — both are polymorphic foreign keys whose downgrade reinstates a key to sessions, which a row belonging to a run cannot satisfy). Anything outside both is held to the rule.
• The round-trip is exercised against a populated database, in its own scratch database, by tests/workflow/test_migration_round_trip.py — both directions, and both refusals.
• Migrations are append-only once merged. Fixing a merged migration means writing a new one — with one exception this project has actually used: a downgrade() that has never run anywhere, and cannot have, may be corrected in place. 0008_chat_threads dropped a constraint whose name the naming convention had already rendered, so no database had ever held it and every downgrade past that revision died there.

The desktop build reads the ORM models directly via create_all plus a startup column sync, so a schema change needs both a migration and the ORM model updated — they are two homes for one contract.