A Supabase job table for Sume webhooks: upsert on job_id

Make job_id the primary key of a table and insert with ON CONFLICT DO NOTHING, so a repeated Sume delivery changes nothing. Sume's docs tell receivers to use job_id as the idempotency key, and a Postgres primary key is the simplest way to enforce that exactly once.

The Supabase changelog lists a stable @supabase/middleware 1.0.0, an OrioleDB public beta, and Postgres 15.19 and 17.11 releases fixing 44 CVEs. None of that changes the pattern; it only means the database underneath it is worth keeping patched.

What the changelog lists

Entries from the Sep 25 to Oct 1 window.

Why a table at all

Sume sends a terminal event for each job in webhook mode: job.completed, job.failed or job.canceled. It retries on network errors and non-2xx responses, up to 10 attempts with a fixed spacing of 30 seconds by default, and each attempt times out after 10 seconds. A slow handler therefore receives the same event repeatedly.

You can also request a redelivery with POST /v1/jobs/{job_id}/webhook/redeliver, which re-sends the real terminal event with a fresh timestamp and signature. Redelivery does not consume one of the automatic attempts, so your receiver sees the same job_id again by design.

The schema

Keep the raw payload so you can reprocess an event, and add a column that records when work on it finished. The primary key does the deduplication.

create table sume_job_events (
  job_id text primary key,
  event text not null,
  status text not null,
  payload jsonb not null,
  received_at timestamptz not null default now(),
  processed_at timestamptz
);

insert into sume_job_events (job_id, event, status, payload)
values ($1, $2, $3, $4)
on conflict (job_id) do nothing
returning job_id;

Read the result of the insert

The returning clause is the signal. If a row comes back, this is the first delivery and you should do the work. If nothing comes back, the event was already stored, and you answer 2xx and stop. This keeps the receiver fast and safe to retry.

The order of steps

The order matters more than the framework.

• Read the raw body and verify the sume-v1 signature first. A bad signature gets a non-2xx and no database write.
• Insert into the table. Handle the conflict case as success.
• Answer 2xx once the row is stored, before slow work such as copying media or notifying people.
• Do the slow work from a separate worker that selects rows where processed_at is null, and set it when finished.

Failed and canceled events

Failed and canceled webhooks use status: "ERROR" and include an error object. Store them in the same table; a job id reaches one terminal state, and the table should record it. Do not resubmit the paid create from the receiver. Decide on a new job separately, with a new idempotency key.

Keep a poll fallback

A failed delivery after ten attempts leaves a job that still reached its terminal state. Run a small periodic query for jobs you submitted that have no row after a deadline, and read GET /v1/jobs/{id}/status for each. The table makes that query easy, because you can store the job id at submit time in a second table and compare.