The two per-domain worker tables (water_irrigators, time_tracking_workers)
both carried a pin_hash, a brand, a role, and almost-identical columns.
Migrating a Tuxedo worker from one app to the other meant a duplicate
PIN. Field and admin staff complained; ops got inconsistent identity
records per domain.
Unify into a single field_workers table. Cycle 10 migration
0097_field_workers.sql:
- creates field_workers (id, brand_id, name, pin_hash, role with CHECK
IN ('worker','time_admin','irrigator','water_admin'),
language_preference, phone, notes, active, last_used_at, …)
- adds brand-scoped RLS, brand index, active index
- nullable field_worker_id on downstream tables, backfilled via
UPDATE … FROM, NULL count verified with RAISE EXCEPTION before
SET NOT NULL
- drops the two old tables and renames FK columns
(irrigator_id → field_worker_id, worker_id → field_worker_id)
- reissues the one-open-clock-in partial unique index against
field_worker_id
Application surface:
- All actions/services now read/write fieldWorkers. Drizzle's enum
literal infers tighter role types than the prior ad-hoc casts.
- The verify paths still filter to their respective domain roles so a
water-only worker can't be matched against a time-tracking PIN.
- API surface preserved: action function names didn't change.
- Seed file updated so QA reseeds work post-deploy.
Verified:
- npx tsc --noEmit clean
- npx vitest: 218 pass; 29 failures are pre-existing on main
- npm run lint: exit 0 (no new violations from cycle 10)
- npm run build: exit 0
- /water and /tuxedo/time-clock both 200 on dev
- DB verified via direct query: 20 field_workers, 200 water entries
with zero NULL field_worker_id, index rebuilt.
PR-reviewer: APPROVED.
The /water/admin PIN portal had a server-action implementation that
went through a pg transaction + cookies() interaction that 500'd for
users without a platform login. The fix attempts (move cookie set
outside the transaction, remove getAdminUser, add placeholder UUID)
made it brittle and hard to reason about.
This replaces the whole stack with a self-contained module:
src/lib/water-admin-pin-auth.ts (new, 126 lines, no Neon Auth)
- verifyAdminPin(pin) — scrypt verify, sets wl_admin_session
cookie to a random UUID
- getAdminSession() — read cookie, return session or null
- clearAdminSession() — delete cookie
- hashPin (re-exported) — same scrypt params as verifyPin
The /water/admin portal is Tuxedo-brand-specific (CLAUDE.md "Public
Storefront Architecture"), so the brand UUID is hardcoded. The cookie
is the session — no DB session table writes. Cookie presence =
authenticated. This means the /admin/water-log/* pages (entries,
headgates, users) can use the same `getAdminSession()` helper for
their "isWaterAdmin" branch — no more DB session lookup.
Deleted:
- verifyWaterAdminPin server action in settings.ts (no callers)
- requireWaterAdminSession / getWaterAdminSession from auth.ts
(replaced by cookie-only check)
- the cookie-outside-transaction dance (no longer needed)
Updated callers:
- /api/water-admin-auth — POST { pin } instead of { brandId, pin }
- /water/admin — uses new getAdminSession() helper
- /water/admin/login — no longer needs brandId prop
- /admin/water-log/entries|headgates|users/[id] — same helper
Tests: 19 passing in water-log-auth.test.ts (3 new for the new module).
verifyWaterAdminPin previously called cookies().set(...) from inside
the withBrand(...) pg transaction. The pg transaction's
AsyncLocalStorage context masks Next.js's request-scoped cookie
store, so for users without a platform login the cookie set threw
and the route handler returned 500.
Move the cookie write to the outer function frame, after withBrand
resolves. The DB session row is still inserted inside the
transaction (so it's atomic with the PIN verification); only the
cookie write is hoisted out.
The prior fix removed getAdminUser() but left the cookie set inside
the transaction, so valid PINs still 500'd. This is the missing
half.
Adds a static regression test that asserts const cookieStore
is declared AFTER the matching close-paren of withBrand(...).
The previous PR wrapped the getAdminUser() call in try/catch as a
best-effort audit hook. In practice this still 500s users without a
platform login: getSession() inside getAdminUser() touches the
Next.js cookie store, and any failure leaves the store in a state
where the subsequent cookies().set('wl_admin_session', ...) throws.
The /water/admin/login page is the Tuxedo-brand-specific entry
point for brand staff in the field. It must work PIN-only, with no
Neon Auth dependency.
Fix:
- Drop the getAdminUser() call inside verifyWaterAdminPin entirely.
- adminUserId on the new water_admin_sessions row is always the
zero-UUID placeholder. Audit attribution is intentionally deferred.
- Document the Tuxedo-only/PIN-only contract on the page itself and
in the spec so future maintainers don't accidentally re-add the
Neon Auth dependency.
Guards (TDD, red → green):
- tests/unit/water-log-auth.test.ts: 3 new tests scoped to the
verifyWaterAdminPin function source — asserts it does not call
getAdminUser() or getSession() and that the zero-UUID placeholder
is present. These fail if a future refactor re-introduces the
dependency.
Field users at /water were blocked because every server action called
getSession() (Neon Auth) even though the field path is PIN-authenticated.
A missing or expired platform session hung or rejected PIN submissions,
so users without a platform login could never reach the water log entry
screen.
Centralize water-log auth in src/actions/water-log/auth.ts with three
helpers — requireFieldSession, requireWaterAdminSession, and
requireWaterAdminPermission — and migrate all server actions off the
stray getSession() calls. Auth.ts deliberately does NOT import
getSession; a static-source unit test enforces that.
Changes:
- src/actions/water-log/auth.ts (new): dedicated auth layer, no Neon Auth
- src/actions/water-log/field.ts: -10 await getSession(), use helpers
- src/actions/water-log/admin.ts: -18 await getSession(), use helpers
- src/actions/water-log/settings.ts: -4 await getSession(), resilient
to missing platform admin
- 3x admin pages: update getWaterAdminSession import path
- tests/unit/water-log-auth.test.ts (new): 17 tests incl. regression
guard that auth.ts never imports getSession
- tests/water-log.spec.ts: 3 E2E regression tests (no platform login)
- vitest.config.ts: alias for deep @/db/schema/water-log path
Rollback = revert this commit. No DB migration; no schema change.
Existing rows/cookies unchanged.
Nora
Tyler