Keeping your video library in sync with Supabase Postgres

Most applications ask the video API for metadata when they need it, and for one screen of one creator's uploads that is the right call. The trouble starts at the first join, when a course page needs each lesson's duration beside enrolment rows in your own Postgres, and no video API can join against a table it cannot see. So you fetch, hold the list in memory and match it in code, which turns sorting a thousand videos by duration into paginating someone else's API.

We built the FastPix Supabase integration to sync video metadata to Postgres, so that work goes back into SQL. The cost we took on is keeping the copy current. Here is the schema our sync creates, the path an event takes to a row, the setup order that catches everybody, and the one gap neither repair command closes.

Postgres can join, the API can't

A video API answers questions about videos, while your product asks about videos joined to everything else you store, a different question against a different database.

Every cell in that middle column is code you write, test and maintain, and it puts somebody else's network on your render path, so the slowest response you get becomes the slowest page you serve. Rate limits say the same from the other side: a budget spent rendering lists is one you cannot spend on uploads.

The five tables the sync creates

npx @fastpix/supabase init writes three migrations into supabase/migrations and creates four edge functions in supabase/functions. You never write the DDL, because npx supabase db push applies the queue and cron migrations while npx @fastpix/supabase migrate creates the tables in a fastpix schema. The video never moves: FastPix holds the media and the schema holds what describes it.

We match column names to the FastPix API, so they are camelCase and need double quotes in SQL: write select "mediaId" from fastpix.media, because Postgres folds an unquoted identifier to lowercase and select mediaId fails. The columns we document are the debugging ones, "mediaId" on media plus "type", "processStatus", "lastError" and "receivedAt" on webhook_events. We do not publish the full list, so read it off the migration files after init rather than from anything written about it, this article included.

Two of these tables are not safe to hand to a browser, because live_streams holds streamKey, srtSecret, srtPlaybackStreamId and srtPlaybackSecret, plus more keys inside simulcastResponses, any of which lets whoever has them stream into or pull from your account, while webhook_events carries the same values in raw payloads. Until you enable row-level security the fastpix tables have none, and row-level security for video works through the per-table policy and the credential column a row policy cannot hide.

How video metadata reaches Postgres

FastPix webhook  ->  fastpix-webhook (edge function)
                 ->  pgmq queue
                 ->  cron job, every 10 seconds
                 ->  fastpix-worker (edge function)
                 ->  re-fetch the resource from the FastPix API
                 ->  write fastpix.media / live_streams / uploads

The worker never writes what the event said, and that is the decision we argued about longest. It treats the event only as a signal that a resource changed, re-fetches it by ID and writes the response, so replaying the same event twice produces the same row. Duplicates turn up more often than people expect, and idempotence is cheaper to build than deduplication is to debug.

Each box on that path has decisions inside it, and we split them across the cluster on purpose: pgmq as a webhook queue takes the queue, pg_cron takes the timer, and Supabase Realtime pushes each row to whoever is watching that upload.

Why the receiver queues the work

A provider sending a webhook holds the connection open and waits, and most allow a few seconds before marking the delivery failed and retrying, so a receiver that re-fetches, writes two tables and only then answers is racing that timer on every event. Lose the race and a duplicate lands while the first attempt is still running.

fastpix-webhook does three cheap things instead, verifying the signature over the raw bytes, writing the event into fastpix.webhook_events and pushing it onto the queue, then answering well inside the window while the worker takes as long as the re-fetch needs.

Writing the event down before queuing also separates two failures that look identical from outside: rows sitting at received mean nothing drained the queue, while no rows mean the delivery never arrived or the signature failed. The pgmq article carries the triage query that reads those states apart.

Why we disable the JWT check

The Supabase gateway checks every edge function request for a valid project JWT and returns 401 without one, and FastPix has no JWT to send, nor should it, because it authenticates the other way round by signing the request body with a secret you both hold.

So init sets verify_jwt to false in config.toml, on the webhook function and nothing else, and the endpoint does not become open: authentication moves from the gateway into the function, which computes the signature over the raw body and rejects anything that does not match.

Two details cost people an afternoon here, and we have watched both happen. The signing secret is base64 and the engine decodes it before verifying, so a re-typed or truncated value fails quietly, with no rows, no error and nothing in the log naming the cause. The verify_jwt setting is read when the stack starts, so skipping the restart after init leaves the gateway still checking JWTs while a perfect function answers 401 to everything. Our integration guide puts the restart immediately after init for that reason.

Getting a real webhook to localhost

Your local stack answers on localhost:54321, which FastPix cannot reach, so put a tunnel in front of it, which is what ngrok is doing in our requirements list. Point the dashboard webhook at the tunnel's URL plus /functions/v1/fastpix-webhook, and since we check that URL before saving it, curl it first and confirm ok comes back.

IMAGE SLOT local-webhook-401-then-200 Type: Real screenshot, native aspect (about 16:10) Shows: A terminal running supabase functions serve, two deliveries logged, the earlier answering 401 and the later 2xx, the restart command between them, and the tunnel's request inspector beside it. Why here: The claim that a correct function answers 401 until it restarts is only believable with both responses in one log. Status: Needs capture from a live local stack. Placeholder until then.

The environment file has its own trap, because supabase functions serve reads supabase/functions/.env once at startup, so pasting FASTPIX_WEBHOOK_SECRET in while it runs leaves the function with whatever it had. Restart that one process: restarting the database, or the whole stack, does not reload it.

Supabase supplies SUPABASE_DB_URL itself, so that one is not yours to set.

Backfill, reconcile, and the gap left

Two gaps open in any sync: history is everything that existed before the webhook was wired up, and silence is an event never delivered, or delivered while your function was down.

npx @fastpix/supabase backfill media          # walk the existing on-demand library
npx @fastpix/supabase backfill live_streams   # same for live
npx @fastpix/supabase reconcile               # last 24 hours
npx @fastpix/supabase reconcile 48            # widen the window to 48 hours

Backfill walks the FastPix API and writes what it finds, checkpointing as it goes, so an interrupted run resumes rather than restarting, and fastpix.sync_state holds the bookkeeping that says how far it got. It has to walk the whole library because the list endpoint takes limit, offset and orderBy and nothing else: there is no updated-since filter, so there is no cheap way to ask the API only for what moved.

Reconcile is the one we get asked about, and the shape of it surprises people: it never calls the list endpoint. It reads your own table. It selects the rows in fastpix.media whose local updatedAt falls inside the window, re-fetches each one from the API by ID, writes back what came out, and deletes the local row when the API answers 404. That is why it can report deletions at all, which a walk of the list could never do, because a deletion shows up as an absence and finding an absence means comparing the API against rows you already hold.

Reading your own table is also what bounds it, and the boundary is sharper than a window usually implies. The filter runs on your local updatedAt, not on anything the API knows, so an asset whose webhook was lost has a stale local timestamp and never enters the query at all. Widening the window does not reach it, because 48 hours of your own stale rows is still 48 hours of your own stale rows. A retitle you missed last month is invisible to reconcile 720 exactly as it is to reconcile 24.

The blunt instrument that catches it is a full backfill, which rewrites every asset it reads. It is the most expensive thing in the toolkit, so run it when you have a reason, such as after an outage in your function, rather than on a schedule.

Sync video metadata to any Postgres

@fastpix/fp-sync-engine is the library underneath our CLI: framework-free TypeScript that runs on Node and Deno and writes to any Postgres, so runMigrations() builds the same schema on a database you host.

import express from "express";
import { FastPixSync, runMigrations, InvalidSignatureError } from "@fastpix/fp-sync-engine";

await runMigrations({ databaseUrl: process.env.DATABASE_URL });

const sync = new FastPixSync({
  databaseUrl: process.env.DATABASE_URL,
  fastpixWebhookSecret: process.env.FASTPIX_WEBHOOK_SECRET,
  fastpixTokenId: process.env.FASTPIX_TOKEN_ID,
  fastpixTokenSecret: process.env.FASTPIX_TOKEN_SECRET,
});

const app = express();
app.get("/webhook", (_req, res) => res.send("ok"));   // FastPix pings this on registration

app.post("/webhook", express.text({ type: "*/*" }), async (req, res) => {
  try {
    const { eventId, isDuplicate } = await sync.ingestWebhook(
      req.body,
      req.headers as Record<string, string>,
    );
    res.sendStatus(202);
    if (!isDuplicate) {
      sync.processStoredEvent(eventId).catch((err) => console.error(err));
    }
  } catch (err) {
    res.sendStatus(err instanceof InvalidSignatureError ? 401 : 500);
  }
});

app.listen(3000);

Five statuses leave that handler, and knowing them turns a silent integration into a readable one:

Three things then decide whether the handler holds up. The parser has to hand over the exact bytes we signed, which is why express.text({ type: "*/*" }) is there rather than express.json(), and note that req.rawBody is not something Express populates: reach for it and you get undefined, plus a signature check that fails every time. Then trust isDuplicate rather than writing your own deduplication, because a re-delivery is routine and processing one twice is not free.

The third bites later. Processing continues after the response here, which is safe on a long-running Node process and unsafe on a serverless handler, where the runtime may freeze or tear down the invocation the moment you respond and take the re-fetch with it. That is why our Supabase version puts the event on a pgmq queue and lets a separate function drain it.

Run the sync on your laptop

Set this up locally first, because every failure above shows up on a laptop and none in a diagram. With Docker running and npx supabase start finished, the order matters:

npx @fastpix/supabase init                 # migrations written, four functions created
npx supabase stop && npx supabase start    # config.toml is read at startup

Add the two Vault secrets in the SQL editor, expose port 54321 with ngrok, and run npx supabase functions serve in a second terminal. Then create the webhook, paste its signing secret into supabase/functions/.env, restart functions serve, upload a video and watch a row appear in fastpix.media. Our integration guide has the sequence in full, troubleshooting maps each symptom to its cause, and running the sync on your own project needs only the free plan, ten videos, no card.