pgpm-sql-conventions（pgpm SQL conventions 开发）

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

1. Create all three files: deploy/, revert/, verify/
2. Add the correct header to each file (-- Deploy, -- Revert, -- Verify)
3. Add -- requires: declarations in the deploy file
4. Add the change to pgpm.plan with dependencies
5. Use CREATE not CREATE OR REPLACE
6. Do NOT wrap in BEGIN/COMMIT — pgpm handles transactions
7. Use snake_case for all identifiers
8. Check existing files in the module for format conventions