Nested data with permission inheritance

Model a project/task/comment hierarchy with inherited permissions, queries, and multi-level inserts.

This recipe shows how to model a simple hierarchy, inherit permissions from parent rows, and query/insert at each level.

Schema

A simple schema with three tables linked by foreign keys. A project has tasks, and tasks have comments.

schema.ts
const schema = {
  projects: s.table(
    {
      name: s.string(),
    },
    { tasks: s.reverse("tasks", "project") },
  ),
  tasks: s.table(
    {
      title: s.string(),
      done: s.boolean(),
      projectId: s.uuid(),
    },
    { project: s.rel("projects", "projectId"), comments: s.reverse("comments", "task") },
  ),
  comments: s.table(
    {
      body: s.string(),
      taskId: s.uuid(),
    },
    { task: s.rel("tasks", "taskId") },
  ),
};
​
type AppSchema = s.Schema<typeof schema>;
export const app: s.App<AppSchema> = s.defineApp(schema);

Inherited permissions

Use allowedTo to inherit access from the parent row. If you can read a project, you can read its tasks, and if you can read a task, you can read its comments.

permissions.ts
s.definePermissions(app, ({ policy, allowedTo, session }) => {
  // Projects: only the creator
  policy.projects.allowRead.where({ "$createdBy.account": session.user.account });
  policy.projects.allowInsert.always();
  policy.projects.allowUpdate.where({ "$createdBy.account": session.user.account });
  policy.projects.allowDelete.where({ "$createdBy.account": session.user.account });
​
  // Tasks: inherit from project
  policy.tasks.allowRead.where(allowedTo.read("project"));
  policy.tasks.allowInsert.where(allowedTo.read("project"));
  policy.tasks.allowUpdate.where(allowedTo.update("project"));
  policy.tasks.allowDelete.where(allowedTo.delete("project"));
​
  // Comments: inherit from task
  policy.comments.allowRead.where(allowedTo.read("task"));
  policy.comments.allowInsert.where(allowedTo.read("task"));
  policy.comments.allowUpdate.where({ "$createdBy.account": session.user.account });
  policy.comments.allowDelete.where({ "$createdBy.account": session.user.account });
});

The allowedTo.read("project") argument is the relationship name explicitly declared on the tasks table, such as project: s.rel("projects", "projectId"). Stored column names and inferred aliases are not accepted. See Permissions for maxDepth and recursive inheritance.

Note

Projects use $createdBy instead of an explicit owner_id column. Jazz tracks who created each row automatically, so you can reference it in permissions without adding a column to your schema. Anyone can insert a project, and it will automatically be created with the appropriate $createdBy information.

Querying

ProjectTasks.tsx
export function ProjectTasks({ projectId }: { projectId: string }) {
  const {
    data: tasks,
    isLoading,
    error,
  } = useAll(app.tasks.where({ projectId }).orderBy("$createdAt", "desc"));
​
  if (isLoading) return <p>Loading…</p>;
  if (error) return <p>Something went wrong!</p>;
​
  return (
    <ul>
      {tasks.map((task) => (
        <li key={task.id}>{task.title}</li>
      ))}
    </ul>
  );
}

See Includes and relations for include(), reverse relations, and select().

Inserting

Insert from the top down — create the project first, then tasks referencing it.

CreateProject.tsx
export function CreateProject() {
  const db = useDb();
  const session = useSession();
​
  async function handleCreate() {
    const { value: project } = await db.insert(app.projects, {
      name: "Website redesign",
    });
​
    await db.insert(app.tasks, {
      title: "Design homepage",
      done: false,
      projectId: project.id,
    });
  }
​
  return <button onClick={handleCreate}>New project</button>;
}

Each insert executes locally and syncs in the background. Because permissions inherit downward, anyone who can access the project automatically gets access to its tasks and comments.