Chapter 20 · Production Capstone: Build and Operate a Complete SQLite Application Database
Capstone Requirements, Workload, Data Model, and SQLite Fit Review
Define the FieldNotes production capstone from actors, invariants, workload, durability, concurrency, backup, security, and deployment requirements before writing any DDL.
Capstone brief: FieldNotes Offline Service Recorder
The final chapter will not invent a new domain. We will turn the course's FieldNotes examples into one coherent deliverable: a local field-service application used by technicians to inspect equipment when network connectivity is unreliable. The database lives on the same device as the application, persists inspection history, queues synchronization events, and remains usable offline. A background synchronization worker may read and mark queued events while the technician continues to browse and record work.
Every design choice must name the requirement it satisfies, the SQLite behavior it depends on, and the condition that would force the choice to be revisited. “Because this is a common SQLite setting” is not a requirement.
Define actors, data, invariants, read/write paths, concurrency, durability, backup, security, and deployment constraints before DDL.
Reuse relational modeling discipline while introducing only the SQLite-specific physical choices needed by the workload.
Defend why an embedded SQLite database is appropriate for this local/offline topology.
Define measurable future conditions that would make the architecture inappropriate or require a client/server database.
Produce a logical schema and workload map that later lessons will implement unchanged.
Write acceptance criteria for correctness, performance, recovery, compatibility, and security before implementation begins.
Actors and trust boundaries
The application has four actors. They do not all need separate database roles because core SQLite is not a server authorization system; application code and operating-system permissions remain the authority boundary taught in Chapter 19.
| Actor | Database behavior | Trust/operational concern |
|---|---|---|
| Field technician | Browse assigned sites/devices; record inspections and notes. | Human input is untrusted data and must be bound/validated. |
| Background sync worker | Read pending outbox events; after remote acknowledgement, mark them delivered. | Must not hold long write transactions while doing HTTP/network work. |
| Supervisor/report reader | Read local inspection summaries when the device is connected to a workstation tool. | Read-only access should not require write permission. |
| Support/operations tool | Run version checks, migrations, integrity checks, backup/restore and diagnostics. | Privileged workflow; must not improvise direct file edits. |
Data and invariants first
The logical model follows the normalized FieldNotes shape already used throughout the course. Stable, queryable facts remain relational. JSON is reserved for genuinely device-specific metadata and measurements whose shape can vary by equipment type. Synchronization is represented explicitly with an outbox rather than hiding remote side effects inside a database trigger.
site 1 ───────< device 1 ───────< inspection │ │ │ └────< maintenance_note ├────< device_tag │ └──── protocol generated from controlled metadata_jsoninspection ──(same transaction)──> sync_outbox eventImportant invariants--------------------site_code and device_code are unique.Every device belongs to an existing site.Retired/active/inspection_due are the only device states.Every inspection has one globally unique request_id for idempotency.finished_at cannot precede started_at in the chosen UTC text representation.JSON columns contain valid JSON text.A critical maintenance note marks an active device inspection_due.An inspection and its sync-outbox event commit together or not at all.Workload map
SQLite fit is primarily a workload/topology question, not a row-count question. Write down the operations and their frequency/order before selecting indexes or journal mode.
| Path | Shape | Concurrency requirement | Failure concern |
|---|---|---|---|
| Open device list | Filter by site/status; small ordered result. | Many reads may overlap. | Stale UI is acceptable for the duration of one read transaction. |
| Device history | One device, recent inspections/notes ordered by time. | Read while sync worker may update outbox. | Must be a consistent snapshot. |
| Record inspection | Insert inspection + optional note + outbox event. | Short write; technician should not wait behind network I/O. | Atomicity and idempotency are mandatory. |
| Mark sync delivered | Update one outbox row after remote acknowledgement. | Background writer competes occasionally with foreground writes. | Retry must not duplicate remote work. |
| Migration | Serialized exclusive release operation. | No concurrent app writes. | All-or-nothing schema release. |
| Backup/restore | Online backup during normal use; restore is controlled incident operation. | Backup may coexist with reads/writes. | Backup is worthless until verified/restored. |
Concrete deployment assumptions
For the capstone, the SQLite engine and database file are on the same local device. The normal application has one process with short foreground writes and one background synchronization component. Reads are more frequent than writes. Power can fail unexpectedly, so the capstone chooses durability over the lowest possible commit latency. Backups are copied off-device after being created with a SQLite-aware mechanism.
Database location: local application-data directory on device storageDirect network filesystem access: prohibitedExpected writers per DB file: short foreground + short sync acknowledgementsLong network call inside DB transaction: prohibitedJournal policy: WAL after capability/host-filesystem validationDurability policy: synchronous=FULL for this capstoneBusy budget: bounded; measure and report exhausted retriesForeign keys: required ON for every connectionJSON support: required capabilityFTS5/RTree: not required by the core capstoneBackup: SQLite Online Backup API via Python, then verify + off-device copyRestore: controlled replacement after integrity/domain verificationMigration owner: one release process; user_version-managedCurrent qualified target: SQLite 3.53.4Declared minimum feature baseline: SQLite 3.38.0 + required JSON capabilityWhy WAL—and what it does not promise
WAL is chosen because this local workload benefits from readers continuing while a short writer appends changes. It does not create multiple simultaneous writers, make a network filesystem safe, or remove the need for checkpoints and busy handling. The capstone sets synchronous=FULL because inspection records are operational evidence and the product requirement values power-loss durability. Another product with different RPO/latency requirements might make a different documented choice.
Why SQLite fits today
The architecture aligns with SQLite's official strong-fit cases: application/device-local storage, offline operation, an application-managed file, modest writer concurrency, and no requirement for direct remote SQL clients or server-owned roles. The application server/data-server separation rule from Chapter 19 is satisfied because the code issuing SQL and the database file live on the same device.
| Fit question | Capstone answer | Future invalidation signal |
|---|---|---|
| Is data separated from SQL-issuing application by a network? | No. | Requirement to open the same file directly from multiple machines. |
| Can writers queue and finish quickly? | Yes by design. | Sustained exhausted busy budget or writer-latency SLO misses after query/transaction fixes. |
| Need database-server roles/GRANT? | No; application owns authorization. | Independent services/users require server-enforced authorization. |
| Need automatic multi-node HA/failover? | No for offline device copy. | Authoritative shared service requires multi-node failover/replication SLO. |
| Operational size manageable as one local file? | Yes for stated device retention policy. | Backup/restore/storage-window requirements become unmanageable even after retention design. |
| Dominant workload OLTP/local state? | Yes. | Workload becomes primarily large analytical scans better served by an analytical engine. |
Acceptance criteria before implementation
Good acceptance tests describe behavior, not implementation trivia. Performance thresholds should be chosen for the target device; the course provides metrics to record rather than pretending one laptop's milliseconds are universal.
| Area | Pass condition |
|---|---|
| Schema correctness | Expected tables/indexes/view/trigger exist; user_version=2 after migrations. |
| Integrity | quick_check and integrity_check return ok; foreign_key_check returns no rows. |
| Constraints | Invalid status, invalid JSON, orphan FK, and bad finished_at writes are rejected. |
| Atomicity | Injected failure before COMMIT leaves no inspection/note/outbox partial state. |
| Idempotency | Replaying the same request_id returns the existing inspection without a duplicate outbox event. |
| Concurrency | Two file-backed connections reproduce BUSY under a held writer; bounded retry succeeds after release. |
| Planner | Hot device/status and device-history queries use justified indexes; no tuning claim lacks EQP evidence. |
| Performance | Representative device hardware meets product-owned latency/throughput targets with recorded configuration. |
| Backup/recovery | SQLite-aware backup opens independently, passes checks, restores, and matches domain counts. |
| Security | All values bound; dynamic SQL structure allowlisted; DB/backup permissions reviewed; extension loading not required. |
| Compatibility | Runtime version/capabilities are checked at startup and migration before using feature-dependent SQL. |
Architecture review gate
Approve the design before DDL
Defend the choices from requirements.
- Why is the sync HTTP call forbidden inside the SQLite write transaction?
- What exact requirement makes request_id UNIQUE useful?
- Why does WAL not solve a future requirement for many simultaneous writers?
- Why is JSON limited to metadata/measurements rather than replacing device/site columns?
- Which future requirement is a stronger reason to migrate than “the file reached 10 GB”?
- What must be defined before claiming a performance regression?
Review the answers
Network calls make transaction duration unpredictable and worsen writer contention. A unique request_id provides an idempotency key so a retried business operation can identify the already committed inspection. WAL permits reader/writer overlap but still serializes writers. Stable searchable facts stay relational while genuinely variable metadata can use controlled JSON. Server-side authorization, many simultaneous writers, direct network clients, HA requirements, or unmanageable operational windows are stronger migration signals than an arbitrary size. A benchmark must define dataset, plan, SQLite version/configuration, hardware/filesystem, cache state, repetitions, and metric/SLO.
Bridge to implementation
Lesson 2 turns this approved model into two versioned migrations. Every index, view, trigger, generated column, and table option will be tied back to this workload rather than added for decoration.