In a food-safety inspectorate, which tables and indexes let an inspector hold a supervisor role in one district only?

middlemust knowfreq 78%

answer

definitions apart from grants
the limit is a column
one role, many postings
principal plus unit is the lookup
unique triple makes revoke one row

basics

~20 s

Three definition tables — role, permission, role_permission — plus a grant table joining a principal to a role with the district on the grant row itself. The hot check reads grants by principal and district, so that pair is the composite index it needs.

solid answer

~50 s

Keep the definitions and the grants apart. role and permission hold the vocabulary, role_permission joins them, and a role_grant table joins a principal to a role. The limit — supervisor here, ordinary inspector there — goes on the grant row as a district_id column, not into a role called supervisor_district_7, because the alternative mints a role per unit and the role table stops describing the product. Uniqueness is (user_id, role_id, district_id), so granting twice is idempotent and revoking is deleting one row. At request time the server looks up grants for this principal in the district the request is operating on, expands them through role_permission, and tests membership; that lookup shape is why the index is composite on (user_id, district_id) rather than on user_id alone. How the district is derived from the request is a separate concern — this answer is about the rows that must exist for the answer to be derivable at all.

Keep the definitions and the grants apart. `role` and `permission` hold the vocabulary, `role_permission` joins them, and a `role_grant` table joins a principal to a role. The limit — supervisor here, ordinary inspector there — goes on the **grant row** as a `district_id` column, not into a role called `supervisor_district_7`, because the alternative mints a role per unit and the role table stops describing the product. Uniqueness is `(user_id, role_id, district_id)`, so granting twice is idempotent and revoking is deleting one row. At request time the server looks up grants for this principal in the district the request is operating on, expands them through `role_permission`, and tests membership; that lookup shape is why the index is composite on `(user_id, district_id)` rather than on `user_id` alone. How the district is derived from the request is a separate concern — this answer is about the rows that must exist for the answer to be derivable at all.

code

sql · 31 lines

sql

CREATE TABLE role (
  id          BIGINT PRIMARY KEY,
  key         TEXT NOT NULL UNIQUE,      -- 'inspector', 'area_supervisor'
  description TEXT NOT NULL
);

CREATE TABLE permission (
  id          BIGINT PRIMARY KEY,
  key         TEXT NOT NULL UNIQUE,      -- 'establishment:inspect'
  description TEXT NOT NULL
);

CREATE TABLE role_permission (
  role_id       BIGINT NOT NULL REFERENCES role(id),
  permission_id BIGINT NOT NULL REFERENCES permission(id),
  PRIMARY KEY (role_id, permission_id)
);

-- the posting: this role, held by this person, in this district
CREATE TABLE role_grant (
  id          BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES app_user(id),
  role_id     BIGINT NOT NULL REFERENCES role(id),
  district_id BIGINT NULL REFERENCES district(id),   -- NULL = service-wide
  granted_by  BIGINT NOT NULL REFERENCES app_user(id),
  granted_at  TIMESTAMPTZ NOT NULL,
  CONSTRAINT role_grant_unique UNIQUE (user_id, role_id, district_id)
);

-- the lookup the request-time check issues, in its column order
CREATE INDEX role_grant_by_principal_and_unit ON role_grant (user_id, district_id);

go deeper

for a junior

Know the three definition tables and the grant table, and that the district lives on the grant row rather than inside the role's name.

for a middle

Draw it: the joins, the unique key on principal, role and unit, and the composite index matching the lookup the request-time check actually issues.

for a senior

Talk about what the shape costs in production — the width of a national account's grant lookup, a second truth copied onto the user row, and a revocation that touches more than one row.

for a principal

Ask whether this shape can express next year's rule without a role per customer, and what it would cost to move off it while both models are read live.

The two halves of the schema

Split the tables into definitions (what roles exist and what they mean) and grants (who holds one, and where).

Definitions:

role — id, key (inspector, area_supervisor, national_administrator), description.
permission — id, key (establishment:inspect, report:sign_off), description.
role_permission — role_id, permission_id, primary key over the pair.

Grants:

role_grant — user_id, role_id, district_id (nullable for a role that is genuinely service-wide), granted_by, granted_at.

Definitions change when the product changes, on a deploy. Grants change constantly, at runtime, by people. Mixing them — for instance by storing permission strings directly on the user — destroys both properties at once: you can no longer say what a role means, and you can no longer change what it means in one place.

The scope lives on the grant row

The requirement is that the same person is an area supervisor in district 7 and an ordinary inspector in district 3. There are two ways to spell that, and only one of them survives.

	Role name per unit (`supervisor_district_7`)	`district_id` column on the grant row
Adding a district	Insert roles and their permission rows for it	Nothing; grants reference the district directly
"What may a supervisor do?"	Answerable only per district, by comparing rows	One row set in `role_permission`
Changing a supervisor's powers	Edit every per-district copy consistently	Edit one role
The request-time check	Parse a unit out of a role name	Compare a column to the request's district
Revoking one posting	Delete the grant of that district's role	Delete the grant row for that district

The column version is the same statement — a grant that is true only inside one organisational unit — expressed as data rather than as naming convention. The role table keeps describing the product, and the number of roles stays proportional to the number of distinct jobs rather than to the number of districts.

A nullable district_id carries the service-wide case (a national administrator), and it is worth deciding explicitly whether NULL means everywhere or nowhere: the safe reading is that a NULL grant applies in every district, which is exactly why the endpoint that creates such a grant deserves more care than the others.

The index the hot check needs

The check issues one predictable question per request: which grants does this principal hold in this district? That is a lookup on (user_id, district_id), so that is the composite index — in that column order, because user_id is always known and equality-matched. An index on user_id alone still works and returns every posting the person holds anywhere, which is fine for a person with three postings and wasteful for a national account with hundreds.

One index seek instead of a scan is the whole claim; nothing here makes the check free, and on a hot endpoint the more valuable move is usually to assemble the effective permission set once per request and pass it down rather than re-querying in each layer.

Also worth having:

A unique constraint on (user_id, role_id, district_id) — granting the same posting twice is then idempotent, and revocation is the deletion of exactly one row.
A unique key on role.key and on permission.key — the strings are what the code references, so they must be unique and stable.
An index on (district_id, role_id) if, and only if, you actually answer "who supervises district 7?" often — the administration screen does, the request path does not.

What the check reads, and what it does not

The server resolves the principal, resolves the district the request is operating on, reads the grants for that pair, expands them through role_permission, and tests whether the required string is in the resulting set. Absence is a refusal.

Two boundaries to keep straight:

How the district arrives on the request is not part of this schema. Deriving it and carrying it through the call is its own design problem; the schema's job is to make the answer derivable and unambiguous once it has arrived.
The table is not the enforcement point. These rows let a check be answered; where that check is placed — handler, service, consumer — is a separate decision, and the schema must serve all of them equally, which is another reason not to shape it around one entry point.

Signs the shape has gone wrong

The role table grows every time a district is created.
A grant row has no unit column, and the district is recovered by parsing a role name or a user attribute.
Permission strings are duplicated onto users "for speed", so two sources of truth drift.
Revoking a posting requires editing several rows, which means it will half-fail one day.

## The two halves of the schema Split the tables into **definitions** (what roles exist and what they mean) and **grants** (who holds one, and where). Definitions: - `role` — `id`, `key` (`inspector`, `area_supervisor`, `national_administrator`), `description`. - `permission` — `id`, `key` (`establishment:inspect`, `report:sign_off`), `description`. - `role_permission` — `role_id`, `permission_id`, primary key over the pair. Grants: - `role_grant` — `user_id`, `role_id`, `district_id` (nullable for a role that is genuinely service-wide), `granted_by`, `granted_at`. Definitions change when the product changes, on a deploy. Grants change constantly, at runtime, by people. Mixing them — for instance by storing permission strings directly on the user — destroys both properties at once: you can no longer say what a role means, and you can no longer change what it means in one place. ## The scope lives on the grant row The requirement is that the same person is an area supervisor in district 7 and an ordinary inspector in district 3. There are two ways to spell that, and only one of them survives. | | Role name per unit (`supervisor_district_7`) | `district_id` column on the grant row | |---|---|---| | Adding a district | Insert roles and their permission rows for it | Nothing; grants reference the district directly | | "What may a supervisor do?" | Answerable only per district, by comparing rows | One row set in `role_permission` | | Changing a supervisor's powers | Edit every per-district copy consistently | Edit one role | | The request-time check | Parse a unit out of a role name | Compare a column to the request's district | | Revoking one posting | Delete the grant of that district's role | Delete the grant row for that district | The column version is the same statement — a grant that is true only inside one organisational unit — expressed as **data** rather than as naming convention. The role table keeps describing the product, and the number of roles stays proportional to the number of distinct jobs rather than to the number of districts. A nullable `district_id` carries the service-wide case (a national administrator), and it is worth deciding explicitly whether `NULL` means *everywhere* or *nowhere*: the safe reading is that a `NULL` grant applies in every district, which is exactly why the endpoint that creates such a grant deserves more care than the others. ## The index the hot check needs The check issues one predictable question per request: **which grants does this principal hold in this district?** That is a lookup on `(user_id, district_id)`, so that is the composite index — in that column order, because `user_id` is always known and equality-matched. An index on `user_id` alone still works and returns every posting the person holds anywhere, which is fine for a person with three postings and wasteful for a national account with hundreds. One index seek instead of a scan is the whole claim; nothing here makes the check free, and on a hot endpoint the more valuable move is usually to assemble the effective permission set **once per request** and pass it down rather than re-querying in each layer. Also worth having: 1. A unique constraint on `(user_id, role_id, district_id)` — granting the same posting twice is then idempotent, and revocation is the deletion of exactly one row. 2. A unique key on `role.key` and on `permission.key` — the strings are what the code references, so they must be unique and stable. 3. An index on `(district_id, role_id)` if, and only if, you actually answer "who supervises district 7?" often — the administration screen does, the request path does not. ## What the check reads, and what it does not The server resolves the principal, resolves the district the request is operating on, reads the grants for that pair, expands them through `role_permission`, and tests whether the required string is in the resulting set. Absence is a refusal. Two boundaries to keep straight: - **How the district arrives on the request is not part of this schema.** Deriving it and carrying it through the call is its own design problem; the schema's job is to make the answer derivable and unambiguous once it has arrived. - **The table is not the enforcement point.** These rows let a check be answered; where that check is placed — handler, service, consumer — is a separate decision, and the schema must serve all of them equally, which is another reason not to shape it around one entry point. ## Signs the shape has gone wrong - The role table grows every time a district is created. - A grant row has no unit column, and the district is recovered by parsing a role name or a user attribute. - Permission strings are duplicated onto users "for speed", so two sources of truth drift. - Revoking a posting requires editing several rows, which means it will half-fail one day.

A role already called supervisor_district_7 exists in the database. What do you do with it?
Treat the suffix as data trapped in a name: create one `area_supervisor` role, insert a grant per holder carrying the district, and leave the old role readable until every check has moved. It is the spelling being replaced, so the migration succeeds when no grant references a district-suffixed role any more.
Should the effective permission set be cached between requests?
You can, and you buy a revocation lag exactly as long as the cache lives: a removed posting keeps working until the entry expires. For a small set read once per request the query is usually cheaper than owning that lag; if you do cache, make the lifetime short and explicit, and invalidate on a grant change.
Where does a grant's expiry belong — a temporary posting that ends in March?
As `valid_from` and `valid_until` columns on the grant row, with the check filtering on the current time. That keeps it a property of the posting, and revocation early is still the same delete. It is not a new role and not a new permission string.

A single warrant card with the district printed on it, not a different card for every district. The card says what the office is; the field says where the office runs. Issue a card type per district and the drawer fills with near-identical cards nobody can compare.

saying these in an interview costs you the question

Mints a role per district and calls the naming convention a model.
Indexes only user_id, then wonders why a national account's lookup is wide.
Copies permission strings onto the user row for speed, creating a second truth.
Says per-user permission lists are equivalent to role rows, so changing a job means editing every holder.
Leaves district_id meaningless on service-wide grants without deciding what NULL means.
Has no unique constraint, so the same posting is granted twice and revoked once.

In a food-safety inspectorate, which tables and indexes let an inspector hold a supervisor role in one district only?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The two halves of the schema

The scope lives on the grant row

The index the hot check needs

What the check reads, and what it does not

Signs the shape has gone wrong

▾common follow-ups3 questions

▾analogy~20 s

▾red flags6 flags

The two halves of the schema

The scope lives on the grant row

The index the hot check needs

What the check reads, and what it does not

Signs the shape has gone wrong

answer

go deeper