Deleting everything older than yesterday is not a safe upload-cleanup strategy. A long transfer may still be active, a database update may lag behind storage, and a crashed process may leave a file that no row currently describes.
This walkthrough designs a cleanup state machine for MySQL 8.4 InnoDB and private immutable storage. It is an integration design with SQL excerpts, not a tested deletion script. Start in report-only mode against synthetic data; do not aim an experimental sweeper at a production storage root.
Model lifecycle, not just age
Give upload sessions explicit states such as receiving, assembling, complete, cancelled, deleting and deleted. Keep expiration, lease expiry and a monotonically increasing lease generation on the session. File/chunk records should carry immutable storage keys, not user-controlled paths.
A writer acquires a bounded lease before accepting bytes. When it renews or finalizes, it must prove that the session still permits writing and that its lease generation remains current. A cleanup claim invalidates the old generation. A process that wakes after a long pause cannot then publish a late chunk into a deleted session.
A timestamp without fencing is insufficient: an old writer may still finish after its lease expires. Give each attempt a new staging key and require a valid generation to publish it. Late unreferenced staging files become reconcilable orphans rather than resurrected data.
Claim a small batch
Use a short transaction to select eligible rows and change their state to deleting. The following is a schematic claim; production columns, enum definitions and indexes must match your application:
START TRANSACTION;
SELECT id
FROM upload_sessions
WHERE state IN ('receiving', 'cancelled')
AND expires_at < UTC_TIMESTAMP(6)
AND lease_until < UTC_TIMESTAMP(6)
ORDER BY expires_at, id
LIMIT 50
FOR UPDATE SKIP LOCKED;
-- Bind only the IDs returned above:
-- UPDATE upload_sessions
-- SET state = 'deleting', lease_generation = lease_generation + 1
-- WHERE id IN (...);
COMMIT;Make lease_until non-null or define how null is handled. Add an index supporting the eligibility scan and inspect its query plan. MySQL documents SKIP LOCKED as a way to skip already-locked rows; it is useful for work distribution, not a guarantee that a query can never deadlock. MySQL locking reads.
Do not keep the transaction open while deleting storage. Record a deletion job or durable claim in the same transaction, then perform external work afterward.
Make deletion retryable
For each deleting session, resolve only its recorded immutable storage keys. Treat an already-missing object as success. A permission error, timeout or unavailable store is a retryable failure with a bounded backoff and operational alert.
Mark the session deleted only after every required object has been removed or deliberately retained under a documented policy. If the worker crashes after removal but before recording success, the next attempt should safely observe missing objects and finish.
A claim must be recoverable after worker death. Include worker lease expiry or make all deleting sessions eligible for idempotent reprocessing. Otherwise the first crash leaves permanent stranded rows.
Reconcile without guessing
Periodically compare sufficiently old storage objects against active manifests and deletion jobs. Use a generous grace period so freshly written staging files are not mistaken for abandoned data.
For filesystem storage, never follow symlinks during a sweep and never construct recursive-delete targets from user filenames. Prefer moving a specifically identified orphan into a quarantine area before eventual removal. Keep a restricted report of internal IDs and actions, without secret link tokens.
Database deletion is not secure erasure. Snapshots, backups, object versioning and replicas may retain content according to separate retention policies. State those limits honestly to users.
Prove the races in a fixture
Test an upload renewing its lease while cleanup scans, a stale writer finishing after the cleanup claim, a storage timeout after partial removal, two cleanup workers and a crash before the final status update. Assert that active-generation writes survive and expired-generation writes cannot publish.
These database/storage integration tests were not run here. A safe rollout begins with reports, then a small synthetic batch, then metrics for queue age and failed removals. Unexpectedly growing deleting rows usually indicate failed retries or unrecoverable worker claims, not a need for more aggressive deletion.
Continue with resumable upload sessions, or discuss lifecycle design for a Next.js client portal.