Supabase content moderation: Edge Functions, Storage and RLS

create table public.posts (
  id bigint generated always as identity primary key,
  author_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  body text not null check (char_length(body) between 1 and 2000),
  status text not null default 'pending'
    check (status in ('pending', 'approved', 'review', 'rejected')),
  request_id bigint,  -- the Detectivision request, once queued
  moderation jsonb,   -- flags or error from the check
  created_at timestamptz not null default now()
);

create table public.photos (
  id bigint generated always as identity primary key,
  owner_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  path text not null unique,  -- object name in the private "photos" bucket
  status text not null default 'pending'
    check (status in ('pending', 'approved', 'review', 'rejected')),
  request_id bigint,
  moderation jsonb,
  created_at timestamptz not null default now()
);

alter table public.posts enable row level security;
alter table public.photos enable row level security;

-- Clients send content, never a status: they may insert these columns and update nothing.
revoke all on table public.posts, public.photos from anon, authenticated;
grant select on table public.posts, public.photos to authenticated;
grant insert (body) on table public.posts to authenticated;
grant insert (path) on table public.photos to authenticated;
grant select, update on table public.posts, public.photos to service_role;

create policy "Approved posts, and your own" on public.posts
  for select to authenticated
  using (status = 'approved' or author_id = (select auth.uid()));
create policy "Post as yourself" on public.posts
  for insert to authenticated
  with check (author_id = (select auth.uid()));

create policy "Approved photos, and your own" on public.photos
  for select to authenticated
  using (status = 'approved' or owner_id = (select auth.uid()));
create policy "Photos from your own folder" on public.photos
  for insert to authenticated
  with check (owner_id = (select auth.uid()) and split_part(path, '/', 1) = (select auth.uid())::text);

insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values ('photos', 'photos', false, 10485760,
  array['image/jpeg', 'image/png', 'image/webp', 'image/gif', 'image/bmp']);

create policy "Upload into your own folder" on storage.objects
  for insert to authenticated
  with check (bucket_id = 'photos' and (storage.foldername(name))[1] = (select auth.uid())::text);

create policy "Your own photos, and approved ones" on storage.objects
  for select to authenticated
  using (
    bucket_id = 'photos'
    and (
      owner_id = (select auth.uid())::text
      or exists (select 1 from public.photos p where p.path = objects.name and p.status = 'approved')
    )
  );

// A post and a photo. Both start as pending.
await supabase.from("posts").insert({ body });

const path = `${user.id}/${crypto.randomUUID()}`;
const { error } = await supabase.storage.from("photos").upload(path, file, { contentType: file.type });
if (!error) await supabase.from("photos").insert({ path });

// Reading needs no status filter: row-level security returns approved rows plus your own.
const { data: posts } = await supabase.from("posts").select("id, body, status").order("created_at", { ascending: false });
const { data: signed } = await supabase.storage.from("photos").createSignedUrl(path, 3600);

[functions.moderate]
verify_jwt = false

[functions.moderation-result]
verify_jwt = false

import { withSupabase } from "npm:@supabase/server@^1";

const API_URL = "https://api.detectivision.ai/api/v1/moderation";
// Longer than an async check can wait: one that cannot run within about a day fails.
const SIGNED_URL_SECONDS = 25 * 60 * 60;

export default {
  fetch: withSupabase({ auth: "secret:moderation" }, async (req, ctx) => {
    const { table, record } = await req.json();
    if (table !== "posts" && table !== "photos") return Response.json({ skipped: true });
    const db = ctx.supabaseAdmin;

    // The payload says which row; the database says what is in it.
    const { data: row } = await db.from(table).select("*").eq("id", record.id).single();
    if (!row || row.status !== "pending" || row.request_id) return Response.json({ skipped: true });

    let request;
    if (table === "posts") {
      request = { type: "text_moderation", input: row.body };
    } else {
      const { data: signed, error } = await db.storage
        .from("photos")
        .createSignedUrl(row.path, SIGNED_URL_SECONDS);
      if (error) {
        await db.from(table).update({ status: "review", moderation: { error: error.message } }).eq("id", row.id);
        return Response.json({ review: true });
      }
      request = { type: "image_moderation", input: signed.signedUrl };
    }

    const res = await fetch(API_URL, {
      method: "POST",
      headers: {
        "Api-Key": Deno.env.get("DETECTIVISION_API_KEY")!,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ ...request, mode: "detect", async: true, metadata: { table, id: row.id } }),
    });
    const answer = await res.json();
    if (res.status === 202) {
      await db.from(table).update({ request_id: answer.id }).eq("id", row.id);
    } else if (res.status !== 429 && res.status < 500) {
      // The input cannot be checked, for example an empty text: a person decides.
      await db.from(table).update({ status: "review", moderation: { error: answer.error } }).eq("id", row.id);
    }
    // 429 and 5xx leave the row pending with no request_id; the sweeper sends it again.
    return Response.json({ status: res.status });
  }),
};

-- Once, with your values: the project URL and the secret key named "moderation".
select vault.create_secret('https://<project-ref>.supabase.co', 'project_url');
select vault.create_secret('sb_secret_...', 'moderation_key');

create schema if not exists private;

create function private.send_for_moderation(tbl text, row_id bigint)
returns bigint
language sql
security definer
set search_path = ''
as $$
  select net.http_post(
    url := (select decrypted_secret from vault.decrypted_secrets where name = 'project_url')
      || '/functions/v1/moderate',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'apikey', (select decrypted_secret from vault.decrypted_secrets where name = 'moderation_key')
    ),
    body := jsonb_build_object('type', 'INSERT', 'table', tbl, 'record', jsonb_build_object('id', row_id)),
    timeout_milliseconds := 10000
  );
$$;

create function private.on_new_row()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  perform private.send_for_moderation(tg_table_name, new.id);
  return new;
end;
$$;

create trigger send_for_moderation after insert on public.posts
  for each row execute function private.on_new_row();
create trigger send_for_moderation after insert on public.photos
  for each row execute function private.on_new_row();

select cron.schedule('moderation-sweeper', '*/10 * * * *', $$
  select private.send_for_moderation(t.tbl, t.id)
  from (
    select 'posts' as tbl, id from public.posts
    where status = 'pending' and request_id is null and created_at < now() - interval '10 minutes'
    union all
    select 'photos', id from public.photos
    where status = 'pending' and request_id is null and created_at < now() - interval '10 minutes'
    limit 100
  ) t
$$);

import { withSupabase } from "npm:@supabase/server@^1";
import { Webhook } from "npm:standardwebhooks@^1";

const webhook = new Webhook(Deno.env.get("DETECTIVISION_WEBHOOK_SECRET")!);
// Your own line. The API raises a flag at 0.5 or above; from here up, reject on its own.
const REJECT_AT = 0.7;

type Flag = { flag: string; confidence: number };

export default {
  fetch: withSupabase({ auth: "none" }, async (req, ctx) => {
    const body = await req.text();
    let event;
    try {
      event = webhook.verify(body, Object.fromEntries(req.headers)) as {
        type: string;
        data: {
          id: number;
          status?: "completed" | "partial";
          flagged?: boolean;
          flags?: Flag[];
          error?: string;
          metadata?: { table?: string; id?: number };
        };
      };
    } catch {
      return new Response("bad signature", { status: 401 });
    }

    const { type, data } = event;
    const table = data.metadata?.table;
    const decides = type === "moderation.completed" || type === "moderation.failed";
    if (!decides || (table !== "posts" && table !== "photos")) return new Response(null, { status: 204 });

    let status;
    if (type === "moderation.failed" || data.status === "partial") status = "review";
    else if (!data.flagged) status = "approved";
    else status = data.flags!.some((f) => f.confidence >= REJECT_AT) ? "rejected" : "review";

    // Only a row that is still pending changes, so a repeated delivery does nothing.
    const { data: changed, error } = await ctx.supabaseAdmin
      .from(table)
      .update({ status, moderation: { flags: data.flags ?? [], error: data.error ?? null, requestId: data.id } })
      .eq("id", data.metadata!.id)
      .eq("status", "pending")
      .select();
    if (error) return new Response("try again", { status: 500 });

    if (table === "photos" && status === "rejected" && changed.length > 0) {
      // Remove the file; its signed URLs stop working with it.
      await ctx.supabaseAdmin.storage.from("photos").remove([changed[0].path]);
    }
    return new Response(null, { status: 204 });
  }),
};

{
  "type": "moderation.completed",
  "timestamp": "2026-10-16T09:41:07.512Z",
  "data": {
    "id": 1043,
    "type": "image_moderation",
    "mode": "detect",
    "status": "completed",
    "flagged": true,
    "flags": [{ "flag": "sexual", "confidence": 0.93 }],
    "checked": ["hate", "scam", "sexual", "violence"],
    "unchecked": [],
    "tookMs": 412,
    "usage": { "charged": 1, "remaining": 4210 },
    "metadata": { "table": "photos", "id": 42 },
    "source": "api"
  }
}