elements
Manual Recipes Team-Partitioned Table

Team-Partitioned Table

elements man recipes/team-partitioned-table Read as markdown

How to gate a LiveTable's partition by group membership instead of by URL secrecy. Two layers of authorization carry the same check. The route guard verifies the signed-in user belongs to the team before calling view({ teamId }) on the LiveTable. The LiveTable's insert, update, and delete handlers verify membership again on every mutation. A user removed from a team mid-session loses access on their next mutation, not their next page load.

The data shape underneath is a tasks table with a teamId column and a teamMembers join table holding the user-to-team relationships. Opening the view on { teamId } partitions the broadcast channel per team as a byproduct of the same object.

Migration

elements create migration 'add teams and tasks' -tables=users,teams,teamMembers,tasks

app/migrations/<timestamp>-add-teams-and-tasks.migration.sql:

-- add teams and tasks

-- 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 teams (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now(),
  name text not null
);

create trigger teamsTouchUpdatedAt
  before update on teams
  for each row execute function touchUpdatedAt();

create table teamMembers (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now(),
  userId uuid not null references users(id) on delete cascade,
  teamId uuid not null references teams(id) on delete cascade,
  unique (userId, teamId)
);

create trigger teamMembersTouchUpdatedAt
  before update on teamMembers
  for each row execute function touchUpdatedAt();

create table tasks (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now(),
  teamId uuid not null references teams(id) on delete cascade,
  title text not null,
  done boolean not null default false
);

create index tasksTeamIdIdx on tasks (teamId);

create trigger tasksTouchUpdatedAt
  before update on tasks
  for each row execute function touchUpdatedAt();

teamMembers is the many-to-many between users and teams. The unique (userId, teamId) constraint prevents duplicate memberships. tasksTeamIdIdx keeps per-team queries fast as the table grows. The on delete cascade on tasks.teamId cleans up tasks when a team is deleted.

Membership helpers

app/shared/services/teams.ts:

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

export function isTeamMember(userId: string, teamId: string): boolean {
  return !sql(
    `select 1 from teamMembers where userId = ${userId} and teamId = ${teamId}`,
  ).empty();
}

export function isTeamMemberOrThrow(teamId: string) {
  session.isLoggedInOrThrow();

  if (!isTeamMember(session.getOrThrow('userId'), teamId)) {
    throw new ForbiddenError("not a member of this team");
  }
}

isTeamMember is the boolean reader. isTeamMemberOrThrow is the guard, used by the route and by every handler on the LiveTable. It first calls session.isLoggedInOrThrow() so an unauthenticated request gets AuthError (401), then it reads the user id and throws ForbiddenError (403) if the signed-in user is not a member of the target team.

LiveTable

app/shared/services/tasks.ts:

import { LiveTable } from "@elements/app";
import { isTeamMemberOrThrow } from "#app/shared/services/teams";

export interface Task {
  id: string;
  createdAt: Date;
  teamId: string;
  title: string;
  done: boolean;
}

export let tasks: LiveTable<Task> = new LiveTable<Task>({
  insert: (item) => {
    isTeamMemberOrThrow(item.teamId!);
    return tasks.insert(item);
  },
  update: (item) => {
    isTeamMemberOrThrow(item.teamId);
    return tasks.update(item);
  },
  delete: (item) => {
    isTeamMemberOrThrow(item.teamId);
    return tasks.delete(item);
  },
});

The route below opens the table with view({ teamId }). The partition names the channel per team and fills the column on insert, so item.teamId is set by the time the insert handler runs (the ! says so to the type checker, since an insert payload is Partial<Task>). Every mutation is also checked against the partition the view was opened on; a browser cannot mutate a row in a team it does not hold a view for, regardless of the handler. The handlers add a second check: even with a view, isTeamMemberOrThrow runs on every mutation, so a member who is removed mid-session loses access on the next mutation rather than on the next page load. Each handler then returns the raw auto-SQL on the declaration, which is why the declaration carries an explicit LiveTable<Task> annotation: its initializer refers to it.

Home page

elements create page home

The home page shows the signed-in user's teams. Each team links to /teams/:id.

app/pages/home/index.ts:

import { sql, session } from "@elements/app";
import home from "./template";
import { Team } from "./services";

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

  let teams = sql<Team>(
    `select t.id, t.name
     from teams t
     join teamMembers m on m.teamId = t.id
     where m.userId = ${session.getOrThrow('userId')}
     order by t.name`,
  ).all();

  return new home({ teams });
}

app/pages/home/services.ts:

export interface Team {
  id: string;
  name: string;
}

The home page has no RPC; services.ts exists only to hold the shared Team interface so both index.ts and template.html can import it without an index-template cycle.

app/pages/home/template.html:

import "./style.css";
import { Team } from "./services";

<html class="home" (teams: Team[])>
  <main>
    <h1>your teams</h1>

    <ul class="teams">
      <li e:for={t of teams}>
        <a href={`/teams/${t.id}`}>{t.name}</a>
      </li>
      <li e:if={teams.length === 0}>
        no teams yet. ask an admin to add you to one.
      </li>
    </ul>
  </main>
</html>

Team page

elements create page team

The team page loads the team's name from the database, opens the team's partition of tasks, and passes both to the template.

app/pages/team/index.ts:

import { sql } from "@elements/app";
import team from "./template";
import { Team } from "./services";
import { isTeamMemberOrThrow } from "#app/shared/services/teams";
import { tasks } from "#app/shared/services/tasks";

export default function route(req, res) {
  let teamId = req.params.id;

  isTeamMemberOrThrow(teamId);

  let row = sql<Team>(
    `select id, name from teams where id = ${teamId}`,
  ).firstOrThrow("team not found");

  return new team({ team: row, tasks: tasks.view({ teamId }) });
}

app/pages/team/services.ts:

export interface Team {
  id: string;
  name: string;
}

app/pages/team/template.html:

import "./style.css";
import { LiveView } from "@elements/app";
import { Task } from "#app/shared/services/tasks";
import { Team } from "./services";

function insert(t: LiveView<Task>, draft: { value: string }) {
  t.insert(
    { title: draft.value, done: false },
    () => draft.value = "",
  );
}

function toggle(t: LiveView<Task>, item: Task) {
  t.update({ ...item, done: !item.done });
}

function remove(t: LiveView<Task>, item: Task) {
  t.delete(item);
}

<html class="team"
      (team: Team,
       tasks: LiveView<Task>,
       private draft: { value: string } = { value: "" })>
  <main>
    <h1>{team.name}</h1>

    <ul class="tasks">
      <li e:for={item of tasks.sort((a, b) => +a.createdAt - +b.createdAt)}
          class={item.done && "done"}>
        <button class="toggle" onclick={() => toggle(tasks, item)}>{item.done ? "☑" : "☐"}</button>
        <span class="text">{item.title}</span>
        <button class="remove" onclick={() => remove(tasks, item)}>×</button>
      </li>
    </ul>

    <form onsubmit={() => insert(tasks, draft)}>
      <input type="text" value={draft.value} placeholder="add a task" required>
      <button type="submit">add</button>
    </form>
  </main>
</html>

tasks.insert({ title, done: false }) does not pass teamId explicitly. The partition fills the column from the view, so the inserted row gets the correct team id automatically, and passing a different teamId would throw PartitionMismatchError.

Routes

Register the pages in index.ts:

import home from "#app/pages/home";
import team from "#app/pages/team";

// ...
app.route("/", home);
app.route("/teams/:id", team);

Notes

  • Seeding users and teams. This recipe assumes signin and signup come from the authentication recipe, plus a way to create teams and assign membership. For development, the simplest path is to run insert into teams (name) values ('engineering') and insert into teamMembers (userId, teamId) select id, (select id from teams where name='engineering') from users where handle='you' in psql.
  • Cross-team data isolation. A user visits /teams/A and /teams/B as separate page loads. Each route opens a separate partition. Broadcasts from team A do not flow to the user's view of team B.
  • Adding a per-task assignee. Add assigneeId uuid references users(id) to the tasks table; auto-SQL picks the column up. The handlers still gate on team membership; row-level "only the assignee can mark it done" layers on top as an extra check inside the update handler.
  • Member roles. To distinguish admins from regular members, add a role column to teamMembers with an enum like the userRole pattern in elements man recipes admin-roles. The membership query checks the role: where userId = ... and teamId = ... and role = 'admin'.