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 <artifacts_dir>/<profile>/, 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 <artifacts_dir>/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 (layout) + packages/agami-core/src/semantic_model/models.py. For SQL safety: shared/sql-generation-rules.md. For dialect-specific syntax: shared/dialect-rules.md. For connection method + execution: shared/connection-reference.md. For DB error classification: shared/db_error_classifier.md. For chart template: shared/chart-template.html.

Invocation conventions

Read 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. 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 <path>. 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 <artifacts_dir>/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 <artifacts_dir>/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 <tool> for a database tool on PATH and python3 -c 'import <module>' for a Python driver.
4. NEVER put the password (or any credential field) in a Bash command line. That includes export PGPASSWORD='<value>', export MYSQL_PWD='<value>', psql -W <password>, mysql -p<password>, 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). For native CLI queries the visible Bash command is PGPASSFILE=<artifacts_dir>/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 <artifacts_dir>/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 <profile>: AGAMI_PROFILE → active_profile in <artifacts_dir>/local/.config → "main". Resolve <artifacts_dir> per shared/file-layout.md: AGAMI_ARTIFACTS_DIR → .config.artifacts_dir → $HOME/agami-artifacts.

The model is the semantic-model tree at <artifacts_dir>/<profile>/ (datasource.yaml + subject_areas/<area>/…). 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.)

ROOT="<artifacts_dir>/<profile>"
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 <entity>, 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 <artifacts_dir>/<profile>/prompt_examples/<area>/examples.yaml. Use cli examples "$ROOT" --area <area> --query "<question>" 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 <artifacts_dir>/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 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.

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 <artifacts_dir>/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. 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="<artifacts_dir>/local/.pgpass" psql -h <host> -U <user> -d <db> -c 'SELECT 1' --csv
cli (mysql)	mysql --defaults-file="<artifacts_dir>/local/.mysql.cnf" --defaults-group-suffix="_<profile>" -e 'SELECT 1' --batch
cli (snowflake)	snowsql --config "<artifacts_dir>/local/.snowsql.cnf" -c "<profile>" -q 'SELECT 1' -o output_format=csv -o friendly=false
cli (sqlite)	sqlite3 -header -csv "<path>" '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="<profile>" "$PY" -m execute_sql --sql 'SELECT 1'

The Python tier's CLI is --sql <string> or --sql-file <path> — those are the only two ways to pass SQL. Optional flag: --profile <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. Common cases:

• auth / dsn → credentials may have rotated; point at <artifacts_dir>/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.

---

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 <artifacts_dir>/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 <path> (macOS), xdg-open <path> (Linux), or start <path> (Windows). Surface a one-liner in chat:

     Reopened: <artifacts_dir>/local/charts/<profile>/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 <artifacts_dir>/local/charts/<profile>/) → surface: "The chart file is gone — <path> 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 <area> --query "<question>". 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 <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 <DB_TYPE> (ANSI_SQL + <DB_TYPE> 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 (<name>: <binding> -- <calculation> + 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.