# Field-governance audit tooling

Measurement tooling for the field-governance epic
([#4863](https://github.com/pingcap-inc/nexuscrm-pingcap-pack/issues/4863), story S1
[#4864](https://github.com/pingcap-inc/nexuscrm-pingcap-pack/issues/4864)).
Epic acceptance is "re-run these metrics, all green" — never a stale snapshot
(bucket assignments moved on 50 fields in the single month between the
2026-07-06 and 2026-08-05 audits).

**Read-only guarantee**: every script issues SELECT/SHOW/DESCRIBE only. Nothing
here mutates the database.

## Usage

```bash
./run_audit.sh                        # all nine audited objects
./run_audit.sh Opportunity Account    # subset
```

Connection settings (`TIDB_PROD_HOST/PORT/USER/PASSWORD/DATABASE`, optional
`TIDB_TLS=0` to disable TLS) follow a single per-variable precedence order:
**process environment → env file → defaults**. The env file is
`$NEXUSCRM_ENV_FILE` when set, else the first of repo-root `.env.prod` /
`.env.local` (git-ignored) or `../nexuscrm/.env` / `../NexusCRM/.env` (local
sibling-checkout convenience fallbacks) that defines a connection key. A
partial process-env override (just the host, or just `TIDB_TLS=0`) is honored
per key. Credentials never live in this repo. Requires `uv`.

Freshness by construction: `run_audit.sh` writes every run into a **new
run-scoped directory** (`$AUDIT_OUT/run-<stamp>/`, with `$AUDIT_OUT/latest`
pointing at the newest) and **never deletes anything**. A fresh directory
cannot contain stale artifacts, and previous runs stay intact — point
`AUDIT_BASELINE` directly at an old run directory for a bucket-move diff.
Within a run, the probe writes `manifest.json` last, so a crashed probe leaves
no manifest and classify/tables/metrics hard-fail rather than reporting
partial data (invoking the scripts directly against a reused `--out` dir keeps
this belt: the probe tombstones `manifest.json`/`twins.json` first). A
baseline that is malformed or matches zero requested objects fails instead of
reporting zero moves; pointing it at the output ROOT instead of a specific
`run-<stamp>` directory produces a warning. When invoking the scripts
directly, always pass `--out <run dir>` — the bare default writes to the ROOT
top level. If you used a pre-run-scoped version of this tool, clean any legacy
top-level artifacts out of the ROOT once so they cannot be mistaken for a
baseline.

Outputs (in the run directory, default `./audit_out/run-<stamp>/`, git-ignored):

- `<Object>.json` — full-field probe: every `_System_Field` row (on- and
  off-layout), layout membership, per-cohort fill counts, HelpText/Description
  coverage
- `manifest.json` — what THIS run actually probed; classification and metrics
  only trust files listed here, so stale artifacts from earlier runs cannot
  turn a partial run green
- `<Object>_all_fields.csv` — bucket classification + dependency references
- `tables/*.md` — per-bucket markdown tables, regional-field scan,
  label/API-name collision groups, optional baseline diff (`AUDIT_BASELINE=<dir>`)
- `metrics_summary.md` — the epic success-criteria measurables (SC-1..SC-4)

## Method

- **Cohort split**: migration preserved Salesforce `CreatedDate`s, so time-based
  splits are invalid. 15/18-char Salesforce Ids = migrated legacy rows; 36-char
  uuids = rows created natively in NexusCRM. Any other Id shape is counted as
  `unknown_cohort`, excluded from fill measurement, and fails the metrics run
  (bucket verdicts would be unreliable).
- **Soft deletes**: all data measurements (row counts, fill rates, twin
  counters) exclude `IsDeleted=1` rows, schema-gated on the column existing.
- **Buckets** (keys identical in CSVs and table headings): `A-empty-both` /
  `B2-bool-never-true` / `B-legacy-only` (native fill 0) / `D-low-fill` (<5%
  both cohorts) / `C-auto` (formula, rollup, audit, not writable) / `E-no-data`
  (no physical column) / `U-indeterminate-no-native` (object has no native rows
  yet — fill evidence is vacuous) / `F-active`.
- **Dependency scan**: active validation rules, active flows, reports, list
  views, approval definitions/actions, lead-convert field mappings, field
  defaults, and cross-field formulas/rollups.
- **Schema adaptivity**: metadata SELECTs are built from the columns actually
  present (`DESCRIBE` first), because the audited schema drifts across
  environments (canonical stack vs prod vs local bootstrap) and across time as
  governance itself retires fields. Absent optional columns degrade to NULL/
  skipped sources with a printed note; absent load-bearing columns abort.

## Known caveats (read before acting on results)

1. **Dependency matches are leads, not proof.** Flow matches are by field name
   across ALL objects (e.g. a Lead field name matching a Credits flow).
   Sources owned by another object are additionally scanned for dotted
   relationship-path references (`.<field>`) and tagged with `~` (e.g.
   `FieldDef~:Quote.X` on an Account field means a Quote formula *may* read a
   same-named field through some relationship or `$`-global path — the match
   does not verify the path targets this object). Own-scope entries rank
   before `~` leads in the capped deps list. Verify manually before deleting
   anything. Hiding
   a field on a layout is unaffected (the engine reads the database, not
   layouts).
2. **Emptiness is type-branched** (boolean `=1`, text `TRIM<>''`, JSON
   `JSON_TYPE<>'NULL'`, else `IS NOT NULL`). Comparing date/decimal columns with
   `<>''` falsely reports them dead — do not "simplify" this.
3. **Boolean fields cannot distinguish "unused" from "all false"** (bucket
   `B2-bool-never-true`).
4. **Formula fields compute on read** — physical-column fill rates underestimate
   them; rollups have no physical column at all (bucket E).
5. **Small native cohorts** (tens of rows on some objects) make "native fill 0%"
   weak evidence; the B/C bucket boundary flips on a single row. Treat B vs C as
   a gradient, not a verdict.
6. **Integration-fed fields** (ZoomInfo/HubSpot/AWS families) showing native 0
   may mean "integration not reconnected yet", not "unused" — confirm with the
   owning team before retiring.