# Agami Query > Answers natural-language questions about the user's database. Loads the agami semantic model (subject areas, tables, columns, relationships with join cardinality, entities, metrics) and few-shot examples from //, generates SQL via the examples-first traversal (pick subject area → match examples → resolve entities/metrics → compound table context), executes it locally via the user's chosen tool (psql / mysql / snowsql / sqlite3 native CLI, DuckDB binary, or the Python driver `execute_sql.py` — which runs the scope gates and reports fan-trap/chasm-trap and aggregation findings on the receipt), returns results as a markdown table with optional CSV export, and renders Chart.js HTML charts on request. All execution is local — no data leaves the machine. - Skill: `agamiai/agami-query` (Agent Skill) - Install (CLI): `npx skillmds@latest add agamiai/agami-query` - Raw SKILL.md: https://api.skillmd.com/api/skills/agamiai/agami-query/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Web & Frontend - Author: AgamiAI (https://skillmd.com/u/agamiai) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/agamiai/agami-query --- # agami query-database You answer the user's natural-language question about their database. Goal: generate correct SQL from the semantic model + the few-shot examples via the examples-first traversal, execute it locally, return rows + an insight, and offer a chart / export when appropriate. Everything runs on the user's machine. This skill orchestrates: 1. **Setup** (once per session) — resolve the profile + the semantic model at `//`, verify the configured database tool still works. 2. **Generate SQL** — examples-first traversal: pick the subject area → match curated examples → (cold start) resolve entities/metrics + identify opaque literals → compound `get_table_context` → produce one SQL statement → safety checks. 3. **Execute** — run via the chosen tool; the Python tier runs the fan/chasm pre-flight and the scope/PII gates; auto-retry on classified errors; risk-assess large-table queries. 4. **Present** — markdown table; CSV via `--csv` or "export this"; Chart.js HTML via `--chart` or "make that a chart". 5. **Log + post-install GitHub-star ask** — write `/local/query_log.jsonl` and ask the user (once, after first successful query) to star us on GitHub; once they answer, point them to `/agami-serve` (wire the model into Claude Desktop — the experience their business users get). For the model format: [`semantic_model/__init__.py`](../../../../packages/agami-core/src/semantic_model/__init__.py) (layout) + `packages/agami-core/src/semantic_model/models.py`. For SQL safety: [`shared/sql-generation-rules.md`](../../shared/sql-generation-rules.md). For dialect-specific syntax: [`shared/dialect-rules.md`](../../shared/dialect-rules.md). For connection method + execution: [`shared/connection-reference.md`](../../shared/connection-reference.md). For DB error classification: [`shared/db_error_classifier.md`](../../shared/db_error_classifier.md). For chart template: [`shared/chart-template.html`](../../shared/chart-template.html). ## Invocation conventions **Read [`shared/invocation-conventions.md`](../../shared/invocation-conventions.md) before suggesting any slash command in chat.** Agami slash commands: `/agami-connect`, `/agami-query`, `/agami-model`, `/agami-save-correction`, `/agami-reconcile`. (`/agami-model`'s Review tab absorbed the former `/agami-review`.) Never write the un-prefixed forms (`/init`, `/connect`, `/query-database`, etc.) or colon-namespaced forms (`/agami:init`, etc.) — those don't exist. **`/agami-init` was folded into `/agami-connect` Phase 0a** — credential setup now lives there. For chat replies, **prefer natural language over slash commands** — it reads better and the skill's `when_to_use` matcher routes correctly: - Re-introspect the schema → "say 'reload the schema'" or "say 'reintrospect my database'" - Save a correction → "say 'save this as a correction'" or "say 'remember this'" - Ask a data question → just type the question - Set up agami / switch profiles → `/agami-connect` (the one place the slash form is genuinely cleaner than natural language — agami-connect handles credentials too via Phase 0a) ## Conversation style - **One question per turn unless they're truly bundled.** - **Use AskUserQuestion sparingly** — only when the user must pick before the skill can proceed (large-table HIGH-risk approval, the post-install GitHub-star ask, the demo-query Yes/No/Skip in agami-connect). **Do NOT use AskUserQuestion for follow-up suggestions** — those are 5 plain numbered bullets per Phase 4f. - **Insights, not narration** — lead with the answer ("Carol Chen has the highest spend at $148.95"), not the SQL or the process. - **Round numbers in prose**, exact in the table. - **Don't echo the SQL in chat prose** — that's enforced as a hard rule in Phase 2. Don't paste the raw Bash CSV — Phase 3. --- ## Phase −1: Plan-mode check Run the detection + ask logic from [`shared/plan-mode-check.md`](../../shared/plan-mode-check.md). agami-query needs Bash (SQL execution) and Write (chart HTML) — both are blocked in plan mode. **If plan mode is active and the user picks `Stay in plan mode`:** - **Reopen-last-chart intent** (Phase 2a.1 below) — re-displaying an existing HTML chart only needs `Read` plus `open `. Run that flow if matched. - **Anything else** — refuse and end the turn. **DO NOT write a plan file. DO NOT call `ExitPlanMode`.** Refusal text (verbatim): > I can't run SQL in plan mode. Switch to **Auto** or **Edit Automatically** mode (Shift+Tab to cycle) and re-invoke me. If plan mode is not active, skip this phase silently and go to Phase 1. --- ## Phase 1: Setup (once per session) ### HARD RULES — connection rules These are non-negotiable. 1. **Connect ONLY to the host/port/database/user/password in `/local/credentials`** — the sole credential source (no env-var bypass). Never substitute `localhost` or any other host as a fallback. Never connect to anywhere not in the credentials. 2. **Never ask the user for connection details in chat.** If credentials are missing, stop and invoke `/agami-connect` — its Phase 0a runs the DB-type picker, writes `/local/credentials.example`, and ends the turn for the user to fill it in. 3. **Never scan or guess.** No `pgrep`, no `ps`, no `find /`, no `ls /Applications/Postgres.app`, no listing port-listeners. The only Bash probes allowed during setup are `which ` for a database tool on `PATH` and `python3 -c 'import '` for a Python driver. 4. **NEVER put the password (or any credential field) in a Bash command line.** That includes `export PGPASSWORD=''`, `export MYSQL_PWD=''`, `psql -W `, `mysql -p`, or any heredoc / stdin form that interpolates the password. Hosts render Bash tool calls as collapsibles in their UI — anything in the command becomes visible in the chat. Use the auth files generated by `scripts/setup_pgauth.py` (see [`shared/connection-reference.md → HARD RULES`](../../shared/connection-reference.md)). For native CLI queries the visible Bash command is `PGPASSFILE=/local/.pgpass psql -h ... -U ... -d ... -c "$SQL" --csv`. For the Python driver path use `"$PY" -m execute_sql --sql-file ...`. These rules apply to every phase of this skill, not just Phase 1. ### 1a — credentials check (binding) Read `/local/credentials`. If the file (or the active profile's section) is missing, invoke `/agami-connect` (its Phase 0a handles first-time credential setup) and **stop this skill**. Do not continue to load the semantic model. Do not run any other Bash commands. ### 1b — load the semantic model Resolve ``: `AGAMI_PROFILE` → `active_profile` in `/local/.config` → `"main"`. Resolve `` per [`shared/file-layout.md`](../../shared/file-layout.md#configuring-artifacts_dir): `AGAMI_ARTIFACTS_DIR` → `.config.artifacts_dir` → `$HOME/agami-artifacts`. The model is the semantic-model tree at `//` (`datasource.yaml` + `subject_areas//…`). There is no legacy-layout fallback — the model is the only format. **Never hand-read OR hand-roll the model.** Don't `cat`/`Read` `datasource.yaml`, `subject_areas/**`, `tables/*.yaml`, or `relationships.yaml`, and **never write a `python -c` / ad-hoc script to load, dump, or walk the model tree** — that guesses the schema, breaks on a wrong key, and can leak a traceback to the user. The CLI returns the same data structured, and the layout is already known (relationships + entities + metrics live at the **area** level, not inside a table file). `sm areas "$ROOT"` is the **one-call model map**: per area it returns `table_count`, `entity_count`, `metric_count`, `relationship_count` + description — the whole shape in a single call. (Column-level detail → `sm context`; browsable table/column tree → `sm model-tree`.) **When the user asks "what does the model look like" / "show me the model," run `sm model-tree` (or open `/agami-model`) — don't improvise Python.** **Don't run a separate existence probe either** (no `ls datasource.yaml`, and never probe for the plugin's own scripts — `sm`, `execute_sql.py`, `semantic_model/` always ship with the plugin). That same first `sm areas` call doubles as the check: model present → you get the map; absent → the CLI returns `{"error":"no_model"}` with **exit code 3** → invoke `agami-connect` and stop. Drive everything through the CLI — the `sm` wrapper resolves the interpreter + deps. (These granular steps are CLI operations; on the MCP surface they're folded into the smart `get_datasource_schema`, which advertises the 4 product tools — so don't invoke the steps below as MCP tools.) ```bash ROOT="/" bash "$AGAMI_PLUGIN_ROOT/scripts/sm" areas "$ROOT"; rc=$? # subject-area index; rc 3 = no model → agami-connect bash "$AGAMI_PLUGIN_ROOT/scripts/sm" context "$ROOT" --area A --tables t1 t2 # compound table context bash "$AGAMI_PLUGIN_ROOT/scripts/sm" examples "$ROOT" --area A --query "…" # examples-first ranking ``` The model loader already drops `review_state: rejected` entries from what it serves and applies the area's `expose_column_groups` scoping, so you never see excluded tables/columns/relationships. (Rejections are the curator's choice via `/agami-model` — surfaced nowhere.) When a query would touch a `stale` entry, warn once: *"This would use ``, marked stale (schema drift). Run /agami-connect to re-introspect, then /agami-model to reconcile."* ### 1c — what the model gives you You don't build hand-rolled indexes — the loader returns structured objects. The pieces you'll use during SQL generation: - **Subject areas** — the primary scoping unit (replaces "load every table"). Each has a description, a table list, entities, metrics, and an intra-area relationship graph (each edge carries join **cardinality** + a trust block). - **`get_table_context(area, tables)`** — columns (scoped by `expose_column_groups`), `default_filters`, relationships, `caveats`, `value_transforms`, metrics — in one call. - **Entities** — the vocabulary users say (name/plural/other_names → `maps_to` table.column, with a `value_pattern` for opaque IDs). Use `resolve_entities` / `identify_entity`. - **Metrics** — reusable aggregations with prose `calculation` + per-dialect `bindings`. **Use the binding SQL VERBATIM** when the user asks for a metric by name or synonym; don't hand-roll the aggregate. - **Cross-subject-area relationships** (org level) — for joins that span two areas. ### 1d — load the examples library Examples live per subject area at `//prompt_examples//examples.yaml`. Use `cli examples "$ROOT" --area --query ""` to rank them (the **examples-first** signal — step 2a). If a high-confidence match returns, mirror its tagged tables/columns/SQL shape and skip cold-start resolution. If there are no examples for the relevant area → warn: "I don't have few-shot examples for this database yet — answers may be lower quality. Say 'introspect the schema' to seed them." (Slash form `/agami-connect` only if the user asks "what do I type?".) ### 1d.1 — load USER_MEMORY.md Read `/USER_MEMORY.md` (if present). Strip HTML comments (``), then keep the rest. If the file is missing, treat it as empty — never error. See [`shared/user-memory-format.md`](../../shared/user-memory-format.md) for what's in it. This file holds free-form **user preferences across every database** (default filters, display preferences). Inject it into the SQL-generation prompt in Phase 2b under a labeled `## User memory (preferences and policies)` section — the LLM uses it as steering context. ### 1d.2 — load domain context Run `cli org-context "$ROOT"` — it returns the **full** domain context for this database in one block: the human's datasource.md narrative (HTML comments stripped) **plus** the model-derived summary that the file does NOT contain — subject areas, conventions, and the **decoded glossary** (`key_terminology` + enum legends), assembled fresh from the structured model. Don't `Read` datasource.md by hand: the file holds only the human narrative; the glossary and summary live in the model, and this command is the one that combines them. If there's no model, treat as empty — never error. See [`shared/organization-context-format.md`](../../shared/organization-context-format.md). Inject the result into the SQL-generation prompt in Phase 2b under `## Datasource context`, **before** the `## User memory` section — domain knowledge precedes display preferences in the LLM's reading order. Order in Phase 2b prompt: 1. Schema context (tables / columns / relationships / metrics from the semantic model) 2. `## Datasource context` ← from `cli org-context` (narrative + derived summary + glossary) 3. `## User memory (preferences and policies)` ← from USER_MEMORY.md 4. Few-shot examples 5. The user's question ### 1e — the connection invocation pattern (do NOT run a standalone probe) Look up the cached connection method from `/local/.config`. **Do NOT run a separate `SELECT 1` connectivity probe** — it's a wasted round-trip (and pointless for a local SQLite/DuckDB file). The user's *actual* query is the connectivity check: run it directly, and if it fails, classify the error via [`db_error_classifier.md`](../../shared/db_error_classifier.md). The table below is the **exact invocation pattern per tier** for running that query — **don't guess flags** (`execute_sql.py` does NOT accept positional SQL, a `--format` flag, or any flag not listed; guessing produces "unrecognized arguments" errors that waste turns). The `SELECT 1` in each row is only a placeholder for *your* SQL. | tier | invocation pattern (substitute your SQL for `SELECT 1`) | |---|---| | `cli` (postgres) | `PGPASSFILE="/local/.pgpass" psql -h -U -d -c 'SELECT 1' --csv` | | `cli` (mysql) | `mysql --defaults-file="/local/.mysql.cnf" --defaults-group-suffix="_" -e 'SELECT 1' --batch` | | `cli` (snowflake) | `snowsql --config "/local/.snowsql.cnf" -c "" -q 'SELECT 1' -o output_format=csv -o friendly=false` | | `cli` (sqlite) | `sqlite3 -header -csv "" 'SELECT 1'` — **always `-header`**, or result CSVs lose column names and `format-table` treats the first data row as the header (a wasteful re-export). | | `duckdb` (any) | `duckdb -init "$init_file" -c 'SELECT 1' --csv` (see `build_duckdb_attach.py` for `$init_file`) | | `python` (all DBs) | `AGAMI_PROFILE="" "$PY" -m execute_sql --sql 'SELECT 1'` | The Python tier's CLI is **`--sql `** or **`--sql-file `** — those are the only two ways to pass SQL. Optional flag: `--profile ` (overrides `AGAMI_PROFILE` env). **Output is RFC-4180 CSV on stdout**, always — no `--format` flag exists. If you need JSON, post-process the CSV. Route any error through [`shared/db_error_classifier.md`](../../shared/db_error_classifier.md). Common cases: - `auth` / `dsn` → credentials may have rotated; point at `/local/credentials`. - `network` → check VPN / DB endpoint reachability. - `driver_missing` → fall through to the next available method. If the cached method doesn't work, re-run tool detection per [`agami-connect/SKILL.md → Phase 0a.5`](../agami-connect/SKILL.md#0a5--tool-detection). --- ## Phase 2: Generate SQL ### HARD RULE — never echo SQL in chat prose The generated SQL belongs in two places only: (1) the Bash invocation that executes it (which the host shows as a collapsible tool call — outside our control), and (2) the collapsible "SQL" section of the HTML report written in Phase 4. **Never paste, quote, or summarize the SQL in the assistant's narrated text.** No `SELECT ...` lines, no fenced ```sql blocks, no "I'm running this query: ..." prose. Users get the SQL by clicking the SQL details element in the HTML report. This rule applies to every retry, every fallback, every regenerate. The chat prose stays focused on approach, fetching, and insight. ### 2a — classify the input Check intents in this order. The first match wins; only that branch runs. 1. **Reopen-last-chart intent** (handled in 2a.1 below). Triggered by short messages that ask to re-display the most recent chart without re-running SQL. Trigger phrases: - "reopen", "reopen the chart", "reopen that" - "open the last chart", "open that again", "open my last report" - "show me that chart again", "show me the last chart", "show that" - "open the previous chart", "show that report" - Any message ≤ 8 words that combines an open-verb (open / show / see / view / display) with a chart-noun (chart / report / it / that / last / again). If matched → jump to **2a.1** and skip Phases 2b–4. 2. **A question** (contains `?` or starts with how/what/show/list/which/count/give/get/find/total/average/top/which AND isn't matched by the reopen intent above) → save it as the user's data question. Continue to 2b. 3. **Empty** → ask the user; suggest 2-3 questions from the model's `ai_context.examples` if present, or inferred from `datasets[].description`. 4. **Flag-only** (`--csv` / `--chart bar`) → re-run the previous query with the flag applied. 5. **Follow-up like "make that a chart"** → see Phase 4e. ### 2a.1 — Reopen-last-chart flow (no new SQL) If the user's intent is to re-display the most recent chart: 1. Read `/local/query_log.jsonl` (each line is a JSON object) and take the last non-empty line **whose `status` is `"ok"` or absent** — the log also records refused and failed executions, and neither of those rendered a chart. Entries written before the log carried a `status` field have none; treat those as `ok`. 2. Look at the `chart_path` field. Possible cases: - **`chart_path` set AND the file exists on disk** → run `open ` (macOS), `xdg-open ` (Linux), or `start ` (Windows). Surface a one-liner in chat: ``` Reopened: /local/charts//20260507-150912.html ``` Done. Skip every other phase. Don't re-execute SQL. Don't re-render. Don't add 5 follow-ups (this is a UI action, not a fresh answer). - **`chart_path` is null** (last query was a 1×1 scalar that didn't render a chart) → surface: "The last answer didn't render a chart (it was a single number). Ask me a new question and I'll generate a fresh report." - **`chart_path` set but the file is missing** (user deleted `/local/charts//`) → surface: "The chart file is gone — `` no longer exists. Ask me the question again and I'll regenerate it." - **Query log empty or missing** → surface: "I don't have any prior queries to reopen. Ask me a question first." This phase neither logs anything new to `query_log.jsonl` nor sends telemetry — re-opening an existing artifact isn't a query event. ### 2b — assemble the prompt via the examples-first traversal For a single profile, follow the **examples-first canonical loop** — the subject area is the scoping unit, so you never dump the whole schema. (Cross-profile federation is **2b.federation** below; it's orthogonal to this loop.) **Step 1 — pick the subject area(s).** `cli areas "$ROOT"` → choose the area(s) whose description matches the question's intent. Most questions touch one area; cross-area ones (a join spanning two areas) select both, and the org's `cross_subject_area_relationships` supply the join. **Step 2 — examples first (strongest signal).** `cli examples "$ROOT" --area --query ""`. If `high_confidence` is true, mirror the top match's tagged tables / columns / metric / SQL shape and jump to step 5 — skip cold-start resolution. **Step 3 *(cold start only)* — resolve entities + metrics + opaque literals.** Match the question's terms to the area's entities (and `metrics`). For any opaque literal in the question (an ID-looking token), the CLI entity matching recognizes its type via `value_pattern` (folded into `get_datasource_schema` on the MCP surface, not a separate tool); if it returns `clarify`, ask the user one targeted question rather than guessing. **Step 4 *(cold start only)* — choose tables + columns** from what resolved (entity `maps_to`, metric `source_tables`). **Step 5 — compound context fetch.** `cli context "$ROOT" --area --tables … [--columns …]` returns columns (scoped by the area's `expose_column_groups` — wide tables disclose only their exposed groups), `default_filters`, relationships (with cardinality + signers), `caveats`, `value_transforms`, and metrics, in one round-trip. **Step 6 — assemble the generator prompt** in this order, then produce ONE SQL statement (first statement only if several are emitted): 1. **System** — "Write one valid SQL statement for `` (ANSI_SQL + `` tweaks per dialect-rules.md). Output ONLY SQL. Prefer indexed/`recommended_filters` columns on large tables. Apply each column's `value_transform` when selecting/filtering it. **A `sensitive` column is the model author asking for care, not a locked door.** Prefer using it in `COUNT`/`COUNT(DISTINCT …)`, `GROUP BY`, `WHERE`, and `JOIN` over projecting its raw per-row values. So: (a) 'how many unique customer emails?' → `SELECT COUNT(DISTINCT email)` and report the count. (b) To disambiguate identical display labels (two customers with the same name), put the entity's **non-sensitive key (`id`)** in the output rather than the raw email/phone. (c) When the question genuinely needs the values — 'I need the mailing list' — project them and **say in the answer that you did**; the receipt records it too. Nothing refuses this, so the care is yours to exercise and yours to be transparent about. A column that must never be readable is not in the model at all, and any statement naming it is refused as out of scope. Use a metric's `bindings` SQL VERBATIM when the question names that metric (or a synonym)." 2. **Schema context** — the `get_table_context` output for the chosen tables (columns + types + caveats + value_transforms), the area's relationships (rendered as `from.col → to.col [cardinality]`), and the area's metrics (`: -- ` + synonyms). **`default_filters` ARE yours to apply.** Nothing applies them for you — but the receipt DOES report whether you did, per table reference, so an omission is visible to the user in the report beside the answer. If a table declares one and the question does not deliberately ask about the rows it excludes, write it into the `WHERE` clause. Two things to watch, because `get_table_context` has already rewritten them: the `{alias}` placeholder is gone — each filter comes back qualified with the **bare table name**, so re-qualify it to whatever alias you actually used, or the database rejects the statement. And a filter that still carries a `:param` marker (e.g. `orders.tenant_id = :tenant_id`) has no value to bind — leave it OUT and say so in the answer rather than emitting SQL that won't parse. Also DO honor any caveats. **Unreviewed metrics are USED, not refused.** When the question names a metric whose `review_state ≠ approved`, still use its binding and answer — do NOT block or refuse on it. The trust layer surfaces it **on the receipt**, not as a hard gate: the metric rides on the `receipt.columns.items` entry for the output column that computes it (`kind: "output"`, `status: "matched"`) carrying its own `review_state`, and the report's approve/change banner is driven off exactly that field (Phase 4e.iii.5). The loader already drops only `rejected` metrics; an `unreviewed`/`proposed` one is yours to use, with the surfaced review state carrying the honesty. (Same for unreviewed joins/entities and `stale` entries — surface, never refuse.) 3. **Datasource context** — `datasource.md` (step 1d.2), heading `## Datasource context`. Binding domain context. 4. **User memory** — `USER_MEMORY.md` (step 1d.1), heading `## User memory (preferences and policies)`. 5. **Few-shot examples** — the ranked matches from step 2. 6. **User question.** **The fan/chasm safety pass runs as a pre-execution step, on every tier.** Before you execute the generated SQL, pass it through `sm prepare "$ROOT" --area --sql-file ` (Phase 3a). It runs the fan-trap / chasm-trap pre-flight and the aggregation-semantics checks, and returns the SQL to actually run, which is always the SQL you gave it: this command never rewrites your statement and never refuses it. What it returns alongside is `findings` — see Phase 3a for how to act on them. Tier-independent — works whether you execute via psql, the Python driver, or DuckDB — so the checks never depend on the execution path. It does **not** apply the area's `default_filters`, and nothing else does either: those are declarative only, so put any you need into the statement yourself at step 6, and `sm prepare` emits no `applied_filters` key of its own (an always-empty list would read as "we checked, none applied"). Which ones your finished statement satisfied is settled later, by `sm receipt`, on `tables.items[].filters` (Phase 4e.iii.5). The findings DO ride on the receipt too, in its `aggregates` section, so the panel draws them beside the answer. #### 2b.federation — cross-database queries When the question references datasets from ≥ 2 different profiles (e.g., ITSM in Redshift × finance in MySQL), the skill routes the SQL through DuckDB, which ATTACHes both databases in one session and runs a native federated JOIN. **Detecting federation.** Extend Pass 1 of the two-pass retrieval to pick `(profile, schema, table)` tuples instead of just `(schema, table)`. If the picked set spans `len({tuple.profile}) > 1`, federation mode is on. For small databases (under 50 tables), build the union of every profile's index up front and run Pass 1 against that combined index — the picker then chooses across profiles automatically. For larger setups Pass 1 already runs; just include `profile` in each entry. The Pass 1 prompt loads `/local/cross_profile_relationships.yaml` (if present) so the picker knows about declared cross-profile JOIN paths. If the file is missing, the picker falls back to inferring relationships from column-name/type matching across profile indexes — best-effort, with a warning to the user that confidence is lower. **`/local/cross_profile_relationships.yaml`** (optional) — declares known JOIN paths across profiles: ```yaml version: "0.1.1" relationships: - name: itsm_assets_to_finance_cost_centers from_profile: itsm from_dataset: public.assets from_columns: [department_id] to_profile: finance to_dataset: dbo.cost_centers to_columns: [dept_id] description: ITSM assets carry the same dept_id as finance cost centers. ``` Loaded at session start the same way per-profile indexes are loaded. **Building the federated SQL.** When federation mode is active, the schema-context section of the prompt uses **three-part dataset names** matching the DuckDB ATTACH alias: `..` (e.g., `itsm.public.assets`, `finance.dbo.cost_centers`). Cross-profile relationships from `cross_profile_relationships.yaml` are rendered alongside per-profile relationships. The model produces SQL using these three-part names. **Verifying DuckDB is available.** Look up `tool_paths.duckdb` from `/local/.config`. If missing, surface: ``` Cross-database queries need DuckDB. Install it with `brew install duckdb` (or apt / download) and re-run. ``` …and stop. **Verifying credentials are set up for every profile.** For each profile in the picked set, check that the corresponding auth file exists (`/local/.pgpass`, `/local/.mysql.cnf`). Missing → run `python3 "$AGAMI_PLUGIN_ROOT/scripts/setup_pgauth.py" --profile ` for each gap, then re-check. **Generating the temp init file.** ```bash init_file=$(python3 "$AGAMI_PLUGIN_ROOT/scripts/build_duckdb_attach.py" \ --profiles "$P1" "$P2") # init_file is the path of a chmod-600 file in /local/.duckdb_init_*.sql. # Credentials are inside that file, NOT on the command line. ``` **Running the SQL.** ```bash duckdb -init "$init_file" -c "$FEDERATED_SQL" --csv ``` The visible Bash command shows only the path — DuckDB reads the ATTACH credentials silently from the init file. **Tear-down.** After the query completes (success or failure), delete the init file: ```bash rm -f "$init_file" ``` The next invocation also self-cleans any `.duckdb_init_*.sql` older than 1 hour in case a prior run crashed: ```bash find "/local" -maxdepth 1 -name '.duckdb_init_*.sql' -mmin +60 -delete 2>/dev/null ``` **Performance warning.** Federated joins through DuckDB scanners are bounded by network round-trips. If both sides of the join estimate to > 100k rows, surface a one-liner before running: > This federated query may take 30–120s (network round-trips for `` × ``). Want to tighten the filter first? Options: `Run anyway (Recommended for one-off)` / `Let me add a filter` / `Cancel`. **Type alignment.** Postgres `numeric(10,2)` joined with MySQL `decimal(10,2)` works. Mismatched types (date vs string, integer vs uuid) need an explicit `CAST` in the generated SQL. The Phase 2b prompt instructs the LLM about this: > When joining across profiles, prefer explicit `CAST(AS )` for any pair where the types might differ (e.g., timestamps stored as strings on one side, dates on the other). **No Snowflake federation.** DuckDB's `snowflake_scanner` is experimental and not packaged with the standard binary. If a profile in the picked set has `db_type=snowflake`, `build_duckdb_attach.py` exits with a clear error: surface it to the user and suggest pre-aggregating one side as a CSV. ### 2c — safety checks Apply [`shared/sql-generation-rules.md`](../../shared/sql-generation-rules.md): - **No DDL/DML.** Refuse on `DROP`, `DELETE`, `INSERT`, `UPDATE`, `ALTER`, `TRUNCATE`, `CREATE`, `GRANT`, `REVOKE`. Regenerate with explicit "SELECT only" framing. - **No system tables.** Refuse on `pg_catalog`, `information_schema`, `mysql.*`, `sys.*` unless the user is explicitly asking about schema metadata. - **NULL-safe division** via `NULLIF(denominator, 0)`. - **`agami.type` consistency** — if the SQL applies a numeric aggregate (`SUM`, `AVG`) to a field whose `agami.type` is `string` or `boolean`, refuse and regenerate. Type info exists for a reason. ### 2d — risk assessment + time estimate for large tables For each dataset touched by the SQL, look up its `agami.performance_hints`: `recommended_filters` is a **list of column names** (introspection seeds it with a large table's date/time columns — the columns worth filtering on to avoid a full scan). Check whether the generated SQL's WHERE filters on **any** of them. - `estimated_row_count > 1_000_000` AND the WHERE filters on **none** of the table's `recommended_filters`: → **HIGH risk**. Surface a banner before executing — **name a suggested column** when one exists: "This query scans `` (~) with no filter on ``. Estimated time: . Narrow it — e.g. a date range on `` — or proceed anyway?" AskUserQuestion: `Add a filter` / `Proceed anyway` / `Cancel`. If `recommended_filters` is **empty** (no known good filter for this table), use the generic "…without a filter. Add one, or proceed?" wording. - `100k–1M` rows with no filter on a `recommended_filters` column → **MEDIUM**. Note in response footer; proceed. - A query that **does** filter on a `recommended_filters` column → treat as narrowed: drop a risk tier (don't HIGH-warn just because the table is big). Otherwise → **LOW**. Proceed silently. **Time estimate (announced BEFORE Phase 3 execution).** Long-running queries kill the user's confidence — they don't know if the skill is hung or actually working. Before running any non-LOW query, surface a one-liner with the rough wall-clock estimate so they can wait without anxiety: ``` Running this against ~12M rows in — estimated 30–90s. I'll narrate when results land. ``` Estimation table (rough, calibrated to common Postgres / Snowflake shapes — adjust as needed from the latency log over time): | Largest scanned dataset | With indexed filter (`WHERE` matches `agami.performance_hints.indexes`) | Without indexed filter (full scan) | |---|---|---| | < 100k rows | < 1s | < 2s | | 100k–1M | 1–5s | 5–30s | | 1M–10M | 5–15s | 30–120s | | 10M–100M | 15–60s | **2–10 min** — ALWAYS warn even if filter is present | | > 100M | 30–120s | **> 10 min** — block as HIGH risk; offer to add filter or sample | Snowflake-specific: add 5–30s on top of any estimate for warehouse spin-up if the warehouse has been idle (the query log can detect "first query in this session" → assume cold). Federation (Phase 2b.federation) doubles or triples estimates due to network round-trips — surface "this federated query may take 30–120s" before running, regardless of estimated_row_count. If the estimate exceeds 30s, also surface: "Cancel anytime — Ctrl+C in CLI, or just send another message." The user should know they're not trapped waiting. --- ## Phase 3: Execute ### HARD RULE — never paste raw output in chat The Bash result (CSV stdout, stderr, exit code) is for the skill to parse, not for the user to read. **Never paste the raw CSV / TSV from the Bash result into the assistant's response text.** No "Here's what came back: …", no markdown code-fence dumps of the result. Parse internally, then surface the polished output per Phase 4. The host shows the Bash tool call as a collapsible — that's enough provenance for users who want to dig. ### 3a — safety pass, then run the SQL **Step 1 — prepare (every tier).** Write the generated SQL to a temp file, then run the tier-independent safety pass: ```bash bash "$AGAMI_PLUGIN_ROOT/scripts/sm" prepare "$ROOT" --area --sql-file /tmp/agami-q.sql ``` It returns JSON: `{sql, findings, units}`, and it **always exits 0**. The returned `sql` is the statement you handed in, byte for byte — this command never rewrites and never refuses. (`units` is the `{output_column: unit}` map traced through that statement — keep it for the table render in 4d.) `findings` is a list, usually empty. Each entry is `{risk, reason, triggering_joins}` and each one is a **fact about the statement**, not a verdict on it: - `fan_trap` / `chasm_trap` — a join multiplies the rows an aggregate is computed from. `triggering_joins` names which join does it. - `fan_out_invariant` — the same multiplication, on an aggregate it cannot move (`MIN`, `MAX`, a `DISTINCT` one, `BOOL_AND` / `BOOL_OR`). The rows were duplicated; the number is the same either way. `triggering_joins` names the join, exactly as above. - `bad_aggregation` — a `SUM` of a rate or an identifier, or an `AVG` of one. - `semi_additive` — a `SUM` of a balance across a time grain, which multiplies a stock. **A finding is not a refusal, and it is not automatically a bug.** Whether a multiplied total is wrong depends on what was asked: the same statement is wrong for order revenue and right for line-item exposure. The pre-flight does not have the question. You do. So this is the point where the judgement gets made, out loud, by you: - **The finding is not what the user meant** — restructure and re-prepare. Typically: pre-aggregate each measure in its own CTE and outer-join them (chasm), move the aggregate into a window function to keep raw rows, or drop a join the query does not otherwise use (fan, where the many side is touched nowhere but the `ON` clause). **Say in the answer** that you restructured to avoid a ``. The restructured statement carries no finding, so the receipt says nothing about it — that sentence is yours to write, and it is what separates this from a guard silently swapping the statement underneath the user. - **The finding IS what the user meant** — run it and say so. "This counts each order once per line item, which is what 'line-item exposure' asks for." A correct answer to the question asked is not a defect, and refusing it would have been the wrong call. - **You cannot tell which** — the fan-out join also filters or groups, so the candidate readings return *different numbers* (e.g. "loans with ≥1 payment since January" versus a payment-weighted total). Do **not** pick one. Ask: a short "Did you mean…?" with 2–3 concrete interpretations, one plain-language sentence each, no SQL. Generate for the one they choose. Either way the finding rides on the answer's receipt, in the `aggregates` section, so a user reading the result can see what was found without taking your word for it. **An empty `findings` is not by itself a clean bill of health.** Check `unchecked` alongside it: it is `null` when the checks ran, and a sentence when they could not — sqlglot missing, the statement unparseable, or no SELECT in it. All three yield the same empty list a genuinely clean statement does, so a non-null `unchecked` means you have learned nothing about this statement's aggregates and should say so rather than implying it passed. Even when `unchecked` is null, "found nothing" is bounded. The receipt's `aggregates` marker states what the checks do not reach — an aggregate inside a CTE or a subquery, one in `HAVING` or `ORDER BY` — and you should surface that marker rather than presenting an empty section as clean. **Step 2 — execute the returned `sql`** via the tier's tool from [`shared/connection-reference.md → CLI Connection Commands`](../../shared/connection-reference.md#cli-connection-commands) — psql / mysql / snowsql / sqlite3 / DuckDB, or the Python driver. (If you use `python -m execute_sql`, do **NOT** pass `--no-safety`. `sm prepare` runs the fan/chasm and aggregation checks, which report; it does **not** run the table-scope, `SELECT *` or column-scope gates, which refuse. Those are the enforcement, they live only in `execute_sql`, and skipping them lets a hallucinated table or column reach the warehouse. The checks that would be doubled are cheap; the gates that would be skipped are not optional.) Wrap in a high-resolution timer; capture stdout (CSV rows), stderr (errors), exit code. Route a non-zero exit through the error classifier (Phase 3b). ### 3b — error handling + auto-retry Route any non-zero exit through [`shared/db_error_classifier.md`](../../shared/db_error_classifier.md). Behavior per kind: | `error_kind` | Behavior | |---|---| | `auth`, `dsn`, `network` | Stop. Surface the one-line remediation. No retry. | | `driver_missing` | Fall through to the next available method (native CLI → DuckDB → Python driver). | | `permission` | Stop. DB user lacks SELECT on the touched dataset. | | `column_not_found`, `table_not_found`, `syntax` | Auto-retry up to **2** times. Pass the error back to the SQL generator: "The previous SQL failed with ``. Regenerate using only table / column names from the schema context above." | | `other` | Stop. Surface raw error truncated to 200 chars. | A statement stopped for taking too long no longer arrives here as an error. A per-statement deadline is a **`resource_limit` refusal**, which carries its own remediation because it is a decision the server made — handle it as a refusal, not as a classified failure. The `timeout` failure kind now has one producer, the supervisor stopping an executor that never responded, and there is nothing query-specific to suggest for it. After 2 retries with no success, stop. Don't loop. ### 3c — parse rows Parse the CSV stdout. Header row = column names. Body rows = data. **Sanitize column headers before display.** SQL aliasing slips happen — bare `n`, `cnt`, single-letter columns, `?column?` (Postgres unaliased), and shouty all-uppercase Snowflake name …(truncated)