Module 1 deliverable: Cursor Pagination in TaskFlow

What are you going to build and why?

You made it to the end of the module. You have cursor pagination conceptually, an implementation with SQLAlchemy 2.0 async + FastAPI, composite cursors with tuple comparison, HMAC for signing, bidirectional navigation, filters, and a dynamic sort. It's time to prove it all works together and to measure the real speedup against OFFSET.

You're going to build a GET /tasks endpoint over a table of 5 million tasks that:

1. Supports opaque cursor pagination with HMAC.
2. Supports bidirectional navigation (next + previous).
3. Accepts filters (status, priority, created_after).
4. Accepts a dynamic sort (created_at, priority, title) with a safe allowlist.
5. Holds constant latency between page 1 and page 50,000.

And you're going to measure the speedup against OFFSET with a reproducible benchmark script. The target number: ~17x faster on deep pages (figure from Design Gurus) — you're going to confirm (or refute) that benchmark with your hardware.

The project produces a repo you can push to GitHub that demonstrates correct cursor pagination. It's portfolio-worthy. It's what you'll cite when an interviewer asks "have you implemented cursor pagination before?".

---

Project objective

By completing this project:

• You'll have a working FastAPI endpoint with cursor pagination over 5M tasks
• You'll have measured p50/p95/p99 of cursor pagination on page 1 vs page 50,000
• You'll have measured OFFSET on the same pages for comparison
• You'll have a BENCHMARKS.md with reproducible numbers
• You'll have validated the ~17x speedup from the Design Gurus paper

---

How it fits with what you learned

This project integrates every capsule of the module:

Capsule	Concept	Where it's used in the project
02	OFFSET is O(n)	The benchmark against cursor — to visualize the problem
03	Opaque cursor with base64	Encoding the cursor you return to the client
04	SQLAlchemy 2.0 async + FastAPI	The endpoint's stack
05	Tuple comparison + composite index	The internal query and the DB schema
06	HMAC + bidirectional	previous_cursor + signing the cursor
07	Filters + dynamic sort	The endpoint's query params

Mental model: this project is the module's "end-of-tour demo." Every feature you implemented separately in a capsule now coexists in a single working endpoint.

---

Technical specifications

Stack

• Language: Python 3.11+
• Framework: FastAPI 0.110+
• ORM: SQLAlchemy 2.0+ async
• DB driver: asyncpg 0.29+
• DB: PostgreSQL 16+
• Validation: Pydantic v2.6+
• Tests: pytest + pytest-asyncio + httpx
• Benchmarking: pgbench for raw SQL + wrk for HTTP

Initial setup

mkdir taskflow-pagination
cd taskflow-pagination
python -m venv venv
source venv/bin/activate

pip install \
  "fastapi[standard]>=0.110" \
  "sqlalchemy[asyncio]>=2.0" \
  "asyncpg>=0.29" \
  "pydantic>=2.6" \
  "uvicorn[standard]>=0.27" \
  "httpx>=0.27" \
  "pytest>=8.0" \
  "pytest-asyncio>=0.23"

# Create the DB
createdb taskflow_demo

# If you prefer Docker:
docker run -d --name pg-taskflow \
  -e POSTGRES_DB=taskflow_demo \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  postgres:16

Expected repo structure

taskflow-pagination/
├── README.md
├── BENCHMARKS.md                  ← the main deliverable
├── pyproject.toml
├── app/
│   ├── __init__.py
│   ├── db.py
│   ├── models.py
│   ├── schemas.py
│   ├── pagination.py             ← opaque cursor with HMAC
│   ├── repositories/
│   │   ├── __init__.py
│   │   └── tasks.py              ← query with the cursor
│   └── main.py
├── tests/
│   ├── conftest.py
│   ├── test_cursor_basic.py
│   ├── test_cursor_bidi.py
│   ├── test_hmac.py
│   └── test_filters_and_sort.py
├── seed.py                        ← inserts 5M rows
├── bench/
│   ├── pgbench-cursor.sql
│   ├── pgbench-offset.sql
│   ├── wrk-cursor.lua
│   └── benchmark_runner.py        ← compares cursor vs OFFSET
└── .env.example

---

Required functionality

1. GET /tasks endpoint with cursor pagination

Spec:

GET /tasks
Query params:
  - cursor:         string (optional)                signed opaque cursor
  - limit:          int (1-200, default 50)          items per page
  - status:         enum (open, in_progress, closed) filter
  - priority:       enum (low, medium, high)         filter
  - created_after:  ISO datetime (optional)          filter
  - sort:           enum (created_at, priority, title) sort
  - order:          enum (asc, desc, default desc)   direction

Response:

{
  "items": [
    {
      "id": 1234567,
      "title": "task_1234567",
      "status": "open",
      "priority": "high",
      "created_at": "2026-04-15T10:23:45.123456+00:00"
    }
  ],
  "next_cursor": "eyJ0Ijoi....SIGNATURE",
  "previous_cursor": "eyJ0Ijoi....SIGNATURE",
  "has_more": true
}

Expected behavior:

• No cursor: returns the first limit items according to the sort.
• With a cursor: decodes, validates the HMAC, validates that sort/order match, returns the next items.
• Changing the sort between pages with an old cursor: HTTP 400 with a clear message.
• Tampered cursor (invalid signature): HTTP 400.
• Cursor with an incompatible version: HTTP 400.
• limit=0 or limit>200: HTTP 422 (Pydantic validation).

2. tasks table schema

CREATE TYPE task_status AS ENUM ('open', 'in_progress', 'closed');
CREATE TYPE task_priority AS ENUM ('low', 'medium', 'high');

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  status task_status NOT NULL DEFAULT 'open',
  priority task_priority NOT NULL DEFAULT 'medium',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Indexes to support each sort + tiebreaker
CREATE INDEX idx_tasks_created_id_desc ON tasks (created_at DESC, id DESC);
CREATE INDEX idx_tasks_priority_id_desc ON tasks (priority DESC, id DESC);
CREATE INDEX idx_tasks_title_id_asc ON tasks (title ASC, id ASC);

-- Indexes for the most common filters (status + the default sort)
CREATE INDEX idx_tasks_status_created_id ON tasks (status, created_at DESC, id DESC);
CREATE INDEX idx_tasks_priority_created_id ON tasks (priority, created_at DESC, id DESC);

3. Seed of 5M rows

seed.py inserts 5M tasks with:

• created_at randomly distributed across the last 2 years
• status distributed: 60% open, 25% in_progress, 15% closed
• priority distributed: 50% medium, 30% low, 20% high
• title in the format task_<id>

Estimated time: 5-15 minutes depending on hardware.

4. Benchmarks measured in BENCHMARKS.md

A structured document with:

• Reproducible context (hardware, PostgreSQL version, table, indexes)
• A cursor vs OFFSET comparison on page 1 vs page 1,000 vs page 50,000
• p50/p95/p99 latency
• The speedup factor on a deep page

---

Validation and error handling

Automatic validations (FastAPI + Pydantic)

• limit must be between 1 and 200 → HTTP 422
• status must be one of the enum values → HTTP 422
• priority must be one of the enum values → HTTP 422
• created_after must be a valid ISO 8601 datetime → HTTP 422
• sort must be one of the enum values → HTTP 422
• order must be asc or desc → HTTP 422

Explicit cursor errors

• Cursor without a signature (no "."): HTTP 400 "Cursor without a signature"
• Cursor with an invalid signature (HMAC doesn't match): HTTP 400 "Invalid cursor signature"
• Cursor with an unknown version: HTTP 400 "Cursor version X not supported"
• Cursor whose sort doesn't match the request: HTTP 400 "Cursor belongs to sort='X', the request asks for sort='Y'"
• Cursor whose order doesn't match: HTTP 400 "Cursor belongs to order='X', the request asks for order='Y'"

---

Minimal implementation example

This is NOT the complete solution — it's the skeleton you extend with all the features.

app/main.py (skeleton)

"""FastAPI app: TaskFlow pagination demo."""
from datetime import datetime
from fastapi import Depends, FastAPI, HTTPException, Query, status as http_status
from sqlalchemy.ext.asyncio import AsyncSession

from app.db import get_session
from app.models import TaskStatus, TaskPriority
from app.pagination import CursorError
from app.repositories.tasks import list_tasks_paginated
from app.schemas import Page, TaskOut, TaskSortField, SortOrder

app = FastAPI(title="TaskFlow Pagination Demo")


@app.get("/health")
async def health():
    return {"status": "ok"}


@app.get("/tasks", response_model=Page[TaskOut])
async def get_tasks(
    cursor: str | None = Query(default=None),
    limit: int = Query(default=50, ge=1, le=200),
    status: TaskStatus | None = Query(default=None),
    priority: TaskPriority | None = Query(default=None),
    created_after: datetime | None = Query(default=None),
    sort: TaskSortField = Query(default=TaskSortField.created_at),
    order: SortOrder = Query(default=SortOrder.desc),
    session: AsyncSession = Depends(get_session),
) -> Page[TaskOut]:
    try:
        return await list_tasks_paginated(
            session=session,
            cursor=cursor,
            limit=limit,
            status=status,
            priority=priority,
            created_after=created_after,
            sort=sort,
            order=order,
        )
    except CursorError as e:
        raise HTTPException(
            status_code=http_status.HTTP_400_BAD_REQUEST,
            detail=f"Invalid cursor: {e}",
        )

This skeleton:

• ✅ Is runnable (with all dependencies installed and the DB running)
• ✅ Defines the endpoint's full signature
• ✅ Handles CursorError correctly
• ❌ Does NOT include the complete list_tasks_paginated — you build that from the previous capsules

seed.py

"""Seed 5M distributed tasks."""
import asyncio
import random
from datetime import datetime, timedelta, timezone

from app.db import AsyncSessionLocal, engine
from app.models import Base, Task, TaskStatus, TaskPriority


STATUS_DISTRIBUTION = [
    (TaskStatus.open, 60),
    (TaskStatus.in_progress, 25),
    (TaskStatus.closed, 15),
]

PRIORITY_DISTRIBUTION = [
    (TaskPriority.medium, 50),
    (TaskPriority.low, 30),
    (TaskPriority.high, 20),
]


def _weighted_choice(choices):
    total = sum(weight for _, weight in choices)
    r = random.uniform(0, total)
    upto = 0
    for value, weight in choices:
        if upto + weight >= r:
            return value
        upto += weight
    return choices[-1][0]


async def init_db():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)


async def seed(n: int):
    now = datetime.now(timezone.utc)
    two_years = 2 * 365 * 24 * 3600
    batch_size = 10_000

    async with AsyncSessionLocal() as session:
        for i in range(0, n, batch_size):
            batch = []
            for j in range(min(batch_size, n - i)):
                idx = i + j
                offset_seconds = random.randint(0, two_years)
                batch.append(
                    Task(
                        title=f"task_{idx}",
                        status=_weighted_choice(STATUS_DISTRIBUTION),
                        priority=_weighted_choice(PRIORITY_DISTRIBUTION),
                        created_at=now - timedelta(seconds=offset_seconds),
                    )
                )
            session.add_all(batch)
            await session.commit()
            print(f"  inserted {i + len(batch):,} / {n:,}")


async def main():
    await init_db()
    n = 5_000_000  # 5M
    print(f"Seeding {n:,} tasks (takes 5-15 min)...")
    await seed(n)
    print("Done.")


if __name__ == "__main__":
    asyncio.run(main())

python seed.py
# ... ~10 min depending on hardware

---

Benchmarks

Benchmark setup

For the numbers to be comparable, run the benchmarks:

• On the same machine with no other load
• After running VACUUM ANALYZE tasks
• With a warm cache (run the benchmark twice, discard the first)
• Multiple runs, reporting the median

Benchmark 1: cursor vs OFFSET with pgbench

bench/pgbench-cursor.sql:

\set offset_id random(1, 5000000)

-- Simulated cursor pagination: uses keyset directly
-- (the opaque cursor decodes to these values in the endpoint)
SELECT id, title, created_at FROM tasks
WHERE (created_at, id) < (
  (SELECT created_at FROM tasks WHERE id = :offset_id),
  :offset_id
)
ORDER BY created_at DESC, id DESC
LIMIT 50;

bench/pgbench-offset.sql:

\set page_offset random(0, 4999000)

SELECT id, title, created_at FROM tasks
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET :page_offset;

Run it:

# Cursor (with keyset)
pgbench -n -f bench/pgbench-cursor.sql -c 10 -j 2 -T 60 --log taskflow_demo

python tools/percentiles.py pgbench_log.* > /tmp/cursor_percentiles.txt

rm pgbench_log.*

# OFFSET
pgbench -n -f bench/pgbench-offset.sql -c 10 -j 2 -T 60 --log taskflow_demo

python tools/percentiles.py pgbench_log.* > /tmp/offset_percentiles.txt

tools/percentiles.py (from guide #12):

import glob, sys
import numpy as np

latencies = []
for filename in sys.argv[1:]:
    with open(filename) as f:
        for line in f:
            parts = line.split()
            latencies.append(float(parts[2]) / 1000.0)

arr = np.array(latencies)
print(f"n: {len(arr)}")
print(f"p50:  {np.percentile(arr, 50):.2f} ms")
print(f"p95:  {np.percentile(arr, 95):.2f} ms")
print(f"p99:  {np.percentile(arr, 99):.2f} ms")
print(f"max:  {np.max(arr):.2f} ms")

Benchmark 2: end-to-end HTTP with wrk

# Cursor pagination on a deep page
wrk -t4 -c50 -d30s "http://localhost:8000/tasks?cursor=eyJ...&limit=50"

# OFFSET on a deep page (you need an auxiliary /tasks-offset endpoint)
wrk -t4 -c50 -d30s "http://localhost:8000/tasks-offset?page=10000&limit=50"

Benchmark 3: measure page 1 vs a deep page with EXPLAIN ANALYZE

-- Page 1 with a cursor (effectively: no cursor, first page)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM tasks
ORDER BY created_at DESC, id DESC LIMIT 50;

-- Page 50,000 with a cursor (~2.5M rows into the dataset)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM tasks
WHERE (created_at, id) < (
  (SELECT created_at FROM tasks ORDER BY created_at DESC OFFSET 2500000 LIMIT 1),
  -- (placeholder id, in practice it comes from the cursor)
  1
)
ORDER BY created_at DESC, id DESC LIMIT 50;

-- Page 50,000 with OFFSET
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM tasks
ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 2500000;

---

BENCHMARKS.md template

# BENCHMARKS — TaskFlow Pagination

## Context

- **Hardware:** [your hardware: e.g. MacBook Air M1, 16GB RAM, SSD]
- **PostgreSQL:** 16.2 (local install via brew / docker:postgres:16)
- **Python:** 3.11.8
- **SQLAlchemy:** 2.0.27, asyncpg 0.29.0, FastAPI 0.110.0
- **DB size:** 5,000,000 tasks (~600MB)
- **Indexes:**
  - idx_tasks_created_id_desc (BTREE, 245MB)
  - idx_tasks_priority_id_desc (BTREE, 198MB)
  - idx_tasks_title_id_asc (BTREE, 280MB)
  - + 2 indexes with filters (status_created, priority_created)
- **Other:** `VACUUM ANALYZE` run before measuring. Warm cache.

## Methodology

- 60s per benchmark with `pgbench`, c=10, j=2
- 30s per benchmark with `wrk`, t=4, c=50
- Multiple runs (minimum 3); I report the median
- Random variables in the pgbench scripts to avoid artificial cache hits

## Results

### Raw `pgbench` (pure DB, no app)

| Approach | Page range | p50 | p95 | p99 | TPS |
|----------|-----------|-----|-----|-----|-----|
| Cursor (keyset)  | random (1 to 5M) | 0.6 ms | 1.2 ms | 2.1 ms | 12,800 |
| OFFSET           | random (0 to 4.99M) | 95 ms | 245 ms | 410 ms | 87 |

**p99 speedup:** 410 / 2.1 ≈ **195x**
**p50 speedup:** 95 / 0.6 ≈ **158x**

> Much higher than the 17x reported by Design Gurus. Likely reason: on a 5M table with a uniformly distributed random page, a large OFFSET happens in most runs. Design Gurus reported a more conservative particular case.

### `wrk` end-to-end HTTP (includes FastAPI + JSON serialization + asyncpg)

| Approach        | Deep page (sim. page 50k) | p50 | p95 | p99 | RPS |
|-----------------|-------------------------------|-----|-----|-----|-----|
| Opaque cursor   | yes                           | 8 ms | 14 ms | 22 ms | 4,800 |
| OFFSET          | yes                           | 280 ms | 520 ms | 880 ms | 175  |

**HTTP p99 speedup:** 880 / 22 ≈ **40x**

### Point-in-time `EXPLAIN ANALYZE`

| Query | Plan | Buffers | Execution Time |
|-------|------|---------|----------------|
| Cursor page 1     | Index Scan idx_tasks_created_id_desc | 4    | 0.3 ms |
| Cursor page 50k   | Index Scan + Index Cond ROW(...)     | 4    | 0.4 ms |
| OFFSET page 1     | Index Scan LIMIT 50 OFFSET 0         | 4    | 0.3 ms |
| OFFSET page 50k   | Index Scan LIMIT 50 OFFSET 2500000   | 92,540 | 380 ms |

**Cursor:** constant latency (~0.4ms) regardless of depth.
**OFFSET:** latency scales linearly with depth.

## Conclusions

1. **Cursor pagination meets the constant-latency objective.** Page 1 and page 50,000 are indistinguishable in latency.
2. **OFFSET scales linearly with depth.** At page 50k it's ~1,000x slower than page 1.
3. **The HTTP speedup (40x) is lower than the raw SQL one (195x).** Reason: the HTTP/serialization cost is constant (~5-15ms), which makes the "absolute" difference proportionally smaller.
4. **The [17x reported by Design Gurus](https://www.designgurus.io/answers/detail/what-is-the-difference-between-cursor-and-offset-pagination)** is a conservative benchmark. In setups with larger tables and deeper pages, the speedup is much greater (40-200x).
5. **The composite index is critical.** Without `idx_tasks_created_id_desc`, cursor pagination falls back to Sort + Seq Scan and loses the entire advantage.

---

Evaluation rubric

Core functionality (40 pts)

• (5 pts) GET /tasks endpoint works without a cursor (first page)
• (5 pts) The endpoint works with a cursor (next page)
• (5 pts) Opaque cursor signed with HMAC
• (5 pts) Bidirectional: previous_cursor works and returns to the same page
• (5 pts) Filters (status, priority, created_after) applied correctly
• (5 pts) Dynamic sort (created_at, priority, title) without SQL injection
• (5 pts) Validation: a cursor with a different sort than the request → HTTP 400
• (5 pts) Validation: a tampered cursor / invalid signature → HTTP 400

Schema and performance (25 pts)

• (5 pts) tasks table with the correct types (BIGSERIAL, TIMESTAMPTZ, ENUM)
• (10 pts) Composite indexes matching every sort
• (5 pts) EXPLAIN ANALYZE shows Index Cond: ROW(...) with a cursor
• (5 pts) Page 50,000 with a cursor: <50ms p99

Tests (15 pts)

• (3 pts) Test: empty page
• (3 pts) Test: navigate every page without duplicates
• (3 pts) Test: tampered cursor rejected
• (3 pts) Test: stability under recent insertions
• (3 pts) Test: filters combined with a cursor

Benchmarks (15 pts)

• (5 pts) BENCHMARKS.md with reproducible context (hardware, PG version, indexes)
• (5 pts) Cursor vs OFFSET comparison on page 1 vs a deep page
• (5 pts) Speedup factor calculated correctly (target: ≥17x p99)

Documentation and portfolio (5 pts)

• (3 pts) README.md with setup, run, and test instructions
• (2 pts) .env.example with CURSOR_SECRET_KEY

Extra credit (optional, up to +15 pts)

• (+5 pts) Rate limiting of aggressive pagination loops (capsule 04, ex. 3)
• (+5 pts) HMAC key rotation (two active keys, capsule 06, ex. 2)
• (+5 pts) Multi-column sort with mixed directions (capsule 05, ex. 4)

Total: 100 points Pass: ≥70 / 100

---

Common mistakes in this project

Mistake 1: the seed hangs or takes too long

Symptom: seed.py runs for 30+ minutes and doesn't finish, or eats all the RAM.

Why it happens:

• No batches: inserting 5M in a single transaction loads everything into memory.
• No periodic commit: the long transaction consumes WAL and memory.
• The wrong async driver.

How to fix it: batches of 10k with a commit per batch. Use session.add_all(batch) + session.commit() per batch. If it's still slow, consider COPY (covered in module 7).

Mistake 2: the composite index isn't used

Symptom: EXPLAIN ANALYZE shows Sort + Index Scan instead of a pure Index Scan.

Why it happens: the composite index's order doesn't match the exact ORDER BY. E.g. the index is (created_at, id) (default ASC) but the query is ORDER BY created_at DESC, id DESC.

How to fix it: create the index with exactly the sort's order:

-- ❌ Less useful
CREATE INDEX idx ON tasks (created_at, id);

-- ✅ For ORDER BY created_at DESC, id DESC
CREATE INDEX idx ON tasks (created_at DESC, id DESC);

PostgreSQL can walk an index in either direction, but a composite with (ASC, ASC) isn't exactly the same as (DESC, DESC) when there's a tiebreaker. For maximum efficiency, align the index with the sort.

Mistake 3: tests fail on a re-run

Symptom: the first pytest passes, the second fails with "duplicate key" or "table already exists".

Why it happens: the fixtures aren't cleaning up between tests.

How to fix it: setup_db with drop_all + create_all per test (it's already in the module 4 conftest.py):

@pytest_asyncio.fixture(scope="function")
async def setup_db():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)
    yield
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)

Mistake 4: HMAC with the dev key in the benchmarks

Symptom: you run benchmarks with the hardcoded development key and the numbers aren't representative.

Why it happens: SECRET_KEY with a placeholder value. The HMAC computation is still the same, but you're conflating environments.

How to fix it: generate a real key for the benchmark:

export CURSOR_SECRET_KEY="$(python -c 'import secrets; print(secrets.token_urlsafe(32))')"
echo "CURSOR_SECRET_KEY=$CURSOR_SECRET_KEY" > .env

Mistake 5: wrk measures more HTTP latency than the actual query

Symptom: pgbench reports the cursor at 0.5ms p99, but wrk reports 30ms p99.

Why it happens: the difference is the overhead of FastAPI + Pydantic serialization + asyncpg + parsing the cursor (including HMAC).

How to tell: you measured correctly. The HTTP overhead "is the app," not a bug.

How to handle it: document the difference in BENCHMARKS.md. It's useful information — it shows that the bottleneck in production may not be just the DB.

Mistake 6: a cold-cache benchmark is misleading

Symptom: the first wrk reports p99 = 800ms; the second wrk reports p99 = 25ms.

Why it happens: the first one paid the cost of loading index and data pages into shared_buffers. The second found everything cached.

How to fix it: run it twice, discard the first (warmup). Report the second (warm cache, representative of production where the DB has been running for a while).

Deeper coverage of benchmarking methodology (warmup, multiple runs, percentiles vs mean) is in guide #12 module 1. This capsule assumes you've already internalized the mindset of measuring correctly.

---

What to do if you get stuck

If the setup doesn't work:

• Verify PostgreSQL is running: psql -d taskflow_demo -c "SELECT 1"
• Verify the Python version: python --version (needs 3.11+)
• Verify the dependencies: pip list | grep -E 'fastapi|sqlalchemy|asyncpg'

If the cursor doesn't decode:

• Verify CURSOR_SECRET_KEY is set and is the same between the encode and the decode
• Verify the cursor doesn't have badly URL-encoded characters (e.g. %3D instead of =)

If the query is slow:

• EXPLAIN ANALYZE the query
• Verify the indexes: \d tasks in psql
• Verify VACUUM ANALYZE ran: SELECT last_vacuum FROM pg_stat_user_tables WHERE relname = 'tasks';

If the tests fail:

• Isolate: run one test at a time with pytest tests/test_X.py::test_Y -v
• SQL logs: engine = create_async_engine(URL, echo=True)

If the benchmarks don't show a 17x speedup:

• Verify the page depth (it must be >>1000)
• Verify the table's size (5M is the minimum)
• Verify you aren't measuring only the HTTP cost — separate pgbench (pure DB) from wrk (HTTP)

---

Resources for the project

1. Markus Winand — "We need tool support for keyset pagination" — the theoretical reference for the expected speedup.
2. Design Gurus — "Cursor vs OFFSET pagination" — the source of the "17x" benchmark we validate.
3. Stripe API Reference — Pagination — a model of a signed opaque cursor in a public API.
4. PostgreSQL Documentation — pgbench — the official pgbench reference (you used it in guide #12 module 1).
5. SQLAlchemy 2.0 — Async Quickstart — the reference for async sessions, the engine, and dependency injection.
6. FastAPI — Testing — testing patterns with httpx.AsyncClient.
7. PostgreSQL — EXPLAIN ANALYZE reference — to understand the plans you measure.
8. Brandur Leach — "Postgres lock conflicts" — bonus: context on how VACUUM and ANALYZE interact with benchmark queries.

---

What comes next

What you built here is worth keeping as a reference and as a piece of the module 8 final project (the complete TaskFlow API). Specifically:

• The HMAC-signed opaque cursor is reused directly in TaskFlow's GET /tasks.
• The (created_at DESC, id DESC) composite index is the same one TaskFlow will use.
• The test pattern (conftest.py with setup_db, httpx.AsyncClient) is reused for TaskFlow's other endpoints.

But before TaskFlow, more patterns come in modules 2-7:

• Module 2 — Correct Soft Deletes. Your current cursor pagination assumes every row is "live." In the real world, there are tasks with a deleted_at that you do NOT want to show. You're going to learn to combine cursor pagination with WHERE deleted_at IS NULL using partial indexes — without making the queries slow.
• Module 3 — Audit Logs. When you want to know "who changed what on this task," you need traceability. You're going to implement audit logs with PostgreSQL triggers.
• Module 4 — Multi-Tenancy with RLS. Your current cursor pagination doesn't isolate tenants. In TaskFlow you'll have Acme, Globex, and Initech sharing the same table — RLS guarantees that one tenant never sees another's data.
• Module 5 — Zero-Downtime Migrations. When you add priority or change the schema, your API can't go down. You're going to learn expand-contract with Alembic.
• Module 6 — Optimistic Locking. Two clients editing the same task: one wins, the other gets an HTTP 409 with a useful message.
• Module 7 — Bulk Operations. Importing 100k tasks with COPY in <1s.

And Module 8 — the Final TaskFlow API consolidates it all: cursor pagination + soft deletes + audit logs + RLS + optimistic locking + a zero-downtime migration + bulk ops, executed in a single codebase, with measured benchmarks.

What you finished today is the first piece. The most visible part of the SaaS that the customer touches first ("how's the task list doing?"). Done well from day 1, it saves you pain forever.

---

Module 1 — SQL Patterns for Production APIs Guide

Next module: Correct Soft Deletes — how to combine WHERE deleted_at IS NULL with partial indexes so your cursor pagination stays fast when there are records marked as deleted.