Expiring objects (Storage)

[j-mendez]

When uploading a file to s3 you have the option to add lifecycles to expire the object after a set period. This API should be exposed to the storage side of supabase. This feature is critical to preventing extra crons or events to clear storage.

[GaryAustin1]

This is not an answer from Supabase, but how would setting s3 to delete an object also then delete the storage.objects info? Seems there would still need to be cron task or event to delete that from the database side. Then you have to asynchronous activities going on though. To sync them you are back to cron/event to delete both storage.objects row and s3 object.

[j-mendez]

Hi @GaryAustin1 , great question. We would need to setup a notification. The notification event would perform the cleanup of the metadata. It could be safer to handle this from the supabase side first.

[pierroo]

I don't mean to hijack the post but I was also looking to create a cron to delete all files in storage that "expired" (or that are older than xx hours/days for example)
did you manage to do it @j-mendez ?

[GaryAustin1]

This should give you some ideas. https://github.com/GaryAustin1/supa-file-helper
you would need to change the trigger function to search on time.

[j-mendez]

No worries, not yet. The productivity would be nice if this was built in, current waiting to see if maybe we can take action on this feature.

[j-mendez]

Thanks @GaryAustin1 will take a look.

[greenstick]

Just adding to the chorus – the ability to set an expiration using an ISO string or millisecond time would be really great when uploading objects to Storage. Something like:

const avatarFile = event.target.files[0]
const { data, error } = await supabase
  .storage
  .from('avatars')
  .upload('public/avatar1.png', avatarFile, {
    cacheControl: '3600',
    upsert: false,
    // For an expiration, for example, using an ISO string
    expiration: '2025-11-09T06:50:50.003Z', 
    // As a PostgreSQL timestamptz
    expiration: '2025-11-09 06:50:50.00398+00', 
    // Or in UTC time 
    expiration: 1762671050003, 
    // Or as a future time (created_at + expires_in) in milliseconds
    expires_in: 30 * 24 * 60 * 60 * 1000,
  })

[greenstick]

In lieu of the Supabase Client handling expiration, I've taken an approach to use an expires_at field in storage.objects.user_metadata:

Creating the object:

const avatarFile = event.target.files[0]
const { data, error } = await supabase
  .storage
  .from('avatars')
  .upload('public/avatar1.png', avatarFile, {
    cacheControl: '3600',
    upsert: false,
    metadata: {
      expires_at: new Date()).toISOString(),
    }
  })

The plpgsql function:

--
-- Delete Expired Supabase Storage Objects
--

create or replace function api.delete_expired_storage_objects()
  returns void
  language plpgsql
  security definer
as $$

  declare
    rows_deleted integer;

  begin

    raise notice 'Storage Expiration Check: Starting storage object expiry check (%)', now();

    -- perform the deletion
    delete from storage.objects
    where
      bucket_id is not null
      and (user_metadata ->> 'expires_at') is not null
      and (user_metadata ->> 'expires_at')::timestamptz <= now();

    get diagnostics rows_deleted = row_count;
    raise notice 'Storage Expiration Check: % storage objects deleted.', rows_deleted;

    return;

  end;
$$;

-- Set Permissions - api.delete_expired_storage_objects
grant execute on function api.delete_expired_storage_objects() to postgres;

Create a cronjob using pg_cron:

--
-- Delete Expired Storage Objects Each Night
--

select cron.unschedule('daily-delete-expired-storage-objects'); -- For idempotency when re-running
select cron.schedule(
  'daily-delete-expired-storage-objects',
  '0 10 * * *', -- 10am UTC/2am PST
  'select api.delete_expired_storage_objects();'
);

Optionally, if you have many objects to expire, you may want to figure out a way to create an index on the storage.objects table. Something like this, if you have the permissions:

create index if not exists idx_objects_expires_on on storage.objects (
    ((user_metadata ->> 'expires_at')::timestamptz)
) where (user_metadata ->> 'expires_at') is not null;