# Audit Log — Metadata-Driven Query Rewrite (Projects / Accounts / Stocklists)

- **Date:** 2026-07-23
- **Status:** Complete
- **Status Date:** 2026-07-24
- **Phases:** 5
- **Phases Complete:** 5 (all phases done; front-end acceptance passed on all three modules,
  Dave, 2026-07-24. Sole open follow-up — attribution → `history_*` — tracked separately
  under Database/Audit in docs/improvement-opportunities.md.)
- **Notes:** Promotes the "Audit-log queries: move per-field CTE unions to a maintainable
  structure" backlog item and folds in Action 2 (blank→blank filter), so these queries are
  touched **once**, not twice. Scope is projects/accounts/stocklists only; opportunity and
  wayleave keep their current audit code (aligned later — see wayleave realignment item).
  **Phase 1 result: clean — resolution is fully meta-encoded, `field_form_id` == history
  column for every static field, no sixth pattern.** **Phase 2 generator SQL validated in
  DataGrip against known ids (2026-07-24) while old CTEs still live — safe to wire (Phase 3)
  + update the frontend (Phase 4) together next.** **Phases 3+4 built (2026-07-24): three
  `*_load.php` now call `buildAuditLogQuery($dbh, …)` + bind `:entity_id`, ~450-line CTE
  strings deleted; three editors render `previous_value`/`new_value` natively (formatters +
  dead Bootstrap-Table block removed). Awaiting Dave's live front-end check — hard-refresh
  to defeat stale-JS caching.**

## Plan Phases

1. Static-field audit & decisions (de-risker — do first)
2. Shared audit-query generator in `global_functions.php`
3. Wire the three load endpoints; retire the ~450-line CTEs and the stopgap filter
4. Frontend audit-render simplification (3 editors)
5. Diff verification & cutover

## Problem

The audit trail in `project_load.php` / `account_load.php` / `stocklist_load.php` is ~450
lines of hand-copied CTEs **per module**. Each builds one CTE `a` that `union all`s:

1. **Dynamic (EAV) field history** — five near-identical `SELECT`s (text/date/int/numeric/
   boolean) from `<entity>_field_values_<type>_history` joined to `<entity>_fields`, each
   with a `lag()` for the previous value. 5 blocks × 3 modules = 15 near-identical chunks.
2. **Static field history** — bespoke named CTEs from `<entity>_history` with per-field
   label joins and change filters.
3. **Fixed rows** — access-log and journal-entry, plus a ~20-column projection
   (`text_value … previous_boolean_value`) with `null::type` placeholders repeated in
   every branch.

The copy-paste has already produced real bugs (wrong labels, a hardcoded
`where project_id = 16` debug filter). It is unmaintainable and cannot pick up
admin-created fields automatically. Action 2's blank→blank filter currently rides on top as
a single outer `WHERE` (a deliberate stopgap) that this rewrite absorbs.

### Settled design facts (from scoping with Dave, 2026-07-23)

- **The audit log is a plain-text log** — timestamp, username, field/action, previous value,
  current value. No interactivity, no id+value pairs. Everything renders as text. See
  [[soft-delete-preserves-audit-log]].
- **Display contract (Tabulator columns the frontend renders, on `dataAuditLog`):**
  | Display (Tabulator `field`) | SQL source |
  |---|---|
  | Record Date (`record_date`) | `record_date` |
  | Username (`username`) | `username` |
  | Field/Action (`field_name`) | `field_name` |
  | Previous Value (`previous_value`) | `previous_value` (new, text) |
  | New Value (`new_value`) | `new_value` (new, text) — journal/access text lands here |
  - **SQL emits `previous_value` / `new_value`** to match the existing Tabulator `field:`
    names (least churn). The current-value column is `new_value`, *not* `current_value`.
    Renaming both to `current_value` is optional and costs an extra edit per editor.
- **Only five static fields need label resolution**, via two mechanisms that already exist
  in the field meta:
  - **Autocomplete label map** (`field_autocomplete_type` → `getAutocompleteLabelMap()` /
    `resolveAutocompleteLabel()`): **username, parent, account, company**.
  - **Options-source canonical table** (`field_options_source`): **status**.
  - **Everything else** is a plain meta-driven value shown as-is.
- **No orphaned label ids, ever** — FK integrity + no-hard-delete (values are *disabled*,
  never deleted, precisely to keep the audit log intact, see
  [[soft-delete-preserves-audit-log]]). The generator can assume every label join resolves;
  no orphan-null handling needed.
- **Column collapse (recommended):** because everything is text, the SQL output collapses
  from five typed value columns (+ five `previous_*`) to just **`new_value text`** and
  **`previous_value text`** — casting each value to text at source. This removes the biggest
  block of boilerplate. Better still, the two live Tabulator formatters only exist to
  *synthesize* `previous_value` / `new_value` from the typed columns via a
  `switch (field_data_type)`; once SQL emits those named columns (coalesced to `''`), the
  **formatters are deleted** and Tabulator renders the fields natively — so the JS change is
  a *removal*, not a rewrite.
  - *Alternative if we want zero SQL-column rename:* the generator keeps emitting the five
    typed columns and the formatters stay. More boilerplate on both sides. Recommendation is
    the collapse + formatter removal.

## Build

### Phase 1 — Static-field audit & decisions (de-risker)

Confirm the assumptions before writing any generator. Cheap, and it turns "hope the metadata
is complete" into fact.

- Enumerate every static-field history block in all three `*_load.php` CTEs and confirm the
  label-resolution set is exactly **{username, parent, account, company, status}** with no
  sixth pattern (e.g. a bespoke join) hiding in one module.
- Confirm `field_form_id` (meta) maps to the real column name on each `<entity>_history`
  table for every static field; flag any mismatch (generation emits bad SQL otherwise).
- Confirm access-log and journal-entry are the only non-field rows, and their text lands in
  `new_value`.
- **Decision — parent field:** show label only (`new_value` = parent name); drop the
  current id-echo into `int_value`. Agreed plain-text.
- Confirm opportunity/wayleave are out of scope for this pass.

Output: a short table of `{static field → source column → resolution mechanism → value
type}` per module that drives Phase 2.

### Phase 2 — Shared audit-query generator in `global_functions.php`

One function builds the entire `a` union for any of the three modules from a small config
(schema, entity name, id column) + the field meta. It returns the SQL string; the id is a
bound parameter, and **all identifiers come from server-side meta/whitelist, never request
input** (no injection surface).

Structure of the generated union, all branches projecting the same columns
(`field_form_id, field_name, field_data_type, record_date, record_datetime, record_user,
username, previous_value::text, new_value::text`):

- **EAV history** — loop over `['text','date','int','numeric','boolean']`, emitting one
  branch per type from `<entity>_field_values_<type>_history`, `new_value = value::text`,
  `previous_value = lag(value)::text`. Blank→blank filter built in
  (`coalesce(new_value,'') <> coalesce(previous_value,'')`), replacing the stopgap.
- **Static history** — for each static field from meta, emit a branch from
  `<entity>_history` with `lag()`; resolve the display value by mechanism:
  - `field_options_source` set → join canonical table for the label (status).
  - `field_autocomplete_type` set → resolve via the label map (username/parent/account/
    company).
  - neither → plain `column::text`.
  Each carries its own change filter.
- **Fixed rows** — access-log and journal-entry branches, text in `new_value`.

**As built (2026-07-23)** — `buildAuditLogQuery($dbh, $entity)` in `global_functions.php`
returns the *complete* query (the `with a as (…) select … where entity_id = :entity_id …`
wrap included), with one bound param `:entity_id`. Notes vs the sketch above:
- Output columns collapsed to 9: `field_form_id, field_name, entity_id, record_date,
  record_datetime, record_user, username, previous_value, new_value` — `field_data_type` and
  the typed value columns are gone (Phase 4 removes the JS that used them). Final projection
  `coalesce()`s both values to text so no null cells reach Tabulator.
- **Static change filter tests the raw stored value**, not the label: each static branch is a
  subquery exposing `raw_value = p.<col>` / `raw_prev = lag(p.<col>)`, filtered
  `coalesce(raw_value::text,'') <> coalesce(raw_prev::text,'')`. Faithful to the old int/text
  filters; avoids the "two ids, same label" edge (negligible, but correct).
- **Blank→blank filter** is the single outer `not (coalesce(new_value,'')='' and
  coalesce(previous_value,'')='')` (Action 2 folded in). Static rows always pass it (a
  changed raw value yields a non-empty label given no orphans — see
  [[soft-delete-preserves-audit-log]]).
- Label joins reuse the `resolveAutocompleteLabel()` / `$optionsSources` triples as a
  server-side config map; `field_form_id` is identifier-validated before use as a column.

### Phase 3 — Wire load endpoints; retire the old CTEs

- Replace the ~450-line inline query in each `*_load.php` with a call to the generator, kept
  as `select * from (<generated a>) a where <entity>_id = :id order by record_datetime desc`.
- Delete the stopgap outer blank→blank `WHERE` (now built into the generator).
- Keep the result-set column names the frontend expects (post-collapse: `new_value` /
  `previous_value` + the metadata columns).

### Phase 4 — Frontend audit-render simplification (3 editors)

The live audit render is the **Tabulator on `dataAuditLog`** (gated by `results_audit_log`),
with two column formatters that synthesize the value cells from the typed columns.

- In `project_edit_v2.js`, `account_edit_v2.js`, `stocklist_edit.js`:
  - **Delete the two Tabulator formatters** on the "Previous Value" (`field: "previous_value"`)
    and "New Value" (`field: "new_value"`) columns — the `switch (item.field_data_type)`
    blocks (project_edit_v2.js: ~2943 and ~2979). With SQL now emitting `previous_value` /
    `new_value` text, Tabulator renders the named fields directly. Keep the column
    definitions; just drop the `formatter` property (and its `minWidth` stays as-is).
  - **Remove the dead commented-out Bootstrap Table block** (the `/* var html = … */` starting
    ~line 3012, which contains a third now-irrelevant `switch` at ~3035). Cleanup only.
- **Leave untouched** the separate current-value → form-input path: the
  `switch (item.field_data_type)` inside `dataDynamic.forEach` (gated by `results_dynamic`,
  project_edit_v2.js: ~2742) ending in `$('#'+item.field_form_id).val(v)`. That loads current
  values into the editor form inputs — a different data structure (`dataDynamic`, not
  `dataAuditLog`) and out of scope.
- Opportunity/wayleave editors unchanged.

### Phase 5 — Diff verification & cutover

Old and new must produce the same audit rows apart from intended normalisations.

- Build a DB-level diff (handed to Dave to run — no local DB): run the retired query and the
  generated query for a set of real records per module, normalising the old query's typed
  columns to a single coalesced text value, and `EXCEPT` both directions.
- Expected/allowed differences (not regressions): blank→blank rows now absent; parent rows
  show label only (no id echo); any label consistently in `new_value`.
- Any *other* diff is investigated before cutover.
- Cutover once each module diffs clean.

## Phase 1 findings (2026-07-23)

Sources: the audit CTEs in the three `*_load.php` files, the static-field meta seeds
(`db/012`–`014`), and `resolveAutocompleteLabel()` (`global_functions.php:845`). The meta —
not the CTEs' hardcoded strings — is authoritative, because the Phase 2 generator reads meta.

### Resolution rule (three branches, all from existing meta columns)

Per static field row (`field_type='static'`):
1. **`field_options_source` set → options-source (status).** Value column = `field_form_id`
   (the `_id` column); label = join the canonical status table → `*_status_desc`.
2. **else `field_autocomplete_type` set → autocomplete label.** Resolve via the *same map*
   `resolveAutocompleteLabel()` already uses (reuse it, don't re-hardcode):
   | type | table | id col | label col |
   |---|---|---|---|
   | `usernames` | `users.users` | `id` | `username` |
   | `companyname` | `users.companies` | `company_id` | `company_name` |
   | `accountname` | `accounts.accounts` | `account_id` | `account_name` |
   | `projectname` | `projects.projects` | `project_id` | `project_name` |
   | `stocklistname` | `stocklists.stocklists` | `stocklist_id` | `stocklist_name` |
3. **else → plain.** Show the raw `field_form_id` column as text (only the entity-name field).

### Static fields per module (from meta seeds; `field_form_id` == history column throughout)

**Project** (`projects.projects_history`, id `project_id`):
| field_form_id | field_name (meta) | resolution | label source |
|---|---|---|---|
| `project_name` | Project Name | plain | — |
| `account_id` | Account | autocomplete `accountname` | accounts.account_name |
| `project_manager` | Project Manager | autocomplete `usernames` | users.username |
| `company_id` | Assigned Supplier/Company | autocomplete `companyname` | companies.company_name |
| `parent_project_id` | Parent Project | autocomplete `projectname` | projects.project_name |
| `project_status_id` | Project Status | options-source `project_status` | project_status.project_status_desc |

**Account** (`accounts.accounts_history`, id `account_id`) — no self "account" field:
| `account_name` | Account Name | plain | — |
| `account_manager` | Account Manager | autocomplete `usernames` | users.username |
| `company_id` | Assigned Supplier/Company | autocomplete `companyname` | companies.company_name |
| `parent_account_id` | Parent Account | autocomplete `accountname` | accounts.account_name |
| `account_status_id` | Account Status | options-source `account_status` | account_status.account_status_desc |

**Stocklist** (`stocklists.stocklists_history`, id `stocklist_id`):
| `stocklist_name` | Stocklist Name | plain | — |
| `stocklist_manager` | Stocklist Manager | autocomplete `usernames` | users.username |
| `company_id` | Assigned Supplier/Company | autocomplete `companyname` | companies.company_name |
| `parent_stocklist_id` | Parent Stocklist | autocomplete `stocklistname` | stocklists.stocklist_name |
| `account_id` | Account | autocomplete `accountname` | accounts.account_name |
| `stocklist_status_id` | Stocklist Status | options-source `stocklist_status` | stocklist_status.stocklist_status_desc |

### Key findings

1. **`field_form_id` == history column for every static field** — including status
   (`project_status_id` == column `project_status_id`). The generator uses `field_form_id`
   directly as the column name; **no form_id≠column mismatch anywhere.** (The old CTEs
   hardcoded `'project_status'` as a display id — inconsistent with meta, now irrelevant.)
2. **No sixth resolution pattern.** The set is exactly {plain-name, status, manager
   (usernames), parent, company, account}, matching the agreed {username, parent, account,
   company, status} + plain name.
3. **Reuse `resolveAutocompleteLabel()`'s map** as the single source of truth for label
   joins — the generator must not re-hardcode the five table/column/id triples.
4. **Copy-paste label bugs get fixed for free.** account/stocklist CTEs hardcode
   `field_name` as `'Project Name'` / `'Project Status'` (visible bug); meta has the correct
   labels, which the generator reads. Also refreshes stale labels (e.g. old `'Assigned
   Company'` → `'Assigned Supplier/Company'`).
5. **Dual `int_value` echo confirmed** on parent/company/account in all three modules —
   decision stands: drop it, label-only in `new_value`. Since output is text, the static
   row's `field_data_type` (all `int` except the `text` name) is irrelevant to display.
6. **Access-log + journal are the only non-field rows** (all three modules): `'User Viewed
   X'` and `log_text`; text in `new_value`, always non-empty (blank filter never touches
   them).
7. **File uploads/downloads are captured *through* the journal, not a separate source.**
   `file_upload.php` and `serve_file.php` both `INSERT INTO <entity>_journal (…, log_text)`
   with a descriptive message, so a file action is a journal row and appears in the audit as
   a `'Journal Entry'`. The journal branch has no type filter, so it catches them for free —
   the generator preserves the two fixed branches and nothing more is needed. (The editor's
   *Files panel* is a separate `vw_file_uploads` query, unrelated to the audit log.)
   - *Possible future enhancement, out of scope:* give file actions their own row label
     (e.g. `field_name = 'File Upload'`) instead of lumping them under `'Journal Entry'`.
     That's a new behaviour, not part of the like-for-like rewrite.

### Open item carried into Phase 2 (not a blocker)

- **Attribution columns.** Static CTEs use `modified_datetime` / `modified_user` (+ join for
  actor username); dynamic EAV branches use `record_datetime` / `record_user`. Standardise
  the generator on `history_datetime` / `history_user` for correct actor attribution on
  manual-SQL/GUC paths — **but first confirm those columns exist on all three `*_history`
  tables and the 15 EAV `*_history` tables** (expected from migrations 017/018). Verify at
  the top of Phase 2; fall back to the current columns if not present.

## Testing checklist

- [x] Phase 1 audit table produced; no sixth resolution pattern; all form_id→column
  mappings confirmed (`field_form_id` == history column throughout).
- [x] Generator output reproduces the live audit log *(2026-07-24 — Dave: generated query
  compared side-by-side against the live front-end audit panel; matching row counts and
  values across text/int/date inputs and both static & dynamic dropdown fields)*. Equivalence
  considered proven; formal `EXCEPT` diff not needed.
- [x] Same for an account *(2026-07-24, front-end side-by-side)*.
- [x] Same for a stocklist *(2026-07-24, front-end side-by-side)*.
- [x] Audit panel renders correctly in all three editors (timestamp, user, field, prev,
  current); journal entries and access-log rows show text in Current Value *(2026-07-24 — Dave, live)*.
- [x] Status / username / parent / account / company changes show human labels, not ids *(2026-07-24)*.
- [x] Blank→blank phantom rows absent (including pre-existing ones) *(2026-07-24)*.
- [x] Form-input population (the untouched path) still loads current values correctly *(2026-07-24)*.
- [ ] Dynamic numeric/boolean fields appear in the audit log (depends on the separate
  "numeric/boolean never saved" fix landing — note the interaction).

## Risks

- **Silently-wrong labels rather than a crash** — a missed resolution pattern renders a
  blank/id instead of a name and won't error. Mitigated by the Phase 1 audit (bounds the
  patterns) and the Phase 5 row-diff (makes any divergence visible before cutover).
- **`field_form_id` ≠ history column** for some static field would emit bad SQL — caught in
  Phase 1.
- **JS blast radius** — the collapse touches three editor files; the form-population path
  must not be caught up in it (Phase 4 note). The zero-JS-change alternative (keep typed
  columns) is the fallback if this proves fiddly.
- **No test harness** — verification is the manual DB diff + visual panel check; there is no
  automated coverage of these queries.
- **Interaction with "numeric/boolean dynamic fields never saved"** (open backlog item) —
  those types are read by the audit query but currently never written; the audit log can't
  show what was never stored. Independent fix, noted so results aren't misread.