| name | agami-connect |
| description | End-to-end database connection for agami: sets up credentials on first run (DB-type picker → writes <artifacts_dir>/local/credentials.example for the user to fill in), then introspects the live DB directly into the agami semantic model (subject areas, tables, columns, relationships with join cardinality, deep-table column groups, sensitive-column flags) under <artifacts_dir>/<profile>/. The structural model is built deterministically by the agami-core semantic_model package (catalog mode, or a probe-mode fallback when the catalog is locked down); the skill then layers LLM enrichment (descriptions, entities, metrics) and seeds EXPLAIN-validated NL→SQL examples. Every model write is gated by the semantic-model validator — no breaking model is ever persisted. |
| when_to_use | Run when the user installs the plugin for the first time, asks 'how do I set up agami' / 'connect to my database' / 'introspect my database' / 'introspect the schema' / 'reload schema' / 'add a new database', wants to try agami WITHOUT a database ('I don't have a database', 'try the sample', 'use the sample data', 'demo data'), or after the user changes their schema and wants the model refreshed. Also auto-invoked by agami-query the first time it runs (when the semantic model is missing). This skill handles credential setup, introspection, enrichment, and seed-example validation — one entry point for everything before the user can query. The sample-database path (Phase 0s) needs no connection and lands a queryable model in under a minute. |
| argument-hint | [sample | reintrospect | profile NAME] |
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.
- 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.
- 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.
- 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>'.
- 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).
- 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
- 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.
- 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.
- 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.
- 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:
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:
[$PROFILE_NAME]
type = sqlserver
host = your-server.database.windows.net
port = 1433
database = your-database
user = your-username
password = your-password
[$PROFILE_NAME]
type = oracle
host = your-host.example.com
port = 1521
service_name = ORCLPDB1
user = your-username
password = your-password
[$PROFILE_NAME]
type = databricks
host = your-workspace.cloud.databricks.com
http_path = /sql/1.0/warehouses/abc123
token = dapiXXXXXXXXXXXX
[$PROFILE_NAME]
type = trino
host = your-coordinator.example.com
port = 8080
user = your-username
catalog = your_catalog
schema = your_schema
[$PROFILE_NAME]
type = duckdb
path = /absolute/path/to/your.duckdb
Always finish with the additional-profiles hint (one commented block):
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.
0a.7 — Write <artifacts_dir>/local/.config
{
"schema_version": 1,
"tier": "<cli | duckdb | python>",
"active_profile": "$PROFILE_NAME",
"artifacts_dir": "<resolved absolute path>",
"tool_paths": { "psql": "...", "python3": "$PY" },
"detected_at": "<ISO8601 UTC from `date -u +%Y-%m-%dT%H:%M:%SZ`>"
}
python3 MUST be the $PY resolved in 0a.5 (the interpreter that has both the model deps and the DB driver) — sm and the introspection engine read it from here, so recording the wrong one reintroduces the interpreter mismatch. chmod 600 <artifacts_dir>/local/.config.
0a.8 — Seed <artifacts_dir>/USER_MEMORY.md if missing
Create the parent (mkdir -p && chmod 755) and write the default seed (per shared/user-memory-format.md), chmod 644. Don't overwrite. Migrate a v1.1 <artifacts_dir>/local/USER_MEMORY.md if present.
0a.9 — Hand-off + END THE TURN
✓ <artifacts_dir>/local/ ready (chmod 700)
✓ Credentials template → <artifacts_dir>/local/credentials.example
✓ Tool detected: <tool> (<tier>)
✓ Artifacts dir: <resolved path>
Next:
1. Open <artifacts_dir>/local/credentials.example and fill in your real connection details
(keep the filename as-is — don't rename it).
2. Come back and say "introspect my database" — I'll secure the file and run the
full introspect → enrich → seed flow.
Tip: agami only runs read-only queries, so a read-only database user is all it needs
(and safest). Want the exact GRANT SQL for your database? Just ask.
Heads-up: a cold cloud warehouse (Snowflake especially) makes introspect the slow
step — ~5–15 min for a sizable account. Postgres / MySQL are seconds.
End the turn. Do NOT continue to Phase 1.
Show the read-only Tip line only for dialects with a user/role concept (postgres,
redshift, mysql, snowflake, sqlserver, oracle, databricks, trino); omit it for
sqlite / duckdb (file-based, no user) and when BigQuery auth is ADC. If the user asks for the
grant, read shared/readonly-grants.md, pick the $DB_TYPE
block, and fill in whatever <…> placeholders it uses (e.g. <db> / <schema> / <warehouse> /
<catalog> / <project> / <dataset>) from the values they entered.
0a.10 — On re-entry: promote the filled-in template, then continue
The user filled in <artifacts_dir>/local/credentials.example and came back (or asked a data question / said "introspect my database"). Promote it deterministically with the helper — do NOT hand-roll an mv/append, and do NOT assume "no file yet." The script handles all four cases (first profile → move; Nth profile → append; name clash → refuse; placeholders → refuse), so the second-profile and [main]/[main] cases can't silently corrupt the file:
python3 "$AGAMI_PLUGIN_ROOT/scripts/promote_credentials.py"
Read the first token of stdout and act:
SECURED <profile> → credentials created from the template (chmod 600, .example consumed). Continue.APPENDED <profile> → the new profile was appended to an existing credentials file (other profiles untouched, chmod 600). Continue.COLLISION <profile> → a profile by that name already exists; nothing was changed. Tell the user "You already have a profile named <profile> — pick a different name for this database," go back to 0a.3 to rename (then re-write the template in 0a.4 under the new name), and stop. Never overwrite or duplicate an existing profile.PLACEHOLDERS_REMAIN <fields> → the template still holds template values; tell the user which fields to fill and stop (never introspect against a template).NOTHING → no credentials.example to promote (already promoted, or never written) — fall back to the preflight decision.
($AGAMI_PLUGIN_ROOT is the plugin root; promote_credentials.py is stdlib-only and needs no special interpreter.)
Run setup_pgauth.py --all before the first native-CLI query (writes .pgpass / .mysql.cnf so passwords never hit the command line). Idempotent. Then continue to Phase 1.
Phase 0s: Sample-database bootstrap (no connection)
Runs only when the user chose Try a sample database in 0a.2 (or said "I don't have a database" / "try the sample" / ran agami-connect sample). This replaces Phases 0a, 1, and 2 with a short deterministic wire-up — no credential template, no hand-off turn, no live introspection on the fast path. The committed sample dataset + its prebuilt model live at $AGAMI_PLUGIN_ROOT/samples/store/ (seed.sql, build_sample.py, model/). Profile is always agami-example.
If <artifacts_dir>/agami-example/datasource.yaml already exists, the sample is already set up → skip to the demo query (step 7), or treat reintrospect as the 6B rebuild.
Todo seed (use these instead of the 9-phase introspect seed). The copy path (6A) skips introspect/enrich/seed entirely (it copies a pre-built model); the rebuild path (6B) does run introspect → enrich → seed, but with the sample carve-outs spelled out in 6B (no prune/org/doc prompts). Keep labels plain and user-facing — describe the action, not how good it is; no "wow"/"magic"/sales framing:
1. Set up the sample database
2. Build the local database file
3. Configure the sample profile
4. Load the semantic model
5. Run a first query
-
Set up local/ (same as 0a.1): mkdir -p "<artifacts_dir>/local" && chmod 700 "<artifacts_dir>/local". Ensure <artifacts_dir>/.gitignore ignores local/. Resolve <artifacts_dir> per Phase 0 (first run: ask via 0a.6, default ~/agami-artifacts).
-
Resolve $PY + the agami-core package (trimmed 0a.5/0a.5b): SQLite needs no DB driver (stdlib), so only ensure the agami-core package is importable in $PY — confirm via AskUserQuestion, then bash "$AGAMI_PLUGIN_ROOT/scripts/sm" install (the one, one-time install via the sm launcher; brings pydantic/sqlglot/pyyaml and works with no packages/ dir). Detect the sqlite3 CLI with which sqlite3 → tier = cli if present else python.
-
Build the .db into the gitignored local/ (it's regenerable machine state, not committed):
"$PY" "$AGAMI_PLUGIN_ROOT/samples/store/build_sample.py" --out "<artifacts_dir>/local/samples/store.db"
Uses the sqlite3 CLI if present, else the stdlib builder. ~seconds; prints the size.
-
Write the [agami-example] credential by reusing the deterministic promoter — don't hand-roll the INI. Write a credentials.example whose [agami-example] section has the resolved absolute path from step 3, then promote it:
[agami-example]
type = sqlite
path = <resolved abs path to local/samples/store.db>
python3 "$AGAMI_PLUGIN_ROOT/scripts/promote_credentials.py"
SECURED/APPENDED → chmod 600, collision-safe. (No placeholders → never trips PLACEHOLDERS_REMAIN.)
-
Write <artifacts_dir>/local/.config (same shape as 0a.7): active_profile: agami-example, artifacts_dir, tool_paths.python3 = $PY (+ sqlite3 if found), tier. chmod 600.
-
Fork — copy the ready-made model, or rebuild it live? (AskUserQuestion). Ask once, before touching <artifacts_dir>/agami-example/:
The sample comes with a ready-made semantic model. Want to query it right away, or watch agami build that model from the data first?
| option | branch |
|---|
Just let me query it (Recommended) | 6A — copy |
Build the model from scratch so I can see it work | 6B — rebuild live (~5–10 min) |
- 6A (copy, < 1 min):
mkdir -p "<artifacts_dir>/agami-example" then cp -R "$AGAMI_PLUGIN_ROOT/samples/store/model/." "<artifacts_dir>/agami-example/". Validate it loads here: bash "$AGAMI_PLUGIN_ROOT/scripts/sm" validate "<artifacts_dir>/agami-example". If it fails, surface the errors and stop — never leave a half-wired profile. Then mint the deployment org_id (a copy carries no id — introspect mints inline, a copy bypasses it): bash "$AGAMI_PLUGIN_ROOT/scripts/sm" ensure-org-id "<artifacts_dir>/agami-example" — idempotent, writes a locally-minted uuid into datasource.yaml so the sample deployment has a portable identity like any other. Then stamp a model_version (a copy doesn't go through introspect/curate, so nothing auto-stamps it): bash "$AGAMI_PLUGIN_ROOT/scripts/sm" snapshot "<artifacts_dir>/agami-example" — best-effort, so the answer receipt shows a version rather than null. (We stamp at copy time instead of committing a static .snapshots/ so it always matches the model's actual content; 6B gets one automatically from introspect.)
- 6B (rebuild live — "watch it build"): ignore the committed
model/ and run the normal Phases 1→2 against the agami-example profile (--db-type sqlite) — the same introspect → enrich → seed pipeline a real onboarding uses, just pointed at the sample SQLite file. It takes a few minutes (the non-default option). Don't mention tokens, cost, or billing — surface time (~5–10 min), not scary money words.
- Sample carve-outs — the dataset is small + curated, so DON'T prompt (build silently over ALL tables): skip the Phase 1.6 prune page, skip the org-description prompt (Phase 2f / 0a), and skip the doc/metrics intake (Phase 1's "do you have a data dictionary / dbt repo?"). These prompts exist for a real unknown DB; for the sample they're noise. Introspect + enrich every sample table without asking.
- Clear the pre-seed gate before seeding (the silent build has no human to sign off). Enrichment lands the sample's metrics/entities
unreviewed, so seed-examples would refuse with preseed_review_pending. Because the sample is curated and trusted, auto-approve the queue as a system signer right before seeding — the silent path clears its own gate:
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" approve-queue "<artifacts_dir>/agami-example" --signer system --role system
(This is only sanctioned for the curated sample; a real database keeps the human sign-off gate.)
- When the model validates, OPEN THE MODEL-EXPLORER so the user sees what was built — render it in browse mode (i.e.
/agami-model browse — not the /agami-model preseed sign-off gate that ends the turn elsewhere in this skill). The script takes the profile and artifacts dir as separate flags (there is no --root); use the same invocation the /agami-model skill documents:
python3 "$AGAMI_PLUGIN_ROOT/scripts/render_model_explorer.py" \
--profile agami-example --artifacts-dir "<artifacts_dir>" \
--out "<artifacts_dir>/local/model/agami-example/<ts>.html"
This is the whole point of "watch it build"; a prose-only wrap would defeat it. Render it together with step 7's short dataset description + starter questions (so don't cede the turn), so the user can both look at the model and start asking. (Do not render the NL→SQL examples-validation page — lower-value for the curated sample.)
6A → step 7 (describe + stop). 6B → step 7's description + starter questions and open /agami-model. Both end with a validated <artifacts_dir>/agami-example/ model.
-
Wrap up — describe the dataset, offer questions, then STOP. Do NOT auto-run a query. This is the entire closing for the sample path: skip the rest of Phases 3–8 (no introspect summary, no "re-introspect <profile>" / "when you want the real thing" framing — that pushes the user off the sample they just picked and can surface another profile's name). The user asked to query the sample, not watch a scripted demo — so hand them the keys, don't drive. (Exception: 6B already opened /agami-model — that's the one review surface the "watch it build" path keeps; see 6B. The 6A copy path opens nothing.)
- Short description (2–3 lines, plain): what the dataset is, drawn from
<artifacts_dir>/agami-example/datasource.md — Acme Store, a retailer with one-time commerce (customers, products by category, orders, line items, payments, refunds) and recurring subscriptions (plans, subscriptions, invoices). ~500 customers, ~4,000 orders over ~2 years. State it; don't sell it.
- A numbered list of ~6 starter questions — and DO NOT answer any of them. The user picks. Include at least two genuinely complex multi-table joins so they see agami handle real joins, not just single-table counts (a flat list of trivial questions undersells it). A good set:
1. What's our revenue by product category? (line items → products → categories)
2. Which product categories drive the most revenue in each sales channel? (line items → products → categories + orders)
3. Who are the top 5 customers by total spend? (customers → orders)
4. Show the monthly revenue trend over the last 12 months.
5. How many customers have both an order and a subscription? (commerce ↔ subscriptions)
6. How many active subscriptions do we have, and what's MRR?
(Questions 1 and 2 are the multi-table joins. All six are backed by EXPLAIN-validated seed examples in the shipped model, so they answer reliably.)
- Then stop and wait. Nothing about agami-serve / agami-model / corrections yet — let them experience one real query in Claude Code first. When the user picks a number or asks anything, hand to agami-query — it answers AND runs its own first-query flow (the one-time GitHub-star ask +
/agami-serve pointer, gated by local/.optins). Do NOT pre-narrate fan/chasm traps or "what agami caught." If their question hits a trap (questions 1–3 can), agami-query surfaces it in the answer's receipt — let it happen on a real question instead of scripting it.
-
The "where to go deeper" footer (corrections / /agami-model / /agami-serve) fires from agami-query, not here. When the user asks their first sample question, agami-query owns the turn — so its Phase 4f surfaces a one-time orientation footer for the agami-example profile (see agami-query SKILL.md → Phase 4f). Don't try to print it from Phase 0s — a footer placed here never executes once agami-query has taken over. Phase 0s ends at step 7 (describe + questions + stop).
Phase 1: Introspect → semantic model
1.0 — Set expectations before kicking off
Introspection can take a while against cloud DBs. Tell the user before the first probe. Honest estimates — don't lowball (a user told "5 min" who waits 4 thinks "almost there"; one told "1 min" thinks "stuck").
| db_type | Typical | Why |
|---|
| sqlite / duckdb | < 5s | local file |
| postgres / mysql (local) | 5–15s | fast catalog |
| postgres / mysql (cloud) | 15–60s | network RTT per query + FK overlap checks |
| redshift / databricks / trino | ~30s for ≤20 tables; 10–30+ min for 50+ | unenforced FKs → the relationship phase confirms joins by value-overlap, one scan per candidate over the network, and big fact tables dominate. Scale your estimate with table count and warn explicitly. |
| snowflake | 5–15 min | cold-warehouse spin-up dominates; per-table queries, sample scans, EXPLAIN validation. A 100-table account measured ~12 min. |
| sqlserver / oracle | 30s–5 min | network + per-table catalog |
Set a HONEST estimate — understating it is what makes a working run read as "stuck." For a large schema (50+ tables) on Redshift/Databricks/Trino, say so up front: e.g. "This is ~50 tables on Redshift — the relationship phase confirms joins by value-overlap, so expect 10–30 minutes. I'll stream progress and report when it lands." Then stream the heartbeat (background + tail, per 1.7) so the user sees columns+grain 30/52 and relationships: declared FK 80/187 rather than silence. For reintrospect, prepend "Re-introspecting (about as long as initial setup)."
1.1 — Existing-model check
If <artifacts_dir>/<profile>/datasource.yaml exists and $ARGUMENTS != reintrospect: the profile is already onboarded. Offer (AskUserQuestion, no (Recommended) — these are equal-weight choices), capped at 4:
- Re-introspect
<profile> — refresh the structure from the live DB (new/changed tables, columns, FKs) while preserving descriptions, entities, metrics, caveats, and sign-offs (the reintrospect path).
- Open model explorer — browse + curate the existing model and review/sign off the trust layer (
/agami-model).
- Onboard another database — set up a different database (a different connection), leaving
<profile> untouched. On this choice, first ask whether it's the same company or a different one (see 1.1a), then start a fresh onboarding: jump to the profile-naming step (Phase 0a's naming question) → have the user name the new profile (must differ from <profile> and any existing [section] in <artifacts_dir>/local/credentials) and pick its DB type → write that profile's credentials.example → run the full flow for it. Never reuse or overwrite the current profile's credentials or model.
- Try the sample database — explore agami's bundled
agami-example sample (retail + subscriptions, no connection needed), leaving <profile> untouched. Routes to Phase 0s. This MUST be a real selectable option here (not a prose aside) — a returning user with an onboarded profile has no other visible path to the sample, since plain /agami-connect resolves their active profile and lands on this menu.
(Cancel isn't a listed option — the modal's Esc / "Other" covers "do nothing"; the four real choices are the actions above. Keep the list at exactly these four.)
Same DB, another schema? That's the Re-introspect path, not "Onboard another database." If the user wants to add a schema that lives in the same database they already onboarded (e.g. they did public, now they want billing too), choose Re-introspect and expand the schema selection in Phase 1.3 to include both the old and the new schemas. The engine scans them together in one pass, so any relationship between the original and the new schema is detected as a first-class cross-schema join (Case 1) and surfaced for review. Picking "Onboard another database" instead would split the two schemas into separate models and demote any link between them to manual cross-profile glue (Phase 2b federation) — wrong for one DB. If you're unsure which the user means, ask: "Is billing in the same database connection as <profile>, or a different server/database?" — same connection → Re-introspect + expand schemas; different → new profile.
The engine auto-backs-up any legacy (v1) model (index.yaml + per-schema _schema.yaml) it finds at the profile root into .legacy_backup/ before writing — so a first run over an old profile is safe and reversible; surface a one-liner when that happens.
1.1a — Same company vs different company
When the user chooses Onboard another database, ask (AskUserQuestion) whether the new database belongs to the same company or a different one. A deployment (one artifacts dir) holds exactly one company/organization with many datasources under it; a genuinely different company is a different folder.
1.2 — Scope: schemas, and the no-catalog case
Run cli areas/probe is not needed yet — schema discovery happens inside the engine. But decide scope first:
-
Catalog reachable (common): after the engine lists schemas, it introspects all of them. If the DB has many schemas (Snowflake with 50+), narrow first — ask the user which schemas matter (multi-select), then pass them as the engine's table allowlist scope. Pre-check public (Postgres) / PUBLIC (Snowflake) / the credentials' database (MySQL).
- Supabase is handled automatically — the engine detects it by signature and drops its system schemas (
auth, storage, vault, realtime, extensions, …) so only the user's app schemas (e.g. public) are modeled (it surfaces a note saying which it skipped). Don't hand-build a --tables allowlist for Supabase to dodge the system tables — that's already done, and a manual allowlist is fragile (shell word-splitting, missed tables). Only allowlist when the user genuinely wants a subset of their own tables.
-
Catalog denied (locked-down role): if a quick probe shows the catalog isn't readable, the engine cannot enumerate tables — ask the user for the table list:
Your role can read the data but not the catalog, so I can't list tables automatically. Paste the tables to model (e.g. sales.orders, sales.customers) — I'll describe each from the data itself.
Pass these to the engine via --tables schema.table …. Everything the engine then infers (types, grain, FKs) lands unreviewed for sign-off.
1.3 — Schema picker (multi-select)
For non-SQLite/DuckDB with multiple schemas, AskUserQuestion multi-select: "Which schemas should I introspect?" One option per schema + All schemas + Just <default> for now. Record selected_schemas; the engine scopes to these.
On re-introspect / an existing model — pre-check the schemas already modeled, and make "add" vs "replace" explicit. Read the schemas already in <profile>'s model (the distinct schema of its existing tables) and pre-check those in the picker, labelled <schema> (already in your model). Tell the user plainly:
The engine re-scans exactly the schemas you check here and rebuilds the structure from them. To add a schema, leave the existing ones checked and tick the new one — relationships between them get found in the same pass. Unchecking a schema removes its tables from the model (its hand-edits are preserved only if you keep it checked).
This is the union-rescan that makes Case 2 work: adding billing to a model that already has public must scan public ∪ billing together — scanning only billing would build the new schema's internal joins but miss every join back to public (a cross-schema edge needs both endpoints in one introspection pass). Never silently re-scan just the delta.
Reconcile what you'll scan vs what's actually there — NEVER silently shrink the scope. When the user picks All schemas (or you narrow the set yourself), do NOT quietly hand the engine a smaller list. Count what the catalog holds (schemas + tables) and state plainly what you're INCLUDING and what you're LEAVING OUT, with a reason for each exclusion, then confirm. Three kinds get excluded and each must be named, not dropped silently:
- System/internal schemas —
pg_auto_copy, pg_*, information_schema, Supabase auth/storage/… (the engine already drops these; still report them).
- A schema that looks like a DIFFERENT dataset — e.g. a
public full of Salesforce objects (accounts, opportunities, leads, contacts, pricebooks) sitting next to ServiceNow module schemas. Don't fold a foreign domain into this model; name it and offer it as its own profile.
- Tables you allowlist away — any explicit
--tables/--tables-file subset.
Example to say BEFORE introspecting: "Found 70 tables across 18 schemas. I'll model the 52 in the 16 ServiceNow module schemas. Excluding: public (17 — accounts/opportunities/leads… looks like a separate Salesforce dataset; onboard it as its own profile if you want it) and pg_auto_copy (1 — Redshift system). Good?" A user who said "all" must SEE the delta up front — never discover later that 19 tables never made it in. This is mandatory whenever discovered-count ≠ scanned-count.
1.4 — Datasource context (MANDATORY — ALWAYS ASK)
Two levels: a company description written once at the deployment root and shared by every datasource, and a per-database narrative for the datasource being connected now. The skill never decides yes/skip for the user; "don't ask clarifying questions" does NOT cancel this — it's required state-gathering. Ask A then B.
A. Company context — ask ONCE, on the first onboard into this deployment. Decide "already authored" by content, not by file existence: treat it as done only if the root <artifacts_dir>/organization.md exists or the root <artifacts_dir>/organization.yaml already has a non-empty name/description. If neither, this is the first onboard → ask:
What's your company/organization called, and a sentence or two on what it does? It's shared across every database you connect here and improves NL→SQL. (e.g. what the company/product is, what "MRR" or "active user" means company-wide.)
On an answer, do BOTH of these (not one — the record write is what populates organization.yaml):
- Run this command to write the company
name + a short description into the record (this is the ONLY thing that fills those fields — a file write alone will not):
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" set-org "<artifacts_dir>" --name "<company name>" --description "<one-line summary>"
- Write the fuller paragraph to the root
<artifacts_dir>/organization.md under # About this company (narrative prose ONLY — no model facts).
Then confirm both landed: <artifacts_dir>/organization.yaml now shows name:/description:, and <artifacts_dir>/organization.md exists.
Skip → leave the company context empty (the composition simply omits the company block). Mention that richer company context (conventions, glossary) can be added later in /agami-model. On a subsequent onboard (company context already authored) do NOT re-ask — narrate "Sharing the <company> context you set earlier."
B. This-database narrative — ask on EVERY onboard. For the datasource being connected now:
Want a one-paragraph description of what this database is about — what it holds, and any term that means something specific here? Optional.
Yes → write to the per-profile <artifacts_dir>/<profile>/datasource.md under # About this database (this datasource's narrative only). Skip → leave it absent; Phase 2f writes a short per-database starter from the enriched model.
chmod 644 whatever you write — these are non-secret model files and must stay readable (e.g. by the deploy container / a teammate reading a checked-in copy); never chmod 600 them (that's for local/ secrets only). See shared/organization-context-format.md for the content-routing rule (company-wide → root; per-database → the profile; per-column units → the structured model; personal → USER_MEMORY.md).
1.5 — Existing data model / semantic layer (MANDATORY — ALWAYS ASK)
Independent of 1.4 (paragraph ≠ doc). Same "required state-gathering" rule. Several very different sources qualify, so ask once and branch on the answer. AskUserQuestion (multi-select; the repo path is the high-value one — it encodes metrics + joins, not just structure):
Got an existing data model or metrics list I can read? A few kinds help:
• A doc — ERD, data dictionary, schema diagram (PDF, PNG/JPG, text, markdown, CSV).
• A metrics / KPI list — a spreadsheet, CSV, or doc of your metrics and how each is defined (e.g. "Approval rate = approved ÷ applications"). I'll turn each into a reusable metric so answers match your numbers.
• A semantic-layer / transform repo — LookML, dbt, Cube, MetricFlow. These define your metrics, dimensions, and joins explicitly, which is gold for NL→SQL accuracy. They're usually git-backed — just point me at the folder.
• A published product schema — if this DB is a well-known product (ServiceNow, Salesforce, Jira, NetSuite, SAP, HubSpot, Workday…), I can look up its official table/column reference online so the standard fields get correct descriptions automatically.
Options: Doc / metrics file — I'll attach it / Semantic-layer repo — I'll give a path / Published product schema — look it up / Skip — nothing to share. (Multi-select — combine as needed.)
If a doc: Read the path (handles PDF/image/md/text/CSV natively; trim huge files to first 20 pages / 50 rows). .xlsx/.docx → ask for PDF, proceed without if not.
If a published product schema (or the user names one — "use the ServiceNow data model online"): the user wants the standard fields grounded in the authoritative reference, not your training memory.
- Prefer the in-DB metadata. Most metadata-driven platforms carry their own dictionary in the database (ServiceNow
sys_dictionary/sys_choice, SAP DD03L, …). That's authoritative AND machine-readable — far better than scraping docs. So the real answer here is usually Phase 2a.0's sm enrich-metadata, not a web fetch. Note that intent now; it runs during enrichment.
- Web reference as fallback / supplement. When there's no in-DB dictionary,
WebSearch/WebFetch the official developer/data-dictionary docs for the named product + the tables you discovered in 1.6. If you chose this path, the fetch is mandatory, not optional — do it once, upfront, and stash into $DATA_MODEL_DOC_TEXT (never written to disk). Note that vendor doc portals are often JS-rendered and return only nav skeletons (ServiceNow's does); when a fetch comes back empty, fall back to WebSearch result snippets / community pages and say so — don't silently substitute training knowledge.
- Propagate it. If you fan enrichment out to subagents, every subagent prompt MUST include
$DATA_MODEL_DOC_TEXT with the instruction: describe standard fields from this reference; if a field isn't in the reference or the data, mark it ai_unknown — do NOT fill it from general product knowledge. (This is the hole that let subagents drift to "from ServiceNow domain knowledge.")
Stay anchored to the tables you actually introspected; don't invent fields the live DB doesn't have.
If a semantic-layer repo: ask for the directory (a local clone / monorepo path — no upload needed since it's git-backed). Glob the definition files and Read them up to a budget (~30 files / ~250 KB total; if larger, prefer metric/model definitions and tell the user what you sampled). Skip compiled SQL and data files — you want the declared metrics/joins, not the warehouse output:
| Layer | Read these | Carries |
|---|
| LookML | *.view.lkml, *.explore.lkml, *.model.lkml | dimensions, measures (→ metrics), joins (→ relationships), sql_table_name |
| dbt | models/**/*.yml (esp. schema.yml), semantic_models/**, metrics/**, dbt_project.yml | column descriptions, relationships tests (→ FKs), MetricFlow metrics/measures |
| Cube | model/**/*.{yml,js} (or schema/**) | measures, dimensions, joins |
Stash everything gathered (doc text + repo definitions) as $DATA_MODEL_DOC_TEXT for enrichment — give entities/metrics/relationships found here confidence: inferred (a declared metric is a strong signal but still wants a human sign-off; FK-derived joins stay as the engine set them). Never written to disk — lives only in the enrichment prompt, then discarded. Skip → proceed.
1.6 — Discover & prune the table list (cheap first pass)
Before the full, expensive introspection, show the user every table + its columns so they can drop what they don't need — staging/backup/scratch tables, dated partition snapshots, irrelevant columns. The full grain/FK/description work then runs on only the kept tables, which on a big DB (hundreds of staging/snapshot tables) is the difference between minutes and hours.
Run the discover pass — it lists tables + columns only (no grain, FK, or row-count probes, so it's fast even over a tunnel):
ts=$(date -u +%Y%m%d-%H%M%S)
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" discover \
--profile <profile> --db-type <db_type> \
--artifacts "<artifacts_dir>" \
--out "<artifacts_dir>/local/prune/<profile>/$ts.html" \
[--schemas <selected_schemas…>]
It writes the inventory to <artifacts_dir>/<profile>/.introspect/inventory.json, renders a standalone prune page, and prints JSON with table_count + prune_html. (This page is a deliberately minimal, separate artifact — not the model explorer — so there's no description/metric/review machinery, just tables + columns + checkboxes.)
Open the page, hand off, and END THE TURN — same pattern as the credentials hand-off; pruning is interactive:
- Auto-open the printed
prune_html path.
- Tell the user: "I listed all
<N> tables and their columns. Uncheck any you don't need (staging, backups, snapshots); you can also expand a table and drop irrelevant columns. Then click Generate for Claude and paste the block back here."
- End the turn. Do NOT introspect yet.
On re-entry — the user pastes an AGAMI PRUNE … block. Don't hand-parse it — pipe it to the parser, which writes a shell-safe tables file (sidestepping the zsh word-split that collapses an unquoted list into one garbage table):
parse_prune_block.py --block-file <pasted> --tables-out /tmp/agami-keep.txt
It prints {data: {tables_kept, tables_file, excluded_columns, kept_everything}, anomalies}. Pass --tables-file "$tables_file" and --exclude-columns <excluded_columns…> to 1.7. kept_everything: true → run 1.7 with neither flag. Surface anomalies (a bad_table_line, a keep_count_mismatch) rather than silently dropping a line.
If the user instead asks to skip pruning ("just introspect everything"), proceed to 1.7 unscoped.
1.7 — Run the introspection engine (on the kept tables)
This is the deterministic core — it replaces hand-authoring tables/columns/FK SQL/confidence formulas. From plugins/agami/scripts/:
printf '%s\n' incident problem change_request sys_user ... > /tmp/agami-keep.txt
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" introspect \
--profile <profile> --db-type <db_type> \
--artifacts "<artifacts_dir>" \
[--tables-file /tmp/agami-keep.txt] \
[--exclude-columns <schema.table.column list from 1.6>]
--tables-file is the prune step's kept set (it also covers the no-catalog/probe-only case from 1.2). --exclude-columns marks the dropped columns excluded during the build — one validated write, no follow-up curate call. Omit both only when the user kept everything or skipped pruning. (If a table name is bogus / can't be described, the engine now drops it with a note and — if nothing describes — errors clearly instead of writing a partial model.)
On a large schema (50+ tables) over a tunnel this takes minutes and the command prints nothing until it returns — which reads as hung. Two ways to keep it legible, in order of preference:
- Batched build (preferred for 30+ tables) —
--append. Split the kept allowlist into batches of ~10–15 tables and introspect one batch per call, each a quick foreground command that returns in tens of seconds. Every call MERGES into the existing model (prior tables are loaded from disk, never re-queried), so you end with the full union — and you report batch 3/5 (+12 tables) between calls, natural progress with no background monitor to babysit:
split -l 12 /tmp/agami-keep.txt /tmp/agami-batch-
for b in /tmp/agami-batch-*; do
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" introspect --profile <p> --db-type <t> \
--artifacts "<artifacts_dir>" --tables-file "$b" --append
done
(--append on the first batch just creates the model — there's nothing to merge yet. The relationship pass runs each batch across the union, deduped, so the final batch yields the complete join graph.)
- Single background call + tail. If you'd rather one call: run it in the background (Bash
run_in_background: true) and tail its progress log every ~15–20s, surfacing the latest line. The engine writes a flushed, throttled (~every 10%) per-phase log to <artifacts_dir>/<profile>/.introspect/progress.log (override with --progress): discovered 52 tables …, columns+grain 30/52, relationships: declared FK 80/187, done.
(For a small DB introspect returns in seconds — no batching or backgrounding needed.)
It builds + validates + writes the model at <artifacts_dir>/<profile>/: storage connection, proposed subject areas, per-table columns + types (catalog or value-inferred), PK→grain, FK→relationships with inferred cardinality (many_to_one/one_to_many/one_to_one), column_groups on deep tables (≥30 cols), sensitive flags on PII, cross-area edges, and a report. Relationships from unenforced-FK dialects (Redshift/Databricks/Trino) and everything from probe mode are confirmed-by-overlap or unreviewed. The report prints the capability mode per step (catalog vs probe) — surface that to the user so they know what was read vs inferred.
The validator gates the write — if it fails, the model is not persisted. Surface the errors verbatim and stop (this should be rare; the engine emits valid models).
Surface: ✓ Introspected <N> tables across <A> subject area(s) (<catalog|probe> mode); <R> relationships, <D> deep tables, <S> sensitive columns flagged.
Backstop reconciliation — if <N> is fewer than the catalog held, say what's NOT in the model and why (the 1.3 guard should have covered this up front; repeat it here so it can't slip). E.g. "Modeled 52 of 70 tables — left out public (17, separate Salesforce dataset) and pg_auto_copy (1, system). Say the word if you want any of them." A user who picked "all" should never have to diff the catalog themselves to notice a schema is missing.
Phase 2: Enrich (the LLM layer — validated into the model)
The engine gives structure; you add meaning. Load the model with cli bundle <root> --area <area> (or read the YAMLs). After each enrichment pass, re-validate (cli validate <root>) and never persist a model that fails. <root> = <artifacts_dir>/<profile>/.
HARD RULE — decisions to you, plumbing to the engine. NEVER write a generator script (build_ops.py, build_desc.py, enrich_*.py, a one-off that loops to build curate ops). It's the recurring failure mode and it's banned. When you have many edits, emit them as data and let a tested command apply them: sm describe-file (TSV of descriptions), sm enrich-metadata (in-DB metadata), sm set-units, sm suggest-metrics, or sm curate --ops-file <json>. You decide what (currency, which metrics, a column's meaning); the engine does the applying.
2a.0 — Deterministic metadata FIRST (run before any hand-enrichment)
Many databases describe themselves. Metadata-driven platforms ship their own data dictionary + value-label tables (ServiceNow sys_dictionary + sys_choice, SAP DD03L, Salesforce metadata…), and plenty of ordinary schemas have code→label lookup/dimension tables. When that metadata is present it is authoritative — strictly better than LLM guessing or fetching JS-walled vendor docs. Always try it first:
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" enrich-metadata "$ROOT" --profile <profile> --db-type <db_type>
It auto-detects a preset (e.g. servicenow) or takes --preset, reads the dictionary/lookup tables, and applies — in validated curate batches — column descriptions (stamped description_source: metadata, authoritative), choice_field labels, and reference/FK relationships (the caller_id → sys_user edges that <x>_id name-matching can't find). On ServiceNow this alone closes most of the column-coverage, enum-decode, and join-graph gaps deterministically. It prints what it enriched. Then hand-enrich only what it didn't cover (2a–2c below). --skip-references if you want descriptions/choices only.
2a — Descriptions (describe coded columns; leave only self-evident ones empty)
For each table fetch up to 5 sample rows for evidence (SELECT * FROM <t> LIMIT 5; Snowflake SAMPLE for >10M rows). Samples are never written to disk — context only, then discarded. Also capture MIN/MAX of each time column → record under the table's performance_hints so Phase 5 anchors "last 30 days" to the data's real MAX, not NOW().
Build a per-schema prompt with $DATA_MODEL_DOC_TEXT first (dominant prior), then datasource.md, then tables/columns/sample rows. Always emit a 1-line table description. For columns, classify each into one of three — don't default everything to empty:
| Kind | Examples | What to write |
|---|
| Self-evident | id, created_at, email, name, gender, city | "" — the name + type already says it; a description would just restate it. |
| Informative | revenue_usd, status, margin_pct, utilization_pct | one line iff samples/doc support a fact the name doesn't (enum values, unit, derivation, a caveat). Else "". |
| Coded / opaque-but-systematic | EL_REVENUE_30D, WEST_ORDERS_12M, <TIER_A_6M>, XX_<metric> families | always describe — unreadable without the legend (see below). |
Coded-schema detection + legend expansion — the case that used to get wrongly left empty. Feature stores, wide denormalized marts, and coded analytic extracts encode meaning in column names via a small recurring token vocabulary: a category prefix (e.g. EL/HM/AP…), a window suffix (_30D, _6M, _12M), a bucket (_0_30, _TIER_A), a threshold (_LT_1K, _LT_10K), a metric stem (_REVENUE, _QTY, _RECENCY). When the same tokens recur across many columns:
- Decode the token legend once — grounded in column samples +
$DATA_MODEL_DOC_TEXT + datasource.md. For any token whose meaning isn't evident, ask the user in one batched question rather than guessing (this is the same decode 2b needs; do it once and share it).
- Expand the legend into one description per coded column — deterministically, NOT as N separate LLM guesses. Write a small in-skill decoder (a token→phrase map + a per-table parse of
(prefix, metric, window, threshold)) and compose each description from it, then emit them through the same sm curate batch. This is exact, internally consistent, and costs zero per-column LLM tokens. e.g. EL_REVENUE_30D → "Total revenue from the Electronics category over the last 30 days"; WEST_ORDERS_12M → "Number of orders in the West region over the last 12 months"; EL_ORDERS_LT_1K_90D → "Number of Electronics orders under 1,000 placed in the last 90 days." (Use the user's actual token vocabulary + domain, not these placeholders.)
- The same decoded legend also lands in the structured glossary
key_terminology (term → definition) — abbreviations and codes a reader needs (an acronym → its expansion; a status/type code → its meaning). Decode once, write both — the per-column descriptions (what shows in the explorer + feeds the SQL generator's column context) AND the glossary. Write it with the packaged command (a JSON object {term: definition, …}); it merges over any existing terms, validates, and commits:
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" set-terminology "$ROOT" --file /tmp/agami-terminology.json
The glossary is rendered into datasource.md's ## Key terminology by org-draft (which also auto-seeds enum legends from choice_field columns) — so it survives a regeneration and is never a bare placeholder. Do NOT skip this on a code-dense schema (abbreviations, master-coded values): an empty Key terminology on a DB full of codes is a real miss.
Don't skip a coded column because "the legend covers it" — the legend lives in a different file; the per-column description is what the explorer shows and what NL→SQL reads. A wide coded table (hundreds of columns) should finish at ~100% column coverage, not ~3%.
Value-enum decode — FILL choice_field for coded-VALUE columns (the highest-leverage structured win). Distinct from the coded-name legend above: this is a column whose stored VALUES are codes — a severity of 1/2/3, a state of 1/2/3/6/7, a priority of 1–5, a short status/type code. Introspection now seeds a choice_field skeleton — the full map of distinct sampled codes with blank labels (e.g. {"1":"","2":"","3":""}, not a single placeholder) — on every low-cardinality coded column, so the keys already exist and you only fill the labels. Your job is to fill the labels — from samples + $DATA_MODEL_DOC_TEXT, or a lookup/choice table when the DB has one (e.g. ServiceNow sys_choice: SELECT value, label FROM sys_choice WHERE name='<table>' AND element='<column>' — read it once and apply, don't hand-guess). Write it as a structured choice_field edit op, not just prose:
{"op":"edit","kind":"table","area":"<area>","name":"<table>","column":"severity","field":"choice_field","value":{"1":"High","2":"Medium","3":"Low"}}
Why structured beats prose: the SQL generator translates "high severity" → severity = 1 and labels result rows from the map deterministically — a decode buried in the description can't be applied programmatically. Fill the skeleton on every coded-value column in the same sm curate batch as the descriptions; leave a label blank only when no sample/doc/lookup reveals it. (The filled map also auto-seeds the glossary via org-draft.) Self-labeling value sets (a region of North/South/East/West) can keep choice_field as the values themselves or be cleared — they need no translation.
Write descriptions with sm curate edit ops — never hand-edit the table YAML, and never write a throwaway generator script to build the ops. Build one ops array and run it once; it validates the whole model + commits + reverts on failure. The op does NOT need area — curate resolves which area owns a table/column by name, so you do not maintain a table → area map (that map is exactly what used to push the LLM into writing an enrich_gen.py; it's gone). Shapes:
- table:
{op:edit, kind:table, name:"<table>", field:"description", value:"<text>", source:"ai"}
- column: same +
column:"<col>"
Pass area only to disambiguate a bare name that exists in two schemas (curate skips an ambiguous op with a clear "pass an explicit area" reason rather than editing the wrong one). Always include "source":"ai" on every generated description — this stamps description_source: ai_unvalidated so the description earns trust through use (agami-query surfaces it in the answer receipt for confirmation the first time a query actually uses that column, instead of forcing an upfront review of hundreds of descriptions). Skip any column that already has a description (so a partial hand-edit or a reintrospect merge is never clobbered):
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" curate "$ROOT" --ops-file /tmp/agami-descriptions.json
| Column | Bad (reject) | Good (keep) | Empty (preferred) |
|---|
id, created_at, email, name | "Primary key" / "When created" / "Email" | (always empty — self-evident) | "" |
customer_id | "The customer ID" | "FK to customers.id; 1:N with orders" | "" if nothing to add |
status | "A status code" | "lifecycle: pending → shipped → cancelled" (only if enum known) | "" |
EL_AOV_90D (coded) | "AOV value" | "Average order value for the Electronics category over the last 90 days" (from the decoded legend) | — don't leave empty |
v_1, tmp_col, x | "A value" | (leave empty — opaque, no decodable structure) | "" |
What NOT to invent: meanings for truly opaque single columns (v_1, tmp_col, xyz) that have no decodable token structure and no sample signal. Business semantics not present in the samples/doc. Name translations on self-evident columns (amt→"amount"). Write the decodable legend; don't fabricate the rest.
For an opaque column you genuinely can't read, say so — don't leave it silently blank. There are two kinds of empty description, and they must be distinguished:
- Self-evident (
id, created_at, email, a clear name) → leave the description empty AND leave description_source unset (null). The name already says it; nothing to flag.
- Opaque / unknown (
xyz, v_1, tmp_col, a code whose meaning no sample or doc reveals) → leave the description empty BUT set description_source: "ai_unknown" via a curate edit op ({op:edit, kind:table, area, name:<table>, column:<col>, field:"description_source", value:"ai_unknown"}). This records "agami looked and couldn't tell" — the human knows what xyz is, and the explorer + answer receipts surface these so they can fill it in. Don't guess a meaning to avoid the flag; the flag is the honest answer. (Do NOT mark a self-evident column ai_unknown — that's noise.)
For large schemas (>100 tables) batch 50 at a time; narrate [batch 2/4] …. Validate after each schema; on failure, surface errors and continue with the rest, then report which need attention.
Regroup wide tables into SEMANTIC column groups (do this right after describing their columns). A deep table (≥30 cols) gets column_groups from the engine, but those are a deterministic name-prefix split — buckets like is, last, latest, created, bp — not concepts. They exist only so the explorer can collapse a wide table; they're a fallback, not the goal. Having just described every column, you are positioned to cluster them by meaning into a handful of named groups that read like mini subject-areas — e.g. identity, location, lifecycle_timestamps, telemetry, swap_details, alerts, flags. Keep it to ~5–10 groups; every column must land in exactly one (the validator rejects orphans on a deep table). Apply via one curate edit op per wide table:
{"op":"edit","kind":"table","name":"<wide_table>","field":"column_groups",
"value":{"identity":["..."],"location":["..."],"lifecycle_timestamps":["..."],"telemetry":["..."]}}
Send these in the same sm curate batch as the descriptions (or a follow-up batch). Only worth doing on genuinely wide tables — narrow tables have no column_groups and need none.
Column-pass completeness gate (MANDATORY — do not skip the column pass). After enriching, run:
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" coverage "$ROOT"
ok: false with unenriched_tables lists tables that got a table description but no column descriptions at all — i.e. you enriched the table layer and skipped the columns. That is not done: go back and run the column pass on those tables (describe each meaningful column from sampled values; mark genuinely-opaque ones ai_unknown; self-evident id/timestamps may stay blank). A wide coded table must finish near 100% described, not 0%. This is also enforced downstream — seed-examples refuses (refused: "columns_unenriched") while any table is unenriched, so you cannot reach the trust dashboard on naked columns. Don't proceed to 2b until coverage returns ok: true.
2b — Entities (the semantic vocabulary)
Propose entities[] per subject area — the names users actually say. For each, fill name, plural, other_names (synonyms), maps_to (table+column, one primary: true), and — for opaque-identifier columns — a value_pattern regex (e.g. a VIN ^[A-Z0-9]{17}$, a BP-prefixed serial) so the runtime can recognize literals. Ground these in column names + samples + the domain doc; don't invent entities the schema doesn't support. Because these are LLM-proposed, write them confidence: inferred, review_state: unreviewed so they surface in the Phase 4 pre-seed review (seeds reference entity vocabulary).
Don't stop at id columns — wide / denormalized tables hide dimensions a maps_to-on-an-id scan misses. When a schema is denormalized to one grain (e.g. one row per customer, every table keyed on the same id), an id scan finds exactly one entity and quits — yet real business dimensions are still there, encoded two other ways. Look for both, discovered from the actual columns + samples, never hardcoded:
- Coded column-name prefixes / suffixes. Many columns sharing a recurring token (
XX_<metric> repeated for several XX, or <metric>_<period>) means that token is a dimension (a product line, a region, a time bucket). Decode it from evidence — column descriptions, sample values, the domain doc; if a code's meaning isn't evident, ask in one batched question rather than guessing. A prefix dimension has no id column, so it isn't a maps_to entity — record the decoded legend in datasource.md ## Key terminology and fold the expansion into each affected column's description, so NL→SQL can map a phrase to the right prefixed columns.
- Value-level entities. A set of sibling string columns whose values are real-world instances (institution/lender names, branch names, merchant names, statuses) is an entity even with no id column — create it (
maps_to the most representative such column; capture distinct sampled values as other_names/a value_pattern cue) so a user naming a literal value resolves to those columns.
If, after this, a single-grain schema genuinely has one entity, that's the right answer — say so. The point is to check the two hidden shapes before concluding "one entity," not to manufacture entities.
Write them with the packaged command, not by hand — build a JSON array and run it once (it validates each item, writes subject_areas/<area>/entities/<slug>.yaml, validates the whole model, reverts on failure, commits). Never author a throwaway script to loop. The canonical entity YAML shape is shared/metric-entity-shape.md — never read another profile's artifacts to copy it (see the boundary note under 2c):
bash "$AGAMI_PLUGIN_ROOT/scripts/sm" add "$ROOT" --kind entity --area <area> --file /tmp/agami-entities.json
2c — Metrics
Metrics come from two sources, handled very differently. Always prefer declared metrics — schema-only inference is a shallow guess (it finds AVG(rating), a row count, an AVG(order_value); it misses the domain KPIs a business actually tracks — refund rate, repeat-purchase rate, cohort retention, fulfillment SLA — because those aren't visible in column names).
(A) Declared metrics — extract in FULL, no cap. If the user attached a semantic-layer repo or a metrics file in 1.5 ($DATA_MODEL_DOC_TEXT), those are the org's real definitions — pull every one, don't sample to 4:
- LookML
measure {} → metric: type + sql → bindings, label/description → calculation, label+view_label → other_names.
