Object audit rows are hard-deleted after a hardcoded 30 days, breaking retention evidence and the hash chain

[rubenvdlinde]

Summary

Object audit rows are hard-deleted 30 days after they are written, by a hardcoded expiry plus an hourly purge cron. For a product whose purpose is multi-year legal retention, the audit trail silently self-destructs after a month.

This surfaced while investigating "330 of 348 procest cases are soft-deleted but only ~19 have a delete audit row". The trail is not lossy at write time and the deletions did not bypass it — the rows were written and then purged.

Positive control (run first)

SELECT action, count(*) FROM oc_openregister_audit_trails GROUP BY action → delete = 1972 rows, 23 distinct actions. Scoped: WHERE action='delete' GROUP BY register, schema returns 25+ non-empty groups including register=17, schema=92 → 17 rows — the exact scope under investigation. The query shape demonstrably finds real rows.

The numbers

procest cases = register 17, schema 92, table oc_openregister_table_17_92.

Metric	Count
Total case rows	348
Soft-deleted (_deleted IS NOT NULL)	330
Soft-deleted with a delete audit row	19

Split by the 30-day boundary:

deleted within last 30d	soft-deleted	has delete audit
no	311	0
yes	19	19

100% coverage inside the retention window, 0% outside. A clean date boundary, not a code path.

Cause

lib/Db/AuditTrailMapper.php:573

// Set default expiration date (30 days from now).
$auditTrail->setExpires(new DateTime('+30 days'));

Unconditional and hardcoded, applied to every object audit row (create, update, delete, read). It ignores the object's own _retention, the retention_period column on the same row, and setExpiryDate(int $retentionMs) at :1686 — the obviously-intended configurable path, which is dead relative to this line.

lib/Db/AuditTrailMapper.php:1600-1613 — clearLogs() issues DELETE FROM openregister_audit_trails WHERE expires IS NOT NULL AND expires < NOW(). Hard delete, no archive, no tombstone.

lib/Cron/LogCleanUpTask.php:99 — runs clearLogs() hourly (setInterval(3600)), registered at appinfo/info.xml:109.

Corroborating evidence

1. date_part('day', expires - created) is 30 for every one of the 258,240 rows with an expiry — a uniform hardcoded window.
2. min(expires) = today → nothing older than 30 days survives.
3. For rows created before the cutoff: 1035 with expires IS NULL, 0 with a non-NULL expires. The only pre-cutoff survivors are action types that never get an expiry (rbac.admin_bypass, folder_access_denied, docudesk.signing.*, graph-run, run, schema.cascade_delete, approve/deny) — written by paths that never call buildAuditTrail(). Zero expiring rows older than the cutoff exist anywhere.
4. Sequence forensics: max(id)-min(id)+1 - count(*) = 114,648 missing ids, min(id)=31518 (not 1), an 80,286-id hole between 2026-06-28 and 2026-06-30, dense gap-free ids from 2026-07-03 on. Rows were physically deleted.
5. oc_jobs: LogCleanUpTask last ran 2026-08-02 10:25:15 UTC, one hour before the earliest surviving expires.

Ruled out

• Different action name — enumerated all 23 distinct values. No deleted/destroy/softDelete.
• Bulk path skipping the audit writer — refuted by data: 19/19 deletions inside the window have audit rows, including 2 referential_integrity.root_delete cascades.
• Different register/schema/table — register 17/schema 92 has 574 audit rows across read/create/update/delete; the legacy monolith oc_openregister_objects is empty.
• Join-key mismatch — the same object_uuid join returns 437 read + 65 update + 15 create rows for these objects.
• Fixture deletion bypassing audit — the June objects were fixture churn (same-day create/delete by admin), but their create rows are equally missing. Creates and deletes vanished together, symmetric with the cutoff.

Why this is serious

1. Retention. procest exists for Archiefwet/selectielijst terms of 5–20+ years. A 30-day audit trail cannot evidence a retention decision made a year earlier.
2. Tamper-evidence. The table carries hash/previous_hash (AuditHashService). Deleting rows mid-chain breaks the chain, so a purge and a tampering event become indistinguishable.

Not fixed here — deliberately

The cause is contained (one line for the expiry, one for the purge), but choosing the replacement policy is not an engineering call: "never expire" grows an already-258k-row table unboundedly, and any finite default re-creates the same class of defect at a different horizon. It needs a decision on (a) configurable retention per register/schema, (b) honouring the object's own _retention, and (c) archive-vs-delete given the hash chain.

Inferred, not proved: that the June rows were purged rather than never written. Deleted rows cannot be recovered to demonstrate they existed. The alternative requires a writer that silently skipped all of register 17 until exactly the 30-day cutoff then worked perfectly — while buildAuditTrail() has no register-conditional logic and creates match object creations 1:1 in the retained window. The purge explanation is the only one consistent with the id-sequence gaps.