Add entries(project_id, created_at DESC) index for the project-scoped list query #95

New issue

Closed

opened 2026-07-15 19:51:30 +00:00 by claude-bot · 1 comment

claude-bot commented

2026-07-15 19:51:30 +00:00

Contributor

Severity: Medium · Confidence: High · Effort: XS · Category: code (performance)

Evidence

api/src/db/migrations/002_entries_tags_audit.sql:19-23 - indexes lead with user_id; 026_projects.sql:119 - partial deleted_at index only.
api/src/routes/entries.ts:73-77,85,187-193,321 - WHERE e.project_id = (${ACTIVE_PROJECT_SQL}) . ORDER BY e.created_at DESC.

Problem
v4 made project_id the scoping column for every list view, but the indexes still lead with user_id. The hottest query in the app (entry list, filtered by project + sorted by created_at) has no index serving its filter+sort.

Impact
Sequential scan + sort on entry listing as data grows; degrades the primary UI. Latent - bites at scale (a long-running research project).

Fix
Migration 044:
CREATE INDEX entries_project_created_idx ON entries (project_id, created_at DESC) WHERE deleted_at IS NULL;
and consider one on motifs(project_id).

Acceptance criteria

Migration 044 adds the index; EXPLAIN on the entry-list query uses it.

Filed from the 2026-07-15 codebase audit. Full report: docs/.internal/report-2026-07-15.md (gitignored).

**Severity:** Medium · **Confidence:** High · **Effort:** XS · Category: code (performance) **Evidence** - `api/src/db/migrations/002_entries_tags_audit.sql:19-23` - indexes lead with `user_id`; `026_projects.sql:119` - partial `deleted_at` index only. - `api/src/routes/entries.ts:73-77,85,187-193,321` - `WHERE e.project_id = (${ACTIVE_PROJECT_SQL}) . ORDER BY e.created_at DESC`. **Problem** v4 made `project_id` the scoping column for every list view, but the indexes still lead with `user_id`. The hottest query in the app (entry list, filtered by project + sorted by created_at) has no index serving its filter+sort. **Impact** Sequential scan + sort on entry listing as data grows; degrades the primary UI. Latent - bites at scale (a long-running research project). **Fix** Migration 044: `CREATE INDEX entries_project_created_idx ON entries (project_id, created_at DESC) WHERE deleted_at IS NULL;` and consider one on `motifs(project_id)`. **Acceptance criteria** - [ ] Migration 044 adds the index; EXPLAIN on the entry-list query uses it. --- _Filed from the 2026-07-15 codebase audit. Full report: `docs/.internal/report-2026-07-15.md` (gitignored)._

claude-bot added this to the v7.2.0 - Reliability, data integrity & docs milestone

2026-07-15 19:51:30 +00:00

claude-bot added the

enhancement

label

2026-07-15 19:51:30 +00:00

rbrooks referenced this issue from a commit

2026-07-17 02:36:46 +00:00

fix(v7.2): wave 2 — ops hygiene (#95, #100, #101, #102)

claude-bot commented

2026-07-17 02:53:54 +00:00

Author

Contributor

Fixed in c1ed843 (v7.2.0 wave 2).

Migration 045_project_scoped_indexes.sql (not 044 — that number went to #98 in wave 1):

CREATE INDEX entries_project_created_idx
  ON entries (project_id, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX motifs_project_idx
  ON motifs (project_id) WHERE deleted_at IS NULL;

Leads with project_id (equality), then created_at DESC (the sort), partial on deleted_at IS NULL to match the query exactly and stay small. Took the "consider one on motifs(project_id)" suggestion too.

Acceptance criteria:

Migration adds the index; EXPLAIN on the entry-list query uses it.

Verified on dev after deploy — migration applied ✓, and the planner picks it with no Sort node (the index supplies the ordering):

 Limit
   InitPlan 1 (returns $0)
     ->  Limit
           ->  Seq Scan on projects
   ->  Index Scan using entries_project_created_idx on entries e
         Index Cond: (project_id = $0)

CI green (242/242).

Fixed in `c1ed843` (v7.2.0 wave 2). Migration **`045_project_scoped_indexes.sql`** (not 044 — that number went to #98 in wave 1): ```sql CREATE INDEX entries_project_created_idx ON entries (project_id, created_at DESC) WHERE deleted_at IS NULL; CREATE INDEX motifs_project_idx ON motifs (project_id) WHERE deleted_at IS NULL; ``` Leads with `project_id` (equality), then `created_at DESC` (the sort), partial on `deleted_at IS NULL` to match the query exactly and stay small. Took the "consider one on `motifs(project_id)`" suggestion too. **Acceptance criteria:** - [x] Migration adds the index; EXPLAIN on the entry-list query uses it. Verified on dev after deploy — migration applied `✓`, and the planner picks it with **no Sort node** (the index supplies the ordering): ``` Limit InitPlan 1 (returns $0) -> Limit -> Seq Scan on projects -> Index Scan using entries_project_created_idx on entries e Index Cond: (project_id = $0) ``` CI green (242/242).

claude-bot closed this issue

2026-07-17 02:54:17 +00:00