Server-side sessions with hashed, rotating ids and CSRF tokens

Decision

Keep sessions on the server. The browser holds an httpOnly, SameSite=Lax cookie (Secure in production) containing an HS256-signed JWT that points at a session row; the table stores only the SHA-256 of the session id. A session ends 24 hours after sign-in however it is used. Its id is replaced every 15 minutes of activity (the old row lives for a 60-second grace period), on every sign-in (so a planted id is worthless) and after a password change (which also signs out every other browser). Members can "sign out everywhere". State-changing requests carry three CSRF defences: Next.js's own Origin check for server actions, our own Origin and Fetch Metadata check (which also covers the JSON routes), and a per-session synchronizer token that comes back in a hidden form field.

Context

In 2022 the API signed a JWT { user, email }, the front end kept it in a JavaScript-readable cookie (js-cookie) and decoded it in the browser, and the server-side session store never worked (legacy/octo/error_report/error_report.md). The first revival moved the JWT into an httpOnly cookie pointing at a session row, but stored the raw session id, never rotated it, and relied on the framework's Origin check plus SameSite=Lax for CSRF.

Options considered

1. Stateless JWTs only. No database read per request, but no way to end a session early: signing out only deletes the browser's copy.
2. Server sessions with raw ids (the first revival). Revocable, but a copy of the database (a backup, an export) contains live session ids.
3. Server sessions with hashed ids, rotation and a CSRF token (chosen).
4. An auth library or service (Auth.js, Lucia, Clerk). Mature, but most of what Cradle needs is small, and owning it is part of the point of this project.
5. Double-submit cookie CSRF without server state. Works, but the synchronizer token is just as cheap here because the session row is read anyway.

Why

• Revocation needs state. "Sign out", "sign out everywhere" and "a new password ends other sessions" are only real with a server-side record.
• Hashing ids costs one SHA-256. It means a leaked database or a CSV export can't be replayed as a cookie.
• Rotation shortens a stolen cookie's useful life to about 15 minutes while the real member is active, without signing anyone out mid-click (the grace period, and a conditional update so only one concurrent request rotates).
• Defence in depth for CSRF. The framework check depends on Host and X-Forwarded-Host being right; a misconfigured proxy or a broad allowedOrigins would quietly disable it. The token doesn't depend on headers at all.

What happened

• Tests (src/server/security.test.ts) cover hashed storage, a single winner when two requests try to rotate the same session, the grace period, the absolute 24-hour limit surviving rotation, and sign-out-everywhere.
• Migration 0002 deletes every existing session, because rows keyed by raw ids can never match a cookie again. Everyone signed in at deploy time signs in once more.
• "Sign out everywhere" is switched off for the shared demo accounts; otherwise any visitor could sign every other visitor out.
• Weak spots, stated plainly:
  • Rotation only happens on requests that can set cookies (server actions and route handlers). The hub polls /api/me/pulse every 5 seconds, so an open hub rotates on time, but someone only reading pages keeps one id for up to 24 hours.
  • The CSRF token reaches forms through a readable cookie after the page hydrates. A signed-in form submitted before JavaScript runs is refused with "refresh and try again", so those forms no longer work without JavaScript.
  • The per-account limit on failed sign-ins (DR-007) lets someone lock an account's password form for 15 minutes. The one-click demo buttons bypass it so the demo can't be locked.

What I'd change

• Rotate in proxy.ts once it can reach the database cheaply, so page views rotate too.
• Add an idle timeout (for example 2 hours without activity) alongside the absolute one.
• Use the __Host- cookie prefix in production.
• Offer passkeys (WebAuthn) for real accounts.