postgres-advanced

---
name: postgres-advanced
description: >
license: MIT
---

# postgres-advanced

## Purpose

Production-grade PostgreSQL backbone for AI agents. Provides persistent storage, safe SQL execution, versioned schema migrations, agent memory (JSONB KV), append-only audit logging, health verification, and backup creation.

This is the foundational persistence layer for distributed systems that need durable, auditable state shared across multiple agents and services.

---

## When To Use

Use this skill when an agent needs:
- Long-term persistent state across sessions
- Shared storage across multiple skills or services
- Event tracking or audit history
- Structured JSON storage (agent memory)
- Safe execution of database queries
- Schema migrations or initialization
- System health validation or backup generation

**Do NOT use this skill to:**
- Run destructive DDL (DROP, ALTER, TRUNCATE) — these are blocked by the safety scanner
- Execute arbitrary multi-statement SQL scripts
- Store plaintext secrets (use environment variables)
- Manage database infrastructure (user provisioning, cluster config)
- Create ad-hoc tables for shared/core schema — use `pg migrate up` with the built-in migration system (skills that own their own storage may create and use their own tables; see boundary clarification below)

## When NOT to Use

NOT for:
- Quick prototyping (use sqlite-quick for lightweight, zero-config local development)
- Embedded or local-only applications where PostgreSQL can't be installed
- Environments where a full database server isn't available or isn't practical

**Boundary clarification:** Core schema (tables shared across multiple skills, like `agent_memory` and `events`) must be managed through `pg migrate up` with versioned migration files. Skills that own their own storage (e.g., analytics dashboards with `watchlist_items` and `portfolio_holdings`) may create their own tables and use them (INSERT, UPDATE, DELETE, SELECT) directly. However, ALTER TABLE is blocked regardless of ownership — schema modifications always require a new migration. When in doubt, prefer the migration system — it provides version history and safe rollback.

---

## Prerequisites

### 1. PostgreSQL Server

You need a running PostgreSQL instance (version 14+, 16+ recommended). Options:

**Local development (macOS):**
```bash
brew install postgresql@16
brew services start postgresql@16
```

**Local development (Linux):**
```bash
sudo apt-get install postgresql postgresql-contrib
sudo systemctl start postgresql
```

**Docker (any platform):**
```bash
docker run -d \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=openclaw \
  -p 5432:5432 \
  postgres:16-alpine
```

### 2. Set Up PG_DSN

Create or obtain a connection string in this format:
```
postgresql://user:password@host:5432/dbname?sslmode=require
```

**Example (local):**
```bash
export PG_DSN="postgresql://postgres:password@localhost:5432/openclaw?sslmode=disable"
```

**Example (cloud, e.g., Neon, Railway, AWS RDS):**
```bash
export PG_DSN="postgresql://user:secretpass@api.neon.tech:5432/mydb?sslmode=require"
```

### 3. Verify Connectivity

```bash
pg health
```

Should return:
```json
{
  "ok": true,
  "server_version": "PostgreSQL 16.1",
  "latency_ms": 42,
  "db": "openclaw",
  "user": "postgres",
  "host": "localhost",
  "ssl": false
}
```

---

## Quick Start

1. **Set up PostgreSQL and PG_DSN** (see Prerequisites)
2. **Run migrations to create core schema:**
   ```bash
   pg migrate up
   ```
3. **Verify with a health check:**
   ```bash
   pg health
   ```
4. **Start using agent memory:**
   ```bash
   pg mem set --namespace my-agent --key config --json '{"version":"v1"}'
   pg mem get --namespace my-agent --key config
   ```
5. **Log an event:**
   ```bash
   pg event append --type my-agent.started --json '{"timestamp":"2026-03-21T10:30:00Z"}'
   ```

---

## Core Capabilities

### Database Health Check
```bash
pg health
```
Validates connectivity, SSL mode, latency, and server version. Run this first when debugging any database issue.

### Migrations
```bash
pg migrate up
pg migrate status
```
Lightweight versioned migration system. MVP schema includes `agent_memory` and `events` tables.

### Safe SQL Execution
```bash
pg query --sql "SELECT * FROM events WHERE type=%s AND ts > %s" --params '["aggregator.completed","2026-02-01"]'
```

**Allowed:** SELECT, INSERT, UPDATE, DELETE, WITH (CTE wrapping allowed statements).

**Blocked:** DROP, ALTER, TRUNCATE, GRANT, REVOKE, CREATE EXTENSION, VACUUM, ANALYZE, COPY PROGRAM, multi-statement SQL. Statement timeout enforced per session.

> **Note:** Only `CREATE EXTENSION` is blocked, not all CREATE statements. Skills that own their own storage (e.g., analytics dashboards) may use CREATE TABLE through this interface. For shared/core schema changes, use `pg migrate up`.

### Agent Memory (JSONB KV)
```bash
pg mem set --namespace my-project --key model_config --json '{"version":"v3","params":1200000}'
pg mem get --namespace my-project --key model_config
pg mem search --namespace my-project --contains version
```

Schema:
```sql
agent_memory(
  namespace TEXT,
  key       TEXT,
  value     JSONB,
  updated_at TIMESTAMPTZ DEFAULT now()
)
-- Primary key: (namespace, key)
```

### Event Log (Append-Only)
```bash
pg event append --type aggregator.completed --json '{"items":23,"duration_s":42}'
pg event tail --type aggregator.completed --limit 50
```

Schema:
```sql
events(
  id        BIGSERIAL PRIMARY KEY,
  ts        TIMESTAMPTZ DEFAULT now(),
  type      TEXT NOT NULL,
  payload   JSONB,
  actor     TEXT,
  trace_id  TEXT
)
-- Indexes: ts DESC, (type, ts DESC)
```

### Backup
```bash
pg backup create --out ./backups/openclaw_YYYYMMDD.dump
```
Uses `pg_dump -Fc`. Requires PostgreSQL client tools installed on the host.

---

## Trigger Examples

| User says | Action |
|-----------|--------|
| "Is the database healthy?" | `pg health` |
| "Run migrations" | `pg migrate up` |
| "Store this result for later" | `pg mem set --namespace {context} --key {key} --json '{...}'` |
| "What did the last data collector run find?" | `pg event tail --type collector.completed --limit 5` |
| "Query the events table for today" | `pg query --sql "SELECT * FROM events WHERE ts > %s" --params '["2026-02-13"]'` |
| "Back up the database" | `pg backup create --out ./backups/openclaw_YYYYMMDD.dump` |
| "What's stored in agent memory for Project Alpha?" | `pg mem search --namespace project-alpha --contains ""` (empty string lists all keys in namespace) |
| "Log that the data collector just ran" | `pg event append --type collector.completed --json '{"items":23}'` |

---

## Namespace Conventions

Agent memory uses `namespace` to isolate data between projects and agents. Follow this convention to prevent collisions:

| Pattern | Use Case | Example |
|---------|----------|---------|
| `{project}` | Project-level config and state | `project-alpha`, `content-automation` |
| `{project}.{agent}` | Agent-specific state within a project | `project-alpha.screener`, `content-automation.tracker` |
| `{skill}` | Skill-level operational state | `analytics-dashboard`, `data-collector` |
| `system` | Infrastructure-level state | `system` (reserved for ops) |

**Event type conventions** follow dot notation: `{domain}.{action}`. Examples: `video.rendered`, `collector.completed`, `portfolio.updated`, `backup.created`.

---

## Schema Ownership Map

Which project uses which tables and namespaces. Items marked *(planned)* are reserved conventions for projects not yet built — agents should not expect data to exist for these yet.

| Project / Skill | Tables Used | Memory Namespace | Event Types |
|----------------|-------------|------------------|-------------|
| **analytics-dashboard** | `watchlist_items`, `portfolio_holdings` (own tables) | `analytics-dashboard` | `portfolio.updated`, `watchlist.changed` *(reserved — not yet emitted)* |
| **Data Collector** | `agent_memory`, `events` | `data-collector` | `collector.completed`, `brief.sent` |
| **System / Ops** | `agent_memory`, `events` | `system` | `backup.created`, `migration.applied` |
| **Project Alpha** *(planned)* | `agent_memory`, `events` | `project-alpha`, `project-alpha.*` | `model.trained`, `signal.detected` |
| **Content Automation** *(planned)* | `agent_memory`, `events` | `content-automation` | `protocol.updated`, `metric.logged` |

---

## Inputs

### Required Environment Variables
```bash
PG_DSN="postgresql://user:pass@host:5432/dbname?sslmode=require"
```

### Optional Environment Variables
```bash
PG_APP_NAME="openclaw-postgres-advanced"   # default
PG_STATEMENT_TIMEOUT_MS=15000              # default
PG_CONNECT_TIMEOUT_S=5                     # default
```

---

## Output Format

All commands return structured JSON to stdout.

**Health example:**
```json
{
  "ok": true,
  "server_version": "PostgreSQL 16.1",
  "latency_ms": 42,
  "db": "openclaw",
  "user": "postgres",
  "host": "localhost",
  "ssl": false
}
```

**Query example:**
```json
{
  "ok": true,
  "kind": "select",
  "rowcount": 2,
  "columns": ["id", "name"],
  "rows": [[1, "a"], [2, "b"]],
  "elapsed_ms": 18
}
```

**Error example:**
```json
{
  "ok": false,
  "error": "Unsafe SQL detected: DROP not allowed",
  "sql_fragment": "DROP TABLE..."
}
```

---

## Errors & Recovery

| Failure | Detection | Recovery Action |
|---------|-----------|-----------------|
| Missing `PG_DSN` | Configuration error on any command | Print: "PG_DSN environment variable not set. Export it with your connection string." Do not attempt to guess or prompt for credentials. |
| Invalid credentials | `pg health` returns `ok=false`, auth error | Verify DSN components: user, password, host, port, dbname. Suggest: `pg health` to test after correcting. |
| Connection timeout | `ok=false`, timeout error | Check if Postgres is running and reachable. Verify host/port. Increase `PG_CONNECT_TIMEOUT_S` if on slow network. |
| Unsafe SQL detected | `ok=false`, safety scanner message | Explain which statement was blocked and why. If the blocked statement is DDL for **core/shared schema** (e.g., ALTER on `agent_memory`), guide the user to create a versioned migration file. If it's DDL for a **skill-owned table** (e.g., CREATE TABLE for a new skill), note that CREATE TABLE is allowed — the issue may be a different blocked keyword (DROP, ALTER). |
| Multi-statement SQL | Blocked | Split into separate `pg query` calls, one statement each. |
| `pg_dump` missing | Backup error, command not found | Install PostgreSQL client tools: `brew install libpq && brew link --force libpq` (macOS) or `apt install postgresql-client` (Linux). Alternatively on macOS: `brew install postgresql@16`. |
| Statement timeout | `ok=false`, timeout | Simplify the query, add indexes, or increase `PG_STATEMENT_TIMEOUT_MS` for heavy analytical queries. |
| Migration conflict | `pg migrate up` fails | Run `pg migrate status` to check current version. Inspect the failing migration file for SQL errors. |

---

## Operational Constraints

- Enforces statement timeout per connection
- Blocks multi-statement SQL unless explicitly enabled
- Parameterized execution only (no string interpolation)
- Requires Python 3.11+, psycopg3, Postgres 14+ (recommended 16+)
- Requires `pg_dump` installed for backups

---

## Security Model

- DSN provided via environment variables only (never hardcoded)
- SQL safety scanner blocks destructive commands before execution
- No `shell=True` subprocess calls anywhere
- Session timeouts prevent runaway queries
- Idle transaction timeout enforced
- Backup uses direct subprocess invocation with argument list (no shell expansion)

---

## Works Well With

- **agent-memory** — Leverage postgres-advanced as the backend for persistent agent state
- **people-memories** — Store and retrieve person-specific memories across sessions
- **sqlite-quick** — For local-only prototyping; graduate to postgres-advanced for production
- **workflow-orchestrator** — Coordinate multi-step workflows with shared database state

---

## Eval Criteria

| Metric | Target | How to Test |
|--------|--------|-------------|
| Health check | Returns `ok=true` with valid server info | `pg health` against running Postgres |
| Migration | Creates `agent_memory` + `events` tables | `pg migrate up` then verify with `pg query --sql "SELECT tablename FROM pg_tables WHERE schemaname='public'"` |
| Memory roundtrip | Set → get returns same value | `pg mem set` then `pg mem get` |
| Memory search | Returns matching keys | `pg mem set` 3 keys, `pg mem search` finds correct subset |
| Event roundtrip | Append → tail returns the event | `pg event append` then `pg event tail` |
| SQL safety | Blocks DROP, ALTER, TRUNCATE | `pg query --sql "DROP TABLE events"` returns `ok=false` |
| CREATE EXTENSION blocked | Blocks CREATE EXTENSION specifically | `pg query --sql "CREATE EXTENSION pg_trgm"` returns `ok=false` |
| CREATE TABLE allowed | Permits skill-owned table creation | `pg query --sql "CREATE TABLE test_skill_table (id SERIAL PRIMARY KEY)"` returns `ok=true`. Clean up via direct `psql` or test teardown (DROP is blocked through the skill). |
| Multi-statement block | Blocks `SELECT 1; DROP TABLE events` | Returns `ok=false` |
| Backup | Produces non-zero `.dump` file | `pg backup create --out /tmp/test.dump` |
| Response time | Health < 1s, queries < 2s for simple selects | Timed execution |

---

## Error Handling

| Error | Cause | Solution |
|-------|-------|----------|
| `FATAL: password authentication failed` | Wrong credentials in PG_DSN | Verify username/password; check `pg_hba.conf` auth method |
| `FATAL: database "X" does not exist` | Typo in database name or not created | Run `createdb X` or verify PG_DSN database name |
| `ERROR: permission denied for table` | Role lacks privileges | Grant via superuser: `GRANT SELECT, INSERT ON table TO role` |
| `ERROR: relation "X" does not exist` | Table not created or wrong schema | Check `search_path`; qualify with schema: `schema_name.table` |
| `FATAL: too many connections` | Connection pool exhausted | Check for leaked connections; increase `max_connections` or use pgBouncer |
| Connection timeout | Network/firewall issue or server down | Verify host:port reachable; check `pg_isready`; review firewall rules |
| `ERROR: deadlock detected` | Concurrent transactions waiting on each other | Retry transaction; review lock ordering; use `SET lock_timeout` |

## Cross-Skill Dependencies

| Skill | Relationship |
|-------|-------------|
| `analytics-dashboard` | Consumes Postgres as optional shared storage backend. Uses own tables (`watchlist_items`, `portfolio_holdings`). |
| `data-collector` | Logs completion events, stores run metadata in agent memory. |
| `content-synthesis` (future) | Reads from multiple skill databases for cross-tool synthesis. |
| All agent skills | Can use `agent_memory` for KV state and `events` for audit logging. |

---

## Roadmap

- **v1.0.0** (current): Production-ready with schema ownership, recovery procedures, eval criteria
- **v1.1.0**: Connection pooling, read replicas, named parameters
- **v2.0.0**: RBAC templates, RLS scaffolding, automated backups, monitoring integration
## Pitfalls
- Document failure modes as you encounter them