postgresql-schema-analyser

name	postgresql-schema-analyser
description	Connects to a live PostgreSQL cluster and produces .crux/docs/postgresql.md and .crux/summaries/postgresql.md. Collects cluster topology, databases, schemas, extensions, roles, and connection stats. Read-only — does not modify any database objects. Use when: (1) .crux/docs/postgresql.md is missing during onboarding, (2) user requests a schema or database review, (3) cluster configuration has significantly changed.
license	MIT
compatibility	opencode
metadata	{"owner":"postgresql-admin","type":"read-only","approval":"No"}

postgresql-schema-analyser

Owner: postgresql-admin Type: read-only Approval: No

---

What I Do

Connects to a live PostgreSQL cluster via psql, collects database and schema facts (topology, extensions, roles, table counts, size, connection stats), and writes a structured architecture document to .crux/docs/postgresql.md with a token-efficient summary at .crux/summaries/postgresql.md.

---

When to Use Me

• .crux/docs/postgresql.md is missing and cluster is reachable
• User requests: "analyse database", "generate schema doc", "update db architecture"
• Cluster or schema configuration has significantly changed
• Onboarding Step 4 triggers me automatically

---

Context Requirements

Requires already loaded:
  .crux/workspace/postgresql-admin/MEMORY.md
    Fields needed:
      primary-host
      primary-port  (default: 5432)
      admin-user
      postgresql-version

Does not load additional docs — all data comes from live cluster.

Estimated token cost: ~550 tokens
Unloaded after: docs written and MANIFEST.md updated

---

Inputs

Input	Source	Required
psql access	environment / PGPASSWORD or .pgpass	Yes
primary-host	MEMORY.md	Yes
target-databases	user	No — analyses all non-system databases if omitted

---

Steps

1. Verify access
   psql -h {host} -p {port} -U {user} -c "SELECT version();" -t 2>&1
   IF connection fails → stop, notify:
     "Cannot connect to PostgreSQL at {host}:{port}.
      Check credentials and pg_hba.conf."

2. Collect cluster-level facts
   psql ... -c "SELECT version();"
   psql ... -c "SHOW max_connections;"
   psql ... -c "SHOW shared_buffers;"
   psql ... -c "SHOW wal_level;"
   psql ... -c "SELECT count(*) FROM pg_stat_activity;"
   Check for replicas:
     psql ... -c "SELECT * FROM pg_stat_replication;"
   Check for pgBouncer:
     psql -h {host} -p 6432 ... -c "SHOW version;" 2>/dev/null

3. Collect database inventory
   psql ... -c "
     SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size,
            datcollate, datctype
     FROM pg_database
     WHERE datname NOT IN ('template0','template1','postgres')
     ORDER BY pg_database_size(datname) DESC;"

4. For each database (up to 10):
   Connect to db: psql -d {dbname} ...

   4a. List schemas
       SELECT schema_name
       FROM information_schema.schemata
       WHERE schema_name NOT IN ('pg_catalog','information_schema','pg_toast')
       ORDER BY schema_name;

   4b. Table counts and sizes per schema
       SELECT schemaname,
              count(*) AS table_count,
              pg_size_pretty(sum(pg_total_relation_size(quote_ident(schemaname)||'.'||quote_ident(tablename)))) AS total_size
       FROM pg_tables
       WHERE schemaname NOT IN ('pg_catalog','information_schema')
       GROUP BY schemaname
       ORDER BY schemaname;

   4c. Extensions
       SELECT name, default_version, installed_version
       FROM pg_available_extensions
       WHERE installed_version IS NOT NULL
       ORDER BY name;

   4d. Roles (top-level, no pg_ system roles)
       SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
       FROM pg_roles
       WHERE rolname NOT LIKE 'pg_%'
       ORDER BY rolname;

   4e. Connection stats
       SELECT state, count(*)
       FROM pg_stat_activity
       WHERE datname = '{dbname}'
       GROUP BY state;

5. Write .crux/docs/postgresql.md (see Output section)

6. Write .crux/summaries/postgresql.md (max 200 tokens)

7. Update MANIFEST.md docs entry:
     last-updated: {DATE}
     tokens: {estimated}
     summary: .crux/summaries/postgresql.md

8. Skill complete — notify:
   "Architecture document written to .crux/docs/postgresql.md"

---

Output

Writes to: .crux/docs/postgresql.md and .crux/summaries/postgresql.md

docs/postgresql.md

# PostgreSQL Architecture

> Generated by postgresql-schema-analyser on {DATE}.
> Source: live cluster at {host}:{port}.
> Update: @postgresql-admin analyse database

## Cluster

- **Host**: {host}:{port}
- **PostgreSQL version**: {version}
- **Max connections**: {n}
- **Shared buffers**: {value}
- **WAL level**: {level}
- **Active connections**: {n}
- **Replication**: {none | {n} standby(s)}
- **pgBouncer**: {detected at {host}:6432 | not detected}

## Databases

| Database | Size | Collation |
|---|---|---|
| {name} | {size} | {collation} |

## Database: {dbname}

### Schemas

| Schema | Tables | Total Size | Notes |
|---|---|---|---|
| {schema} | {n} | {size} | {tenant schema / system / public} |

### Extensions

| Extension | Version |
|---|---|
| {name} | {version} |

### Roles

| Role | Superuser | Can Login | Notes |
|---|---|---|---|
| {role} | {yes/no} | {yes/no} | {app role / admin / read-only} |

### Connection State

| State | Count |
|---|---|
| active | {n} |
| idle | {n} |
| idle in transaction | {n} |

## Notes

{Anomalies, partial data, or observations}

---
*Last updated: {DATE} — source: live-analysis*

summaries/postgresql.md

# PostgreSQL — Summary

{n} database(s) at {host}:{port}. Version: {version}.
Schemas: {list or count}. Extensions: {key ones}.
Connections: {active}/{max}. Replication: {yes/no}.
Last analysed: {DATE}.

---

Error Handling

Condition	Action
psql not found	Stop. Notify: "psql is required. Install postgresql-client."
Connection refused	Stop. Notify with host:port, suggest checking pg_hba.conf and firewall.
Permission denied	Stop. Notify: "User {user} lacks SELECT on system catalogs. Grant pg_monitor role."
More than 10 databases	Analyse first 10 by size descending. Note truncation.
Partial data (some queries fail)	Continue. Mark affected sections (partial — {reason}).
.crux/docs/ not writable	Write to .crux/workspace/postgresql-admin/output/. Notify user.