Agent-First PSQL v0.6.3: Look Before You Touch

The through-line of Agent-First PSQL v0.6.3 is the gap between deciding to run SQL and running it. An agent that writes a query against a schema it hasn’t seen, or commits a multi-statement change it never validated, is guessing. This release fills that gap with four ways to look before you touch — and then hardens what happens when you do.

afpsql inspect: schema discovery without catalog SQL

Before v0.6.3, an agent that needed to know what tables exist wrote a pg_catalog / information_schema query by hand and parsed the rows. That is exactly the kind of human-shaped detour afpsql exists to remove. inspect makes schema discovery a first-class command with structured output:

afpsql --dsn-secret "$DSN" inspect databases
afpsql --dsn-secret "$DSN" inspect schemas
afpsql --dsn-secret "$DSN" inspect tables --schema public --like 'order%'
afpsql --dsn-secret "$DSN" inspect views  --schema public
afpsql --dsn-secret "$DSN" inspect table  public.orders

inspect table accepts schema.table and defaults to public.NAME when unqualified; it returns each column’s name, type, nullability, and default. inspect tables / views take an optional --like pattern. The agent gets the same JSONL row sets it gets from any query — no second output format to parse, and no hand-written catalog query that breaks across PostgreSQL versions.

--dry-run: validate without executing

--dry-run prepares the statement inside a transaction that is then rolled back. Nothing executes, but PostgreSQL still validates table and column existence, and the placeholder count, and infers parameter types:

afpsql --dsn-secret "$DSN" --dry-run \
  --sql 'insert into orders (customer_id, total) values ($1, $2)' \
  --param 42 --param 19.99

{
  "code": "dry_run",
  "sql": "insert into orders (customer_id, total) values ($1, $2)",
  "params": ["42", "19.99"],
  "param_types": ["int4", "numeric"],
  "columns": [],
  "trace": {"duration_ms": 3}
}

param_types is the useful part: the agent sees how PostgreSQL will interpret each $n before binding for real. If preparation fails, the agent gets the same sql_error (with SQLSTATE) it would get from a real run, and a non-zero exit — so a dry run is a real validation gate, not a hint.

--explain and --explain-analyze

--explain wraps the query in EXPLAIN (FORMAT JSON) and returns the plan tree instead of executing the user’s SQL. --explain-analyze uses EXPLAIN (ANALYZE, FORMAT JSON, BUFFERS) — which does run the underlying statement, so a write needs the matching write permission:

afpsql --dsn-secret "$DSN" --explain \
  --sql 'select * from orders where customer_id = $1' --param 42

The plan comes back as structured JSON, not the pgAdmin-style text tree, so an agent can branch on node types or estimated cost without parsing indented ASCII.

Explicit transactions in pipe mode

Pipe mode now accepts begin / commit / rollback as their own requests, so an agent can run a real multi-statement transaction instead of jamming everything into one SQL string:

{"code":"begin","id":"b1","session":"default","read_only":false,"permission":"write"}
{"code":"query","id":"q1","session":"default","sql":"update accounts set balance = balance - 100 where id = 1"}
{"code":"query","id":"q2","session":"default","sql":"update accounts set balance = balance + 100 where id = 2"}
{"code":"commit","id":"c1","session":"default"}

A read-write begin requires a write permission matching the session’s transport family (write / ssh-write / container-write); read_only:true sends BEGIN READ ONLY and always passes. The key safety property is failure isolation: each query inside the transaction is wrapped in a savepoint, so one failing statement rolls back to before that statement without discarding prior progress. The agent then decides — commit to persist the work done so far, or rollback to discard the whole transaction. A bad statement no longer forces an all-or-nothing abort the agent didn’t choose.

Oversized results soft-truncate instead of erroring

When streaming is off and an inline result exceeds the configured row or byte caps, v0.6.3 no longer fails the query. It returns a normal code:"result" event with the first N rows and truncated:true, plus whichever cap fired (truncated_at_rows / truncated_at_bytes):

{"code":"result","truncated":true,"truncated_at_rows":1000,"rows":[ ... ]}

The underlying SQL still executed in full; only the projection handed back to the agent is capped. The agent sees real data plus an explicit signal to either narrow the query with WHERE / LIMIT or switch to --stream-rows — a far better failure mode than an error that returns nothing.

Correctness: cancellation, decoding, precision

Three fixes underneath the new features:

• Cancellation race. The terminal frame for a query is now claimed atomically, so a query that is cancelled mid-flight can never emit two terminal events (a result and a cancellation). An agent waiting on a single terminal frame per query id can rely on getting exactly one.

• Decode robustness. A value that fails to decode is now surfaced as an error instead of being silently emitted as null — a silent null is the worst possible outcome, because the agent branches on data that was never really there. bytea and array types are now decoded correctly rather than falling into that path.

• --param and NUMERIC precision. The string form of --param is preserved end to end, and NUMERIC values bind without precision loss, so 12345678901234567890.123456789 survives the round trip instead of being mangled through a float.

session_info also now reports the connection’s database and user, so an agent can confirm which identity a session actually resolved to — closing the loop on the introspection work from v0.6.2.

Adoption

brew install agentfirstkit/tap/afpsql   # macOS / Linux
cargo install agent-first-psql          # any platform

afpsql skill install
afpsql skill status

For the container transport family, SSH chaining, and the session_info introspection this release builds on, see the v0.6.2 release post.