Add sign-in and roles to an existing API

What you will be able to do

Take an API that works for one team and make it safe for two. The API course’s city data API answers anyone who has its one shared key. In this case study, a second city department joins. So the API must know who is calling, which department they work for, and what their role lets them do. You add sign-in, roles and department-scoped queries, and prove the rules with a test matrix. Then you run the same API with a real identity provider.

Case studyPortfolio piece

Dataset

NYC 311 Service Requests, transport (DOT) and parks (DPR) departments, January to June 2025

Licence

NYC Open Data: no restrictions on use

Time

About 4 hours

Runs in

VS Code, Python 3.12+

Skills used

• OIDC sign-in with PKCE
• Access token validation
• Memberships and roles
• Department-scoped queries
• 404 and 403 rules
• Audit events
• Access-control test matrix
• Keycloak

Download the starter project (.zip)Finished project (.zip)

Unzip the starter, open the folder in VS Code, and follow the steps below. They start with a new virtual environment.

The starter continues the API course’s case study, Build a data API for city service requests. It is that finished project, and it has a second department’s data. It also has the parts of this course that you reuse as they are. They are the practice provider, the token validator, a sign-in script and the made-up people. You add the identity layer. The finished project is the result. The code is under the MIT licence, so you can publish your version.

Before you startWhat to know and to have readyAbout 240 minutes

What you need to know

This case study uses the whole course. The lessons it leans on most:

• The three questions, from Three separate questions, and roles on memberships, from Users and organizations.
• What a signed token is and how the API checks it, from Read a token and Token meaning.
• The order of the checks and the 404 and 403 rules, from Authorization in the API, and tenant-scoped queries, from Tenant-safe queries.
• The test matrix and the audit, from Negative testing and Auditability.
• The data API itself: read the API course’s case study, parts 1 to 5, if you did not do it. The starter has the code.

What you need on your computer

• Python 3.12 or newer, VS Code and curl, as in the course.
• About 150 MB of free disk space: the packages and the database (13 MB to download, 65 MB unpacked).
• For Part 8 only: Docker Desktop, about 800 MB for the Keycloak image, and 1 GB of free memory.

Tested on macOS (Apple silicon). Versions: Python 3.12, FastAPI 0.142.2, PyJWT 2.15.1, pytest 9.1.1, Keycloak 26.8.0. The Windows commands follow the course’s commands, but nobody ran them. The part on Microsoft Entra ID comes from its documentation; nobody ran it either.

The situation

Teresa Okafor’s analytics team runs the city data API of the API course. Dashboards read the transport department’s 311 requests from it with one shared key. It works well, so the Parks department asks for the same. It wants its tree and park requests, its own dashboards, and one new feature that both departments want. Their staff want to add short notes to requests, such as “Crew sent on 4 March.”

The quick plan is to load the parks data into the same database and give Parks the key. Lena Fischer, who leads the parks team, asks three questions before she agrees. Can my people see transport’s requests? Can a contractor who only reads also write notes? When someone leaves, can we take their access away without changing the key for everyone?

With one shared key, the answers are yes, yes and no. Those are the course’s three questions in a city office: who is calling, in which department, and what may they do there. You are the developer who must make the answers right.

We made up the people and the departments’ plans. The requests are real: they come from New York City’s open data.

What you will build

Version 2 of city-requests-api. The data stays read-only. The routes move under the department, and every route needs an access token:

Endpoint	Who may use it
GET /v1/me	Anyone with a valid token: who you are, and your memberships
GET /v1/departments/{department}/service-requests, .../{id}	Members of the department
GET .../service-requests/{id}/notes	Members
POST .../service-requests/{id}/notes	Owners and staff
GET .../complaint-types, .../stats/daily	Members
GET .../members	Owners and staff
PUT .../members/{user_id}, DELETE .../members/{user_id}	Owners
GET .../audit	Owners

The people are the same in every part. Teresa (owner), Priya Nair (staff) and the dashboard server (transport-dashboards, read-only) work in transport. Lena (owner), Jamal Carter (staff) and Marcus Bell (read-only) work in parks. Hana Sato is read-only in transport and staff in parks: one person, two roles. Oliver Grant can sign in, but has no membership.

The data

The transport data is the API course’s: 109,915 requests of the city’s Department of Transportation (agency DOT), January to June 2025. For this case study, Nextia Learning added the Department of Parks and Recreation (agency DPR). On 2026-10-10 it pulled that department’s requests of the same months from NYC Open Data: 48,748 requests. NYC Open Data says: “There are no restrictions on the use of Open Data.” The city asks users to name the source and the version, and to describe changes. The project’s DATASET.md does that.

The same nine cleaning rules of the API course apply to both departments. A tenth rule adds the column department: transport for DOT and parks for DPR. The rules removed 5 repeated parks submissions, so 48,743 parks requests remain. The transport rows are exactly the API course’s rows. The script checks that no request ID is in both departments.

Column	Meaning
id, created_at, closed_at, status, complaint_type, …	As in the API course
department	transport or parks: the organization whose people may see the request

Look at the data from Python, in the starter folder after the set-up below (type python, then the lines after >>>):

>>> import sqlite3
>>> con = sqlite3.connect("data/service_requests_2025h1_v2.db")
>>> con.execute("SELECT department, COUNT(*) FROM service_requests GROUP BY department ORDER BY 2 DESC").fetchall()
[('transport', 109915), ('parks', 48743)]
>>> con.execute("SELECT complaint_type, COUNT(*) FROM service_requests WHERE department = 'parks' GROUP BY 1 ORDER BY 2 DESC LIMIT 3").fetchall()
[('Damaged Tree', 12198), ('Maintenance or Facility', 10482), ('Overgrown Tree/Branches', 8171)]
>>> con.execute("SELECT department, COUNT(*) FROM service_requests WHERE created_at >= '2025-03-03' AND created_at < '2025-03-04' GROUP BY 1 ORDER BY 2 DESC").fetchall()
[('transport', 752), ('parks', 163)]

What this tells us. The two departments share one table and the same days. On 3 March 2025, 752 requests were transport’s and 163 were parks’. A query that forgets the department mixes them, and nothing in the result says so: a parks row looks exactly like a transport row. This is the leak to watch in every part below.

The data has no names or addresses of the people who called 311.

Set up the starter

1. Download the starter project from the box at the top of this page. Unzip it into your projects folder. You get the folder city-requests-api. If you still have the API course’s folder of the same name, unzip this one somewhere else.

2. In VS Code, choose File > Open Folder, and open city-requests-api. Then open a terminal: Terminal > New Terminal.

3. Make and activate a virtual environment:

   macOS

   python3 -m venv .venv
   source .venv/bin/activate

   Windows

   python -m venv .venv
   .venv\Scripts\Activate.ps1

   Linux

   python3 -m venv .venv
   source .venv/bin/activate

   The prompt starts with (.venv). If VS Code asks whether to use the new environment for this folder, choose Yes.

4. Install the exact versions. The list is the API course’s, with PyJWT and cryptography for the tokens:

   python -m pip install -r requirements-lock.txt

5. Download the database, check it and unpack it:

   python scripts/get_db.py

   Output

   Downloading https://github.com/Nextia-Intelligence/nextia-learning-labs/raw/main/C23/M07-L02-add-sign-in-and-roles-to-an-existing-api/data/service_requests_2025h1_v2.db.gz
   Checked service_requests_2025h1_v2.db.gz: SHA-256 f6d4201fd849... is correct
   Wrote data/service_requests_2025h1_v2.db: transport 109,915, parks 48,743 service requests

6. Run the API course’s tests:

   python -m pytest

   Output (end)

   ============================== 67 passed in 0.59s ==============================

What this tells us. The API course’s API runs on version 2 of the data, and its 67 tests pass. They pass because their small test database has only transport rows. The tests cannot see the problem yet.

Part 1: the baseline, one shared key

A case study needs a baseline: the simplest thing that the team would do. Here it is the quick plan, Parks gets the key. Start the starter API with the key, as in the API course:

macOS

API_KEY=local-dev-key fastapi dev

Windows

$env:API_KEY = "local-dev-key"
fastapi dev

Linux

API_KEY=local-dev-key fastapi dev

In a second terminal (the + in the terminal panel; activate the environment there too), run the five attempts of the starter. Each one is a thing that Lena asked about. Every attempt sends the same key, because that is all a caller has:

Second terminal

python -m scripts.attempts_shared_key

Output

GOT IT   Jamal (parks) reads transport request 64246800: 200 Street Sign - Missing
GOT IT   Jamal lists requests: 94 of the first 100 are transport's
GOT IT   Lena (parks) opens the complaint-type menu: 31 of 42 types are transport's, first 'Street Condition'
GOT IT   Transport's dashboard, 3 March 2025: opened 915, of which 163 are parks requests
GOT IT   Oliver left the team last month; his copy of the key still works: 200

5 of 5 attempts got what they should not get. The API saw the same caller every time: the key.

What this tells us. Every attempt worked, and the API cannot even tell the callers apart. Two failures are leaks: parks staff read transport’s requests. Two are wrong numbers, which are just as harmful: transport’s dashboard now says 915 requests opened on 3 March, not 752. Nobody gets an error, so nobody notices. The last one is about time: nobody can take the key from one person. This is the score to beat, 5 of 5. Stop the API with Ctrl+C.

Parts 4 to 6 change files that need each other, so the API runs again only at the end of Part 6. An import error in fastapi dev between those parts is normal.

Part 2: sign in with the practice provider

The starter has the course’s practice identity provider in idp/. It is a mock: it signs real RS256 tokens, but it shows made-up people instead of asking for a password. It is a copy of the course project’s provider, with its MIT licence. Four things changed:

• the application names;
• the scopes: requests:read, requests:write and members:manage;
• the audience: city-requests-api;
• the port: 8410.

The course project’s provider uses 8400, so both can run at the same time.

Make the provider’s signing key, then start it in the second terminal:

Second terminal

python -m idp init
python -m idp

Output

Made a signing key (N8WXOiTO1y0iX_Ni) and the clients in /.../city-requests-api/.idp.
Wrote the client secret of transport-dashboards to /.../city-requests-api/.env.
The provider keeps only its SHA-256. Never commit .env or .idp/.
Practice identity provider: http://localhost:8410 (made-up people, no passwords)

Your key ID is different: your computer made it. init also made .env from .env.example, with OIDC_ISSUER=http://localhost:8410 in it. In a third terminal, sign in as Hana with the starter’s script. It is the application city-cli: a public client, so it uses the authorization code flow with PKCE, as in Application flows:

Third terminal

python -m scripts.login --user usr-hana

Output

1. The sign-in page of the practice provider, with a PKCE challenge: http://localhost:8410/authorize?response_type=code&client_id...
   You picked usr-hana.
2. The provider sent the browser back with a one-time code; the state matches.
3. The code and the PKCE verifier were swapped for tokens (POST /token):
   200: token_type Bearer, expires_in 600 s
   access_token  eyJhbGciOiJSUzI1NiIsImtp...(742 characters)
   id_token      eyJhbGciOiJSUzI1NiIsImtp...(713 characters)

access_token (decoded, not checked):
  header {"alg": "RS256", "kid": "N8WXOiTO1y0iX_Ni", "typ": "at+jwt"}
  claims {
    "iss": "http://localhost:8410",
    "sub": "usr-hana",
    "aud": "city-requests-api",
    "exp": 1791618331,
    "nbf": 1791617731,
    "iat": 1791617731,
    "jti": "ad913838abef41deb96972aa526c36fc",
    "client_id": "city-cli",
    "scope": "requests:read requests:write members:manage"
  }

Saved in .tokens/usr-hana.json.

What this tells us. The token says who Hana is (sub), who made the token (iss), which API it is for (aud), until when (exp), and what the application may do for her (scope). It says nothing about departments or roles, on purpose: Hana has two roles, and the API answers both questions from its own table.

Part 3: the access database

The data file is read-only, and it stays that way. Who may do what goes in a second, small SQLite file that the API may write: data/access.db. The starter has its schema, city_api/access_schema.sql, and the script that fills it from data/identity/:

Third terminal

python -m scripts.init_access

Output

Wrote data/access.db: 2 departments, 8 users, 8 memberships.

The important lines of the schema:

city_api/access_schema.sql (part)

-- The role belongs to the membership, not to the person: Hana is read_only in
-- transport and staff in parks.
CREATE TABLE memberships (
    department_id TEXT NOT NULL REFERENCES departments (department_id),
    user_id       TEXT NOT NULL REFERENCES users (user_id),
    role          TEXT NOT NULL CHECK (role IN ('owner', 'staff', 'read_only')),
    created_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    PRIMARY KEY (department_id, user_id)
);

-- Append-only: nobody changes or deletes an event, not even the API.
CREATE TRIGGER audit_no_update BEFORE UPDATE ON audit_events
BEGIN SELECT RAISE(ABORT, 'audit_events is append-only'); END;
CREATE TRIGGER audit_no_delete BEFORE DELETE ON audit_events
BEGIN SELECT RAISE(ABORT, 'audit_events is append-only'); END;

The other tables are departments, users (by the provider’s sub), request_notes and audit_events. A note points to a request in the other file, so SQLite cannot check that link with a foreign key. The API checks it instead: it writes a note only after it found the request in the caller’s department.

The API needs a connection to this file. Replace city_api/db.py with the finished project’s version. It adds connect_access(), which opens the file for writing and checks its schema version.

Part 4: check every token

The validator is the course’s, copied into the starter as city_api/auth.py. It checks a token in a fixed order, and stops at the first failure. It checks that the token is a JWT, then the algorithm (RS256 only) and the type (at+jwt). Then it checks the key, the signature, the expiry, the issuer, the audience and the needed claims. It fetches the provider’s public keys once and keeps them. You connect it in four files. Each step says which file to replace with the finished project’s, and shows the part that matters.

Settings. Replace city_api/config.py with the finished project’s version. It drops API_KEY, reads .env, and adds the identity settings. Each has a safe default, so the .env of Part 2 is enough:

city_api/config.py (the new settings)

    access_db_path: Path = Path("data/access.db")
    oidc_issuer: str | None = None  # not set: sign-in is off, and /v1 answers 503
    oidc_audience: str = "city-requests-api"
    oidc_internal_url: str | None = None  # where the API reaches the provider, if different
    access_token_types: tuple[str, ...] = tuple(DEFAULT_TOKEN_TYPES.split(","))
    jwks_cache_seconds: float = 600
    jwks_min_refresh_seconds: float = 10
    token_leeway_seconds: float = 30
    user_id_claim: str = "sub"  # Microsoft Entra ID: "oid" (see docs/entra-id.md)

Who is calling. Replace city_api/security.py. The API key check goes; this dependency takes its place:

city_api/security.py (the dependency)

def current_identity(
    request: Request,
    credentials: Annotated[HTTPAuthorizationCredentials | None, Depends(bearer)],
) -> Identity:
    validator = request.app.state.validator
    if validator is None:
        raise SignInUnavailable("OIDC_ISSUER is not set")
    if credentials is None:
        raise NotSignedIn("Send an access token: Authorization: Bearer <token>.")
    identity = validator.validate(credentials.credentials)  # raises TokenRejected
    request.state.identity = identity  # for the access log
    return identity


CurrentIdentity = Annotated[Identity, Depends(current_identity)]

The answers. Replace city_api/errors.py. Its new “sign-in and roles” section gives a refused token 401 invalid_token, a WWW-Authenticate header, and an X-Token-Check header that names the check. A refused token is also an audit event, without the token.

The app. Replace city_api/main.py. It makes the validator once, at the start, and includes the new routers of Parts 5 and 6. It also logs the caller’s ID with each request:

city_api/main.py (in create_app)

    app.state.validator = make_validator(settings)

city_api/main.py (the access log line)

        identity = getattr(request.state, "identity", None)
        logger.info(
            "%s %s %d %.0fms id=%s user=%s",
            request.method,
            request.url.path,
            response.status_code,
            (time.perf_counter() - started) * 1000,
            request.state.request_id,
            identity.user_id if identity else "-",
        )

The log now answers “who read request 64246800?” with a person, not “a key”.

Part 5: the department in the path, the role from the table

The idea: three checks before any data

Think of an office building pass. The guard first checks that the pass is real (who). Then the guard checks that the pass opens this floor (where). Then a sign on the door says what you may do inside (what). A real pass for floor 3 does not open floor 5, and the guard does not tell you what is on floor 5.

The API does the same for every department route, in this order:

The four checks of every route under /v1/departments/{department}/, in order. Each 'no' has its own answer, and the checks stop there.

Text description of the diagram

A request names its department in the path and carries a token. Check 1 asks who the caller is: without a valid token the answer is 401. Check 2 looks for a membership row of the caller in that department: without one the answer is 404 “Not found.”, the same for a real and a made-up department. Check 3 asks the access matrix whether the role and the token’s scope allow the action: if not, 403. Check 4 is the query itself, with the department in it: a request of another department is not found, 404. Only a request that passes all four gets 200.

Build it: the matrix as a function

Make city_api/access.py. The table of roles is the access matrix of Users and organizations, for this API:

city_api/access.py (the rules)

ACTIONS = ("request.read", "note.write", "member.read", "member.manage", "audit.read")

# What each role may do in its department.
ROLE_ACTIONS: dict[str, frozenset[str]] = {
    "owner": frozenset(ACTIONS),
    "staff": frozenset({"request.read", "note.write", "member.read"}),
    "read_only": frozenset({"request.read"}),
}

# The token scope that an action needs. A role says what the PERSON may do; a scope says
# what the APPLICATION may do for them (what they allowed at sign-in). Both must allow it.
ACTION_SCOPE = {
    "request.read": "requests:read",
    "member.read": "requests:read",
    "note.write": "requests:write",
    "member.manage": "members:manage",
    "audit.read": "members:manage",
}


def decide(role: str | None, action: str, scopes: frozenset[str] | None = None) -> Decision:
    if action not in ACTION_SCOPE:
        raise ValueError(f"unknown action {action!r}")
    if role is None:
        return Decision(False, 404, "not_a_member", "no membership in this department")
    if action not in ROLE_ACTIONS.get(role, frozenset()):
        return Decision(False, 403, "role_cannot", f"{role} may not {action}")
    needed = ACTION_SCOPE[action]
    if scopes is not None and needed not in scopes:
        return Decision(False, 403, "missing_scope", f"the token has no {needed} scope")
    return Decision(True, 200, "allowed", f"{role} may {action}")

The finished file adds the Decision dataclass and comments. decide() reads no database and no request, so a test can check every cell in microseconds.

Build it: the check before every route

Make city_api/tenancy.py. Its require(action) is a FastAPI dependency: a route that asks for it runs only after the three checks. The core:

city_api/tenancy.py (the check)

def check(
    request: Request, con: sqlite3.Connection, identity: Identity, department: str, action: str
) -> Caller:
    role = role_of(con, department, identity.user_id)
    decision = decide(role, action, identity.scopes)
    if not decision.allowed:
        # Who TRIED matters as much as who did. The department is recorded only for a
        # member (403): an outsider's attempt is not written into its log.
        audit.record(
            request.app.state.settings.access_db_path,
            action=action,
            result="denied",
            actor_id=identity.user_id,
            actor_kind=identity.kind,
            department_id=department if decision.status == 403 else None,
            target=f"{request.method} {request.url.path}",
            reason=decision.code,
            request_id=getattr(request.state, "request_id", None),
            details={"rule": decision.rule, "department_in_path": department},
        )
        raise AccessDenied(decision, department, action)
    return Caller(identity, department, role)


def require(action: str):
    """A FastAPI dependency: the route runs only if the caller may do `action` in the
    department named in the path."""

    def dependency(
        request: Request, department: DepartmentId, identity: CurrentIdentity, con: AccessDb
    ) -> Caller:
        return check(request, con, identity, department, action)

    return dependency

role_of() is one SELECT role FROM memberships WHERE department_id = ? AND user_id = ?, on every request. There is no cache, so a removed member is out at their next request. Lifecycle changes measured what a 30-second cache costs: 30 seconds of access after removal. Copy the rest of the file (Caller, AccessDenied, the connection to the access database) from the finished project, and city_api/audit.py too. audit.write() drops any detail whose name sounds like a secret, and replaces anything that looks like a JWT.

Why 404 for an outsider? A 403 would tell Jamal “transport exists, and you are not in it”; a 404 tells him nothing. Hana is a member of transport and already sees its requests, so her 403 reveals nothing and explains the failure.

Part 6: queries that need the department

The problem: one forgotten WHERE

Part 5 decides who may enter a department. The queries must still read only that department. Remember the baseline: a list without department = ? gave Jamal 94 transport rows in his first 100. One forgotten condition in one query undoes every check before it.

The idea: make forgetting impossible

Every function in city_api/queries.py gets a required, keyword-only argument department, and where() always starts with it:

city_api/queries.py (the changed parts)

def where(filters: dict, *, department: str) -> tuple[str, list]:
    """Build the WHERE clause and its parameters: the department first, then the
    filters that are set."""
    parts, params = ["department = ?"], [department]
    for name, value in filters.items():
        if value is not None:
            parts.append(CONDITIONS[name])
            params.append(value)
    return " AND ".join(parts), params


def get_request(
    con: sqlite3.Connection, request_id: int, *, department: str
) -> sqlite3.Row | None:
    """One request of this department. Another department's request is not found."""
    return con.execute(
        f"SELECT {COLUMNS} FROM service_requests WHERE department = ? AND id = ?",
        (department, request_id),
    ).fetchone()

A call that forgets it fails in Python before any SQL runs: get_request(con, 64246800) raises a TypeError that names the missing argument department, and a test checks this. list_requests, complaint_types and daily_stats change in the same way: copy the finished queries.py. The new indexes of version 2, such as (department, created_at, id), keep the keyset pages of the API course fast inside one department.

The course’s project adds a second lock, PostgreSQL row-level security (Tenant-safe queries). SQLite has none, so here the tests must cover the one lock well.

The routes

Replace city_api/routes.py, and add city_api/people.py (/v1/me, members and audit), from the finished project. Every route takes its department from the caller, never from the client:

city_api/routes.py (part)

router = APIRouter(prefix="/v1/departments/{department}", responses=ERRORS)

ReadRequests = Annotated[Caller, Depends(require("request.read"))]
WriteNotes = Annotated[Caller, Depends(require("note.write"))]


def request_of_department(con: sqlite3.Connection, request_id: int, department: str):
    """The request, if it is this department's. Another department's request gets the
    same answer as an ID that does not exist: inside this department, it does not."""
    row = queries.get_request(con, request_id, department=department)
    if row is None:
        raise ApiError(404, "not_found", f"No service request has the ID {request_id}.")
    return row

add_note writes the note and its audit event in one transaction. PUT .../members/{user_id} refuses to demote the last owner (409 last_owner). Also replace city_api/models.py and contract/openapi.json (the reviewed contract of version 2).

Run it

Start the API again in the first terminal with fastapi dev: no API_KEY now, the settings come from .env. Keep the provider running, and call the API from the third terminal as Hana:

macOS

TOKEN=$(python -m scripts.login --user usr-hana --print-access-token)
curl -s -H "Authorization: Bearer $TOKEN" http://127.0.0.1:8000/v1/me
curl -s -i -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -d '{"body": "Crew sent on 4 March."}' \
  http://127.0.0.1:8000/v1/departments/transport/service-requests/64246800/notes

Windows

$TOKEN = python -m scripts.login --user usr-hana --print-access-token
curl.exe -s -H "Authorization: Bearer $TOKEN" http://127.0.0.1:8000/v1/me
curl.exe -s -i -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" `
  -d '{\"body\": \"Crew sent on 4 March.\"}' `
  http://127.0.0.1:8000/v1/departments/transport/service-requests/64246800/notes

Linux

TOKEN=$(python -m scripts.login --user usr-hana --print-access-token)
curl -s -H "Authorization: Bearer $TOKEN" http://127.0.0.1:8000/v1/me
curl -s -i -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -d '{"body": "Crew sent on 4 March."}' \
  http://127.0.0.1:8000/v1/departments/transport/service-requests/64246800/notes

Output

{"user_id":"usr-hana","kind":"person","client_id":"city-cli","scopes":["members:manage","requests:read","requests:write"],"memberships":[{"department":"parks","role":"staff"},{"department":"transport","role":"read_only"}]}
HTTP/1.1 403 Forbidden
date: Sat, 10 Oct 2026 07:35:43 GMT
server: uvicorn
content-length: 140
content-type: application/json
x-request-id: 51f50de2832a

{"error":{"code":"forbidden","message":"Your role may not do this (read_only may not note.write).","request_id":"51f50de2832a","fields":[]}}

The same note on a parks request, .../departments/parks/service-requests/64225360/notes, gives 201 with "author_id":"usr-hana": in parks she is staff. Now Oliver, who has no membership, asks for a real department and for a made-up one, sanitation:

Output (Oliver: transport, then sanitation; the status line and the body)

HTTP/1.1 404 Not Found
{"error":{"code":"not_found","message":"Not found.","request_id":"cf7fde715793","fields":[]}}
HTTP/1.1 404 Not Found
{"error":{"code":"not_found","message":"Not found.","request_id":"e6a07bbe8dae","fields":[]}}

What this tells us. /v1/me shows the memberships from the API’s table. The same person gets 403 in one department and 201 in the other. Oliver’s two answers are the same bytes apart from request_id. Their time can still differ slightly, as Authorization in the API measured; this design does not try to hide that.

Then check every bad token at once. scripts/token.py makes a made-up token for each mistake or attack (constructed for this case study) and sends it to /v1/me:

Third terminal

python -m scripts.token cases

Output

Made-up tokens sent to GET /v1/me (constructed for this case study):

  valid               200  accepted                        Priya's access token, as the provider issued it
  no_token            401  refused: not_signed_in          no Authorization header
  expired             401  refused: expired                expired 5 minutes ago
  wrong_audience      401  refused: wrong_audience         made for another API (aud billing-api)
  wrong_issuer        401  refused: wrong_issuer           from another issuer
  id_token            401  refused: wrong_token_type       Priya's ID token sent as an access token
  alg_none            401  refused: algorithm_not_allowed  unsigned (alg none)
  unknown_key         401  refused: unknown_key            signed with a key the provider never published
  wrong_key_same_kid  401  refused: bad_signature          signed with another key, with the provider's kid
  tampered            401  refused: bad_signature          sub changed to usr-teresa after signing
  missing_sub         401  refused: missing_claim          no sub claim
  not_a_jwt           401  refused: malformed              a random string

Common mistake

Every request gets 503 sign_in_unavailable, or 401 wrong_issuer

503 sign_in_unavailable: the API has no OIDC_ISSUER. Check that .env has the line, and restart fastapi dev. 401 with X-Token-Check: wrong_issuer: the issuer in the token and in .env must match exactly, also localhost against 127.0.0.1. The practice provider says http://localhost:8410. A 503 identity_provider_unavailable means that the provider is not running: start python -m idp.

Part 7: the access-control test matrix

The problem: rules that nobody tests

Ten routes, eight identities, two departments: 160 answers. A change to one route can break one cell, and a test of a few happy paths does not see it. Negative testing taught the cure: write the whole matrix as a test, so that every run checks every cell.

Build it

The tests need tokens without a running provider. The finished tests/conftest.py runs the provider inside the test process, and makes a test client for any person: client_as("usr-hana"). It also builds a small test database: 30 transport rows (the API course’s) and 12 real parks rows of the same days. So a forgotten department would mix them. Copy it with tests/fixture_rows_parks.csv and the API course’s five test files from the finished project. Those now call /v1/departments/transport/... as Teresa, and their numbers did not change: inside transport, the API answers as before.

Then write the matrix. The expected answer of each cell comes from decide(), the same function that the API uses, and the people come from data/identity/:

tests/test_access_matrix.py (core)

def expected(user: str, department: str, route: str) -> int:
    action, *_, ok = ROUTES[route]
    decision = decide(MEMBERSHIPS.get((department, user)), action)
    return ok if decision.allowed else decision.status


CASES = [(u, d, r) for u in PEOPLE for d in DEPARTMENTS for r in ROUTES]


@pytest.mark.parametrize("user, department, route", CASES, ids=["|".join(c) for c in CASES])
def test_matrix(client_as, user, department, route):
    response = call(client_as(user), department, route)
    assert response.status_code == expected(user, department, route), response.text

ROUTES lists the 10 routes with their action, method, path and body. Two more tests complete the file. test_another_departments_request_id sends a member’s own department path with the other department’s request ID: 5 people, 3 requests each. test_bad_identity sends 6 bad identities to all 10 routes: no token, expired, wrong audience, wrong issuer, an ID token, and a token with no scope. Copy the finished file for those, and test_tokens.py and test_access_control.py for the single rules. Then run everything, and print the matrix:

python -m pytest
python -m scripts.matrix_report

Output (end of pytest, then the report, shortened)

============================= 326 passed in 18.19s =============================
                                     list   read  notes  note+  types  daily  membr   role remove  audit
usr-teresa @ transport               200    200    200    201    200    200    200    200    204    200
usr-teresa @ parks                   404    404    404    404    404    404    404    404    404    404
usr-priya @ transport                200    200    200    201    200    200    200    403    403    403
...
transport-dashboards @ parks         404    404    404    404    404    404    404    404    404    404

225 of 225 checks passed. (The grid: 160 cells. The other checks: another department's request ID, 5 x 3, and 6 bad identities x 10 routes.)

The access-control test matrix, from the real test run: every identity in every department on every route. Green: allowed. Amber 404: not a member. Red 403: a member whose role may not.

Text description of the diagram

Each row is one identity in one department; each column is one route. The pattern is the access matrix. Owners may do everything in their own department. Staff may read, add notes and list members. Read-only members may only read. Anyone without a membership gets 404 everywhere, also on owner-only routes, because the membership check runs first. Hana’s two rows differ, because her two memberships have different roles. 225 of 225 checks passed.

What this tells us. The grid is the access matrix of docs/access-matrix.md, checked. An outsider never sees a 403, and Hana’s two rows differ in four columns. If a future change opens one route to the wrong role, one cell fails and the test names it, for example test_matrix[usr-hana|transport|add a note].

Part 8: a real identity provider

Keycloak, on your computer (tested)

A mock can hide real problems: the course’s own run with Keycloak found two, in Provider configuration. So run the same API with Keycloak (Apache-2.0), free, in Docker, and delete it afterwards. optional/keycloak/README.md has the same steps:

1. Start Keycloak 26.8.0. Choose your own admin password:

   docker run -d --name city-keycloak -p 127.0.0.1:8480:8080 -e KC_BOOTSTRAP_ADMIN_USERNAME=admin -e KC_BOOTSTRAP_ADMIN_PASSWORD=choose-one quay.io/keycloak/keycloak:26.8.0 start-dev

2. About a minute later, make the realm city, the scopes, an audience mapper for city-requests-api, the application city-cli and one made-up person, teresa:

   macOS

   KEYCLOAK_ADMIN_PASSWORD=choose-one python optional/keycloak/setup.py

   Windows

   $env:KEYCLOAK_ADMIN_PASSWORD = "choose-one"
   python optional/keycloak/setup.py

   Linux

   KEYCLOAK_ADMIN_PASSWORD=choose-one python optional/keycloak/setup.py

   Output

   Keycloak is ready. Put these lines in .env (instead of the practice provider's):
   OIDC_ISSUER=http://127.0.0.1:8480/realms/city
   OIDC_CLIENT_ID=city-cli
   ACCESS_TOKEN_TYPES=JWT
   
   Sign in as teresa with the password <printed once> (made up; shown once).
   Keycloak's user ID for teresa (the token's sub): 3f5e5bb0-e331-47db-9baf-6fa1e61f573e
   Make her the owner of transport:
     python -m scripts.init_access grant --department transport --user 3f5e5bb0-e331-47db-9baf-6fa1e61f573e --role owner --name 'Teresa (Keycloak)'

3. Put the first two lines into .env, instead of the practice provider’s OIDC_ISSUER. Leave ACCESS_TOKEN_TYPES as it is for now. Restart fastapi dev.

4. Sign in through your browser: python -m scripts.login --browser --name teresa-kc. Sign in as teresa on Keycloak’s page. The script waits on http://127.0.0.1:8775/callback. That is a loopback redirect. A command-line program has no web address of its own. So it runs a small web server on your computer for one request, and the provider sends the code there.

Output (part)

2. The provider sent the browser back with a one-time code; the state matches.
3. The code and the PKCE verifier were swapped for tokens (POST /token):
   200: token_type Bearer, expires_in 600 s
   access_token  eyJhbGciOiJSUzI1NiIsInR5...(1159 characters)

access_token (decoded, not checked):
  header {"alg": "RS256", "typ": "JWT", "kid": "FXrCCgHvyrCNp6CC707HRjilq6lf67QBk9YVn4QvbFk"}
  claims {
    "iss": "http://127.0.0.1:8480/realms/city",
    "aud": "city-requests-api",
    "sub": "3f5e5bb0-e331-47db-9baf-6fa1e61f573e",
    "azp": "city-cli",
    "scope": "openid requests:read requests:write email profile offline_access members:manage",
    ...
  }

Call /v1/me with this token. Then set ACCESS_TOKEN_TYPES=JWT in .env, restart the API, and call it again:

Output (/v1/me, before and after ACCESS_TOKEN_TYPES=JWT)

HTTP/1.1 401 Unauthorized
x-token-check: wrong_token_type
{"error":{"code":"invalid_token","message":"This is not an access token (an ID token is for the application, not the API).","request_id":"b1525a9d0b47","fields":[]}}

{"user_id":"3f5e5bb0-e331-47db-9baf-6fa1e61f573e","kind":"person","client_id":"city-cli","scopes":["email","members:manage","offline_access","openid","profile","requests:read","requests:write"],"memberships":[]}

What this tells us. Keycloak’s access token says typ: JWT, not at+jwt, so the type check refused a real access token. With ACCESS_TOKEN_TYPES=JWT it passes, and Keycloak’s ID token is still refused, by the audience check this time (X-Token-Check: wrong_audience). Teresa now has a new sub, a UUID, and no membership. Transport answers her with 404 until the grant command of step 2 makes her an owner. Then the list answers 200, and parks still 404.

Someone must make the first owner: here an operator on the server, with an audit event. After that, owners add people through the API. Delete Keycloak at the end (docker rm -f city-keycloak), and put the practice lines back in .env.

Microsoft Entra ID (described, not tested)

Many city offices sign in with Microsoft Entra ID. docs/entra-id.md maps this design to it from Microsoft’s documentation. It was not run: an Entra ID directory needs a Microsoft account, and this case study needs none. The settings that change:

Setting	Practice provider	Keycloak (tested)	Entra ID (untested)
OIDC_ISSUER	http://localhost:8410	http://127.0.0.1:8480/realms/city	https://login.microsoftonline.com/<directory ID>/v2.0
OIDC_AUDIENCE	city-requests-api	city-requests-api (audience mapper)	the API’s application (client) ID, a GUID
ACCESS_TOKEN_TYPES	at+jwt	JWT	JWT
USER_ID_CLAIM	sub	sub	oid

Entra ID’s sub differs for the same person in each application; oid does not, so memberships use oid. And Entra ID calls the whole directory a “tenant”: the city has one Entra ID tenant, and two departments in the API’s own table.

A fair comparison

Both versions answer the same five questions, on the same data. The metric is the same too: attempts that got what they should not get.

The starter's five attempts, asked of both versions with the same data. The finished answers come from each person's own token.

Text description of the diagram

Left, the attempt; middle, what the shared-key starter answered; right, what the finished API answered when each person used their own token. The starter got all five wrong: two leaks, two wrong numbers, and a key that nobody can take from one person. The finished API refused all five. Its own attempt script adds harder ones, and the API refused all ten.

The finished project’s python -m scripts.attempts sends ten harder attempts, with real tokens, to the running API:

Output of python -m scripts.attempts

refused  Jamal (parks) reads a transport request        404 not_found
refused  Jamal asks for it through his own department   404 not_found
refused  Jamal adds the header X-Department: transport  404 not_found
refused  Oliver (no membership) lists transport         404 not_found
refused  Hana (read_only in transport) adds a note      403 forbidden
refused  The dashboard service adds a note              403 forbidden
refused  Priya's ID token as an access token            401 invalid_token (wrong_token_type)
refused  An expired token (constructed)                 401 invalid_token (expired)
refused  A token for another API (constructed)          401 invalid_token (wrong_audience)
refused  The old shared key X-API-Key                   401 not_signed_in

0 of 10 attempts got what they should not get.

What did it cost? One list request of 50 requests took a median of 1.08 ms on the starter and 1.50 ms on the finished API. The difference is the token check with cached keys, one membership query and one more condition. These are medians of 5 passes of 200 requests each, on an Apple M4 that other work used at the same time. Both ran with fastapi run. Your numbers will differ; the size of the gap, under half a millisecond, is the point.

	Starter (shared key)	Finished (token + membership)
Attempts that got what they should not get	5 of 5	0 of 5, and 0 of 10 harder ones
Who is in the log	“a key”	the person’s ID, user=usr-hana
Remove one person	change the key for everyone	one DELETE, effective at the next request
List request, median	1.08 ms	1.50 ms
Tests	67	326 (225 in the matrix)

End to end: a new colleague joins

A final check on a case that no test scripted: Oliver joins transport, changes role, and leaves, with one access token for all of it. Teresa manages him through the API, with curl calls as in Part 6. The output below is the real run, one line per call:

Output (summarised: who, the call, the answer)

Oliver   GET    /v1/me                                       memberships: []
Oliver   GET    .../transport/service-requests               404
Teresa   PUT    .../transport/members/usr-oliver  read_only  200 (the member list, with Oliver)
Oliver   GET    .../transport/service-requests               200
Oliver   POST   .../service-requests/64246800/notes          403 forbidden: read_only may not note.write
Teresa   PUT    .../transport/members/usr-oliver  staff      200
Oliver   POST   .../service-requests/64246800/notes          201 {"note_id":2,"author_id":"usr-oliver",...}
Teresa   DELETE .../transport/members/usr-oliver             204
Oliver   GET    .../transport/service-requests               404 (the same token as before)
Teresa   PUT    .../transport/members/usr-teresa  staff      409 last_owner

Then Teresa reads the department’s audit, GET .../transport/audit?limit=6. The newest event is first:

Output (shortened to one line per event)

28  usr-teresa            member.remove       user usr-oliver                                         allowed
27  usr-oliver            note.create         service-request 64246800                                allowed
26  usr-teresa            member.change_role  user usr-oliver                                         allowed
25  usr-oliver            note.write          POST /v1/departments/transport/service-requests/...     denied  role_cannot
24  usr-teresa            member.add          user usr-oliver                                         allowed
 9  transport-dashboards  note.write          POST /v1/departments/transport/service-requests/...     denied  role_cannot

What this tells us. Each change worked at Oliver’s next request, with the same token, because the API reads the membership every time. His first 404, as an outsider, is not in transport’s log: the audit records an outsider’s attempt without the department. His refused note is, because he was a member then. Event 9 is the dashboard service’s attempt from the attempt script. No event holds a token or a note’s text: python -m scripts.audit --check reported 0 with a token or a secret.

Insights and limits

• The token says who; the API says where and what. Keeping roles out of the token made two-role people (Hana) and instant removal (Oliver) simple.
• The biggest risk was quiet, not loud. The worst baseline failures were wrong numbers, not error pages. Department-scoped functions and a test database with both departments on the same days catch them.
• One lock, not two. SQLite has no row-level security, so the database does not stop a bug in queries.py. A move to PostgreSQL, as in the course’s project, would add that second lock.
• The matrix proves the rules, not their wisdom. Lena and Teresa must review docs/access-matrix.md itself.
• The mock hid one real difference. The type check refused Keycloak’s real access tokens until one setting changed. Test with a real provider before you go live.
• Not covered: a browser front end with sessions (Session lifecycle), invitations, time-limited support access for city IT, and rate limits per person.

Responsible use

• Only your own practice API. Every attempt in this case study targets the API on your own computer, with made-up people. Never test access to someone else’s system without written permission.
• The data is about the public’s reports. It has no caller names, but the coordinates point to streets near people’s homes. Do not join it with other data to find out who reported what. Keep the notes for work facts, never for remarks about callers.
• Access is not purpose. A member may read a request; that does not make every use fair. A dashboard that ranks neighbourhoods by request counts still measures who reports, as the API course’s case study warned.
• Secrets stay out of files you share. .env and .idp/ are in .gitignore. The Keycloak set-up prints its passwords once, and Keycloak’s development mode is never for real people.

Make it a portfolio piece

This project shows what employers ask for on any team API. It has standard sign-in, roles per organization, safe queries, a tested access matrix and an audit trail. It also shows judgment: a measured cost, and named limits.

What to put in the repository: the finished folder, with README.md (the design and how to run it), docs/access-matrix.md, docs/entra-id.md, DATASET.md, the tests and optional/keycloak/. Keep LICENSE (MIT) and the attribution of the copied idp/. Never commit .env, .idp/, .tokens/ or the databases.

How to describe it in an interview, in two sentences. “I added OpenID Connect sign-in and per-department roles to a FastAPI API over 158,000 real New York 311 requests. A 225-check access matrix proves every identity, department and route, and the API ran with Keycloak after one setting.”

Go further: each of these is one main change.

• PostgreSQL with row-level security, for a second lock, as in Tenant-safe queries.
• Invitations with an expiry, instead of PUT by user ID, as in Lifecycle changes.
• A city-wide read role for the mayor’s office, with its own rows in the matrix.
• Entra ID for real, if your organization has a test directory: follow docs/entra-id.md, and record what differed.

The other case study of this course, Audit an open-source multi-tenant app, turns the matrix on software that someone else wrote.

Knowledge check

Check your understanding

Lena suggests putting each person's department and role into the access token, to save the membership query. What goes wrong for Hana, and for Oliver when Teresa removes him?

Show the answer

Hana has two roles: read-only in transport and staff in parks. One “role” claim cannot say both. And the API would trust the token for a decision that the department’s owners make. Worse is time. A token lives for 10 minutes here. When Teresa removes Oliver, his token would still say “transport, staff” until it expires, and the API would let him in. The API reads the membership from its table on every request, so his next request got 404, with the same token. The token answers only “who”; the table answers “where” and “what”.

Check your understanding

Jamal (parks staff) asks for transport request 64246800 through /v1/departments/parks/... and through /v1/departments/transport/.... What does he get each time, and why are the two answers different?

Show the answer

Through parks he is a member, so checks 1 to 3 pass. The query has department = 'parks' and finds nothing. He gets 404 No service request has the ID 64246800., the same answer as for an ID that does not exist anywhere. Through transport he has no membership, so check 2 stops him with 404 Not found., the same answer as for a made-up department. Neither answer tells him that the request or the department exists. The messages differ because he is a member of parks: he may know that parks has no such request. He may not learn anything about transport.

Check your understanding

Keycloak's tokens failed with wrong_token_type until ACCESS_TOKEN_TYPES=JWT. Does that setting let an ID token through? Use the run in Part 8.

Show the answer

No. With ACCESS_TOKEN_TYPES=JWT, the type check cannot tell Keycloak’s access tokens from its ID tokens, because both say typ: JWT. But the audience check still can. An access token for this API has aud: city-requests-api, from the audience mapper. An ID token’s audience is the application that asked for the sign-in, city-cli. In the run, Keycloak’s ID token got 401 with X-Token-Check: wrong_audience. The audience is the essential check; the type is a cheap extra that works only with providers that set at+jwt.

Recap

• A shared key cannot answer who, where or what: the baseline got 5 of 5 attempts wrong, two of them as quietly wrong numbers.
• The token says who. The department is in the path. The role comes from the API’s own membership table, read on every request.
• The order of the checks gives the answers. 401: no valid token. 404: no membership. 403: a role or scope too small. 404: another department’s request.
• Department-scoped query functions make a forgotten WHERE a Python error. A test database with both departments on the same days makes a leak visible.
• A test matrix built from the same decide() checks every identity, department and route: 225 of 225.
• A real provider differs in small ways: Keycloak needed ACCESS_TOKEN_TYPES=JWT, a new sub and a first owner. Entra ID needs oid and a GUID audience.