perf: appforge.bases.list is N+1 (hydrates every base+table per call) and dashboard polls it every 5s

[webdevtodayjason]

Summary

appforge.bases.list is the single biggest non-agent load on the gateway right now. Two compounding issues:

1. N+1 query cascade inside the listBases adapter
2. Dashboard polls it every ~5 seconds with no cache or SSE invalidation

Result: 200+ calls in the last few minutes of ~/.argent/logs/gateway.log, slow ones at 6–8 seconds, fast ones at 200ms. During an agent turn, those 6-8s slow calls compete with the chat for gateway worker capacity and Postgres connections.

Evidence

Code path (src/infra/app-forge-store.ts:678)

async listBases(opts) {
  ...
  const rows = await sql`SELECT ... FROM appforge_bases WHERE app_id = ${opts.appId} ...`;
  return Promise.all(rows.map((row) => hydrateBase(sql, row)));
}

// hydrateBase (line 528):
async function hydrateBase(sql, row) {
  const tableRows = await listTableRows(sql, row.id);
  const tables = await Promise.all(
    tableRows.map((tableRow) => hydrateTable(sql, row.id, tableRow)),
  );
  return baseRowToBase(row, tables);
}

For 3 bases × 5 tables each: 1 + 3 + 15 = 19+ queries per call, and that's before hydrateTable fans out for fields/records/interfaces. Multiply by 200 polls in a few minutes and Postgres is doing thousands of queries serving the same answer.

Gateway log sample

appforge.bases.list 5971ms
appforge.bases.list 5879ms
appforge.bases.list 5881ms
appforge.bases.list 7089ms
appforge.bases.list 7093ms
appforge.bases.list 8504ms
appforge.bases.list 8510ms
appforge.bases.list  229ms
appforge.bases.list  206ms
appforge.bases.list  239ms
...(repeats every ~5s)

The fast ones are warm-cache hits at the Postgres / connection-pool level. The slow ones are cold or under contention.

Proposed fixes (rank-ordered by impact)

1. Single query instead of N+1 (biggest win)

Replace Promise.all(hydrateBase) with a single SQL join + JSON aggregation:

SELECT
  b.id, b.app_id, b.name, b.description, b.active_table_id, b.revision, b.updated_at,
  COALESCE(jsonb_agg(t.*) FILTER (WHERE t.id IS NOT NULL), '[]'::jsonb) AS tables
FROM appforge_bases b
LEFT JOIN appforge_tables t ON t.base_id = b.id
WHERE b.app_id = $1
GROUP BY b.id
ORDER BY b.updated_at DESC, b.id ASC;

One round-trip for everything listBases needs. Same shape after deserialization.

2. Server-side micro-cache with mutation-aware invalidation

Add an in-memory cache keyed by (appId) with a short TTL (5–15s) plus explicit invalidation on every appforge.bases.put/delete/update. The dashboard's 5s polling will hit the cache 99% of the time. Tradeoff: small staleness window after a mutation if the cache eviction misses.

3. Replace dashboard polling with SSE/WS subscription

Convert the dashboard's useForgeStructuredData hook from setInterval(fetch, 5000) to a subscription on a bases.changed event the gateway emits when AppForge state mutates. Zero load when nothing changes, immediate updates when it does.

4. Reduce the dashboard's poll rate as an immediate stopgap

If 1–3 are too much to ship together, just dropping the dashboard's poll cadence from 5s → 30s removes ~85% of the load tomorrow with one config bump. Not a real fix but a pressure relief.

Why this matters beyond AppForge

The 5-second AppForge polling steals gateway worker capacity from chat turns. Evidence (turn breakdown for a 13.9s web chat turn):

• Pre-session overhead: 10.4s
• TUI same agent (no AppForge polling): 5.5s overhead
• Delta attributable to dashboard background load: ~5s per turn

Half of every web chat turn's overhead is AppForge polling. Fixing this is the lowest-hanging perf win in the whole stack.

Acceptance criteria

• listBases runs ≤ 2 SQL queries total regardless of base/table count.
• Dashboard bases.list cache hit rate >90% under normal use.
• During an active chat turn, dashboard polling adds <500ms of contention (vs ~5s today).
• No regression: a fresh appforge.bases.put immediately reflects in subsequent bases.list calls (cache invalidates).

Related

• perf: chat turn 4x slower than equivalent subctl turn (13.5s vs 3s, same OAuth provider + tool use) #405 — 4x slowness vs subctl; this is one of the largest contributors.
• perf: ArgentSessionManager + 113-tool registry rebuilt every turn — cache across turns #406 — agent session rebuild per turn (separate problem).
• perf: 113-tool JSON-schema block (~17k tokens) sent on every turn regardless of intent #407 — 113-tool prompt block (separate problem).

🤖 Filed from a live perf investigation against the AppForge handler.