# Email/Password Login with Server-Side Sessions Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Add email/password registration and login to the existing Flask app, with sessions stored server-side in SQLite so they can be expired and revoked.

**Architecture:** All persistence lives in `models.py`: user accounts (the existing, unused `users` table) plus a new `sessions` table keyed by a random token. `app.py` gains register/login/logout routes, a `before_request` hook that resolves the session cookie to `g.current_user`, and a `login_required` decorator. The browser only ever holds the opaque token in a cookie — no session data client-side.

**Tech Stack:** Flask, Jinja2 templates, SQLite via the `sqlite3` module, `werkzeug.security` for password hashing, pytest.

**Spec:** The task brief supplied for this plan (add email/password login with server-side sessions to the existing app: `app.py` routes `/`, `/items`, `/items/<id>`; `models.py` with an unused `users` table; `templates/`; 3 existing pytest tests). No separate spec document exists.

## Global Constraints

- No new third-party packages and no ORM. Password hashing uses `werkzeug.security`, which ships with Flask.
- The existing `users` table is unused and holds no data worth keeping (per the brief). If its schema differs from the pinned schema in Task 1, drop and recreate it.
- Session lifetime is 604800 seconds (7 days) everywhere: the `sessions.expires_at` value and the cookie Max-Age use the same number.
- Session cookie name is `session_id`. Flags: `HttpOnly` always, `SameSite=Lax`, `Secure` whenever `app.debug` is False.
- Login failure returns one identical message — `Invalid email or password` — and status 401 for unknown email and wrong password alike.
- Session functions in `models.py` take `now: int` (epoch seconds) as a parameter and never read the clock themselves, so expiry is testable without sleeping.
- All new tests go in `tests/test_auth.py`. The full suite (`pytest`) — new tests plus the 3 existing ones — must pass at the end of every task.

## Review Focus

1. Registering with an email that already has an account → form error, and exactly one row for that email afterwards. (Test pinned in Task 3.)
2. Login with an unregistered email vs. a wrong password → identical status and body, so the form cannot enumerate accounts. (Test pinned in Task 4.)
3. A session token past its `expires_at` → treated as logged out, and the row is deleted when seen. (Test pinned in Task 2.)
4. A session cookie carrying a random, forged value → treated as logged out, never a 500. (Tests pinned in Tasks 2 and 4.)
5. Submitting `/register` or `/login` with an empty email or password → form error; no user row and no session row created. (Tests pinned in Tasks 3 and 4.)

---

## File Structure

- `models.py` (modify) — owns all auth persistence: `ensure_auth_schema`, user functions, session functions, `SESSION_TTL_SECONDS`. One responsibility: data.
- `app.py` (modify) — owns HTTP: `get_conn`, register/login/logout routes, `load_current_user`, `login_required`, cookie flags. One responsibility: requests and responses.
- `templates/register.html` (create) — registration form.
- `templates/login.html` (create) — login form with error area.
- `templates/base.html` (modify) — nav shows login/register links or the current user's email plus a logout form. (If the base template has a different name, it is the template the existing pages extend.)
- `tests/test_auth.py` (create) — all new tests; model tests run against `:memory:` connections, route tests against a temp-file database.

## Task 1: Auth schema and user accounts in models.py

**Files:**
- Modify: `models.py`
- Test: `tests/test_auth.py`

**Interfaces:**
- Consumes: nothing (first task).
- Produces:
  - `ensure_auth_schema(conn: sqlite3.Connection) -> None` — guarantees `users(id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, password_hash TEXT NOT NULL)` and `sessions(token TEXT PRIMARY KEY, user_id INTEGER NOT NULL, expires_at INTEGER NOT NULL)` exist.
  - `create_user(conn: sqlite3.Connection, email: str, password: str) -> int` — stores `generate_password_hash(password)`, returns the new user id, raises `sqlite3.IntegrityError` on duplicate email.
  - `get_user_by_email(conn: sqlite3.Connection, email: str) -> sqlite3.Row | None` — row keys: `id`, `email`, `password_hash`.
  - `verify_password(user: sqlite3.Row, password: str) -> bool`.

- [ ] **Step 1: Check what the existing `users` table actually looks like**

Run: `python -c "import sqlite3; c = sqlite3.connect('app.db'); print(c.execute(\"SELECT sql FROM sqlite_master WHERE name='users'\").fetchall())"` (adjust the database path to the one `models.py` already uses)
Expected: one CREATE TABLE statement. Compare its columns against the pinned schema above — the comparison decides nothing, the pinned schema wins either way (Global Constraints), but the implementer needs to know whether `sessions` already exists too.

- [ ] **Step 2: Write the failing tests for schema and users**

```python
def test_create_user_stores_hash_not_plaintext(conn):
    uid = create_user(conn, "a@example.com", "s3cret")
    row = get_user_by_email(conn, "a@example.com")
    assert row["id"] == uid
    assert row["password_hash"] != "s3cret"
    assert verify_password(row, "s3cret") is True
    assert verify_password(row, "wrong") is False

def test_create_user_duplicate_email_raises(conn):
    create_user(conn, "a@example.com", "s3cret")
    with pytest.raises(sqlite3.IntegrityError):
        create_user(conn, "a@example.com", "other")
```

(`conn` is a fixture in the same file returning an `:memory:` connection with `row_factory = sqlite3.Row` and `ensure_auth_schema` applied.)

- [ ] **Step 3: Run the tests to verify they fail**

Run: `pytest tests/test_auth.py -v`
Expected: FAIL with `ImportError` / `NameError` — `create_user` not defined yet.

- [ ] **Step 4: Implement the four functions in `models.py` with the exact signatures above**

`ensure_auth_schema` runs `CREATE TABLE IF NOT EXISTS` for both pinned tables; if Step 1 showed a `users` table whose columns differ, it drops and recreates `users` first (unused table, Global Constraints). `create_user` is one INSERT; the UNIQUE constraint is what raises on duplicates — do not pre-check.

- [ ] **Step 5: Run the tests to verify they pass**

Run: `pytest tests/test_auth.py -v`
Expected: PASS (2 tests).

- [ ] **Step 6: Commit**

```bash
git add models.py tests/test_auth.py
git commit -m "feat: auth schema and user accounts in models"
```

## Task 2: Server-side session store in models.py

**Files:**
- Modify: `models.py`
- Test: `tests/test_auth.py`

**Interfaces:**
- Consumes: `ensure_auth_schema` and user ids from Task 1.
- Produces:
  - `SESSION_TTL_SECONDS = 604800`
  - `create_session(conn: sqlite3.Connection, user_id: int, now: int) -> str` — token is `secrets.token_urlsafe(32)`; inserts `(token, user_id, now + SESSION_TTL_SECONDS)`; returns the token.
  - `get_user_id_for_session(conn: sqlite3.Connection, token: str, now: int) -> int | None` — returns the user id when the row exists and `expires_at > now`; an expired row is deleted and returns None; an unknown token returns None.
  - `delete_session(conn: sqlite3.Connection, token: str) -> None` — deleting an unknown token is not an error.

- [ ] **Step 1: Write the failing tests**

```python
def test_session_roundtrip(conn):
    uid = create_user(conn, "a@example.com", "s3cret")
    token = create_session(conn, uid, now=1000)
    assert get_user_id_for_session(conn, token, now=1001) == uid

def test_unknown_token_returns_none(conn):
    assert get_user_id_for_session(conn, "forged-value", now=1001) is None

def test_expired_session_returns_none_and_is_deleted(conn):
    uid = create_user(conn, "a@example.com", "s3cret")
    token = create_session(conn, uid, now=1000)
    later = 1000 + SESSION_TTL_SECONDS + 1
    assert get_user_id_for_session(conn, token, now=later) is None
    assert conn.execute("SELECT COUNT(*) FROM sessions").fetchone()[0] == 0
```

- [ ] **Step 2: Run the tests to verify they fail**

Run: `pytest tests/test_auth.py -v`
Expected: FAIL — `create_session` not defined.

- [ ] **Step 3: Implement the three functions and the constant with the exact signatures above**

- [ ] **Step 4: Run the tests to verify they pass**

Run: `pytest tests/test_auth.py -v`
Expected: PASS (5 tests).

- [ ] **Step 5: Commit**

```bash
git add models.py tests/test_auth.py
git commit -m "feat: server-side session store with expiry"
```

## Task 3: Registration route and template

**Files:**
- Modify: `app.py`
- Create: `templates/register.html`
- Test: `tests/test_auth.py`

**Interfaces:**
- Consumes: `create_user`, `get_user_by_email` (Task 1). Also produces the connection wiring every later route task uses:
  - `get_conn() -> sqlite3.Connection` in `app.py` — returns `models.connect(app.config["DATABASE_PATH"])` when `models.py` exposes a `connect(path)` helper; if it does not, add `connect(path: str) -> sqlite3.Connection` to `models.py` in this task (same `:memory:`-style settings: `row_factory = sqlite3.Row`). Route tests set `app.config["DATABASE_PATH"]` to a temp file and call `ensure_auth_schema` on it in the fixture.
- Produces: `GET /register` renders the form; `POST /register` with form fields `email`, `password`:
  - empty email or password → 400, form re-rendered with an error, no row created;
  - duplicate email (`sqlite3.IntegrityError` from `create_user`) → 400, error text `Email already registered`, still exactly one row;
  - success → user row created, 302 redirect to `/login`.

- [ ] **Step 1: Write the failing route tests**

```python
def test_register_creates_user_and_redirects(client, db_conn):
    resp = client.post("/register", data={"email": "a@example.com", "password": "s3cret"})
    assert resp.status_code == 302 and "/login" in resp.headers["Location"]
    assert get_user_by_email(db_conn, "a@example.com") is not None

def test_register_duplicate_email_rejected(client, db_conn):
    client.post("/register", data={"email": "a@example.com", "password": "s3cret"})
    resp = client.post("/register", data={"email": "a@example.com", "password": "other"})
    assert resp.status_code == 400
    assert db_conn.execute("SELECT COUNT(*) FROM users").fetchone()[0] == 1

def test_register_empty_password_creates_no_user(client, db_conn):
    resp = client.post("/register", data={"email": "a@example.com", "password": ""})
    assert resp.status_code == 400
    assert get_user_by_email(db_conn, "a@example.com") is None
```

- [ ] **Step 2: Run the tests to verify they fail**

Run: `pytest tests/test_auth.py -v`
Expected: FAIL — 404 on `/register`.

- [ ] **Step 3: Add `get_conn()` wiring and the `/register` route to `app.py`, and create `templates/register.html`**

The template is a form posting `email` and `password` to `/register` with an error paragraph rendered when the route passes one. The route validates emptiness before calling `create_user` and catches `sqlite3.IntegrityError` for the duplicate case — the two error paths above are the whole behaviour.

- [ ] **Step 4: Run the tests to verify they pass**

Run: `pytest tests/test_auth.py -v`
Expected: PASS (8 tests).

- [ ] **Step 5: Commit**

```bash
git add app.py models.py templates/register.html tests/test_auth.py
git commit -m "feat: registration route and template"
```

## Task 4: Login, logout, and current-user loading

**Files:**
- Modify: `app.py`
- Create: `templates/login.html`
- Test: `tests/test_auth.py`

**Interfaces:**
- Consumes: `get_user_by_email`, `verify_password` (Task 1); `create_session`, `get_user_id_for_session`, `delete_session`, `SESSION_TTL_SECONDS` (Task 2); `get_conn()` (Task 3).
- Produces:
  - `POST /login` with form fields `email`, `password`:
    - empty field → 400 with a form error, no session row;
    - unknown email or wrong password → 401 with body containing `Invalid email or password`, identical for both cases, no session row, no cookie;
    - success → one session row, 302 to `/`, and `Set-Cookie: session_id=<token>` carrying `HttpOnly`, `SameSite=Lax`, `Max-Age=604800`, plus `Secure` when `app.debug` is False. Set it with `resp.set_cookie("session_id", token, httponly=True, samesite="Lax", secure=not app.debug, max_age=SESSION_TTL_SECONDS)`.
  - `POST /logout` — deletes the session row for the presented token (if any) and clears the cookie; the old token no longer resolves afterwards.
  - `load_current_user()` registered with `@app.before_request` — sets `g.current_user` to the user row for a valid cookie token, else None. A forged cookie value yields None, never an exception (Task 2 already guarantees None for unknown tokens).

- [ ] **Step 1: Write the failing tests**

```python
def test_login_success_sets_flagged_cookie_and_session(client, db_conn):
    client.post("/register", data={"email": "a@example.com", "password": "s3cret"})
    resp = client.post("/login", data={"email": "a@example.com", "password": "s3cret"})
    assert resp.status_code == 302
    cookie = resp.headers["Set-Cookie"]
    assert "session_id=" in cookie and "HttpOnly" in cookie and "SameSite=Lax" in cookie
    assert db_conn.execute("SELECT COUNT(*) FROM sessions").fetchone()[0] == 1

def test_login_failures_are_identical(client):
    client.post("/register", data={"email": "a@example.com", "password": "s3cret"})
    unknown = client.post("/login", data={"email": "ghost@example.com", "password": "x"})
    wrong = client.post("/login", data={"email": "a@example.com", "password": "nope"})
    assert unknown.status_code == wrong.status_code == 401
    assert unknown.data == wrong.data

def test_logout_kills_the_session_server_side(client, db_conn):
    client.post("/register", data={"email": "a@example.com", "password": "s3cret"})
    client.post("/login", data={"email": "a@example.com", "password": "s3cret"})
    token = db_conn.execute("SELECT token FROM sessions").fetchone()[0]
    client.post("/logout")
    assert get_user_id_for_session(db_conn, token, now=int(time.time())) is None

def test_forged_cookie_is_logged_out_not_an_error(client):
    client.set_cookie("session_id", "forged-value")
    assert client.get("/").status_code == 200
```

- [ ] **Step 2: Run the tests to verify they fail**

Run: `pytest tests/test_auth.py -v`
Expected: FAIL — 404 on `/login`.

- [ ] **Step 3: Implement the routes, `load_current_user`, and `templates/login.html` with the behaviour pinned in Interfaces**

Login looks the user up first and calls `verify_password` only when a row exists, but returns the pinned 401 body in both branches — one code path for the response is what keeps the two failures identical.

- [ ] **Step 4: Run the tests to verify they pass, then the whole suite**

Run: `pytest tests/test_auth.py -v` then `pytest -q`
Expected: PASS (12 tests in the file; full suite green including the 3 pre-existing tests).

- [ ] **Step 5: Commit**

```bash
git add app.py templates/login.html tests/test_auth.py
git commit -m "feat: login, logout, and current-user loading"
```

## Task 5: Protect item creation and show auth state in the nav

**Files:**
- Modify: `app.py`
- Modify: `templates/base.html`
- Test: `tests/test_auth.py`

**Interfaces:**
- Consumes: `g.current_user` from `load_current_user` (Task 4).
- Produces:
  - `login_required(view)` decorator in `app.py` — when `g.current_user` is None, returns a 302 redirect to `/login`; otherwise calls the view.
  - `POST /items` (the item-creation route, if it exists alongside the listed `GET /items`) requires login; `GET /`, `GET /items`, and `GET /items/<id>` stay public. If item creation is not a route in this app, apply `login_required` to nothing yet and say so in the commit message — the decorator and its tests are still this task's deliverable.
  - Nav in the base template: logged out → "Log in" and "Register" links; logged in → the user's email and a logout form posting to `/logout`.

- [ ] **Step 1: Write the failing tests**

```python
def test_anonymous_cannot_create_item(client):
    resp = client.post("/items", data={"name": "x"})
    assert resp.status_code == 302 and "/login" in resp.headers["Location"]

def test_logged_in_user_can_create_item(client):
    client.post("/register", data={"email": "a@example.com", "password": "s3cret"})
    client.post("/login", data={"email": "a@example.com", "password": "s3cret"})
    resp = client.post("/items", data={"name": "x"})
    assert resp.status_code in (200, 302) and "/login" not in resp.headers.get("Location", "")
```

(If the app creates items at a different path or with different fields, the implementer matches the route and form the codebase already exposes — the two assertions about who gets in are what must not change.)

- [ ] **Step 2: Run the tests to verify they fail**

Run: `pytest tests/test_auth.py -v`
Expected: FAIL — anonymous POST is not redirected.

- [ ] **Step 3: Implement `login_required`, apply it to item creation, and update the nav in the base template**

- [ ] **Step 4: Run the full suite**

Run: `pytest -q`
Expected: PASS — all new tests plus the 3 pre-existing tests.

- [ ] **Step 5: Commit**

```bash
git add app.py templates/base.html tests/test_auth.py
git commit -m "feat: require login for item creation, auth state in nav"
```