Module 8: Final Project — TaskFlow API

You've reached the end of the guide. Modules 1-7 taught you 7 patterns in isolation — cursor pagination, soft delete, audit logs, multi-tenancy with RLS, zero-downtime migrations, optimistic locking, bulk operations. Each with its own mini-project to internalize the individual pattern. But production isn't those patterns in isolation — it's all seven operating together in a real codebase.

TaskFlow is the integrating project. A multi-tenant SaaS API in FastAPI 0.110+ with SQLAlchemy 2.0 async + asyncpg + PostgreSQL 16 + Alembic + PgBouncer. You'll build it by applying the 7 patterns in a single codebase. When you finish, you'll have a GitHub repo with code, migrations, isolation tests, measured benchmarks, and a runbook for a zero-downtime migration executed live with wrk running and ZERO failed requests.

It's the deliverable you'll link from your resume. When a senior interviewer asks you "do you have experience with multi-tenancy?", you answer "yes, look at this project where I implemented RLS with aggressive isolation tests". When they ask "have you ever done a zero-downtime migration?", "yes, look at this runbook where I ran expand-contract with real traffic". And so on.

---

What is TaskFlow?

TaskFlow is a multi-tenant SaaS task-management API. Each tenant (client company) has users who belong to projects, which contain tasks.

Domain model

tenants
  └── users (each user belongs to a tenant)
       └── projects (each project belongs to a tenant)
            └── tasks (each task belongs to a project + tenant)
                 └── audit.task_log (full audit of changes to tasks)

Technical stack

• API: FastAPI 0.110+, async end-to-end.
• DB: PostgreSQL 16 with RLS enabled.
• ORM: SQLAlchemy 2.0 async + asyncpg 0.29+.
• Migrations: Alembic 1.13+.
• Pool: PgBouncer in transaction mode.
• Auth: JWT (simple mock for the exercise).
• Test: pytest + pytest-asyncio + httpx + testcontainers (real Postgres).

Implemented endpoints

# Auth (mock)
POST /auth/login         → JWT with tenant_id

# Projects
GET    /projects          → list the tenant's projects
POST   /projects          → create a project

# Tasks
GET    /tasks             → cursor pagination
POST   /tasks             → create a task
GET    /tasks/{id}        → detail (with If-None-Match)
PUT    /tasks/{id}        → update (with If-Match → 412 / StaleDataError → 409)
DELETE /tasks/{id}        → soft delete (set deleted_at)
POST   /tasks/bulk        → bulk upsert with COPY + ON CONFLICT
GET    /tasks/{id}/history → audit log entries

---

Mapping: each pattern in TaskFlow

Pattern (module)	Implementation in TaskFlow
Cursor pagination (module 1)	GET /tasks with an opaque cursor based on (created_at DESC, id DESC)
Soft delete (module 2)	tasks.deleted_at TIMESTAMPTZ, partial index, WHERE deleted_at IS NULL in queries
Audit log (module 3)	audit.task_log table populated by PostgreSQL triggers on tasks
Multi-tenancy (module 4)	RLS enabled on every table with a tenant_id, a FastAPI dependency sets app.tenant_id
Zero-downtime migration (module 5)	Migration of priority INTEGER NOT NULL with expand-contract + wrk validating
Optimistic locking (module 6)	tasks.version with __mapper_args__, If-Match header, 412/409 with an informative body
Bulk operations (module 7)	POST /tasks/bulk with COPY into temp + INSERT...ON CONFLICT

7 patterns × 1 codebase. Each one defensible in code review.

---

Module plan

8 progressive capsules. Each one adds a layer to the project:

Capsule	Topic	What you add
01	Module introduction	You're here. Scope, optional scaffold, success criteria.
02	Initial setup: FastAPI + SQLAlchemy + Alembic	Project structure, base tables, first endpoint.
03	Multi-tenancy with RLS + mock auth	Tenant isolation, dependency with SET LOCAL app.tenant_id, mock JWT.
04	Task CRUD + cursor pagination + soft delete	GET /tasks with cursor, soft delete with a partial index.
05	Audit log with triggers + optimistic locking	PostgreSQL trigger → audit.task_log, If-Match header on PUT.
06	Bulk endpoint + live zero-downtime migration	POST /tasks/bulk + migration with wrk running.
07	Complete tests (isolation, integration, benchmarks)	Aggressive RLS test, integration tests, benchmarks.
08	Final documentation + wrap-up	BENCHMARKS.md, MULTITENANCY.md, RUNBOOK-MIGRATION.md.

Order suggestion: follow the capsules linearly. Each one builds on the previous. Atomic commits per capsule.

---

Success criteria (verifiable)

Your project is complete when you can show:

1. A public GitHub repo with code, migrations, tests, docs.
2. docker-compose up works and the app starts.
3. Tests pass with real Postgres (no mocks).
4. RLS isolation test passes: tenant A can't see tenant B's tasks.
5. Zero-downtime migration executed with wrk running: 0 errors in 600s of traffic.
6. BENCHMARKS.md with measured numbers, not abstract ones.
7. MULTITENANCY.md with justification of the architectural decision.
8. RUNBOOK-MIGRATION.md with actionable steps, not narrative ones.
9. README.md with reproducible instructions.

---

Scope: what's in and what's out

In scope

• The 7 patterns applied in TaskFlow.
• Automated tests with real Postgres.
• A zero-downtime migration executed and documented.
• Measured performance benchmarks.
• Documentation of architectural decisions.

Out of scope (mentioned at the end of the module)

• Rate limiting (topic for another guide).
• Idempotency keys for POSTs (mentioned as an improvement).
• Full observability (Prometheus metrics, distributed tracing — guide #15).
• Backup/disaster recovery (specific DBA guide).
• Deployment to a real cloud (DevOps guide).
• Frontend (TaskFlow is API-only).
• Real auth with OAuth/SSO (mock JWT is enough for the demo).
• Notifications, emails, WebSockets (not SQL patterns).

Pedagogical honesty: TaskFlow is portfolio-worthy, not production-ready out-of-the-box. It demonstrates mastery of SQL patterns in production. For real production, the layers listed above are missing.

---

Two routes: optional scaffold or from scratch

Route A: minimal scaffold

If you're short on time (or want to focus on the patterns, not the setup), you can clone a project scaffold:

git clone https://github.com/your-org/taskflow-scaffold
cd taskflow-scaffold

The scaffold has:

• Folder structure.
• docker-compose.yml.
• requirements.txt.
• pyproject.toml.
• An example User model.
• A base pytest test.

Your job: implement the patterns + tests + docs.

Route B: from scratch

If you have more time, build everything from scratch. The following capsules guide you step by step. More complete pedagogically.

Recommendation: route B if this is your first SaaS API project; route A if you already have experience with basic FastAPI and want to focus on the patterns.

---

Architectural decisions (documented in MULTITENANCY.md)

Three explicit decisions that the module justifies:

1. Multi-tenancy: shared schema + RLS (not schema-per-tenant)

Why shared schema with RLS:

• ✅ Low operational cost: a single DB, a single set of migrations, one pool.
• ✅ Cross-tenant analytics easy when needed (rare).
• ✅ Works perfectly up to hundreds/thousands of tenants.
• ❌ Doesn't isolate "performance" — a noisy tenant can affect others.

Alternatives ruled out:

• Schema-per-tenant: better isolation but N migrations, N times the operational complexity.
• DB-per-tenant: total isolation but N pools, N backups, N monitoring. Only justifiable at 100k+ tenants or strict compliance requirements.

2. Pagination: cursor (not OFFSET)

Why cursor:

• ✅ O(1) regardless of depth.
• ✅ Stable under concurrent writes.
• ❌ Doesn't allow "jump to a specific page N".

Mitigation: the UI uses "Load more" / infinite scroll, not numeric pagination.

3. Soft delete: only on tasks (not on users or projects)

Why selective:

• tasks are deleted and recovered frequently — soft delete justified.
• users are deleted rarely, with compliance (GDPR) — hard delete with backup.
• projects are deleted rarely — hard delete accepted.

Applying soft delete to everything by default is over-engineering.

---

How the zero-downtime migration's success is measured

It's the binary criterion of the module:

# Terminal 1: start the app and wrk in the background
docker-compose up -d
wrk -t 4 -c 50 -d 600s --latency \
    -s post-task.lua \
    http://localhost:8000/tasks > wrk_during_migration.log &

# Terminal 2: run the migration in 3 deploys (expand-contract)
git checkout deploy-1-add-priority-nullable
docker-compose restart app  # zero-downtime restart
sleep 60  # continuous traffic

git checkout deploy-2-write-priority-everywhere
docker-compose restart app
sleep 60

git checkout deploy-3-make-priority-not-null
docker-compose restart app
sleep 60

# Check the results
wait  # wait for wrk to finish
grep -c "Non-2xx or 3xx" wrk_during_migration.log
# Expected: 0

grep -c "Non-2xx" → 0 = success. Any other number = failure.

---

Testing stack

Tests use real Postgres, not mocks:

# conftest.py
import pytest
from testcontainers.postgres import PostgresContainer


@pytest.fixture(scope="session")
def postgres():
    with PostgresContainer("postgres:16") as pg:
        yield pg


@pytest.fixture
async def db(postgres):
    # ... create engine + session pointing at the testcontainer
    pass

testcontainers spins up a real Postgres in Docker for the tests. Slow (~3s setup) but the only way to truly test RLS, triggers, and COPY. Mocks won't do.

---

Final repo structure

taskflow/
├── README.md
├── BENCHMARKS.md
├── MULTITENANCY.md
├── RUNBOOK-MIGRATION.md
├── docker-compose.yml
├── Dockerfile
├── pyproject.toml
├── alembic.ini
├── alembic/
│   ├── env.py
│   └── versions/
│       ├── 001_initial.py
│       ├── 002_add_audit_log.py
│       ├── 003_add_priority_nullable.py     # expand
│       ├── 004_backfill_priority.py
│       └── 005_make_priority_not_null.py    # contract
├── app/
│   ├── __init__.py
│   ├── main.py
│   ├── database.py
│   ├── auth.py
│   ├── deps.py
│   ├── models/
│   │   ├── __init__.py
│   │   ├── tenant.py
│   │   ├── user.py
│   │   ├── project.py
│   │   └── task.py
│   ├── schemas/
│   │   ├── task.py
│   │   └── project.py
│   ├── routers/
│   │   ├── auth.py
│   │   ├── tasks.py
│   │   ├── tasks_bulk.py
│   │   └── projects.py
│   └── services/
│       └── audit.py
├── tests/
│   ├── conftest.py
│   ├── test_auth.py
│   ├── test_rls_isolation.py        # ← critical
│   ├── test_tasks_crud.py
│   ├── test_tasks_pagination.py
│   ├── test_tasks_optimistic_lock.py
│   ├── test_tasks_bulk.py
│   ├── test_audit_log.py
│   └── test_migration_zero_downtime.py
├── benchmarks/
│   ├── bench_pagination.py
│   ├── bench_bulk.py
│   └── bench_migration.py
└── scripts/
    ├── seed.py
    └── run_migration_with_load.sh

---

How much time to invest

Suggested distribution:

• Initial setup (capsule 02): 1-2 hours.
• Multi-tenancy with RLS (03): 1-2 hours.
• CRUD + pagination + soft delete (04): 1-2 hours.
• Audit log + optimistic locking (05): 1-2 hours.
• Bulk + live migration (06): 1-2 hours.
• Complete tests (07): 2-3 hours.
• Documentation + cleanup (08): 1-2 hours.

Total: 8-15 hours spread across several sessions. If you have the bandwidth to do it in a weekend, that works well. If you only have 1-2 hours at a time, spread it over a week.

---

Pitfalls to avoid while taking the module

1. "I'll skip the tests, I'll do them at the end." Integrated tests are how you validate that each pattern works. Without tests, you end up with code that "seems to work" but breaks on edge cases. Tests per capsule, not at the end.

2. "I'll use mocks for the tests, they're faster." RLS isn't mocked. Triggers aren't mocked. COPY isn't mocked. For this module's patterns, real Postgres (testcontainers) is mandatory.

3. "I'll skip the zero-downtime migration, it's just a demo." It's the binary criterion of the module. A project without the migration executed live with wrk is not complete.

4. "I'll write the documentation later." BENCHMARKS.md, MULTITENANCY.md, RUNBOOK-MIGRATION.md are part of the deliverable. Without docs, the project looks half-done in your portfolio.

5. "I'll copy code from the mini-projects directly." Some patterns applied in isolation require adjustments to integrate. E.g.: cursor pagination has to respect tenant_id; soft delete has to interact with the audit log; etc. Re-implementing lets you see the interactions.

---

Getting started in the next capsule

We start with capsule 02: initial setup. Project structure, dependencies, first endpoint with a multi-tenant base. If you go with the scaffold, clone + skip to the CRUD; if you go from scratch, the next 6 capsules guide you through the build.

Before moving on:

• Have Docker running.
• Python 3.12+.
• Access to GitHub to create the public repo.
• 8-15 hours spread out.

---

Resources for the module

1. FastAPI — Async — reference.
2. SQLAlchemy 2.0 — Async — reference.
3. Alembic — Tutorial — migrations.
4. PostgreSQL — Row Security Policies — official RLS.
5. testcontainers-python — real Postgres in tests.
6. GitLab Database Guide — real runbooks and patterns.
7. Stripe API Reference — reference for API design.
8. Supabase — RLS in production — real case with RLS.

---

Module 8 — SQL Patterns for Production APIs Guide