Identity Mesh LogoIdentity Mesh
Runbooks/Audit Retention

Audit Retention

Operator runbook for the hourly background sweep that deletes old rows from IdentityMesh’s audit and history tables.

Tables on the sweep

TableTimestamp columnPolicy sourceWhy
IM_ObjectAuditChangedOnRetention.AuditDaysIdentity-data change evidence (SOX/SOC2)
IM_AdminAuditWhenUtcRetention.AuditDaysAdmin-action evidence — same compliance bar
IM_GroupAuditEventsWhenUtcRetention.AuditDaysSelf-service group evidence — same compliance bar
IM_RunHistoryStartTimeRetention.RunHistoryDaysSync-run telemetry — operational, not compliance
IM_ConnectorLogsTimestampOperator configPer-run connector log lines — operational
IM_NotificationsCreatedUtcOperator configPortal inbox — not evidence; purged read or unread

The sweep starts 5 minutes after the Sync Engine boots, then runs on a 1-hour cadence. Each cadence pass walks all six tables in order using DELETE TOP (1000) batched loops so a long-overdue purge doesn’t lock the table for the duration.

IM_VaultAuditEvents is not on the sweep. Vault audit rows accumulate indefinitely today; if that matters for a deployment, archive or purge them by hand until the sweep covers them.

Policies

Audit evidence — one window for all three tables

IM_ObjectAudit, IM_AdminAudit and IM_GroupAuditEvents share a single operator-configured window: the Retention.AuditDays system setting, default 30 days. One window means one number to explain to a compliance reviewer, rather than a different answer per table.

A value of 0 or negative is the explicit “keep forever” opt-in — it skips all three tables. An unset, blank or unparseable value falls back to 30, so a typo in the settings box cannot silently stop the purge and let the tables grow unbounded.

The license’s audit-retention figure does not drive this. It is shown on the Operations posture panel and returned by the license endpoint as a statement of what the tier entitles you to, but it is informational. Retention.AuditDays is the only thing the purge reads.

Raising the window is a real trade-off, not a free setting. These tables are hash-chained and grow roughly one row per changed object per import, so a long window inflates them and slows every import and export — each write stamps against the chain head. Deployments that need long history are usually better served by shipping the audit feed to a SIEM (see siem-integration.md) and keeping the in-database window short.

Purging and the hash chain

Ageing by a timestamp removes a prefix — the oldest rows — so every surviving row still points at its surviving predecessor and the chain stays internally verifiable. Nothing in the sweep can delete from the middle, which is the case that would break verification permanently.

Expect one artefact, and do not read it as tampering: a full-history chain verification on a purged deployment reports exactly one broken link at the retention boundary, because the oldest surviving row names a predecessor retention deleted. The bounded verification (the Operations posture’s recent-tail walk) seeds from its own window’s entry point and is unaffected.

Operational logs

IM_RunHistory, IM_ConnectorLogs and IM_Notifications aren’t compliance evidence, they’re operational data.

Run history is set from the Admin Portal, via the Retention.RunHistoryDays system setting (default 90, 0 = keep forever). The appsettings.json key below still applies when no system setting has been stored, so a deployment that tuned it there keeps its window after upgrading. Connector logs and notifications are appsettings.json only:

"IdentityMesh": {
  "Retention": {
    "RunHistoryDays":    90,
    "ConnectorLogDays":  30,
    "NotificationsDays": 90
  }
}

Defaults are 90 / 30 / 90. Any value ≤ 0 disables that specific table’s purge — useful when you want to keep run history forever for trend analysis, or when you’re already shipping connector logs to an external system (Splunk, Sentinel, OTLP) and the in-DB copy is just a triage convenience.

IM_Notifications is the self-service group portal inbox (new in this release): one row per recipient per event, and expiry warnings fan out to every owner at three thresholds, which makes it the fastest-growing table on this sweep. Every row past the window goes, read or unread. An undismissed notice about a group that expired last year is noise, and treating “unread” as “keep” would pin rows forever for exactly the people who never open the bell. It is an inbox, not evidence — the audit trail of what happened to a group is IM_GroupAuditEvents, on the compliance window above.

What you can verify

Did the sweep run?

The sweep writes a log line at INFO level to the Sync Engine log whenever it actually deletes anything:

Audit retention: purged 1234 rows from IM_AdminAudit older than 2026-01-25T00:00:00Z.

Idle sweeps log nothing — the absence of “purged” lines for a few days is a signal that either retention is working steadily (rows fall off as fast as they arrive) or that the schedule isn’t firing (check service health).

How many rows were purged?

The OpenTelemetry meter IdentityMesh.Engine exposes:

identitymesh.audit.purged{table="IM_ObjectAudit"}
identitymesh.audit.purged{table="IM_AdminAudit"}
identitymesh.audit.purged{table="IM_GroupAuditEvents"}
identitymesh.audit.purged{table="IM_RunHistory"}
identitymesh.audit.purged{table="IM_ConnectorLogs"}

A monotonically increasing counter. If you’ve wired the OTLP exporter (Telemetry:Otlp:Enabled = true), this lands in your metrics backend; otherwise it’s still observable via dotnet-counters monitor.

Direct SQL spot-check

-- "Oldest row still in IM_ObjectAudit" — should be ≈ retention window
SELECT MIN([ChangedOn]) AS OldestRow,
       DATEDIFF(DAY, MIN([ChangedOn]), SYSUTCDATETIME()) AS AgeDays
FROM   dbo.IM_ObjectAudit;

-- Same shape, IM_AdminAudit
SELECT MIN([WhenUtc]) AS OldestRow,
       DATEDIFF(DAY, MIN([WhenUtc]), SYSUTCDATETIME()) AS AgeDays
FROM   dbo.IM_AdminAudit;

-- Same shape, self-service group audit
SELECT MIN([WhenUtc]) AS OldestRow,
       DATEDIFF(DAY, MIN([WhenUtc]), SYSUTCDATETIME()) AS AgeDays
FROM   dbo.IM_GroupAuditEvents;

If AgeDays exceeds the policy by more than ~24 hours, the sweep is stuck — investigate.

How to override

Audit tables — the one setting

IM_ObjectAudit, IM_AdminAudit and IM_GroupAuditEvents all move together on Retention.AuditDays. There is no per-table window, by design — one window is one answer for a reviewer.

If you need a one-off “preserve this evidence for an investigation past the normal window,” the right move is to copy the relevant rows to a separate archive table before the window expires:

SELECT *
INTO   dbo.IM_AdminAudit_Archive_2026Q1
FROM   dbo.IM_AdminAudit
WHERE  WhenUtc >= '2026-01-01' AND WhenUtc < '2026-04-01';

The archive table sits outside the sweep’s purview and isn’t governed by Retention.AuditDays. The same shape works for IM_GroupAuditEvents.

Operational tables — config

Set the operator keys in appsettings.json and restart the Sync Engine:

# Bump connector-log retention from 30 to 180 days while
# debugging a multi-month integration.
$cfg = "$env:ProgramFiles\IdentityMesh\engine\appsettings.json"
$json = Get-Content $cfg -Raw | ConvertFrom-Json
$json.IdentityMesh.Retention.ConnectorLogDays = 180
$json | ConvertTo-Json -Depth 10 | Set-Content $cfg -Encoding UTF8

sc stop  IdentityMeshEngine
sc start IdentityMeshEngine

Or, to disable the connector-log sweep entirely (rows accumulate indefinitely — only do this if you’re shipping to an external log system that has its own retention):

"ConnectorLogDays": 0

One-off aggressive purge

Upgrading a deployment with millions of rows already past the window? The hourly sweep only purges 1000 per pass per table, so catching up takes a while. To force a faster catch-up:

-- 100k-row chunks until the table is current. WAITFOR adds
-- breathing room between chunks so the engine + Admin API can
-- still query the table.
DECLARE @cutoff datetime2 = DATEADD(DAY, -90, SYSUTCDATETIME());
WHILE 1 = 1
BEGIN
    DELETE TOP (100000) FROM dbo.IM_ConnectorLogs WHERE [Timestamp] < @cutoff;
    IF @@ROWCOUNT < 100000 BREAK;
    WAITFOR DELAY '00:00:01';
END

What’s NOT swept

Some tables grow but aren’t on the retention schedule, by design: