Ultimate Video Onboarding Pipeline – SQL Triggers & Supabase Storage

Q: Where are processed files stored?

All processed files are stored in the public-processed bucket under the user's ID, organized in subfolders: thumbnails/ for image and video thumbnails, and videos/ for resized video versions.

Every video platform faces the same problem: users upload raw media, and you need to deliver it in multiple formats, resolutions, and with thumbnails. Doing this manually is a nightmare. Doing it with a serverless, event‑driven architecture is the solution — especially when you combine PostgreSQL triggers, pgmq, Supabase Storage, and ffmpeglab.

This guide shows you how to build the ultimate video onboarding pipeline — a fully automated system that turns a single upload into a complete media package:

Video thumbnails at multiple sizes (160×90, 320×180, 640×360)
Video transcoding to multiple resolutions (480p, 720p, 1080p)
Image thumbnails (320×320)
Real‑time notifications via pg_notify
Per‑user isolation with RLS policies
Durable job queuing with pgmq

Key takeaways

One SQL script — everything is set up with a single execution against your Supabase database.
Zero manual intervention — upload a file, and the pipeline handles everything.
Scalable and reliable — pgmq provides durable, transaction‑safe job queuing.
Configurable — easily adjust thumbnail sizes and video resolutions.
Production‑ready — includes monitoring views, logging, and notifications.
No table conflicts — uses existing render and logpiece tables from FFmpegLab server.
Exact FFmpeg commands — ready to use in your runner.

The Gap: Manual Media Processing Is Broken

Most media processing pipelines require manual steps: upload a file, trigger a script, wait for processing, then manually move the file. This is slow, error‑prone, and doesn't scale.

What if the pipeline could be fully automated — triggered by the upload itself, processing in the background, and notifying the user when complete?

This guide shows you exactly how to build that pipeline.

Architecture Overview

User Uploads File → Supabase Storage → PostgreSQL Trigger → pgmq Queue → ffmpeglab-runner
Processed Media → Public Folder → pg_notify → User Notified

The pipeline consists of:

Supabase Storage — two buckets: private-uploads (per‑user) and public-processed (per‑user, with subfolders for thumbnails and resized media).
RLS policies — restrict access to each user's own folders.
PostgreSQL trigger — fires on INSERT into storage.objects.
pgmq — message queue for job processing.
ffmpeglab-runner — executes FFmpeg commands to generate thumbnails and resized media.
pg_notify — real‑time status updates.

Important: This pipeline uses the existing render and logpiece tables from the FFmpegLab server. It does not create new tables — it only adds the pipeline components.

What the Pipeline Delivers

Media Type	Output	Location
Video	Thumbnails: 160×90, 320×180, 640×360	`public-processed/{userId}/thumbnails/`
Video	Resolutions: 480p, 720p, 1080p (MP4)	`public-processed/{userId}/videos/`
Image	Thumbnail: 320×320	`public-processed/{userId}/thumbnails/`
All	Real‑time notifications	`pg_notify` channels
All	Job tracking	`render` table (existing)
All	Logs	`logpiece` table (existing)

Prerequisites

A Supabase project (cloud or self‑hosted).
ffmpeglab-server and ffmpeglab-runner deployed (see setup guide).
The render and logpiece tables must already exist (created by the FFmpegLab server migrations).
Access to your Supabase database (psql or the Supabase SQL Editor).

Quick Start – The Setup Script

The fastest way to set up the pipeline is to run the SQL script.

Step 1

Save the SQL script

Copy the complete SQL script from the section below and save it as setup_media_pipeline.sql.

Step 2

Run the script

Execute the script against your Supabase database.

        # Via psql
        psql -U postgres -d your_database -f setup_media_pipeline.sql
        
        # Or via the Supabase SQL Editor
        # Copy and paste the entire script into the SQL Editor and run it.

Step 3

Configure the runner

Add the media processing queue to your runner's environment.

        # Add to your .env or docker-compose.yml
        MEDIA_QUEUE_NAME=media_processing_queue
      

Step 4

Restart the runner

Restart the runner to pick up the new queue.

docker compose restart ffmpeglab-runner

That's it! The pipeline is now live. Users can upload videos and images to private-uploads/{userId}/, and they will be automatically processed.

The Complete SQL Setup Script

Note: This script assumes that the render and logpiece tables already exist (from the FFmpegLab server migrations). It only adds the pipeline components.

      -- ============================================================

      -- ULTIMATE VIDEO ONBOARDING PIPELINE

      -- Complete SQL Setup Script

      -- ============================================================

      -- This script adds the media processing pipeline components.

      -- It assumes the render and logpiece tables already exist.

      -- ============================================================

      -- ============================================================

      -- 1. Create storage buckets

      -- ============================================================

      INSERT INTO storage.buckets (id, name, public, avif_autodetection, file_size_limit, allowed_mime_types)

      VALUES 

          ('private-uploads', 'private-uploads', false, false, 5368709120, ARRAY['video/mp4', 'video/quicktime', 'video/x-msvideo', 'video/webm', 'video/mpeg', 'image/jpeg', 'image/png', 'image/webp', 'image/gif']),

          ('public-processed', 'public-processed', true, false, 5368709120, ARRAY['video/mp4', 'image/jpeg', 'image/png', 'image/webp'])

      ON CONFLICT (id) DO NOTHING;

      -- ============================================================

      -- 2. RLS policies for private-uploads bucket

      -- ============================================================

      DROP POLICY IF EXISTS "Users can upload to their own folder" ON storage.objects;

      CREATE POLICY "Users can upload to their own folder"

      ON storage.objects

      FOR INSERT

      TO authenticated

      WITH CHECK (

          bucket_id = 'private-uploads' AND

          (storage.foldername(name))[1] = auth.uid()::text

      );

      DROP POLICY IF EXISTS "Users can download from their own folder" ON storage.objects;

      CREATE POLICY "Users can download from their own folder"

      ON storage.objects

      FOR SELECT

      TO authenticated

      USING (

          bucket_id = 'private-uploads' AND

          (storage.foldername(name))[1] = auth.uid()::text

      );

      DROP POLICY IF EXISTS "Users can update their own files" ON storage.objects;

      CREATE POLICY "Users can update their own files"

      ON storage.objects

      FOR UPDATE

      TO authenticated

      USING (

          bucket_id = 'private-uploads' AND

          (storage.foldername(name))[1] = auth.uid()::text

      );

      DROP POLICY IF EXISTS "Users can delete their own files" ON storage.objects;

      CREATE POLICY "Users can delete their own files"

      ON storage.objects

      FOR DELETE

      TO authenticated

      USING (

          bucket_id = 'private-uploads' AND

          (storage.foldername(name))[1] = auth.uid()::text

      );

      -- ============================================================

      -- 3. RLS policies for public-processed bucket

      -- ============================================================

      DROP POLICY IF EXISTS "Public read access to processed media" ON storage.objects;

      CREATE POLICY "Public read access to processed media"

      ON storage.objects

      FOR SELECT

      USING (bucket_id = 'public-processed');

      DROP POLICY IF EXISTS "Service role can manage processed media" ON storage.objects;

      CREATE POLICY "Service role can manage processed media"

      ON storage.objects

      FOR ALL

      TO service_role

      USING (bucket_id = 'public-processed');

      DROP POLICY IF EXISTS "Users can read their own processed media" ON storage.objects;

      CREATE POLICY "Users can read their own processed media"

      ON storage.objects

      FOR SELECT

      TO authenticated

      USING (

          bucket_id = 'public-processed' AND

          (storage.foldername(name))[1] = auth.uid()::text

      );

      -- ============================================================

      -- 4. Enable pgmq extension and create the queue

      -- ============================================================

      CREATE EXTENSION IF NOT EXISTS pgmq;

      SELECT pgmq.create('media_processing_queue');

      -- ============================================================

      -- 5. Create the trigger function for media uploads

      -- This function builds the exact FFmpeg commands to be executed by the runner.

      -- ============================================================

      DROP FUNCTION IF EXISTS handle_media_upload() CASCADE;

      CREATE OR REPLACE FUNCTION handle_media_upload()

      RETURNS TRIGGER AS $$

      DECLARE

          user_id text;

          file_path text;

          file_name text;

          file_extension text;

          mime_type text;

          msg jsonb;

          video_mime_types text[] := ARRAY['video/mp4', 'video/quicktime', 'video/x-msvideo', 'video/webm', 'video/mpeg'];

          image_mime_types text[] := ARRAY['image/jpeg', 'image/png', 'image/webp', 'image/gif'];

          thumbnail_sizes text[] := ARRAY['160x90', '320x180', '640x360'];

          video_resolutions text[] := ARRAY['480p', '720p', '1080p'];

          size text;

          w text;

          h text;

          res text;

          commands jsonb := '[]';

      BEGIN

          -- Only process files in the private-uploads bucket

          IF NEW.bucket_id = 'private-uploads' THEN

              user_id := (storage.foldername(NEW.name))[1];

              file_path := NEW.name;

              file_name := (storage.filename(NEW.name));

              file_extension := split_part(file_name, '.', array_length(string_to_array(file_name, '.'), 1));

              mime_type := NEW.metadata->>'mimetype';

              -- Build the list of FFmpeg commands based on media type

              IF mime_type = ANY(video_mime_types) THEN

                  -- Thumbnail commands

                  FOREACH size IN ARRAY thumbnail_sizes LOOP

                      w := split_part(size, 'x', 1);

                      h := split_part(size, 'x', 2);

                      commands := commands || jsonb_build_object(

                          'type', 'thumbnail',

                          'size', size,

                          'output_path', user_id || '/thumbnails/' || size || '.jpg',

                          'command', 'ffmpeg -i "INPUT_FILE" -vf "thumbnail,scale=' || w || ':' || h || '" -frames:v 1 "OUTPUT_FILE"'

                      );

                  END LOOP;

                  -- Video transcoding commands

                  FOREACH res IN ARRAY video_resolutions LOOP

                      h := regexp_replace(res, 'p$', '');

                      commands := commands || jsonb_build_object(

                          'type', 'video',

                          'resolution', res,

                          'output_path', user_id || '/videos/' || res || '.mp4',

                          'command', 'ffmpeg -i "INPUT_FILE" -c:v libx264 -crf 23 -preset medium -vf "scale=-2:' || h || '" -c:a aac -b:a 128k "OUTPUT_FILE"'

                      );

                  END LOOP;

              ELSIF mime_type = ANY(image_mime_types) THEN

                  commands := commands || jsonb_build_object(

                      'type', 'thumbnail',

                      'size', '320x320',

                      'output_path', user_id || '/thumbnails/320x320.jpg',

                      'command', 'ffmpeg -i "INPUT_FILE" -vf "scale=320:320:force_original_aspect_ratio=decrease,pad=320:320:(ow-iw)/2:(oh-ih)/2" -q:v 85 "OUTPUT_FILE"'

                  );

              END IF;

              -- Build the job message

              msg := jsonb_build_object(

                  'userId', user_id,

                  'inputPath', file_path,

                  'inputBucket', NEW.bucket_id,

                  'outputBucket', 'public-processed',

                  'fileName', file_name,

                  'fileExtension', file_extension,

                  'mimeType', mime_type,

                  'originalSize', NEW.metadata->>'size',

                  'isVideo', mime_type = ANY(video_mime_types),

                  'isImage', mime_type = ANY(image_mime_types),

                  'commands', commands,

                  'timestamp', NOW()

              );

              -- Push to pgmq queue

              PERFORM pgmq.send('media_processing_queue', msg::jsonb);

              -- Insert into render table for job tracking

              INSERT INTO "render" (id, title, project, status, public, user_id, data)

              VALUES (

                  gen_random_uuid(),

                  file_name,

                  'media-processing',

                  'queued',

                  false,

                  user_id::uuid,

                  msg

              );

              -- Notify via pg_notify

              PERFORM pg_notify(

                  'media_upload_channel',

                  jsonb_build_object(

                      'userId', user_id,

                      'filePath', file_path,

                      'fileName', file_name,

                      'mimeType', mime_type,

                      'status', 'queued',

                      'timestamp', NOW()

                  )::text

              );

          END IF;

          RETURN NEW;

      END;

      $$ LANGUAGE plpgsql SECURITY DEFINER;

      -- ============================================================

      -- 6. Attach the trigger to storage.objects

      -- ============================================================

      DROP TRIGGER IF EXISTS media_upload_trigger ON storage.objects;

      CREATE TRIGGER media_upload_trigger

      AFTER INSERT ON storage.objects

      FOR EACH ROW

      EXECUTE FUNCTION handle_media_upload();

      -- ============================================================

      -- 7. Helper views for monitoring

      -- ============================================================

      DROP VIEW IF EXISTS media_processing_queue_view;

      CREATE OR REPLACE VIEW media_processing_queue_view AS

      SELECT 

          msg_id,

          read_ct,

          enqueued_at,

          vt,

          message::jsonb as payload,

          (message::jsonb->>'userId') as user_id,

          (message::jsonb->>'fileName') as file_name,

          (message::jsonb->>'isVideo')::boolean as is_video,

          (message::jsonb->>'isImage')::boolean as is_image

      FROM pgmq.q_media_processing_queue

      ORDER BY msg_id DESC;

      -- ============================================================

      -- 8. Initialize notification channels

      -- ============================================================

      DO $$

      BEGIN

          PERFORM pg_notify('media_upload_channel', '{"init": true}');

          PERFORM pg_notify('render_status_channel', '{"init": true}');

          PERFORM pg_notify('log_channel', '{"init": true}');

      END $$;

      -- ============================================================

      -- All done!

      -- ============================================================

What the Script Does

Component	Description
Storage Buckets	Creates `private-uploads` (private) and `public-processed` (public) buckets with file size limits and allowed MIME types
RLS Policies	Sets per‑user isolation for private uploads and public read access for processed media
pgmq Queue	Creates the `media_processing_queue` for job processing
Trigger Function	`handle_media_upload()` — fires on new uploads, builds FFmpeg commands, pushes to pgmq, inserts into `render`, sends notifications
Views	Helper views for monitoring queue status
Uses Existing Tables	Uses `render` for job tracking and `logpiece` for logs (from FFmpegLab server)

Processing Logic Explained

When a file is uploaded to private-uploads/{userId}/, the pipeline:

Identifies the media type — video or image.
Builds the exact FFmpeg commands for thumbnails and transcoding.
Creates a render job in the existing render table with the commands in the data column.
Pushes a job to the media_processing_queue with the commands payload.
Sends a notification via pg_notify.
The ffmpeglab-runner picks up the job, resolves the INPUT_FILE and OUTPUT_FILE placeholders, and executes the commands.

Exact FFmpeg Commands

The trigger function generates the following FFmpeg commands using placeholders:

INPUT_FILE — The path to the downloaded input file (resolved by the runner).
OUTPUT_FILE — The temporary path for the output file (resolved by the runner).

1. Video Thumbnails

      # 160x90 thumbnail

      ffmpeg -i "INPUT_FILE" -vf "thumbnail,scale=160:90" -frames:v 1 "OUTPUT_FILE"
      
      # 320x180 thumbnail

      ffmpeg -i "INPUT_FILE" -vf "thumbnail,scale=320:180" -frames:v 1 "OUTPUT_FILE"
      
      # 640x360 thumbnail

      ffmpeg -i "INPUT_FILE" -vf "thumbnail,scale=640:360" -frames:v 1 "OUTPUT_FILE"

2. Video Transcoding

      # 480p

      ffmpeg -i "INPUT_FILE" -c:v libx264 -crf 23 -preset medium -vf "scale=-2:480" -c:a aac -b:a 128k "OUTPUT_FILE"
      
      # 720p

      ffmpeg -i "INPUT_FILE" -c:v libx264 -crf 23 -preset medium -vf "scale=-2:720" -c:a aac -b:a 128k "OUTPUT_FILE"
      
      # 1080p

      ffmpeg -i "INPUT_FILE" -c:v libx264 -crf 23 -preset medium -vf "scale=-2:1080" -c:a aac -b:a 128k "OUTPUT_FILE"

3. Image Thumbnail

      # 320x320 centered thumbnail with padding

      ffmpeg -i "INPUT_FILE" -vf "scale=320:320:force_original_aspect_ratio=decrease,pad=320:320:(ow-iw)/2:(oh-ih)/2" -q:v 85 "OUTPUT_FILE"

Configure ffmpeglab-runner

The runner needs to be configured to poll the media_processing_queue and execute the provided FFmpeg commands.

Step 1

Add the queue to your environment

Add the following to your .env file or Docker Compose configuration.

        # Media processing queue
        MEDIA_QUEUE_NAME=media_processing_queue
      

Step 2

Implement the processing loop

The runner should execute the following steps for each job:

        # 1. Connect to Supabase and listen to the queue
        
        # 2. For each job:
        
        #    a. Download the input file from private-uploads
        
        #    b. Parse the 'commands' array from the job payload
        
        #    c. For each command:
        
        #        - Replace 'INPUT_FILE' with the local input path
        
        #        - Replace 'OUTPUT_FILE' with a temporary local path
        
        #        - Execute the FFmpeg command
        
        #        - Upload the output file to public-processed/{output_path}
        
        #        - Update the render table with progress and logs
        
        #    d. Mark the job as complete in the render table
        
        #    e. Delete the job from the queue

Step 3

Install FFmpeg on the runner

Ensure FFmpeg is installed and available in the PATH.

        # Debian/Ubuntu
        apt-get install -y ffmpeg
        
        # Alpine
        apk add ffmpeg

Step 4

Restart the runner

After updating the environment, restart the runner service.

docker compose restart ffmpeglab-runner

Monitor the Pipeline

You can monitor the pipeline using SQL queries and notifications.

Step 1

Check queued jobs

Use the helper view to see all queued jobs.

SELECT * FROM media_processing_queue_view;

Step 2

Check render status

Query the existing render table for job status.

        SELECT id, title, status, progress, data

        FROM "render"

        WHERE project = 'media-processing'

        ORDER BY created_at DESC;

Step 3

Listen to notifications

In your application, listen for real‑time updates.

        -- In your PostgreSQL client:

        LISTEN media_upload_channel;

        LISTEN render_status_channel;

        LISTEN log_channel;

Step 4

Check processed files

List all processed files in the public bucket.

        SELECT name, metadata, created_at FROM storage.objects

        WHERE bucket_id = 'public-processed'

        ORDER BY created_at DESC;

Frequently Asked Questions (FAQ)

What does the Ultimate Video Onboarding Pipeline do?

It automatically processes uploaded videos and images. For videos, it generates thumbnails (160x90, 320x180, 640x360) and transcodes to multiple resolutions (480p, 720p, 1080p). For images, it creates thumbnails (320x320). All processed files are stored in a public bucket under the user's ID with real-time notifications.

What FFmpeg commands are used for processing?

The pipeline uses ffmpeg with specific commands: for video thumbnails: ffmpeg -i input.mp4 -vf 'thumbnail,scale=W:H' -frames:v 1 output.jpg. For video transcoding: ffmpeg -i input.mp4 -c:v libx264 -crf 23 -preset medium -vf 'scale=-2:H' -c:a aac -b:a 128k output.mp4. For images: ffmpeg -i input.jpg -vf 'scale=320:320:force_original_aspect_ratio=decrease,pad=320:320:(ow-iw)/2:(oh-ih)/2' -q:v 85 output.jpg.

Where are processed files stored?

All processed files are stored in the public-processed bucket under the user's ID, organized in subfolders: thumbnails/ for image and video thumbnails, and videos/ for resized video versions.

Can I customize the thumbnail sizes and video resolutions?

Yes. The pipeline is designed to be configurable. You can modify the thumbnail_sizes and video_resolutions arrays in the trigger function to match your needs.

How is the pipeline triggered?

A PostgreSQL trigger fires on INSERT into storage.objects when a file is uploaded to the private-uploads bucket. It pushes a job to the pgmq queue, which is processed by the ffmpeglab-runner.

Does this pipeline create new tables?

No. The pipeline uses the existing render and logpiece tables from the FFmpegLab server. It only adds storage buckets, RLS policies, the pgmq queue, and the trigger function — no table conflicts.

Final Word

You now have the Ultimate Video Onboarding Pipeline — a fully automated, event‑driven media processing system that turns a single upload into a complete media package. With PostgreSQL triggers, pgmq, and Supabase Storage, you get:

Automatic thumbnails for videos and images
Multiple video resolutions for different devices
Real‑time notifications for your users
Per‑user isolation with RLS policies
Durable job queuing with pgmq
Full observability with monitoring views and logs
Exact FFmpeg commands ready to use

The pipeline is production‑ready, scalable, and configurable. It uses the existing render and logpiece tables from the FFmpegLab server, so there are no table conflicts — just pure, automated media processing.