Recommendation: Bite 31 should be Production Data Protection and Recovery Hardening

The next highest-value problem is not PostgreSQL, i18n, SaaS licensing, or device authorization. It is ensuring that the Production data you now depend on can survive operator mistakes, software failures, disk/server loss, and a recovery event.

Issue #840 explicitly asks for periodic Production backups and remains open. The current implementation is already a good foundation: every Production deployment takes a consistent SQLite .backup, runs PRAGMA integrity_check, runs PRAGMA foreign_key_check, copies the validated database out of the container, and prints its SHA-256.

But there are three important gaps:

• backups happen principally as part of deployments, not on an enforced daily schedule;
• backups remain on the same server, so they do not protect against server/disk loss;
• there is no canonical Production restore/recovery workflow proving that those backups can actually recover ERS.

That makes #840 more important, in my view, than #838's PostgreSQL migration. #838 is motivated by anticipated concurrency/performance growth, but there is no evidence in the current backlog of an existing Production performance failure. By contrast, the backup gap exists today and applies to every Production write.

A useful rule here is:

Before making the database more sophisticated, make the current database recoverable.

This also materially reduces the risk of the eventual SQLite → PostgreSQL migration.

---

1. Next highest-value problem

I would rank the remaining roadmap approximately this way:

Priority	Area	Existing issues	Why
1	Production backup + recovery	#840	Existing Production risk; relatively bounded work; protects every other feature
2	Business-rule integrity audit/hardening	#11	Some intended service rules remain unenforced; should be decomposed before implementation
3	Database scalability / PostgreSQL	#28, #838	Important before significant concurrency growth, but larger and riskier than backup hardening
4	Authentication device/session security	#841	IP/User-Agent exist; device identity/authorization does not. #841 explicitly proposes that expansion.
5	SaaS user definition/metering	#842	Bite 30 gives this a sound Account/Actor foundation; business rules still need definition
6	SaaS licensing	#843	Depends logically on #842's definition of billable/active users
7	i18n	#304, #621–#625	Genuine product gap but not currently a Production integrity risk
8	Smaller domain/UX gaps	#128, #181	Useful but narrower
9	OpenAPI/runtime contract cleanup	#26	Technical consistency work; lower immediate Production value

I would therefore name the next parent issue:

Bite 31 — Production Data Protection and Recovery Hardening

Its central outcome should be stronger than “we run backups”:

ERS Production data is backed up automatically, retained safely, copied outside the Production server, continuously verifiable, and recoverable through a rehearsed procedure without risking the live database.

---

2. Proposed Bite 31 scope

Bite 31 should own #840 and establish an explicit Production recovery contract.

The intended recovery objective should be:

RPO: no more than 24 hours of Production data loss under the normal scheduled-backup policy.

RTO: initially measured rather than invented. Bite 31 should perform an actual recovery rehearsal, record the elapsed recovery time, and establish the first evidence-based RTO target from that result.

The scope should include scheduled verified SQLite backups; deterministic metadata/checksums; retention management; an off-server copy; freshness/failure verification; non-destructive restore verification; a guarded Production recovery procedure; and repository-owned operational documentation.

I would specifically keep PostgreSQL migration out of Bite 31. Bite 31 should make the current SQLite system operationally safe, while designing the backup-policy layer so that a later DB implementation can replace SQLite-specific mechanics.

Likewise, Bite 31 should not add a backup-management UI. This is infrastructure/recovery functionality, not a Tenant or Application Administrator application workflow.

---

3. Smaller sub-bites

I recommend five small deliveries.

Bite 31.1 — Canonical Production Backup Artifact and Policy

Turn the existing server-backup mechanism into the authoritative backup primitive.

A successful backup should produce the SQLite snapshot plus machine-readable evidence containing at least UTC creation time, environment, database SHA-256, size, source server Git commit/tree, latest applied migration, integrity-check result, foreign-key-check result, and backup format/version.

A failed verification must never be treated as a successful backup.

This slice should also define retention policy. A reasonable initial policy is 30 daily + 12 weekly backups, but I would make that a documented/configurable policy rather than scattering those numbers through scripts.

Bite 31.2 — Scheduled Production Backups and Retention

Install a repository-owned server-side schedule.

I prefer a systemd timer over a bare cron entry: it gives us explicit service status, logs, failure state, and Persistent=true behavior if a scheduled run is missed while the server is unavailable.

There should be repository targets similar to:

server-prod-backup-install
server-prod-backup-status
server-prod-backup-run
server-prod-backup-freshness-check

The schedule should invoke the exact same canonical backup implementation used manually and during deployment—not a second backup script.

Bite 31.3 — Restore Verification and Recovery Rehearsal

This is the slice that turns “backup” into “recoverable backup.”

Introduce a non-destructive verifier that restores a selected backup into an isolated temporary SQLite location/volume and verifies integrity, foreign keys, migration state, and ERS database expectations.

It must not replace Production data.

Then establish a recovery rehearsal that records backup identity, start/end timestamps, verification result, and elapsed recovery time.

Production data should not be copied into ordinary Development/Test environments merely to perform this test. Recovery verification can happen in an isolated, non-routable recovery context.

Bite 31.4 — Off-Server Backup Replication

A backup stored next to /opt/EnterpriseRemoteSystems/production is useful for application/operator recovery, but it is not disaster recovery.

At least one verified copy needs to leave the Production server.

Your existing Hetzner documentation already contemplated Storage Box/S3-style off-server backup, but never implemented it. I would use a provider-neutral configuration contract and select the concrete remote before implementation.

For a Hetzner-hosted ERS server, a Hetzner Storage Box over SSH/SFTP/rsync would be a sensible operational choice, but the repository should not hard-code Hetzner credentials or account identifiers.

Remote replication must verify the copied object's checksum before the backup is considered protected off-site.

Bite 31.5 — Recovery Operations, Evidence, and Production Gate

Finish with operational hardening:

• explicit stale/missing-backup detection;
• documented break-glass Production restore process;
• strong confirmation before any destructive restore;
• backup/recovery evidence;
• disaster-recovery runbook;
• one complete Production backup + off-site replication + non-destructive restore verification after deployment.

At this point #840 can close.

---

4. Dependencies and risks

The sub-bites should execute in order 31.1 → 31.2 → 31.3 → 31.4 → 31.5. 31.4 additionally needs one external decision: the off-server storage target and credentials mechanism.

The major engineering risks are worth treating as explicit Bite 31 invariants.

SQLite/WAL consistency: never copy the live .db file directly. The existing .backup usage is correct and should remain the canonical mechanism.

False confidence: a checksum proves that a file did not change; it does not prove that it is a recoverable database. Integrity, foreign-key and restore verification are required.

Same-host backups: these do not protect against disk/server destruction.

Retention mistakes: rotation must never delete the last valid backups because the newest run failed. Create and verify first; rotate only after success.

Production overwrite: restore tooling is inherently dangerous. Non-destructive restore verification should be routine; actual Production replacement should require explicit break-glass confirmation.

Secrets: off-site backup credentials cannot live in Git, backup manifests, logs, or GitHub artifacts.

Production-data exposure: do not routinely copy Production data into Development, Test, CI artifacts, or Playwright fixtures.

Schema/version drift: recovery tooling must know what migration state a backup contains and whether the running ERS revision can safely consume it.

Disk exhaustion: both backup creation and retention need free-space awareness.

Future PostgreSQL: separate the policy—schedule, retention, evidence, freshness, off-site protection—from the SQLite-specific snapshot operation. That reduces throwaway work when #28/#838 happens.

---

5. Acceptance criteria

I would not call Bite 31 complete until all of these are true:

Area	Acceptance criterion
Scheduling	Production automatically produces a backup at least once every 24 hours without requiring a deployment or operator action
Consistency	Every accepted SQLite backup was created using a live-safe SQLite backup mechanism
Integrity	Every accepted backup passes PRAGMA integrity_check and PRAGMA foreign_key_check
Identity	Every backup has machine-readable UTC timestamp, environment, SHA-256, size, Git revision/tree and schema/migration identity
Atomicity	Failed/incomplete backup files are never advertised as successful
Retention	A deterministic documented retention policy is automatically enforced and tested
Off-site	At least one verified copy of each required Production backup exists outside the Production server
Remote integrity	The off-site copy's checksum matches the locally verified backup
Freshness	An automated command returns failure when no sufficiently recent successful Production backup exists
Restore	A selected backup can be restored and verified in isolation without modifying the live Production database
Recovery evidence	At least one complete recovery rehearsal has passed and recorded elapsed recovery time
Safety	Actual Production restore requires an explicit break-glass path and cannot happen accidentally through a normal deployment
Privacy	Production DB contents never appear in CI artifacts or logs
Existing deployment	The current pre-migration Production backup/release gate continues to work
Regression	make local-check, CI, Test deployment and Production release gates all pass

I would set Bite 31's headline business acceptance criterion to:

Destroying the live Production database must no longer imply losing all data since the previous software deployment.

And its disaster criterion:

Loss of the Production server must not imply loss of the only usable database backup.

---

6. Automated and manual verification strategy

Automated verification

This Bite should be unusually heavy on infrastructure tests rather than browser E2E.

LOCAL/CI should test backup-manifest generation, checksum validation, retention selection with deterministic dates, rejection of corrupt SQLite files, rejection of foreign-key-invalid backups, stale-backup detection, and restoration into an isolated temporary SQLite database.

make local-check should include all repository-pure tests. The existing migration rehearsal must remain green.

For Test deployment, I would exercise the complete backup and isolated-restore workflow against Test data. The normal frontend/unit/Playwright suite should still run as a regression gate, but we do not need a new browser test merely to prove a server backup happened.

For Production, promotion should verify the existing deployment backup, install/check the periodic scheduler, execute one actual scheduled-equivalent backup, verify off-site replication, and perform the non-destructive restore verification.

Manual verification

This is one of the rare ERS features where UI-first testing is not appropriate. The functionality intentionally has no application UI.

The manual suite should therefore explicitly identify the operator as the Production server/deployment operator, state ENV=production, and use repository Make targets rather than ad hoc shell commands wherever possible.

I would require manual scenarios for:

1. produce a Production backup and record filename, timestamp, manifest and SHA;
2. verify the scheduler is installed/enabled and next run is visible;
3. verify the freshness command passes;
4. verify the off-site object exists and checksum matches;
5. run an isolated restore verification and prove the live Production application remains operational throughout;
6. intentionally test a corrupt copy and confirm restore verification refuses it;
7. verify an old/stale test fixture causes freshness validation to fail;
8. inspect the recovery runbook and prove the operator can identify exactly which backup to restore;
9. run normal Production smoke checks after all verification.

An actual destructive Production restore should not be part of routine promotion testing. The safe rehearsal must prove the mechanics without risking live Production.

---

7. Cleanup before implementation

There is one cleanup item I consider a prerequisite rather than optional polish.

Your deployment documentation contains three competing server layouts:

/opt/EnterpriseRemoteSystems/{development,test,production}   ← actual implementation

/opt/enterpriseremotesystems/{dev,tst,prd}                  ← stale Server Setup doc

/opt/collaborator-accounting/{dev,tst,prd}                  ← stale Infrastructure-Hetzner doc

The Makefile and current GitHub deployment workflow use /opt/EnterpriseRemoteSystems/.... The older backup example in Infrastructure-Hetzner.md is therefore dangerous as implementation guidance: it uses the old path and directly references a host data/app.db, while current ERS stores SQLite in the Docker backend volume and correctly backs it up through the running container.

I would make documentation normalization part of 31.1 before installing any scheduler.

I would also refine #840. Its present body says only “daily/weekly backup,” which is too weak for the actual requirement. Keep #840 as the product use case, create Bite 31 as the parent implementation issue, and make 31.1–31.5 its subissues.

I would not combine #28/#838 with Bite 31. The current Production backend is explicitly SQLite-only, and #838 remains an independent performance/scalability request. After Bite 31, PostgreSQL becomes substantially safer to tackle because we will already have formalized backup policy, recovery evidence and disaster-recovery expectations.

Where I would take the roadmap after Bite 31

My current recommended sequence is:

Bite 31  Production Data Protection and Recovery Hardening
   ↓
Bite 32  Business-Rule Integrity Audit and Enforcement (#11 decomposed)
   ↓
Bite 33  Database Scalability / PostgreSQL Foundation (#28 + #838)
   ↓
Bite 34  Device and Session Security (#841)
   ↓
Bite 35  SaaS User Definition and Usage Metering (#842)
   ↓
Bite 36  SaaS Licensing / Renewal Management (#843)

I would keep i18n and the smaller #128/#181 UX/domain items available to move earlier if actual users make them more valuable than that technical sequence.

The key recommendation is therefore: do not start Bite 31 by adding another business feature. Now that ERS is genuinely in Production, first make its Production data independently recoverable.