# Technical Documentation: tconbeer/harlequin > ℹ️ **Provenance:** Multi-Tier Ingestion · [CodeWiki Reference](https://codewiki.google/github.com/tconbeer/harlequin) ### Overview (`README.md`) # Harlequin [](https://pypi.org/project/harlequin/) The SQL IDE for Your Terminal. > [!TIP] > This README contains a small subset of the docs available at > [harlequin.sh](https://harlequin.sh/docs/getting-started/index). ## Installing Harlequin Harlequin is a Python program, and there are many ways to install and run it. We strongly recommend using [uv](https://docs.astral.sh/uv): 1. [Install uv](https://docs.astral.sh/uv/getting-started/installation/#standalone-installer). From a POSIX shell, run: ```bash curl -LsSf https://astral.sh/uv/install.sh | sh ``` Or using Windows Powershell: ```powershell powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex" ``` 2. Install Harlequin as a tool using `uv`: ```bash uv tool install harlequin ``` This command will install Harlequin into an isolated environment and add it to your PATH so you can easily run the executable. ### Other Installation Methods Alternatively, if you know what you're doing, after installing Python 3.9 or above, install Harlequin using `pip`, `pipx`, `poetry`, or any other program that can install Python packages from PyPI: ```bash pip install harlequin ``` There is also a [Homebrew formula](https://formulae.brew.sh/formula/harlequin) for Harlequin, although this is maintained by the community and is not as rigorously tested as the Python installations. Note that the formula includes several Harlequin adapter packages (Postgres, MySQL/MariaDB, and ODBC) and their dependencies, which is convenient but increases the application size. ```bash brew install harlequin ``` ## Installing Database Adapters Harlequin can connect to dozens of databases using adapter plug-ins. Adapters are distributed as their own Python packages that need to be installed into the same environment as Harlequin. For a list of known adapters provided either by the Harlequin maintainers or the broader community, see the [adapters](https://harlequin.sh/docs/adapters) page. The adapter docs also include installation instructions. Some adapters can be installed as Harlequin extras, like `postgres`. If you used `uv` to install Harlequin: ```bash uv tool install 'harlequin[postgres]' ``` You can install multiple extras: ```bash uv tool install 'harlequin[postgres,mysql,s3]' ``` ## Running Harlequin Once Harlequin is installed, you run it from the command line. The arguments and options you pass in at the command line affect Harlequin's behavior, like what database adapter it uses, which database it connects to, whether or not the file picker is visible, and more. Assuming you have installed Harlequin so that it is on your [PATH]() (`uv tool install harlequin` does this automatically), you run Harlequin by typing a command of this form into your shell: ```bash harlequin [OPTIONS] [CONN_STR] ``` where `[OPTIONS]` is 0 or more pairs of the form `--[option-name] [option-value]`, and `[CONN_STR]` is 0 or more connection strings. `[OPTIONS]` are composed of both Harlequin options and adapter options. For a full list of options, run Harlequin with the `--help` option: ```bash harlequin --help ``` ## Using Harlequin with DuckDB Harlequin defaults to using its DuckDB database adapter, which ships with Harlequin and includes the full DuckDB in-process database. To open an in-memory DuckDB session, run Harlequin with no arguments: ```bash harlequin ``` To open one or more DuckDB database files, pass in relative or absolute paths as connection strings (Harlequin will create DuckDB databases if they do not exist): ```bash harlequin "path/to/duck.db" "another_duck.db" ``` ## Using Harlequin with SQLite and Other Adapters Harlequin also ships with a SQLite3 adapter. To use that adapter, you specify the `--adapter sqlite` option. Like DuckDB, you can open an in-memory SQLite database by omitting the connection string: ```bash harlequin --adapter sqlite ``` You can open one or more SQLite database files by passing in their paths as connection strings; note that the `--adapter` option has a short alias, `-a`: ```bash harlequin -a sqlite "path/to/sqlite.db" "another_sqlite.db" ``` Other adapters can be installed as plug-ins; for more information, see the [installation guide](https://harlequin.sh/docs/getting-started/index#installing-database-adapters), and the guides for individual [adapters](https://harlequin.sh/docs/adapters). Each adapter can define its own options, which you can view using `harlequin --help`. ## Configuring Harlequin Harlequin contains a large number of options that allow you to [set the theme](https://harlequin.sh/docs/themes), [customize key bindings](https://harlequin.sh/docs/keymaps/index), [show remote and local files](https://harlequin.sh/docs/files/index), set the locale for number formatting, and much more. These can always be entered at the command line, but it can be convenient to define a configuration as a profile instead. For more information on configuring Harlequin, see [Using Config Files](https://harlequin.sh/docs/config-file/index). A profile's string values can name environment variables — `password = "${MYPASSWORD}"`, or `${MYHOST:-localhost}` to supply a default — so a config file your team shares holds no credentials, and values an adapter declares as secrets are masked wherever Harlequin prints them. hsql, Harlequin's companion CLI (below), reads the same files and can report on them, with `hsql --config show`, `--config validate` and `--config schema`. ## Using Harlequin with Agents or in Scripts Harlequin has a companion CLI, hsql, that uses the same adapters and config files, but is optimized for headless use by agents and in automations. hsql is packaged with Harlequin (no additional installation required): ```bash $ hsql -P dev -c "select * from users" id | name ----+--------- 1 | Ted 2 | Patrick (2 rows) ``` hsql can also explore the catalog without writing SQL: `hsql --catalog --path mydb.analytics` lists a schema's relations (and `--path mydb.analytics.orders` lists that table's columns), while `hsql --catalog-search customer_id` searches every level at once. To bound what an agent can do, `--read-only` connects in a mode the database refuses writes in, and `hsql --timeout 30` cancels a run that takes longer than that. Both are also profile keys, and both refuse to start if the adapter cannot enforce them. Harlequin takes `--read-only` too. For more information on hsql, see the [getting started docs](https://harlequin.sh/docs/getting-started/hsql). ## Using Harlequin with Django [django-harlequin](https://pypi.org/project/django-harlequin/) provides a command to launch Harlequin using Django’s database configuration, like: ```bash ./manage.py harlequin ``` ## Keep Reading at [harlequin.sh](https://harlequin.sh/docs/getting-started/usage) Visit [harlequin.sh](https://harlequin.sh/docs/getting-started/usage) for an overview of features and full documentation, starting with a guided walkthrough of how to edit and execute queries, use the data catalog, export data, and more. ## Getting Help To view all command-line options for Harlequin and all installed adapters, after installation, simply type: ```bash harlequin --help ``` To view a subset of these docs (and a link back here) from within the app, press `F1`. See the [Troubleshooting](https://harlequin.sh/docs/troubleshooting/index) guide for help with key bindings, appearance issues, copy-paste, etc. [GitHub Discussions](https://github.com/tconbeer/harlequin/discussions) are a good place to ask questions, request features, and say hello. [GitHub Issues](https://github.com/tconbeer/harlequin/issues) are the best place to report bugs. ## Sponsoring Harlequin Please consider [sponsoring Harlequin's author](https://github.com/sponsors/tconbeer), so he can continue to dedicate time to Harlequin. ## Contributing Thanks for your interest in Harlequin! Harlequin is primarily maintained by [Ted Conbeer](https://github.com/tconbeer), but he welcomes all contributions! Please see [`CONTRIBUTING.md`](./CONTRIBUTING.md) for more information. --- ### Overview (`packaging/hsql/README.md`) # hsql `hsql` is your agent's favorite SQL client. It's the headless CLI for [Harlequin](https://harlequin.sh), and shares the same config and query engine, with an interface optimized for agents, scripts, and automations. > [!TIP] > This README contains a small subset of the docs available at > [harlequin.sh](https://harlequin.sh/docs/getting-started/hsql). If you already use Harlequin and know about adapters and config files, jump ahead to [Running hsql](#running-hsql). ## Installing hsql hsql is packaged with [Harlequin](https://pypi.org/project/harlequin/), so if you already use Harlequin, hsql is already installed. Otherwise, you can install hsql directly. hsql is a Python program, and there are many ways to install and run it. We strongly recommend using [uv](https://docs.astral.sh/uv): 1. [Install uv](https://docs.astral.sh/uv/getting-started/installation/#standalone-installer). From a POSIX shell, run: ```bash curl -LsSf https://astral.sh/uv/install.sh | sh ``` Or using Windows Powershell: ```powershell powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex" ``` 2. Install hsql as a tool using `uv`: ```bash uv tool install hsql ``` This command will install hsql into an isolated environment and add it to your PATH so you can easily run the executable. ## Installing Database Adapters hsql can connect to dozens of databases using adapter plug-ins. Adapters are distributed as their own Python packages that need to be installed into the same environment as hsql and harlequin. For a list of known adapters provided either by the Harlequin maintainers or the broader community, see the [adapters](https://harlequin.sh/docs/adapters) page. The adapter docs also include installation instructions (installing an adapter with Harlequin also installs it for hsql). Some adapters can be installed as Harlequin extras, like `postgres`. If you used `uv` to install hsql directly, you can add adapter packages using `--with`: ```bash uv tool install hsql --with harlequin-postgres ``` You can install multiple extras: ```bash uv tool install hsql --with harlequin-postgres --with harlequin-mysql ``` ## Running hsql Once hsql is installed, you run it from the command line. If you have used psql or the duckdb CLI, hsql will feel familiar, but hsql has the major advantage that is works with most databases and provides the same interface and produces the same output, regardless of the connected database. This means you (and your agent) can learn one tool, instead of several. In your shell, all hsql commands take the same form: ```bash hsql [OPTIONS] [CONN_STR] ``` where `[OPTIONS]` is 0 or more pairs of the form `--[option-name] [option-value]`, and `[CONN_STR]` is 0 or more connection strings. `[OPTIONS]` are composed of both hsql options and adapter options. For a full list of options, run hsql with the `--help` option: ```bash hsql --help ``` ## Using hsql with DuckDB hsql defaults to using its DuckDB database adapter, which ships with hsql and includes the full DuckDB in-process database. Run a query against an in-memory DuckDB session, run hsql and pass in a query with the `-c` option: ```bash $ hsql -c "select 1" 1 --- 1 (1 row) ``` To query one or more DuckDB database files, pass in relative or absolute paths as connection strings (hsql will create DuckDB databases if they do not exist): ```bash $ hsql "path/to/duck.db" -tAc "select count(*) from orders" 42 ``` ## Using hsql with SQLite and Other Adapters hsql also ships with a SQLite3 adapter. To use that adapter, you specify the `--adapter sqlite` option. Like DuckDB, you can open an in-memory SQLite database by omitting the connection string: ```bash $ hsql --adapter sqlite -c "select 'Ted' as author" author -------- Ted (1 row) ``` You can query one or more SQLite database files by passing in their paths as connection strings; note that the `--adapter` option has a short alias, `-a`: ```bash $ hsql -a sqlite "path/to/sqlite.db" -c "select * from users" id | name ----+--------- 1 | Ted 2 | Patrick (2 rows) ``` Other adapters work the same way; for example, Postgres: ```bash $ hsql -a postgres "postgresql://example.com/postgres:5432" -c "select * from invoices" ``` > [!TIP] > You should use Profiles to keep credentials out of your shell > history. For more information, keep reading or see > [the docs](https://harlequin.sh/docs/config-file/index) on > config files. ## Configuring hsql and Using Profiles hsql supports a number of options for setting the query limit, configuring output formats, and defining connection parameters. Options can be passed as command-line flags, or read from [config files](https://harlequin.sh/docs/config-file/index). Config files store configurations under separate profiles, so you can easily switch between databases by reading from different profiles with the `-P` option: ```bash $ hsql -P prod -c "select count(*) from orders" --csv $ hsql -P dev -c "select * from users" --vertical --limit 5 $ hsql -P warehouse -c "..." --format parquet -o invoices.pq ``` hsql reads the same config files as Harlequin, and merges them one profile at a time, nearest file first; pass `--config-path PATH` to read a single file instead, or `-P None` to skip them entirely. ### Keeping Credentials Out of Config Files A profile's string values can name environment variables, so a config file your team shares — and commits — holds no credentials: ```toml [profiles.prod] adapter = "postgres" host = "${MYHOST:-localhost}" # ${VAR:-default} supplies a default password = "${MYPASSWORD}" # ${VAR} is required; hsql exits 2 if it is unset ``` Write `$${` for a literal `${`, if a value really does start with one. Values an adapter declares as secrets are masked wherever hsql prints them — `--config show`, `--info`, and error messages — as is the password inside a connection string. ### Inspecting and Writing Config Files Five `--config` modes work on your config files instead of running SQL, and none of them connects to a database: - `list-profiles` — the names you can pass to `-P`, with each one's adapter, and which is the default. - `show` — the merged config as TOML, with the file each value came from beside it; `--json` for JSON. - `validate` — every problem in every discovered file, exiting `2` if it finds any. - `schema` — a JSON Schema for a config file, covering every adapter you have installed; point your editor at it for completion and validation as you type. - `init` — a profile written from the options you pass, prompting for nothing: `hsql --config init -P prod -a sqlite ./my.db --limit -1` writes `[profiles.prod]` into the nearest config file. ```bash $ hsql --config show default_profile = "dev" # from /home/user/.config/harlequin/harlequin.toml [profiles.dev] # from /home/user/proj/harlequin.toml, overriding /home/user/.config/harlequin/harlequin.toml adapter = "postgres" host = "${MYHOST:-localhost}" password = "********" limit = 100 ``` `list-profiles` and `validate` are result sets, so `--csv`, `-t`, `-A` and `-o` work on them as they do on a query. ## Data Layouts and File Formats hsql supports all of the following formats for displaying and writing data: - table - markdown (alias: md) - vertical - csv - tsv - json - jsonl (alias: ndjson) - parquet - orc - feather (alias: arrow) - none (suppresses output) You can select a format with the `--format ` or using the shorthand `--`, so these are equivalent: `--format csv`, `--csv`. Some layouts can present the results from multiple queries. Others will raise an error and exit with code 2 if multiple queries are executed. Additionally, for any layout, pass `--stats` to print summary info as JSON to stderr: ```bash $ hsql -c "select 1" --format none --stats {"status":"ok","statements":1,"rows":1,"truncated":false,"limit":500,"elapsed_ms":1,"columns":[{"name":"1","type":"#"}]} ``` ## Exploring the Catalog Before you can write a query, you have to know what is in the database. `--catalog` lists the objects one level below `--path`, and exits without running SQL: ```bash $ hsql "path/to/duck.db" --catalog path | name | query_name | type | type_label ------+------+------------+----------+------------ duck | duck | "duck" | database | db (1 row) ``` Every row's `path` is what lists that object's own children, so you walk down one level at a time: ```bash $ hsql "path/to/duck.db" --catalog --path duck path | name | query_name | type | type_label ----------------+-----------+--------------------+--------+------------ duck.analytics | analytics | "duck"."analytics" | schema | sch duck.main | main | "duck"."main" | schema | sch (2 rows) $ hsql "path/to/duck.db" --catalog --path duck.analytics path | name | query_name | type | type_label -----------------------------+--------------+----------------------------+------------+------------ duck.analytics.customers | customers | "analytics"."customers" | BASE TABLE | t duck.analytics.order_totals | order_totals | "analytics"."order_totals" | VIEW | v duck.analytics.orders | orders | "analytics"."orders" | BASE TABLE | t (3 rows) $ hsql "path/to/duck.db" --catalog --path duck.analytics.orders path | name | query_name | type | type_label -----------------------------------+-------------+---------------+---------------+------------ duck.analytics.orders.customer_id | customer_id | "customer_id" | BIGINT | ## duck.analytics.orders.id | id | "id" | BIGINT | ## duck.analytics.orders.placed_at | placed_at | "placed_at" | TIMESTAMP | ts duck.analytics.orders.total | total | "total" | DECIMAL(18,2) | #.# (4 rows) ``` A listing is rows, so every format and output option applies to it, exactly as they do to a query: ```bash $ hsql "path/to/duck.db" --catalog --path duck.analytics -tA --csv duck.analytics.customers,customers,"""analytics"".""customers""",BASE TABLE,t duck.analytics.order_totals,order_totals,"""analytics"".""order_totals""",VIEW,v duck.analytics.orders,orders,"""analytics"".""orders""",BASE TABLE,t ``` ### Searching the Catalog Walking is the wrong tool for *where does `orders` live* and *which tables have a `customer_id`*. `--catalog-search TERM` searches every level of the catalog at once, for objects whose name contains TERM: ```bash $ hsql "path/to/duck.db" --catalog-search customer_id path | name | query_name | type | type_label -----------------------------------------+-------------+---------------+--------+------------ duck.analytics.order_totals.customer_id | customer_id | "customer_id" | BIGINT | ## duck.analytics.orders.customer_id | customer_id | "customer_id" | BIGINT | ## duck.main.staging_events.customer_id | customer_id | "customer_id" | BIGINT | ## (3 rows) ``` `--path` narrows the search to one subtree, and the output can be laid out like any query: ```bash $ hsql "path/to/duck.db" --catalog-search order --path duck.analytics -tA duck.analytics.order_totals|order_totals|"analytics"."order_totals"|VIEW|v duck.analytics.orders|orders|"analytics"."orders"|BASE TABLE|t ``` Note: not all adapters support catalog search at this time. ## Scripting with hsql > [!WARNING] > To make hsql safe and efficient for agents, by default hsql applies a 500-row > limit to all queries. To remove this limit, use `--limit -1` or set > `limit = -1` in your profile. If limits truncate data, hsql will print > a warning on stderr; we recommend that you do NOT suppress or redirect > that message so do NOT use hsql with `2>/dev/null`. hsql can write data to files, either with the `-o` option or by piping output (hsql only writes data to stdout; other messages go to stderr): ```bash $ hsql -P prod --limit -1 -c "select * from users" --format parquet -o "users.pq" $ hsql -P prod --limit -1 -c "select * from users" --csv > users.csv ``` hsql can execute multiple statements in one invocation, and supports several methods for doing so: - Pass `-c` multiple times - Include multiple queries, separated by `;`, in one `-c` option - Pass one or more .sql files with `-f`, with multiple statements in each - Use `--result` to define which queries output data to stdout - Use `--on-error` to either `stop` or `continue` if one or more queries produces an error. In other words, this works: ```bash $ hsql -P prod --limit -1 --format md --result all --on-error stop \ -f ./setup.sql \ -c "select count(*) from raw_table" \ -f ./build-models.sql \ -c "select count(*) from modeled_table" ``` hsql's exit codes are meaningful and stable: - 0: Success - 1: Query error - 2: Usage/config error - 3: Connection error - 4: Timeout - 130: Interrupted You can also use `--stats` and `jq` together to error on a truncated query: ```bash hsql --limit -1 -c "select 1" --csv -o data.csv --stats 2>&1 | jq -e '.truncated | not' > /dev/null ``` > [!TIP] > A script or agent that runs many invocations against the same database can > hold one connection open instead of reconnecting for each one. > `hsql --serve dev -P dev` runs a session named `dev`, and > `hsql --session dev -c "..."` (or `HSQL_SESSION=dev`) sends invocations to > it, which answer in milliseconds. A session is state rather than a cache: > temp tables, settings and open transactions survive from one invocation to > the next. POSIX only; see > [warm sessions](https://harlequin.sh/docs/hsql/sessions). ## Running Safely Two options bound what an invocation can do, and hsql refuses to run at all if the adapter cannot enforce them. `--read-only` (or `-r`) connects in a mode the database itself refuses writes in: ```bash $ hsql -r "path/to/duck.db" -c "insert into orders values (1)" hsql: error: Invalid Input Error: Cannot execute statement of type "INSERT" on database "duck" which is attached in read-only mode! ``` `--timeout SECONDS` limits the duration of executing and fetching a query, and exits `4` when it runs out: ```bash $ hsql --timeout 0.5 -c "select count(*) from range(100000000000) t(i)" hsql: error: timed out after 0.5s ``` Both are also profile keys: ```toml [profiles.agent] adapter = "postgres" read_only = true timeout = 30 ``` hsql refuses to open a connection with an adapter that cannot enforce `--read-only` or `--timeout`. `hsql --info` reports `implements_read_only` and `implements_cancel` for every installed adapter. ## Describing hsql to an Agent `hsql --help` is written for a person; two flags answer the same questions as JSON, so an agent can find its way around an installation it has never seen. `--spec` is a machine-readable `--help`: every option on the command, plus the connection options every installed adapter declares, each with its type, choices, default and help text. `--info` describes the installation instead — versions, platform, the config files hsql found, the profile that would be active, and what each installed adapter declares it supports — so a script can check that an adapter is there, and what it can do, before depending on it. ```bash $ hsql --info -a sqlite | jq '.adapters.sqlite' { "distribution": "harlequin", "version": "2.10.0", "capabilities": { "implements_cancel": true, "implements_catalog_search": true, "implements_read_only": true, "implements_validate_sql": false }, "error": null } ``` Neither connects to a database, both write JSON to stdout, and `-a NAME` narrows either one to a single adapter, which is much faster than importing them all. ## Installing the Agent Skill hsql ships an [Agent Skill](https://agentskills.io) that teaches an agent how to drive it: ask before you assume, keep credentials off the command line, read the catalog before writing SQL, consider a warm session, watch the 500-row limit, branch on the exit code, and hand off to `harlequin` when a human should drive. `hsql --skill` installs it, with no network and matched to the version you have: ```bash $ hsql --skill -o ~/.claude/skills/hsql/ # for you, in every project $ hsql --skill -o .claude/skills/hsql/ # for this repo, committed with it note: wrote 6 files to .claude/skills/hsql: SKILL.md, references/config.md, references/queries.md, references/scripting.md, references/sessions.md, references/troubleshooting.md ``` With no `-o`, `hsql --skill` writes `SKILL.md` to stdout, so you can read it before you install it. In Claude Code, the same skill is a plugin in Harlequin's repository: ``` /plugin marketplace add tconbeer/harlequin /plugin install hsql@harlequin ``` ## Keep Reading at [harlequin.sh](https://harlequin.sh/docs/getting-started/hsql) Visit [harlequin.sh](https://harlequin.sh/docs/getting-started/hsql) for an overview of features and full documentation. ## Getting Help To view all command-line options for Harlequin and all installed adapters, after installation, simply type: ```bash hsql --help ``` [GitHub Discussions](https://github.com/tconbeer/harlequin/discussions) are a good place to ask questions, request features, and say hello. [GitHub Issues](https://github.com/tconbeer/harlequin/issues) are the best place to report bugs. ## Sponsoring Harlequin and hsql Please consider [sponsoring Harlequin's author](https://github.com/sponsors/tconbeer), so he can continue to dedicate time to hsql. ## Contributing Thanks for your interest in Harlequin! Harlequin and hsql are primarily maintained by [Ted Conbeer](https://github.com/tconbeer), but he welcomes all contributions! Please see [`CONTRIBUTING.md`](./CONTRIBUTING.md) for more information. ## Differences from psql `-c`, `-f`, `-t`, `-A` and `-x` mean what they mean in psql, so `hsql -tAc "select count(*) from orders"` prints a bare number, exactly as it would there. These do not: | | psql | hsql | |---|---|---| | `-P` | `--pset`, an output setting | `--profile`, a config-file profile | | Field separator | `-F` | `--csv`, `--format tsv`, or any other `--format` | | Listing databases | `-l` | `--catalog` | | Describing an object | `\d`, `\dt` | `--catalog --path`, `--catalog-search` | | Stopping on the first error | `-v ON_ERROR_STOP=1` | `--on-error stop`, which is the default | | One transaction | `-1` | write `begin` and `commit` in your script | | `-o` | a file for query output | a file, or a directory that gets one file per result set | | Connection flags | `-h`, `-p`, `-U`, built in | the adapter's, so `hsql --help -a postgres` lists them | | Row limits | none | 500 rows by default; `--limit -1` removes it | | Suppressing chatter | `-q` | nothing to suppress: stdout is only ever results | | Exit codes | `1` its own error, `2` connection, `3` script error | `1` query error, `2` usage/config, `3` connection, `4` timeout | --- ### Overview (`tests/skill_evals/README.md`) # Skill trigger evals `trigger_queries.json` is the eval set for the `hsql` skill's `description`, which is the only part of a skill always in context and the whole of what decides whether it loads. Run it with the description-optimization loop in the `skill-creator` skill, which needs the `claude` CLI. It is a pre-merge exercise on a change to the description, not a CI check: scoring it costs model calls, and the answer is a rate rather than a pass or fail. Ten positives and ten negatives, split train/test so a description tuned on one half is scored on turns it was never tuned against. The positives include turns that never say "hsql" or "harlequin", because under-triggering is the failure mode; the negatives are near misses — SQL-shaped work that is not a query, and database-shaped work that belongs to another tool. It lives here rather than beside the skill because `src/harlequin/hsql/skill/` is what ships in the wheel, and is the directory a marketplace entry points at as a plugin. ## Last run 2026-08-28, against the description as it ships: 10/10 positives and 10/10 negatives on both splits. The description was not tuned against this set — it scored at ceiling on the first pass — so the set's value now is as a regression check on the next edit to the description, and it will need harder near misses before it can distinguish one good description from another. --- ### Overview (`tests/data/unit_tests/completions/README.md`) The completions were generated from this database: ```python from sklearn.datasets import load_iris import pandas as pd import duckdb # Load the iris dataset iris = load_iris() iris_df = pd.DataFrame(data=iris.data, columns=iris.feature_names) iris_df["target"] = iris.target iris_df["species"] = pd.Categorical.from_codes(iris.target, iris.target_names) # Connect to DuckDB (this will create a new database if it doesn't exist) con = duckdb.connect("iris.db") # Create and insert data into the table con.execute(""" CREATE TABLE IF NOT EXISTS iris ( sepal_length FLOAT, sepal_width FLOAT, petal_length FLOAT, petal_width FLOAT, target INTEGER, species VARCHAR ) """) # Insert the data con.execute("INSERT INTO iris SELECT * FROM iris_df") # Verify the data (optional) result = con.execute("SELECT * FROM iris LIMIT 5").fetchall() print("First 5 rows:", result) # Close the connection con.close() ``` Essentially the database was created and then sunk the data in the completions to json using `dataclasses.asdict` and `json.dumps`. Proceeded with some manual deletion to make the file smaller. --- ### Cli Options (`docs/cli-options.md`) # CLI options: the groups, and how to add one Harlequin and `hsql` share their option machinery, and `hsql` additionally sorts every option into a **group**. This document is the reference for that, and the checklist for adding an option. ## Where an option comes from There are two kinds, and they are declared in different places. **An adapter's options** are `AbstractOption` subclasses in the adapter's own `ADAPTER_OPTIONS` list. Declaring one gets all three renderings — `to_click()` for the CLI, `to_widgets()` for the TUI, `to_questionary()` for the config wizard — and both commands attach them dynamically once the first pass has named the adapter. Nothing in core lists them. **A command's own options** are `CoreOption` declarations in `harlequin/core_options.py`, one list for both commands. Each says which command takes it (`harlequin=On(...)`, `hsql=On(...)`, or both), how that command spells it, and the flags that decide where its value may come from — `group`, `cli_only`, and `first_pass`, which is whether the pass that names the adapter reads it before there is a command to parse with. `attach_core_options()` builds the click options from that list, in the order it declares them, which is the order `hsql --help` lists them; the part only a command can answer — a `click.Choice` of what is installed, a callback, help naming a computed default — stands in the declaration as a `RuntimeValue`. These are the frozen part of each command's surface; an adapter option whose spelling collides with one loses that spelling (`first_pass.attach_adapter_options`). Every option that is not `CONN_STR` is also a **profile key**, under the name click gives it (`--read-only` is `read_only`). `config.py` validates a profile against the command's parameters plus the adapter's declared options, so a key neither declares is an error. ## The five groups `hsql` runs in two roles — a server (`--serve NAME`) and a client (`--session NAME`) — and an option belongs to exactly one group, decided by **when its value is read**. An option hsql declares cannot be declared without one; the frozensets `hsql` refuses against are derived from the declarations. | Group | Read | `--serve` | Served request | | --- | --- | --- | --- | | `Group.CONNECTION` | once, when the connection opens | accepted | compared against the session's; equal is served, differing exits 2 | | `Group.PER_REQUEST` | on every invocation | refused, exit 2 | accepted | | `Group.SERVER` | once, at start-up; only a server has one | accepted | refused, exit 2 | | `Group.ROLE` | to pick which process runs the invocation | — | — | | `Group.CONFIG` | to pick the file and profile the rest come from | accepted | accepted | Two of these need a word. **`Group.CONFIG` (`-P`, `--config-path`) names where values come from rather than being values**, so which group a profile belongs to is decided by what it holds. A profile of nothing but `format` and `limit` is per-request whichever way it was named; one that names a database is compared like a typed flag. **Every adapter option is a connection option.** `connection_option_names()` is the join: `CONNECTION_OPTIONS` plus the adapter's declared names. It is a *positive* test — a key that is neither hsql's nor the adapter's, such as one of `TUI_ONLY_KEYS` or a misspelling in a profile, describes no connection and is not compared. ## Adding an option 1. **Decide whose it is.** If it changes how a database is reached, it is an adapter option — declare it in that adapter's `ADAPTER_OPTIONS` and stop. Everything below is for a core option on `harlequin` or `hsql`. 2. **Write the `CoreOption`** in `harlequin/core_options.py`, among the ones it belongs with — where it sits in that list is where `hsql --help` lists it. Give it `harlequin=On()`, `hsql=On()`, or both, and put in `kwargs` what both commands share; an `On`'s own `kwargs` merge over those, and its `decls` replace them, which is for a difference the two commands really have. For `hsql`, keep the help one or two sentences and name the default. 3. **Give it a group**, by when its value is read: - once, at connect time → `Group.CONNECTION` - on each invocation, including every mode → `Group.PER_REQUEST` - once, for a server that is up → `Group.SERVER` An option `hsql` takes has to have one, and an option only the IDE takes has to have none — `tests/unit_tests/test_hsql_serve.py::test_every_option_is_in_exactly_one_group` pins the groups to the command they are refused against. 4. **If it is a mode** — one that reports rather than running SQL — also add it to `_one_mode()`, so two modes in one invocation are refused. A mode that reports on the installation or the config files declares `first_pass=True` and goes in the `needs_profile` / `needs_adapter` predicates in `build_cli()` too, so the first pass reads no profile for it; a mode that is configured like a run does not (`--catalog`, `--history`). 5. **If a config file may not set it**, mark it `cli_only=True`. That is for options whose value decides *which process runs the invocation*, or which a config file discovered in the working directory should not be able to weaken; `CLI_ONLY_SESSION_KEYS` and `CLI_ONLY_SSH_KEYS`, which are what refuse it, are derived from the flag. 6. **An option the IDE reads and `hsql` must not** is one with no `hsql=`: `TUI_ONLY_KEYS` is every declaration `harlequin` takes and `hsql` does not. 7. **Regenerate the committed artifacts**, which are pinned by tests: ```bash uv run python scripts/write_config_schema.py uv run python scripts/write_cli_reference.py ``` 8. **Add a `CHANGELOG.md` entry** under `[Unreleased]` if the option is user-facing. ## What the partition test still catches Group membership travels with the declaration, so the two cannot drift. What the test reads is the built command against the derived sets, which is what catches an option added to `harlequin/hsql/cli.py` as a bare `@click.option` rather than declared — it would reach the command in no group at all, and nobody would refuse it. It cannot catch a profile key that no command declares; that class is handled instead by `connection_option_names()` being a positive test. --- ### Generated Hsql Reference (`docs/generated/hsql-reference.md`) # hsql Execute SQL and exit. CONN_STR: one or more connection strings, or paths to local db files. Generated from the bare `hsql` command, with no adapters loaded. An adapter contributes its own options on top of these: `hsql --help -a NAME` shows one adapter's, and `hsql --spec` reports every installed adapter's as JSON. ## Usage ``` hsql [OPTIONS] [CONN_STR]... ``` ## Arguments | Argument | Type | Accepts | | --- | --- | --- | | `CONN_STR` | text | zero or more | ## Options Alphabetical by name. Flags are off by default. | Option | Type | Values | Default | Env var | Description | | --- | --- | --- | --- | --- | --- | | `-a`, `--adapter` | choice | `NAME` | `duckdb` | | The name of an installed database adapter plug-in to use to connect to the database at CONN_STR. | | `--catalog` | boolean | | | | List the catalog objects one level below --path, and exit without running SQL. | | `--catalog-search` | text | `TERM` | | | Search the whole catalog, at every level, for objects whose name contains TERM, and exit without running SQL. Not every adapter can; see --info. | | `--color` | choice | `auto`, `always`, `never` | `never` | | Color text output. `auto` follows the terminal and NO_COLOR. | | `-c`, `--command` | text | | | | Execute SQL. Repeatable. | | `--config` | choice | `show`, `list-profiles`, `validate`, `schema`, `init` | | | Report on the config files hsql found, or write a profile into one, and exit without running SQL. One of: show, list-profiles, validate, schema, init. | | `--config-path` | path | `PATH` | | `HARLEQUIN_CONFIG_PATH` | By default, Harlequin finds files named .harlequin.toml in the current directory and the home directory (~) and merges them. Use this option to specify the full path to a config file at a different location. | | `--csv` | boolean | | | | Shorthand for --format csv. | | `--display-rows` | integer | `N` | | | Rows printed per result set by the text layouts. -1 for all rows. [default: 40 for table, markdown, md; 10 for vertical] | | `-f`, `--file` | text | `PATH` | | | Execute SQL from a file, or from stdin for `-`. Repeatable. | | `--format` | choice | `table`, `markdown`, `md`, `vertical`, `csv`, `tsv`, `json`, `jsonl`, `ndjson`, `parquet`, `orc`, `feather`, `arrow`, `none` | `table` | | Output format. See below for the list. | | `--help` | boolean | | | | Show this message and exit. | | `--history` | boolean | | | | List the queries harlequin and hsql have run, newest first, and exit without running SQL. --limit says how many; -P, -a or a CONN_STR narrows it to one database. | | `--history-search` | text | `TERM` | | | List the logged queries whose SQL contains TERM, newest first, and exit without running SQL. Scoped like --history. | | `--idle-timeout` | number | `SECONDS` | `1800.0` | | With --serve: stop the session once it has gone SECONDS with no request. 0 for a session that waits as long as it takes. | | `--info` | boolean | | | | Versions, config files, the active profile, and what each installed adapter declares it supports, as JSON. Connects to nothing. -a narrows it to one adapter. | | `--json` | boolean | | | | Shorthand for --format json. | | `--jsonl` | boolean | | | | Shorthand for --format jsonl. | | `--limit` | integer | `N` | `500` | | Maximum rows fetched per result set. -1 for no limit. | | `--markdown` | boolean | | | | Shorthand for --format markdown. | | `--max-lifetime` | number | `SECONDS` | `28800.0` | | With --serve: stop the session SECONDS after it connected, whatever it is doing; a request already running finishes first. 0 for a session that runs until something stops it. | | `-A`, `--no-align` | boolean | | | | Unaligned output. As in psql. | | `--no-footer` | boolean | | | | Omit the row-count footer, keeping other chrome. | | `--no-header` | boolean | | | | Omit the header row, keeping other chrome. | | `--no-write-history` | boolean | | | | Do not record this run's queries in the query history that Harlequin and hsql share. | | `--null-string` | text | `TEXT` | | | Render NULL as TEXT. Defaults to NULL for text formats, empty for csv. | | `--on-error` | choice | `stop`, `continue` | `stop` | | What to do when a statement fails. | | `-o`, `--output` | path | `PATH` | | | Write results to PATH instead of stdout. Accepts a file or directory. | | `--path` | text | `TEXT` | | | Where in the catalog --catalog looks, and what --catalog-search searches under. Dotted segments, named by the adapter; the top of the catalog by default. A trailing * filters a --catalog listing. | | `-P`, `--profile` | text | | | | Select a profile from an available config file to load its values. Other options passed here will take precedence over those loaded from the profile. Use the special profile named None to use Harlequin's defaults, instead of the default profile specified in the config file. | | `--queue-timeout` | number | `SECONDS` | | | With --serve: a request waits at most SECONDS for the one before it, then exits 4 without reaching the database. [default: no limit] | | `-r`, `--read-only` | boolean | | | | Connect read-only, and refuse to run at all if the adapter cannot. To check an adapter's capabilities, use --info. | | `--result` | text | `all\|last\|N` | `all` | | Which result set(s) to emit. | | `--serve` | text | `NAME` | | | Connect, then hold the connection open as the session named NAME and answer `--session NAME` invocations from it until stopped. Takes connection and session-lifetime options; no per-request ones. Not on native Windows. | | `--session` | text | `NAME` | | | Send this invocation to the running session named NAME, started with --serve. HSQL_SESSION=NAME does the same for every invocation, and runs without the session, with a warning, when none is up. | | `--session-reset` | boolean | | | | Ask the session to close its connection and open a fresh one, and exit without running SQL. Temp tables, settings and an open transaction are gone. Needs --session. | | `--session-status` | boolean | | | | Poll the server for its status as JSON, and exit. Reports while a query is running. Needs --session. | | `--skill` | boolean | | | | Write the Agent Skill for driving hsql, as markdown. -o installs it: 'hsql --skill -o ~/.claude/skills/hsql/'. | | `--spec` | boolean | | | | Every option here, plus every installed adapter's, as JSON. -a narrows it to one adapter. | | `--ssh-allow-reuse` | boolean | | | | When the local port is already bound, warn and connect through the listener that has it instead of failing. | | `--ssh-batch-mode` | boolean | | | | Fail rather than prompt for a passphrase, a password or a host key. ssh's own BatchMode. | | `--ssh-forward` | text | `TEXT` | | | A local forward, spelled as ssh -L takes one: LOCAL:HOST:REMOTE. Repeat this option for more than one. Omit it when your ssh config already has the LocalForward. | | `--ssh-host` | text | `TEXT` | | | Open an SSH tunnel to this destination first, and connect through it. A Host alias, host, user@host or ssh://user@host:port, passed to ssh verbatim. | | `--ssh-timeout` | number | `SECONDS` | | | Seconds to wait for the tunnel's forwards. Default is 60. | | `--stats` | boolean | | | | Write a one-line JSON summary to stderr. | | `--timeout` | number | `SECONDS` | | | Cancel the run after SECONDS and exit 4. Refused if the adapter cannot cancel a query; to check, use --info. | | `-t`, `--tuples-only` | boolean | | | | Rows only: no header, no footer. As in psql. | | `--version` | boolean | | | | Show the version and exit. | | `-x`, `--vertical` | boolean | | | | Shorthand for --format vertical. As in psql. | `-a`/`--adapter` names an installed adapter plug-in. `hsql --info` reports adapters installed in your environment. ## Formats `--format` takes one of these names, which affect the layout of data in stdout. Modify output further with options above (e.g., `-t`, `-A`, `--display-rows`). `none` runs the SQL but generates no output. The suffix is what `-o DIRECTORY` names a file with. | Format | Kind | Suffix | Holds several result sets | | --- | --- | --- | --- | | `table` | text layout | `.txt` | yes | | `markdown` | text layout | `.md` | yes | | `md` | text layout | `.md` | yes | | `vertical` | text layout | `.txt` | yes | | `csv` | file | `.csv` | no | | `tsv` | file | `.tsv` | no | | `json` | file | `.json` | no | | `jsonl` | file | `.jsonl` | yes | | `ndjson` | file | `.ndjson` | yes | | `parquet` | file | `.parquet` | no | | `orc` | file | `.orc` | no | | `feather` | file | `.feather` | no | | `arrow` | file | `.arrow` | no | | `none` | writes nothing | | yes | ## Exit codes | Code | Meaning | | --- | --- | | `0` | Success. | | `1` | The database rejected the SQL. | | `2` | A bad flag, a bad profile, or a config file hsql could not read. | | `3` | hsql could not connect. | | `4` | `--timeout` ran out, and hsql stopped the run. | | `70` | hsql hit a bug in itself and wrote a crash report. | | `130` | Interrupted. | --- ### Plans Crash Recovery Design (`docs/plans/crash-recovery-design.md`) # Crash recovery and bug reporting The design behind [#687](https://github.com/tconbeer/harlequin/issues/687): never dump a raw traceback at a user, preserve their work when something goes wrong, and make the report they send us worth reading. ## The problem An uncaught exception on Harlequin's message pump falls straight through `AppBase._handle_exception` (`app_base.py:68`) into Textual's `_fatal_error()`, which renders a `rich.traceback.Traceback` **with `show_locals=True`** and prints it to stderr. That is intimidating, it tells the user nothing about what to do next, and it puts local variables — connection strings, tokens, query results — on the terminal. Replacing it is a privacy fix as much as a UX one. `hsql` has the same hole from the other end. `main()` (`hsql/__init__.py`) catches `ClickException`, `Abort` and `KeyboardInterrupt`; anything else escapes to Python's own handler, which prints a traceback and exits 1 — and 1 is `ExitCode.QUERY`, so a bug in Harlequin is indistinguishable from "the database rejected the SQL." A caller scripting against hsql's exit codes cannot tell the two apart. Three problems compound it: - **The user's work is lost.** `write_editor_cache` is called from exactly one place, `Harlequin.action_quit()` (`app.py:970`). A crash, a `kill -9`, a closed terminal or a dropped ssh session loses every open buffer — as does the `self.exit(return_code=2)` path. - **The reports we get are thin.** `bug_report.md` asks for version, adapter, shell, terminal, OS, locale and install method as free text, so most reports arrive missing half of it. - **A crash at startup could repeat forever.** [#745](https://github.com/tconbeer/harlequin/issues/745) crashed *inside* `EditorCollection.on_mount` while replaying cached buffers. Saving buffers on crash without a guard turns a one-time crash into a crash loop, which is worse than what we have now. Outcome: a crash exits cleanly with a short panel naming a crash report file; the user's buffers survive and come back on the next launch; a crash *while restoring* those buffers cannot repeat; and the report drops into an issue template built to receive it. ## The two ideas that carry it **A per-process recovery file, separate from the shared cache.** Checkpoints go to `recovery-.pickle`, never to `cache-1.pickle`. Two Harlequins on one machine keep today's comprehensible "last clean quit wins" semantics instead of thrashing a shared file every minute, and a checkpoint can never overwrite what another process wrote when it quit cleanly. **Rename before replay.** `os.replace` the recovery file to `.replayed` *before* unpickling and replaying it. If unpickling or replaying crashes, the next start finds nothing to replay. That is the whole crash-loop guard — one line, no session sentinel, no PID-liveness check, and correct across concurrent processes by construction. **Where the crash message goes.** Textual's `panic()` calls `_close_messages_no_wait()`, so the message pump is gone the moment we handle the exception: an in-app modal is unreachable from `_handle_exception`, and attempting one re-enters an app that has already proven it is broken. The message is a rich `Panel` at `_exit_renderables[0]`, printed to stderr after the terminal is restored — the same mechanism `AppBase.__init__` already uses for a bad theme. ## `editor_cache.py` ```python CHECKPOINT_INTERVAL_SECONDS = 60.0 RECOVERY_STALE_SECONDS = 3 * CHECKPOINT_INTERVAL_SECONDS def write_cache(cache: Cache) -> bool # now atomic, and never raises def get_recovery_file() -> Path # cache dir / f"recovery-{os.getpid()}.pickle" def write_recovery(cache: Cache) -> bool def clear_recovery() -> None def adopt_recovery() -> tuple[Cache | None, Path | None] ``` `CACHE_VERSION` stays `1` — the pickled shape does not change. - **Atomic write.** Both writers go through `_write_pickle(cache, path)`: a temp file in the same directory (carrying the pid, so a killed process leaves at most one bounded stray), `flush()` + `os.fsync()`, then `os.replace()`. Same-directory means a same-volume rename, atomic on POSIX and on Windows. The `fsync` is what stops a power loss leaving a zero-length file. - **Never raises.** Returns `False` on `OSError` / `pickle.PicklingError`. Both callers run where an exception would itself take the app down. On Windows `os.replace` raises `PermissionError` when another process holds the destination open, so a checkpoint has to be allowed to skip silently. - **`adopt_recovery()`** globs `recovered-*.pickle` (written by the crash handler) newest first, then `recovery-*.pickle` whose mtime is older than `RECOVERY_STALE_SECONDS` — that age test is how a live sibling process's file is left alone, without a PID-liveness check that would be wrong on Windows and wrong under PID reuse. It renames the chosen file to `.replayed` **before** unpickling it, and returns `(cache, replayed_path)` so the app can say where the buffers came from. Everything wrapped → `(None, None)`. Derive `get_recovery_file()` from the same `user_cache_dir` call as `get_cache_file()`, so `tests/functional_tests/test_cache.py:43`'s existing monkeypatch covers the new files for free. ## Checkpointing (`app.py`) - `on_mount`: `self.set_interval(CHECKPOINT_INTERVAL_SECONDS, self._checkpoint_editor_cache)`. - `_checkpoint_editor_cache()`: - bail when `getattr(self, "editor_collection", None)` is None or not mounted — the collection is created in `compose()`, not `__init__`; - build `Cache(focus_index=..., buffers=...)` on the message pump (the `buffers` property queries widgets — `code_editor.py:294` calls `_save_loaded_buffer()` first); - **bail when every buffer is blank.** A startup crash gives `buffers == []`, and writing that over a good recovery file destroys exactly the work this feature exists to save; - bail when `cache == self._last_checkpointed_cache`. `Cache`/`BufferState` are dataclasses and `Selection` is a NamedTuple, so `==` is an exact content comparison — no dirty flag to keep in sync across tab swaps; - `write_recovery(cache)`, synchronously. Pickling a few SQL buffers is microseconds; a thread worker would add ordering hazards (two checkpoints racing; `exclusive=True` cancelling one mid-flight while its thread still completes the write) for no measured win. - `action_quit()` keeps its `write_editor_cache(...)` and then calls `clear_recovery()`. ## Restoring (`components/code_editor.py`) `EditorCollection.__init__` keeps `self.startup_cache = load_cache()` and adds `self.recovered_cache, self.recovered_from = adopt_recovery()`. **Two separate module-level names**, not folded into `load_cache()`, so `tests/functional_tests/conftest.py`'s `no_use_buffer_cache` fixture stays the one seam. `on_mount` prefers `recovered_cache` over `startup_cache` when present — a recovered session started from the cache, so it is strictly newer — and then notifies: *"Recovered buffers from a session that ended unexpectedly."* The shared `cache-1.pickle` is left untouched, so if the recovered content is itself the problem, the last clean-quit state is still on disk. ## `environment.py` (new, headless) Facts about the running installation, shared by `hsql --info`, the crash report, and (later) the Debug Info screen. No textual, no rich; `adapter_facts` defers `harlequin.plugins` into its body as `info._adapters` already does. ```python def harlequin_version() -> str def python_facts() -> dict[str, str] # version, implementation, executable def platform_facts() -> dict[str, str] # system, release, machine def terminal_facts() -> dict[str, Any] # term, term_program(+version), colorterm, shell, # wsl_distro, ssh (bool), locale, size def install_facts() -> dict[str, Any] # installer, in_venv def adapter_facts(only: str | None = None) -> dict[str, dict[str, Any]] def runtime_report() -> dict[str, Any] ``` - `terminal_facts()` reports `ssh: True/False` and **never the value of `SSH_CONNECTION`**, which holds IP addresses. `size` from `shutil.get_terminal_size()`. - `install_facts()` reads `distribution("harlequin").read_text("INSTALLER")` (pip/uv/pipx write it) and `sys.prefix != sys.base_prefix` — that answers the bug template's install-method checkbox automatically. - `adapter_facts` is `info._adapters` + `info._capabilities` moved verbatim, minus the `diagnostics.note()` call, which a shared module cannot make. `hsql/modes/info.py` then calls these instead of building the facts itself, and keeps its stderr note by walking `adapter_facts()`'s result for entries with an `error`. **Acceptance criterion: `hsql --info`'s JSON is byte-identical.** `terminal_facts` and `install_facts` are *not* added to that document — it is an agent-facing contract with a generated reference pinned by `test_cli_reference.py`, and `TERM` means nothing to a non-interactive program. Add `harlequin.environment` and `harlequin.crash` to the `source_modules` of the "adapter API is reachable without the TUI" import-linter contract, and to `HEADLESS_IMPORTS` in `tests/unit_tests/test_import_hygiene.py`. Both have to stay headless anyway, because `hsql` imports them. ## `crash.py` (new, headless) ```python CRASH_REPORT_KEEP = 10 ISSUE_URL = "https://github.com/tconbeer/harlequin/issues/new?template=crash_report.md" def get_crash_report_dir() -> Path def root_cause(error: BaseException) -> BaseException def build_crash_report(error: BaseException, context: Mapping[str, Any], program: str = "harlequin") -> str def write_crash_report(report: str) -> Path | None def crash_message(report_path: Path | None, error: BaseException, saved: bool, program: str = "harlequin") -> str ``` - **Location.** `platformdirs.user_log_path(appname="harlequin", appauthor=False)` — `~/Library/Logs/harlequin` on macOS, `~/.local/state/harlequin/log` on Linux, `%LOCALAPPDATA%\harlequin\Logs` on Windows. Not the cache dir: a cache is what a user is told to delete when things go wrong, and this is the file we are asking them to keep. Match `config.py`'s `appauthor=False` — `editor_cache`/`catalog_cache` omit it and so nest twice on Windows; don't copy that. - **`root_cause`** unwraps a Textual `WorkerFailed` via `getattr(error, "error", None)` rather than importing it, so this module stays headless. It matters: `database_tree.py:498` (`_loader`) and `code_editor.py:88` (`read_symbols`) are the two `@work` decorators in `src/` that omit `exit_on_error=False`, so they *do* reach `_handle_exception`, wrapped. A report naming `WorkerFailed` is a useless report. - **Content**, in order: a header telling the user to review the file before sharing and linking `ISSUE_URL`; `runtime_report()`; the `context` the caller supplied; `traceback.format_exception` for the root cause and, when different, the wrapper — **no `show_locals`**; and last, under a heading that says what it is, the SQL in the active buffer. - **Plain text, not markdown**, written as `crash-{utc:%Y%m%dT%H%M%SZ}-{pid}.log`. The user pastes this into a fenced block in an issue, and a markdown report's own ` ``` ` fences would break out of it. Sections are delimited with plain `=== SECTION ===` rules for the same reason. - **The active buffer's SQL is included deliberately.** It is usually what is needed to reproduce a TUI bug, the file is local, and the user chooses whether to paste it. The tradeoff is real — SQL holds table names and literals — so it goes last, under an explicit heading, and both the panel and the issue template say to review before sharing. Only the *active* buffer; the rest live in the recovery file. - **Redaction.** The whole assembled string goes through `redact.redact_text()` once at the end — one choke point, matching `hsql/diagnostics.py::_write`'s discipline. - **Pruning.** Newest `CRASH_REPORT_KEEP` kept. Distinct filenames per crash, so a loop cannot destroy the evidence. `write_crash_report` returns `None` and raises nothing when the directory is unwritable. `exception.py` gains `class HarlequinCrashError(HarlequinError): pass`, so the panel is `pretty_error_message(HarlequinCrashError(crash_message(...), title="Harlequin crashed."))` — no new rendering code, rich stays lazily imported, and it looks like every other Harlequin panel. ## The handler (`app_base.py`) Two hooks with working base implementations, so `keys_app.py` gets crash reports too: ```python def _save_work_on_crash(self) -> bool: """Persist anything the user would lose. False if there was nothing to save.""" return False def _crash_context(self) -> dict[str, Any]: """What this app was doing, for the crash report.""" return {} def _handle_exception(self, error: Exception) -> None: if self._exit or self._crash_handled: return self._crash_handled = True saved = False try: saved = self._save_work_on_crash() except BaseException: pass report_path = None try: report = build_crash_report(error, self._crash_context()) report_path = write_crash_report(report) except BaseException: pass try: self.bell() self._return_code = 1 # run_test re-raises from these if self._exception is None: self._exception = error self._exception_event.set() message = crash_message(report_path, error, saved) panel = HarlequinCrashError(message, title="Harlequin crashed.") self.panic(pretty_error_message(panel)) except BaseException: # last resort: the user gets *something* super()._handle_exception(error) return # textual run --dev, i.e. `make serve`: append the full traceback at index 1 if "debug" in self.features: super()._handle_exception(error) ``` /* Detailed source-code truncated for AI context efficiency. */ ```python CRASH = 70 """hsql hit a bug in itself. `sysexits.h`'s EX_SOFTWARE.""" ``` 70 rather than the next free small integer: `sysexits.h` reserves it for an internal software error, and it cannot be confused with the 1–4 hsql already documents or with 130. **`exit_code_for()` does not change.** It maps *caught* failures, and an unrecognized error caught while running a query genuinely is a query failure — that is what `QUERY` means. `CRASH` belongs only at the boundary where nothing else caught the error at all. **`main()` (`hsql/__init__.py`) gains a final handler**, after the `ClickException` and `Abort`/`KeyboardInterrupt` clauses: ```python except BaseException as e: report_path = write_crash_report(build_crash_report(e, _crash_context(argv), program="hsql")) diagnostics.report_crash(report_path) sys.exit(ExitCode.CRASH) ``` /* Detailed source-code truncated for AI context efficiency. */ ```markdown --- name: Crash Report about: Harlequin or hsql exited unexpectedly and wrote a crash report. title: 'Crash: ' labels: 'bug' assignees: '' --- **Before proceeding, please acknowledge**: - [ ] I have searched Issues and Discussions in this repo for this error. **Paste your crash report below.** Harlequin and `hsql` write a crash report and print its path when they exit unexpectedly. If you still have that message on screen, the path is in it. Otherwise, the reports are here: | OS | Location | |---|---| | macOS | `~/Library/Logs/harlequin/` | | Linux | `~/.local/state/harlequin/log/` | | Windows | `%LOCALAPPDATA%\harlequin\Logs\` | The newest `crash-*.log` is the one you want. > [!IMPORTANT] > Please read the report before pasting it. It contains your configuration > (with passwords masked) and the SQL that was in your active buffer.
Crash report ``` PASTE YOUR CRASH REPORT HERE ```
**What were you doing when it crashed?** Steps to reproduce it, if you know them. **Anything else?** Screenshots, or anything the report doesn't cover. **Contributing** Are you interested in contributing a fix? - [ ] Yes - [ ] Maybe - [ ] No ``` `bug_report.md` gains one line under its acknowledgements pointing here: *"Did Harlequin exit with an error and print a crash report path? Use the Crash Report template instead."* ## Ordered commits Each is independently green under `make check`. 1. `write_cache` atomic and non-raising. `editor_cache.py` only; no behavior change visible to the app. 2. Recovery file + rename-before-replay. `editor_cache.py`, `code_editor.py`, `app.py`'s `clear_recovery`, conftest patches. Nothing writes a recovery file yet, so this is provably inert on the happy path. 3. The checkpoint timer — commit 2's machinery gets a producer. 4. `environment.py` + the `info.py` refactor. Acceptance: `hsql --info` byte-identical. Import-linter contract and `HEADLESS_IMPORTS` in the same commit. 5. `crash.py`, `HarlequinCrashError`, `AppBase._handle_exception`, `Harlequin`'s two hooks, `hide_secrets_in`, `ctx.exit(return_code)`, and the `test_cli.py` fixture fix. 6. `ExitCode.CRASH`, `diagnostics.report_crash`, the `main()` handler, hsql's `_crash_context`. 7. `.github/ISSUE_TEMPLATE/crash_report.md` and the `bug_report.md` pointer. ## Test infrastructure — the landmine `run_test`'s re-raise path goes through `_handle_exception`, so **every failing functional test in the suite would write a crash report into the developer's real `user_log_dir`.** Add to `tests/conftest.py`, autouse and session-wide with no marker opt-out, in the spirit of the existing `no_discovered_config`: ```python @pytest.fixture(autouse=True) def crash_reports_go_to_tmp(monkeypatch, tmp_path) -> Path: """Every crash a test causes writes here, not into the developer's log dir.""" report_dir = tmp_path / "crash-reports" monkeypatch.setattr("harlequin.crash.get_crash_report_dir", lambda: report_dir) return report_dir ``` Extend `tests/functional_tests/conftest.py::no_use_buffer_cache` in the same commit as the checkpoint timer: patch `harlequin.components.code_editor.adopt_recovery` → `lambda: (None, None)` and `harlequin.app.write_recovery` / `clear_recovery` → no-ops. Keep calling the existing `harlequin.app.write_editor_cache` name so the current patch keeps working. `tests/unit_tests/test_cli.py::mock_harlequin` is `MagicMock(spec=Harlequin)`, so `tui.return_code` is a MagicMock and `ctx.exit(mock)` makes `res.exit_code` a MagicMock — **about fifteen existing assertions break.** Set `mock_harlequin.return_value.return_code = 0` in the fixture, and add a test that `return_code = 1` produces `res.exit_code == 1`. ## Tests to add **`tests/unit_tests/test_editor_cache.py`** (new): no `*.tmp` left behind; a `pickle.dump` that raises leaves the previous cache byte-identical and returns `False`; an unwritable directory returns `False`; `adopt_recovery` renames to `.replayed` *before* returning (the crash-loop guarantee); it ignores a fresh `recovery-.pickle` and adopts a stale one; it always adopts `recovered-*`. **`tests/unit_tests/test_crash.py`** (new): the report names the exception type, message, version and frames; **it prints no secret** — `hide_secrets_in({"conn_str": ["postgres://u:hunter2-and-more@h/db"]})`, raise an error quoting the DSN, assert the secret is absent and `********` present, mirroring `test_the_debug_screen_prints_no_secret`; the report contains no ` ``` ` fence, so it pastes into one; `root_cause` unwraps a `WorkerFailed`; `write_crash_report` prunes to `CRASH_REPORT_KEEP` and returns `None` on `PermissionError`. **`tests/functional_tests/test_crash.py`** (new), injecting through `_handle_exception` directly: ```python with pytest.raises(RuntimeError): async with app.run_test() as pilot: ... app._handle_exception(RuntimeError("boom")) await pilot.pause() ``` Assert `app.return_code == 1`; one crash file in the redirected dir; a recovery file holding the buffer text (`@pytest.mark.use_cache`); `"boom"` and the report path in the rendered `_exit_renderables[0]`; `"Traceback (most recent call last)"` **not** in it. Plus: two exceptions in a row produce one report and no recursion; a `write_crash_report` that raises still exits 1 with a panel; `Harlequin(...)` constructed but never run returns `False` from `_save_work_on_crash` without raising (crash before mount); a blank-buffer checkpoint leaves a good recovery file unchanged; and the crash loop — plant a `recovered-*.pickle`, make `action_new_buffer` raise, run and crash, then run a second app and assert one empty buffer and the poisoned file now `.replayed`. **`tests/unit_tests/test_hsql.py`**: a callback that raises exits `ExitCode.CRASH` (70) and not `QUERY`; the stderr line names the report path; a DSN passed as `CONN_STR` does not appear in a report written before `hide_secrets_in` has run. **No snapshot churn.** Nothing renders in-app except the recovery toast, which is a standard Textual notification; the crash message reaches stderr after the app is gone. ## Verification ```bash localedef -i en_US -f UTF-8 en_US.UTF-8 # fresh container only make check # ruff, pytest 3.10 + 3.12, mypy uv run lint-imports uv run pytest tests/unit_tests/test_import_hygiene.py tests/unit_tests/test_crash.py \ tests/unit_tests/test_editor_cache.py uv run pytest tests/functional_tests/test_crash.py tests/functional_tests/test_cache.py -n0 ``` By hand: 1. `uv run harlequin f1.db`, type SQL, wait past the checkpoint interval, `kill -9`, restart — the SQL is back and the toast says where it came from. 2. Temporarily raise from a key handler and run **without** dev mode (`uv run harlequin f1.db`, not `make serve`): the panel prints instead of a traceback, `echo $?` is 1, and the report at the named path holds the traceback. Repeat with a profile carrying a password and confirm it is masked and that no local variables appear. 3. Temporarily raise from `EditorCollection.on_mount`, crash, remove the raise, restart twice: the second start is clean and the poisoned file is `.replayed`. 4. `make serve` and crash: the full traceback still prints for developers. 5. Temporarily raise from an hsql mode: `echo $?` is 70, the stderr line names the report, and a DSN on the command line is masked in it. 6. Paste a real report into the crash report template's fenced block on a scratch issue and confirm it renders as one block. ## Known risks - **Partial-replay convergence.** If a crash happens partway through replaying recovered buffers, `_save_work_on_crash` may write a new `recovered-*.pickle` holding the subset that loaded. The next start replays fewer buffers, so it converges and terminates — and salvages what it can — but it can take more than one restart. - **Behavior change: both commands now exit non-zero where they exited 0.** `harlequin` returns 1 on a crash and 2 on a config or connection error; `hsql` returns 70 where it previously let Python exit 1. Correct, but user-visible, so both get a changelog line. - **The report names `sys.executable`, the config path and `SHELL`**, all of which reveal a username — and all of which `hsql --info` already prints, so this is consistent rather than new. ## Deliberately out of scope - **`_handle_worker_error` swallows unknown worker errors** ([#1117](https://github.com/tconbeer/harlequin/issues/1117)). `app.py:576` branches on two worker names with no `else`, so any other worker error vanishes silently. The fix needs its own triage of which workers can fail benignly, so it is filed separately. - **Debug Info screen work** — environment rows and a "copy a bug report" binding. Cheap once `environment.py` exists, but it is the only piece with snapshot churn: a visible version or Python row bakes `2.12.2` and `3.10.14` into `Debug Info Screen.svg`, which then fails on every CI Python and after every release. If picked up, put the rows in a **collapsed** `Collapsible` (collapsed children never reach the SVG) and back them with a non-snapshot unit test. Drive-by finding for whoever does: `DebugInfoScreen.move_focus()` queries `#collapsible-client-adapter-details`, which `AdapterDebugInfo.parse_info()` never produces — dead code with a latent `NoMatches`. - **Narrowing hsql's broad `except Exception` handlers** so an internal bug caught inside `cli.py` reports `CRASH` rather than `QUERY`. - A prefilled GitHub issue URL (the ~8KB URL cap cannot carry a traceback) and converting the templates into YAML issue forms. - A `harlequin --debug` flag: the report already holds the full traceback, and `TEXTUAL=debug` / `make serve` already prints it inline. --- ### Plans Harlequin For Agents (`docs/plans/harlequin-for-agents.md`) # Harlequin for Agents **A product plan for making Harlequin the best SQL client for coding agents.** Status: draft for discussion. Product and user stories only — implementation details are deliberately sketched, not specified. Scope: the `harlequin` package (this repo) and the `harlequin-web` site (). --- ## 1. The opportunity Today an agent asked to "look at my warehouse" reaches for whatever CLI is nearest: `psql`, `duckdb`, `bq`, `databricks sql`, `mysql`, `trino`. Each has different flags, different output formats, different auth, different error semantics, and different ideas about what "give me the schema" means. The agent burns tokens rediscovering all of that on every task, and it hallucinates flags for the ones it has seen least. Harlequin already has the thing that fixes this, and nobody else does: - **One uniform adapter interface across ~15 databases** — DuckDB, SQLite, Postgres, MySQL, BigQuery, Trino, Databricks, ODBC, ADBC, Cassandra, NebulaGraph, RisingWave, Wherobots, Exasol. - **A normalized catalog abstraction** (`Catalog` / `CatalogItem`) that already answers "what tables exist and what are their column types" without dialect-specific `information_schema` queries. - **Profiles in a config file** that already encode "how to connect to my warehouse," discovered automatically from the cwd and home directory. - **sqlfmt** as an existing dependency. - **A TUI on the other end of the same config**, which no other CLI has. The pitch writes itself: **`hsql -P prod -c "select 1"` works the same way against every database your team uses.** One CLI contract, one set of flags, one output format vocabulary, one exit-code scheme — and when the agent needs a human, the human opens Harlequin against the same profile and sees the same catalog. That last point is the real differentiator. Every other SQL client can be made agent-friendly. Only Harlequin can make the *handoff* good. ## 2. Who we are designing for Three personas, all of which are "an agent," but with materially different needs. ### P1 — Agent as operator Runs queries headlessly on a human's behalf, usually in a loop, usually inside a coding agent's Bash tool. Cares about: deterministic output, honest truncation, exit codes, token-efficient results, not accidentally dropping a table. ### P2 — Agent as pair Works alongside a human who has Harlequin open. Drafts a query the human will run; reads what the human already ran. Cares about: handoff in both directions. ### P3 — Agent as support engineer Helps a human install, configure, and troubleshoot Harlequin — "why won't it connect to Snowflake," "add a prod profile to my config," "what does this error mean." Cares about: machine-readable docs, a formal config spec, a self-describing CLI, good diagnostics. Most existing "AI-friendly CLI" work only serves P1. Serving P2 and P3 is where Harlequin can be uniquely good. ## 3. Design principles These are the calls I'd want made up front, because they resolve most of the small decisions downstream. 1. **One engine, two front doors.** `harlequin` (TUI) and `hsql` (headless) share adapters, config discovery, profiles, limit semantics, and one execution core. Neither reimplements the other. See §4. 2. **stdout is data; stderr is narration.** Results go to stdout and nothing else does. Truncation notices, warnings, errors, and `--stats` go to stderr — and nothing that stdout already carries: the row count is the result's own footer, and a timing is a field of `--stats`. This is already the intent in `exception.py` — make it a hard, tested contract. 3. **Exit codes are an API.** Documented, stable, and distinct enough that an agent can branch on them without parsing text. 4. **Determinism over cleverness: identical output whether stdout is a TTY, a pipe, or a pty.** Agent harnesses vary in which they give you. A client that formats differently under a pipe is a bug factory. No TTY-sniffing for format selection. (Color is the one exception, and it follows `NO_COLOR` / `--color`.) 5. **Token efficiency is a feature.** Results cost the agent money. Defaults should be small, and truncation must always be announced — never silent. 6. **Be boring and psql-shaped.** An agent's prior is `psql`, secondarily `duckdb`. Every flag we can borrow, we borrow. Novel spellings cost tokens and cause retries. 7. **Never make the agent guess.** Everything Harlequin knows about itself — options (including plugin-contributed ones), profiles, formats, adapters, capabilities, catalog — should be dumpable as JSON on demand. This is the one place we should be *more* capable than psql, because our option surface is dynamic and plugin-driven, so no amount of pretraining can teach an agent what's actually installed. 8. **Safety is a product feature, not a warning in the docs.** `--read-only`, `--timeout`, and `--dry-run` are what let a human hand an agent a warehouse credential without flinching. 9. **No LLM inside Harlequin.** No API keys, no model config, no vendor coupling. We make Harlequin excellent for the agent already sitting next to the user. (See §10 for how this answers issue #952.) --- ## 4. Shape of the CLI: two commands, one engine **Decision: headless mode ships as a separate command, `hsql`, not as flags on `harlequin`.** The tempting reason — it frees the flag namespace — is the weakest one. The real reasons are these. **Two audiences want two stability contracts.** The TUI's promise is "delight a human, evolve freely." The headless promise is "output format and exit codes are an API, frozen." Inside one command those pull against each other on every release, and a TUI change can silently break a machine contract. A separate command makes the boundary structural rather than something a reviewer has to remember. **Cold start stops being a discipline problem.** With one entry point, "never import Textual on the headless path" is a rule any contributor can regress silently. With a separate module graph it becomes an import-linter test in CI — *the `hsql` module graph must not contain `textual`* — which is the difference between hitting the 300ms target and hoping to. **It opens a lean install later.** `pip install hsql` with no Textual, no pyperclip, is plausible for CI images and agent sandboxes, and hard to reach from a single entry point. ### What actually ships **The same package, with a second console script:** `hsql = "harlequin.hsql:main"` alongside the existing `harlequin` script. That is the key de-risking move. **The command split is cheap and near-reversible; the package split is the expensive part, and it can wait indefinitely** — possibly forever. We get the boundary and the enforceable import graph now, and decide about a separate distribution only if the lean-install demand materializes. `harlequin` itself gains **nothing**: no `-c`, no growth in an already-large `--help`, no behavior change. The one addition is a custom unknown-option handler, so `harlequin -c "select 1"` fails with *"`-c` isn't a harlequin option — did you mean `hsql -c`? See harlequin.sh/docs/headless."* Everything in Workstream B lands as `hsql` subcommands: `hsql catalog`, `hsql describe`, `hsql fmt`, `hsql spec`, `hsql info`, `hsql config`, `hsql history`, `hsql mcp`, `hsql open`. Bare `hsql` with no arguments prints help; it never launches the TUI. > **Amended in M2's plan: these are mode options, not subcommands.** `hsql --catalog`, > `hsql --info`, `hsql --spec`, `hsql --config show`. It matches `harlequin --config`, so > one shape covers both commands — and it sidesteps the objection this section raises > against subcommands, which applies here too: `hsql` takes `CONN_STR` positionally, so > `hsql catalog` and a database file named `catalog` would have needed a disambiguation > rule, docs for the rule, and a test for the rule. No verb, no rule. ### Consequences - **The `-f` collision disappears, and with it the plan's only breaking change.** `harlequin -f` stays `--show-files` forever; `hsql -f` is `--file`, as psql users expect. Nothing here needs to wait for 3.0 — the whole plan becomes additive and can ship in 2.x minors. - `hsql` gets clean access to `-c`, `-f`, `-o`, `-A`, and friends, so psql muscle memory works without compromise. - `hsql --help` omits every TUI-only option (`--theme`, `--keymap-name`, `--show-files`, `--show-s3`, `--locale`, `--config`, `--keys`), which is a real token saving for an agent reading it. It also omits the adapter connection-option matrix: `hsql --help` lists the *names* of installed adapters and points at `hsql --help -a ` for one adapter's options. That keeps the first thing an agent reads small and stable, and keeps `--help` true for every adapter rather than showing whichever one is the default. - The handoff gets a natural verb: `hsql open query.sql` reads much better than `harlequin --new-buffer query.sql`. ### Alternatives rejected - **Flags on `harlequin`.** Requires the 3.0 `-f` break, grows an already-large help surface, and leaves cold start and the stability boundary as matters of discipline. - **Subcommands on `harlequin` with a bare-invocation TUI fallback** (`harlequin query -c ...`). Needs a custom Click `Group` that falls back to the TUI when the first argument isn't a known subcommand, and it is genuinely ambiguous: a DuckDB file named `catalog` or `config` in the cwd resolves as a subcommand. Low probability, but exactly the confusing failure we don't want an agent hitting. ### On the name `hsql` is free on PyPI as of this writing — M0 in §11 secures it before anything else starts. HSQLDB ships `sqltool` and `java -jar`, not a binary named `hsql`, so there's no PATH collision — the cost is search-results mindshare with HyperSQL, which is real but mild and fading. `hq` is taken. `harlequin-cli` is free but too long for something an agent types a hundred times in a task; short names are cheap in tokens and cheap in working memory. I'd take `hsql`. It's obviously "harlequin sql," and it sharpens the positioning rather than diluting it: > **Harlequin** — the SQL IDE for your terminal. > **hsql** — the same engine, headless, for scripts and agents. > Fifteen databases, one adapter ecosystem. That's a better story than "one tool with a flag." --- ## 5. Workstream A — `hsql`, headless execution Closes [#524](https://github.com/tconbeer/harlequin/issues/524). This is the foundation; everything else in this plan assumes it. ### Proposed surface ``` hsql [OPTIONS] [CONN_STR]... Execute SQL and exit hsql [OPTIONS] catalog, describe, fmt, spec, info, config, history, open, mcp -c, --command TEXT Execute SQL. Repeatable. -f, --file PATH Execute SQL from a file (or `-` for stdin). Repeatable. -o, --output PATH Write results to PATH instead of stdout. --format NAME Output format (see below). Default: table. --csv/--json/--jsonl/--markdown/--vertical Format shorthands. -t, --tuples-only Rows only: no header, no footer. As in psql. -A, --no-align Unaligned output. As in psql. --no-header Omit the header row (keep other chrome). --null-string STR Render NULL as STR. Default: empty for csv, "NULL" for table. -P, --profile NAME Same profiles as the TUI. --limit N Max rows fetched per result set. Default: 500. -1 = no limit. --result all|last|N Which result set(s) to emit. Default: all. --on-error stop|continue Default: stop. --stats Write a one-line JSON summary to stderr. --color auto|always|never Default: never. --timeout SECONDS Cancel the query and exit non-zero. --read-only Refuse to run if the adapter can't enforce read-only. --dry-run Parse/validate (and EXPLAIN where supported) without executing. --single-transaction Wrap the whole script; roll back on any error. ``` Config discovery, profiles, and precedence are identical to the TUI: CLI > env > `--config-path` > cwd config > user config dir > home > defaults. That's the whole point — `hsql -P prod -c ...` means the agent never handles a credential. ### Formats | Format | Use | | --- | --- | | `table` (default) | Aligned text. Human-legible, deterministic. | | `markdown` / `md` | Pipe table. The most reliable format for an LLM to read back. | | `csv`, `tsv` | Pipelines, spreadsheets. | | `json` | Array of row objects. Matches `duckdb -json`. | | `jsonl` / `ndjson` | One object per line. Streaming, large results. | | `vertical` | psql `\x`. One column per line — best for inspecting a single wide row. | | `parquet`, `arrow`, `orc` | Bulk handoff. Already implemented in `export.py`. | | `none` | Discard rows; report status only. For DDL/DML/ETL. | > **Amended in implementation.** There is no `-F`: that is psql's > `--field-separator`, and a flag that sets a delimiter in one command and picks a > format in the other is a mistake waiting for a script — the same reasoning that > keeps `-l` off `--limit`. The format is chosen by `--format` spelled out, or by > the `--csv`/`--json`/`--jsonl`/`--markdown`/`--vertical` shorthands, which cover > the choices anyone types often enough to want a short flag for. ### Behaviors that matter more than the flag list **Truncation is always announced.** If `--limit` bites, stderr gets `note: results truncated at --limit 500; pass --limit -1 for all rows`, and text formats append a visible `… 500 of >500 rows` footer. Note that `--limit` here is a *hard* limit — it caps what leaves the database, via the adapter's `set_limit()`. The TUI's `--limit` is a soft display cap: it fetches everything and caps what loads into the viewer, which is why the TUI can report an exact `Showing 100,000 of 3,412,887`. `hsql` deliberately doesn't buy that number, so it says `>500`; only `--limit -1` yields an exact count. The TUI's default of 100,000 is right for a TUI and a catastrophe for an agent; `hsql` defaults to 500. > **Amended by [#1026](https://github.com/tconbeer/harlequin/issues/1026).** One key > naming both of those limits was the bug, not the design: the TUI's display cap is now > `viewer_max_rows`, `limit` is the hard fetch limit in both commands, and unlimited is > spelled `-1` rather than `0`, so that `--limit 0` can fetch a header and no rows (and > there is no `-l`, which is psql's `--list`). The text > layouts additionally cap what they *print* (`--display-rows`, 40 rows or 10 records), > since fetching 500 rows and printing 500 rows are separate questions. Detecting truncation at all requires fetching `limit + 1` rows and emitting `limit` — `set_limit(n)` followed by `fetchall()` returns at most n rows and cannot say whether an n+1th existed. Worth stating here rather than leaving to implementation, because it's the kind of detail that gets optimized away and quietly breaks this promise. The stderr notice fires **even under `-t`**. `-t` suppresses stdout chrome, not warnings; a flag that silently defeats truncation reporting would undo principle 5. **Row counts and timing go to stderr**, so `hsql -c "select 1" --csv > out.csv` produces a clean file while the agent still sees `1 row in 0.02s`. **`--stats` emits one line of JSON to stderr** for any output format: ```json {"status":"ok","statements":1,"rows":500,"truncated":true,"limit":500, "elapsed_ms":412,"columns":[{"name":"id","type":"BIGINT"}]} ``` This is how an agent gets structured metadata without polluting stdout, and it works identically whether stdout is CSV or Parquet. **Errors are plain.** No Rich panels, no ANSI, no box drawing: `hsql: error: relation "usres" does not exist` on stderr. The panel is a TUI affordance. > **Amended in M2's plan**, on two of the safety flags above. > > **`--timeout` has to attribute the cancellation itself.** A cancelled DuckDB query comes > back as an empty result set with no error — `fetchall()` swallows the interrupt and > returns `None`, which is also what an empty result looks like — so a timeout that trusts > the adapter prints "no rows" and exits 0. `hsql` knows it fired the deadline, discards > that result, and exits 4. It also refuses up front on an adapter that can't cancel, > since there is otherwise no way to stop the work. > > **`--single-transaction` is cut too.** Adapters name their transaction modes freely — > SQLite and Postgres offer "Auto"/"Manual", DuckDB overrides nothing and reports none, and > a third-party adapter may name its modes anything and default to whatever its driver > does — so core cannot ask for "the manual one" through that interface without first > reworking what a transaction mode is. Story A6's guarantee comes from `begin` and > `commit` in the caller's own script until then. > > **`--dry-run` is cut**, from M2 and possibly for good. Only DuckDB implements > `validate_sql` at all, and there it is a parse check rather than an existence check: > `select * from no_such_table` validates clean, because the adapter treats every > non-parser error as valid so that DDL passes. Making it honest would mean a `PREPARE` > pass in the DuckDB adapter plus a refusal path on every other adapter — a lot of > machinery for a flag that would be truthful on one backend. **Exit codes:** | Code | Meaning | | --- | --- | | 0 | Success | | 1 | Query error (the database rejected the SQL) | | 2 | Usage / config error (bad flag, bad profile, bad TOML) | | 3 | Connection error (couldn't reach or authenticate to the database) | | 4 | Timeout / cancelled | | 130 | SIGINT | **Cold start is a feature.** An agent may run twenty queries in a task. Target: **`hsql -c "select 1"` against DuckDB in under 300ms**, tracked as a benchmark in CI and protected by the import-linter rule from §4. **Large results stream — in M5, not M1.** Streaming has to start at the cursor: `HarlequinCursor.fetchall()` materializes the whole result before any writer exists, so no amount of care in the format layer helps. Deferred wholesale to M5 along with [#875](https://github.com/tconbeer/harlequin/issues/875), rather than half-designed now. ### User stories - **A1.** As an agent, I run `hsql -P prod -c "select count(*) from orders"` and get a small, aligned result on stdout and exit code 0, so I can report a number to the human without parsing a TUI. - **A2.** As an agent, I run the same command against Postgres, BigQuery, and DuckDB and the flags, output shape, and exit codes are identical, so I don't relearn a CLI per database. - **A3.** As an agent, when my SQL is wrong I get a one-line error on stderr and exit code 1, and nothing on stdout, so I can distinguish "the query failed" from "the query returned zero rows." - **A4.** As an agent, when a result is larger than the limit I am told so explicitly, so I never report a truncated aggregate as a complete one. - **A5.** As an agent, I pass `--json` and pipe to `jq` without stripping banners, timings, or a "(500 rows)" footer. - **A6.** As an agent, I run a multi-statement script with `-f setup.sql --single-transaction` and either all of it applies or none of it does. - **A7.** As a human, I set `read_only = true` in my agent profile so the agent physically cannot write to prod, and I can point at that line in the config when someone asks whether it's safe. - **A8.** As a human, I use `hsql` in a Makefile or CI job because it's the same engine and the same profile I already use interactively. ### On `-t`, and the `-tAc` idiom `-t` is `--theme` in `harlequin` and "tuples only" in psql. Since `hsql` has no themes, `-t` is free, and it takes the **psql meaning**. The reason isn't `-t` in isolation — it's that `psql -tAc "select count(*)"` is a single well-worn idiom, the standard way to capture a scalar in a shell script. Supporting `-t` and `-A` separately but not together would be pointless; refusing `-t` would break the cluster and make first contact with a scripting audience an error message. Borrowing the flag is principle 6 doing its job. The residual risk is small and worth naming: someone with `harlequin -t nord` habits types `hsql -t nord -c ...`, `-t` parses as a boolean, and `nord` is read as a connection string. The resulting "could not open database 'nord'" is diagnosable, and we can special-case the hint when an unparseable conn_str matches a known theme name. `hsql -tAc "select count(*) from orders"` returning a bare number and nothing else should be an explicitly tested case, and the first example in the scripting docs. A short "differences from psql" table in the docs should still cover `-P` (profile here, pset there) and anything else that diverges. --- ## 6. Workstream B — Self-description and introspection If Workstream A is what lets an agent *run* a query, this is what lets it write a correct one on the first try. All of it lands as `hsql` subcommands. ### `hsql catalog` — the catalog is a filesystem, not a document The highest-leverage feature in this plan after `-c`. Harlequin already normalizes the catalog across every adapter; today that value is locked inside the TUI. **The binding constraint is fetch cost, not token cost.** `get_catalog()` issues one query and returns databases only; every level below it is a separate `fetch_children()` round trip, with columns as the leaf. Listing one schema's columns is `1 + 1 + 1 + T` queries. A "dump the tree, then filter" interface is an N+1 walk that gets slower the more useful it would be — and #1007 already moved the TUI off eager loading for exactly this reason. Filtering in the client protects the agent's context and does nothing for the database. So the model is a filesystem: **`ls`, `stat`, `find`** — not dump-and-grep. That bounds cost structurally, because you only pay for the level you asked for, and it rides on the navigation loop agents are most practiced at. ``` hsql catalog [PATH] [--depth N] [--max-nodes N] [--format compact|json|md|tree] hsql describe hsql find [--in tables|columns|all] ``` - **One level below the match, by default.** `hsql catalog` → databases; `hsql catalog mydb` → schemas; `hsql catalog mydb.analytics` → relations; `hsql catalog mydb.analytics.orders` → columns. Exactly one `fetch_children()` call. `--depth N` opts into recursion; there is no unbounded default. Counting depth **from the match** rather than from the root is what makes this safe rather than merely conservative: at every level, depth 1 is a single round trip, so there is no path on which the default degrades into a walk. Depth ≥2 from a schema is the first N+1, and it's opt-in. - **Patterns are paths with an optional trailing glob.** A trailing wildcard filters one resolved parent's children and stays a single round trip, so `analytics.*` is just sugar for `analytics`, and `analytics.ord*` narrows it. An *interior* wildcard (`*.orders`, `mydb.*.orders`) can't be evaluated without fetching every candidate level, so it belongs to `find` below — never something a `catalog` call does quietly. - **Report child counts** wherever the adapter can get them cheaply. `400 relations` next to a schema is what lets an agent decide *not* to recurse. Cost should be predictable before the call, not just measurable after it. - **A node budget.** `--max-nodes` (default ~2000) stops a recursive walk and says so, naming the path to narrow. Same rule as row truncation: never silent. - **`--stats` reports round trips**, so the expensive shape of a query is visible. - **Emit `query_name`, not just the label.** Adapters already compute the correctly quoted identifier for every item. Handing it over means the agent never guesses whether this backend wants `"Orders"` or `` `orders` ``. It costs nothing and no other client does it. - **`--format compact`** stays for the leaf and shallow-recursion cases: `analytics.orders(id BIGINT, customer_id BIGINT, total DECIMAL(18,2))`. Roughly an order of magnitude cheaper than pretty JSON and perfectly readable by a model. - **`describe`** returns one object in full — columns, types, comments, row counts where the adapter can supply them. One round trip. > **Amended in M2's plan**, on four things this section assumes the catalog contract can > already do. > > **A listing is one round trip per path segment, plus one** — four for > `--catalog --path mydb.analytics.orders`, not one. An item can only be reached through > its ancestors, so the resolve in front of the listing is itself a walk. The listing stays > single-level and cheap; the resolve is what a network database charges for. > > **`--depth N` is one number over levels with wildly different costs, so M2 ships no > recursion at all.** Depth 2 from the root fetches each database's schema names — one > round trip per database, and databases are few. Depth 2 from a *schema* is one round trip > per relation: measured, 400 relations is **403 round trips and 2.3 seconds against a > local DuckDB file**, on the call an agent would most want to make. Recursion comes back > with `fetch_descendants()`, which is what would make it a single query rather than a > walk with a budget bolted on. > > **Child counts have no cheap case.** A count is only knowable by fetching the children, > so "report child counts wherever the adapter can get them cheaply" cannot be implemented > as written. What a listing can report is what it fetched, which it already does. > > **`describe` is `catalog` on a relation, so there is no separate verb.** One level below > a relation *is* its columns, with their names, real types and quoted identifiers. All a > second mode could add is detail the contract does not carry (comments) or that costs a > table scan (row counts). > > **`--format compact` is cut, and the type it wanted is added.** `CatalogItem.type_label` > is a 1–3 character label for a tree column (`##`, `s`, `ts`), so the type an agent needs > is not in the contract at all — M2 adds an optional `type_name`, which the in-tree > adapters already fetch and discard, falling back to the short label where an adapter > never populates it. The *format* goes: `parent(child TYPE, …)` only reads for a relation > and its columns, and what a level is belongs to the adapter, so a database's schemas > would render as a signature they are not. `-tA --format csv` over the same rows is as > cheap and is true at every level. ### `hsql find` — the real gap What an agent most often needs isn't "list this schema," it's *"where does `orders` live"* or *"which tables have a `customer_id`."* That cannot be a walk. This wants an **optional adapter capability** (`implements_catalog_search`): one `information_schema`-style query where the adapter can serve it, and an explicit "not supported by this adapter" where it can't, surfaced through the capability flags in `hsql info`. High value, but it should not block the rest of the workstream. ### An adapter-API question this raises `fetch_children` is per-item, which is exactly right for a viewport-driven TUI and forces N+1 for bulk access. An adapter that could pull an entire schema's columns in a single `information_schema` query has no way to say so. Worth considering an optional **`fetch_descendants(depth)`** on `InteractiveCatalogItem`, with a default implementation that simply walks `fetch_children`. Adapters that can do better override it; everything keeps working for those that don't. This is a Harlequin-core API decision, not an `hsql` one, and it would improve the TUI's expand-all path too. **Stories:** - **B1.** As an agent, I run `hsql -P prod catalog` and then `hsql -P prod catalog mydb` to orient myself, paying one query per step and knowing what each step cost. - **B2.** As an agent, I see `analytics — 400 relations` and scope my next call instead of recursing into a walk that would take a minute and blow my context. - **B3.** As an agent, I run `hsql -P prod catalog mydb.analytics.orders --format compact` and write a correct join on the first attempt, pasting the adapter's own quoted identifiers rather than guessing at them. - **B4.** As an agent handed an unfamiliar warehouse, I run `hsql find orders` and get a path instead of walking three levels to look for one. ### `hsql spec --json` Dump the full, *installed* CLI surface — including options contributed by whichever adapter plugins are present — as JSON. Harlequin already builds these dynamically from `AbstractOption`, so the data exists; it just isn't reachable. This is unusually valuable here precisely because the option surface is plugin-driven. No model's pretraining can know that this machine has `harlequin-databricks` installed with its particular options. Self-description turns a guessing game into a lookup. - **B4.** As an agent, I run `hsql spec --json` once at the start of a task and never hallucinate a flag for the rest of it. ### `hsql info --json` The debug-info screen, headless: versions, installed adapters, discovered config files in precedence order, the active profile, and **capability flags per adapter** (`implements_cancel`, `implements_copy`, `implements_read_only`, `implements_validate_sql`, `supports_transactions`). Credentials and connection strings redacted. Capability flags matter: they let an agent know *not* to try `--dry-run` on an adapter that can't validate, instead of trying and failing. > **Amended in M2's plan.** The flags have to be *declared* on the adapter class, not > detected on the connection. Reflection can't answer the question: > `HarlequinSqliteConnection` defines `validate_sql` and raises `NotImplementedError` from > it, so an override check reports SQLite as validating SQL. Nothing is broken by that > today — the app catches the error at the call site — but a class variable is the pattern > that can be read *before* the call, and the only one `hsql --info` can read while the > database is unreachable, which is when a diagnostic is worth most. So `--info` imports > adapters and opens no connection. - **B5.** As an agent debugging a human's setup, I run `hsql info --json` and can tell them exactly which config file is winning and why. - **B6.** As an agent, I check capability flags before offering the human a feature their adapter doesn't support. ### `hsql fmt` Expose sqlfmt, which is already a dependency. `hsql fmt query.sql`, `hsql fmt -c "select 1"`, `--check` for CI. Agents write inconsistent SQL; this normalizes it before it lands in a human's repo. Cheap, and a genuinely nice human feature too. > **Cut in M2's plan.** An agent that wants formatted SQL can run `sqlfmt`, which is what > this would have wrapped. It is also the only headless mode that would have forced a > 196ms sqlfmt import into a package whose import graph is otherwise an enforced contract — > paid by the mode alone, but a deferral to maintain forever for a wrapper nobody asked > for. ### `hsql history` Query history is already cached per connection — but as a pickle. Expose it (`hsql history --json -n 20`) and additionally write an append-only JSONL log, so both agents and humans can read it with ordinary tools. - **B7.** As an agent joining a task mid-stream, I read the human's recent query history and continue their line of investigation instead of starting over. --- ## 7. Workstream C — The config file as a formal contract Agents write config files. Today they'd be guessing at TOML structure from prose docs, and a wrong guess produces a confusing runtime error. The config is shared between `harlequin` and `hsql`, so this serves both. ### Deliverables 1. **A published JSON Schema** for `.harlequin.toml`, at `https://harlequin.sh/schemas/config/v1.json` and shipped in the package. Enables the `#:schema` comment for taplo / Even Better TOML — editor validation for humans, and a verifiable target for agents. - Wrinkle: adapter options are dynamic. Solution: publish a base schema for the site, and have `hsql config schema --json` generate a locally-accurate one that includes the installed adapters' options. 2. **`hsql config validate [--json]`** — structured diagnostics with file paths and line numbers, so an agent can fix its own mistake without a human in the loop. The existing `_raise_on_bad_schema` messages are already good; they just need a machine channel. 3. **`hsql config show [--json]`** — the effective merged config, with provenance per key (`limit = 500 # from ~/.harlequin.toml`). This is the single best troubleshooting artifact we could produce. 4. **`hsql config init --non-interactive`** — the TUI wizard (`harlequin --config`) is questionary-only, so agents can't use it. A non-interactive path that edits TOML through tomlkit (preserving the human's comments and formatting) means an agent can safely modify a config it didn't write. The interactive wizard stays exactly where it is; each command gets the affordance right for its audience. 5. **Environment variable interpolation** (`${DB_PASSWORD}`) in config values — closes [#898](https://github.com/tconbeer/harlequin/issues/898), and it's a prerequisite for agents and CI ever touching a config that involves secrets. 6. **A formal spec page on the site**: discovery order, merge semantics, precedence, every key with its type and default, and the reserved/invalid names. > **Amended in M2's plan**, on two things this workstream would otherwise document as they > stand. > > **The merge is per top-level key, not per profile, and the result is a start-up > failure.** `_merge_config_files()` is a `dict.update()` per file, so a cwd config file > that defines any profile replaces the home file's whole `profiles` table — while leaving > its `default_profile` in place, since that is a different top-level key. Both commands > then refuse to start: *"Config files set the default_profile to personal, but do not > define a profile with that name."* Reproduced on this checkout, and against the > pre-refactor `config.py` as well, so it is long-standing rather than something the > `tomllib` change introduced. M2 makes the merge per-key and treats it as a bug fix; the > spec page should describe that rather than what exists today. > > **Validation runs on the merged document, so no error can name a file.** `load_config()` > merges every discovered file and then validates the result, which means a diagnostic can > only ever point at a key in a structure nobody wrote. M2 validates each file as it is > read and merges only valid ones, which is what makes deliverable 2's file-and-line > diagnostics possible. It also adds `hsql --config list-profiles`, since the first > question a human asks is what they can pass to `-P`, and a missing name is how the merge > bug above becomes visible. > > **`${VAR}` interpolation has to be a read-path transform.** `harlequin --config` reads > the config and writes it back, so resolving values too early bakes a plaintext password > into the user's file on their next wizard run. The syntax is `${VAR}` and > `${VAR:-default}` — a bare `{` never triggers it, so nothing anyone has in a password > today needs escaping — and an unset variable with no default is a config error naming > the variable and its file, never an empty string. ### Secrets: #667, #898, and redaction are one feature [#667](https://github.com/tconbeer/harlequin/issues/667) asks for a password prompt, and the maintainer's read at the time was that it wants **a new type of adapter option**. That's the right shape, and it does far more than serve the prompt: it turns redaction from a discipline into a property of the declaration. The decisive argument is the plugin ecosystem. Core cannot enumerate every adapter's secret — `--service-account-key`, `--token`, `--tls-key`, whatever the next adapter invents. But each adapter can declare its own, once, and then every consumer gets it free: `hsql info --json`, `config show`, `spec --json`, error output, the debug-info screen, and the config wizard's masked input. Combine that with `${VAR}` interpolation (#898) and the secrets story closes: - **`PasswordOption`** (or `secret=True` on `AbstractOption`) — declares an option as sensitive. Drives `hide_input` at the prompt, a masked widget in the TUI, and redaction everywhere else. - **Never prompt in headless mode.** A prompt that blocks on stdin is the *worst* failure mode for an agent: no output, no exit code, the whole turn burned until something times out. `hsql` must fail fast with a message pointing at the profile or the env var. > **Amended in M2's plan: no `--password-stdin` either.** Between profiles, `${VAR}` > interpolation and the variables every driver already reads, it fills no gap worth a > flag — and the gap it does fill, it fills by consuming the stream `-f -` wants. - **`spec --json` marks secret options.** This is what teaches an agent *not* to construct `hsql --password hunter2`, which would leak through `ps` and shell history to every other user on the box. The skill should say it too, but the machine-readable flag is what makes it checkable. - **Scrub known secret values from outbound strings** as a backstop. Declarative redaction covers values we own; a driver exception that echoes a DSN does not go through our option layer. - **Connection strings need their own handling.** `conn_str` is a positional argument, so no option type covers `postgres://user:pw@host/db`. It needs DSN-aware redaction of its own — and [#354](https://github.com/tconbeer/harlequin/issues/354) is evidence that people really do put passwords there. - **History is the one surface this can't fix.** `create user x password 'y'` is a secret inside a query. Worth saying plainly in the docs that history is local-only and may contain secrets by construction. ### Stories - **C0.** As a human, I hand my agent a profile name, and no secret ever appears in an argument list, a transcript, or `ps` output. - **C1.** As an agent, I add a `prod` profile to a human's existing config without clobbering their comments or reformatting the file. - **C2.** As an agent, I validate the config I just wrote and get a line number when I get it wrong. - **C3.** As a human, my editor red-squiggles a typo'd key before I ever run Harlequin. - **C4.** As an agent, I write a config that references `${SNOWFLAKE_PASSWORD}` and never see the secret itself. --- ## 8. Workstream D — Docs that machines can read (harlequin.sh) The docs are already markdown files on disk (`src/docs/**/*.md`) and there's already an `/api/docs` index route. Most of this is plumbing we half-have. ### D1. `llms.txt` and `llms-full.txt` Highest ROI item on the site. `llms.txt` at the root: an index of every docs page with a one-line description and a link. `llms-full.txt`: the whole documentation set concatenated as clean markdown. Both prerendered at build time. An agent fetches one URL and knows everything. ### D2. Raw markdown per page `https://harlequin.sh/docs/getting-started/running.md` returns `text/markdown`. Implemented as a catch-all `+server.ts` reading the source via `import.meta.glob(..., { query: '?raw' })`. The real work is sanitization: the sources carry mdsvex `