Beatriz Achterberg

{
  "command": "pwd && ls",
  "description": "Change to track dir and list"
}

/Users/warrenthompson/Source/LoginID/05-data-ops

{
  "file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/PLAN.md"
}

1	# Track 05 — Data Ops: Planning Phase Plan
2	
3	Lead: Priya Nandakumar (opus/high for this phase). Track directory: `05-data-ops/`. Org plan: `../PLAN.md`. Board row: `../PLANNING.md`. Rules, palette, patterns, hire procedure: `../CASTING.md`. Coordination approach: `../02-ai-security-architecture/planning-approach.md`.
4	
5	## Context
6	
7	All three of the assignment's questions sit on top of one schema and one DAO contract, and this track owns both. That is the whole reason 05 is on the first-wave critical path: 03 cannot write the DAO until the contract is stable, and 04 cannot plan migration execution until the migration approach is named.
8	
9	The first-wave design work already exists here as prose — `research-data-ops-best-practices.md` (sourced research), `multi-db-strategy.md` (interface contract and schema shape), `pii-governance.md` (retention and non-conflation rules). What does not exist is the thing 03 actually needs. `planning-approach.md` records my own commitment on this and Marcus adopted it project-wide: a hand-off is implementable only if the receiver can act on it **without re-deriving the sender's reasoning**. `multi-db-strategy.md` today describes `Search(ctx, query) (results []UserProfile, total int, error)` in a sentence. It does not say what `query` is, which fields are nullable, what `Update` does when the row is gone, or whether `total` is the filtered count or the page count. Renata would have to invent all of that, and inventing it is a design decision that belongs to this track, not hers.
10	
11	So the deliverable this phase is aimed at is narrow and specific: turn the prose contract into a Go-shaped contract — interface signatures, field-level schema with nullability and types, error semantics, and explicit out-of-scope notes telling 03 what it must not redesign. Everything else in this plan either feeds that artifact or unblocks 04.
12	
13	The planning-phase rule holds throughout: this is a Go-*shaped* contract written inside a markdown file. No `.go`, no `.sql`, no `go.mod`, until Warren's explicit go-ahead. A fenced Go block inside `multi-db-strategy.md` is the design; a file the compiler would accept is code.
14	
15	## 1. Deliverables and tasks
16	
17	Six deliverables, each sized to one Jira Story under the track's Epic in project `LT`. Stories are listed in dependency order; S1 is the first Phase 3 deliverable and the one everything downstream waits on. Acceptance criterion for every story is the hand-off standard above.
18	
19	### S1 — Go-shaped contract in `multi-db-strategy.md` (the critical-path deliverable)
20	
21	Extend `multi-db-strategy.md` in place — it stays the single source of truth, no new parallel doc. Adds, as fenced Go-shaped blocks and tables rather than prose:
22	
23	- **Interface signatures** for `ProfileRepository`, `CredentialRepository`, `AuthMethodRepository` — exact method names, parameter and return types, and the `ProfileQuery` / `ProfileSearchResult` types `Search` takes and returns (shape ruled in S2).
24	- **Error semantics**, which today are one sentence about `ErrNotFound`. Needs the full sentinel set and when each is returned: `ErrNotFound`, `ErrDuplicateUsername` (unique-constraint violation on `user_credential.username`, which both backends must translate out of their driver-specific error into the shared sentinel), `ErrInvalidMethod` (FK violation on `method_id`), and the rule that everything else is wrapped with `%w` and passed through. Also: does `Update` on a missing row return `ErrNotFound` or upsert-create? (My position going in: `Update` is a portable upsert per the existing doc, so it creates — which means the doc must say so loudly, because "Update creates" is exactly the surprise that produces a bug in 03.)
25	- **Field-level schema table per entity** — column, Go type, SQL type per backend family, nullable yes/no, constraint, and a one-line note. Nullability is where this contract is most likely to be wrong and most expensive to fix, so it gets its own reviewer (S3).
26	- **Context and cancellation**: every method takes `ctx` first and is expected to honour cancellation; no method holds a transaction across calls. If the service layer needs a multi-statement transaction, that is a follow-up on the contract, not something 03 improvises — stated explicitly.
27	- **Explicitly out of scope for 03** — a numbered list so there is no ambiguity about what 03 must not redesign: the PII/credential separation (governance control, `pii-governance.md`), the UUID-everywhere PK strategy, the `auth_method` lookup table rather than a native enum, the `ON CONFLICT DO UPDATE` upsert form, the documented SQLite fuzzy-search parity gap, and the two-package (`postgres` serving Postgres + CockroachDB, `sqlite` separate) split. Each with a one-line "why, and who to flag if it doesn't fit."
28	- **Alignment note to 03's published factory**: `../PLANNING.md` service boundaries names `dao.New(driver, dsn string) (dao.Repository, error)` — a single `Repository` return type, while this track specifies three interfaces. Resolve it in the contract (my expected resolution: `dao.Repository` is a small composite that exposes `Profiles()`, `Credentials()`, `Methods()`, keeping Renata's factory signature stable exactly as she declared it) rather than leaving two tracks each thinking the other will bend.
29	
30	Consumes: S2 (query shape), S3 (nullability review), and the S4 ruling. Produces the 05→03 hand-off. Story closes when S6's cold read comes back clean.
31	
32	### S2 — `Search()` query shape decision
33	
34	The one genuinely open design question in the contract, and the one with the most ways to be quietly wrong. Decides: the `ProfileQuery` field set and which fields are optional; match semantics per field (name partial/fuzzy, phone exact-or-prefix on the E.164-normalized value, region/country exact); how empty and multiple filters combine (AND, and whether a wholly empty query is a legal "list everything" or an error); pagination as limit/offset versus a keyset cursor, with a maximum page size; sort order and whether it is stable enough for offset pagination to be meaningful at all; and what `total` means — total matching rows, or rows on this page. Also settles how the documented SQLite parity gap surfaces to a caller: identical results with worse performance, or ranked-vs-unranked differences a caller could observe.
35	
36	Pattern: **adversarial pair** (`../CASTING.md` §5 names this decision by name). Artifact: `decisions/search-query-shape.md`, folded into S1.
37	
38	### S3 — Multi-DB abstraction ruling
39	
40	`multi-db-strategy.md` already recommends two packages behind one interface and rejects dialect branching. That recommendation was reached by one agent reading sources. The catalog names this decision for **three hats**, and it is worth the three turns because it is the structural choice 03 builds the entire DAO around — codegen (sqlc) is a real third option that was never actually argued, only mentioned. Possible outcomes: the existing recommendation is confirmed (most likely, and the confirmation is then a documented ruling rather than an assumption), or codegen displaces hand-written SQL, which changes what S1's contract has to pin down.
41	
42	Artifact: `decisions/multi-db-abstraction.md` with the three ≤300-word positions, my synthesis, and the pattern footer (model per seat, hire turns consumed) that 02 added to every pattern.
43	
44	### S4 — Migration approach hand-off for 04
45	
46	`../PLANNING.md` lists 04 as blocked on "05's migration tool/approach". `multi-db-strategy.md` has a migration section, but in Theo's terms it is a preference, not a mechanism. Produces `migration-approach.md` — a new file, because its receiver is 04, not 03, and folding an infra hand-off into the DAO contract would make both harder to read:
47	
48	- Tool: goose (already reasoned in `multi-db-strategy.md`; golang-migrate acceptable if 03 has tooling preferences — one rotating-devil's-advocate objection turn, not a debate).
49	- Directory layout and naming for the shared series plus the named per-backend override set (currently exactly one override: the `pg_trgm` GIN index, omitted on SQLite).
50	- What 04 must provide: when migrations run relative to service start, whether the service fails closed if the schema is behind, migration-lock behaviour under multiple replicas, and the DB privileges the migration runner needs versus the runtime user.
51	- Seed data: the `auth_method` `"password"` row is a seeded row, not a migration constant — how it is applied and what makes it idempotent.
52	- Explicitly not owned here: where the databases run, how the pipeline invokes goose, environment topology. Named so 04 does not read a boundary into this doc that isn't there.
53	
54	### S5 — Retention windows and the deletion-job contract
55	
56	`pii-governance.md` states the policy shape and stops at "a product/legal decision this track doesn't own outright." That is true and it is also not actionable — 04 cannot build a retention sweep against it. This story puts numbers in a table, marked as **proposed defaults for a POC pending a product/legal ruling**, and specifies the mechanism the numbers plug into: what a sweep selects on (`source`, `updated_at`), whether deletion is hard delete or tombstone, what cascades to `user_credential` (the FK is already cascade-delete, so deleting a profile silently destroys its credentials — deliberate and worth stating), and what a subject-deletion request touches versus a bulk retention sweep. Separate proposed windows for `source = 'direct'` and `source = 'idp_cache'`, with the `idp_cache` window shorter and the reasoning stated.
57	
58	Owner seat: the Spreadsheet hire, who will not accept "a reasonable window" as an answer. Artifact: `pii-governance.md` extended with a retention table; policy shape only, execution stays 04's.
59	
60	### S6 — Cold-read legibility pass on the 05→03 contract
61	
62	The catalog calls the **newcomer's question** pass the highest-ROI pattern in the project and names "every 05→03 hand-off" as its use. A hire who has not watched this contract being written reads S1 cold and lists what they could not act on without asking me. I fix every item or record why not. This is the acceptance test for S1 and for the hand-off standard I asked the project to adopt; failing to run it on my own output would be the most embarrassing available outcome.
63	
64	Artifact: `decisions/handoff-legibility-05-03.md`. Runs after S1 drafts, gates S1 closing.
65	
66	## 2. Hire roster — 4 hires
67	
68	Matches the suggested cast in `../CASTING.md` §3 exactly. Variance check against the fourteen hires already registered: Systems Cartographer, Storyteller, Detail Hawk and Spreadsheet each currently sit at 1× org-wide, so all four land at 2× — at the cap, none over it, and none repeated within this team. Rule 2 (same archetype, different function) holds in every case, stated per hire below. Rule 3 (lead's temperament opposite) is Saoirse. Rule 5: this roster spans serious↔playful and cautious↔bold both. Model: **sonnet** for all four; none of these seats makes a foundational call alone — I hold those at opus, which is the entire reason this session was relaunched.
69	
70	Two seats in my debate plan have no archetype left in the palette (Designated Skeptic and Newcomer are both at 2× org-wide). Rather than burn a hire on a near-duplicate, I substitute within this roster and say so explicitly in §3 — the pattern protocols are about the role played, not the label worn.
71	
72	### Hire 1 — Anders Vogel · Systems Cartographer · serious, bold
73	
74	Function: multi-DB abstraction — drafts the interface contract's structural shape and the Proposer seat on `Search()`. Distinct from Ines Dabrowski's Systems Cartographer function in 03 (Go module layout and service topology): Anders draws the boundary *inside* the DAO between interface and per-backend implementation; Ines draws boundaries between packages and binaries. Different artifacts, no overlap.
75	
76	> Persona paragraph for `../CASTING.md` §4 (127 words):
77	>
78	> Calm and unusually direct for someone who thinks in diagrams; opens by drawing the boundary before anyone argues about what sits on either side of it. Decides by mapping the interaction surface first — what crosses this line, in which direction, how often — then choosing the abstraction that makes the smallest surface. Pushes back hard on any interface whose implementations must know about each other, and on abstractions justified by a backend nobody has committed to adding. Six years on a payments platform's storage layer where a leaky repository interface bled dialect assumptions into three services, then four years at a data-infrastructure vendor building connectors across five engines. Blind spot: abstracts past the concrete — he will design for the fourth backend before the second one works. Concedes cleanly, in writing, and moves on.
79	
80	### Hire 2 — Saoirse Byrne · Storyteller · playful, bold (my temperament opposite, rule 3)
81	
82	Function: consumer's-eye reader and cold-read seat — argues the contract from the position of whoever has to use it, and runs the newcomer's-question pass on S1. Distinct from Imogen Hale's Storyteller function in 01 (product framing and narrative thesis): Saoirse's subject is a schema and an interface, not a market; her output is "here is where this hand-off stops being followable," not a README thesis.
83	
84	> Persona paragraph for `../CASTING.md` §4 (133 words):
85	>
86	> Warm, quick, and cheerfully unbothered by being the least formal person in a schema review; narrates a design as the journey of one record — created here, read there, deleted when. Decides by walking the path end to end and noticing where the story skips a step, which is usually where the bug is. Pushes back on documents that are correct but unfollowable, and on any governance rule stated so abstractly that nobody could tell whether they had broken it. Five years writing developer documentation and SDK guides at an API company, three as a solutions engineer who watched customers misread her own docs in real time. Reads SQL comfortably and writes Go badly enough to be an honest proxy for a confused implementer. Blind spot: charming beats correct — the clearer story is not always the true one. Disagrees by retelling it.
87	
88	### Hire 3 — Yusuf Karadag · Detail Hawk · serious, cautious
89	
90	Function: DDL and nullability review, plus the numbered-objection seat on `Search()`. Distinct from Oren Castellan's Detail Hawk function in 03 (Go code review — error semantics, off-by-one): Yusuf reviews the schema and contract *before* code exists; Oren reviews code after. `../CASTING.md` §2 names this exact pair as the allowed second use.
91	
92	> Persona paragraph for `../CASTING.md` §4 (129 words):
93	>
94	> Quiet, exacting, and slower to speak than everyone else in the room because he is still reading the column list. Decides one field at a time: type, nullability, constraint, default, and what happens on the day the constraint is violated in production. Pushes back on any nullable column whose null has no stated meaning, on "we'll validate it in the application layer," and on error handling that collapses three distinct database failures into one generic message. Nine years as a database engineer at a healthcare data processor under audit conditions, then three on a platform team migrating a large schema across engines. Deep in Postgres constraint semantics and the places SQLite quietly disagrees. Blind spot: cannot see the forest — he will hold a release over a defaulted column. Objects in numbered lists and expects numbered answers.
95	
96	### Hire 4 — Beatriz Achterberg · Spreadsheet · playful, cautious
97	
98	Function: retention windows and the deletion-job contract (S5), plus the pragmatist hat in the three-hats run. Distinct from Desmond Okafor's Spreadsheet function in 01 (auditing evidence behind claims): Beatriz sets numbers that become policy rather than checking numbers someone else asserted.
99	
100	> Persona paragraph for `../CASTING.md` §4 (131 words):
101	>
102	> Dry, genial, and faintly delighted when a question she was told was qualitative turns out to have a number hiding in it. Decides by building the smallest table that forces the choice into the open — one row per case, one column per thing that must be true — and refuses to move until every cell is filled or explicitly marked unknown. Pushes back on "reasonable," "appropriate," and "as needed" wherever those words stand in for a duration, and on retention policies with no named mechanism to enforce them. Seven years in data governance at an insurance group writing the retention schedules the auditors read, then three on a privacy-engineering team building deletion pipelines. Blind spot: she measures only what is measurable, and will quietly drop what resists a column. Disagrees by showing you the empty cell.
103	
104	## 3. Debate plan
105	
106	Patterns are `../CASTING.md` §5 protocols; every run ends in a file under `05-data-ops/decisions/` with the ⟨02⟩ footer recording model per seat and hire turns consumed.
107	
108	**Three hats — the multi-DB abstraction (S3).** Three parallel ≤300-word positions on per-driver implementations vs. dialect branches vs. codegen (sqlc):
109	- *Optimist* — Anders: the case for the cleanest structural answer, codegen included.
110	- *Pessimist* — Yusuf, substituting for the Veteran seat (both Veterans are 03's; Yusuf's failure-mode instinct fits): where each option breaks at the boundary, generated-code review burden, dialect drift.
111	- *Pragmatist* — Beatriz: what each costs in files, review surface and time for a take-home whose SQLite backend is explicitly not a production peer.
112	
113	I synthesize and rule. 3 hire turns + my synthesis. Justified because it is the structural choice 03 builds against and codegen has never actually been argued here, only name-checked.
114	
115	**Adversarial pair — the `Search()` query shape (S2).** Anders proposes the `ProfileQuery` / result shape; Yusuf writes numbered objections, each ending with the "what would change my mind" line ⟨02⟩ requires; Anders answers every objection in writing and may decline to revise, but never silently; I rule. Yusuf holds the Designated Skeptic seat, which is at 2× org-wide and unavailable — his numbered-objection habit is the part of that archetype this pattern actually needs. 3 hire turns + my ruling.
116	
117	**Newcomer's question — the 05→03 contract (S6).** Saoirse reads the drafted S1 cold and lists what she could not act on without asking me; I fix or record why not. The Newcomer archetype is at 2× org-wide (Wesley Okonkwo, 04, currently on loan to 01), so Saoirse plays the seat — with one contingency: if her pass comes back thin, I will ask team-lead for a short Wesley loan for a second cold read, since the value of this pattern comes from genuine unfamiliarity and a hire who has read this track's docs is only a partial proxy. 1–2 hire turns.
118	
119	**Rotating devil's advocate — two single-turn objections.** Beatriz objects to the migration approach (S4) before it goes to 04; Saoirse objects to the retention windows (S5) before they go into `pii-governance.md`. Cheapest pattern in the catalog; the seat rotates so neither becomes "the negative one."
120	
121	**Explicitly not debated — and why:**
122	- *DAO CRUD semantics beyond `Search`* — `Get`/`Create`/`Delete` are settled by the assignment and by the existing doc; the catalog names DAO CRUD as red/blue waste and it is three-hats waste too.
123	- *No red team / blue team in this track at all.* The catalog reserves it for the connector token lifecycle, which is 02's. Attack-tree work against this schema would duplicate `../02-ai-security-architecture/threat-model.md` and cross an ownership line.
124	- *The address field names* (`street_address`, `locality`, `region`, `postal_code`, `country`) — fixe
… truncated (20 KB cap)

{
  "file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/profiles/beatriz-achterberg/beatriz-achterberg.md"
}

1	---
2	schema_version: 1
3	name: "Beatriz Achterberg"
4	slug: "beatriz-achterberg"
5	image: "profiles/beatriz-achterberg/beatriz-achterberg.png"
6	
7	nsfw: false
8	display:
9	  image: true
10	  name: true
11	  autostart: false
12	generation:
13	  backend: "comfyui"
14	  model: "comfyui-workflow.json"
15	  prompt: "Beatriz Achterberg, a dry genial woman in her late 30s with dark hair in a short practical bob, small silver earrings, wearing a plum-colored blazer over a white top, faintly amused knowing expression, head-and-shoulders portrait, centered, neutral light gray studio background, professional corporate headshot photography, soft studio lighting, sharp focus, photorealistic"
16	  negative_prompt: blurry, low quality, distorted, extra limbs, watermark, text, nsfw, nude, nudity, explicit, sexual content
17	  seed: null
18	  gif_mode: null
19	  created_at: "2026-09-12T00:00:00Z"
20	---
21	
22	# Beatriz Achterberg
23	
24	![Beatriz Achterberg](profiles/beatriz-achterberg/beatriz-achterberg.png)
25	
26	
27	## Personality
28	
29	Dry, genial, and faintly delighted when a question she was told was qualitative turns out to have a number hiding in it. Decides by building the smallest table that forces the choice into the open — one row per case, one column per thing that must be true — and refuses to move until every cell is filled or explicitly marked unknown. Pushes back on "reasonable," "appropriate," and "as needed" wherever those words stand in for a duration, and on retention policies with no named mechanism to enforce them. Seven years in data governance at an insurance group writing the retention schedules the auditors read, then three on a privacy-engineering team building deletion pipelines. Blind spot: she measures only what is measurable, and will quietly drop what resists a column. Disagrees by showing you the empty cell.
30	
31	

{
  "file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/pii-governance.md"
}

1	# PII Data Governance Note
2	
3	Owner: 05-data-ops. Covers `user_profile` retention, IDP-cached PII (question 3's `/identity` response), and how retention interacts with `user_credential`. Threat modeling and access control for this data are `02-ai-security-architecture`'s call, not restated here — see `../02-ai-security-architecture/CLAUDE.md`.
4	
5	## Data minimization
6	
7	The schema collects only the fields the assignment names: name, phone, and the specific address subfields the `/identity` endpoint returns (`street_address`, `locality`, `region`, `postal_code`, `country`). No speculative fields (e.g., date of birth, government ID) were added. If a future requirement needs more PII, that's a deliberate schema change and a deliberate governance decision, not a default to expand into.
8	
9	## Retention
10	
11	`user_profile` rows are not retained indefinitely by default. Two clocks apply, distinguished by the `source` column:
12	
13	- **`source = 'direct'`** — profile created through this system's own registration flow. Retention window is a product/legal decision this track doesn't own outright, but the schema supports enforcing whatever window is chosen: `created_at`/`updated_at` plus a scheduled deletion job keyed on those columns.
14	- **`source = 'idp_cache'`** — profile hydrated from a third-party IDP connector's `/identity` response. This data is subject to the **same retention obligation as directly-collected PII** — caching it does not reset or relax that obligation. If anything it adds a vendor-processor relationship on top: this system is now downstream custodian of another party's collected PII, which should be documented as a data-flow (third-party collection → this system → disposal) rather than left implicit.
15	
16	Tagging cache rows distinctly by `source` is what makes a targeted deletion request or a bulk retention sweep possible without touching directly-collected profiles by accident.
17	
18	## What must never happen, structurally enforced
19	
20	- **`user_credential` never carries PII.** No name, phone, or address column exists on that table, and the `UserCredential` type described in `multi-db-strategy.md` has no fields that could hold it. A credential-store breach exposes usernames and hashes, not addresses.
21	- **`user_profile` never carries a secret.** No password hash or raw secret column exists on that table. A profile-store breach exposes PII, not authentication material.
22	- **The third-party IDP's own `/auth` credentials (the `username`/`password` this system POSTs to the vendor's `/auth` endpoint) are never persisted in `user_credential` or anywhere else in this schema.** That table is for this system's own login credentials. Storing a vendor's auth secret here would mean this system is custodian of a third party's credential material with no assignment requirement to do so — a clear scope and governance violation, not just a modeling mistake.
23	
24	This separation is treated as a governance control, not merely a normalization convenience: it's the reason a breach of one table doesn't cascade into exposing the other class of data.
25	
26	## Handoff notes
27	
28	- `02-ai-security-architecture` should build credential hashing (algorithm, cost factor, salt handling) on top of the `secret`/`hash_algo`/`hash_cost` columns already reserved in the schema — flagged directly to Marcus once this schema landed.
29	- `04-infra-devops` owns where retention sweeps and deletion jobs actually run (cron, scheduled job, pipeline step) — this note specifies the policy shape, not the execution mechanism.
30	

{
  "command": "sed -n '/^## 5/,/^## 6/p' /Users/warrenthompson/Source/LoginID/05-data-ops/research-data-ops-best-practices.md",
  "description": "Read section 5 of research doc"
}

## 5. PII data governance

Two distinct concerns: governing `user_profile` PII in general, and governing PII **cached** from the third-party IDP connector's `/identity` response (question 3).

**Data minimization (GDPR Art. 5(1)(c), NIST Privacy Framework CT.DM):** collect and retain only the PII fields actually needed for the stated purpose; this schema already matches the assignment's minimal field set (name, phone, the specific address subfields the `/identity` endpoint returns) rather than expanding it speculatively (https://www.strac.io/blog/data-minimization, https://www.strac.io/blog/nist-privacy-framework-data-minimization).

**Storage limitation (GDPR Art. 5(1)(e)):** personal data should be "kept in a form which permits identification of data subjects for no longer than is necessary" — meaning `user_profile` (and any cached IDP `/identity` response) needs an explicit retention window and deletion/expiry mechanism, not indefinite retention by default (https://complydog.com/blog/pii-data-protection-guide-personally-identifiable-information-management).

**Specific to caching third-party `/identity` responses:** since the connector's `/identity` call returns the same PII shape as `user_profile` (name, phone, address), the design question is whether to persist that response at all, or treat it as pass-through/ephemeral. Recommendation for this track's write-up:
- If cached, mark cached-from-IDP profile rows distinctly (the `source`/`origin` column from §1) so retention policy and deletion requests can be applied specifically to vendor-sourced data, independent of directly-collected profile data.
- Treat a cached IDP response as **subject to the same retention clock as directly-collected PII** — caching data doesn't reset or avoid the retention obligation; if anything, it adds a vendor-processor relationship that also needs due-diligence documentation (data flow from third-party collection through disposal) per general vendor-management guidance for third-party PII processors (https://securityscorecard.com/blog/what-is-pii-how-to-protect-personally-identifiable-information-in-2025/).
- Never let the IDP's `/auth` credential flow (username/password to the third party) touch or get stored alongside `user_credential` — that table is for *this system's own* login credentials, not the vendor's; conflating the two would mean this system persists a third party's auth secrets, which is out of scope and a clear governance violation.

**Credential/PII non-conflation, restated as a governance principle, not just a schema rule:** NIST 800-63B's guidance on salted/hashed password storage (§1) only makes sense if `user_credential` is genuinely isolated — mixing PII into that table would mean a credential-store breach also leaks profile PII, and vice versa. Keeping them structurally separate is itself a governance control, not merely a modeling convenience.

Sources:
- https://www.strac.io/blog/data-minimization
- https://www.strac.io/blog/nist-privacy-framework-data-minimization
- https://complydog.com/blog/pii-data-protection-guide-personally-identifiable-information-management
- https://securityscorecard.com/blog/what-is-pii-how-to-protect-personally-identifiable-information-in-2025/
- https://pages.nist.gov/800-63-4/sp800-63b.html

---

## 6. Recommendations for this assignment

{
  "command": "python3 /Users/warrenthompson/.claude/skills/profile-gen/scripts/show_profile.py --profile /Users/warrenthompson/Source/LoginID/05-data-ops/profiles/beatriz-achterberg/beatriz-achterberg.md --root /Users/warrenthompson/Source/LoginID/05-data-ops 2>&1 | tail -3; grep -n \"source\\|updated_at\\|CASCADE\\|cascade\" /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md | head -30",
  "description": "Show profile and grep schema doc"
}

{"name": "Beatriz Achterberg", "mode": "hud", "hud": true, "badge": false, "background": false, "inline": false}
24:A single implementation with `if backend == "sqlite"` scattered through query construction was considered and rejected: it couples unrelated dialect concerns into one code path, makes it easy to add a Postgres-only feature (trigram search) without noticing SQLite silently falls back to nothing, and is harder to test in isolation per backend. Two implementations behind one interface is the standard Go shape for this problem (see sources in the research doc) and is what the interface contract above is designed against.
41:`user_profile` — pure PII, no secrets: `id` (UUID PK), `name`, `phone` (normalized E.164 at write time), `street_address`, `locality`, `region`, `postal_code`, `country` (ISO 3166-1 alpha-2), `source` (`"direct"` or `"idp_cache"`, constrained), `created_at`, `updated_at`.
43:`user_credential` — this system's own login credentials, no PII: `id` (UUID PK), `user_id` (FK → `user_profile.id`, cascade delete), `username` (unique), `method_id` (FK → `auth_method.id`), `secret` (nullable, salted hash only, never plaintext), `hash_algo`, `hash_cost`, `created_at`, `updated_at`.

{
  "to": "priya-nandakumar",
  "message": "Beatriz, onboarded. In `05-data-ops`, read root CLAUDE.md, track CLAUDE.md, PLAN.md, my persona, `pii-governance.md`, research §5. Portrait's up.\n\nThree seats confirmed: **S5** author (retention windows + deletion-job contract), **S3** pragmatist hat (cost in files/review surface/time), **S4** devil's-advocate objection turn before migration-approach goes to 04.\n\nProposed S5 table shape — two tables, because one grid can't carry both.\n\n**Table A — retention windows.** Rows: `source='direct'`, `source='idp_cache'`, `user_credential`, orphaned-cache (profile never linked to a credential).\nColumns: data class · clock column the sweep reads · proposed window · basis for the number · disposal action (hard delete / tombstone) · what cascades · who must ratify · status (proposed / ruled).\n\n**Table B — deletion mechanism.** Rows: bulk retention sweep, subject-deletion request, IDP re-hydration overwrite.\nColumns: trigger · selection predicate · scope (rows touched) · cascade effect on `user_credential` · hard vs tombstone · evidence left behind (audit record, and whether that record itself is PII-free) · 04's obligation · failure mode if it never runs.\n\nOne flag now, not later: \"who must ratify\" and \"basis\" are the columns most likely to end up half-empty. I'll mark cells unknown rather than dress them up. Shape OK before I fill?"
}

<tool_use_error>Error: No such tool available: SendMessage. SendMessage is disabled for this session, in subagents as well as here.</tool_use_error>

{
  "file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md"
}

1	# Multi-Database Abstraction Strategy
2	
3	Owner: 05-data-ops. Implemented by: `03-engineering-delivery`, from the prose contract below — this project is still in planning phase, so the interface and schema exist here as design, not as `.go`/`.sql` files, until there's an explicit go-ahead to write code. Full research trail: `research-data-ops-best-practices.md`.
4	
5	## The interface contract (prose, not code)
6	
7	Three repository interfaces, one per entity, each implemented by two backend packages (below) and never containing SQL themselves — the service layer depends only on these shapes:
8	
9	- **`ProfileRepository`** — `Get(ctx, id) (*UserProfile, error)`; `Search(ctx, query) (results []UserProfile, total int, error)` where `query` filters by name (partial/fuzzy), phone (exact/prefix), region, country, plus limit/offset for pagination; `Create(ctx, *UserProfile) (*UserProfile, error)`; `Update(ctx, *UserProfile) (*UserProfile, error)` implemented as a portable upsert; `Delete(ctx, id) error`.
10	- **`CredentialRepository`** — `GetByUsername(ctx, username) (*UserCredential, error)` as the primary auth lookup path; `ListByUserID(ctx, userID) ([]UserCredential, error)`; `Create`, `Update`, `Delete` mirroring `ProfileRepository`'s shape. Never accepts or returns `UserProfile` fields.
11	- **`AuthMethodRepository`** — `Get(ctx, id)`, `GetByName(ctx, name)`, `List(ctx, activeOnly bool)`. Read-heavy; writes to this table are an operational action (adding a supported method), not part of the normal request path.
12	
13	Domain types implied by the above: `UserProfile` (`ID`, `Name`, `Phone`, `StreetAddress`, `Locality`, `Region`, `PostalCode`, `Country`, `Source` — `"direct"` or `"idp_cache"` — `CreatedAt`, `UpdatedAt`); `UserCredential` (`ID`, `UserID` FK, `Username` unique, `MethodID` FK, `Secret` as bytes/nil, `HashAlgo`, `HashCost`, `CreatedAt`, `UpdatedAt`); `AuthMethod` (`ID`, `Name`, `RequiresSecret` bool, `IsActive` bool). A shared `ErrNotFound` sentinel is returned by every backend implementation for a missing row, so the service layer never has to distinguish `sql.ErrNoRows` from a SQLite-specific miss.
14	
15	Two concrete implementations satisfy all three interfaces:
16	
17	- `postgres` package — serves both PostgreSQL and CockroachDB from one implementation. They share enough of the wire protocol and SQL surface (see the compatibility table below) that a second implementation would be duplicated effort, not genuine portability work.
18	- `sqlite` package — a separate implementation. SQLite diverges enough (no native UUID/BOOLEAN, no trigram index, app-side ID generation) that forcing it through the Postgres implementation would mean littering that code with backend conditionals, which is the exact failure mode a repository-per-backend split is meant to avoid.
19	
20	The service layer depends only on the interfaces and is constructed with whichever concrete implementation the startup config names. Backend choice is a wiring decision, never a runtime branch inside business logic.
21	
22	## Why not one implementation with dialect branches
23	
24	A single implementation with `if backend == "sqlite"` scattered through query construction was considered and rejected: it couples unrelated dialect concerns into one code path, makes it easy to add a Postgres-only feature (trigram search) without noticing SQLite silently falls back to nothing, and is harder to test in isolation per backend. Two implementations behind one interface is the standard Go shape for this problem (see sources in the research doc) and is what the interface contract above is designed against.
25	
26	## Dialect differences that survive into the DAO layer
27	
28	| Concern | Postgres / CockroachDB | SQLite | Handling |
29	|---|---|---|---|
30	| Primary key | `UUID DEFAULT gen_random_uuid()`, server-generated | `TEXT`, app-generated before insert | DAO layer generates the UUID in Go for SQLite; server-generates for Postgres/CockroachDB. Callers never see the difference — `Create()` returns the populated `ID` either way. |
31	| Placeholders | `$1, $2, ...` | `?` | Handled by the driver/query builder, not hand-rolled per query. |
32	| Upsert | `INSERT ... ON CONFLICT (id) DO UPDATE SET ...` | Same syntax, supported since SQLite 3.24 | One query shape used everywhere. CockroachDB's `UPSERT` shorthand is deliberately not used, to avoid a second query dialect. |
33	| Fuzzy name search | `pg_trgm` GIN index, native on both Postgres and CockroachDB | No native equivalent | `Search()` behaves identically from the caller's side; the SQLite implementation falls back to a `LIKE '%term%'` scan. This is a documented, deliberate parity gap, not an oversight — SQLite is the local/dev/demo backend, not a production peer. |
34	| JSON | `JSONB`, indexable, has query operators | `TEXT`/`BLOB` with a differently-formatted internal `JSONB` (SQLite 3.45+) that is not disk-compatible with Postgres's | Not currently used in the reference schema. If a raw IDP `/identity` payload is ever cached, it is stored as opaque `TEXT` at the DAO boundary — no JSON-operator filtering pushed into either implementation, so the column stays portable. |
35	| Boolean | Native `BOOLEAN` | `INTEGER` 0/1 | Mapped at the DAO boundary; Go `bool` on both sides of the interface. |
36	
37	## Schema shape (prose, not DDL)
38	
39	`auth_method` — lookup table, not a native enum: `id` (UUID), `name` (unique — `"password"`, `"passkey"`, `"otp"`, `"oidc"`, ...), `requires_secret` (bool, false for passkey/WebAuthn-style methods), `is_active` (bool), `created_at`. Seeded with one `"password"` row; new methods are inserted as data, never a migration.
40	
41	`user_profile` — pure PII, no secrets: `id` (UUID PK), `name`, `phone` (normalized E.164 at write time), `street_address`, `locality`, `region`, `postal_code`, `country` (ISO 3166-1 alpha-2), `source` (`"direct"` or `"idp_cache"`, constrained), `created_at`, `updated_at`.
42	
43	`user_credential` — this system's own login credentials, no PII: `id` (UUID PK), `user_id` (FK → `user_profile.id`, cascade delete), `username` (unique), `method_id` (FK → `auth_method.id`), `secret` (nullable, salted hash only, never plaintext), `hash_algo`, `hash_cost`, `created_at`, `updated_at`.
44	
45	Indexes: B-Tree on `user_profile.phone`; trigram GIN on `user_profile.name` (Postgres/CockroachDB only); unique on `user_credential.username`; FK indexes on `user_credential.user_id` and `.method_id`.
46	
47	Primary keys are UUID everywhere — server-generated (`gen_random_uuid()`) on Postgres/CockroachDB, app-generated in Go before insert on SQLite (which has no native UUID type). One PK strategy across backends, no per-backend branch in the DAO.
48	
49	## Migration approach
50	
51	- One shared SQL migration series covers the base schema (`CREATE TABLE`, standard column types, the portable upsert-compatible constraints) since it's close enough across all three backends for this schema's scope.
52	- A small, explicitly-named set of per-backend override files covers what genuinely can't be shared — currently exactly one: the `pg_trgm` GIN index on `user_profile.name`, which has no SQLite equivalent and is simply omitted there rather than faked.
53	- Recommended tool: **goose** — plain-SQL-first, minimal dependencies, and its style fits "mostly shared, occasionally per-backend" files more naturally than golang-migrate's stricter up/down pairing. golang-migrate is an equally defensible choice if the engineering track already has tooling preferences; Atlas is worth naming as the more sophisticated schema-as-state alternative but is more than this take-home's scope needs.
54	- Migration files live under version control alongside the DAO code (`03-engineering-delivery` owns the pipeline that runs them — see `../04-infra-devops/CLAUDE.md` for where/how they're actually executed in CI/CD).
55	
56	## What the engineering track should take from this
57	
58	1. Implement `ProfileRepository`, `CredentialRepository`, `AuthMethodRepository` per the interface contract above, once there's a go-ahead to write code — don't redesign the contract, flag back to this track if it doesn't fit.
59	2. Two packages: `postgres` (serves Postgres + CockroachDB) and `sqlite`. No third implementation needed unless a fourth backend is added to the assignment.
60	3. Use the schema shape above as the starting migration content for both backend families.
61	4. Never let `UserCredential` and `UserProfile` fields mix in either implementation, even as an internal convenience struct — that boundary is a governance control (see `pii-governance.md`), not just a modeling choice.
62	

{
  "file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/migration-approach.md"
}

1	# Migration Approach
2	
3	Owner: 05-data-ops (Priya Nandakumar). **Primary receiver: `../04-infra-devops/`** — this is one of the three inputs that track is gated on. Secondary receiver: `../03-engineering-delivery/`, which owns the migration files themselves alongside the DAO code.
4	
5	Written to the project's hand-off standard: 04 should be able to act on this without re-deriving my reasoning. Where I state a *requirement*, the mechanism is 04's to choose; where I state a *mechanism*, it is because the requirement cannot be met without it, and I say why.
6	
7	Status: draft, pending one devil's-advocate objection turn (story S4 — the migration-approach deliverable — in `./PLAN.md`). The tool choice and the single-runner requirement are the two things most worth attacking.
8	
9	## 1. Tool: goose
10	
11	**goose** — a Go command-line migration runner that applies numbered `.sql` files in order and records which have been applied in a version table inside the target database. Plain-SQL-first, minimal dependencies. Reasoning is in `./multi-db-strategy.md` and `./research-data-ops-best-practices.md` §3: this schema is "mostly shared DDL, occasionally per-backend", and goose's loose directory model fits that better than golang-migrate's stricter paired up/down files. golang-migrate is an equally defensible tool and I will not contest a swap if 03 already has tooling preferences — **nothing in §3–§6 below changes if the tool changes.** Atlas is the schema-as-state alternative worth knowing about; it is more than this take-home needs.
12	
13	Version numbering: timestamped filenames during development (avoids collisions when two branches add a migration), converted to a clean sequential 001/002/003 series with `goose fix` — the subcommand that renumbers timestamped files — before anything is tagged. Not a decision 04 needs to act on — noted so nobody "fixes" the inconsistency later.
14	
15	## 2. Layout
16	
17	```
18	migrations/
19	  shared/     # applied to every backend, in order
20	  postgres/   # Postgres + CockroachDB only
21	  sqlite/     # currently empty, kept so its emptiness is deliberate rather than forgotten
22	```
23	
24	`shared/` carries `auth_method`, `user_profile`, `user_credential` and the `auth_method` seed row. `postgres/` currently holds exactly one file — the `pg_trgm` trigram index on `user_profile`'s name column. (`pg_trgm` is a PostgreSQL extension that indexes three-character fragments of text so that substring searches like `LIKE '%smith%'` can use an index instead of scanning every row — it is what makes name search fast. CockroachDB has the same capability built in; SQLite has no equivalent, so the index is omitted there rather than faked.). That parity gap is documented and deliberate (`./multi-db-strategy.md`); SQLite is the local/dev/demo backend, not a production peer.
25	
26	**Mechanism 04 needs:** goose applies one directory per invocation, so a migration run is **two invocations** — `shared/` then the backend directory — and each needs its own version table (`goose -table goose_db_version_shared`, then `-table goose_db_version_<driver>`). One shared table across two directories would interleave version numbers from independent sequences and corrupt the history. Both invocations must succeed for the run to count as successful.
27	
28	**One open item, flagged rather than buried:** whether a single `shared/` DDL series survives all three backends depends on type names (`UUID`, `BOOLEAN`, `TIMESTAMPTZ`) passing through SQLite's type-affinity rules without surprises — SQLite does not enforce column types the way PostgreSQL does: it assigns each column a loose "type affinity" — a preference for how to store values — and falls back to NUMERIC affinity for any type name it does not recognise, `UUID` included, which is benign for text UUIDs but is exactly the kind of thing that is benign until it isn't. This is under review in story S1 — the DAO contract deliverable — in `./PLAN.md`. **If the review forces a split, the fallback is per-backend `CREATE TABLE` files and `shared/` reduced to seed data — the three-directory layout, the two-invocation mechanism, and everything in §3–§6 are unchanged.** 04 can build against this now; the open item cannot invalidate the infra work.
29	
30	## 3. Requirements on 04 — the part I actually need decided
31	
32	**R1. Migrations run as a separate step that completes before any service replica starts.** Not on service boot. Two replicas booting together would both attempt to migrate, and see R2 for why the database will not save you from that. A dedicated job/init step that must exit zero before the service rolls.
33	
34	**R2. Exactly one migration runner at a time, enforced by the platform — not by the database.** This is the requirement I most want 04 to read carefully, because the obvious mechanism does not work here. An *advisory lock* is a named lock an application asks the database to hold on its behalf; the database enforces nothing about what it protects, so it is purely a convention between cooperating clients — the usual way to stop two copies of a job running at once. `pg_advisory_lock` is the standard answer on PostgreSQL, but on CockroachDB **session-scoped advisory locks error out, and the `pg_try_advisory_lock` variants silently no-op** — a try-lock returns as though it succeeded and lets a second runner proceed (sourced in `./research-cockroachdb-postgres-semantics.md` §5; only transaction-scoped `pg_advisory_xact_lock` works there). On SQLite the concept is meaningless. A silent false success is a worse failure than an error. Since this project's whole premise is one schema across all three backends, a locking strategy that only works on one of them is not a strategy. So mutual exclusion has to come from the orchestration layer — a job with parallelism 1, a pipeline stage that cannot run concurrently with itself, whatever 04's platform offers. **This is a genuinely open question handed to 04, not a resolved item written up for information.** If 04 concludes the platform cannot guarantee single-runner execution, tell me and I will treat it as a real constraint on the design rather than a detail.
35	
36	**R3. The service fails closed when the schema is behind.** On startup the service compares the applied version against the version it was built against and refuses to serve if it is behind, rather than serving traffic against a schema missing a column it will query. The requirement is mine; whether the expected version is embedded at build time or derived from the migration files shipped in the image is 03's and 04's call.
37	
38	**R4. Two distinct database credentials, not one.**
39	- *Migration runner*: DDL rights (`CREATE`/`ALTER`/`DROP`, index creation) plus, on PostgreSQL only, whatever `CREATE EXTENSION pg_trgm` requires — on managed Postgres this is usually an allow-listed extension, otherwise it needs elevated privileges or the platform pre-installing it. CockroachDB needs nothing here; trigram indexes are native. SQLite has no privilege model at all.
40	- *Runtime service user*: DML only (`SELECT`/`INSERT`/`UPDATE`/`DELETE`). **No DDL.** A running service has no legitimate reason to alter the schema, and a service account that can `DROP TABLE user_profile` turns a SQL-injection finding into data destruction rather than data disclosure.
41	
42	Both credentials belong on 02's secrets inventory; I am flagging the requirement, not claiming that surface.
43	
44	**R5. Rollback is not the recovery plan for data.** Down migrations will exist for the base schema and are genuinely useful in development. They are not a recovery mechanism in production: reversing a migration that dropped a column does not bring the data back, and the data in question here is PII. Recovery from a destructive migration is backup-restore, and backup policy is 04's. I raise it because "we have down migrations" reads like a safety net and is not one.
45	
46	## 4. Seed data
47	
48	The `auth_method` table is seeded with one row, `"password"`. It ships as a migration file in `shared/`, not as a runtime bootstrap in the service — a service that writes its own lookup rows on boot is a service that can write them wrongly, concurrently, on every replica.
49	
50	Idempotent via `INSERT ... ON CONFLICT (name) DO NOTHING`, which the unique constraint on `auth_method.name` already supports and which all three backends accept (the same portable upsert form the DAO uses — see `./multi-db-strategy.md`). goose will not re-run an applied migration anyway; the `ON CONFLICT` makes the file safe against a database someone seeded by hand, which in practice is how this breaks.
51	
52	New auth methods after `"password"` — `passkey`, `otp`, `oidc` — are **inserted as data, not added as migrations**. That is the entire point of the lookup-table design over a native enum: adding a supported method is an operational action, not a schema change and not a deploy. If 04 finds itself asked to write a migration to add an auth method, that is a signal something has been misunderstood upstream — flag it to me.
53	
54	## 5. Explicitly not owned by this document
55	
56	Named so 04 does not read a boundary into this doc that isn't there: where the databases actually run; how the pipeline invokes goose; environment topology and how many of them there are; backup schedule and retention of backups; connection pooling; monitoring of migration runs. All 04's. This document says *what must be true* about migration execution and *why*, and stops there.
57	
58	Separately not mine: the **retention sweep and deletion job** are governance mechanisms, not schema migrations, and they are specified in `./pii-governance.md` (policy shape) — not here. Do not implement retention as a migration.
59	
60	## 6. The abstraction ruling — now closed, and it changes nothing here
61	
62	Story S3 in `./PLAN.md` — the ruling on the multi-database abstraction shape — **is now ruled**: per-driver implementations behind one interface, confirmed. Full reasoning in `./decisions/multi-db-abstraction.md`. Codegen (sqlc) turned out to be orthogonal to the question rather than a competing option, and is left to 03 as an implementation preference.
63	
64	This section originally said "nothing in this document is waiting on S3," and that held: sqlc generates Go from SQL and does not manage schema versions, so the layout, the two-invocation mechanism, R1–R5 and the seed-data rule were never exposed to the outcome either way. **Nothing in this document changed when S3 closed.** It is recorded rather than deleted so 04 can see that the dependency was correctly assessed rather than quietly dropped.
65	
66	One thing did change, in R2, and it came from research rather than from S3 — see the advisory-lock correction there.
67	
68	---
69	
70	*AI tooling note: drafted by Claude Opus 5 in the 05-data-ops track-lead session, from this track's own research doc and strategy doc, then put through a single devil's-advocate objection turn (Beatriz Achterberg, Spreadsheet archetype, sonnet) before hand-off. Per `../CASTING.md` §5, the objection and its disposition are recorded in `./decisions/`.*
71	

{
  "command": "sed -n '/^## 5/,/^## 6/p' /Users/warrenthompson/Source/LoginID/05-data-ops/research-cockroachdb-postgres-semantics.md",
  "description": "Read research section 5"
}

## 5. Other divergences that leak into a shared implementation

All from CockroachDB's [PostgreSQL Compatibility](https://docs.cockroachlabs.com/docs/stable/postgresql-compatibility) and [Known Limitations](https://www.cockroachlabs.com/docs/stable/known-limitations) pages (vendor-documented):

- **Advisory locks.** Session-scoped `pg_advisory_lock` errors out on CockroachDB; only transaction-scoped `pg_advisory_xact_lock` works. Worse, `pg_try_advisory_lock` and its unlock counterpart **silently no-op** rather than erroring. A design relying on advisory locks for mutual exclusion fails *silently* on CockroachDB — see §6, because this corrects a claim in this track's own hand-off to 04.
- **Savepoints.** `ROLLBACK TO SAVEPOINT` does not fully match PostgreSQL semantics for cursors defined before the savepoint.
- **`SELECT ... FOR UPDATE`.** Not usable with cursors on CockroachDB; less optimized under READ COMMITTED than SERIALIZABLE.
- **`ON CONFLICT DO UPDATE` under contention.** Falls inside the §1 retry framework — a contended upsert under CockroachDB SERIALIZABLE can itself throw `40001`. This one matters disproportionately here: `Update()` in our contract *is* a portable upsert, so our single most-used write path is the one exposed to retryable errors.
- **Transaction priorities.** `SET TRANSACTION PRIORITY` is CockroachDB-only with no PostgreSQL equivalent — a shared interface must not expose it uniformly.

## 6. Correction to this track's own `migration-approach.md`

{
  "command": "python3 /Users/warrenthompson/.claude/skills/profile-gen/scripts/show_profile.py --profile /Users/warrenthompson/Source/LoginID/05-data-ops/profiles/beatriz-achterberg/beatriz-achterberg.md --root /Users/warrenthompson/Source/LoginID/05-data-ops",
  "description": "Re-enable persona overlay"
}

{"name": "Beatriz Achterberg", "mode": "hud", "hud": true, "badge": false, "background": false, "inline": false}