OfflineKit — append-audit partitioning and its retention windows

Sprint: SPRINT_20260722_025 OK-3, carrying the OfflineKit family’s share of SPRINT_20260830_001 P13-4.

This runbook is named by a refusal. Migration 006_offlinekit_append_audit_partitioning.sql aborts and points here when it finds rows in a table it was about to convert. It also documents the partition runway, which is the operational property this family is most likely to lose quietly.

What 006 did, and to which tables

ADR-039 P13 closes append-audit as “partitioned with a window”, and docs/architecture/retention/README.md makes that machine-checkable: the class is earned only when the table is declared PARTITION BY and a routine drops aged partitions by a name built at run time. Five tables in this family carried the label and none carried the shape.

Four were genuinely immutable and were converted:

The fifth, export_center.export_timeline_notifications, was reclassified rather than partitioned. Its only writer upserts and mutates attempt_count, last_published_at, delivery_state and last_error on every retried publish, and the baseline gives it a BEFORE UPDATE trigger. It is a durable state-machine row, so its class is operational-state. Its bounded-retention obligations are still owed and 006 says so in the table comment rather than claiming a budget that does not exist.

The refusal: “is not empty, so a row-preserving conversion is required”

006 converts by renaming the heap out of the way and creating a partitioned replacement. That is correct only for an empty table, so every conversion asserts emptiness under ACCESS EXCLUSIVE and aborts the whole migration otherwise. Nothing is changed by a refusal — the transaction rolls back and the estate is byte-identical.

If you hit it, the estate has rows this migration was never written to move. Do not edit 006: applied SQL is immutable (ADR-004), and on an estate that already converged, editing it changes a checksum that will never match again.

1. Record what is actually there, per table: SELECT count(*), min(<partition key>), max(<partition key>) FROM <schema>.<table>;
2. Decide whether the rows must survive. These are audit trails, so the default answer is yes.
3. Write a new forward migration that converts with rows, following src/Timeline/__Libraries/StellaOps.Timeline.Persistence.Consolidated/Migrations/000_v1_partition_existing_unified_audit_events.sql: rename the heap, create the partitioned parent, create partitions spanning the observed range, INSERT INTO … SELECT, then check parity both ways with EXCEPT ALL before committing.
4. Do not retain a …_heap_rollback. It is relispartition = false, so it enters this family’s exact table-set assertions (15 export-engine / 5 own-schema) carrying no retention-class: header, and the schema tests will fail on it. Take the parity check as the proof instead.

The partition runway, and the defect that made it a check

A range-partitioned table refuses an insert whose key falls outside every declared partition (23514, no partition of relation … found for row). There is no degraded mode and no prior symptom: writes succeed until the last partition’s upper bound and then stop completely for that table.

This was measured, not theorised. On 2026-09-12 scanner.artifact_boms — at that point the only table in the estate that earned append-audit — held partitions for 2026-07, 2026-08 and 2026-09 only, and nothing in production calls its ensure_artifact_boms_future_partitions routine. Every write to it would have failed from 2026-10-01. That is recorded as an out-of-scope defect against Scanner in SPRINT_20260722_025 (D-OK3-P13-1) and is not fixed by this runbook.

So this family does not inherit the pattern silently:

• 006 seeds 12 months back and 12 months ahead. Backwards matters as much as forwards: these trails record a caller-supplied timestamp, so an event from last month needs a home too.
• doctor.offlinekit.retention.partition-runway reports Warning when any of the four tables has under 90 days of forward runway, and Critical when one is not partitioned at all.
• Extending is one call per table, and it is idempotent:

SELECT export_center.ensure_export_incident_updates_future_partitions(12);
SELECT export_center.ensure_risk_bundle_job_events_future_partitions(12);
SELECT offlinekit.ensure_bundle_version_history_future_partitions(12);
SELECT offlinekit.ensure_bundle_import_part_events_future_partitions(12);

Both routines take (p_months_ahead, p_months_back); the second argument defaults to 0, so the calls above extend forwards only.

Reclaiming aged partitions

Every drop routine takes (p_retain_months, p_dry_run) and returns the partitions it would drop. Always dry-run first — the routine reports what it will reclaim, and on an audit trail that list is worth reading before it executes:

SELECT * FROM offlinekit.drop_bundle_import_part_events_partitions_older_than(24, true);   -- list
SELECT * FROM offlinekit.drop_bundle_import_part_events_partitions_older_than(24, false);  -- reclaim

offlinekit.bundle_import_part_events is the case worth understanding, because a future reader will otherwise reach for the wrong tool. Its append-only trigger is BEFORE UPDATE OR DELETE FOR EACH ROW, so a DELETE … WHERE occurred_at < … retention path would be refused by the estate’s own guard. Dropping a partition is DDL and does not fire a row trigger. Partitioning is therefore not merely the preferred mechanism for that table; it is the only one it can have.

Verifying the class is earned

python tools/scripts/validate/check-p13-retention.py --repo-root . --tables
python tools/scripts/validate/check-p13-retention.py --repo-root . --self-test

Each of the four tables must read shape: partition-parent, class: append-audit, reason: null in docs/architecture/retention/p13-table-inventory.json. To red-proof the window rather than trust it, delete one drop routine, re-run --tables, and confirm that subject falls back to partitioned-without-declared-window; then restore it.

Two limits worth stating when citing a green run. The census is source-only — it does not inspect a live database, so it cannot tell you whether a given estate has actually converged. And the checker’s default mode (without --tables) is red at HEAD with p13.uninventoried-or-stale-source-site, a pre-existing P13-1/P13-2 drift recorded 2026-09-04 in SPRINT_20260830_001; it is unrelated to this work.