docs/reference/database.md

# Inspector SQLite substrate (`inspector.db`)

A single container-local `inspector.db` (SQLite, WAL, single-writer) is the source of truth for what used to be 7 bucket-resident JSON stores (state, catalog, access, audit, activity/dismissals, claims, pending requests) + the audit log. Per-reciter content (`reciters/<slug>/`) stays JSON-on-bucket and is **not** in the DB. The DB is full-file synced to the HF bucket (`db/inspector.db`) — pulled at boot, pushed after every committed write under a `db_seq` compare-and-swap guard. The inspector runs single-worker gunicorn-gthread (16 threads); all writes serialize through one writer connection.

Code lives in `inspector/services/db/`. Public API is `inspector/services/db/__init__.py`: `init_db`, `healthcheck`, `current_db_seq`, `current_version`, `transaction`, `get_conn`, `get_writer`, `reset`, `db_path`, `set_db_path_for_test`, `run_migrations`.

DB path resolves from `INSPECTOR_DB_PATH` (else `<tempdir>/inspector.db`). `init_db()` opens the writer, runs pending migrations, chmods `0600` (POSIX; no-op on Windows). `:memory:` is supported (tests).

---

## Concurrency model

`inspector/services/db/connection.py`. Single-worker gunicorn, 16 threads.

| Aspect | Mechanism |
|---|---|
| Writer | ONE shared `sqlite3.Connection` (`_writer`), guarded by re-entrant `_WRITE_LOCK = threading.RLock()`. Autocommit (`isolation_level=None`); `transaction()` manages `BEGIN`/`COMMIT` explicitly. |
| Write txn | `transaction()` CM: top-level acquires `_WRITE_LOCK`, `BEGIN IMMEDIATE` on writer, yields conn, bumps `db_seq`, `COMMIT` (or `ROLLBACK` on any exception, then re-raise). |
| Readers | Thread-local connections (`_readers`), autocommit + `PRAGMA query_only = ON`. WAL lets them run concurrently with the writer. |
| Read-after-write | Readers hold no long-lived txn → every `SELECT` sees the latest committed WAL snapshot. No stale reads after a commit. |
| Re-entrancy | `transaction()` is savepoint-aware. Nested calls (active conn tracked via `_active` ContextVar) enroll in the active connection and use `SAVEPOINT sp_<depth>` (+ `ROLLBACK TO`/`RELEASE`), never a fresh `BEGIN`. Multi-repo handlers are one atomic unit. |
| Connection routing | `get_conn()` returns the active write-txn conn if one is open on this context, else the thread-local reader. Repos call `get_conn()` and work on whichever they get. |
| db_seq bump | `_bump_db_seq()` runs inside the top-level commit only (nested savepoints don't bump). Monotonic; CAS guard for bucket sync. |
| Pragmas | `foreign_keys=ON`, `busy_timeout=5000`, `journal_mode=WAL`, `synchronous=NORMAL`, `temp_store=MEMORY`; readers add `query_only=ON`. |
| Reset | `reset()` closes the writer + bumps `_generation` so each thread's reader lazily reopens. Tests + path switches only. |

**Invariant when adding a write:** every mutation MUST run through `transaction()` (or `sync.durable_transaction()` at a service boundary — see below). Never write on a reader connection (`query_only` will reject it; repos that need the writer call `get_conn()` *inside* an open txn). A handler that touches multiple repos opens ONE outer `transaction()` (or `durable_transaction()`); the inner repo calls nest as savepoints and commit atomically with it. Repo write functions do **not** open their own txn (except `repo_transitions.append`, which uses re-entrant `transaction()` so it nests under the caller).

---

## Table inventory

All from `inspector/services/db/migrations/0001_init.sql`. Datetimes are TEXT (ISO-8601 UTC, `Z` suffix); booleans INTEGER 0/1; JSON columns TEXT (orjson). No CHECK on free-vocabulary columns (`transitions.event`, `requests.kind`, `claims.close_reason`) — new kinds need zero migration; validity enforced in pydantic/app layer.

| Table | Purpose | Owning repo |
|---|---|---|
| `db_meta` | key/value runtime metadata; holds `db_seq` (seeded `'0'`) | `connection.py` (`_bump_db_seq`, `current_db_seq`) |
| `users` | HF identity + cached login + first/last seen | `repo_access` |
| `role_assignments` | maintainer/owner grants (active + soft-revoked); `ux_role_active` partial-unique = one active role per user | `repo_access` |
| `riwayahs` | catalog vocab: riwayah slug/short/name | `repo_catalog` |
| `styles` | catalog vocab: style slug/short/name | `repo_catalog` |
| `sources` | catalog vocab: source + `audio_categories` JSON | `repo_catalog` |
| `channels` | catalog vocab: channel + `host_patterns` JSON | `repo_catalog` |
| `recording_contexts` | catalog vocab: studio/broadcast/prayer/taraweeh/mixed | `repo_catalog` |
| `catalog_meta` | single row id=1: schema_version, generated_at, persisted `derived` JSON | `repo_catalog` |
| `catalog_aliases` | slug/reciter_id rename history | `repo_catalog` |
| `reciters` | reciter identity (name_en/ar, country, notes) | `repo_catalog` |
| `deliveries` | per-(reciter × riwayah × style × source × channel …) audio delivery; FKs into all vocab tables | `repo_catalog` |
| `transitions` | canonical append-only event log (replaces `audit/*.jsonl`); `seq` PK, unique `id`, `content_hash` | `repo_transitions` |
| `delivery_states` | current lifecycle projection per slug (state, visibility, last_save_at, timestamps_job_ids, revision_in_progress) | `repo_state` |
| `claims` | first-class claims (current + history); `ux_claim_open_slug` = one open claim per slug | `repo_claims` |
| `requests` | unified pending + 3 archives (status pending/accepted/returned/discarded); `ux_request_pending_slug` = one pending per non-null slug; evolving fields in `payload` JSON. `kind` ∈ `existing_combo_edit` (slug-based edit) \| `existing_reciter_new_combo` \| `new_reciter` (the latter two are **slugless** intake — `slug=NULL` until accept; `payload` carries `reciter_id`, `source`, `attestations`, cached `probe`; exempt from `ux_request_pending_slug`). | `repo_requests` |
| `request_views` | **unused** — was per-admin "viewed" marks for the retired Requests-tab unviewed badge, `(request_id, hf_user_id)` PK. "New request" awareness moved to the My Notifications rail (`request.received` alert); table left in place (no drop migration). Migration `0004` | — |
| `review_views` | **retained but unused** — was the per-admin marked-ready "viewed" mark for the Reviews-tab dot, `(slug, hf_user_id)` PK. The marked-ready notification was retired with the Releases restructure; `repo_review_views` is deleted and nothing reads the table. The frozen migration `0005` keeps the table; no reader. | — |
| `activity_tombstones` | owner-only global deletes for public-rail cards, keyed on `audit_content_hash`. Per-user dismissals (`activity_dismissals`) were dropped in migration 0006 alongside the retired admin notifications rail. | `repo_activity` |
| `guide_views` | per-user "read" marks for the validation-accordion help guides, `(view_key, hf_user_id)` PK, **write-once** (`INSERT OR IGNORE` — no unread transition). `view_key` is the collapsed guide id (`low_confidence_v2 → low_confidence`); no FK (app-level enum). Drives the FE unread `?` border + first-edit onboarding gate. Surfaced on `/api/me` as `guides_read`. Migration `0010` | `repo_guides` |
| `ts_reports` | public Timestamps-tab categorized reports (`audio`/`timing`/`mapping`/`tajweed`/`other`) pointing at a flexible target (verse/word/cell/phoneme/column/cell_group). One row per `(slug, verse_key, category, target_key, identity)` where identity is `hf_user_id` (signed in) XOR `anon_token`; two **partial** unique indexes split the per-identity uniqueness (`ON CONFLICT` targets echo the `WHERE` predicate). `target_key` is the canonical descriptor string. `snap_*` is the targeted-content fingerprint for staleness; `status` open/resolved + `resolved_*`; `stale` set on re-stamp. Migration `0025` | `repo_ts_reports` |
| `ts_verse_flags` | superseded by `ts_reports` — table retained until its few rows are manually migrated, then dropped in a follow-up migration. Migration `0024` | (no repo) |

Key indexes: `ix_transitions_slug_ts`, `ix_transitions_ts` (DESC), `ix_transitions_event`, `ix_transitions_hash`; `ix_delivery_states_state`; `ix_claim_open_assignee` (partial, open only); `ix_requests_status`, `ix_requests_slug`; `ix_request_views_user`, `ix_review_views_user`, `ix_guide_views_user`; `ix_deliveries_reciter`; `ix_role_user`.

Note: `assignee_*` / `marked_ready` are **not** columns on `delivery_states` — they live on the open `claims` row and are LEFT-JOINed in by `repo_state`.

---

## Repos

`inspector/services/db/repo_*.py`. Each owns its table(s) and assembles the legacy pydantic read model so the service/route/FE wire contract is unchanged. Write functions assume the caller already opened a `transaction()`.

| Repo | Table(s) | Key ops | Read model |
|---|---|---|---|
| `repo_state` | `delivery_states` (+ LEFT JOIN `claims`) | `get_row`, `all_rows`, `exists`, `upsert_state`, `update_state(**writable)` | `ReciterRow` (assignee/marked_ready filled from open claim; only on `under_review`) |
| `repo_catalog` | `riwayahs`/`styles`/`sources`/`channels`/`recording_contexts`/`catalog_meta`/`catalog_aliases`/`reciters`/`deliveries` | `snapshot`, `find_reciter`/`find_delivery`/`find_source`, `edit_reciter`/`edit_delivery`, `add_reciter`/`add_delivery`/`add_source` (raise `Duplicate`), `insert_*`, `load_vocab`, `insert_alias`, `set_meta`, `refresh_derived` | `ReciterCatalog` (full `model_dump(by_alias=True)` byte round-trips parity gate) |
| `repo_access` | `users`, `role_assignments` | `ensure_user`, `get_login`, `resolve_role`, `find_member`, `active_members`, `snapshot`, `grant_role`, `revoke_role` (soft), `update_role`, `has_any_active` | `Member` / `RolesFile` (snapshot = active + revoked) |
| `repo_transitions` | `transitions` | `append` (re-entrant txn), `get`, `get_by_content_hash`, `for_slug`, `since`, `feed` | `AuditRecord` (append) / audit-record-shaped dict (reads) — `ts` is exact stored string so `activity_classification.audit_id` recomputes `content_hash` |
| `repo_claims` | `claims` | `get_open_claim`, `open_claim_for_user` (O(1) one-claim check), `open_claims_for_user`, `open_claim`, `close_claim`, `set_marked_ready`, `reassign` | raw `sqlite3.Row` (consumed by `repo_state`) |
| `repo_requests` | `requests`, `request_views` | `submit`, `resolve` (→ accepted/returned/discarded), `delete_pending`, `get_pending`/`has_pending`/`all_pending`, `count_pending`, `all_archived(kind)`, `get_for_slug(kind, slug)`, `get_by_id`, `admin_list_rows(status)`, `counts_by_status`; views: `mark_viewed`/`is_viewed`/`viewed_ids_for_user`/`count_unviewed_open_for_user` | `PendingRequest` / `ArchivedRequest` (archive kind→status: completed→accepted, returned→returned, discarded→discarded) |
| `repo_activity` | `activity_tombstones` | `delete`/`undelete`/`is_deleted`/`deleted_set` (global tombstones for the public feed; owner-only writes) | `set[str]` keyed on `content_hash` |
| `repo_guides` | `guide_views` | `record_view` (INSERT OR IGNORE — write-once), `read_views` | `list[str]` of read `view_key`s |
| `repo_ts_reports` | `ts_reports` | `create(...) -> (row, created)` (per-identity ON CONFLICT on `target_key`), `verse_counts(slug)` (open/resolved), `list_for_verse`, `resolve(...)`, `list_open_for_recheck`/`mark_stale` (staleness), `delete(...)` | plain dicts (the route assembles the `TsReport` wire model) |

`repo_errors` (`errors.py`): `RepoError`, `Duplicate` (PK / partial-unique violation), `NotFound`. Services map these to their own contracts (e.g. catalog's `InvalidCatalogChange`) instead of leaking `sqlite3.IntegrityError`.

`one-claim-per-non-owner` is enforced in the transition layer (owners exempt by policy), NOT a DB index — `ix_claim_open_assignee` is non-unique. `ux_claim_open_slug` (unique) does enforce one open claim per slug.

---

## Migrations

`inspector/services/db/migrate.py`, scripts in `inspector/services/db/migrations/NNNN_*.sql`.

- Version tracked via `PRAGMA user_version` (NOT a table).
- `run_migrations(conn)` (called by `init_db`): `_discover()` globs `*.sql`, parses the 4-digit prefix (`^(\d{4})_.*\.sql$`), sorts ascending, applies every file with number > `user_version`.
- Each script is wrapped `BEGIN; <sql> PRAGMA user_version = NNNN; COMMIT;` and run via `executescript` — DDL + version bump are atomic. Failure rolls back and **aborts boot** (fail-fast → `/healthz` 503), never half-applies.
- Non-conforming filenames are logged and skipped.

**Adding a migration:** create `NNNN_<desc>.sql` (next 4-digit number) in `migrations/`. Write raw DDL/DML only — do NOT add `BEGIN`/`COMMIT`/`PRAGMA user_version` (the runner wraps it). It applies on next boot. Current head: `0025_ts_reports.sql`.

> **Never edit an already-applied migration to change schema.** Existing DBs (every deployed/dev/local one is pulled from the bucket already past `user_version 1`) re-run nothing ≤ their version, so edits to `0001` etc. reach fresh DBs only and silently miss live data. New columns/tables ALWAYS go in a new `NNNN_*.sql` with an additive `ALTER`/`CREATE`. (0007 exists precisely because the `claims.mark_ready_*` columns were first inlined into `0001` and never reached the live `claims` table — the Reviews drawer detail query 500'd with `no such column`.)

---

## Bucket sync

`inspector/services/db/sync.py`. Bucket paths: `db/inspector.db` (the DB), `db/inspector.seq` (CAS sidecar), `db/inspector-<YYYY-MM-DD>.db` (daily snapshots, 30-day retention). Uses bucket primitives directly (`read_bytes_direct`/`write_bytes_direct`), bypassing the mount's debounced flush so an acked write is durable before the response.

**Boot pull** — `pull(dest)`: downloads `db/inspector.db` to the local path (default configured DB path), clears stale `-wal`/`-shm`, chmods `0600`. Returns False (fresh init) if the bucket has no DB. Always trusts the bucket.

**Push after commit** — `durable_transaction()` is the mutating service boundary (wraps `state.transition`, catalog/requests mutations, access grant/revoke/update, activity mutations). It opens `connection.transaction()`, and **only the OUTERMOST** boundary calls `mark_durable()` after the txn commits and the active-conn ContextVar clears (`snapshot()` requires no open txn). Nested `durable_transaction()` is a savepoint and does NOT upload.

- `snapshot()` → standalone DB bytes via SQLite online `backup()` (no WAL sidecar) + the `db_seq` read **from the snapshot itself**, so labelled seq always matches uploaded bytes. Raises if called inside an active write txn.
- `upload()`: snapshot → read remote `inspector.seq` → CAS check → write `inspector.db` then `inspector.seq` (`{seq, nonce, ts}`). Returns the uploaded seq.

**CAS guard** (`db_seq`): the per-process `_NONCE` (12 hex) identifies "our own prior upload" vs "another container raced us during a rolling deploy". Upload **refuses** (`UploadConflict`, → 5xx; local commit stays ahead, later upload reconciles) iff remote `seq >= local snap_seq` AND remote `nonce != _NONCE`. Caveat: bucket I/O has no atomic CAS, so this catches a *stale* racer, not two simultaneous writers — safe under the single-active-writer invariant; it covers the rolling-deploy overlap window.

**Batching** — `deferred_sync()` (ContextVar depth counter): boot-scan (`hydrate_initial_seen`) applies N transitions in a loop; this coalesces them into ONE upload on outermost exit (only if no exception **and the batch actually advanced `db_seq`** — a no-op batch, e.g. a boot scan that finds nothing stuck, skips the upload so it doesn't trip the equal-seq CAS guard against the previous container's nonce and log a spurious `ERR` on every restart). `mark_durable()` is a no-op inside it. `set_sync_enabled(False)` disarms uploads (tests; default armed in prod).

**`current_db_seq()`** (in `connection.py`) reads `db_meta.db_seq` — the monotonic counter the CAS guard compares. Bumped once per committed top-level write txn.

Daily snapshot: `daily_snapshot()` writes `db/inspector-<day>.db` and prunes any older than `_SNAPSHOT_RETENTION_DAYS` (30). `status()` (counters: `nonce`, `last_bucket_upload_ts`, `bucket_lag_seconds`, `last_error`, `queue`) backs `/healthz`.

---

## serde

`inspector/services/db/_serde.py` — row ↔ pydantic helpers.

| Helper | Behavior |
|---|---|
| `to_iso(dt)` / `from_iso(s)` | datetime ↔ ISO-8601 UTC string. `to_iso` emits a **`Z`** suffix (not `+00:00`) to match pydantic v2 `model_dump(mode="json")` byte-for-byte — stored `ts` and wire `ts` must be identical (activity card parity). |
| `json_dumps`/`json_loads` | orjson; `json_loads("")`/`None` → `None`. |
| `now()` | `datetime.now(timezone.utc)`. |
| `new_transition_id()` | `req_<12hex>` (mirrors old audit `request_id`). |
| `content_hash(ts, event, slug, actor_hf, result)` | `sha1(ts\|event\|slug\|actor_hf\|result)[:16]` from the exact stored strings. MUST stay byte-identical to `services.activity.activity_classification.audit_id` so dismissals/tombstones keep matching transitions. |
| `content_hash_for_record(record)` | same hash from a raw audit/transition dict (used by the JSON→SQLite migration on historical records). |

Repos round-trip these pydantic models from `qua_shared/schemas/` (enum values serialized via `.value`; the FE-facing subset re-exported at `qua_shared/schemas/fe_types.py`):

| Schema module | Models | Repo |
|---|---|---|
| `state.py` | `ReciterRow`, `ReciterState`, `Visibility`, `RevisionContext` | `repo_state` |
| `catalog.py` | `ReciterCatalog`, `Vocab`, `Riwayah`, `Style`, `Source`, `Channel`, `RecordingContext`, `ReciterEntry`, `Delivery`, `Alias`, `Derived` | `repo_catalog` |
| `access.py` | `Member`, `Role`, `RolesFile` | `repo_access` |
| `audit.py` | `AuditRecord`, `Actor` | `repo_transitions` |
| `pending_requests.py` | `PendingRequest`, `ArchivedRequest`, `ProposedEdits` | `repo_requests` |
| `activity_state.py` | `ActivityState` (migration read of legacy store) | `repo_activity` |

---

## JSON→SQLite migration

`scripts/migrations/migrate_json_to_sqlite.py` — one-shot, run once per bucket (`--bucket dev|prod`). Reads the 7 legacy stores + audit JSONL via the storage backend, decomposes into the SQLite tables, runs a parity readback, and (unless `--dry-run`) uploads `db/inspector.db`. Refuses to overwrite an existing bucket DB without `--force`. Refuses orphan slugs (state/request slug missing from catalog deliveries) without `--allow-orphans`.

Decomposition (`build()`, all in one `transaction()`; guards that target tables are empty first):

| Source | → Tables |
|---|---|
| `access` (RolesFile) | `users` + `role_assignments` (active + revoked) |
| `catalog` (ReciterCatalog) | vocab + `reciters` + `deliveries` + `catalog_aliases` + persisted `derived`/`generated_at` |
| `state` (ReciterStateFile) | `delivery_states` + a synthesized OPEN `claims` row for each `under_review` row with an assignee (+ `marked_ready` stamp) |
| `requests/pending.json` + 3 archives | `requests` (status pending / accepted / returned / discarded) |
| `audit/<YYYY>-<MM>.jsonl` | `transitions` (ts/id/content_hash preserved verbatim; `slug` NULLed if its delivery no longer exists, to satisfy the FK while preserving the event) |
| `activity/state.json` (legacy `deleted` list only) | `activity_tombstones` (keyed on content_hash). The legacy `dismissals` field is ignored — per-user dismissals were dropped with the admin notifications rail. |

**Parity gate** (`parity_check()`, → `SystemExit` on any issue): SEMANTIC parity (list order normalized — repos read slug-sorted), not raw byte-for-byte file diff. Checks:
- catalog `snapshot().model_dump(mode="json", by_alias=True)` == source dump (after `_norm_catalog` sorts vocab/reciters/deliveries) — the full-fidelity round-trip.
- active roles set match; full `role_assignments` count == source members.
- every in-catalog state row `model_dump` matches.
- pending slug set match; per-status archive row counts match.
- open claims set (slug, assignee) == `under_review` rows with assignee.
- activity tombstones set matches the migrated `deleted` list.
- `transitions` count == audit record count.

`run()` ordering: `read_sources` → `orphan_check` → `build` → `parity_check` → upload. `main()` sets `INSPECTOR_BACKEND=bucket` + `INSPECTOR_BUCKET_REPO`, `init_db()`, then `sync.upload()`.

---

## healthz

`healthcheck()` (`__init__.py`) — cheap, never raises:

```json
{ "open": true, "schema_version": <PRAGMA user_version> }
```

On any error: `{ "open": false, "error": "<first 200 chars>" }`. Sync counters from `sync.status()` (`bucket_lag_seconds`, `last_error`, `nonce`, `last_bucket_upload_ts`, `queue`) supplement the DB health on `/healthz`; a failed boot migration aborts startup → `/healthz` 503.
```