SCRUM-198 — admin-auth database schema¶

Plan ref: AA-2 (docs/11-admin-plane-plan.md). Stacked on SCRUM-197.

What changed¶

New migration services/admin_auth/migrations/00002_staff_schema.sql. (00001 stays the AA-1 no-op so any database that already ran SCRUM-197's migrate upgrades cleanly — verified below.)

Table	Purpose	Key constraints
`staff_user`	one row per staff account	`lower(email)` unique; roles ⊆ {viewer, live_ops, admin} and non-empty; root must hold `admin`; at most one root (unique index over a constant `WHERE is_root`); status ∈ {active, disabled}; lockout counters; TOTP columns
`staff_invite`	one-time invite / password-reset link (only `sha256(token)` stored)	invite ⇒ role set & no user_id; reset ⇒ user_id set; `expires_at > created_at`; pending-by-email index
`refresh_session`	one row per refresh token; `family_id` for reuse detection	`token_hash` unique; cascade on user delete; family + active-by-user indexes
`mfa_recovery_code`	hashed single-use recovery codes	PK (user_id, code_hash); cascade
`signing_key`	Ed25519 public keys for JWKS	same shape as `services/auth` (code reuse; keys are never shared between domains)
`audit_log`	every mutating action	same shape as Config's (Dashboard merges them)

No extensions (no citext): case-insensitive email uniqueness is a functional index.

How to verify¶

cd services/admin_auth
export ADMIN_AUTH_TEST_DATABASE_URL='postgres://USER:PASS@127.0.0.1:5433/admin_auth_test?sslmode=disable'
go vet ./... && go test -race -count=2 ./...     # -count=2: tests are re-runnable on a shared DB

Test	Proves
`TestMigrateIsIdempotent`	Up twice records each embedded migration once
`TestMigrateUpDownUp`	full Down to 0 and back Up; schema usable after
`TestEmailUniquenessIsCaseInsensitive`	`A@x` then `a@x` → 23505
`TestRolesCheck`	`{}` and `{superuser}` → 23514; `{viewer,admin}` ok
`TestSingleRoot`	second root → 23505 (skips itself if a root already exists; cleans up)
`TestInviteChecks`	invite w/o role, reset w/o user_id, `expires_at <= created_at` → 23514
`TestStaffDeleteCascades`	deleting a user removes its sessions and recovery codes

Upgrade path (done by hand during review): fresh DB → SCRUM-197 binary migrate (records 00001) → this branch's binary migrate → OK 00002_staff_schema.sql, version 2, all six tables present.

Results at time of writing¶

go vet, go test -race -count=2: pass (4 packages) against Postgres 16.

How it was built¶

DeepSeek run scoped (Landlock) to services/admin_auth (111 s, ~20k output tokens). Claude review changed three things: moved the schema from an in-place edit of 00001 into 00002 and removed a DROP SCHEMA public CASCADE the test had added to paper over that edit; corrected two comments (a wrong claim that signing keys are movable between services; "token only in the email" — there is no email); made the idempotency test count embedded files instead of hardcoding 1.