TL;DR:

-- Drop all tables in a PostgreSQL schema (here is 'public' schema)
-- REF: https://supabase.com/docs/guides/database/postgres/dropping-all-tables-in-schema
do $$ declare
    r record;
begin
    for r in (select tablename from pg_tables where schemaname = 'public') loop
        execute 'drop table if exists ' || quote_ident(r.tablename) || ' cascade';
    end loop;
end $$;