WS-D3 Whole-DB at-rest encryption (SQLCipher) + migration + 'stolen DB reveals nothing' test [HEADLINE] #299

New Issue

2026-07-31T18:14:27Z

KofTwentyTwo commented

2026-07-31 18:14:27 +00:00

(Migrated from github.com)

Encrypt the entire file incl. FTS shadow tables with a keyring-sealed key; sqlcipher_export migration from the plaintext DB + atomic swap + backup. Search works unchanged against an opaque file. New dep libsqlite3-sys/SQLCipher (vendored-openssl). Size L. Dep: WS-D2.

Roadmap-to-9/10 (design: scratchpad/design/). Serial PROTO queue via WS-A. Brief at dispatch. DoD per docs/STORY-WORKFLOW.md.

Encrypt the entire file incl. FTS shadow tables with a keyring-sealed key; sqlcipher_export migration from the plaintext DB + atomic swap + backup. Search works unchanged against an opaque file. New dep libsqlite3-sys/SQLCipher (vendored-openssl). Size L. Dep: WS-D2. Roadmap-to-9/10 (design: scratchpad/design/). Serial PROTO queue via WS-A. Brief at dispatch. DoD per docs/STORY-WORKFLOW.md.

KofTwentyTwo commented

2026-07-31 18:39:50 +00:00

(Migrated from github.com)

WS-D2 spike (#298) outcome — decisive, from real builds:

APPROACH: stay on sqlx 0.8; add libsqlite3-sys with bundled-sqlcipher-vendored-openssl (unifies onto one SQLite, no symbol clash); sqlx pre-seeds PRAGMA key correctly, so the store options chain gains .pragma("key", ). NO query rewrites, NO rusqlite migration. => WS-D3 is MEDIUM.
CRITICAL must-fix IN THE SAME CHANGE: recovery.rs::has_corrupted_sqlite_header checks the literal 'SQLite format 3\0' header; an encrypted DB has a random header, so it would flag every healthy encrypted DB as corrupt and trigger destructive salvage that DELETES the live DB. Fix via cipher_plaintext_header_size (+ explicit cipher_salt) so a real plaintext header survives, or replace with a keyed quick_check. Also key the recovery.rs second/salvage pool with the same PRAGMA key.
KDF: seal with the raw full-entropy keyring key (recommended; no passphrase/PBKDF2) — mirror the existing storage/WORM/ledger key provisioning via SecretManager/vault.rs.
ENV PREREQ (dev box): bundled-sqlcipher-vendored-openssl needs a complete Perl for the OpenSSL C build; the dev box's msys2 Perl is incomplete. Install Strawberry Perl ahead of msys on PATH before this story compiles locally; document in DEVELOPING-IN-RUSTROVER.md. CI (ubuntu/macos/windows-latest) is fine.
MIGRATION: PO decision at dispatch — migrate existing plaintext DB via backup+sqlcipher_export (non-destructive) vs start-new-encrypted (existing DBs are pre-alpha). Full spike: scratchpad/issues/ws-d2-spike-report.md.

WS-D2 spike (#298) outcome — decisive, from real builds: - APPROACH: stay on sqlx 0.8; add libsqlite3-sys with bundled-sqlcipher-vendored-openssl (unifies onto one SQLite, no symbol clash); sqlx pre-seeds PRAGMA key correctly, so the store options chain gains .pragma("key", <hex>). NO query rewrites, NO rusqlite migration. => WS-D3 is MEDIUM. - CRITICAL must-fix IN THE SAME CHANGE: recovery.rs::has_corrupted_sqlite_header checks the literal 'SQLite format 3\0' header; an encrypted DB has a random header, so it would flag every healthy encrypted DB as corrupt and trigger destructive salvage that DELETES the live DB. Fix via cipher_plaintext_header_size (+ explicit cipher_salt) so a real plaintext header survives, or replace with a keyed quick_check. Also key the recovery.rs second/salvage pool with the same PRAGMA key. - KDF: seal with the raw full-entropy keyring key (recommended; no passphrase/PBKDF2) — mirror the existing storage/WORM/ledger key provisioning via SecretManager/vault.rs. - ENV PREREQ (dev box): bundled-sqlcipher-vendored-openssl needs a complete Perl for the OpenSSL C build; the dev box's msys2 Perl is incomplete. Install Strawberry Perl ahead of msys on PATH before this story compiles locally; document in DEVELOPING-IN-RUSTROVER.md. CI (ubuntu/macos/windows-latest) is fine. - MIGRATION: PO decision at dispatch — migrate existing plaintext DB via backup+sqlcipher_export (non-destructive) vs start-new-encrypted (existing DBs are pre-alpha). Full spike: scratchpad/issues/ws-d2-spike-report.md.

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: KofTwentyTwo/nuncio#299