Metadata-Version: 2.4
Name: fastpluggy-db-purge
Version: 1.0.4
Summary: Centralized database retention management and purge plugin for FastPluggy
License-Expression: MIT
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: FastPluggy>=0.4.0
Requires-Dist: sqlalchemy>=2.0.0
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Requires-Dist: pytest-cov>=4.0; extra == "dev"
Provides-Extra: tests
Requires-Dist: pytest>=7.0; extra == "tests"
Requires-Dist: pytest-cov>=4.0; extra == "tests"
Requires-Dist: fastpluggy-tasks-worker; extra == "tests"
Requires-Dist: psycopg2-binary>=2.9; extra == "tests"
Provides-Extra: e2e
Requires-Dist: fastpluggy-cli; extra == "e2e"
Requires-Dist: playwright>=1.40.0; extra == "e2e"

# fastpluggy-db-purge

![DB Purge](https://img.shields.io/badge/FastPluggy-DB%20Purge-blue)
[![Release](https://gitlab.ggcorp.fr/open/fastpluggy/plugins/db_purge/-/badges/release.svg)](https://gitlab.ggcorp.fr/open/fastpluggy/plugins/db_purge/-/releases)
[![Pipeline Status](https://gitlab.ggcorp.fr/open/fastpluggy/plugins/db_purge/badges/main/pipeline.svg?key_text=CI)](https://gitlab.ggcorp.fr/open/fastpluggy/plugins/db_purge/-/pipelines?ignore_skipped=true)
[![Coverage](https://gitlab.ggcorp.fr/open/fastpluggy/plugins/db_purge/badges/main/coverage.svg)](https://gitlab.ggcorp.fr/open/fastpluggy/plugins/db_purge/-/pipelines)

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

```bash
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()`:

```python
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:

```python
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`:

```sql
-- 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](https://gitlab.ggcorp.fr/open/fastpluggy/plugins/db_purge/-/work_items/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](https://gitlab.ggcorp.fr/open/fastpluggy/plugins/db_purge/-/work_items/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:
```sql
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.
