Operate, Troubleshoot, Document, and Defend the Final Architecture

The final deliverable is an operated system

The capstone ends where production work begins. The schema and repository are only part of the database system. Operations also owns startup capability checks, migrations, connection initialization, WAL/checkpoint observation, backup/restore, integrity monitoring, maintenance, incident response, version qualification, and the decision to keep or replace SQLite as requirements evolve.

Startup/opening runbook

Order matters. A startup that begins serving requests before it verifies the schema/runtime can turn a recoverable deployment error into inconsistent application behavior.

1. Resolve approved local database path; reject network-share deployment.2. Record application version + sqlite_version() + sqlite_source_id().3. Verify minimum feature version and required JSON capability.4. Open administrative/migration connection.5. Enable and verify PRAGMA foreign_keys=ON.6. Set trusted_schema=OFF for application policy.7. Refuse user_version newer than this application understands.8. Acquire release/migration serialization; create verified pre-migration backup.9. Apply ordered migrations to target user_version.10. Run quick_check + foreign_key_check + schema/domain assertions.11. Open normal application connections.12. On each connection: foreign_keys=ON; trusted_schema=OFF; busy timeout policy.13. Establish/verify WAL and synchronous=FULL policy for this deployment.14. Record journal_mode/synchronous/compile capability snapshot.15. Run a lightweight representative read; only then mark database READY.16. Schedule/observe backup, WAL/checkpoint and maintenance according to workload evidence.

Runtime compatibility policy

The database file format is exceptionally stable, but SQL features and compiled modules still vary by library build. FieldNotes therefore documents both a minimum and a current qualified target.

Capability probe

SELECT sqlite_version();SELECT sqlite_source_id();PRAGMA user_version;PRAGMA foreign_keys;PRAGMA journal_mode;PRAGMA synchronous;PRAGMA trusted_schema;PRAGMA compile_options;-- Required JSON capability: should return 1.SELECT json_valid('{"probe":1}');-- Optional module inventory where supported by current build.PRAGMA module_list;

Do not infer availability from the filename extension or from the programming-language version alone. Host operating systems, mobile frameworks, Python/Node runtimes, and application packages may bundle different SQLite libraries.

Incident 1: “database is locked” / SQLITE_BUSY

Start with the transaction timeline, not with an ever-larger timeout. Ask which connection owns the writer, how long it has held the transaction, whether it is doing network/UI/file work while the transaction remains open, and whether a read transaction is preventing checkpoint progress.

[ ] capture timestamp, operation/request_id, primary + extended SQLite code[ ] identify active/long transactions and connection ownership[ ] verify busy_timeout policy on the failing connection[ ] reproduce with two controlled file-backed connections[ ] inspect whether writes are short and idempotent/retry-safe[ ] remove network/user interaction from DB transaction[ ] measure retry count / exhausted busy budget[ ] only then consider workload-specific queueing or architecture change

Incident 2: slow query

Record the actual SQL shape with non-sensitive parameter categories, data volume/distribution, runtime version, statistics state, and EXPLAIN QUERY PLAN. A query that changed from SEARCH to SCAN after a release may indicate an index/migration/statistics problem; a SEARCH can still be slow if it returns huge payloads or performs application N+1 loops.

SELECT sqlite_version();PRAGMA optimize;EXPLAIN QUERY PLANSELECT inspection_id, started_at, outcome, summaryFROM inspectionWHERE device_id=?ORDER BY started_at DESCLIMIT 20;-- Compare current schema/index manifest, row counts and realistic parameter distribution.SELECT count(*) FROM inspection;SELECT * FROM pragma_index_list('inspection');

Incident 3: WAL growth

WAL growth is a symptom to explain. A long-lived reader may pin an old end mark so a checkpoint cannot advance fully. A burst of writes may simply produce a temporarily larger log. Observe checkpoint results and active readers before forcing TRUNCATE/RESTART modes in a busy system.

PRAGMA journal_mode;PRAGMA wal_autocheckpoint;PRAGMA wal_checkpoint(PASSIVE);

The checkpoint result reports whether it was busy plus WAL/checkpointed frame counts. Current SQLite defaults new connections to an autocheckpoint threshold of 1000 pages unless the build changes SQLITE_DEFAULT_WAL_AUTOCHECKPOINT; that default is a starting point to measure, not a tuning commandment.

Incident 4: failed migration

If a migration transaction fails before COMMIT, roll it back, preserve logs/error codes, and leave the pre-migration backup untouched. Do not “finish the schema manually” under pressure. Determine whether the release encountered unsupported syntax, unexpected old data, schema drift, missing capability, storage error, or a concurrent connection.

1. Stop serving writes from the incompatible release.2. Record sqlite_version/source_id/user_version and exact migration number.3. ROLLBACK if transaction remains active.4. Keep failed DB + logs for diagnosis; do not overwrite last good backup.5. Run quick_check / foreign_key_check if safe to inspect.6. Compare schema manifest with expected pre/post states.7. Fix migration or data precondition in source control.8. Rehearse on a restored copy of production-like data.9. Redeploy only after migration + rollback/recovery acceptance passes.

Incident 5: foreign keys unexpectedly not enforced

PRAGMA foreign_keys is connection-specific and changing it while a transaction is active is a no-op. If an orphan write slipped through a misconfigured connection, enabling enforcement later does not erase the bad row. Fix initialization, run foreign_key_check, and repair/recover data using a controlled procedure.

PRAGMA foreign_keys;PRAGMA foreign_key_check;SELECT * FROM pragma_foreign_key_list('inspection');

Incident 6: unexpected type

STRICT tables reduce dynamic-typing surprises but do not eliminate semantic mistakes such as a date string in the wrong format or valid JSON with the wrong business shape. Inspect typeof(), declared schema, validation code, and migration history. Do not “fix” production by editing bytes.

SELECT typeof(device_id), typeof(started_at), typeof(measurements_json),       json_valid(measurements_json)FROM inspectionWHERE inspection_id=?;SELECT sql FROM sqlite_schema WHERE type='table' AND name='inspection';

Incident 7: disk full or read-only

SQLITE_FULL and SQLITE_READONLY are not concurrency errors. Retrying indefinitely cannot create disk space or permissions. Stop destructive churn, preserve diagnostics, inspect free space/quota/mount state/permissions, and make room or restore correct authority. Remember that WAL/journal/temp/backup operations may need writable directory space beyond the main database file.

[ ] Is the database file writable by the service identity?[ ] Is the parent directory writable for WAL/journal/temp needs?[ ] Is the filesystem/device out of space, quota, or in read-only mode?[ ] Is a container using an ephemeral/read-only layer instead of the intended volume?[ ] Did a backup/restore destination fill the same filesystem?[ ] Are logs/core dumps consuming the application volume?[ ] After remediation, do integrity/domain checks still pass?

Incident 8: corrupt backup candidate

Never promote a backup that fails to open or does not return ok from integrity checks. Keep the last known-good backup, investigate the backup job/storage, and create a fresh candidate from the live source if the source is healthy. If the source is corrupt, Chapter 16's recovery path applies: restore known-good backup first; .recover is last-resort salvage, not a substitute for recovery media.

Maintenance runbook

Daily/normal startup:  - version/capability + user_version checks  - lightweight application smoke query  - capture failed BUSY/storage/corruption signalsAfter schema/index changes:  - run PRAGMA optimize under current documented workflow  - record critical EQP plansLong-running WAL application:  - monitor WAL size + checkpoint progress  - investigate long readers before aggressive checkpointsBackup schedule:  - SQLite-aware candidate -> verify -> off-device copy -> retention  - scheduled restore drill with documented RPO/RTOPeriodic/deployment health:  - quick_check routinely as policy permits  - integrity_check on maintenance/backup validation schedule  - foreign_key_check + domain consistency checksVACUUM:  - only when measured free-space/locality/privacy requirement justifies rebuild cost

Why SQLite—and measurable triggers to migrate away

FieldNotes keeps SQLite because the SQL-issuing application and file are co-located, writes are short and queueable, offline operation matters, no server-role or multi-node HA requirement exists, and one portable local database simplifies deployment/recovery. Revisit that decision using product metrics, not fear of a large row count.

Extension exercises

The course ends with optional directions that preserve the core contract rather than rewriting the application around every new feature.

Course-wide production reliability checklist

Final architecture defense

Course completion

You now have the full production loop: model the problem, implement constrained schema, own transactional application access, test concurrency and failures, measure plans/performance, back up and restore, secure the file/runtime boundary, operate incidents, and re-evaluate whether SQLite still matches the system. That loop—not a list of PRAGMAs—is the transferable SQLite skill.

Authoritative references

• SQLite latest release and source
• SQLite appropriate uses
• SQLite WAL
• SQLite PRAGMA reference
• SQLite Online Backup API
• SQLite security guidance