Broadcasting from the database
Send Realtime Broadcast messages straight from Postgres with realtime.send and realtime.broadcast_changes, using triggers and private channels instead of Postgres Changes.
You can publish Realtime messages from SQL. realtime.send() sends any JSON payload to a topic, and realtime.broadcast_changes() sends a row change in a ready-made format. Call either from a trigger and every insert, update or delete reaches subscribed clients as a Broadcast message.
Why use it instead of Postgres Changes
Postgres Changes checks RLS for every change and every subscriber. Database broadcast checks authorization once, when a client joins a channel, through policies on realtime.messages. You also choose the topic and the payload, so you can send only the fields clients need, or fan out one change to a specific topic such as booking:381.
Send a custom message
select realtime.send(
payload => jsonb_build_object('status', 'confirmed', 'booking_id', 381),
event => 'status-changed',
topic => 'booking:381',
private => true
);
private defaults to true. A private message only reaches channels created with config: { private: true }; a public one only reaches public channels. They must match.
Broadcast row changes with a trigger
This example sends every change to a booking to that booking's own topic.
create or replace function public.broadcast_booking_change()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
perform realtime.broadcast_changes(
'booking:' || coalesce(new.id, old.id)::text, -- topic
tg_op, -- event name
tg_op, -- operation
tg_table_name,
tg_table_schema,
new,
old
);
return null;
end;
$$;
create trigger bookings_broadcast
after insert or update or delete on public.bookings
for each row execute function public.broadcast_booking_change();
Allow users to receive it
Private channels need a select policy on realtime.messages. Here a guest can listen only to their own bookings' topics:
create policy "Guests receive their booking topics"
on realtime.messages for select
to authenticated
using (
exists (
select 1 from public.bookings b
where b.guest_id = (select auth.uid())
and 'booking:' || b.id::text = (select realtime.topic())
)
);
Add only policies. Don't run alter table on realtime.messages; see must be owner of table messages.
Listen in the client
await supabase.realtime.setAuth() // use the signed-in user's token
const channel = supabase
.channel(`booking:${bookingId}`, { config: { private: true } })
.on('broadcast', { event: 'UPDATE' }, ({ payload }) => {
console.log('booking changed', payload)
})
.on('broadcast', { event: 'status-changed' }, ({ payload }) => {
showStatus(payload.status)
})
.subscribe()
Common problems
- Nothing arrives: a public channel listening for a private message (or the reverse), a topic typo, or no
selectpolicy for the user. CHANNEL_ERRORon join: the client isn't signed in, or no policy matches the topic.- The trigger errors:
realtime.sendcatches its own errors so it won't break your write, but a bug elsewhere in your trigger function will. Test it in Studio's SQL editor first.
On SupaHost
Database broadcast works on SupaHost as upstream: each project's database has its own realtime schema and its own Realtime tenant. There's no dashboard switch to make a project accept private channels only, so treat any topic without a private flag as public. Broadcast messages are free on SupaHost; there are no per-message fees, though each project is limited to 5,000 events per second.