Agent Skills Basics

What a Postgres SME Agent Skill contains and when to invoke it - ten examples for teams using AI assistants on PostgreSQL 18.4, Patroni 4.x, pgBackRest 2.55+, and Flyway/Liquibase migrations.

Prerequisites

Skills live beside the database platform repo or monorepo docs:

.cursor/skills/
├── explain-review/
│   └── SKILL.md
├── migration-safety/
│   └── SKILL.md
├── failover-drill/
│   └── SKILL.md
└── incident-triage/
    └── SKILL.md

Pin the stack in every skill header:

## Stack pin
- PostgreSQL 18.4 (server and psql client)
- pgBackRest 2.55+ (self-hosted backup)
- Patroni 4.x / RDS Multi-AZ (per cluster ADR)
- Flyway 10+ or Liquibase 4+ (migration runner)

Scope: Skills orchestrate how an agent should work. Human cookbooks in EXPLAIN Basics and Migrations Basics remain authoritative.

Basic Examples

1. What an Agent Skill Is

An Agent Skill is a structured SKILL.md file that tells an AI assistant what to do, what to ask for, and what not to do on a database task.

Artifact	Purpose
`SKILL.md`	Triggers, inputs, outputs, guardrails
`references/`	Links to ADRs, runbooks, migration folders
`examples/`	Prompts that passed staff DBA review

Skills are operational contracts, not SQL tutorials
Skills must be version-pinned - agents suggest PG 14 syntax without 18 context
One skill = one decision domain (EXPLAIN review, migration safety, failover, incident)

2. Minimum SKILL.md Structure

# Migration Safety Skill
 
## What this skill does
Scores DDL lock risk and proposes safer rollout order.
 
## When to invoke
- PR adds ALTER TABLE, CREATE INDEX, or FK
- Large table migration without expand-contract
 
## Inputs (required)
- Migration SQL file path
- Table row count estimate
- Target environment (staging/prod)
- Maintenance window policy
 
## Outputs
- Lock risk score (low/medium/high)
- Safer DDL alternative if applicable
- Verification: psql -f migration on staging + lock watch query
 
## Guardrails
- Never suggest non-CONCURRENT index on prod hot tables
- Never run DDL in agent without human approval
- Require rollback SQL in PR
 
## Stack pin
PostgreSQL 18.4 · Flyway 10+
 
## Example prompts
See §10 below.

3. When to Invoke vs When to Read Docs

Situation	Use a skill	Read human docs
"Review this PR query plan"	EXPLAIN Review Skill	EXPLAIN Basics
"Score migration lock risk"	Migration Safety Skill	Zero Downtime DDL
"Quarterly Patroni failover drill"	Failover Drill Skill	HA Basics
"Disk full on PGDATA at 3am"	Incident Triage Skill	Disk Full on PGDATA
"Learn what MVCC is"	Read fundamentals docs	Not a skill invocation

4. Stack Pinning Prevents Hallucinations

## Stack pin (required)
- PostgreSQL 18.4 - NOT deprecated syntax without justification
- CREATE INDEX CONCURRENTLY - NOT plain CREATE INDEX on prod OLTP
- IDENTITY columns preferred over SERIAL for new tables (PG 18 idioms)

Agents default to older training data (SERIAL, unsafe DDL)
Pin migration runner commands your CI already runs: flyway migrate, not ad hoc psql

5. Invocation Rules for the Team

## Team invocation policy
1. Name the skill in the prompt: "Use Migration Safety Skill on V20260709__add_index.sql"
2. Attach table size query output if available
3. Agent must output verification SQL before claiming done
4. Human runs migration on staging before prod approval
5. Never auto-merge skill-generated migration PRs without DBA review

6. Guardrails Are the Highest-Value Section

## Guardrails
- No prod DDL from agent without ticket ID
- No pg_terminate_backend without blocker PID evidence
- No DROP DATABASE / TRUNCATE without named backup checkpoint
- EXPLAIN must use (ANALYZE, BUFFERS) on staging, not prod by default
- Rollback SQL required for every forward migration

Copy guardrails from postmortems - each SEV-1 becomes a bullet
See Agent Skills Best Practices

7. Verifiable Outputs Only

Bad output: "The index migration is safe."

Good output:

-- Staging verification
BEGIN;
\i db/migrations/V20260709__add_index.sql
SELECT locktype, mode, granted FROM pg_locks WHERE relation = 'orders'::regclass;
ROLLBACK;

flyway -url=jdbc:postgresql://staging:5432/myapp migrate
psql "$STAGING_URL" -c "SELECT indexname FROM pg_indexes WHERE tablename = 'orders';"

Intermediate Examples

8. Skill Registry in Platform Repo

docs/skills/README.md          # index with invoke triggers
.cursor/skills/                # Cursor-native paths

# Postgres SME Skills Index
| Skill | Trigger | Owner |
|-------|---------|-------|
| explain-review | query PR | app + DBA rotation |
| migration-safety | DDL PR | platform DBA |
| failover-drill | quarterly | SRE |
| incident-triage | SEV1/2 database | on-call |

9. Composing Multiple Skills

Prompt: "Use Migration Safety Skill on V12__fk.sql, then EXPLAIN Review Skill on linked ORM query"
 
Order:
1. Score DDL lock risk
2. If medium+, propose CONCURRENTLY or expand-contract
3. Review application SELECT plan after index exists

10. Example Prompts That Work

Use EXPLAIN Review Skill:
- PR: #842 orders list query
- Attach: EXPLAIN (ANALYZE, BUFFERS) from staging
- Table: orders ~45M rows
- Output: pass/fail checklist + index suggestion if needed

Use Failover Drill Skill:
- Topology: Patroni 4.x, 3 nodes, etcd
- Target: staging cluster only
- Output: step-by-step failover + verification SQL + rollback to old primary

FAQs

Where should SKILL.md files live?

Platform repo .cursor/skills/ for cluster-specific conventions; org registry for shared incident and upgrade playbooks. Always pin PostgreSQL 18.4 unless ADR documents mixed majors.

Skills vs Cursor rules?

Rules are always-on constraints ("no prod DDL in chat"). Skills are invoked playbooks with inputs/outputs. Use both.

Should agents run flyway migrate automatically?

Skill may suggest commands; human approves on staging first. CI uses controlled service account, not developer laptop creds for prod.

Agent Skills Best Practices - 25-item team summary
EXPLAIN Review Skill - plan critique
Migration Safety Skill - DDL lock scoring
Failover Drill Skill - HA verification
Incident Triage Skill - SEV1 decision tree

Stack versions: This page was written for PostgreSQL 18.4, pgBackRest 2.55+, Patroni 4.x, and Flyway 10+ / Liquibase 4+.

Agent Skills Basics

Prerequisites

Skills live beside the database platform repo or monorepo docs:

.cursor/skills/
├── explain-review/
│   └── SKILL.md
├── migration-safety/
│   └── SKILL.md
├── failover-drill/
│   └── SKILL.md
└── incident-triage/
    └── SKILL.md

Pin the stack in every skill header:

## Stack pin
- PostgreSQL 18.4 (server and psql client)
- pgBackRest 2.55+ (self-hosted backup)
- Patroni 4.x / RDS Multi-AZ (per cluster ADR)
- Flyway 10+ or Liquibase 4+ (migration runner)

Scope: Skills orchestrate how an agent should work. Human cookbooks in EXPLAIN Basics and Migrations Basics remain authoritative.

Basic Examples

1. What an Agent Skill Is

An Agent Skill is a structured SKILL.md file that tells an AI assistant what to do, what to ask for, and what not to do on a database task.

Artifact	Purpose
`SKILL.md`	Triggers, inputs, outputs, guardrails
`references/`	Links to ADRs, runbooks, migration folders
`examples/`	Prompts that passed staff DBA review

Skills are operational contracts, not SQL tutorials
Skills must be version-pinned - agents suggest PG 14 syntax without 18 context
One skill = one decision domain (EXPLAIN review, migration safety, failover, incident)

2. Minimum SKILL.md Structure

# Migration Safety Skill
 
## What this skill does
Scores DDL lock risk and proposes safer rollout order.
 
## When to invoke
- PR adds ALTER TABLE, CREATE INDEX, or FK
- Large table migration without expand-contract
 
## Inputs (required)
- Migration SQL file path
- Table row count estimate
- Target environment (staging/prod)
- Maintenance window policy
 
## Outputs
- Lock risk score (low/medium/high)
- Safer DDL alternative if applicable
- Verification: psql -f migration on staging + lock watch query
 
## Guardrails
- Never suggest non-CONCURRENT index on prod hot tables
- Never run DDL in agent without human approval
- Require rollback SQL in PR
 
## Stack pin
PostgreSQL 18.4 · Flyway 10+
 
## Example prompts
See §10 below.

3. When to Invoke vs When to Read Docs

Situation	Use a skill	Read human docs
"Review this PR query plan"	EXPLAIN Review Skill	EXPLAIN Basics
"Score migration lock risk"	Migration Safety Skill	Zero Downtime DDL
"Quarterly Patroni failover drill"	Failover Drill Skill	HA Basics
"Disk full on PGDATA at 3am"	Incident Triage Skill	Disk Full on PGDATA
"Learn what MVCC is"	Read fundamentals docs	Not a skill invocation

4. Stack Pinning Prevents Hallucinations

## Stack pin (required)
- PostgreSQL 18.4 - NOT deprecated syntax without justification
- CREATE INDEX CONCURRENTLY - NOT plain CREATE INDEX on prod OLTP
- IDENTITY columns preferred over SERIAL for new tables (PG 18 idioms)

Agents default to older training data (SERIAL, unsafe DDL)
Pin migration runner commands your CI already runs: flyway migrate, not ad hoc psql

5. Invocation Rules for the Team

## Team invocation policy
1. Name the skill in the prompt: "Use Migration Safety Skill on V20260709__add_index.sql"
2. Attach table size query output if available
3. Agent must output verification SQL before claiming done
4. Human runs migration on staging before prod approval
5. Never auto-merge skill-generated migration PRs without DBA review

6. Guardrails Are the Highest-Value Section

## Guardrails
- No prod DDL from agent without ticket ID
- No pg_terminate_backend without blocker PID evidence
- No DROP DATABASE / TRUNCATE without named backup checkpoint
- EXPLAIN must use (ANALYZE, BUFFERS) on staging, not prod by default
- Rollback SQL required for every forward migration

Copy guardrails from postmortems - each SEV-1 becomes a bullet
See Agent Skills Best Practices

7. Verifiable Outputs Only

Bad output: "The index migration is safe."

Good output:

-- Staging verification
BEGIN;
\i db/migrations/V20260709__add_index.sql
SELECT locktype, mode, granted FROM pg_locks WHERE relation = 'orders'::regclass;
ROLLBACK;

flyway -url=jdbc:postgresql://staging:5432/myapp migrate
psql "$STAGING_URL" -c "SELECT indexname FROM pg_indexes WHERE tablename = 'orders';"

Intermediate Examples

8. Skill Registry in Platform Repo

docs/skills/README.md          # index with invoke triggers
.cursor/skills/                # Cursor-native paths

# Postgres SME Skills Index
| Skill | Trigger | Owner |
|-------|---------|-------|
| explain-review | query PR | app + DBA rotation |
| migration-safety | DDL PR | platform DBA |
| failover-drill | quarterly | SRE |
| incident-triage | SEV1/2 database | on-call |

9. Composing Multiple Skills

Prompt: "Use Migration Safety Skill on V12__fk.sql, then EXPLAIN Review Skill on linked ORM query"
 
Order:
1. Score DDL lock risk
2. If medium+, propose CONCURRENTLY or expand-contract
3. Review application SELECT plan after index exists

10. Example Prompts That Work

Use EXPLAIN Review Skill:
- PR: #842 orders list query
- Attach: EXPLAIN (ANALYZE, BUFFERS) from staging
- Table: orders ~45M rows
- Output: pass/fail checklist + index suggestion if needed

Use Failover Drill Skill:
- Topology: Patroni 4.x, 3 nodes, etcd
- Target: staging cluster only
- Output: step-by-step failover + verification SQL + rollback to old primary

FAQs

Where should SKILL.md files live?

Platform repo .cursor/skills/ for cluster-specific conventions; org registry for shared incident and upgrade playbooks. Always pin PostgreSQL 18.4 unless ADR documents mixed majors.

Skills vs Cursor rules?

Rules are always-on constraints ("no prod DDL in chat"). Skills are invoked playbooks with inputs/outputs. Use both.

Should agents run flyway migrate automatically?

Skill may suggest commands; human approves on staging first. CI uses controlled service account, not developer laptop creds for prod.

Agent Skills Best Practices - 25-item team summary
EXPLAIN Review Skill - plan critique
Migration Safety Skill - DDL lock scoring
Failover Drill Skill - HA verification
Incident Triage Skill - SEV1 decision tree

Stack versions: This page was written for PostgreSQL 18.4, pgBackRest 2.55+, Patroni 4.x, and Flyway 10+ / Liquibase 4+.

Agent Skills Basics

Search Documentation

Agent Skills Basics