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.table({
    title: s.string(),
    done: s.boolean(),
    projectId: s.ref("projects"),
  }),
  comments: s.table({
    body: s.string(),
    taskId: s.ref("tasks"),
  }),
};

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: session.user_id });
  policy.projects.allowInsert.always();
  policy.projects.allowUpdate.where({ $createdBy: session.user_id });
  policy.projects.allowDelete.where({ $createdBy: session.user_id });

  // Tasks: inherit from project
  policy.tasks.allowRead.where(allowedTo.read("projectId"));
  policy.tasks.allowInsert.where(allowedTo.read("projectId"));
  policy.tasks.allowUpdate.where(allowedTo.update("projectId"));
  policy.tasks.allowDelete.where(allowedTo.delete("projectId"));

  // Comments: inherit from task
  policy.comments.allowRead.where(allowedTo.read("taskId"));
  policy.comments.allowInsert.where(allowedTo.read("taskId"));
  policy.comments.allowUpdate.where({ $createdBy: session.user_id });
  policy.comments.allowDelete.where({ $createdBy: session.user_id });
});

The allowedTo.read("projectId") argument is the FK column name. See Permissions for maxDepth and recursive inheritance.

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 tasks = useAll(app.tasks.where({ projectId }).orderBy("$createdAt", "desc"));

  if (!tasks) return <p>Loading…</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 } = db.insert(app.projects, {
      name: "Website redesign",
    });

    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.

Nested data with permission inheritance

Schema

Inherited permissions

Querying

Inserting

On this page