nike/.claude/rules/db.md

description, paths

description	paths
psycopg2 pool, get_conn contextmanager, cache-table upserts, cache_meta TTL, schema.sql	nike/db.py schema.sql scripts/apply_schema.py

PostgreSQL cache layer

• psycopg2 with a ThreadedConnectionPool, not SQLAlchemy, not async. The pool is created in the FastAPI lifespan (create_pool) and closed on shutdown. New DB work checks out a connection via the get_conn() contextmanager (commit-on-success / rollback-on-exception / return-to-pool). Never open a bare connection for query work.

• Exception: check_connection() deliberately connects outside the pool (a fresh psycopg2.connect) so it can report health even when the pool is unhealthy. Note this is called on every /ready, /metrics, and /api/status — it's a known hot-path cost (flag if you're optimising), not a pattern to copy for normal queries.

• Cache writes are upserts (cache_team, cache_league, cache_player, cache_event, and the event sub-tables). They tolerate the raw TheSportsDB dict shape. Reads (query_team, query_player_by_id, query_roster) return the DB-row shape (name/id/league_name). Keep both shapes consistent with what the tools expect (see cache-api / mcp-tools).

• cache_meta drives TTL freshness for the volatile-ish rows and powers the dashboard's "last cache" display. invalidate_cache("%") clears it (the Clear Cache path). Keep meta updates alongside the data upserts they describe.

• Schema lives in schema.sql, applied by scripts/apply_schema.py. There is no migration framework — a schema change is an edit to schema.sql plus a thought about existing cache DBs (the cache is disposable, so a rebuild is usually fine, but say so). get_table_counts lists the canonical tables; keep it in sync when you add one.

• Parameterise all SQL. Never f-string user/API values into a query. The one f-string on a table name in get_table_counts is over a hardcoded allowlist — keep it that way.