40000x · 03 — Migration 035: Auto-instantiate log retention policy

Goal

Bring the productized audit log (iu_auto_instantiate_event_log, migration 033) under the existing append-only retention substrate (migration 025).

The macro authority pack explicitly prohibits enabling retention (iu_core.retention_enabled=true); registration is dry-run-only. This migration only adds one row to iu_core_retention_policy. No data is deleted. The gate stays false.

What 035 adds

target_table	age_column	keep_days	actor_scope	reason
iu_auto_instantiate_event_log	created_at	180	NULL	40000x: keep 180 days of productized auto-instantiate audit; actor-scoped cleanup is served by fn_iu_auto_instantiate_rollback_by_actor from migration 033.

Idempotent — ON CONFLICT (target_table) DO NOTHING. D9 surface unchanged.

Why actor_scope=NULL (and not actor-scoped)

fn_iu_core_retention_cleanup (migration 025) hard-codes the actor column name as the literal string actor:

v_actor_clause := format(' AND actor = ANY(%L::text[])', pol.actor_scope);

But iu_auto_instantiate_event_log (migration 033) names its actor column triggered_by. If we registered the policy with actor_scope <> NULL, the cleanup function would build SQL referencing a non-existent actor column and the dry-run itself would fail at parse time.

Two options:

1. (taken in 40000x) Register age-only (actor_scope=NULL). Actor-scoped pruning is already served by fn_iu_auto_instantiate_rollback_by_actor (now governed by dot_iu_auto_instantiate_rollback_by_actor per mig 034).
2. (carry-forward) Extend iu_core_retention_policy with actor_column text DEFAULT 'actor', teach the fn to read it, then re-register with actor_column='triggered_by'.

Option (1) is the minimal, no-substrate-change move; option (2) is the future expansion.

Live apply transcript (verbatim, irrelevant lines elided)

$ docker exec postgres psql -U directus -d directus -v ON_ERROR_STOP=on \
    -f /tmp/035_auto_instantiate_log_retention_policy.sql
BEGIN
DO          -- preflight OK
INSERT 0 1
-- 035 verification: policy row + retention gate stays false
   check    | policy_rows | expected_4
------------+-------------+------------
 035_policy |           4 | t

    check    |         target_table          | age_column | keep_days | actor_scope
-------------+-------------------------------+------------+-----------+-------------
 035_new_row | iu_auto_instantiate_event_log | created_at |       180 |

-- 035 gate-stays-closed proof
  check   | retention_enabled
----------+-------------------
 035_gate | false

-- 035 dry-run sanity (cleanup must not delete; gate is false)
   check    |         target_table          | rows_eligible | rows_deleted | dry_run
------------+-------------------------------+---------------+--------------+---------
 035_dryrun | iu_auto_instantiate_event_log |             0 |            0 | t

COMMIT

rows_eligible=0 is expected: all 13 existing log rows from 25000x were created on 2026-05-25 and the 180-day cutoff is 2025-11-26.

Rollback proof (live)

BEGIN
DELETE 1
    check     | still_have | total_policy
--------------+------------+--------------
 035_rollback |          0 |            3

       check       | retention_enabled
-------------------+-------------------
 035_rollback_gate | false
COMMIT

Then re-apply (3 → 4) succeeded cleanly.

Authority boundary — what 035 did NOT do

• Did not enable retention (iu_core.retention_enabled stays false).
• Did not delete a single row anywhere (dry-run only).
• Did not alter fn_iu_core_retention_cleanup.
• Did not touch iu_auto_instantiate_event_log rows.

Future enablement checklist (NOT this macro)

1. Decide the keep_days envelope (180 days now is a conservative default).
2. Open the gate: UPDATE dot_config SET value='true' WHERE key='iu_core.retention_enabled';
3. Drive a fn_iu_core_retention_cleanup(actor, FALSE) dry-run-then-live cycle.
4. Close the gate again at the end of the maintenance window.
5. Schedule a cron driver — explicitly out-of-scope here.