---
phase: 02-authentication
plan: "01"
subsystem: backend/auth-substrate
tags: [go, postgres, sqlc, migration, auth-foundation, goose, citext, uuid]
dependency_graph:
  requires: [01-foundation]
  provides: [auth-migration, sqlc-bindings, auth-package-skeleton, test-harness]
  affects: [02-02, 02-03, 02-04, 02-05, 02-06, 02-07]
tech_stack:
  added:
    - github.com/pressly/goose/v3 v3.27.1 (test harness — per-schema migration isolation)
  patterns:
    - per-test isolated schema via unique schemaName + schema-scoped DSN
    - goose.SetTableName for per-test version table isolation (no public-schema collision)
    - sqlc citext→string + uuid→uuid.UUID overrides (Pattern 10)
key_files:
  created:
    - backend/migrations/0002_auth.sql
    - backend/internal/db/queries/users.sql
    - backend/internal/db/queries/sessions.sql
    - backend/internal/auth/doc.go
    - backend/internal/auth/types.go
    - backend/internal/auth/testdb_test.go
  modified:
    - backend/sqlc.yaml (added overrides block)
    - backend/.env.example (added TEST_DATABASE_URL + SESSION_SECRET)
    - backend/go.mod + go.sum (added pressly/goose/v3)
decisions:
  - "Consolidated internal/auth package (not split with internal/session) — RESEARCH Open Question 3 resolved"
  - "Test harness uses compose Postgres + schema isolation (not testcontainers-go) — RESEARCH Open Question 1 resolved"
  - "goose.SetTableName per test-schema prevents collision with public goose_db_version table"
  - "goose v3.27.1 pinned to match justfile CLI version"
  - "schemaName uses UUID[:12] with hyphens replaced by underscores for valid SQL identifiers"
metrics:
  duration: "~10 minutes"
  completed_date: "2026-05-14"
  tasks_completed: 3
  tasks_total: 3
  files_created: 6
  files_modified: 3
---

# Phase 2 Plan 01: Auth Substrate (Migration + sqlc + Package Skeleton) Summary

**One-liner:** Postgres migration with citext users+sessions tables, sqlc type-safe bindings with citext→string and uuid→uuid.UUID overrides, and internal/auth package with types/constants/sentinel-errors plus a per-test-schema Postgres harness.

## What Was Built

### Task 1: Migration 0002_auth.sql
Created `backend/migrations/0002_auth.sql` with goose Up/Down annotations:
- `CREATE EXTENSION IF NOT EXISTS citext` + `pgcrypto` (defensive for gen_random_uuid)
- `users` table: id uuid PK, email citext UNIQUE, password_hash text, created_at/updated_at timestamptz (D-01)
- `sessions` table: id text PK (SHA-256 hex per D-05), user_id FK ON DELETE CASCADE, created_at/expires_at (D-04)
- Indexes: `sessions_user_id_idx` and `sessions_expires_at_idx`
- No deleted_at, email_verified_at, user_agent, ip_address, last_seen_at (D-02, D-03, D-04)
- Up/Down round-trip verified against compose Postgres (goose Up → Down → Up exits 0)

### Task 2: sqlc.yaml overrides + query files + generated bindings
- `sqlc.yaml`: added `overrides` block mapping `citext→string` and `uuid→uuid.UUID` (Pattern 10, Pitfall 3)
- `users.sql`: InsertUser :one + GetUserByEmail :one
- `sessions.sql`: InsertSession :exec, GetSessionWithUser :one (with `expires_at > now()` per D-07), DeleteSession :exec, DeleteSessionsByUser :exec, ExtendSession :exec
- `sqlc generate` produces `Email string` (not pgtype.Text) and `uuid.UUID` (not pgtype.UUID) — citext and uuid overrides verified
- Generated files are gitignored (per Phase 1 .gitignore) and regenerated by `sqlc generate`

### Task 3: internal/auth package skeleton + test harness + env docs
- `auth/doc.go`: package comment — consolidated layout decision documented
- `auth/types.go`: User struct, Session struct, SessionCookieName (D-12), SessionTTL 30d (D-09), SessionExtendThreshold 7d (D-09), ErrSessionNotFound, ErrInvalidHash, ErrIncompatibleVersion
- `auth/testdb_test.go`: `setupTestDB(t)` creates unique schema (test_\<uuid12\>), runs goose Up with schema-specific version table via `goose.SetTableName`, returns pool + cleanup. `TestSetupTestDB_Roundtrip` pings pool and verifies users table visibility. Test skips when neither TEST_DATABASE_URL nor DATABASE_URL is set.
- `.env.example`: added `TEST_DATABASE_URL` and `SESSION_SECRET` with generation instructions
- `go.mod`: added `github.com/pressly/goose/v3 v3.27.1` as direct dependency

## Decisions Made

1. **Consolidated internal/auth package** (not split with internal/session): all auth capabilities (password, session, ratelimit, cookie, csrf) live in one package. Phase 1's `internal/session/doc.go` placeholder is kept as-is (one-liner pointing at `internal/auth`). See RESEARCH Open Question 3.

2. **compose Postgres + schema isolation** (not testcontainers-go): reuses the existing compose Postgres via `TEST_DATABASE_URL`. Per-test isolation via unique schemas avoids testcontainers podman friction (RESEARCH Open Question 1, Assumption A7).

3. **goose.SetTableName per test**: each test schema gets its own goose version table (`test_<uuid>_goose_version`) so the public `goose_db_version` (tracking production migration state) is never read/modified during test runs.

4. **UUID hyphens stripped from schema/table names**: `uuid.New().String()[:12]` can contain hyphens which are invalid in unquoted SQL identifiers; replaced with underscores.

5. **goose v3.27.1 pinned**: matches the `goose_version` in `backend/justfile` CLI installation.

## Deviations from Plan

None — plan executed exactly as written. All acceptance criteria verified.

## Threat Flags

No new security-relevant surface introduced. This plan only creates:
- Schema migration (DDL only — no network endpoints)
- sqlc query files and generated bindings (parameterized queries prevent SQL injection — T-2-06 defense)
- Internal Go package with types and test helpers

T-2-06 mitigated: sessions.id column is designed for SHA-256(token) hex per D-05; sqlc query `GetSessionWithUser` includes `AND expires_at > now()` gate (D-07 lazy expiry).
T-2-11 mitigated: goose Up/Down round-trip exercised and exits 0.
T-2-12 mitigated: `.env.example` SESSION_SECRET has empty placeholder value only; real value comes from `.env` (gitignored since Phase 1).

## Commits

| Task | Commit | Description |
|------|--------|-------------|
| 1 | 513044d | feat(02-01): add 0002_auth.sql migration |
| 2 | 799c260 | feat(02-01): add sqlc queries + citext/uuid overrides |
| 3 | 2c84f42 | feat(02-01): create internal/auth package skeleton, test DB harness, env docs |

## Self-Check: PASSED

Files verified:
- backend/migrations/0002_auth.sql ✓
- backend/internal/db/queries/users.sql ✓
- backend/internal/db/queries/sessions.sql ✓
- backend/internal/auth/doc.go ✓
- backend/internal/auth/types.go ✓
- backend/internal/auth/testdb_test.go ✓
- backend/sqlc.yaml ✓
- backend/.env.example ✓

Commits verified: 513044d, 799c260, 2c84f42 all present in git log.
