fastpluggy-db-purge

Centralized database retention management and purge plugin for FastPluggy.

Features

• Auto-discover all database tables with row counts and sizes
• Per-table retention policies via admin UI
• Plugin-declared purge targets via fp_purge_targets hook — declaring a table upserts a disabled rule an admin enables to validate (see below)
• Manual and scheduled purge with batched deletes
• Full audit trail of purge operations

Installation

pip install fastpluggy-db-purge

Configuration

Setting	Default	Description
default_retention_days	30	Global fallback retention
default_timestamp_column	updated_at	Global fallback timestamp column
batch_size	5000	Rows per DELETE batch
auto_purge_enabled	False	Enable scheduled purge
auto_purge_cron	0 4 * * *	Cron schedule (daily 4am UTC)

Declaring purge targets from another plugin

A plugin can declare its own tables as purgeable from its on_load_complete():

from fastpluggy_plugin.db_purge.hooks import register_purge_target

register_purge_target("task_reports", "end_time", default_retention_days=30)

This does two things:

1. Registers the table + declared defaults in the in-memory registry, so it shows on the DB Purge dashboard (marked declared by plugin).
2. Upserts a persistent rule in fp_purge_rules, created disabled. An admin must enable it in the web UI before anything is deleted — a validation gate, so declaring a table can never silently purge its data.

If the plugin later changes what it declares (timestamp column or retention), the rule is refreshed and re-disabled for the admin to re-validate. Provenance is tracked by a declared_hash column: plugin-managed rows carry the hash; admin-created rows have declared_hash = NULL and are never touched by the hook.

To exclude a config/system table from purge entirely:

from fastpluggy_plugin.db_purge.hooks import register_purge_excluded

register_purge_excluded("my_plugin_config")

Observability

Audit Trail

All purge runs (manual, scheduled, and dry-run) are logged to fp_purge_logs:

-- Recent purges
SELECT table_name, rows_deleted, duration_ms, status, executed_at, executed_by
FROM fp_purge_logs
ORDER BY executed_at DESC LIMIT 20;

-- Failed purges
SELECT table_name, error_message, executed_at
FROM fp_purge_logs
WHERE status = 'error'
ORDER BY executed_at DESC;

-- Stalled purges (running > 1 hour)
SELECT table_name, executed_at, executed_by
FROM fp_purge_logs
WHERE status = 'running' AND executed_at < NOW() - INTERVAL '1 hour';

Metrics

No Prometheus metrics are exposed yet. Use the audit trail table for monitoring.

Logging

Purge lifecycle events are logged at INFO level:

[db_purge] Declared purge target (disabled, awaiting admin): task_reports
Purge task_reports: deleted 5000 rows (total: 5000)
db_purge_all: task_reports — deleted 25000 rows (success)

Errors are logged at ERROR with full tracebacks.

Failure Modes

DB statement timeout during purge

Symptom: Purge fails with statement_timeout error

Cause: Large table (>1TB) with slow DELETE or COUNT(*)

Recovery:

• COUNT(*) is skipped automatically above 1GB (since v1.0.1)
• Reduce batch_size setting to smaller chunks (default 5000)
• Increase DB statement_timeout if appropriate for your workload

Hard crash mid-purge (OOM, kill -9)

Symptom: fp_purge_logs row stuck in status='running' forever

Impact: Partial rows deleted; exact count visible in rows_deleted field (updated per batch commit)

Recovery:

1. Check rows_deleted to see progress
2. Manually update the row to status='error' or delete it
3. Re-run the purge (safe to retry — it's an idempotent DELETE WHERE ts < cutoff)

Known issue: #3 tracks automatic stale-running reaper

Scheduled purge keeps running after toggle OFF

Symptom: Auto-purge disabled in UI but cron still executes

Impact: P1 data loss if retention policy was changed before disabling

Recovery: Manually disable the task in the tasks_worker scheduled tasks table

Known issue: #4 tracks the fix

Purging table from uninstalled plugin

Risk: Enabled purge rule for a table whose owning plugin was uninstalled

Prevention: No automatic guard — admin must disable purge rules before uninstalling plugins

Recovery: If data was incorrectly purged, restore from DB backup

Upgrade Notes

v1.0.0 → v1.0.1+

Schema migration: declared_hash column added automatically on first load (idempotent ALTER TABLE ADD COLUMN)

Rollback: If downgrading to <1.0.0, drop the column:

ALTER TABLE fp_purge_rules DROP COLUMN declared_hash;

v0.x → v1.0.0

Breaking: Plugin-declared rules now re-disable on declaration change. Previously-enabled rules may need re-validation after upgrade.