Lesson 7 · Authorization — what may you do?

Locations & access_paths

Permissions aren't just "what" — they're "where." A tree of locations, encoded so one grant covers a whole branch.

Your win: explain how the location hierarchy is modeled, what an access_path is, and how a single permission grant at one location automatically covers its entire subtree — with one indexed string comparison.

Locations form a tree

An organization is a hierarchy of places: the org at the root, then brands, regions, centers, classrooms. The locations table models it with a self-referencing parent pointer:

migrations/bob/1124_migrate.up.sql + 1144 (parent_location_id)
locations( location_id, name, parent_location_id, access_path, location_type, resource_path )
Manabie (org) access_path = "org" ├── Brand A access_path = "org/A" │ ├── Center A1 access_path = "org/A/A1" │ └── Center A2 access_path = "org/A/A2" └── Brand B access_path = "org/B" └── Center B1 access_path = "org/B/B1"

(There's a parallel tree, location_types, describing the kinds of place — org, brand, center — but the access model runs on locations.)

The trick: access_path is a materialized path

Notice the access_path column above. Rather than store only the parent pointer, each location also stores its full ancestry as a string — the location_ids from the root down to itself, joined by /. This is the materialized path model of hierarchical data.1 It's computed by a recursive walk:

migrations/bob/1157_migrate.up.sql (the backfill)
WITH RECURSIVE with_locations AS (
    SELECT location_id, location_id::TEXT AS access_path    -- root: path = own id
    FROM locations WHERE parent_location_id IS NULL
  UNION ALL
    SELECT lo.location_id, (wl.access_path || '/' || lo.location_id)  -- child: parent path + "/" + own id
    FROM with_locations wl JOIN locations lo ON lo.parent_location_id = wl.location_id
) UPDATE locations SET access_path = with_locations.access_path FROM with_locations WHERE …

Why materialized path: the subtree is one prefix match

Here's the payoff, and it's the single most important idea in this lesson. To find every location in the subtree under some location L, you don't recurse — you ask for every location whose access_path starts with L's path:

the pattern used everywhere (e.g. granted_permission.go:29)
l1.access_path ~~ (l.access_path || '%')      -- ~~ is Postgres LIKE
-- "org/A%" matches org/A, org/A/A1, org/A/A2 — the whole Brand-A branch, in one indexed scan
The idea to carry from this lesson A permission granted at location L automatically covers L and everything beneath it, because "the subtree of L" is exactly "rows whose access_path begins with L's." One grant at "Brand A" reaches every center in Brand A — no per-center rows, no recursive query, just a prefix LIKE (indexed with text_pattern_ops). Grant high, inherit down.
Why not adjacency list or a closure table? A plain parent pointer (adjacency list) needs a recursive query to get a subtree; a closure table needs an extra row per ancestor-descendant pair. Materialized path trades a little denormalization (you must recompute paths when the tree moves) for the cheapest possible subtree read — a single indexed prefix scan.1 For an authorization check that runs on every request, that trade is worth it.

Which locations is a user attached to?

Two tables connect users to the tree, for two different purposes:

TableMeaning
granted_role_access_pathwhich location a role grant applies to (Lesson 6) — where a permission is effective
user_access_pathswhich locations a user belongs to (their home places) — migrations/bob/1147

Both carry an access_path, so both participate in the same prefix-match subtree logic. In Lesson 8 we combine them: "does this user hold permission P on a location that covers the data they're asking for?"

Read this next

Storing hierarchical data — the materialized path

The tree model this uses, and why prefix-matched paths beat recursion for subtree reads.

→ SitePoint — storing hierarchical data
→ Materialized path, explained · in-repo migrations/bob/1124,1144,1157

Check yourself (from memory)

Q1. What does a location's access_path store?

The materialized path: root-to-node ids joined by /, e.g. org/A/A1. Parent pointer alone would need recursion.

Q2. How do you find every location in the subtree under location L?

The subtree of L = rows whose path starts with L's path. One indexed prefix LIKE (~~), no recursion.

Q3. A permission is granted at "Brand A". Which locations does it cover?

Grant high, inherit down: the subtree of Brand A is every location whose path starts with Brand A's path. One grant, whole branch.
Recall: the location tree + access_path + subtree scoping.
model + the trick, then reveal
Locations = a tree (org → brand → center → …) via parent_location_id (migrations/bob/1124,1144); location_types is a parallel tree of place-kinds. access_path = the materialized path: each location stores its full ancestry as a /-joined string of ids (org/A/A1), backfilled by a recursive CTE (1157). Subtree = prefix match: the subtree of L is every location where access_path ~~ (L.access_path || '%') (Postgres LIKE, indexed text_pattern_ops) — one grant at "Brand A" covers the whole branch, no recursion. Beats adjacency list (needs recursion) / closure table (extra rows). Two user↔location tables: granted_role_access_path (where a grant is effective) and user_access_paths (where a user belongs).
🎯 Interview one-liner "How do you scope permissions to a location hierarchy?" → "Each location stores a materialized path — its ancestry as a slash-joined id string. A permission granted at a node covers its whole subtree, found with a single indexed prefix LIKE instead of a recursive query. Grant at the brand, inherit at every center."
Last lesson of Part 2: putting it together — resolving a permission to its granted locations and gating a request by them. Ask me if materialized paths vs the alternatives need another pass.

1. Hierarchical data models, materialized path. In-repo: migrations/bob/1124,1144,1147,1157.