# Fire Vehicle Management — data model

The program runs entirely on **flat files**. Nothing depends on a database server, so the data can live on
the web server, a thumb drive, a laptop folder or a single backup file — and later be loaded into a
database without conversion work.

## 1. Principles

| Principle | What it means |
|---|---|
| **Append-only records** | Every action (inspect, lock out, release, check out, check in, open / update a work order, record a PM, edit a unit) adds one new record. Nothing is edited or deleted. Current state is *derived* by replaying records in time order. |
| **One file per record type** | `records/<table>.jsonl` — one JSON object per line. Each file maps 1-to-1 to a future database table. |
| **Globally unique ids** | Every record has a UUID `id`; every unit has a UUID (`unit`). Copies made on different devices merge by `id` with no conflicts. |
| **Self-verifying** | Each record carries `integrity.sha256` — a SHA-256 of the record (excluding `integrity` and any field starting with `_`). Any later change is detectable. |
| **Configuration is data too** | Checklists, inventories, PM intervals and program rules are JSON in `config/`. Each inspection stores the checklist `configVersion` it was made with. |

## 2. Where the files live

| Location | Layout |
|---|---|
| Web server (`api.php`) | `~/fvm-data/` (outside the public web root): `records/*.jsonl`, `fleet.json`, `manifest.json`, `ids.txt`, `seq.txt`, `server.json` (access-code hashes) |
| Thumb drive / folder | `<chosen folder>/FireVehicleMgmt/`: `records/*.jsonl` (master), `fleet.json`, `manifest.json`, `exports/*.csv`, `README.txt` |
| Backup file | `fire-vehicle-mgmt_<date>.json` → `{schema:"fvm-bundle/1", tables:{units:[…], inspections:[…], …}}`; optional AES-256-GCM encryption (PBKDF2-SHA256, 250,000 iterations) |
| Each browser | IndexedDB `fvm` (stores `records`, `outbox`, `kv`) — works offline; the outbox uploads when online |

`fleet.json`, `manifest.json` and the CSV files are **derived** — they can always be rebuilt from `records/`.

## 3. Common envelope (every record)

| Field | Type | Notes |
|---|---|---|
| `schema` | text | `fvm/1` |
| `id` | UUID | primary key |
| `type` | text | `unit`, `inspection`, `lockout`, `release`, `checkout`, `checkin`, `workorder`, `workorder-update`, `pm` |
| `unit` | UUID | the unit this record belongs to (foreign key → units) |
| `unitName` | text | unit name at the time (denormalized for readable files) |
| `at` | ISO-8601 UTC | when the event happened (entered on the form) |
| `createdAt` | ISO-8601 UTC | when the record was saved (orders same-minute events) |
| `device` | UUID | browser that created it |
| `integrity` | object | `{algorithm:"SHA-256", sha256}` |
| `_seq`, `_receivedAt`, `_key`, `_keyId` | server only | server sequence number, receive time, name and id of the access code that uploaded it — not part of the hash |

People are stored as `{name, memberId, role?}`. Signatures are PNG data URLs.

## 4. Record types → future tables

| File / table | Key fields (in addition to the envelope) |
|---|---|
| `units` | `data`: `name, type` (profile id), `station, status` (front-line / reserve / retired), `serviceLevel, year, make, model, vin, assetNo` (last 6 characters of the VIN; entered by hand only when there is no VIN), `plate, inServiceDate, odometer, engineHours, equipment[], notes`. Latest record per `unit` is the current version; earlier ones are history. |
| `inspections` | `inspectionType, unitType, serviceLevel, inspector, station, odometer, engineHours, pumpHours, fuel, equipment[]`; `items[]` `{id, section, label, critical, status pass/fail/na, note, value?, min?, unit?}`; `inventory[]` `{id, category, name, par, qty, lot, expires, controlled, critical, status, note}`; `seals`, `witness`, `witnessSignature` (ALS controlled substances); `summary {failCount, criticalFailCount, shortCount, expiredCount, expiringCount, csDiscrepancy, suggested, determination, overrideReason}`; `notes, signature, configVersion` |
| `lockouts` | `placedBy, tagNumber, reason, description, officerNotified, odometer, inspection` (→ inspections), `workorders[]` (→ workorders), `controls{}`, `energy {used, sources{}, locks[{name, lock}], verified}`, `signature` |
| `lockout_releases` | `lockoutId` (→ lockouts), `releasedBy` (with role), `repairSummary, odometer, rtsInspection` (→ inspections), `checks{}`, `overrideReason, signature` |
| `checkouts` | `checkedOutTo, driverQualified, issuedBy, purpose, destination, expectedReturn, odometerOut, fuelOut, engineHoursOut, itemsIssued[], preTripInspection` (→ inspections), `conditionNotes, signature` |
| `checkins` | `checkoutId` (→ checkouts), `returnedBy, odometerIn, miles, fuelIn, engineHoursIn, itemsReturned, missingItems, newDefects, defectDescription, notes, signature` |
| `workorders` | `title, description, category, priority` (critical / high / normal / low), `status, reportedBy, source {inspection+item \| checkin \| pm \| manual}` |
| `workorder_updates` | `workorderId` (→ workorders), `status, assignedTo, notes, parts, laborHours, cost, odometer, by` |
| `pm_services` | `task` (id from `config/pm.json`), `taskLabel, result` (pass / conditional / fail), `performedBy, reportNo, odometer, engineHours, notes, by` |

### Derived state (computed, never stored)

* **Unit status** — active lock-out → *out of service*; else the later of the last inspection's determination or last release; plus badges for check-out, overdue daily / weekly checks, open work orders and PM due.
* **Work-order state** — the work order folded with its updates in time order.
* **PM due** — last passing `pm` record for the task (or the unit's in-service date) + the task interval (months / miles / engine hours, whichever comes first).

## 5. Fleet roster spreadsheet

`templates/fleet-import-template.xlsx` (built by `tools/make_fleet_template.py` from `config/apparatus.json`) and the
*Fleet* CSV export share one column layout, defined in `js/fleetimport.js` (`COLUMNS`): unit name, type, station, status,
service level, year, make, model, VIN, Asset No. (always the last 6 of the VIN when a VIN is given), plate, in-service date, odometer, engine hours, notes, System ID, then one
`Equip: <label>` column per equipment item (Y / N / blank = type default). Each imported row becomes a normal `units`
record; units match by System ID, then by name, and blank cells keep the current value on update.

`tools/convert_apparatus_master.py <ApparatusMaster.xlsx> [out.xlsx]` converts the district's Apparatus Master
(Fleet Master tab) into this layout: Current Assignment → unit name, Chassis Type → unit type, Date Acquired →
in-service date, Mileage → odometer, last 6 of VIN → Asset No. (the master's Asset No. for units without a VIN, with a
warning if the master disagrees with the VIN); pump hours, priority and notes go into Notes. Keep its output out of
the web root (it holds VINs and asset numbers).

## 6. Moving to a database later

1. Create one table per file in §4 with the envelope columns plus the key fields. Nested arrays become child tables (`inspection_items`, `inspection_inventory`, `lockout_locks`, `checkout_items`) or JSON columns.
2. Load: for each `records/<table>.jsonl`, insert every line; `id` is the primary key, so re-running the load is safe.
3. Keep the append-only rule (no `UPDATE` / `DELETE` on these tables) and build views for current state — `current_units`, `open_workorders`, `active_lockouts`, `pm_due`.
4. Replace `api.php` with an endpoint that speaks the same two calls (`push` records, `pull since=<seq>`). The browser app does not change.
5. Recommended at that stage: per-person sign-in (replacing the shared access code), role-based release of lock-outs, and server-side integrity checks.

## 7. Sync API (`api.php`)

| Call | Purpose |
|---|---|
| `GET ?action=health` | `{ok, version, configured}` |
| `GET ?action=pull&since=N` (header `X-FVM-Key`) | records with `_seq > N`, up to 1,500 per call; `more:true` means call again |
| `POST ?action=push` `{records:[…]}` (header `X-FVM-Key`) | appends new records, skips ids already stored, returns `{accepted, duplicates, rejected[], seq}` |

| `GET ?action=whoami` | the calling code's `{id, label, memberId, role}` |
| `GET ?action=keys` (admin) | every code (no hashes) and records uploaded per code |
| `POST ?action=key-issue` (admin) `{label, memberId, role}` | new code — returned once |
| `POST ?action=key-update` (admin) `{id, active?, role?, label?, memberId?}` | deactivate / reactivate / change role |
| `GET ?action=audit&limit=N` (admin) | activity log, newest first |

Access codes live in `server.json` as SHA-256 hashes with a role (`member`, `officer`, `admin`) and an `active` flag; `unit` records from a member code are refused. `audit.jsonl` logs uploads, code changes and refused codes with the IP address (server only, never synced). Shell: `php api.php issue-key "<name>" [role] [member id] | list-keys | deactivate-key | reactivate-key`.