agami connect

Before suggesting any slash command in chat, read shared/invocation-conventions.md. Agami slash commands: /agami-connect, /agami-query, /agami-model, /agami-save-correction, /agami-reconcile. (/agami-model is also the trust-review surface — its Review tab absorbed the former /agami-review.) Never write the un-prefixed forms (/init, /connect, etc.) or colon forms (/agami:connect) — those don't exist. For chat replies, prefer natural language ("say 'reload the schema'", "say 'introspect my database'") — the when_to_use matcher routes correctly without an explicit slash command.

You are setting up the agami semantic model for the user's database. Goal: by the end there is a validated semantic model at <artifacts_dir>/<profile>/ (datasource.yaml + subject_areas/<area>/… + datasources/<connection>/storage.yaml), a seeded examples library at <artifacts_dir>/<profile>/prompt_examples/<area>/examples.yaml, an datasource.md the user can edit, and the user has seen one demo query execute end-to-end.

The structural model is built by a deterministic engine, not hand-authored. bash "$AGAMI_PLUGIN_ROOT/scripts/sm" introspect introspects the live DB across all supported dialects — PostgreSQL (incl. Supabase / Redshift), MySQL/MariaDB, Snowflake, BigQuery, SQL Server, Oracle, Databricks, Trino/Presto, DuckDB, SQLite — into the model: storage connection, proposed subject areas, tables, columns + types, primary-key grain, foreign-key relationships with join cardinality, column_groups on wide tables, and sensitive flags on PII. When the catalog (information_schema / PRAGMA / data-dictionary) is reachable it runs in catalog mode; when a locked-down role denies the catalog it falls back per-capability to probe mode (describe via a zero-row header, infer types from a value sample, grain from uniqueness probes, FKs from name+overlap) and everything inferred lands unreviewed for sign-off. Your job is the layer the engine can't do: enrichment (prose descriptions, entities, metrics, caveats) and curation (subject-area boundaries, trust review).

For the model format: semantic_model/__init__.py (layout) and the Pydantic models in packages/agami-core/src/semantic_model/models.py. For credentials: shared/credentials-format.md. For connection method + local execution: shared/connection-reference.md. For DB error classification: shared/db_error_classifier.md.

Conversation style

• Combine acknowledge + next question — don't waste turns on "Got it!"
• Use AskUserQuestion for every Yes/No/Skip — never inline-bullet options. Use (Recommended) only when there's a genuine recommendation. For fact-of-environment questions ("which database type?", "which schemas?"), don't mark any option Recommended — the user picks what they have.
• Every multiSelect needs an explicit "none / continue" option — AskUserQuestion cannot be submitted with zero boxes checked, so any multiSelect where "pick nothing" is a valid answer MUST offer a selectable "Nothing — continue" (or "Keep everything") option. Never phrase the prompt as "leave all unchecked" — that's unsubmittable and traps the user.
• Keep the user oriented — print one-line progress markers between phases (✓ Introspected 12 tables, ✓ Validator passed, ✓ Generated 10 examples).
• Plain voice — state what happened, never how impressive it is. Progress markers, todo labels, and narration describe the action and the result; they do NOT editorialize. Banned: "wow moment", "magic", "watch this", "the exciting part", "you'll love this", exclamation hype. The reader is a data professional — a query running correctly speaks for itself. Say "Running the first query", not "Now the wow moment".

Progress tracking — set up a todo list at the very start

This is a multi-phase skill that often takes 5–15 minutes end-to-end. The very first action on every invocation is to call TodoWrite with the skill's major phases, so the user can watch progress. Validated as a strong UX signal — it makes the wait feel intentional rather than opaque.

Seed (one task per major phase, in order):

1. Preflight: credentials check + tool detection
2. Organization context (MANDATORY): first onboard → company name + description (root record via `sm set-org`); every onboard → this-database narrative
3. Discover & prune: list tables + columns, user prunes what they don't need
4. Introspect the KEPT tables → semantic model (engine: grain, FK cardinality)
5. Enrich: descriptions, entities, metrics (LLM, validated into the model)
6. Curate before examples: exclude columns/tables + sign off metrics & entities
7. Generate seed NL→SQL examples (validated against the live DB)
8. Validate every seed example (user reviews via dashboard)
9. Post-introspect trust summary
10. Follow-up suggestions

Use content for the imperative form and activeForm for the present-continuous form. Mark each todo in_progress when its phase starts and completed immediately when it ends. Exactly one in_progress at a time.

Skip the seeding if the todo list already contains these items (the skill is resuming after Phase 0 wrote the credentials template and waited). When $ARGUMENTS == reintrospect, the same todos apply.

---

Phase −1: Plan-mode check

Run the detection + ask logic from shared/plan-mode-check.md. agami-connect needs Bash (introspection) and Write (model files) — both blocked in plan mode.

If plan mode is active and the user stays in plan mode (or the skill is invoked under plan mode with no prompt): refuse with the one-liner below and end the turn. DO NOT write a plan file. DO NOT call ExitPlanMode.

I can't introspect in plan mode — switch to Auto or Edit Automatically mode (Shift+Tab to cycle) and re-invoke me. Introspection, enrichment, and the demo query all need write access to <artifacts_dir>/<profile>/.

If plan mode is not active, skip silently.

---

Phase 0: Preflight

HARD RULES — read before doing anything

Non-negotiable. They override every other instruction here when they conflict.

1. Connect ONLY to the host/port/database/user/password in <artifacts_dir>/local/credentials. That file is the sole credential source — there is no env-var bypass. Never connect to anything else. Never probe localhost unless the credentials say so. Never substitute defaults for missing fields.
2. Never ask the user for connection values (host / port / user / password / token / DSN) in chat. Not even temporarily. The single authorized credential path is Phase 0a, which writes a credentials.example template the user fills in and saves. Phase 0a never reads secrets inline — it writes a template, surfaces a hand-off, and ends the turn.
3. Never scan or guess. No pgrep, ps, lsof, find /, ls /Applications, no port-listener scans, no testing connections to common hostnames. The only acceptable Bash probes here are which <tool> and python3 -c 'import <module>'.
4. If credentials are missing for the active profile, run Phase 0a. After the user fills in the template they re-invoke (or just ask a data question — agami-query auto-invokes us).
5. NEVER put a credential on a Bash command line — no export PGPASSWORD=…, no psql -W <pw>, no heredoc that interpolates a secret. Hosts render Bash calls in chat; anything on the line leaks. Runtime queries use the auth files from scripts/setup_pgauth.py (psql/mysql) or python -m execute_sql (every driver, reads <artifacts_dir>/local/credentials itself). See shared/connection-reference.md → HARD RULES.

If you reach for a command that doesn't fit, stop and re-read this section.

Preflight steps

0. Sample-path short-circuit (check FIRST, before resolving any profile). If the user explicitly asked for the sample — $ARGUMENTS is sample, or they said "try the sample" / "I don't have a database" / "use the sample (or example) data" / "demo data" — then bind $PROFILE_NAME = agami-example, $DB_TYPE = sample, and go straight to Phase 0s. Do this regardless of any existing active_profile or onboarded profiles — the sample is a deliberate, explicit choice and must not be intercepted by another profile's credential/existing-model checks. (This is why the option in 0a.2 isn't enough on its own: a returning user with an onboarded profile never reaches 0a.) If agami-example is already set up, Phase 0s sends them to the demo. Only fall through to step 1 when there's no sample signal.
1. Resolve the environment — python3 "$AGAMI_PLUGIN_ROOT/scripts/connect_resolve.py" [--db-type <t>] [--profile <p>] prints {data, anomalies} (self-describing JSON): profile, artifacts_dir, the [<profile>] credentials section + chmod, the cached config, the scored interpreter.python3 (the one with model deps + driver — use it everywhere; never a Python missing a dep), native tools, and a next decision. Branch on data.next: ready → continue (Phase 1 / existing-model check); promote → run 0a.10 to promote the filled template, then continue; bootstrap → run Phase 0a and stop. On a first-time bootstrap data.profile is null (profile_source: "default") — there's no profile yet, so narrate it without a name: "No credentials yet — first-time setup." (Never say "profile main" — the user names their profile in 0a.3.) Only when data.profile is non-null (a bootstrap for an explicitly-named/env profile) may you name it. Surface anomalies (e.g. credentials_world_readable → offer chmod 600); never substitute a missing_fields value — surface "missing field X for profile Y" and stop.
2. Update-check (best-effort). Run the probe from shared/version-check.md; surface a one-liner if a newer version exists. Never block on network failure.
3. If $ARGUMENTS is reintrospect: re-introspect from scratch, but preserve hand-edits (descriptions, entities, metrics, caveats, trust sign-offs). The engine writes the structural skeleton; merge it over the existing enrichment rather than discarding it (see Phase 2's reintrospect note).

---

Phase 0a: First-time credential bootstrap

Runs only when preflight step 1 returns next: bootstrap (no credentials for the profile). If <artifacts_dir>/local/credentials already has the [<profile>] section, skip Phase 0a entirely.

0a.1 — Set up <artifacts_dir>/local/

mkdir -p "<artifacts_dir>/local" && chmod 700 "<artifacts_dir>/local"

If the user chose a non-default <artifacts_dir>, persist the pointer so future sessions find it, and gitignore local/:

mkdir -p ~/.config/agami && printf '%s\n' "<artifacts_dir>" > ~/.config/agami/path
grep -qxF 'local/' "<artifacts_dir>/.gitignore" 2>/dev/null || printf 'local/\n' >> "<artifacts_dir>/.gitignore"

0a.2 — Ask the database type

AskUserQuestion (no (Recommended) — fact-of-environment). The first option is always the no-database sample path; cap the rest at 4 visible + Other.

Name the Other-only engines in the QUESTION PROMPT so they're visibly supported — the auto-provided "Other" field can't carry its own description/examples, so the examples must live in the prompt. End the prompt with a line like:

Something else — BigQuery, SQL Server, Oracle, Databricks, Trino/Presto, DuckDB, or SQLite? Choose Other and type it (a name or a full DSN).

label	description
Try a sample database — no connection needed	Don't have a database handy (or not ready to connect one)? agami ships a small Acme Store SQLite dataset (commerce + subscriptions) with a ready-made model — query it in under a minute, nothing leaves your machine.
PostgreSQL	Postgres + compatible: Supabase, Neon, RDS, Aurora, Cloud SQL, Timescale, and Amazon Redshift (port 5439, SSL by default).
MySQL	MySQL, MariaDB, RDS MySQL, PlanetScale.
Snowflake	Snowflake. Account identifier instead of host.
Other (Other field)	BigQuery, SQL Server, Oracle, Databricks, Trino/Presto, DuckDB, SQLite, or paste any DSN.

Bind $DB_TYPE ∈ sample | postgres | mysql | snowflake | bigquery | sqlserver | oracle | databricks | trino | duckdb | sqlite | dsn.

Routing:

• Try a sample database (or the user says "I don't have a database" / "try the sample" / runs agami-connect sample) → bind $DB_TYPE = sample, set $PROFILE_NAME = agami-example, and jump straight to Phase 0s — skip the rest of 0a (no credential template, no hand-off turn; the connection is known).
• PostgreSQL → postgres; if the user later enters port 5439 or a *.redshift.*.amazonaws.com host, transparently re-bind to redshift. A *.pooler.supabase.com host stays postgres (Supabase is hosted Postgres).
• MySQL/Snowflake → pass-through. BigQuery lives under Other now (or the user types it) → bigquery.
• Other → parse the free-form input: a DSN scheme → derive db_type; .db/.sqlite/.duckdb suffix or absolute file path → SQLite or DuckDB; a named DB (bigquery, sqlserver/mssql, oracle, databricks, trino/presto, duckdb) → that dialect. Only refuse with "not supported yet" for engines outside the supported set above (e.g. MongoDB, Cassandra, ClickHouse).

0a.3 — Name the database profile (the user's choice)

Ask the user to name this connection — don't pick for them. The name is how they'll switch databases later (AGAMI_PROFILE=<name>) and it names the model folder (<artifacts_dir>/<name>/), so a name that means something to them — their database, product, team, or environment — beats a generic default.

AskUserQuestion, with the Other free-text as the encouraged path (that's where they type their own name):

What should I call this database? Pick a name you'll recognize when you connect more than one — e.g. your database or product name, or an environment.

Offer a few examples as options (prod, staging, analytics) but make clear in the prompt that typing their own in Other is the point — don't present a main default that nudges them past the choice. Bind $PROFILE_NAME to their answer (the Other text, or a picked example). Validate: lowercase letters/digits/dashes/underscores, 1–32 chars; and not already a [section] in <artifacts_dir>/local/credentials (a profile name is a unique key — reusing one would clash). If it fails either rule, show the reason and re-ask. (The 0a.10 promote helper enforces the uniqueness backstop too — it returns COLLISION rather than overwrite — but catch it here so the user isn't surprised later.)

0a.4 — Write <artifacts_dir>/local/credentials.example

Use the Write tool. Shared header first, then the $DB_TYPE body with [$PROFILE_NAME] as the section.

Header:

# <artifacts_dir>/local/credentials.example
# Fill in your values below, then come back and say "introspect my database".
# agami moves this file to <artifacts_dir>/local/credentials and chmod-600s it for you — no
# manual save or chmod needed. (Don't rename it yourself.)
# agami only runs read-only SELECT queries, so a read-only database user is all it needs
# (and the safest thing to connect). Copy-paste GRANT SQL for your database:
# plugins/agami/shared/readonly-grants.md — or ask agami for "the read-only grant".
# Format reference: plugins/agami/shared/credentials-format.md
# Switch profiles with AGAMI_PROFILE=<name>.

Bodies — postgres, redshift, snowflake, mysql, bigquery, sqlite are unchanged from shared/credentials-format.md (URL-form first for Postgres/MySQL/Redshift; account fields for Snowflake; project+service_account_path for BigQuery; path for SQLite). The new dialects:

# SQL Server / Azure SQL.
[$PROFILE_NAME]
type = sqlserver
host = your-server.database.windows.net
port = 1433
database = your-database
user = your-username
password = your-password

# Oracle.
[$PROFILE_NAME]
type = oracle
host = your-host.example.com
port = 1521
service_name = ORCLPDB1
user = your-username
password = your-password
# OR a full DSN:  dsn = host:1521/ORCLPDB1

# Databricks SQL warehouse.
[$PROFILE_NAME]
type = databricks
host = your-workspace.cloud.databricks.com
http_path = /sql/1.0/warehouses/abc123
token = dapiXXXXXXXXXXXX
# catalog = main      # optional Unity Catalog

# Trino / Presto.
[$PROFILE_NAME]
type = trino
host = your-coordinator.example.com
port = 8080
user = your-username
catalog = your_catalog
schema = your_schema
# password = ...       # uncomment for HTTPS + basic auth

# DuckDB (local file or in-memory).
[$PROFILE_NAME]
type = duckdb
path = /absolute/path/to/your.duckdb

Always finish with the additional-profiles hint (one commented block):

# Add more profiles by appending another [section]. Switch with AGAMI_PROFILE=<name>.
# [staging]
# type = postgres
# url  = postgresql://readonly:pass@staging-db.example.com:5432/mydb

For BigQuery / Databricks / any key-or-token file: remind the user to chmod 600 it.

0a.5 — Resolve the agami interpreter + detect tools

$PY = data.interpreter.python3 from the Phase-0 connect_resolve.py call — the ONE Python agami uses for the model and DB connections (introspection always runs python -m execute_sql under it on every tier, so the DB driver must live in it). It's already the scored pick — the candidate that has pydantic+sqlglot+pyyaml AND the $DB_TYPE driver — so there's no guessing and the user sets nothing. Re-run connect_resolve.py --db-type $DB_TYPE now that the DB type is known, so the interpreter is scored against the right driver and native tools are refreshed. It records as tool_paths.python3 in 0a.7. (AGAMI_PYTHON is honored as a first-priority override but is never required.)

Native CLIs (optional fast path for queries — introspection doesn't use them) are in data.tools (psql/mysql/snowsql/sqlite3/duckdb/bq, null if absent), detected with which only. Forbidden elsewhere: pgrep/ps/lsof/find //ls /Applications/port scans.

If data.interpreter.has_driver is false the $DB_TYPE driver isn't in $PY yet — confirm via AskUserQuestion, then "$PY" -m pip install --user <package> from the table (probe was already done in $PY by connect_resolve.py):

$DB_TYPE	probe ("$PY" -c '…')	pip package
postgres / redshift	import psycopg2	psycopg2-binary
mysql	import pymysql	pymysql
snowflake	import snowflake.connector	snowflake-connector-python
bigquery	import google.cloud.bigquery	google-cloud-bigquery
sqlserver	import pymssql	pymssql
oracle	import oracledb	oracledb
databricks	from databricks import sql	databricks-sql-connector
trino	import trino	trino
duckdb	import duckdb, pytz	duckdb pytz
sqlite	stdlib — always present	—

duckdb needs pytz to materialize TIMESTAMP WITH TIME ZONE values into Python — without it a query that selects a timestamptz column fails at runtime (Required module 'pytz' failed to import), so it's part of the driver install, not optional.

If the driver is missing, confirm via AskUserQuestion, then "$PY" -m pip install --user <package> (plain pip install fallback). Same "never install silently" convention as the model deps (0a.5b). Do this for $PY so sm introspect connects on the first try.

0a.5b — Ensure the semantic-model dependencies

The model (introspection, validation, traversal, curation — everything the sm wrapper drives) needs pydantic + sqlglot + pyyaml in the interpreter agami uses. Check the resolved interpreter ($AGAMI_PYTHON → .config tool_paths.python3 → python3):

"$PY" -c 'import pydantic, sqlglot, yaml' 2>/dev/null && echo "model deps OK"

If they're present, continue. If missing, confirm via AskUserQuestion before installing (same convention as the DB-driver install above — agami never installs silently):

agami needs the agami-core package (which pulls pydantic, sqlglot, pyyaml) to build and read the semantic model. Install it now? (one-time, user-site — pip install --user)

On Yes: bash "$AGAMI_PLUGIN_ROOT/scripts/sm" install — the sm launcher is the single place that installs the agami-core library into the resolved interpreter (editable from a dev checkout, else the published package via PyPI or pinned git — so it works in a marketplace install with no packages/ dir), and everything (python -m execute_sql / python -m semantic_model.cli / sm) then resolves it. On No: stop with "Can't build the model without it — re-run when you're ready to install." — don't proceed to introspect.

(The sm wrapper also self-installs these on first use as a safety net, but doing it here makes it explicit, confirmed, and at a predictable moment rather than mid-introspection.)

0a.6 — Ask for <artifacts_dir> (first run only)

The folder is chosen once, on the very first onboarding, and reused for every profile thereafter. Before asking, check whether it's already set: if the pointer ~/.config/agami/path exists (non-empty) or AGAMI_ARTIFACTS_DIR is set or <artifacts_dir>/local/.config already records artifacts_dir, skip this question entirely — reuse that path and continue to 0a.7. Only a brand-new install (none of those present) reaches the question below. This guarantees a second/third database lands as a sibling inside the same folder rather than re-prompting (and risking a fragmented, multi-folder setup).

Detect the OS once so the options are platform-native — uname -s (Darwin = macOS, Linux = Linux) or treat $OS == Windows_NT / a MINGW*/MSYS* uname as Windows. Then AskUserQuestion with the two defaults for that OS as named options (Recommended first). The auto-provided Other lets the user type any absolute path — so this both gives sensible options and allows a full custom path:

Where should agami save your semantic model, examples, and preferences? This is the parent for ALL profiles — each lands in <artifacts_dir>/<profile>/. It's non-secret (no credentials) — point it inside a git repo to share the tuned model with your team. Credentials stay in <artifacts_dir>/local/ regardless.

OS	Option 1 — Recommended	Option 2
macOS	~/agami-artifacts	~/Documents/agami-artifacts
Linux	~/agami-artifacts	~/Documents/agami-artifacts
Windows	%USERPROFILE%\agami-artifacts	%USERPROFILE%\Documents\agami-artifacts

(For Other, suggest a team repo path as the example, e.g. ~/code/acme-data/agami.) Expand ~ / %USERPROFILE% to an absolute path. Validate: absolute, not inside <artifacts_dir>/local/, parent creatable. Store the resolved absolute path in .config.artifacts_dir.