Bidirectional Scope

Some rows belong to two parties at once. A direct message has a sender and a recipient; a friendship has both members; a trade has a buyer and a seller. Either party should see the row in their feed. The pattern: two scoped LiveTable instances on the same database table, scoped by different columns (senderId, recipientId), with a Postgres trigger that notifies both channels on every change. The page template merges both instances into one chronological list.

The recipe is direct messages. A user lands on /inbox and sees every message they sent or received, ordered by time, with new messages from either side appearing live.

Migration

elements create migration 'add direct messages' -tables=users,directMessages

app/migrations/<timestamp>-add-direct-messages.migration.sql:

-- add direct messages

-- Auto-update updatedAt on row changes.
create or replace function touchUpdatedAt()
returns trigger
language plpgsql
as $$
begin
  new.updatedAt = now();
  return new;
end;
$$;

create table users (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now(),
  handle text not null unique,
  passwordHash text not null
);

create trigger usersTouchUpdatedAt
  before update on users
  for each row execute function touchUpdatedAt();

create table directMessages (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now(),
  senderId uuid not null references users(id) on delete cascade,
  recipientId uuid not null references users(id) on delete cascade,
  body text not null
);

create index directMessagesSenderIdIdx on directMessages (senderId);
create index directMessagesRecipientIdIdx on directMessages (recipientId);

create trigger directMessagesTouchUpdatedAt
  before update on directMessages
  for each row execute function touchUpdatedAt();

create or replace function notifyDirectMessages() returns trigger as $$
declare
  payload text;
  row directMessages%rowtype;
begin
  row := case when tgOp = 'DELETE' then old else new end;

  payload := jsonBuildObject(
    'op', tgOp::text,
    'data', rowToJson(row)
  )::text;

  perform pgNotify(channelName(format('directMessages:sender:%s', row.senderId)), payload);
  perform pgNotify(channelName(format('directMessages:recipient:%s', row.recipientId)), payload);

  return row;
end;
$$ language plpgsql;

create trigger directMessagesNotify after insert or update or delete
  on directMessages for each row execute function notifyDirectMessages();

notifyDirectMessages fires on every insert, update, and delete. It calls pgNotify twice: once for the sender's channel and once for the recipient's channel. The channelName(...) SQL function hashes the channel name the same way the Elements runtime does, so app-side LiveTables that listen on the matching channel see the notifications.

The two Idx indexes back the scope-filtered selects each LiveTable runs on first paint.

Page setup

elements create page inbox

The two LiveTables, the DirectMessage interface, and the findUserId rpc are used only by the /inbox page, so they live in the page's own services.ts. The route binds both LiveTables to the current user and hands the scoped instances to the template; the template merges them into one chronological list.

app/pages/inbox/services.ts:

import { LiveTable, sql, session } from "@elements/app";

export interface DirectMessage {
  id: string;
  createdAt: Date;
  senderId: string;
  recipientId: string;
  body: string;
}

export let sentMessages = new LiveTable<DirectMessage>({
  table: "directMessages",
  scope: "senderId",
  channel: (sv) => `directMessages:sender:${sv}`,
  realtime: "db",
  sort: "createdAt asc",
  insert: { auth: (item, s) => item.senderId === s.getOrThrow('userId') },
});

export let receivedMessages = new LiveTable<DirectMessage>({
  table: "directMessages",
  scope: "recipientId",
  channel: (sv) => `directMessages:recipient:${sv}`,
  realtime: "db",
  sort: "createdAt asc",
  insert: { auth: () => false },
  update: { auth: () => false },
  delete: { auth: () => false },
});

/** @rpc */
export function findUserId(handle: string): string | null {
  return sql<{ id: string }>(
    `select id from users where handle = ${handle}`,
  ).first()?.id ?? null;
}

Both LiveTables back the same directMessages table (table: "directMessages" on each). They scope by different columns: sentMessages partitions on senderId, receivedMessages on recipientId. The explicit channel override gives each a deterministic Postgres channel name that the trigger above publishes to. realtime: "db" tells the LiveTable that the trigger is the broadcast source; the app-side notify on LiveTable.insert is disabled, so the trigger isn't double-firing.

sentMessages allows insert when the row's senderId is the calling user. receivedMessages disables all mutations: a user can't insert a message into someone else's inbox, can't edit a message they received, can't delete one. The integrity is the table's, not the user's.

findUserId is the lookup for the composer: type a handle, get the user id back, then sentMessages.insert(...) with the resolved id.

app/pages/inbox/index.ts:

import { session } from "@elements/app";
import inbox from "./template";
import { sentMessages, receivedMessages } from "./services";

export default function route(req, res) {
  session.isLoggedInOrThrow();

  let myUserId = session.getOrThrow('userId');

  return new inbox({
    myUserId,
    sent: sentMessages.scope(myUserId),
    received: receivedMessages.scope(myUserId),
  });
}

The route binds both LiveTables to the current user. sentMessages.scope(myUserId) shows messages this user sent; receivedMessages.scope(myUserId) shows messages this user received. The template gets two scoped handles plus the user's own id for the "is this mine?" check.

app/pages/inbox/template.html:

import "./style.css";
import { LiveTable } from "@elements/app";
import { DirectMessage, findUserId } from "./services";

function merged(sent: LiveTable<DirectMessage>, received: LiveTable<DirectMessage>): DirectMessage[] {
  let combined = sent.map(m => m).concat(received.map(m => m));
  combined.sort((a, b) => a.createdAt.getTime() - b.createdAt.getTime());
  return combined;
}

function onSend(
  sent: LiveTable<DirectMessage>,
  myUserId: string,
  recipientHandle: { value: string },
  body: { value: string },
  error: { value: string },
) {
  let recipientId = findUserId(recipientHandle.value.trim());

  if (!recipientId) {
    error.value = "no user with that handle";
    return;
  }

  sent.insert(
    { senderId: myUserId, recipientId, body: body.value },
    () => {
      body.value = "";
      recipientHandle.value = "";
      error.value = "";
    },
  );
}

<html class="inbox"
      (myUserId: string,
       sent: LiveTable<DirectMessage>,
       received: LiveTable<DirectMessage>,
       private recipient: { value: string } = { value: "" },
       private body: { value: string } = { value: "" },
       private error: { value: string } = { value: "" })>
  <h1>inbox</h1>

  <ul class="thread">
    <li e:for={m of merged(sent, received)} class={m.senderId === myUserId ? "out" : "in"}>
      <span class="direction">{m.senderId === myUserId ? "→" : "←"}</span>
      <span class="body">{m.body}</span>
    </li>
    <li e:if={sent.length === 0 && received.length === 0}>no messages yet.</li>
  </ul>

  <form onsubmit={() => onSend(sent, myUserId, recipient, body, error)}>
    <input type="text" value={recipient.value} placeholder="recipient handle" required>
    <input type="text" value={body.value} placeholder="message" required>
    <p e:if={error.value} class="error">{error.value}</p>
    <button type="submit">send</button>
  </form>
</html>

merged(sent, received) copies each scoped LiveTable into an array with .map() and sorts by createdAt. Like .find, .filter, and .some, .map() registers a reactive dependency, so any insert into either table re-runs the merge and re-renders the list. Spreading a LiveTable ([...sent]) would not: raw iteration is not reactive, so reach for the query methods when you need the merge to stay live.

The e:for iterates a plain array (the merged result), so the runtime cannot patch per-row the way it does when iterating a LiveTable directly. For an inbox-scale list this is fine; the re-render cost is bounded by total message count, not by churn rate. For a much larger merged feed, prefer two separate iterations side by side or a unified scope (one column, one LiveTable) over the two-scope merge.

Each rendered row class (out / in) is derived from m.senderId === myUserId. The arrow shows direction. The composer at the bottom uses findUserId to resolve the recipient's handle, then inserts into the sender-scoped table.

Routes

Register the page in index.ts:

import inbox from "#app/pages/inbox";

// ...
app.route("/inbox", inbox);

Notes

• Why realtime: "db". With app-side realtime, only the app instance that called LiveTable.insert(...) broadcasts. A two-channel notification (sender:X and recipient:Y) can't ride on one app-side broadcast cleanly. The Postgres trigger fires on every change to the table from any source (app inserts, psql writes, jobs, other services) and publishes to both channels in one transaction, so both parties see the row regardless of who wrote it or how.
• Why two LiveTables. A single LiveTable with where senderId = X or recipientId = X would deliver every message in the database to every browser, because the broadcast channel is the table itself. The scope column has to be a real column for the partition to make sense; bidirectional ownership doesn't fit a single scope.
• Auth integrity at the database. insert.auth on sentMessages checks that senderId === session.getOrThrow('userId'), which stops a client from spoofing the sender. The recipient is unconstrained on the app side, which matches DM semantics (anyone can message anyone). Tighten with if (!sql(...)) { throw new AuthError(); } if you want only mutual contacts to be reachable.
• Three-party rows. The same shape extends. A trade row with buyer, seller, and broker becomes three LiveTables, three scope columns, and a trigger that publishes to three channels. Storage and indexes scale linearly with the number of parties.
• Deleting a message. A delete on sentMessages.scope(myUserId) runs the trigger, which publishes to both the sender's and the recipient's channels, so both sides see the deletion immediately. The recipient cannot initiate the delete; their LiveTable has delete.auth: () => false.