familysync/.planning/milestones/v1.1-phases/19-local-auth-no-oidc-mode/19-01-PLAN.md

phase, plan, type, wave, depends_on, files_modified, autonomous, requirements, user_setup, must_haves

phase	plan	type	wave	depends_on	files_modified	autonomous	requirements	user_setup	must_haves
19-local-auth-no-oidc-mode	01	tdd	1		apps/api/src/db/schema.ts apps/api/src/db/migrations/0003_local_credentials.sql apps/api/src/auth/localCredentials.ts apps/api/src/auth/localSession.ts apps/api/src/lib/bootGuards.ts apps/api/src/index.ts scripts/generate-secrets.mjs .dockerignore apps/api/tests/auth/localCredentials.test.ts apps/api/tests/auth/localSession.test.ts apps/api/test/setup.ts	false	AUTH-LOCAL-01 AUTH-LOCAL-02	service why env_vars env New env-floor secret for signing local-session JWTs (D-05). Operator must add LOCAL_SESSION_SECRET to docker-compose env (>=32 chars). generate-secrets.mjs emits a value to copy. name source LOCAL_SESSION_SECRET Generate with `node scripts/generate-secrets.mjs` (this plan extends it to emit LOCAL_SESSION_SECRET) or `openssl rand -base64 32`	truths artifacts key_links A password can be hashed and the same password verifies true; a wrong password verifies false verifyPassword returns false (never throws) on a malformed stored hash A signed local-session JWT round-trips: issue then verify returns the same userId An expired or tampered local-session token verifies to null, never throws The API process refuses to boot (exit 1) when LOCAL_SESSION_SECRET is missing/short and dev-bypass is off The local_credentials table exists after migration with unique user_id and unique username path provides exports min_lines apps/api/src/auth/localCredentials.ts hashPassword + verifyPassword (node:crypto scrypt, PHC-encoded) hashPassword verifyPassword 25 path provides exports min_lines apps/api/src/auth/localSession.ts issueLocalSessionCookie + verifyLocalSessionCookie + clearLocalSessionCookie issueLocalSessionCookie verifyLocalSessionCookie clearLocalSessionCookie 30 path provides contains apps/api/src/db/migrations/0003_local_credentials.sql additive CREATE TABLE local_credentials CREATE TABLE path provides contains apps/api/src/db/schema.ts localCredentials Drizzle table export localCredentials path provides contains apps/api/src/lib/bootGuards.ts assertLocalSessionSecretSet boot guard assertLocalSessionSecretSet from to via pattern apps/api/src/auth/localSession.ts process.env.LOCAL_SESSION_SECRET Jwt.sign / Jwt.verify HS256 using the env secret LOCAL_SESSION_SECRET from to via pattern apps/api/src/index.ts apps/api/src/lib/bootGuards.ts assertLocalSessionSecretSet() called in isMainModule() boot block assertLocalSessionSecretSet from to via pattern apps/api/src/db/schema.ts apps/api/src/db/migrations/0003_local_credentials.sql drizzle-kit generate emits SQL from the localCredentials table local_credentials

Build the Phase 19 local-auth foundation: the `local_credentials` table + migration, the password hashing primitives, the stateless JWT session-cookie helpers, the new `LOCAL_SESSION_SECRET` env var + boot-time assertion, and the D-15 image-hygiene fix for the break-glass script directory.

Purpose: Every other Phase 19 plan depends on these primitives. Hashing and session signing are security-critical with defined I/O — prime TDD candidates (RED before GREEN). This plan also closes the two D-15 gaps the researcher flagged (scripts/ not in .dockerignore; LOCAL_SESSION_SECRET not in generate-secrets) so no later plan ships a dev artifact.

Output: localCredentials.ts, localSession.ts, the schema table + 0003 migration, the assertLocalSessionSecretSet boot guard wired in index.ts, generate-secrets emitting LOCAL_SESSION_SECRET, and .dockerignore excluding the break-glass scripts.

Derived REQ-IDs covered: AUTH-LOCAL-01 (local_credentials schema + migration, per D-09), AUTH-LOCAL-02 (scrypt hash/verify, per D-08). Also lands the LOCAL_SESSION_SECRET env + boot assertion (D-05) and D-15 hygiene preconditions.

<execution_context> @$HOME/.claude/gsd-core/workflows/execute-plan.md @$HOME/.claude/gsd-core/templates/summary.md </execution_context>

@.planning/PROJECT.md @.planning/ROADMAP.md @.planning/STATE.md @.planning/phases/19-local-auth-no-oidc-mode/19-RESEARCH.md @.planning/phases/19-local-auth-no-oidc-mode/19-PATTERNS.md @.planning/phases/19-local-auth-no-oidc-mode/19-CONTEXT.md Task 1: hashPassword / verifyPassword (node:crypto scrypt, PHC-encoded) - apps/api/src/auth/user.ts (analog imports + module style; localCredentials.ts substitutes node:crypto for the drizzle imports) - .planning/phases/19-local-auth-no-oidc-mode/19-RESEARCH.md §Password Hashing Pattern (the verified scrypt + PHC implementation) - .planning/phases/19-local-auth-no-oidc-mode/19-PATTERNS.md §apps/api/src/auth/localCredentials.ts (PHC params N=16384 r=8 p=1 KEY_LEN=32) - apps/api/tests/auth/user.test.ts (vitest unit test style for auth helpers) apps/api/src/auth/localCredentials.ts, apps/api/tests/auth/localCredentials.test.ts - RED first: write apps/api/tests/auth/localCredentials.test.ts asserting: - Test 1: verifyPassword(hashPassword('hunter2'), 'hunter2') === true - Test 2: verifyPassword(hashPassword('hunter2'), 'wrong') === false - Test 3: two hashPassword('x') calls produce different encoded strings (unique salt) - Test 4: verifyPassword('not-a-valid-hash', 'x') === false (no throw) - Test 5: a hash encodes 'scrypt' + N + r + p + salt + hash joined by '$' (6 segments) - Run the suite; confirm it FAILS (module not yet implemented). Create apps/api/src/auth/localCredentials.ts exporting `hashPassword(password: string): string` and `verifyPassword(storedEncoded: string, candidate: string): boolean`. Import `scryptSync`, `randomBytes`, `timingSafeEqual` from `node:crypto` — no npm deps (D-08). Constants: SCRYPT_N=16384, SCRYPT_R=8, SCRYPT_P=1, KEY_LEN=32. hashPassword: 16-byte random salt, scryptSync to KEY_LEN, return `['scrypt', N, r, p, salt.toString('base64url'), hash.toString('base64url')].join('$')`. verifyPassword: split on '$', parse params, re-derive with scryptSync using `storedHash.length` as keylen (so buffers are equal length for timingSafeEqual), return `timingSafeEqual(storedHash, candidateHash)` inside try/catch that returns false on any error. Do not log the password. After implementing, run the suite — it must pass (GREEN). pnpm --filter @familysync/api test tests/auth/localCredentials.test.ts - `pnpm --filter @familysync/api test tests/auth/localCredentials.test.ts` exits 0 with all 5 tests green - Source assertion: `grep -c "node:crypto" apps/api/src/auth/localCredentials.ts` >= 1 and the file contains no `import` from any npm auth/hash package - Source assertion: `grep -c "timingSafeEqual" apps/api/src/auth/localCredentials.ts` == 1 hashPassword/verifyPassword implemented with scrypt + timingSafeEqual; all unit tests pass; zero new dependencies. Task 2: localSession.ts JWT cookie helpers + LOCAL_SESSION_SECRET boot guard - apps/api/src/auth/persistSessionCookie.ts (exact analog: setCookie attributes httpOnly/secure/sameSite/maxAge; cookie-name resolution) - apps/api/tests/auth/persistSessionCookie.test.ts (test harness for cookie middleware/helpers) - .planning/phases/19-local-auth-no-oidc-mode/19-RESEARCH.md §JWT Session Cookie Pattern + §Common Pitfalls 8/9/10 (Jwt namespace import; verify throws on expiry; missing-secret boot guard) - apps/api/src/lib/bootGuards.ts (assertNotDevBypassInProduction structure to mirror) - apps/api/src/index.ts lines 121-140 (isMainModule + assertNotDevBypassInProduction call site) apps/api/src/auth/localSession.ts, apps/api/src/lib/bootGuards.ts, apps/api/src/index.ts, apps/api/tests/auth/localSession.test.ts - RED first: write apps/api/tests/auth/localSession.test.ts asserting (set process.env.LOCAL_SESSION_SECRET to a >=32-char test value in the test): - Test 1: issue then verify round-trips userId (use a minimal Hono Context mock or a real Hono app route that sets then reads the cookie) - Test 2: verifyLocalSessionCookie returns null when no local-session cookie present (no throw) - Test 3: verifyLocalSessionCookie returns null for a tampered/garbage token (no throw — covers Pitfall 9 expiry/throw path) - Test 4 (bootGuards): assertLocalSessionSecretSet does NOT exit when DEV_AUTH_BYPASS='true' even if secret unset; and the function is exported - Run the suite; confirm FAIL. Create apps/api/src/auth/localSession.ts. Import `{ Jwt }` from `hono/utils/jwt` (namespace import — NOT named sign/verify, per Pitfall 8). Import `setCookie, getCookie, deleteCookie` from `hono/cookie`, `Context` type from `hono`. Cookie name constant `local-session` (distinct from `oidc-auth` — Pitfall 4). `SESSION_MAX_AGE_SECONDS = Number(process.env.LOCAL_SESSION_EXPIRES ?? 86400)`. Export async `issueLocalSessionCookie(c, userId)`: read `process.env.LOCAL_SESSION_SECRET`, throw if unset; sign `{ userId, iat, exp }` HS256; setCookie with httpOnly:true, secure:(NODE_ENV==='production'), sameSite:'Lax', path:'/', maxAge. Export async `verifyLocalSessionCookie(c): Promise`: return null if no secret or no cookie; try Jwt.verify and return `payload.userId` when numeric, catch → return null. Export `clearLocalSessionCookie(c)`: deleteCookie with matching path/httpOnly/secure/sameSite attributes.

In apps/api/src/lib/bootGuards.ts add `export function assertLocalSessionSecretSet(): void`: return early when `process.env.DEV_AUTH_BYPASS === 'true'` (bypass issues no real secret-signed cookie in dev); otherwise if `LOCAL_SESSION_SECRET` is unset or shorter than 32 chars, console.error a FATAL message and `process.exit(1)`. Mirror the assertNotDevBypassInProduction structure exactly.

In apps/api/src/index.ts, import `assertLocalSessionSecretSet` and call it inside the existing `isMainModule()` boot block immediately AFTER the existing `assertNotDevBypassInProduction()` call (around line 136). Do not change any other boot behavior. Run the suite — it must pass (GREEN).

pnpm --filter @familysync/api test tests/auth/localSession.test.ts && pnpm --filter @familysync/api typecheck - `pnpm --filter @familysync/api test tests/auth/localSession.test.ts` exits 0, all 4 tests green - Source assertion: `grep -c "import { Jwt }" apps/api/src/auth/localSession.ts` == 1 (namespace import, Pitfall 8) - Source assertion: localSession.ts cookie name is `local-session` (grep `'local-session'`) and is NOT `oidc-auth` - Source assertion: `grep -c "assertLocalSessionSecretSet" apps/api/src/index.ts` >= 1 (wired at boot) - `pnpm --filter @familysync/api typecheck` exits 0 localSession helpers issue/verify/clear the local-session JWT cookie; verify never throws; LOCAL_SESSION_SECRET boot guard added and wired in index.ts. Task 3: local_credentials schema + 0003 migration + generate-secrets + .dockerignore (D-15) - apps/api/src/db/schema.ts (memberCredentials block — the exact template; confirm int/varchar/timestamp/unique/index already imported) - apps/api/src/db/migrations/0002_lethal_millenium_guard.sql (additive-migration example shape) - apps/api/test/setup.ts (afterEach TRUNCATE list — localCredentials must be added so tests reset it) - scripts/generate-secrets.mjs (existing secret-emitter to extend with LOCAL_SESSION_SECRET) - .dockerignore (currently excludes only apps/api/scripts/seed-credential.mjs — the break-glass dir is NOT covered; D-15 / RESEARCH open question 4) - .planning/phases/19-local-auth-no-oidc-mode/19-PATTERNS.md §apps/api/src/db/schema.ts (localCredentials table definition) apps/api/src/db/schema.ts, apps/api/src/db/migrations/0003_local_credentials.sql, apps/api/test/setup.ts, scripts/generate-secrets.mjs, .dockerignore In apps/api/src/db/schema.ts add and export `localCredentials = mysqlTable('local_credentials', {...})` mirroring memberCredentials: `id` autoincrement PK; `userId` int('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }); `username` varchar('username', { length: 128 }).notNull(); `passwordHash` varchar('password_hash', { length: 256 }).notNull(); `createdAt` timestamp defaultNow().notNull(); `updatedAt` timestamp defaultNow().onUpdateNow(). Indexes/constraints: `unique('uniq_local_cred_user').on(t.userId)`, `unique('uniq_local_cred_username').on(t.username)`, `index('idx_local_credentials_user_id').on(t.userId)`. No new imports needed.

Generate the migration: run `pnpm --filter @familysync/api db:generate` to emit apps/api/src/db/migrations/0003_local_credentials.sql. Review it — it MUST be purely additive (CREATE TABLE local_credentials only; no ALTER/DROP/TRUNCATE on existing tables). Drizzle generate+migrate, never push (established rule). Commit the generated SQL as an artifact.

In apps/api/test/setup.ts add `local_credentials` to the afterEach TRUNCATE set so unit/integration tests reset it between runs.

In scripts/generate-secrets.mjs add a `LOCAL_SESSION_SECRET` line emitting a base64 32-byte value (same generation approach as the existing SESSION_SECRET / encryption key it already emits), so an operator copies it into env (D-05 / Pitfall 10).

In .dockerignore add a line `apps/api/scripts/` (exclude the entire break-glass scripts dir) so the future reset-admin.ts can never ship in the prod image (D-15, IMG-02). Keep the existing `apps/api/scripts/seed-credential.mjs` line or let the dir exclusion supersede it.

pnpm --filter @familysync/api db:migrate && pnpm --filter @familysync/api test tests/db 2>/dev/null || pnpm --filter @familysync/api test - File exists: `apps/api/src/db/migrations/0003_local_credentials.sql` and `grep -c "CREATE TABLE" apps/api/src/db/migrations/0003_local_credentials.sql` >= 1 - Negative assertion: the 0003 SQL contains no `DROP TABLE` and no `TRUNCATE` (grep -c each == 0) - `pnpm --filter @familysync/api db:migrate` exits 0 (table applied to dev DB) - Source assertion: `grep -c "localCredentials" apps/api/src/db/schema.ts` >= 1 and the export is present - Source assertion: `grep -c "apps/api/scripts/$" .dockerignore` >= 1 OR `.dockerignore` contains a line `apps/api/scripts/` excluding the dir - Source assertion: `grep -c "LOCAL_SESSION_SECRET" scripts/generate-secrets.mjs` >= 1 - Source assertion: `grep -c "local_credentials" apps/api/test/setup.ts` >= 1 local_credentials table defined + migrated; generate-secrets emits LOCAL_SESSION_SECRET; .dockerignore excludes the break-glass scripts dir; test teardown truncates the new table. Task 4: Verify the 0003 migration is purely additive + LOCAL_SESSION_SECRET set Pause for human review of the generated migration SQL and the local env before proceeding. This is a blocking checkpoint — the executor performs no code change here; it presents the migration and waits for approval. The 0003 migration was generated by drizzle-kit and applied to the dev DB. Because Drizzle's generate step can occasionally emit unexpected ALTER/DROP statements against populated MariaDB (the exact reason this repo forbids `push`), the generated SQL needs a human eyeball before it is trusted as a committed artifact. 1. Open apps/api/src/db/migrations/0003_local_credentials.sql. 2. Confirm it contains ONLY a `CREATE TABLE local_credentials (...)` statement with the two UNIQUE constraints (uniq_local_cred_user, uniq_local_cred_username) and the user_id index. 3. Confirm there is NO statement touching users, member_credentials, calendars, calendar_events, app_config, or any existing table (no ALTER, DROP, RENAME, TRUNCATE). 4. Confirm LOCAL_SESSION_SECRET is set in your local .env (>=32 chars) — without it the API will refuse to boot in non-bypass mode. Type "approved" if the migration is purely additive and LOCAL_SESSION_SECRET is set, or describe what the migration unexpectedly touches.

<threat_model>

Trust Boundaries

Boundary	Description
operator env → API process	LOCAL_SESSION_SECRET and scrypt run server-side only; never returned to a client
build context → published image	.dockerignore is the boundary that keeps dev/break-glass artifacts out of the prod image

STRIDE Threat Register (ASVS L1, block on high)

Threat ID	Category	Component	Disposition	Mitigation Plan
T-19-01	Information Disclosure	password hashing	mitigate	scrypt + 16-byte per-hash random salt; timingSafeEqual; no password in logs (V2/V6 ASVS L1)
T-19-02	Spoofing	local-session JWT	mitigate	HS256 signed with LOCAL_SESSION_SECRET; verify rejects tampered tokens (returns null) (V3)
T-19-03	Elevation of Privilege	missing LOCAL_SESSION_SECRET	mitigate	assertLocalSessionSecretSet boot guard: refuse to start (exit 1) when unset/<32 chars in non-bypass mode (Pitfall 10)
T-19-04	Tampering	dev/break-glass artifact in prod image	mitigate	.dockerignore excludes apps/api/scripts/ (IMG-02); tests/ already excluded; defense-in-depth for D-15
T-19-SC	Tampering	npm installs	mitigate	Zero new packages this phase (RESEARCH §Standard Stack); nothing to vet
</threat_model>

- `pnpm --filter @familysync/api test` green (includes the two new unit suites) - `pnpm --filter @familysync/api typecheck` exits 0 - `pnpm --filter @familysync/api db:migrate` applies 0003 cleanly - Human checkpoint confirms the migration is purely additive

<success_criteria>

• AUTH-LOCAL-01: local_credentials table exists with unique user_id + unique username (migration applied)
• AUTH-LOCAL-02: hashPassword/verifyPassword pass round-trip, wrong-password, malformed-hash, and unique-salt tests
• LOCAL_SESSION_SECRET present in generate-secrets.mjs; boot guard wired in index.ts
• .dockerignore excludes apps/api/scripts/ (D-15) </success_criteria>

<artifacts_produced>

Artifacts this phase produces (Plan 01)

• Table: local_credentials (columns: id, user_id [UNIQUE, FK→users.id cascade], username [UNIQUE], password_hash, created_at, updated_at)
• Migration: apps/api/src/db/migrations/0003_local_credentials.sql
• Functions: hashPassword, verifyPassword (apps/api/src/auth/localCredentials.ts)
• Functions: issueLocalSessionCookie, verifyLocalSessionCookie, clearLocalSessionCookie (apps/api/src/auth/localSession.ts)
• Function: assertLocalSessionSecretSet (apps/api/src/lib/bootGuards.ts)
• Env var: LOCAL_SESSION_SECRET (env-only; never in app_config/DB)
• Cookie: local-session (httpOnly, Secure in prod, SameSite=Lax)
• Schema export: localCredentials
• .dockerignore: apps/api/scripts/ exclusion (D-15) </artifacts_produced>

Create `.planning/phases/19-local-auth-no-oidc-mode/19-01-SUMMARY.md` when done