Designing the Data

Last post ended with a running Postgres and zero tables. That was the correct result for Phase 0, and it’s also the least interesting database in the world. This post is where TaskFlow gets its shape. We design five tables, decide which rules the database itself will enforce, write a migration that creates them, and fill them with data believable enough to demo against. By the end you’ll be able to type a query and get Alice back.

Five tables, read top to bottom

Here is the whole data model. Read it from the top: each table depends only on the ones above it, which is also the order the migration creates them and the reverse of the order the seed deletes them.

users ──┬──< project_members >──┬── projects
        │                       │
        │                       └──< tasks ──< comments
        └───────────────────────────┘
             (assignee, created_by, author)

Two of those columns exist because of what comes later in the series, and they’re worth calling out now so they don’t look arbitrary.

• projects.key is there for the language model. Every row has a UUID primary key, but nobody — human or AI — says “move 3f2b8c91-… to in progress”. They say “move WEB-14”. A model writing get_task("WEB-14") gets it right far more often than one copying a 36-character UUID out of an earlier tool result. Designing identifiers a model can hold in its head is a real MCP skill, and this column is where we start practising it.
• project_members is its own table, not a JSON column on projects. Every question the app will ever ask about permissions — may Bob see this project, may Alice delete this task — is answered by looking up one row here. Keeping it as a proper table with a proper primary key is also what makes the trick in the next section possible.

Where the rules live

Before a single line of Python, the design doc listed three rules the database itself must enforce:

1. A task’s assignee must be a member of the task’s project.
2. Within a project, task numbers are unique — there is only one WEB-14.
3. Deleting a task deletes its comments.

The word itself is doing the work in that sentence. There are two places a rule like “assignee must be a member” can live:

• In application code. Every route that creates or updates a task runs a check first. It works — until someone adds a new write path (a bulk import, an admin script, the MCP server three phases from now) and forgets. The rule is only as good as the memory of whoever writes the next feature.
• In the schema. The database refuses the bad row. There is no write path that skips it, because the check happens below every write path. Nobody has to remember anything.

Rules two and three are easy — a unique constraint and an ON DELETE CASCADE. Rule one is the one most schemas get wrong, and it’s the reason this post exists.

The composite foreign key

Start with the obvious way to write “a task has an assignee”:

tasks.assignee_id  →  users.id

Read what that actually promises: the assignee is some user who exists. That’s all. It says nothing about whether that user is a member of this project. Carol, who is only in the API project, could be assigned a WEB task and the database would shrug. The real rule would have to live in code.

Now look at what project_members gives us. Its primary key is the pair (project_id, user_id). A foreign key can point at any primary key, including a two-column one. So instead of pointing assignee_id at users, we point the pair (project_id, assignee_id) at project_members:

tasks.(project_id, assignee_id)  →  project_members.(project_id, user_id)

Read that promise: whatever project this task is in, the assignee has a membership row in that same project. The rule is now a physical property of the storage. Here it is in the model, from </>task.py:

# Nullable on purpose — see below. The composite FK ties this to
# project_members when it *is* set.
assignee_id: Mapped[uuid.UUID | None] = mapped_column(
    UUID(as_uuid=True), nullable=True
)

__table_args__ = (
    # Per-project ticket number is unique — this is what "WEB-14" rests on.
    sa.UniqueConstraint("project_id", "number", name="uq_tasks_project_number"),
    # THE composite FK. This is the invariant this file exists for.
    sa.ForeignKeyConstraint(
        ["project_id", "assignee_id"],
        ["project_members.project_id", "project_members.user_id"],
        name="fk_tasks_assignee_is_project_member",
    ),
)

Line by line:

• assignee_id has no ForeignKey of its own. That’s deliberate. The composite constraint below is the only thing that references it. We don’t also add a plain assignee_id → users.id, because project_members already points at users, so the composite key implies it. Two overlapping constraints are harder to reason about than one.
• __table_args__ is where SQLAlchemy puts table-level constraints — anything that spans more than one column. Both of our multi-column rules live here.
• UniqueConstraint("project_id", "number") is rule two. Not number alone — WEB-1 and API-1 are different tasks — but the pair.
• ForeignKeyConstraint([...], [...]) takes two lists: our columns, and the columns they point at, in the same order. That ordering is the whole constraint.
• name= gives the constraint a name we chose. When the database rejects a row, this name is in the error message — and a test later in this post asserts on it.

Three properties of this constraint are worth knowing, because two of them are surprising:

• Unassigned tasks still work. A foreign key with a NULL in any of its columns is simply not checked — that’s SQL’s default matching rule, called MATCH SIMPLE. So assignee_id IS NULL passes through, which is exactly what “unassigned” should do. A hand-written check in application code would have to remember this case explicitly. The schema gets it for free.
• You can’t remove a member who still has tasks. Deleting a project_members row that a task points at fails, because the default action on delete is RESTRICT. This is the honest trade: the app will have to unassign first. We think it’s the right rule, and we’ll meet it for real in Phase 3 when the “remove member” route lands.
• The membership table had to have that primary key. If project_members had a surrogate id column as its primary key, with (project_id, user_id) merely unique, this would still work — a foreign key can target a unique constraint too. But it reads more naturally when the pair is the identity, and there’s nothing else that identity would be for.

Two layers for the small rules

Emails must be lowercase (so Alice@Example.com and alice@example.com are one account) and project keys must be uppercase (so the UI never shows “web-14”). These are small rules, and they’re enforced twice on purpose. From </>user.py:

__table_args__ = (
    # The DB-level backstop for email casing.
    sa.CheckConstraint("email = lower(email)", name="email_lowercase"),
)

@validates("email")
def _lowercase_email(self, _key: str, value: str) -> str:
    # Called by SQLAlchemy whenever `user.email = ...` is set. Fires on
    # ORM writes only — the CHECK above covers everything else.
    return value.lower() if value is not None else value

• The @validates hook is the friendly layer. Any code that goes through the ORM gets its email lowercased silently. Well-behaved writers never see an error.
• The CheckConstraint is the backstop. Raw SQL, a migration, someone in psql — anything that bypasses the ORM still can’t write a mixed-case email. It’s the same “code can lie, schema can’t” instinct, at small scale.

The same pair — a validator plus a CHECK — guards projects.key, only with upper(). And one more detail hides in that snippet: the constraint is named email_lowercase in the model but shows up as ck_users_email_lowercase in the database. That prefix comes from a naming convention we set once, on the shared declarative base in </>base.py. Without it, SQLAlchemy invents names like ck_1 and fk_2 on the fly, two developers get different names for the same constraint, and every migration diff fills with noise. One dictionary, set in Phase 1, pays for itself on every migration afterwards.

From models to a migration

The models describe the tables to Python. A migration is the script that actually creates them in Postgres — and, later, changes them without losing data. We use Alembic, the migration tool that comes with SQLAlchemy. Its </>env.py is wired to read the same settings the app does, so there’s exactly one place that knows how to reach the database.

Alembic can generate a migration automatically by diffing the models against a live database, and from the next migration onwards that’s what we’ll do. The first one we wrote by hand. Autogenerate would have produced the same DDL, but as a wall of op.create_table calls with no comments — and this is the one migration people will actually open and read. Writing it ourselves means the composite key is explained at the point the DDL happens, not only in a Python file three folders away. Here is the tasks table, from </>0001_initial.py:

op.create_table(
    "tasks",
    sa.Column("id", sa.dialects.postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column("project_id", sa.dialects.postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column("number", sa.Integer(), nullable=False),
    sa.Column("title", sa.Text(), nullable=False),
    sa.Column("status", task_status, nullable=False),
    sa.Column("priority", task_priority, nullable=False),
    sa.Column("assignee_id", sa.dialects.postgresql.UUID(as_uuid=True), nullable=True),
    # ... created_by, due_date, created_at, updated_at ...

    sa.PrimaryKeyConstraint("id", name=op.f("pk_tasks")),
    # "WEB-14" — a task's per-project number is unique.
    sa.UniqueConstraint("project_id", "number", name="uq_tasks_project_number"),
    # Plain FK to the project the task lives in.
    sa.ForeignKeyConstraint(
        ["project_id"], ["projects.id"],
        name=op.f("fk_tasks_project_id_projects"), ondelete="CASCADE",
    ),
    # The one every reader should stop and read.
    sa.ForeignKeyConstraint(
        ["project_id", "assignee_id"],
        ["project_members.project_id", "project_members.user_id"],
        name="fk_tasks_assignee_is_project_member",
    ),
)

It’s the same shape as the model, one layer down. The only new thing is op.f(...), which tells Alembic “this name is already final, don’t apply the naming convention to it again”. The two constraints we named by hand in the model are passed through as plain strings, because they already are the final name.

What went wrong: the enum that got created twice

Three columns use Postgres enum types — real database types with a fixed list of allowed values, so a status of "dnoe" is rejected at the storage layer, not just by the app. Two tables share member_role… and that’s where the first run of the migration died:

psycopg.errors.DuplicateObject: type "member_role" already exists

The migration creates each enum type explicitly, once, at the top of upgrade(). We then told each column not to create the type again, with create_type=False. What we found is that the generic sa.Enum does not honour that flag reliably when the same enum is referenced from more than one table — the first create_table emits a CREATE TYPE, and so does the second. The fix is one import:

from sqlalchemy.dialects import postgresql

# Why postgresql.ENUM and not the generic sa.Enum: in current SQLAlchemy +
# Alembic, the generic sa.Enum emits a CREATE TYPE per table that uses it,
# even with create_type=False set. The dialect-specific one honours the flag.
member_role = postgresql.ENUM(
    "admin", "member",
    name="member_role",
    create_type=False,
)

def upgrade() -> None:
    bind = op.get_bind()
    # Create the enum types first, once. Every sa.Column(member_role, ...)
    # below has been told not to try creating the type again.
    member_role.create(bind, checkfirst=False)
    ...

A seed you can run twice

An empty schema is correct and useless. The seed script fills it with demo data: three users, two projects, fifteen tasks, four comments. Most tutorials treat this as a throwaway. We didn’t, because two properties of a seed decide whether the rest of the series is pleasant or painful. From </>seed.py:

# A fixed UUID5 namespace so uuid5(NAMESPACE, "alice@example.com") is
# reproducible across machines. The value itself is arbitrary — what matters
# is that it never changes.
NAMESPACE = uuid.UUID("6c8a1b2e-7d3f-4e5a-9b0c-1f2d3e4a5b6c")


def _id(kind: str, *parts: str) -> uuid.UUID:
    """Return a deterministic UUID for a named object.

    _id("user", "alice@example.com") and _id("task", "WEB", "1")
    both always produce the same UUID.
    """
    return uuid.uuid5(NAMESPACE, ":".join([kind, *parts]))


def main() -> None:
    """Wipe the five tables and re-seed them, all in one transaction."""
    with SessionLocal.begin() as db:  # opens a transaction, commits on success
        _wipe(db)
        _seed(db)
    print("seeded 3 users, 2 projects, 15 tasks, 4 comments")

• Deterministic. uuid.uuid5 hashes a name into a UUID, and the same name always gives the same UUID. Alice’s id is derived from her email, WEB-4’s id from "task:WEB:4". So Alice has the same primary key on your laptop, on mine, and in CI. A query in this post works for every reader, and tests can hard-code an id without ever flaking.
• Rerunnable. main() deletes every row and inserts everything fresh, inside one transaction. Either the whole seed lands or nothing does. There’s no “skip if the table isn’t empty” branch and no upsert logic — both of those need editing every time the dataset changes. Wipe-then-insert is the shortest thing that survives schema changes.

The dataset itself is small but chosen, not random. Alice is admin of both projects. Bob is only in WEB, Carol is only in API — so “assign a WEB task to Carol” is a clean test of the composite key. Every status and priority appears at least once, a few tasks are overdue, and a couple are unassigned, so the MCP tools we build later (overdue_tasks, project_summary) have something interesting to say.

How you know the rules hold

Six tests. None of them test the happy path — the seed already proves that. Each one tries to write bad data and asserts that the database refuses it, because “the code checked” is not evidence that the schema does. The marquee test, from </>test_invariants.py:

def test_assigning_task_to_non_member_is_rejected(db: Session) -> None:
    alice = _mk_user(db, "alice@example.com")
    bob = _mk_user(db, "bob@example.com")
    web = _mk_project(db, "WEB", admin=alice)
    # Note: Bob is deliberately NOT added to WEB.

    with pytest.raises(IntegrityError) as exc:
        _mk_task(db, web, number=1, created_by=alice, assignee=bob)

    # Belt-and-suspenders: assert it's *our* FK that fired, not some other
    # constraint. If a future migration renames it, this test will tell us.
    assert "fk_tasks_assignee_is_project_member" in str(exc.value)

• The setup is the point. Bob exists as a user but has no membership in WEB. A plain assignee_id → users.id foreign key would have let this insert through.
• IntegrityError is what SQLAlchemy raises when the database rejects a constraint. The test isn’t catching a validation error from our own code — there is no such code.
• The last line is deliberately fussy. It checks the constraint’s name is in the error, so a rejection from some other constraint can’t make the test pass by accident. It also means a future migration that renames the key has to come and edit this line. That’s a feature.

The companion test, test_unassigned_task_is_allowed, inserts a task with assignee=None and asserts it lands — proving we didn’t break “unassigned” while enforcing the rule. And test_email_lowercase_enforced_by_check bypasses the ORM entirely with raw SQL, so the validator hook can’t help, and asserts the CHECK constraint fires. The seed gets one test of its own in </>test_seed.py: run it twice, snapshot the row counts and every primary key after each run, and assert the two snapshots are identical.

Closing a loop from Phase 0

Last post’s .env had the database password written twice: once as POSTGRES_PASSWORD, and again inside a full DATABASE_URL. We flagged it as ugly and left it, because Docker Compose can’t interpolate one variable into another inside .env itself, and there was no code yet to fix it in. Now there is. The backend’s </>config.py reads the parts and assembles the URL:

class Settings(BaseSettings):
    postgres_host: str = Field(default="localhost", alias="POSTGRES_HOST")
    postgres_port: int = Field(default=5432, alias="POSTGRES_PORT")
    postgres_user: str = Field(default="taskflow", alias="POSTGRES_USER")
    postgres_password: str = Field(default="taskflow_dev_only", alias="POSTGRES_PASSWORD")
    postgres_db: str = Field(default="taskflow", alias="POSTGRES_DB")

    @computed_field
    @property
    def database_url(self) -> str:
        # postgresql+psycopg:// selects the psycopg 3 driver (not psycopg2,
        # which SQLAlchemy would pick if we just said postgresql://).
        return (
            f"postgresql+psycopg://{self.postgres_user}:{self.postgres_password}"
            f"@{self.postgres_host}:{self.postgres_port}/{self.postgres_db}"
        )

One source of truth for the password, and the DATABASE_URL line is gone from .env.example. The cost is one new variable, POSTGRES_HOST. It’s localhost for now, because the backend runs on your machine and talks to the database through the port Compose publishes. When the backend moves into its own container it will flip to db, the Compose service name. One variable, two answers, set per environment.

What went wrong: passlib versus bcrypt

The seed needs real password hashes, so the demo users can actually log in once Phase 2 exists. We use bcrypt through passlib, as the design doc said. The first import blew up:

ValueError: password cannot be longer than 72 bytes, truncate manually if necessary

We hadn’t hashed anything yet. The error comes from passlib’s startup probe: on load it verifies a deliberately over-long string to detect an ancient bcrypt bug. Newer bcrypt releases stopped tolerating that and raise instead, and passlib — unmaintained since 2020 — can’t handle the raise. Neither project’s changelog mentions the other. The fix for Phase 1 is a pin in </>pyproject.toml, with the reason written next to it:

# Why the extra pin on bcrypt: passlib 1.7.4 is unmaintained, and on load it
# probes its bcrypt backend by verifying a >72-byte string. bcrypt 5.0 raises
# outright, and passlib fails to import. Pinning to <4.1 keeps the probe quiet
# until we migrate off passlib (pwdlib / bcrypt directly) in a later phase.
"passlib>=1.7.4,<2",
"bcrypt>=4.0,<4.1",

We’re not proud of a pin, but a pin with a comment is honest, and the smaller change was the right one for a phase that isn’t about auth. When Phase 2 is about auth, we’ll decide whether to leave passlib behind.

Run it

Start from the last Phase 1 commit, so your tree matches every snippet above. You need the database from last post running, and uv installed.

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

# the database, from last post
cp .env.example .env
docker compose up -d db

# the data layer
cd fastapi-backend
uv sync                          # installs from uv.lock into .venv/
uv run alembic upgrade head      # creates the five tables
uv run python -m scripts.seed    # → seeded 3 users, 2 projects, 15 tasks, 4 comments
uv run python -m scripts.seed    # run it again — same output, same data

# the proof
uv run pytest                    # 6 passed

Then go and find Alice. Her id is the same on every machine, because it’s uuid5 of her email:

docker compose exec db psql -U taskflow -d taskflow -c \
  "SELECT id, email, full_name FROM users WHERE email = 'alice@example.com'"

#                   id                  |       email       |  full_name
# --------------------------------------+-------------------+-------------
#  96ef51a2-db48-5b2c-9d4b-19aec65e71c2 | alice@example.com | Alice Adams

And try to break the rule yourself. Carol is only a member of API. Assign her a WEB task by hand and watch the database — not any Python — say no:

docker compose exec db psql -U taskflow -d taskflow -c \
  "UPDATE tasks SET assignee_id = '453b06c8-de8e-5974-91dc-ecb7dee595d9'
   WHERE id = 'e2571807-6216-5642-af48-b6cffd91bea5'"   # Carol → WEB-4

# ERROR:  insert or update on table "tasks" violates foreign key constraint
#         "fk_tasks_assignee_is_project_member"

That error message is the whole post in one line. No route was called, no validator ran, and the bad row still didn’t land.

What’s next

We have tables, rules the database keeps, and data to point at. Nobody can log in yet. Next post is auth, understood: register, login, and a JWT — and the current_user dependency that every route, and eventually every MCP tool, will lean on. It’s the second half of the permission story that project_members started here.

• Five tables. The rules that matter are constraints in the schema, not checks in the code.
• A composite foreign key makes “assignee must be a member” impossible to violate — and lets NULL through for free.
• Enforce small rules twice: a validator for friendly errors, a CHECK as the backstop.
• Seed with uuid5 ids and wipe-then-insert, so every reader has the same Alice.
• In Alembic, shared enums want postgresql.ENUM. And pin bcrypt if you’re still on passlib.