indexes list (unscoped) misses managed-database indexes

[eddietejeda]

Summary

Whole-workspace hotdata indexes list (no --connection-id) returns "No indexes found" for indexes on managed databases, even though indexes list --connection-id <db-scoped-conn> shows them. The connection-scoped path was fixed in #164 (#161); the unscoped path has the same root cause and was left as a gap.

Repro

hotdata databases set <managed-db>
hotdata indexes create --catalog <db-name> --schema public --table <t> --column <c> --type bm25 --name probe
hotdata indexes list --connection-id <db.default_connection_id>   # shows the index ✅
hotdata indexes list                                              # "No indexes found" ❌

Verified against production (workspace AgentRyan, sf_airbnb / nyc_taxi managed DBs).

Root cause

In the unscoped branch of collect_connection_wide (src/indexes.rs), each table's connection id is resolved by mapping the information_schema connection field through connection_lookup (= connections list). For a managed database that field is an internal label __db_<dbid-suffix> (e.g. db dbidijbxfx… → __db_ijbxfx…). connections list hides database-scoped connections, so the label never resolves; scan_connection_id falls back to using the label itself as the id, the per-table call 404s, and none_if_404 swallows it to an empty result.

The SDK's TableInfo carries only connection/schema/table — no real connection id or database id — so the label can't be resolved from information_schema alone. The databases list summary also omits default_connection_id; only databases get has it.

Proposed fix (stopgap, CLI-side)

In the unscoped branch, after collect_tables, resolve the distinct __db_* labels that actually appear and enrich conn_ids before the parallel per-table fetch:

let mut conn_ids = connection_lookup(api);
let db_labels: BTreeSet<&str> = tables.iter()
    .map(|t| t.connection.as_str())
    .filter(|c| c.starts_with("__db_") && !conn_ids.contains_key(*c))
    .collect();
let resolved: Vec<(String, String)> = db_labels.par_iter().filter_map(|label| {
    let db_id = format!("dbid{}", label.strip_prefix("__db_")?);
    let db = none_if_404(databases::get_database(api, &db_id)).ok()??;
    Some((label.to_string(), db.default_connection_id))
}).collect();
conn_ids.extend(resolved);

Confined to the unscoped branch (the --connection-id path and search are untouched). Resolves only labels present in the scan (zero extra calls when no managed-db tables exist), parallelized and deduped per distinct label.

Caveats

• Couples to the dbid ↔ __db_ encoding convention — unavoidable CLI-side given the current API surface. If the server changes it, behavior degrades to today's silent-empty (no regression).
• One databases get per distinct managed-db catalog with tables, run in parallel.

The durable fix is server-side — see hotdata-dev/www.hotdata.dev (linked below).

Test plan

• Unit: extend the collect_connection_wide mockito test with a managed-db row (__db_* label), mock databases get dbid<suffix> → default_connection_id, mock the per-table indexes endpoint only at the real id; assert the index resolves.
• Pure-fn unit for the __db_x ↔ dbidx transform incl. the strip_prefix miss.
• E2E: create index on a managed DB → unscoped indexes list shows it → delete.

Refs #161, #164.

[eddietejeda]

Server-side root-cause issue (durable fix): hotdata-dev/www.hotdata.dev#234

[eddietejeda]

Root-cause correction (verified against production)

The proposed CLI-side fix doesn't actually resolve this — and the stated root cause is incorrect. I built the CLI and tested against production (workspace AgentRyan, the managed DBs referenced here).

What the proposal assumes: in the unscoped branch, information_schema returns managed-DB tables under __db_* labels that simply fail to resolve to a connection id.

What actually happens: the workspace-wide information_schema call (connection_id = None) returns zero tables. Instrumented locally:

DEBUG collect_tables -> 0 tables: []

The __db_* labels never appear in the unscoped enumeration at all — they only show up in the connection-scoped enumeration (which is likely where they were observed; that path was fixed in #164). This workspace also has no regular connections (connections list → "No connections found", since it hides DB-scoped ones), so the unscoped scan has nothing to iterate over → "No indexes found."

So the label __db_<suffix> → dbid<suffix> resolution is a no-op: there's no row to resolve. The real gap is discovery: the unscoped path can't find managed databases at all.

The actual fix (#170)

In the unscoped branch, discover managed databases separately:

1. databases list → the managed DBs
2. resolve each default_connection_id via databases get (parallel)
3. scan each connection scoped, exactly like the --connection-id path
4. union with the regular-connection enumeration (the two sets are disjoint)

Verified against production:

• Before: hotdata indexes list → No indexes found.
• After: lists all 4 managed-DB indexes.

The server-side durable fix (hotdata-dev/www.hotdata.dev#234) is still the right long-term answer — ideally the workspace-wide information_schema (or a dedicated index-list endpoint) would include managed-DB catalogs, letting the CLI drop the per-database fan-out.

Fix in #170.