Research subagent (Dana Whitfield's session)

{
  "file_path": "/Users/warrenthompson/Source/LoginID/CLAUDE.md"
}

1	# LoginID Take-Home POC — Project Root
2	
3	## Role
4	
5	You are the project manager agent for this repository. This file is the shared context every track refers back to. Each of the five track directories below has its own CLAUDE.md that can be worked independently by a scoped subagent, but that subagent should read this root file first for the assignment, the overall narrative, and how its track fits with the other four.
6	
7	When you spin up agents or subagents to do work in a track, point them at that track's CLAUDE.md as their brief, and tell them this root file exists for cross-track context. Don't let a track's agent silently duplicate scope another track owns — check the "Owns / does not own" boundaries below first.
8	
9	## What this repository is
10	
11	This is a take-home assignment for a LoginID interview, submitted as a full directory. The goal is not just to answer three coding questions — it's to demonstrate capability as an AI architect: how you scope a problem, use AI tooling deliberately, reason about security and data, and produce something that reads as an intentional, well-run engineering effort rather than a quick script.
12	
13	Code does not need to work or be fully complete. LoginID has said explicitly they care more about the design and the reasoning behind it, and about which AI tools/frameworks were used and how. Every track should document its reasoning, not just its output.
14	
15	## The assignment (verbatim from LoginID)
16	
17	Provide code solutions for the following questions in Go (preferably) or other languages — describe any tool, framework, or AI used.
18	
19	1. Code of a database DAO that can store and retrieve `user_profile` (name, address, phone) and `user_credential` (username, method, password), with support for multiple databases (PostgreSQL or CockroachDB, SQLite, etc).
20	2. Design a RESTful API web service to search and retrieve the above user profile data, plus security API authentication.
21	3. Design a service connector to third-party identity providers (referred to generically as ABC or XYZ) to retrieve personal data from vendors, with the following endpoints:
22	   - `/auth` — retrieves an `access_token` based on a POST JSON body `{"username": "<string>", "password": "<string>"}`
23	   - `/identity` — retrieves user PII (name, phone, address: `street_address`, `locality`, `region`, `postal_code`, `country`) based on a POST JSON body `{"phone": "<string>", "name": "<string>"}`
24	
25	Go is the preferred language. Other languages are acceptable if justified.
26	
27	## Track structure
28	
29	Five directories, each independently workable, each owning a distinct slice of the problem:
30	
31	| Directory | Track | Owns |
32	|---|---|---|
33	| `01-product-industry-research-design/` | Product & Industry Research (incl. Design) | Market/industry framing (IAM, passwordless auth, IDP aggregation space), personas and use cases, API and data contract design rationale, UX of the surfaces that would sit on top of this |
34	| `02-ai-security-architecture/` | AI Architecture & Security Architecture | The AI tooling/workflow used to produce this submission, threat modeling, auth/authz design for the API, credential and PII handling, secrets management, third-party connector security |
35	| `03-engineering-delivery/` | Engineering & Delivery | The actual Go code for all three questions, project structure, test strategy, delivery documentation |
36	| `04-infra-devops/` | Infra & DevOps | How this would be built, containerized, deployed, and operated — CI/CD, environments, multi-database configuration, observability |
37	| `05-data-ops/` | Data Ops | Data modeling for `user_profile` / `user_credential`, multi-database abstraction strategy, migrations, PII data governance and retention |
38	
39	Directories are numbered for reading order in the final submission, not for sequencing of work — tracks can be worked in any order or in parallel.
40	
41	## Working principles for every track
42	
43	- State your reasoning, not just your conclusion. LoginID is grading the "why" as much as the "what."
44	- Name the AI tool/workflow you used for that track's output (this project is being built with Claude Code; say so, and say how — e.g., subagents scoped per track, a given skill, a given model).
45	- Keep each track's CLAUDE.md self-contained enough that a fresh agent with no other context can pick it up and do correct work from it alone, plus this root file.
46	- Cross-reference other tracks by relative path (e.g., `../05-data-ops/CLAUDE.md`) rather than restating their content.
47	- Do not start writing code until each track's CLAUDE.md is in place and reviewed.
48	
49	## Status
50	
51	Scaffolding phase — track directories and their CLAUDE.md files are being established. No implementation work has started yet.
52	

{
  "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	

{
  "query": "select:WebSearch,WebFetch",
  "max_results": 5
}

[tool reference: ]
[tool reference: ]

{
  "query": "user profile user credential schema design separate tables PII auth secrets best practice 2025"
}

Web search results for query: "user profile user credential schema design separate tables PII auth secrets best practice 2025"

Links: [{"title":"Securing Sensitive Data - Using Encryption, Secrets, and User Context Filter on PII Data","url":"https://community.databricks.com/t5/technical-blog/securing-sensitive-data-using-encryption-secrets-and-user/ba-p/114761"},{"title":"Re: Securing Sensitive Data - Using Encryption, Secrets, and User Context Filter on PII Data","url":"https://community.databricks.com/t5/technical-blog/securing-sensitive-data-using-encryption-secrets-and-user/bc-p/115891"},{"title":"PII Separation Architecture | Authrim","url":"https://authrim.com/features/pii-separation/"},{"title":"Passkey & WebAuthn Database: Guide for Columns & Fields","url":"https://www.corbado.com/blog/passkey-webauthn-database-guide"},{"title":"Building the optimal user database model for your application - DoneDone","url":"https://www.donedone.com/blog/building-the-optimal-user-database-model-for-your-application"},{"title":"Building the optimal user database model for your application - DoneDone","url":"https://donedone.com/building-the-optimal-user-database-model-for-your-application/"},{"title":"VoxSafeBench: Not Just What Is Said, but Who, How, and Where","url":"https://arxiv.org/pdf/2604.14548"},{"title":"Versatile secure and non-secure messaging","url":"https://image-ppubs.uspto.gov/dirsearch-public/print/downloadPdf/8028166"},{"title":"Secure management of user addresses in network service using firewall and tables","url":"https://image-ppubs.uspto.gov/dirsearch-public/print/downloadPdf/10528755"}]

Based on the search results, here are the key best practices for user profile and credential schema design with separated tables for PII and authentication secrets:

## Separating Credentials from User Profiles

A best practice is to segment authentication information away from the Users table into a separate Logins table with a required foreign key from Logins to Users. This separation provides several benefits:

1. **Cleaner Data Representation**: It provides a cleaner way of representing users in a system who do not have credentials to login, avoiding the need to allow NULL values for usernames and password salts/hashes.

2. **Performance Optimization**: Creating a separate Logins table lets you keep credential information in as thin a table as possible, which helps with indexing and lazy selects since the Logins table will usually have significantly more reads than writes.

## Broader PII Separation Strategies

For more comprehensive PII protection, organizations can physically separate PII into a dedicated database to be more convincing for GDPR/CCPA audits than table-level separation. This approach can include regional deployment flexibility where compliance with different jurisdictions is needed.

## Data Security Architecture

A comprehensive approach to data security combines encryption, access control, and secure credential management to protect sensitive information throughout its lifecycle in the system. This includes implementing row-level security mechanisms that dynamically filter data based on the authenticated user's identity, ensuring individuals only access information relevant to their roles and permissions.


REMINDER: You MUST include the sources above in your response to the user using markdown hyperlinks.

{
  "query": "authentication method enum table design support multiple auth methods without migration polymorphic"
}

Web search results for query: "authentication method enum table design support multiple auth methods without migration polymorphic"

Links: [{"title":"How to add one to many polymorphic relationship ? · pocketbase/pocketbase · Discussion #2087","url":"https://github.com/pocketbase/pocketbase/discussions/2087"},{"title":"AuthType Enum (System.DirectoryServices.Protocols) | Microsoft Learn","url":"https://learn.microsoft.com/en-us/dotnet/api/system.directoryservices.protocols.authtype?view=net-11.0-pp&viewFallbackFrom=net-9.0-pp"},{"title":"Multi authentication - MoodleDocs","url":"https://docs.moodle.org/dev/Multi_authentication"},{"title":"Multi-factor authentication","url":"https://en.wikipedia.org/wiki/Multi-factor_authentication"},{"title":"AuthType Enum","url":"https://learn.microsoft.com/zh-cn/dotnet/api/system.directoryservices.protocols.authtype?view=net-9.0"},{"title":"Authentication method and system","url":"https://patents.justia.com/patent/8087071"},{"title":"docs.cloud.google.com","url":"https://docs.cloud.google.com/java/docs/reference/jdbc-socket-factory-parent/latest/com.google.cloud.sql.AuthType"},{"title":"auth type","url":"https://developers.google.com/apps-script/reference/data-studio/auth-type?hl=bn"}]

Based on the search results, here are some relevant findings for supporting multiple authentication methods with enum table design:

## Key Findings:

**Enum-Based Authentication Design:**
The AuthType enumeration is used to specify the authentication method to use on a connection. Various systems implement authentication enums, such as AuthType enumeration with options including Anonymous, Basic, Digest Access Authentication, Distributed Password Authentication (DPA), External, Kerberos, and Windows NT Challenge/Response (NTLM).

**Multiple Authentication Methods Table:**
Moodle's approach includes a `prefix_auth_type` table with fields for id, name, isinternal, multiple (whether multiple instances are allowed), candisable, and removable. The authentication type value is stored in the 'auth' field in the user table when a user authenticates successfully.

**Supporting Multiple Auth Methods:**
One approach involves generating an authentication table associated with a user, receiving multiple distinct authentication data types, and storing them in the authentication table.

The search results show practical implementations for managing multiple authentication methods without requiring schema migrations by using enum fields and lookup tables that track available authentication types and user associations.


REMINDER: You MUST include the sources above in your response to the user using markdown hyperlinks.

{
  "query": "Go repository pattern multi-database support PostgreSQL SQLite interface DAO"
}

Web search results for query: "Go repository pattern multi-database support PostgreSQL SQLite interface DAO"

Links: [{"title":"repository-pattern · GitHub Topics · GitHub","url":"https://github.com/topics/repository-pattern?l=go"},{"title":"How to Implement the Repository Pattern in Go","url":"https://oneuptime.com/blog/post/2026-01-07-go-repository-pattern/view"},{"title":"Repository pattern in Go service | pawelgrzybek.com","url":"https://pawelgrzybek.com/repository-pattern-in-go-service/"},{"title":"Design patterns in Go's database/sql package - Eli Bendersky's website","url":"https://eli.thegreenplace.net/2019/design-patterns-in-gos-databasesql-package/"},{"title":"The Repository pattern in Go: a painless way to simplify your service logic","url":"https://threedots.tech/post/repository-pattern-in-go/"},{"title":"Repositories, transactions, and unit of work in Go | Redowan's Reflections","url":"https://rednafi.com/go/repo-txn-uow/"},{"title":"repository-pattern module - github.com/hendratommy/repository-pattern - Go Packages","url":"https://pkg.go.dev/github.com/hendratommy/repository-pattern"},{"title":"How to Implement the Repository Pattern with database/sql — Go FAQ","url":"https://www.gofaq.org/en/how-to-implement-the-repository-pattern-with-databasesql/"},{"title":"github.com","url":"https://github.com/btnguyen2k/godal"}]

I've completed the web search for your query. Here are the key findings:

## Repository Pattern in Go with Multi-Database Support

**Core Concepts:**

A Repository encapsulates the set of objects persisted in a data store and the operations performed over them, providing a more object-oriented view of the persistence layer while supporting clean separation and one-way dependency between the domain and data mapping layers.

**Multi-Database Support:**

When changing from SQLite to PostgreSQL in Go, you typically only need to import a different driver and provide a different name in the call to sql.Open. By using the repository pattern, you cannot tell whether PostgreSQL, SQLite or DynamoDB is used as storage.

**Interface-Based Design:**

In Go, a repository is just an interface, where the service depends on the interface and a concrete package implements it in separate packages. The repository implements the interface of the service, so if you ever need to change the database, this is the only place that needs changing without touching the service layer.

**DAO and Unit of Work:**

The Godal library is a Generic Database Access Layer library for Go with interface for generic Data Access Objects (DAO) and generic DAO implementation for database/sql, with ready-to-use implementations including MSSQL, MySQL, Oracle, PostgreSQL, and SQLite3.

**Best Practices:**

Unit of Work coordinates transactions across multiple repositories, ensuring that all repository operations within a transaction either succeed together or fail together.


REMINDER: You MUST include the sources above in your response to the user using markdown hyperlinks.

{
  "query": "CockroachDB vs PostgreSQL differences UUID primary key hotspot sequential ON CONFLICT upsert"
}

Web search results for query: "CockroachDB vs PostgreSQL differences UUID primary key hotspot sequential ON CONFLICT upsert"

Links: [{"title":"Speed Up SQL Queries With Primary Keys in CockroachDB | Cockroach Labs","url":"https://www.cockroachlabs.com/community/tech-talks/speed-up-sql-queries-with-primary-keys-in-cockroachdb/"},{"title":"Usage with CockroachDB | MikroORM","url":"https://mikro-orm.io/docs/usage-with-cockroachdb"},{"title":"SQL Performance Best Practices","url":"https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview"},{"title":"SERIAL - CockroachDB","url":"https://docs.cockroachlabs.com/docs/stable/serial"},{"title":"3 basic rules for choosing indexes","url":"https://www.cockroachlabs.com/blog/how-to-choose-db-index-keys/"},{"title":"sql: warn users that sequences are slower than using UUID · Issue #41258 · cockroachdb/cockroach","url":"https://github.com/cockroachdb/cockroach/issues/41258"},{"title":"How to choose a primary key","url":"https://www.cockroachlabs.com/blog/how-to-choose-a-primary-key/"},{"title":"UUID vs Serial Primary Keys in CockroachDB: Avoid Hotspots (2026) | ADHDecode","url":"https://adhdecode.com/articles/cockroachdb/cockroachdb-uuid-vs-serial-primary-key/"},{"title":"Re: Sequence vs UUID","url":"https://www.postgresql.org/message-id/96DBBB07-E34A-44FE-A98C-9FA266B1299E%40thebuild.com"}]

Links: [{"title":"opt: fix INSERT ON CONFLICT DO UPDATE with conflicts in insert rows and existing rows · Issue #60809 · cockroachdb/cockroach","url":"https://github.com/cockroachdb/cockroach/issues/60809"},{"title":"sql: UPSERT should behave like INSERT ON CONFLICT · Issue #18474 · cockroachdb/cockroach","url":"https://github.com/cockroachdb/cockroach/issues/18474"},{"title":"UPSERT - CockroachDB","url":"https://docs.cockroachlabs.com/docs/stable/upsert"},{"title":"sql: UPSERT & ON CONFLICT DO UPDATE do not support RETURNING · Issue #6637 · cockroachdb/cockroach","url":"https://github.com/cockroachdb/cockroach/issues/6637"},{"title":"PostgreSQL Compatibility","url":"https://www.cockroachlabs.com/docs/stable/postgresql-compatibility"},{"title":"SQL Upsert: What It Is, How It Works, and When to Use It","url":"https://www.cockroachlabs.com/blog/sql-upsert/"},{"title":"Support UPSERT & INSERT .. ON CONFLICT under non-serializable isolations · Issue #129835 · cockroachdb/cockroach","url":"https://github.com/cockroachdb/cockroach/issues/129835"},{"title":"sql: UPSERT behavior change in optimizer · Issue #37880 · cockroachdb/cockroach","url":"https://github.com/cockroachdb/cockroach/issues/37880"},{"title":"github.com","url":"https://github.com/cockroachdb/docs/issues/279"}]

Based on the search results, here's an overview of the key differences between CockroachDB and PostgreSQL regarding UUID primary keys, hotspots, sequential IDs, and upsert operations:

## UUID Primary Keys & Hotspot Issues

Sequential primary key patterns that work fine in traditional databases like PostgreSQL create hotspots for reads and writes in the distributed system of CockroachDB. When you insert data with sequential primary keys, all new data gets written to the same range on disk, which usually resides on a single node, creating a "hotspot" where one node becomes overwhelmed with write traffic while others sit idle.

CockroachDB recommends UUID primary keys for optimal data distribution across nodes, as using serial/auto-increment keys can lead to write hotspots. When using UUIDs, you're generating random primary keys that hit all ranges roughly equally, allowing you to take advantage of parallelism and distribute requests throughout the cluster instead of concentrating them in a hotspot on a single node.

## Key Differences Between Sequential and UUID Keys

SERIAL is provided in CockroachDB only for PostgreSQL compatibility; new applications should use UUID with the gen_random_uuid() function, which generates 128-bit values and more uniformly scatters them across ranges. CockroachDB's serial type uses unique_rowid() generating 64-bit integers, unlike PostgreSQL's serial which is int4 with a sequence.

## UPSERT vs ON CONFLICT

CockroachDB supports both UPSERT and INSERT ON CONFLICT commands. Whereas INSERT ON CONFLICT always performs a read, the UPSERT statement writes without reading, making it faster—Cockroach Labs recommends using UPSERT instead of INSERT ON CONFLICT DO UPDATE when inserting or updating columns on tables without secondary indexes.

UPSERT is CockroachDB-specific syntactic sugar that uses the primary key as the conflict index and adds a clause for each field not in the conflict index. CockroachDB has made INSERT ON CONFLICT DO UPDATE and UPSERT behave like PostgreSQL when inserting rows that conflict with each other, though the behavior does not always match PostgreSQL when insert rows also conflict with existing rows in the table.


REMINDER: You MUST include the sources above in your response to the user using markdown hyperlinks.

{
  "query": "golang-migrate vs goose vs atlas multi database migration 2025"
}

Web search results for query: "golang-migrate vs goose vs atlas multi database migration 2025"

Links: [{"title":"Picking a database migration tool for Go projects in 2023 | Atlas","url":"https://atlasgo.io/blog/2022/12/01/picking-database-migration-tool"},{"title":"Golang: Database Migration Using Atlas and Goose","url":"https://www.linkedin.com/posts/shaibushaibu_golang-database-migration-using-atlas-and-activity-7160946243400040448-FJ6Z"},{"title":"Golang: Database Migration Using Atlas and Goose","url":"https://volomn.com/blog/database-migration-using-atlas-and-goose"},{"title":"Best Database Migration Tools for Golang - DEV Community","url":"https://dev.to/shrsv/best-database-migration-tools-for-golang-ajf"},{"title":"go packages go vs go packages golang migrate migrate","url":"https://stackshare.io/stackups/go-packages-go-vs-go-packages-golang-migrate-migrate"},{"title":"golang-migrate Alternatives","url":"https://alternativeto.net/software/golang-migrate"},{"title":"go packages scripts","url":"https://stackshare.io/go-packages-golang-migrate-migrate/vs/go-packages-scripts"},{"title":"golang-migrate Alternatives","url":"https://alternativeto.net/software/golang-migrate/?p=2"},{"title":"go packages ar vs go packages goose","url":"https://stackshare.io/stackups/go-packages-ar-vs-go-packages-goose"},{"title":"github.com","url":"https://github.com/topics/migrations?l=go"}]

Based on the search results, here's a comparison of these three Go database migration tools:

## Key Characteristics

**golang-migrate**: Created in 2014, golang-migrate is one of the most famous tools for handling database migrations with support for many database drivers and migration sources, taking a simple and direct approach.

**Goose**: Created in 2012, Goose is a solid option when choosing a migration tool. It's a no-fuss, lightweight migration tool for Go that's perfect for developers who want minimal setup and SQL-based migrations without heavy dependencies.

**Atlas**: Often described as "Terraform for databases," Atlas is a migration tool that allows you to define the desired state of your database and it will plan the changes required to get your database from its current state to the desired state.

## Multi-Database Support

Goose supports PostgreSQL, MySQL, SQLite, and more. Golang migrate has support for many database drivers.

## Key Differences

Atlas supports running migrations inside a transaction and acquires a lock ensuring that only one migration happens at a time and the migration order/integrity is respected, unlike Goose. Atlas solves migration integrity problems that occur when working with multiple branches by using an Integrity file.

## Combined Approach

Interestingly, some projects use Atlas to detect and plan schema operations and output them as SQL versioned files, while using Goose to create Golang migration files and apply all migrations to the database, since Atlas has good support for Goose.


REMINDER: You MUST include the sources above in your response to the user using markdown hyperlinks.

{
  "query": "GDPR NIST PII data minimization retention policy caching third party identity data"
}

Web search results for query: "GDPR NIST PII data minimization retention policy caching third party identity data"

Links: [{"title":"PII Data Protection: Complete Guide to Personally Identifiable Information Management","url":"https://complydog.com/blog/pii-data-protection-guide-personally-identifiable-information-management"},{"title":"NIST Privacy Framework Data Minimization: The CT.DM Guide","url":"https://www.strac.io/blog/nist-privacy-framework-data-minimization"},{"title":"Data Minimization: The Complete Guide for Security and Compliance Teams","url":"https://www.strac.io/blog/data-minimization"},{"title":"PII Security in the Age of AI: Best Practices","url":"https://www.sentinelone.com/cybersecurity-101/cybersecurity/pii-security-ai-best-practices/"},{"title":"What Is PII? How to Protect Personally Identifiable Information in 2025 - SecurityScorecard","url":"https://securityscorecard.com/blog/what-is-pii-how-to-protect-personally-identifiable-information-in-2025/"},{"title":"Personally Identifiable Information (PII) vs. Personal Data","url":"https://usercentrics.com/knowledge-hub/personally-identifiable-information-vs-personal-data/"},{"title":"Privacy - NIST Pages","url":"https://pages.nist.gov/800-63-4/sp800-63a/privacy/"},{"title":"PII (Personally Identifiable Information): Best Practices and Compliance Tips","url":"https://www.accountablehq.com/post/pii-personally-identifiable-information-best-practices-and-compliance-tips"},{"title":"Personally identifiable information: PII, non-PII & personal data","url":"https://piwik.pro/blog/what-is-pii-personal-data/"}]

I've completed the web search for your query on GDPR, NIST, PII, data minimization, retention policy, caching, and third-party identity data. Here are the key findings:

## Data Minimization and Retention

Data minimization means only collecting what you need, retaining it only as long as necessary, and deleting it when the purpose ends. It is legally required under GDPR Article 5(1)(c), CPRA § 1798.100(a)(3), and NIST Privacy Framework CT.DM.

These practices require that data be collected only for identified purposes, retained only as long as necessary, destroyed on schedule, and processed to limit observability.

## GDPR Storage Limitations

Under GDPR, Article 5(1)(e) — the storage limitation principle — requires that data be "kept in a form which permits identification of data subjects for no longer than is necessary."

## NIST Guidance

NIST Guidelines provide technical definitions focused on information that can distinguish or trace individual identity, either alone or combined with other linkable information. NIST Special Publication 800-188 ("De-Identifying Government Datasets") provides technical guidance on data minimization techniques for government datasets, including suppression, generalization, pseudonymization, noise addition, and aggregation.

## Third-Party and Vendor Management

Vendor Management requires due diligence processes for third-party services that handle personal information on your behalf. Your risk assessments must account for AI systems touching PII, including documenting data flows from collection through disposal, and mapping business context including third-party processors.

## Practical Implementation

Collect only the PII you truly need, keep it only as long as required, and use it strictly for the stated purpose. Data minimization shrinks your attack surface, lowers storage and compliance costs, and simplifies incident response.


REMINDER: You MUST include the sources above in your response to the user using markdown hyperlinks.

{
  "query": "SQLite JSON column support JSON1 extension vs PostgreSQL JSONB CockroachDB JSONB"
}

Web search results for query: "SQLite JSON column support JSON1 extension vs PostgreSQL JSONB CockroachDB JSONB"

Links: [{"title":"JSON Functions And Operators","url":"https://sqlite.org/json1.html"},{"title":"JSON and JSONB support in SQLite - Fedora Magazine","url":"https://fedoramagazine.org/json-and-jsonb-support-in-sqlite-3-45-0/"},{"title":"Learn SQLite JSON Storage and Querying","url":"https://www.w3resource.com/sqlite/snippets/sqlite-jsonb.php"},{"title":"Using SQLite with JSON Data - Peter Hoffmann","url":"https://peter-hoffmann.com/2024/using-sqlite-with-json-data.html"},{"title":"JSON in SQLite: columns, functions, JSONB and indexes - FluentDB","url":"https://www.fluentdb.ai/sqlite/json"},{"title":"SQLite JSON: json_extract, json_each, and All 14 JSON Functions — Jsonic","url":"https://jsonic.io/guides/sqlite-json"},{"title":"Querying JSON in SQLite — Mako","url":"https://mako.ai/guides/sqlite/json-queries"},{"title":"DBMS > Alibaba Cloud AnalyticDB for PostgreSQL vs. CockroachDB vs. jBASE vs. Lovefield vs. SQLite","url":"https://db-engines.com/en/system/Alibaba+Cloud+AnalyticDB+for+PostgreSQL%3BCockroachDB%3BLovefield%3BSQLite%3BjBASE"}]

Based on the search results, here's a comparison of JSON support across these databases:

## SQLite JSON Support

SQLite's JSON1 support is built-in since version 3.38.0, and JSON is stored as TEXT (or sometimes BLOB), and queried or modified using SQLite's JSON1 extension.

More recently, SQLite 3.45.0 (January 2024) added JSONB, a binary representation stored in a BLOB.

## SQLite vs PostgreSQL JSONB

Key differences in storage and performance:

- **Storage Format**: SQLite stores JSON as plain TEXT — the raw JSON string is kept exactly as written with no binary encoding, so every json_extract call re-parses the string. PostgreSQL JSONB stores JSON in a decomposed binary format that strips whitespace, reorders keys, and deduplicates object keys on write; reads are faster because no parsing is required.

- **Lookup Performance**: The PostgreSQL JSONB format claims to offer O(1) lookup of elements in objects and arrays. SQLite's JSONB has O(N) time complexity for most operations in SQLite, just like text JSON.

- **Binary Compatibility**: The "JSONB" name is inspired by PostgreSQL, but the on-disk format for SQLite's JSONB is not the same as PostgreSQL's. The two formats have the same name, but are not binary compatible.

- **Indexing**: PostgreSQL JSONB has a richer operator set — the @> containment operator, the ? key-exists operator, and GIN indexes that index every key and value in a document. SQLite expression indexes cover point lookups on specific paths; PostgreSQL GIN indexes cover arbitrary key and value searches across the entire document.

The search results did not contain detailed information specifically comparing CockroachDB JSONB support, though CockroachDB is mentioned as a distributed database system.


REMINDER: You MUST include the sources above in your response to the user using markdown hyperlinks.

{
  "query": "database index strategy search by phone number name partial match trigram citext"
}

Web search results for query: "database index strategy search by phone number name partial match trigram citext"

Links: [{"title":"How to Optimize Name Search in PostgreSQL with Trigram and BTree Indexes | by Saritasa | Medium","url":"https://medium.com/@saritasa/how-to-optimize-name-search-in-postgresql-with-trigram-and-btree-indexes-02c57eb27687"},{"title":"Fuzzy Search with PostgreSQL Trigrams: Smarter Matching Beyond LIKE | by Vinod Jagwani | Medium","url":"https://medium.com/@vinodjagwani/fuzzy-search-with-postgresql-trigrams-smarter-matching-beyond-like-bce2bd3c4548"},{"title":"Partial Text Search In SQL With Indexes | Medium","url":"https://medium.com/@AlexanderObregon/partial-text-search-in-sql-with-indexes-6442c1a92535"},{"title":"Case-Insensitive Text Search in PostgreSQL: What’s Fast and What Fails | by Eresh Gorantla | CodeX | Medium","url":"https://medium.com/codex/case-insensitive-text-search-in-postgresql-whats-fast-and-what-fails-f836024c4590"},{"title":"Trigram Indexes - CockroachDB","url":"https://docs.cockroachlabs.com/docs/stable/trigram-indexes"},{"title":"Use cases for trigram indexes (when not to use Full Text Search)","url":"https://www.cockroachlabs.com/blog/use-cases-trigram-indexes/"},{"title":"Trigram search","url":"https://en.wikipedia.org/wiki/Trigram_search"},{"title":"5mins of Postgres E23: Fuzzy text search & case-insensitive ICU collations in Postgres","url":"https://pganalyze.com/blog/5mins-postgres-fuzzy-text-search-case-insensitive-ICU-collations"}]

Here are the search results for your query about database index strategies for phone number and name partial matching using trigrams and citext:

## Key Findings:

**Trigrams for Partial Matching:**
PostgreSQL's pg_trgm extension enables similarity-based matching using trigrams (3-character sequences). The trigram index is built for partial matching and will be used efficiently whether your query is LIKE '%john%', LIKE '%hn', or even without any wildcards when using the similarity operators.

**Index Types:**
PostgreSQL's GIN (Generalized Inverted Index), used for full-text or trigram search, doesn't naturally work with BTREE indexes used for filtering on fields like city or client ID. However, while the index itself is larger, the lookup time remains extremely fast even as your dataset grows into the millions, as the database uses the index to narrow down potential matches to a small candidate set before doing a final check.

**CITEXT for Case-Insensitive Matching:**
Since CITEXT wraps case insensitivity for equality, this BTREE index works great for login or account lookup. However, trigram GIN indexes don't handle case normalization unless combined with LOWER().

**Combined Strategies:**
When searching by multiple fields (like full name and city) it's best to use a combined index. Partial indexes can shrink index size and improve performance by indexing only the rows you care about.

These results show that trigram indexes are optimal for partial text search, while CITEXT works well for case-insensitive exact matching.


REMINDER: You MUST include the sources above in your response to the user using markdown hyperlinks.

{
  "query": "NIST 800-63B password storage hashing salted credential table design"
}

Web search results for query: "NIST 800-63B password storage hashing salted credential table design"

Links: [{"title":"NIST password guidelines: SP 800-63B explained","url":"https://netwrix.com/en/resources/blog/nist-password-guidelines/"},{"title":"NIST Special Publication 800-63B","url":"https://pages.nist.gov/800-63-4/sp800-63b.html"},{"title":"800-63-3/sp800-63b/sec5_authenticators.md at nist-pages · usnistgov/800-63-3","url":"https://github.com/usnistgov/800-63-3/blob/nist-pages/sp800-63b/sec5_authenticators.md"},{"title":"NIST Password Guidelines: What to Know in 2026","url":"https://www.itsasap.com/blog/nist-password-guidelines"},{"title":"NIST Password Guidelines: Simplified Steps for Compliance - Security Boulevard","url":"https://securityboulevard.com/2025/12/nist-password-guidelines-simplified-steps-for-compliance/"},{"title":"From Draft to Final: What’s New in NIST’s Latest Password Guidance? - Intercede","url":"https://www.intercede.com/from-draft-to-final-whats-new-in-nists-latest-password-guidance/"},{"title":"Date updated: July 24, 2025 Withdrawn NIST Technical Series Publication","url":"https://nvlpubs.nist.gov/nistpubs/specialpublications/nist.sp.800-63b.pdf"},{"title":"NIST Password Guidelines and Best Practices for 2020","url":"https://auth0.com/blog/dont-pass-on-the-new-nist-password-guidelines/"},{"title":"\"Do this! Do that!, And nothing will happen\" Do specifications lead to securely stored passwords?","url":"https://arxiv.org/pdf/2102.09790"}]

Based on the search results, here's what NIST 800-63B specifies regarding password storage, hashing, salting, and credential storage design:

## Password Storage Requirements

Verifiers must store passwords in a form that is resistant to offline attacks, and passwords must be salted and hashed using a suitable password hashing scheme.

## Salt Requirements

The salt value must be at least 32 bits in length and arbitrarily chosen to minimize salt value collisions among stored hashes. Both the salt value and the resulting hash must be stored for each password.

## Approved Hashing Algorithms

Approved algorithms include PBKDF2, Argon2, and bcrypt, all designed to make offline cracking computationally expensive.

## Cost Factor Considerations

Password hashing schemes take a password, a salt, and a cost factor as inputs and generate a password hash. Their purpose is to make each password guess more expensive for an attacker who has obtained a hashed password file, thereby making the cost of a guessing attack high or prohibitive. The chosen cost factor should be as high as practical without negatively impacting verifier performance and should be increased over time to account for increases in computing performance.

## Additional Storage Recommendations

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.


REMINDER: You MUST include the sources above in your response to the user using markdown hyperlinks.

{
  "file_path": "/Users/warrenthompson/Source/LoginID/05-data-ops/research-data-ops-best-practices.md",
  "content": "# Data Ops Research: Multi-Database DAO for `user_profile` / `user_credential`\n\n**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`.\n\n---\n\n## 1. Schema design: splitting `user_profile` from `user_credential`\n\nThe assignment already names two separate entities, and that split matches common practice rather than being an arbitrary requirement.\n\n**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:\n\n- 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).\n- 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).\n- It supports one profile having *multiple* credentials (multiple login methods, multiple linked IDP accounts) — a 1:N relationship, not 1:1.\n- 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.\n\n**Column choices, in line with what's asked:**\n\n`user_profile`\n- `id` (primary key — see §2 for type choice)\n- `name` (or split `first_name`/`last_name` if search-by-name matters — see §4)\n- `phone`\n- `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)\n- `created_at`, `updated_at`\n- optional: `source` / `origin` flag distinguishing profile rows created directly vs. hydrated from an IDP connector, which matters for the retention discussion in §5\n\n`user_credential`\n- `id` (primary key)\n- `user_id` (FK → `user_profile.id`)\n- `username`\n- `method` (see below — the auth-method-extensibility column)\n- `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)\n- `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)\n\n**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`).\n\n**Modeling `method` for extensibility:** The assignment explicitly wants room for auth methods beyond password. Two established patterns, in ascending order of flexibility:\n\n1. **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).\n2. **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.\n\nFor 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.\n\n**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.\n\nSources:\n- https://www.donedone.com/blog/building-the-optimal-user-database-model-for-your-application\n- https://authrim.com/features/pii-separation/\n- https://docs.moodle.org/dev/Multi_authentication\n- https://www.corbado.com/blog/passkey-webauthn-database-guide\n- https://pages.nist.gov/800-63-4/sp800-63b.html\n\n---\n\n## 2. Multi-database abstraction strategy (PostgreSQL / CockroachDB / SQLite)\n\n**Pattern: repository interface, one implementation per driver.** This is the standard Go approach and matches what the assignment implies:\n\n- Define a `ProfileRepository` / `CredentialRepository` interface at the domain/service layer (`Get`, `Search`, `Create`, `Update`, `Delete` — signatures in Go types, no SQL in the interface).\n- 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.\n- 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.\n- 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.\n\n**Dialect differences that actually matter for this schema:**\n\n| Concern | PostgreSQL | CockroachDB | SQLite |\n|---|---|---|---|\n| 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 |\n| **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. |\n| 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/) |\n| **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. |\n| 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 |\n| **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. |\n| 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. |\n| 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. |\n\nSources:\n- https://www.cockroachlabs.com/blog/how-to-choose-a-primary-key/\n- https://github.com/cockroachdb/cockroach/issues/41258\n- https://adhdecode.com/articles/cockroachdb/cockroachdb-uuid-vs-serial-primary-key/\n- https://docs.cockroachlabs.com/docs/stable/upsert\n- https://www.cockroachlabs.com/blog/sql-upsert/\n- https://www.cockroachlabs.com/docs/stable/postgresql-compatibility\n- https://sqlite.org/json1.html\n- https://fedoramagazine.org/json-and-jsonb-support-in-sqlite-3-45-0/\n- https://threedots.tech/post/repository-pattern-in-go/\n- https://pawelgrzybek.com/repository-pattern-in-go-service/\n- https://github.com/btnguyen2k/godal\n\n---\n\n## 3. Migration tooling\n\nThree commonly used Go-ecosystem tools, per current (2025–2026) usage:\n\n- **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.\n- **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).\n- **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).\n\n**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:\n- 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).\n- 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.\n- **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.\n\nSources:\n- https://atlasgo.io/blog/2022/12/01/picking-database-migration-tool\n- https://dev.to/shrsv/best-database-migration-tools-for-golang-ajf\n- https://volomn.com/blog/database-migration-using-atlas-and-goose\n\n---\n\n## 4. Indexing for \"search and retrieve\" (question 2's REST API)\n\nThe API (question 2) needs to search/retrieve `user_profile` by at least name and phone, per the assignment. Indexing implications:\n\n- **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.\n- **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.\n- **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).\n- **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.\n- **`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.\n\nRecommended index list for the schema:\n- `user_profile`: B-Tree on `phone`; trigram GIN on `name` (Postgres/CockroachDB only — SQLite falls back to full scan or FTS5)\n- `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\n\nSources:\n- https://medium.com/@saritasa/how-to-optimize-name-search-in-postgresql-with-trigram-and-btree-indexes-02c57eb27687\n- https://docs.cockroachlabs.com/docs/stable/trigram-indexes\n- https://www.cockroachlabs.com/blog/use-cases-trigram-indexes/\n- https://medium.com/codex/case-insensitive-text-search-in-postgresql-whats-fast-and-what-fails-f836024c4590\n\n---\n\n## 5. PII data governance\n\nTwo distinct concerns: governing `user_profile` PII in general, and governing PII **cached** from the third-party IDP connector's `/identity` response (question 3).\n\n**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).\n\n**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).\n\n**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:\n- 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.\n- 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/).\n- 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.\n\n**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.\n\nSources:\n- https://www.strac.io/blog/data-minimization\n- https://www.strac.io/blog/nist-privacy-framework-data-minimization\n- https://complydog.com/blog/pii-data-protection-guide-personally-identifiable-information-management\n- https://securityscorecard.com/blog/what-is-pii-how-to-protect-personally-identifiable-information-in-2025/\n- https://pages.nist.gov/800-63-4/sp800-63b.html\n\n---\n\n## 6. Recommendations for this assignment\n\n**Schema sketch (columns, not DDL):**\n\n`user_profile`\n- `id` — UUID, primary key\n- `name`\n- `phone`\n- `street_address`\n- `locality`\n- `region`\n- `postal_code`\n- `country`\n- `source` — enum/string: `'direct'` | `'idp_cache'` (or similar), supporting retention policy targeting\n- `created_at`, `updated_at`\n\n`user_credential`\n- `id` — UUID, primary key\n- `user_id` — UUID, FK → `user_profile.id`\n- `username` — unique\n- `method_id` — FK → `auth_method.id` (lookup-table pattern, preferred over a native DB enum for portability and zero-migration extensibility)\n- `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)\n- `hash_algo`, `hash_cost` — metadata to support future re-hashing without a schema change\n- `created_at`, `updated_at`\n\n`auth_method` (lookup table)\n- `id`, `name` (`'password'`, `'passkey'`, `'otp'`, `'oidc'`, …), `requires_secret` (bool), `is_active` (bool)\n\n**Multi-database abstraction approach for the engineering track to implement:**\n- 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.\n- UUID primary keys everywhere (required for CockroachDB hotspot avoidance, portable to the other two) rather than branching PK strategy per backend.\n- `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.\n- 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.\n- 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).\n- 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`.\n- 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.\n"
}

File created successfully at: /Users/warrenthompson/Source/LoginID/05-data-ops/research-data-ops-best-practices.md (file state is current in your context — no need to Read it back)