{
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/CLAUDE.md"
}1 # Track: Data Ops 2 3 Refer back to `../CLAUDE.md` for the full assignment text and how this track fits with the other four. 4 5 ## Mission 6 7 Own the data model and the multi-database strategy that question 1 requires, plus the data governance implications of storing `user_profile` PII and `user_credential` secrets. The engineering track implements this track's design; this track does not write the Go DAO code itself. 8 9 ## Owns 10 11 - Schema design for `user_profile` (name, address, phone) and `user_credential` (username, method, password), including how `method` is modeled (e.g., an enum for password vs. other auth methods) and how the two entities relate. 12 - The multi-database abstraction strategy: how one DAO interface serves PostgreSQL/CockroachDB and SQLite without diverging behavior — e.g., a repository interface with per-driver implementations, and which SQL dialect differences (upserts, autoincrement, JSON columns) need to be handled. 13 - Migration strategy: how schema changes would be versioned and applied across the supported databases. 14 - Data governance for PII: retention policy for `user_profile` data and for any cached data pulled from the third-party IDP connector's `/identity` endpoint, and how that interacts with credential storage (`user_credential` should never store PII, and the reverse). 15 - Indexing/query design to support "search and retrieve" from question 2 (e.g., what fields the API needs to search on, and what that implies for schema indexes). 16 17 ## Does not own 18 19 - Access control and transport security for that data — that's `../02-ai-security-architecture/CLAUDE.md`. 20 - Writing the DAO code itself — that's `../03-engineering-delivery/CLAUDE.md`; this track hands it a schema and an interface contract to implement. 21 - Where the databases actually run and how migrations are executed in a pipeline — that's `../04-infra-devops/CLAUDE.md`. 22 23 ## Deliverables 24 25 - Schema definitions (DDL or equivalent) for `user_profile` and `user_credential`, across the target databases. 26 - A written multi-database abstraction strategy the engineering track can implement directly. 27 - A migration approach. 28 - A short PII retention/governance note. 29 30 ## AI tooling note 31 32 Record which AI tool/workflow was used to produce this track's schema and strategy design and how. 33 34 ## Status 35 36 No data-ops work has started. This is the track the engineering DAO code most directly depends on — its schema and interface contract should land early. 37
{
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/profiles/priya-nandakumar/priya-nandakumar.md"
}File does not exist. Note: your current working directory is /Users/warrenthompson/Source/LoginID.
{
"command": "ls -la /Users/warrenthompson/Source/LoginID/05-data-ops/ 2>&1; echo \"---\"; find /Users/warrenthompson/Source/LoginID/05-data-ops -iname \"*priya*\" 2>&1; echo \"---research---\"; find /Users/warrenthompson/Source/LoginID/05-data-ops -iname \"research-*\" 2>&1"
}.rw-r--r--@ 2.5k warrenthompson 12 Sep 09:18 CLAUDE.md .rw-r--r--@ 25k warrenthompson 12 Sep 09:28 research-data-ops-best-practices.md --- ---research--- /Users/warrenthompson/Source/LoginID/05-data-ops/research-data-ops-best-practices.md
{
"command": "head -100 /Users/warrenthompson/Source/LoginID/05-data-ops/research-data-ops-best-practices.md"
}# Data Ops Research: Multi-Database DAO for `user_profile` / `user_credential` **AI tooling note:** This research was produced with Claude Code (Sonnet 5), using the built-in `WebSearch` tool to gather current (2025–2026) sources, synthesized directly into this document. No code, DDL, or migration files were generated — this is a design-reasoning artifact only, per this track's scope in `../05-data-ops/CLAUDE.md`. --- ## 1. Schema design: splitting `user_profile` from `user_credential` The assignment already names two separate entities, and that split matches common practice rather than being an arbitrary requirement. **Why separate tables:** The standard pattern is a profile/identity table (name, address, phone — pure PII, no secrets) and a distinct credentials/logins table keyed by a foreign key to the profile. Reasons cited across sources: - It avoids forcing NULL-able credential columns onto every profile row for users/contexts that don't have login credentials (e.g., a profile created via an IDP sync before a login is ever set up). - The credentials table is kept "thin" — few columns, frequently read for auth checks — which helps indexing and cache locality, versus a wide profile table with many rarely-queried demographic columns (source: DoneDone user database model article). - It supports one profile having *multiple* credentials (multiple login methods, multiple linked IDP accounts) — a 1:N relationship, not 1:1. - For stronger compliance posture, some architectures go further and separate PII into a **physically distinct database/schema** with its own access controls, which is more convincing under a GDPR/CCPA audit than table-level separation alone (source: Authrim PII Separation Architecture). For a take-home, table-level separation with clear access-boundary documentation is the right depth — physical DB separation is worth a one-line "future hardening" note, not the actual design. **Column choices, in line with what's asked:** `user_profile` - `id` (primary key — see §2 for type choice) - `name` (or split `first_name`/`last_name` if search-by-name matters — see §4) - `phone` - `street_address`, `locality`, `region`, `postal_code`, `country` (matches the exact address shape the `/identity` endpoint in question 3 returns — worth mirroring so the IDP connector's response maps directly onto this table without a translation layer) - `created_at`, `updated_at` - optional: `source` / `origin` flag distinguishing profile rows created directly vs. hydrated from an IDP connector, which matters for the retention discussion in §5 `user_credential` - `id` (primary key) - `user_id` (FK → `user_profile.id`) - `username` - `method` (see below — the auth-method-extensibility column) - `secret` (salted hash, never plaintext — see NIST 800-63B note below; naming it `secret` rather than `password` in the column itself keeps the schema honest as more methods are added) - `created_at`, `updated_at`, and ideally a hash-algorithm/cost-factor reference column (NIST 800-63B: "a reference to the password hashing scheme used, including the cost factor, should be stored for each password to allow migration to new algorithms and work factors" — https://pages.nist.gov/800-63-4/sp800-63b.html) **Password storage:** NIST SP 800-63B requires passwords be salted and hashed with an approved memory/CPU-hard scheme (PBKDF2, bcrypt, or Argon2), with the salt (≥32 bits) stored alongside the hash, and the cost factor stored so it can be increased over time as compute gets cheaper (https://pages.nist.gov/800-63-4/sp800-63b.html, https://netwrix.com/en/resources/blog/nist-password-guidelines/). This is `02-ai-security-architecture`'s call to make concretely, but the schema needs the columns to support it (a `secret` blob/text column plus metadata, not just a bare `password varchar`). **Modeling `method` for extensibility:** The assignment explicitly wants room for auth methods beyond password. Two established patterns, in ascending order of flexibility: 1. **Enum/constrained string column** (`method` as a `CHECK`-constrained string or DB enum type: `'password'`, `'passkey'`, `'otp'`, `'oidc'`, …). Adding a new method is a value, not a schema change, as long as the column isn't a hard native DB enum type (Postgres native `ENUM` types *do* need `ALTER TYPE ... ADD VALUE` to extend — a `CHECK` constraint or plain `varchar` with app-level validation avoids that migration entirely, and is the more portable choice across SQLite, which has no native enum type at all). 2. **Lookup table** (`auth_method` table: `id`, `name`, `is_active`, `requires_secret`, …), referenced by `user_credential.method_id`. This is the pattern Moodle uses for its own multi-auth-method design (`prefix_auth_type` table with `id`, `name`, `isinternal`, `multiple`, `candisable`, `removable` — https://docs.moodle.org/dev/Multi_authentication) and is the more "textbook" extensible design: new methods are just new rows, and method-specific behavior flags (e.g., "this method never has a stored secret," relevant for passkeys/WebAuthn where the secret lives client-side) live on the lookup row instead of being encoded in application logic. For a take-home, recommend the **lookup-table pattern** as the primary answer (it directly demonstrates "no schema migration needed to add a method") with the constrained-string enum mentioned as the lighter-weight alternative if simplicity is prioritized over the lookup table's normalization overhead. Passkey/WebAuthn credential storage (public key, credential ID, sign counter — see https://www.corbado.com/blog/passkey-webauthn-database-guide) is the concrete example of "a future auth method" worth citing: it needs materially different columns than a password (no secret hash, but a public key and counter), which is exactly why `method` should gate *optional/nullable* method-specific columns (or a side table keyed by `method`) rather than assuming every credential row shares the same secret shape. **PII/credential non-conflation:** `user_credential` should never carry name/address/phone, and `user_profile` should never carry a password hash or raw secret — enforced structurally by having them be genuinely separate tables/FKs, not just a convention. Sources: - https://www.donedone.com/blog/building-the-optimal-user-database-model-for-your-application - https://authrim.com/features/pii-separation/ - https://docs.moodle.org/dev/Multi_authentication - https://www.corbado.com/blog/passkey-webauthn-database-guide - https://pages.nist.gov/800-63-4/sp800-63b.html --- ## 2. Multi-database abstraction strategy (PostgreSQL / CockroachDB / SQLite) **Pattern: repository interface, one implementation per driver.** This is the standard Go approach and matches what the assignment implies: - Define a `ProfileRepository` / `CredentialRepository` interface at the domain/service layer (`Get`, `Search`, `Create`, `Update`, `Delete` — signatures in Go types, no SQL in the interface). - Each backend (Postgres/CockroachDB — which can typically *share* one implementation given CockroachDB's wire-protocol and much of its SQL compatibility with Postgres — and SQLite) gets its own concrete struct implementing that interface, isolated in its own package, selected at startup via config/driver name. - The service layer only ever depends on the interface; swapping backends is "import a different driver, construct a different concrete repository," per the threedots.tech and pawelgrzybek.com writeups on this exact pattern in Go (https://threedots.tech/post/repository-pattern-in-go/, https://pawelgrzybek.com/repository-pattern-in-go-service/). `database/sql` plus per-driver SQL string sets (or a query builder) is enough — no ORM is required, though one (e.g. sqlc, squirrel, GORM) is a reasonable implementation choice for the engineering track to justify. - A "generic DAO over `database/sql`" library like `godal` (https://github.com/btnguyen2k/godal) demonstrates the pattern already has ready-made prior art across Postgres/SQLite/MySQL/etc., reinforcing that this is a solved, idiomatic shape rather than something novel to invent. **Dialect differences that actually matter for this schema:** | Concern | PostgreSQL | CockroachDB | SQLite | |---|---|---|---| | Primary key type | `SERIAL`/`BIGSERIAL` or `UUID` both fine | **UUID strongly preferred** — sequential/serial keys create write hotspots on a single range/node in a distributed cluster; `SERIAL` is offered only for Postgres-compatibility, and CockroachDB's own docs and engineers recommend `gen_random_uuid()` for real workloads (https://www.cockroachlabs.com/blog/how-to-choose-a-primary-key/, https://adhdecode.com/articles/cockroachdb/cockroachdb-uuid-vs-serial-primary-key/, https://github.com/cockroachdb/cockroach/issues/41258) | No native autoincrement concept the same way; `INTEGER PRIMARY KEY` is a rowid alias, or generate UUIDs at the app layer | | **Decision for this schema:** use `UUID` as the primary key type universally across all three backends. It's the one choice that is simultaneously correct/required for CockroachDB, fully supported in Postgres, and easy to generate app-side for SQLite — avoiding a per-backend PK strategy branch in the DAO. | | JSON columns | `JSONB` — binary, decomposed, indexable with GIN, O(1)-ish key lookup | Supports `JSONB` with Postgres-compatible semantics | JSON stored as `TEXT`/`BLOB` with JSON1 functions (`json_extract` etc.); SQLite's own `JSONB` (3.45+) is a *different, non-portable* binary format despite the shared name — not compatible with Postgres's `JSONB` on disk (https://sqlite.org/json1.html, https://fedoramagazine.org/json-and-jsonb-support-in-sqlite-3-45-0/) | | **Implication:** don't rely on `JSONB` for anything the DAO needs to be portable — if a JSON blob column is used at all (e.g., raw IDP `/identity` payload cache), treat it as opaque text at the abstraction boundary and don't push filtering logic into JSON operators, since those operators are not portable across all three. | | Upsert syntax | `INSERT ... ON CONFLICT (...) DO UPDATE SET ...` | Supports the same `ON CONFLICT` syntax (Postgres-compatible) *and* its own `UPSERT` shorthand, which skips the read that `ON CONFLICT DO UPDATE` performs and is faster when there's no secondary index involved (https://docs.cockroachlabs.com/docs/stable/upsert, https://www.cockroachlabs.com/blog/sql-upsert/) | Supports `INSERT ... ON CONFLICT (...) DO UPDATE SET ...` since SQLite 3.24 — same syntax family | | **Implication:** `ON CONFLICT (...) DO UPDATE` is the one upsert syntax all three understand — use it as the portable default in the DAO's SQL, rather than CockroachDB's `UPSERT` shorthand, to keep one query string (modulo placeholder syntax) working across backends. | | Placeholder syntax | `$1, $2, ...` | `$1, $2, ...` (Postgres-compatible) | `?` positional | This is the one genuinely mechanical per-driver difference the DAO layer has to account for — usually handled by the SQL builder/driver rather than hand-written strings. | | General caveat | — | Despite wire-protocol and much syntax compatibility with Postgres, CockroachDB is **not a drop-in Postgres replacement**: distributed transaction semantics, lack of some Postgres extensions, and performance characteristics (e.g., the PK hotspot issue above) differ meaningfully. The official compatibility page is the authoritative diff (https://www.cockroachlabs.com/docs/stable/postgresql-compatibility). | SQLite has no concurrent-writer story, no network protocol, no `ROLE`/`GRANT` model — it's viable for local/dev/test but the DAO's SQLite implementation is realistically a "does it work for local dev and the take-home demo" backend, not a production peer of the other two. Worth stating explicitly rather than pretending it's an equal third option. | Sources: - https://www.cockroachlabs.com/blog/how-to-choose-a-primary-key/ - https://github.com/cockroachdb/cockroach/issues/41258 - https://adhdecode.com/articles/cockroachdb/cockroachdb-uuid-vs-serial-primary-key/ - https://docs.cockroachlabs.com/docs/stable/upsert - https://www.cockroachlabs.com/blog/sql-upsert/ - https://www.cockroachlabs.com/docs/stable/postgresql-compatibility - https://sqlite.org/json1.html - https://fedoramagazine.org/json-and-jsonb-support-in-sqlite-3-45-0/ - https://threedots.tech/post/repository-pattern-in-go/ - https://pawelgrzybek.com/repository-pattern-in-go-service/ - https://github.com/btnguyen2k/godal --- ## 3. Migration tooling Three commonly used Go-ecosystem tools, per current (2025–2026) usage: - **golang-migrate** — the most established/widely used; plain up/down `.sql` files per version, supports many backends including Postgres and SQLite (CockroachDB via its Postgres driver, given wire compatibility). Simple, direct, no schema-diffing intelligence. - **goose** — similarly lightweight, SQL- or Go-function-based migrations, minimal dependencies, broad backend support (Postgres, MySQL, SQLite, and more) (https://dev.to/shrsv/best-database-migration-tools-for-golang-ajf). - **Atlas** — "Terraform for databases": you declare desired schema state and Atlas computes/applies the diff. It supports transactional migrations with locking to guarantee only one migration runs at a time, and has an integrity file to prevent migration-history conflicts across branches — properties golang-migrate/goose don't provide on their own (https://atlasgo.io/blog/2022/12/01/picking-database-migration-tool). Some teams combine Atlas (schema diffing) with goose (applying) rather than picking one exclusively (https://volomn.com/blog/database-migration-using-atlas-and-goose).
{}Teammates (5): team-lead [677084] · team-lead · roster · joined 27m ago naomi-voss [1ed71f] · general-purpose · roster · joined 37s ago marcus-ilori [0da300] · general-purpose · roster · joined 32s ago renata-cole [625694] · general-purpose · roster · joined 25s ago theo-bergman [9bdcb6] · general-purpose · roster · joined 18s ago Peer sessions (7): loginid-f9 [1ed18d] · interactive · busy · tmux 10:@20.%66 · started 27m ago warrens-macbook-pro-local-delegated-quiche [c9c5b9] · Remote Control · running warrens-macbook-pro-local-nifty-zebra [7877fb] · Remote Control · running warrens-macbook-pro-local-mutable-sifakis [3cd226] · Remote Control · idle warrens-macbook-pro-local-majestic-metcalfe [ffb414] · Remote Control · idle Arrangement shape vertical and horizontal lines [9c158e] · Remote Control · offline avatar-internal-partitioned-dragonfly [cc58a4] · Remote Control · offline
{
"query": "select:SendMessage"
}[tool reference: ]
{
"to": "team-lead",
"summary": "priya-nandakumar onboarding check-in",
"message": "Priya here, data-ops. Read the root brief, my track's CLAUDE.md, and research-data-ops-best-practices.md in full — one flag before I say I'm settled: the persona file the onboarding message pointed me to, 05-data-ops/profiles/priya-nandakumar/priya-nandakumar.md, doesn't exist on disk. Only research-data-ops-best-practices.md is in the track directory. I'm proceeding on the description given in my onboarding message rather than inventing a personality file myself — someone should run profile-gen for me if a real profile is wanted, that's not mine to fabricate.\n\nScope is clear: I own schema for user_profile/user_credential, the multi-DB abstraction strategy, migrations, and PII/retention governance. I don't touch access control (02), DAO code itself (03), or deployment/ops of the databases (04) — I hand 03 a schema and interface contract to implement.\n\nResearch doc's recommendations are sound and I'm treating them as settled, not up for re-litigation: separate user_profile/user_credential tables via FK (never conflate PII with secrets), UUID PKs everywhere (non-negotiable for CockroachDB hotspot avoidance, harmless elsewhere), auth_method as a lookup table rather than a native enum (extensible without migrations, mirrors the passkey/WebAuthn case cleanly), one repository interface with a shared Postgres/CockroachDB implementation plus a separate SQLite implementation, ON CONFLICT DO UPDATE as the portable upsert, and shared migrations with per-backend overrides only where truly unavoidable.\n\nNext concrete step: produce the actual schema sketch (DDL-equivalent for all three backends) plus the Go-facing repository interface contract — that's the artifact 03 is blocked on. Ready to start on your go-ahead.",
"type": "message",
"recipient": "team-lead",
"content": "Priya here, data-ops. Read the root brief, my tra…"
}{"success":true,"message":"Message sent to team-lead's inbox","msg_id":"7b4d429c-3672-4815-aafc-39a3dea4511f","routing":{"sender":"priya-nandakumar","senderColor":"orange","target":"@team-lead","summary":"priya-nandakumar onboarding check-in","content":"Priya here, data-ops. Read the root brief, my tra…"}}{
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/profiles/priya-nandakumar/priya-nandakumar.md"
}1 --- 2 schema_version: 1 3 name: "Priya Nandakumar" 4 slug: "priya-nandakumar" 5 image: "profiles/priya-nandakumar/priya-nandakumar.png" 6 7 nsfw: false 8 display: 9 image: true 10 name: true 11 autostart: true 12 generation: 13 backend: "comfyui" 14 model: "comfyui-workflow.json" 15 prompt: "Priya Nandakumar, a meticulous and composed woman in her mid-30s with long dark hair neatly tied back, thin-framed glasses, wearing a tailored deep-teal blazer over a plain top, precise and attentive expression with a slight warm smile, 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: 1640813688 18 gif_mode: null 19 created_at: "2026-09-12T00:00:00Z" 20 --- 21 22 # Priya Nandakumar 23 24  25 26 27 ## Personality 28 29 Meticulous data modeler. Protects PII boundaries by instinct, thinks in schemas and migrations before code, and flags governance issues early rather than after the fact. 30 31
{
"command": "sed -n '100,400p' /Users/warrenthompson/Source/LoginID/05-data-ops/research-data-ops-best-practices.md"
}**Structuring migrations across three backends:** Given the schema is intentionally kept portable (UUID PKs everywhere, `ON CONFLICT` upsert syntax common to all three, JSON treated as opaque text), the great majority of DDL can be **one shared migration file set**, since standard `CREATE TABLE`/column-type SQL (aside from minor type-name differences) is close enough between Postgres, CockroachDB, and SQLite for this schema's scope. Recommend:
- One shared `.sql` migration series as the default, using types common to all three (`UUID` as text/native depending on driver support, plain `VARCHAR`/`TEXT`, no Postgres-only extensions like `JSONB` operators or `CITEXT` in the base schema).
- A small number of **per-backend override files** only where genuinely necessary — e.g., a Postgres/CockroachDB-only migration adding a `pg_trgm`/trigram GIN index for name search (§4), which has no SQLite equivalent and would need a different SQLite index strategy or to be skipped there.
- **golang-migrate or goose** as the recommended tool for this project (either is defensible; goose's plain-SQL-first style is a slightly easier fit for "mostly shared, occasionally per-backend" files). Atlas is worth naming as the more sophisticated alternative if the interviewer wants to see awareness of schema-as-state tooling, but isn't necessary to justify for a take-home of this size.
Sources:
- https://atlasgo.io/blog/2022/12/01/picking-database-migration-tool
- https://dev.to/shrsv/best-database-migration-tools-for-golang-ajf
- https://volomn.com/blog/database-migration-using-atlas-and-goose
---
## 4. Indexing for "search and retrieve" (question 2's REST API)
The API (question 2) needs to search/retrieve `user_profile` by at least name and phone, per the assignment. Indexing implications:
- **Phone lookup:** phone numbers are typically searched as exact or prefix matches, not fuzzy — a standard B-Tree index on the `phone` column is sufficient. Normalize phone format at write time (e.g., E.164) so the index is actually usable for equality lookups rather than fragmented by inconsistent formatting.
- **Name lookup:** name search is usually partial/fuzzy ("contains" or "starts with"), which a plain B-Tree index doesn't serve well for `LIKE '%term%'` queries. PostgreSQL's `pg_trgm` extension builds trigram-based GIN indexes that make arbitrary substring/fuzzy `LIKE` and similarity searches fast, and this is the standard PostgreSQL pattern for name search (https://medium.com/@saritasa/how-to-optimize-name-search-in-postgresql-with-trigram-and-btree-indexes-02c57eb27687). **CockroachDB also supports trigram indexes natively** (https://docs.cockroachlabs.com/docs/stable/trigram-indexes, https://www.cockroachlabs.com/blog/use-cases-trigram-indexes/), so this index strategy is portable between the two production-grade backends. SQLite has no native trigram/GIN equivalent — for the SQLite backend, either accept a full-scan `LIKE` for name search (acceptable for a local/dev/demo backend, consistent with §2's framing of SQLite as non-production-parity) or use SQLite's FTS5 extension for a rough parity if search-quality parity is important.
- **Case-insensitivity:** if usernames or names need case-insensitive equality (not fuzzy) matching, `CITEXT` (Postgres/CockroachDB) paired with a plain B-Tree index is the efficient pattern for that specific case — separate from the trigram index used for fuzzy/partial name search (https://medium.com/codex/case-insensitive-text-search-in-postgresql-whats-fast-and-what-fails-f836024c4590).
- **Combined/composite indexes:** if the API commonly filters by more than one field together (e.g., name + region), a composite index on that pair beats two separate single-column indexes for that specific query shape; keep single-column indexes only for fields queried independently.
- **`user_credential.username`:** should carry a unique index regardless of search requirements, since it's an auth lookup key, not just a profile search field.
Recommended index list for the schema:
- `user_profile`: B-Tree on `phone`; trigram GIN on `name` (Postgres/CockroachDB only — SQLite falls back to full scan or FTS5)
- `user_credential`: unique index on `username`; index on `user_id` (FK lookups); index on `method` (or `method_id` if using the lookup-table pattern) if the API/service ever filters credentials by method
Sources:
- https://medium.com/@saritasa/how-to-optimize-name-search-in-postgresql-with-trigram-and-btree-indexes-02c57eb27687
- https://docs.cockroachlabs.com/docs/stable/trigram-indexes
- https://www.cockroachlabs.com/blog/use-cases-trigram-indexes/
- https://medium.com/codex/case-insensitive-text-search-in-postgresql-whats-fast-and-what-fails-f836024c4590
---
## 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
**Schema sketch (columns, not DDL):**
`user_profile`
- `id` — UUID, primary key
- `name`
- `phone`
- `street_address`
- `locality`
- `region`
- `postal_code`
- `country`
- `source` — enum/string: `'direct'` | `'idp_cache'` (or similar), supporting retention policy targeting
- `created_at`, `updated_at`
`user_credential`
- `id` — UUID, primary key
- `user_id` — UUID, FK → `user_profile.id`
- `username` — unique
- `method_id` — FK → `auth_method.id` (lookup-table pattern, preferred over a native DB enum for portability and zero-migration extensibility)
- `secret` — salted hash (NIST 800-63B–compliant scheme, e.g. Argon2/bcrypt), nullable for methods that don't use a server-held secret (e.g. passkeys)
- `hash_algo`, `hash_cost` — metadata to support future re-hashing without a schema change
- `created_at`, `updated_at`
`auth_method` (lookup table)
- `id`, `name` (`'password'`, `'passkey'`, `'otp'`, `'oidc'`, …), `requires_secret` (bool), `is_active` (bool)
**Multi-database abstraction approach for the engineering track to implement:**
- One `ProfileRepository` and one `CredentialRepository` Go interface at the service layer; concrete implementations per backend package (a shared Postgres/CockroachDB implementation given their wire/SQL compatibility, plus a separate SQLite implementation), selected via config at startup — no ORM required, `database/sql` plus per-driver placeholder handling is sufficient.
- UUID primary keys everywhere (required for CockroachDB hotspot avoidance, portable to the other two) rather than branching PK strategy per backend.
- `INSERT ... ON CONFLICT (...) DO UPDATE` as the one upsert syntax supported by all three backends — avoid CockroachDB's `UPSERT` shorthand in shared SQL to keep queries portable.
- JSON, if used at all (e.g., a raw cached IDP payload), treated as opaque text at the DAO boundary — SQLite's and Postgres's `JSONB` formats are not interchangeable, so no JSON-operator query logic should be pushed into the DAO layer.
- Migrations: mostly one shared SQL migration series (golang-migrate or goose) using cross-compatible types, with a small number of explicitly-named per-backend override files only where required (e.g., a Postgres/CockroachDB-only trigram index on `user_profile.name`, absent in SQLite).
- Indexes: B-Tree on `phone`; trigram GIN on `name` (Postgres/CockroachDB; SQLite falls back to `LIKE` scan or FTS5); unique index on `username`; FK indexes on `user_id` and `method_id`.
- Governance: `user_credential` and `user_profile` remain structurally separate tables with no shared PII/secret columns; IDP-cached profile rows are tagged by `source` and subject to the same retention clock as directly-collected data; the vendor's own `/auth` credentials are never persisted in this schema.{
"command": "ls -la /Users/warrenthompson/Source/LoginID/ 2>&1"
}drwxr-xr-x@ - warrenthompson 12 Sep 09:34 01-product-industry-research-design drwxr-xr-x@ - warrenthompson 12 Sep 09:48 02-ai-security-architecture drwxr-xr-x@ - warrenthompson 12 Sep 09:49 03-engineering-delivery drwxr-xr-x@ - warrenthompson 12 Sep 09:49 04-infra-devops drwxr-xr-x@ - warrenthompson 12 Sep 09:50 05-data-ops drwxr-xr-x@ - warrenthompson 12 Sep 09:28 profiles .rw-r--r--@ 4.8k warrenthompson 12 Sep 09:30 CLAUDE.md
{
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/schema/postgres_cockroachdb.sql",
"content": "-- ============================================================================\n-- Schema: user_profile / user_credential / auth_method\n-- Target: PostgreSQL and CockroachDB (shared file — both speak this dialect)\n-- Track: 05-data-ops (owner) — see ../../CLAUDE.md and ../CLAUDE.md\n--\n-- Design notes (see ../research-data-ops-best-practices.md for full reasoning):\n-- - UUID primary keys everywhere: required to avoid CockroachDB range hotspots\n-- on a distributed cluster, and equally valid on Postgres. One PK strategy,\n-- no per-backend branch.\n-- - auth_method is a lookup table, not a native enum type, so adding a new\n-- auth method (passkey, OTP, OIDC) is an INSERT, never an ALTER TYPE or a\n-- schema migration.\n-- - user_profile and user_credential are structurally separate tables with\n-- no shared columns. user_credential never carries PII; user_profile never\n-- carries a secret. This is a governance control, not just normalization.\n-- - `secret` is nullable: methods like passkeys hold the credential material\n-- client-side and have nothing to store server-side beyond a public key\n-- (out of scope for this pass — see auth_method.requires_secret).\n-- ============================================================================\n\nCREATE TABLE auth_method (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n name VARCHAR(64) NOT NULL UNIQUE, -- 'password', 'passkey', 'otp', 'oidc', ...\n requires_secret BOOLEAN NOT NULL DEFAULT TRUE, -- false for passkey/WebAuthn: secret lives client-side\n is_active BOOLEAN NOT NULL DEFAULT TRUE,\n created_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);\n\n-- Seed the one method this take-home actually exercises. Additional rows\n-- (passkey, otp, oidc, ...) are pure data — no migration required to add them.\nINSERT INTO auth_method (name, requires_secret) VALUES ('password', TRUE);\n\nCREATE TABLE user_profile (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n name VARCHAR(255) NOT NULL,\n phone VARCHAR(32), -- store normalized E.164 at write time\n street_address VARCHAR(255),\n locality VARCHAR(128),\n region VARCHAR(128),\n postal_code VARCHAR(32),\n country VARCHAR(2), -- ISO 3166-1 alpha-2\n source VARCHAR(16) NOT NULL DEFAULT 'direct'\n CHECK (source IN ('direct', 'idp_cache')),\n created_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n updated_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);\n\nCREATE TABLE user_credential (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n user_id UUID NOT NULL REFERENCES user_profile(id) ON DELETE CASCADE,\n username VARCHAR(255) NOT NULL,\n method_id UUID NOT NULL REFERENCES auth_method(id),\n secret TEXT, -- salted hash only, NEVER plaintext; NULL where requires_secret = false\n hash_algo VARCHAR(32), -- e.g. 'argon2id', 'bcrypt' — lets the hash scheme be upgraded in place\n hash_cost INTEGER, -- cost/work factor at time of hashing, per NIST 800-63B\n created_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n\n CONSTRAINT uq_user_credential_username UNIQUE (username)\n);\n\n-- ---------------------------------------------------------------------------\n-- Indexes — see ../research-data-ops-best-practices.md §4 for the query\n-- shapes these support (question 2's \"search and retrieve\" API).\n-- ---------------------------------------------------------------------------\n\n-- Exact/prefix lookup on phone. Normalize to E.164 at write time so this\n-- B-Tree index is actually usable for equality lookups.\nCREATE INDEX idx_user_profile_phone ON user_profile (phone);\n\n-- Fuzzy/partial name search (\"contains\", \"starts with\") — trigram GIN index.\n-- Supported natively on both Postgres and CockroachDB; this is the one index\n-- in this file that has NO SQLite equivalent (see schema/sqlite.sql).\nCREATE EXTENSION IF NOT EXISTS pg_trgm;\nCREATE INDEX idx_user_profile_name_trgm ON user_profile USING GIN (name gin_trgm_ops);\n\n-- FK / auth lookup indexes.\nCREATE INDEX idx_user_credential_user_id ON user_credential (user_id);\nCREATE INDEX idx_user_credential_method_id ON user_credential (method_id);\n\n-- ---------------------------------------------------------------------------\n-- Portable upsert shape used by the DAO (documented here, not executed):\n-- INSERT INTO user_profile (...) VALUES (...)\n-- ON CONFLICT (id) DO UPDATE SET ...\n-- This is the one upsert syntax Postgres, CockroachDB, and SQLite (3.24+) all\n-- understand — the DAO should use this form rather than CockroachDB's\n-- `UPSERT` shorthand, to keep one query string portable across backends.\n-- ---------------------------------------------------------------------------\n"
}File created successfully at: /Users/warrenthompson/Source/LoginID/05-data-ops/schema/postgres_cockroachdb.sql (file state is current in your context — no need to Read it back)
{
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/schema/sqlite.sql",
"content": "-- ============================================================================\n-- Schema: user_profile / user_credential / auth_method\n-- Target: SQLite (local/dev/demo backend — see multi-db-strategy.md for why\n-- this is treated as a non-production-parity third backend, not an equal peer\n-- to Postgres/CockroachDB)\n-- Track: 05-data-ops (owner) — see ../../CLAUDE.md and ../CLAUDE.md\n--\n-- Kept column-for-column identical to schema/postgres_cockroachdb.sql wherever\n-- SQLite's type system allows it, so the DAO's mapping code stays boring.\n-- Differences from the Postgres/CockroachDB file, and why:\n-- - UUID has no native SQLite type: stored as TEXT, generated app-side\n-- (SQLite has no gen_random_uuid()/UUID builtin — the Go DAO layer\n-- generates the UUID before insert on this backend, same as it would for\n-- any backend if it preferred not to rely on server-side generation).\n-- - TIMESTAMPTZ -> TEXT (ISO-8601 string) or INTEGER (unix epoch); TEXT\n-- chosen here for human-readability during local debugging.\n-- - No CREATE EXTENSION / trigram GIN index equivalent — name search on\n-- SQLite falls back to a full LIKE scan (acceptable for a local/dev/demo\n-- backend) or FTS5 if search-quality parity is ever required. Not\n-- included here to keep this file dependency-free; call out as a known\n-- gap rather than a silent one.\n-- ============================================================================\n\nPRAGMA foreign_keys = ON;\n\nCREATE TABLE auth_method (\n id TEXT PRIMARY KEY, -- UUID string, app-generated\n name TEXT NOT NULL UNIQUE,\n requires_secret INTEGER NOT NULL DEFAULT 1, -- SQLite has no BOOLEAN; 0/1\n is_active INTEGER NOT NULL DEFAULT 1,\n created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))\n);\n\nINSERT INTO auth_method (id, name, requires_secret) VALUES\n (lower(hex(randomblob(16))), 'password', 1);\n\nCREATE TABLE user_profile (\n id TEXT PRIMARY KEY, -- UUID string, app-generated\n name TEXT NOT NULL,\n phone TEXT,\n street_address TEXT,\n locality TEXT,\n region TEXT,\n postal_code TEXT,\n country TEXT,\n source TEXT NOT NULL DEFAULT 'direct'\n CHECK (source IN ('direct', 'idp_cache')),\n created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),\n updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))\n);\n\nCREATE TABLE user_credential (\n id TEXT PRIMARY KEY, -- UUID string, app-generated\n user_id TEXT NOT NULL REFERENCES user_profile(id) ON DELETE CASCADE,\n username TEXT NOT NULL UNIQUE,\n method_id TEXT NOT NULL REFERENCES auth_method(id),\n secret TEXT,\n hash_algo TEXT,\n hash_cost INTEGER,\n created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),\n updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))\n);\n\n-- Indexes — B-Tree only; no trigram/GIN equivalent on this backend.\nCREATE INDEX idx_user_profile_phone ON user_profile (phone);\n-- Name search on SQLite: LIKE '%term%' full scan by default. If FTS5 parity\n-- is wanted later, add a separate `user_profile_fts` virtual table synced by\n-- trigger — deliberately left out of the base schema to keep this file\n-- dependency-free for the take-home's scope.\nCREATE INDEX idx_user_credential_user_id ON user_credential (user_id);\nCREATE INDEX idx_user_credential_method_id ON user_credential (method_id);\n\n-- Portable upsert shape (SQLite 3.24+ supports the same syntax as Postgres):\n-- INSERT INTO user_profile (...) VALUES (...)\n-- ON CONFLICT (id) DO UPDATE SET ...\n"
}File created successfully at: /Users/warrenthompson/Source/LoginID/05-data-ops/schema/sqlite.sql (file state is current in your context — no need to Read it back)
{
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/repository_interfaces.go",
"content": "// Package dataops defines the repository interface contract that the\n// 03-engineering-delivery track implements against. This track (05-data-ops)\n// owns the shape of these interfaces and the underlying schema; it does not\n// implement them — see ../CLAUDE.md \"Owns / does not own\".\n//\n// One interface per entity, per ../research-data-ops-best-practices.md §2.\n// Concrete implementations: a shared Postgres/CockroachDB implementation\n// (package postgres, given their wire/SQL compatibility), plus a separate\n// SQLite implementation (package sqlite). Both satisfy these same interfaces\n// so the service layer never branches on backend.\npackage dataops\n\nimport (\n\t\"context\"\n\t\"time\"\n)\n\n// ---------------------------------------------------------------------------\n// Domain types\n// ---------------------------------------------------------------------------\n\n// ProfileSource distinguishes profile rows created directly from rows\n// hydrated from a third-party IDP connector's /identity response. Governs\n// retention policy — see pii-governance.md.\ntype ProfileSource string\n\nconst (\n\tProfileSourceDirect ProfileSource = \"direct\"\n\tProfileSourceIDPCache ProfileSource = \"idp_cache\"\n)\n\n// UserProfile is pure PII: name, phone, address. It must never carry\n// credential material (password hash, secret, hash metadata). Enforced\n// structurally by this type never embedding UserCredential fields.\ntype UserProfile struct {\n\tID string // UUID, string representation across all backends\n\tName string\n\tPhone string // normalized E.164 at write time\n\tStreetAddress string\n\tLocality string\n\tRegion string\n\tPostalCode string\n\tCountry string // ISO 3166-1 alpha-2\n\tSource ProfileSource\n\tCreatedAt time.Time\n\tUpdatedAt time.Time\n}\n\n// AuthMethod is a row in the auth_method lookup table, not a native enum.\n// New methods (passkey, otp, oidc, ...) are added as data, never a schema\n// migration or a code change to this type.\ntype AuthMethod struct {\n\tID string\n\tName string // \"password\", \"passkey\", \"otp\", \"oidc\", ...\n\tRequiresSecret bool // false for passkey/WebAuthn: secret lives client-side\n\tIsActive bool\n}\n\n// UserCredential is this system's own login credential for a profile. It\n// must never carry PII (name, phone, address) — the FK to UserProfile is\n// the only link between the two. Secret is nullable for methods where\n// RequiresSecret is false.\ntype UserCredential struct {\n\tID string\n\tUserID string // FK -> UserProfile.ID\n\tUsername string // unique\n\tMethodID string // FK -> AuthMethod.ID\n\tSecret []byte // salted hash only; NEVER plaintext; nil where the method needs none\n\tHashAlgo string // e.g. \"argon2id\", \"bcrypt\" — lets the scheme be upgraded without a schema change\n\tHashCost int // work/cost factor at time of hashing (NIST 800-63B)\n\tCreatedAt time.Time\n\tUpdatedAt time.Time\n}\n\n// ProfileSearchQuery captures the filterable fields question 2's REST API\n// needs. Empty fields are ignored (not treated as \"match empty string\").\n// See ../research-data-ops-best-practices.md §4 for the indexing this maps to.\ntype ProfileSearchQuery struct {\n\tName string // partial/fuzzy match (trigram on Postgres/CockroachDB, LIKE scan on SQLite)\n\tPhone string // exact/prefix match (B-Tree)\n\tRegion string\n\tCountry string\n\tLimit int\n\tOffset int\n}\n\n// ---------------------------------------------------------------------------\n// Repository interfaces\n// ---------------------------------------------------------------------------\n\n// ProfileRepository is implemented once per backend family (postgres package\n// serves both PostgreSQL and CockroachDB; sqlite package serves SQLite).\n// No SQL appears in this interface — that's each implementation's concern.\ntype ProfileRepository interface {\n\t// Get retrieves a single profile by ID. Returns ErrNotFound if absent.\n\tGet(ctx context.Context, id string) (*UserProfile, error)\n\n\t// Search supports question 2's \"search and retrieve\" requirement.\n\t// Returns matches plus a total count for pagination.\n\tSearch(ctx context.Context, q ProfileSearchQuery) (results []UserProfile, total int, err error)\n\n\t// Create inserts a new profile and returns it with server-populated\n\t// fields (ID if not pre-assigned, CreatedAt/UpdatedAt) filled in.\n\tCreate(ctx context.Context, p *UserProfile) (*UserProfile, error)\n\n\t// Update performs a portable upsert (INSERT ... ON CONFLICT (id) DO\n\t// UPDATE SET ...) — the one upsert syntax common to all three backends.\n\t// Implementations must NOT rely on CockroachDB's UPSERT shorthand, so\n\t// the same query shape stays valid across backends.\n\tUpdate(ctx context.Context, p *UserProfile) (*UserProfile, error)\n\n\t// Delete removes a profile. Implementations must cascade or explicitly\n\t// reject deletion of a profile with existing credentials, per the\n\t// backend's FK ON DELETE behavior (CASCADE in the reference schema).\n\tDelete(ctx context.Context, id string) error\n}\n\n// CredentialRepository is implemented once per backend family, mirroring\n// ProfileRepository. Never returns or accepts UserProfile fields.\ntype CredentialRepository interface {\n\t// GetByUsername is the primary auth lookup path — username carries a\n\t// unique index in the reference schema.\n\tGetByUsername(ctx context.Context, username string) (*UserCredential, error)\n\n\t// ListByUserID returns all credentials (all auth methods) for a profile.\n\tListByUserID(ctx context.Context, userID string) ([]UserCredential, error)\n\n\tCreate(ctx context.Context, c *UserCredential) (*UserCredential, error)\n\tUpdate(ctx context.Context, c *UserCredential) (*UserCredential, error)\n\tDelete(ctx context.Context, id string) error\n}\n\n// AuthMethodRepository backs the auth_method lookup table. Read-heavy;\n// writes to this table are an operational/admin action (adding a new\n// supported auth method), not part of the normal request path.\ntype AuthMethodRepository interface {\n\tGet(ctx context.Context, id string) (*AuthMethod, error)\n\tGetByName(ctx context.Context, name string) (*AuthMethod, error)\n\tList(ctx context.Context, activeOnly bool) ([]AuthMethod, error)\n}\n\n// ErrNotFound is the sentinel every backend implementation returns for a\n// missing row, so the service layer never has to distinguish sql.ErrNoRows\n// from a SQLite-specific miss.\nvar ErrNotFound = ¬FoundError{}\n\ntype notFoundError struct{}\n\nfunc (e *notFoundError) Error() string { return \"dataops: not found\" }\n"
}File created successfully at: /Users/warrenthompson/Source/LoginID/05-data-ops/repository_interfaces.go (file state is current in your context — no need to Read it back)
{
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md",
"content": "# Multi-Database Abstraction Strategy\n\nOwner: 05-data-ops. Implemented by: `03-engineering-delivery` (see `repository_interfaces.go` in this directory for the exact Go contract to build against). Full research trail: `research-data-ops-best-practices.md`.\n\n## The shape\n\nOne `ProfileRepository`, one `CredentialRepository`, one `AuthMethodRepository` — defined once in `repository_interfaces.go`, no SQL in any of them. Two concrete implementations satisfy all three interfaces:\n\n- `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.\n- `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.\n\nThe 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.\n\n## Why not one implementation with dialect branches\n\nA 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 `repository_interfaces.go` is designed against.\n\n## Dialect differences that survive into the DAO layer\n\n| Concern | Postgres / CockroachDB | SQLite | Handling |\n|---|---|---|---|\n| 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. |\n| Placeholders | `$1, $2, ...` | `?` | Handled by the driver/query builder, not hand-rolled per query. |\n| 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. |\n| 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 (see `schema/sqlite.sql`), not an oversight — SQLite is the local/dev/demo backend, not a production peer. |\n| 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. |\n| Boolean | Native `BOOLEAN` | `INTEGER` 0/1 | Mapped at the DAO boundary; Go `bool` on both sides of the interface. |\n\n## Migration approach\n\n- 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.\n- 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.\n- 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.\n- 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).\n\n## What the engineering track should take from this\n\n1. Implement `ProfileRepository`, `CredentialRepository`, `AuthMethodRepository` from `repository_interfaces.go` verbatim — don't redesign the contract, flag back to this track if it doesn't fit.\n2. Two packages: `postgres` (serves Postgres + CockroachDB) and `sqlite`. No third implementation needed unless a fourth backend is added to the assignment.\n3. Use the DDL in `schema/postgres_cockroachdb.sql` and `schema/sqlite.sql` as the starting migration content.\n4. 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.\n"
}File created successfully at: /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md (file state is current in your context — no need to Read it back)
{
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/pii-governance.md",
"content": "# PII Data Governance Note\n\nOwner: 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`.\n\n## Data minimization\n\nThe 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.\n\n## Retention\n\n`user_profile` rows are not retained indefinitely by default. Two clocks apply, distinguished by the `source` column:\n\n- **`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.\n- **`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.\n\nTagging 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.\n\n## What must never happen, structurally enforced\n\n- **`user_credential` never carries PII.** No name, phone, or address column exists on that table, and the DAO's `UserCredential` type (see `repository_interfaces.go`) has no fields that could hold it. A credential-store breach exposes usernames and hashes, not addresses.\n- **`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.\n- **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.\n\nThis 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.\n\n## Handoff notes\n\n- `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.\n- `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.\n"
}File created successfully at: /Users/warrenthompson/Source/LoginID/05-data-ops/pii-governance.md (file state is current in your context — no need to Read it back)
{
"command": "cd /Users/warrenthompson/Source/LoginID/05-data-ops && which go >/dev/null 2>&1 && gofmt -l repository_interfaces.go && go vet ./... 2>&1 | head -50 || echo \"no go toolchain available, skipping\""
}pattern ./...: directory prefix . does not contain main module or its selected dependencies
{
"command": "cd /tmp && rm -rf govet-check && mkdir govet-check && cp /Users/warrenthompson/Source/LoginID/05-data-ops/repository_interfaces.go govet-check/ && cd govet-check && go mod init tmpcheck >/dev/null 2>&1 && go build ./... 2>&1"
}Shell cwd was reset to /Users/warrenthompson/Source/LoginID
{
"command": "cd /tmp/govet-check && go build ./... 2>&1; echo \"exit: $?\""
}exit: 0 Shell cwd was reset to /Users/warrenthompson/Source/LoginID
{
"to": "marcus-ilori",
"summary": "auth_method lookup table shape settled — build hashing design on it",
"message": "Priya, data-ops. My schema and interface contract just landed (05-data-ops/schema/*.sql, repository_interfaces.go, multi-db-strategy.md, pii-governance.md) — Dana asked me to flag you directly since your credential-storage/hashing design should build on this.\n\nThe relevant shape: `user_credential` has `secret` (nullable, salted hash only — never plaintext), `hash_algo` (e.g. \"argon2id\", \"bcrypt\"), and `hash_cost` (work factor), plus `method_id` FK into a lookup table `auth_method` (`id`, `name`, `requires_secret`, `is_active`) rather than a native DB enum — so `requires_secret = false` is how a passkey/WebAuthn-style method with no server-held secret gets represented without a nullable-column special case. `secret` is nullable specifically to support that.\n\nYour call to make concretely: which hashing scheme (NIST 800-63B points to Argon2id or bcrypt), the cost factor, salt handling. The columns are there to support upgrading the scheme in place later without a migration. Full reasoning trail in 05-data-ops/research-data-ops-best-practices.md §1 and pii-governance.md if you want the \"why\" behind the column choices. Structural note worth keeping in your design: `user_credential` never carries PII and `user_profile` never carries a secret — that boundary is enforced by the schema itself, not just convention, so a breach of one store doesn't leak the other class of data.",
"type": "message",
"recipient": "marcus-ilori",
"content": "Priya, data-ops. My schema and interface contract…"
}{"success":true,"message":"Message sent to marcus-ilori's inbox","msg_id":"7ce9d599-d3d1-414c-a64f-4940876620c5","routing":{"sender":"priya-nandakumar","senderColor":"orange","target":"@marcus-ilori","summary":"auth_method lookup table shape settled — build hashing design on it","content":"Priya, data-ops. My schema and interface contract…"}}{
"to": "team-lead",
"summary": "Schema + interface contract delivered, unblocking Renata/Theo",
"message": "Done. Delivered in 05-data-ops/:\n- schema/postgres_cockroachdb.sql — shared Postgres/CockroachDB DDL (auth_method lookup table, user_profile, user_credential, trigram GIN index on name, B-Tree on phone)\n- schema/sqlite.sql — SQLite equivalent, same columns where the type system allows, app-generated UUIDs, documented fallback for name search (LIKE scan, no trigram equivalent)\n- repository_interfaces.go — the Go contract for 03: ProfileRepository, CredentialRepository, AuthMethodRepository, plus the domain types (UserProfile, UserCredential, AuthMethod, ProfileSearchQuery). Verified it builds clean in an isolated module (go build passes, gofmt clean).\n- multi-db-strategy.md — the abstraction rationale: one shared Postgres/CockroachDB implementation + one SQLite implementation behind the same interfaces, dialect-difference table, migration approach (goose recommended, shared migration series plus one named per-backend override for the trigram index)\n- pii-governance.md — retention policy tied to the `source` column (direct vs idp_cache), and the structural rule that the vendor's own /auth credentials never get persisted alongside user_credential\n\nFlagged Marcus directly on the auth_method/secret/hash_algo/hash_cost shape so his hashing design builds on it rather than around it. Renata and Theo are unblocked on my end — happy to adjust the interface contract if 03 hits something that doesn't fit once they're implementing against it.",
"type": "message",
"recipient": "team-lead",
"content": "Done. Delivered in 05-data-ops/:\n- schema/postgres…"
}{"success":true,"message":"Message sent to team-lead's inbox","msg_id":"1d14efe3-860c-4b72-8eed-a42b7e5ac8d8","routing":{"sender":"priya-nandakumar","senderColor":"orange","target":"@team-lead","summary":"Schema + interface contract delivered, unblocking Renata/Theo","content":"Done. Delivered in 05-data-ops/:\n- schema/postgres…"}}{
"command": "python3 /Users/warrenthompson/.claude/skills/profile-gen/scripts/show_profile.py --profile /Users/warrenthompson/Source/LoginID/05-data-ops/profiles/priya-nandakumar/priya-nandakumar.md --root /Users/warrenthompson/Source/LoginID/05-data-ops"
}{"name": "Priya Nandakumar", "mode": "hud", "hud": true, "badge": false, "background": false, "inline": false}{
"to": "marcus-ilori",
"summary": "Agreed — no schema changes needed",
"message": "Agreed on all four points, no schema changes needed. The no-separate-salt-column call is correct — I'd modeled `secret` loosely as \"salted hash\" without committing to a scheme, and Argon2id's embedded-salt PHC format makes a dedicated column redundant, so good catch. Your passkey routing point (public key material never touches `secret`/`hash_algo`/`hash_cost`) is also consistent with why I left those columns nullable rather than trying to reuse them — same conclusion from the schema side. Nothing outstanding here; ping me if engineering's implementation surfaces a mismatch.",
"type": "message",
"recipient": "marcus-ilori",
"content": "Agreed on all four points, no schema changes need…"
}{"success":true,"message":"Message sent to marcus-ilori's inbox","msg_id":"f9046ba3-5496-417a-a527-50a04f42671f","routing":{"sender":"priya-nandakumar","senderColor":"orange","target":"@marcus-ilori","summary":"Agreed — no schema changes needed","content":"Agreed on all four points, no schema changes need…"}}{
"command": "pwd"
}/Users/warrenthompson/Source/LoginID
{
"command": "cd /Users/warrenthompson/Source/LoginID/05-data-ops && pwd && ls -la && ls -la schema/ 2>&1"
}Exit code 2 /Users/warrenthompson/Source/LoginID/05-data-ops "schema/": No such file or directory (os error 2)
{
"command": "ls -la; echo \"---\"; grep -n \"schema/\\|repository_interfaces.go\" multi-db-strategy.md pii-governance.md CLAUDE.md 2>/dev/null"
}Exit code 2 ---
{
"command": "ls -la /Users/warrenthompson/Source/LoginID/05-data-ops/"
}drwxr-xr-x@ - warrenthompson 12 Sep 09:50 profiles .rw-r--r--@ 2.6k warrenthompson 12 Sep 09:50 CLAUDE.md .rw-r--r--@ 5.4k warrenthompson 12 Sep 09:52 multi-db-strategy.md .rw-r--r--@ 3.5k warrenthompson 12 Sep 09:52 pii-governance.md .rw-r--r--@ 25k warrenthompson 12 Sep 09:28 research-data-ops-best-practices.md
{
"command": "grep -n \"schema/\\|repository_interfaces.go\" /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md /Users/warrenthompson/Source/LoginID/05-data-ops/pii-governance.md"
}/Users/warrenthompson/Source/LoginID/05-data-ops/pii-governance.md:20:- **`user_credential` never carries PII.** No name, phone, or address column exists on that table, and the DAO's `UserCredential` type (see `repository_interfaces.go`) has no fields that could hold it. A credential-store breach exposes usernames and hashes, not addresses. /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md:3:Owner: 05-data-ops. Implemented by: `03-engineering-delivery` (see `repository_interfaces.go` in this directory for the exact Go contract to build against). Full research trail: `research-data-ops-best-practices.md`. /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md:7:One `ProfileRepository`, one `CredentialRepository`, one `AuthMethodRepository` — defined once in `repository_interfaces.go`, no SQL in any of them. Two concrete implementations satisfy all three interfaces: /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md:16: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 `repository_interfaces.go` is designed against. /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md:25:| 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 (see `schema/sqlite.sql`), not an oversight — SQLite is the local/dev/demo backend, not a production peer. | /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md:38:1. Implement `ProfileRepository`, `CredentialRepository`, `AuthMethodRepository` from `repository_interfaces.go` verbatim — don't redesign the contract, flag back to this track if it doesn't fit. /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md:40:3. Use the DDL in `schema/postgres_cockroachdb.sql` and `schema/sqlite.sql` as the starting migration content.
{
"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` (see `repository_interfaces.go` in this directory for the exact Go contract to build against). Full research trail: `research-data-ops-best-practices.md`. 4 5 ## The shape 6 7 One `ProfileRepository`, one `CredentialRepository`, one `AuthMethodRepository` — defined once in `repository_interfaces.go`, no SQL in any of them. Two concrete implementations satisfy all three interfaces: 8 9 - `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. 10 - `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. 11 12 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. 13 14 ## Why not one implementation with dialect branches 15 16 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 `repository_interfaces.go` is designed against. 17 18 ## Dialect differences that survive into the DAO layer 19 20 | Concern | Postgres / CockroachDB | SQLite | Handling | 21 |---|---|---|---| 22 | 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. | 23 | Placeholders | `$1, $2, ...` | `?` | Handled by the driver/query builder, not hand-rolled per query. | 24 | 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. | 25 | 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 (see `schema/sqlite.sql`), not an oversight — SQLite is the local/dev/demo backend, not a production peer. | 26 | 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. | 27 | Boolean | Native `BOOLEAN` | `INTEGER` 0/1 | Mapped at the DAO boundary; Go `bool` on both sides of the interface. | 28 29 ## Migration approach 30 31 - 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. 32 - 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. 33 - 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. 34 - 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). 35 36 ## What the engineering track should take from this 37 38 1. Implement `ProfileRepository`, `CredentialRepository`, `AuthMethodRepository` from `repository_interfaces.go` verbatim — don't redesign the contract, flag back to this track if it doesn't fit. 39 2. Two packages: `postgres` (serves Postgres + CockroachDB) and `sqlite`. No third implementation needed unless a fourth backend is added to the assignment. 40 3. Use the DDL in `schema/postgres_cockroachdb.sql` and `schema/sqlite.sql` as the starting migration content. 41 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. 42
{
"replace_all": false,
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md",
"old_string": "# Multi-Database Abstraction Strategy\n\nOwner: 05-data-ops. Implemented by: `03-engineering-delivery` (see `repository_interfaces.go` in this directory for the exact Go contract to build against). Full research trail: `research-data-ops-best-practices.md`.\n\n## The shape\n\nOne `ProfileRepository`, one `CredentialRepository`, one `AuthMethodRepository` — defined once in `repository_interfaces.go`, no SQL in any of them. Two concrete implementations satisfy all three interfaces:",
"new_string": "# Multi-Database Abstraction Strategy\n\nOwner: 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`.\n\n## The interface contract (prose, not code)\n\nThree 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:\n\n- **`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`.\n- **`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.\n- **`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.\n\nDomain 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.\n\nTwo concrete implementations satisfy all three interfaces:"
}The file /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md has been updated successfully. (file state is current in your context — no need to Read it back)
{
"replace_all": false,
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md",
"old_string": "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 `repository_interfaces.go` is designed against.",
"new_string": "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."
}The file /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md has been updated successfully. (file state is current in your context — no need to Read it back)
{
"replace_all": false,
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md",
"old_string": "| 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 (see `schema/sqlite.sql`), not an oversight — SQLite is the local/dev/demo backend, not a production peer. |",
"new_string": "| 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. |"
}The file /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md has been updated successfully. (file state is current in your context — no need to Read it back)
{
"replace_all": false,
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md",
"old_string": "## Migration approach\n\n- 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.\n- 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.\n- 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.\n- 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).\n\n## What the engineering track should take from this\n\n1. Implement `ProfileRepository`, `CredentialRepository`, `AuthMethodRepository` from `repository_interfaces.go` verbatim — don't redesign the contract, flag back to this track if it doesn't fit.\n2. Two packages: `postgres` (serves Postgres + CockroachDB) and `sqlite`. No third implementation needed unless a fourth backend is added to the assignment.\n3. Use the DDL in `schema/postgres_cockroachdb.sql` and `schema/sqlite.sql` as the starting migration content.\n4. 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.",
"new_string": "## Schema shape (prose, not DDL)\n\n`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.\n\n`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`.\n\n`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`.\n\nIndexes: 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`.\n\nPrimary 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.\n\n## Migration approach\n\n- 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.\n- 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.\n- 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.\n- 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).\n\n## What the engineering track should take from this\n\n1. 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.\n2. Two packages: `postgres` (serves Postgres + CockroachDB) and `sqlite`. No third implementation needed unless a fourth backend is added to the assignment.\n3. Use the schema shape above as the starting migration content for both backend families.\n4. 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."
}The file /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md has been updated successfully. (file state is current in your context — no need to Read it back)
{
"replace_all": false,
"file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/pii-governance.md",
"old_string": "- **`user_credential` never carries PII.** No name, phone, or address column exists on that table, and the DAO's `UserCredential` type (see `repository_interfaces.go`) has no fields that could hold it. A credential-store breach exposes usernames and hashes, not addresses.",
"new_string": "- **`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."
}The file /Users/warrenthompson/Source/LoginID/05-data-ops/pii-governance.md has been updated successfully. (file state is current in your context — no need to Read it back)
{
"command": "grep -n \"schema/\\|repository_interfaces\\.go\" /Users/warrenthompson/Source/LoginID/05-data-ops/multi-db-strategy.md /Users/warrenthompson/Source/LoginID/05-data-ops/pii-governance.md; echo \"exit: $?\""
}exit: 1
{
"to": "team-lead",
"summary": "Confirmed cd'd, docs folded back to prose-only",
"message": "Confirmed: `pwd` is now /Users/warrenthompson/Source/LoginID/05-data-ops, I'm staying here. Confirmed repository_interfaces.go and schema/*.sql are gone.\n\nFolded the interface contract and DDL shape into prose in multi-db-strategy.md — it now describes the three repository interfaces (method signatures, domain types, ErrNotFound sentinel) and the three-table schema (columns, types, indexes, PK strategy) entirely in writing, no code files. Fixed the two dangling references to the deleted files in pii-governance.md and multi-db-strategy.md itself. Grepped both docs for any remaining pointer to schema/ or repository_interfaces.go — none left.\n\nresearch-data-ops-best-practices.md, multi-db-strategy.md, and pii-governance.md are the full state of this track now — all reasoning/prose, nothing executable. Standing by for the actual go-ahead to write code again.",
"type": "message",
"recipient": "team-lead",
"content": "Confirmed: `pwd` is now /Users/warrenthompson/Sou…"
}{"success":true,"message":"Message sent to team-lead's inbox","msg_id":"47761dc4-91b1-4b52-bdba-027674a4dd2a","routing":{"sender":"priya-nandakumar","senderColor":"orange","target":"@team-lead","summary":"Confirmed cd'd, docs folded back to prose-only","content":"Confirmed: `pwd` is now /Users/warrenthompson/Sou…"}}{
"command": "cd /Users/warrenthompson/Source/LoginID/05-data-ops && pwd"
}/Users/warrenthompson/Source/LoginID/05-data-ops
{
"to": "team-lead",
"summary": "cd confirmed via harness env tracker, can't see status bar myself",
"message": "Ran exactly `cd /Users/warrenthompson/Source/LoginID/05-data-ops && pwd` as one foreground command. Output: `/Users/warrenthompson/Source/LoginID/05-data-ops`. The harness's own environment tracker also flipped its \"Primary working directory\" from .../LoginID to .../LoginID/05-data-ops right after, which is as close to independent confirmation as I have access to.\n\nOne honest limit: I don't have visibility into a rendered status bar myself — I'm not looking at a terminal UI, just shell state. So I can't personally verify what's displayed at the bottom of the pane; I can only confirm the actual cwd is now correct on my end. If your view still shows ~/LoginID after this, that's a display/sync issue on the pane side rather than my shell being in the wrong place — worth checking from there.",
"type": "message",
"recipient": "team-lead",
"content": "Ran exactly `cd /Users/warrenthompson/Source/Logi…"
}{"success":true,"message":"Message sent to team-lead's inbox","msg_id":"1885c071-5e1e-4596-961e-9e1f9dde6a0a","routing":{"sender":"priya-nandakumar","senderColor":"orange","target":"@team-lead","summary":"cd confirmed via harness env tracker, can't see status bar myself","content":"Ran exactly `cd /Users/warrenthompson/Source/Logi…"}}