PostgreSQL Query Testing

Use this skill to run ad-hoc PostgreSQL queries for testing, debugging, and performance analysis.

Running Queries

Use the included query script, and name the target:

node .claude/skills/postgres-query/query.mjs --prod "SELECT * FROM \"User\" LIMIT 5"
node .claude/skills/postgres-query/query.mjs --dev  "SELECT * FROM \"User\" LIMIT 5"

Targets

Pick exactly one. Two target flags in one call is an error, not a silent precedence rule, and an unrecognised option (--devv, --data_packet) is rejected rather than ignored — a swallowed typo would fall through to the default target, which is production.

Omitting the target still runs against prod, so old commands keep working — but the run prints No target flag given, defaulted to --prod. Pass the flag.

Every run prints which database answered

Before connecting, the script writes a line to stderr naming the target, the access mode, the user@host:port/database pg itself resolved, and the env var it came from:

Target: PROD (read-only) -> <user>@<host>:25061/civitai [DATABASE_REPLICA_URL, timeout 30s]

It prints under --quiet and --json too, and before every exit path that reaches a database — including a blocked write, so you can always see which database you just aimed a DELETE at. It is on stderr, so --json piped to a file is still clean JSON.

The host and port come from the client's own resolved connection parameters rather than a re-parse of the string, so the line cannot disagree with where the query actually went.

🔴 Read that line before you trust a result. This skill's .env and the app's root .env both define DATABASE_URL, and they name different databases — the skill's is production, the root one is the dev snapshot the dev server uses. The skill's .env wins: it is loaded first, and loadEnv only fills in keys that are not already present (an empty value counts as present, so a bare DATABASE_REPLICA_URL= left in this file does not hand that key to the root .env — the target falls back to this file's DATABASE_URL instead, and the banner says read-only, but pointed at the primary). So a bare query.mjs answers from production while your dev server answers from dev. Comparing a value read here against one read by the running app is comparing two different databases unless both lines say the same host and port. That cost an hour and produced a confidently wrong root cause on 2026-08-16: limit: 8 from the app's DB and limit: 40 from this skill's, reported as a config bug that did not exist.

Options

--writable combined with --data-packet or --notifications is an error — both are read-only replicas, so the combination can only mean a mistake.

Examples

# Simple query
node .claude/skills/postgres-query/query.mjs --prod "SELECT id, username FROM \"User\" LIMIT 5"

# Same query against the dev database
node .claude/skills/postgres-query/query.mjs --dev "SELECT id, username FROM \"User\" LIMIT 5"

# Check query performance
node .claude/skills/postgres-query/query.mjs --prod --explain "SELECT * FROM \"Model\" WHERE id = 1"

# Override default 30s timeout for longer queries
node .claude/skills/postgres-query/query.mjs --prod --timeout 60 "SELECT ... (complex query)"

# Query the notifications-db
node .claude/skills/postgres-query/query.mjs --notifications "SELECT count(*) FROM \"Notification\""

# Query from file
node .claude/skills/postgres-query/query.mjs --dev -f my-query.sql

# JSON output for processing (banner goes to stderr, stdout stays valid JSON)
node .claude/skills/postgres-query/query.mjs --prod --json "SELECT id, username FROM \"User\" LIMIT 3"

There is a second skill with this name

apps/event-engine/.claude/skills/postgres-query/ is a separate copy with its own .env, and it has none of the above — no target flag, no banner. Skills are directory-scoped, so work under apps/event-engine resolves to that one. Check which script you are invoking by path before trusting its output.

Querying the dev database (cnpg)

The dev database is not reachable directly — it needs an SSH tunnel to an internal host. Ask an infra owner for the connection recipe; the specifics are not documented here because this repository is public (see the Security section of CLAUDE.md).

Once the tunnel is up, set DEV_DATABASE_URL in .claude/skills/postgres-query/.env to point at your local forwarded port.

Running dev queries

# Read-only (default — writes are blocked client-side)
node .claude/skills/postgres-query/query.mjs --dev "SELECT count(*) FROM \"User\""

# Writable DML (needs user permission)
node .claude/skills/postgres-query/query.mjs --dev --writable "UPDATE ..."

--dev uses DEV_DATABASE_URL either way — unlike --prod, there is no separate replica credential, so --writable only lifts the client-side guard. Whether a write lands is decided by the role in that URL.

DDL does work through this credential — verified 2026-08-17 on both dev and the prod primary as role civitai: ALTER TABLE ... ADD COLUMN, ADD CONSTRAINT, CREATE INDEX and a CREATE TABLE/DROP TABLE round trip all succeeded. Migrations can be applied with --writable.

This section previously said the opposite — that a ddl_command_end event trigger reassigning object ownership made any CREATE TABLE roll back with must be able to SET ROLE "<schema owner>". Nobody has observed that, and it is contradicted by the run above. It cost an escalation to an infra owner that was not needed. If you do hit an ownership error, record what you ran and correct this section again rather than restoring the blanket claim.

⚠️ A multi-statement script needs --writable even when its first statement is harmless. The client-side write guard checks every statement's leading keyword, so a migration opening with SET lock_timeout / BEGIN is still refused on account of the ALTERs underneath. That is deliberate.

Querying the notifications-db

The notifications database is not reachable directly — it needs an SSH tunnel to an internal host, and access has to be granted first.

Ask an infra owner for access and the connection recipe. The bastion host, the forward target, and where the credentials live are deliberately not documented here, because this repository is public — see the Security section of CLAUDE.md.

Once you have the tunnel open, set NOTIFICATION_DB_REPLICA_URL in .claude/skills/postgres-query/.env to point at your local forwarded port.

Running queries

# With the tunnel open in another terminal:
node .claude/skills/postgres-query/query.mjs --notifications \
  "SELECT count(*) FROM \"Notification\""

node .claude/skills/postgres-query/query.mjs --notifications --explain \
  "SELECT * FROM \"UserNotification\" WHERE \"userId\" = 12345 ORDER BY \"createdAt\" DESC LIMIT 50"

Available tables (read-only)

• Notification — canonical notifications
• UserNotification — per-user fanout (largest table)
• PendingNotification — processing queue (often empty)

The role notifications_readonly only has SELECT. Writes are also rejected at the pooler level (replica routing).

Safety Features

1. Read-only by default: --prod uses DATABASE_REPLICA_URL to prevent accidental writes
2. Write protection: Blocks INSERT/UPDATE/DELETE/DROP unless --writable flag is used
3. Replica targets refuse --writable: --notifications and --data-packet error on the flag, and their roles/poolers reject writes anyway
4. Explicit permission required: Before using --writable, you MUST ask the user for permission
5. The target is printed on every run: no result can be attributed to the wrong database by accident

⚠️ The write guard is a typo-catcher, not a sandbox. It matches the query's leading keyword (after stripping leading comments) and the (INSERT|UPDATE|DELETE|… inside a leading WITH. A write buried deeper than that gets through the client. What actually stops writes is the role you connect as — which is why --prod defaults to the read-only replica credential. Do not treat a missing --writable as proof a query cannot write.

When to Use --writable

Only use the --writable flag when:

• The user explicitly requests write access
• You need to test write operations
• You're verifying transaction behavior

IMPORTANT: Always ask the user for permission before running with --writable.

Comparing Query Performance

To compare two query approaches — same target on both runs, or the comparison is meaningless:

# Run first approach
node .claude/skills/postgres-query/query.mjs --prod --explain "SELECT ... (approach 1)"

# Run second approach
node .claude/skills/postgres-query/query.mjs --prod --explain "SELECT ... (approach 2)"

# Compare actual results
node .claude/skills/postgres-query/query.mjs --prod --json "SELECT ... (approach 1)" > /tmp/q1.json
node .claude/skills/postgres-query/query.mjs --prod --json "SELECT ... (approach 2)" > /tmp/q2.json

Verifying Index Usage

Run with --explain and look for:

• Good: "Index Scan", "Bitmap Index Scan", "Index Only Scan"
• Bad: "Seq Scan" on large tables (indicates missing or unused index)

node .claude/skills/postgres-query/query.mjs --prod --explain "SELECT * FROM \"Account\" WHERE provider = 'discord'"