To-do app backend

The idea

A to-do backend is a small CRUD service: the client sends HTTP requests, the API validates them, and rows land in a tasks table. Create, read, update, delete — that is the whole surface.

Two things separate a real one from a toy. First, ownership: every request carries a logged-in user, and the API only ever touches rows where user_id matches that user. Second, pagination: a list endpoint returns a bounded page, not the whole table, so a busy account never ships ten thousand rows in one response.

See it work

How it works

The API maps HTTP verbs to rows. Each handler first reads the authenticated user, then scopes every query by user_id. The list endpoint is paginated and ordered so pages are stable.

# --- schema ---------------------------------------------------
CREATE TABLE tasks (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT      NOT NULL REFERENCES users(id),
  title       TEXT        NOT NULL,
  done        BOOLEAN     NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX tasks_by_user ON tasks (user_id, id);

# --- a couple of REST endpoints (pseudo-Python) ---------------
@post("/tasks")
def create_task(req, user):
    title = req.json["title"].strip()
    if not title or len(title) > 200:        # validate input
        return error(422, "title required, max 200 chars")
    row = db.insert(
        "INSERT INTO tasks (user_id, title) VALUES (?, ?) RETURNING *",
        user.id, title)
    return json(201, row)

@get("/tasks")
def list_tasks(req, user):
    limit  = min(int(req.query.get("limit", 20)), 100)
    cursor = req.query.get("cursor")          # keyset, not offset
    rows = db.query(
        "SELECT * FROM tasks "
        "WHERE user_id = ? AND (? IS NULL OR id < ?) "  # own rows only
        "ORDER BY id DESC LIMIT ?",
        user.id, cursor, cursor, limit + 1)
    next_cursor = rows[limit].id if len(rows) > limit else None
    return json(200, {"items": rows[:limit], "next_cursor": next_cursor})

Toggle and delete follow the same shape — they update or remove WHERE id = ? AND user_id = ?, so a request can never touch another person's task even if it guesses the id.

Trade-offs

Watch out for

• Missing ownership check. WHERE id = ? without AND user_id = ? lets anyone read or delete another person's task by guessing an id. Scope every query by the authenticated user.
• Offset pagination drift. With LIMIT/OFFSET, inserting a row between two page loads shifts everything down — the reader sees a duplicate or skips an item. Keyset (cursor) pagination on a stable sort key avoids it.
• No input validation. Trusting title blindly lets in empty strings, megabyte payloads, or HTML you later render unescaped. Validate type, presence, and length at the edge, and return 422, not 500.
• N+1 on the list. Fetching tasks, then looping to fetch each task's tags one query at a time, turns one page into hundreds of round-trips. Batch with a join or a single WHERE id IN (…).
• Unbounded list. SELECT * FROM tasks WHERE user_id = ? with no limit is fine until one account has 50,000 tasks. Always cap limit server-side.

Worked example

Maria opens the app. The client sends GET /tasks?limit=20 with her session token. The API resolves the token to user_id = 7, runs SELECT … WHERE user_id = 7 ORDER BY id DESC LIMIT 21, and returns the first 20 rows plus a next_cursor. She types "Buy milk" and hits add: POST /tasks with {"title":"Buy milk"}. The handler trims and length-checks the title, inserts (user_id=7, title='Buy milk', done=false), and replies 201 with the new row — which the client prepends to the list. She checks it off: PATCH /tasks/91 {"done":true} runs UPDATE tasks SET done=true WHERE id=91 AND user_id=7. One row changed. If Maria's id had been anything but 7, that update would have matched zero rows and returned 404 — the ownership clause does the security work silently.

Check yourself