# Agent-First PSQL — Design
## Purpose: a database contract agents can rely on
`afpsql` is a reliability layer between AI agents and PostgreSQL. It gives an
agent a stable operational contract: structured events, explicit permission
boundaries, predictable session state, and machine-readable failures.
It is not designed as a general-purpose high-performance pooler. Backend reuse is
used to make session semantics reliable for agents, not to promise throughput,
fair scheduling, or database-side load balancing.
## The problem: terminal clients make agents guess
Agents often reach PostgreSQL through terminal tooling built for humans. That
creates brittle automation:
1. Results are rendered as text tables instead of typed records.
2. Errors are prose instead of structured data with stable codes.
3. Writes can happen accidentally when a generated SQL statement is wrong.
4. Remote/container access often pushes agents toward SSHing into servers or
using container exec commands for human `psql`.
5. Stateful multi-step work is unclear when every command opens an unrelated
backend session.
6. Large results can flood stdout instead of using a bounded protocol.
`afpsql` addresses these with one Agent-First Data runtime protocol.
## Product boundary: one runtime, several entry points
`afpsql` has one runtime interface. All execution modes feed the same
agent-first core.
Modes:
- native CLI: one request, one structured response stream, exit
- pipe: long-lived JSONL runtime with named sessions and FIFO execution
- psql compatibility: argument translation for non-interactive scripts only
Non-goals:
- no interactive psql terminal behavior
- no psql meta-command compatibility (`\d`, `\x`, `\timing`, ...)
- no table/text output compatibility as the runtime contract
- no client-side SQL template interpolation
- no ORM/query-builder abstractions
- no database administration UI
- no high-performance pooler semantics
- no automatic remote/local host classification
## Core principles: reliability over throughput
1. Reliability over throughput.
2. Structured AFDATA events are the protocol, and the event destination follows
the invocation's consumption mode rather than being a caller preference.
3. Native/pipe writes require explicit permission.
4. PostgreSQL `SQLSTATE` is preserved for database errors.
5. Permission, validation, transport, and protocol errors include actionable hints.
6. Dynamic values use PostgreSQL parameters, never client text interpolation.
7. Session state is explicit: pipe named sessions are stable backend sessions.
8. Per-session query execution is FIFO.
9. Query shape and write enforcement are based on PostgreSQL metadata and
transaction state. The readonly binary uses a narrow SQL-text classifier
only for transaction-control statements.
10. SSH and container boundaries are first-class transports, including
`--ssh` plus a container driver family for containers on SSH hosts.
11. `psql mode` is compatibility translation only; it does not fork runtime semantics.
## Execution architecture: translate inputs, preserve one contract
High-level layering:
- CLI parser translates native flags or psql-compatible flags into canonical requests.
- Pipe reader validates JSONL protocol input and queues work by session.
- Handler resolves session config, permissions, timeouts, cancellation, and output routing.
- `DbExecutor` is the database adapter boundary.
- PostgreSQL execution uses `tokio-postgres` and transaction settings derived from resolved options.
- SSH and container transports are first-class connection setup paths that still
feed the same runtime protocol.
The user-facing model should stay simple: an agent sends SQL plus params and gets
structured events back.
## Permission model: writes cross an explicit boundary
Native CLI and pipe mode resolve a `permission` value for each query:
| direct PostgreSQL connection | `read` | `write` |
| afpsql SSH transport | `ssh-read` | `ssh-write` |
| afpsql container transport | `container-read` | `container-write` |
Read permissions start PostgreSQL read-only transactions. A write attempt in
that transaction fails as a `sql_error` with SQLSTATE `25006`.
Permission values are intentionally tied to transport:
- direct sessions accept only `read` or `write`
- afpsql SSH sessions accept only `ssh-read` or `ssh-write`
- afpsql container sessions accept only `container-read` or `container-write`
Mismatches fail before execution as `invalid_request` with a hint that tells the
agent which permission family to use.
`--ssh` and a `--container-<driver>-*` flag family can be combined to run that
driver on the SSH host. The database boundary is still the container, so the session
uses container permissions rather than SSH permissions.
`psql mode` keeps psql's writable default for script compatibility and does not
expose native permission flags.
## Readonly executable
`afpsql-readonly` selects an immutable read-only capability in the shared runner.
The handler checks resolved read-family permission for native, inspect, dry-run,
explain, and pipe queries; pipe transaction creation separately rejects
read-write begins. Raw SQL transaction control remains rejected so it cannot
bypass afpsql's transaction state machine. Psql translation remains unavailable
because it currently selects write permission by default.
The ordinary readonly entrypoint is not a host sandbox. It accepts local SQL
files, arbitrary explicitly named secret environment variables, config secret
files, SSH options, custom container runtimes, stream redirection, skill
management, and caller-selected direct/SSH/container targets. These capabilities
can read local data or start processes even though the final database permission
cannot leave the read family.
PostgreSQL authorization remains the strict database security boundary.
Deployments requiring adversarial isolation must use a dedicated
non-owner reader role, audit role membership/functions/RLS, and apply database
resource governance. PostgreSQL `READ ONLY` does not by itself rule out `NOTIFY`,
advisory locks, extension behavior, external-side-effect functions, or any
capability already granted to the reader role.
For narrower host authorization, the executable basename itself can bind a
trusted profile: `afpsql-readonly-NAME` loads the fixed root-owned file
`/etc/afpsql/readonly-profiles/NAME.json`. Selection is outside agent-controlled
CLI/pipe data. The locked mode rejects connection flags and pipe session
patches, while allowing administrator profile values to use SSH config files,
ProxyCommand, identity paths, or custom runtimes. Root ownership, a 64 KiB size
cap, and no group/world write bits establish the Unix trust boundary.
## Config secret source architecture
Canonical and psql-compatible argument parsers translate each
`--*-secret-config FILE` plus `--*-secret-config-path DOT_PATH` pair into a
typed config reference. A single
in-process resolver detects JSON, TOML, YAML, or dotenv through
agent-first-data's document layer, reads the file once, resolves the dot-path,
and requires a non-empty string. It never invokes a shell, expands dotenv
variables, or writes the value into argv or the process environment.
Parser-library errors are converted into source-safe CLI errors containing only
the flag, file, dot-path, format, and safe line/column metadata. The resolved
value is stored only in the existing session secret slot. Session serialization
has one safe projection for direct, env-resolved, and config-resolved values, so
pipe config responses and every output formatter emit `***` rather than secret
text. Config references are startup-only in pipe v1; dynamic pipe file/path
references and automatic rotation are deliberately out of scope.
Direct, SSH, and container paths resolve DSN/conninfo through the same typed
`tokio_postgres::Config`. Boundary transports derive one internal PostgreSQL
endpoint from that parsed config rather than splitting connection text. An SSH
stdio bridge carries the PostgreSQL wire stream without a local listener;
authentication, database,
startup options, timeout/keepalive behavior, channel binding, and supported TLS
settings remain attached to the connection. Multi-host sources fail explicitly
until one bridge can represent multiple failover targets.
## Session semantics: named sessions mean backend state
A pipe named session is intended to correspond to one PostgreSQL backend session
for as long as the session config remains valid and the process remains alive.
This lets agents rely on PostgreSQL session state, including temp tables and
session-level settings, within that named session.
Rules:
- queries in the same named session run FIFO
- different named sessions are isolated
- config changes invalidate affected sessions
- invalidation creates a new backend session on next use
- checked-out work must keep any required transport resources alive until it finishes
This is a reliability contract. It should not be documented as a pooler or a
performance feature.
## Protocol shape: every recoverable outcome is an event
Input commands:
- `query`
- `cancel`
- `config`
- `ping`
- `close`
- `session_info`
- `begin`, `commit`, `rollback` (pipe-mode explicit transactions; subsequent
`query` events on the same session run inside the open transaction with
per-query savepoint isolation)
Output events:
- `result` (includes optional `truncated`, `truncated_at_rows`,
`truncated_at_bytes` when the inline cap fired; tx control events reuse
`result` with `command_tag` of `BEGIN`/`COMMIT`/`ROLLBACK`)
- `result_start`
- `result_rows`
- `result_end`
- `sql_error`
- `error`
- `dry_run` (preview-only response to `--dry-run`; carries `param_types`
and `columns` inferred from a rolled-back PREPARE)
- `config`
- `pong`
- `close`
- `session_info` (includes resolved `database`/`user`/`host`/`port`/
`server_version` from a probe SELECT when reachable)
- `log`
Every recoverable runtime condition should be represented by one of these
AFDATA events. Startup argument parsing can still exit with code `2`, but it
should use structured CLI error output when possible.
## Event destination: the invocation picks it, not the caller
Where events go follows the invocation's consumption mode. A plain query emits
at most one terminal event and exits, so it is a finite one-shot command and
splits by kind: the `result` payload is what a caller captures, so it goes to
stdout, while `error`, `log`, and `progress` are diagnostics and go to stderr.
That keeps `rows=$(afpsql …)` from ever capturing a failure as data.
`--mode pipe` and `--stream-rows` are different. They emit an interleaved
multi-event stream whose consumer reads it in order, so they are event streams:
every event goes to one stream, stdout by default. Splitting a stream across
stdout and stderr would destroy the ordering that makes it a stream, and would
strand `result_rows` — which carries the actual row payload — on the diagnostic
stream while only the row-less `result_end` terminator reached stdout.
`--output-to` overrides the destination but cannot override the mode: asking a
stream for `split` is a usage error rather than a silent reordering. The
destination is resolved from raw arguments before the parser runs, so startup
failures, `--help`, and `--version` honor the same policy.
## Parameter binding: values never become SQL text
When values are dynamic, clients should use `$N` placeholders and `params`.
```json
{"code":"query","id":"q1","sql":"select * from users where id = $1","params":[123]}
```
Validation rules:
1. Placeholder count must match `params` length, using prepared-statement metadata.
2. Invalid client-side shapes or local binding conversions return `error.code:"invalid_params"`.
3. PostgreSQL server conversion/execution failures remain `sql_error` events with the original SQLSTATE.
Unsupported by design:
- `:name` interpolation
- raw text expansion in SQL templates
- SQL text scanning to infer placeholders or statement kind
## Result handling: bounded inline, explicit streaming
Row/command behavior is decided from PostgreSQL statement metadata after prepare:
- statement has result columns -> row result path (`result` or streamed `result_*`)
- statement has no result columns -> command result path
Inline results are bounded by:
- `inline_max_rows`
- `inline_max_bytes`
When streaming is enabled:
1. emit `result_start` with column metadata
2. read PostgreSQL rows incrementally
3. emit repeated `result_rows` batches
4. emit `result_end` with totals in `trace`
If streaming is off and limits are exceeded, the inline collector stops
accepting rows at the cap and the `result` event carries `truncated:true`
plus `truncated_at_rows` or `truncated_at_bytes`. The underlying statement
still ran in full: for `UPDATE ... RETURNING`, the writes happened and the
RETURNING projection is what got capped. Agents that need the full set
should narrow the query or switch to `--stream-rows`.
## Error taxonomy: SQLSTATE or actionable runtime code
### `sql_error`
PostgreSQL execution failure. Include:
- `sqlstate`
- `message`
- optional `detail`, `hint`, `position`
- `trace`
Agents should branch on `sqlstate`, not parse message text.
### `error`
Client/protocol/runtime failure. Include:
- `error.code`
- `error.message`
- optional `sqlstate`, `message`, `detail` when PostgreSQL rejects the
connection before query execution
- optional `hint`
- `retryable`
- `trace`
Permission mismatches, validation errors, connection setup failures, unsupported
TLS settings, and SSH transport validation should use this path.
Connection-stage PostgreSQL failures still use `kind:"error"` with
`error.code:"connect_failed"`, but should preserve SQLSTATE diagnostics when
available so agents can distinguish authentication, role, database, capacity, and
startup failures without parsing prose.
## psql compatibility boundary: scripts yes, terminal semantics no
`psql mode` exists to let non-interactive scripts use familiar flags while
receiving structured afpsql events.
The canonical afpsql surface follows AFDATA's long-flags-only default:
`--host`, `--help`, `--version`, and `--output` have no short aliases.
Psql-compatible shorts such as `-h`, `-V`, and `-v` belong only to
`--mode psql` and the managed psql wrapper, where their established meanings
are unambiguous.
Psql's `-o` / `--output` names an output file, while canonical afpsql's
`--output` selects the structured rendering format. Psql mode rejects both
spellings instead of silently changing their meaning. Use `--stdout-file` for
process-level redirection, or canonical mode's long-only `--output` for
json/yaml/plain rendering.
It may translate:
- `-c` / `--command`
- `-f` / `--file`
- `-l` / `--list`
- `-h`, `-p`, `-U`, `-d` and long aliases
- numeric `-v N=value` bind parameters
- selected non-interactive behavior flags that are safe to ignore or translate
It must reject or mark unsupported:
- interactive password prompts
- single-step/single-line interactive modes
- no command source
- meta-command workflows
- psql output-file flags `-o` / `--output`
- afpsql native permission flags
- afpsql SSH transport extension flags
## Implementation guardrails: protect the reliability contract
- Keep runtime errors structured and honor `--output-to`.
- Add tests for each permission boundary and hint.
- Preserve session/bridge lifetimes with active work rather than only map entries.
- Avoid destructive changes to session state on config updates until active work is safe.
- Keep `docs/cli.md` generated from the registry with `afpsql --docs`; the
registry is the only source, so the document cannot disagree with the parser.
- Preserve `clippy.toml` bans against ad-hoc process-stream writes. Keep the
readonly transaction-control classifier narrow and explicitly tested; do not
extend it into a DML/DDL permission heuristic.
## Skill design: behavior, not flag reference
`skills/agent-first-psql/SKILL.md` is loaded by Claude Code and Codex as the agent's
behavior contract when working with afpsql. Its audience is users of the
installed binary — the source tree and `docs/reference.md` may not be present on
the user's machine, but `afpsql --help` always is. The skill is shaped around
that asymmetry.
Keep in the skill:
- behavior rules (defaults, what to do / not do)
- decision rules (when to use which mode, permission, or transport)
- recovery rules (specific `SQLSTATE` or `error.code` → action)
- non-obvious defaults the agent would otherwise miss (e.g. read-only default,
libpq `PG*` env silently filling missing fields)
- a small set of canonical examples that establish the pattern
Drop from the skill:
- flag enumerations
- driver / option matrices
- env-var lists beyond the few that change agent behavior
- full canonical command variants for every transport combination
For anything in the "drop" list, the agent runs `afpsql --help` or
`afpsql <command> --help` to discover the current flag surface. Mirroring
`--help` content into the skill makes it rot every release, bloats agent
context, and trains the agent on stale flag names.
This sits alongside the existing "skip routine `afpsql --help` preflight before
known query forms" guidance: the agent should not help-probe for **known** query
shapes, but should help-probe for **unknown** flag detail rather than reading it
from the skill.