postgres-skills/CONTRIBUTING.md

Contributing to PostgreSQL Agent Skills

Thank you for your interest in improving these skills! This guide covers how to add new skills or improve existing ones.

Contributor License Agreement

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.

Repository layout

postgres-skills/
├── .github/plugin/marketplace.json     # Copilot marketplace manifest
├── .claude-plugin/marketplace.json     # Claude Code marketplace manifest
├── .agents/plugins/marketplace.json    # Codex marketplace manifest
├── plugin/                             # Plugin root (MCP server config + skills)
│   ├── plugin.json                     # Agent Plugins v1.0 manifest
│   ├── mcp.json                        # Agent Plugins v1.0 MCP declaration
│   ├── .mcp.json                       # postgres-mcp server launch (npx)
│   └── skills/
│       ├── postgresql-best-practices/SKILL.md   # Routing table + principles
│       └── postgresql-best-practices/references/ # 23 detailed reference files
├── tests/
│   ├── .skills.json                     # Routing fixture (used by tests + evals only)
│   ├── conftest.py                      # pytest fixtures + MCP client + routing engine
│   ├── pytest.ini                       # marker registration (integration, pg, azure)
│   ├── docker-compose.yml               # local/CI PostgreSQL (pgvector) for pg + integration lanes
│   ├── evals/                           # Eval pipeline (300 challenges, LLM-as-judge)
│   │   ├── pipeline.py                  # Main eval orchestrator
│   │   ├── challenges/challenges.yaml   # 300 test challenges
│   │   └── results/latest.json          # Eval results (results/ is gitignored)
│   ├── test_skill_routing.py            # Skill routing + content contracts (no DB)
│   ├── test_activation_precision.py     # SKILL.md routing-table precision (no DB)
│   ├── test_mcp_protocol.py             # MCP protocol conformance (no DB)
│   ├── test_sql_syntax.py              # SQL fence validation: structural (no DB) + parse (marker: pg)
│   ├── test_mcp_e2e.py                  # End-to-end MCP queries (marker: integration)
│   └── test_ai_app.py                   # AI-application dogfood (marker: azure)
├── .github/workflows/ci.yml             # CI pipeline (default pytest lane + pg/integration/eval jobs)
└── README.md

Keep the three marketplace manifests identical. They are regular JSON files rather than symlinks because marketplace clients on Windows may check out Git symlinks as plain text, which makes the manifest invalid.

How routing works

1. Agent loads plugin/skills/postgresql-best-practices/SKILL.md (lightweight routing table)
2. Routing table matches user's question to a reference file via keyword triggers
3. Azure references are gated: postgres_mcp_get_server_capabilities must confirm isAzure: true
4. Agent loads the specific reference file and combines it with its own knowledge
5. Reference provides Azure-specific constraints, decision guides, and anti-hallucination guardrails

Adding or editing a skill

This repo ships a single skill, postgresql-best-practices, made up of:

• plugin/skills/postgresql-best-practices/SKILL.md — the lightweight routing table that loads first
• plugin/skills/postgresql-best-practices/references/*.md — 23 detailed reference files (11 generic + 12 Azure)

Most contributions add or improve a reference file. Two steps:

1. Add the reference at plugin/skills/postgresql-best-practices/references/<name>.md, following the naming convention:

   • postgresql-* — generic, works on any PostgreSQL deployment (always available)
   • azure-postgresql-* — Azure Database for PostgreSQL (gated by isAzure: true)

2. Register it in the routing table in SKILL.md by adding a row under the matching section ("PostgreSQL Skills (always available)" or "Azure PostgreSQL Skills"):

   | keyword, triggers, here | [<name>](references/<name>.md) | when to use it |
   

Reference file template

---
title: "Human-Readable Title"
description: "One-line description of what this reference covers."
tags: [postgresql, topic]
---

Recommended sections

References are supplemental context, so focus on what LLMs get wrong. Common sections used across existing references:

1. When to use — trigger conditions and when to route elsewhere
2. Key Facts (what models get wrong) — version-gated features, correct parameters
3. Common Mistakes — pitfalls and anti-patterns
4. Anti-Hallucination Rules — managed-service constraints, what NOT to suggest

Quality Standards

• Token budget: keep each reference under ~3,000 tokens (CI warns above this)
• Hard cap: 4,000 tokens — tests/test_skill_size.py fails the build above this (estimated as characters / 4)
• Use CREATE EXTENSION vector (binary name), not CREATE EXTENSION pgvector
• Azure references must use the azure_pg_admin role, never SUPERUSER
• Never suggest ALTER SYSTEM or OS-level access for Azure Flexible Server — use az ... parameter set or the portal
• Include non-reversible operation warnings BEFORE the command
• No diagnostic loops (no pre-query role checks)

Testing

The entire test suite is pytest-based and lives in tests/, sharing fixtures via tests/conftest.py. Install dependencies once with pip install -r tests/requirements.txt, then run from the repo root.

The pg and integration lanes run against a local PostgreSQL (with pgvector) started via Docker Compose — start it first so those lanes connect (they fail, rather than skip, when no database is reachable):

# Start the test database (PG 16 by default; PG_VERSION=15 selects another).
docker compose -f tests/docker-compose.yml up -d --wait

# Run the whole suite except the Azure-only dogfood (which needs a real Azure DB).
python -m pytest -m "not azure"

# Fast loop — no database needed: default unmarked lane only.
python -m pytest -m "not pg and not integration and not azure"

# Stop the database when done.
docker compose -f tests/docker-compose.yml down -v

Tests are grouped into four lanes via pytest markers:

• default (unmarked) — no database required; runs on every CI push. Covers token budgets, terminology, links, activation precision, licenses, security, manifests/structure, MCP protocol, skill routing, performance budget, and the no-DB structural SQL fence lint.
• pg — SQL fence validation that executes fences against the Docker PostgreSQL (tests/test_sql_syntax.py::test_sql_blocks_parse). Always uses the fixed Docker credentials; fails if no DB is reachable.
• integration — generic end-to-end MCP queries against the Docker PostgreSQL (tests/test_mcp_e2e.py). Always uses the fixed Docker credentials; fails if no DB is reachable.
• azure — the AI-application dogfood (tests/test_ai_app.py). Requires a real Azure Database for PostgreSQL (it asserts isAzure and SHOW azure.extensions), so it cannot use the Docker DB and fails when PGSQL_TEST_CONNECTION_STRING is unset. This is the only lane that reads that env var, and it is an edge case run manually against an Azure server; CI does not run it.

Everything is collected by python -m pytest — there is no separate check runner.

To run the Azure-only dogfood lane, point PGSQL_TEST_CONNECTION_STRING at a real Azure server (with the vector extension allowlisted via azure.extensions):

export PGSQL_TEST_CONNECTION_STRING="host=... port=5432 dbname=... user=... password=... sslmode=require"

python -m pytest -m azure               # AI-application dogfood (real Azure DB)

Manual testing (local install)

Install the plugin from your local checkout to test changes end-to-end before submitting. Re-adding the marketplace picks up edits to SKILL.md, references, and marketplace.json. Run from the repo root:

# GitHub Copilot CLI
copilot plugin marketplace remove postgres-skills --force
copilot plugin marketplace add "$(pwd)"
copilot plugin install postgres-skills@postgres-skills

# Claude Code CLI
claude plugin marketplace remove postgres-skills
claude plugin marketplace add "$(pwd)"
claude plugin install postgres-skills@postgres-skills

# Codex CLI
codex plugin marketplace remove postgres-skills
codex plugin marketplace add "$(pwd)"
codex plugin add postgres-skills@postgres-skills

Running the full eval pipeline

Every skill is continuously evaluated against 300 test challenges across generic PostgreSQL and Azure-specific scenarios. CI runs on manual trigger (workflow_dispatch).

# Run evals (requires Azure OpenAI key)
cd tests/evals
python pipeline.py --provider azure --model gpt-5.4 --concurrency 3

# Run generic-only subset
python pipeline.py --provider azure --model gpt-5.4 --concurrency 3 --challenges challenges/generic_only.yaml

# Dry run (no API calls, validates structure)
python pipeline.py --dry-run

Pull Request Process

1. Fork the repository
2. Create a feature branch
3. Add or modify skill(s)
4. Run the validation checks (see Testing) to validate token budgets and structure
5. Submit a PR with a clear description of what the skill teaches