MEGAINFRA-WEBAPP/docs/data-model.md

Data model

Every PocketBase collection that backs the app, with fields, relations, and the site-attribution strategy each one uses.

Source of truth for types: src/lib/pb-types.ts (16 collection interfaces). Source of truth for the live schema: scripts/pb-bootstrap-schema.py. Production instance: https://pb.mega.infra.apphub.co.za.

This reflects the shape produced by the 2026-09-11 ingestion restructure (docs/superpowers/plans/2026-09-11-ingestion-restructure.md): the shared data_import_batches pool is gone, replaced by typed document collections (timesheets, fuel_sheets, plant_hours_sheets) and, for purchase orders, queue fields carried directly on purchase_orders itself. project_code denormalisation is gone everywhere except sites.

---

1. Entity-relationship diagram

erDiagram
    SITES ||--o{ EMPLOYEES : "site (relation)"
    SITES ||--o{ VEHICLES : "site (relation)"
    SITES ||--o{ FIXED_COSTS : "site (relation)"
    SITES ||--o{ INCOME_CLAIMS : "site (relation)"
    SITES ||--o{ PURCHASE_ORDERS : "site (relation)"
    SITES ||--o{ DELIVERY_NOTES : "site (relation)"
    SITES ||--o{ FUEL_LOGS : "site (relation)"
    SITES ||--o{ PLANT_HOURS_LOG : "site (relation)"
    SITES ||--o{ TIMESHEET_ENTRIES : "site (relation, stamped at import)"
    SITES ||--o{ TIMESHEETS : "site (relation, required)"
    SITES ||--o{ FUEL_SHEETS : "site (relation, optional)"
    SITES ||--o{ PLANT_HOURS_SHEETS : "site (relation, optional)"
    SITES ||--o{ SITE_MODELS : "site (relation)"
    SITES ||--o{ SITE_PROGRESS_EVENTS : "site (relation)"
    SITE_MODELS ||--o{ SITE_PROGRESS_EVENTS : "model (relation)"
    SITES ||--o{ SITE_SURVEY_IMPORTS : "site (relation)"

    EMPLOYEES ||--o{ TIMESHEET_ENTRIES : "employee (relation)"
    VEHICLES ||--o{ FUEL_LOGS : "vehicle (relation)"
    VEHICLES ||--o{ PLANT_HOURS_LOG : "vehicle (relation)"
    TIMESHEETS ||--o{ TIMESHEET_ENTRIES : "timesheet (relation, optional)"
    FUEL_SHEETS ||--o{ FUEL_LOGS : "fuel_sheet (relation, optional)"
    PLANT_HOURS_SHEETS ||--o{ PLANT_HOURS_LOG : "plant_hours_sheet (relation, optional)"

    PURCHASE_ORDERS ||--|{ PURCHASE_ORDER_ITEMS : "purchase_order (cascade)"
    PURCHASE_ORDERS ||--o{ DELIVERY_NOTES : "purchase_order"
    DELIVERY_NOTES ||--|{ DELIVERY_NOTE_ITEMS : "delivery_note (cascade)"
    PURCHASE_ORDER_ITEMS ||--o{ DELIVERY_NOTE_ITEMS : "purchase_order_item"

data_import_batches and the deprecated plant alias (0 rows, 0 code references) were deleted entirely as part of this restructure — neither exists in production any more.

---

2. Site attribution strategy, per collection

This is the table to read first. Every non-joining collection now carries a real site relation; project_code survives only on sites itself, as the one canonical, human-readable label.

Collection	site relation	Attribution source	Drift risk
sites	n/a	The site row itself; project_code is required + unique	None — the unique index is authoritative
employees	✅ optional	The worker's current site assignment	Low — read directly, nothing denormalised to go stale
vehicles	✅ optional	The vehicle's current site assignment	Low — same
fixed_costs	✅ optional	Stamped by the entry form	Low
income_claims	✅ optional	Stamped by the entry form	Low
purchase_orders	✅ required	Resolved by matchSite at upload time	Low — required field, no null-site POs
delivery_notes	✅ required	Stamped at upload time from the form	Low
fuel_logs	✅ required	Resolved per-row during sheet import (site cell, or vehicle's current site as fallback)	Low — required field
plant_hours_log	✅ required	Always set from the matched vehicle's site at import time	Low — required field
timesheets (document)	✅ required	A wage sheet belongs to one site, stamped at upload	None
timesheet_entries	✅ required	Stamped from the employee's site at import time — not joined at read time	None — this ends the historical drift the old timesheets collection had when a worker moved sites after the fact
fuel_sheets (document)	✅ optional	A pump sheet can span sites; left empty and resolved per-row against fuel_logs.site	Low
plant_hours_sheets (document)	✅ optional	Same reasoning as fuel_sheets	Low
purchase_order_items	❌	Inherited through purchase_order → site	None — never queried directly by site
delivery_note_items	❌	Inherited through delivery_note → site	None — same

Reading the table. timesheet_entries (the renamed collection that used to be called timesheets, and used to have no site of its own) is no longer the worst drift risk in the system — it now carries a site relation that is stamped once, at import time, from the employee's site as it was that day. A worker moving sites later does not silently rewrite history the way the old at-read-time join through employees.site did. The two join tables (purchase_order_items, delivery_note_items) remain the only collections with no site attribution of their own, by design — they're always reached through their parent.

---

3. The project_code denormalisation — removed

Before this restructure, project_code (a free-text identifier like MEG-001) was copied onto every child collection as a denormalised string at insert time, alongside the site relation. That copy was never updated when the parent site's project_code changed, which was the single biggest drift risk in the schema (see the old §3 in git history for the full writeup).

Current state: project_code now lives in exactly one place — sites.project_code, required and unique (idx_sites_project_code, enforced in scripts/pb-bootstrap-schema.py). Every other collection that needs a human-readable site label resolves it by expanding or joining the site relation at read time (src/lib/agent/load.ts's loadSiteRefs() is the canonical example). There is no denormalised copy left anywhere to drift.

scripts/pb-relax-rules.py and the PATCH /api/sites/[id] route (src/app/api/sites/[id]/route.ts) still refuse to let you change project_code on an existing site — that rule now protects the single source of truth rather than a chain of denormalised copies, but it is exactly as important as it always was: renaming a site is a new site, not an edit.

---

4. Collection reference

Each entry below lists every typed field, the relation direction, and any notable constraints.

4.1 sites

The site is the unit of business. One row per construction contract.

Field	Type	Required	Notes
project_code	text	✅	Unique (idx_sites_project_code). Locked after creation.
name	text	optional	Human label, e.g. "The Hills"
duration	text	optional	Free-text duration ("8 months", "52 weeks") — parsed by parseDurationMonths
contract_value	number	optional	Rand value, no currency field
work_hours	number	optional	Total project work hours
hours_per_week	number	optional	Standard workweek for the site
wages_hours_per_day	number	optional	Standard wage day length
latitude, longitude	number	optional	Site coordinates
job_start_date	date	optional	ISO date; the projection helper treats this as the authoritative start when present

4.2 employees

Field	Type	Notes
site	relation → sites	The site the worker is currently assigned to
name, employee_no, position	text	employee_no is the cross-system payroll id; uppercased on import
rate_per_hour	number	Drives payroll burn
hours_per_day_standard	number	9 by default for auto-created casuals
worker_type	select	permanent or casual

Worker status

employment_status is an optional select (active, inactive). Blank status is also treated as active for compatibility with older imports. inactive_from is an optional date recording when the worker stopped working. The status takes effect immediately; future inactive dates are rejected by the app.

The workforce Needs attention rows offer Mark inactive beside Add rate. Worker detail offers the same control and Reactivate worker. The worker list hides inactive workers by default and provides Include inactive workers. Inactive workers are excluded from current headcounts, missing-rate alerts, attendance alerts and new clock-ins. A pending same-day clock-out can still be completed. Recorded timesheets, rate history and historical payroll costs stay available. Reactivation clears inactive_from; it does not change rates or hours.

The live migration initialized all 156 existing workers as active. No worker is automatically inactivated because they are absent from a wage list. Run scripts/pb-add-worker-status.py to add the schema fields and initialize blank statuses in another environment; it preserves existing inactive statuses.

Employee rate history: employee_rates

Created by scripts/pb-add-employee-rates.py. One row per employee per effective calendar day; the next effective date implicitly ends the previous period. There is no fixed limit on annual changes.

Field	Type	Notes
employee	required relation → employees	Existing worker record; deletion does not cascade
effective_from	required date	Inclusive business date, written at UTC midnight like date_logged
rate_per_hour	required number	Positive hourly rate; PocketBase's required number rejects zero
notes	text	Optional source or reason for change
created, updated	autodate	Automatic record timestamps

Unique index on employee and the calendar-day portion of effective_from prevents duplicate changes even when timestamps differ within that day. A second employee/date index supports dated lookups. Read rules inherit employee visibility; create/update/delete are superuser-only.

Rollout status: 169 September/October history records are imported; existing employee baseline rates are preserved. The workforce overview uses dated history and provides a missing-rate dialog. Other cost calculations still use employees.rate_per_hour; connect them to dated history before updating that legacy baseline. The imported payroll lists supply previous and new rates, with new rates effective 2026-10-01. Previous rates need an effective date covering their actual known period; do not invent rates for earlier years. Missing historical coverage must be flagged, never silently priced at the newest rate. Imports match employee numbers globally, preserve existing site assignments, and place only missing/unassigned workers at Mega Head Office. History lookups use the date worked, not the upload date or a site's reporting-month boundaries.

4.3 vehicles (unified — includes plant)

Field	Type	Notes
site	relation → sites	Current assignment
description	text	e.g. "Toyota Hilux 2.4 GD-6"
standard_work_hours	text/number	Free-text on schema; numeric in practice. Used by the plant utilisation calc
fuel_limits	text	Free-form like "8.5 L/h", "6.5 km/L" — parsed by parseFuelLimit (src/lib/fuel-variance.ts)
qr_code_id	text	The clean identifier the fuel importer matches against. NOT unique in schema — see multi-site-assessment §5
cost_per_hour	number	Drives plant utilisation value
unit_type	select	fuel or plant (default fuel). Splits the bucket between Fleet and Plant pages
attention_dismissed_at	date	When the user dismissed the vehicle's "over fuel limit" alert from the fuel page
last_latitude	number	Latest GPS fix from a MEGA Clock Fleet scan. Decimal degrees. Null until first scan
last_longitude	number	Decimal degrees. Null until first scan
last_scanned_at	date	UTC midnight anchor of the most recent scan, same convention as fuel_logs.date_logged. Indexed via idx_vehicles_last_scanned
last_location_accuracy_m	number	Position.coords.accuracy from the kiosk phone, in metres. Diagnostic

The PLANT collection (deprecated legacy alias) was deleted in this restructure — it had 0 rows and 0 code references. All plant items live in vehicles with unit_type="plant".

4.4 fixed_costs

Monthly or one-off costs. is_recurring is the partition key.

Field	Type	Notes
site	relation → sites
date_logged	date	Drives the monthly tile and trend charts
description, notes	text
cost	number	Rand value
is_recurring	bool	true (or missing) → monthly bucket; false → one-off bucket. Partitioned by partitionFixedCosts (src/lib/partition-fixed-costs.ts)

4.5 income_claims

Field	Type	Notes
claim_number	text	The contractor's IPN / claim ref
site	relation → sites
period_start, period_end	date	The period the claim covers
gross_amount	number	Rand
certified_by	text	Signature line on the cert
paid_on	date	Empty until the claim is actually paid — drives the income series
status	select	draft / certified / paid
is_verified	bool	Verified by a human
notes	text

4.6 fuel_prices

Unchanged by this restructure. Global (not per-site) pump price history.

Field	Type	Notes
effective_from	date	Required, unique (idx_fuel_prices_effective_from)
price_per_litre	number	Required
notes	text	optional

4.7 purchase_orders

Now carries the queue state data_import_batches used to carry — see docs/n8n/purchase-order-ingest-implementation.md for the full async pipeline. The row is created at upload time with a placeholder number and processing_status: pending; there is no longer a separate batch row.

Field	Type	Notes
purchase_order_number	text	Unique (idx_po_number). Placeholder PENDING-... (src/lib/po-placeholder.ts) until OCR fills it in
site	relation → sites, required	Resolved by matchSite at upload time
supplier	text
required_by_date	date	When the materials are needed
ordered_by	text	Who placed it
items_count, total_value	number	Denormalised roll-ups for table views
purchase_order_file	file	The original PDF/image; mime allowlist enforced by the upload route
raw_ocr_data	json	Full OCR payload + ingest metadata
is_verified	bool	Human-verified flag
file_hash	text	SHA-256 of the original file; drives the upload-time dedup window
processing_status	select	pending / processing / processed / failed / dead
attempts	number	Incremented on each claim
last_error	text	Last failure reason
next_retry_at, last_attempt_at	date	Backoff scheduling for the n8n cron
date_uploaded	date

Index: idx_po_site_status on (site, processing_status) — the shape the n8n cron's "fetch pending" query and the dashboard's per-site pending count both need.

4.8 purchase_order_items

Joins POs to line items. cascadeDelete: true on the parent relation.

Field	Type	Notes
purchase_order	relation → purchase_orders	Required parent
material_code, cost_code	text
description	text	The free-text the OCR matches against in the DN line
qty	number	Ordered quantity
unit, rate, disc_percent, vat_percent, amount		The amount field is the computed total, not stored separately

qty_outstanding no longer exists as a stored field. Outstanding quantity is computed at read time from delivery_note_items via src/lib/po-fulfilment.ts (computeOutstanding) — there is nothing left to drift out of sync with the actual delivery history.

4.9 timesheets (uploaded document)

New collection. One row per uploaded wage sheet — the file itself, plus queue/outcome fields shared with fuel_sheets and plant_hours_sheets.

Field	Type	Notes
site	relation → sites, required	A wage sheet belongs to one site, unlike the fuel/plant sheets
source_file	file	The uploaded bytes
content_type	text	MIME of the original file
file_hash	text	SHA-256
import_key	text	Unique non-empty site + SHA-256 claim; blocks concurrent repeat uploads
duplicate_of	text	Canonical document ID for a verified historic duplicate; source file retained
date_uploaded, uploaded_by	date/text
processing_status	select	pending / processing / processed / failed / dead
processed_at	date
attempts, last_error	number/text
rows_total, rows_created, rows_failed	number	Set by resolveDocumentOutcome() (src/lib/document-status.ts) — never hardcoded to "processed" before rows are actually inserted
row_errors	json	Up to MAX_ROW_ERRORS (200) per-row failure reasons

4.10 timesheet_entries (renamed from timesheets)

The actual time log: one row per (employee, date, hours). This is the collection that used to be called timesheets before the restructure — watch for stale references to the old name in anything not covered by this rewrite.

Field	Type	Notes
employee	relation → employees
date_logged	date
hours_worked	number	Decimal; preserved even on excluded duplicate rows
excluded_duplicate	bool	Excluded from every application list read and calculation by the shared PocketBase client
duplicate_of	text	Canonical entry ID for audit
import_key	text	Unique non-empty employee/site/day claim for imported daily totals
site	relation → sites, required	Stamped from the employee's site at import time — not joined at read time (see §2)
timesheet	relation → timesheets, optional	Empty means captured directly by QR scan, not from an uploaded sheet — this stays optional forever

Indexes: idx_ts_entries_site_date on (site, date_logged); idx_ts_entries_emp_date on (employee, date_logged).

4.11 fuel_sheets (uploaded document)

Same shape as timesheets (§4.9) except site is optional — a pump sheet can span multiple sites, resolved per-row when the actual fuel_logs rows are created.

Indexes: idx_fuel_sheets_hash on file_hash; idx_fuel_sheets_status on (processing_status, date_uploaded).

4.12 fuel_logs

Field	Type	Notes
site	relation → sites, required	Resolved per-row at import time
vehicle	relation → vehicles	Required by the schema but the importer tolerates a missing relation and falls back to QR
date_logged	date	ISO datetime; importer anchors at UTC midnight
litres	number	Required
odo_reading	number	Required — used for variance calculation
latest_consumption	number	Optional. Empty cells preserved as undefined rather than 0 (intentional)
latitude, longitude	number	Optional. Captured by the MEGA Clock Fleet scanner (/api/clock/vehicle-scan); the spreadsheet import path has no GPS context and leaves them null. Decimal degrees. Range validation lives in the route
location_accuracy_m	number	Optional. Position.coords.accuracy in metres at scan time. Diagnostic only
fuel_sheet	relation → fuel_sheets, optional	Which uploaded sheet produced this row, if any

Indexes: idx_fuel_logs_site_date on (site, date_logged); idx_fuel_logs_vehicle_date on (vehicle, date_logged).

4.13 plant_hours_sheets (uploaded document)

Identical shape to fuel_sheets (§4.11), same optional-site reasoning.

Indexes: idx_plant_hours_sheets_hash on file_hash; idx_plant_hours_sheets_status on (processing_status, date_uploaded).

4.14 plant_hours_log

The most disciplined collection. Every site-attribution field is required.

Field	Type	Notes
date_logged, start_date, end_date	date
vehicle_qr	text	The QR text — part of the composite unique index below
vehicle	relation → vehicles	Required (minSelect=1)
site	relation → sites	Required (minSelect=1)
start_hours, end_hours, hours	number
hour_meter_budget, cost_per_hour, value	number	value = hours × cost_per_hour
over_budget, hours_mismatch	bool	Computed at import time
vehicle_unit_type_at_import	select	Snapshot of the vehicle's unit_type at the moment of import — see the note in multi-site-assessment §5 about why this discipline should extend to site
plant_hours_sheet	relation → plant_hours_sheets, optional	Which uploaded sheet produced this row, if any

Unique index: (vehicle_qr, start_date). Prevents accidental double-imports of the same week for the same vehicle. Surfaced as a friendly "duplicate: vehicle_qr=X start_date=Y" error in the import response. Additional index: idx_ph_log_vehicle_start on (vehicle, start_date).

4.15 delivery_notes

Field	Type	Notes
processing_status	select	pending → ocr-complete → auto-verified / needs-review / manual-verified / failed
delivery_note_number	text	Optional; OCR may fill it in
site	relation → sites, required	Resolved at upload time
purchase_order	relation → purchase_orders	Filled by OCR (exact → normalised → Levenshtein) or by manual assign-po route
date_received	date
received_by	text	The uploader's identity — currently always "web-upload"
slip_image	file	The photo/PDF, served via /api/delivery-notes/:id/slip to n8n
raw_ocr_data	json	Full OCR payload + match metadata
is_verified	bool	Final flip from auto-verified or manual-verified
ocr_confidence	number	0..100 integer
ocr_completed_at	date
received_by_name	text	Signature OCR'd from the slip
file_hash	text	SHA-256, used for upload-time dedup

Indexes: idx_dn_site_date on (site, date_received); idx_dn_status on processing_status.

4.16 delivery_note_items (renamed from received_items)

Joins DNs to PO line items. cascadeDelete: true on the parent DN.

Field	Type	Notes
delivery_note	relation → delivery_notes	Required parent
purchase_order_item	relation → purchase_order_items	Optional — can be empty for items the OCR couldn't match, or for orphans where the PO item was deleted between fetch and create
qty_received	number
condition_notes	text	Free-text audit trail (e.g. "OCR 92% · desc: Cement 50kg · unit: bag")

4.17 ask_threads, ask_messages, ask_log (Ask agent actions)

Additive, created by scripts/pb-add-ask-collections.py (--apply is a human step). All rules null — superuser only; the Next.js server is the only client. See modules.md §7.1 for how these back threads, memory, actions and change requests.

ask_threads — one row per conversation.

Field	Type	Notes
title	text	autoTitle() of the first message, or renamed by the user
owner	relation → users, required	The picked actor (x-ask-user)
archived	bool
last_message_at	date	Bumped on every appendMessage; a lost race here is harmless

ask_messages — one row per message, attachment or generated image. Append-only: created once, never rewritten, so concurrent tabs/users can't overwrite each other.

Field	Type	Notes
thread	relation → ask_threads, required, cascade delete
owner	relation → users
kind	select	user / assistant / attachment / image
text	text (max 100 000)	The message; for an attachment, its summary; for an image, the prompt
display	text	The slash command as typed, when applicable
charts	json	ChartSpec[] the answer placed or left unplaced
action_ids, attachment_ids, tools	json	Arrays of ids / tool names
file	file (≤25 MB; xlsx/xls/csv/pdf/png/jpg/webp/txt)	Attachment or generated image
parsed	json (≤5 MB)	Attachment preview (capped 2 000 rows / 50 000 chars — read_attachment re-parses the stored file beyond that), or { pdf: {...} } for a report turn
parse_error	text

ask_log — one row per thing Ask keeps track of, by type.

Field	Type	Notes
type	select	change (proposed data edit, for Undo) / memory (lasting fact) / request (app change request)
owner	relation → users
shared	bool	Memories only — visible to everyone when true
thread	relation → ask_threads (optional, no cascade)	The log outlives a deleted thread
status	select	change: pending/applied/rejected/failed/undone. memory: applied (active) / undone (forgotten). request: open/planned/done/declined
text	text (max 2 000)	Change summary / memory text / request title
detail	text	Request detail
site	relation → sites (optional)	A memory about one site
ops	json	The planned AgentOp[], $refs intact
before, after	json	Snapshots for Undo. before: one entry per op. after: while pending, the proposed values per op; once applied/failed, { results, confirmed_by, undone_by? }
error	text	Set on applied_action → failed
applied_at, undone_at	date

Indexes: ask_threads(owner, archived, last_message_at), ask_messages(thread, created), ask_log(type, status), ask_log(owner, type), ask_log(thread).

---

site_models, site_progress_events, site_survey_imports (Site map)

Created by scripts/migrations/2026-10-14-site-map.py. All API rules are null; the dashboard reads and writes them through pbAdmin() only. Spec: docs/superpowers/specs/2026-10-14-site-map-phase1-design.md.

site_models — one row per model revision of a site; at most one active per site (enforced by scripts/site-model/upload.py, which archives the previous one).

Field	Type	Notes
site	relation → sites, required
label	text, required	e.g. "Bushveld Joy Road — RD LO Rev 1"
status	select active / archived
files	file (max 10, JSON, protected)	bushveld, services, earthworks, schedules, surface .json
sources	json	source drawings the model was built from
stats	json	headline figures
built_at	date

site_progress_events — append-only, one row per change. Current state = the latest event per (site, key). Keys: see src/lib/site-map/keys.ts.

Field	Type	Notes
site	relation → sites, required
model	relation → site_models, required	model the change was made against
key	text, required	e.g. road:ch240-260:asphalt:asphalt-30mm-medium-graded, peg:BJ45
value	number	meaning depends on the key type
user, user_name	text	who made the change
source	select manual / survey_import / admin_clear
created	autodate

Index: (site, key, created).

site_survey_imports — site, model, file (CSV, ≤ 1 MB, protected), matched (number), unmatched (json: point, nearest peg, distance), user, user_name, created.

5. Indexes and constraints summary

Index	Where	Purpose
idx_sites_project_code	sites(project_code) UNIQUE	Prevents duplicate site codes; enforced on the POST /api/sites route
idx_po_number	purchase_orders(purchase_order_number) UNIQUE	Prevents duplicate PO numbers — final backstop behind the app-level dedup in process-batch
idx_po_site_status	purchase_orders(site, processing_status)	Per-site pending-PO counts and the n8n cron's fetch-pending query
idx_plant_hours_log_qr_start	plant_hours_log(vehicle_qr, start_date) UNIQUE	Prevents accidental double-imports of the same week for the same vehicle
idx_ph_log_vehicle_start	plant_hours_log(vehicle, start_date)	Per-vehicle history queries
idx_fuel_prices_effective_from	fuel_prices(effective_from) UNIQUE	One price per effective date
idx_dn_site_date	delivery_notes(site, date_received)	Per-site DN listing
idx_dn_status	delivery_notes(processing_status)	Pending-verification dashboards
idx_fuel_logs_site_date, idx_fuel_logs_vehicle_date	fuel_logs	Per-site / per-vehicle fuel history
idx_vehicles_last_scanned	vehicles(last_scanned_at)	"Scanned in the last X hours" filters on the map page and the future attention rail
idx_ts_entries_site_date, idx_ts_entries_emp_date	timesheet_entries	Per-site / per-employee timesheet queries
idx_timesheets_hash, idx_timesheets_status	timesheets (document)	Upload dedup + pending-import dashboards
idx_fuel_sheets_hash, idx_fuel_sheets_status	fuel_sheets	Same, for fuel sheet uploads
idx_plant_hours_sheets_hash, idx_plant_hours_sheets_status	plant_hours_sheets	Same, for plant hours sheet uploads

No collection outside the above has a server-enforced unique constraint. In particular, vehicles.qr_code_id is not unique — see multi-site-assessment §5.

---

6. PocketBase API rules

scripts/pb-relax-rules.py writes the ruleset used in production. For every collection:

• listRule, viewRule, createRule → @request.auth.id != '' (any authenticated user)
• updateRule, deleteRule → null (superuser only)

The collection list is the full set of 16 — there is no collection where clients can update or delete records through the API. The /api/records/[collection]/[id]/verify route hand-picks two collections (purchase_orders, delivery_notes) for the is_verified toggle, but it runs server-side as pbAdmin(), so it is not constrained by these rules.

The only routes that respect the API rules at all are the (currently unused) browser-side pb client and the n8n file-proxy route /api/delivery-notes/:id/slip, which uses the admin token in its Authorization header. The PB layer never blocks the Next.js server.

---

7. Schema drift

pb-bootstrap-schema.py is the canonical schema definition — a fresh PocketBase must come up in the exact shape production is in. That script had drifted ten-plus migrations behind production before the 2026-09-11 restructure's final task (Task 20) brought it back to truth: it was missing fuel_prices and income_claims entirely, still described the deleted data_import_batches and plant collections, still carried project_code on eight collections that no longer have it, still had qty_outstanding on purchase_order_items, and was missing every index added across the restructure.

That drift is exactly the failure mode this section exists to name, so the rule going forward is unchanged from before: any time the schema changes, the new field must be added to both pb-bootstrap-schema.py (so a fresh PB starts in the right shape) and src/lib/pb-types.ts (so the TypeScript code agrees), in the same change that adds it to production. Forgetting either side is a known foot-gun — it is what produced this section's rewrite in the first place. scripts/pb-relax-rules.py's collection list is the third thing that must move in lockstep; it drifted just as far (missing fuel_prices, income_claims and plant_hours_log entirely, on top of the same renames/deletions).

One-off data-migration scripts under scripts/migrations/* are expected to exist and are not a drift signal by themselves — they are how the schema got to its current shape. The drift signal is pb-bootstrap-schema.py (or pb-relax-rules.py) disagreeing with what a migration already did to production.

Worker sick notes: employee_sick_notes

A separate private collection stores a required relation to employees, the required note_date (the first day covered), optional end_date (the inclusive last day off), a required protected photo (JPEG, PNG or WebP, up to 10 MB), and an upload created timestamp. Multiple notes can be retained for the same date or overlapping ranges. No employee or payroll data is changed by uploading a note. Notes remain accessible on inactive worker detail pages.

All collection rules are locked to the superuser; the app validates worker site access before uploading or streaming a photo. The photo endpoint checks that the note belongs to the worker and uses private, no-store responses. Run scripts/pb-add-sick-notes.py to create and verify the collection in another environment. Run scripts/pb-add-sick-note-ranges.py on existing installations to add end dates and backfill single-day notes without altering their photos, original dates or employee records. Missing end dates are treated as single-day notes. Date edits update the original note after origin, authentication, worker site and note ownership checks.

Worker detail shows timesheet history one populated Monday–Sunday week at a time, starting with the latest recorded week, with Previous/Next and a week selector. Overall totals still include all recorded weeks. Sick notes appear above timesheet history, newest first, with ten notes per page. Each note has Open and Edit dates actions; the photo viewer also offers Edit dates. Uploads and edits accept a start date and last day off, both inclusive.

Worker monthly attendance calendar

The detail page replaces the eight-week sparkline with a Monday-first month calendar. Recorded hours are summed per date; normal weekdays are green, weekend or above-standard hours orange, sick-note dates blue and incomplete scans, short days or expected weekdays with no work recorded red ("Needs checking", never confirmed absence). Non-working weekends stay neutral, including weekends within sick-note ranges; their documents remain available in the day popup. Recorded weekend hours or clock scans retain their attendance status. Future weekdays covered by a sick note are blue; other future days, days before the worker's first recorded history and inactive days without hours stay neutral. Expected workdays default to Monday–Friday; no payroll or leave decision is automated. Saved missed-clock-out corrections are respected. Imported timesheets count without requiring clock scans. The day popup displays hours, scans, issues, all sick-note links covering that day and modelled rainfall.

/api/employees/[id]/calendar-weather?month=YYYY-MM requires Workforce access and employee site access. Rain uses each day's recorded timesheet/scan site, not the worker's latest assignment. Multiple-site days, sites outside access, missing coordinates or missing weather readings remain unknown. The server uses Open-Meteo archive data and recent past forecast data, cached for an hour with a six-second upstream timeout. Only completed dates are included, and rainfall is displayed as estimated context, never a confirmed reason for a short shift. Weather failures do not prevent attendance from rendering.

Timesheet matrix for HR

The Timesheets screen opens a site/date-range preview and CSV or A4 landscape PDF exports through /api/timesheets/matrix. Dates are inclusive, up to 93 days; calendar weeks run Monday–Sunday and partial weeks total only selected dates. A shared model aggregates integer minutes and renders hours as HH:MM, including weekly, worker and site totals. Entries retain their recorded site, even after employee transfers. The preview pages workers; exports include all workers.

Rows include the site's current active roster plus workers with recorded hours in the selected period. Inactive dates stay grey, while historical work remains included. No hours recorded is not confirmed absence. Today/future gaps stay neutral, non-working weekends grey, covered sick weekdays blue and recorded weekend hours orange. Multiple entries on one worker/site/date are summed and marked with an asterisk; CSV has a count of these days. No records are deduplicated or changed by reporting. Historical assignment start dates are not inferred.

The API requires Workforce access and checks the selected site against permitted sites. “All sites” means only permitted sites. Private sick-note dates are read only for workers whose current site the user can access, matching worker-detail permissions. Exports contain dates/statuses, never medical images. All responses are private/no-store. CSV text neutralizes spreadsheet formula prefixes. PDF splits dates into sections of up to two weeks, repeats table headings/site labels on continuation pages, and retains the full-period total in each section.

Timesheet duplicate protection: src/lib/pb.ts installs the shared billableTimesheetRequest hook on browser and admin clients. It adds excluded_duplicate=false to entry list queries while preserving existing site, worker and date filters. Individual record IDs remain readable for audit. External integrations reading PocketBase directly must apply this filter too. Imports SHA-256 the source bytes and use database unique keys to prevent concurrent repeat files and worker/site/day totals. A daily record already present (including direct clock records) produces a row conflict for review instead of adding hours. Failed imports with no entries release their file claim; partial imports retain it to avoid repeating successful rows. Review missing rows on the original import before retrying.

scripts/pb-deduplicate-timesheets.py defaults to a read-only audit and private backup. --apply installs the schema, hashes historic source files, and flags only entries matched to an identical source file and canonical employee/site/date/hours record. It preserves original hours, entries and files. The October 7 audit excluded 427 repeated entries / 3,659.5 hours.