Phase 2 of the time-tracking buildout — turns the existing clock widget
into a full payroll-ready system for Drivers / Supervisors / Admins.
Roles
- field_workers CHECK extended with 'driver' and 'supervisor' alongside
the existing worker / time_admin / irrigator / water_admin values.
Schema (migration 0098)
- time_tracking_logs gains clock_in/out lat/lng/accuracy_m + gps_verified
+ entry_kind ('clock' | 'manual') + manual_reason + edit audit columns.
- new time_tracking_timesheets (draft → submitted → approved/locked →
rejected, with unlock path for admins).
- new time_tracking_audit_log (append-only).
- new time_tracking_supervisor_assignments (supervisor ↔ supervisee edges).
- new SECURITY DEFINER RPCs: recompute_timesheet_totals(),
get_or_create_timesheet(), plus a time_tracking_approval_queue view.
Workflow + locking (src/actions/time-tracking/timesheets.ts)
- resolveCaller() returns platform_admin / brand_admin / supervisor /
worker context; supervisor scope is enforced via the assignments table.
- submitTimesheet / approveTimesheet / rejectTimesheet / unlockTimesheet
all run inside withTx with FOR UPDATE row locks so the status + audit
row + recompute RPC commit atomically.
- Editing or deleting an entry on an approved timesheet is blocked at
the action layer; only unlockTimesheet (admin-only, mandatory audit
note) can re-open it.
Manual entry (src/actions/time-tracking/entries.ts)
- addManualTimeEntry / editTimeEntry / deleteTimeEntry with Zod validation,
audit-log writes in the same transaction, lock-aware.
GPS + offline (src/actions/time-tracking/field.ts,
src/lib/offline/time-tracking-queue.ts,
src/actions/time-tracking/offline-handlers.ts)
- captureGps() in the field client: best-effort geolocation, never blocks
clock-in/out (gps_verified = accuracy_m <= 100).
- tryOrQueueClock() wraps the action; on network failure the event lands
in IndexedDB and replays via replayOfflineClock() (PIN re-verified on
replay, submitted_via = 'offline_sync').
Export (src/actions/time-tracking/export.ts +
src/app/api/time-tracking/timesheets/[id]/export/route.ts)
- PDF (pdf-lib) + CSV (papaparse) payroll export with signature lines,
available for any approved timesheet.
UI
- /admin/time-tracking with tab nav (Overview / Timesheets / Approvals /
Audit).
- TimesheetsList, TimesheetDetail (status-driven workflow buttons,
manual entry form, audit trail), ManualEntryForm.
- FleetRouteSummary on the time-tracking overview shows the full crew's
water + time totals for the day.
- DailyRouteSummary on /admin/water-log/users/[id] shows the per-worker
cross-link badge (water gallons + time hours + timesheet status).
Verification
- npx tsc --noEmit → 0 errors.
- npm run build → all 10 time-tracking routes compile.
- npm run lint → 0 errors/warnings in new files.
- Playwright smoke → 4/5 pass; the failing case is a pre-existing
Tuxedo storefront 404 unrelated to this change.
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.
Nora