Stamp credential last-used and test results

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`); 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. ## This child (architecture C1b): use and test stamps Covers item 7's stored data: when a credential was last used and its last test result. It needs the columns from the "Store credential roster facts and indexes" child. Approach (settled decisions): - **A4 — last-used stamp.** Add `app/credentials/health.py::record_use(session, ids)`, a set-based, throttled stamp: `UPDATE credentials SET last_used_at = now() WHERE id = ANY(:ids) AND (last_used_at IS NULL OR last_used_at < now() - interval '1 hour')` - Runs in a SAVEPOINT; exceptions are logged and swallowed. - Called ONLY from these node/runner fetch routes in `apps/api/app/routes/agent/task_secrets.py`: `get_task_git_token` (:185), `get_clone_git_token` (:266), `get_account_api_key` (:339), Bedrock (:414) and `get_task_credential` (:691). - NOT called from `credential_crypto.open_secret`. "Used" means a node or runner fetched the credential, not that the CP tested or refreshed it. - A Core UPDATE does not bump `updated_at`. - It runs after the secret is resolved and is one indexed UPDATE. It never holds a row lock across the response. - **A5 — test stamp.** `POST /api/credentials/test` stamps `last_test_ok`/`last_tested_at` only on the saved-row arm (`routes/credentials/connection_test.py:139-142`: an `id` with no sealed envelope). The create-form arm (:143-151) and the inline arm stamp nothing. - **R3 — write load.** At most one write per row per hour; the `WHERE` guard makes every other call a no-op. - **Security-adjacent (runner fetch path).** The autonomous review lane MUST include `security-reviewer`. It verifies that each fetch route's response, authz and path resolution are byte-for-byte unchanged, and that the only change is the additive stamp. Credentials & reach (architecture §9, verbatim): "Unit/API tests for every AC | Nothing beyond the worktree (`./dc test`). | verified — same." 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 roster read path, the UI, the migration or the config setter (sibling children). Done means (PRD criteria verbatim with their oracles): - **7.3** `last_used_at` is stamped when a node/runner fetches the credential's secret (credential get, git-token, account api-key fetches that read a `Credential`), at most one write per hour per row, never failing or slowing the fetch (best-effort). → verify: API test fetches twice within an hour → one stamp; a stamp failure still returns the secret. - Tightened: there is one test per wired route (all five). - The existing fetch-route tests pass unmodified, which proves response and authz did not change. - A test with `record_use` raising asserts the fetch returns the same body with status 200. - **7.4** `last_test_ok`/`last_tested_at` are stamped by `POST /api/credentials/test` for a saved credential; testing unsaved form values stamps nothing. → verify: API tests for three cases: a saved OK, a saved failure, and a create-form test that stamps nothing. - The `security-reviewer` verdict is recorded in the PR. `./dc check quick` is green.

Credential last_used_at stamp (5 runner fetch routes, ≤1/h, best-effort) + Test result stamp (saved row only); trigger narrowed so stamps aren't edits. Quick gate green, security/code/verify lanes pass. PR #6748 merged ec37e03; deploy superseded. Memory: proposed mem-24626.