pgpm SQL Conventions

Rules and format for writing SQL migration files in pgpm modules.

When to Apply

Use this skill when:

• Writing new deploy/revert/verify SQL files

• Adding database changes to a pgpm module

• Reviewing SQL migration code for correctness

• Debugging deployment failures related to SQL format

Critical Rules

1. NEVER Use CREATE OR REPLACE

pgpm is deterministic — each change is deployed exactly once and reverted exactly once. Use CREATE , not CREATE OR REPLACE :

-- CORRECT CREATE FUNCTION app.my_function() ...

-- WRONG — never do this in pgpm CREATE OR REPLACE FUNCTION app.my_function() ...

If you need to modify an existing function, create a new change that drops and recreates it, or use the revert/redeploy cycle.

2. NO Transaction Wrapping

Do NOT add BEGIN /COMMIT or BEGIN /ROLLBACK to your SQL files. pgpm handles transactions automatically. Just write the raw SQL:

-- CORRECT — just the SQL -- Deploy schemas/app/tables/users to pg

CREATE TABLE app.users ( id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), email text NOT NULL UNIQUE, name text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() );

-- WRONG — do not wrap in transactions BEGIN; CREATE TABLE app.users ( ... ); COMMIT;

3. Use snake_case for All Identifiers

All SQL identifiers must use snake_case :

-- CORRECT CREATE TABLE app.user_profiles ( user_id uuid NOT NULL, display_name text, created_at timestamptz NOT NULL DEFAULT now() );

-- WRONG CREATE TABLE app.userProfiles ( userId uuid NOT NULL, displayName text, createdAt timestamptz NOT NULL DEFAULT now() );

File Header Format

Every SQL file starts with a header comment declaring its purpose and path.

Deploy Files

-- Deploy schemas/app/tables/users to pg

-- requires: schemas/app/schema

CREATE TABLE app.users ( ... );

Revert Files

-- Revert schemas/app/tables/users from pg

DROP TABLE IF EXISTS app.users;

Verify Files

-- Verify schemas/app/tables/users on pg

SELECT id, email, name, created_at FROM app.users WHERE FALSE;

Header pattern:

• Deploy: -- Deploy <change_path> to pg

• Revert: -- Revert <change_path> from pg

• Verify: -- Verify <change_path> on pg

Always check existing files in the same directory for the exact format used in that module.

Dependency Declarations

Use -- requires: comments after the header to declare dependencies:

-- Deploy schemas/app/tables/user_profiles to pg

-- requires: schemas/app/schema -- requires: schemas/app/tables/users

CREATE TABLE app.user_profiles ( user_id uuid NOT NULL REFERENCES app.users(id), bio text, avatar_url text );

Cross-Module Dependencies

When depending on a change from another module, prefix with the module name:

-- Deploy schemas/app/procedures/get_user to pg

-- requires: schemas/app/schema -- requires: other-module:schemas/shared/tables/users

CREATE FUNCTION app.get_user(user_id uuid) ...

The format is module_name:change_path .

Common Change Types

Schema

-- Deploy schemas/app/schema to pg

CREATE SCHEMA app;

Revert: DROP SCHEMA IF EXISTS app;

Verify: SELECT 1/count(*) FROM information_schema.schemata WHERE schema_name = 'app';

Table

-- Deploy schemas/app/tables/users to pg

-- requires: schemas/app/schema

CREATE TABLE app.users ( id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), email text NOT NULL UNIQUE, name text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() );

Revert: DROP TABLE IF EXISTS app.users;

Verify: SELECT id, email, name, created_at FROM app.users WHERE FALSE;

Function / Procedure

-- Deploy schemas/app/procedures/authenticate to pg

-- requires: schemas/app/schema -- requires: schemas/app/tables/users

CREATE FUNCTION app.authenticate(email text, password text) RETURNS app.users AS $$ DECLARE result app.users; BEGIN SELECT * INTO result FROM app.users u WHERE u.email = authenticate.email;

IF result IS NULL THEN RAISE EXCEPTION 'Invalid credentials'; END IF;

RETURN result; END; $$ LANGUAGE plpgsql STRICT SECURITY DEFINER;

Revert: DROP FUNCTION IF EXISTS app.authenticate(text, text);

Verify: SELECT has_function_privilege('app.authenticate(text, text)', 'execute');

Index

-- Deploy schemas/app/tables/users/indexes/users_email_idx to pg

-- requires: schemas/app/tables/users

CREATE INDEX users_email_idx ON app.users (email);

Revert: DROP INDEX IF EXISTS app.users_email_idx;

Grant / RLS Policy

-- Deploy schemas/app/tables/users/policies/users_select_policy to pg

-- requires: schemas/app/tables/users

ALTER TABLE app.users ENABLE ROW LEVEL SECURITY;

CREATE POLICY users_select_policy ON app.users FOR SELECT TO authenticated USING (id = current_setting('auth.user_id')::uuid);

Revert: DROP POLICY IF EXISTS users_select_policy ON app.users;

View (PostgreSQL 17+)

-- Deploy schemas/app/views/active_users to pg

-- requires: schemas/app/tables/users

CREATE VIEW app.active_users WITH (security_invoker = true) AS SELECT id, email, name FROM app.users WHERE active = true;

Note: security_invoker requires PostgreSQL 17+.

Trigger

-- Deploy schemas/app/tables/users/triggers/update_timestamp to pg

-- requires: schemas/app/tables/users

CREATE FUNCTION app.tg_update_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at := now(); RETURN NEW; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER update_timestamp BEFORE UPDATE ON app.users FOR EACH ROW EXECUTE FUNCTION app.tg_update_timestamp();

Nested Path Organization

Changes are organized in nested directory paths that mirror the database structure:

deploy/ schemas/ app/ schema.sql tables/ users.sql posts.sql posts/ indexes/ posts_author_idx.sql policies/ posts_select_policy.sql procedures/ authenticate.sql views/ active_users.sql

The path in the plan file matches the directory path:

schemas/app/schema [deps] timestamp author <email> # comment schemas/app/tables/users [schemas/app/schema] timestamp author <email> # comment

Checklist for New Changes

• Create all three files: deploy/ , revert/ , verify/

• Add the correct header to each file (-- Deploy , -- Revert , -- Verify )

• Add -- requires: declarations in the deploy file

• Add the change to pgpm.plan with dependencies

• Use CREATE not CREATE OR REPLACE

• Do NOT wrap in BEGIN /COMMIT — pgpm handles transactions

• Use snake_case for all identifiers

• Check existing files in the module for format conventions