# NL → SQL — candidate seed (system) questions

**Status:** design only · **Captured:** 2026-07-24 · **Build nothing yet.**

The **top ~10 most-likely questions per role/scope** — the candidates to **pre-wire** into the
install as `nl_query` rows with `status = system` (seeded, admin read-write, experimenter read-only;
see the `nl_query.status` note). These are the **one-click buttons** a user sees the moment they open
Ask in a context, before anyone has saved anything.

They serve three purposes at once:
1. **Pre-wired buttons** — the `label` + `derived_sqls` a context ships with.
2. **The initial few-shot exemplar pool** for the compiler (`nl-sql-mapping-design.md` §5.1) — so
   generation has house-style worked examples on day one, before admins approve anything.
3. **Happy-path regression anchors** — every one is a `SINGLE`/`VARIANTS` expectation.

**How to read this.** Each entry: **`label`** *(display)* — "the `canonical_prompt`" — `params` — then
the parameterized SQL. Params come in three **kinds** (contract in `nl-sql-mapping-design.md` §4.5,
lookups in **Parameter conventions** below): **scalar** (a value, bound `%(name)s`); **category** (a
value from a bounded set — model, cohort, status — rendered as a **drop-down** and still bound); and
**dimension** (the group-by / slice **selector**, e.g. group scores by nudge *or* model *or* cohort —
allowlist-substituted `{{group_by.select}}`, shown as *Template* + a *Resolved* example). A context
**seeds default params from the screen**, so most "for a run" buttons need no typing. Every
column/enum was verified against `schema.dbml` on 2026-07-24.

**Roles & scope.** The Ask surface is staff-only:
- **Enrollees — none.** Enrollees are in the blinded study UI; they have no Ask surface, so no seeds.
- **Experimenter** — the Workspace scopes: **results · experiments · models · cohorts · documents · global**.
- **Admin** — all of the above **plus** the admin scopes: **audit · users · ops**.

**Frequency is an estimate.** The ranking is a design judgment of what gets used most; `hit_count`
telemetry after launch is what should actually promote/demote seeds. This is the starting set.

---

## Parameter conventions

Two rules apply to **every** seed below, so they are stated once here rather than repeated 90 times
(the param-kind contract is in `nl-sql-mapping-design.md` §4.5):

**(1) Every filter over a bounded set is a `category` drop-down — never a free-text box** — even when
the values are not an enum. The chosen value is still bound as `%(name)s`; the drop-down is populated
by an `options_sql` lookup (read-only, RLS-scoped). The standard lookups:

| param | `options_sql` (returns `value, label`) |
|---|---|
| `model` | `SELECT id AS value, display_name AS label FROM model_catalog WHERE enabled = true ORDER BY display_name` |
| `config_id` | `SELECT id AS value, name AS label FROM model_config ORDER BY name` |
| `cohort_id` | `SELECT id AS value, name AS label FROM cohort ORDER BY name` |
| `enrollee_id` | `SELECT id AS value, username AS label FROM profile WHERE role = 'enrollee' AND enabled = true ORDER BY username` |
| `user_id` | `SELECT id AS value, username AS label FROM profile WHERE role IN ('experimenter','admin') ORDER BY username` |
| `experiment_id` | `SELECT id AS value, name AS label FROM experiment ORDER BY name` |
| `run_id` | `SELECT id AS value, name AS label FROM experiment_run ORDER BY started_at DESC` |
| `aptitude` | `SELECT DISTINCT a AS value, a AS label FROM model_catalog CROSS JOIN unnest(aptitudes) a WHERE enabled = true ORDER BY 1` |
| `target_type` | `SELECT DISTINCT target_type AS value, target_type AS label FROM audit_log WHERE target_type IS NOT NULL ORDER BY 1` |
| enum params (`state`, `extraction_status`, `kind`, `combine_method`, …) | static — the enum's values, no lookup |

**Assume this throughout:** every `%(…_id)s` in the seeds below (`run_id`, `experiment_id`,
`config_id`, `cohort_id`, `enrollee_id`, `user_id`), plus the `model` / `aptitude` / `target_type` /
enum filters, is a **category** drop-down populated by the matching lookup — the seeds don't
re-annotate each one. Where a param is **seeded from the screen context** (the run/experiment being
viewed), the drop-down is pre-selected but still switchable.

**(2) "Compare / by / across X" is a `dimension` drop-down, not a hard-coded column.** The user picks
the slice; the engine allowlist-substitutes the fragment (`{{group_by.select}}` / `{{group_by.join}}`,
resolved *before* value binding — §4.5). Standard choices, by scope:

| value | label | `select` | `join` |
|---|---|---|---|
| `nudge` | Nudge | `n.name` | `JOIN nudge n ON n.id = er.nudge_id` |
| `model` | Model set-up | `mc.name` | `JOIN model_config mc ON mc.id = er.model_config_id` |
| `cohort` | Enrollee group | `co.name` | `JOIN cohort co ON co.id = er.cohort_id` |
| `retention` | Retention policy | `er.candidate_retention_policy` | — |
| `scenario` ¹ | Scenario | `e.scenario` | `JOIN experiment e ON e.id = er.experiment_id` |
| `opener` ¹ | Opening message | `e.opening_message` | *(same experiment join)* |
| `sysprompt` ¹ | System prompt | `e.system_prompt` | *(same experiment join)* |
| `reveal` ¹ | Reveal policy | `e.reveal_policy` | *(same experiment join)* |

¹ **Cross-experiment only.** `scenario` / `opening_message` / `system_prompt` / `reveal_policy` live on
`experiment` and are **constant within** one experiment's series, so they are offered **only at
`global` scope**. Within a single experiment (the `results` scope, `experiment_id` bound), the
meaningful slices are the five per-run variables (`nudge` … `retention`).

---

## Scope: `results` — analyzing a run/experiment *(experimenter · admin)*

Most-trafficked context. `%(run_id)s` / `%(experiment_id)s` are seeded from the viewed run/experiment.

1. **`Scorecard`** *(table)* — "the scorecard for this run" — `run_id`
   ```sql
   SELECT groundedness_mean, relevance_mean, coherence_mean, instruction_following_mean, composite_mean,
          n_rounds, n_enrollees, total_generator_cost + total_judge_cost AS total_cost
   FROM run_result WHERE run_id = %(run_id)s;
   ```
2. **`Composite by …`** *(bar)* — "compare composite score across a chosen dimension" — `experiment_id` *(category)* · `group_by` *(dimension, within-experiment set, default `nudge`)*
   *Template:*
   ```
   SELECT {{group_by.select}} AS dimension, avg(cr.score_composite) AS composite
   FROM chat_round cr JOIN experiment_run er ON er.id = cr.run_id
   {{group_by.join}}
   WHERE er.experiment_id = %(experiment_id)s
   GROUP BY {{group_by.select}} ORDER BY composite DESC;
   ```
   *Resolved (`group_by = model`):*
   ```sql
   SELECT mc.name AS dimension, avg(cr.score_composite) AS composite
   FROM chat_round cr JOIN experiment_run er ON er.id = cr.run_id
   JOIN model_config mc ON mc.id = er.model_config_id
   WHERE er.experiment_id = %(experiment_id)s
   GROUP BY mc.name ORDER BY composite DESC;
   ```
3. **`Groundedness by …`** *(bar)* — "groundedness across a chosen dimension" — `experiment_id` *(category)* · `group_by` *(dimension)* — *VARIANTS: round- vs enrollee-weighted (corpus Q80)*
   *Template:*
   ```
   SELECT {{group_by.select}} AS dimension, avg(cr.score_groundedness) AS groundedness
   FROM chat_round cr JOIN experiment_run er ON er.id = cr.run_id
   {{group_by.join}}
   WHERE er.experiment_id = %(experiment_id)s
   GROUP BY {{group_by.select}} ORDER BY groundedness DESC;
   ```
   *Resolved (`group_by = nudge`):*
   ```sql
   SELECT n.name AS dimension, avg(cr.score_groundedness) AS groundedness
   FROM chat_round cr JOIN experiment_run er ON er.id = cr.run_id
   JOIN nudge n ON n.id = er.nudge_id
   WHERE er.experiment_id = %(experiment_id)s
   GROUP BY n.name ORDER BY groundedness DESC;
   ```
4. **`Cost of this run`** *(table)* — "how much did this run cost, generation vs judging" — `run_id`
   ```sql
   SELECT total_generator_cost, total_judge_cost, total_generator_cost + total_judge_cost AS total_cost
   FROM run_result WHERE run_id = %(run_id)s;
   ```
5. **`Lowest-scoring enrollees`** *(table)* — "which enrollees scored lowest in this run" — `run_id`
   ```sql
   SELECT enrollee_id, avg(score_composite) AS composite
   FROM chat_round WHERE run_id = %(run_id)s GROUP BY enrollee_id ORDER BY composite ASC LIMIT 10;
   ```
6. **`Winning model`** *(bar)* — "which model won the most rounds in this experiment" — `experiment_id`
   ```sql
   SELECT cat.display_name, count(*) AS wins
   FROM chat_round cr JOIN experiment_run er ON er.id = cr.run_id
   JOIN model_config_model mcm ON mcm.id = cr.best_model
   JOIN model_catalog cat ON cat.id = mcm.catalog_id
   WHERE er.experiment_id = %(experiment_id)s GROUP BY cat.display_name ORDER BY wins DESC;
   ```
7. **`Stop reasons`** *(pie)* — "why did the loops stop in this run" — `run_id`
   ```sql
   SELECT stop_reason, count(*) AS rounds FROM chat_round WHERE run_id = %(run_id)s GROUP BY stop_reason;
   ```
8. **`Rounds per enrollee`** *(bar)* — "how many rounds each enrollee did in this run" — `run_id`
   ```sql
   SELECT enrollee_id, count(*) AS rounds FROM chat_round WHERE run_id = %(run_id)s
   GROUP BY enrollee_id ORDER BY rounds DESC;
   ```
9. **`Per-factor scores`** *(bar)* — "the four factor scores for this run" — `run_id`
   ```sql
   SELECT groundedness_mean, relevance_mean, coherence_mean, instruction_following_mean
   FROM run_result WHERE run_id = %(run_id)s;
   ```
10. **`Cost & effort by …`** *(table)* — "cost, tokens and rounds per chosen dimension" — `experiment_id` *(category)* · `group_by` *(dimension)*
    *Template:*
    ```
    SELECT {{group_by.select}} AS dimension,
           sum(rr.total_generator_cost + rr.total_judge_cost) AS cost,
           sum(rr.total_tokens) AS tokens, sum(rr.n_rounds) AS rounds
    FROM run_result rr JOIN experiment_run er ON er.id = rr.run_id
    {{group_by.join}}
    WHERE er.experiment_id = %(experiment_id)s
    GROUP BY {{group_by.select}};
    ```
    *Resolved (`group_by = cohort`):*
    ```sql
    SELECT co.name AS dimension,
           sum(rr.total_generator_cost + rr.total_judge_cost) AS cost,
           sum(rr.total_tokens) AS tokens, sum(rr.n_rounds) AS rounds
    FROM run_result rr JOIN experiment_run er ON er.id = rr.run_id
    JOIN cohort co ON co.id = er.cohort_id
    WHERE er.experiment_id = %(experiment_id)s
    GROUP BY co.name;
    ```

### Mixture-of-experts routing *(these two apply only to a run whose `model_config.combine_method = 'moe'`)*

Added 2026-08-25 with the `moe` combine method. Both read `chat_round.moe_routing`, the per-round record of
how the router chose ([ALGORITHMS §19](ALGORITHMS.md#19-moe-routing)). They are the reason that column
exists: **no screen surfaces routing**, so these buttons are how the decision is read. Two consequences
worth stating on the buttons themselves:

- **They need the raw rounds.** `chat_round.moe_routing` is deleted by a Cleanup, and only
  `run_result.model_perf` survives — which gives routing *share* but not *how* each round was decided.
  So Q12 keeps working after a cleanup (from `model_perf`); Q11 does not.
- **`moe_routing` carries no jsonb index** (see `schema.dbml`'s access-paths note), so `moe_routing->>'stage'`
  is a sequential scan. Fine at these volumes; revisit in the deferred index audit if either becomes hot.

11. **`Did routing escalate?`** *(table)* — "how often did the router fall back to the judge on this run, and did those rounds score better" — `run_id`

    The headline MoE question, and the one that says whether the judge tie-break earns its cost:
    the embedding stage is free, the judge stage is a model call, so a high escalation rate with no
    quality gain is an argument for a smaller `MOE_TIE_MARGIN`.
    ```sql
    SELECT cr.moe_routing->>'stage'                  AS decided_by,
           count(*)                              AS rounds,
           round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct_of_rounds,
           avg(cr.score_composite)               AS composite,
           avg(cr.judge_cost)                    AS judge_cost_per_round
    FROM chat_round cr
    WHERE cr.run_id = %(run_id)s AND cr.routing IS NOT NULL
    GROUP BY 1 ORDER BY rounds DESC;
    ```

12. **`Which expert answered?`** *(bar)* — "how the router spread this run's rounds across the experts" — `run_id`

    The routing distribution, with each expert's quality and generation cost beside it — the
    cheap-expert-carries-the-easy-prompts claim, checked. Reads the raw rounds so it can also break out
    the deciding stage; after a cleanup the same distribution (without the stage split) is still readable
    from `run_result.model_perf`.
    ```sql
    SELECT mcat.display_name          AS expert,
           count(*)                   AS rounds_routed,
           avg(cr.score_composite)    AS composite,
           avg(cr.token_cost)         AS generator_cost_per_round,
           count(*) FILTER (WHERE cr.moe_routing->>'stage' = 'judge') AS via_tie_break
    FROM chat_round cr
    JOIN model_config_model mcm ON mcm.id = cr.best_model
    JOIN model_catalog mcat     ON mcat.id = mcm.catalog_id
    WHERE cr.run_id = %(run_id)s AND cr.routing IS NOT NULL
    GROUP BY mcat.display_name ORDER BY rounds_routed DESC;
    ```

## Scope: `experiments` — managing studies *(experimenter · admin)*

1. **`Runs in this study`** *(table)* — "the runs of this experiment and their status" — `experiment_id`
   ```sql
   SELECT id, name, state, started_at, ended_at FROM experiment_run
   WHERE experiment_id = %(experiment_id)s ORDER BY started_at DESC;
   ```
2. **`Runs per study`** *(bar)* — "how many runs each experiment has"
   ```sql
   SELECT e.name, count(r.id) AS runs FROM experiment e
   LEFT JOIN experiment_run r ON r.experiment_id = e.id GROUP BY e.id, e.name ORDER BY runs DESC;
   ```
3. **`Nudges tried`** *(table)* — "which nudges have been tried in this experiment" — `experiment_id`
   ```sql
   SELECT DISTINCT n.name FROM experiment_run er JOIN nudge n ON n.id = er.nudge_id
   WHERE er.experiment_id = %(experiment_id)s ORDER BY n.name;
   ```
4. **`Studies with no results`** *(table)* — "experiments with no finished runs"
   ```sql
   SELECT e.id, e.name FROM experiment e
   WHERE NOT EXISTS (SELECT 1 FROM experiment_run r
                     WHERE r.experiment_id = e.id AND r.state IN ('done', 'aborted'));
   ```
5. **`Most expensive studies`** *(bar)* — "the experiments that cost the most"
   ```sql
   SELECT er.experiment_id, sum(rr.total_generator_cost + rr.total_judge_cost) AS cost
   FROM run_result rr JOIN experiment_run er ON er.id = rr.run_id
   GROUP BY er.experiment_id ORDER BY cost DESC LIMIT 10;
   ```
6. **`Cost per point`** *(table)* — "cost per composite point by experiment"
   ```sql
   SELECT er.experiment_id,
          sum(rr.total_generator_cost + rr.total_judge_cost)
            / nullif(sum(rr.composite_mean * rr.n_rounds) / nullif(sum(rr.n_rounds), 0), 0) AS cost_per_point
   FROM run_result rr JOIN experiment_run er ON er.id = rr.run_id GROUP BY er.experiment_id;
   ```
7. **`Model set-ups used`** *(table)* — "which model configs this experiment has used" — `experiment_id`
   ```sql
   SELECT DISTINCT mc.name FROM experiment_run er
   JOIN model_config mc ON mc.id = er.model_config_id WHERE er.experiment_id = %(experiment_id)s;
   ```
8. **`Live runs`** *(table)* — "runs currently running or paused"
   ```sql
   SELECT id, name, experiment_id, state, started_at FROM experiment_run
   WHERE state IN ('running', 'paused') ORDER BY started_at;
   ```
9. **`Latest run per study`** *(table)* — "the most recent run of each experiment"
   ```sql
   SELECT DISTINCT ON (experiment_id) experiment_id, id, state, started_at
   FROM experiment_run ORDER BY experiment_id, started_at DESC;
   ```
10. **`My launches`** *(table)* — "runs a given person launched" — `user_id`
    ```sql
    SELECT id, name, state, started_at FROM experiment_run WHERE launched_by = %(user_id)s ORDER BY started_at DESC;
    ```

## Scope: `models` — catalog & configurations *(experimenter · admin)*

1. **`Available models`** *(table)* — "which models can we use"
   ```sql
   SELECT id, display_name, provider, model FROM model_catalog WHERE enabled = true ORDER BY display_name;
   ```
2. **`Cheapest models`** *(bar)* — "the cheapest enabled models"
   ```sql
   SELECT display_name, input_price, output_price FROM model_catalog
   WHERE enabled = true ORDER BY input_price + output_price ASC LIMIT 10;
   ```
3. **`Models good at X`** *(table)* — "enabled models good at a given aptitude" — `aptitude`
   ```sql
   SELECT display_name FROM model_catalog
   WHERE enabled = true AND %(aptitude)s = ANY(aptitudes) ORDER BY display_name;
   ```
4. **`Models in a set-up`** *(table)* — "which models a configuration uses" — `config_id`
   ```sql
   SELECT cat.display_name, mcm.role, mcm.weight
   FROM model_config_model mcm JOIN model_catalog cat ON cat.id = mcm.catalog_id
   WHERE mcm.config_id = %(config_id)s ORDER BY mcm.role;
   ```
5. **`Win-rate leaderboard`** *(bar)* — "which models win the most rounds"
   ```sql
   SELECT cat.display_name, count(*) AS wins
   FROM chat_round cr JOIN model_config_model mcm ON mcm.id = cr.best_model
   JOIN model_catalog cat ON cat.id = mcm.catalog_id
   GROUP BY cat.display_name ORDER BY wins DESC LIMIT 10;
   ```
6. **`Local vs hosted`** *(pie)* — "how many local vs hosted models"
   ```sql
   SELECT CASE WHEN input_price = 0 AND output_price = 0 THEN 'local/free' ELSE 'hosted' END AS kind,
          count(*) AS models FROM model_catalog WHERE enabled = true GROUP BY 1;
   ```
7. **`Never-used models`** *(table)* — "configured models that never won a round"
   ```sql
   SELECT DISTINCT cat.display_name
   FROM model_config_model mcm JOIN model_catalog cat ON cat.id = mcm.catalog_id
   WHERE NOT EXISTS (SELECT 1 FROM chat_round cr WHERE cr.best_model = mcm.id);
   ```
8. **`Set-ups & combine method`** *(table)* — "the model configurations and how they combine"
   ```sql
   SELECT name, combine_method, max_iterations FROM model_config ORDER BY name;
   ```
9. **`Score per set-up`** *(bar)* — "average composite score per model configuration"
   ```sql
   SELECT er.model_config_id, avg(cr.score_composite) AS composite
   FROM chat_round cr JOIN experiment_run er ON er.id = cr.run_id
   GROUP BY er.model_config_id ORDER BY composite DESC;
   ```
10. **`Retired but referenced`** *(table)* — "disabled models still used by a config"
    ```sql
    SELECT DISTINCT cat.display_name FROM model_catalog cat
    JOIN model_config_model mcm ON mcm.catalog_id = cat.id WHERE cat.enabled = false;
    ```

## Scope: `cohorts` — enrollee groups *(experimenter · admin)*

1. **`Group sizes`** *(bar)* — "how many people are in each group"
   ```sql
   SELECT c.name, count(cm.enrollee_id) AS members FROM cohort c
   LEFT JOIN cohort_member cm ON cm.cohort_id = c.id GROUP BY c.id, c.name ORDER BY members DESC;
   ```
2. **`Who's in this group`** *(table)* — "the members of this group" — `cohort_id`
   ```sql
   SELECT p.username FROM cohort_member cm JOIN profile p ON p.id = cm.enrollee_id
   WHERE cm.cohort_id = %(cohort_id)s ORDER BY p.username;
   ```
3. **`Dynamic groups`** *(table)* — "which groups are dynamic (system) groups"
   ```sql
   SELECT id, name FROM cohort WHERE selection_type = 'system' ORDER BY name;
   ```
4. **`An enrollee's groups`** *(table)* — "which groups an enrollee belongs to" — `enrollee_id`
   ```sql
   SELECT c.name FROM cohort_member cm JOIN cohort c ON c.id = cm.cohort_id WHERE cm.enrollee_id = %(enrollee_id)s;
   ```
5. **`Empty groups`** *(table)* — "groups with no members"
   ```sql
   SELECT c.id, c.name FROM cohort c WHERE NOT EXISTS (SELECT 1 FROM cohort_member cm WHERE cm.cohort_id = c.id);
   ```
6. **`Ungrouped enrollees`** *(table)* — "active enrollees not in any group"
   ```sql
   SELECT p.id, p.username FROM profile p
   WHERE p.role = 'enrollee' AND p.enabled = true
     AND NOT EXISTS (SELECT 1 FROM cohort_member cm WHERE cm.enrollee_id = p.id);
   ```
7. **`Active enrollees`** *(table)* — "list the active enrollees"
   ```sql
   SELECT id, username FROM profile WHERE role = 'enrollee' AND enabled = true ORDER BY username;
   ```
8. **`Enrollees in many groups`** *(table)* — "enrollees who belong to more than one group"
   ```sql
   SELECT cm.enrollee_id, count(*) AS groups FROM cohort_member cm
   GROUP BY cm.enrollee_id HAVING count(*) > 1 ORDER BY groups DESC;
   ```
9. **`Recently created`** *(table)* — "the most recently created groups"
   ```sql
   SELECT id, name, created_at FROM cohort ORDER BY created_at DESC LIMIT 10;
   ```
10. **`Group provenance`** *(pie)* — "how groups were assembled"
    ```sql
    SELECT selection_type, count(*) AS groups FROM cohort GROUP BY selection_type;
    ```

## Scope: `documents` — RAG corpus *(experimenter · admin)*

1. **`All documents`** *(table)* — "every document and its extraction status"
   ```sql
   SELECT id, name, extraction_status FROM document ORDER BY created_at DESC;
   ```
2. **`Failed extractions`** *(table)* — "documents that failed to extract"
   ```sql
   SELECT id, name, source FROM document WHERE extraction_status = 'failed' ORDER BY updated_at DESC;
   ```
3. **`Stale documents`** *(table)* — "documents needing re-extraction"
   ```sql
   SELECT id, name FROM document WHERE extraction_status = 'stale';
   ```
4. **`Chunks per document`** *(bar)* — "how many text chunks each document has"
   ```sql
   SELECT source_id AS document_id, count(*) AS chunks FROM embedding
   WHERE source_table = 'document' GROUP BY source_id ORDER BY chunks DESC;
   ```
5. **`Not yet embedded`** *(table)* — "documents with no embeddings"
   ```sql
   SELECT d.id, d.name FROM document d
   WHERE NOT EXISTS (SELECT 1 FROM embedding e WHERE e.source_table = 'document' AND e.source_id = d.id);
   ```
6. **`Attached to a run's experiment`** *(table)* — "documents a run draws on" — `run_id`
   ```sql
   SELECT d.name, d.extraction_status
   FROM experiment_run er
   JOIN experiment_context_file ecf ON ecf.experiment_id = er.experiment_id
   JOIN document d ON d.id = ecf.document_id
   WHERE er.id = %(run_id)s;
   ```
7. **`Pending embeds`** *(table)* — "embedding jobs still pending"
   ```sql
   SELECT source_schema, source_table, source_id, attempts FROM embedding_job
   WHERE status = 'pending' ORDER BY requested_at;
   ```
8. **`Largest documents`** *(bar)* — "documents with the most chunks"
   ```sql
   SELECT source_id AS document_id, count(*) AS chunks FROM embedding
   WHERE source_table = 'document' GROUP BY source_id ORDER BY chunks DESC LIMIT 10;
   ```
9. **`Recently uploaded`** *(table)* — "the most recently added documents"
   ```sql
   SELECT id, name, created_at FROM document ORDER BY created_at DESC LIMIT 10;
   ```
10. **`By status`** *(pie)* — "documents by extraction status"
    ```sql
    SELECT extraction_status, count(*) AS documents FROM document GROUP BY extraction_status;
    ```

## Scope: `global` — cross-cutting *(experimenter · admin)*

0. **`Compare scores by …`** *(bar)* — "compare composite score across a chosen dimension, all experiments" — `group_by` *(dimension, **cross-experiment set** — adds scenario / opening-message / system-prompt / reveal-policy)*
   *Template:*
   ```
   SELECT {{group_by.select}} AS dimension, avg(cr.score_composite) AS composite, count(*) AS rounds
   FROM chat_round cr JOIN experiment_run er ON er.id = cr.run_id
   {{group_by.join}}
   GROUP BY {{group_by.select}} ORDER BY composite DESC;
   ```
   *Resolved (`group_by = scenario`):*
   ```sql
   SELECT e.scenario AS dimension, avg(cr.score_composite) AS composite, count(*) AS rounds
   FROM chat_round cr JOIN experiment_run er ON er.id = cr.run_id
   JOIN experiment e ON e.id = er.experiment_id
   GROUP BY e.scenario ORDER BY composite DESC;
   ```
1. **`Total spend`** *(table)* — "how much have we spent in total"
   ```sql
   SELECT sum(total_generator_cost + total_judge_cost) AS total_cost FROM run_result;
   ```
2. **`Most expensive runs`** *(table)* — "the 10 most expensive runs"
   ```sql
   SELECT run_id, total_generator_cost + total_judge_cost AS total_cost
   FROM run_result ORDER BY total_cost DESC LIMIT 10;
   ```
3. **`Daily round volume`** *(line)* — "how many rounds per day over time"
   ```sql
   SELECT date_trunc('day', created_at) AS day, count(*) AS rounds FROM chat_round GROUP BY day ORDER BY day;
   ```
4. **`Overall composite`** *(table)* — "the overall average composite score"
   ```sql
   SELECT sum(composite_mean * n_rounds) / nullif(sum(n_rounds), 0) AS composite_mean FROM run_result;
   ```
5. **`Model leaderboard`** *(bar)* — "which models win the most rounds overall"
   ```sql
   SELECT cat.display_name, count(*) AS wins
   FROM chat_round cr JOIN model_config_model mcm ON mcm.id = cr.best_model
   JOIN model_catalog cat ON cat.id = mcm.catalog_id GROUP BY cat.display_name ORDER BY wins DESC LIMIT 10;
   ```
6. **`Busiest studies`** *(bar)* — "experiments with the most rounds"
   ```sql
   SELECT er.experiment_id, count(cr.id) AS rounds
   FROM chat_round cr JOIN experiment_run er ON er.id = cr.run_id
   GROUP BY er.experiment_id ORDER BY rounds DESC LIMIT 10;
   ```
7. **`Spend by month`** *(line)* — "monthly spend over time"
   ```sql
   SELECT date_trunc('month', computed_at) AS month, sum(total_generator_cost + total_judge_cost) AS cost
   FROM run_result GROUP BY month ORDER BY month;
   ```
8. **`Runs completed per week`** *(line)* — "how many runs finished each week"
   ```sql
   SELECT date_trunc('week', ended_at) AS week, count(*) AS runs
   FROM experiment_run WHERE state = 'done' GROUP BY week ORDER BY week;
   ```
9. **`Average by factor`** *(bar)* — "the average of each scoring factor across all runs"
   ```sql
   SELECT sum(groundedness_mean * n_rounds)         / nullif(sum(n_rounds), 0) AS groundedness,
          sum(relevance_mean * n_rounds)            / nullif(sum(n_rounds), 0) AS relevance,
          sum(coherence_mean * n_rounds)            / nullif(sum(n_rounds), 0) AS coherence,
          sum(instruction_following_mean * n_rounds)/ nullif(sum(n_rounds), 0) AS instruction_following
   FROM run_result;
   ```
10. **`Tokens over time`** *(line)* — "token usage per day"
    ```sql
    SELECT date_trunc('day', computed_at) AS day, sum(total_tokens) AS tokens
    FROM run_result GROUP BY day ORDER BY day;
    ```

---

## Scope: `audit` — the forensic trail *(admin only)*

1. **`Recent activity`** *(table)* — "the most recent privileged actions"
   ```sql
   SELECT created_at, actor_label, action, target_type FROM audit_log ORDER BY created_at DESC LIMIT 50;
   ```
2. **`History of an entity`** *(table)* — "everything that happened to a given entity" — `target_type`, `target_id`
   ```sql
   SELECT created_at, actor_label, action, before_state, after_state FROM audit_log
   WHERE target_type = %(target_type)s AND target_id = %(target_id)s ORDER BY created_at DESC;
   ```
3. **`Corrections`** *(table)* — "which audit entries were later corrected, and by whom"
   ```sql
   SELECT orig.id AS corrected_entry, orig.action, corr.actor_label AS corrected_by, corr.created_at
   FROM audit_log corr JOIN audit_log orig ON orig.id = corr.corrects_entry_id ORDER BY corr.created_at DESC;
   ```
4. **`A person's actions`** *(table)* — "everything a given user did" — `user_id`
   ```sql
   SELECT created_at, action, target_type, target_id FROM audit_log WHERE actor_id = %(user_id)s ORDER BY created_at DESC;
   ```
5. **`Deletions & purges`** *(table)* — "deletion, cleanup and purge actions"
   ```sql
   SELECT created_at, actor_label, action, target_type FROM audit_log
   WHERE action IN ('delete', 'cleanup', 'purge') ORDER BY created_at DESC;
   ```
6. **`Activity in a window`** *(table)* — "actions between two dates" — `from`, `to`
   ```sql
   SELECT created_at, actor_label, action, target_type FROM audit_log
   WHERE created_at BETWEEN %(from)s AND %(to)s ORDER BY created_at DESC;
   ```
7. **`Invites`** *(table)* — "invitations sent"
   ```sql
   SELECT created_at, actor_label, target_id FROM audit_log WHERE action = 'invite' ORDER BY created_at DESC;
   ```
8. **`Busiest actors`** *(bar)* — "who has taken the most actions"
   ```sql
   SELECT actor_label, count(*) AS actions FROM audit_log GROUP BY actor_label ORDER BY actions DESC LIMIT 10;
   ```
9. **`Run lifecycle events`** *(table)* — "launch / abort / cleanup / purge events, by run"
   ```sql
   SELECT created_at, actor_label, action, run_id FROM audit_log
   WHERE action IN ('launch', 'abort', 'cleanup', 'purge') ORDER BY created_at DESC;
   ```
10. **`Access changes`** *(table)* — "account enable/disable events"
    ```sql
    SELECT created_at, actor_label, action, target_id FROM audit_log WHERE action = 'disable' ORDER BY created_at DESC;
    ```

## Scope: `users` — people & roles *(admin only)*

1. **`Users by role`** *(pie)* — "how many users of each role"
   ```sql
   SELECT role, count(*) AS users FROM profile GROUP BY role;
   ```
2. **`Disabled accounts`** *(table)* — "which accounts are switched off"
   ```sql
   SELECT id, username, role FROM profile WHERE enabled = false;
   ```
3. **`Recently added`** *(table)* — "the most recently added people"
   ```sql
   SELECT id, username, role, created_at FROM profile ORDER BY created_at DESC LIMIT 10;
   ```
4. **`Experimenter activity`** *(bar)* — "how many experiments each experimenter has created"
   ```sql
   SELECT p.username, count(e.id) AS experiments FROM profile p
   LEFT JOIN experiment e ON e.created_by = p.id
   WHERE p.role = 'experimenter' GROUP BY p.id, p.username ORDER BY experiments DESC;
   ```
5. **`Never-run enrollees`** *(table)* — "enrollees who have never been in a run"
   ```sql
   SELECT p.id, p.username FROM profile p
   WHERE p.role = 'enrollee' AND NOT EXISTS (SELECT 1 FROM run_enrollment rp WHERE rp.enrollee_id = p.id);
   ```
6. **`Active enrollees`** *(table)* — "the enabled enrollees"
   ```sql
   SELECT id, username FROM profile WHERE role = 'enrollee' AND enabled = true ORDER BY username;
   ```
7. **`Admins`** *(table)* — "who the administrators are"
   ```sql
   SELECT id, username, email FROM profile WHERE role = 'admin';
   ```
8. **`Launchers`** *(table)* — "who has launched runs"
   ```sql
   SELECT DISTINCT p.username FROM experiment_run er JOIN profile p ON p.id = er.launched_by;
   ```
9. **`Most-used enrollees`** *(bar)* — "enrollees who have taken part in the most runs"
   ```sql
   SELECT rp.enrollee_id, count(*) AS runs FROM run_enrollment rp
   GROUP BY rp.enrollee_id ORDER BY runs DESC LIMIT 10;
   ```
10. **`Recently active`** *(table)* — "enrollees by last activity"
    ```sql
    SELECT enrollee_id, max(last_activity_at) AS last_seen FROM run_enrollment
    GROUP BY enrollee_id ORDER BY last_seen DESC LIMIT 20;
    ```

## Scope: `ops` — embeddings, jobs & savepoints *(admin only)*

1. **`Failed jobs`** *(table)* — "embedding jobs that failed"
   ```sql
   SELECT source_schema, source_table, source_id, attempts, error FROM embedding_job
   WHERE status = 'failed' ORDER BY finished_at DESC;
   ```
2. **`Embedding coverage`** *(table)* — "registered sources with failed or open jobs"
   ```sql
   SELECT r.source_schema, r.source_table,
          count(*) FILTER (WHERE j.status = 'failed')                AS failed_jobs,
          count(*) FILTER (WHERE j.status IN ('pending', 'running')) AS open_jobs
   FROM embedding_ingest_registry r
   LEFT JOIN embedding_job j ON j.source_schema = r.source_schema AND j.source_table = r.source_table
   WHERE r.is_active = true GROUP BY r.source_schema, r.source_table;
   ```
3. **`Active sources`** *(table)* — "which sources are registered and active"
   ```sql
   SELECT source_schema, source_table FROM embedding_ingest_registry
   WHERE is_active = true ORDER BY source_schema, source_table;
   ```
4. **`Open jobs`** *(table)* — "embedding jobs pending or running"
   ```sql
   SELECT source_schema, source_table, source_id, status, attempts FROM embedding_job
   WHERE status IN ('pending', 'running') ORDER BY requested_at;
   ```
5. **`Retried jobs`** *(table)* — "jobs that have retried"
   ```sql
   SELECT source_schema, source_table, source_id, attempts, error FROM embedding_job
   WHERE attempts > 1 ORDER BY attempts DESC;
   ```
6. **`Embeddings by source`** *(bar)* — "how many embeddings each source kind has"
   ```sql
   SELECT source_table, count(*) AS embeddings FROM embedding GROUP BY source_table ORDER BY embeddings DESC;
   ```
7. **`Savepoints`** *(table)* — "the database savepoints"
   ```sql
   SELECT id, name, kind, size_bytes, created_at FROM savepoint ORDER BY created_at DESC;
   ```
8. **`Latest savepoint per kind`** *(table)* — "the most recent snapshot of each kind"
   ```sql
   SELECT DISTINCT ON (kind) kind, name, created_at FROM savepoint ORDER BY kind, created_at DESC;
   ```
9. **`Documents by status`** *(pie)* — "documents by extraction status"
   ```sql
   SELECT extraction_status, count(*) AS documents FROM document GROUP BY extraction_status;
   ```
10. **`Stale sources`** *(table)* — "documents flagged stale (re-embed needed)"
    ```sql
    SELECT id, name FROM document WHERE extraction_status = 'stale';
    ```

---

## Notes

- **`action` and `dimension` are free text, not enums** — the `audit` seeds that filter on
  `action IN ('delete','cleanup','purge','invite','disable','launch','abort')` assume the app writes those
  verbs (they are the ones listed in `audit_log.action`'s note). If a deployment uses different verbs,
  those seeds need adjusting; treated as a soft assumption, not an enum guarantee.
- **Two `*_mean` aggregation rules honored:** the cross-run averages here weight by `n_rounds`
  (`sum(mean*n)/sum(n)`), never a plain `AVG(*_mean)`. Cross-run **enrollee-weighted** and **median**
  seeds are intentionally **omitted** — they must recompute from raw `chat_round` (see
  `nl-sql-mapping-design.md` §1 #2/#3), which is heavier than a seed button should default to; they
  live in the per-run scorecard (#1 of `results`) and as on-demand questions instead.
- **Parameterization (added 2026-07-24).** Filters are **category drop-downs** (per the conventions
  table), not free-text — even non-enum categories that span joins (model, cohort, config, user).
  "Compare/by/across X" is a **dimension** selector (`group_by`), so a single seed slices by nudge *or*
  model *or* cohort *or* — at global scope — scenario / opening-message / system-prompt / reveal-policy,
  instead of hard-coding `nudge`. Dimension fragments are **allowlist-substituted, not interpolated**
  (§4.5): safe because the choice set is author-defined and the resolved SQL is re-validated. The
  group-by seeds are shown as *Template* + a *Resolved* example.
- **What is NOT decided:** the final labels/wording, which seeds ship enabled by default per
  deployment, the exact dimension choice set per scope, and the frequency ranking (to be driven by
  `hit_count` after launch, not guessed).