v0.6.0 — direct Postgres backend + 213-test backend suite

Internal hardening. No user-visible feature changes. The PostgREST service is gone — FastAPI now talks to Postgres directly via a psycopg async pool — and the project finally has a real backend test suite (213 tests against a real Postgres testcontainer with per-test transaction rollback). Plus a handful of correctness + security fixes surfaced by the post-migration audits.

Changed — architecture

• Dropped PostgREST. Backend services now use psycopg[pool] async pool directly. Pool is opened/closed in the FastAPI lifespan; every service module migrated to the new helpers (db.fetch, db.fetchrow, db.fetchval, db.execute, db.db()). One fewer container, one fewer hop. POSTGREST_URL / POSTGREST_API_KEY / POSTGREST_AUTH env vars removed; the pool reads DATABASE_URL directly. Stack is now three containers (postgres + openstudy + frontend), not four.
• Auth code consumption is now atomic — single DELETE … RETURNING *, closing the previous TOCTOU window.
• Lecture-topics insertion is transactional — add_lecture_topics wraps the optional lecture create + every topic insert in one psycopg transaction.
• /api/health no longer blocks the event loop — storage stat moved off the request thread.
• Rate limiter prefers CF-Connecting-IP over the spoofable X-Forwarded-For.

Added — test infrastructure

• pytest + testcontainers-postgres with per-test transaction rollback (force_rollback=True + a _TxnPool connection pin) — clean DB every test without container churn.
• 213 backend tests: ~145 service-layer + 60 MCP-tool tests across 11 files (one per entity family) + 5 helper unit tests + 3 multi-step end-to-end scenarios (full OAuth lifecycle, login rate-limit, TOTP enroll+login).
• validated_cols helper — filters dict keys to Pydantic-declared schema fields before f-stringing into INSERT/UPDATE SQL. Defence in depth applied at all 16 patch/insert sites across 7 service files.

Added — OAuth

• POST /oauth/revoke (RFC 7009) — public clients call this on logout. Advertised in /.well-known/oauth-authorization-server.

Fixed

• update_settings no longer NULLs valid timezone/locale when callers pass None for unset parameters (exclude_none=True).
• update_settings fallback insert uses ON CONFLICT (id) DO UPDATE — concurrent first-callers no longer race the PK constraint into a 500.
• /api/auth/totp/setup is now an upsert. On a fresh DB the previous bare UPDATE matched zero rows but returned 200 with a generated secret — /totp/enable then 400'd.
• consume_auth_code rejects non-S256 PKCE unconditionally (OAuth 2.1 spec compliance).
• record_event ordering tie-breaker — clock_timestamp() + id DESC so events inserted in the same transaction order deterministically.
• storage._log savepoint — swallowed FK violations no longer poison the outer request transaction.
• Pool-level UUID loader — psycopg's uuid.UUID now decodes to str so Pydantic schemas don't need per-service conversions.

Removed

• postgrest-py dependency, the PostgREST container, and the legacy app.db.client() helper — all call sites migrated.

Migration notes (upgrading from v0.5.0)

1. Pull, then ./deploy.sh — runs with --remove-orphans so the PostgREST container is cleaned up automatically.
2. POSTGREST_URL / POSTGREST_API_KEY / POSTGREST_AUTH are no longer read; safe to remove from .env.
3. No schema changes.