Audit History & Historical Queries¶

Identity Atlas v5 uses a shared _history audit table populated by PostgreSQL triggers. Every insert, update, and delete on tracked tables is recorded as a JSONB snapshot — giving you a complete, queryable change history without any application-level code.

v4 to v5 change

v4 used SQL Server system-versioned temporal tables (FOR SYSTEM_TIME syntax). v5 replaced these with a single _history table and trigger-based recording after the migration to PostgreSQL. The concept is the same — automatic change tracking — but the query syntax is different.

How It Works¶

A generic PostgreSQL trigger function (fg_record_history) fires on INSERT, UPDATE, and DELETE. It writes one row to _history per actual change:

Column	Type	Description
`id`	`bigserial`	Auto-incrementing primary key
`tableName`	`text`	Name of the source table (e.g. `Principals`, `Resources`)
`rowId`	`text`	The `id` value of the changed row
`operation`	`char(1)`	`I` = insert, `U` = update, `D` = delete
`changedAt`	`timestamptz`	Timestamp of the change
`rowData`	`jsonb`	Full row snapshot after the change (or the deleted row for `D`)
`prevData`	`jsonb`	Previous row state (NULL for inserts)

Tracked Tables¶

Table	Tracked	Row key strategy
`Principals`	Yes	surrogate `id`
`Resources`	Yes	surrogate `id`
`ResourceAssignments`	Yes	composite `resourceId\\|principalId\\|assignmentType`
`ResourceRelationships`	Yes	composite `parentResourceId\\|childResourceId\\|relationshipType`
`IdentityMembers`	Yes	composite `identityId\\|principalId`
`AssignmentPolicies`	Yes	surrogate `id`
`GovernanceCatalogs`	Yes	surrogate `id`
`Systems`	Yes	surrogate `id`
`PrincipalActivity`	No — upsert-based; daily sign-in timestamps would generate excessive audit noise
`RiskScores`	No — recalculated on each scoring run

Composite-key tables (migration 018)

Some relationship tables have no surrogate id column — the primary key is a tuple of foreign keys. Migration 018 (April 2026) updated fg_record_history() to synthesise a deterministic a|b|c row key for these tables so their changes land in _history. Before 018, those inserts and deletes were silently skipped, which is why the Recent Changes timeline on v5 installations only backfills from the date that migration ran, not from initial data load.

No-op Filtering¶

The UPDATE trigger uses a WHEN (OLD IS DISTINCT FROM NEW) clause, so re-ingesting unchanged rows during a sync does not generate audit entries. This is critical because the crawler upserts every row on every run.

Query Patterns¶

Full change history for a single entity¶

-- All versions of a principal, newest first
SELECT "changedAt", operation, "rowData", "prevData"
FROM "_history"
WHERE "tableName" = 'Principals'
  AND "rowId" = 'principal-guid-here'
ORDER BY "changedAt" DESC;

Changes within a time range¶

-- All resource assignment changes in the last 30 days
SELECT "rowId", operation, "changedAt", "rowData"
FROM "_history"
WHERE "tableName" = 'ResourceAssignments'
  AND "changedAt" >= now() - interval '30 days'
ORDER BY "changedAt" DESC;

Detecting what changed in an update¶

-- Compare rowData vs prevData to see what fields changed
SELECT
  "changedAt",
  "prevData"->>'department' AS old_department,
  "rowData"->>'department'  AS new_department
FROM "_history"
WHERE "tableName" = 'Principals'
  AND "rowId" = 'principal-guid-here'
  AND operation = 'U'
  AND "prevData"->>'department' IS DISTINCT FROM "rowData"->>'department'
ORDER BY "changedAt" DESC;

Deleted entities¶

-- Resources that were deleted (no longer in the current table)
SELECT "rowId", "changedAt", "rowData"->>'displayName' AS name
FROM "_history"
WHERE "tableName" = 'Resources'
  AND operation = 'D'
ORDER BY "changedAt" DESC;

Point-in-time reconstruction¶

To reconstruct the state of an entity at a specific point in time, find the most recent history row at or before that timestamp:

-- What did this principal look like on January 15, 2026?
SELECT "rowData"
FROM "_history"
WHERE "tableName" = 'Principals'
  AND "rowId" = 'principal-guid-here'
  AND "changedAt" <= '2026-01-15 23:59:59+00'
ORDER BY "changedAt" DESC
LIMIT 1;

How the UI Uses History¶

The entity detail pages (User Detail, Resource Detail, Business Role Detail) query _history to build a Version History section that shows diffs between sync runs. The API endpoint compares consecutive rowData / prevData snapshots and highlights changed fields.

Retention¶

By default, history is retained indefinitely. For environments with high churn, add a periodic cleanup job:

-- Delete history older than 2 years
DELETE FROM "_history"
WHERE "changedAt" < now() - interval '2 years';

The Admin page provides a History Retention setting (Admin > History Retention) to configure automatic cleanup.

Adding History to a New Table¶

To track a new table, create triggers that call the existing fg_record_history function:

-- INSERT and DELETE: always record
CREATE TRIGGER trg_history_ins_del
AFTER INSERT OR DELETE ON "MyNewTable"
FOR EACH ROW EXECUTE FUNCTION fg_record_history();

-- UPDATE: only when something actually changed
CREATE TRIGGER trg_history_upd
AFTER UPDATE ON "MyNewTable"
FOR EACH ROW
WHEN (OLD IS DISTINCT FROM NEW)
EXECUTE FUNCTION fg_record_history();

The trigger function is generic — it works with any table that has an id column.