Audit Retention

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

Tables on the sweep

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

Policies

License-driven (compliance evidence)

The license carries an AuditRetentionDays value per tier. Both IM_ObjectAudit and IM_AdminAudit are purged at exactly that window — that’s the contract the license describes (“we’ll retain your audit evidence for N days at your tier”), and the engine enforces it without operator override.

A license with AuditRetentionDays = -1 (or IsUnlimitedAuditRetention = true) skips the audit-table sweep entirely. Compliance teams frequently want this, and the licence tier is the right place to ask for it — talk to sales.

Operator config (operational logs)

IM_RunHistory and IM_ConnectorLogs aren’t compliance evidence, they’re operational data. Operators set the window via two appsettings.json keys:

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

Defaults are 90 / 30. 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.

What you can verify

Did the sweep run?

The sweep emits structured Serilog events at INFO level 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_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;

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

How to override

Compliance tables — only via license

IM_ObjectAudit and IM_AdminAudit retention is pinned to the license. Do not edit the engine to widen these in code; the license-issued value is the contractual commitment. Customers needing longer retention should be moved to a higher tier or to the Enterprise unlimited tier.

If a customer needs 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 the license retention.

Operational tables — config

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

# Bump connector-log retention from 30 to 180 days for a customer
# debugging a multi-month integration.
$cfg = "$env:ProgramFiles\IdentityMesh.Service\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

Adding new tables with millions of legacy rows? 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:

• IM_MeshObjects and IM_MeshObjectAttributes — these are the identity data itself; deletion happens via connector semantics, not retention.
• IM_Connectors, IM_JoinRules, IM_AttributeFlowRules, IM_ProjectionRules, IM_ComposerRules (and friends) — configuration, not history. Operators delete via the Admin API.
• IM_Secrets — connector credentials. Lifecycle tied to the parent connector; rotation per secret-rotation.md.
• IM_ExportQueue — queue items mark Complete/Failed and stay; we don’t purge until item completion-age policy is defined. Track on the readiness backlog if it becomes a problem.
• IM_ExportedAttributeSnapshots — current state per (mesh-object, connector); deletion happens when the connector or mesh object is removed.

Related

• secret-rotation.md — companion runbook for the other recurring operator chore.