# Entity History Capture via DB Triggers

- **Date:** 2026-07-19
- **Status:** Complete — tested and verified by Dave 2026-07-22 (one checklist item
  not exercised, see Testing checklist)
- **Status Date:** 2026-07-22
- **Phases:** 4
- **Phases Complete:** 4 ✅
- **Notes:** Promoted from `docs/improvement-opportunities.md` (Database / Audit).
  Scope confirmed 2026-07-19: projects, accounts, stocklists — entity tables **and**
  their EAV value tables. Wayleave deferred to a later phase (its hybrid per-field
  `agreements_history` + audit-log UI needs its own realignment plan). Supersedes
  the open interim backlog item "explicit column lists in the two `SELECT *`
  history inserts" — those inserts are removed entirely here.
  **Discovery (2026-07-19):** `trg_projects_history` is already live on
  `projects.projects` in the baseline (`AFTER INSERT OR DELETE OR UPDATE`, executing
  `public.fn_projects_history()`) — so projects editor saves currently write
  **double** history rows (trigger + PHP insert), which Phase 4 fixes. The existing
  function has no no-op suppression and no GUC attribution; Phase 2 upgrades it in
  place. Also confirmed: `update_modified_fields_trigger` (BEFORE UPDATE) on all
  three entity tables bumps `modified_datetime` on every update, so the audit
  panels' `modified_datetime` ordering stays reliable for bypass-path writes.

## Plan Phases

1. ✅ Actor attribution plumbing (`set_config` in `db.php`) — built 2026-07-20,
   tested 2026-07-22 (manual psql attribution behaved as expected). Note: `db.php`
   is gitignored, so this change is local-only and must be replicated by hand in
   any other environment's `db.php`.
2. ✅ Migration: history-table shape upgrades + entity triggers — built 2026-07-20
   as `db/017_entity_history_triggers.sql`, applied and tested 2026-07-22.
3. ✅ Migration: EAV value-table triggers — built 2026-07-20 as
   `db/018_eav_history_triggers.sql`, applied and tested 2026-07-22.
4. ✅ PHP cleanup: remove application-side history inserts — built 2026-07-20,
   tested 2026-07-22 (no double rows on any of the tested save paths).

## Problem

Row history for projects/accounts/stocklists is written by PHP inside the three
`*_save.php` endpoints only. Consequences:

- **Bypassed write paths record nothing:** `image_upload.php` (cover image),
  `map_boundary_update.php` / `map_feature_save.php` (geometry),
  `project_create_boundary.php` and `stocklist_create.php` (INSERTs), journal-save
  side updates, and any manual SQL.
- **No INSERT/DELETE auditing** — only UPDATE snapshots, and only from the save
  endpoints.
- **Non-atomic** — the UPDATE and its history snapshot are separate statements; a
  failure between them loses the audit row.
- **Inconsistent shapes** — `projects.projects_history` has
  `history_id/history_action/history_datetime/history_user`;
  `accounts.accounts_history` and `stocklists.stocklists_history` are bare column
  copies written via `insert … select *` (positionally fragile).
- **EAV gaps** — `*_field_values_*_history` rows are written by the same PHP
  endpoints (change-gated), so non-endpoint writes are invisible; and the save
  endpoints only handle text/date/int — numeric/boolean dynamic values are
  collected but never written at all (separate bug, tracked in the backlog; once
  fixed, trigger-based history covers them automatically).

## Design decisions (confirmed 2026-07-19)

- **Modules in scope:** projects, accounts, stocklists. Wayleave later phase.
- **EAV in scope now** (15 value tables: 5 types × 3 modules).
- **Actor attribution:** triggers resolve `history_user` as
  `current_setting('app.user_id', true)` → fall back `NEW.modified_user`
  (`NEW.record_user` for EAV) → NULL. Security review (2026-07-19): `app.user_id`
  is a custom dot-qualified GUC — our own namespace, cannot collide with or
  overwrite any built-in PostgreSQL setting; session-scoped (dies with the
  connection); a plain string with no privilege semantics. Value originates from
  `$_SESSION['id']` (server-side), so a user cannot choose it short of session
  hijack/SQL injection — both of which already defeat `modified_user` equally.
  Two hardening rules: (a) triggers only cast the setting when it matches
  `^\d+$`, otherwise they use the fallback — a malformed value must degrade, not
  abort the triggering write; (b) `db.php` sets the parameter on **every**
  request (empty string when not logged in) so a stale value can never survive
  connection reuse if persistent PDO connections are ever enabled.
  `db.php` sets `app.user_id` per request;
  endpoints that open private PDO connections (journal saves, map saves,
  auto-route) still attribute correctly via the fallback because they all set
  `modified_user`/`record_user`. Consolidating those private connections onto
  `db.php`'s `$pdo` is a separate backlog item. For manual psql work:
  `SELECT set_config('app.user_id','<id>',false);` first attributes the session.
- **No-op suppression:** UPDATE trigger inserts nothing when no meaningful column
  changed — compare all columns except `modified_datetime`/`modified_user`
  (entity) and `record_datetime`/`record_user` (EAV) with `IS DISTINCT FROM`.
  Journal-note saves that only bump `modified_*` therefore create no history row.
- **Explicit column lists** inside trigger functions — positional `NEW.*` inherits
  the same fragility as `select *`.
- **INSERT snapshots NEW** with `history_action='INSERT'`; **UPDATE snapshots
  NEW** (post-change state, matching current PHP behaviour); **DELETE snapshots
  OLD** with `'DELETE'`.
- **EAV history keeps its value-log shape** (auto `value_id`, not the source
  row's) so the existing `lag()`-based audit queries in `*_load.php` keep working;
  a `history_action` column is added so deletes are distinguishable (existing rows
  backfilled `'UPDATE'`).
- **Audit/log views (load side) are out of scope** (confirmed 2026-07-19). No
  changes to the `lag()` CTEs in `project_load.php` / `account_load.php` /
  `stocklist_load.php`: no `history_action` filtering or INSERT/DELETE rendering —
  the audience isn't DBAs; blank-previous → populated reads as creation, the
  reverse as removal, and entity deletes aren't possible through the app. (Projects
  already shows trigger-written INSERT rows this way today.) Repointing the CTEs
  at `history_datetime`/`history_user` is a sub-note on the existing "audit-log
  queries" backlog item in `docs/improvement-opportunities.md`.
- **One trigger function per table** (confirmed 2026-07-19) — 3 entity + 15 EAV,
  following the established `public.fn_projects_history()` pattern (public schema,
  `fn_*` function / `trg_*` trigger naming, explicit column lists, `TG_OP` branch,
  static SQL). Rejected the shared-per-module `TG_TABLE_NAME` alternative: it needs
  dynamic `EXECUTE format(...)`, loses compile-time column checking, and would
  introduce a second style alongside the existing per-table functions
  (`fn_projects_history`, `prospector.network_cables_history_trigger`).

## Build

### Phase 1 — Actor attribution plumbing

`www/fn/db.php`: immediately after `$pdo` is created, when `$_SESSION['id']` is
set, run `SELECT set_config('app.user_id', :id, false)` (session-scoped, not
transaction-scoped, so it survives across statements on the pooled-per-request
connection). No behaviour change for triggers-less tables.

### Phase 2 — Migration A: history-table shapes + entity triggers

One migration (`db/017_*.sql`), in this order:

1. **`accounts.accounts_history`** — add `history_id bigint GENERATED ALWAYS AS
   IDENTITY PRIMARY KEY`, `history_action varchar(10) NOT NULL DEFAULT 'UPDATE'`,
   `history_datetime timestamp NOT NULL DEFAULT now()`, `history_user integer`;
   backfill existing rows `history_datetime = modified_datetime`,
   `history_user = modified_user`; then drop the `DEFAULT` from `history_action`.
   Remove the stray `accounts_history_account_id_seq` default/sequence if present.
2. **`stocklists.stocklists_history`** — identical upgrade.
3. **`projects.projects_history`** — no shape change.
4. **Trigger functions** — per-table, modeled on the existing
   `public.fn_projects_history()` (explicit column lists, `TG_OP` branch):
   `CREATE OR REPLACE` `public.fn_projects_history()` adding no-op suppression and
   the attribution chain; new `public.fn_accounts_history()` and
   `public.fn_stocklists_history()` to match.
5. **Triggers** — `trg_projects_history` already exists on `projects.projects`
   (keep it; the replaced function body takes effect immediately); create
   `trg_accounts_history` / `trg_stocklists_history` (`AFTER INSERT OR UPDATE OR
   DELETE FOR EACH ROW`) on `accounts.accounts` / `stocklists.stocklists`.

### Phase 3 — Migration B: EAV value-table triggers

Second migration (`db/018_*.sql`):

1. Add `history_action varchar(10) NOT NULL DEFAULT 'UPDATE'` to the 15
   `*_field_values_*_history` tables (projects/accounts/stocklists × 5 types);
   existing rows keep the default (accurate — PHP only ever wrote on change).
2. One trigger function per value table (15: e.g.
   `public.fn_project_field_values_text_history()`), same `fn_projects_history()`
   pattern, `AFTER INSERT OR UPDATE OR DELETE`, no-op suppression on
   `value IS NOT DISTINCT FROM`, history row carries `now()` as `record_datetime` +
   attribution chain (falling back to `NEW.record_user`) as `record_user` —
   matching what the PHP history inserts write today.
3. Triggers on all 15 value tables.

### Phase 4 — PHP cleanup (same change set, deployed with the migrations)

- `project_save.php` — remove the `projects_history` insert block and the three
  `*_field_values_*_history` inserts.
- `account_save.php` / `stocklist_save.php` — remove the `insert … select *`
  history inserts and their three EAV history inserts each.
- Wayleave endpoints untouched (own phase later).
- Deploy note: PHP removal and migrations must land together — migrations first
  then code, else double rows are written in the gap (harmless but noisy); code
  first then migrations loses history in the gap (avoid).

## Testing checklist

- [x] Pre-flight: confirm live trigger state matches the baseline — not run as a
      standalone query, but proven true by every functional test below behaving
      exactly as the expected trigger set would produce
- [x] Ordinary editor save (projects) → exactly one `projects_history` row,
      `history_action='UPDATE'`, correct `history_user`, no double row — tested
      2026-07-22, ok
- [x] Same for accounts and stocklists (upgraded shape populated) — tested
      2026-07-22, ok
- [x] Journal-note save (only `modified_*` bumped) → **no** history row — tested,
      ok (journal notes aren't editable, so no history table is needed for them
      either — confirmed as by-design, not a gap)
- [x] Map boundary / feature geometry save → history row now captured — tested via
      `map_boundary_update.php` 2026-07-22, ok. `map_feature_save.php` shares the
      identical `update projects.projects set geom = ...` mechanism so is expected
      to behave identically; not separately re-tested.
- [x] Cover-image upload → history row now captured — tested 2026-07-22: the row
      **is** written correctly (`fn_projects_history`'s no-op check includes
      `cover_image_url`). It just isn't currently rendered in the audit panel (no
      CTE branch for that field in `project_load.php`) — confirmed acceptable
      as-is, not a defect to chase here.
- [ ] `project_create_boundary.php` / `stocklist_create.php` → `'INSERT'` row —
      **not exercised** (no new project/stocklist was created during testing)
- [x] Manual psql UPDATE without `set_config` → row with `history_user` = previous
      `modified_user`; with `set_config` → attributed to the set id — tested
      2026-07-22, rows created correctly (see Risks for the timestamp/timezone
      note — unrelated to attribution, confirmed a display-side non-issue)
- [x] Manual psql DELETE → `'DELETE'` row snapshotting the old values — tested
      2026-07-22, ok
- [x] Dynamic text/date/int field save → one EAV history row per changed value,
      none for unchanged — confirmed (surfaced via the first-save null-population
      investigation, now tracked as its own separate backlog item, unrelated to
      trigger correctness)
- [x] Editor history/audit panels (project/account/stocklist `_load.php` lag()
      queries) still render correctly against upgraded tables — confirmed
      2026-07-22, with two known/accepted caveats: the first-save blank-row noise
      (separate backlog item) and `cover_image_url` not having a display branch
      (noted above)
- [x] `php db/migrate.php --apply` runs both migrations cleanly on a baseline
      restore — confirmed 2026-07-22

## Risks

- **Double-write window** if migrations and PHP cleanup don't deploy together
  (see Phase 4 deploy note).
- **`accounts_history`/`stocklists_history` backfill** assumes existing rows'
  `modified_*` values are trustworthy actor/time proxies — they are what the old
  `select *` snapshots copied, so no worse than today.
- **Geometry compare cost** — no-op suppression compares `geom` with
  `IS DISTINCT FROM` on every entity UPDATE; fine at current row sizes/volumes.
- **Trigger errors block writes** — a bug in a trigger function aborts the
  triggering statement. Mitigated by the loud testing checklist before prod.
- **History volume growth** — triggers capture paths that previously wrote
  nothing (geometry saves during heavy map editing). Acceptable; monitor table
  sizes.
- **Timestamps are stored in UTC, not local time** (confirmed 2026-07-22,
  `SHOW timezone` = UTC) — `now()` in the trigger functions behaves exactly like
  `now()` anywhere else in the app (no session `TimeZone` override exists
  anywhere in the codebase, confirmed by search). A manual psql session showing
  a time "1 hour behind" during BST is expected/correct raw-storage behaviour,
  not a bug; the app's front-end display layer is what applies the local-time
  conversion. Not something this migration introduced or needs to fix.
- **Existing duplicate rows in `projects_history`** — the live
  `trg_projects_history` + the PHP insert have both been writing since (at least)
  the baseline, so historical saves have near-identical row pairs. Phase 4 stops
  new duplicates; cleaning up historical ones is optional (they render as
  no-change rows in the audit view) — decide when eyeballing the data during
  testing.