PostgreSQL Project

Scenario

You maintain a Node.js application backed by PostgreSQL.

The project already has established patterns for:

• database access;
• SQL migrations;
• transactions;
• repository modules.

You want coding agents to work with PostgreSQL safely instead of introducing ad hoc queries or modifying production schema history.

Repository Structure

api/
├── src/
│   ├── services/
│   ├── repositories/
│   │   ├── users.ts
│   │   └── orders.ts
│   └── db/
│       ├── client.ts
│       └── transaction.ts
├── migrations/
│   ├── 001_create_users.sql
│   └── 002_create_orders.sql
├── tests/
└── AGENTS.md

AGENTS.md

# Project Instructions

## PostgreSQL

- Access PostgreSQL through the existing database client.
- Keep application queries in repository/data-access modules.
- Do not open independent database connections when the shared client is appropriate.
- Use parameterized queries for dynamic values.

## Schema Changes

- Create a new migration for schema changes.
- Do not modify already-applied migrations to change current schema behavior.
- Follow the existing migration naming and ordering conventions.

## Queries

- Select only the data required by the operation when practical.
- Preserve existing query and mapping conventions.
- Consider existing indexes when introducing frequently executed lookup patterns.

## Transactions

- Use the existing transaction helper when multiple writes must succeed or fail together.
- Pass the transaction context through repository operations instead of opening unrelated connections.

## Validation

For database changes:

- run relevant repository or integration tests;
- run `pnpm typecheck`;
- run migration validation supported by the repository.

What This Does

This establishes a normal path for database access:

Service
   ↓
Repository
   ↓
Database client
   ↓
PostgreSQL

An agent adding a user query should extend the existing repository layer instead of creating database access directly inside an HTTP handler.

It also establishes migration history as something that should be treated carefully.

What This Does NOT Do

The file does not teach SQL syntax.

It also does not prohibit writing SQL.

If the repository intentionally uses raw SQL, that may be exactly the correct approach.

The important rules concern:

where queries belong
how dynamic values are handled
how schema changes are recorded
how transactions are managed

The file also does not claim that every query needs a new index.

Index decisions should be based on actual access patterns and performance requirements.

Why These Instructions Matter

Consider:

const result = await db.query(
  `SELECT * FROM users WHERE email = '${email}'`,
);

Constructing SQL from untrusted dynamic values can create SQL injection vulnerabilities.

The repository should instead use its parameterized query pattern, for example:

const result = await db.query(
  "SELECT id, email, name FROM users WHERE email = $1",
  [email],
);

Migration history has similar risks.

Suppose:

002_create_orders.sql

has already been applied in production.

Changing that file locally does not necessarily change existing production databases.

A new migration records the next schema transition explicitly.

Key Decisions

Keep persistence ownership clear

Repositories or data-access modules provide a predictable place for SQL and mapping logic.

Parameterize dynamic values

Do not build SQL by concatenating untrusted values.

Treat applied migrations as history

Once migrations have been applied to shared environments, changing old files can create differences between fresh and existing databases.

Use transactions for atomic workflows

If several writes represent one logical operation, partial completion may leave inconsistent state.

Don't optimize blindly

Database guidance should prevent obvious mistakes without encouraging speculative indexes or query rewrites.

When to Use This Pattern

Use this pattern when:

• an application talks directly to PostgreSQL;
• SQL queries live in repository/data-access modules;
• schema changes use migrations;
• some workflows require transactions;
• agents need explicit database safety boundaries.

The exact SQL library may differ, but the ownership and migration principles remain useful.