​
ADR-0049: Device data lifecycle — per-user local database, lock-vs-wipe policy, encryption at rest

​
Status

Accepted (pending FieldForce Phase 1 implementation)

​
Tags

fieldforce, digital-worker, mobile, flutter, security, privacy, powersync, sqlite, sqlcipher, offboarding, offline

​
Decision

The local PowerSync SQLite database is treated as a re-syncable cache with a defined lifecycle, not an implicit forever-store.
  • Per-user database file. The SQLite file is keyed by user id (e.g. powersync-<userId>.db). Signing in as a different user opens a different file; no two users ever share a local database. Field crews share devices — this is assumed, not exceptional.
  • Lock vs wipe. Session end is not one event; the policy distinguishes three cases (extending the offline-grace model, ADR-0041):
    1. Passive refresh-token expiry (e.g. a long offline stretch): lock, retain. The UI hard-locks, but the database — including queued unsynced writes — is kept. Re-authenticating as the same user resumes; queued uploads then flow normally.
    2. Explicit refresh rejection (server refuses: user revoked / terminated / device blocked): lock + wipe. Delete the user’s database file, tokens in secure storage, and cached flags.
    3. Explicit user logout: wipe — but first warn if unsynced writes are pending (“N unsynced changes will be lost”).
  • Encryption at rest from day one. The local database uses SQLCipher via powersync_sqlcipher (a drop-in database-factory swap). A random per-device key is generated on first launch and stored in flutter_secure_storage (Keychain/Keystore). Tokens were already secure-storage-only (ADR-0041); this closes the same gap for the synced data itself.
  • Accepted residual risk: a revoked worker’s device that never reconnects keeps its last-synced data locally. Mitigations are encryption at rest plus tightly scoped Sync Rules buckets (ADR-0040 — a device only ever held its own user/team slice). Remote wipe via MDM is out of scope for MVP (the fleet is not MDM-managed).

​
Why

Field devices get lost, stolen, shared, and reassigned — the local database’s lifecycle is a security property, and every piece here is trivial now but painful later: per-user filenames become a data migration once a shared DB ships; encryption retrofit leaves a fleet of plaintext devices in the field during the transition; wipe semantics bolted on after launch meet real queued data they were never designed around. The must-record nuance is the lock-vs-wipe split. The two naive policies each fail in opposite directions. Wipe on any session end destroys a field worker’s queued offline work on passive token expiry — the worst possible data loss for an offline-first product, triggered by nothing but time passing in a dead zone. Retain on any session end leaks business data to offboarded staff whose refresh was explicitly rejected. The resolution is that passive expiry is a liveness event (lock, retain, resume) while explicit rejection is a trust event (lock, wipe) — the server said no, so the cache’s owner is no longer entitled to it. A future implementer collapsing these into one code path breaks one side or the other. Per-user DB files also make wipe surgical (delete one file), keep PowerSync sync state consistent per identity, and make the “second worker borrows the device” case safe by construction rather than by query discipline. Rejected alternatives:
  • Single shared database file for whoever is signed in. Cross-user data leakage on shared devices; entangles PowerSync sync state across identities. Rejected.
  • Wipe on every session end (including passive expiry). Destroys queued offline work; anti-offline-first. Rejected.
  • Retain on explicit rejection. Leaks data to offboarded staff. Rejected.
  • Plaintext SQLite, rely on the OS app sandbox. Fails on rooted/extracted devices and varies by OEM; field hardware is budget Android. Rejected — SQLCipher is a factory swap, and since local data is a re-syncable cache the cost is near zero now versus a fleet-wide plaintext window later.
  • MDM remote wipe. Assumes fleet management that doesn’t exist for MVP. Deferred; revisit if the fleet becomes managed.

​
Consequences

  • The database factory takes the user id (filename) and the encryption key (secure storage) at open time — both flow from the composition root like all config (CLAUDE.md §11).
  • Sign-out and hard-lock flows gain defined data behavior; the “unsynced changes will be lost” warning is a v1 UX requirement on explicit logout.
  • Losing the device-local encryption key (secure-storage wipe, OS restore) is recoverable by design: delete the database and re-sync — the local store is a cache; unsynced work on that device is lost, which is the same exposure any device loss has.
  • powersync_sqlcipher replaces the plain powersync package as the database dependency in core_database.

​
Rules for agents

  • Key the local database filename by user id; never share one database file across users.
  • Passive refresh-token expiry → lock and RETAIN the database (queued writes survive). Explicit refresh rejection → lock and WIPE (database file, tokens, cached flags). Explicit logout → warn if unsynced writes exist, then wipe.
  • Local database is SQLCipher-encrypted; the key lives in flutter_secure_storage and is generated per device. Never store the key elsewhere or derive it from user data.
  • Treat the local database as a re-syncable cache: recovery from corruption or key loss is delete + re-sync, never a hand-written repair path.