# 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:

```ts 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

```bash Terminal
elements create migration 'notify pings'
```

```sql 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:

```sql 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:

```bash 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`.

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