Discovery Lifecycle

A discovery run is the core operation of DecisionBox. The agent autonomously explores your data warehouse, finds patterns, validates them, and generates recommendations. This page explains each phase in detail.

Phases Overview

Phase	What happens	Duration
Startup	Load project config, secrets, providers (pre-phase)	~2s
1. Load project context	Fetch previous discoveries + feedback	~1s
2. Load schemas	List tables, read schemas from cache	5-30s
3. Exploration	AI writes + executes SQL queries	2-30 min
4. Analysis	Generate insights per analysis area	1-5 min
4.5. Insight validation	LLM verifier + refuter check insights against row evidence	1-5 min
5. Recommendations	Generate actionable advice from supported insights	1-3 min
5.5. Recommendation validation	Same verifier + refuter check recommendations	1-3 min
6. Update project context	Write rolling context for the next run	<1s
7. Save	Write results to MongoDB	~1s
9. Embed + index	Denormalize insights + recommendations to Qdrant for dashboard search (non-fatal)	5-30s

Total time depends on exploration steps, LLM speed, and warehouse query time. A typical 100-step run with Claude Sonnet takes 5-15 minutes.

The phase numbers above match the orchestrator's logged phase numbers — find any phase in the agent log by grepping for the relevant numeric prefix.

Startup (pre-phase)

The agent starts with a project ID and loads everything it needs before Phase 1. The entry point is agentserver.Run(), which parses CLI flags, initializes providers, and runs the discovery pipeline. Custom builds can import agentserver and register plugins (e.g., warehouse middleware) via init() blank imports before calling Run().

Agent receives: --project-id=abc123 --run-id=run456 --max-steps=100
  ↓
Loads project from MongoDB (name, domain, category, warehouse, llm, profile)
  ↓
Sets project ID in context (warehouse.WithProjectID) for middleware
  ↓
Initializes secret provider (reads LLM API key, warehouse credentials)
  ↓
Initializes warehouse provider (BigQuery/Redshift with credentials)
  ↓
Applies warehouse middleware (warehouse.ApplyMiddleware)
  ↓
Initializes LLM provider (Claude/OpenAI/etc. with API key from secrets)
  ↓
Loads domain pack (e.g., gaming/match3 or social/content_sharing → analysis areas, prompts, profile schema)
  ↓
Loads project-level prompt overrides from MongoDB (if any)

Secret loading order:

1. Read llm-api-key from secret provider (per-project)
2. Read warehouse-credentials from secret provider (optional, for cross-cloud)
3. These credentials are passed to the LLM/warehouse provider constructors

Phase 1: Load project context

The agent loads context from previous runs to avoid repetition:

Fetch last 5 discoveries for this project
  ↓
Fetch all feedback (likes/dislikes with comments)
  ↓
Build previous context:
  - Previously found insights (names, areas, severity, dates)
  - Disliked insights with user comments → "AVOID similar conclusions"
  - Liked insights → "MONITOR for changes"
  - Previous recommendations → "Don't repeat unless changed"

This context is injected into all prompts via the {{PREVIOUS_CONTEXT}} template variable in base_context.md.

Phase 2: Load schemas

The agent reads your warehouse structure:

For each dataset in project.warehouse.datasets:
  ↓
  List all tables (excluding system tables like pg_*, stl_*, svv_*)
  ↓
  For each table:
    Get column names, types, nullable flags
    Get approximate row count
  ↓
  Cache schemas for the exploration phase

A compact Level-0 catalog (one line per table: name, column count, row count, keyword hints, joins) is injected into the exploration prompt via {{SCHEMA_INFO}}. The agent fetches per-table column lists and sample rows on demand during exploration via lookup_schema (up to 10 tables per call, 30 calls per run) or search_tables (semantic query against the per-project Qdrant index, 30 calls per run). See On-Demand Schema for the rationale (the previous "always inject L1 detail" approach exhausted the Bedrock 1M-token context on long runs).

Phase 3: Exploration

The core phase. The AI writes SQL queries, executes them, analyzes results, and decides what to query next.

Send to LLM:
  - System prompt (exploration.md + category context)
  - Schema information
  - Profile (game mechanics, monetization model, etc.)
  - Previous context (past insights, feedback)
  - Filter rules (WHERE clause for multi-tenant)
  ↓
LLM responds with JSON:
  {
    "thinking": "I want to check retention rates...",
    "query": "SELECT cohort_date, retention_d1 FROM ..."
  }
  ↓
Agent executes query against warehouse
  ↓
Send results back to LLM
  ↓
LLM writes next query based on results
  ↓
Repeat for max_steps (default: 100)

Each step is written to the discovery_runs collection in real-time, so the dashboard can show live progress.

Self-healing SQL: If a query fails, the agent sends the error message to the LLM and asks it to fix the SQL. This uses the warehouse provider's SQLFixPrompt() (BigQuery-specific SQL fix instructions, Redshift-specific, etc.).

Tolerant action parsing: Exploration continues only when the LLM returns a JSON object containing a recognized action key — query (run a query), lookup_schema / search_tables (inspect the warehouse), done (terminate), the legacy action field, or a tool-use envelope ({"name": "…", "input": {…}}). To reach that action reliably on reasoning / open models, the parser strips <think>…</think> reasoning blocks (recovering an action written inside one) so brace-y reasoning can't shadow the real action, and rejects a truncated/malformed object rather than extracting a nested draft from it. The per-step output budget (EXPLORATION_MAX_OUTPUT_TOKENS) is sized so a long reasoning block cannot truncate the action to empty. (Exploration does not force a provider-native response_format on the action — doing so dropped the model's thinking and corrupted output on some backends; the tolerant parser and retry are the net instead.) Responses that are pure prose, malformed JSON, or JSON with no usable action (e.g. a reasoning-model preamble like {"plan": "...", "thinking": "..."}) are rejected and the agent re-prompts with a reason-aware reformat nudge (up to 3 retries per step), preventing silent early termination on models whose prose mentions words like "done" or "complete".

Early-termination guard (--min-steps): Models biased toward early completion (Qwen3, DeepSeek-R1, GPT-OSS) sometimes return {"done": true} after just 1–2 steps even with a 100-step budget. Setting --min-steps=N rejects done signals until step N, records a complete_rejected exploration step, and injects a nudge telling the model how many steps remain. Default is 0 (no floor) for backwards compatibility.

Cube-shaped sources stop on signal, not on a step count. The floor works because on a warehouse a step is roughly a table or a join, so counting steps approximates counting coverage. A cube-shaped source has no tables to work through — a query is a choice of metrics and dimensions — and the number of available slices is combinatorial, so any step count can be reached without covering anything. Sixty trivial breakdowns and fifteen revealing ones satisfy a floor equally well.

So on a run that can query a cube-shaped datasource, the marginal-value rule is added past the floor. --min-steps still gates completion exactly as it does today; once a run is past it, each executed step is scored against the steps already taken, using the per-run vector index the analysis phase already builds. While recent steps are still turning up something the run has not seen, a done signal is rejected; once three consecutive steps repeat earlier work, the exploration has finished. --max-steps is unaffected and remains the runaway cap.

The rule can only make a run longer, never shorter. That ordering matters because every discovery run has tables — a source that cannot anchor may not run discovery on its own, so a cube is only ever present alongside a warehouse, and the floor remains a good coverage proxy for that warehouse. An earlier version let novelty replace the floor outright, which meant connecting an enrichment source to an existing project silently dropped its effective floor from sixty steps to four, with no setting changed and nothing in the run log naming the floor, because the floor never fired to be named.

Two properties are worth knowing when reading a run:

• A run whose novelty cannot be measured falls back to --min-steps rather than refusing every completion until the cap. A degraded index should not turn every run into a maximum-length one against a source metered per request. Four cases reach that fallback: no vector index is wired at all (the rule is never armed), the index has been offered steps and kept none (its write path never worked), the index kept earlier steps but is now refusing several in a row (it is stale — it holds the run's early work and none of its latest, so a search reports steps as new that were never stored to be recognised), and several steps in a row could not be judged (its reads are failing).

  Both index checks ask about the index's state now, not about a tally of past observations — a judgement made while the machinery still looked healthy would otherwise stay on the books after it stopped being true, and the rule would never stand down.

• Steps novelty could not be measured on — no vector index, or one that is failing — count as neither new nor repeated. Three measurement failures are not three repetitions. A step with no earlier step to compare against is different: it cannot be repeating anything, so it counts as new ground. That distinction matters because neighbours are scoped per datasource, so a run's first query against each source has none routinely.

• Novelty is judged within one datasource. The same question asked of a second source returns different data, so it is new ground rather than a repeat — and a multi-datasource run asks parallel questions across its sources deliberately. Steps are only scored against earlier steps that queried the same datasource.

• A query that failed is not evidence either. It returned no data, so it says nothing about what the source has left to give, and re-asking a broken request is a model that is stuck rather than a run that is finished. Failed steps are also skipped when scoring later steps, so a retry is never counted as a repeat of the attempt it retries. A run whose queries all fail therefore falls back to --min-steps rather than exploring to the cap.

Whether a run takes this path is decided from the registered shape of its datasources' providers, and is logged at exploration start.

Testing note. The Qdrant-backed tests for the run-scoped step index and the novelty lookup live behind the integration build tag in services/agent/internal/discovery. That package is not in CI's integration job, so those tests run only where someone runs them — cd services/agent && go test -tags=integration ./internal/discovery/. Adding the package to CI was tried and reverted: a pre-existing test there fails on a Qdrant gRPC connection reset when its container is started on a CI runner, which needs its own investigation. A run that can only reach tables behaves exactly as it always has.

Step types reported to the dashboard:

• query — SQL query executed (with thinking, SQL, row count, timing)
• lookup_schema — Agent fetched L1 detail (columns + sample rows) for one or more tables from the cache (no warehouse traffic)
• search_tables — Agent ran a semantic search against the per-project Qdrant index for tables not surfaced by the catalog
• complete_rejected — LLM signalled done too early — before --min-steps, or, on a cube-reaching run, while steps were still turning up new ground; rejected and exploration continued. The log line carries the engine's own reason, so it names the rule that actually refused rather than always pointing at the floor
• insight — The AI identified a pattern (name, severity)
• analysis — Analysis phase started for an area
• validation — Insight validation result
• error — Something went wrong (with error message)

Phase 4: Analysis

For each analysis area defined by the domain pack (e.g., churn, engagement, monetization for gaming; growth, engagement, retention for social), the agent:

Load area-specific prompt (e.g., analysis_churn.md)
  ↓
Pick relevant exploration steps:
  - vector-rank against the per-run Qdrant index of every step
    (embedding text = step purpose + SQL)
  - promote any step whose text contains a verbatim area keyword
  - drop the lowest-scored steps until the rendered prompt fits
    the per-area token budget
  ↓
Render each picked step as a compact digest (per-column
statistics + head/tail rows + small-result inlining), instead
of inlining the raw row blob
  ↓
Prepend base context (profile + previous context)
  ↓
Substitute template variables:
  {{DATASET}} → dataset names
  {{TOTAL_QUERIES}} → number of picked steps
  {{QUERY_RESULTS}} → JSON array of compact digests for this area
  ↓
Send to LLM
  ↓
LLM responds with JSON:
  {
    "insights": [
      {
        "name": "Day 0-to-Day 1 Drop: 67% Never Return",
        "description": "...",
        "severity": "critical",
        "affected_count": 8298,
        "risk_score": 0.67,
        "confidence": 0.85,
        "indicators": ["...", "..."],
        "source_steps": [1, 3, 5]
      }
    ]
  }
  ↓
Agent parses insights, assigns IDs (e.g., "churn-1", "churn-2")

Insight IDs: The agent generates deterministic IDs in the format {area}-{index} (e.g., churn-1, monetization-3). These IDs are used by recommendations to reference which insights they address.

Markdown descriptions: The platform instructs the LLM to author each description as a small GitHub-Flavored Markdown subset (a bold one-line takeaway, short paragraphs, lists, small sub-headings, simple tables), following a takeaway-first anatomy where the finding supports it. At parse time the agent splits this into two fields: description keeps the plain-text reduction (the raw form read by API consumers, previews, and embeddings) and description_md keeps the Markdown rendition rendered on the detail view. Plain descriptions leave description_md empty.

If analysis fails (e.g., LLM timeout), the error is recorded in analysis_log and the area is skipped. If ALL areas fail, the run is marked as run_type: "failed". If some fail, it's run_type: "partial". The errors are surfaced in the dashboard as a red banner.

Phase 4.5: Insight validation (LLM-native)

Immediately after each analysis area produces its insights, the orchestrator runs the verifier + refuter agent pair on every insight, in affected_count descending order. Per-run cap: VALIDATION_MAX_INSIGHTS_PER_RUN (default 30).

For each insight (desc by affected_count):
  ↓
  Build verifier.Bundle:
    - Doc digest (headline + description + severity + source_steps)
    - SourceSteps[] from explorationResult.Steps (sample-capped + cell-capped)
    - Warehouse info (dialect, dataset, run-wide filter)
    - Discovery context (project, run, domain, language)
  ↓
  Run verifier agent (defender frame):
    - Enumerate claims_considered (headline first)
    - Gather evidence via lookup_schema / query_warehouse / read_step_rows
    - Submit StructuredVerdict with per-claim evidence rows
  ↓
  Run refuter agent (skeptic frame, if VALIDATION_REFUTER_ENABLED):
    - Bundle carries PriorClaims (verifier's enumerated set, verbatim)
    - Attempt to refute each claim; tool-less verdicts are rejected
    - Submit StructuredVerdict with row-level contradicting evidence
  ↓
  Coverage finaliser runs deterministic checks:
    - duplicate detection, headline positional rule, set-equality,
      evidence-required, status-enum validation, derive-Overall fallback
  ↓
  Combine(verifier, refuter, refuterDisabled) → one of 7 statuses:
    confirmed | supported | rejected | partial | unverifiable
    | validation_disabled | skipped_budget_cap
  ↓
  Stamp insight.Validation = {Verifier, Refuter, Combined, RefuterDisabled}

The validator does NOT overwrite the writer's affected_count. The Validation block carries the verifier+refuter verdicts and the dashboard surfaces them alongside the writer's number — when row-level evidence disagrees, both numbers stay visible.

See Insight validation for the architecture and the Configuration → Validation reference for every knob.

Phase 5: Recommendations

Only insights with Combined ∈ {supported, confirmed} are fed to the recommendations prompt — partial, rejected, unverifiable, and skipped_budget_cap insights are filtered out at this gate.

Fail-open exception: insights with Combined == "validation_disabled" (and legacy docs whose Validation field is missing entirely) are treated as eligible. The rationale is permissive: when validation didn't run at all (no LLM client, no schema provider), it would be misleading to penalise insights for the agent's absence — those insights should flow through unchanged. Operators who want strict gating should ensure validation is configured. When the eligible set is empty the recommendation phase is skipped and a RecommendationStep{Status: "skipped_no_eligible_insights"} is persisted for observability.

After generation, validateRelatedInsightIDs drops any recommendation whose related_insight_ids list is empty or references an insight not in the eligible set. The dropped recommendation is logged with the bad IDs so operators can trace the cause, and the per-run drop counts are stamped onto the persisted RecommendationStep (recommendations_dropped, recommendations_dropped_missing_ids, recommendations_dropped_unknown_id — see the RecommendationStep data model). The live dashboard run-step message reads "Generated N recommendations (M dropped due to invalid related_insight_ids)" when the drop counter is non-zero. The most common cause of recommendations_dropped_unknown_id is an LLM that emits category/severity/theme slug strings in place of the input insight's actual UUID (issue #237); the recommendation discipline rules explicitly forbid this shape, but a regression on a specific model surfaces here.

Load recommendations.md prompt
  ↓
Prepend base context (profile + previous context)
  ↓
Substitute:
  {{DISCOVERY_DATE}} → current date
  {{INSIGHTS_SUMMARY}} → "Total: 7 insights (churn: 3, engagement: 2, monetization: 2)"
  {{INSIGHTS_DATA}} → full JSON array of all insights (with IDs)
  ↓
Send to LLM
  ↓
LLM responds with JSON:
  {
    "recommendations": [
      {
        "title": "Send Extra Lives After 3 Failures on Level 42",
        "description": "...",
        "priority": 1,
        "target_segment": "Players who failed level 42 3+ times",
        "segment_size": 642,
        "expected_impact": {
          "metric": "retention_rate",
          "estimated_improvement": "+15-20%"
        },
        "actions": ["Step 1...", "Step 2...", "Step 3..."],
        "related_insight_ids": ["churn-1", "levels-2"],
        "confidence": 0.85
      }
    ]
  }

Related insight IDs: Each recommendation references the insights it addresses via related_insight_ids. These are the IDs assigned in Phase 4. The dashboard shows bidirectional links — recommendations show which insights they address, and insight detail pages show related recommendations.

Phase 5.5: Recommendation validation (LLM-native)

Each kept recommendation is then validated by the same verifier + refuter pair. The bundle's source steps are the token-budgeted union (VALIDATION_REC_STEPS_TOKEN_BUDGET, default 12 000) of source steps from the recommendation's related insights. When over-budget steps are dropped, source_steps_truncated: true is surfaced in the prompt so the agent can mark claims dependent on omitted steps as unverifiable.

Per-run cap: VALIDATION_MAX_RECOMMENDATIONS_PER_RUN (default 15). Output: recommendation.Validation populated with the same StructuredVerdict shape used for insights.

Phase 7: Save

The agent writes the complete DiscoveryResult to MongoDB:

DiscoveryResult:
  - project_id, domain, category
  - run_type: "full" | "partial" | "failed"
  - areas_requested (if selective run)
  - total_steps, duration
  - insights[] (with validation results)
  - recommendations[] (with related_insight_ids)
  - summary (totals, errors)
  - exploration_log[] (every SQL query + result)
  - analysis_log[] (full LLM dialog per area)
  - recommendation_log (full LLM dialog)
  - validation_log[] (verification queries + results)

The run status is updated to completed (or failed if critical errors occurred).

Error Handling

Error	What happens
Invalid API key	Agent fails immediately. Run marked "failed" with error message.
LLM timeout	The specific area is skipped. Other areas continue. Run marked "partial".
All areas timeout	Run marked "failed". Error banner shown in dashboard.
SQL query error	Agent asks LLM to fix the SQL. If still fails, step is skipped.
Warehouse unreachable	Agent fails during schema discovery. Run marked "failed".
Agent process crash	Subprocess runner detects exit code, updates run to "failed" with error from stderr.
K8s Job failure	K8s runner polls Job status, detects failure, updates run.

Cost

Each run costs:

• LLM tokens — Exploration (many small calls) + Analysis (few large calls) + Recommendations (one call)
• Warehouse queries — Each exploration step executes one SQL query

Use the cost estimation feature (POST /api/v1/projects/{id}/discover/estimate) to preview costs before running. The dashboard has a checkbox: "Estimate cost before running."

Next Steps

• Architecture — System components and data flow
• Prompts — Template variables and customization
• Domain Packs — How domain-specific analysis works