The Backend

Last post ended with a token and a single protected route that returns its owner. Nobody could create a project, add a member, or file a task. This post is the whole rest of the API — projects, members, tasks, comments, and two routes under /me — and it is the last post before the app has a face. It is also where the design decisions from the two previous posts stop being decisions and start being code: the composite foreign key from Phase 1 gets its “remove member” route, and “roles are not in the token” from Phase 2 becomes a dependency that reads the row.

The chain

Phase 2 shipped one dependency, current_user. Phase 3 adds three more and they form a chain. The module docstring of </>deps.py draws it, and it is worth reading before any of the code:

current_user      who is this token?               401 if unknown
  └─ get_project  which project is in the URL?     404 if no such key
                  (asks current_user first, so 401 always beats 404)
       └─ require_member  is the caller in it?     403 if not
            └─ require_admin  ...as an admin?      403 if not
            └─ get_task  which task is in the URL? 404 if no such number

Each link is a plain function that FastAPI calls before the route body. A route asks for the deepest link it needs and gets every check above it for free — Depends(require_admin) means “valid token, real project, member, admin”, in that order, and the route body never runs unless all four held. Here are the two links that carry the authorisation:

def get_project(
    key: str,
    _: User = Depends(current_user),
    db: Session = Depends(get_db),
) -> Project:
    # The key is uppercased first. The model uppercases on *write*; this is
    # the matching read side, so /projects/web finds WEB. Language models in
    # particular will send lowercase.
    project = db.scalar(select(Project).where(Project.key == key.upper()))
    if project is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=(
                f"No project with key {key.upper()!r}. "
                "GET /projects lists the ones you belong to."
            ),
        )
    return project


def require_member(
    user: User = Depends(current_user),
    project: Project = Depends(get_project),
    db: Session = Depends(get_db),
) -> ProjectMember:
    # Composite primary key, so `db.get` takes a tuple in PK column order.
    member = db.get(ProjectMember, (project.id, user.id))
    if member is None:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail=f"You are not a member of project {project.key!r}",
        )
    return member


def require_admin(
    member: ProjectMember = Depends(require_member),
    project: Project = Depends(get_project),
) -> ProjectMember:
    # Note what this reads: the project_members row, loaded on *this*
    # request. Not the token.
    if member.role != MemberRole.ADMIN:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail=f"Only an admin of project {project.key!r} can do this",
        )
    return member

Line by line:

• key: str comes from the URL. FastAPI fills it from whichever route is using the dependency — /projects/{key}, /projects/{key}/tasks, /tasks/{key}-{number} — so one function serves every route with a key in its path.
• _: User = Depends(current_user) asks for the user and throws it away. That is the line the first draft didn’t have, and it is a security fix. The story is in “What went wrong”; the short version is that it makes “401 before 404” true for every route regardless of how that route happens to order its parameters. FastAPI caches each dependency per request, so the token is still decoded once.
• require_member returns the membership row, not None. The row carries the role, and several routes want it (“detail + my role”). Returning it costs nothing and saves a second query.
• 404 for a missing key, 403 for a project you’re not in. Not 404 for both. 403 is a different instruction to a client — “ask an admin” — than 404 — “check the key”. The MCP server will map them to different tool errors. A project’s key is not a secret in this app, so nothing leaks.
• require_admin reads the row from this request. This is last post’s decision, running. A token issued before Alice was promoted works as an admin token the moment the row changes. Nothing in the JWT is consulted for the role, because nothing in the JWT knows it.
• Every error message says what to do next. “GET /projects lists the ones you belong to.” That sentence is not for a human; it is for the language model that will read these errors in a few phases’ time. Errors are instructions.

Fifteen routes, five status codes

Here is the surface, straight from the design doc. Three routes existed after last post; the rest are new.

GET    /projects                          → projects I'm a member of
POST   /projects                          → create; I become admin
GET    /projects/{key}                    → detail + my role
GET    /projects/{key}/members            → list members
POST   /projects/{key}/members            → add member          [admin]
DELETE /projects/{key}/members/{user_id}  → remove member       [admin]

GET    /projects/{key}/tasks              → filters: status, assignee_id, overdue
POST   /projects/{key}/tasks              → create              [member]
GET    /tasks/{key}-{number}              → detail
PATCH  /tasks/{key}-{number}              → partial update
DELETE /tasks/{key}-{number}              → delete              [admin]

GET    /tasks/{key}-{number}/comments
POST   /tasks/{key}-{number}/comments

GET    /me/tasks                          → assigned to me, across projects
GET    /me/capabilities                   → { can_admin_any_project, admin_project_keys }

Every one of them answers with one of five status codes when it says no, and the code is chosen by what the client should do about it:

The 409/422 line is the one most APIs blur. Duplicate project key, already a member, removing the last admin, assigning a non-member — all of those are valid requests that happen to clash with the current state, so they are 409 with a sentence. 422 is reserved for “this JSON is wrong” and keeps FastAPI’s list-shaped validation body. One status per body shape; a client can branch on the number alone.

Projects: the join is the authorisation

</>projects.py has the six project and member routes. Two of them carry an idea each.

@router.get("", response_model=list[ProjectOut])
def list_projects(user: User = Depends(current_user), db: Session = Depends(get_db)) -> list[ProjectOut]:
    """Projects the caller belongs to, with their role in each.

    The join *is* the authorisation: there's no "all projects" list. If you
    aren't in project_members for a project, it does not exist for you.
    """
    rows = db.execute(
        select(Project, ProjectMember.role)
        .join(ProjectMember, ProjectMember.project_id == Project.id)
        .where(ProjectMember.user_id == user.id)
        .order_by(Project.key)
    ).all()
    return [_project_out(project, role) for project, role in rows]

There is no SELECT * FROM projects anywhere in the codebase. The only way to list projects is through the membership table, so the question “may Bob see this project?” never has to be asked — the query cannot return a project Bob isn’t in. Filtering after the fact is a check someone can forget; joining is a shape the data has.

The second is remove_member, and it is the decision Phase 1 deferred. The composite foreign key on tasks is RESTRICT: the database refuses to delete a membership while any task still points at it. Two options were on the table — a migration to ON DELETE SET NULL on the assignee column, or keep the strict rule and make the one route that removes members do the unassigning.

    if target.role == MemberRole.ADMIN:
        admins = db.scalar(
            select(func.count()).select_from(ProjectMember)
            .where(ProjectMember.project_id == project.id, ProjectMember.role == MemberRole.ADMIN)
        )
        if admins == 1:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail=f"Cannot remove the last admin of project {project.key!r}. Add another admin first.",
            )

    # Unassign first (Core UPDATE — the model's `onupdate` still bumps
    # `updated_at`), then delete. Same transaction; one commit.
    db.execute(
        update(Task)
        .where(Task.project_id == project.id, Task.assignee_id == user_id)
        .values(assignee_id=None)
    )
    db.delete(target)
    db.commit()

• We kept RESTRICT. The database never has to bend “an assignee is a member”, and remove_member is the only place that needs to know what to do about it. Both statements run in one transaction, so a crash between them leaves nothing half-done.
• A project must keep one admin. Otherwise nobody could ever manage it again. Removing the last one is a 409 with the fix in the message.
• Members are added by email; tasks are assigned by id. There is no user-search route, so an email is the only handle you have for someone not yet in the project. Assignees come from the members list, which carries ids. When the MCP server’s assign_task tool takes an email, translating it will be the MCP server’s job — the API stays one-shape.

Tasks: the one non-obvious query

</>tasks.py is the file the brief flags as the one with real logic in it. Most of it is plain: list with filters, get by ref, admin-only delete. The interesting part is how a task gets its number.

@router.post("/projects/{key}/tasks", response_model=TaskOut, status_code=http_status.HTTP_201_CREATED)
def create_task(
    body: TaskCreate,
    project: Project = Depends(get_project),
    member: ProjectMember = Depends(require_member),
    db: Session = Depends(get_db),
) -> Task:
    _check_assignee(db, project, body.assignee_id)

    db.execute(select(Project.id).where(Project.id == project.id).with_for_update())
    current_max = db.scalar(select(func.max(Task.number)).where(Task.project_id == project.id))
    next_number = (current_max or 0) + 1

    task = Task(
        project_id=project.id,
        number=next_number,
        title=body.title,
        # ...
        created_by=member.user_id,
    )
    db.add(task)
    db.commit()
    # Re-read so `assignee` (a view-only relationship, see models/task.py)
    # and the server-side timestamps reflect the row we just wrote.
    db.refresh(task)
    return task

• number is a per-project counter with no sequence behind it. The database only enforces that (project_id, number) is unique. So we compute MAX(number) + 1 ourselves — and two requests doing that at the same moment would both read 8, both try to insert 9, and the unique constraint would reject the loser with a 500.
• The fix is to take a lock first — but on what? SELECT … FOR UPDATE locks rows, and MAX(...) is an aggregate with no row to lock. So we lock the project’s own row instead. Any other creator in the same project waits at that line until this transaction commits, then reads the new max. Creators in different projects don’t wait for each other. “One task at a time per project” is a cost nobody will ever notice in a tracker.
• _check_assignee runs before any of it. The composite FK would reject a non-member assignee anyway, but as a 500 with a Postgres message. The check turns it into a 409 that says add them with POST /projects/{key}/members first.

Two smaller things in the same file are worth a sentence each. The path /tasks/{key}-{number} needs no parser: Starlette compiles it to a pattern that splits on the hyphen, number: int in the signature gets FastAPI’s conversion and a 422 for WEB-four, and project keys can’t contain hyphens (the schema’s regex says so), so the split is never ambiguous. And PATCH has to tell apart two things a client can say about a field:

class TaskPatch(BaseModel):
    """A partial update. Every field is optional; only the ones sent change.

    - **leave it out**  → don't touch it.
    - **send null**     → clear it (unassign, drop the due date).

    Pydantic tracks which fields were actually present in the JSON; the
    handler uses model_dump(exclude_unset=True) to get only those. So
    {"assignee_id": null} unassigns, and {} changes nothing.

    title, status and priority can't be cleared — a task always has them.
    Declaring them as str (not str | None) with a default of None gets
    exactly that: leaving them out is fine (the default is never validated),
    sending null is a 422 instead of a NOT NULL error from the database.
    It reads oddly, hence this paragraph.
    """

    title: str = Field(default=None, min_length=1, max_length=500)  # type: ignore[assignment]
    description: str | None = None
    status: TaskStatus = None  # type: ignore[assignment]
    priority: TaskPriority = None  # type: ignore[assignment]
    assignee_id: uuid.UUID | None = None
    due_date: date | None = None

That str = None with a type: ignore is deliberately odd-looking, and the docstring exists so nobody “fixes” it. It is the shortest way to get “absent is fine, null is a 422” for a field that must always have a value.

Relationships, and a stale read

Task responses carry "ref": "WEB-14" and a nested assignee object rather than a bare UUID, because a language model — and a human — reads “WEB-14, assigned to Bob” far better than two identifiers. Both come from the ORM model, not from hand-built dicts. Commit </>task.py adds four relationships and two properties, and changes nothing in the database — a relationship only tells the ORM how to read rows that already point at each other. One of them has a catch:

# The assignee, as a User. Two things to know:
#
# 1. `assignee_id` has no FK to `users` — the only constraint on it is the
#    composite FK into project_members. The ORM can't infer a join from
#    that, so `primaryjoin` spells the join out.
# 2. `viewonly=True`: this attribute is for *reading*. Writes go through
#    `assignee_id`, so the composite FK is always what the database
#    checks. The cost — the first draft of phase 3 hit it — is that after
#    you change `assignee_id` the ORM does NOT re-read this attribute on
#    its own. A route that changes the assignee must `db.refresh(task)`
#    before returning it, or it answers with the old person.
assignee: Mapped["User | None"] = relationship(
    "User",
    primaryjoin="Task.assignee_id == User.id",
    foreign_keys="Task.assignee_id",
    viewonly=True,
    lazy="selectin",
)


@property
def ref(self) -> str:
    """The human (and language-model) identifier: "WEB-14"."""
    return f"{self.project.key}-{self.number}"

• The join is spelled out because there is no plain FK to follow. Phase 1 deliberately did not add assignee_id → users.id alongside the composite key. The ORM therefore cannot guess the relationship; primaryjoin tells it.
• viewonly=True keeps writes going through assignee_id, so the composite FK is always the thing being checked. The consequence is the third bug in “What went wrong”.
• ref is a property, not a column. Pydantic’s from_attributes reads it like any other attribute. No column that would have to be kept in sync with project.key.
• The loading strategies are chosen, not defaulted. joined where the child is never shown without the parent (a task always shows its project key); selectin for assignees on a list, so fifteen tasks cost one extra query rather than fifteen. Relationships hide queries — worth knowing which ones you are hiding.

/me/capabilities: the route that exists for the MCP server

One route in </>me.py has no purpose in the frontend at all. It is here because a later phase needs it, and what it returns is a design decision:

class CapabilitiesOut(BaseModel):
    """What the caller is allowed to do, as facts — not as tool names.

    This shape exists for the MCP server: before it shows a user their
    tool list, it asks this route and hides admin-only tools from people
    who can't admin anything. The backend answers with facts about
    *membership*; deciding which tools those facts unlock is the MCP
    server's job. That way the rule "only admins delete" is written once,
    here, and the gate can never drift from it.
    """

    can_admin_any_project: bool
    admin_project_keys: list[str]

The backend does not know what a tool is. It knows who is an admin of what, and it says so, read fresh from project_members on every call. Which tools those facts unlock is decided in exactly one other place, later. If the backend returned tool names, the rule would live in two systems and drift within a month.

How you know it holds

Sixty-six new tests, eighty-seven in total, every one through HTTP. Two fixtures in </>conftest.py make them cheap enough to write that way:

@pytest.fixture
def seeded(db: Session) -> None:
    """Load the demo dataset into the (truncated) tables."""
    seed.main()


# Tokens are cached for the whole pytest run, across the TRUNCATE that
# separates tests. That works — and is worth understanding — because a JWT
# is a signed statement "this is user <id>", not a server-side session. The
# seed gives Alice the same uuid5 id every time, so a token minted in test 1
# still names the Alice that test 40 just re-seeded.
_TOKENS: dict[str, str] = {}


@pytest.fixture
def token_for(client: TestClient, seeded: None) -> Callable[[str], str]:
    def _get(email: str, password: str = SEED_PASSWORD) -> str:
        if email not in _TOKENS:
            r = client.post("/auth/login", json={"email": email, "password": password})
            assert r.status_code == 200, r.text
            _TOKENS[email] = r.json()["access_token"]
        return _TOKENS[email]

    return _get

• Tests run against the seed, so they can say WEB-4 and BOB instead of building fixtures. Re-seeding is about thirty inserts because the seed caches its one bcrypt hash per process.
• token_for does a real login — the way the frontend and the MCP server will get theirs — once per user per run, then reuses it. Three bcrypt calls instead of three per test.
• The cache surviving TRUNCATE is a small JWT lesson. A token is a signed claim about an id, not a session the server stores. Alice’s id is uuid5 of her email, so the Alice test 40 re-seeds has the same id the token names. current_user looks it up fresh, finds her, and carries on.

Two tests are the ones a later post will point back at:

def test_delete_task_as_member_is_403(client: TestClient, token_for) -> None:
    """THE boundary test. In phase 8 the MCP server will hide `delete_task`
    from members — that's a courtesy. This 403 is the rule, and it holds
    even when a member calls the route directly."""
    r = client.delete("/tasks/WEB-4", headers=bearer(token_for(BOB)))
    assert r.status_code == 403, r.text
    assert r.json()["detail"] == "Only an admin of project 'WEB' can do this"
    assert client.get("/tasks/WEB-4", headers=bearer(token_for(BOB))).status_code == 200

def test_project_route_without_token_is_401_even_for_unknown_key(client: TestClient, seeded: None) -> None:
    """Pins "401 beats 404": `get_project` asks for `current_user` before it
    looks the key up. The first draft didn't, and a route that listed
    `get_project` before `require_member` let an anonymous caller probe which
    keys exist by comparing 404 against 401."""
    assert client.get("/projects/WEBB").status_code == 401
    assert client.get("/projects/WEB").status_code == 401

The first is the fence around the rule the whole series leans on. The second is a fence around a bug — which brings us to the bugs.

What went wrong

The Phase 2 tamper test was flaky, and it was the test’s fault

Step zero of Phase 3 was “run the Phase 2 suite before touching anything”. test_me_with_tampered_signature_is_401 failed: assert 200 == 401. Six reruns passed. The test — you read it last post — flipped the last base64url character of the signature.

An HS256 signature is 32 bytes. base64url spells 32 bytes in 43 characters, and 43 × 6 bits is 258 bits of text for 256 bits of data. The last character’s low two bits are padding the decoder throws away. So when the real last character was B, C or D — or A, which the test flipped to B — the “tampered” string decoded to the identical signature and verified. Four characters in sixty-four: about one run in sixteen. The fix in </>test_auth.py is one line:

-    bad_sig = sig[:-1] + ("A" if sig[-1] != "A" else "B")
+    bad_sig = ("A" if sig[0] != "A" else "B") + sig[1:]

Every bit of the first character is real. Nothing in the PyJWT or base64 docs will tell you that two different base64url strings can decode to the same bytes when the length isn’t a multiple of four. The older lesson: a test that “tampers” with an encoded value has to tamper with the decoded value.

404 before 401 — an anonymous caller could probe project keys

test_project_route_without_token_is_401_even_for_unknown_key failed on its first run: GET /projects/WEBB with no token returned 404, while GET /projects/WEB returned 401. Compare the two and you know which keys exist, without logging in.

FastAPI resolves a route’s dependencies in the order they are declared as parameters. require_member had current_user first, correctly — but the route get_project_detail listed project: Depends(get_project) before member: Depends(require_member), so the key lookup ran before any token check. Getting the order right inside one dependency isn’t enough; every route that uses it would have to get it right too, forever.

The fix is the _: User = Depends(current_user) you saw in get_project. The token check is inside the lookup, so it happens first on every route no matter how the parameters are ordered. The FastAPI docs describe resolution order for sub-dependencies but don’t call out that a route’s own parameter order is part of the picture, or that it has a security consequence. If a guarantee depends on ordering, make the ordering structural, not conventional.

The PATCH response showed the old assignee

Caught during design — the scenario was run against the seeded database before any code was written — so it never reached a test, but it is exactly the bug the first draft would have shipped. Reassign WEB-4 from Bob to Alice; the response body says Bob.

Task.assignee is a viewonly relationship; writes go through assignee_id. The session had already loaded assignee (Bob) when it loaded the task. Setting assignee_id and committing doesn’t re-read the relationship, and the session is configured to keep loaded objects after commit rather than re-fetch them. The serialiser read the cached Bob. One db.refresh(task) after the commit fixes it, and test_patch_task_response_shows_new_assignee_not_stale keeps it fixed. The SQLAlchemy docs for viewonly=True explain that the ORM won’t write through it. They don’t say “and it won’t notice when you write the underlying column either” — which follows, but only once you’ve been bitten.

Said plainly: what this backend does not do

• MAX + 1 reuses a deleted top number. Delete WEB-8, create a task, it is WEB-8 again — and an old comment or chat transcript saying “WEB-8” now points at something else. test_create_task_after_deleting_latest_reuses_number documents it on purpose. The fix is a counter column on projects behind the same row lock. Not done: nothing in the next ten phases needs it.
• No concurrency test for numbering. Two requests racing inside TestClient would need two real connections. The argument is the row lock; a load test belongs to the container phase at the earliest.
• overdue uses the server’s date, not the client’s timezone. No pagination — the seed has fifteen tasks. No project delete, rename, or “leave”; no comment edit or delete; no role change (promote by remove and re-add). Every feature must earn its place.
• The IntegrityError → 409 handler in main.py is untested by HTTP, on purpose. It exists for the race the handler-level checks can’t see: two requests that both pass “is the key taken?” and both insert. The loser gets a 409 naming the constraint instead of a 500. Making that race happen in a test needs two real connections; the argument for correctness is the constraint itself.

Run it

Check out the last Phase 3 code commit. The database from Phase 0 must be running; dependencies did not change this phase, but uv sync is harmless.

git clone https://github.com/Jayantkhandebharad/mcp-taskflow.git
cd mcp-taskflow
git checkout 6c6533a

cp .env.example .env
docker compose up -d db

cd fastapi-backend
uv sync
uv run alembic upgrade head
uv run python -m scripts.seed
uv run pytest                      # 87 passed

uv run uvicorn app.main:app --reload --port 8000

In a second terminal, log in as all three seeded people and walk the chain. Alice is admin of WEB and API; Bob is a member of WEB only; Carol is a member of API only. Every password is password.

login() {
  curl -s -X POST http://localhost:8000/auth/login -H 'Content-Type: application/json' \
    -d "{\"email\":\"$1\",\"password\":\"password\"}" \
    | python3 -c 'import json,sys; print(json.load(sys.stdin)["access_token"])'
}
ALICE=$(login alice@example.com); BOB=$(login bob@example.com); CAROL=$(login carol@example.com)

# 401 beats 404: anonymous, unknown key → still 401
curl -s -o /dev/null -w '%{http_code}\n' http://localhost:8000/projects/WEBB          # 401

# a real user, an unknown key → 404 with an instruction
curl -s http://localhost:8000/projects/WEBB -H "Authorization: Bearer $ALICE"
# {"detail":"No project with key 'WEBB'. GET /projects lists the ones you belong to."}

# a real project the caller isn't in → 403, not 404
curl -s http://localhost:8000/projects/WEB -H "Authorization: Bearer $CAROL"
# {"detail":"You are not a member of project 'WEB'"}

Then the task lifecycle, and watch who is allowed to do what:

# Bob (member) creates a task → 201, and it gets the next number
curl -s -X POST http://localhost:8000/projects/WEB/tasks -H "Authorization: Bearer $BOB" \
  -H 'Content-Type: application/json' -d '{"title":"Write the phase 3 post"}' | python3 -m json.tool | grep -E '"ref"|"assignee"'
#     "ref": "WEB-9",
#     "assignee": null,

# Alice tries to assign it to Carol, who isn't in WEB → 409 that says what to do
CAROL_ID=$(curl -s http://localhost:8000/auth/me -H "Authorization: Bearer $CAROL" | python3 -c 'import json,sys; print(json.load(sys.stdin)["id"])')
curl -s -X PATCH http://localhost:8000/tasks/WEB-9 -H "Authorization: Bearer $ALICE" \
  -H 'Content-Type: application/json' -d "{\"assignee_id\":\"$CAROL_ID\"}"
# {"detail":"User ... is not a member of project 'WEB'. Add them with POST /projects/WEB/members first, ..."}

# Bob tries to delete it → 403. The gate is a courtesy; this is the rule.
curl -s -X DELETE http://localhost:8000/tasks/WEB-9 -H "Authorization: Bearer $BOB"
# {"detail":"Only an admin of project 'WEB' can do this"}

# Alice deletes it → 204
curl -s -o /dev/null -w '%{http_code}\n' -X DELETE http://localhost:8000/tasks/WEB-9 -H "Authorization: Bearer $ALICE"   # 204

# and the route that exists for the MCP server: facts, not tool names
curl -s http://localhost:8000/me/capabilities -H "Authorization: Bearer $BOB"
# {"can_admin_any_project":false,"admin_project_keys":[]}
curl -s http://localhost:8000/me/capabilities -H "Authorization: Bearer $ALICE"
# {"can_admin_any_project":true,"admin_project_keys":["API","WEB"]}

Finally, open http://localhost:8000/docs. Thirteen paths, every request and response shape, generated from the type hints. Paste a token into the bearer box and the whole API is clickable.

What’s next

The app is complete from the database up to the API, and nobody has clicked anything. Next post is the frontend: five screens in React against exactly these routes, one api.ts, and a scripted walkthrough so the screenshots are reproducible. It will be the shortest post in the series, because every rule it displays was already enforced here. Then the part this series exists for begins: the first MCP server.

• Authorisation is a chain of dependencies. Ask for the deepest link; get every check above it in a fixed order.
• 401 before 404 before 403 — and make the order structural, not a convention every route has to remember.
• Roles are read from the row on every request. require_admin never looks at the token.
• Per-project numbers: lock the parent row, then MAX + 1. An aggregate has no row to lock.
• PATCH: “left out” and “sent null” are different sentences. exclude_unset hears the difference.
• /me/capabilities returns facts about membership, not tool names. The rule lives once.
• A test that tampers with an encoded value must change a bit that is actually there.