# Database retention – Coaster World

The Live synchronizer produces high-volume operational history. Version 1.106.0 bounds that growth without removing current Live state or the 28-day wait-time forecast window.

## Default retention

- `wait_time_snapshots`: non-forecastable rows (closed/down/unknown or no wait value) after 6 hours; usable wait samples after 30 days.
- unchanged usable wait snapshots: one heartbeat every 30 minutes instead of every 15 minutes; changes are still recorded immediately.
- successful `live_sync_runs`: 3 days.
- failed `live_sync_runs`: 30 days.
- `live_events`: 14 days.
- processed `domain_events` in category `visit_live`: 14 days.

These defaults can be overridden with `CW_DB_*` environment variables defined in `config/database_retention.php`.

## Safe cleanup

Always create a current database backup first.

Preview only (no deletion):

```bash
php artisan cw:database:prune
```

Initial cleanup of all eligible rows, in 5,000-row transactions:

```bash
php artisan cw:database:prune --force --max-rows=0
```

The scheduler then runs bounded maintenance every day at 05:40. The default scheduled limit is 250,000 rows per retention rule and can be changed with `CW_DB_PRUNE_SCHEDULED_MAX_ROWS`.

## Physical disk size after cleanup

InnoDB normally reuses space freed by deleted rows but may not immediately shrink the `.ibd` file. If disk space must be returned immediately, make a fresh backup and run the following during a maintenance window because MySQL may rebuild/lock the table and require temporary free disk space:

```sql
OPTIMIZE TABLE `wait_time_snapshots`;
OPTIMIZE TABLE `live_sync_runs`;
OPTIMIZE TABLE `live_events`;
OPTIMIZE TABLE `domain_events`;
```

Do not run `OPTIMIZE TABLE` automatically from the scheduler.