03 — Audit / Change-History Subsystem & Trigger Machinery

Evidence base: references/db-analysis/raw/{triggers.tsv, table_inventory.tsv, columns.tsv, indexes.tsv, pk_presence.tsv, fk_graph.tsv, layer_details_*, function_inventory.tsv, schema_full.sql} plus live read-only queries against smartinventory_qa (PostgreSQL 14.8). All row counts below were measured, not estimated, unless marked “(est)”.

Scope

This subsystem is how SmartInventory records who changed what, when. It has three mechanically distinct layers, all implemented as row-level PL/pgSQL triggers in the monolithic public schema:

Layer	Mechanism	Objects
Shadow-table audit	Per-base-table audit_* mirror, one row appended per INSERT/UPDATE/DELETE	127 audit_* tables, 117 fn_trg_audit_* triggers
Narrow history logs	Append-only *_history tables capturing a few key columns	~23 *_history tables, ~9 dedicated trigger fns + the shared fn_trg_entity_status_history (46 attachments)
Business-logic triggers	Triggers that mutate other tables (cascade deletes, denormalised counters, status maintenance)	~62 distinct fns

Trigger inventory: triggers.tsv lists 300 trigger attachments on 148 tables, bound to 199 distinct trigger functions (the function catalog has 207 fn_trg_* functions in total; the extras are unattached/dead). Two functions dominate by reuse: fn_trg_update_entity_attribute (56 tables) and fn_trg_entity_status_history (46 tables).

Storage cost (from table_inventory.tsv): the 127 audit_* tables hold 3,551,529 rows / 1.20 GB. The whole DB is 12,407,251 rows / 7.99 GB. So audit shadow tables alone are ~29% of all rows and ~15% of total bytes. Add entity_status_history (1.26M rows / 96 MB) and the other history tables (~0.5M rows) and change-history data is ~38% of all rows in the database.

The “68” figure from prior notes is the count of audit_att_details_* tables specifically. The full 127 breaks down as: 68 audit_att_details_*, 36 audit_item_template_*, 9 audit_isp_*, and 14 other (audit_point_master, audit_line_master, audit_polygon_master, audit_user_master, audit_global_settings, audit_ticket_master, audit_role_template_permission, audit_connection_info, audit_landbase_layer_master, audit_region_boundary, audit_province_boundary, audit_upload_summary, audit_configuration_setting, audit_landbase_dropdown_master).

Data model & relationships

The shadow-table pattern (audit_<base>)

For an audited base table att_details_cable, there is a sibling audit_att_details_cable. The audit table is NOT a strict superset of the base — it is a partial snapshot plus audit metadata. Measured on the cable pair (columns.tsv):

• att_details_cable: 152 columns
• audit_att_details_cable: 125 columns
• Extra columns the audit table adds (6): audit_id (the only PK-ish surrogate), action (varchar(10), holds 'I'/'U'/'D'), action_date, plus three denormalised label columns start_name, end_name, ticket_id.
• Columns present in the base but DROPPED from the audit table (33): a_city, a_latitude, a_region, address, b_longitude, cable_remark, codification_sequence, elevation, is_barcode_verified, is_manual_barcode, is_new_entity, is_used, loop_count, loop_length, manhole_count, section_name, splited_by, splitting_system_id, … — i.e. the audit table silently does not record many columns of the live row.

So the relationship base↔audit is logical only: keyed by system_id (the entity surrogate), with no foreign key (fk_graph.tsv shows zero FKs from any audit_* or *_history table back to its base) and, for the large tables, no index at all (see Issues). A given snapshot row also stores created_by/on, modified_by/on, status, network_status, network_id copied verbatim from the base row.

Audit tables are append-only fact logs: the trigger never updates or deletes audit rows; it only INSERTs. The action column is the only thing distinguishing a create from a modify from a delete. Note this captures a NEW-image snapshot for I/U and an OLD-image snapshot for D — there is no before/after pairing in a single row; to reconstruct a diff you must self-join consecutive snapshots of the same system_id ordered by action_date.

The narrow-history pattern (*_history)

entity_status_history (the largest non-audit history table, 1.26M rows) is a thin status ledger: 9 columns — audit_id, entity_system_id, entity_type, status, status_updated_on, status_updated_by, status_remarks, created_by, created_on. One row is appended whenever a watched entity’s status or status_remark changes (or on any insert/delete). user_permission_area_history, user_module_mapping_history, role_module_mapping_history follow the same “copy a handful of identity columns + an action_name” shape. So *_history tables are purpose-built projections (status only, permissions only), whereas audit_* tables attempt a full row snapshot.

Catalog

Object	Rows (measured)	Cols	Purpose
audit_att_details_cable	591,334	125	snapshot of every cable I/U/D (base = 95,149)
audit_point_master	538,278	27	snapshot of every point geometry write (base = 424,887)
audit_att_details_building	387,422	96	building attribute snapshots
audit_att_details_pole	368,815	85	pole snapshots (base ≈ 183k)
audit_line_master	310,651	23	line geometry snapshots
audit_att_details_duct	276,352	100	duct snapshots
audit_isp_shaft_info	176,601	19	ISP shaft snapshots
audit_att_details_manhole	171,808	90	manhole snapshots
entity_status_history	1,263,778	9	per-entity status-change ledger
user_permission_area_history	175,005	12	geographic-permission grant/revoke log
user_module_mapping_history	149,021	10	module-access change log
role_module_mapping_history	101,954	6	role→module mapping change log
att_details_bulk_entity_delete_history	37,269	5	bulk-delete audit
user_login_history	13,357	19	session/login log (separate trg_user_login_history)

(Many declared *_history tables — feasibility_history, task_tracking_history, route_issues_history, hpsm_ticket_master_history, location_tracking_history, etc. — currently hold 0 rows: feature wiring exists but is unused in this dataset.)

Registry wiring (layer_details)

Auditing is config-driven through the entity registry layer_details (66 layers). Relevant columns: is_history_enabled (bool), history_view_name (read-side view, e.g. vw_att_details_pole_audit), audit_table_name (e.g. audit_att_details_pole), is_report_enable/report_view_name, other_info_view_audit. Measured: 59 of 67 layer rows have is_history_enabled = t and 52 have audit_table_name populated. So the registry both declares that a layer is audited and names the shadow table and the view the UI reads history through. The shared trigger functions look the layer up at runtime: fn_trg_entity_status_history does select upper(layer_name) … from layer_details where upper(layer_table)=UPPER(TG_RELNAME) to resolve the entity_type string it stamps into history.

What data is actually held

The audit tables reveal the true write-churn of the system, which is far higher than the live row counts suggest:

• att_details_cable: 95,149 live rows, but 591,334 audit rows — broken down by action: I = 262,907, D = 167,758, U = 160,669. Roughly 2.8 INSERT events per surviving cable, i.e. most cables are created and deleted repeatedly (heavy plan/redo churn). The 167k deletes confirm cables are routinely removed.
• att_details_pole: ≈183k live, 368,815 audit — I = 183,134, U = 185,564, D = 258. Here almost no deletes; updates roughly equal the live population (each pole edited ~once on average).
• point_master: 424,887 live, 538,278 audit — I = 537,155, U = 1,123 (deletes routed away by the geometry-cascade triggers, see below). Geometry is re-inserted, essentially never updated in place — point edits create a new master row rather than mutate the existing one.
• entity_status_history distribution by entity_type (top): CABLE 430,746; DUCT 263,841; POLE 183,464; BUILDING 142,933; STRUCTURE 142,530; MANHOLE 86,163; TRENCH 76,619. CABLE’s 430k status events against 95k live cables again signals very high status-transition volume per asset.

These numbers are the strongest evidence in the whole subsystem: the audit machinery is the only place that records the real lifecycle volume — the live tables are just the current snapshot of a much busier edit stream.

Business logic — key stored functions

fn_check_history_record(old, new, ignore_columns [, ignored_by]) — function_inventory: 22 src_lines

The shared change-detector used by every UPDATE branch. It does a generic JSON diff: json_each(to_json(new_p)) JOIN json_each(to_json(old_p)) ON key, and sets status = 1 if any column (excluding a configurable ignore-list) is IS DISTINCT FROM its old value. Callers always pass the same ignore-list: modified_on,gis_design_id,codification_sequence,area_system_id,area_id,subarea_system_id, subarea_id,dsa_system_id,dsa_id,csa_system_id,csa_id plus modified_by. So edits that only touch bookkeeping/denormalised geography columns are not audited. Note: v_status is never initialised, so when nothing material changed the function returns NULL (not 0); callers guard with if(_value = 1), which treats NULL as “no record” — correct, but fragile.

fn_trg_audit_att_details_cable() — 163 src_lines (representative of the 117 audit triggers)

AFTER INSERT OR UPDATE OR DELETE … FOR EACH ROW. Structure:

• INSERT: INSERT INTO audit_att_details_cable (… 110+ cols …) SELECT new.* , 'I' AS action FROM att_details_cable WHERE system_id = new.system_id. It then also mutates the base table — patches other_info JSON with record_system_id via dynamic EXECUTE.
• UPDATE: calls fn_check_history_record(OLD,NEW,…); only if a material column changed does it append a 'U' snapshot. It then runs unrelated denormalisation (recomputing att_details_fiber_link route/loop lengths) — i.e. audit logic and business logic are entangled in one trigger.
• DELETE: appends an 'D' snapshot of OLD, then frees downstream att_details_fiber_link rows.

The 110+ explicit column list is hand-maintained — adding a base column requires editing this list (and 116 sibling functions) by hand. This is the schema-drift engine (see Issues).

fn_trg_entity_status_history() — 46 src_lines, attached to 46 tables

Resolves entity_type from layer_details by table name, then on INSERT/DELETE always logs a status row, and on UPDATE logs only if status or status_remark changed (gated again by fn_check_history_record). It also has a side effect unrelated to history: when an entity tied to a NETWORK_TICKET source_ref_id is touched, it transitions the linked ticket from ASSIGNED → INPROGRESS in att_details_networktickets. Another example of auditing fused with workflow.

fn_trg_update_entity_attribute() — 105 src_lines, attached to 56 tables

Despite the name this is not audit — it maintains denormalised display labels: on UPDATE it reads display_name_settings/layer_details, recomputes the entity’s display name and propagates label changes. Gated by fn_check_history_record. It is the single most-attached trigger function.

fn_trg_user_permission_area_history() — 32 src_lines

The clean *_history archetype: INSERT/UPDATE/DELETE each append a row tagging action_name (‘INSERT’/‘UPDATE’/‘DELETE’) — no diff check, every event recorded. Same shape as fn_trg_user_module_mapping_history, fn_trg_role_module_mapping_history.

Trigger categorisation (all 300 attachments)

Purpose	How identified	≈ Count
Audit shadow-table	fn_trg_audit_*	117
Display-label denormalisation	fn_trg_update_entity_attribute	56
Status-history ledger	fn_trg_entity_status_history	46
Geometry cascade-delete	fn_trg_delete_<entity>_geom (40 fns) + fn_trg_add_delete_<entity>_geometry (10)	~50
Denormalised counters / rollups	fn_trg_update_duct_cable_count, fn_trg_update_used_entity_info, fn_trg_updatecablecalculatedlength, fn_trg_updateductcalculatedlength	~6
Status / functional maintenance	fn_trg_update_core_port_status (342 src_lines!), fn_trg_update_building_status, fn_trg_structure_functionality, fn_trg_update_core_port_status	~6
Narrow domain history	fn_trg_user_*_history, fn_trg_role_module_mapping_history, fn_trg_redline_status_history, fn_trg_route_issue_history, fn_trg_task_tracking_history, fn_trg_hpsm_ticket_master_history, fn_sf_trg_feas_cable_type_history	~10
Region/province enrichment, sync, misc	fn_trg_update_entity_region_province, fn_sync_fms_latlon_to_point_master, fn_trg_sync_layer_details, fn_refresh_dynamic_view_on_attr_def, fn_fs_trg_feas_input, etc.	~9

So only ~54% of triggers are auditing (audit + status-history). The rest do real data manipulation. The geometry-cascade triggers (fn_trg_add_delete_cable_geometry, 23 src_lines) are pure manual referential integrity: on DELETE of att_details_cable they DELETE FROM line_master WHERE system_id=old.system_id AND UPPER(entity_type)='CABLE' — re-implementing ON DELETE CASCADE because the geometry/master tables have no FK to the attribute tables.

Design choices & trade-offs

• Trigger-based, full-row shadow tables, configured per layer via layer_details. Pros: capture is automatic regardless of which app path writes the row; history survives even raw SQL edits; the snapshot is queryable with plain SQL. This is a coherent, if dated, “config-not-code” auditing strategy.
• Snapshot (not delta) model. Each audit row is a whole NEW (or OLD) image plus an action flag. Easy to read a single point-in-time state; expensive to store and awkward to diff (requires self-join on system_id ordered by action_date).
• Change filtering via fn_check_history_record. Avoids logging no-op updates and bookkeeping-only edits — sensible, and the JSON-diff approach is column-list-agnostic (it iterates whatever columns the row has), which partly mitigates drift on the detection side even though the insert side is hardcoded.
• Auditing entangled with business logic. The same trigger function both writes the audit row and performs unrelated mutations (fiber-link length recompute, ticket status transitions, other_info JSON patching). This couples observability to behaviour and makes the triggers hard to reason about or disable.

Issues, risks & anti-patterns (evidence-backed)

1. Schema drift between base and audit is real and silent. att_details_cable has 152 columns; audit_att_details_cable has 125 and is missing 33 base columns (a_latitude, is_barcode_verified, loop_length, codification_sequence, …). Because each of the 117 audit functions hardcodes a 110+-column INSERT … SELECT list, every base-table column added since the audit table was last edited is not audited at all, with no error. Maintaining this requires editing 117 functions + 127 tables in lockstep.

2. No indexes on the largest audit tables → history reads are full scans. indexes.tsv shows only 12 indexes across all 127 audit tables, and the 12 that have them are the small ones (audit_att_details_accessories, …_adb, …_bdb, audit_isp_*, audit_global_settings…). The big ones — audit_att_details_cable (591k), audit_att_details_pole (369k), audit_point_master (538k), audit_att_details_building (387k) — have no index on system_id or action_date. Fetching the history of one asset is a sequential scan of a half-million-row table.

3. 115 of 127 audit tables have NO primary key (pk_presence.tsv). Combined with append-only writes under trigger churn, duplicate/garbage rows cannot be constrained out.

4. No referential integrity from audit/history to base (0 FKs in fk_graph.tsv). Audit rows are orphan-able and the base↔audit link is “trust the system_id”.

5. Write amplification. Every att_details_* row write fires multiple AFTER ROW triggers — typically audit + entity_status_history + update_entity_attribute + a geometry trigger (4 per table, see att_details_adb in triggers.tsv). A single cable INSERT therefore does its own write plus an audit INSERT, a status-history INSERT, a label recompute, and base-table mutations — and the audit INSERT re-SELECTs the freshly written row by system_id. Bulk loads pay this per-row cost 300× over.

6. Unbounded growth / no retention. Audit rows are never pruned. Cable churn already produced 591k audit rows for 95k live cables; entity_status_history is 1.26M rows. There is no partitioning by action_date and no archival path. Audit is already ~38% of all rows.

7. Auditing fused with mutation (see Design). Disabling a noisy audit trigger would also disable ticket-status transitions / length recomputes hidden in the same function — they cannot be separated without code surgery.

8. NULL-return reliance in fn_check_history_record. Uninitialised v_status returns NULL; correctness depends on every caller writing exactly if(_value = 1). A future caller using if(_value <> 1) or IS NOT NULL would silently behave wrong.

Implications for the new platform

Drop the trigger-per-table shadow model entirely. It is the single largest source of schema-drift risk, storage bloat, and write amplification in the database, and it fuses observability with business logic.

Keep the intent, not the mechanism:

• What to preserve: (a) the per-entity status ledger concept (entity_status_history) is genuinely useful and well-shaped — a status timeline is a first-class domain concept; (b) the layer_details registry knowing “is this entity audited” is a clean config point worth keeping; (c) the change-filter idea (don’t log no-op/bookkeeping edits).

Modern replacement options, in order of recommendation:

1. Native temporal / system-versioned history. Use one history table per base table generated by tooling (or PostgreSQL extensions such as temporal_tables, or a single generic function driven by catalog reflection rather than 117 hardcoded column lists). Eliminates the hand-maintained column lists → kills schema drift. Index every history table on (entity_id, valid_from) and partition by time for retention.
2. Append-only event log / CDC (e.g. logical replication → Debezium → Kafka/object storage, or a single domain_events table). Decouples audit from the transactional path entirely, removes per-row trigger overhead, and gives an immutable, replayable stream that doubles as integration. Best fit if the new platform is event-oriented.
3. Generic JSONB delta audit — one audit_log(table, pk, action, actor, at, old jsonb, new jsonb) table written by a single reflective trigger or in the application/service layer. Stores deltas, not full snapshots, so storage scales with change size, and it is column-list-agnostic so no drift.

Non-negotiables for whichever path: separate audit from business mutations; index/partition all history; enforce retention; never hand-maintain parallel column lists; and move cascade-delete behaviour (the 50 *_geom triggers) to real FK ON DELETE CASCADE once geometry and attributes are properly related.