# Pr Review Database API

> PR review checklist for database-backed FastAPI + SQLite + React APIs. Use when reviewing changes that touch API response models, database queries, or frontend-backend data contracts.

- Skill: `zaxbyhub/pr-review-database-api` (Agent Skill)
- Install (CLI): `npx skillmds@latest add zaxbyhub/pr-review-database-api`
- Raw SKILL.md: https://api.skillmd.com/api/skills/zaxbyhub/pr-review-database-api/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Web & Frontend
- Author: zaxbyhub (https://skillmd.com/u/zaxbyhub)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/zaxbyhub/pr-review-database-api

---


# PR Review: Database-Backed API

Use this skill alongside `swarm-pr-review` or `reviewing-code-core` when reviewing changes that touch API response models, SQL queries, or frontend-backend data contracts.

## Required Checks

### 1. Response Model / SELECT Column Alignment
- [ ] For every new or changed field in a Pydantic `BaseModel` response class, verify it appears in the SQL SELECT column list of every query path that returns that model.
- [ ] Check `list_*` endpoints separately from `get_*` endpoints — they often have different SELECT columns.
- [ ] Check that `_row_to_*_response` helper functions read the column (with a guard like `if "col" in keys`) and that the column is actually present in the query.
- [ ] Red flag: field in model, absent from SELECT, silently returns `null` via a guard clause.

### 2. Frontend / Backend Type Boundary
- [ ] Verify the TypeScript interface in `frontend/src/lib/api.ts` matches the backend response model.
- [ ] Do not assume backend `int` maps to frontend `number` — check the actual interface definition.
- [ ] Watch for `Set<string>.has(number)` or similar mismatches when document IDs are strings in the API but numbers in the database.
- [ ] Verify that frontend selection state (bulk selection, filters) uses the same ID type as the API response.

### 3. SQLite Transaction Safety
- [ ] When a route uses `BEGIN IMMEDIATE` or `BEGIN EXCLUSIVE`, check that no helper function called before it commits on the same shared connection.
- [ ] If a helper does commit (e.g., `mark_claims_stale_by_file`), verify it cannot raise before its internal commit, or use a separate connection.
- [ ] Check that `_purge_file_derived_data` style helpers do not leave implicit transactions open.
- [ ] Prefer separate connections for cleanup/audit vs. atomic operations.

### 4. Unbounded Concurrent Requests
- [ ] Scan for `Promise.allSettled` over list items in polling hooks or effects.
- [ ] If found, verify there is a batch endpoint or concurrency limit (`p-limit`, chunked requests).
- [ ] Red flag: `fetchWikiStatuses` firing N concurrent GETs for N indexed documents.
- [ ] Check that adaptive polling backoff does not amplify the flood on every tick.

### 5. SQL Performance
- [ ] Correlated subqueries for per-row aggregation (e.g., `COUNT(*) ... WHERE dt.tag_id = t.id`) are correct but suboptimal at scale.
- [ ] For tables expected to grow >100 rows, recommend JOIN + GROUP BY rewrite.
- [ ] Check that `IN (...)` subqueries have supporting indexes.
- [ ] Verify `INSERT OR IGNORE` in nested loops — for bulk operations, prefer `executemany` or batch INSERT.

### 6. Test Name / Behavior Alignment
- [ ] Tests named after behavior (e.g., "emits X on selection") must actually trigger and assert that behavior.
- [ ] A test that only renders the component without firing events is a coverage gap, not a behavior test.
- [ ] Check that `fireEvent` or `userEvent` calls exist for interaction tests.

### 7. Schema Migration Safety (when database.py modified)
- [ ] New columns added via `ALTER TABLE ADD COLUMN` only after `PRAGMA table_info()` confirms absence.
- [ ] New tables and indexes use `IF NOT EXISTS` guards in migration functions.
- [ ] FK indexes on new columns are created in the migration function, not in the SCHEMA string — avoids "no such column" on legacy databases where the column may not exist yet.
- [ ] Migration function is idempotent (safe to run multiple times).
- [ ] Migration function is registered in `run_migrations()` and called in the correct order relative to other migrations.
- [ ] Legacy databases are tested: start the app against an existing DB and verify the migration runs without `no such column` or `duplicate column name` errors.

## Forbidden Shortcuts
- Do not assume backend int = frontend number without checking the API type.
- Do not approve response model changes without checking all query SELECT columns.
- Do not ignore `Promise.allSettled` over dynamic lists without checking for batch endpoints.

## Delegation Template
When delegating a PR review affected by this skill, include:

```
SKILLS: file:.opencode/skills/generated/pr-review-database-api/SKILL.md
```

## Source Knowledge
- 9e69117c-f877-42e6-9ade-848b8552a03b — Response model / SELECT alignment
- 13da653c-37f5-4193-9a88-644f9a31fa2c — Frontend/backend type boundaries
- 5dd99955-2103-41c7-ba31-b1c1d8ca17c7 — SQLite transaction edge cases
- 670ea64b-3607-4a9c-9252-b09e12d22444 — Unbounded concurrent requests
- cfde9fb8-03e1-4f5c-95e7-13a460b85293 — Correlated subquery performance
- 0aa2d48d-66a3-4ff9-89bd-37bdb3a9f8d7 — Test name/behavior alignment
- 3905045d-3ae0-4bd8-ac0b-9ab65c1417ce — SQLite schema migration safety (IF NOT EXISTS, PRAGMA table_info, migration ordering)