Cleaning up failed or partial uploads

A Storage upload either completes or fails on its own. The mess usually comes from the step around it: the file uploaded but your app's database row wasn't written, or the row was written but the upload failed. Both leave records that don't match, and orphaned files keep counting toward your storage quota.

What you see

• Files in a bucket that no row in your tables points to.

• Rows that point to files that don't exist, so the app shows broken images or 404s on download:

  {"statusCode":"404","error":"not_found","message":"Object not found"}
  

Why it happens

An upload and the insert into your own table are two separate requests. Anything between them can fail: the network drops, the user closes the tab, an RLS policy rejects the insert, or the function doing it times out. Nothing rolls the first step back automatically.

How to fix it

1. Upload first, then write the row, and undo the upload if the row fails:

   async function attachFile(invoiceId, file) {
     const path = `${invoiceId}/${crypto.randomUUID()}-${file.name}`
   
     const { error: upErr } = await supabase.storage.from('attachments').upload(path, file)
     if (upErr) throw upErr
   
     const { error: dbErr } = await supabase
       .from('invoice_attachments')
       .insert({ invoice_id: invoiceId, path, size_bytes: file.size })
   
     if (dbErr) {
       await supabase.storage.from('attachments').remove([path]) // best effort
       throw dbErr
     }
     return path
   }
   

   Unique names (a UUID in the path) mean a retry never overwrites another upload.

2. Find orphaned files that are more than a day old, so uploads still in progress aren't caught:

   select o.name
   from storage.objects o
   left join public.invoice_attachments a on a.path = o.name
   where o.bucket_id = 'attachments'
     and a.id is null
     and o.created_at < now() - interval '1 day';
   

3. Find rows whose file is missing:

   select a.id, a.path
   from public.invoice_attachments a
   left join storage.objects o on o.bucket_id = 'attachments' and o.name = a.path
   where o.id is null;
   

4. Delete the orphans through the Storage API. Storage doesn't allow deleting rows from storage.objects directly with SQL, because the file on disk would stay behind. First wrap the query from step 2 in a function that only service_role can run:

   create or replace function public.orphaned_attachments()
   returns table (name text)
   language sql stable
   set search_path = ''
   as $$
     select o.name
     from storage.objects o
     left join public.invoice_attachments a on a.path = o.name
     where o.bucket_id = 'attachments'
       and a.id is null
       and o.created_at < now() - interval '1 day';
   $$;
   
   revoke execute on function public.orphaned_attachments() from public, anon, authenticated;
   grant execute on function public.orphaned_attachments() to service_role;
   

   Then remove the files from a server with the service_role key:

   import { createClient } from '@supabase/supabase-js'
   
   const admin = createClient(process.env.SUPABASE_URL, process.env.SUPABASE_SERVICE_ROLE_KEY, {
     auth: { persistSession: false, autoRefreshToken: false },
   })
   
   const { data: rows, error } = await admin.rpc('orphaned_attachments')
   if (error) throw error
   
   const paths = rows.map((r) => r.name)
   for (let i = 0; i < paths.length; i += 100) {
     const { error: rmErr } = await admin.storage.from('attachments').remove(paths.slice(i, i + 100))
     if (rmErr) throw rmErr
   }
   console.log(`Removed ${paths.length} orphaned files`)
   

5. Run it on a schedule. Deploy the cleanup as an Edge Function and call it from a pg_cron job once a night.

On SupaHost

• The S3 protocol isn't offered on SupaHost, so there are no S3 multipart uploads to abort.
• The "Package files" export on the Backups tab adds a missing.txt to the archive when some storage.objects rows have no file on disk. That's a quick way to spot the second problem across a whole project.
• Edge Functions get SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY automatically. To run the script there, read them with Deno.env.get() instead of process.env and import the client with npm:@supabase/supabase-js@2.

Related

• Uploading files
• Running functions on a schedule
• Unexpectedly high storage usage
• Folder operations and hierarchical policies