# Team-Scoped Table

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

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

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

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

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

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

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

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

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

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

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

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

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

`app/pages/team/template.html`:

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

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