Skip to main content

ADR-0106: Encrypt investigation_persons natural-person PII at rest

Date: 2026-07-12 Status: Accepted Deciders: Adrian (Soft4U), Claude Opus 4.8

Context​

investigation_persons stores natural persons discovered during OSINT investigation (directors, UBOs) — first_name, last_name, date_of_birth, plus JSONB identification / phones / emails. These columns carry a PIIField annotation declaring they require encryption, yet nothing encrypts them:

  • first_name / last_name are plain Text; date_of_birth is a plain Date.
  • The cipher for the JSONB fields (app/pii/jsonb_crypto.py) has zero call sites.

The regenerated docs/pii_manifest.json (ADR-0104, M0-W4) honestly reports all of these as encryption_gaps rather than as controls. That honesty is correct, but the underlying gap is real: pii_manifest.json is the evidence artifact offered for bank security questionnaires and ISO 27001 audits, and a regulated customer will ask why three PII fields are unencrypted.

M0-W4 deliberately did not widen EncryptedText to these columns, because doing so naïvely would have been worse than the honest plaintext: the writers are raw SQL (four INSERT INTO investigation_persons in app/workflows/activities.py), and raw SQL bypasses the SQLAlchemy TypeDecorator. Widening the model type without fixing the writers would produce a column that reads as encrypted, is written as plaintext, and fails on decrypt — a silent, corrupting half-state.

A 2026-07-12 investigation resolved the apparent design forks:

  • All reads are ORM. There is no raw SELECT … FROM investigation_persons anywhere in app/; every consumer (dsr_service, goaml/mapper, goaml/export_service, case_transactions, person_index) reads via ORM attribute access, which decrypts transparently. → encryption is read-safe.
  • date_of_birth is never SQL-range-queried (only string-masked in portal.py), and the writer already coerces it to an ISO string (dob_iso) before insert. → storing it as an encrypted string loses no queryability.
  • activities.py is the sole writer. Switching those four statements to the ORM captures 100% of writes.
  • EncryptedText.impl = LargeBinary — an encrypted column is physically bytea, not text. So this is an encrypt-in-place type migration (text/date → bytea), the same shape the codebase already ran for users.email in migrations 047 → 048.

Decision​

Encrypt the natural-person PII of investigation_persons at rest, mirroring the established users.email pattern (ADR-0036 lineage, migrations 047/048):

  1. Switch the four activities.py writers to the ORM — pg_insert(InvestigationPerson).values(...).on_conflict_do_nothing() — preserving the existing dedup semantics (the partial unique index on case_id, source, source_reference). This is the load-bearing prerequisite: only ORM writes route through the EncryptedText TypeDecorator.
  2. Widen the model so first_name, last_name, and date_of_birth use EncryptedText(). date_of_birth becomes Mapped[str | None] holding an encrypted ISO-8601 string (the write path already produces dob_iso; no consumer does date arithmetic on it).
  3. One atomic Alembic migration performs the expand-contract in a single upgrade() (the project runs in PoC mode with no production data and no rolling deploy — ADR context "no backward compat needed"): add <col>_encrypted bytea, backfill by reading plaintext and encrypting in Python via EnvKeyProvider, drop the plaintext column, rename <col>_encrypted → <col>. The backfill loop skips rows already encrypted (idempotent). A matching downgrade() reverses the shape (decrypt back to plaintext) for reversibility in dev.
  4. Wire jsonb_crypto into the activities.py write path for identification / phones / emails, and into the ORM read path, so those JSONB PII fields are encrypted too — rather than dropping the annotation. Dropping it would reduce a claimed control, which violates the project's standing rule: the system may add scrutiny but never silently remove a declared protection.
  5. Regenerate docs/pii_manifest.json — fields_encrypted_at_rest rises to match fields_requiring_encryption; encryption_gaps for these fields empties.

The migration and the model change ship in the same PR so the encrypted-write and encrypted-read halves are never deployed apart.

Decision context:

  • Latency: +1 AES-256-GCM encrypt per person-write (~microseconds; writes are already DB-bound) and +1 decrypt per person-read. Negligible against the OSINT activity's network cost. Not separately measured because the same EncryptedText path already runs on users.email with no observed impact.
  • Dependency surface: none new — reuses app/pii/encryption.py, app/pii/key_providers.py, app/pii/jsonb_crypto.py, all already in-tree.
  • Debuggability: a missing/rotated key surfaces at read as a decrypt failure naming the key_id embedded in the ciphertext (historical keys are tried). startup_guard (ADR-0104) already refuses production boot with encryption disabled, so the plaintext-in-prod failure mode cannot recur.
  • Reversibility: the migration's downgrade() decrypts back to plaintext columns; the model change is a one-line revert per column. Data-losing only if a key is destroyed (out of scope — key lifecycle is ADR-0107 crypto-shred).
  • Blast radius: substitutive on three columns of one table. All readers are ORM (verified), so no read path changes. The sole writer is activities.py.
  • Alternative considered: drop the encryption annotation and keep plaintext — rejected; it makes the honest manifest permanently report a gap on genuine PII and abandons a declared control.

Consequences​

Positive​

  • The three natural-person PII fields (plus the JSONB contact fields) are AES-256-GCM encrypted at rest; pii_manifest.json stops reporting them as gaps.
  • Removes the raw-SQL TypeDecorator bypass — future PII columns on this table are protected automatically once annotated.
  • Aligns investigation_persons writes with the ORM Repository direction (ADR-0008 superseded).

Negative​

  • Migrations now require the PII encryption key in the environment at migration time (the backfill encrypts in Python). This is an added operational dependency, though the app already requires the key to run.
  • date_of_birth is no longer a native DATE column; any future need to range-query it in SQL would require the HMAC-hash pattern (as used for email_hash) or decryption in the application layer.
  • Encrypted columns are not indexable for equality search without an accompanying _hash column; this table's dedup uses source_reference, not the encrypted fields, so no index is lost today.

Neutral​

  • person_index already computes its HMAC from the in-memory plaintext at write time, so the search-hash path is unaffected by the storage-layer change.

Alternatives Considered​

Alternative 1: Drop the PIIField encryption annotation, keep plaintext​

  • Stop claiming the fields are meant to be encrypted; the manifest would show no gap.
  • Why rejected: they are PII (names, DOB, ID numbers of natural persons). Dropping the claim trades an honest gap for a dishonest silence and permanently forgoes a real control — the opposite of the project's "add scrutiny, never suppress" principle.

Alternative 2: Expand-contract across two deploys (047 → backfill → 048), as done for users.email​

  • Add encrypted columns in deploy 1, dual-write, backfill, drop plaintext in deploy 2.
  • Why rejected: that dance exists to protect a live production table during a rolling deploy. This project has no production data and no rolling deploy (PoC mode), so the single-migration collapse is safe and avoids a transient dual-write code path that would itself need testing and later removal.