42 lines
2.0 KiB
Markdown
42 lines
2.0 KiB
Markdown
---
|
|
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.
|