[EL-27] Build SQLCipher diagnostics, encrypted backup, and recovery UX

[sodejm]

Catalog ID: EL-27

Public issue: #123

Reviewed baseline: 71abaf4067d4118f28910a22c20524b4e0282b28

Core objective: Provide fail-closed database diagnostics, encrypted backup,
restore validation, and recovery guidance without exposing key material or
replacing a healthy database unsafely.

Priority / phase: P1 / Existing-module desktop parity

Publication status: Published as #123.

Dependency mapping

• Blocks: #130, #129
• Depends On: #103, #105, #106, #109, Improvement: add SQLCipher and keyring first-run diagnostics #13
• Relates To: Release process: create security and interoperability evidence checklist #19

UX/UI requirements

• Present health, schema version, last verified backup, recovery options, and
  coded remediation without revealing sensitive paths or contents.
• Require explicit confirmation and preflight validation for restore.
• Distinguish backup creation, verification, rehearsal, and destructive
  replacement.
• Preserve recovery instructions when the sidecar cannot start normally.

Public interfaces

• Add DatabaseHealthSummary, BackupRequest, BackupArtifact,
  RestorePreflight, and RecoveryAction.
• Expose key presence/access status, never key values.
• Restore uses opaque grants, expected fingerprints, and explicit confirmation
  tokens.

Underlying app changes

• Add SQLCipher integrity checks, encrypted backup, restore preflight, schema
  compatibility checks, and atomic database replacement.
• Preserve the previous database as a recoverable artifact until post-restore
  verification succeeds.
• Add coded startup recovery states available when feature APIs are disabled.

Acceptance criteria

• Health checks detect unavailable keys, invalid encryption, corruption,
  and incompatible schema versions.
• Backups are encrypted, verified, and atomically published.
• Restore preflight checks integrity, format, version, key, size, and free
  space without mutation.
• Failed restore leaves the active database unchanged.
• Successful restore supports deterministic rollback.
• No key, database payload, or sensitive path appears in renderer state,
  logs, telemetry, or crash reports.
• Recovery flows are keyboard accessible and do not rely on color.
• Tests cover wrong key, truncation, downgrade, low space, cancellation,
  crash, and rollback.

Security mitigations and rejected anti-patterns

• Applies TM-S01, TM-F01, TM-F02, TM-A02, TM-C01, and TM-O02.
• Reject plaintext backups, renderer key access, unverified restore, in-place
  overwrite, implicit downgrade, browser storage, Electron safeStorage, and
  plaintext Linux fallback.

Workspace mapping

• Exclusive owned paths: desktop/src/features/database-health/**,
  src/ancestryllm/api/routes/database_health/**,
  src/ancestryllm/services/database_recovery/**,
  tests/services/database_recovery/**, and
  desktop/tests/database-health/**.
• Shared-file rule: Migration registry, startup bootstrap, generated
  contracts, dependencies, and lockfiles require coordinator edits.
• Base rule: Branch from updated main after all dependencies merge.
• Branch: feature/electron-el-27-database-recovery
• Worktree: /Users/justinsoderberg/Development/worktrees/ancestryllm-electron-el-27-database-recovery
• Validation: make test; make lint; make typecheck; make security;
  pnpm --dir desktop test; pnpm --dir desktop typecheck

[sodejm]

Recommended Codex configuration

• Planning: gpt-5.6-sol with xhigh reasoning
• Implementation: gpt-5.6-sol with max reasoning
• Rationale: This is release-, sandbox-, plugin-, backup-, architecture-, or writeback-critical work where a subtle error could compromise security, user data, or release integrity; maximum implementation verification is warranted.
• Delivery: Use test-case-driven development: translate the acceptance criteria into failing or characterization tests first, implement incrementally, then run the applicable tests, lint, typecheck, security, integration, and hosted gates.