Story 1599: catalog list endpoints — finish the 1+N → 1+k sweep
Why this exists (the catastrophe it prevents). Every PRAMAAN
request runs inside a Lambda talking to RDS over the network, so each SQL
statement is a round-trip and latency is round-trip accumulation, not
slow SQL (pramaan-docs ADR-130). Story-1598 (B1) shipped the shared
bulk_count_in_use_references contract and fixed the worst
fanouts (the matter-config bootstrap + participant labels). But the
standalone settings catalogs — the screens a firm opens to manage
clients, document types, filing types, forums, matter types, proceeding types,
and firm roles — were still asking the database one more question per
row: “how many things use this row?”. A firm with 50 forums
paid ~50 extra round-trips on every list load, and the cost grew with the
firm’s data. This Story converts the last seven list endpoints to a
grouped, page-wide count so the query count is bounded by the
number of reference tables (fixed), never the number of rows.
n-plus-one-audit-implementation-plan.md — WS-3
remaining catalog LIST sites + WS-8 (O(1)-query tests), §D.3
per-domain notes. Builds directly on B1 (pramaan-spec #1598): the shared
BulkInUseReference / bulk_count_in_use_references and
the _bulk_usage_counts twins already on forum / filing_type /
proceeding_type — this Story reuses them and wires the
list endpoints to consume them. No DTO / OpenAPI change —
response bodies are byte-identical, so make openapi shows no diff.
No new observability span (ADR-124: the grouped queries ride
SQLAlchemy T1 auto-instrumentation under the existing request span).🛰️ Satellite: ask once for the whole page, not once per row
BEFORE (forbidden N+1, HR-19 / DR-1) AFTER (bounded, this Story)
SELECT page of catalog rows ── 1 SELECT page of catalog rows ── 1
for row in page: counts = GROUP BY subject_col
count(refs) WHERE id = row ── N×k WHERE id IN (page ids) ── k tables
───────────────────────────── → dict{id: count}, zip in memory
total = 1 + N×k (grows with rows) ─────────────────────────────
total = O(1) + k (k = #ref tables, FIXED)
One sustained analogy: a teacher taking attendance. The slow way walks to
each desk and asks “are you here?” (one trip per student). The fast
way reads the whole room once and ticks a list. The grouped COUNT ...
GROUP BY subject_column WHERE subject_column IN (page ids) reads the
whole room once, per reference table.
✈️ Airplane: the seven endpoints and their reference tables
| Endpoint | Reference table(s) counted | Before | After |
|---|---|---|---|
GET /app/clients | matters | 1 + N | 1 + 1 |
GET /app/document-types | documents (incl. soft-deleted) | 1 + N | O(1) + 1 |
GET /app/filing-types | matter_template_filing_types, proceeding_filing_types | 1 + 2N | O(1) + 2 |
GET /app/forums | proceedings | 1 + N | O(1) + 1 |
GET /app/matter-types | matters, matter_templates | 1 + 2N | O(1) + 2 |
GET /app/proceeding-types | matter_template_proceeding_types, matter_proceeding_types | 1 + 2N | O(1) + 2 |
GET /app/firm/roles | firm_memberships (member count) | 1 + N | O(1) + 1 |
The constant k is the count of reference tables for
that catalog — bulk_count_in_use_references loops over tables,
never over rows. firm_role is the one shape that does not use the
shared in-use helper: its member count is a plain display count (not a
delete-guard), so it gets a domain-specific grouped loader
_bulk_member_counts. Its active_count (the
can_delete driver) was already O(1) — the page length.
🔬 Microscopic: the conversion, and the invariants it must not break
# forum/service.py — list_forums, AFTER
forums = result.scalars().all()
usage_counts = await cls._bulk_usage_counts( # 1 grouped query / ref table
session, firm_id=firm_id, forum_ids=[f.id for f in forums],
)
return ForumListResponse(
forums=[cls._view(f, usage_count=usage_counts[f.id]) for f in forums]
)
Preserve, do not regress:
| Invariant | How it is held |
|---|---|
| V3 — body shape + order byte-identical | The page query is unchanged; the bulk dict is keyed by id and zipped in the same order. Golden tests assert model_validate(body).model_dump() == body. |
| V4 — firm_id predicate on every count | bulk_count_in_use_references applies entity.firm_id == firm_id itself, so the firm filter can never be forgotten. |
Summed semantics (NOT COUNT(DISTINCT)) | Counts are summed across reference tables — a matter type used by a matter AND a template counts twice. Locked by the matter_type/filing_type/proceeding_type summed-count goldens. |
| document_type soft-delete inclusion (§6) | The bulk BulkInUseReference carries predicates=(), matching the scalar predicate VERBATIM: a referencing document with availability='deleted' STILL counts. A dedicated golden seeds 1 live + 1 deleted document and asserts usageCount == 2. |
| Single-row delete guard unchanged | The scalar _usage_count / _member_count stays for create/update/delete paths; only the LIST path moved to bulk. |
tests/integration/test_story_1599_catalog_list_bulk_counts.py:
- Golden-response ✓ — one per converted endpoint (7),
full DTO round-trip + exact counts + ordering + zero-row inclusion;
document_typegolden exercises the soft-delete inclusion. - O(1)-query ✓ —
document_type(required),client, andmatter_type: 1 row vs 5 rows yield an identicaldb_query_countoff the WS-7 counter.
[] on a freshly
provisioned firm (system-boundary, not component-boundary).Verify
make test-db && make test # 11 new guards + 41 existing domain/B1 tests green uv run ruff check # changed files clean make openapi # NO diff (no contract change) git diff --exit-code -- openapi/*.json
One-sentence recall: a catalog list must answer “how many use each row?” with one grouped question per reference table, never one question per row — and the answer must be byte-for-byte what the per-row path gave, soft-deletes and summed double-counts included.