Connect a published SQLite extract

Use a SQLite extract when the authority can publish an immutable snapshot but cannot offer a bounded live API. Evidence Gateway runs one reviewed SQL statement against the mounted file. It does not connect to the authority’s database, hold a source credential, or create the extract.

This guide starts with synthetic data. By the end, you will have proved the complete source, statement, extraction, derivation, output, and privacy path before binding a real extract.

Create and prove the starter

Create an editable project without an OpenAPI document:

evidencectl init registry-status \
  --transport sqlite-extract \
  --profile local

Run its synthetic fixture through the real Evidence Gateway evaluator:

evidencectl test registry-status --explain

The starter passes 13 cases. They cover true and false answers, no match, ambiguity, the row bound, extract age, source failure, parameter binding, statement refusal, hostile selector text, the output gate, and anti-reconstruction. --explain reports the stage each case reached without printing selector or source values.

Read the editable path

These are the files that make one source work, grouped by responsibility. The project holds more than this: a .gitignore that keeps secrets/ out of version control, a project marker file, disposable local key material under secrets/, and the editor schema mappings your YAML tooling reads.

registry-status/
  README.md
  selectors/record-reference-v1.yaml
  sources/record-status.yaml
  queries/record-status.sql
  adapters/record-status-extract.rhai
  schemas/record-status-response.schema.yaml
  schemas/record-status-facts.schema.yaml
  questions/record-status.yaml
  derivations/record-status.rhai
  fixtures/record-status.yaml

README.md names each of these files and the command that comes next, and evidencectl init prints its path with the rest of the project.

Start with sources/record-status.yaml. Its extractProfile is the logical name an operator will bind to a file. Its request names one fixed statement, its authorized selector input, the SQL parameter binding, the expected columns, and the row, cell, execution, timeout, response, and concurrency bounds.

The statement receives record_reference as a bound value. Keep caller input out of SQL text. The extraction script decides only match, no_match, or ambiguous, and carries facts only for one match. The derivation reduces those facts to the concepts the question allows.

Adapt the source as one reviewed unit

Change these files together:

Replace the table and column names in queries/record-status.sql. Select only the narrow facts the derivation needs.
Make request.columns and request.projection in sources/record-status.yaml match the SQL result exactly. Keep every caller-derived value in parameterBindings.
Update the response and fact schemas to close the result shape and fact shape.
Update the extraction and derivation scripts without adding I/O or a second query path.
Replace the example requirement, concept, framework, Evidence Type, purpose, and disclosure family identifiers in questions/record-status.yaml.
Replace the synthetic schema and rows under common.extract in fixtures/record-status.yaml. Keep the cases synthetic and retain the privacy canary.

Rerun the fixture command after each coherent change. A project is not ready for production while the starter’s example identifiers or table contract remain.

Leave the fixture: identifier in fixtures/record-status.yaml as generated while you replace the others: a fixture is approved only when that identifier begins registry.evidence.reference. and ends /v1, or the file declares coequal_acceptance_definition: true, so renaming it to your own namespace stops the run with evidence: fixture is not an approved synthetic acceptance definition. Fixtures are also the only way to exercise this project, because evidencectl dev refuses a SQLite source with local serving does not bind SQLite extracts.

Both rules, and the three the fixture runner applies before it evaluates a case, are stated once in the Evidence authoring form reference under Rules that block a build.

Publish the real extract

Create the SQLite file on the publisher side with the authority’s own export tooling. Evidence Gateway does not become an ETL pipeline. The finished file must contain exactly one metadata row:

CREATE TABLE evidence_extract (
  published_at TEXT NOT NULL,
  publisher    TEXT NOT NULL,
  extract_id   TEXT NOT NULL
);

Write published_at as the publisher’s snapshot instant. If the reviewed statement compares text timestamps with :evidence_now, normalize those columns to whole-second UTC in YYYY-MM-DDTHH:MM:SSZ form before publication.

Checkpoint the database into one file with no -wal or -journal beside it. Transfer it under a new versioned path, make it non-writable to the Evidence Gateway identity, and bind the source’s extractProfile to that path in the target runtime.yaml:

sourceExtracts:
  record-status-extract:
    path: /var/lib/registry-evidence/extracts/record-status-2026-08-09.sqlite

Never replace bytes behind a running immutable connection. Publish a new path and restart.

Gate the deployment

Create a complete governed target by following Build and deploy an Evidence Gateway project. Then build the editable project and run the checks against the exact candidate and mounted extract:

evidencectl package registry-status \
  --target registry-status/deployment-targets/staging \
  --output candidate-staging

evidencectl doctor --runtime-config "$(pwd)/candidate-staging/runtime.yaml"
evidencectl test candidate-staging
evidence --runtime candidate-staging/runtime.yaml check

The last command opens the bound extract, validates its metadata and fixed statement, and refuses an extract that is already older than maximumExtractAgeSeconds. Start the service only after that check passes. Route traffic after /ready and one authorized synthetic-subject request pass, then verify the signed response with independently pinned policy and public keys.