Maintenance and Data Retention

This project stores operational data (mutations, mutation jobs, activity logs) for observability and retries. To keep databases lean and queries fast, a built‑in cleaner removes old rows on a schedule.

Retention settings

Retention is defined by each cleanup processor (class) so logic stays close to the data it manages. The list of processors to run lives in `config/cleanup.php` as a simple class list:

return [
    'tasks' => [
        App\Services\Cleanup\MutationsCleanup::class,
        App\Services\Cleanup\ActivityLogsCleanup::class,
    ],
];

• MutationsCleanup: retains the last N days of rows (default currently 15 days).
• ActivityLogsCleanup: retains the last N days of rows (default currently 7 days).

To change retention, update the corresponding processor class (e.g., the `$cutOffDays` value). If you want environment overrides, you can read from `env(...)` in those classes.

Cleanup command

• Command: `php artisan log:clean`
• Options:

- `--dry-run` — prints how many rows would be removed without deleting.

What it does:

• Deletes mutations older than the cutoff (related `mutation_jobs` are removed automatically via FK cascade).
• Deletes `activity_logs` older than the cutoff.

Deletes happen in chunks (1000 rows) to avoid large locks.

Scheduling

In production, the cleanup runs via Ofelia (Docker job runner) configured in `compose.production.yml`. It runs daily at 00:00 and appends output to `storage/logs/cron.log` inside the app container.

Compose labels on the `app` service:

ofelia.job-exec.cleanup-logs.schedule: "0 0 * * *"
ofelia.job-exec.cleanup-logs.command: "sh -lc 'doppler run -- php artisan log:clean >> storage/logs/cron.log 2>&1'"

Adjust the cron expression as needed per environment.

Query performance improvements

To mitigate timeouts (e.g., filtering mutations by SKU/identifier), the following were added:

• Mutations table:

- Generated columns with indexes for JSON payload keys:

- `payload_identifier` (from `payload->identifier`)

- `payload_code` (from `payload->code`)

- Indexes on `created_at` and `(entity_type, created_at)`.

- Controller updated to prefer these columns for exact identifier lookups.

• Mutation jobs:

- Indexes on `(mutation_uuid, status)`, `(integration_uuid, status)`, and `updated_at` for faster whereHas filters and dashboards.

• Entity mappings:

- Composite index on `(integration_uuid, mappable_type, mappable_id)` for counts/drilldowns.

• Activity logs:

- Existing indexes on `(entity_type, entity_uuid)`, `integration_uuid`, and `(entity_type, entity_identifier)` are used by filters.

After deploy, run migrations and consider analyzing the tables for fresh stats:

php artisan migrate
mysql> ANALYZE TABLE mutations, mutation_jobs, entity_mappings, activity_logs;

If identifiers are always filtered by exact value, prefer the dedicated `entity_identifier` or `payload_identifier` inputs instead of broad free‑text `q` searches to leverage indexes.