PostgreSQL AI Experts

A composable team of AI agents and skills that can design, build, and operate complete systems on Azure Database for PostgreSQL — Flexible Server, built on the premise that "PostgreSQL can be used for everything."

Status: v0.2 · Last updated: 2026-07-17

What is this?

Modern PostgreSQL — with its extension ecosystem — can credibly absorb the roles of many specialised systems (cache, queue, search, vector DB, document store, geospatial, time-series, analytics). The "just use Postgres" movement is now a mainstream architectural stance, not a novelty.

This project packages that expertise as Agent Skills and presides over them with a small number of agents, all targeting Azure Database for PostgreSQL — Flexible Server.

Goal. A composable set of AI skills covering PostgreSQL as it applies to Azure, plus the agent(s) that give them a consistent operating posture. Secondary goal: drive Azure consumption and give product/customer teams a reusable asset.
Non-goals (v1). Vanilla/self-hosted Postgres tuning as a first-class target; non-Azure managed Postgres (RDS, Cloud SQL, Neon). These may be added later behind a platform abstraction.

What Postgres replaces (the "for everything" surface)

Replaced system	PostgreSQL mechanism	Azure Flexible Server note
Pinecone / vector DB	`pgvector`, `pgvectorscale`, DiskANN index	First-class; DiskANN GA
Elasticsearch	`pg_search`/ParadeDB (BM25), `tsvector`, hybrid + RRF	Extension allow-list dependent
Redis (cache)	unlogged tables, JSONB, TTL via triggers/`pg_cron`	Supported
Redis / Kafka (queue)	`pgmq`, `SELECT … FOR UPDATE SKIP LOCKED`, `LISTEN/NOTIFY`	`pgmq` allow-list dependent
MongoDB	`JSONB` + GIN indexes	Native
Kafka (CDC)	logical decoding / logical replication	Supported
Cron	`pg_cron`	Supported
Geospatial	PostGIS	Supported
Time-series	TimescaleDB, native partitioning	Partitioning native; TimescaleDB varies
Analytics / scale-out	Citus (elastic clusters), columnar	Elastic clusters GA
In-DB AI calls	`azure_ai` (Azure OpenAI / Cognitive / ML from SQL)	Azure-exclusive value-add

The hard part is not feasibility — it's version/freshness management and Azure-specific constraints. Both are designed for below (§5, §6).

What's built

v0.2 — MVP skill set + agents. 11 azpg-* skills and 2 agents are built and committed. The semantic doc index and scheduled reconciler (freshness Layers B/C, §5) are still to come; see the roadmap.

Skills (all prefixed `azpg-`)

Skill	Cluster	Safety
`azpg-schema-design`	C1 data modeling	read-write
`azpg-index-selection`	C2 query performance	read-write
`azpg-explain-analyze`	C2 query performance	read
`azpg-pgvector-rag`	C3/C4 vector & RAG (reference)	read-write
`azpg-ai-in-database`	C4 in-database AI	read-write
`azpg-config-tuning`	C5 administration	read-write
`azpg-backup-pitr`	C5 administration	read-write
`azpg-roles-rbac`	C6 security	read-write
`azpg-provision-iac`	C7 provisioning / IaC	read-write
`azpg-stat-diagnostics`	C9 observability	read-write
`azpg-when-not-to-use`	C10 right-tool advisor	read

Agents

Agent	Role	Boundary
`azure-pg-expert`	Umbrella — sets posture, sequences work, lets skills auto-trigger	read + gated writes
`azure-pg-diagnostician`	Read-only incident-response / forensics specialist	hard read-only (safe on prod)

See §7. Agents for why the roster stops at these two.

Demo & evaluation

The skills are exercised end-to-end against a real Flexible Server, not just described:

demos/pgflow — PgFlow, a multi-tenant support/knowledge assistant where one Postgres server is the whole backend: JSONB document store, in-database azure_ai embeddings, pgvector/DiskANN search, and Row-Level Security tenant isolation, with a small live FastAPI + HTML app (demos/pgflow/app) you can run locally. Provisioned and validated on a live PG 16 server.
evals/iteration-1 — a genuine with-skill-vs-without-skill evaluation over three build tasks (provisioning, RAG core, async pipeline), scored against grounded expectations. Overall 12/18 → 16/18 (+22 pts) with the skills.

Building the demo surfaced two verified field findings now baked back into the skills: azure_ai needs the server's system-assigned managed identity (not user-assigned), and in-database azure_ai.generate (v1.3.1) is incompatible with gpt-5 reasoning models (fixed temperature=0.2) — see azpg-ai-in-database.

Repository layout

postgres-ai-experts/
├── README.md              # this file — overview + full design & conventions
├── skills/                # SKILL.md packages, grouped by cluster
│   └── azpg-<name>/SKILL.md
├── agents/                # agent definitions (umbrella + read-only diagnostician; see §7)
├── demos/                 # end-to-end showcases on a live server (PgFlow + runnable app)
│   └── pgflow/
├── evals/                 # with-skill vs without-skill evaluations
│   └── iteration-1/
└── .github/               # workflows (scheduled reconciler, CI) — planned

Distribution: authored to the Agent Skills open standard for portability; released to GitHub as the shareable asset.

Design & conventions

The rest of this document is the source of truth for how skills and agents in this repo are built. Skills cite these sections (e.g. "design §6").

3. Skill model (how we build knowledge)

We follow the Agent Skills open standard (agentskills.io, Dec 2025) as used by Claude Code.

A skill is a directory with a SKILL.md (required) plus optional supporting files. Progressive disclosure has three levels:

Metadata — YAML frontmatter (name, description). Pre-loaded into the agent's context so it knows when to trigger the skill. The description is the routing signal — it must be precise.
Body — the SKILL.md markdown, loaded only when the skill is triggered. Holds the procedure and judgement.
Bundled files — reference.md, scripts/, templates/, examples, loaded on demand. Holds detail and deterministic code.

3.1 Design principles for our skills

Version-aware, not version-pinned. Skills carry procedure + judgement; they discover the live version/extension matrix at runtime (see §5).
Azure-scoped. Every skill assumes Flexible Server constraints (superuser restrictions, extension allow-list, managed HA/backup).
Read/write safety split. Inspection skills are safe & auto-invocable; mutating skills require explicit invocation + a guarded protocol (see §6).
Deterministic where it matters. Destructive or repeatable operations are shipped as scripts/ (transaction-wrapped, --dry-run capable), not free-form model behaviour.
Index-backed. Exhaustive reference lives in a semantic doc index (§5), not inside skills. Skills point to it.

3.2 Standard skill layout

azpg-<name>/
├── SKILL.md            # frontmatter (name, description) + procedure
├── reference.md        # deep reference, loaded on demand
├── azure-constraints.md# Flexible Server caveats (where relevant)
├── scripts/
│   └── *.sh|*.py|*.sql # deterministic, guarded helpers
└── examples/
    └── *.md            # worked examples / expected output

3.3 SKILL.md frontmatter convention

---
name: azpg-pgvector-rag
description: >
  Design and implement vector / RAG search on Azure Database for PostgreSQL
  Flexible Server using pgvector, azure_ai embeddings and DiskANN. Use when the
  task involves semantic search, embeddings, RAG retrieval, or vector indexing
  on Azure Postgres.
safety: read-write        # read | read-write  (our extension field)
invocation: explicit      # auto | explicit    (write skills = explicit)
azure_target: flexible-server
pg_versions: ">=14"
requires_extensions: [vector]
---

safety, invocation, azure_target, pg_versions, requires_extensions are project conventions layered on top of the standard name/description.

4. Skill taxonomy

Skills are grouped into clusters. Each row is a candidate skill; ★ = MVP (built).

C1. Data modeling & SQL

★ azpg-schema-design — tables, keys, constraints, naming
jsonb-document-modeling — document-store patterns
partitioning-strategy — declarative partitioning, pruning
window-functions-and-ctes — advanced query construction
data-types-and-domains — type selection, enums, domains

C2. Query performance

★ azpg-explain-analyze — read/interpret plans, spot regressions
★ azpg-index-selection — btree/GIN/GiST/BRIN/HNSW/DiskANN choice
vacuum-and-autovacuum-tuning — bloat, wraparound, stats
statistics-and-planner — ANALYZE, extended stats, planner knobs
query-rewrite-patterns — anti-patterns → optimised forms

C3. Extensions / "replace X"

★ azpg-pgvector-rag — vector/RAG (reference skill)
fulltext-and-bm25 — tsvector + pg_search/ParadeDB, hybrid + RRF
queues-with-pgmq — durable/ephemeral queues, SKIP LOCKED
caching-patterns — unlogged tables, JSONB KV, TTL
postgis-geospatial — spatial types, indexes, queries
timeseries-timescaledb — hypertables, continuous aggregates
citus-scale-out — elastic clusters, distribution keys

C4. AI / RAG

★ azpg-ai-in-database — azure_ai embeddings & LLM calls from SQL
chunking-and-ingestion — document chunking, ingestion pipelines
hybrid-search-rrf — combine vector + lexical + reciprocal rank fusion
vector-index-tuning — HNSW/DiskANN params, recall/latency trade-offs

C5. Administration / HA

★ azpg-config-tuning — server params, PgBouncer
★ azpg-backup-pitr — automated backups, point-in-time restore
replication-and-ha — zone-redundant HA, read replicas, failover
connection-pooling — built-in PgBouncer, sizing
major-version-upgrade — near-zero-downtime upgrade path

C6. Security / compliance

★ azpg-roles-rbac — roles, least privilege, GRANT, Entra auth
row-level-security — RLS policies, multi-tenant isolation
entra-id-auth — Microsoft Entra ID authentication
encryption-tls-cmk — TLS enforcement, customer-managed keys
auditing-and-pgaudit — audit logging, compliance evidence

C7. Azure platform (provision & operate)

★ azpg-provision-iac — Bicep/Terraform/AVM provisioning
scaling-and-storage — compute/storage scaling, auto-grow
monitoring-and-alerts — Azure Monitor, metrics, pg_stat_statements
cost-optimization — SKU right-sizing, reserved capacity
waf-alignment — Well-Architected review for the DB tier

C8. Migration

migration-assessment — source analysis, compatibility
schema-and-data-migration — pgloader/DMS, cutover
oracle-mysql-to-postgres — dialect conversion
migration-validation — row counts, checksums, parity

C9. Observability / SRE

★ azpg-stat-diagnostics — pg_stat_*, slow-query forensics
incident-triage — AppLens / Azure Monitor triage flow
alerting-and-slos — define SLOs, alert rules

C10. Right-Tool Advisor ("devil's advocate")

★ azpg-when-not-to-use — break-out thresholds & trade-offs
breakout-eventhubs-servicebus — messaging beyond pgmq
breakout-ai-search — search/vector beyond pgvector at scale
breakout-cosmosdb — global distribution beyond JSONB
breakout-redis-enterprise — extreme-latency cache

The Right-Tool Advisor deliberately argues against the premise where the evidence warrants, recommending when and how to break out of the single- platform approach. It keeps the system honest.

5. Freshness & knowledge pipeline

Three complementary layers keep guidance current without constant skill rewrites.

Layer A — Dynamic context injection (per skill, at invocation)

Skills embed shell commands (!`command`) whose output is inlined into the skill before the model reads it. We use this so skills read the live state instead of hardcoding facts:

live extension/version matrix — SELECT extname, extversion FROM pg_extension
current server config — az postgres flexible-server show/list
this month's platform changes — fetch Azure "What's new" feed

Layer B — Semantic documentation index (cross-cutting)

Ingest Tier-1 sources into Azure AI Search / Foundry IQ (hybrid + semantic). Skills carry procedure; the index carries exhaustive reference. Skills instruct: "for parameter specifics, query the index." This keeps the 3,500-page manual out of skill bodies and makes freshness an indexing problem (easier to automate) rather than a rewriting problem.

Layer C — Scheduled reconciler (weekly/monthly job)

A scheduled workflow that:

diffs upstream release notes / "What's new" since last run;
re-runs the skills-builder to flag skills whose procedures changed (facts drift into the index automatically — only behavioural changes touch skills);
opens a PR with proposed skill updates for human review.

                 ┌─────────────────────────┐
  upstream docs ─▶  Ingest → AI Search /    │──▶ retrieval for all agents
  (Tier 1)       │  Foundry IQ (hybrid)     │
                 └─────────────────────────┘
                 ┌─────────────────────────┐
  release notes ─▶  Scheduled reconciler    │──▶ PR: skill procedure updates
                 │  (diff → skills-builder) │
                 └─────────────────────────┘
  live instance ─▶  Dynamic injection at skill invocation (version-aware)

Source tiers

Tier 1 — canonical (ground truth):

PostgreSQL official manual (Tutorial, SQL, Server Admin, Client Interfaces, Server Programming, Reference, Internals).
Microsoft Learn — Azure Database for PostgreSQL Flexible Server, azure_ai, pgvector, AVM/Bicep/Terraform module docs, Well-Architected.
Per-extension official docs: pgvector, PostGIS, Citus, TimescaleDB, pgmq, pg_cron, ParadeDB/pg_search, pgvectorscale, PgBouncer, Patroni.

Tier 2 — operational depth:

psql/pg_dump/pg_basebackup references, postgresql.conf params, system catalogs (pg_stat_*), EXPLAIN semantics.
Azure CLI/REST for PostgreSQL, azure-mcp Postgres tooling, azqr/WAF.
Practitioner corpora (use-the-index-luke, pgMustard, Depesz, Cybertec, EDB) — cite, don't treat as canonical.

Tier 3 — living / telemetry:

Release notes + Azure "What's new" feeds (freshness signal).
The target instance's own catalogs (schema, extensions, config, plans) as dynamic context, not baked knowledge.

6. Safety model

Split skills by blast radius; enforce via invocation control + guarded scripts.

Class	Examples	Invocation	Guardrails
Read / inspect	schema, EXPLAIN, `pg_stat_*`, config audit	`auto`	none needed; safe
Write / mutate	DDL, param writes, provisioning, upgrade, failover	`explicit`	plan → dry-run / txn-wrapped → confirm

Rules:

Mutating skills MUST present a plan and require explicit confirmation before execution.
Destructive operations ship as scripts/ that are transaction-wrapped and support --dry-run; the model does not free-hand destructive SQL.
Mutating skills should run in a subagent to contain blast radius.
A backup/PITR precheck is required before schema-destructive operations.

7. Agents

Do not build one omniscient agent — but also do not reflexively build a roster of specialists. Current agent-engineering practice (Anthropic's Agent Skills standard; Cognition's "Don't Build Multi-Agents", 2025) is that skills are auto-invoked by a general-purpose agent from their description metadata — they already are the routing layer — and that a **single well-architected agent

strong context engineering** beats a multi-agent fan-out for most tasks (fan-out adds coordination cost, context fragmentation, weaker traceability).

So we ship a small, deliberate set:

Agent	Owns / draws on	Produces
`azure-pg-expert` (umbrella)	all skills; shared "connect + inspect" context	posture, task decomposition, sequencing, gated writes
`azure-pg-diagnostician` (specialist)	read-only surfaces of C9/C2/C5/C10	incident triage reports; hands remediation back to the umbrella

The umbrella agent holds shared context (target instance, PG + extension version matrix) and reconciles cross-cutting conflicts (e.g. a security control that harms performance). The diagnostician is the one justified split (see §7.1).

7.1 Read the fuller roster as a capability map, not a build mandate

An earlier design imagined an Orchestrator + seven specialists (Architect, Performance, AI/RAG, DBA/Reliability, Security, SRE, Right-Tool Advisor). Treat that as a capability map — it documents which skill clusters exist and who conceptually owns them — not a mandate to run eight standing agents.

Applied here:

Default: one umbrella agent. azure-pg-expert sets the posture (inspect-live-first, read/write safety split, least privilege, cite Learn) and lets the azpg-* skills auto-trigger and carry the procedure.
Split out a specialist only for a real boundary: a permission boundary (e.g. azure-pg-diagnostician — a read-only diagnostician with no write tools, safe against prod), context isolation (a large parallel investigation), or a divergent persona that can't coexist.
Blast-radius subagents for mutating skills (§6) are a runtime pattern, not a standing persona.

Roadmap

Phase 0 — Design ✅

Taxonomy, agents, freshness, safety.

Phase 1 — Reference skill + spine ✅

azpg-pgvector-rag end-to-end (exercises azure_ai, dynamic injection, read/write split, bundled scripts).
azure-pg-expert umbrella agent.

Phase 2 — Core clusters ✅ (skills + agents)

★ MVP skills across C1–C10 (11 skills built).
azure-pg-diagnostician read-only specialist.
✅ Validated against a sandbox Flexible Server — see demos/pgflow and the iteration-1 evaluation.

Phase 3 — Breadth + freshness automation

Fill remaining (non-★) skills across the taxonomy.
Stand up the AI Search index (Layer B) + scheduled reconciler (Layer C).
Add the Migration cluster (C8).

Phase 4 — Release

Package to the Agent Skills open standard; publish to GitHub with docs and examples.

Name		Name	Last commit message	Last commit date
agents		agents
demos/pgflow		demos/pgflow
evals/iteration-1		evals/iteration-1
skills		skills
.gitattributes		.gitattributes
.gitignore		.gitignore
README.md		README.md

Latest commit

History

Folders and files

Repository files navigation