Data model

Postgres, via Prisma 7. The schema is split across prisma/models/*.prisma and composed by prisma/schema.prisma. Twenty-four tables carry the product; a twenty-fifth (example) is leftover template scaffolding and is not part of the domain. (Counted from the model files at the api's 6f34cd2, 2026-09-11 — recount rather than trust the number if the schema has moved since.)

Round 4 (2026-09-11) changed this page

areas and area_grants are gone, and so are five permit columns — planId, planX, planY, latitude, longitude — replaced by one pinId pointing at a new pins table. workers.role is gone too, and permit_scans is new. The sections below say where each went; §7 replaces what used to be "Areas and per-contractor visibility".

Read this before you "fix" the diagrams

Actor fields are not foreign keys, on purpose. The convention throughout is a pair:

• xById — a plain indexed scalar, used for filtering and lookup
• x — a Json snapshot ({ id, email, firstName, lastName, permitRole }), so a permit or audit entry still displays correctly after the user record changes or is deactivated

This affects permits.createdById/createdBy, approvedById/approvedBy, closedById/closedBy, closeRequestedById/closeRequestedBy, audit_log.actorId/actor, facility_plans.uploadedById/uploadedBy, pins.createdById/createdBy, permit_workers.notAvailableById/notAvailableBy, permit_photos.uploadedById, gas_log_entries.recordedById, entrant_events.recordedById, permit_safety_readings.recordedById, certificates.createdById, inspector_visits.inspectorId/inspector, permit_scans.inspectorId/inspector, inspector_visit_notes.createdById and inspector_visit_photos.uploadedById.

The one place this convention does not apply is workers: a worker is a real row with a real foreign key, and certificates.workerId, permit_workers.workerId and entrant_events.workerId are genuine relations — see §6 below.

Drawing user ||--o{ permits would be a wrong diagram. It is drawn below as a dashed annotation instead, and there is deliberately no referential constraint behind it.

1. Overview — what relates to what

Standalone, related to nothing: verification, count.

A permit no longer relates to facility_plans directly. Its plan is reached only through its pin: permits.pinId → pins.planId → facility_plans.

Two edges that are not drawn, and their absence is the design:

• inspector_visits has no foreign key to entrant_events or gas_log_entries. A visit references those by association — same permitId, inside the visit's own time window — computed at read time by whoever wants it, never stored. An entrant check-in must stay queryable without knowing which visit it happened in.
• certificates no longer joins permit_workers by text. Both carry workerId; see §6.

2. Identity & auth

better-auth owns user, session, account, verification. contractor_profile and the permitRole / active columns are the product's additions.

Two distinct role fields, do not conflate them: permitRole is the domain role and is the only one authorization decisions read; role is reserved for better-auth's admin plugin.

The deployment is single-tenant — one domain is one company. firmName describes the contracting firm a person works for and nothing may be scoped by it.

3. Permit core

Notes worth carrying:

• id is human-readable, generated as WP-{TYPE}-{YYYYMMDD}-{seq}. The sequence comes from the count table used as an atomic per-key counter — one row per permit:{type}:{yyyymmdd}, incremented with INSERT … ON CONFLICT DO UPDATE.
• pinId is the permit's whole answer to "where". It replaced five columns: planId, planX, planY (the contractor's own pin on a plan version) and latitude/longitude (a coordinate parsed from a pasted map URL). All five no longer exist, and the URL parser — including its refusal to follow a maps.app.goo.gl shortener — was deleted with them, not left dormant. The pin already knows its plan and position, so the permit carries neither. See §7.
• pinId is writable only while DRAFT or REJECTED — the contractor's own editable window — and frozen from PENDING onward. A wrong pin is fixed by reject-with-reason, not by editing a permit an officer is about to approve.
• Unplaced is a real state, and nothing was invented to avoid it. The nine pre-round-4 permit positions and the six permits that carried an areaId were dropped, not migrated: a pin exists only because safety placed and named it, and there is no principled mapping from a percentage or an area onto one. Those permits read as unplaced.
• offlineClientId is a client-generated UUID with a unique index. It is what makes offline sync replay idempotent: a re-sent batch collides instead of duplicating.
• The work window is multi-day and repeats daily. startDate/endDate are the calendar range; dailyStart/dailyEnd are one daily window that repeats on every day in it. They replaced the single-day workDate + workTimeStart/workTimeEnd, which no longer exist anywhere. Six fields (a first-day window and a last-day window) were rejected: they leave the middle days of a multi-day permit undefined, and both the expiry sweep and the overlap query need a window they can compute per day.
• dailyStart/dailyEnd are @db.Time — a Postgres TIME, a clock time with no date part at all. That is load-bearing: if a date component varied row to row, dailyStart <= other.dailyEnd would compare 1970-against-2026 instead of 8-against-17. Prisma reads every such column back anchored to 1970-01-01T00:00:00Z, so a client must render them through a local-time conversion and never as a UTC wall clock — the backfill preserved each permit's real instant, and a naive UTC render silently shifts every migrated permit by the deployment's offset.
• scheduleNote is deliberately unstructured. "Not working Sat/Sun" lives there. Nothing queries it; structured weekday exclusion is explicitly out of scope.
• ppeDeclared is the contractor's PPE declaration — values from the seven-item EPpeItem vocabulary (§6). Empty by default and optional to submit unless a deployment sets PPE_REQUIRED, in which case an empty declaration is refused with 400 PPE_REQUIRED. Unlike certType it has no legacy data to tolerate, so an unrecognised item is a 400 at the model layer rather than a silent strip.
• closeRequestedAt & co. are a flag, not a status (round 4). A contractor or inspector asking for closure sets them on whatever status the permit already holds — ACTIVE or FIRE_MONITOR — and they are never cleared, even after the permit closes; closing is the terminal act and these stay as the record of who asked and when. A dedicated status was rejected because it would have to steal FIRE_MONITOR's slot from a hot permit mid-watch. See Permit lifecycle.
• notAvailable lives on the permit's own worker row, not in entrant_events. It records a worker who is on the permit but not on site (didn't show, sent home, unfit), with a required note. A third direction value would force every "who is inside" query to learn a state meaning the opposite of being inside. A worker marked not-available can still check in later, and doing so clears the flag.
• The gas-alert markers are recurring, not once-only. fireWatchAlertedAt is set once per permit and never cleared. gasReadingNotifiedAt / gasReadingEscalatedAt look the same and are not: a gas reading is owed every two hours for as long as the permit stays open, so the sweep compares the stored instant against the current cycle's deadline rather than testing null-vs-not-null. A null-guard would notify the first miss and fall silent on every later one.

4. Supporting — plan, pin, audit, notification, certificate

The audit chain

One facility-wide hash chain ordered by id, not one chain per permit. GET /permits/:id/audit filters that same chain by permitId. Tampering anywhere in the sequence is therefore detectable end to end. The table is append-only — no update or delete path is ever exposed.

A field action's payload.source records how it reached the permit: scan or manual (client- asserted, not a security control), or — since round 4 — system, written only by the closure's own auto-checkout of entrants still inside, never by a client. A row with no source predates the field and means "unknown"; the safety app's audit log renders system and "unknown" as distinct labels, because on an append-only record "the server did this" and "nobody knows" are different claims.

The AREA_APPROVED / AREA_REJECTED action labels survive the areas table. The log is append-only, so rows written before Area was removed render forever and still need a label.

Facility plans — a flat set of named places

A facility plan is a named place ("Floor 1", "Tank Farm") carrying the uploaded, cropped raster its pins are measured against. Plans are a flat set, not a version chain:

• Immutable and retained. There is create, activate and deactivate — no update, no delete. A new scan of a place is a new plan; the old one is deactivated with its own POST /v1/facility-plans/:id/deactivate, never automatically. The name is set once at creation.
• Several may be active at once. Activating one never touches another's active flag. activatedAt / deactivatedAt are history markers, never cleared by a later transition.
• Cropping happens client-side before upload, so a plan arrives already in its final frame.
• Writes are safety_officer-only (POST /v1/facility-plans/upload, POST /v1/facility-plans, /:id/activate, /:id/deactivate); reads are open to every signed-in role. The generic POST /v1/upload route cannot target the facility-plans prefix even if asked.

When round 4 landed, the twelve existing plans — two of them active, both area drawings, none a site plan — were given honest placeholder names and all deactivated, so none would be silently treated as authoritative under the new model and switch the submit gate on for every contractor.

Removed with Area: the area drawing and its fallback route

Until 2026-09-11 a plan row could carry an areaId ("this is one area's own drawing"), active was exclusive within a group, and GET /v1/facility-plans/active?areaId= fell back to the site plan when an area had none. All three are deleted. There are no areas, so "a plan came back" and "this area has a drawing" are no longer different facts. A client still calling /facility-plans/active gets a 404 — the safety app's risk map did exactly that until ticket 124.

Pins — names editable, positions frozen

A pin is a named position on one plan, placed by safety (POST /v1/pins, safety_officer). The contractor selects one; they no longer place their own.

• name is the only field with an update path (PATCH /v1/pins/:id). x/y are set once and never written again — a position is a claim about where work happened, so moving a pin means placing a new one and deactivating the old (POST /v1/pins/:id/deactivate).
• Deactivated, never deleted. A retired pin keeps resolving by id for every permit that references it.
• Usable right now means both flags. GET /v1/pins?active=true — what the contractor's picker asks for — returns pins that are active and whose plan is active. The submit gate below uses the same derivation, so the gate and the picker cannot disagree about what is selectable.
• Every contractor sees every pin. The per-contractor grant table (area_grants) was deleted with Area; scoping returns only if someone asks.

Notifications

A notification targets a role, so read state cannot live on the notification row — hence notification_reads, unique on (notificationId, userId). targetUserId narrows a role-wide notification to one person: a rejection notice must reach only the contractor who owns the permit, not every contractor. Both frontends poll GET /notifications; there is no push.

Certificates

A certificate belongs to a Worker row by foreign key. It is checked twice — at submit (a per-worker query, blocking with CERT_MISSING / CERT_EXPIRED) and again at entrant scan, which denies entry and writes a CERT_BLOCKED audit entry. See §6 for the identity rules and for what certType does.

A certificate needs a licence number or an attachment — at least one. A create, or an update whose final state has neither, is refused with 400 CERT_LICENCE_OR_ATTACHMENT_REQUIRED. The server re-checks only when a patch touches licenceNo or filePath, so certificates recorded before the rule (with neither) stay editable for unrelated fields — which is also why a client must omit untouched fields rather than round-trip licenceNo: '' or filePath: null. description is free text for the training or examination detail.

6. Workers, certificates and the two vocabularies

Until 2026-09-09 a worker was a free-text workerName on three tables, joined by string: the same person under two spellings was two people, and both certificate gates matched that name with no contractor scope, so one contractor's certificate satisfied another's gate. Worker is now an entity and workerName exists on no table at all.

• Identity is the id. A permit worker row, a certificate and an entrant scan all send workerId. Names are echoed back in responses for display and are never accepted as identity.
• Uniqueness is per contractor, case- and whitespace-insensitive, enforced on the derived nameKey, never on name — a plain unique index on name would hold "Somchai", "somchai" and "Somchai " as three people, rebuilding the hole one layer down. Clients never send nameKey.
• The worker's QR card encodes the workerId, which is why identity had to move onto the card: an offline scanner has nothing to resolve a name against.
• A worker is a name; the role belongs to the job. workers.role ("who a person is", asked once at registration) was deleted in round 4. permit_workers.roleOnPermit — what they do on one permit, picked from a template list filtered by permit type or typed freely — is the only role left anywhere. Existing workers.role values were copied onto that worker's empty roleOnPermit rows, never overwriting; a worker with no permit rows lost the value. certificates.role was dropped earlier for the same reason: it only ever copied the person's role onto every card.
• Retirement is deletedAt. A worker referenced by a permit is never hard-deleted.

The vocabularies are compiled in, not tables

ECertType, EWorkerRole (now roleOnPermit's template list) and EPpeItem live in TypeScript constants files (src/libs/config/worker-vocabulary.const.ts, ppe-vocabulary.const.ts) and are mirrored in the frontends. They are not Prisma enums and not admin-managed tables. For certType that is load-bearing: real rows hold spellings matching no closed set, so a database enum would turn every unrecognised existing row into a write-time error, and the vocabulary is enforced at the gate, not at the column. scripts/check-worker-vocabulary-sync.mjs compares the enum values across repos — it does not compare the permitType→certType map, and a drift there would pass.

ECertType is 1:1 with the permit type. Gas Testing was dropped in round 4 — it described an inspector's competence, never a permit requirement — so every certificate type now gates exactly one permit type and the old role→certType map, which only filtered a picker, went with workers.role. A 'Gas Testing' value already on a row is tolerated like any other legacy spelling and satisfies nothing. (The data audit found none in development; production had not been checked when this was written.)

flag-gated CERT_TYPE_REQUIRED is off by default. With it unset,

certType is recorded and gates nothing — any unexpired certificate satisfies any permit type. With it set, a permit type requires its matching certType at both the submit check and the entrant scan:

Permit type	Satisfying certType
hot	Hot Work
confined	Confined Space Entry
heights	Working at Heights

EPpeItem is seven items, one shared constant across the API and both frontends, gated like the certificate vocabulary: Safety Glasses, Hardhat, Respiratory Protection, Earmuffs, Construction Vest, Gloves, Protective Boots. The contractor declares from it (permits.ppeDeclared); the inspector checks against it (§8).

7. Pins and the overlap warning

areas and area_grants were deleted on 2026-09-11 (ticket 106), reversing 034/036/044. An area was a flat named place a contractor proposed and an officer approved, with an optional default position and a per-contractor visibility grant behind AREA_VISIBILITY_SCOPED; PERMIT_AREA_REQUIRED could make one mandatory at submit. All of it is gone — the entity, the propose/approve flow, both flags, permits.areaId. The error codes AREA_NOT_APPROVED, AREA_NOT_PENDING and AREA_REQUIRED stay declared in both apps but can no longer be emitted.

What an area was for survives on the pin: the overlap warning was re-keyed from areaId to pinId, and proven with the same real-Postgres test before and after the removal. The removal is a rename plus a simplification — the contractor selects a place safety named instead of proposing one.

The position gate

PERMIT_POSITION_REQUIRED (400) refuses a submit with no pinId — but only once an active pin on an active plan exists. That takes three deliberate acts by safety: create a plan, activate it, place a pin on it. Until then the gate stays off for every contractor, exactly as it was before round 4. It was written that narrowly on purpose: this gate once locked every contractor out of submitting by switching on as a side effect.

Overlapping permits

Every permit-detail response carries overlappingPermits: { checked, permits[] } — other permits on the same pin whose work window overlaps this one's. Advisory only: it never gates submit or approve and adds no errorCode.

• Each row names the other permit, its colliding dates and daily window, and the pin by name (pinName).
• checked: false means this permit has no pinId, so nothing was compared — structurally distinct from checked: true, permits: [] ("compared, nothing overlaps"), so a client cannot render one reassuring empty state for both.
• Two axes, both closed intervals. The date ranges must intersect and the daily windows must intersect. A day shift and a night shift on one pin do not warn; two permits touching only at a shared endpoint do.
• PENDING, ACTIVE and FIRE_MONITOR occupy a pin — FIRE_MONITOR deliberately, because the place is still hazardous while someone stands watch over it.
• The trade-off, accepted knowingly: a pin is a point, not a zone, so two crews on adjacent pins no longer warn. Radius-based detection was considered and rejected — it invents a threshold nobody has data to choose.

Served by @@index([pinId, startDate, endDate]); the daily-window comparison is a further WHERE on that index's result set.

8. Inspector visits

One row per scan-started inspector run — one site visit, not the permit's lifetime, and the permit shows a timeline of runs. A scan (or the manual-entry fallback) creates the row; a single POST /:id/inspector-visits/:visitId/submit fills in its PPE checklist, notes and photos and stamps submittedAt. Since round 4 the inspector works a visit from an action menu rather than a fixed sequence of steps; the record it produces is the same shape. See Using the Safety app.

Append-only, on the same instinct as the audit log. There is no PATCH route, not even for typos — an inspector who got it wrong files another visit. A visit cannot be submitted twice; the created-but-unsubmitted state is the only mutable window, and it is fill-once, not editable.

A visit changes no Permit field. The inspector witnesses; closing is the safety officer's act (round 4 — it used to be the foreman's). An inspector who thinks the work is done raises a closure request instead, which is a flag, not a transition. Inspector-triggered suspension was considered and ruled out of scope, because it would hand an inspector the power to halt a crew by mis-tapping. It is spelled WARNING, not WARN.

A visit starts from a scan — or, since ticket 101, from history, any time later, no re-scan required. permit_scans (append-only) records one row per genuine QR scan by a signed-in inspector: who, which permit, when. A history reopen calls the same lookup but writes no row — looking at your own history is never itself proof of a scan. POST /:id/inspector-visits with source: 'history' is accepted whenever a PermitScan row exists at all for that inspector and that permit — no time bound any more: ticket 101 originally capped this at the earlier of 12 hours after the scan or the permit's work-window end (403 SCAN_WINDOW_EXPIRED past it), and an owner decision on 2026-09-13 reversed that cap after it kept forcing inspectors to re-scan permits that were still perfectly ACTIVE — SCAN_WINDOW_EXPIRED still exists, but now means only "no scan of this permit by you exists at all," never "your scan is too old." GET /qr/:token mirrors the verdict (canStartFromHistory; historyWindowEndsAt is always null now, kept only for wire back-compat) so a client never recomputes it. The real safety argument this rule ever needed is the one below — only an ACTIVE/FIRE_MONITOR permit is startable at all, from a scan or from history — not a clock on top of it. Shipped together on dev (smart-work-permit-api PR #13, smart-work-permit-frontend PR #8), not yet merged as of this writing.

Only ACTIVE and FIRE_MONITOR permits can be inspected. Starting a visit, like every other field action, refuses anything else with 403 PERMIT_NOT_ACTIVE — a CLOSED or EXPIRED permit is read-only.

Who is told, by noteType. Decided in one table (NOTE_TYPE_ROUTING), not scattered:

noteType	Pushes to
EMERGENCY, INCIDENT	every safety officer and the permit's owner
CORRECTIVE_ACTION	the permit's owner only
GENERAL, WARNING	nobody — read in the timeline

None of the five changes permit state.

Who may read a visit. Safety officers and inspectors read any permit's visits. Since round 4 the contractor reads the full visit record — notes included — on their own permits (GET /permits/:id/inspector-visits; another contractor's permit is a 403). Full visibility was chosen over a noteType filter, knowingly; inspectors must be told their notes are contractor-visible. The contractor app does not render visits yet — the access is API-side.

The PPE checklist

ppeChecklist is written once, at submit, as { worn, undeclaredGaps, note }:

• worn — one { item, worn } pair per item the contractor declared. An item that was not declared is refused (PPE_ITEM_NOT_DECLARED).
• undeclaredGaps — items from EPpeItem the contractor did not declare and the inspector flags as missing. That flag is the point of the check: a JSA that forgot respiratory protection is what an inspection exists to catch. A gap that is declared is refused (PPE_GAP_ALREADY_DECLARED), and a flagged gap needs a CORRECTIVE_ACTION, EMERGENCY or INCIDENT note on the same visit (PPE_GAP_REQUIRES_CORRECTIVE_ACTION), so it travels the existing routing to the owner.
• A checklist present but carrying none of the three is refused (PPE_CHECKLIST_EMPTY) rather than recorded as a silent "nothing checked".

It is an array of pairs, not a map, because Elysia strips unknown object keys. Checklists written before round 4 are read back exactly as written and render as legacy — evidence is never remapped.

The gas-reading clock

gasReadingStatus rides on every permit-detail payload: { dueAt, overdue, graceEndsAt, escalated }. It is non-null only for a Confined Space permit in ACTIVE or FIRE_MONITOR, and null everywhere else. The interval is GAS_LOG_RETEST_INTERVAL_MINUTES (120) plus GAS_LOG_RETEST_GRACE_MINUTES (30), both server-owned constants.

Clients render this verdict and never recompute the threshold. The same computation backs GET /permits/:id/gas-log's overdue flag and the sweep that notifies and escalates — one number, read in three places. Two implementations of one safety threshold in two repos is the exact shape that let certType gate nothing while both gates looked correct.

The sweep runs on the same one-minute in-process cron tick as the expiry sweep (each in its own try/catch, so a fault in one cannot take the other down). Past dueAt it notifies every inspector on duty and the safety officer; past graceEndsAt it escalates to a banner for the officer and the contractor foreman — it is their crew inside. That server-owned clock is what made it safe to replace the inspector's fixed step sequence with a menu: a skipped gas step no longer means a missed alarm.

9. Prisma extensions

Four client extensions in prisma/extensions/ change behaviour globally: soft-delete (the deletedAt columns above), pagination, exists, find-or-create. Code that queries these models directly without going through the extended client will see soft-deleted rows.