route-commerce/docs/water-log.md

Water Log

A standalone irrigation / water-usage tracker for ditch riders, water admins, and farm operators. Tracks flow measurements against physical headgates, attributes them to named water users, and rolls them up for reporting.

The module is PIN-based and lives entirely outside the platform admin auth: an irrigator with a 4-digit PIN can submit entries from a phone in the field without an account on the platform. Site admins manage headgates, users, and settings from /admin/water-log.

When to use this

• You run irrigation ditches / headgates and need to track daily flow with named operators.
• You need a mobile-friendly entry surface that works in low connectivity (no login, just a PIN).
• You want a per-brand, brand-scoped record of who recorded what, with audit trail + CSV export for water-rights reporting.

When NOT to use this

• For sensor/IoT integrations, use the time-series / Square Sync flow rather than this module — entries here are hand-typed.
• For multi-tenant water-rights billing, use the wholesale deposit flow; this module is a measurement ledger, not a billing system.

---

Architecture at a glance

┌────────────────────────┐  ┌─────────────────────────┐
│  /water  (PIN form)    │  │  /water/admin/login     │
│  irrigator → submit    │  │  (water_admin PIN)      │
│  → wl_session cookie   │  │  → wl_admin_session     │
└──────────┬─────────────┘  └──────────┬──────────────┘
           │                            │
           │  ┌─────────────────────────┘
           ▼  ▼
    ┌─────────────────────────────────────┐
    │   Postgres (RLS) — brand scoped    │
    │  water_headgates, water_irrigators, │
    │  water_log_entries, water_sessions, │
    │  water_admin_sessions, water_       │
    │  audit_log, water_alert_log,        │
    │  water_admin_settings               │
    └─────────────────────────────────────┘
                  ▲
                  │
    ┌─────────────┴──────────────┐
    │  /admin/water-log          │
    │  Site-admin CRUD + exports │
    └────────────────────────────┘

Two separate PIN surfaces:

Surface	Cookie	TTL	Purpose
/water (irrigator)	wl_session	8 h	Submit flow entries
/water/admin (admin)	wl_admin_session	configurable (1–168 h)	Manage headgates/users, view all entries

Both cookies are httpOnly, sameSite=lax, and secure in production.

---

Data model

water_headgates

Physical gates a measurement is tied to.

Column	Type	Notes
id	uuid PK
brand_id	uuid FK → brands	brand scope (RLS)
name	text	e.g. "Upper Ditch Headgate"
headgate_token	text UNIQUE	opaque token used in the QR code
status	text	open / closed / maintenance
unit	text	default measurement unit (CFS, GPM, AF, …)
max_flow_gpm	numeric	optional marker
high_threshold	numeric	alert when reading > this
low_threshold	numeric	alert when reading < this
notes	text
active	bool	soft-delete
last_used_at	timestamptz	updated on each entry
created_at	timestamptz

water_irrigators

Field workers with PIN access.

Column	Type	Notes
id	uuid PK
brand_id	uuid FK → brands
name	text
pin_hash	text	scrypt $N$r$p$salt$hash — never plain
role	text	irrigator (submit only) or water_admin (manage)
language_preference	text	en / es
phone	text	optional
notes	text
active	bool	soft-delete
last_used_at	timestamptz
created_at	timestamptz

water_log_entries

The actual readings.

Column	Type	Notes
id	uuid PK
brand_id	uuid FK → brands
headgate_id	uuid FK
irrigator_id	uuid FK	who submitted
measurement	numeric	raw value in unit
unit	text	CFS, GPM, AF/Day, etc.
method	text	manual / meter / estimate / qr
total_gallons	numeric	auto-computed when CFS × duration is known
notes	text
submitted_via	text	field / admin / qr
photo_url	text	optional
latitude / longitude	double precision	optional GPS
logged_date	date	date-only mirror for fast grouping
logged_at	timestamptz	the actual time
logged_by	uuid FK → admin_users	set when a site admin enters data

Sessions

• water_sessions — short-lived (8 h) PIN sessions for irrigators.
• water_admin_sessions — admin sign-in sessions, TTL from water_admin_settings.session_duration_hours.

Audit + alerts

• water_audit_log — who changed what, when. Captures every headgate/user/setting mutation with actor + JSON details.
• water_alert_log — high/low threshold breach history.
• water_admin_settings — per-brand admin PIN, permission flags, alert config, session duration.

---

Security model

PIN hashing

PINs are hashed with scrypt (Node built-in crypto.scryptSync) at N=16384, r=8, p=1, 32-byte key, with a per-PIN random salt. The hash is self-describing: scrypt$N$r$p$salt_b64$hash_b64. We avoid adding a bcrypt or argon2 dependency — scrypt is in the Node core and matches the password module already used elsewhere.

Brute-force hardening

• 4–8 digit PINs are low entropy, so we:
  • Reject "weak" PINs at generation (generatePin skips 1111, 1234, palindromes, monotonic sequences).
  • Add a 200 ms delay on every failed verifyPin call to slow online guessing.
  • Use timingSafeEqual for the hash comparison.
  • Sessions are short-lived (8 h for irrigators, configurable 1–168 h for admins).
• The admin PIN can be regenerated from /admin/water-log/settings — regenerating invalidates all existing admin sessions.

Auth gates

Every server action enforces one of:

• requireFieldSession() — reads wl_session cookie, looks up water_sessions row, verifies expires_at. Returns userId + brandId.
• requireWaterAdminPermission() — calls getAdminUser() and checks can_manage_water_log OR role === "platform_admin".
• requireWaterAdminSession() — reads wl_admin_session cookie and verifies the row.

There is no implicit "logged in" state. Each action that mutates data explicitly checks its gate.

Data isolation

• All tables have RLS policies (withBrand() helper sets a per-request GUC; the policy reads it).
• globalThis.__TUXEDO_BRAND_ID__ is a one-time Tuxedo-brand hint for cold-start paths; the active getAdminUser().brand_id always wins on later requests.
• No Supabase REST, no service-role keys — all access is direct through Drizzle over a single pg Pool.

---

Day-to-day usage

Add a headgate

1. /admin/water-log → scroll to Headgates → + Add Headgate.
2. Enter a unique name (e.g. "North Field Gate 1"), default unit, optional thresholds.
3. The new headgate gets a printable QR code. Print it and stick it on the gate.

Add a water user

1. Water Users → + Add User.
2. Enter name, choose role (Admin or Irrigator), pick language.
3. A 4-digit PIN is generated. Write it down now — it is shown once and never recoverable.
4. Hand the PIN to the worker. They sign in at /water with just that.

Submit a flow entry (irrigator)

1. Open /water on a phone → enter 4-digit PIN.
2. Pick the headgate (or scan the QR code — it deep-links to that gate).
3. Enter the measurement, duration, method, optional notes/photo.
4. Tap Submit. Total gallons is auto-computed when CFS × duration is known.
5. If the reading crosses a high/low threshold, an alert row is written to water_alert_log and (if enabled) an SMS is dispatched to the configured phone.

Edit or delete an entry (admin)

• From the Recent Entries table, click any row.
• Edit form is gated by canEditEntries / canDeleteEntries in water_admin_settings. Defaults: edit ✅, delete ✅.

Reset a PIN

• Water Users row → Reset PIN → new PIN is generated and shown once.

Export CSV

• Recent Entries → Export CSV button. Or hit GET /api/water-logs/export?format=csv (auth-gated, requires can_manage_water_log).

---

API surface

Route	Method	Auth	Purpose
/api/water-admin-auth	POST	none (PIN-protected)	Exchange admin PIN for wl_admin_session cookie
/api/water-logs/export	GET	admin	Stream entries as JSON or CSV

POST /api/water-admin-auth body:

{ "brandId": "64294306-5f42-463d-a5e8-2ad6c81a96de", "pin": "1234" }

On success, sets the wl_admin_session cookie. On failure, returns a generic error (we don't leak whether the brand has a PIN configured vs. whether the PIN is wrong).

---

Testing

Unit (Vitest)

npm test -- tests/unit/water-log

Covers:

• PIN format validation + weak-PIN detection
• scrypt round-trip + tampering
• Reporting utilities (CSV escaping, date filters, season detection)
• Display age helpers

E2E (Playwright)

npx playwright test water-log

Covers:

• /water and /water/admin/login render with PIN form
• PIN input strips non-digits and caps at 4 chars
• Wrong PIN does not navigate
• /admin/water-log/* redirects unauthenticated users
• /api/water-logs/export and /api/water-admin-auth are auth-gated

For the full DB-backed workflow (add headgate → add user → submit → export), set WATER_LOG_E2E_DB=1 and run the same command against a test database.

---

Environment variables

No Water Log–specific env vars are required. The module uses the existing DATABASE_URL and (optionally) the MinIO bucket MINIO_BUCKET_WATER_LOGS for photo uploads.

If you want high/low threshold SMS alerts, configure /admin/water-log/settings with a phone number — SMS dispatch uses the brand's existing Twilio config (same as Harvest Reach).

---

Migration history

• 0001_init.sql — initial water_* tables + RLS policies.
• 0090_water_log_completion.sql — adds headgate_token, threshold fields, role on irrigators, method + logged_date on entries, plus the water_admin_*, water_audit_log, and water_alert_log tables.

---

Admin function checklist

Manual regression pass after changes:

Field side

• /water PIN screen loads, accepts 4 digits, rejects letters
• Wrong PIN shows an error, does not redirect
• Correct PIN shows the entry form with the user's headgates
• Submitting an entry creates a row, shows confirmation, returns to the form
• QR-code link (/water?h=TOKEN) pre-selects the headgate
• High/low threshold entries write a water_alert_log row

Admin side

• /admin/water-log shows headgates, users, recent entries
• Add headgate → appears in list + QR can be generated
• Edit headgate thresholds → reflected in the field form
• Add user → PIN shown once, user appears in list
• Reset PIN → new PIN shown, old sessions invalidated
• Edit entry → measurement, notes, method all updatable
• Delete entry → row removed (or soft-flagged per audit policy)
• Filter by date range / headgate / user / method works
• CSV export downloads valid CSV
• Audit log shows the actor for every change

Settings + portal

• /admin/water-log/settings shows current config
• Toggling "Enable Admin Portal" blocks /water/admin/login
• Regenerating admin PIN signs out all current admin sessions
• Session duration slider clamps to 1–168 hours

Edge cases

• Zero data: empty states render, no crashes
• Invalid PIN: form rejects, error is friendly
• Duplicate headgate name: server rejects, UI shows error
• Very large measurement (1e9): renders, doesn't blow up float
• 1,000+ entries: list paginates, filters stay responsive
• Concurrent submissions: both rows present, no lost writes