| name | postgres-advanced |
| version | 1.0.0 |
| description | Production-grade PostgreSQL backbone for AI agents. Persistent storage, safe SQL execution, versioned schema migrations, agent memory, audit logging, health checks, and backups. Use this skill when you need a database, persistent storage, SQL queries, data migrations, audit trails, or agent state management. Also trigger for "database", "postgres", "SQL", "migration", "backup", or "persistent storage."
|
| author | G-HunterAi |
| license | MIT |
| tags | ["database","postgres","sql","persistence","migrations","backup"] |
| platforms | ["all"] |
| category | infrastructure |
| metadata | {"clawdbot":{"emoji":"🐘","requires":{"bins":["psql","python3"]},"secrets":[{"name":"PG_DSN","description":"PostgreSQL connection string. Format: postgresql://user:pass@host:5432/dbname"}]}} |
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):
brew install postgresql@16
brew services start postgresql@16
Local development (Linux):
sudo apt-get install postgresql postgresql-contrib
sudo systemctl start postgresql
Docker (any platform):
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):
export PG_DSN="postgresql://postgres:password@localhost:5432/openclaw?sslmode=disable"
Example (cloud, e.g., Neon, Railway, AWS RDS):
export PG_DSN="postgresql://user:secretpass@api.neon.tech:5432/mydb?sslmode=require"
3. Verify Connectivity
pg health
Should return:
{
"ok": true,
"server_version": "PostgreSQL 16.1",
"latency_ms": 42,
"db": "openclaw",
"user": "postgres",
"host": "localhost",
"ssl": false
}
Quick Start
- Set up PostgreSQL and PG_DSN (see Prerequisites)
- Run migrations to create core schema:
pg migrate up
- Verify with a health check:
pg health
- Start using agent memory:
pg mem set --namespace my-agent --key config --json '{"version":"v1"}'
pg mem get --namespace my-agent --key config
- Log an event:
pg event append --type my-agent.started --json '{"timestamp":"2026-03-21T10:30:00Z"}'
Core Capabilities
Database Health Check
pg health
Validates connectivity, SSL mode, latency, and server version. Run this first when debugging any database issue.
Migrations
pg migrate up
pg migrate status
Lightweight versioned migration system. MVP schema includes agent_memory and events tables.
Safe SQL Execution
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)
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:
agent_memory(
namespace TEXT,
key TEXT,
value JSONB,
updated_at TIMESTAMPTZ DEFAULT now()
)
Event Log (Append-Only)
pg event append --type aggregator.completed --json '{"items":23,"duration_s":42}'
pg event tail --type aggregator.completed --limit 50
Schema:
events(
id BIGSERIAL PRIMARY KEY,
ts TIMESTAMPTZ DEFAULT now(),
type TEXT NOT NULL,
payload JSONB,
actor TEXT,
trace_id TEXT
)
Backup
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
PG_DSN="postgresql://user:pass@host:5432/dbname?sslmode=require"
Optional Environment Variables
PG_APP_NAME="openclaw-postgres-advanced"
PG_STATEMENT_TIMEOUT_MS=15000
PG_CONNECT_TIMEOUT_S=5
Output Format
All commands return structured JSON to stdout.
Health example:
{
"ok": true,
"server_version": "PostgreSQL 16.1",
"latency_ms": 42,
"db": "openclaw",
"user": "postgres",
"host": "localhost",
"ssl": false
}
Query example:
{
"ok": true,
"kind": "select",
"rowcount": 2,
"columns": ["id", "name"],
"rows": [[1, "a"], [2, "b"]],
"elapsed_ms": 18
}
Error example:
{
"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
