backend-auto-cleanup-worker

Use pg_cron to schedule a net.http_post call to the cleanup-worker edge function, for example at 03:15 nightly. The cron SQL includes the function URL and an Authorization header with an internal worker secret, never the service role key.

Delete the storage object first via storage.from(bucket).remove(paths), then delete the referencing database row. Verify the object's first path segment matches a known tenant_id to prevent cross-tenant deletion.

No. Every delete query must exclude rows tied to active customers, jobs, leads, projects, or financial records via explicit JOIN or NOT EXISTS guards. Tables like tenants, users, invoices, and signed estimates are never touched.

Stale AI jobs are marked as failed, not deleted. The worker calls normalizeResultStateForWrite() to set result_state to ai_failed_unknown and records hard_fail_reason as cleanup_worker_timeout.

New tasks ship with dry_run=true so deletions are only counted and logged, not executed. Promotion to live deletes requires a separate change after at least one dry-run summary is stored in cleanup_worker_runs.

Deletes use DELETE ... WHERE id IN (SELECT id ... LIMIT N) with a default batch limit of 5000 rows. Long tasks like orphaned storage and old logs can split work across multiple nightly runs to stay under the 5-minute budget.