elements
Manual Recipes Team-Scoped Table

Team-Scoped Table

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

How to gate a LiveTable's scope 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 .scope() on the LiveTable. The LiveTable's per-mutation auth callbacks verify membership again on every insert, update, and delete. 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. The LiveTable's scope: "teamId" partitions the broadcast channel per team as a byproduct of the scope binding.

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, used by the LiveTable's auth callbacks. isTeamMemberOrThrow is the route guard. It first calls session.isLoggedInOrThrow() so an unauthenticated request gets a 401, then it 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 { isTeamMember } from "#app/shared/services/teams";

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

export let tasks = new LiveTable<Task>({
  scope: "teamId",
  sort: "createdAt asc",
  insert: { auth: (item, s) => isTeamMember(s.getOrThrow('userId'), item.teamId) },
  update: { auth: (item, s) => isTeamMember(s.getOrThrow('userId'), item.teamId) },
  delete: { auth: (item, s) => isTeamMember(s.getOrThrow('userId'), item.teamId) },
});

scope: "teamId" partitions the broadcast channel per team and auto-fills the column on insert. Every mutation is also validated against the bound scope value; a browser cannot mutate a row in a team it does not have a scoped handle for, regardless of the auth callback. The auth callbacks add a second check: even with a scoped handle, the membership check 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.

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[])>
  <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>
</html>

Team page

elements create page team

The team page loads the team's name from the database, scopes the tasks LiveTable to the team, 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.scope(teamId) });
}

app/pages/team/services.ts:

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

app/pages/team/template.html:

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

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

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

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

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

  <ul class="tasks">
    <li e:for={item of tasks} 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>
</html>

tasks.insert({ title, done: false }) does not pass teamId explicitly. The LiveTable's scope: "teamId" auto-fills the column from the bound scope value, so the inserted row gets the correct team id automatically.

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 returns a separate scoped LiveTable. 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 and include it in the LiveTable's auto-fill. The auth callbacks still gate on team membership; row-level "only the assignee can mark it done" can layer on top with an extra check inside the update callback.
  • 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'.