Skip to content

Database

Jaloliddin Ismailov edited this page Nov 8, 2026 · 5 revisions

This page is about where Paper Trail keeps its data: the tables, how they connect, how the schema changes over time, and the database logins that make human review more than a promise. It's mostly reference for developers and operators, but there's enough explanation along the way that a newcomer can follow why things are built like this.

One database for everything

Everything lives in PostgreSQL 16 with the pgvector extension. That covers the public record, the deployment's configuration data, the cache of fetched pages and their embeddings, and the job queue. There's no second store, no file database and no separate vector service. Development and tests just use a throwaway pgvector container (Getting-Started).

The code talks to it through SQLAlchemy Core, split by job. repositories/store.py handles the records, repositories/index.py handles pages, passages and the hybrid search, and jobs/postgres.py runs the queue. Rows get turned into domain models in exactly one place, repositories/mapping.py.

The main tables

Here's how the important tables connect:

erDiagram
    documents ||--o{ candidates : "extracted from"
    candidates ||--o| commitments : "confirmed as"
    commitments ||--o{ commitments : "parent of"
    commitments ||--o{ evidence : "searched for"
    commitments ||--o{ links : "has"
    evidence ||--o{ links : "suggested in"
    evidence ||--o{ budgets : "mentions money"
    evidence ||--o{ artifact_evidence : "names"
    implementation_artifacts ||--o{ artifact_evidence : "named by"
    pages ||--o{ chunks : "cut into"

    documents {
        bigint id PK
        text title
        text publisher
        text url
    }
    candidates {
        bigint id PK
        bigint document_id FK
        text suggested_type
        text normalized_title
        int source_page
        text source_excerpt "the receipt"
        bool excerpt_on_page
        text review_status
    }
    commitments {
        bigint id PK
        bigint candidate_id FK
        bigint parent_id FK
        text kind
        text title
        text asset_owner_jurisdiction
    }
    evidence {
        bigint id PK
        bigint commitment_id FK
        text url
        text snippet "the passage"
        text status_hint
        text polarity
        text flags
    }
    links {
        bigint id PK
        bigint commitment_id FK
        bigint evidence_id FK
        float score
        text suggested_relationship
        text relationship
        text review_status
    }
    budgets {
        bigint id PK
        bigint evidence_id FK
        text kind "budget state"
        text currency
        text amount_raw
        int fiscal_year
    }
    implementation_artifacts {
        bigint id PK
        text procurement_id
        text status
    }
    artifact_evidence {
        bigint artifact_id FK
        bigint evidence_id FK
    }
    pages {
        bigint id PK
        text url
        text text
    }
    chunks {
        bigint id PK
        bigint page_id FK
        text embed_model
        vector embedding "1024 dims"
    }
Loading

The tables fall into three groups, and the grouping tells you who's allowed to touch them.

The public record

The pipeline writes these, and people decide on them.

Table What it holds
documents ingested strategy documents: title, publisher, official URL
candidates the LLM's proposals, each with its page, verbatim quote (source_excerpt), excerpt_on_page and review_status
commitments confirmed candidates, forming a tree through parent_id, each with an asset_owner_jurisdiction
evidence matched passages from official sources: URL, passage, organisations, places, dates, status hint, polarity, flags, procurement ids and parcels. One row per commitment and URL
links suggested commitment-evidence matches: score, the features behind it, reasons, and the suggested and final relationship. review_status starts as unreviewed
budgets money quoted in a passage: the raw wording, amount, currency, budget state and fiscal year
implementation_artifacts contracts and funded projects keyed by their official id, with a status that only ever escalates
artifact_evidence which passages name which artifact (many to many)
notes optional plain-English notes and AI suggestions per candidate or link. Only the owner login writes them (maintenance notes), and they never touch the review columns

Deployment data

Operators manage these, and they never go in the repository.

Table What it holds
official_sources the source registry: URLs that always get searched, the allowlisted hosts, and jurisdiction routing for asset owners' own sites
places the gazetteer: OpenStreetMap streets, squares and parks, plus parcel numbers found in documents (confirmed stays false until a person checks)
language_profiles one JSON document per language code with word lists, patterns and the glossary (Concepts)

System tables and caches

These are safe to rebuild.

Table What it holds
jobs the durable queue: kind, payload, status, progress, result, leases, heartbeats, attempts and next try time
pages fetched page text by URL, fetched once
chunks passage text, its embedding (vector(1024), HNSW index), lemma text and full-text index, keyed per embedding model
readiness_probe written and rolled back by GET /readyz to prove the API can still write

Migrations

The schema is a series of numbered SQL files in db/sql/, applied in order by Alembic (db/alembic.ini, with revisions in db/alembic/versions/). Each revision runs one or more of the SQL files:

Revision SQL Adds
0001_local_baseline 001-003, 005-008 core tables, pgvector, jobs, source registry, hybrid search, "reviewed rows are final"
0002_worker_leases 009 leases, uniqueness, worker grants and triggers
0003_accountability 010 polarity, artifacts, five budget states, jurisdiction, gazetteer
0004_language_profiles 011 language profiles in the database
0005_notes 012 plain-English notes
0006_reviewed_budgets 013 workers can't add money to reviewed evidence
DATABASE_URL=postgresql://paper_trail:<password>@<host>/paper_trail \
  uv run alembic -c db/alembic.ini upgrade head

Three rules keep this sane. First, migrations always run with the owner login, never the API or worker login, and Alembic only reads DATABASE_URL from the environment. Second, a released SQL file is never edited, so any change is a new numbered file plus a new revision. Third, downgrades are refused on purpose. Every downgrade() raises an error telling you to restore a tested backup instead, because rolling a migration back could weaken the review boundaries or throw away audit evidence.

Logins and the review wall

The database has three logins, and each one can do a different set of things.

Login Used by Can Can't
owner (paper_trail) migrations, maintenance commands, scripts everything (never handed to the running services)
paper_trail_api the API + workspace process read everything, record review decisions, set jurisdiction (every change goes through the review-key check first) write notes or language profiles, which it can only read
paper_trail_worker the job worker insert documents, candidates, evidence, links, budgets, artifacts and unconfirmed places; update caches and jobs accept candidates or links, create commitments, change jurisdiction, confirm places, or rewrite reviewed evidence or links, or add money to reviewed evidence

The worker's limits are enforced in three layers, so there's no single place where a bug could quietly break them. The first layer is column-level grants. The worker can insert a candidate but not set its review_status, and it can insert a link but not its review_status or relationship. It has no insert right on commitments at all. The second layer is triggers: if the worker tries to update a link that's been reviewed, or evidence that has a reviewed link, or to add a money figure to that evidence, the database raises an error. The money trigger takes the same per-evidence lock as a review decision, so an insert racing a click waits for it and then sees it. The third is a check at startup. python -m paper_trail.worker refuses to run unless it's logged in as paper_trail_worker with no review capabilities and with PROCESS_ROLE=worker.

The paper_trail_api and paper_trail_worker roles are created by deploy/zakazserver/db/init-roles.sh when the database volume is first created. The migrations only grant them rights if the roles exist, so a development database without them works fine.

Checking the database layer

There are two levels of checks, and both only ever run against throwaway databases. The everyday one is the test suite (make test), which needs TEST_DATABASE_URL pointing at a disposable pgvector server with an owner login. It migrates one template database per run and clones a fresh copy for every test (Contributing).

The heavier one is an integration check that exercises the production rules end to end:

TEST_DATABASE_URL=postgresql://paper_trail:<pw>@localhost:55432/paper_trail \
  uv run python scripts/ops/check_postgres.py

It runs the real migrations and then checks role denials, reviewed-row protection, idempotent writes, vector replacement and the HNSW plan, concurrent job claims and dedupe, stale lease recovery, lease fencing, timeout retry, artifact escalation, gazetteer permissions and a real maintenance reset, among other things.

Warning

check_postgres.py creates test roles with a publicly known password. Never point it at a real database.

To run the migrations in production, see Deployment. Every setting and command is listed in Reference.

Clone this wiki locally