Outbox Retention & Pruning¶

This document covers operational disposal of outbox rows after the in-process dispatcher has delivered them. Reliable-delivery mechanics live in conventions.md § Transactional Outbox. For the dispatch loop itself, see outbox-dispatcher.md and src/shared/outbox/poller.py.

Why prune¶

The outbox table is append-only. Every command-handler write inserts a row; the OutboxPoller (in src/shared/outbox/poller.py) marks each row completed (or failed) with a processed_at timestamp once dispatch finishes. Without a cleanup job the table grows unboundedly: rows are conceptually disposable once delivered (the completed status + processed_at on the committed row is the durable record), but Postgres doesn't know that, and the partial index ix_outbox_unprocessed only narrows the poller's claim query — it doesn't keep the table from bloating.

Pruning is the recovery: it deletes completed rows that are old enough we won't need them for audit / replay / debugging.

What gets deleted¶

Only rows where both predicates hold:

• status = 'completed'
• processed_at < now() - retention_days

Concretely:

A status='failed' row is something a human needs to look at. The pruner deliberately leaves them alone — they accumulate under whatever retry / quarantine policy a future ticket lands. If failed rows ever need their own retention, that's a separate decision, not a flag on this CLI.

How to run¶

python -m src.shared.outbox.prune [--retention-days N] [--batch-size N]

Flags:

A successful run emits one structured log line:

outbox_pruned total=<N> retention_days=<N> cutoff=<ISO timestamp>

total=0 means nothing was eligible (which is the expected steady-state once the cron is running regularly).

DB role & RLS¶

The CLI opens its sessions via get_jobs_session_factory() (in src/shared/db/session.py), which reads JOBS_DB_URL (falling back to DATABASE_URL when unset).

The outbox table has ENABLE + FORCE ROW LEVEL SECURITY with a per-tenant tenant_isolation policy. The pruner's DELETE is cross-tenant by design — it scans rows belonging to every tenant in a single pass. For that to actually match rows, the connection must bypass RLS.

In production, JOBS_DB_URL points at the grinsystem_jobs role provisioned by GRI-150: LOGIN + BYPASSRLS, no DDL, DML-only on public. See GRI-150's "Roles to create" table for the full privilege model and the rationale for keeping grinsystem_jobs separate from both grinsystem_app (which must not bypass RLS) and grinsystem_migrations (which has DDL the pruner doesn't need).

In dev / test, JOBS_DB_URL is unset and falls back to DATABASE_URL (the bootstrap superuser), which also bypasses RLS — so the CLI works locally without extra wiring.

Idempotency & safety¶

• Idempotent. Re-running deletes zero additional rows. The cron can fire on overlapping schedules without harm.
• Batched. Each DELETE is LIMIT batch_size (default 1000) wrapped in its own transaction. Locks are held for one batch at a time, so the script never blocks an in-flight commit-handler write for more than ~milliseconds.
• Interruptible. A SIGINT between batches leaves the table in a consistent state — the rows from previous batches stay deleted, the rest stay. Re-running picks up where the previous run left off.
• Cutoff frozen at start. The retention boundary is computed once before the loop, so rows that became "old" partway through the run aren't pruned in the same invocation. The next scheduled run picks them up.

Coolify scheduled-task config¶

Coolify supports scheduled tasks attached to an app resource. The recommended cadence:

Only wire this on uat / prod after M3 brings the dev environment online (per GRI-112). Local development runs the command ad-hoc when the table needs trimming.

Troubleshooting¶