Live Updates From Any Write

A LiveTable view broadcasts the writes that go through it. A row written any other way, by an @rpc calling sqlAsync, a cron job, a webhook handler, a migration backfill, or elements db -c, lands in the table without telling anyone, and a page watching that table does not move until someone reloads it.

This recipe closes that gap with a Postgres trigger. The write itself becomes the broadcast, so every path is live: the one the framework knows about and the ones it never sees.

You do not need this for an ordinary app. 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. Reach for a trigger only when something outside the view writes to a table a page is watching.

Pin the channel

A view listens on a channel name the compiler derives from where the LiveTable is declared, which is not a name you can write into a migration. Name it yourself instead, and the trigger has something stable to notify:

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

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

export let pings = new LiveTable<Ping>({
  channel: (partition) => (partition ? `pings:${partition}` : "pings"),
});

channel takes the partition key and returns the channel for it. A view opened whole gets the empty key, so that is the pings branch. Partitioned views get pings:<value>; see the partitioned section below.

The trigger

elements create migration 'notify pings'

-- notify pings
create or replace function pingsNotify() returns trigger
language plpgsql as $$
declare
  r record;
begin
  r := coalesce(new, old);

  perform pg_notify(
    channel_name('pings'),
    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
  );

  return r;
end;
$$;

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

Four things have to line up, and all four 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. Postgres folds unquoted identifiers, so r.createdAt reads the created_at column while the key you build is the createdAt the browser expects.
• 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.

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

A partitioned view listens per partition, so the trigger notifies the partition the row belongs to:

perform pg_notify(
  channel_name('chatMessages:roomId=' || r.roomId),
  json_build_object(...)::text
);

The partition key is <field>=<value>, with several fields sorted by name and joined with commas: roomId=42, or roomId=42,teamId=7. So the whole channel for one room is chatMessages:roomId=42, which is what the channel function returns for that view and what the trigger has to hash. A browser watching room A never receives room B. On an update that moves a row between partitions, notify both, a delete on the old channel and an insert on the new one, which is what the framework's own writes do.

Check it

Open the page, then write from outside the app:

elements db -c "insert into pings (body) values ('from psql')"

The row appears in the open page. Without the trigger it does not, and nothing reports an error: the write succeeds, the page is simply stale.

elements db -c is raw psql, so write the column names the database has: room_id, not roomId. The camelCase folding is a migration and inline-SQL convenience, and a camelCase name here is just an unknown column.

Related

• 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.