Schema Indexing

Every project builds a persistent, searchable schema index before its first discovery run. The index lets discovery work on warehouses of any size (ERPs with 2K+ tables) without dumping every table's columns into every prompt.

How the index is used:

• Discovery — the agent receives a compact Level-0 catalog up front (one line per table) and pulls per-table column lists + sample rows on demand via the lookup_schema and search_tables actions during exploration. search_tables is the path that actually queries the Qdrant collection. See On-Demand Schema.
• /ask — gates on the schema index being ready (so we know discovery can run) but performs its own retrieval against the insights/recommendations vector store, not the schema collection.

What gets indexed

For every table in the project's configured datasets:

• A 2-4 sentence natural-language description ("blurb") generated by the project's blurb_llm model, grounded in column metadata and sample rows — no hallucinations.
• An embedding of the blurb (via the project's embedding provider).
• A compact payload carrying dataset, table name, row count, column count, domain-pack keywords, and the model IDs used (for audit).

All points live in a per-project Qdrant collection named decisionbox_schema_{project_id}. Collections are dropped and rebuilt on every user-triggered re-index.

Default models

Defaults were validated against a real 2K-table ERP warehouse:

• Blurb model: the project's analysis LLM unless a project-specific blurb LLM is configured in Settings → Schema Index. (Earlier installs defaulted to bedrock/qwen.qwen3-32b-v1:0 — perfect MRR on curated queries, ~$0.004 / 50 tables, 830 ms/table — which remains a strong opt-in choice.)
• Embedding model: openai/text-embedding-3-large — top R@5 across every combination tested; Turkish and English queries are equivalent.

Fallbacks by preference:

Blurb model	Cost/50 tables	Latency (avg)	Quality (R@5)
Qwen3-32B (Bedrock)	~$0.004	830 ms	0.897
GPT-4.1-nano (OpenAI)	~$0.003	1.7 s	0.795
Claude Haiku 4.5 (Anthropic)	~$0.045	3.5 s	0.750

Reasoning-class models (DeepSeek R1, o1/o3/o4-mini, Claude extended-thinking modes) are rejected at save time — their output channel doesn't carry user-visible text through Converse/Chat, which produced empty blurbs in every spike combo.

Lifecycle

Six states on project.schema_index_status:

pending_indexing ──┬─> indexing ──┬─> ready          ── Run discovery works
                   │              ├─> failed         ── Retry indexing button
                   │              └─> cancelled      ── User stopped an in-flight run
                   ├── (user clicks Re-index at any time)
                   └── needs_reindex                 ── Set when warehouse / dataset config drifts

• pending_indexing — project needs indexing; worker hasn't claimed it yet. This is the state right after project create (with a warehouse configured), after a manual retry, or after a re-index.
• indexing — worker is running. Progress observable via GET /api/v1/projects/{id}/schema-index/status.
• ready — collection populated; discovery + /ask work.
• failed — last attempt failed. Error surfaced in the dashboard; user clicks Retry indexing → back to pending_indexing.
• cancelled — the user stopped the in-flight indexer. Partial progress is discarded; user clicks Retry to start over.
• needs_reindex — the platform detected a drift (warehouse swap, dataset list change) that invalidates the current index. The dashboard surfaces a banner; user clicks Re-index to rebuild.

Triggering a re-index

Re-indexing is user-triggered only. None of these auto-reindex:

• Warehouse config change
• Dataset / filter change
• Embedding-model swap
• Blurb-model swap

Why: an auto-reindex on every save would drain credits for users tweaking config. The dashboard surfaces a "schema may be stale" nudge after edits that plausibly invalidate the index.

# Re-index a specific project (drops and rebuilds).
curl -X POST http://localhost:8080/api/v1/projects/{id}/reindex

# Retry after a failure (only valid from `failed`).
curl -X POST http://localhost:8080/api/v1/projects/{id}/schema-index/retry

# Poll current status (dashboard uses this every 2 s).
curl http://localhost:8080/api/v1/projects/{id}/schema-index/status

Per-datasource run history

schema_index_status is project-level and live — it is reset at the start of every run, so it tells you only about the run in flight or the last one's lifecycle state. Indexing, however, runs per datasource: a project can have more than one warehouse, and each is indexed independently.

So every time a datasource finishes indexing — success or failure — the agent stamps a durable result record into the project_schema_index_runs collection, one document per (datasource × run). Unlike the live status doc, these records are never reset: they are the audit trail of what was indexed, from which datasource, when, and whether it succeeded.

Each record carries the datasource id + name, the run id, the object kind (tables today — kept generic so other object kinds slot in without a schema change), the number of objects indexed and blurbs generated, the blurb-LLM token totals, per-phase durations, the ready / failed status with the error on failure, and the start / finish timestamps.

Read the history — newest finished first, optionally filtered to one datasource:

# All datasources' runs for a project.
curl http://localhost:8080/api/v1/projects/{id}/schema-index/runs

# Just one datasource, most recent 20.
curl "http://localhost:8080/api/v1/projects/{id}/schema-index/runs?datasource_id=wh_redshift&limit=20"

The dashboard renders this in two places: a per-datasource "Indexing" section with an expandable history table on the Data Warehouse settings panel, and a persistent per-datasource status roll-up on the project page (so a successful re-index leaves a visible record, not just a toast).

Editing the indexed schema

Indexing is automatic, but the result isn't always perfect: a generated blurb can be off, a table can be irrelevant, or a column may be noise (or sensitive) you don't want the agent to consider. The schema editor lets you hand-correct the index. It's an advanced tool, reached from an understated "Advanced: edit indexed schema" link on the Data Warehouse settings panel (/projects/{id}/settings/schema-editor).

You can:

• Rewrite a table's blurb — the new text is re-embedded and the table's Qdrant vector is replaced in place, so semantic search reflects the edit immediately.
• Edit a table's keywords — the sparse-rerank terms; a payload-only change, no re-embedding.
• Remove columns — dropped from the Mongo schema cache (and the derived key/metric/dimension lists) so the discovery agent stops seeing them. Removal only; you can't add or retype a column.
• Remove a whole table — drops both its cache row and its Qdrant blurb point.

Reads are available to any viewer; edits and deletions require member.

Edits take effect immediately, and are recorded

Manual edits are applied to the live stores, so they take effect immediately — discovery and Ask read table columns straight from project_schema_cache, and retrieval reads blurbs from Qdrant.

Edits are ephemeral: the next re-index rebuilds the schema from the warehouse as it is now. A re-index drops the schema cache and re-discovers the catalog (rather than reusing the cached one), then regenerates blurbs and embeddings — so every manual edit, of any kind (blurb, keywords, removed columns, deleted tables), is discarded. This is intentional: it keeps a re-index meaning "reflect the real warehouse," which also picks up schema drift (added / dropped tables and columns) that an unchanged warehouse config would otherwise hide. Clear schema cache (Settings → Advanced) discards the same edits — it deletes the cache + Qdrant data and marks the project needs_reindex — but it does not contact the warehouse itself; the re-discovery happens on the next re-index.

There is deliberately no durable overrides layer: a re-index always returns to the warehouse's real schema. To make that non-destructive, every manual edit is written to a durable, append-only audit trail (project_schema_edits) capturing the table, action, before/after values, who made it, and when. The editor's "Edit history" panel shows this trail so you can review and re-apply your changes after a rebuild. Before a re-index or cache clear, the dashboard warns you when there are manual edits at risk — with the history a click away — so you never discard curation work unaware. GET /schema-editor/edits returns a single counter, since_last_index: the number of manual edits (all types) made since the latest indexing run — what a re-index or a Clear schema cache will discard, because both re-discover the schema. It is scoped to datasource_id when one is supplied.

# Browse a datasource's indexed tables (structure + blurb + keywords).
curl "http://localhost:8080/api/v1/projects/{id}/schema-editor/tables?datasource_id=wh_redshift&search=orders"

# Rewrite a blurb (re-embeds) — member+.
curl -X PUT "http://localhost:8080/api/v1/projects/{id}/schema-editor/tables?table=public.orders" \
  -H 'Content-Type: application/json' -d '{"blurb":"One row per customer order."}'

# The manual-edit audit trail + count since the last index.
curl "http://localhost:8080/api/v1/projects/{id}/schema-editor/edits"

When does an embedding-model change force a rebuild?

Always. Qdrant collections are bound to a fixed vector dimension, so swapping embedding models (e.g. text-embedding-3-small → text-embedding-3-large, 1536 → 3072 dims) means the collection is incompatible. POST /reindex drops and recreates it automatically.

How the dimension is resolved

The collection is sized from the embedding model's vector dimension, resolved in this order: an explicit dimensions value in the project's embedding config (an escape hatch for a model the provider catalog doesn't know — e.g. a managed gateway alias), then the provider's known dimension for catalogued models, and otherwise a probe — the agent embeds one short string and measures the returned vector. The probe means any provider/model indexes correctly, including an OpenAI-compatible gateway alias whose dimension the catalog can't know up front.

Cost and wall-clock envelope

With the defaults (Bedrock Qwen3-32B + OpenAI text-embedding-3-large) and 8 parallel blurb workers (BLURB_WORKERS=8):

Warehouse size	Blurb cost	Embed cost	Wall-clock
Small (40 tables)	~$0.01	~$0.0004	~30 s
Medium (500)	~$0.15	~$0.005	~2 min
Large (2K)	~$0.60	~$0.02	~6 min
Huge (10K)	~$3.00	~$0.10	~30 min

Override worker count for faster rebuilds on big warehouses:

export BLURB_WORKERS=16

(Mind Bedrock / OpenAI rate limits — increasing past ~16 workers usually trips them on a single account.)

Failure modes

Indexing runs are all-or-nothing: on failure the collection is left dropped and the next user-triggered retry starts from a clean slate. Partial progress is thrown away. A 30-min rebuild that dies at minute 25 costs roughly $0.60 + 6 min to redo on a representative ERP warehouse — the simplicity of full rebuilds is worth the occasional redo. The failure is still recorded in the per-datasource run history (status failed with the error), so the audit trail shows the attempt even though the index itself was rolled back.

Qdrant is required

Qdrant is a hard dependency for the platform. Without QDRANT_URL set, schema indexing fails and discovery stays blocked until the index is ready; vector search and /ask return 503 Service Unavailable with an explicit error body. The API server logs a warning at startup when QDRANT_URL is not set.

Relevant env vars

Variable	Default	Purpose
QDRANT_URL	—	Required. host or host:port for Qdrant gRPC
QDRANT_API_KEY	empty	Optional; set on secured Qdrant instances
BLURB_WORKERS	8	Parallel blurb-generation goroutines

The Level-0 catalog rendered into the discovery prompt is capped at schema_render.DefaultCatalogBudgetTokens (150K). This is a constant in the agent today; if a per-deployment override becomes useful it should be wired through RenderOptions.Budget rather than re-introduced as an env var.

The legacy knobs SCHEMA_RETRIEVAL_TOP_K, SCHEMA_RETRIEVAL_TOP_K_NOTOOL, and the per-project project.schema_retrieval.top_k field were retired when discovery moved to on-demand schema actions — discovery no longer performs an upfront top-K retrieval at all, and search_tables clamps its TopK at the constant ai.MaxSearchTopK (30).