# Team-Partitioned Table

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

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

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

```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[])>
  <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

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

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

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

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

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

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