Group permissions

This recipe shows how to build a workspace where users have different levels of access depending on their role. The same pattern applies to any group-like concept — teams, projects, channels, organisations.

Roles at a glance

Schema

Three tables: the workspace itself, a members join table that records each user's role, and the documents that belong to the workspace.

schema.ts
const schema = {
  workspaces: s.table(
    {
      name: s.string(),
    },
    {
      members: s.reverse("workspaceMembers", "workspace"),
      documents: s.reverse("documents", "workspace"),
    },
  ),
  workspaceMembers: s.table(
    {
      workspaceId: s.uuid(),
      user_id: s.uuid(),
      role: s.enum("reader", "writer", "contributor", "admin"),
    },
    { workspace: s.rel("workspaces", "workspaceId") },
  ),
  documents: s.table(
    {
      title: s.string(),
      content: s.string(),
      workspaceId: s.uuid(),
    },
    { workspace: s.rel("workspaces", "workspaceId") },
  ),
};
​
type AppSchema = s.Schema<typeof schema>;
export const app: s.App<AppSchema> = s.defineApp(schema);

Permissions

permissions.ts
type Role = "reader" | "writer" | "contributor" | "admin";
​
s.definePermissions(app, ({ policy, session, anyOf, allOf }) => {
  // Re-usable helpers to improve readability.
  const isMember = (workspaceId: RowRefValue) =>
    policy.workspaceMembers.exists.where({ workspaceId, user_id: session.user.account });
​
  const hasRole = (workspaceId: RowRefValue, role: Role) =>
    policy.workspaceMembers.exists.where({ workspaceId, user_id: session.user.account, role });
​
  const isAdmin = (workspaceId: RowRefValue) => hasRole(workspaceId, "admin");
​
  // --- documents ---
​
  policy.documents.allowRead.where((doc) => isMember(doc.workspaceId));
​
  policy.documents.allowInsert.where((doc) =>
    anyOf([
      hasRole(doc.workspaceId, "writer"),
      hasRole(doc.workspaceId, "contributor"),
      hasRole(doc.workspaceId, "admin"),
    ]),
  );
​
  // Writers and admins can edit any document; contributors can only edit their own
  policy.documents.allowUpdate.where((doc) =>
    anyOf([
      hasRole(doc.workspaceId, "writer"),
      hasRole(doc.workspaceId, "admin"),
      allOf([
        { "$createdBy.account": session.user.account },
        hasRole(doc.workspaceId, "contributor"),
      ]),
    ]),
  );
​
  // Writers and admins can delete any document; contributors can delete their own
  policy.documents.allowDelete.where((doc) =>
    anyOf([
      hasRole(doc.workspaceId, "writer"),
      isAdmin(doc.workspaceId),
      allOf([
        { "$createdBy.account": session.user.account },
        hasRole(doc.workspaceId, "contributor"),
      ]),
    ]),
  );
​
  // --- workspaces ---
​
  policy.workspaces.allowRead.where((workspace) => isMember(workspace.id));
  policy.workspaces.allowInsert.always();
  policy.workspaces.allowUpdate.where((workspace) => isAdmin(workspace.id));
  policy.workspaces.allowDelete.where((workspace) => isAdmin(workspace.id));
​
  // --- workspaceMembers ---
​
  policy.workspaceMembers.allowRead.where((member) => isMember(member.workspaceId));
​
  // Admins can add members; workspace creators can bootstrap themselves as the first admin
  policy.workspaceMembers.allowInsert.where((member) =>
    anyOf([
      isAdmin(member.workspaceId),
      allOf([
        { user_id: session.user.account, role: "admin" },
        policy.workspaces.exists.where({
          id: member.workspaceId,
          "$createdBy.account": session.user.account,
        }),
      ]),
    ]),
  );
​
  policy.workspaceMembers.allowUpdate.where((member) => isAdmin(member.workspaceId));
​
  // Admins can remove any member; members can leave on their own
  policy.workspaceMembers.allowDelete.where((member) =>
    anyOf([isAdmin(member.workspaceId), { user_id: session.user.account }]),
  );
});

• Contributor edit access uses allOf to require both "$createdBy.account": session.user.account (the row belongs to this author) and a matching contributor membership. Writers and admins bypass the creator check entirely.
• Bootstrap insert: the second branch of allowInsert lets the workspace creator add themselves as the first admin — otherwise isAdmin would block everyone, since there are no members yet.
• Leave on your own: members can delete their own membership row regardless of role. Admins can remove anyone.

See Permissions for more on exists.where, anyOf, allOf, and $createdBy.

Creating a workspace

export async function createWorkspace(
  db: ReturnType<typeof useDb>,
  name: string,
  creatorId: string,
) {
  const { value: workspace } = await db.insert(app.workspaces, { name });
  // Add the creator as admin immediately so they can manage the workspace
  db.insert(app.workspaceMembers, {
    workspaceId: workspace.id,
    user_id: creatorId,
    role: "admin",
  });
  return workspace;
}

Managing members

Adding a member

export async function addMember(
  db: ReturnType<typeof useDb>,
  workspaceId: string,
  userId: string,
  role: "reader" | "writer" | "contributor" | "admin",
) {
  await db.insert(app.workspaceMembers, { workspaceId, user_id: userId, role });
}

Listing members

WorkspaceMembers.tsx
export function WorkspaceMembers({ workspaceId }: { workspaceId: string }) {
  const { data: members = [], isLoading } = useAll(app.workspaceMembers.where({ workspaceId }));
​
  if (isLoading) return <p>Loading…</p>;
​
  return (
    <ul>
      {members.map((member) => (
        <li key={member.id}>
          {member.user_id} — {member.role}
        </li>
      ))}
    </ul>
  );
}

Changing a member's role

export async function changeRole(
  db: ReturnType<typeof useDb>,
  memberId: string,
  newRole: "reader" | "contributor" | "writer" | "admin",
) {
  await db.update(app.workspaceMembers, memberId, { role: newRole });
}

Removing a member

export async function removeMember(
  db: ReturnType<typeof useDb>,
  workspaceId: string,
  userId: string,
) {
  const member = await db.one(app.workspaceMembers.where({ workspaceId, user_id: userId }));
  if (member) await db.delete(app.workspaceMembers, member.id);
}

Querying documents

WorkspaceDocuments.tsx
export function WorkspaceDocuments({ workspaceId }: { workspaceId: string }) {
  const { data: docs, isLoading, error } = useAll(app.documents.where({ workspaceId }));
​
  if (isLoading) return <p>Loading…</p>;
  if (error) return <p>Something went wrong!</p>;
​
  return (
    <ul>
      {docs.map((doc) => (
        <li key={doc.id}>{doc.title}</li>
      ))}
    </ul>
  );
}