05 — Data and Persistence#

Who this is for. Anyone adding a table, changing a query, touching backup or restore, or debugging "the report says zero and I do not believe it".

What you'll learn. How Ring configures SQLite and why; the repository shape and its deliberate absence of a base class; how migrations work and the one gap no test covers; the restore contract and how it is kept honest; the maintenance latch and writer quiesce; the historian and the two append-only journals; and what exports do.

Authorities this chapter defers to: docs/production-readiness/09_DB_BACKUP_RESTORE.md for the backup/restore procedure, docs/production-readiness/10_AUDIT_TRAIL.md for what the operator audit trail records, and docs/CONFIG_AND_STARTUP.md for the complete on-disk file set.

5.1 One database, resolved to the exe directory#

Ring keeps everything in a single SQLite file, by default <exe dir>\RingwoodDatabase.db (plus its -wal and -shm sidecars).

The path is normalized at config load by ConfigurationService.NormalizeConnectionStringDbPath: a relative Data Source= is rewritten to <exe dir>\<name>, because repositories open the connection string verbatim and a launch with a different working directory would otherwise silently create a second database. Absolute paths and :memory: are left alone (docs/CONFIG_AND_STARTUP.md §2).

For a real plant install, Ring/appsettings.production.template.json is the starting point, and with Production.Enabled true, validation requires an absolute DB path and an absolute backup directory (ConfigurationValidator.cs:106, 112) — see chapter 02 §2.3, "Production mode — the strict profile" for the switch that arms this and the rest of what it checks.

Per-connection pragmas#

Every repository opens its own connection per call and applies the same three pragmas through Ring/Database/RingwoodDbAccess.cs:

PRAGMA busy_timeout = 15000;
PRAGMA journal_mode = WAL;
PRAGMA synchronous  = FULL;

synchronous = FULL is an override, not a default: WAL defaults to NORMAL, which can lose the last committed transactions on power loss. The class comment is explicit — "A plant PC must keep the most recent commits, so force FULL fsync-on-commit" (RingwoodDbAccess.cs:22-27). The 15 s busy timeout exists because multiple repositories open the same file concurrently; without it, concurrent access throws "database is locked".

5.2 Repository shape#

Ring/Database/Interfaces/ holds 43 interfaces and Ring/Database/Repositories/ holds 43 implementations (verified by count). No ORM, no Dapper — hand-written ADO over System.Data.SQLite.

There is no base class. Each repository is an independent class that:

1. opens a connection per call,
2. applies RingwoodDbAccess.ApplyConcurrencyPragmas,
3. exposes public static void ApplySchema(SQLiteConnection), which its instance EnsureTable() also calls.

That static is the single source of DDL truth, shared between first-run creation and migration. Copy the shape from an existing repository rather than inventing a new one; a third pattern is the thing to avoid.

RepositoryReadScope — telling "no rows" from "the read failed"#

Ring/Database/RepositoryReadScope.cs closes a real reporting hazard. Every list-returning repository traps its exceptions, logs them, and returns an empty list — deliberately, because a SQL blip must never crash the HMI. But that makes "the database could not be read" indistinguishable from "there are no records", so a failed read renders (and emails) as a clean "0 batches in range".

Each repository catch block calls RepositoryReadScope.ReportFailure. A caller that cares wraps its read in Begin / Capture<T> / Watch and inspects HadFailure. Callers that do not opt in are completely unaffected — no signature changes, no behaviour change, no allocation when no scope is active.

Threading constraint: the current scope is [ThreadStatic]. Repository reads are synchronous ADO calls, so the catch block runs on the caller's thread — including the report scheduler's STA render worker. A scope must not be held across an await; keep it around the synchronous read only (RepositoryReadScope.cs class doc).

The user-facing wording for the distinction lives in one place: Ring/Services/Reports/ReportDataUnavailableNotice.cs.

5.3 Migrations#

Ring/Database/DatabaseInitializer.cs drives schema evolution with PRAGMA user_version steps.

• An ordered MigrationStep list from GetMigrationSteps() (:361), each a (version, description, Action<SQLiteConnection>) triple (:331-340).
• Steps are applied inside a SAVEPOINT and the version stamped atomically.
• Every step must be idempotent — CREATE TABLE IF NOT EXISTS, guarded ALTER.
• Step 1 is the baseline: new MigrationStep(1, "Baseline schema (all CREATE TABLE IF NOT EXISTS)", _ => { }) (:366) — an empty body whose job is to stamp an aged, unversioned database (user_version == 0) up to 1 without altering it.
• CurrentSchemaVersion is the one constant to bump (DatabaseInitializer.cs:743). Read the file for its value; do not quote one into prose.

SampleDbFileExistence() (:158) is the first-run latch discussed in chapter 02 §2.2. TryReadCurrentSchemaVersion() (:198) reads PRAGMA user_version without creating or touching the database, which is what lets the pre-migration backup decide whether there is anything worth backing up.

One gap no test covers. If you author a step as new MigrationStep(CurrentSchemaVersion, …) and forget to bump CurrentSchemaVersion, you get two steps with the same version. Every test still passes — a fresh database runs both steps — but a plant database already at that version silently skips your migration. Check the number by eye. (Recorded in ARCHITECTURE.md.)

5.4 Backup and restore#

Ring/Services/DatabaseBackupService.cs (~646 lines) owns both directions.

Backup uses SQLite's online SQLiteConnection.BackupDatabase API, so the snapshot is transactionally consistent even while the live app holds connections open — and so pages still parked in the WAL are flushed into the destination rather than silently lost. It runs on startup and before every migration, into %ProgramData%\Ring\backups or the configured DatabaseMaintenance.BackupDirectory.

Restore is heavier, and the order matters:

1. Validate the source — PRAGMA integrity_check plus the expected tables being present.
2. Take an automatic safety backup of the current database to a .pre-restore-<ts>.bak sidecar as rollback insurance.
3. File.Copy, then delete stale -wal / -shm sidecars so the restored database does not open against an alien WAL frame.

The restore contract, and how it stays honest#

Two arrays in DatabaseBackupService define what a backup must contain:

The selection is one line: var requiredTables = userVersion >= CurrentSchemaVersion ? ExpectedTables : CoreTables; (DatabaseBackupService.cs:496).

Ring.Tests/DatabaseExpectedTablesDriftTests.cs runs the real initializer against a temp database, reads sqlite_master, and asserts a symmetric set-difference against ExpectedTables — so the list cannot drift from reality in either direction without a red test. Ring.Tests/DatabaseBackupServiceTests.cs holds a mirrored AllTables literal.

The full operator procedure is docs/production-readiness/09_DB_BACKUP_RESTORE.md; this book does not restate it.

What the backup does not cover#

The .db file, and nothing else. Not the three JSON stores under %LocalAppData%\Ring\ — tank_roster.json, operator-session.json, group_hardware_setup.json — and not the two JSONL journals under %ProgramData%\Ringwood\Ring\. The tank roster is the one that hurts: it determines which PLC index a storage-tank write goes to (chapter 04 §4.3). Copy that directory by hand before reimaging a plant PC. (docs/CONFIG_AND_STARTUP.md §4.)

5.5 Health, the maintenance latch, and writer quiesce#

Three cooperating pieces keep the file safe while it is being operated on.

DatabaseHealthService#

Ring/Services/DatabaseHealthService.cs runs the three operations a live plant database needs:

1. Startup PRAGMA integrity_check — early warning of corruption from a bad shutdown, disk bit-rot or tampering. Failures are logged and surfaced; the policy choice is the operator's.
2. Startup online backup into the configured directory, plus a retention prune to the newest DatabaseMaintenance.RetentionCount auto-files.
3. Shutdown PRAGMA wal_checkpoint(TRUNCATE) — flushes every dirty WAL frame into the main file and zeroes the WAL, so the sidecar is safe to delete after the process exits. Without it, a power cut between SQLite's automatic checkpoint (every 1000 dirty pages) and the next start can lose committed rows that an operator then "cleans up" by deleting the WAL.

It also runs background maintenance on a timer and has a bounded 10 s join in StopBackgroundMaintenance — the model the polling coordinator's drain copies.

DatabaseWriterQuiesce#

Ring/Services/DatabaseWriterQuiesce.cs stops — and puts back — every background writer that can touch the database, so a file swap (restore) or delete (factory reset) cannot race a timer tick into the file being replaced.

Its existence is a lesson about duplicated lists. Both dialogs previously carried a byte-identical inline list of four services, and that list was incomplete: BatchStepsPoller, InventoryEventCaptureService, AlarmAlarmNumberPlcService and ReportSchedulerService all kept running. "Two copies of an incomplete list is exactly the shape that stays incomplete, so there is now one list, in one place, used by both dialogs."

Two design properties:

• Best-effort, per writer. Every stop and every resume is individually guarded: a writer that fails to stop must never block the restore, and a writer that fails to resume must never stop the others coming back.
• Resume fidelity. Each entry reports whether it was actually running before the stop, and ResumeAll only restarts what it actually stopped — so quiescing never silently arms a recorder the site had deliberately left off. Resume runs in reverse order.

On the PLC side it uses PlcPollingCoordinator.PauseDatabaseWriters(), which stops only BatchStepsPoller and leaves the read-only pollers live, so the operator's screens are not blacked out for the duration (chapter 03 §3.2).

DatabaseMaintenanceLatch#

Ring/Services/DatabaseMaintenanceLatch.cs is a process-wide "the database file is being swapped or deleted right now — do not touch it" flag, held by DatabaseWriterQuiesce.StopAll and released only by ResumeAll.

The reason it is needed on top of the quiesce is precise and worth internalising: the restore and reset dialogs put a modal MessageBox up around the swap, and a modal message box runs a nested dispatcher pump. Anything already scheduled on the dispatcher keeps running inside that window, and two things there can undo the quiesce:

1. The PLC auto-reprobe. MainWindow.PlcAutoReprobeTick (30 s) marshals StartPlcMonitoring() onto the dispatcher, which runs PlcPollingCoordinator.Start() — clearing the batch-steps pause — and restarts InventoryEventCaptureService and ViscometerLiveRecorder.
2. Dashboard refresh ticks. DashboardView's 4 s and 60 s DispatcherTimers run repository reads, and every repository call begins with EnsureTable(), which issues CREATE TABLE / CREATE INDEX IF NOT EXISTS. Opening a SQLite connection creates the file, so after a factory reset a read alone is enough to leave a partially-schema'd RingwoodDatabase.db on disk — which defeats the first-run detection SampleDbFileExistence exists to protect and hands the next launch a half-built database it will treat as an upgrade.

The latch is never released on the paths that end in Application.Current.Shutdown(), which is the point.

5.6 Batch lifecycle and history#

BatchStepsPoller observes current_step edges and drives Ring/Services/Batch/BatchLifecycleRecorder.cs, which on batch end calls BatchRepository.CompleteWithUsage. Completion and every IngredientUsage row commit in one transaction — if a usage insert fails, the batch stays Running rather than completing with missing usage. (Older runbooks describe this as an open "Phase A" gap; it shipped, and ARCHITECTURE.md records the closure.)

Two startup catch-ups run on a background task from PlcPollingCoordinator.Start() (:165-180):

• Orphan reconcile — closes Batch rows left Running by a crash or reboot, but never interrupts a batch the plant is genuinely running. Its branch selection is factored into the pure, testable PlcPollingCoordinator.DecideStartupReconcile (:459-492) with three rules: any active read wins (reconcile only the other tanks, or defer if the active tank cannot be attributed); any failed read defers (a failed read is never a confirmed idle); otherwise it needs BatchStepsPoller.IdleConfirmReadsRequired confirmed idle reads before closing anything. The debounce exists because a between-steps current_step == 0 flap during a restart used to mark the live batch Interrupted and spawn a duplicate row.
• Missed controller verdicts — BatchStampsBackfillService attaches the controller's retained batch summaries for batches that finished while Ring was down. Deliberately bulk-only, no indexed fallback: four 40-element fallbacks would be 160 sequential reads in one burst against a controller whose EIP session table is the scarce resource, and "a missed verdict is a cosmetic loss where a starved session table is not" (PlcPollingCoordinator.cs:224-233).

Related history services: Ring/Services/Batch/BatchHistoryBackfillService.cs (preview-first importer for the legacy six-month history), BatchStampsMatcher.cs (which controller stamps slot belongs to which Ring batch), and Ring/Services/LegacyRs360ImportService.cs.

5.7 The historian#

Ring/Services/ProcessHistorianService.cs is a singleton background service that, on a configurable cadence (DefaultCadenceMs = 60_000), reads the existing in-memory StorageTanksSnapshotHolder.Latest and persists one ProcessHistorySample row per installed tank. It performs no new PLC reads.

Two properties that make it safe to leave on:

• It samples only while PlcHeartbeatConnectionTracker.AllowHeavyPlcPolling() is true, so a starved or dead link never writes confident-looking frozen rows.
• Retention prune runs on a coarser schedule (RetentionPruneEveryTicks) — every 60 sample ticks at the default cadence, i.e. roughly hourly. The window is Database.ProcessHistorianRetentionDays in appsettings.json, editable on Setup → Database Backup → "Data retention" (default 365 days; 0 means keep forever). Corrected 2026-09-01: this used to be a hardcoded ProcessHistorianService.DefaultRetentionDays = 30 constant with no config surface at all — the constant still exists as the fallback when no setting is present, but the shipped app always passes the configured value. A change only takes effect after Ring is restarted (the value is baked into a DI-constructed singleton at startup, not re-read live). 0 never triggers a full-table scan — ApplyRetentionDays returns immediately on a non-positive value without opening a connection.
• Alarm history (PlcAlarmEvents, AlarmLifecycle) has an equivalent, independently-configurable window: AlarmSettings.AlarmHistoryDays (default raised 2026-09-01 from 30 to 365 days; 0 = keep forever), same Setup field. Unlike the historian value, this one is re-read on every alarm tick (AlarmAlarmNumberPlcService), so a change applies immediately — no restart required.

For what this cadence and retention window mean in rows-per-year, and why the row count is a query-latency non-issue, see chapter 03 §3.9 — not repeated here to keep the growth arithmetic in one place.

Ring/Services/RuntimeAccumulatorService.cs follows the same shape for accumulated equipment runtime, and Ring/Services/TankLevelTrendService.cs is an additive, read-only time-to-empty estimator over the same snapshots.

Ring/Services/PLC/TrendingDataService.cs is the exception in this family — it does construct its own PlcTagReader per non-snapshot tag on every 2 s tick (PollIntervalMs = 2000, :32).

5.8 The two append-only journals#

Neither lives in SQLite, and neither is covered by any backup.

UiAuditJournal — the tamper-evident operator trail#

Ring/Services/Audit/UiAuditJournal.cs, at %ProgramData%\Ringwood\Ring\ui-audit\ui-audit.jsonl (:1040-1041).

Integrity chain. Every record carries the previous record's hash, and VerifyChain re-derives every hash over the supplied files in order and reports the first break (:377-...). A separate UiAuditTailAnchor written beside the journal closes the one hole a backward chain cannot: a tip on disk that is behind the anchored tip means the journal was truncated (:71-90).

What the chain is and is not — the class states its own limits (:116-120): the hash is a plain, unkeyed SHA-256. It reliably detects accidental corruption and casual edits; someone with the same code and the patience to re-hash every downstream record can produce a self-consistent forgery. It is tamper-evident, not tamper-proof.

Writer-thread-only state means the chain is race-free without a lock: only the writer ever assigns a sequence number or a hash (:158-159).

Collaborators: UiActionAuditor (app-wide, observe-only capture of operator interactions), UiActionRecord / UiActionKinds, UiAuditQueryService (the UiAuditTrailScreen read model), and UiScreenResolver, which answers "which screen was the operator looking at?".

UiScreenResolver is part of the rename freeze. It writes GetType().Name into the hashed record payload, so renaming a screen class splits its identity across the rename boundary: old records still verify but no longer match live labels (ARCHITECTURE.md).

What the trail records for operators is docs/production-readiness/10_AUDIT_TRAIL.md.

PlcWriteEvidenceJournal#

Covered in chapter 04 §4.9, including the important scope limit: only writes that go through PlcSetpointWriteQueue produce evidence lines.

Ring/Services/Audit/IncidentExportBuilder.cs reads both journals back for an incident export.

5.9 Exports and reports#

Database export. Ring/Services/Export/DatabaseExportService.cs streams selected columns from a whitelisted table to CSV. The whitelist is Ring/Services/Export/DatabaseExportSchema.cs — a static IReadOnlyList<DatabaseExportTable> Tables (:19) with a lookup by name (:141). A table that is not in that list cannot be exported, which is the point: the export form takes a table name from the UI. Ring.Tests/DatabaseExportSchemaSmokeTests.cs exercises it.

Full database export. Ring/Services/Export/SqlDumpService.cs is a second, independent export path — schema-generic rather than whitelisted: it reads sqlite_master directly (skipping only internal sqlite_% bookkeeping objects such as sqlite_sequence), so it is not limited to DatabaseExportSchema's curated table list. DumpDatabase opens its own connection, applies RingwoodDbAccess.ApplyConcurrencyPragmas (§5.1) like every other repository, and runs the whole read — schema, then every table's rows — inside one read transaction, so the resulting .sql script is a consistent point-in-time snapshot even while the rest of the app keeps writing on other connections. The output is streamed to a .partial sibling file, FileStream.Flush(true)-fsynced, and only then renamed to the final <name>.sql name — write-to-temp-then-atomic-rename, so a crash mid-export can leave at most a stale .partial file, never a final-named file that looks complete but is truncated. Ring.Tests/SqlDumpServiceTests.cs covers it, including the interrupted-before-rename case.

This is a separate guarantee from the live database's own durability: RingwoodDbAccess.ApplyConcurrencyPragmas (§5.1) already configures every SQLite connection in the app with journal_mode = WAL and synchronous = FULL — fsync on every commit, stronger than the NORMAL floor typically recommended for WAL — which is what protects RingwoodDatabase.db itself against corruption or a lost commit on a power outage. SqlDumpService inherits that same protection for its own connection, but the two exist for different failure modes: the pragmas protect the live file continuously; the .sql export is a one-shot, portable snapshot an operator or IT can hand off or replay into a brand-new database. Ring.Tests/RingwoodDbAccessTests.cs is regression coverage added alongside this work for the pragma configuration, which previously had none.

Reports. Ring/Services/Reports/ holds the query services (read-only aggregations over persisted data), the pure calculators, and the *ReportDocument builders that produce WPF FlowDocuments for on-screen preview and PrintDialog output. PdfExportService renders a FlowDocument to PDF. ReportSchedulerService (1-minute timer) drives subscription emails via ScheduledReportRenderer, with due-math in the pure ReportScheduleCalculator. Chapter 07 catalogues them.

Diagnostics. Ring/Services/DiagnosticBundleService.cs builds a one-click support zip.

Plant profile. Ring/Services/PlantProfileService.cs builds a versioned *.ringprofile.json commissioning export. Note from docs/CONFIG_AND_STARTUP.md §3 that a plant-profile import deliberately does not carry the PLC IP or ReadOnlyMode.

5.10 Adding a table — the whole checklist#

Reproduced from ARCHITECTURE.md because it is the one place a partial change silently succeeds:

1. Write IXRepository + XRepository with the per-call connection + ApplyConcurrencyPragmas shape.
2. Expose public static void ApplySchema(SQLiteConnection); have EnsureTable() call it.
3. Append a MigrationStep at the end of GetMigrationSteps() that calls XRepository.ApplySchema.
4. Bump CurrentSchemaVersion (DatabaseInitializer.cs:743). Three tests fail if you forget in the ordinary case — DatabaseSchemaMigrationTests.CurrentSchemaVersion_Matches_HighestMigrationStep (:47), DatabaseExpectedTablesDriftTests.CurrentSchemaVersion_MatchesInitializerLatest (:120), and DatabaseSchemaMigrationTests.Wave6Migrations_CreateBothTables_AndStampCurrent, which asserts the version as a literal (:139-140) and therefore has to be edited too. But see the same-version gap above, which no test catches.
5. Add the table name to ExpectedTables in DatabaseBackupService (:43). DatabaseExpectedTablesDriftTests.ExpectedTablesExactlyMatchesWhatInitializeActuallyCreates (:93) fails in both directions if this and step 3 disagree. You do not normally touch CoreTables (:144).
6. Mirror it in the AllTables literal in Ring.Tests/DatabaseBackupServiceTests.cs:38.
7. Register the repository in ServiceCollectionExtensions.
8. Register both new .cs files in Ring/Ring.csproj (chapter 08).
9. Have the repository's catch blocks call RepositoryReadScope.ReportFailure (§5.2) so a failed read is distinguishable from an empty result.
10. If a background timer will write the table, add it to DatabaseWriterQuiesce (§5.5).

The step-by-step version, with the reasoning inline, is chapter 10 §10.5.

Next: 06 — UI Architecture.

Verified against#

Every claim in this chapter was read out of these files on 2026-09-01:

Ring/Database/ — DatabaseInitializer.cs, RingwoodDbAccess.cs, RepositoryReadScope.cs, Interfaces/ and Repositories/ (listings), Repositories/BatchRepository.cs · Ring/Services/DatabaseBackupService.cs, DatabaseHealthService.cs, DatabaseWriterQuiesce.cs, DatabaseMaintenanceLatch.cs, ProcessHistorianService.cs, RuntimeAccumulatorService.cs, DiagnosticBundleService.cs, PlantProfileService.cs · Ring/Services/Audit/UiAuditJournal.cs, IncidentExportBuilder.cs · Ring/Services/Export/DatabaseExportSchema.cs, DatabaseExportService.cs, SqlDumpService.cs · Ring/Services/Batch/ (listing) · Ring/Services/PLC/PlcPollingCoordinator.cs · Ring.Tests/DatabaseExpectedTablesDriftTests.cs, DatabaseBackupServiceTests.cs, DatabaseSchemaMigrationTests.cs, SqlDumpServiceTests.cs, RingwoodDbAccessTests.cs · docs/CONFIG_AND_STARTUP.md · ARCHITECTURE.md