# PGSandbox

PGSandbox is a local-first Rust CLI and MCP stdio server that lets coding
agents create, inspect, use, clone, and delete disposable PostgreSQL databases.
It is for engineers who want agents to validate migrations, SQL, seed data, and
backend bug reproductions against a real isolated database without touching a
shared developer database.

## Key Features

- Creates one tracked PostgreSQL database and one scoped login role per task.
- Starts and reuses a managed local Postgres cluster under `~/.pgsandbox/` by
  default, without Docker and without binding `localhost:5432`.
- Supports explicit external Postgres admin profiles when you intentionally opt
  in through environment variables or a JSON config file.
- Enforces positive TTLs, max TTL caps, metadata-backed deletion, and optional
  per-owner active sandbox quotas.
- Runs user SQL through sandbox role credentials, not the admin connection.
- Returns bounded, typed SQL result sets so agents do not dump unbounded rows.
- Describes schemas, computes schema digests, diffs schemas, creates named
  schema snapshots, and returns `EXPLAIN (FORMAT JSON)` plans.
- Runs repo migration, seed, and smoke commands with sandbox credentials
  injected through environment variables or validated app-specific aliases,
  plus bounded stdin and output controls instead of temporary repo files.
- Returns host-local and Docker-friendly connection variants so containerized
  local apps can reach a host-managed sandbox without guessing the right host.
- Creates reusable local template artifacts from PGSandbox-owned sandboxes.
- Writes MCP client config for Codex, Cursor, VS Code, and Claude Desktop.
- Masks admin URLs and sandbox credentials in diagnostics and safe summaries.

## Table Of Contents

- [Tech Stack](#tech-stack)
- [Prerequisites](#prerequisites)
- [Getting Started](#getting-started)
- [Development From This Repo](#development-from-this-repo)
- [Architecture](#architecture)
- [Environment Variables](#environment-variables)
- [Available Scripts](#available-scripts)
- [Testing](#testing)
- [Deployment](#deployment)
- [Troubleshooting](#troubleshooting)
- [Contributing](#contributing)
- [License](#license)

## Tech Stack

- **Language**: Rust 2021 edition
- **Runtime**: Native CLI binary named `pgsandbox`
- **MCP Framework**: `rmcp` stdio server
- **Async Runtime**: Tokio
- **Database Client**: `tokio-postgres`
- **TLS**: `native-tls` and `postgres-native-tls` for Postgres URLs that use
  TLS options such as `sslmode=require`
- **Configuration**: Serde, JSON, TOML, and environment variables
- **Serialization**: `serde`, `serde_json`, `serde_yaml_ng`, and `schemars`
- **Security Helpers**: `aes-gcm` for encrypted sandbox role passwords, `sha2`
  for digests/checksums, `uuid` for ids
- **Diagnostics and HTTP**: `reqwest` for telemetry delivery, `url` for
  connection-string parsing
- **Website**: Astro 6, TypeScript, Node.js, and npm under `site/`
- **Testing**: Cargo unit tests plus opt-in live Postgres integration tests
- **Packaging**: GitHub release archives, Homebrew tap packaging, and a hosted
  install script
- **Deployment**: Native binary distribution for the MCP server; CapRover
  deployment for the separate static Astro site

## Prerequisites

For normal end-user installation:

- macOS or Linux on `x86_64` or `aarch64`
- Local PostgreSQL server binaries for the managed local runtime. `setup`
  checks for them, installs PostgreSQL through a supported package manager when
  available, and starts the managed local cluster.
- Optional PostgreSQL dump tools for clone/template workflows:
  `pg_dump` and `pg_restore`
- One MCP client: Codex, Cursor, VS Code, Claude Desktop, or another client
  that can launch stdio MCP servers
- Homebrew, `curl`, or `wget` if installing a released binary

For repository development:

- Git
- Rust toolchain compatible with the repo. CI uses Rust `1.91.1`.
- Node.js 22 or newer. CI uses Node 22; `mise.toml` pins Node `24.15.0` for
  local toolchain management.
- npm
- PostgreSQL server binaries available on `PATH`, in a common package-manager
  location, or through `PGSANDBOX_POSTGRES_BIN_DIR`

PGSandbox checks `PATH`, common package-manager locations such as
`/opt/homebrew/opt/postgresql/bin`, `/usr/lib/postgresql/<major>/bin`,
versioned Homebrew kegs from `postgresql@18` through `postgresql@13`,
Postgres.app locations, and explicit bin dir environment variables. Homebrew
kegs do not need to be linked globally if PGSandbox can discover the `opt` path.

Docker is not required. `docker-compose.example.yml` is only a demo helper for
users who intentionally want an external local Postgres profile.

## Getting Started

These steps are for a normal user who wants to install the released
`pgsandbox` binary and use it from an MCP client. You do not need to clone
this repository, install Rust, or run Cargo for the standard setup.

### 1. Install PGSandbox

Homebrew is the recommended install path:

```bash
brew install LVTD-LLC/tap/pgsandbox
```

If you do not use Homebrew, install the latest GitHub release binary with the
hosted installer:

```bash
curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh | sh
```

If the installer uses `~/.local/bin`, make sure that directory is on your
`PATH` before continuing.

Verify the installed binary:

```bash
pgsandbox --version
```

### 2. Run Setup

Pick the client you use:

```bash
pgsandbox setup --client codex
pgsandbox setup --client cursor
pgsandbox setup --client vscode
pgsandbox setup --client claude-desktop
```

`setup` does the normal local setup work for you:

- checks for the PostgreSQL server binaries the managed runtime needs
- installs PostgreSQL with a supported package manager when those binaries are
  missing and one is available
- initializes and starts the managed local Postgres cluster under
  `~/.pgsandbox/`
- writes the MCP client config

For Cursor or VS Code project-local config:

```bash
pgsandbox setup --client cursor --scope project
pgsandbox setup --client vscode --scope project
```

By default, setup does not write `PGSANDBOX_ADMIN_DATABASE_URL`. That is
intentional: the MCP server will use the managed local Postgres cluster under
`~/.pgsandbox/`.

Only pass `--admin-url` when you intentionally want to use an explicit external
local/private Postgres admin profile:

```bash
pgsandbox setup --client codex --admin-url "$PGSANDBOX_ADMIN_DATABASE_URL"
```

### 3. Restart Your MCP Client

Restart Codex, Cursor, VS Code, or Claude Desktop after setup. MCP clients cache
server metadata and usually do not notice a newly configured server until
restart.

In Codex, run:

```text
/mcp
```

Verify that the `pgsandbox` server appears.

### 4. Optional Verification

Run diagnostics:

```bash
pgsandbox doctor
```

Run the disposable end-to-end check:

```bash
pgsandbox smoke-test
```

The smoke test creates a sandbox, runs SQL, validates serialization behavior,
and deletes the sandbox before exiting. Its default output is a compact list of
pass/fail results. Use `pgsandbox smoke-test --verbose` to include the full
structured SQL result dictionaries for diagnostics.

You can also inspect the managed local runtime directly:

```bash
pgsandbox local status
pgsandbox local start
```

The runtime starts at port `65432` and scans upward for a free port. It should
not collide with Docker or another developer Postgres already using `5432`.

### 5. Use A Sandbox

Ask your agent to create a disposable Postgres sandbox for the task. A typical
MCP workflow is:

1. Create a sandbox with `create_database`.
2. Run SQL with `run_sql` or run a repo command with `run_repo_command`.
3. Inspect schema with `describe_schema` or `schema_digest`.
4. Delete the sandbox with `delete_database`, or let TTL cleanup remove it
   later.

Agents that prefer shell commands can run the same tool contracts directly
through the CLI:

```bash
pgsandbox create-database --name-hint "migration check" --ttl-minutes 30
pgsandbox run-sql --database-id "$DATABASE_ID" --sql "select 1" --readonly
pgsandbox delete-database --database-id "$DATABASE_ID"
```

Every MCP tool has a CLI command. Use hyphenated command names for normal shell
usage, or call the exact MCP tool name through `tool`:

```bash
pgsandbox schema-digest --database-id "$DATABASE_ID"
pgsandbox tool schema_digest --input "{\"databaseId\":\"$DATABASE_ID\"}"
pgsandbox run-repo-command --database-id "$DATABASE_ID" --repo-path "$PWD" -- npm run migrate
```

For a complete one-shot test session, let PGSandbox create the database, inject
its connection settings, run the child process, and clean up:

```bash
pgsandbox with-database \
  --postgres-version 18 \
  --extension vector \
  --cleanup always \
  -- python -m pytest tests/unit
```

The child receives `DATABASE_URL`, `PGSANDBOX_DATABASE_URL`, and libpq `PG*`
variables. PGSandbox captures bounded child output so it can redact the
generated credentials before returning it. The command preserves nonzero child
exit codes, translates interruption to the conventional `128 + signal` status,
and uses exit `124` for a timeout. Use `--cleanup on-success` to retain a failed
database for debugging or `--cleanup keep` to retain every run; TTL remains the
backstop. Add `--result-format json` for the versioned, credential-free session
result.

See [Agent test-suite recipes](docs/agent-testing.md) for framework boundaries,
Redis assumptions, Django test-database handling, and troubleshooting.

`--input` and `--input-file` accept the same camelCase JSON objects documented
for MCP tools, so the CLI can reach the full tool surface without adding a
custom flag for every field.

If your application runs inside Docker while PGSandbox runs on the host, call
`get_connection_string` with `includeCredentials: true` and pass
`connectionStrings.localContainer` to the app service as `DATABASE_URL`. Keep
the raw value out of chat, logs, issues, PR comments, and task trackers. Docker
Desktop supports the returned `host.docker.internal` host automatically. On
Linux Docker, add this to the service:

```yaml
extra_hosts:
  - "host.docker.internal:host-gateway"
```

For `run_repo_command`, set `connectionMode: "localContainer"` and add app
aliases such as `databaseUrlEnvNames: ["DATABASE_URI"]`; PGSandbox injects the
Docker-friendly URL without returning the raw value in command metadata.

For direct CLI troubleshooting, this command starts the MCP server over stdio:

```bash
pgsandbox mcp
```

You normally do not run it yourself; your MCP client launches it.

## Development From This Repo

Use this section when contributing to PGSandbox, testing unreleased changes,
or pointing an MCP client at a local development build. Normal users should use
the packaged setup in [Getting Started](#getting-started).

### 1. Clone The Repository

```bash
git clone https://github.com/LVTD-LLC/pgsandbox.git
cd pgsandbox
```

### 2. Install Toolchains

If you use `mise`, the repo already declares tool versions:

```bash
mise install
```

Without `mise`, install Rust and Node manually:

```bash
rustup toolchain install stable
node --version
npm --version
```

The site requires Node `>=22.12.0`.

### 3. Install JavaScript Dependencies

The root package is mostly a command runner for Cargo and packaging scripts.
The website has its own package under `site/`.

```bash
npm ci
npm --prefix site ci --include=dev
```

### 4. Build And Check The CLI

```bash
cargo build
cargo run -- doctor
cargo run -- smoke-test
```

The development binary is created at:

```text
target/debug/pgsandbox
```

For an optimized release build:

```bash
cargo build --release
```

### 5. Configure An MCP Client For Local Development

For development, point the MCP client at the binary in this checkout so it does
not accidentally launch a separately installed release:

```bash
cargo build
cargo run -- setup --client codex --command "$(pwd)/target/debug/pgsandbox"
```

Other supported clients:

```bash
cargo run -- setup --client cursor --scope project --command "$(pwd)/target/debug/pgsandbox"
cargo run -- setup --client vscode --scope project --command "$(pwd)/target/debug/pgsandbox"
cargo run -- setup --client claude-desktop --command "$(pwd)/target/debug/pgsandbox"
cargo run -- setup --client all --command "$(pwd)/target/debug/pgsandbox"
```

Use `--dry-run` to inspect the config without writing it:

```bash
cargo run -- setup --client codex --dry-run --command "$(pwd)/target/debug/pgsandbox"
```

Restart the MCP client after setup. MCP clients cache tool metadata.

### 6. Verify MCP Server Startup Manually

The binary starts the stdio server by default:

```bash
cargo run
```

Equivalent explicit form:

```bash
cargo run -- mcp
```

You usually do not run this command directly because the MCP client owns the
stdio transport. Use it only when checking startup failures.

### 7. Run The Documentation Site

The Astro website lives in `site/`.

```bash
npm --prefix site run dev
```

Build the site:

```bash
npm run site:build
```

No build-time environment variables are required for the site.

## Architecture

### Directory Structure

```text
.
|-- rust-src/
|   |-- main.rs              Binary entrypoint
|   |-- cli.rs               CLI dispatch, setup, doctor, local runtime commands, smoke test
|   |-- mcp.rs               MCP server, public tool registration, response envelopes
|   |-- config.rs            Env and JSON config loading, profile validation
|   |-- local.rs             Managed local Postgres init/start/stop/status
|   |-- postgres.rs          Sandbox lifecycle, SQL, schema, repo workflow, templates
|   |-- names.rs             Identifier generation and SQL quoting helpers
|   |-- doctor.rs            Diagnostics and connection-string masking
|   |-- setup.rs             MCP client config writers
|   |-- telemetry.rs         Anonymous usage telemetry
|   `-- lib.rs               Library exports and package version
|-- docs/
|   |-- architecture.md      Resource model and backend notes
|   |-- mcp-tools.md         MCP tool contracts
|   |-- install.md           Install and setup guide
|   |-- homebrew.md          Homebrew packaging notes
|   |-- agent-workflows.md   Copyable agent workflow examples
|   `-- open-questions.md    Product and architecture questions
|-- tests/
|   |-- dogfood_reliability.rs
|   |-- extensions.rs
|   `-- run_sql_serialization.rs
|-- scripts/
|   |-- install.sh
|   |-- package-homebrew.sh
|   |-- package-release.sh
|   `-- update-homebrew-formula.sh
|-- site/                    Astro documentation and marketing site
|-- .github/workflows/       CI, site deploy, and Homebrew tap update workflows
|-- Cargo.toml               Rust package metadata
|-- package.json             Root npm command wrappers
|-- docker-compose.example.yml
|-- .env.example
`-- README.md
```

### Runtime Shape

PGSandbox is one native binary with multiple command modes:

```text
User or MCP client
        |
        v
pgsandbox CLI
        |
        +-- mcp stdio server
        +-- direct CLI tool commands
        +-- setup config writer
        +-- doctor diagnostics
        +-- local Postgres runtime manager
        `-- smoke-test verifier
```

The MCP server talks to one selected Postgres profile at a time unless the
caller explicitly requests all-version listing or cleanup. The default profile
is a managed local Postgres cluster. External profiles are opt-in.

```text
Agent / MCP client
        |
        v
PGSandbox stdio MCP mode
        |
        v
Managed local cluster or explicit Postgres admin profile
        |
        v
Tracked sandbox databases and scoped sandbox roles
```

### Entry Points

- `rust-src/main.rs` starts Tokio and calls `pgsandbox::cli::run`.
- `rust-src/cli.rs` defaults to MCP stdio when no command is provided.
- `rust-src/mcp.rs` exposes the public MCP tools.
- `rust-src/postgres.rs` owns lifecycle behavior and database interactions.

CLI commands:

```text
pgsandbox
pgsandbox mcp
pgsandbox setup [options]
pgsandbox doctor [options]
pgsandbox create-database [options]
pgsandbox run-sql [options]
pgsandbox delete-database [options]
pgsandbox tool <mcp_tool_name> --input '{...}'
pgsandbox local init [options]
pgsandbox local start [options]
pgsandbox local stop [options]
pgsandbox local status [options]
pgsandbox smoke-test [options]
pgsandbox uninstall [options]
pgsandbox --version
pgsandbox --help
```

### Managed Local Runtime

When neither `PGSANDBOX_ADMIN_DATABASE_URL` nor `PGSANDBOX_CONFIG` is set,
PGSandbox initializes and starts a local Postgres cluster:

- root: `~/.pgsandbox` by default
- default profile: `local`
- data directory: `~/.pgsandbox/postgres/data`
- private runtime config: `~/.pgsandbox/local-postgres.json`
- default port search start: `65432`
- Unix socket directory on Unix: short PGSandbox-owned path under
  `/tmp/pgsandbox-sockets/`
- admin user: `pgsandbox_admin`

Versioned local profiles use separate state:

```text
Postgres 18 profile: local-pg18
Config:              ~/.pgsandbox/local-postgres-18.json
Data:                ~/.pgsandbox/postgres/versions/18/data
Log:                 ~/.pgsandbox/postgres/versions/18/postgres.log
```

Start a specific installed version:

```bash
pgsandbox local start --postgres-version 18
```

PGSandbox probes common local install paths for installed Postgres 18, 17, 16,
15, 14, and 13 binaries. Explicit `PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR` settings
can still target any numeric major version that reports matching server
binaries.

In MCP tools, agents should usually omit `profile` and pass only
`postgresVersion`:

```json
{ "postgresVersion": "18" }
```

Supplying both `profile` and `postgresVersion` is reserved for exact profile
targeting. A mismatch returns a structured `version_mismatch` error.

### Profiles

A profile describes an admin connection PGSandbox may use for lifecycle and
metadata operations. Profiles can be:

- managed local profiles created by PGSandbox
- explicit local Postgres URLs
- explicit private remote Postgres URLs, only with external-host opt-in
- versioned profiles carrying `postgresVersion` metadata

Admin connections are used for:

- metadata table setup
- role creation
- database creation
- database deletion
- allowlisted extension installation
- cleanup
- audit events

User SQL and repo commands use sandbox role credentials generated for the
specific database. Requested sandbox extensions are validated against the
profile's `allowedExtensions` policy and installed as an admin lifecycle
operation; admin credentials are never returned to the caller. Managed-local
profiles allow `pgcrypto`, `pg_stat_statements`, `pg_trgm`, `uuid-ossp`,
and `vector` by default. Explicit profiles deny privileged extension
installation until the operator configures an allowlist. The profile admin
role must be able to install every allowlisted extension. Installed extensions
remain owned by that lifecycle role; sandbox roles can use them but cannot
manage or drop them unless Postgres explicitly grants that ability.

### Resource Model

Each sandbox gets:

- a UUID `databaseId`
- one database
- one login role
- one generated role password
- one owner string, if supplied
- one purpose/name hint, if supplied
- JSON labels, if supplied
- optional installed extensions requested at creation time
- a `createdAt` timestamp
- an `expiresAt` timestamp
- a `deletedAt` timestamp after deletion

Generated database and role names use the configured prefix, a slugified name
hint, and a short random id while staying within Postgres's 63 byte identifier
limit:

```text
pgsandbox_<hint>_<short_id>
pgsandbox_<hint>_<short_id>_role
```

All generated SQL identifiers and literals go through helpers in
`rust-src/names.rs`.

### Metadata And Audit Tables

PGSandbox stores lifecycle metadata in the admin database for the selected
profile.

`pgsandbox_databases`:

| Column | Purpose |
|--------|---------|
| `database_id` | Stable sandbox id returned to agents |
| `profile_name` | Profile that owns the sandbox |
| `database_name` | Generated Postgres database name |
| `role_name` | Generated Postgres login role |
| `role_password` | Encrypted sandbox role password |
| `owner` | Optional agent/session/user owner |
| `purpose` | Optional name hint or task purpose |
| `labels` | JSON metadata for repo, branch, task, suite, etc. |
| `created_at` | Creation timestamp |
| `expires_at` | TTL deadline |
| `deleted_at` | Deletion marker |

`pgsandbox_events`:

| Column | Purpose |
|--------|---------|
| `event_id` | Event UUID |
| `profile_name` | Profile for the event |
| `database_id` | Sandbox id |
| `database_name` | Sandbox database name |
| `role_name` | Sandbox role name when applicable |
| `event_type` | Lifecycle event such as `create_database` or `cleanup_expired` |
| `details` | Small JSON details |
| `created_at` | Event timestamp |

Destructive operations must find a live metadata row before dropping a
database or role. PGSandbox does not drop arbitrary databases by name.

### MCP Response Envelope

Every public MCP tool returns JSON text using a compact envelope:

```json
{
  "ok": true,
  "summary": "Tool completed successfully.",
  "warnings": [],
  "errors": [],
  "detailHandles": [],
  "result": {}
}
```

Workflow tools can also include `changedObjects`. Tool failures use the same
shape with `ok: false`, stable error `code`, a broad `category`, a human
message, and a hint. Expected categories include:

- `validation`
- `database_not_found`
- `version_mismatch`
- `restore_incompatible`
- `sql_analysis`
- `sql_syntax`
- `constraint_violation`
- `readonly_violation`
- `template_not_found`
- `timeout`

Postgres errors include SQLSTATE when available.

### Public MCP Tools

| Tool | Purpose |
|------|---------|
| `list_profiles` | List configured profiles and discovered local Postgres versions. |
| `ensure_postgres` | Install missing local Postgres server binaries with a supported package manager when available, then start a managed local profile. |
| `doctor` | Return MCP-safe diagnostics and profile health. |
| `list_extensions` | List available extensions for a profile and installed extensions for an existing sandbox. |
| `create_database` | Create one isolated sandbox database and role, optionally installing requested extensions. |
| `clone_database` | Clone an existing source database into a new sandbox with `pg_dump`/`pg_restore`, optionally installing target extensions and skipping source-only extension entries. |
| `delete_database` | Delete a metadata-owned sandbox database and role. |
| `get_connection_string` | Return redacted direct/container connection variants by default, or raw credentials when explicitly requested. |
| `run_sql` | Run SQL against a sandbox with bounded result rows. |
| `describe_schema` | Return relation, column, constraint, index, view, materialized view, foreign table, and extension metadata. |
| `schema_digest` | Return a compact checksummed schema summary. |
| `schema_diff` | Compare a previous digest with the current schema. |
| `explain_query` | Return `EXPLAIN (FORMAT JSON)` for one safe plannable statement. |
| `create_schema_snapshot` | Store a named local schema checkpoint. |
| `list_schema_snapshots` | List named schema checkpoints for a sandbox. |
| `diff_schema_snapshot` | Compare a stored snapshot with the current schema. |
| `delete_schema_snapshot` | Delete a local schema snapshot artifact. |
| `prepare_for_repo` | Write secret-free repo workflow metadata to `.pgsandbox/project.json`. |
| `run_repo_command` | Run an explicit repo command with sandbox DB env vars. |
| `validate_schema_change` | Capture before/after schema digests around a repo command. |
| `seed_database` | Run an explicit seed command against a sandbox. |
| `create_template_from_sandbox` | Export a sandbox to a reusable local template artifact. |
| `create_sandbox_from_template` | Restore a template into a new tracked sandbox. |
| `list_templates` | List local template artifacts for a profile. |
| `delete_template` | Delete a template dump and metadata. |
| `list_databases` | List active metadata-owned sandboxes. |
| `cleanup_expired` | Delete expired metadata-owned sandboxes, or dry-run the selection. |

See [docs/mcp-tools.md](docs/mcp-tools.md) for full tool inputs and outputs.

### Extension Discovery And Installation

Use `list_extensions` before creating or cloning when an app depends on
Postgres extensions:

```json
{ "postgresVersion": "18" }
```

For an existing sandbox, pass `databaseId` or `databaseName` to also return
installed extension names and versions:

```json
{ "databaseName": "pgsandbox_example_abc123" }
```

The CLI exposes the same discovery path and prints JSON:

```bash
pgsandbox list-extensions --postgres-version 18
pgsandbox list-extensions --database-name pgsandbox_example_abc123
```

`create_database` and `clone_database` accept an optional `extensions` list:

```json
{
  "nameHint": "trigram search repro",
  "extensions": ["pg_trgm", "uuid-ossp"]
}
```

PGSandbox trims names, normalizes them to lowercase, deduplicates them, and
allows only letters, numbers, underscores, and hyphens. The installed names are
returned as `installedExtensions`.

Extension availability depends on the selected target profile's Postgres
installation. PGSandbox checks `pg_available_extensions` inside the target
sandbox before using the profile admin connection to run
`CREATE EXTENSION IF NOT EXISTS`; the returned sandbox role remains restricted.
Unavailable or invalid names return `invalid_extensions` and the new sandbox is
rolled back. For `clone_database`, requested extensions are installed in the
empty target sandbox before `pg_restore` runs.

For clone sources that include observability or environment-specific extensions
that the sandbox role should not create, `clone_database` skips
`pg_stat_statements` source extension entries by default. Pass
`excludeSourceExtensions` to skip additional source extensions while still
restoring the rest of the schema:

```json
{
  "sourceDatabaseUrl": "postgres://...",
  "schemaOnly": true,
  "excludeSourceExtensions": ["auto_explain"]
}
```

Use `extensions` for target extensions that should exist in the sandbox, and
`excludeSourceExtensions` for source extension entries that should be omitted
from the restore archive.

Some extensions require server-level setup such as installed extension packages
or `shared_preload_libraries`. Those failures return
`extension_setup_required`; use profile setup docs or a recipe for that profile
instead of adding a one-off MCP tool for the extension.

### SQL Execution

`run_sql` resolves the selected sandbox, obtains its sandbox role connection
string, and connects as that role. It does not execute user SQL through the
admin connection.

Result behavior:

- default `rowLimit`: `100`
- `rowLimit: 0`: valid zero-row preview
- hard row limit cap: `1000`
- negative `rowLimit` values return `invalid_row_limit`
- returns `returnedRowCount`
- returns `affectedRowCount` for DML/DDL command tags when available
- reports `totalRowCountKnown`
- reports `truncated`
- returns ordered `resultSets` for multi-statement SQL with 1-based
  `statementIndex` values. Top-level `rows` and row metadata summarize the last
  row-returning statement, or the last statement when no statement returned
  rows. The row limit applies independently to each row-returning result set.
- preserves `int8` and `numeric` values as JSON strings
- serializes `json`/`jsonb` as nested JSON
- serializes common Postgres arrays as JSON arrays
- returns unsupported non-null types as an object with the original type and a
  cast-to-text hint
- keeps SQL `NULL` as JSON `null`

With `readonly: true`, PGSandbox runs SQL in a read-only transaction, rejects
transaction-control escape hatches, and rolls the transaction back after
execution. Mutating statements are returned as structured `readonly_violation`
errors; harmless settings that Postgres permits inside the transaction, such as
`SET search_path`, may still run.

### Repo Workflow Tools

Repo workflow tools are intentionally conservative:

- They require an explicit `repoPath`.
- They execute argv arrays directly without an implicit shell.
- They reject shell wrappers and indirect launchers such as `bash -lc`, `sh -c`,
  `env`, `sudo`, and `nsenter`.
- They inject sandbox credentials into the child process environment.
- They return bounded stdout/stderr with truncation flags.
- They redact the injected database URL and password from captured output.
- They do not permanently rewrite application configuration.

Injected database environment variables include:

```text
DATABASE_URL
PGSANDBOX_DATABASE_URL
PGHOST
PGPORT
PGDATABASE
PGUSER
PGPASSWORD
```

When `databaseId` is present, `run_repo_command` resolves the owning
profile/Postgres version from sandbox metadata. Repo-inferred versions are not
allowed to override that id; an explicitly supplied conflicting profile/version
still fails normally.

Use `stdin` with a direct interpreter to run a multi-line smoke without writing
a temporary file into the repo:

```json
{
  "repoPath": "/absolute/path/to/repo",
  "databaseId": "sandbox-id",
  "command": ["python", "-"],
  "stdin": "print('run app smoke here')\n"
}
```

`stdin` accepts up to 65,536 UTF-8 bytes and rejects NUL characters. The normal
no-shell command validation remains in force.

Dockerized apps and repos with nonstandard settings can select the connection
variant and validated aliases directly:

```json
{
  "repoPath": "/absolute/path/to/repo",
  "databaseId": "sandbox-id",
  "command": ["docker", "compose", "run", "--rm", "web", "python", "manage.py", "check"],
  "connectionMode": "localContainer",
  "databaseUrlEnvNames": ["DATABASE_URI"]
}
```

Noisy commands can request `stripAnsi`, `stdoutLimit`, `stderrLimit`,
`tailLines`, and `suppressDockerLifecycle`. Limits remain bounded to 8,000
bytes per stream. `run_repo_command` reports `changedObjects: null` with
`changedObjectsUnsupportedReason` because writes made by a child process cannot
be observed reliably; use `run_sql`, `schema_digest`, or
`validate_schema_change` when side effects must be measured.

Good command examples:

```json
["npm", "run", "migrate"]
```

```json
["psql", "-v", "ON_ERROR_STOP=1", "-f", "migrations/schema.sql"]
```

```json
["./scripts/seed.sh"]
```

`prepare_for_repo` writes `.pgsandbox/project.json` without secrets. It can
store `migrationCommand`, `seedCommand`, `databaseUrlEnv`, `postgresVersion`,
and `preparedAt`. It can infer a Postgres major version from Compose files or a
devcontainer image such as `postgres:16`, `postgis/postgis:16-3.4`, or
`timescale/timescaledb:pg16`.

### Schema Snapshots And Templates

Schema snapshots are JSON metadata artifacts under:

```text
~/.pgsandbox/schema-snapshots/<profile>/<database-id>/<snapshot-name>.json
```

They store object counts, fingerprints, profile, sandbox id, owner/purpose,
labels, Postgres version, digest version, notes, and creation time. They are
manual checkpoints, not automatically refreshed truth.

Templates are dump plus metadata artifacts under:

```text
~/.pgsandbox/templates/<profile>/<template-name>.dump
~/.pgsandbox/templates/<profile>/<template-name>.json
```

Templates can only be created from PGSandbox-owned sandboxes and restored into
new PGSandbox-owned sandboxes. They are useful for local seeded-state loops,
regression fixtures, and repeatable agent QA. They are not copy-on-write forks
or a production-data import workflow.

### Clone Workflow

`clone_database` uses a portable dump/restore path:

1. Preflight source and target Postgres major versions, and capture a
   best-effort source size estimate.
2. Create an empty tracked target sandbox.
3. Install any requested and profile-allowed target extensions through the
   admin lifecycle.
4. Run `pg_dump` against the source database.
5. Skip source extension archive entries from `pg_stat_statements` and any
   requested `excludeSourceExtensions`.
6. Run `pg_restore` into the target sandbox using the sandbox role.
7. Delete the target sandbox if restore fails or times out.

Newer-to-older clone paths fail before target creation with
`restore_incompatible`. The dump/restore phase defaults to a 240 second
timeout so PGSandbox can return `command_timeout` with the created sandbox id,
source size estimate when available, and cleanup status before common MCP
clients exhaust their own call budget. Cloning and template tools require
`pg_dump` and `pg_restore`; ordinary create/query/delete flows do not.

### Telemetry

Telemetry is enabled by default and sends anonymous, personless usage events to
PostHog. It records command/tool names, version, OS/architecture, success,
elapsed time, and small booleans/counts.

Telemetry must not include:

- Postgres URLs
- connection strings
- database names or ids
- SQL text
- owner values
- label keys or values
- full local paths
- raw error messages

Telemetry never blocks CLI or MCP tool results. See
[Environment Variables](#environment-variables) for opt-out settings.

### Safety Boundaries

PGSandbox is designed as local/private infrastructure:

- It installs PostgreSQL packages only during explicit `setup`,
  `ensure-postgres`, or `local start --install-missing` runs when a supported
  package manager is available.
- It does not require Docker.
- It does not stop Docker containers.
- It does not bind `localhost:5432` by default.
- It does not silently configure external admin URLs.
- It does not expose a public network admin surface.
- It does not delete databases missing from `pgsandbox_databases`.
- It does not log full connection strings in diagnostics.
- It does not return raw sandbox credentials unless a caller explicitly asks
  for them.

Hosted database platform work is a future product direction, but it needs a
deliberate auth, tenancy, quota, billing, and security design before a public
network admin surface is added.

### Website

The `site/` directory is an Astro site. It contains docs pages, changelog
rendering, blog content, sitemap/robots routes, and a CapRover deployment
workflow. It is separate from the MCP runtime and does not run the MCP server.

## Environment Variables

### Configuration Precedence

PGSandbox loads runtime configuration in this order:

1. If `PGSANDBOX_CONFIG` is set, load the JSON config file it points to.
2. Else if `PGSANDBOX_ADMIN_DATABASE_URL` is set, create a single explicit
   profile from environment variables.
3. Else start or reuse the managed local profile under `PGSANDBOX_HOME` or
   `~/.pgsandbox`.

Telemetry opt-out environment variables are applied after config loading.

### Core Runtime Variables

| Variable | Required | Description | Default |
|----------|----------|-------------|---------|
| `PGSANDBOX_CONFIG` | No | Path to JSON multi-profile config. Takes precedence over single-profile env setup. | unset |
| `PGSANDBOX_ADMIN_DATABASE_URL` | No | Explicit Postgres admin URL for single-profile mode. Use only when intentionally bypassing managed local. | unset |
| `PGSANDBOX_DEFAULT_PROFILE` | No | Name for the single env profile. With managed local it must remain `local`. | `default` with admin URL, `local` without |
| `PGSANDBOX_HOME` | No | Local state root for managed Postgres, templates, and snapshots. | `~/.pgsandbox` |
| `PGSANDBOX_DATABASE_PREFIX` | No | Prefix for generated database and role names. | `pgsandbox` |
| `PGSANDBOX_DEFAULT_TTL_MINUTES` | No | Default sandbox TTL for the env profile. Must be positive. | `240` |
| `PGSANDBOX_MAX_TTL_MINUTES` | No | Max allowed TTL for the env profile. Must be positive and >= default TTL. | `1440` |
| `PGSANDBOX_MAX_ACTIVE_DATABASES_PER_OWNER` | No | Optional per-owner active sandbox quota for the env profile. | unlimited |
| `PGSANDBOX_ALLOWED_EXTENSIONS` | No | Comma-separated extensions authorized for privileged installation. | built-in allowlist for managed local; none for explicit profiles |
| `PGSANDBOX_POSTGRES_VERSION` | No | Default managed local Postgres major version to select. | discovered/default |

### Local Postgres Binary Discovery

| Variable | Description |
|----------|-------------|
| `PGSANDBOX_POSTGRES_BIN_DIR` | Directory containing `initdb`, `pg_ctl`, and `postgres`. |
| `PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR` | Version-specific binary directory, such as `PGSANDBOX_POSTGRES_18_BIN_DIR`. |

Discovery order favors explicit version-specific settings, then other
configured bin dirs, common package-manager locations, local `PATH` entries,
and finally direct `PATH` command resolution. Common-path version discovery
includes installed Postgres 18, 17, 16, 15, 14, and 13 binaries.

### External Admin URL Policy

By default, explicit profile admin URLs must be local:

- `localhost`
- `127.0.0.1`
- `::1`
- URLs without a host, such as Unix socket style libpq URLs

Remote/private hosts require opt-in.

| Variable | Description | Example |
|----------|-------------|---------|
| `PGSANDBOX_ALLOW_EXTERNAL_ADMIN_URL` | Allows a non-local admin URL in single-profile env mode. | `true` |
| `PGSANDBOX_ALLOWED_ADMIN_HOSTS` | Comma-separated allowlist for admin URL hosts. | `db.internal.example,postgres.internal` |

### Telemetry Variables

| Variable | Effect |
|----------|--------|
| `PGSANDBOX_TELEMETRY=false` | Disable telemetry. |
| `PGSANDBOX_NO_TELEMETRY=1` | Disable telemetry. |
| `PGSANDBOX_DISABLE_TELEMETRY=1` | Disable telemetry. |
| `DO_NOT_TRACK=1` | Disable telemetry. |

JSON config can also disable telemetry:

```json
{
  "defaultProfile": "external-pg17",
  "profiles": [
    {
      "name": "external-pg17",
      "adminUrl": "postgres://postgres:postgres@localhost:6543/postgres"
    }
  ],
  "telemetry": {
    "enabled": false
  }
}
```

### Install Script Variables

These variables are consumed by `scripts/install.sh`:

| Variable | Description | Default |
|----------|-------------|---------|
| `PGSANDBOX_REPO` | GitHub repo to download releases from. | `LVTD-LLC/pgsandbox` |
| `PGSANDBOX_GITHUB_BASE_URL` | GitHub web base URL. | `https://github.com` |
| `PGSANDBOX_GITHUB_API_URL` | GitHub API base URL. | `https://api.github.com` |
| `PGSANDBOX_INSTALL_DIR` | Directory for the installed binary. | `~/.local/bin` or `/usr/local/bin` |
| `PGSANDBOX_VERSION` | Release version to install. | latest release |
| `PGSANDBOX_TARGET` | Release target triple. | detected OS/arch/libc |
| `PGSANDBOX_SKIP_CHECKSUM` | Skip checksum verification when set to `1`. | `0` |

Pin the current manifest version explicitly:

```bash
curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh \
  | PGSANDBOX_VERSION=0.5.0 sh
```

### Site Variables

The Astro site has no required build-time environment variables. Production
deployment uses GitHub Actions secrets:

| Secret | Purpose |
|--------|---------|
| `CAPROVER_PGSANDBOX_SITE_URL` | CapRover instance URL |
| `CAPROVER_PGSANDBOX_SITE_APP` | CapRover app name |
| `CAPROVER_PGSANDBOX_SITE_TOKEN` | CapRover app token |

### Example `.env`

The root `.env.example` documents a simple local/default setup:

```bash
# By default, PGSandbox uses a managed local cluster under ~/.pgsandbox.
# Set this only when intentionally using an external Postgres admin profile.
# PGSANDBOX_ADMIN_DATABASE_URL=postgres://postgres:postgres@localhost:6543/postgres
# PGSANDBOX_HOME=/path/to/pgsandbox-home
PGSANDBOX_DATABASE_PREFIX=pgsandbox
PGSANDBOX_DEFAULT_TTL_MINUTES=240
PGSANDBOX_MAX_TTL_MINUTES=1440

# Optional alternative to single-profile env configuration.
# PGSANDBOX_CONFIG=./pgsandbox.config.json

# Optional telemetry opt-out.
# PGSANDBOX_TELEMETRY=false
```

### Multi-Profile JSON Config

Use `PGSANDBOX_CONFIG` for multiple external profiles or version-specific
profiles:

```json
{
  "defaultProfile": "external-pg17",
  "profiles": [
    {
      "name": "external-pg17",
      "adminUrl": "postgres://postgres:postgres@localhost:6543/postgres",
      "postgresVersion": "17",
      "databasePrefix": "pgsandbox",
      "defaultTtlMinutes": 240,
      "maxTtlMinutes": 1440,
      "allowedExtensions": ["pg_trgm", "vector"],
      "maxActiveDatabasesPerOwner": 3
    },
    {
      "name": "external-pg16",
      "adminUrl": "postgres://postgres:postgres@localhost:6544/postgres",
      "postgresVersion": "16"
    }
  ],
  "telemetry": {
    "enabled": false
  }
}
```

Use it:

```bash
export PGSANDBOX_CONFIG="$PWD/pgsandbox.config.json"
pgsandbox doctor
```

For a private remote host, opt in explicitly:

```json
{
  "defaultProfile": "private-dev",
  "profiles": [
    {
      "name": "private-dev",
      "adminUrl": "postgres://postgres:postgres@db.internal.example/postgres?sslmode=require",
      "postgresVersion": "17",
      "allowedAdminHosts": ["db.internal.example"],
      "allowedExtensions": ["pgcrypto", "pg_trgm"]
    }
  ]
}
```

## Available Scripts

Run root scripts from the repository root.

| Command | Description |
|---------|-------------|
| `npm run check` | Run `cargo check`. |
| `npm test` | Run `cargo test`. |
| `npm run build` | Run `cargo build --release`. |
| `npm run typecheck` | Alias for `cargo check`. |
| `npm run start` | Run `cargo run --`. |
| `npm run package:homebrew` | Build release binary and create `dist/pgsandbox-<version>.tar.gz` for the Homebrew formula flow. |
| `npm run package:release` | Build target-specific release archive and checksum file in `dist/`. |
| `npm run uninstall:pgsandbox -- --dry-run` | Preview a local uninstall/reset of PGSandbox binaries, MCP client entries, and managed local state. |
| `npm run site:build` | Run the Astro site build through the root package. |
| `npm run site:check-changelog` | Verify site changelog fallback content. |
| `npm run site:check-blog-content` | Validate blog content conventions. |
| `npm run site:check-blog-tables` | Validate generated/rendered blog tables after site build. |

Direct Cargo commands:

| Command | Description |
|---------|-------------|
| `cargo fmt -- --check` | Check Rust formatting. |
| `cargo clippy --all-targets -- -D warnings` | Run Clippy with warnings denied. |
| `cargo check` | Typecheck the Rust package. |
| `cargo test` | Run unit tests and skipped-by-default integration tests. |
| `cargo build` | Build debug binary. |
| `cargo build --release` | Build optimized release binary. |
| `cargo run -- --help` | Print CLI help. |
| `cargo run -- --version` | Print package version. |

CLI commands after installation:

| Command | Description |
|---------|-------------|
| `pgsandbox` | Start stdio MCP server. |
| `pgsandbox mcp` | Start stdio MCP server explicitly. |
| `pgsandbox setup --client codex` | Prepare managed local Postgres and write user-scoped Codex MCP config. |
| `pgsandbox setup --client cursor --scope project` | Prepare managed local Postgres and write project `.cursor/mcp.json`. |
| `pgsandbox setup --client vscode --scope project` | Prepare managed local Postgres and write project `.vscode/mcp.json`. |
| `pgsandbox setup --client claude-desktop` | Prepare managed local Postgres and write Claude Desktop user config. |
| `pgsandbox setup --client all` | Prepare managed local Postgres and write supported user-scoped configs. |
| `pgsandbox setup --client codex --dry-run` | Print intended config without writing or preparing local Postgres. |
| `pgsandbox doctor` | Check config and Postgres connectivity. |
| `pgsandbox doctor --postgres-version 18` | Check a requested managed local major version. |
| `pgsandbox create-database --name-hint task --ttl-minutes 30` | Create a tracked sandbox through the CLI. |
| `pgsandbox run-sql --database-id <id> --sql "select 1" --readonly` | Run bounded SQL through the sandbox role. |
| `pgsandbox delete-database --database-id <id>` | Delete a metadata-owned sandbox. |
| `pgsandbox tool <mcp_tool_name> --input '{...}'` | Run any MCP tool contract from the shell with camelCase JSON input. |
| `pgsandbox ensure-postgres --postgres-version 13` | Install missing local Postgres 13 binaries with a supported package manager when available, then start `local-pg13`. |
| `pgsandbox upgrade` | Upgrade Homebrew/install-script installs, rerun setup for all clients, and run doctor. |
| `pgsandbox upgrade --setup codex` | Upgrade and only refresh the Codex config. |
| `pgsandbox uninstall --dry-run` | Preview removal of PGSandbox binaries, MCP client entries, and managed local state. |
| `pgsandbox local init` | Initialize managed local Postgres without starting it. |
| `pgsandbox local start` | Initialize if needed and start managed local Postgres. |
| `pgsandbox local status` | Show managed local status. |
| `pgsandbox local stop` | Stop managed local Postgres. |
| `pgsandbox local start --postgres-version 18 --install-missing` | Start versioned local profile `local-pg18`, installing binaries with a supported package manager when available. |
| `pgsandbox smoke-test` | Create, query, and delete a sandbox with concise result lines. |
| `pgsandbox smoke-test --verbose` | Include full structured SQL result dictionaries in the smoke output. |
| `pgsandbox smoke-test --postgres-version 18` | Smoke test a specific local major version. |

Site commands:

| Command | Description |
|---------|-------------|
| `npm --prefix site run dev` | Start Astro dev server. |
| `npm --prefix site run check` | Run `astro check`. |
| `npm --prefix site run build` | Run `astro check && astro build`. |
| `npm --prefix site run preview` | Preview built Astro output. |

## Testing

### Standard Test Suite

Run the expected local checks:

```bash
npm run check
npm test
npm run build
```

For full CI parity:

```bash
cargo fmt -- --check
cargo clippy --all-targets -- -D warnings
npm run check
npm test
npm run build
npm run site:check-changelog
npm run site:check-blog-content
npm --prefix site ci --include=dev
npm run site:build
npm run site:check-blog-tables
```

### Test Layout

Rust unit tests live beside the code they cover:

```text
rust-src/config.rs     Config loading, env handling, profile validation
rust-src/local.rs      Managed local runtime paths, ports, binary discovery
rust-src/mcp.rs        Envelope normalization and tool error shaping
rust-src/names.rs      Identifier generation and SQL quoting
rust-src/postgres.rs   SQL execution, schema digest/diff, workflow behavior
rust-src/setup.rs      MCP client config writing
```

Integration-style tests live under `tests/`:

```text
tests/dogfood_reliability.rs
tests/extensions.rs
tests/run_sql_serialization.rs
tests/session_lifecycle.rs
```

These tests are compiled by `cargo test`, but live database scenarios are
skipped unless the relevant environment variable is set.

### Live Postgres Tests

Run the dogfood reliability suite against real disposable sandboxes:

```bash
PGSANDBOX_DOGFOOD_E2E=1 cargo test --test dogfood_reliability -- --nocapture
```

Run the PG18 schema snapshot regression when Postgres 18 binaries are
installed:

```bash
PGSANDBOX_DOGFOOD_PG18_E2E=1 \
  cargo test --test dogfood_reliability \
  pg18_schema_snapshot_minimal_schema_returns_without_timeout_when_enabled \
  -- --nocapture
```

Run the SQL serialization E2E test:

```bash
PGSANDBOX_RUN_SQL_SERIALIZATION_E2E=1 \
  cargo test --test run_sql_serialization -- --nocapture
```

Run the readonly transaction contract E2E test:

```bash
PGSANDBOX_RUN_SQL_READONLY_E2E=1 \
  cargo test --test run_sql_serialization \
  run_sql_readonly_contract_matches_postgres_transaction_when_enabled \
  -- --nocapture
```

Run the extension discovery and installation E2E test. It uses `pg_trgm` by
default; override the extension name when a target profile exposes a different
extension package:

```bash
PGSANDBOX_EXTENSION_E2E=1 \
  cargo test --test extensions -- --nocapture

PGSANDBOX_EXTENSION_E2E=1 PGSANDBOX_EXTENSION_E2E_NAME=citext \
  cargo test --test extensions -- --nocapture
```

Run the one-shot session cleanup matrix. It verifies a real query plus
`always`, `on-success`, `keep`, timeout, and child-spawn failure behavior:

```bash
PGSANDBOX_SESSION_E2E=1 \
  cargo test --test session_lifecycle -- --nocapture
```

Each live test creates a sandbox and attempts cleanup at the end. If cleanup
fails, the test prints the failure so you can remove the sandbox with the MCP
tool or with `pgsandbox smoke-test`/manual diagnostics. Live tests use the
normal PGSandbox config path: the managed local runtime when no explicit config
is set, or the configured `PGSANDBOX_ADMIN_DATABASE_URL`/`PGSANDBOX_CONFIG`
profile when present.

### Manual Runtime Checks

Useful local runtime checks:

```bash
pgsandbox local status
pgsandbox doctor
pgsandbox smoke-test
```

For a versioned local runtime:

```bash
pgsandbox local status --postgres-version 18
pgsandbox doctor --postgres-version 18
pgsandbox smoke-test --postgres-version 18
```

### Packaging Checks

```bash
npm run package:homebrew
npm run package:release
```

Generated release archives and checksum files are build artifacts. Do not edit
them by hand.

## Deployment

PGSandbox has two separate deployable artifacts:

1. The native `pgsandbox` CLI/MCP binary.
2. The static Astro website under `site/`.

The MCP server is intended to run locally or on a private trusted machine as a
stdio process launched by an MCP client. It is not a public web service in this
repository.

### Install Released Binary With Homebrew

This is the same recommended packaged path shown in
[Getting Started](#getting-started).

Recommended user flow:

```bash
brew install LVTD-LLC/tap/pgsandbox
pgsandbox setup --client codex
pgsandbox doctor
pgsandbox smoke-test
```

The formula lives in
[LVTD-LLC/homebrew-tap](https://github.com/LVTD-LLC/homebrew-tap), which
Homebrew addresses as `LVTD-LLC/tap`.

Restart the MCP client after setup. In Codex, run `/mcp` after restart to
verify that the `pgsandbox` server is available.

### Install Released Binary With The Script

```bash
curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh | sh
pgsandbox setup --client codex
pgsandbox doctor
```

The script downloads a platform-specific release archive, verifies checksums
when the release includes `pgsandbox-<version>-checksums.txt`, and installs
to `~/.local/bin` by default.

Install the current manifest version explicitly:

```bash
curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh \
  | PGSANDBOX_VERSION=0.5.0 sh
```

Install to a custom directory:

```bash
curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh \
  | PGSANDBOX_INSTALL_DIR=/usr/local/bin sh
pgsandbox setup --client codex --command /usr/local/bin/pgsandbox
```

### Install From Source For Development

Use source installs when contributing, testing a local checkout, or validating
an unreleased tag. Normal users should prefer Homebrew or the GitHub release
installer.

From a checkout:

```bash
cargo install --path . --force
pgsandbox setup --client codex
pgsandbox doctor
```

From GitHub:

```bash
cargo install --git https://github.com/LVTD-LLC/pgsandbox --tag v0.5.0 --force
pgsandbox setup --client codex
pgsandbox doctor
```

### Update An Existing Installation

For Homebrew and GitHub install-script installs, the shortest path is:

```bash
pgsandbox upgrade
```

`upgrade` updates the installed binary, reruns `setup --client all`, runs
`doctor`, and reminds you to restart MCP clients. It supports the same release
targets as the GitHub installer: macOS and Linux on `x86_64` or `aarch64`.
Homebrew installs are upgraded through Homebrew. GitHub install-script installs
rerun the hosted installer into the current binary directory. `--version` is
only supported for GitHub install-script installs because Homebrew upgrades use
the tap formula version.

To update only one client config, skip post-upgrade steps, or pin a GitHub
installer release:

```bash
pgsandbox upgrade --setup codex
pgsandbox upgrade --no-setup
pgsandbox upgrade --no-doctor
pgsandbox upgrade --version 0.5.0
```

Homebrew:

```bash
brew update
brew upgrade LVTD-LLC/tap/pgsandbox
pgsandbox --version
pgsandbox setup --client codex
pgsandbox doctor
```

Install script:

```bash
curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh | sh
pgsandbox --version
pgsandbox setup --client codex
pgsandbox doctor
```

Source install for development:

```bash
cargo install --path . --force
pgsandbox setup --client codex
pgsandbox doctor
```

Rerun `setup` when the binary path, explicit admin URL, selected client, or
scope changes. Restart the MCP client after updating.

### MCP Client Rollout

`pgsandbox setup` writes config while preserving unrelated servers.

Codex user config:

```bash
pgsandbox setup --client codex
```

Cursor project config:

```bash
pgsandbox setup --client cursor --scope project
```

VS Code project config:

```bash
pgsandbox setup --client vscode --scope project
```

Claude Desktop user config:

```bash
pgsandbox setup --client claude-desktop
```

A generated Codex config entry looks like:

```toml
[mcp_servers.pgsandbox]
command = "pgsandbox"
args = ["mcp"]
```

If you intentionally configure an external Postgres admin URL, `setup` writes
it into the client config environment so desktop clients do not depend on shell
startup files:

```bash
pgsandbox setup \
  --client codex \
  --admin-url "$PGSANDBOX_ADMIN_DATABASE_URL"
```

Do not use `--admin-url` for the managed local default.

### Release Packaging For Maintainers

1. Update versions in `Cargo.toml` and `package.json`.
2. Run the full test suite.
3. Build release archives.
4. Publish a GitHub release with the generated artifacts.
5. Let the Homebrew tap workflow open a PR.
6. Merge the tap PR before telling Homebrew users to upgrade.

Commands:

```bash
cargo test
npm run package:homebrew
npm run package:release
```

`npm run package:homebrew` creates:

```text
dist/pgsandbox-<version>.tar.gz
```

`npm run package:release` creates:

```text
dist/pgsandbox-<version>-<target>.tar.gz
dist/pgsandbox-<version>-checksums.txt
```

When a GitHub release is published,
`.github/workflows/update-homebrew-tap.yml` downloads or builds the Homebrew
archive, computes SHA-256, checks out `LVTD-LLC/homebrew-tap`, updates
`Formula/pgsandbox.rb`, and opens or updates a PR.

The workflow requires the `HOMEBREW_TAP_PAT` repository secret with write access
to the tap repository.

### Static Site Deployment

The site deploy workflow runs on pushes to `main` that affect site files,
`CHANGELOG.md`, the changelog checker, or the deploy workflow itself. It can
also be run manually with `workflow_dispatch`.

Workflow:

1. Install site dependencies with Node 22.
2. Run the changelog fallback check.
3. Build the Astro site.
4. Package `site/` without `node_modules`.
5. Deploy the archive to CapRover.

Local site build:

```bash
npm --prefix site ci --include=dev
npm --prefix site run build
```

Production deployment requires these GitHub secrets:

```text
CAPROVER_PGSANDBOX_SITE_URL
CAPROVER_PGSANDBOX_SITE_APP
CAPROVER_PGSANDBOX_SITE_TOKEN
```

### Docker Demo Postgres

`docker-compose.example.yml` starts a normal Postgres service on `5432` for
users who want to test explicit external profile mode:

```bash
docker compose -f docker-compose.example.yml up -d
export PGSANDBOX_ADMIN_DATABASE_URL="postgres://postgres:postgres@localhost:5432/postgres"
pgsandbox doctor
pgsandbox smoke-test
```

This is optional. Runtime code must not require Docker.

## Troubleshooting

### `could not find local Postgres binaries`

Rerun setup first. It checks the local runtime, installs PostgreSQL through a
supported package manager when available, starts the managed local cluster, and
writes MCP config:

```bash
pgsandbox setup --client codex
```

If setup cannot install PostgreSQL automatically, install the server binaries
with your system package manager and point PGSandbox at their bin directory:

```bash
export PGSANDBOX_POSTGRES_BIN_DIR="/path/to/postgres/bin"
pgsandbox setup --client codex
pgsandbox doctor
```

For a requested major version, let PGSandbox install the package when your
system package manager still provides it:

```bash
pgsandbox ensure-postgres --postgres-version 14
pgsandbox setup --client codex --postgres-version 14
```

For an older major such as Postgres 13:

```bash
pgsandbox ensure-postgres --postgres-version 13
pgsandbox setup --client codex --postgres-version 13
```

From MCP, agents can call `ensure_postgres` before creating a sandbox:

```json
{ "postgresVersion": "13", "installMissing": true }
```

Automatic PostgreSQL package installs use Homebrew on macOS; `apt-get`, `dnf`,
`yum`, `zypper`, or `pacman` on Linux; and WinGet or Chocolatey on Windows.
Versioned packages still depend on what that package manager currently
publishes. If an automatic install fails, the MCP error includes bounded
installer output and the next action: install manually and set the matching
`PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR`, or choose a newer clone target when a
matching older target is unavailable.

If your package manager does not provide that major version, point PGSandbox at
existing server binaries:

```bash
export PGSANDBOX_POSTGRES_13_BIN_DIR="/opt/homebrew/opt/postgresql@13/bin"
pgsandbox setup --client codex --postgres-version 13
```

### Requested Postgres Version Is Missing

List discovered local versions:

```bash
pgsandbox doctor
```

From MCP, call `list_profiles` with:

```json
{ "includeDiscoveredLocal": true }
```

Then install the missing version, set `PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR`, or
choose an available version.

### Existing Postgres On Port 5432

This is expected and should not be a problem. The managed local runtime starts
at `65432` and scans upward.

```bash
pgsandbox local start
pgsandbox local status
```

If you intentionally want to use the service on `5432`, set an explicit admin
URL or JSON profile.

### Stale MCP Config Uses An Old Admin URL

If `doctor` reports that it is using an admin URL from an MCP config but you
want managed local, rerun setup without `--admin-url`:

```bash
pgsandbox setup --client codex
```

Restart the MCP client and rerun:

```bash
pgsandbox doctor
```

### `pgsandbox --version` Shows A Node.js Stack Trace

An old npm/global install may be earlier in `PATH`.

```bash
which -a pgsandbox
/opt/homebrew/bin/pgsandbox --version
```

Remove the stale install or point the MCP client at the native binary:

```bash
npm uninstall -g pgsandbox
hash -r 2>/dev/null || rehash
pgsandbox setup --client codex --command /opt/homebrew/bin/pgsandbox
```

### `clone_database` Or Template Restore Fails

Install `pg_dump` and `pg_restore` from PostgreSQL client tools. Ordinary
create/query/delete flows do not require them.

```bash
pg_dump --version
pg_restore --version
```

Also check source and target Postgres major versions. Newer-to-older clone
paths are rejected before creating the target sandbox.

Large clone operations time out inside PGSandbox before common MCP client call
budgets expire. The structured timeout includes the created sandbox id/name,
best-effort source size estimate, and whether cleanup already deleted the
sandbox. Retry with a larger `timeoutSeconds`, `schemaOnly: true`, or a smaller
source/template path.

If clone restore fails on a source-only extension such as an observability
extension, retry with `excludeSourceExtensions`. `pg_stat_statements` is skipped
by default because sandbox roles commonly cannot create it.

### `run_repo_command` Rejects A Command

Repo commands are executed directly and cannot invoke shells or launchers.
Change this:

```json
["bash", "-lc", "npm run migrate && npm run seed"]
```

To direct commands or an executable repo script:

```json
["npm", "run", "migrate"]
```

```json
["./scripts/seed.sh"]
```

For a one-off multi-line Python script, keep the direct-command boundary and
send the script over stdin:

```json
{
  "command": ["python", "-"],
  "stdin": "print('smoke')\n"
}
```

### Repo Workflow Has No Migration Command

Pass a command directly:

```json
{
  "repoPath": "/absolute/path/to/repo",
  "databaseId": "sandbox-id",
  "command": ["npm", "run", "migrate"]
}
```

Or store a secret-free default:

```json
{
  "repoPath": "/absolute/path/to/repo",
  "migrationCommand": ["npm", "run", "migrate"]
}
```

`prepare_for_repo` writes `.pgsandbox/project.json`.

### SQL Results Are Truncated

`run_sql` defaults to 100 rows, accepts `rowLimit: 0` for a zero-row preview,
rejects negative `rowLimit` values with `invalid_row_limit`, and caps
`rowLimit` at 1000.

```json
{
  "databaseId": "sandbox-id",
  "sql": "select * from large_table order by id",
  "readonly": true,
  "rowLimit": 1000
}
```

Use SQL filters, aggregates, or pagination for larger inspection tasks.

### Readonly SQL Fails

With `readonly: true`, PGSandbox runs SQL in a read-only transaction and rolls
it back after execution. Mutating statements such as `INSERT` or
`CREATE TEMP TABLE` fail with `readonly_violation`; harmless settings that
Postgres permits inside the transaction, such as `SET search_path`, may still
run. If mutation is intentional, omit `readonly` or set it to `false`.

### Sandbox Was Not Found

Unscoped `databaseId` lookup searches configured profiles and running managed
local profiles. If the sandbox cannot be resolved:

1. Call `list_databases` with `includeAllVersions: true`.
2. Retry the operation with the returned `profile` or `postgresVersion`.
3. Check whether the sandbox expired or was deleted.

### Expired Sandboxes Remain

Cleanup is explicit unless you run it from a scheduler or call the MCP tool.
`list_databases` excludes expired sandboxes by default. Pass
`includeExpired: true` to inspect them; every returned database includes
`expired`, `ttlStatus`, and signed `expiresInSeconds` fields.

Profile-scoped dry run:

```json
{ "dryRun": true }
```

All running versions:

```json
{ "includeAllVersions": true, "dryRun": true }
```

Only sandboxes matching the current owner and task labels:

```json
{
  "includeAllVersions": true,
  "dryRun": true,
  "owner": "codex-run-1",
  "labels": { "task": "PGS-043" }
}
```

Then run without `dryRun` to delete selected expired sandboxes.

### Local State Permissions

If PGSandbox cannot write under `~/.pgsandbox`, fix ownership or use another
state root:

```bash
export PGSANDBOX_HOME="$HOME/.local/state/pgsandbox"
pgsandbox local start
```

### Old Unix Socket Path After Upgrade

TCP connections continue to work. If a local Unix-socket consumer needs the new
short socket path under `/tmp/pgsandbox-sockets/`, restart the local runtime:

```bash
pgsandbox local stop
pgsandbox local start
```

### External Admin URL Is Refused

Non-local admin URLs require explicit opt-in:

```bash
export PGSANDBOX_ALLOW_EXTERNAL_ADMIN_URL=true
```

Or use a host allowlist:

```bash
export PGSANDBOX_ALLOWED_ADMIN_HOSTS="db.internal.example"
```

For JSON config, use `allowExternalAdminUrl` or `allowedAdminHosts`.

### Site Build Fails

Install site dependencies and run Astro checks directly:

```bash
npm --prefix site ci --include=dev
npm --prefix site run check
npm --prefix site run build
```

If blog table checks fail, build first and then run:

```bash
npm run site:check-blog-tables
```

## Contributing

Before editing, check the worktree:

```bash
git status --short
```

Keep changes scoped to the relevant module:

- Config loading changes belong in `rust-src/config.rs` with tests there.
- Identifier generation and quoting changes belong in `rust-src/names.rs`.
- SQL execution, cleanup, TTL, schema, template, and response-shape changes
  belong in `rust-src/postgres.rs`.
- MCP tool surface changes belong in `rust-src/mcp.rs` and should be reflected
  in [docs/mcp-tools.md](docs/mcp-tools.md).
- MCP client config writing changes belong in `rust-src/setup.rs`.
- Managed local runtime changes belong in `rust-src/local.rs`.
- User-facing setup, config, command, and packaging changes should update this
  README and any relevant files in `docs/`.

Run at least:

```bash
npm run check
npm test
npm run build
```

For behavior that needs live Postgres, add the smallest practical integration
path and document whether it uses the managed local runtime or an explicit
`PGSANDBOX_ADMIN_DATABASE_URL`.

## License

MIT. See [LICENSE](LICENSE).