Problem
schema_indexes.sql is applied only by the ingest index phase (ingest/index.py) and db.py create_indexes (same file, ingest-driven). init_db applies tables but not indexes. Any index added to the file after a database's last full ingest silently never materializes there.
Observed impact (2026-07-17)
The four contacts indexes shipped with the contacts subsystem (#100, 2026-07-12) were absent from the live database (last ingest: May). Missing idx_contact_addresses_contact_id made contacts_search O(63k²) via its correlated name-variants subquery — 213 seconds per interactive contact lookup (also hit the Chronicle /api/people proxy). With the index: 268ms.
Live DB has been repaired manually (CREATE INDEX CONCURRENTLY for all four; verified). The schema file itself was always correct — this is purely an application-path gap.
Proposed fix (discuss)
Options:
- A lightweight
maildb ingest migrate-style step (or extend the existing migrate) that diffs schema_indexes.sql names against pg_indexes and creates missing ones CONCURRENTLY — safe to run anytime, surfaced in Data Health / verify-env.
- Have
verify-env at minimum WARN on missing schema-file indexes (detection without mutation).
- Table-scoped ensure: contacts-related code paths (
contacts refresh) ensure their own indexes.
Option 1 + the verify-env warning from 2 seems right: detection everywhere, one explicit repair command, no surprise index builds at server startup (HNSW etc. must never auto-build in init_db).
Also: add a people/contacts-search scenario to the Chronicle perf harness (apps/chronicle/server/perf/harness.py) — this regression was invisible to the §16.2 verification because the harness had no contacts scenario.
🤖 Generated with Claude Code
https://claude.ai/code/session_01XP7M17NHxw6mjpTFtMqwZu
Problem
schema_indexes.sqlis applied only by the ingest index phase (ingest/index.py) anddb.py create_indexes(same file, ingest-driven).init_dbapplies tables but not indexes. Any index added to the file after a database's last full ingest silently never materializes there.Observed impact (2026-07-17)
The four contacts indexes shipped with the contacts subsystem (#100, 2026-07-12) were absent from the live database (last ingest: May). Missing
idx_contact_addresses_contact_idmadecontacts_searchO(63k²) via its correlated name-variants subquery — 213 seconds per interactive contact lookup (also hit the Chronicle/api/peopleproxy). With the index: 268ms.Live DB has been repaired manually (
CREATE INDEX CONCURRENTLYfor all four; verified). The schema file itself was always correct — this is purely an application-path gap.Proposed fix (discuss)
Options:
maildb ingest migrate-style step (or extend the existingmigrate) that diffsschema_indexes.sqlnames againstpg_indexesand creates missing ones CONCURRENTLY — safe to run anytime, surfaced in Data Health /verify-env.verify-envat minimum WARN on missing schema-file indexes (detection without mutation).contacts refresh) ensure their own indexes.Option 1 + the verify-env warning from 2 seems right: detection everywhere, one explicit repair command, no surprise index builds at server startup (HNSW etc. must never auto-build in init_db).
Also: add a people/contacts-search scenario to the Chronicle perf harness (
apps/chronicle/server/perf/harness.py) — this regression was invisible to the §16.2 verification because the harness had no contacts scenario.🤖 Generated with Claude Code
https://claude.ai/code/session_01XP7M17NHxw6mjpTFtMqwZu