Auditing retention, pruning, and compliance

A retention policy must identify the evidence class, owning tenant, cutoff, and approval for an irreversible operation. The current contract carries tenant and logical scope into storage; it no longer lets one category’s schedule prune every table.

1. Defaults and legal floors

0 means permanent retention. A positive tenant-policy value keeps the most recent N days and may not be below its floor.

The tenant policy caps values at 365,000 days and uses ExpectedVersion for optimistic concurrency. A tenant without an explicit row receives defaults.

2. Tenant policy API

All three bind to the authenticated owner’s tenant, reject platform tenant 0, and do not switch to a target tenant during operate-as.

One authorization gap needs explicit governance: the save command currently uses auditing.audit.view, like read and preview. A principal able to view audit data may therefore mutate retention policy. Restrict that permission to audit administrators until the mutation is split onto Update/Manage.

3. Impact-preview accuracy

Preview counts rows before each proposed cutoff through the same IAuditQueryPort. It is useful before save, but it is neither approval nor a guaranteed execution result.

The source iterates the seven AuditCategory values while HTTP Request and business Activity share Activity:

• RequestRetentionDays does not produce a separate estimate row;
• Activity TotalCount can include both HTTP and business activity;
• preview does not first run the complete policy-validity check, so an invalid proposal may still produce an estimate;
• concurrent writes or pruning change the actual count after preview.

The UI should label the result as a point-in-time estimate and display Request duration separately. Do not describe seven rows as an exact eight-class deletion plan.

4. Scheduled execution

The default Cron is 0 3 * * *. JobHost uses Quartz; an explicitly enabled API fallback uses the same executor through the common Cron calculator. Audit:Retention:Enabled=false skips scheduled pruning.

The executor processes HttpRequest, Activity, EntityChange, Security, Exception, ExternalRequest, and Communication. For each tenant it resolves explicit policy or global values, enters persistence under a trusted RootCrossTenantOperation, and records intent and result. A prune failure does not prevent later tenant/scope attempts, but the job eventually fails with the first exception retained.

If policy-store lookup itself returns Failure, the current implementation skips that tenant without increasing the final failure count. This is an observability gap: monitor policy-store errors and do not treat job Success alone as proof that every tenant was processed.

5. Scope mapping

Communication combines two evidence classes. When tenant policy assigns different CAP and BackgroundJob durations, the executor uses the longer value. This prevents premature deletion of the longer-lived class, at the cost of retaining the shorter-lived one longer.

6. Bounded physical deletion

SqlSugar and Mongo physically delete in 1,000-ID batches, with at most 10,000 batches per table/collection—ten million rows in one execution. Reaching that limit throws; a later run continues from remaining rows instead of issuing an unbounded DeleteMany.

SqlSugar narrows old split tables, then applies TenantId, Scope, and CreateTime < cutoff to rows. Mongo applies tenant_id, category/HTTP shape, and occurred_at < cutoff, selects a stable _id batch, and deletes those IDs.

Deletion is not soft deletion and has no automatic undo. Confirm backup policy, legal holds, and impact first. Large tenants also need lock-wait, transaction-log, replica-lag, and job-timeout monitoring.

7. Manual pruning

Manual pruning requires operations.audit.delete on an admin-sensitive resource:

• POST /api/operations/audit/prune with beforeDate and approvalTicket in JSON;
• DELETE /api/audit/retention/{beforeDate}?ApprovalTicket=... as the compatibility route over the same command.

The handler rejects dates before the SQL Server 1753 boundary, future dates, noncanonical tickets or tickets over 64 characters, and a caller that cannot form a stable actor. In Production, ISensitiveOperationApprovalPort must validate the ticket; other environments follow their composition-root approval policy.

Before deletion, the handler records PruneAudit:<cutoff>:started. If that intent cannot enter the audit boundary, it does not call the retention port. Success records total deleted rows; failure records the exception type and preserves the original failure.

8. Compliance deletion procedure

Treat Production pruning as a numbered change rather than an ordinary API call:

1. freeze tenant, scope, cutoff, purpose, and requester;
2. preview rows, time range, split tables, and capacity impact;
3. verify legal floors, contract retention, legal hold, litigation preservation, and backup health;
4. approve with Incident, dual review, expiry, and execution window;
5. record intent, then run bounded deletion;
6. verify deleted count, oldest remaining evidence, failed batches, and replication;
7. persist ticket, command, result, and verifier in a longer-lived record that this command cannot delete.

The general Activity proof remains inside the prunable audit store and cannot be the sole proof of deletion. Strict regulation needs an independent Ledger/WORM destination.

9. Retention acceptance contract

Preview and save accept the same input. This example keeps HTTP and outbound requests for 180 days, exceptions for 365 days, and business activity, entity changes, and security evidence indefinitely:

{
  "requestRetentionDays": 180,
  "activityRetentionDays": 0,
  "entityChangeRetentionDays": 0,
  "securityRetentionDays": 0,
  "exceptionRetentionDays": 365,
  "externalRequestRetentionDays": 180,
  "capConsumerRetentionDays": 90,
  "backgroundJobRetentionDays": 90,
  "expectedVersion": 0
}

Preserve the preview response, then replace expectedVersion with the latest value returned by GET before the approved PUT. Never increment a conflicting version blindly:

# Preview the exact input first; save it only after approval.
curl -fsS -H "Authorization: Bearer <TOKEN>" -H "Content-Type: application/json" \
  --data @retention-policy.json https://<HOST>/api/audit/retention-policy/preview
curl -fsS -X PUT -H "Authorization: Bearer <TOKEN>" -H "Content-Type: application/json" \
  --data @retention-policy.json https://<HOST>/api/audit/retention-policy

• each scope touches only its physical source; strict Security/Exception and HTTP/Activity separation;
• permanent classes remain unchanged under every other scope/cutoff;
• two tenants, platform tenant, operate-as, and directory enumeration;
• explicit/default policy, optimistic concurrency, legal floors, and 365,000 cap;
• independent Request execution and the known preview aggregation boundary;
• longer-of CAP/Job Communication duration;
• month/year split boundaries, timezone, leap day, equal time, and 1753 floor;
• 1,000-ID batches, ten-million limit, cancellation, partial failure, and continuation on the next run;
• Production POST/DELETE ticket validation, expired ticket, and unavailable approval service;
• zero deletion after intent-audit failure plus combined prune/result-audit failure;
• policy-store Failure is observable rather than silently omitting a tenant.

Previous: Storage and query · Next: Testing and operations