• ARCH-001

  reviewed 1. Local persistence subsystem UID: ARCH-001 RELATIONS (Parent):

  • SRS-001 First-launch database initialisation (derivedFrom)

  RELATIONS (Child):

  • IT-001 First-launch DB initialisation (verifies)

  STATEMENT:

  The local persistence subsystem owns the application's local database — a SQLite file encrypted at rest via SQLCipher, located under the configured AppData path — applies schema migrations via Drift on first open, and exposes a typed API to the application layer for the clinic, patient, and recording domain entities.

  RATIONALE:

  Hexagonal layer realising the broader local persistence concern. Encapsulates file lifecycle, encryption (SQLCipher), schema migration (Drift), and the typed domain-API contract — keeps the rest of the application unaware of storage details.

  TYPE:

  subsystem

  SAFETY_CLASS:

  B

  ACTIVE:

  true

  REVIEWED_HASH:

  1910b72998ec774f37586ae58434e21b6689aab3a07dcda6d78d70caf0cdf592

  REVIEWED_BY:

  @DougYoungberg

IT-001

reviewedPassed 1. First-launch DB initialisation UID: IT-001 RELATIONS (Parent):

• ARCH-001 Local persistence subsystem (verifies)

STATEMENT:

Instantiate AppDatabase against a temporary filesystem path at which no database file exists. Trigger the initialisation pathway by performing any query that forces Drift's onCreate migration to run. Inspect the resulting state of the database on disk and of the seeded tables.

TEST_METHOD:

automated

TEST_TOOL:

flutter_test

ACCEPTANCE_CRITERIA:

The database file exists at the temporary path after the first query. PRAGMA user_version on the opened database equals the value of AppDatabase.schemaVersion declared in application code. The clinics table contains at least one row whose origin is "local", confirming that the bootstrap seed step executed.

ACTIVE:

true

REVIEWED_HASH:

d71dca889c0c0d8ce35e6f03e6a56b46c86ca82d2348bf5560d63d65f7c819b5

REVIEWED_BY:

@DougYoungberg

NOTES:

Test harness couples to the StrictDoc item via tags: ['IT-001'] on the flutter_test test entry. Test file location: test/integration/first_launch_db_initialization_integration_test.dart. Verifies the architectural integration of the local persistence subsystem (ARCH-001); contributes evidence to SRS-001 / SYS-001 indirectly via the typed-link graph.

• ARCH-002

  unreviewed 2. Local database encryption UID: ARCH-002 RELATIONS (Parent):

  • SRS-002 Local database confidentiality at rest (derivedFrom)

  RELATIONS (Child):

  • IT-002 Database file is encrypted at rest (verifies)

  STATEMENT:

  The local SQLite database is encrypted on disk via the SQLCipher extension. The encryption key is held by flutter_secure_storage, which on Windows persists it as an entry in a DPAPI-encrypted file (flutter_secure_storage.dat) under the user's local app-data directory. On application bootstrap, the key is retrieved from secure storage and generated-and-persisted on first launch if absent. On every database connection open, the database connection service applies the key as the first statement (PRAGMA key) before any other operation; SQLCipher then transparently encrypts and decrypts SQLite pages.

  RATIONALE:

  Page-level encryption via SQLCipher avoids per-record encryption logic in application code. Holding the key in flutter_secure_storage (rather than alongside the database file) ensures a copy of the database file alone cannot be opened. Bootstrap-time key generation lets the application be self-installing on a fresh workstation without a manual setup step.

  TYPE:

  data

  ACTIVE:

  true

IT-002

unreviewedPassed 2. Database file is encrypted at rest UID: IT-002 RELATIONS (Parent):

• ARCH-002 Local database encryption (verifies)

STATEMENT:

Initialise an AppDatabase against a temporary filesystem path through the production openEncryptedConnection pathway with a known test key. Write a canonical row through the typed clinic API and close the database. Attempt to open the resulting file with the plain sqlite3 driver (no credential supplied) and read any user table. Attempt to open the same file through the production pathway with an incorrect key and read any user table. Then re-open the file through the production pathway with the correct key and read the canonical row.

TEST_METHOD:

automated

TEST_TOOL:

flutter_test

ACCEPTANCE_CRITERIA:

The unkeyed open and the wrong-key open both return a SQLCipher error (typically "file is not a database" or "file is encrypted"), demonstrating the file cannot be read with any arbitrary key. The correct-key open succeeds and the canonical row written before close is readable.

ACTIVE:

true

NOTES:

Test name carries the UID prefix (IT-002) so the Flutter/JUnit join channel resolves. Test file location: test/integration/database_encryption_at_rest_integration_test.dart (distinct from test/integration/encryption_test.dart, which covers EncryptionService primitives only).