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.).

The action format is the engine's, not the pack's. The first exploration message states it — exactly one JSON object per reply, {"rationale": "...", "query": "..."} / lookup_schema / search_tables / {"done": true, ...} — from the same source the reformat nudge uses, so a domain pack whose exploration prompt never describes it (a custom or generated pack) does not leave the model guessing and paying a reformat round-trip per guess. The format follows what the run can query: on a multi-datasource run every query and lookup names its datasource_id; when the only datasource is not SQL, the query example names that source's declared query language rather than showing SQL, and a cube-shaped source (no tables) is not offered lookup_schema. The free-text key is rationale; thinking is still read, and legacy pack prompts that teach it are rewritten at render time, because Claude Opus 5.5 and Sonnet 5.5 refuse a conversation that asks for a JSON field named thinking. A refusal (stop reason refusal, no content) fails the step with an explicit error instead of being re-asked as an unparseable reply — re-sending the same conversation is refused again.

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)

Checkpointing and resume

Exploration is where a run's cost sits: one agentic LLM call and one warehouse query per step, dozens of steps. Until checkpointing existed, none of that was written anywhere replayable until Phase 7, so a process that died anywhere earlier lost all of it — and the run was marked failed, which is terminal.

Each completed exploration step now lands in its own discovery_checkpoints document as it finishes, plus one summary document when exploration ends. A failed run with a checkpoint can be resumed: it re-enters the same run id, replays the steps already executed instead of re-querying them, and continues from the next one. A run that died after exploration finished goes straight to analysis, making zero exploration LLM calls and zero warehouse queries.

Analysis is checkpointed too, one document per completed area in discovery_analysis_checkpoints. A run that died with some areas finished restores those and analyses only the rest, so a resumed run re-pays for neither exploration nor the areas already done. The saving is smaller than exploration's — an area reasons over the cached exploration digests rather than querying the warehouse — but it covers the per-insight validation calls each area makes, and it is what lets a run killed by DISCOVERY_MAX_DURATION make progress on resume instead of starting the same areas again in the same order.

Resume is operator-initiated — the dashboard shows a Resume from step N button on a failed run that has a checkpoint, and the same thing is available as POST /api/v1/runs/{runId}/resume. Nothing retries automatically.

What a checkpoint keeps

Kept	Why
The action, its reasoning, the SQL and its purpose	Replay rebuilds the model's own prior turn from them
row_count, timing, token counts, self-heal summary	The result message and the run's accounting
The compact result digest	What the analysis phase renders
Up to 50 rows of the result, normalised	The insight validation phases read raw rows; their evidence bundle is already a 50-row sample on the live path, so a resumed run's verifier sees the same evidence
Quality caveats	Every insight's evidence label derives from them, and they are knowable nowhere else — a resumed run without them would relabel findings computed over withheld rows as sound

Deliberately not kept: the full result set (what makes a step unbounded in size), the per-attempt SQL-fix log (audit data, written once at Phase 7), and the raw LLM dialog.

A resumed run's audit log therefore carries the 50-row sample for its pre-crash steps rather than their full results — those rows died with the crashed process. A normal run, and the steps a resumed run executes itself, are unchanged.

What a completed analysis area keeps

The area's whole analysis step: its prompt and response, its insights with their validation verdicts and evidence-quality labels already attached, its validation results, and its picker telemetry. Nothing is stripped — unlike an exploration step, an area's size is already bounded by the picker's token budget, and the identical payload is what Phase 7 writes to discovery_analysis_steps anyway, so a restored area's audit trail reads exactly like one the run produced itself.

Alongside it, two numbers that cannot be recomputed later:

Kept	Why
How many of the area's insights were validated	The per-run validation cap is a budget threaded across areas. A resumed run that reset it would validate past the cap.
Which exploration the area was analysed over	Insights cite exploration step numbers. An area restored into a different exploration would attribute its evidence to the wrong queries, so a run that had to re-explore restores nothing.

Only a completed area is checkpointed. An area whose analysis call failed — a provider error, a parse failure, a context deadline — is deliberately left unmarked so a resume retries it. This is what makes the deadline case work: when DISCOVERY_MAX_DURATION expires mid-analysis the loop does not stop, it error-marks every area it has left, and if those counted as done the resume would restore them all, re-run nothing, and reproduce the same near-empty discovery.

What replay re-executes

Split by cost, so the expensive thing is never paid for twice:

Action	On replay
query_data	Never re-executed. The result message is rebuilt from the retained rows and the step's metadata.
lookup_schema	Re-executed against the schema cache — a map lookup plus a Mongo read, no warehouse traffic and no LLM call.
search_tables	Re-executed — one embedding call and one vector query.
get_correlations	Re-executed — an in-process lookup.
complete_rejected	The nudge is re-derived from the persisted reason.

Re-executing the cheap actions through their real code paths is what restores the per-run lookup_schema / search_tables / get_correlations budgets and the already-fetched-table dedupe as a side effect, so a resumed run cannot be handed a fresh schema budget.

No synthetic "you were resumed" message is injected. The replayed transcript is the signal: the model sees its own prior actions and the last result, and answers with the next step.

Limits worth knowing

• Checkpoints expire. DISCOVERY_CHECKPOINT_RETENTION (default 48h) must exceed DISCOVERY_MAX_DURATION, or a long run's early checkpoints expire while it is still running. Raise them together. A resume with nothing left to replay is refused with an explanation rather than silently re-exploring. A surviving exploration summary is not resumable on its own once the step rows have gone: skipping to analysis means analysing the replayed steps, and with none the picker finds nothing for any area, so the run would cost the whole analysis phase to produce a discovery with no insights. Resume refuses it instead, the same way it refuses a run that never checkpointed.
• A gap stops the replay. Checkpoint writes are best-effort so they can never abort a working run. If one fails, replay stops at that step and re-explores from there — replaying across a hole would misnumber every later step, and insights cite step numbers.
• So does a stale tail. Once a gap has been filled, the rows no longer have a hole in them, but the steps after the filled one are the previous attempt's answers to a step that no longer exists. The replay therefore also stops at the first step written by an older attempt than the step before it, and re-explores from there — otherwise the model would be handed a conversation spliced from two different explorations. Attempts may rise along the replayed prefix (that is just a later attempt continuing where an earlier one stopped); they never go backwards.
• Cancel stays terminal. Cancelling is a deliberate hard kill; its checkpoints are deleted and the run cannot be resumed. A cancel leaves the attempt unchanged and the runners can return before the workload is gone, so a dying agent can write one more checkpoint row after the purge. It is bounded to one — the marker write that follows it refuses a cancelled run, which stops the agent — and inert, because the orphan sweep only keeps the vector collection of runs that are genuinely resumable rather than any run that has rows.
• A cancelled run is not owned by anybody. The ownership probe answers three ways, not two: owned by this attempt, taken over by a newer one, or cancelled. Cancellation is checked before the attempt comparison and regardless of it, because a cancel does not change the attempt — so an attempt-only check told a killed run's agent it still owned the run, and the workload can still be alive since the runners can return before it is gone. Two consequences. The ownership gates stop the agent instead of letting it spend its whole remaining budget, and a refused terminal write on a cancelled run retires that attempt's result rather than keeping it. The second is the one that matters: the result used to survive the cancel, on display with nothing to explain it. A run the startup sweep merely marked failed is deliberately NOT treated this way — its agent may still be working and its result must survive.
• Retiring a superseded result is all or nothing. When a resume supersedes an attempt that already saved a result, that result and everything derived from it are removed: its standalone insight and recommendation rows, their vectors, its split logs, and the discovery itself. If the vectors cannot be cleaned up, nothing is removed. The rows cannot go first — their ids are the point ids, so a surviving point becomes unaddressable, and project search renders a hit it cannot load as a blank result rather than skipping it. And the parent cannot go while the rows stay: those collections are read by project id, so the superseded attempt's findings would go on being shown as current with no result behind them. The degraded state is therefore two coherent results for one run, which is visible and retryable.
• A resumed run holds no plan reservation. The run's charge is keyed on its run id and a resume re-enters that id, so resume is free rather than re-priced. The consequence is that a resumed run is invisible to anything enforcing concurrency through reservations: resume-versus-resume is serialised, and so is a competing run that is already visible, but a deployment whose per-project concurrency is governed by the reservation can still start a fresh run alongside a resumed one. That is accepted for now and revisited with the operator-Pause increment.
• Analysis resumes per area, and a failed area is retried rather than restored. A resumed run restores the areas a previous attempt completed and analyses the rest. Three things follow. An area that failed is re-run, which is deliberate (see above). An area is restored only when the run is skipping exploration — if it has to re-explore, every area is analysed again, because the step numbers its insights cite are about to stop meaning what they meant. And a checkpoint for an area this run is not running, because the project's domain pack changed between attempts or the resume used a narrower area selection, is simply never read.
• A run never deduplicates against its own earlier attempts. The duplicate check runs before the new vectors are upserted and before the superseded attempts are retired, so a predecessor's indexed findings are still there while it runs — and a restored area's insights carry identical text, so they match it outright. The check therefore excludes every discovery this run produced, not just the one being written, or the live result would be stamped a duplicate of a row that is deleted moments later. Other runs stay in scope, which is what the check is for. A run with a single attempt excludes exactly what it always did.
• Deleting a project takes its analysis checkpoints with it, alongside the exploration ones — an area checkpoint holds that area's whole prompt, response and validation evidence, so leaving it to the retention TTL would keep a deleted project's reasoning on disk.
• Analysis checkpoints alone do not make a run resumable. Resumability is still decided by the exploration prefix, and that is not an oversight: skipping to analysis means analysing the replayed steps, so a run whose exploration rows have expired would analyse nothing and bill the operator for an empty discovery. If the exploration rows go, the area rows become unreachable and age out on their own TTL.
• The novelty counters reset. A cube-reaching resumed run has to re-establish its judged steps. Since that rule can only ever lengthen a run, resume can never end one early. They are not restored from the checkpoints because they cannot be: a novelty judgement is a neighbour search against the index as it stood at that step, so re-judging a replayed step against an index that already holds the whole prefix would score it against steps that had not run yet. If a resume cannot drop the previous attempt's vector collection, the index is stood down for that attempt rather than reasoned over: the points that survive the failed drop are steps the replay discarded, and the novelty rule would read them as earlier work and could end the run early — the one thing this rule must never do. Analysis ranking still uses the collection, deliberately: a search error there skips the area entirely, while a stale point can at worst skew a score, because hits are filtered against the run's own step list and the content always comes from it.
• A resumed run is the project's current run, even if it is not the newest. Resume keeps the run's original started_at — active_ms is what carries compute across attempts — so resuming a failed run after a newer one has since finished leaves the live run with the older start time. The project's status therefore reports its active run when it has one, and falls back to the most recently started otherwise. Without that the dashboard would show the finished run and offer no progress or cancel for the one actually spending budget.
• A previous attempt's agent may still be alive, and cannot corrupt the new one. The API's startup sweep marks in-flight runs failed after a restart without reaping their workloads, so resuming such a run can leave two agents on one run id. Every write either agent makes is fenced by attempt: a superseded attempt cannot overwrite a newer one's checkpoints, declare exploration finished on its behalf, stamp its terminal status, or delete its results. It also finds out on its next step — the run-document marker it writes per step answers the question for free — and stops there rather than spending the rest of its budget on results that will be discarded. The residual is one checkpoint row: a resume landing between that marker write and the step write can let a single row through, which the row-level fence then catches whenever the live attempt has already written that step.
• Long-term project memory is written after ownership is decided, not before. A run's patterns are merged into the project's context at the end, and that write is keyed on the PROJECT — so patterns from a discovery that is later deleted would outlive it and steer every future run, and nothing retracts them. An ownership check before the write can only shorten the gap between asking and writing; for a write that cannot be taken back, shorter is not closed. So it happens on the terminal path instead: when the attempt claimed the run, and when the claim was undetermined but the result is being kept, and never when the result is about to be deleted.
• The live step feed is scoped to the run's current attempt. Every write that decides a run's outcome is fenced by attempt or retired at the tail — checkpoints, the exploration summary, the terminal status, the discovery itself — and ownership gates stop a superseded attempt before each expensive phase, before each analysis area, and before the project-context update (the one write nothing can undo). The discovery_run_steps rows behind the dashboard's live log are fenced too, but by filtering rather than by gating: the agent stamps its attempt on every row and the API serves only the attempt the run is on now. Gating could not have closed this one — it narrows the window to a single in-flight LLM call, and a window always remains between the last gate and the write. Filtering also repairs something subtler. The feed's _id > since_id cursor assumes one writer process per run; an ObjectID is 4 bytes of seconds then 5 bytes random per process, so with two attempts alive the ordering within a second is decided by that random prefix, and a cursor that advances onto a superseded row can permanently skip the live attempt's rows from that same second. One writer in the stream restores the assumption. Two consequences worth knowing. Rows written before the field existed carry no attempt and are served to an attempt-1 read, so historical runs keep their logs. And because a replayed prefix belongs to the attempt that executed it, a resume re-emits that prefix under its own attempt — rows only, since the progress field and the per-action counters live on the run document and already carry across attempts.
• A re-executed lookup_schema can answer differently if the schema cache was re-indexed between attempts. That is visible rather than hidden: the replayed turn shows what the cache says now, which is also what the resumed run will query against.

Across attempts

The run document records attempt, last_resumed_at, last_checkpoint_step, active_ms and an append-only lifecycle log, plus the run's own max_steps / min_steps / areas / effort so a resume replays the budget the operator chose rather than the defaults. Per-area resume adds nothing here: an area's progress lives in its own checkpoint document, and last_checkpoint_step is already non-zero for a run that reached analysis, so such a run was always offered a Resume. What it does change is what the live panel says while the resumed run works — the analysis phase reports how many of its areas were already complete.

active_ms is cumulative active compute across attempts, which is what the dashboard shows as elapsed — otherwise a run resumed the next morning would report the hours it spent waiting to be noticed as work. It is recorded at each attempt's terminal write, so an attempt killed hard enough never to reach that point contributes nothing: a slight undercount, accepted because a dead attempt's compute is not worth counting.

Each attempt's lifecycle event carries the LLM provider and model that served it, which is how "which model ran which attempt" stays answerable.

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.

The prompt receives those insights trimmed to the fields the recommender reasons over — id, analysis_area, name, description, severity, affected_count, risk_score, confidence, metrics, indicators, target_segment, evidence_quality. validation, source_steps, sql_metadata, discovered_at and description_md are deliberately left out: none of them is used to write a recommendation, the verdict has already been applied by the filter above, and the verifier + refuter write-ups carried in validation were roughly 80% of the rendered prompt — enough on their own to exceed a 40 960-token context window, fail the request, and end the run with no recommendations. The stored insights are unchanged; only the prompt copy is trimmed (insightsForRecommenderPrompt, see Prompt variables → {{INSIGHTS_DATA}}).

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}} → eligible insights as JSON, trimmed to the recommender's fields (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_id (the run that produced it — lets a resumed run retire the partial
    result its previous attempt left behind, and keep that result out of its
    own "previously discovered" context)
  - 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).

A resumed run writes its new result first and retires the superseded one afterwards, rather than clearing the old rows before writing the new ones. There is no window in which the project shows no result at all, and because the new rows go in under a freshly minted discovery_id, the unique index on discovery_recommendation_log.discovery_id cannot be violated by a second pass. Once the result is durable the run's checkpoints are deleted — both the exploration rows and the per-area analysis rows, from the same place, so the two cannot drift apart; on a failure they are kept, which is what leaves the run resumable.

This is also why restoring an analysis area does not write anything into the result collections. A restored area is merged back into the run's in-memory analysis log and insight list, and the tail then writes the union exactly as it writes a run that did everything in one go: one clean insert, one discovery_recommendation_log row, one pass of embedding. Per-area durability lives entirely in the checkpoint collection, so there is no partial result to upsert and no re-embed to make idempotent.

No identity crosses attempts, either: a restored area's insights are given fresh ids as they are merged back in, and every reference to them is re-pointed — the area's validation results, and the verifier and refuter verdicts inside both those results and each insight's own validation, which name the document they judged separately from the outer id. An insight id keys the embedded insight, the standalone row and the Qdrant point, all written by the tail as this attempt's own — and the previous attempt may already have written those rows before it died, in the window between the standalone write and the terminal status. Carrying an id over would therefore make the standalone insert a duplicate key (and because the indexing phase is non-fatal it would log, return, and skip the standalone recommendations and the embedding too, leaving a successful run with nothing in search), make the pre-upsert duplicate check match the surviving point and mark the insight a duplicate of itself, and let a superseded attempt that is still persisting reclaim rows the live attempt owns. Reminting is also not a change in behaviour: before per-area resume a resumed run re-ran analysis and minted new ids anyway.

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. Resumable from the last exploration checkpoint, and from the last completed analysis area.
K8s Job failure	K8s runner polls Job status, detects failure, updates run. Resumable from the last exploration checkpoint.
API restarted mid-run	Startup sweep marks the run "failed". Resumable — the checkpoints outlive the process that wrote them.
Run exceeded DISCOVERY_MAX_DURATION	Partial result is saved and the run is marked "failed". Resumable: exploration is replayed, not re-run.
Resume with no checkpoint	Refused with 409 and an explanation (it expired, or the run died before its first step). Start a new 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

Next Steps

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