elements
Manual Recipes Bidirectional Partition

Bidirectional Partition

elements man recipes/bidirectional-partition Read as markdown

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: one LiveTable on the table, opened on two partitions ({ senderId } and { recipientId }), with a Postgres trigger that notifies both partition channels on every change. The page template merges both views 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,
    'data', rowToJson(row)
  )::text;

  perform pgNotify(channelName(format('dm:senderId=%s', row.senderId)), payload);
  perform pgNotify(channelName(format('dm:recipientId=%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 on the sender's partition channel and once on the recipient's. The channelName(...) SQL function hashes the channel name the same way the Elements runtime does, so the views below hear the notifications.

The two Idx indexes back the partition selects each view runs on first paint.

Page setup

elements create page inbox

The LiveTable, 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 opens two partitions of the table for the current user and hands both views to the template; the template merges them into one chronological list.

app/pages/inbox/services.ts:

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

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

export let directMessages: LiveTable<DirectMessage> = new LiveTable<DirectMessage>({
  channel: (partition) => `dm:${partition}`,
  insert: (item) => {
    session.isLoggedInOrThrow();

    if (item.senderId !== session.getOrThrow('userId')) {
      throw new ForbiddenError();
    }

    return directMessages.insert(item);
  },
  update: () => {
    throw new ForbiddenError();
  },
  delete: (item) => {
    session.isLoggedInOrThrow();

    if (item.senderId !== session.getOrThrow('userId')) {
      throw new ForbiddenError();
    }

    return directMessages.delete(item);
  },
});

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

One declaration backs both feeds. channel receives the encoded partition, senderId=<id> or recipientId=<id>, and names the channel dm:<partition>, which is what the trigger above publishes on. The declaration carries an explicit LiveTable<DirectMessage> annotation because its own initializer refers to it.

insert requires a signed-in session (isLoggedInOrThrow throws AuthError otherwise), then accepts a row only when senderId is the calling user. delete makes the same two checks. update is refused outright. A user cannot put a message in someone else's inbox, cannot edit a message, and cannot delete one they received. directMessages.insert(item) inside the handler is the raw auto-SQL on the declaration; the handler only gates it.

An insert through the app broadcasts on the channel of the partition it came through, the sender's. The trigger then notifies both channels. The sender's view hears the row twice and keeps one, because the browser dedupes by id. The recipient's view hears it once, from the trigger.

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

app/pages/inbox/index.ts:

import { session } from "@elements/app";
import inbox from "./template";
import { directMessages } from "./services";

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

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

  return new inbox({
    myUserId,
    sent: directMessages.view({ senderId: myUserId }),
    received: directMessages.view({ recipientId: myUserId }),
  });
}

The route opens two partitions for the current user. view({ senderId: myUserId }) is the messages this user sent; view({ recipientId: myUserId }) is the messages this user received. The template gets both views plus the user's own id for the "is this mine?" check. The route is the authorization: it only ever opens the caller's own partitions.

app/pages/inbox/template.html:

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

function merged(sent: LiveView<DirectMessage>, received: LiveView<DirectMessage>): DirectMessage[] {
  return [...sent, ...received].sort((a, b) => +a.createdAt - +b.createdAt);
}

function onSend(
  sent: LiveView<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: LiveView<DirectMessage>,
       received: LiveView<DirectMessage>,
       private recipient: { value: string } = { value: "" },
       private body: { value: string } = { value: "" },
       private error: { value: string } = { value: "" })>
  <main>
    <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>
  </main>
</html>

merged(sent, received) spreads both views into one array and sorts it by createdAt. It runs inside the loop's expression, and iterating a view takes a dependency, so a row entering either partition re-runs the merge. The e:for then reconciles the new array against what is on screen by id: the new row is inserted and the rows already there keep their DOM and their state.

The insert payload carries senderId explicitly even though the partition would fill it. The insert handler compares it to the session, and the optimistic row is painted from exactly this object.

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 through the sent view.

Routes

Register the page in index.ts:

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

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

Notes

  • Why a trigger. An insert through the app broadcasts on one channel, the partition the view was opened on. The recipient is watching a different partition of the same table and would not hear it. The trigger publishes to both channels in one transaction, and it fires on every write from any source (app inserts, psql writes, jobs, other services). The two sources coexist: the browser dedupes inserts by id, and update and delete are idempotent.
  • Why two partitions. A single whole-table view with .filter((m) => m.senderId === me || m.recipientId === me) in the template would deliver every message in the database to every browser, because the channel is the table itself. A partition is a column equality, and bidirectional ownership is two of them, so it is two views.
  • Auth integrity in the handler. insert checks that senderId === session.getOrThrow('userId'), which stops a client from spoofing the sender. The recipient is unconstrained, which matches DM semantics (anyone can message anyone). Tighten with a sql lookup that throws ForbiddenError 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 partitions of one LiveTable and a trigger that publishes to three channels. Storage and indexes scale linearly with the number of parties.
  • Deleting a message. A delete through sent 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: the handler refuses a row whose senderId is not theirs.