postgres-advanced

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:[e-mail]: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

1. Set up PostgreSQL and PG_DSN (see Prerequisites)
2. Run migrations to create core schema:

   pg migrate up
   

3. Verify with a health check:

   pg health
   

4. Start using agent memory:

   pg mem set --namespace my-agent --key config --json '{"version":"v1"}'
   pg mem get --namespace my-agent --key config
   

5. 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()
)
-- Primary key: (namespace, key)

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
)
-- Indexes: ts DESC, (type, ts DESC)

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"   # default
PG_STATEMENT_TIMEOUT_MS=15000              # default
PG_CONNECT_TIMEOUT_S=5                     # default

---

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