rosa/vernier

Watch

Fork 0

perf: post-listing queries are unbounded; paginate profile/feed/tag/admin lists #154

New issue

Open

opened 2026-08-09 01:05:28 +00:00 by rosa · 2 comments

rosa commented

2026-08-09 01:05:28 +00:00

Owner

Found during a pre-production review.

Location: crates/infra/src/repositories/sqlite.rs (post-listing queries); consumed by profile, feed, tag, and admin handlers in crates/web/src/handlers/
Severity: Unbounded growth — scaling / availability

Problem

Every post-listing query is ORDER BY … DESC with no LIMIT/OFFSET. The listing paths — a user's Profile, RSS/Atom feeds, tag pages, and the admin post/user lists — each load the full matching history into memory on every request and render all of it. Confirmed across the compiled statements in .sqlx/ (no LIMIT in any listing query).

For a service whose top design goal is "runs on a small box... for years" (ARCHITECTURE.md, Goal #1), this degrades without bound as post count grows: memory per request, render time, and — for feeds — payload size all scale with total history rather than a fixed window.

Failure scenario

A prolific author accumulates thousands of Notes. Every hit on their Profile, every feed poll by every RSS reader, and every tag page reloads and renders the entire corpus. Feed responses grow to megabytes; a burst of feed pollers can pin CPU and memory on the small box the project targets.

Suggested fix

Feeds: cap to the most recent N items (readers expect a bounded window; ~20–50 is conventional). This alone removes the worst case.
Profile / tag / admin lists: paginate (keyset/created_at-cursor fits the ORDER BY … DESC shape better than OFFSET and stays cheap deep into history). Push the limit down into the repository query, not the handler.

Since the port method signatures change, this touches the AppRepository port and the Posts Service, not just SQL.

Found during a pre-production review. **Location:** `crates/infra/src/repositories/sqlite.rs` (post-listing queries); consumed by profile, feed, tag, and admin handlers in `crates/web/src/handlers/` **Severity:** Unbounded growth — scaling / availability ## Problem Every post-listing query is `ORDER BY … DESC` with **no `LIMIT`/`OFFSET`**. The listing paths — a user's Profile, RSS/Atom feeds, tag pages, and the admin post/user lists — each load the full matching history into memory on every request and render all of it. Confirmed across the compiled statements in `.sqlx/` (no `LIMIT` in any listing query). For a service whose top design goal is "runs on a small box... for years" (ARCHITECTURE.md, Goal #1), this degrades without bound as post count grows: memory per request, render time, and — for feeds — payload size all scale with total history rather than a fixed window. ## Failure scenario A prolific author accumulates thousands of Notes. Every hit on their Profile, every feed poll by every RSS reader, and every tag page reloads and renders the entire corpus. Feed responses grow to megabytes; a burst of feed pollers can pin CPU and memory on the small box the project targets. ## Suggested fix - **Feeds:** cap to the most recent N items (readers expect a bounded window; ~20–50 is conventional). This alone removes the worst case. - **Profile / tag / admin lists:** paginate (keyset/`created_at`-cursor fits the `ORDER BY … DESC` shape better than `OFFSET` and stays cheap deep into history). Push the limit down into the repository query, not the handler. Since the port method signatures change, this touches the `AppRepository` port and the Posts Service, not just SQL.

rosa added the

workflow

needs-triage

label

2026-08-09 01:05:37 +00:00

rosa referenced this issue

2026-08-09 01:12:45 +00:00

perf: bound RSS/Atom feeds to the most recent N posts (split from #154) #158

rosa referenced this issue

2026-08-09 01:13:05 +00:00

perf: bound RSS/Atom feeds to the most recent N posts (split from #154) #158

rosa added

and removed

labels

2026-08-09 01:13:06 +00:00

rosa commented

2026-08-09 01:13:06 +00:00

Author

Owner

This was generated by AI during triage.

Triage: scope narrowed, feed cap split out

The feed-bounding half of this issue is decision-free and has been split to its own ready-for-agent issue (feed item cap). This issue now covers only the profile, tag-listing, and admin post/user lists — the parts that need design decisions before an agent can build them.

Marked ready-for-human because the remaining work turns on judgment calls, not a mechanical fix:

Pagination strategy — keyset/cursor (fits the existing ORDER BY created_at DESC shape, stays cheap deep into history) vs. LIMIT/OFFSET (simpler, degrades on deep pages).
Page size, and whether it differs per surface (public profile vs. admin tables).
Navigation UX — page links vs. "load more" vs. cursor-in-URL, and how that reads on a minimalist hypermedia site.
Port surface — these choices change AppRepository listing method signatures and the Posts Service, so the shape wants deciding before implementation.

Once these are settled (a /grilling pass would do it), this can move to ready-for-agent with a concrete brief.

> *This was generated by AI during triage.* ## Triage: scope narrowed, feed cap split out The feed-bounding half of this issue is decision-free and has been split to its own `ready-for-agent` issue (feed item cap). **This issue now covers only the profile, tag-listing, and admin post/user lists** — the parts that need design decisions before an agent can build them. Marked `ready-for-human` because the remaining work turns on judgment calls, not a mechanical fix: - **Pagination strategy** — keyset/cursor (fits the existing `ORDER BY created_at DESC` shape, stays cheap deep into history) vs. `LIMIT`/`OFFSET` (simpler, degrades on deep pages). - **Page size**, and whether it differs per surface (public profile vs. admin tables). - **Navigation UX** — page links vs. "load more" vs. cursor-in-URL, and how that reads on a minimalist hypermedia site. - **Port surface** — these choices change `AppRepository` listing method signatures and the Posts Service, so the shape wants deciding before implementation. Once these are settled (a `/grilling` pass would do it), this can move to `ready-for-agent` with a concrete brief.

rosa added

and removed

labels

2026-08-09 04:25:51 +00:00

rosa commented

2026-08-09 04:25:52 +00:00

Author

Owner

This was generated by AI during triage.

Agent Brief

Supersedes the earlier ready-for-human scope note above. Design settled via a grilling session; this brief is the contract.

Category: enhancement
Summary: Paginate the four unbounded post/user listing surfaces with opaque keyset cursors and two-way (Newer/Older) hyperlink navigation.

Current behavior:
The public profile "listed" feed, the tag listing, the admin posts list, and the admin users list each load their entire collection in one query (ORDER BY … DESC, no limit) and render every row. The backing port methods return unbounded Vec<T>. This grows without bound as content accumulates, against the "runs on a small box for years" goal.

Desired behavior:
Each of those four surfaces returns at most one page of rows, newest-first as today, with in-page links to the adjacent pages. Navigation is keyset (cursor)-based, not offset-based, and is expressed as plain hyperlinks (no client JS — the site has no JS framework and CSP is script-src 'self').

Settled design:

Scope — exactly these four: profile listed (public), tag listing (public), admin posts, admin users. Explicitly unchanged: the author-only pending list on the profile page stays unbounded (small, author-only; a dedicated pending page is a possible future follow-up); the dashboard (not a list); and the feeds (bounded separately in #158).
Keyset cursor with a unique tiebreaker. The cursor is the last row's full sort-key tuple, with the primary key appended as the final tiebreaker and added to ORDER BY so the order is strictly total (no skipped or repeated rows across pages): (published_at, created_at, id) for profile/tag, (created_at, id) for admin posts and admin users. Ids are UUIDv7, so they order consistently with the timestamps.
Opaque cursor in the URL. A single ?cursor=<base64url> query parameter encoding the key tuple. No signing (it only encodes public timestamps). An undecodable cursor is a malformed URL → 404. A well-formed cursor that points past the last row yields a normal empty page, not an error.
Page size: 20 for the public surfaces (profile, tag), 50 for the admin surfaces. Two named constants, not magic numbers.
Two-way navigation. Render "← Newer" and "Older →" links at the foot of the list. Determine "has older" by fetching limit + 1 rows and trimming the extra. "Has newer" is true whenever the request carried a cursor (i.e. you are not on the first page). The "Newer" direction runs the keyset comparison and ORDER BY reversed, then re-reverses the fetched rows for display; this stays hidden inside the repository/service.
rel="prev"/rel="next" on the public pages only (profile, tag), emitted in the document head from the same cursors: next = older, prev = newer, per the HTML pagination convention. Admin pages omit these.

Key interfaces:

Two new domain types: an opaque Cursor that owns its own base64url encode/decode (so the web layer never handles the key tuple directly, and decode failure is representable), and Page<T> { items: Vec<T>, older: Option<Cursor>, newer: Option<Cursor> }>, where each cursor field is Some exactly when that link should render. Keep Page<T> general-purpose — it should serve any keyset-paged listing, not just these four.
A Direction (Older / Newer) input.
The four listing port methods change from returning Vec<T> to taking cursor: Option<Cursor> + direction: Direction and returning Page<T>. The limit + 1 probe and the reverse-and-re-reverse for the Newer direction live behind the port, in the SQLite implementation. The web layer performs no DB access and no cursor-tuple handling — it passes the opaque cursor through and builds URLs from Page.older / Page.newer (architecture invariant: web reaches persistence only through domain services).
The relevant Services (Posts for profile/tag; the admin/setup surface for admin lists) thread cursor + direction through to the ports and hand Page<T> to the handlers.

Storage:

A new migration (never edit the deployed baseline) adding ascending composite indexes matching the keysets: posts(author_id, published_at, created_at, id), posts(created_at, id), users(created_at, id). Ascending is sufficient — SQLite scans them backward for the DESC pages. The tag query's json_each(tags) cannot be indexed; the posts(author_id, published_at, …) index still carries the author+order portion.

Documentation:

A short ADR recording the decision ("post/user listings are paginated with opaque keyset cursors").
Glossary entries for Cursor and Page added to CONTEXT.md, in the house style (definition + Avoid line).

Acceptance criteria:

Profile, tag, admin-posts, and admin-users pages return at most their page size (20 / 20 / 50 / 50) and render Newer/Older links reflecting availability.
Navigating Older then Newer returns to the original page with no rows skipped or duplicated, including across equal timestamps (the id tiebreaker is exercised).
The limit is enforced in SQL (queries carry LIMIT), not by post-fetch truncation.
An undecodable ?cursor= yields 404; a valid cursor past the end yields an empty page with a working "Newer" link and no "Older" link.
Profile and tag pages emit rel="prev"/rel="next" (next=older, prev=newer) when those pages exist; admin pages do not.
The pending list, dashboard, and feeds are unchanged.
A new migration adds the three composite indexes; the baseline migration is untouched.
Cursor and Page<T> live in the domain; the web crate does no DB access and no cursor-tuple decoding.
An ADR and CONTEXT.md Cursor/Page glossary entries are added.
format, ci, and clippy mise tasks pass.

Out of scope:

Paginating the profile pending list, the dashboard, or the feeds (#158).
Offset/numbered-page navigation or a total-count query.
Making page sizes configurable via env/config.
Any client-side JS ("load more"/infinite scroll).
Cursor signing/encryption.

> *This was generated by AI during triage.* ## Agent Brief Supersedes the earlier `ready-for-human` scope note above. Design settled via a grilling session; this brief is the contract. **Category:** enhancement **Summary:** Paginate the four unbounded post/user listing surfaces with opaque keyset cursors and two-way (Newer/Older) hyperlink navigation. **Current behavior:** The public profile "listed" feed, the tag listing, the admin posts list, and the admin users list each load their entire collection in one query (`ORDER BY … DESC`, no limit) and render every row. The backing port methods return unbounded `Vec<T>`. This grows without bound as content accumulates, against the "runs on a small box for years" goal. **Desired behavior:** Each of those four surfaces returns at most one page of rows, newest-first as today, with in-page links to the adjacent pages. Navigation is keyset (cursor)-based, not offset-based, and is expressed as plain hyperlinks (no client JS — the site has no JS framework and CSP is `script-src 'self'`). Settled design: - **Scope — exactly these four:** profile *listed* (public), tag listing (public), admin posts, admin users. **Explicitly unchanged:** the author-only *pending* list on the profile page stays unbounded (small, author-only; a dedicated pending page is a possible future follow-up); the dashboard (not a list); and the feeds (bounded separately in #158). - **Keyset cursor with a unique tiebreaker.** The cursor is the last row's full sort-key tuple, with the primary key appended as the final tiebreaker and added to `ORDER BY` so the order is strictly total (no skipped or repeated rows across pages): `(published_at, created_at, id)` for profile/tag, `(created_at, id)` for admin posts and admin users. Ids are UUIDv7, so they order consistently with the timestamps. - **Opaque cursor in the URL.** A single `?cursor=<base64url>` query parameter encoding the key tuple. No signing (it only encodes public timestamps). An **undecodable** cursor is a malformed URL → **404**. A well-formed cursor that points past the last row yields a normal **empty page**, not an error. - **Page size:** 20 for the public surfaces (profile, tag), 50 for the admin surfaces. Two named constants, not magic numbers. - **Two-way navigation.** Render "← Newer" and "Older →" links at the foot of the list. Determine "has older" by fetching `limit + 1` rows and trimming the extra. "Has newer" is true whenever the request carried a cursor (i.e. you are not on the first page). The "Newer" direction runs the keyset comparison and `ORDER BY` reversed, then re-reverses the fetched rows for display; this stays hidden inside the repository/service. - **`rel="prev"`/`rel="next"` on the public pages only** (profile, tag), emitted in the document head from the same cursors: `next` = older, `prev` = newer, per the HTML pagination convention. Admin pages omit these. **Key interfaces:** - Two new domain types: an **opaque `Cursor`** that owns its own base64url encode/decode (so the web layer never handles the key tuple directly, and decode failure is representable), and **`Page<T> { items: Vec<T>, older: Option<Cursor>, newer: Option<Cursor> }>`**, where each cursor field is `Some` exactly when that link should render. Keep `Page<T>` general-purpose — it should serve any keyset-paged listing, not just these four. - A `Direction` (Older / Newer) input. - The four listing port methods change from returning `Vec<T>` to taking `cursor: Option<Cursor>` + `direction: Direction` and returning `Page<T>`. The `limit + 1` probe and the reverse-and-re-reverse for the Newer direction live behind the port, in the SQLite implementation. The web layer performs no DB access and no cursor-tuple handling — it passes the opaque cursor through and builds URLs from `Page.older` / `Page.newer` (architecture invariant: `web` reaches persistence only through domain services). - The relevant Services (Posts for profile/tag; the admin/setup surface for admin lists) thread cursor + direction through to the ports and hand `Page<T>` to the handlers. **Storage:** - A **new** migration (never edit the deployed baseline) adding ascending composite indexes matching the keysets: `posts(author_id, published_at, created_at, id)`, `posts(created_at, id)`, `users(created_at, id)`. Ascending is sufficient — SQLite scans them backward for the DESC pages. The tag query's `json_each(tags)` cannot be indexed; the `posts(author_id, published_at, …)` index still carries the author+order portion. **Documentation:** - A short ADR recording the decision ("post/user listings are paginated with opaque keyset cursors"). - Glossary entries for **Cursor** and **Page** added to CONTEXT.md, in the house style (definition + _Avoid_ line). **Acceptance criteria:** - [ ] Profile, tag, admin-posts, and admin-users pages return at most their page size (20 / 20 / 50 / 50) and render Newer/Older links reflecting availability. - [ ] Navigating Older then Newer returns to the original page with no rows skipped or duplicated, including across equal timestamps (the `id` tiebreaker is exercised). - [ ] The limit is enforced in SQL (queries carry `LIMIT`), not by post-fetch truncation. - [ ] An undecodable `?cursor=` yields 404; a valid cursor past the end yields an empty page with a working "Newer" link and no "Older" link. - [ ] Profile and tag pages emit `rel="prev"`/`rel="next"` (next=older, prev=newer) when those pages exist; admin pages do not. - [ ] The pending list, dashboard, and feeds are unchanged. - [ ] A new migration adds the three composite indexes; the baseline migration is untouched. - [ ] `Cursor` and `Page<T>` live in the domain; the web crate does no DB access and no cursor-tuple decoding. - [ ] An ADR and CONTEXT.md `Cursor`/`Page` glossary entries are added. - [ ] `format`, `ci`, and `clippy` mise tasks pass. **Out of scope:** - Paginating the profile *pending* list, the dashboard, or the feeds (#158). - Offset/numbered-page navigation or a total-count query. - Making page sizes configurable via env/config. - Any client-side JS ("load more"/infinite scroll). - Cursor signing/encryption.

rosa referenced this issue from a commit

2026-08-09 18:03:46 +00:00

perf: bound RSS/Atom feeds to the most recent 20 posts

rosa added this to the v0.1 milestone

2026-08-12 03:35:49 +00:00