Cleaning up orphaned uploads before they become the bill

Your storage bill is 40 GB. Your database references maybe 9 GB of files. The difference has been accumulating since launch, nobody noticed because storage is cheap at first, and now nobody knows which objects are safe to delete.

Orphaned objects are the default outcome of a direct-to-storage upload flow. If you do nothing, you get them.

Where they come from

Abandoned uploads. The browser gets a signed URL, PUTs the file, and then the tab closes before the server action that records the key runs. The object exists; nothing points at it. This is the biggest source, and it is entirely normal user behaviour: people do close tabs.

Replaced files. A user changes their avatar four times. Unless the old key is deleted when the new one is saved, you now store four avatars and render one.

Deleted rows. The message with an attachment is deleted; the attachment is not. Cascading deletes in Postgres do not reach into a bucket.

Failed transactions. The object uploads, the row insert fails on a constraint, the request returns an error. The bytes are still there.

Multipart leftovers. A large upload that never completed leaves its parts stored and billed, invisible in an object listing.

Stop making new ones first

Cleanup is worth nothing if the leak is still open.

Delete on replace. Every save path that overwrites a key deletes the old object:

const previous = await db.user.findUnique({ where: { id: user.id }, select: { avatarKey: true } });
await db.user.update({ where: { id: user.id }, data: { avatarKey: key } });

if (previous?.avatarKey && previous.avatarKey !== key) {
  await deleteObject(previous.avatarKey).catch(() => undefined);
}

Delete on delete. Object first, then the row. An orphaned object costs storage; an orphaned row renders a broken image, which the user sees.

await deleteObject(attachment.key);
await db.attachment.delete({ where: { id: attachment.id } });

Record the intent before the upload. The strongest fix: insert a row with status pending at the moment you sign, and flip it to ready when the client confirms. Now every object has a row from the first second, and cleanup is a query rather than a diff:

const key = objectKey({ ownerId: uploader.id, filename, contentType, prefix });
await db.upload.create({ data: { key, ownerId: uploader.id, status: "pending" } });
const signed = await getSignedUploadUrl(key, contentType);

-- anything still pending an hour later was abandoned
select key from uploads where status = 'pending' and created_at < now() - interval '1 hour';

This costs one insert per signing request and removes the entire class of problem. If you are building an upload flow today, build this one.

The sweeper, for what already exists

Listing a bucket and comparing against your database is the retrofit. Two directions to check, and one of them is dangerous.

// scripts/storage/find-orphans.ts
import { createClient } from "@supabase/supabase-js";

const supabase = createClient(process.env.NEXT_PUBLIC_SUPABASE_URL!, process.env.SUPABASE_SERVICE_ROLE_KEY!);
const bucket = process.env.SUPABASE_STORAGE_BUCKET!;

async function* listAll(prefix = ""): AsyncGenerator<{ name: string; created_at: string }> {
  const pageSize = 100;
  for (let offset = 0; ; offset += pageSize) {
    const { data, error } = await supabase.storage
      .from(bucket)
      .list(prefix, { limit: pageSize, offset, sortBy: { column: "name", order: "asc" } });

    if (error) throw error;
    if (!data || data.length === 0) return;

    for (const entry of data) {
      // A "folder" has no id; recurse into it.
      if (!entry.id) yield* listAll(prefix ? `${prefix}/${entry.name}` : entry.name);
      else yield { name: prefix ? `${prefix}/${entry.name}` : entry.name, created_at: entry.created_at };
    }

    if (data.length < pageSize) return;
  }
}

const referenced = new Set(await db.upload.findMany({ select: { key: true } }).then((r) => r.map((x) => x.key)));
const cutoff = Date.now() - 24 * 60 * 60 * 1000;

for await (const object of listAll()) {
  if (referenced.has(object.name)) continue;
  if (new Date(object.created_at).getTime() > cutoff) continue; // in-flight upload
  console.log(object.name);
}

Three rules for running it:

• Age cutoff, always. An object created two minutes ago is probably an upload in progress whose row does not exist yet. Twenty-four hours is a safe floor.
• Report before you delete. Run it in dry-run for a week. Read the list. The first run always finds something you did not expect, and half the time it is a key your code references in a way your query missed.
• One direction only. Deleting objects with no row is safe once you trust the query. Deleting rows with no object is not: a listing failure or a paging bug would delete user data. Report those and look at them by hand.

Once you trust it, delete in batches and log every key you removed. Keep the log: "why is this file gone" is a question that arrives weeks later.

Lifecycle rules, where they fit

Supabase Storage has no S3-style lifecycle rules, so scheduled cleanup is your job: a Postgres cron job calling a function, a scheduled task in your host, or a job runner in the app.

What you can do declaratively is bound the damage: file_size_limit and allowed_mime_types on the bucket stop one abandoned upload being a 5 GB abandoned upload.

Knowing the size of the problem

-- rough per-prefix usage, straight from storage.objects
select
  split_part(name, '/', 1) as owner,
  count(*) as objects,
  pg_size_pretty(sum((metadata->>'size')::bigint)) as size
from storage.objects
where bucket_id = 'uploads'
group by 1
order by sum((metadata->>'size')::bigint) desc
limit 20;

Compare the total against the sum of what your own tables reference. The gap is your orphan estimate, and watching it every month is how you notice a new leak in the week it appears rather than the year it becomes expensive.