Postgres Schema Introspection

You are inspecting a live PostgreSQL database via a read-only MCP server.

When to use this skill

• Snapshot is stale (> 24h) or user asks to "check the database"
• User asks what's in the schema for a given table or wants a Mermaid diagram reviewed against actual state
• Suspected drift between code and database, or after a migration was mentioned
• User wants performance diagnostics from pg_stat_statements

Pre-flight

Confirm a Postgres MCP tool is loaded (mcp__postgres-mcp-pro__*, mcp__zen-postgres__*, mcp__pgedge__*). If none, point the user at mcp-configuration.md. Then run one combined check:

SELECT current_user,
       current_setting('default_transaction_read_only') AS read_only,
       pg_is_in_recovery()                              AS on_replica;

Halt and report a § XV violation (constitution: MCP must be read-only on a replica) if read_only is off and no SELECT-only role is in use, or if on_replica is false for a production connection.

What to introspect

Full per-table query set lives in introspection-queries.md (columns, constraints, indexes, RLS, triggers, FKs in/out, size, JSONB sampling, pg_stat_statements). Two most-used inline:

-- Columns + nullability + defaults
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = $1 AND table_name = $2
ORDER BY ordinal_position;

-- Indexes with predicates
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = $1 AND tablename = $2;

For multi-table batches across the whole schema, use the project's /speckit.dbsync workflow — never one query per table.

Project-specific guardrails

• No tenant PII into the LLM context — project columns, never SELECT * (§ XV)
• No unbounded query on tables > 10 k rows — TABLESAMPLE SYSTEM (1) or LIMIT n
• No side-effect functions (pg_terminate_backend, dblink_*, pg_read_file) — read-only invariant
• Halt on privilege escalation — report violation, do not introspect (§ XV)
• Every recommended index cites a query pattern or constraint (§ XI)

After introspection

Write the results into .specify/memory/current-schema-state.md between the <!-- BEGIN AUTO-GENERATED --> and <!-- END AUTO-GENERATED --> markers. Never touch the hand-edited sections. Snapshot shape:

| field | value |
|-------|-------|
| Last sync | 2026-05-04T10:31:08Z |
| Migration head | 20260503_140012_add_pricing_rules_target_price |
| Snapshot version | 42 |

<!-- BEGIN AUTO-GENERATED -->
### catalog_products  (module: catalog, ~5,012,418 rows)
- Columns: id (bigint PK), tenant_id (bigint NOT NULL), sku (text), …
- Constraints: PK(id), UQ(tenant_id, sku), FK(brand_id → catalog_brands.id)
- Indexes: idx_catalog_products_tenant_sku (tenant_id, sku) — UQ
- RLS: tenant_isolation USING (tenant_id = current_setting('app.tenant_id')::bigint)
<!-- END AUTO-GENERATED -->

If new tables appeared since the last sync, add them to Open Schema Questions asking the user to assign module ownership. Diff against the previous version with git diff .specify/memory/current-schema-state.md and report drift.

Examples

• "Add a wholesale price to products." — inspect catalog_products columns + sample metadata JSONB keys. Surface duplicates (existing price, cost, compare_at_price) and informal JSONB usage. Hand off to schema-evolution-workflow.

• "Why is product listing slow?" — pull the slow-query block from introspection-queries.md, then surface missing tenant-first composite indexes, sequential scans, and JSONB GIN opportunities. Every recommended index cites a query pattern (§ XI) and uses CREATE INDEX CONCURRENTLY.