---
description: psycopg2 pool, get_conn contextmanager, cache-table upserts, cache_meta TTL, schema.sql
paths:
  - "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.