Nora
9e6accb3df
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.
59 lines
2.2 KiB
TypeScript
59 lines
2.2 KiB
TypeScript
/**
|
|
* Cycle 10 — Unified field worker table.
|
|
*
|
|
* Replaces `water_irrigators` + `time_tracking_workers` (one row per
|
|
* person with two independent PIN hashes) with one row, one PIN hash,
|
|
* one role vocabulary.
|
|
*
|
|
* Role vocabulary (matches the CHECK constraint in
|
|
* `db/migrations/0097_field_workers.sql`):
|
|
* - `worker` — default; can submit water entries + clock in/out
|
|
* - `time_admin` — can manage time-tracking tasks, settings, and
|
|
* other time workers
|
|
* - `irrigator` — legacy role from `water_irrigators`; same powers
|
|
* as `worker` but kept distinct for UI filtering
|
|
* (the water admin UI shows this label)
|
|
* - `water_admin` — can manage headgates, water workers, and water
|
|
* entries
|
|
*
|
|
* The PIN is a scrypt self-describing hash from `@/lib/water-log-pin`
|
|
* (column name `pin_hash`, NOT `pin` — see cycle 10 migration for the
|
|
* rename).
|
|
*/
|
|
import { boolean, index, pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";
|
|
import { brands } from "./brands";
|
|
|
|
export const fieldWorkers = pgTable(
|
|
"field_workers",
|
|
{
|
|
id: uuid("id").primaryKey().defaultRandom(),
|
|
brandId: uuid("brand_id")
|
|
.notNull()
|
|
.references(() => brands.id, { onDelete: "cascade" }),
|
|
name: text("name").notNull(),
|
|
pinHash: text("pin_hash").notNull(),
|
|
role: text("role", {
|
|
enum: ["worker", "time_admin", "irrigator", "water_admin", "driver", "supervisor"],
|
|
})
|
|
.notNull()
|
|
.default("worker"),
|
|
languagePreference: text("language_preference").notNull().default("en"),
|
|
phone: text("phone"),
|
|
notes: text("notes"),
|
|
active: boolean("active").notNull().default(true),
|
|
lastUsedAt: timestamp("last_used_at", { withTimezone: true }),
|
|
createdAt: timestamp("created_at", { withTimezone: true })
|
|
.notNull()
|
|
.defaultNow(),
|
|
updatedAt: timestamp("updated_at", { withTimezone: true })
|
|
.notNull()
|
|
.defaultNow(),
|
|
},
|
|
(t) => ({
|
|
brandIdx: index("field_workers_brand_idx").on(t.brandId),
|
|
}),
|
|
);
|
|
|
|
export type FieldWorker = typeof fieldWorkers.$inferSelect;
|
|
export type NewFieldWorker = typeof fieldWorkers.$inferInsert;
|
|
export type FieldWorkerRole = FieldWorker["role"]; |