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

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

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

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

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

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