Server-side lazy credential folder tree

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; 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`. [op] marks behavior that applies to the operator only. - No scope widens. Every query is built from `roster.py` (`roster_where`), so tree counts can never disagree with the roster. - No secret or config value appears in any response. - UI copy follows `docs/standards/ui-copy-standards.md`; layout follows `docs/standards/ui-style-guide.md`. ## This child (architecture C5): server tree, lazy at every level Covers item 5. It needs: - the roster builder from the "Keyset roster query builder and API" child: `roster.py`, `RosterFilters`, and the keyset codec `app/keyset.py`; - the `home_key`/`parent_path` generated columns and the tree indexes from the foundation child: `ix_credentials_home_parent_pat`, `ix_credentials_home_parent_lname_id`, `ix_credentials_home_key`. Approach (settled decisions): - **§4.3 — endpoint** `GET /api/credentials/tree`. - Params: `level: "tenants"|"homes"|"folder"`; `org_id` for `homes`; for `folder`, `home: <home_key>` + `path` (lowercase folder path, `''` at the home root, no trailing `/`); `cursor?`; and every roster filter. - Response by level, each with `next_cursor`: - `tenants`: `{"tenants":[{org_id,name,count,count_capped}]}` - `homes`: `{"homes":[{key,kind:"org|system|project|user",label,count,count_capped}]}` - `folder`: `{"folders":[{name,count,count_capped}],"leaves":[CredentialOut]}` - A tenant caller's `level=tenants` → 422; its root is `homes` for its own org. - **A14 — levels.** - **tenants** [op]: orgs holding at least one in-scope row, ordered `lower(name), id`, `EXISTS` via `ix_credentials_org_created_id`, paged 50. - **homes**: distinct `home_key` by skip-scan, paged 100. - **folder**: distinct next segments under `path`, by skip-scan over `(home_key, parent_path text_pattern_ops)`, paged 100. Leaves are the rows with `parent_path = path`, keyset-paged by `(home_key, parent_path, lower(name), id)` with the roster cursor codec. - **Skip-scan** is a recursive CTE walking in byte order. - `B` = the level base (`''`, else `:p||'/'`); `S` = `B||prev_seg`. - Each step takes the smaller of two `LIMIT 1` probes inside `parent_path LIKE B||'%'` (plus `parent_path <> ''` at the root): - (a) the first key strictly between `S` and `S||'/'`, which catches siblings such as `a-b` after `a`; - (b) the first key `>= S||'0'`, which skips past every descendant of `prev_seg`. - The next segment is `split_part(substr(key, length(B)+1), '/', 1)`. - **Counts** are per node and capped with `LIMIT 1001`, shown as "1,000+". This deviates from AC-5.3's 10,000 cap: 100 folders × 10,000 would break the 1 s bound. The roster total keeps its 10,000 cap. - **Filtered tree.** First take a capped count of the filtered rows under the level (`LIMIT 10001`). - ≤ 10,000 matches (sparse): folders and counts come from a GROUP BY over exactly those rows, so the result is exact. - > 10,000 matches (dense): run the unfiltered skip-scan for candidate folders, then a filtered capped count per folder, and hide folders whose count is 0. - Either way, no folder with matches goes missing and none with zero matches is shown. - **R9.** If a dense filtered case fails the explain budget, add a composite `(filter column, home_key, parent_path)` index. That would be this wave's only schema migration; no sibling in this wave adds one. - **UI.** - Tree mode loads children on open: tenant → home → folder for the operator; home → folder for tenants. - Drop the single `PICKER_PAGE` = 200 fetch (`apps/web/src/pages/credentials/api.ts`, `Credentials.tsx`) and the truncation notice. - Keep `view.expanded` persistence, and silently drop expanded keys that no longer exist. - Folder names render in full (wrap, or ellipsize with a tooltip). Counts are right-aligned. - Show "Load more" wherever a level has a `next_cursor`. - **Explain cases.** Add the tree cases to `tools/perf/credentials_roster_explain.py`: tenants; homes; a folder with 10k leaves and 200 subfolders. Run each also with `q`, a rare owner and `health=test_failed` on the hot tenant. - **Reader docs.** Add a tree paragraph to `apps/docs/content/console-reference/directory/credentials.md`. 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 flat-mode paging/search/health UI, the owner picker or bulk actions (sibling children). Exact tree counts above 1,000 are deferred. Done means (PRD criteria verbatim with their oracles; the A14 deviation is marked): - **5.1** Tree mode no longer fetches one 200-row page. A server endpoint lists, for a given home + folder path under the current filters, the child folders with their row counts and the leaf credentials of that level (leaves paged with the same keyset cursor). → verify: API test with a folder of 1,000 leaves and nested folders; web test opens a folder and sees its children fetched on open. - **5.2** Top level lists homes (org shared / project / personal / system, grouped per tenant for the operator) with counts; nothing is loaded for a closed folder. → verify: web test asserts no child request until a folder is expanded. - **5.3** Folder counts honor every active filter and equal the number of leaves reachable beneath (**capped at 1,000 per node, shown "1,000+"** — A14 deviation from 10,000). - → verify: an API test sums children against the count, in both the sparse and the dense filtered branch, with the cap patched low. - A property test with folder names containing `-`, `.`, `/` and Unicode asserts the skip-scan lists exactly the distinct folders a full GROUP BY finds. - **5.4** Expanded folders persist across reload as today (`view.expanded`); a folder that no longer exists is dropped silently. → verify: web test. - **5.5** The "showing first 200 rows" truncation notice is gone. → verify: grep + web test. - **5.6** Folder names render in full (wrap or ellipsize with a tooltip; never truncated to a few characters) and counts are right-aligned and legible. → verify: e2e screenshots at desktop and at the narrowest supported width, attached to the PR. The acceptance child repeats this visual check on dev. - **AC-P (tree)** The tree cases in the explain script run in under 1000 ms with no `Seq Scan on credentials` on the 1M seed. The output goes in the PR. - `./dc check quick` is green.