AGENTS.md Template for PostgreSQL Production Architecture

Overview

Direct answer: This AGENTS.md Template governs the PostgreSQL production architecture workflow and multi-agent orchestration, providing a complete operating manual for a project using AI coding agents. It supports both single-agent and multi-agent collaboration across planning, implementation, review, testing, deployment, and continuous governance of database infrastructure.

This GAENTS.md Template defines the agent roster, responsibilities, supervisor behavior, handoff rules, context/memory rules, tool access, architecture constraints, and escalation paths to ensure reliable, auditable, and secure production operations for PostgreSQL ecosystems.

When to Use This AGENTS.md Template

• Starting a PostgreSQL production deployment with multi-node replication, backups, and failover planning.
• Orchestrating AI-driven changes to schemas, configs, and performance tuning with clear handoffs.
• Establishing tool governance and memory rules to prevent context drift and unsafe automation.
• Setting up role-based access and audit trails for production operations.

Copyable AGENTS.md Template

# AGENTS.md

Project: PostgreSQL Production Architecture - Multi-Agent Orchestration Template

Agent roster and responsibilities:
- Planner: defines architecture goals, constraints, success criteria, and acceptance tests.
- Implementer: applies DDL/DML changes, migrations, tuning scripts, and config updates.
- Reviewer: validates changes, runs SQL tests, and checks replication and backups.
- Tester: executes end-to-end tests, failover simulations, and performance checks.
- DevOps/Platform Engineer: handles deployment pipelines, observability, backups, and disaster recovery runs.
- Security Auditor: ensures access controls, secrets handling, encryption, and compliance.
- Data Engineer: validates data pipelines, ETLs, materialized views, and data integrity.
- Domain Specialist: ensures business requirements and SLAs are met.

Supervisor or orchestrator behavior:
- The Orchestrator coordinates all agents, maintains a central memory store, and validates outputs against the plan. It enforces a single source of truth and triggers escalations when outputs drift or risks exceed thresholds.

Handoff rules between agents:
- Planner -> Implementer: hand off plan, constraints, and success criteria.
- Implementer -> Reviewer: hand off migrations, scripts, and test plans.
- Reviewer -> Tester: hand off validated changes and replication/data-checks.
- Tester -> DevOps: hand off deployment plans and rollback criteria.
- DevOps -> Security Auditor/Domain Specialist: trigger reviews for sensitive changes and SLA alignment.

Context, memory, and source-of-truth rules:
- All decisions, outcomes, and artifacts must be stored in a central memory store accessible to all agents.
- The source-of-truth is the PostgreSQL catalog, backups, and monitoring/logs.

Tool access and permission rules:
- Tools allowed: psql, pg_dump, pg_restore, monitoring dashboards, CI/CD runners, secret vaults.
- Secrets must live in a vault; access restricted to authorized roles only.
- Production actions require approval gates and an auditable trail.

Architecture rules:
- Use multi-node replication with streaming replication and WAL archiving.
- Use synchronous commits where required by SLAs; default asynchronous otherwise.
- Maintain disaster recovery drills and clear failover procedures.

File structure rules:
- migrations/ contains idempotent migration scripts.
- configs/ holds database and server config templates.
- ops/ contains deployment and runbooks.
- docs/ provides architecture decisions and runbooks.

Data, API, or integration rules:
- Use parameterized SQL and prepared statements; avoid string concatenation.
- All external calls should be versioned and auditable.

Validation rules:
- All migrations pass unit and integration tests; replication lag under threshold; backups succeed.
- Security audits pass before production deployment.

Security rules:
- Enforce least privilege, encryption at rest and in transit, and rotation policies for keys/secrets.
- Audit trails for all admin actions.

Testing rules:
- Include unit tests for SQL, integration tests for replication, and end-to-end DR tests.

Deployment rules:
- Canaries and staged rollouts with rollback paths documented.
- Migrate first in non-prod or canary environments before prod.

Human review and escalation rules:
- Any high-risk change or SLA breach triggers human review and approval.

Failure handling and rollback rules:
- Maintain rollback scripts and safe mode for production changes.

Things Agents must not do:
- Do not bypass approvals or gates.
- Do not mutate memory outside approved channels.
- Do not perform production deployments without canary and rollback plans.

Recommended Agent Operating Model

The recommended operating model clarifies agent roles, decision boundaries, and escalation paths for PostgreSQL production architectures. It defines who can decide, what they can decide, and when escalation occurs to human operators or the supervisor.

Recommended Project Structure

postgresql-prod-arch/
├── agents/
│   ├── planner/
│   │   └── plan.md
│   ├── implementer/
│   ├── reviewer/
│   ├── tester/
│   ├── devops/
│   ├── security/
│   └── data-engineer/
├── configs/
├── migrations/
│   ├── 20260520_initial.sql
│   └── 20260521_tuning.sql
├── memory/
├── pipelines/
├── docs/
├── deployments/
└── tests/

Core Operating Principles

• Operate with a single source of truth and auditable decision logs.
• Keep changes idempotent, replayable, and testable.
• Limit automation to well-scoped, reviewable plans with clear exit criteria.
• Enforce least privilege and strict secret management for all tools.
• Encourage modular, observable, and safe migrations and deployments.

Agent Handoff and Collaboration Rules

• Planner to Implementer: pass the plan, success criteria, constraints, and acceptance tests.
• Implementer to Reviewer: pass migration scripts, data checks, and expected post-migration state.
• Reviewer to Tester: pass validation results, replication checks, and backup status.
• Tester to DevOps: pass deployment plan, canary criteria, and rollback plan.
• DevOps to Security Auditor: trigger security review for any credential changes.
• Security Auditor to Domain Specialist: escalate governance concerns for SLA alignment.

Tool Governance and Permission Rules

• Commands: run via approved toolchain; direct shell access is restricted.
• File edits: only through approved workflows; avoid ad-hoc changes.
• APIs: use versioned endpoints with tokens from a vault; rotate credentials regularly.
• Secrets: stored securely; never hard-coded in code.
• Production systems: changes must pass gates and be validated by the Orchestrator.
• External services: access subject to vendor contracts and security review.
• Approval gates: require supervisor approval for high-risk actions.

Code Construction Rules

• Write idempotent migrations; include down scripts for rollback.
• Use parameterized SQL; avoid dynamic SQL unless necessary with strong validation.
• Limit transaction scope to protect long-running transactions; verify commits atomically.
• Isolate schema changes from data changes when possible.
• Document assumptions and expected outcomes in migration notes.

Security and Production Rules

• Enforce least privilege for all roles; separate read/write duties.
• Encrypt data at rest and in transit; enable TLS for DB connections.
• Rotate credentials and keys periodically; store in a secrets vault.
• Audit admin actions and provide traceable logs for all changes.
• Isolate staging, pre-prod, and prod environments with clear promotion gates.

Testing Checklist

• Unit tests for SQL functions and procedures.
• Integration tests for replication, failover, and backups.
• End-to-end tests including schema changes and application data paths.
• Performance tests for query plans and WAL throughput.
• Canary validation before production deployment.

Common Mistakes to Avoid

• Skipping security reviews for credential changes.
• Rushing migrations without adequate testing or rollback plans.
• Allowing drift between memory, state, and actual production data.
• Over-privileging agents or leaking secrets into logs.
• Neglecting disaster recovery drills and backup verification.

Related implementation resources: AI Use Case for Sales Pipeline Reviews and Deal Risk Scoring and AI Use Case for Policy Documents and Internal Question Answering.

FAQ

What is the AGENTS.md Template for PostgreSQL production architecture?

It is a copyable operating manual that defines agent roles, rules, and governance for PostgreSQL in production with multi-agent orchestration.

How does multi-agent orchestration work in this template?

Agents operate in defined roles coordinated by a central Orchestrator, sharing memory state and passing explicit handoffs between stages.

What are the handoff rules between agents?

Handoffs occur at milestones with explicit context, artifacts, and acceptance criteria to minimize drift and ensure accountability.

How is tool access and security enforced?

Access is role-based, secrets are vaulted, and all production actions require approved gates and auditable logs.

What about failure handling and rollback?

There are documented rollback scripts, safe-mode protocols, and escalation paths to human review for high-risk changes.