# pgpeek

> A minimal, read-only, team-shared Postgres browser. It pages through tables,
> previews and counts single read-only SQL statements, saves queries, and exports
> gzip-compressed CSV. It ships
> as one static Go binary.

status: ok
canonical: https://descope-sample-apps.github.io/pgpeek/
repository: https://github.com/descope-sample-apps/pgpeek
structured-index: https://descope-sample-apps.github.io/pgpeek/agent.json
full-context: https://descope-sample-apps.github.io/pgpeek/llms-full.txt

## Current facts

- 6 documented product features.
- 20 built-in UI themes.
- 3 read-only enforcement layers.
- 23 configuration rows in the website reference.
- 25 documented HTTP routes, including MCP, OAuth metadata, both probes, and the UI.
- Default row cap: 1000 (`PGPEEK_ROW_CAP`).
- Direct query exports are row-uncapped with a 512 MiB raw CSV limit.
- Default statement timeout: 30s (`PGPEEK_STATEMENT_TIMEOUT`).
- Saved queries use SQLite; browsed data remains in Postgres.
- The app is intentionally auth-thin and must sit behind an authentication layer.
- MCP is available at `/mcp` over stateless Streamable HTTP with four structured,
  read-only tools: `list_databases`, `list_tables`, `describe_table`, and `query`.
  Requests are capped at 32 KiB and structured outputs at 448 KiB with
  `truncated` signaling. Optional Descope OAuth validates issuer, exact `/mcp`
  audience, expiry, and required scopes while Descope handles DCR; Cloudflare
  Access remains an additional gate.

## Start locally

```bash
export DATABASE_URL='postgres://descoperead:PASSWORD@host:5432/db?sslmode=require'
export PGPEEK_STORE_PATH=./pgpeek.db
go run .
# open http://localhost:8080
```

Use a Postgres role with no write privileges. `Ctrl`/`Cmd` + `Enter` previews the
current SQL query.

Enable Descope OAuth by setting `DESCOPE_MCP_SERVER_WELL_KNOWN_URL` (or
`DESCOPE_CONFIG_URL`), `PGPEEK_MCP_SERVER_URL`, and
`PGPEEK_MCP_REQUIRED_SCOPES` together.

## Safety contract

1. Database role: the real security boundary; grant no write privileges.
2. Session: every pooled connection enables `default_transaction_read_only`.
3. Application: only one `SELECT`, `WITH`, `VALUES`, `TABLE`, or `EXPLAIN`
   statement is accepted.

The application guard is a guardrail, not a substitute for the read-only role.
Do not expose pgpeek publicly without SSO or another authentication proxy.

## HTTP behavior

- API errors are JSON: `{"error":"<message>"}` with an appropriate non-2xx status.
- An empty saved-query list is the explicit JSON array `[]`.
- `GET /healthz` returns JSON with `status`, semantic `version`, Git `commit`,
  and `buildDate` when the process is alive.
- `GET /readyz` returns `ready` when Postgres is reachable; otherwise it returns
  `503` with `{"error":"database not ready"}`.
- `POST /mcp` sends stateless Streamable HTTP MCP messages; `GET` and `DELETE`
  retain their protocol-defined transport behavior.
- `GET /.well-known/oauth-protected-resource` publishes public OAuth resource
  metadata when Descope MCP auth is enabled.

## Agent-readable resources

- `agent.json` — compact structured index with counts, tasks, and contracts.
- `llms-full.txt` — full repository documentation and all configuration/API detail.
- `/` — human documentation with the same canonical product facts.
- Repository `README.md` — source documentation for maintainers.

## Help

- Need configuration details: read `llms-full.txt`, section "Configuration".
- Need routes: read `llms-full.txt`, section "Endpoints".
- Need a compact machine response: fetch `agent.json`.
- Need to verify a running instance:
  `curl -fsS http://localhost:8080/healthz` then
  `curl -fsS http://localhost:8080/readyz`.
- Need more website context: fetch `/` and follow section fragment links from
  `agent.json`.