Module 8: Final Project — TaskFlow API

Final documentation + guide wrap-up

You close out TaskFlow and the whole guide. This capsule covers the three documents that finish the deliverable: BENCHMARKS.md with measured numbers, MULTITENANCY.md with architectural justification, RUNBOOK-MIGRATION.md with actionable steps. Then, a formal wrap-up with "what's missing for real production" and connections to the path's upcoming guides.

Without these documents, the repo looks half-done in your portfolio. With them, you show that you think like a senior — the code works, the decisions are justified, and there's a runbook for operations.

`BENCHMARKS.md`

A template with sections you fill in with measured numbers:

# TaskFlow — Performance Benchmarks

Benchmarks measured on a reproducible setup.

## Setup

- **PostgreSQL:** 16
- **PgBouncer:** 1.22 (transaction mode)
- **FastAPI:** 0.110
- **SQLAlchemy:** 2.0.25 (async)
- **asyncpg:** 0.29
- **Hardware:** Apple M2 Pro, 16GB RAM, NVMe SSD
- **Data:** 100k tasks (10k tenants × 10 tasks average)
- **Load tool:** wrk 4.2.0

To reproduce:

```bash
docker-compose up -d
alembic upgrade head
python scripts/seed.py --tasks 100000
python benchmarks/bench_pagination.py
python benchmarks/bench_bulk.py

Cursor pagination vs OFFSET

Approach	Page 1 (p95)	Page 1,000 (p95)	Page 10,000 (p95)	Improvement vs OFFSET
OFFSET	8 ms	145 ms	2,400 ms	baseline
Cursor	9 ms	9 ms	9 ms	270x at deep

Cursor pagination is O(1). OFFSET degrades linearly with the page.

Soft delete: with a partial index vs without

Setup	Query (1M tasks, 30% deleted)	Plan
Without a partial index	145 ms p95	Index Scan + Filter
With a partial index	12 ms p95	Index Scan

12x improvement with WHERE deleted_at IS NULL in the index.

Bulk endpoint: COPY + ON CONFLICT vs alternatives

N tasks	Loop	executemany	bulk_insert	COPY+temp+ONCONFLICT
100	25 ms	12 ms	8 ms	14 ms
1,000	245 ms	75 ms	38 ms	28 ms
10,000	2,450 ms	820 ms	410 ms	145 ms
50,000	timeout	4,800 ms	2,200 ms	680 ms

COPY + temp + ON CONFLICT is best-in-class from 10k rows on.

API endpoints (latency under load)

wrk -t 4 -c 50 -d 60s with 50 concurrent connections:

Endpoint	p50	p95	p99	RPS
GET /tasks (cursor)	12 ms	28 ms	65 ms	4,500
POST /tasks	15 ms	38 ms	89 ms	3,200
PUT /tasks/{id} (with If-Match)	18 ms	45 ms	110 ms	2,800
GET /tasks/{id}/history	10 ms	25 ms	55 ms	5,200
POST /tasks/bulk (1k items)	78 ms	145 ms	235 ms	650

Zero-downtime migration

Migration of priority from NULL → NOT NULL executed live:

Wrk running: 4 threads, 50 connections, 600s duration
Total requests: 720,000
HTTP errors: 0
p95 latency during migration: 28ms (vs baseline 28ms)
p99 latency during migration: 65ms (vs baseline 65ms)

Result: 0 errors across 720k requests during the migration. Latencies kept in line with baseline. No detectable downtime.

Learnings

Cursor pagination is the difference between 9ms and 2400ms. It's not a marginal optimization.
A partial index on deleted_at IS NULL is a 12x improvement for free if you use soft delete.
COPY + temp + ON CONFLICT scales better than the alternatives for >10k rows.
A zero-downtime migration with expand-contract is executable with real traffic when you follow the pattern correctly.

Limitations / considerations

Benchmarks are single-instance. Multi-instance production may differ.
Specific hardware — numbers vary on other setups.
No benchmarks of RLS overhead (measure separately).


---

## `MULTITENANCY.md`

```markdown
# TaskFlow — Multi-tenancy Architecture

## Decision: Shared Schema with Row-Level Security (RLS)

TaskFlow uses **shared schema with RLS** to isolate tenants. All multi-tenant
tables have a `tenant_id` UUID + an RLS policy that filters by
`current_setting('app.tenant_id')`.

## Alternatives considered

### Option A: Shared schema + WHERE filter in code

Each query filters manually: `WHERE tenant_id = X`.

**Pros:**
- Simple, no special features.
- Predictable performance.

**Cons:**
- ❌ **Any bug in a query** → leak between tenants.
- ❌ Code review must catch every query.
- ❌ ORM features that generate queries (eager loading, lazy loading) are a risk.

**Verdict:** rejected. Security risk too high.

### Option B: Shared schema + RLS (the chosen one)

All tables have a `tenant_id`. RLS enabled with policies. The app sets
`app.tenant_id` per request.

**Pros:**
- ✅ **Defense in depth**: if the Python query omits the filter, RLS protects.
- ✅ One DB, one set of migrations, one pool — low operational cost.
- ✅ Cross-tenant analytics easy (with superuser, rare).
- ✅ Works perfectly up to hundreds/thousands of tenants.

**Cons:**
- ❌ Small performance overhead (~5-10% on queries with the RLS check).
- ❌ Noisy tenants can affect others' performance.
- ❌ RLS doesn't isolate resources (CPU, IO).

**When it's the right answer:** B2B SaaS with hundreds to tens of thousands of tenants similar in usage.

### Option C: Schema-per-tenant

Each tenant has its own PostgreSQL schema: `tenant_acme.tasks`, `tenant_globex.tasks`.

**Pros:**
- ✅ Stronger isolation than RLS.
- ✅ Tenant cleanup = drop schema.

**Cons:**
- ❌ N migrations (one per schema).
- ❌ More complex connection pool (search_path per tenant).
- ❌ Doesn't scale to >1k schemas (PostgreSQL wasn't designed for that).
- ❌ Cross-tenant queries are a nightmare.

**When it's the right answer:** tens to hundreds of tenants each with high value (enterprise).

### Option D: Database-per-tenant

Each tenant has a full DB: `taskflow_acme`, `taskflow_globex`.

**Pros:**
- ✅ Total isolation: performance, security, backup.
- ✅ Tenant deletion = drop database.
- ✅ Strict compliance possible (data residency, etc.).

**Cons:**
- ❌ N pools, N backups, N monitoring, N migrations.
- ❌ Enormous operational cost.
- ❌ Cross-tenant queries impossible.

**When it's the right answer:** 100k+ enterprise tenants (Slack, Notion enterprise) or strict compliance requirements (HIPAA, PCI per tenant).

## Why TaskFlow chose B (shared + RLS)

TaskFlow targets a typical B2B SaaS:

- Tenants are small-to-medium companies (10-200 users).
- Similar workload across tenants.
- Moderate compliance requirements (no PCI/HIPAA).
- Economics: we want low operational cost.

For this profile, shared schema + RLS is optimal. It offers adequate security
(RLS as a backstop), operational simplicity, and low cost.

## Implementation

### Schema

All multi-tenant tables have `tenant_id UUID NOT NULL`:

- `tenants` (PK)
- `users` (FK to tenants)
- `projects` (FK to tenants)
- `tasks` (FK to tenants)

The `audit` schema doesn't have RLS enabled (a conscious decision — the audit log
is filtered manually in queries to keep flexibility for cross-tenant audit).

### RLS Policies

```sql
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_users ON users
USING (tenant_id::text = current_setting('app.tenant_id', TRUE))
WITH CHECK (tenant_id::text = current_setting('app.tenant_id', TRUE));

-- Same for projects, tasks

Application layer

The get_db_with_tenant dependency sets SET LOCAL app.tenant_id = X based on the request's JWT. Critical: SET LOCAL (not SET) so it gets cleaned up at the end of the transaction (PgBouncer transaction mode).

Connection pooling

The app connects as app_user (not superuser). PgBouncer in transaction mode.

Isolation tests

Aggressive suite in tests/test_rls_isolation.py:

HTTP endpoint test: tenant A doesn't see tenant B's tasks in GET /tasks.
Direct fetch test: tenant A gets 404 when trying GET /tasks/{b's_id}.
Update test: tenant A gets 404 when trying PUT /tasks/{b's_id}.
Raw SQL test: a "malicious" query with no WHERE tenant_id returns only the correct tenant's rows.
No-context test: without SET app.tenant_id, no row is visible.

All pass with the current implementation. They document the isolation guarantee.

Accepted trade-offs

~5-10% overhead on queries from the RLS check. Acceptable for defense in depth.
A noisy tenant affects others in performance. Mitigable with: per-tenant rate limiting, per-tenant monitoring, an eventual move to option C/D if it becomes critical.
No storage isolation: backups include all tenants. Strict compliance requirement not met.

When to migrate to another option

Triggers to reconsider:

>1000 tenants with uneven usage → consider schema-per-tenant for the top tenants.
Compliance that requires per-tenant data residency → DB-per-tenant.
A noisy tenant systemically affecting others → scale vertically or isolate.


---

## `RUNBOOK-MIGRATION.md`

```markdown
# Runbook: Zero-Downtime Migration

Operational guide for executing a TaskFlow migration in production with real traffic.

## Prerequisites

- [ ] Recent verified backup (`pg_dump` in the last 24h).
- [ ] Monitoring (logs, metrics) accessible and working.
- [ ] Rollback plan written and tested in staging.
- [ ] On-call team notified (at least 1 on-call engineer during the migration).
- [ ] A reasonable time window: low traffic (not Black Friday, not Sunday
      night), 1-2 hours reserved for the exercise.

## Pre-deploy checklist

```bash
# 1. Check the current version
docker-compose exec app alembic current

# 2. Check pending migrations
docker-compose exec app alembic history

# 3. Check connections aren't saturated
docker-compose exec postgres psql -U postgres -c "SELECT count(*) FROM pg_stat_activity"

# 4. Check disk space (pg_repack/migration may need 2x)
docker-compose exec postgres df -h /var/lib/postgresql

# 5. Check replication lag (if applicable)
# pg_stat_replication SELECT pg_wal_lsn_diff(...)

All OK → proceed.

Execute the migration

Step 1: start control traffic (`wrk`)

# Terminal 1
wrk -t 4 -c 50 -d 1800s --latency \
    -s scripts/post-task.lua \
    http://localhost:8000/tasks > wrk_migration_$(date +%Y%m%d_%H%M%S).log &
WRK_PID=$!

Step 2: Migration 1 (expand)

Adds a nullable column with a default:

# Terminal 2
docker-compose exec app alembic upgrade XXX_add_priority_nullable

Expect: ~5 seconds (ALTER TABLE ADD COLUMN nullable is a fast path).

Verify:

The app keeps responding: curl localhost:8000/health → 200.
Wrk keeps running: check that $WRK_PID is alive.
Logs without errors: docker-compose logs --tail=50 app | grep ERROR.

Step 3: Wait & monitor

sleep 60
# Check errors up to this point
grep -c "Non-2xx" wrk_migration_*.log
# Expected: 0

Step 4: Deploy 2 (code that writes the new column)

git checkout deploy-2-write-priority
docker-compose restart app

Expect: ~10 seconds (restart) + wait for the healthcheck to pass.

Verify:

curl localhost:8000/health → 200.
The app writes priority on new tasks: insert a test task and verify.
Wrk keeps running with no error spike.

Step 5: Wait & monitor

sleep 60
grep -c "Non-2xx" wrk_migration_*.log

Step 6: (Optional) Backfill

If existing rows don't have values, backfill in batches:

docker-compose exec app python scripts/backfill_priority.py --batch-size 5000

In this case, DEFAULT 0 already covered it, so the backfill is a no-op.

Step 7: Migration 3 (contract — SET NOT NULL)

docker-compose exec app alembic upgrade head

Expect: seconds (in PG 12+ with an existing default, fast path).

Verify:

The app keeps working.
Schema updated: \d tasks → priority NOT NULL.

Step 8: Wait & complete

sleep 60
grep -c "Non-2xx" wrk_migration_*.log
# Expected: 0

Step 9: Stop wrk and report

# Wait for wrk to finish (or kill it)
kill $WRK_PID 2>/dev/null
wait $WRK_PID 2>/dev/null

# Final report
echo "Total errors:"
grep -c "Non-2xx" wrk_migration_*.log

cat wrk_migration_*.log | tail -30

Success criterion: 0 HTTP errors, latencies in the baseline range.

If something fails

Migration 1 (expand) hangs

Likely cause: ALTER TABLE blocked by another transaction.

# See locks
docker-compose exec postgres psql -U postgres -c "
    SELECT pid, query, state, wait_event_type
    FROM pg_stat_activity
    WHERE pid <> pg_backend_pid()
      AND state != 'idle'
    ORDER BY query_start
"

Identify the blocker. Decide:

If it's a user transaction blocking, wait.
If it's a stuck job, terminate it: SELECT pg_terminate_backend(<pid>).

Retry the migration.

Deploy 2 (app restart) throws errors

Likely cause: the app crashes from a code bug.

# See logs
docker-compose logs --tail=100 app

# If there's an error, rollback
git checkout deploy-1
docker-compose restart app

# Wrk should go back to 0 errors

After the rollback, debug the bug locally before retrying.

Migration 3 (SET NOT NULL) fails

Likely cause: there are rows with priority NULL (rare if the default covered it).

docker-compose exec postgres psql -U postgres -d taskflow -c "
    SELECT count(*) FROM tasks WHERE priority IS NULL
"

If > 0, backfill first:

docker-compose exec postgres psql -U postgres -d taskflow -c "
    UPDATE tasks SET priority = 0 WHERE priority IS NULL
"

# Retry
docker-compose exec app alembic upgrade head

Wrk reports errors at some point

Common causes:

Momentary app restart (5-15s): acceptable if the volume is low.
PgBouncer pool exhausted: increase DEFAULT_POOL_SIZE.
Migration locks longer than expected: check whether it's really a fast path.

If > 0.1% errors during the migration, roll back to the previous state:

# For each migration
docker-compose exec app alembic downgrade -1

And re-evaluate the plan.

Post-deploy

Immediate verification

curl localhost:8000/health → 200
Error rate metrics in monitoring < 0.1%
Latencies within the baseline range
No new errors in the logs
Schema verified: \d+ tasks shows priority NOT NULL

Verification 24h later

Metrics stable, no regressions
Audit log populated correctly
Backups run without problems with the new schema

Post-migration backup

docker-compose exec postgres pg_dump -U postgres -d taskflow > backup_post_migration.sql

Keep this backup as a baseline of the post-migration state.

Communication

Internal announcement beforehand: "We're going to deploy a zero-downtime migration on [date]. Users shouldn't notice any changes. On-call: [name]. If you see errors, contact: [channel]."
Public status page: do NOT change (because it's zero-downtime).
Post-mortem if something fails: document within 24h.


---

## Module and guide wrap-up

You've reached the end. What you have:

### A public GitHub repo with:

- A working TaskFlow codebase.
- Alembic migrations including a zero-downtime migration.
- Tests with real Postgres (testcontainers) — ~13 tests passing.
- `BENCHMARKS.md` with measured numbers.
- `MULTITENANCY.md` with architectural justification.
- `RUNBOOK-MIGRATION.md` with actionable steps.
- A reproducible `README.md`.

### Patterns applied (the guide's 7):

1. ✅ Cursor pagination (module 1).
2. ✅ Soft delete with a partial index (module 2).
3. ✅ Audit logs via PostgreSQL triggers (module 3).
4. ✅ Multi-tenancy with RLS (module 4).
5. ✅ Zero-downtime migration executed live (module 5).
6. ✅ Optimistic locking with `If-Match` (module 6).
7. ✅ Bulk operations with COPY + ON CONFLICT (module 7).

### Demonstrable capabilities

- Designing production-ready multi-tenant SaaS APIs.
- Implementing advanced SQL patterns correctly.
- Executing zero-downtime migrations with confidence.
- Documenting architectural decisions.
- Writing actionable runbooks.
- Producing portfolio-worthy deliverables.

---

## What's missing for real production

TaskFlow is **portfolio-worthy**, not production-ready out-of-the-box. For real production, what's missing:

- **Rate limiting** per-tenant to prevent noisy tenants. (Topic for another guide.)
- **Idempotency keys** on POSTs with side effects (charges, emails). Mentioned in module 6 capsule 02.
- **Full observability**: Prometheus metrics, distributed tracing, log aggregation. Next guide.
- **Real auth**: OAuth2/OIDC with an identity provider, not a mock JWT.
- **Automated backups** with periodic restore verification.
- **Disaster recovery plan**: failover, replication, defined RTO/RPO.
- **Performance monitoring**: alerts for slow queries, lock contention, replication lag.
- **Compliance**: GDPR (right to be forgotten), SOC2, verifiable audit trails.
- **Cloud deployment**: AWS/GCP configuration, IaC with Terraform, CI/CD pipelines.

Pedagogical honesty: this project demonstrates mastery of SQL patterns in production, not the operation of full distributed systems.

---

## The path's upcoming guides

Your natural next step depends on what you want to go deeper into:

### Guide #14: Advanced PostgreSQL for Backend

A deep dive into advanced features you'd apply to TaskFlow:

- **JSONB with GIN/GiST indexes**: TaskFlow has `audit.task_log.old_data/new_data` JSONB that could be indexed for queries.
- **Full-Text Search**: implement `GET /tasks?search=X` with `tsvector`/`tsquery`.
- **Declarative partitioning**: `audit.task_log` grows endlessly — partition it by date.
- **Materialized views**: pre-computed stats (counts per tenant, tasks per day, etc.).
- **Advisory locks**: distributed locks for coordination between instances.
- **Recursive CTEs**: hierarchical queries (sub-tasks, dependencies).

### Guide #15: Observability for Backend Systems

Real production needs visibility:

- **Custom Prometheus metrics** for TaskFlow: throughput, latency, error rate.
- **Distributed tracing** with OpenTelemetry: visualize request paths.
- **Slow query monitoring**: tracking queries that cross thresholds.
- **Lock contention detection**: alerts when locks block operations.
- **RLS policy performance**: measure the overhead.
- **Audit log analytics**: useful queries over `audit.task_log`.

### Guide #16: AI Engineering Path (cross-link)

If TaskFlow had AI features (semantic task search, comment summarization), it would connect with:
- Embeddings + vector search.
- LLM-powered features (natural search, auto-tagging).
- Cost optimization of LLM calls.

---

## Your next action

1. **Verify the repo is public** on GitHub.
2. **Link it in your resume/portfolio** with a clear description: "Multi-tenant SaaS API demonstrating production patterns: cursor pagination, soft delete, audit logs, RLS, zero-downtime migrations, optimistic locking, bulk operations."
3. **Practice explaining it out loud** — the next senior interview is coming.
4. **Apply the patterns** in real projects: if you work on a codebase with OFFSET pagination, slow COUNT(*), no RLS — propose the refactors based on your experience building TaskFlow.
5. **Continue the path:** choose guide #14 or #15 based on your next interest.

---

## Final resources

1. [GitLab Database Guide](https://docs.gitlab.com/ee/development/database/) — reference for runbooks.
2. [Stripe API Versioning](https://stripe.com/blog/api-versioning) — enterprise patterns.
3. [Supabase Architecture](https://supabase.com/docs/guides/database) — real case with RLS at scale.
4. [PostgreSQL Documentation](https://www.postgresql.org/docs/) — always the official reference.
5. [Brandur's blog](https://brandur.org/) — real patterns and deep writeups.
6. [PostgreSQL Weekly](https://postgresweekly.com/) — newsletter to stay up to date.
7. [Awesome Postgres](https://github.com/dhamaniasad/awesome-postgres) — curated resources.

---

*Capsule 08 of 08 — Module 8 — SQL Patterns for Production APIs Guide*

*End of the guide. Your next senior interview will find you ready.*

← PreviousComplete tests: isolation, integration, benchmarks

Final documentation + guide wrap-up

BENCHMARKS.md

Cursor pagination vs OFFSET

Soft delete: with a partial index vs without

Bulk endpoint: COPY + ON CONFLICT vs alternatives

API endpoints (latency under load)

Zero-downtime migration

Learnings

Limitations / considerations

Application layer

Connection pooling

Isolation tests

Accepted trade-offs

When to migrate to another option

Execute the migration

Step 1: start control traffic (wrk)

Step 2: Migration 1 (expand)

Step 3: Wait & monitor

Step 4: Deploy 2 (code that writes the new column)

Step 5: Wait & monitor

Step 6: (Optional) Backfill

Step 7: Migration 3 (contract — SET NOT NULL)

Step 8: Wait & complete

Step 9: Stop wrk and report

If something fails

Migration 1 (expand) hangs

Deploy 2 (app restart) throws errors

Migration 3 (SET NOT NULL) fails

Wrk reports errors at some point

Post-deploy

Immediate verification

Verification 24h later

Post-migration backup

Communication

`BENCHMARKS.md`

Step 1: start control traffic (`wrk`)