test(backend): integration fixtures — psycopg, truncate isolation, seeded users

[AndresL230]

Context

Re-scoped 2026-07-23 against main @ 5dee71d. The original text described PR #377 as unmerged and #396 as pending; both have landed. The scope below is unchanged and still entirely undone — only the starting point moved.

The integration lane exists but does not yet test the database. backend/tests/integration/conftest.py currently provides:

• a mint_session wrapper that now delegates to the canonical services/session_tokens.py::mint_session (test(backend): cookie-minting test-auth endpoint for local/test envs #381),
• a session-scoped _require_local_stack gate (RUN_INTEGRATION=1 + local SUPABASE_URL) that idempotently runs db.seed_local_rich,
• client and anon_client fixtures that are bare TestClient(app) instances with no cookie — and, today, literally identical to each other.

Three gaps make the lane much weaker than it looks:

1. Nothing verifies a write independently of the layer that made it. In tests/integration/test_local_stack.py, test_db_roundtrip_write_read_delete and test_encryption_roundtrip_text_and_numeric write and assert through db.connection.table() — the same PostgREST layer both ways, which tests the echo rather than the database; test_route_e2e_auth_me and test_route_e2e_gradebook_decrypt_numeric assert only on the HTTP JSON the app returns. A wrong .eq() chain or a broken on_conflict target can round-trip cleanly through the same wrong abstraction. The defining constraint of this lane: writes go through the app; assertions read back with raw SQL.
2. Seeding is additive and never resets, so tests are order-dependent by construction and one test's writes leak into the next.
3. There is no second user, so ownership/IDOR negatives cannot be expressed against real rows.

Each of the four existing tests also hand-rolls its own auth (client.cookies.set("sapling_session", mint_session(_ACTIVE))), which is exactly the duplication authed_client should absorb.

What changed since this issue was written

• test(backend): integration marker + conftest gating for the real-DB lane #396 is closed — the marker and the hermetic-fixture bypasses are on main; there is now also a third guard (_hermetic_llm_transport, test(backend): hermetic LLM guard fixture — fail on unstubbed Gemini egress #379) that exempts integration.
• test(backend): cookie-minting test-auth endpoint for local/test envs #381 landed, which gives you two ways to build authed_client: call services.session_tokens.mint_session directly (in-process, preferred for pytest), or drive POST /api/auth/test-login over HTTP. Prefer the direct call here and leave the HTTP endpoint for Playwright (test(e2e): Playwright harness and fixtures #385).
• The default lane is now 1034 passing, not the 970 quoted in the epic.

Scope

• Session-scoped psycopg connection on SUPABASE_DB_URL — the raw-SQL assertion seam. This is the point of the issue; do not substitute PostgREST reads.
• A local-only assertion on SUPABASE_DB_URL itself, before any truncate can run. The existing _require_local_stack guard checks only that SUPABASE_URL is local; SUPABASE_DB_URL is an independent variable and is never inspected, so the guard can pass while the psycopg connection points elsewhere. backend/.env.staging and backend/.env.production both hold live direct-Postgres strings for the real projects. Mirror _is_local() against SUPABASE_DB_URL and fail loudly rather than skip — a silent skip reads as "safe" while a misconfigured var would truncate real data on every run. This is a hard prerequisite of the truncate fixture, not a nice-to-have.
• Autouse truncate between tests, with the seeded baseline restored (or re-seeded) after each. Must not truncate the catalog tables the rich seed depends on unless it re-seeds them.
• A seeded_user factory producing distinct, approved users on demand.
• authed_client and other_user_client, replacing the per-test cookies.set boilerplate.
• Refactor the four tests in test_local_stack.py onto the new fixtures; convert at least one to assert via raw SQL so the pattern is demonstrated.
• Build on the existing conftest — do not duplicate or fork it.

Acceptance criteria

• The psycopg connection refuses to open against a non-local SUPABASE_DB_URL, with a test proving it raises (not skips).
• A sample test writes via the app and asserts via raw SQL (psycopg), not via table().
• Truncate isolation proven: the suite passes under -p no:randomly and in reversed/shuffled order.
• authed_client and other_user_client resolve to genuinely different users, asserted by a test.
• The default hermetic lane (1034 tests) is untouched and still green.

Dependencies

Depends on: #381 (done), #396 (done) — this issue is now unblocked
Blocks: #398, #400

---

Chapter: 1 (Regression Suite) | Lane: subcutaneous | Size: M
Part of epic #402