Roles and What They Mean

What roles represent

A role is a label that groups a set of capabilities. Instead of checking “is this user Alice?” you check “does this user have the editor role in this organization?”

For our notes app, we define three roles:

Owner: Full control. Can create, read, edit, and delete notes. Can invite and remove members. Can change members’ roles. Can delete the organization.

Editor: Can create, read, and edit notes. Cannot delete notes. Cannot manage members.

Viewer: Can read notes. Cannot create, edit, or delete. Cannot manage members.

Roles are stored in the memberships table

The memberships table from the project setup connects users to organizations with a role:

CREATE TABLE memberships (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  org_id TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'viewer',
  UNIQUE(user_id, org_id)
);

A user’s role is not a property of the user — it is a property of the relationship between the user and the organization. Alice is an owner of Acme Corp but a viewer of Globex Inc. Same user, different roles in different contexts.

Looking up a user’s role

Create src/membership.ts:

// src/membership.ts
import db from "./db.js";

export interface Membership {
  id: string;
  userId: string;
  orgId: string;
  role: string;
}

export function getMembership(userId: string, orgId: string): Membership | undefined {
  return db
    .prepare(
      "SELECT id, user_id as userId, org_id as orgId, role FROM memberships WHERE user_id = ? AND org_id = ?",
    )
    .get(userId, orgId) as Membership | undefined;
}

export function getUserOrgs(userId: string): Membership[] {
  return db
    .prepare(
      "SELECT id, user_id as userId, org_id as orgId, role FROM memberships WHERE user_id = ?",
    )
    .all(userId) as Membership[];
}

getMembership looks up a user’s role in a specific organization. If they have no membership, they have no access. getUserOrgs lists all organizations a user belongs to.

The role as a gate

In the next lesson, we will build a requireRole function that uses getMembership to check access before allowing an action. But the important concept here is: the role is data, not code.

We do not have an isAdmin() function hard-coded into the app. The role comes from the database. This means:

Roles can be changed without a code deploy (update the membership row)
A user can have different roles in different organizations
New roles can be added without changing the code (later, in the custom permissions lesson)

Exercises

Exercise 1: Query the memberships table to find all of Alice’s memberships. What role does she have in each organization?

# Using the sqlite3 CLI (or add a debug route)
sqlite3 app.db "SELECT m.role, o.name FROM memberships m JOIN organizations o ON m.org_id = o.id WHERE m.user_id = 'user-alice'"

Exercise 2: Try to insert a membership that gives Bob a role in Globex. What SQL would you use? (Answer: INSERT INTO memberships (id, user_id, org_id, role) VALUES ('mem-5', 'user-bob', 'org-globex', 'editor'))