Live Updates From Any Write

A LiveTable view broadcasts every write that goes through it. Add a Postgres trigger and every other write broadcasts too: an @rpc calling sqlAsync, a cron job, a webhook handler, a migration backfill, or elements db -sql. The write itself becomes the broadcast, and Postgres delivers it to every app server, so every open page moves. A view announces its own writes because it is the code making them; the trigger has the database announce the rest.

Insert, update and delete through a live view already broadcast, which is what elements man recipes/todo-app and most of the recipes here do. The trigger is useful on any table a page watches: a job, a backfill or a psql session writing to the table updates the page, so you can test the UI by changing the database directly.

The channel

Every write to a LiveTable notifies the table's one channel, and the channel's name is the table's name. Each app server listens on it once and sends each row to the views whose partition it matches. The trigger notifies that same channel:

LiveTable
import { LiveTable } from "@elements/app";

export interface Ping {
  id: string;
  createdAt: Date;
  body: string;
}

export let pings = new LiveTable<Ping>();

The compiler names the table from the variable, camelCase to snake_case, so pings is pings and chatMessages is chat_messages. That string is the channel the trigger hashes.

The trigger

Terminal
elements create migration 'notify pings'

app/migrations/<timestamp>-notify-pings.migration.sql
-- notify pings
create or replace function pingsNotify() returns trigger
language plpgsql as $$
declare
  r record;
  payload text;
begin
  r := coalesce(new, old);

  payload := json_build_object(
    'op', lower(tg_op),
    'data', json_build_object(
      'id', r.id,
      'createdAt', json_build_object('$type', 'Date', '$value', (extract(epoch from r.createdAt) * 1000)::bigint),
      'body', r.body
    )
  )::text;

  -- NOTIFY takes under 8000 bytes. A larger row goes as its id, and the
  -- server reads the row back.
  if octet_length(payload) >= 8000 then
    payload := json_build_object('op', lower(tg_op), 'id', r.id)::text;
  end if;

  perform pg_notify(channel_name('pings'), payload);

  return r;
end;
$$;

create trigger pingsNotifyTrigger
  after insert or update or delete on pings
  for each row execute function pingsNotify();

Five things have to line up, and all five are in that block:

channel_name() hashes a logical name to 16 characters. Postgres caps an identifier at 63 and truncates silently past it, so the framework hashes every channel name and your trigger has to hash the same way. The function ships with the database; you do not create it.
op is insert, update or delete. lower(tg_op) gives exactly that.
data is the row in the field names your app uses, not the column names. Elements converts camelCase identifiers in a migration, function bodies included, so r.createdAt reads the created_at column, while the quoted key 'createdAt' stays as written for the browser.
A Date crosses as { "$type": "Date", "$value": <milliseconds> }. A plain timestamp string arrives as a string, and a template that sorts or formats it then sees the wrong type. Every other column goes across as itself.
The size. Postgres rejects a NOTIFY payload of 8000 bytes or more, and the write that fired the trigger fails with it. A row that large goes as { "op": ..., "id": ... } with no data, and each app server reads the row back through the view's select before the browsers get it. A delete needs only the id.

after matters: the row has to be committed before the notification goes out, or a listener can query for a row that is not there yet. coalesce(new, old) covers delete, where new is null.

Partitioned tables

The same trigger serves every view of the table, whole or partitioned. A page on chatMessages.view({ roomId }) hears the table's one channel, and its app server sends it only the rows whose roomId matches:

Partitioned table
perform pg_notify(channel_name('chat_messages'), payload);

A browser watching room A never receives room B. An update that moves a row to another room reaches the old room's pages as a delete and the new room's pages as an insert, because each server knows which rows each of its pages holds. The trigger sends the row as it is now, and the server does the rest.

Check it

Open the page, then write from outside the app:

Terminal
elements db -sql "insert into pings (body) values ('from outside the app')"

The row appears in the open page, written from outside the app.

elements db -sql converts camelCase names like sql() does, so roomId works there. The interactive shell that elements db opens is plain psql: there, write the names the database has, room_id, not roomId.

livetable/mutations: writing through a view, which needs none of this.
livetable/options: the channel and table options.
channel: the lower-level pub/sub this rides on.

Live Updates From Any Write

The channel

The trigger

Partitioned tables

Check it

Related