DEV Community: Matías Battocchia

5 Supabase patterns I learned building an open-source WhatsApp platform

Matías Battocchia — Tue, 21 Apr 2026 03:55:47 +0000

I've been building OpenBSP — an open-source WhatsApp Business Platform — on Supabase since 2023. Back then, Supabase was promising but not what it has become today. Over these years I got to know its features in depth.

Here are 5 patterns I came up with while building a production multi-tenant messaging platform. All of them are running in production.

Multi-tenant RLS
Event-driven architecture
NoSQL-like JSON merging
Dev/Prod environments
Declarative schemas

1. Multi-tenant RLS with dual auth and role hierarchy

Every table in OpenBSP belongs to an organization. Users can be members of multiple organizations with different roles (owner, admin, member). And on top of that, we also support API key authentication for service-to-service access.

The typical Supabase approach is to use auth.uid() in RLS policies. But what if you also need to support API keys? And what about role levels?

We solved it with a single function that every RLS policy calls:

create function public.get_authorized_orgs(
  role public.role default 'member'
) returns setof uuid
language plpgsql
security definer
set search_path to ''
as $$
declare
  req_level int;
  api_key text;
  org_id uuid;
begin
  req_level := case role::text
    when 'owner' then 3
    when 'admin' then 2
    else 1 -- 'member'
  end;

  -- First, try JWT authentication via auth.uid()
  if auth.uid() is not null then
    return query select organization_id from public.agents
    where user_id = auth.uid()
    and (
      case (extra->>'role')
        when 'owner' then 3
        when 'admin' then 2
        else 1
      end
    ) >= req_level;

    if found then return; end if;
  end if;

  -- Fallback to API key authentication
  api_key := current_setting(
    'request.headers', true
  )::json->>'api-key';

  if api_key is not null then
    select a.organization_id into org_id
    from public.api_keys a
    where a.key = api_key
    and (
      case (a.role::text)
        when 'owner' then 3
        when 'admin' then 2
        else 1
      end
    ) >= req_level;

    if org_id is not null then
      return next org_id;
      return;
    end if;
  end if;

  raise exception using
    errcode = '42501',
    message = 'authentication required';
end;
$$;

The trick is mapping roles to numbers (owner=3, admin=2, member=1) so a single >= comparison handles the entire hierarchy. If you ask for 'admin' access, owners get in too.

Then every RLS policy becomes a one-liner:

create policy "members can read their orgs messages"
on public.messages
for select
to authenticated, anon
using (
  organization_id in (
    select public.get_authorized_orgs('member')
  )
);

Notice to authenticated, anon — this is what makes API key auth work. The anon role allows unauthenticated requests (no JWT), and the function falls back to checking the api-key header. The function is SECURITY DEFINER so it can access auth.uid() and request headers regardless of who calls it.

One function, two auth methods, three role levels, and every table uses the same pattern.

2. Event-driven architecture: triggers that call Edge Functions

This is the core of OpenBSP's architecture. When a message is inserted into the database, Postgres triggers decide what happens next — depending on the message direction, status, and type.

Here's a simplified view of the trigger chain on the messages table:

-- Incoming message → call the AI agent
create trigger handle_incoming_message_to_agent
after insert on public.messages
for each row
when (
  new.direction = 'incoming'
  and (new.status ->> 'pending') is not null
)
execute function public.edge_function('/agent-client', 'post');

-- Outgoing message → send via WhatsApp
create trigger handle_outgoing_message_to_dispatcher
after insert on public.messages
for each row
when (
  new.direction = 'outgoing'
  and new.timestamp <= now()
  and (new.status ->> 'pending') is not null
)
execute function public.edge_function('/whatsapp-dispatcher', 'post');

-- File attachment → extract content with AI
create trigger handle_message_to_media_preprocessor
after insert on public.messages
for each row
when (
  (new.direction = 'outgoing' or new.direction = 'incoming')
  and (new.status ->> 'pending') is not null
  and (new.content ->> 'type') = 'file'
)
execute function public.edge_function('/media-preprocessor', 'post');

The WHEN clause is doing the heavy lifting here. It acts as a router — each trigger only fires for its specific combination of direction, status, and content type. No if/else logic in the Edge Functions, no message bus, no queue.

The edge_function() is a reusable trigger function that calls any Edge Function:

create function public.edge_function() returns trigger
language plpgsql
security definer
as $$
declare
  request_id bigint;
  payload jsonb;
  base_url text;
  auth_token text;
  path text := tg_argv[0]::text;   -- e.g., '/agent-client'
  method text := tg_argv[1]::text;  -- 'post' or 'get'
begin
  -- Credentials stored in Supabase Vault
  select decrypted_secret into base_url
  from vault.decrypted_secrets
  where name = 'edge_functions_url';

  select decrypted_secret into auth_token
  from vault.decrypted_secrets
  where name = 'edge_functions_token';

  payload = jsonb_build_object(
    'record', new,
    'type', tg_op
  );

  select http_post into request_id from net.http_post(
    base_url || path,
    payload,
    '{}'::jsonb,
    jsonb_build_object(
      'content-type', 'application/json',
      'authorization', 'Bearer ' || auth_token
    ),
    10000  -- timeout_ms
  );

  return new;
end;
$$;

We use tg_argv (trigger arguments) to pass the Edge Function path and HTTP method. One function handles all invocations — you just configure different triggers with different arguments.

The net.http_post call is asynchronous (pg_net extension), so the INSERT returns immediately. The Edge Function runs in the background — you don't want a slow AI response blocking your database transaction.

The credentials are stored in Supabase Vault, not as environment variables. Triggers don't have access to Edge Function env vars, so Vault is the way to go.

3. NoSQL-like JSON merging in Postgres

Here's a problem we hit early on. A message has a status column that looks like this:

{"pending": "2024-01-01T10:00:00Z"}

When the message is sent, we want to add a key:

{"pending": "2024-01-01T10:00:00Z", "sent": "2024-01-01T10:00:05Z"}

But Supabase's default behavior on UPDATE replaces the entire column. If we do:

update messages
set status = '{"sent": "2024-01-01T10:00:05Z"}'
where id = '...';

We lose the pending timestamp. Gone. In MongoDB or Firestore, you'd use something like $set or updateDoc to merge fields. Postgres doesn't have that out of the box.

We built a recursive merge function:

create function public.merge_update_jsonb(
  target jsonb, path text[], object jsonb
) returns jsonb
language plpgsql
immutable
set search_path to ''
as $$
declare
  key text;
  value jsonb;
begin
  if target is null then
    target := '{}'::jsonb;
  end if;

  case jsonb_typeof(object)
    when null then
      target := null;
    when 'object' then
      -- Recurse into each key
      for key, value in select * from jsonb_each(object) loop
        target := public.merge_update_jsonb(
          target, array_append(path, key), value
        );
      end loop;
    else
      -- Scalars replace at path
      target := jsonb_set(target, path, object, true);
  end case;

  return target;
end;
$$;

The behavior is:

Value type	What happens
`null`	Replaces entire target with null
`{}` (empty object)	Recursively merges (no-op if empty)
Non-empty object	Recursively merges nested keys
String/Number/Boolean	Replaces value at that path

Then we wrap it in a generic BEFORE UPDATE trigger. It takes the column name as argument, merges OLD.column with NEW.column, and writes the result back into NEW:

create function public.merge_update() returns trigger ...
-- Takes tg_argv[0] as column name
-- Calls merge_update_jsonb(OLD.column, NEW.column)
-- Sets NEW.column = merged result

Attach it to any column that needs merge semantics:

create trigger set_status
before update on public.messages
for each row
when (new.status is not null)
execute function public.merge_update('status');

create trigger set_message
before update on public.messages
for each row
when (new.content is not null)
execute function public.merge_update('content');

Now when we update status, new keys are added and existing keys are preserved. Document-store semantics in a relational database, with no application-level code.

4. Dev/Prod environments without Supabase Branching

Supabase has a branching feature for preview environments. It's great, but it's a paid feature. We needed separate staging and production environments with automated deployments, and we needed it for free.

The solution: two Supabase projects + GitHub Environments + one workflow.

name: Release

on:
  push:
    branches:
      - main
      - develop
  workflow_dispatch:

jobs:
  db-release:
    runs-on: ubuntu-latest

    # This is the key line — same workflow, different target
    environment: ${{ github.ref == 'refs/heads/main'
      && 'Production' || 'Staging' }}

    env:
      SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
      SUPABASE_DB_PASSWORD: ${{ secrets.SUPABASE_DB_PASSWORD }}
      SUPABASE_PROJECT_ID: ${{ vars.SUPABASE_PROJECT_ID }}

    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v1

      - run: supabase link --project-ref $SUPABASE_PROJECT_ID
      - run: supabase db push
      - run: supabase functions deploy

The environment line does all the magic. Push to develop → deploys to Staging (Supabase project A). Push to main → deploys to Production (Supabase project B). Each GitHub Environment has its own set of secrets and variables pointing to a different Supabase project.

supabase db push applies pending migrations. supabase functions deploy deploys all Edge Functions.

5. Declarative schemas: the source of truth

Traditional Supabase projects use migrations as the source of truth. Over time, you end up with dozens of migration files, and understanding the current state of the database requires reading them all in order.

We took a different approach. Instead of writing migrations by hand, we maintain schema files that represent the desired state of the database:

supabase/schemas/
  00_extensions.sql
  01_types.sql
  02_functions/
    02-01_json_utils.sql
    02-02_edge_functions.sql
    02-03_trigger_functions.sql
    02-04_rcp_functions.sql
  03_models/
    03-00_organizations.sql
    03-01_organizations_addresses.sql
    03-02_contacts.sql
    03-03_conversations.sql
    03-04_agents.sql
    03-05_messages.sql
    ...
  04_functions_post_tables/
    04-01_auth_helpers.sql
  05_rls/
    05-00_organizations_rls.sql
    05-05_messages_rls.sql
    ...
  06_billing/
    ...

The numbered prefixes enforce execution order: extensions first, then types, then functions (that don't reference tables), then tables, then functions that DO reference tables, then RLS policies. This ordering matters because Postgres needs to see types before functions that use them, and tables before RLS policies.

The workflow is:

Edit a schema file (e.g., add a column to 03-05_messages.sql)
Run supabase db diff -f my_change — Supabase compares the schema files against the running database and generates a migration
Review and commit the migration
supabase db push applies it

You never write migration SQL by hand — you describe what you want, and the diff tool figures out how to get there. When you review a PR, you see the final state of the table, not a chain of ALTER statements.

Note: Cron and Realtime statements are not caught by supabase db diff thus these are exceptions to the rule, you need to write migrations for them.

These patterns emerged from building a real product on Supabase over 3 years. Some I planned, some I stumbled into. All of them are running in production at Mirlo.

If you want to see them in context:

API repo: github.com/matiasbattocchia/open-bsp-api
UI repo: github.com/matiasbattocchia/open-bsp-ui
Live demo: web.openbsp.dev

Happy to discuss any of these — some things worked great, some I had to work around. What patterns have you found useful in your Supabase projects?

Pipeline Parallelism in PyTorch

Matías Battocchia — Wed, 30 Aug 2023 14:26:19 +0000

As LLMs keep growing in size I started wondering about the possibility of deploying a model across many machines. While training is done in a distributed way, inference rarely goes beyond one machine as it has much less requirements to serve a model.

When the model is big, many of us would request a single-GPU machine with enough resources to serve the model. As a rule of thumb, you need at least twice GPU space for inference than the number of the model parameters. For example, LLaMA 13B has 13 giga-parameters (a giga is a billion), hence it requires 26 GB of GPU. Using float precision, it is 2 bytes per parameter, that is why. Choose a NVIDIA A100 40GB and we are good to go.

If the model is too big, then a tool like Hugging Face Accelerate can help us to run the model on a single machine with multiple GPUs. You are able to attach up to 8 devices to one machine in most cloud providers.

Then there are projects like Alpa that enable the deployment of really big models like OPT 175B and BLOOM 176B on multi-machine multi-device clusters. One drawback though, the model needs to be supported by the project.

Doing further research I came across PyTorch's PiPPy project. It features
automatic splitting of model code. It means that you do not have to make modifications to the model code to make parallelism work.

While consulting PiPPy docs and source code, I did the following exercise in order to grasp elemental insights from this tool and pipeline parallelism in general: to run a model across two Docker containers.

The idea is to deploy heavyweight models but for the sake of simplicity, let's use this model.

example.py

import os
import pippy
from torch.distributed import rpc
from torch import nn

class Net(nn.Module):
    def __init__(self):
        super().__init__()
        self.fc1 = nn.Linear(128, 128)
        self.fc2 = nn.Linear(128,   8)

    def forward(self, x):
        x = self.fc1(x)
        x = nn.functional.relu(x)
        x = self.fc2(x)

        return x

net = Net()
net.eval()

Let's assume that we have a bunch of machines, all on the same network. The same script runs on all the nodes, there are a head node and worker nodes. Worker nodes should be able to reach the head node at a host:port. Each node has a rank number and the total count of nodes is the world.

example.py (continuation)

RANK  = int(os.environ["RANK"])
WORLD = int(os.environ["WORLD"])
HOST  = os.environ["HOST"]
PORT  = os.environ["PORT"]
print(f"My rank is {RANK}")


# first thing to do is to init RCP
print("Waiting for all the nodes...")
rpc.init_rpc(
    f"worker{RANK}", # just an identifier
    rank=RANK,
    world_size=WORLD,
    rpc_backend_options=rpc.TensorPipeRpcBackendOptions(
        num_worker_threads=8,
        rpc_timeout=10, # seconds
        init_method=f"tcp://{HOST}:{PORT}", # head node's address and port
    )
)

# split the model, each process materializes its pipeline stage
driver, stage = pippy.all_compile(
    net,
    num_ranks=WORLD,
    num_chunks=WORLD, # microbatching
    schedule="FillDrain", # feed chunks through the pipeline sequentially
    split_policy=pippy.split_into_equal_size(WORLD), # split the model into specified number of equal-size stages
)
print(stage)

if rank == 0:
    x = torch.randn(4, 128)
    y = driver(x) # only rank 0 is able the call the pipeline's driver
    print(y)

rpc.shutdown()
print("Bye!")

In a terminal:

$ docker build -t example .
$ docker network create rpc
$ docker run -e RANK=0 -e WORLD=2 -e HOST=head -e PORT=3000 \
  --net rpc --name head --rm -it example

Notice how the HOST address equals the container's name.

In another terminal:

$ docker run -e RANK=1 -e WORLD=2 -e HOST=head -e PORT=3000 \
  --net rpc --name worker --rm -it example

This container is named worker, HOST points to the rank 0 container head.

Head output, after all processes join.

My rank is 0
Waiting for all nodes...

PipeStageModule(
  (fc1): Linear(in_features=128, out_features=128, bias=True)
)

def forward(self, x):
    fc1 = self.fc1(x);  x = None
    return fc1

tensor([[-0.1043,  ..., -0.0093],
        ...
        [-0.1566,  ..., -0.0765]])
Bye!

Worker output.

My rank is 1
Waiting for all nodes...

PipeStageModule(
  (fc2): Linear(in_features=128, out_features=8, bias=True)
)

def forward(self, fc1):
    relu = nn.functional.relu(fc1, inplace = False); fc1 = None
    fc2 = self.fc2(relu);  relu = None
    return fc2

Bye!

Next steps in this topic are

use GPU,
more than one GPU device per machine,
deploy on local Kubernetes,
deploy on Google Cloud GKE,
use some really big models.

requirements.txt — CPU-only PyTorch

packaging # missing dependency from PiPPy
numpy
--index-url https://download.pytorch.org/whl/cpu
torch

Dockerfile

FROM python:3

WORKDIR /usr/src/app

COPY requirements.txt ./
RUN pip install --no-cache-dir -r requirements.txt

RUN git clone https://github.com/pytorch/PiPPy.git \
    && cd PiPPy \
    && python setup.py install \
    && cd ..

COPY . .

ENTRYPOINT ["python", "example.py"]

A straightforward Flask replacement for ML model deployment

Matías Battocchia — Thu, 10 Aug 2023 19:27:02 +0000

Why would someone need to replace Flask or FastAPI for the task of deploying a model? They get the job done. Well, yes — that was what I used to think.

Some time ago I gave the task of serving a model to a team of junior developers. It was a data science team, good at developing models, without prior web development knowledge.

I told the team: use something simple like Flask. They finally did it, but struggled with some web concepts such as requests, responses and headers during that day.

So, we are used to web frameworks for model serving, while we could be using a leaner and more specific tool for the task. That is what Deserve is about.

I bid goodbye with an example:

import deserve
from transformers import pipeline

# Load your model
classifier = pipeline('sentiment-analysis')

@deserve
async def predict(payload: object) -> object:
    return classifier(payload)