Analytics agent

How Analytics and Ask Kwilo keeps model-written SQL inside one institution's data. The agent runs in kwilo-chatbot; the webapp backend owns the data, the SQL and every check.

sequenceDiagram
  participant B as Browser
  participant API as Backend
  participant BOT as Chatbot
  participant DB as Postgres (analytics.* views)
  B->>API: POST /analytics/ask
  API->>BOT: question + catalog + signed scope token (10 min)
  BOT->>API: tool call (signed callback)
  API->>DB: guarded SELECT as reader role
  DB-->>API: rows for this tenant only
  API-->>BOT: preview rows
  BOT-->>API: answer + chart spec
  API-->>B: SSE: answer, chart, preview id (no SQL)
  B->>API: Add to tab (preview id)

Two walls

Database wall. Queries run as a dedicated read-only login that cannot bypass RLS, owns nothing and can SELECT only the analytics.* views. Policies read the tenant through analytics.current_org_unit_id(), which returns NULL unless an HMAC over the org unit and a ten-minute expiry verifies. A forged context reads zero rows.

Code wall. Every model SQL string is parsed with the Postgres grammar (pglast) and must be one allowlisted SELECT over catalogued views and columns. It may not name org_unit_id at all. Each tenant relation is rewritten into a fenced subselect (WHERE org_unit_id = $1), and for heads of department also department_id = ANY($n). Queries are plan-gated and run alone through the extended protocol. Failures return one generic message.

Contract

• The browser never sees SQL. It gets a preview id, answer, assumptions, a public description of the query, the chart spec and rows. Pinning resolves the preview server-side.
• Tenant comes from the token. Tool arguments are strict schemas; tenant, user and scope come from the scope token, never from the model.
• Tools: get_schema, sample_rows, lookup_values, run_query, run_metric_query, propose_chart, list_cards. Per turn: 12 tool calls, 3 run_query, 3 repairs.
• Catalog: one YAML file per domain under apps/backend/src/analytics/catalog/, describing every view and column. The backend refuses to start if a view lacks an entry, an entry names a missing column, exposes org_unit_id, or has an empty description.
• Metric registry: verified KPI definitions the dashboards use and the catalog cites, so agent answers match the tiles.
• Handles: execution ids, previews and threads live in Redis for ten minutes, bound to tenant and user. Without Redis the agent fails closed outside development.

Configuration

Values live in the team secret manager.

Service	Variable	Purpose
Backend	ANALYTICS_AGENT_ENABLED	Kill switch for /analytics/ask, default off
Backend	ANALYTICS_SCOPE_TOKEN_SECRET	Signs the tenant-bound scope token
Backend	ANALYTICS_READER_DSN	The restricted reader login, never the app owner
Backend	ANALYTICS_CTX_SIGNING_KEY	Matches the database's ctx.signing_key
Backend	ANALYTICS_QUERY_BUDGET_PER_HOUR	Per-institution execution allowance, default 60
Backend	ANALYTICS_AGENT_HANDLE_TTL_SECONDS	Handle TTL, default 600
Both	ANALYTICS_CALLBACK_S2S_SECRET, ANALYTICS_CALLBACK_S2S_KEY_ID	HMAC for chatbot → backend callbacks
Chatbot	ANALYTICS_CALLBACK_URL, ANALYTICS_CALLBACK_TIMEOUT_SECONDS	Backend origin and timeout
Chatbot	ANALYTICS_MODEL	Model for the analytics runner

Keep ANALYTICS_ALLOW_UNSIGNED_CONTEXT=false.

Operating it

• Enable: deploy backend with the flag off, deploy the chatbot with the same callback key, verify callbacks, then turn the flag on.
• Verify the wall: apps/backend/scripts/analytics_verify_wall.py after migrations. It is a manual gate, not a nightly job.
• Rotate the context key: analytics_rotate_signing_key.py --id <new> inserts a key; wait ten minutes before --delete-id on the old one.
• Rotate the callback key: flag off, deploy the new id and secret to both services, verify, flag on.
• Roll back: flag off first. Pinned cards keep running server-side.