Keyset roster query builder and API

Part of epic Task 9387 "Operator credentials roster UX fixes at scale". PRD = Task 9385 and architecture = Task 9386, both settled. IDs AC-x.y and D0–D9 are the PRD's; A1–A22 are the architecture's. Everything this child needs is restated here. Base: `develop` already contains Task 9378: sort Created/Name/Type/Org via `_ROSTER_ORDER` in `apps/api/app/routes/credentials/reads.py`, and `RosterSummary` in `apps/web/src/pages/credentials/Credentials.tsx`. Build on that shape and never re-implement it. Original work item (verbatim): > Follow-up to Task 9378 (operator credentials page UX). That task shipped server-side sort (Created/Name/Type/Org), a "1–25 of N" count, and shows the tenant once per row. These items remain for an operator working across all tenants' credentials, up to millions of rows: > 1. **Secrets filter scales.** `?secrets=` decrypts every row in scope on each request (`apps/api/app/routes/credentials/reads.py::_secrets_filter`) and puts every id into an `IN` list (Postgres allows at most 65535). Store a "has stored secret" boolean when the credential is written, so the filter becomes an indexed predicate. > 2. **Paging without full counts.** Replace exact `COUNT(*)` + OFFSET with keyset (cursor) paging and a capped/estimated total ("10,000+"). Add indexes behind each sort order (`lower(name)`, `type`, org name). > 3. **Search that finds things at scale.** Add a pg_trgm index on name/description. For operators, also match org name/slug and owner email. > 4. **Owner picker as an async typeahead.** A native select cannot hold thousands of owners across tenants. Make it multi-select. > 5. **Tree mode beyond 200 rows.** Build tree mode from a server-side folder listing (folders with counts, children loaded on open) instead of one 200-row page. > 6. **Bulk actions.** Let operators select rows (per page, plus "all N matching") and then delete, change org or move them to a folder. > 7. **Health columns and filters.** Add last used, last test result, and expired/expiring/never used, each also available as a filter. > > Out of scope: the three items Task 9378 shipped; credential editor pages; runner credential resolution. > > Done means: > - Each item above is shipped or explicitly declined with a reason. > - With 1M credentials in a dev DB, the roster's first page with any filter or sort loads in under 1s (EXPLAIN shows an index scan). > - An operator can bulk-delete 500 matching credentials in one action. Shared rules: - D1: "operator" means a principal on the operator plane holding cross-tenant `credentials:view` (the arm of `_roster_scope` that is not `mine`, with no org narrowing). [op] marks behavior that applies to the operator only. - Everything else applies to every caller within their existing scope; no scope widens. - No secret or config value appears in any response, log, audit text or search match. ## This child (architecture C2): roster query builder, keyset API, indexed filters, operator search, explain script Covers the API side of items 1, 2, 3 and 7, plus multi-owner on the roster. This builder is the shared seam: the pager/search/health UI, owner typeahead, tree and bulk children all build on `roster.py`. Expect a large PR, and keep it to this scope. Approach (settled decisions): - **§4.2 — the builder** lives in `app/routes/credentials/roster.py`. - `@dataclass(frozen=True) class RosterFilters` with fields `type`, `q`, `owner_ids: tuple[UUID,...]`, `org_id`, `secrets: Literal["yes","no"]|None`, `health: Literal["expired","expiring","never_used","test_failed"]|None`, `mine: bool`. The filters are normalised and hashed into the cursor shape. - `roster_filters_from_query(...)`: the 422s move here from `_list_filters`. - `roster_where(session, principal, f) -> list[ColumnElement]`: scope (`_roster_scope`) plus filters, including the A11 pre-queries. - `capped_count(session, where, cap=ROSTER_COUNT_CAP) -> tuple[int,bool]`. - `keyset_page(session, where, sort, per, cursor) -> tuple[list[Credential], str|None]`. - `RosterFilterSet`: the pydantic wire mirror of `RosterFilters`, used as the bulk child's `match`. - **Item 1 — secrets filter.** `secrets=` becomes a predicate on `has_stored_secret`. `secrets=no` uses the partial index; `secrets=yes` is a plain filter on the sort index. Delete `_secrets_filter` and `_row_has_stored_secret` from reads.py; the rule now lives in `app/credentials/health.py`. - **§4.1 — precedence on `GET /api/credentials`.** New query params: `paging?: "keyset"`, `cursor?`, repeatable `owner_id`, `health?`. - `cursor` present → keyset path. - `cursor` with `page` ≠ 1 → 422 "cursor and page cannot be combined". - The cursor shape is sort + `per` + a hash of the normalised filters. A mismatched shape or an undecodable cursor → 422 "This page link is out of date. Reload the list." - `paging=keyset` without `cursor` → the first keyset page. - Neither → today's OFFSET path with today's exact `COUNT(*)`, unchanged; `total_capped` is always false there. - Response (additive): `items`, `total`, `total_capped`, `next_cursor`, `page`, `per`. - **A6 — next and previous pages.** Fetch `per + 1` rows; `next_cursor` is null on the last page. There is no server `prev_cursor`, which deviates from AC-2.1's wording. Prev is a client-held cursor stack in the UI child; the oracle (Next ×3 then Prev back gives the same rows) is unchanged. - **A7 — cursor codec.** `app/keyset.py::encode_cursor(shape, values)`. - **A8 — row-value comparison.** Add `keyset.row_after(terms, values)`, emitting `(a,b,c) > (:a,:b,:c)` when all terms share a direction. Use it for newest, oldest, name and type, so each page is one index range scan. The OR-chain `after_clause` stays for mixed directions. - **A9 — `org` sort as a LATERAL walk.** - Outer: `orgs` ordered by `(lower(name), id)`. - CROSS JOIN LATERAL: that org's credentials, filtered, ordered by `(lower(name), id)`, LIMIT per+1. - Then `ORDER BY lower(o.name), o.id, lower(c.name), c.id LIMIT per+1`. - A UNION ALL tail for a dangling `org_id` (there is no FK to `orgs`), sorted last. - Sort key: `(org_name, org_id, name, id)`. - The five sorts are the ones Task 9378 shipped (newest, oldest, name, type, org). Add no `dir` parameter. - **A10 — capped total.** `SELECT count(*) FROM (SELECT 1 … LIMIT 10001)`, returned as `total` (at most 10,000) plus `total_capped`. `ROSTER_COUNT_CAP` is a module constant that tests can patch. - **Item 3 / A11 — search.** - `q` matches `lower(name)`/`lower(description)` through the trigram indexes. Wildcards stay literal. - [op] Two uncapped pre-queries run first: org ids whose name or slug ILIKE the term, and `admin_users` ids whose email ILIKE it (both trigram-indexed). - They become `= ANY(:org_ids)` and `coalesce(owner_id, created_by_id) = ANY(:owner_ids)` arms of the same OR, so the planner builds a BitmapOr over four indexes. A bound array is one parameter, never an `IN` list. - Tenant callers get no org or email arms. Config is never searched. - **A12 — owners.** `owner_id` is repeatable, and the owners are a union. `created_by_id` stays as a legacy alias. - **Health filter (AC-7.2, API side).** Each value is served by its partial index. - `expired`: `expires_at <= now()`. - `expiring`: `expires_at` in `(now(), now()+7 days]` (D7). - `never_used`: `last_used_at IS NULL`. - `test_failed`: `last_test_ok = false`. - **A22 — overlap data revision.** An idempotent data revision re-derives `has_stored_secret` and `expires_at` for every credential row, in batches of 1,000 through the ORM. It uses the same rules and the same fail-closed handling as the foundation backfill. Prod holds thousands of rows, so a full re-derive takes seconds and needs no apply-time bookkeeping. This revision is this wave's only migration. - **R1 — rare filter + sort pairs.** If a pair still seq-scans, add the composite `(filter, sort key, id)` index for that pair inside this child's same revision file, and name it in the PR. - **§6 — explain script** `tools/perf/credentials_roster_explain.py`. - Imports the real statements from `roster.py`; no hand-written SQL. - Runs as the operator AND as a tenant Org Admin of the 300k hot tenant. - Matrix: {no filter, each filter (type, q 3-char, owner, rare owner, org, secrets yes/no, each health value)} × {newest, oldest, name, type, org}, plus the capped count. - Prints the plan root and `Execution Time`. FAILs on `Seq Scan on credentials` or > 1000 ms. - Later children add their cases. It runs in CI as an opt-in job only. Credentials & reach (architecture §9, verbatim; "C1's script" = the foundation child's seed script): - "Unit/API tests for every AC | Nothing beyond the worktree (`./dc test`). | verified — same." - "AC-P (1M rows, EXPLAIN < 1 s) | Nothing beyond the worktree: the slot's local compose DB (`./dc psql`), seeded by C1's script. | verified — needs only the worktree; the stack is started by the coder (`./dc up`)." Out of scope: - MUST NOT re-implement or change Task 9378's sort control, "1–25 of N" count format (other than the capped variant) or once-per-row tenant display. - MUST NOT change credential editor pages: `apps/web/src/pages/credentials/CredentialEditPage.tsx`, `AccountEditPage.tsx`, `credentialForm.tsx`, nor the Accounts page (`Accounts.tsx`). - MUST NOT change runner credential resolution: what `routes/agent/task_secrets.py` and the credential-get path return, who may call them, or path resolution (`runner_path`). Only the additive best-effort `last_used_at` stamp (D6) is allowed. - MUST NOT change the sealing scheme (`app/credential_crypto.py`) or put config into SQL predicates. - MUST NOT make health columns sortable, add "jump to page N", or add bulk verbs beyond delete / change org / move to folder. - MUST NOT change `CredentialSelect` or other `page`-based pickers' behavior. - Do NOT touch the web UI, the `/owners`, `/tree` or bulk endpoints, or the use/test stamps (sibling children). Do NOT add `prev_cursor` or a sort direction parameter. Done means (PRD criteria verbatim with their oracles; deviations marked): - **1.1** A `GET /api/credentials?secrets=yes|no` request opens no credential config (no tier-1 decrypt) and sends no `IN`/`NOT IN` id list. → verify: a unit test patches the tier-1 opener to raise and asserts the filtered list still returns 200 with correct rows; SQL captured by the test contains no `credentials.id IN`. - **1.4** `secrets=yes` ∪ `secrets=no` = unfiltered roster, disjoint, for every caller scope. → verify: test asserts counts add up. - **2.1** (A6 deviation: no `prev_cursor` field; Prev is the client stack) Roster requests carry `cursor`; responses carry `next_cursor` (null on the last page). Paging Next through a result and Prev back returns the same rows in the same order with no duplicates or gaps, for each sort (Created, Name, Type, Org), including ties (stable tiebreak on `id`). → verify: an API test walks 3 pages forward and back (replaying held cursors) per sort, over a fixture with deliberate ties. - **2.2 (API half)** Total: exact when ≤ 10,000; `total_capped: true` when > 10,000, and the count query stops at the cap (LIMIT-bounded subquery). → verify: API test with cap lowered via a test hook asserts the flag. - **2.3** A cursor from a different sort/filter is a 422 with an actionable message, never a wrong page. → verify: API test. - **2.6** Existing `page`-based callers (`CredentialSelect`, other pickers) keep working unchanged. → verify: their existing tests pass unmodified. - **3.1** `q` matches name or description by substring (case-insensitive), backed by a pg_trgm GIN index on each. Wildcards stay literal. → verify: AC-P with `q`; existing escaping tests pass. - **3.2 [op]** For the operator, `q` also matches the row's org name, org slug and owner email (owner = `coalesce(owner_id, created_by_id)`). For tenant callers it does NOT match owner email or org fields (no cross-member email probing beyond today). → verify: API test per caller kind: operator finds a row by an org slug substring and by an owner email substring; a tenant admin searching the same email gets no extra rows. - **3.3** Config is never searched. → verify: test with a host value in config returns no match. - **4.2 (API half)** API accepts `owner_id` repeated; single `owner_id` and legacy `created_by_id` keep working. → verify: API test with two ids. - **7.2 (API half)** Health filter values Expired, Expiring (7 days), Never used, Last test failed — server-side, indexed, applied to page and total alike. → verify: API test per value; AC-P. - **AC-P** With 1,000,000 credentials spread across ≥100 orgs and ≥5,000 owners in a slot DB (seed script committed, D9), the first page for: no filter; each single filter (type, q 3-char, owner, org, secrets yes/no, each health value); and each sort responds in < 1 s server time as the operator (and as the hot-tenant Org Admin), and `EXPLAIN (ANALYZE)` of the page query shows an index scan (or bitmap index scan for trigram `q`), no Seq Scan on `credentials`. Same for the capped count query. → verify: run `tools/perf/credentials_roster_explain.py` against the seeded slot DB. Every case must be under 1000 ms with no `Seq Scan on credentials`. Paste the output in the PR. - `./dc check quick` is green.

Keyset roster builder (roster.py): indexed secrets/health/owner filters, capped total, cursor paging for all 5 sorts, operator org/email search, and migration 202610240200. AC-P 120/120 PASS (≤400 ms). Review lane passed. Merged #6750 as 8c3bd8e; its deploy was superseded by a newer one. Deferred: opt-in CI job for the explain script. Memory: none applied.