Compact contiguous file-purged segments into a single bridging row

Summary

File-purged audit_trail_segment rows accumulate indefinitely. Each row is small (~1 KB) but on a long-running install with hourly granularity the table grows linearly with chain age. The rows can't be deleted today -- they carry anchor_before / anchor_after hashes the verifier uses to bridge across the now-empty id range -- but consecutive file-purged segments on the same chain can be COMPACTED into a single bridging row without breaking verification.

Proposed: a new ChainArchiver::compactFilePurgedSegments(string $chain_id, int $cutoff_id): int method, plus an opt-in cron pass that compacts past a configurable retention.

Motivation

Hygiene only. The bookkeeping rows are small (~1 KB each), carry no PII (just anchor hashes + ids + counts), and with proper retention + indexing the per-chain segment scans stay bounded even for very large tables. Nothing's broken today and nothing's projected to break. This is just a "clean lifecycle should end with a way to compact the bookkeeping rather than just stop touching it" feature -- the existing transient-purge / archive / live-purge / file-purge chain ends with a row that lives forever; compaction closes that asymmetry.

Existing deleteSegment() explicitly refuses any segment with any lifecycle flag set (src/Archive/ChainArchiver.php:1757), with a docblock that explains why file-purged rows are load-bearing for the verifier's gap-bridge. Compaction is the design-correct way to reduce the row count without breaking that invariant: replace N consecutive file-purged segments with a single equivalent bridging row.

Why deletion breaks the chain (recap)

The verifier walks rows by id. When it sees a gap, findArchiveBridging() looks for a segment row covering the gap; the segment's anchor_before must match the running expected_previous, and its anchor_after becomes the new expected_previous after the bridge. Without the segment row, the verifier has no source for the hash that should sit between the last pre-gap row and the first post-gap row.

Segments on the same chain often chain to each other: segment N+1's anchor_before equals segment N's anchor_after. Deleting segment N breaks segment N+1's chain back to genesis.

Why compaction is safe

If segments S1=[1, 100], S2=[101, 200], S3=[201, 300] are all file-purged on the same chain, they can be replaced with a single row S' = [1, 300] whose:

• anchor_before = S1.anchor_before (the original genesis anchor).
• anchor_after = S3.anchor_after (the last covered row's hash).
• identity_hmac + archive_hmac + lifecycle_hmac re-signed under the current secret against the consolidated record.
• file_purged_at = max of the originals; file_purged_event_id = the most recent.
• row_count = sum of originals; ack_count = sum.
• file_sha256 = empty (the original files are gone; the consolidated row attests to a range, not a file).

The verifier walks the chain, hits the gap [1, 300], finds S', bridges via anchor_before -> anchor_after. Identical to the pre-compaction behavior, fewer rows in the table.

Implementation sketch

1. New public method ChainArchiver::compactFilePurgedSegments(string $chain_id, int $cutoff_id): array.
   • Acquires the chain-write lock via ChainWriteLock::acquireWithDeadline().
   • Selects all segments on $chain_id with file_purged_at != 0 AND to_id <= $cutoff_id, ordered by from_id.
   • Walks the result, accumulating CONTIGUOUS runs (segment N+1's from_id == segment N's to_id + 1, OR segment N's anchor_after matches segment N+1's anchor_before when there's an id gap caused by other-chain rows).
   • For each run of 2+ segments, INSERTs the consolidated row + DELETEs the originals in one transaction.
   • Returns the list of (new_segment_id, deleted_segment_ids) pairs for the audit trail.
2. Emit a segment_compacted chain event per run, payload referencing the consolidated range and the deleted segment ids. Signed normally.
3. Optional cron pass CronArchiveHook::compactionPass() using a new config knob compact_after (default disabled / NULL; opt-in only). When set, runs after the existing file-purge pass and compacts segments past now - compact_after.
4. Operator interface: a drush command audit_trail:compact with explicit chain + cutoff arguments. Admin form deferred to a follow-up.

Verifier impact

None of the verifier rules change. findArchiveBridging() already accepts any segment whose anchors satisfy the bridge predicate; a wider segment that bridges the same gap is functionally identical. The segment-event cross-reference check (verifySegmentEventCrossReference) operates per-event, not per-segment, so consolidating segments doesn't affect it -- the original segment_archived / segment_live_purged / segment_file_purged events on the chain reference segment ids that no longer exist after compaction, but those events are emitted before this MR's compaction runs.

The segment_* events that reference deleted segments are a real concern: the verifier's cross-reference check looks up the segment by id and would find it missing. Two resolutions:

• (a) Add compacted_into_segment_id on the chain-event payload of the consolidated segment_compacted event, and extend the cross-reference rule to follow that pointer when the original segment is gone. Self-contained, no other rule changes.
• (b) Treat the original segment_* events as superseded by segment_compacted the same way the existing live-purge supersession rule handles a later segment_restored.

Option (a) is the cleaner pattern (explicit pointer rather than re-using the supersession rule with new semantics).

Tests to add

• Happy path: 3 file-purged segments + post-cutoff rows. Run compactFilePurgedSegments. Assert: 1 consolidated row exists, 3 originals are gone, verifyChain() returns ok.
• Edge: only one file-purged segment in the range. No-op (compaction requires 2+ contiguous).
• Edge: file-purged segments interleaved with non-file-purged ones (e.g., one still in live-purged state). Compaction must NOT cross the boundary.
• Cross-chain id-space interleaving (per the testing convention #3591949): the consolidated range spans ids belonging to other chains. Confirm the consolidated row's anchors are correct.
• Concurrency: chain-write lock acquired around the read-modify-INSERT-DELETE so a concurrent createBareSegment / acknowledgeChainReset can't slip a bisecting row into the consolidated range.
• Verifier acceptance of the compacted_into_segment_id pointer (per resolution (a)).

Open questions

• Should we keep a separate audit_trail_compacted_segment table (or similar) as a forensic record of which segments were consolidated when, OR is the segment_compacted chain event sufficient? Probably the latter -- the chain event already gives operators a queryable history.
• Default value for compact_after: opt-in (NULL = no compaction) seems right. A long default (P30D? P90D?) would be opinionated for a feature that mostly matters on multi-year installs.
• Compaction granularity: per-run (2+ contiguous segments) vs per-bucket (e.g., one consolidated per month). Per-run is simpler and matches the existing coverage-pass bucket-aligned shape if we want.