Story 1598: bulk usage-counts — kill the worst row-scaled query fanouts

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). Several list/aggregate endpoints fetched a page of rows and then asked the database one more question per row — “how many things use this row?”. That is a classic N+1: a firm with 50 catalog rows pays ~50 extra round-trips, and the cost grows with the firm’s data. The matter-config bootstrap was the worst offender — it does this across FOUR catalogs in a single response, so its query count was 4 + Σ(N×refs). This Story replaces the per-row counts with grouped, page-wide counts so the query count is bounded by the number of reference tables (fixed), never the number of rows.

Scope & doctrine. Master plan n-plus-one-audit-implementation-plan.md — WS-3 (Step 1 contract + Step 2 worst sites) and WS-8 (O(1)-query tests), §D.3 per-domain notes. Builds on CHORE-A (pramaan-spec #1597): the shared db_query_count counter and session.info foundation. 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 boto3/SQLAlchemy T1 auto-instrumentation under the existing request span).

🛰️ Satellite: ask once for the whole page, not once per row

BEFORE  (forbidden N+1)                     AFTER  (bounded, this Story)
  SELECT page of catalog rows  ── 1          SELECT page of catalog rows   ── 1
  for row in page:                           usage = GROUP BY subject_col
      SELECT count(*) WHERE id = row ── N         WHERE id IN (page ids)    ── k tables
  ─────────────────────────────                  → dict{id: count}, zip in memory
  total = 1 + N  (grows with rows)           ─────────────────────────────
                                             total = 1 + k  (k = #ref tables, FIXED)

The antidote already existed in the codebase (MatterService._party_counts): one grouped IN query → dict keyed by id → zip into the response. This Story generalizes it into a shared contract and applies it to the four worst sites.

✈️ Airplane: the change surface

File	Role
`src/shared/lifecycle.py`	New contract. `BulkInUseReference(entity, subject_column, predicates)` + `bulk_count_in_use_references(session, *, firm_id, references, target_ids) → dict[UUID,int]`. Loops reference TABLES (fixed), one `GROUP BY subject_column` filtered `subject_column.in_(target_ids)` per table, SUMS across tables. The helper enforces `entity.firm_id == firm_id` itself, so the firm predicate (V4) can never be forgotten.
`src/app_api/matter_config_bootstrap_service.py`	The 4-catalog bootstrap: per-row `_usage_count` calls replaced by one `_bulk_usage_counts` per catalog. `4 + Σ(N×refs)` → `4 + 7` grouped.
`src/domains/{forum,filing_type,proceeding_type,participant_label}/service.py`	Each gains `_bulk_in_use_references()` + `_bulk_usage_counts(...)` (bulk twins of the scalar `_in_use_references`/`_usage_count`, which stay for single-row delete guards). `list_participant_labels` converted (1+2N → 1+2).
`src/domains/matter_preset/template_service.py`	`list_matter_templates`: 1+4N → 1+4. New `_bulk_counts` issues 4 grouped queries (matter usage + 3 default-link tables); `_list_item` is now a pure builder taking precomputed counts; `_detail_view` reuses `_bulk_counts` for one id.
`src/domains/matter/identity_service.py`	`list_affiliations`: ~1+2N → 1+1. New `_entities_by_id` loads every affiliation’s person + organization in ONE firm-scoped `IN` query; views built from the dict.
`tests/integration/test_story_1598_bulk_counts.py` (new)	The auto-merge guard: golden-response tests (exact counts, full DTO round-trip) for all four endpoints + O(1)-query tests (1 row vs 5 → identical `db_query_count`) for bootstrap + participant-labels.
`AGENTS.md` ×7	§D.3 `## Query Shape` notes (app_api, matter_preset, participant_label, matter, forum, filing_type, proceeding_type) citing pramaan-docs ADR-130.

🔬 Microscopic: the bulk contract, and why it SUMs (not DISTINCTs)

The existing scalar path (count_in_use_references) sums count(*) across each reference table for a single row. A catalog row referenced in two tables therefore counts twice today. The bulk path must preserve that number exactly — performance work must not silently redefine usage_count (Codex #7). So the loader SUMs per-table grouped counts into the dict; it deliberately does NOT COUNT(DISTINCT) or join the tables.

async def bulk_count_in_use_references(session, *, firm_id, references, target_ids):
    counts = {tid: 0 for tid in target_ids}
    if not target_ids:
        return counts                       # empty first-run firm → no query, []
    for reference in references:            # loop TABLES (fixed), never rows
        rows = await session.execute(
            select(reference.subject_column, func.count())
            .where(reference.entity.firm_id == firm_id)   # V4 enforced HERE
            .where(*reference.predicates)                  # archived/live, verbatim
            .where(reference.subject_column.in_(target_ids))
            .group_by(reference.subject_column))
        for subject_id, usage in rows.all():
            counts[subject_id] += int(usage)               # SUM across tables
    return counts

Invariants held. V3 — response body shape and ordering byte-identical: the bulk dict is keyed by id, and ordering still comes from the page query, so views are built in the same order with the same numbers. V4 — the firm_id predicate is on every grouped count (structurally guaranteed by the helper). V6 — no custody/audit rows touched; this is read-path enrichment only. The archived/live/deleted filters are the verbatim _in_use_references predicates.

Auto-merge guard — both present.

Golden-response (test_story_1598_bulk_counts.py): each converted endpoint asserts a full DTO round-trip (Model.model_validate(body).model_dump(by_alias=True) == body) plus EXACT usage/link counts on a seeded fixture — including a participant label referenced in BOTH tables asserting usageCount == 2 (the summed semantics), and the affiliations test asserting each affiliation maps its correct person/organization (the batch-dict lookup cannot swap them).
O(1)-query: bootstrap and participant-label list each assert two requests over firms with 1 vs 5 rows report an identical db_query_count on the WS-7 completion log line.

System boundary

Every test drives the real ASGI app (middleware → RLS dep → service → DB) against a freshly-provisioned firm. The empty first-run state (new firm, no catalog rows → [], no crash) is preserved: target_ids empty short-circuits before any grouped query, and the existing empty-bootstrap / empty-participant-label tests stay green.

Recall in one sentence: count the whole page in one grouped query per reference table — never one query per row — and the query count stops growing with the firm’s data.