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 & selection rules

Cleanup behavior is defined by each 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\SuccessMutationJobsCleanup::class,
        App\Services\Cleanup\FailedMutationJobsCleanup::class,
        App\Services\Cleanup\ActivityLogsCleanup::class,
        App\Services\Cleanup\MutationsCleanup::class,
    ],
];

Each cleanup class exposes a set of conditions that determine what to delete. Conditions are applied to a base query as closures. If a class returns no conditions, the fallback is `created_at < now() - cutOffDays` using the class’ `$cutOffDays` value.

Current cleanup tasks and rules:

• SuccessMutationJobsCleanup — delete `mutation_jobs` where `status = 'success'`.
• FailedMutationJobsCleanup — delete `mutation_jobs` where `status = 'failed'` AND `last_attempt_at < now() - 10 days`.
• ActivityLogsCleanup — delete `activity_logs` where `created_at < now() - 5 days`.
• MutationsCleanup — delete `mutations` that have no related jobs (orphans): `where not exists (select 1 from mutation_jobs where mutation_uuid = mutations.uuid)`.

You can change retention by adjusting the specific class’ conditions or, if using the fallback behavior, by changing its `$cutOffDays` property.

Cleanup command

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

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

How it works internally:

• `php artisan log:clean` loads tasks from `config/cleanup.php` and executes each one.
• Each task extends `AbstractCleanupTask` and provides `getConditions(): array` closures that mutate the query builder.
• Dry‑run (`--dry-run`) uses the same filtered query to count rows without deleting.
• Deletions occur in batches by primary key (default 20,000) to reduce lock contention.

Scheduling

In production, the cleanup runs via Ofelia (Docker job runner) configured in `compose.production.yml`. It runs daily 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)`.

- Controllers and dashboards 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.