Pgpm

pgpm (PostgreSQL Package Manager)

pgpm provides deterministic, plan-driven database migrations with dependency management and modular packaging. It brings npm-style modularity to PostgreSQL database development — every change is deployed exactly once and reverted exactly once.

When to Apply

Use this skill when:

• Creating projects: Setting up workspaces, initializing modules
• Writing changes: Adding tables, functions, triggers, indexes, RLS policies
• Managing dependencies: Within-module and cross-module references, .control files
• Deploying: Running deploy/verify/revert, tagging releases, checking status
• Testing: Writing PostgreSQL integration tests with pgsql-test
• Configuring: Docker setup, environment variables, connection config
• Managing extensions: Adding PostgreSQL extensions or pgpm modules
• Publishing: Bundling and publishing @pgpm/* modules to npm
• Troubleshooting: Connection issues, deployment failures, testing problems

Quick Start

# 1. Install pgpm
npm install -g pgpm

# 2. Start a local PostgreSQL container
pgpm docker start
eval "$(pgpm env)"

# 3. Create a workspace
pgpm init workspace
# Enter workspace name when prompted
cd my-database-project
pnpm install

# 4. Create a module
pgpm init
# Enter module name (e.g., "pets") and select extensions

# 5. Add a change
cd packages/pets
pgpm add schemas/pets
pgpm add schemas/pets/tables/pets --requires schemas/pets

# 6. Write your SQL (see "Three-File Pattern" below)

# 7. Deploy
pgpm deploy --createdb --database mydb

Core Concepts

Three-File Pattern

Every database change consists of three files:

pgpm.plan

The plan file controls deployment order. Each line:

change_name [dep1 dep2] 2026-01-25T00:00:00Z author <email@example.org>

Dependencies [...] must come immediately after the change name, before the timestamp.

.control File

Each module has a .control file declaring its name and PostgreSQL extension dependencies:

comment = 'My database module'
default_version = '0.0.1'
requires = 'uuid-ossp,plpgsql'

The requires field uses control file names (e.g., pgpm-base32), NOT npm names (e.g., @pgpm/base32). See references/module-naming.md for details.

Workspace vs Module

• Workspace = pnpm monorepo containing one or more modules (pgpm init workspace)
• Module = individual database package with its own .control, pgpm.plan, and deploy/revert/verify directories (pgpm init)

Critical Rules

1. NEVER Use CREATE OR REPLACE

pgpm is deterministic. Each change deploys exactly once. To modify an existing object, create a new change that drops and recreates it.

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

-- WRONG
CREATE OR REPLACE FUNCTION app.my_function() ...

2. NO Transaction Wrapping

Do NOT add BEGIN/COMMIT to SQL files. pgpm handles transactions automatically.

-- CORRECT — just the raw SQL
CREATE TABLE app.users ( ... );

-- WRONG
BEGIN;
CREATE TABLE app.users ( ... );
COMMIT;

3. NEVER Run CREATE EXTENSION Directly

pgpm handles extension creation during deploy. Declare extensions in your .control file's requires field instead.

Key Commands Quick Reference

Essential Development Workflow

# Start PostgreSQL and load environment
pgpm docker start
eval "$(pgpm env)"

# Bootstrap admin users (first time)
pgpm admin-users bootstrap --yes
pgpm admin-users add --test --yes

# Add a new change
pgpm add schemas/app/tables/orders --requires schemas/app

# Edit deploy/revert/verify SQL files

# Deploy and verify
pgpm deploy --createdb --database mydb
pgpm verify

# Run tests
pnpm test

# Tag a release
pgpm tag v1.0.0

Common Workflows

Adding a Table

# Add the change
pgpm add schemas/app/tables/users --requires schemas/app

-- deploy/schemas/app/tables/users.sql
-- Deploy schemas/app/tables/users to pg
-- requires: schemas/app

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/schemas/app/tables/users.sql
-- Revert schemas/app/tables/users from pg

DROP TABLE IF EXISTS app.users;

-- verify/schemas/app/tables/users.sql
-- Verify schemas/app/tables/users on pg

DO $$
BEGIN
  ASSERT (SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_schema = 'app' AND table_name = 'users'
  )), 'Table app.users does not exist';
END $$;

Writing Tests (with pgsql-test)

import { getConnections } from 'pgsql-test';
import * as seed from 'pgsql-test/seed';

let db, teardown;

beforeAll(async () => {
  ({ db, teardown } = await getConnections({}, [
    seed.pgpm({ database: 'mydb' })
  ]));
});
afterAll(() => teardown());
beforeEach(() => db.beforeEach());
afterEach(() => db.afterEach());

it('creates a user', async () => {
  const result = await db.query(`
    INSERT INTO app.users (email, name)
    VALUES ('test@example.com', 'Test User')
    RETURNING *
  `);
  expect(result.rows[0].email).toBe('test@example.com');
});

CI/CD Full-Cycle Validation

pgpm test-packages --full-cycle

This proves: deploy → verify → revert → redeploy works for every module.

Fixing a Broken Deploy

# Check status
pgpm migrate status

# Revert the bad change
pgpm revert --to <last-good-change>

# Fix the SQL, then redeploy
pgpm deploy

Troubleshooting Quick Reference

See references/troubleshooting.md for detailed solutions.

Reference Guide

Consult these reference files for detailed documentation on specific topics:

Project Scaffolding

Database Operations (from constructive-db)

Cross-References

Related skills:

• constructive-testing — PostgreSQL testing patterns (RLS, seeding, snapshots, JWT context)
• constructive-setup — Monorepo setup and local development environment
• constructive-cli — Generated CLI commands and scaffolding

Authoring Database Changes with PGPM

Create safe, reversible database changes using pgpm's three-file pattern. Every change has deploy, revert, and verify scripts.

When to Apply

Use this skill when:

• Adding tables, functions, triggers, or indexes
• Creating database migrations
• Modifying existing schema
• Organizing database changes in a pgpm module

The Three-File Pattern

Every database change consists of three files:

Adding a Change

pgpm add schemas/pets/tables/pets --requires schemas/pets

This creates:

deploy/schemas/pets/tables/pets.sql
revert/schemas/pets/tables/pets.sql
verify/schemas/pets/tables/pets.sql

And updates pgpm.plan:

schemas/pets/tables/pets [schemas/pets] 2025-11-14T00:00:00Z Author <author@example.com>

Writing Deploy Scripts

Deploy scripts create database objects. Use CREATE, not CREATE OR REPLACE (pgpm is deterministic).

deploy/schemas/pets/tables/pets.sql:

-- Deploy: schemas/pets/tables/pets
-- requires: schemas/pets

CREATE TABLE pets.pets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  breed TEXT,
  owner_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Important: Never use CREATE OR REPLACE unless absolutely necessary. pgpm tracks what's deployed and ensures idempotency through its migration system.

Writing Revert Scripts

Revert scripts undo the deploy. Must leave database in pre-deploy state.

revert/schemas/pets/tables/pets.sql:

-- Revert: schemas/pets/tables/pets

DROP TABLE IF EXISTS pets.pets;

Writing Verify Scripts

Verify scripts confirm deployment succeeded. Use DO blocks that raise exceptions on failure.

verify/schemas/pets/tables/pets.sql:

-- Verify: schemas/pets/tables/pets

DO $$
BEGIN
  PERFORM 1 FROM pg_tables
  WHERE schemaname = 'pets' AND tablename = 'pets';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Table pets.pets does not exist';
  END IF;
END $$;

Nested Paths

Organize changes hierarchically using nested paths:

schemas/
└── app/
    ├── schema.sql
    ├── tables/
    │   └── users/
    │       ├── table.sql
    │       └── indexes/
    │           └── email.sql
    ├── functions/
    │   └── create_user.sql
    └── triggers/
        └── updated_at.sql

Add changes with full paths:

pgpm add schemas/app/schema
pgpm add schemas/app/tables/users/table --requires schemas/app/schema
pgpm add schemas/app/tables/users/indexes/email --requires schemas/app/tables/users/table
pgpm add schemas/app/functions/create_user --requires schemas/app/tables/users/table

Key insight: Deployment order follows the plan file, not directory structure. Nested paths are for organization only.

Plan File Format

The pgpm.plan file tracks all changes:

%syntax-version=1.0.0
%project=pets
%uri=pets

schemas/pets 2025-11-14T00:00:00Z Author <author@example.com>
schemas/pets/tables/pets [schemas/pets] 2025-11-14T00:00:00Z Author <author@example.com>
schemas/pets/tables/pets/indexes/name [schemas/pets/tables/pets] 2025-11-14T00:00:00Z Author <author@example.com>

Format: change_name [dependencies] timestamp author <email> # optional note

Two Workflows

Incremental (Development)

Add changes one at a time:

pgpm add schemas/pets --requires uuid-ossp
pgpm add schemas/pets/tables/pets --requires schemas/pets

Plan file updates automatically with each pgpm add.

Pre-Production (Batch)

Write all SQL files first, then generate plan:

# Write deploy/revert/verify files manually
# Then generate plan from requires comments:
pgpm plan

pgpm plan reads -- requires: comments from deploy files and generates the plan.

Common Change Types

Schema

pgpm add schemas/app

-- deploy/schemas/app.sql
CREATE SCHEMA app;

-- revert/schemas/app.sql
DROP SCHEMA IF EXISTS app CASCADE;

-- verify/schemas/app.sql
DO $$ BEGIN
  PERFORM 1 FROM information_schema.schemata WHERE schema_name = 'app';
  IF NOT FOUND THEN RAISE EXCEPTION 'Schema app does not exist'; END IF;
END $$;

Table

pgpm add schemas/app/tables/users --requires schemas/app

-- deploy/schemas/app/tables/users.sql
CREATE TABLE app.users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- revert/schemas/app/tables/users.sql
DROP TABLE IF EXISTS app.users;

-- verify/schemas/app/tables/users.sql
DO $$ BEGIN
  PERFORM 1 FROM pg_tables WHERE schemaname = 'app' AND tablename = 'users';
  IF NOT FOUND THEN RAISE EXCEPTION 'Table app.users does not exist'; END IF;
END $$;

Function

pgpm add schemas/app/functions/get_user --requires schemas/app/tables/users

-- deploy/schemas/app/functions/get_user.sql
CREATE FUNCTION app.get_user(user_id UUID)
RETURNS app.users AS $$
  SELECT * FROM app.users WHERE id = user_id;
$$ LANGUAGE sql STABLE;

-- revert/schemas/app/functions/get_user.sql
DROP FUNCTION IF EXISTS app.get_user(UUID);

-- verify/schemas/app/functions/get_user.sql
DO $$ BEGIN
  PERFORM 1 FROM pg_proc WHERE proname = 'get_user';
  IF NOT FOUND THEN RAISE EXCEPTION 'Function get_user does not exist'; END IF;
END $$;

Index

pgpm add schemas/app/tables/users/indexes/email --requires schemas/app/tables/users

-- deploy/schemas/app/tables/users/indexes/email.sql
CREATE INDEX idx_users_email ON app.users(email);

-- revert/schemas/app/tables/users/indexes/email.sql
DROP INDEX IF EXISTS app.idx_users_email;

-- verify/schemas/app/tables/users/indexes/email.sql
DO $$ BEGIN
  PERFORM 1 FROM pg_indexes WHERE indexname = 'idx_users_email';
  IF NOT FOUND THEN RAISE EXCEPTION 'Index idx_users_email does not exist'; END IF;
END $$;

Deploy and Verify

# Deploy to database
pgpm deploy --database myapp_dev --createdb --yes

# Verify deployment
pgpm verify --database myapp_dev

References

• Related reference: references/workspace.md for workspace setup
• Related reference: references/dependencies.md for cross-module dependencies
• Related reference: references/testing.md for testing database changes

Configure GitHub Actions workflows for PostgreSQL database testing, PGPM migrations, and CI/CD pipelines in Constructive projects.

When to Apply

Use this skill when:

• Setting up CI/CD for a PGPM-based project
• Configuring PostgreSQL service containers in GitHub Actions
• Running database tests with pgsql-test in CI
• Generating SDKs or types from database schemas in CI
• Building and publishing Docker images for PostgreSQL

Core Workflow Pattern

Every Constructive CI workflow follows this pattern:

1. Spin up PostgreSQL service container with health checks 2. Install pnpm and Node.js with caching 3. Cache and install pgpm CLI globally 4. Build the workspace with pnpm -r build 5. Bootstrap database users with pgpm admin-users 6. Run tests per package

PostgreSQL Service Container

Use the Constructive PostgreSQL image with extensions pre-installed:

services:
  pg_db:
    image: ghcr.io/constructive-io/docker/postgres-plus:17
    env:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
    options: >-
      --health-cmd "pg_isready -U postgres"
      --health-interval 10s
      --health-timeout 5s
      --health-retries 5
    ports:
      - 5432:5432

For simpler setups without custom extensions:

services:
  pg_db:
    image: docker.io/constructiveio/postgres-plus:18
    env:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
    options: >-
      --health-cmd "pg_isready -U postgres"
      --health-interval 10s
      --health-timeout 5s
      --health-retries 5
    ports:
      - 5432:5432

Environment Variables

Standard PostgreSQL environment variables for tests:

env:
  PGHOST: localhost
  PGPORT: 5432
  PGUSER: postgres
  PGPASSWORD: password

For MinIO/S3 testing (uploads, storage):

env:
  MINIO_ENDPOINT: http://localhost:9000
  AWS_ACCESS_KEY: minioadmin
  AWS_SECRET_KEY: minioadmin
  AWS_REGION: us-east-1
  BUCKET_NAME: test-bucket

PGPM CLI Caching

Cache the pgpm CLI to speed up workflows:

env:
  PGPM_VERSION: '2.7.9'

steps:
  - name: Cache pgpm CLI
    uses: actions/cache@v4
    with:
      path: ~/.npm
      key: pgpm-${{ runner.os }}-${{ env.PGPM_VERSION }}

  - name: Install pgpm CLI globally
    run: npm install -g pgpm@${{ env.PGPM_VERSION }}

Database User Bootstrap

Before running tests, bootstrap the database users:

- name: Seed pg and app_user
  run: |
    pgpm admin-users bootstrap --yes
    pgpm admin-users add --test --yes

This creates:

• The app_user role for RLS testing
• Test-specific roles and permissions

Complete Test Workflow

Full workflow for running tests across multiple packages:

name: CI tests
on:
  push:
    branches: [main]
  pull_request:
    branches: [main]
  workflow_dispatch:

concurrency:
  group: ${{ github.workflow }}-${{ github.ref }}-tests
  cancel-in-progress: true

env:
  PGPM_VERSION: '2.7.9'

jobs:
  test:
    runs-on: ubuntu-latest
    
    strategy:
      fail-fast: false
      matrix:
        package:
          - packages/my-package
          - packages/another-package

    env:
      PGHOST: localhost
      PGPORT: 5432
      PGUSER: postgres
      PGPASSWORD: password

    services:
      pg_db:
        image: ghcr.io/constructive-io/docker/postgres-plus:17
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: password
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - name: Configure Git
        run: |
          git config --global user.name "CI Test User"
          git config --global user.email "ci@example.com"

      - name: Checkout
        uses: actions/checkout@v4

      - name: Setup pnpm
        uses: pnpm/action-setup@v2
        with:
          version: 10

      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'pnpm'

      - name: Install dependencies
        run: pnpm install

      - name: Cache pgpm CLI
        uses: actions/cache@v4
        with:
          path: ~/.npm
          key: pgpm-${{ runner.os }}-${{ env.PGPM_VERSION }}

      - name: Install pgpm CLI globally
        run: npm install -g pgpm@${{ env.PGPM_VERSION }}

      - name: Build
        run: pnpm -r build

      - name: Seed pg and app_user
        run: |
          pgpm admin-users bootstrap --yes
          pgpm admin-users add --test --yes

      - name: Test ${{ matrix.package }}
        run: cd ./${{ matrix.package }} && pnpm test

Integration Test Workflow

For running pgpm's built-in integration tests:

- name: Run Integration Tests
  run: pgpm test-packages

This runs all package tests defined in the pgpm workspace.

SDK Generation Workflow

Generate typed SDKs from database schemas:

name: generate-sdk
on:
  workflow_dispatch:
    inputs:
      commit_changes:
        description: 'Commit and push generated SDK changes'
        required: false
        default: 'false'
        type: boolean

jobs:
  generate-sdk:
    runs-on: ubuntu-latest
    
    # ... services and setup steps ...

    steps:
      # ... checkout, pnpm, node, pgpm setup ...

      - name: Build
        run: pnpm -r build

      - name: Seed pg and app_user
        run: |
          pgpm admin-users bootstrap --yes
          pgpm admin-users add --test --yes

      - name: Generate SDK
        run: |
          cd sdk/my-sdk
          pnpm run generate

      - name: Check for changes
        id: check_changes
        run: |
          if git diff --quiet sdk/my-sdk/src/generated; then
            echo "has_changes=false" >> $GITHUB_OUTPUT
          else
            echo "has_changes=true" >> $GITHUB_OUTPUT
          fi

      - name: Commit and push changes
        if: ${{ inputs.commit_changes == 'true' && steps.check_changes.outputs.has_changes == 'true' }}
        run: |
          git add sdk/my-sdk/src/generated
          git commit -m "chore: regenerate SDK types"
          git push

      - name: Upload generated SDK as artifact
        if: ${{ steps.check_changes.outputs.has_changes == 'true' }}
        uses: actions/upload-artifact@v4
        with:
          name: generated-sdk
          path: sdk/my-sdk/src/generated
          retention-days: 7

Test Sharding

For large test suites, split tests across parallel jobs:

strategy:
  fail-fast: false
  matrix:
    package: [packages/core]
    test_pattern: ['']
    include:
      - package: packages/large-package
        test_pattern: 'auth|rls'
        shard_name: 'large-package-auth-rls'
      - package: packages/large-package
        test_pattern: 'permissions|orgs'
        shard_name: 'large-package-permissions-orgs'

steps:
  - name: Test ${{ matrix.package }}${{ matrix.shard_name && format(' ({0})', matrix.shard_name) || '' }}
    shell: bash
    run: |
      cd ./${{ matrix.package }}
      if [ -n "${{ matrix.test_pattern }}" ]; then
        pnpm test -- "${{ matrix.test_pattern }}"
      else
        pnpm test
      fi

MinIO Service Container

For testing uploads and S3-compatible storage:

services:
  minio_cdn:
    image: minio/minio:edge-cicd
    env:
      MINIO_ROOT_USER: minioadmin
      MINIO_ROOT_PASSWORD: minioadmin
    ports:
      - 9000:9000
      - 9001:9001
    options: >-
      --health-cmd "curl -f http://localhost:9000/minio/health/live || exit 1"
      --health-interval 10s
      --health-timeout 5s
      --health-retries 5

Concurrency Control

Prevent duplicate workflow runs:

concurrency:
  group: ${{ github.workflow }}-${{ github.ref }}-tests
  cancel-in-progress: true

Docker Build Workflow

Build and push PostgreSQL images:

name: Docker
on:
  workflow_dispatch:
    inputs:
      process:
        description: 'Process to build'
        type: choice
        options: [pgvector, postgis, pgvector-postgis]

jobs:
  build-push:
    runs-on: ubuntu-latest
    permissions:
      contents: read
      packages: write

    env:
      REPO: ghcr.io/${{ github.repository_owner }}
      PLATFORMS: linux/amd64,linux/arm64

    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Set up QEMU
        uses: docker/setup-qemu-action@v3

      - name: Set up Docker Buildx
        uses: docker/setup-buildx-action@v3

      - name: Login to GHCR
        uses: docker/login-action@v3
        with:
          registry: ghcr.io
          username: ${{ github.actor }}
          password: ${{ secrets.GITHUB_TOKEN }}

      - name: Build and push
        run: |
          make \
            PROCESS=${{ inputs.process }} \
            REPO_NAME=$REPO \
            PLATFORMS="$PLATFORMS" \
            build-push-process

Per-Package Environment Variables

Pass package-specific environment variables:

strategy:
  matrix:
    include:
      - package: packages/client
        env:
          TEST_DATABASE_URL: postgres://postgres:password@localhost:5432/postgres
      - package: uploads/s3-streamer
        env:
          BUCKET_NAME: test-bucket

steps:
  - name: Test ${{ matrix.package }}
    run: cd ./${{ matrix.package }} && pnpm test
    env: ${{ matrix.env }}

Best Practices

1. Always use health checks — Ensure PostgreSQL is ready before tests run 2. Cache pgpm CLI — Speeds up workflow execution significantly 3. Use concurrency control — Prevent duplicate runs on rapid pushes 4. Configure Git — Required for tests that use git operations 5. Use matrix strategy — Run tests in parallel across packages 6. Bootstrap users before tests — pgpm admin-users creates required roles 7. Use fail-fast: false — Let all tests complete even if some fail 8. Pin pgpm version — Ensure consistent behavior across runs

References

• Related skill: pgsql-test for database testing framework
• Related skill: pgpm (references/workspace.md) for PGPM project setup
• Related skill: pnpm-workspace for PNPM monorepo configuration
• GitHub Actions documentation
• pnpm/action-setup

pgpm CLI Reference

Complete reference for the pgpm (PostgreSQL Package Manager) command-line interface. pgpm provides deterministic, plan-driven database migrations with dependency management.

When to Apply

Use this skill when:

• Deploying database changes
• Managing database migrations
• Installing or upgrading pgpm modules
• Testing pgpm packages in CI/CD
• Setting up local PostgreSQL development

Quick Start

# Install pgpm globally
npm install -g pgpm

# Ensure PostgreSQL is running and env vars are loaded
# See references/docker.md and references/env.md for setup

# Create workspace and module
pgpm init workspace
cd my-app
pgpm init
cd packages/your-module

# Deploy to database
pgpm deploy --createdb --database mydb

Core Commands

Database Operations

pgpm deploy — Deploy database changes and migrations

# Deploy to current database (from PGDATABASE)
pgpm deploy

# Create database if missing
pgpm deploy --createdb

# Deploy to specific database
pgpm deploy --database mydb

# Deploy specific package to a tag
pgpm deploy --package mypackage --to @v1.0.0

⚠️ WARNING: `--fast` flag (use with extreme caution)

>

```bash

pgpm deploy --fast --no-tx

```

>

--fast is NOT idempotent — it is meant to be run once only for

quick testing on a fresh database. It skips dependency-tracking checks, so

if your package shares dependencies with anything already deployed (e.g.

pgpm-verify), it will blindly attempt to re-deploy them, causing errors.

>

Only use `--fast` on a throwaway database that has nothing else deployed.

For all other cases, use the standard pgpm deploy command.

pgpm verify — Verify database state matches expected migrations

pgpm verify
pgpm verify --package mypackage

pgpm revert — Safely revert database changes

pgpm revert
pgpm revert --to @v1.0.0

Migration Management

pgpm migrate — Comprehensive migration management

# Initialize migration tracking
pgpm migrate init

# Check migration status
pgpm migrate status

# List all changes
pgpm migrate list

# Show change dependencies
pgpm migrate deps

Module Management

pgpm install — Install pgpm modules as dependencies

# Install single package
pgpm install @pgpm/faker

# Install multiple packages
pgpm install @pgpm/base32 @pgpm/faker

pgpm upgrade-modules — Upgrade installed modules to latest versions

# Interactive selection
pgpm upgrade-modules

# Upgrade all without prompting
pgpm upgrade-modules --all

# Preview without changes
pgpm upgrade-modules --dry-run

# Upgrade specific modules
pgpm upgrade-modules --modules @pgpm/base32,@pgpm/faker

# Upgrade across entire workspace
pgpm upgrade-modules --workspace --all

pgpm extension — Interactively manage module dependencies

pgpm extension

Workspace Initialization

pgpm init — Initialize new module or workspace

# Create new workspace
pgpm init workspace

# Create new module (inside workspace)
pgpm init

# Use full template path (recommended)
pgpm init --template pnpm/module
pgpm init -t pgpm/workspace

# Create workspace + module in one command
pgpm init -w
pgpm init --template pnpm/module -w

# Use custom template repository
pgpm init --repo https://github.com/org/templates.git --template my-template

Change Management

pgpm add — Add a new database change

pgpm add my_change

This creates three files in sql/:

• deploy/my_change.sql — Deploy script
• revert/my_change.sql — Revert script
• verify/my_change.sql — Verify script

pgpm remove — Remove a database change

pgpm remove my_change

pgpm rename — Rename a database change

pgpm rename old_name new_name

Tagging and Versioning

pgpm tag — Version your changes with tags

# Tag latest change
pgpm tag v1.0.0

# Tag with comment
pgpm tag v1.0.0 --comment "Initial release"

# Tag specific change
pgpm tag v1.1.0 --package mypackage --changeName my-change

Packaging and Distribution

pgpm plan — Generate deployment plans

pgpm plan

pgpm package — Package module for distribution

pgpm package
pgpm package --no-plan

Testing

pgpm test-packages — Run integration tests on all modules in workspace

# Deploy only
pgpm test-packages

# Full deploy/verify/revert/deploy cycle
pgpm test-packages --full-cycle

# Continue after failures
pgpm test-packages --continue-on-fail

# Exclude specific modules
pgpm test-packages --exclude legacy-module

# Combine options
pgpm test-packages --full-cycle --continue-on-fail --exclude broken-module

Docker and Environment

pgpm docker — Manage local PostgreSQL container

pgpm docker start
pgpm docker stop

pgpm env — Print PostgreSQL environment variables

# Standard PostgreSQL
eval "$(pgpm env)"

# Supabase local development
eval "$(pgpm env --supabase)"

Admin Users

pgpm admin-users — Manage database admin users

# Bootstrap admin users from pgpm.json roles config
pgpm admin-users bootstrap

# Add specific user
pgpm admin-users add myuser

# Remove user
pgpm admin-users remove myuser

Utilities

pgpm dump — Dump database to SQL file

# Dump to timestamped file
pgpm dump --database mydb

# Dump to specific file
pgpm dump --database mydb --out ./backup.sql

# Dump with pruning (for test fixtures)
pgpm dump --database mydb --database-id <uuid>

pgpm kill — Clean up database connections

# Kill connections and drop databases
pgpm kill

# Only kill connections
pgpm kill --no-drop

pgpm clear — Clear database state

pgpm clear

pgpm export — Export migrations from existing databases

pgpm export

pgpm analyze — Analyze database structure

pgpm analyze

Cache and Updates

pgpm cache clean — Clear cached template repos

pgpm cache clean

pgpm update — Install latest pgpm version

pgpm update

Environment Variables

pgpm uses standard PostgreSQL environment variables:

Quick setup with eval "$(pgpm env)" or manual export.

Global Options

Most commands support:

Common Workflows

Starting a New Project

pgpm init workspace
cd my-app
pgpm init
cd packages/new-module
pgpm add some_change
# Edit sql/deploy/some_change.sql
pgpm deploy --createdb

Installing and Using a Module

cd packages/your-module
pgpm install @pgpm/faker
pgpm deploy --createdb --database mydb
psql -d mydb -c "SELECT faker.city('MI');"

CI/CD Testing

# Bootstrap admin users
pgpm admin-users bootstrap

# Test all packages
pgpm test-packages --full-cycle --continue-on-fail

References

• Related reference: references/workspace.md for workspace structure
• Related reference: references/changes.md for authoring changes
• Related reference: references/dependencies.md for module dependencies
• Related skill: github-workflows-pgpm for CI/CD workflows

Managing PGPM Dependencies

Handle dependencies between database changes and across modules in pgpm workspaces.

When to Apply

Use this skill when:

• Adding dependencies between database changes
• Referencing objects from other modules
• Managing cross-module dependencies
• Resolving dependency order issues

Dependency Types

Within-Module Dependencies

Changes within the same module reference each other by path:

-- deploy/schemas/pets/tables/pets.sql
-- requires: schemas/pets

CREATE TABLE pets.pets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL
);

Add with --requires:

pgpm add schemas/pets/tables/pets --requires schemas/pets

Cross-Module Dependencies

Reference changes from other modules using module:path syntax:

-- deploy/schemas/app/tables/user_pets.sql
-- requires: schemas/app/tables/users
-- requires: pets:schemas/pets/tables/pets

CREATE TABLE app.user_pets (
  user_id UUID REFERENCES app.users(id),
  pet_id UUID REFERENCES pets.pets(id),
  PRIMARY KEY (user_id, pet_id)
);

The pets:schemas/pets/tables/pets syntax means:

• pets = module name (from .control file)
• schemas/pets/tables/pets = change path within that module

The .control File

Module metadata and extension dependencies live in the .control file:

# pets.control
comment = 'Pet management module'
default_version = '0.0.1'
requires = 'uuid-ossp,plpgsql'

Adding Extension Dependencies

When your module needs PostgreSQL extensions:

# Interactive mode
pgpm extension

# Or edit .control directly
requires = 'uuid-ossp,plpgsql,pgcrypto'

Dependency Resolution

pgpm resolves dependencies recursively:

1. Reads pgpm.plan for change order 2. Parses -- requires: comments 3. Resolves cross-module references 4. Deploys in correct topological order

Example deployment order:

1. uuid-ossp (extension)
2. plpgsql (extension)
3. schemas/pets (schema)
4. schemas/pets/tables/pets (table)
5. schemas/app (schema)
6. schemas/app/tables/users (table)
7. schemas/app/tables/user_pets (references both)

Common Patterns

Schema Before Tables

pgpm add schemas/app
pgpm add schemas/app/tables/users --requires schemas/app
pgpm add schemas/app/tables/posts --requires schemas/app/tables/users

Functions After Tables

pgpm add schemas/app/functions/create_user --requires schemas/app/tables/users

Triggers After Functions

pgpm add schemas/app/triggers/user_updated --requires schemas/app/functions/update_timestamp

Cross-Module Reference

Module A (users):

pgpm add schemas/users/tables/users

Module B (posts):

pgpm add schemas/posts/tables/posts --requires users:schemas/users/tables/users

Viewing Dependencies

Check what a change depends on:

# View plan file
cat pgpm.plan

Plan shows dependencies in brackets:

schemas/app/tables/user_pets [schemas/app/tables/users pets:schemas/pets/tables/pets] 2025-11-14T00:00:00Z Author <author@example.com>

Circular Dependencies

pgpm prevents circular dependencies. If you see:

Error: Circular dependency detected

Refactor to break the cycle: 1. Extract shared objects to a base module 2. Have both modules depend on the base 3. Remove direct cross-references

Before (circular):

module-a depends on module-b
module-b depends on module-a

After (resolved):

module-base (shared objects)
module-a depends on module-base
module-b depends on module-base

Deploying with Dependencies

Deploy resolves all dependencies automatically:

# Deploy single module (pulls in dependencies)
pgpm deploy --database myapp_dev --createdb --yes

# Deploy specific module in workspace
cd packages/posts
pgpm deploy --database myapp_dev --yes

Troubleshooting

Best Practices

1. Explicit dependencies: Always declare what you need 2. Minimal dependencies: Only require what's directly used 3. Consistent naming: Use same paths in requires and plan 4. Test deployments: Verify order with fresh database

References

• Related reference: references/workspace.md for workspace setup
• Related reference: references/changes.md for authoring changes
• Related reference: references/testing.md for testing modules

pgpm Deploy Lifecycle

The complete deploy → verify → revert lifecycle for pgpm database modules.

When to Apply

Use this skill when:

• Deploying database changes with pgpm deploy
• Reverting deployments with pgpm revert
• Verifying deployed state with pgpm verify
• Tagging deployment points with pgpm tag
• Checking deployment status with pgpm migrate status
• Running full-cycle tests with pgpm test-packages

Core Concept

pgpm deployments are deterministic and plan-driven. Every change is tracked in pgpm.plan, and each change has exactly three scripts:

• deploy/ — applies the change
• verify/ — confirms it was applied correctly
• revert/ — undoes the change

pgpm handles transactions automatically — you just write the SQL.

Deploy

Basic Deploy

# Deploy all pending changes for the current module
pgpm deploy

# Deploy with database creation (if database doesn't exist)
pgpm deploy --createdb

# Deploy a specific module by name
pgpm deploy my-module

# Deploy all modules in the workspace
pgpm deploy --workspace --all

What Happens During Deploy

1. Dependency resolution — reads .control file, resolves all required extensions and pgpm modules 2. Extension creation — native Postgres extensions get CREATE EXTENSION IF NOT EXISTS 3. Module dependency deploy — pgpm modules from extensions/ are deployed first (topological order) 4. Plan execution — each change in pgpm.plan is executed in order:

• Checks if already deployed (via tracking schema)
• Runs the deploy script
• Records the change in the tracking schema

5. Automatic verification — after deploy, verify scripts run to confirm state

Deploy to a Tag

# Deploy only up to a specific tag
pgpm deploy --to @v1.0.0

Deploy Options

Verify

Verify checks that deployed changes are actually in the expected state.

# Verify all deployed changes
pgpm verify

# Verify a specific module
pgpm verify my-module

What Happens During Verify

For each deployed change, pgpm runs the corresponding verify/ script. Verify scripts typically use SELECT statements that will fail if the expected objects don't exist:

-- verify/schemas/app/tables/users.sql
SELECT id, email, name, created_at
FROM app.users
WHERE FALSE;

If any verify script fails, pgpm reports which changes are in a bad state.

Revert

Revert undoes deployed changes in reverse order.

# Revert the last deployed change
pgpm revert

# Revert to a specific tag
pgpm revert --to @v1.0.0

# Revert all changes
pgpm revert --all

# Revert with confirmation skip
pgpm revert --yes

What Happens During Revert

1. Changes are reverted in reverse plan order (last deployed = first reverted) 2. Each revert script runs (e.g., DROP TABLE, DROP FUNCTION) 3. The change is removed from the tracking schema 4. Verify scripts run to confirm the revert

Revert Options

Tagging

Tags mark specific points in the deployment plan for targeted deploy/revert.

# Tag the current state
pgpm tag v1.0.0

# Tag with a description
pgpm tag v1.0.0 -m "Initial release"

Tags appear in pgpm.plan as:

@v1.0.0 2024-01-15T10:00:00Z user <user@example.com> # Initial release

Using Tags

# Deploy up to a tag
pgpm deploy --to @v1.0.0

# Revert to a tag (keeps the tag, reverts everything after it)
pgpm revert --to @v1.0.0

Status

Check what's deployed and what's pending.

# Show deployment status
pgpm migrate status

This shows:

• Which changes are deployed
• Which changes are pending (in plan but not yet deployed)
• The current tag (if any)

Full-Cycle Testing

pgpm test-packages runs a full deploy → verify → revert → deploy cycle to validate that all scripts work correctly in both directions.

# Full cycle test for current module
pgpm test-packages --full-cycle

# Full cycle test for all workspace modules
pgpm test-packages --full-cycle --workspace --all

This is the gold standard for validating migrations — it proves: 1. Deploy scripts apply correctly 2. Verify scripts confirm the deployed state 3. Revert scripts cleanly undo everything 4. Re-deploy works (proving revert was complete)

Common Workflows

First-time workspace deploy

Prerequisite: Ensure PostgreSQL is running and environment is loaded. See references/docker.md and references/env.md for setup.

pgpm admin-users bootstrap --yes
pgpm deploy --createdb --workspace --all --yes

Deploy after adding new changes

pgpm deploy
pgpm verify

Revert a bad deploy

pgpm revert --yes
# Fix the issue, then redeploy
pgpm deploy

Tag a release and deploy to that point

pgpm tag v1.0.0
pgpm deploy --to @v1.0.0

Validate all migrations (CI)

Note: In CI, start Postgres and load env vars first. See references/docker.md and references/env.md, or github-workflows-pgpm for CI-specific patterns.

pgpm admin-users bootstrap --yes
pgpm test-packages --full-cycle --workspace --all

Tracking Schema

pgpm tracks deployments in a PostgreSQL schema (typically pgpm_migrate). This contains:

• changes table — records each deployed change with timestamp and deployer
• tags table — records tagged points

This is how pgpm knows what's already deployed and what's pending.

Troubleshooting

PGPM Docker

Manage PostgreSQL Docker containers for local development using the pgpm docker command.

When to Apply

Use this skill when:

• Setting up a local PostgreSQL database for development
• Starting or stopping PostgreSQL containers
• Recreating a fresh database container
• User asks to run tests that need a database
• Troubleshooting database connection issues

Quick Start

Start PostgreSQL Container

pgpm docker start

This starts a PostgreSQL 17 container with default settings:

• Container name: postgres
• Port: 5432
• User: postgres
• Password: password

Start with Custom Options

pgpm docker start --port 5433 --name my-postgres

Recreate Container (Fresh Database)

pgpm docker start --recreate

Stop Container

pgpm docker stop

Command Reference

pgpm docker start

Start a PostgreSQL Docker container.

pgpm docker stop

Stop a running PostgreSQL container.

Common Workflows

Development Setup

# Start fresh database
pgpm docker start --recreate

# Load environment variables
eval "$(pgpm env)"

# Deploy your PGPM modules
pgpm deploy

Running Tests

# Ensure database is running
pgpm docker start

# Run tests with environment
pgpm env pnpm test

Multiple Databases

# Start main database on default port
pgpm docker start --name main-db

# Start test database on different port
pgpm docker start --name test-db --port 5433

PostgreSQL Version

The default image docker.io/constructiveio/postgres-plus:18 includes PostgreSQL 17 which is required for:

• security_invoker views
• Latest PostgreSQL features used by Constructive

If you see errors like "unrecognized parameter security_invoker", ensure you're using PostgreSQL 17+.

Troubleshooting

Environment Variables

After starting the container, use pgpm env to set up environment variables:

eval "$(pgpm env)"

This sets:

• PGHOST=localhost
• PGPORT=5432
• PGUSER=postgres
• PGPASSWORD=password
• PGDATABASE=postgres

References

For related references:

• Environment management: See references/env.md
• Running tests: See references/testing.md

PGPM Env

Manage PostgreSQL environment variables with profile support using the pgpm env command.

When to Apply

Use this skill when:

• Setting up environment variables for database connections
• Running commands that need PostgreSQL connection info
• Switching between local Postgres and Supabase profiles
• Deploying PGPM modules with correct database settings
• Running tests or scripts that need database access

Quick Start

Load Environment Variables

eval "$(pgpm env)"

This sets the following environment variables:

• PGHOST=localhost
• PGPORT=5432
• PGUSER=postgres
• PGPASSWORD=password
• PGDATABASE=postgres

Run Command with Environment

pgpm env pgpm deploy --database mydb

This runs pgpm deploy --database mydb with the PostgreSQL environment variables automatically set.

Profiles

Default Profile (Local Postgres)

eval "$(pgpm env)"

Supabase Profile

eval "$(pgpm env --supabase)"

Command Reference

Print Environment Exports

pgpm env                    # Default Postgres profile
pgpm env --supabase         # Supabase profile

Output (for shell evaluation):

export PGHOST="localhost"
export PGPORT="5432"
export PGUSER="postgres"
export PGPASSWORD="password"
export PGDATABASE="postgres"

Execute Command with Environment

pgpm env <command> [args...]
pgpm env --supabase <command> [args...]

Examples:

pgpm env createdb mydb
pgpm env pgpm deploy --database mydb
pgpm env psql -c "SELECT 1"
pgpm env --supabase pgpm deploy --database mydb

Common Workflows

Development Setup

# Start database container
pgpm docker start

# Load environment into current shell
eval "$(pgpm env)"

# Now all commands have database access
createdb myapp
pgpm deploy --database myapp

Running Tests

# Run tests with database environment
pgpm env pnpm test

# Or load into shell first
eval "$(pgpm env)"
pnpm test

PGPM Deployment

# Deploy to a specific database
pgpm env pgpm deploy --database constructive

# Verify deployment
pgpm env pgpm verify --database constructive

Supabase Local Development

# Start Supabase locally (using supabase CLI)
supabase start

# Load Supabase environment
eval "$(pgpm env --supabase)"

# Deploy modules to Supabase
pgpm deploy --database postgres

Shell Integration

Bash/Zsh

Add to your shell profile for automatic loading:

# ~/.bashrc or ~/.zshrc
alias pgenv='eval "$(pgpm env)"'
alias pgenv-supa='eval "$(pgpm env --supabase)"'

Then use:

pgenv          # Load default Postgres env
pgenv-supa     # Load Supabase env

One-liner Commands

# Create database and deploy in one command
pgpm env bash -c "createdb mydb && pgpm deploy --database mydb"

Environment Variables Reference

The pgpm env command sets standard PostgreSQL environment variables that are recognized by:

• psql and other PostgreSQL CLI tools
• Node.js pg library
• PGPM CLI commands
• Any tool using libpq

Troubleshooting

References

For related skills:

• Docker container management: See references/docker.md
• Running tests: See references/testing.md

Environment Configuration with @pgpmjs/env

Unified environment configuration for PGPM and Constructive projects. Provides config file discovery, environment variable parsing, and hierarchical option merging.

When to Apply

Use this skill when:

• Configuring PostgreSQL connections programmatically
• Setting up PGPM environment options
• Managing database configuration across environments
• Writing code that needs consistent environment handling

Installation

pnpm add @pgpmjs/env

Core Concepts

Configuration Hierarchy

Options are merged in this order (later overrides earlier):

1. PGPM defaults — Built-in sensible defaults 2. Config file — pgpm.json discovered via walkUp 3. Environment variables — PGHOST, PGPORT, etc. 4. Runtime overrides — Passed programmatically

Basic Usage

getEnvOptions()

Get merged PGPM options:

import { getEnvOptions } from '@pgpmjs/env';

const options = getEnvOptions();
// Returns merged options from defaults + config + env vars

// With runtime overrides
const options = getEnvOptions({
  pg: { database: 'mydb' }
});

// With custom working directory
const options = getEnvOptions({}, '/path/to/project');

getConnEnvOptions()

Get database connection options specifically:

import { getConnEnvOptions } from '@pgpmjs/env';

const connOptions = getConnEnvOptions();
// Returns db-specific options with roles and connections resolved

getDeploymentEnvOptions()

Get deployment-specific options:

import { getDeploymentEnvOptions } from '@pgpmjs/env';

const deployOptions = getDeploymentEnvOptions();
// Returns deployment options (useTx, fast, usePlan, etc.)

Environment Variables

PostgreSQL Connection

Database Configuration

Connection Credentials

Deployment Options

Server Configuration

CDN/Storage

Jobs Configuration

Error Output

Config File Discovery

loadConfigSync()

Load pgpm.json by walking up directory tree:

import { loadConfigSync } from '@pgpmjs/env';

const config = loadConfigSync('/path/to/project');
// Finds nearest pgpm.json walking up from given path

loadConfigSyncFromDir()

Load config from specific directory:

import { loadConfigSyncFromDir } from '@pgpmjs/env';

const config = loadConfigSyncFromDir('/path/to/project');

resolvePgpmPath()

Find the pgpm.json file path:

import { resolvePgpmPath } from '@pgpmjs/env';

const pgpmPath = resolvePgpmPath('/path/to/project');
// Returns full path to pgpm.json or undefined

Workspace Resolution

resolvePnpmWorkspace()

Find pnpm-workspace.yaml:

import { resolvePnpmWorkspace } from '@pgpmjs/env';

const workspacePath = resolvePnpmWorkspace('/path/to/project');

resolveLernaWorkspace()

Find lerna.json:

import { resolveLernaWorkspace } from '@pgpmjs/env';

const lernaPath = resolveLernaWorkspace('/path/to/project');

resolveWorkspaceByType()

Find workspace config by type:

import { resolveWorkspaceByType, WorkspaceType } from '@pgpmjs/env';

const path = resolveWorkspaceByType('/path/to/project', 'pnpm');
// WorkspaceType: 'pnpm' | 'lerna' | 'npm'

Utility Functions

walkUp()

Walk up directory tree to find a file:

import { walkUp } from '@pgpmjs/env';

const found = walkUp('/start/path', 'pgpm.json');
// Returns path to file or undefined

getEnvVars()

Parse environment variables into PgpmOptions:

import { getEnvVars } from '@pgpmjs/env';

const envOptions = getEnvVars();
// Or with custom env object
const envOptions = getEnvVars(process.env);

getNodeEnv()

Get normalized NODE_ENV:

import { getNodeEnv } from '@pgpmjs/env';

const env = getNodeEnv();
// Returns 'development' | 'production' | 'test'

parseEnvBoolean()

Parse boolean environment variable:

import { parseEnvBoolean } from '@pgpmjs/env';

parseEnvBoolean('true');  // true
parseEnvBoolean('1');     // true
parseEnvBoolean('yes');   // true
parseEnvBoolean('false'); // false
parseEnvBoolean(undefined); // undefined

parseEnvNumber()

Parse numeric environment variable:

import { parseEnvNumber } from '@pgpmjs/env';

parseEnvNumber('5432');    // 5432
parseEnvNumber('invalid'); // undefined
parseEnvNumber(undefined); // undefined

pgpm.json Configuration

Example pgpm.json with environment options:

{
  "name": "my-module",
  "version": "1.0.0",
  "db": {
    "rootDb": "postgres",
    "template": "template1",
    "prefix": "myapp_",
    "extensions": ["uuid-ossp", "pgcrypto"],
    "roles": {
      "admin": "admin_role",
      "app": "app_role",
      "anonymous": "anon_role",
      "authenticated": "auth_role"
    },
    "connections": {
      "app": {
        "user": "app_user",
        "password": "app_password"
      },
      "admin": {
        "user": "admin_user",
        "password": "admin_password"
      }
    }
  },
  "deployment": {
    "useTx": true,
    "fast": false,
    "usePlan": true
  }
}

Integration with pgsql-test

import { getConnEnvOptions } from '@pgpmjs/env';
import { getConnections } from 'pgsql-test';

const connOptions = getConnEnvOptions();
const { db, teardown } = await getConnections(connOptions);

Integration with pgpm CLI

The pgpm CLI uses @pgpmjs/env internally. Quick setup:

# Export standard PostgreSQL env vars
eval "$(pgpm env)"

# Now all pgpm commands use these vars
pgpm deploy --createdb

Best Practices

1. Use getEnvOptions(): Let the library handle merging 2. Config file for defaults: Put project defaults in pgpm.json 3. Env vars for secrets: Never commit passwords to pgpm.json 4. Override at runtime: Pass overrides for test-specific config 5. Consistent cwd: Pass explicit cwd when running from different directories

References

• Related skill: references/cli.md for CLI commands
• Related skill: references/workspace.md for workspace configuration
• Related skill: github-workflows-pgpm for CI/CD environment setup
• Related skill: constructive-env — Covers the full two-layer architecture (@pgpmjs/env + @constructive-io/graphql-env), GraphQL-specific env vars, SMTP config, and the "which package to import" decision guide

pgpm Extensions

How extensions and modules work in pgpm — adding dependencies, installing packages, and understanding the .control file.

When to Apply

Use this skill when:

• Adding a PostgreSQL extension (uuid-ossp, pgcrypto, plpgsql, etc.) to a module
• Installing a pgpm-published module (@pgpm/faker, @pgpm/base32, etc.)
• Editing a .control file's requires list
• Running pgpm extension or pgpm install
• Debugging missing extension errors during deploy

Critical Rule

NEVER run `CREATE EXTENSION` directly in SQL migration files. pgpm is deterministic — it reads the .control file and handles extension creation automatically during pgpm deploy. Writing CREATE EXTENSION in a deploy script will cause errors or duplicate operations.

Two Kinds of Extensions

1. Native PostgreSQL Extensions

Built into Postgres or installed via OS packages. Examples:

These are resolved by Postgres itself during deploy. pgpm issues CREATE EXTENSION IF NOT EXISTS for them automatically.

2. pgpm Modules

Published to npm under scoped names (e.g., @pgpm/faker, @pgpm/base32, @pgpm/uuid). These contain their own deploy/revert/verify scripts and are installed into the workspace's extensions/ directory.

During deploy, pgpm resolves these from the extensions/ directory and deploys them before your module (topological dependency order).

The .control File

Every pgpm module has a .control file at its root. This declares metadata and dependencies.

Anatomy

# my-module extension
comment = 'My module description'
default_version = '0.0.1'
requires = 'plpgsql, uuid-ossp, pgpm-base32, pgpm-types'

Key fields:

• comment — Human-readable description
• default_version — Version string (typically 0.0.1)
• requires — Comma-separated list of dependency control names (not npm names)

Control Names vs npm Names

The requires field uses control file names, not npm package names:

See references/module-naming.md for the full naming convention.

Adding Dependencies

Interactive: pgpm extension

Run inside a module directory to interactively select dependencies:

cd packages/my-module
pgpm extension

This shows a checkbox picker of all available modules in the workspace. Selected items are written to the .control file's requires list. You can also type custom extension names for native Postgres extensions.

Installing npm-published pgpm modules: pgpm install

To add an npm-published pgpm module to your workspace:

# Install a single module
pgpm install @pgpm/base32

# Install multiple modules
pgpm install @pgpm/base32 @pgpm/types @pgpm/uuid

# Install all missing modules declared in .control requires
pgpm install

pgpm install downloads the module from npm and places it in the workspace's extensions/ directory (e.g., extensions/@pgpm/base32/).

After installing, use pgpm extension to add the installed module to your .control file's requires.

Manual Editing

You can also edit the .control file directly:

requires = 'plpgsql, uuid-ossp, pgpm-base32'

Then run pgpm install (no arguments) to install any missing modules.

The extensions/ Directory

When you run pgpm install @pgpm/foo, it creates:

extensions/
  @pgpm/
    foo/
      pgpm-foo.control     # Module's control file
      pgpm.plan             # Module's deployment plan
      deploy/               # Deploy scripts
      revert/               # Revert scripts
      verify/               # Verify scripts
      package.json          # npm metadata

This directory is typically committed to version control so that pgpm deploy can resolve all dependencies without needing npm access.

Upgrading Modules

# Upgrade a specific module
pgpm upgrade-modules @pgpm/base32

# Upgrade all modules in the workspace
pgpm upgrade-modules --workspace --all

# Preview what would be upgraded
pgpm upgrade-modules --workspace --all --dry-run

Dependency Resolution During Deploy

When you run pgpm deploy, pgpm:

1. Reads the target module's .control file for requires 2. Resolves native Postgres extensions → queues CREATE EXTENSION IF NOT EXISTS 3. Resolves pgpm modules from extensions/ → deploys them first (recursively resolving their dependencies) 4. Deploys your module's changes in plan order

This is fully automatic — you never need to manually order extension creation.

Common Workflows

Add a native Postgres extension to your module

1. Edit .control:

   requires = 'plpgsql, uuid-ossp, pgcrypto'

2. Deploy — pgpm creates the extensions automatically

Add a pgpm module dependency

1. Install: pgpm install @pgpm/base32 2. Add to requires: pgpm extension (interactive) or edit .control 3. Deploy — pgpm deploys @pgpm/base32 before your module

Check what's installed

# List installed modules in the workspace extensions/ dir
ls extensions/

# Check a module's dependencies
cat packages/my-module/my-module.control

Troubleshooting

PGPM Module Naming: npm Names vs Control File Names

pgpm modules have two different identifiers that serve different purposes. Understanding when to use each is critical for correct dependency management.

When to Apply

Use this skill when:

• Creating or editing .control files
• Writing -- requires: statements in SQL deploy files
• Running pgpm install commands
• Referencing dependencies between modules
• Publishing modules to npm

The Two Identifiers

Every pgpm module has two names:

1. npm Package Name (for distribution)

Defined in package.json as the name field. Used for npm distribution and the pgpm install command.

Format: @scope/package-name (scoped) or package-name (unscoped)

Examples:

• @sf-bot/rag-core
• @san-francisco/sf-docs-embeddings
• @pgpm/base32

2. Control File Name / Extension Name (for PostgreSQL)

Defined by the .control filename and %project= in pgpm.plan. Used in PostgreSQL extension system and SQL dependency declarations.

Format: module-name (no scope, no @ symbol)

Examples:

• rag-core
• sf-docs-embeddings
• pgpm-base32

When to Use Each

Use npm Package Name (@scope/name)

1. pgpm install command:

pgpm install @sf-bot/rag-core @sf-bot/rag-functions @sf-bot/rag-indexes

2. package.json dependencies:

{
  "dependencies": {
    "@sf-bot/rag-core": "^0.0.3"
  }
}

Use Control File Name (name)

1. .control file requires line:

# sf-docs-embeddings.control
requires = 'rag-core'

2. SQL deploy file requires comments:

-- Deploy data/seed_collection to pg
-- requires: rag-core

3. pgpm.plan %project declaration:

%project=sf-docs-embeddings

4. Cross-package references in pgpm.plan:

data/seed [rag-core:schemas/rag/schema] 2026-01-25T00:00:00Z Author <author@example.com>

Real-World Example

Consider the sf-docs-embeddings module:

package.json (npm name for distribution):

{
  "name": "@san-francisco/sf-docs-embeddings",
  "version": "0.0.3"
}

sf-docs-embeddings.control (control name for PostgreSQL):

# sf-docs-embeddings extension
comment = 'San Francisco documentation embeddings'
default_version = '0.0.1'
requires = 'rag-core'

pgpm.plan (control name for project):

%project=sf-docs-embeddings

deploy/data/seed_collection.sql (control name in requires):

-- Deploy data/seed_collection to pg
-- requires: rag-core

The Mapping

pgpm maintains an internal mapping between control names and npm names. When you run pgpm install, it:

1. Reads the .control file's requires list (control names) 2. Maps those to npm package names 3. Installs the npm packages

For example, if your .control has requires = 'pgpm-base32', pgpm knows to install @pgpm/base32 from npm.

Common Mistakes

Wrong: Using npm name in .control file

# WRONG
requires = '@sf-bot/rag-core'

# CORRECT
requires = 'rag-core'

Wrong: Using control name in pgpm install

# WRONG
pgpm install rag-core

# CORRECT
pgpm install @sf-bot/rag-core

Wrong: Using npm name in SQL requires

-- WRONG
-- requires: @sf-bot/rag-core

-- CORRECT
-- requires: rag-core

Quick Reference Table

Summary

• npm names (@scope/name): Used for distribution and installation via npm/pgpm install
• Control names (name): Used for PostgreSQL extension system, .control files, and SQL dependency declarations

Think of it this way: npm names are for the JavaScript/npm ecosystem, control names are for the PostgreSQL ecosystem.

References

• Related skill: references/cli.md for CLI commands
• Related skill: references/workspace.md for workspace structure
• Related skill: references/changes.md for authoring database changes

Constructive Next.js App Boilerplate

A frontend-only Next.js application that connects to a Constructive backend. Provides production-ready authentication flows, organization management, invite handling, member management, and account settings — all powered by a generated GraphQL SDK.

Setup

1. Scaffold from Template

pgpm init -w \
  --repo constructive-io/sandbox-templates \
  --template nextjs/constructive-app \
  --name <workspace-name> \
  --fullName "<Author Full Name>" \
  --email "<author@example.com>" \
  --repoName <workspace-name> \
  --username <github-username> \
  --license MIT \
  --moduleName <module-name>

2. Install and Configure

cd <workspace-name>/packages/<module-name>
pnpm install

Create .env.local:

NEXT_PUBLIC_SCHEMA_BUILDER_GRAPHQL_ENDPOINT=http://api.localhost:3000/graphql

3. Generate SDK and Start

pnpm codegen   # Generate GraphQL SDK against running backend
pnpm dev       # Opens at http://localhost:3001

Backend Requirements

Requires a running Constructive backend (typically via Constructive Hub):

Project Structure

src/
├── app/                        # Next.js App Router pages
│   ├── login/ register/        # Auth flows
│   ├── account/ settings/      # User management
│   └── orgs/[orgId]/           # Org-scoped pages (activity, invites, members, settings)
├── components/
│   ├── ui/                     # shadcn/ui components (43 components)
│   ├── auth/                   # Auth forms
│   ├── organizations/          # Org CRUD
│   ├── invites/ members/       # Org management
│   └── app-shell/              # Sidebar, navigation, layout
├── graphql/
│   └── schema-builder-sdk/     # Generated SDK (via codegen)
└── lib/
    ├── auth/                   # Auth utilities and context
    ├── gql/                    # GraphQL hooks and query factories
    └── permissions/            # Permission checking

Customization

Branding

Edit src/config/branding.ts — app name, tagline, logo paths, legal links.

Adding UI Components

npx shadcn@latest add @constructive/<component>

Registry URL configured in components.json. Components use Base UI primitives, Tailwind CSS 4, and cva for variants.

Features

• Authentication — Login, register, logout, password reset, email verification
• Organizations — Create and manage organizations
• Invites — Send and accept organization invites
• Members — Manage organization members and roles
• Account Management — Profile, email verification, account deletion
• App Shell — Sidebar navigation, theme switching, responsive layout
• Permissions — Role-based access control for org features

pgpm Export

Export a provisioned Constructive DB back to pgpm packages — two outputs:

CLI Usage (Partial Automation)

cd path/to/your-app  # your project workspace
eval "$(pgpm env)"

pgpm export \
  --author "name <email>" \
  --extensionName myapp \
  --metaExtensionName myapp-svc

Three prompts still require TTY: select database, select database_id, select schemas.

Programmatic API

Key steps: 1. Look up database_id from metaschema_public.database 2. Look up schema_names from metaschema_public.schema 3. Call exportMigrations() from @pgpmjs/core

await exportMigrations({
  project, options,
  dbInfo: { dbname: HOST_DB, databaseName: DB_NAME, database_ids: [databaseId] },
  author: AUTHOR, schema_names,
  extensionName: EXT_NAME, metaExtensionName: SVC_NAME,
  outdir: resolve(WORKSPACE, 'packages'),
});

How It Works

1. Queries db_migrate.sql_actions for raw migration history 2. Applies schema name replacer (internal names → portable extensionName prefix) 3. Writes extension package with pgpm.plan, deploy/, revert/, verify/ 4. Reads metaschema records via export-meta 5. Writes service package

Re-Running

• Interactive: prompts to confirm overwrite
• Programmatic: silently overwrites SQL files, preserves pgpm.json/package.json

Related

• constructive-sdk skill — provision before exporting

pgpm Table Creation Rules

When creating a new table in metaschema_modules_public, metaschema_public, or services_public schemas in the constructive-db repository, follow these steps.

Required: Deterministic ID Trigger

Every table with a uuid primary key MUST have a zzz_set_deterministic_id trigger. This is critical for deterministic test runs and reproducible deployments.

Pattern

For a table named my_table in schema metaschema_modules_public:

1. Deploy file at packages/metaschema/deploy/schemas/metaschema_modules_public/tables/my_table/triggers/set_deterministic_id.sql:

-- Deploy schemas/metaschema_modules_public/tables/my_table/triggers/set_deterministic_id to pg

-- requires: schemas/metaschema_modules_private/schema
-- requires: schemas/metaschema_modules_public/tables/my_table/table
-- requires: schemas/metaschema_private/procedures/deterministic_id

BEGIN;

CREATE FUNCTION metaschema_modules_private.tg_set_my_table_deterministic_id()
RETURNS TRIGGER AS $$
BEGIN
  IF current_setting('metaschema.deterministic_ids', true) = 'true' THEN
    NEW.id := metaschema_private.deterministic_id(NEW.table_id, NEW.node_type);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER zzz_set_deterministic_id
BEFORE INSERT ON metaschema_modules_public.my_table
FOR EACH ROW
EXECUTE FUNCTION metaschema_modules_private.tg_set_my_table_deterministic_id();

ALTER TABLE metaschema_modules_public.my_table
ENABLE ALWAYS TRIGGER zzz_set_deterministic_id;

COMMIT;

Note: The deterministic_id() arguments vary by table. Check existing tables for the correct arguments.

2. Verify file — use verify_function and verify_trigger 3. Revert file — DROP TRIGGER IF EXISTS + DROP FUNCTION IF EXISTS 4. pgpm.plan entry — add with proper dependencies

Checklist for New Tables

• [ ] Table DDL (deploy/verify/revert + pgpm.plan + extension SQL)
• [ ] Insert trigger (deploy/verify/revert + pgpm.plan)
• [ ] Deterministic ID trigger (deploy/verify/revert + pgpm.plan + extension SQL)
• [ ] COMMENT ON COLUMN for every column
• [ ] COMMENT ON TABLE
• [ ] Foreign key constraints with @omit manyToMany comments
• [ ] Indexes on foreign key columns

PGPM Plan File Format

Guide to the correct format for pgpm.plan files and common format errors.

When to Apply

Use this skill when:

• Encountering "Invalid line format" errors from pgpm
• Creating new pgpm.plan files
• Adding changes with dependencies to a plan file
• Debugging plan file parse errors

Plan File Format

Basic Structure

A pgpm.plan file has the following structure:

%syntax-version=1.0.0
%project=module-name
%uri=module-name

change_name [dependencies] timestamp planner <email> # comment

Change Line Format

The correct format for a change line is:

change_name [dep1 dep2] 2026-01-25T00:00:00Z planner-name <email@example.org> # optional comment

Order matters! The components must appear in this exact order: 1. change_name - The name/path of the change (e.g., schemas/public/tables/users) 2. [dependencies] - Optional, space-separated list of dependencies in square brackets 3. timestamp - ISO 8601 format: YYYY-MM-DDTHH:MM:SSZ 4. planner - Name of the person/entity who planned the change 5. <email> - Email in angle brackets 6. # comment - Optional comment starting with #

Common Mistake: Dependencies After Email

Wrong:

data/seed_chunks 2026-01-25T00:00:00Z city-of-san-francisco <opensource@sfgov.org> [data/create_collection] # comment

Correct:

data/seed_chunks [data/create_collection] 2026-01-25T00:00:00Z city-of-san-francisco <opensource@sfgov.org> # comment

The parser expects dependencies immediately after the change name, not after the email.

Error Messages

"Line N: Invalid line format"

Symptom:

PgpmError: Failed to parse plan file /path/to/pgpm.plan: Line 6: Invalid line format

Cause: The line doesn't match the expected format. Most commonly:

• Dependencies placed in wrong position
• Missing or malformed timestamp
• Missing angle brackets around email
• Invalid characters in change name

Solution: Check the line format matches:

change_name [deps] timestamp planner <email> # comment

Parser Regex

The pgpm parser uses this regex pattern for change lines:

/^(\S+)(?:\s+\[([^\]]*)\])?(?:\s+(\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z)(?:\s+([^<]+?))?(?:\s+<([^>]+)>)?(?:\s+#\s+(.*))?)?$/

This breaks down as:

• (\S+) - change name (required)
• (?:\s+\[([^\]]*)\])? - dependencies in brackets (optional)
• (?:\s+(\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z) - ISO timestamp
• (?:\s+([^<]+?))? - planner name
• (?:\s+<([^>]+)>)? - email in angle brackets
• (?:\s+#\s+(.*))? - comment

Examples

Change Without Dependencies

schemas/public/tables/users 2026-01-25T00:00:00Z dan <dan@example.org> # create users table

Change With Single Dependency

schemas/public/tables/posts [schemas/public/tables/users] 2026-01-25T00:00:00Z dan <dan@example.org> # posts table

Change With Multiple Dependencies

schemas/public/views/user_posts [schemas/public/tables/users schemas/public/tables/posts] 2026-01-25T00:00:00Z dan <dan@example.org>

Cross-Module Dependency

data/seed [other-module:schemas/setup] 2026-01-25T00:00:00Z dan <dan@example.org> # depends on other module

SQL File Dependencies

Dependencies can also be declared in SQL deploy files using the -- requires: comment:

-- Deploy module-name:schemas/public/tables/posts to pg
-- requires: schemas/public/tables/users

CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES users(id),
  content text
);

Note: Do not wrap SQL in BEGIN/COMMIT transactions - pgpm handles transactions automatically.

These are used by pgpm for dependency resolution but the plan file format is what gets parsed first.

Quick Reference

*Required if any metadata is present

References

• Related skill: references/troubleshooting.md for general pgpm issues
• Related skill: references/dependencies.md for dependency management
• Related skill: references/changes.md for adding changes to modules

Publishing PGPM Modules (Constructive Standard)

Publish pgpm SQL modules to npm using pgpm package bundling and lerna for versioning. This covers the workflow for @pgpm/* scoped packages.

When to Apply

Use this skill when:

• Publishing SQL database modules to npm
• Bundling pgpm packages for distribution
• Managing @pgpm/* scoped packages
• Working with pgpm-modules or similar repositories

PGPM vs PNPM Workspaces

Workspace Structure

pgpm-modules/
├── .gitignore
├── lerna.json
├── package.json
├── packages/
│   ├── faker/
│   │   ├── deploy/
│   │   ├── revert/
│   │   ├── verify/
│   │   ├── package.json
│   │   ├── pgpm-faker.control
│   │   └── pgpm.plan
│   └── utils/
│       ├── deploy/
│       ├── revert/
│       ├── verify/
│       ├── package.json
│       ├── pgpm-utils.control
│       └── pgpm.plan
├── pgpm.json
├── pnpm-lock.yaml
└── pnpm-workspace.yaml

Configuration Files

pgpm.json

Points to packages containing SQL modules:

{
  "packages": [
    "packages/*"
  ]
}

pnpm-workspace.yaml

Same packages directory:

packages:
  - packages/*

lerna.json (Fixed Versioning)

For pgpm modules, use fixed versioning so all modules release together:

{
  "$schema": "node_modules/lerna/schemas/lerna-schema.json",
  "version": "0.16.6",
  "npmClient": "pnpm"
}

Note: Unlike TypeScript packages which often use independent versioning, pgpm modules typically use fixed versioning because they're tightly coupled.

Root package.json

{
  "name": "pgpm-modules",
  "version": "0.0.1",
  "private": true,
  "repository": {
    "type": "git",
    "url": "https://github.com/constructive-io/pgpm-modules"
  },
  "license": "MIT",
  "engines": {
    "node": ">=20"
  },
  "scripts": {
    "bundle": "pnpm -r bundle",
    "lint": "pnpm -r lint",
    "test": "pnpm -r test",
    "deps": "pnpm up -r -i -L"
  },
  "devDependencies": {
    "@types/jest": "^30.0.0",
    "jest": "^30.2.0",
    "lerna": "^8.2.3",
    "pgsql-test": "^2.18.6",
    "ts-jest": "^29.4.5",
    "typescript": "^5.9.3"
  }
}

Module Configuration

Module package.json

{
  "name": "@pgpm/faker",
  "version": "0.16.0",
  "description": "Fake data generation utilities for testing",
  "author": "Dan Lynch <pyramation@gmail.com>",
  "keywords": ["postgresql", "pgpm", "faker", "testing"],
  "publishConfig": {
    "access": "public"
  },
  "scripts": {
    "bundle": "pgpm package",
    "test": "jest",
    "test:watch": "jest --watch"
  },
  "dependencies": {
    "@pgpm/types": "workspace:*",
    "@pgpm/verify": "workspace:*"
  },
  "devDependencies": {
    "pgpm": "^1.3.0"
  },
  "repository": {
    "type": "git",
    "url": "https://github.com/constructive-io/pgpm-modules"
  }
}

Key differences from TypeScript packages:

• No publishConfig.directory — publishes from package root
• Uses pgpm package for bundling instead of makage
• Dependencies on other @pgpm/ modules use `workspace:`

Module .control File

# pgpm-faker.control
comment = 'Fake data generation utilities'
default_version = '0.16.0'
requires = 'plpgsql,uuid-ossp'

Module pgpm.plan

%syntax-version=1.0.0
%project=pgpm-faker
%uri=pgpm-faker

schemas/faker 2025-01-01T00:00:00Z Author <author@example.com>
schemas/faker/functions/random_name [schemas/faker] 2025-01-01T00:00:00Z Author <author@example.com>

Build Workflow

Bundle a Module

cd packages/faker
pgpm package

Or bundle all modules:

pnpm -r bundle

Run Tests

# All modules
pnpm -r test

# Specific module
pnpm --filter @pgpm/faker test

Publishing Workflow

1. Prepare

pnpm install
pnpm -r bundle
pnpm -r test

2. Version

# Fixed versioning (all packages get same version)
pnpm lerna version

# Or with conventional commits
pnpm lerna version --conventional-commits

3. Publish

# Use from-package to publish versioned packages
pnpm lerna publish from-package

One-Liner

pnpm install && pnpm -r bundle && pnpm -r test && pnpm lerna version && pnpm lerna publish from-package

Dry Run Commands

# Test versioning (no git operations)
pnpm lerna version --no-git-tag-version --no-push

# Test publishing
pnpm lerna publish from-package --dry-run

Module Dependencies

Internal Dependencies

Use workspace:* for dependencies on other pgpm modules:

{
  "dependencies": {
    "@pgpm/types": "workspace:*",
    "@pgpm/verify": "workspace:*"
  }
}

SQL Dependencies

Declare SQL-level dependencies in the .control file:

requires = 'plpgsql,uuid-ossp,@pgpm/types'

And in deploy scripts:

-- Deploy: schemas/faker/functions/random_name
-- requires: schemas/faker
-- requires: @pgpm/types:schemas/types

CREATE FUNCTION faker.random_name()
RETURNS TEXT AS $$
  -- implementation
$$ LANGUAGE plpgsql;

Three-File Pattern

Every SQL change has three files:

Example:

deploy/schemas/faker/functions/random_name.sql:

-- Deploy: schemas/faker/functions/random_name
-- requires: schemas/faker

BEGIN;
CREATE FUNCTION faker.random_name()
RETURNS TEXT AS $$
BEGIN
  RETURN 'John Doe';
END;
$$ LANGUAGE plpgsql;
COMMIT;

revert/schemas/faker/functions/random_name.sql:

-- Revert: schemas/faker/functions/random_name

BEGIN;
DROP FUNCTION IF EXISTS faker.random_name();
COMMIT;

verify/schemas/faker/functions/random_name.sql:

-- Verify: schemas/faker/functions/random_name

SELECT verify_function('faker.random_name');

Naming Conventions

• Package name: @pgpm/<module-name>
• Control file: pgpm-<module-name>.control
• SQL uses snake_case for identifiers
• Never use CREATE OR REPLACE — pgpm is deterministic

Best Practices

1. Fixed versioning: Use for tightly coupled SQL modules 2. Test before publish: Run pnpm -r test to verify all modules 3. Bundle before publish: Run pnpm -r bundle to create packages 4. Use verify helpers: Leverage @pgpm/verify for consistent verification 5. Document dependencies: Keep .control file and SQL requires in sync

References

• Related reference: references/workspace.md for workspace setup
• Related reference: references/changes.md for authoring SQL changes
• Related reference: references/dependencies.md for managing dependencies
• Related skill: pnpm-publishing for TypeScript package publishing

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

Project Scaffolding with pgpm init

Scaffold new Constructive projects using pgpm init — workspace/module templates (PGPM and PNPM variants), Next.js app boilerplate, custom template repositories, and boilerplate authoring.

When to Apply

Use this reference when:

• Scaffolding a new workspace or module with pgpm init
• Setting up a Constructive Next.js frontend application
• Using custom template repositories
• Authoring new boilerplate templates
• Setting up non-interactive pgpm init for CI/CD

Quick Start

# Create a PGPM workspace + module
pgpm init -w

# Create a Next.js app from template
pgpm init -w --repo constructive-io/sandbox-templates --template nextjs/constructive-app

# Create a pure TypeScript workspace
pgpm init workspace --dir pnpm

Available Templates

CLI Options

Non-Interactive Mode

For CI/CD pipelines and automation, use --no-tty or set CI=true:

pgpm init workspace --no-tty \
  --name my-workspace \
  --fullName "Your Name" \
  --email "you@example.com" \
  --username your-github-username \
  --license MIT

Required Parameters for Non-Interactive Module

Detailed References

• template-authoring.md — Creating custom boilerplate templates
• nextjs-app.md — Constructive Next.js app boilerplate

Custom Boilerplate Authoring

Create and customize boilerplate templates for pgpm init.

Template Repository Structure

my-boilerplates/
  .boilerplates.json       # Root config (points to default directory)
  pgpm/                    # Default template variant (PGPM)
    module/
      .boilerplate.json    # Module template config
      package.json         # Template files with placeholders
      pgpm.plan
    workspace/
      .boilerplate.json    # Workspace template config
  pnpm/                    # Alternative variant (pure PNPM)
    module/
      .boilerplate.json
    workspace/
      .boilerplate.json

Root Configuration

.boilerplates.json at the repository root specifies the default template directory:

{
  "dir": "pgpm"
}

Template Configuration

Each template has a .boilerplate.json file defining its type, workspace requirements, and questions.

Template Types

Workspace Requirements

{
  "type": "module",
  "requiresWorkspace": "pgpm"
}

Placeholder System

Templates use the ____placeholder____ pattern (4 underscores on each side) for variable substitution:

{
  "name": "@____username____/____moduleName____",
  "version": "0.0.1",
  "description": "____moduleDesc____",
  "author": "____fullName____ <____email____>"
}

Question Configuration

Resolvers

defaultFrom: git.user.name, git.user.email, npm.whoami, workspace.dirname

setFrom: workspace.name, workspace.author.name, workspace.author.email, workspace.license, workspace.organization.name

optionsFrom: licenses (SPDX license identifiers)

Creating a Custom Repository

1. Create a new repository with the structure above 2. Add .boilerplates.json pointing to your default directory 3. Create template directories with .boilerplate.json configs 4. Add template files with ____placeholder____ patterns 5. Use with pgpm init --repo owner/your-boilerplates

Best Practices

1. Use setFrom for values that inherit from workspace context 2. Use defaultFrom for sensible defaults that users can override 3. Keep placeholder names descriptive and consistent 4. Test templates with --no-tty to ensure all required fields are defined

PGPM Testing

Run PostgreSQL integration tests with isolated databases using the pgsql-test package.

Testing Framework Standard

IMPORTANT: Constructive projects use Jest as the standard testing framework. Do NOT use vitest, mocha, or other test runners unless explicitly approved. Jest provides:

• Consistent testing experience across all packages
• Built-in mocking and assertion libraries
• Snapshot testing support
• Parallel test execution

When to Apply

Use this skill when:

• Writing integration tests that need a database
• Testing PGPM modules or migrations
• Setting up isolated test databases
• Seeding test data from SQL files or PGPM modules
• Running PostGraphile/GraphQL integration tests

Quick Start

Installation

pnpm add -D pgsql-test

Basic Test Setup

import { getConnections } from 'pgsql-test';

let db: any;
let teardown: () => Promise<void>;

beforeAll(async () => {
  ({ db, teardown } = await getConnections());
});

afterAll(() => teardown());
beforeEach(() => db.beforeEach());
afterEach(() => db.afterEach());

test('database query works', async () => {
  const result = await db.query('SELECT 1 as num');
  expect(result.rows[0].num).toBe(1);
});

Core API

getConnections()

Creates an isolated test database and returns clients plus cleanup function.

import { getConnections } from 'pgsql-test';

const { db, teardown } = await getConnections(
  connectionOptions?,  // Optional: custom connection settings
  seedAdapters?        // Optional: array of seed adapters
);

Returns:

• db - PgTestClient with query methods and transaction helpers
• teardown - Cleanup function to drop the test database

PgTestClient Methods

Seeding Data

SQL File Seeding

import { getConnections, seed } from 'pgsql-test';

const { db, teardown } = await getConnections({}, [
  seed.sqlfile(['./fixtures/schema.sql', './fixtures/data.sql'])
]);

Function Seeding

import { getConnections, seed } from 'pgsql-test';

const { db, teardown } = await getConnections({}, [
  seed.fn(async (client) => {
    await client.query(`
      CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL
      )
    `);
    await client.query(`
      INSERT INTO users (name) VALUES ('Alice'), ('Bob')
    `);
  })
]);

PGPM Module Seeding

Deploy a PGPM module into the test database:

import { getConnections, seed } from 'pgsql-test';

const { db, teardown } = await getConnections({}, [
  seed.pgpm(process.cwd())  // Deploy module from current directory
]);

CSV Seeding

import { getConnections, seed } from 'pgsql-test';

const { db, teardown } = await getConnections({}, [
  seed.sqlfile(['./fixtures/schema.sql']),
  seed.csv('users', './fixtures/users.csv')
]);

Test Patterns

Transaction Isolation

Each test runs in a savepoint that gets rolled back:

beforeEach(() => db.beforeEach());  // Creates savepoint
afterEach(() => db.afterEach());    // Rolls back to savepoint

test('insert is isolated', async () => {
  await db.query("INSERT INTO users (name) VALUES ('Test')");
  // This insert is rolled back after the test
});

test('previous insert not visible', async () => {
  const result = await db.query("SELECT * FROM users WHERE name = 'Test'");
  expect(result.rows).toHaveLength(0);  // Rolled back!
});

Setting User Context

For RLS (Row Level Security) testing:

test('user can only see own data', async () => {
  await db.setContext('user_id', 'user-123');

  const result = await db.query('SELECT * FROM user_data');
  // Only returns rows where user_id = 'user-123'
});

Multiple Connections

const { db: adminDb, teardown: teardownAdmin } = await getConnections({
  user: 'postgres'
});

const { db: appDb, teardown: teardownApp } = await getConnections({
  user: 'app_user'
});

Running Tests

Prerequisites

1. Start PostgreSQL:

pgpm docker start

2. Load environment:

eval "$(pgpm env)"

3. Run tests:

pnpm test

One-liner

pgpm env pnpm test

Watch Mode

pgpm env pnpm test --watch

Common Workflows

Testing PGPM Module

import { getConnections, seed } from 'pgsql-test';

describe('my-module', () => {
  let db: any, teardown: () => Promise<void>;

  beforeAll(async () => {
    ({ db, teardown } = await getConnections({}, [
      seed.pgpm(__dirname + '/..')  // Deploy parent module
    ]));
  });

  afterAll(() => teardown());
  beforeEach(() => db.beforeEach());
  afterEach(() => db.afterEach());

  test('function works correctly', async () => {
    const result = await db.query('SELECT my_function($1)', ['input']);
    expect(result.rows[0].my_function).toBe('expected');
  });
});

Testing with Fixtures

import { getConnections, seed } from 'pgsql-test';
import path from 'path';

const fixtures = path.join(__dirname, '__fixtures__');

beforeAll(async () => {
  ({ db, teardown } = await getConnections({}, [
    seed.sqlfile([
      path.join(fixtures, 'schema.sql'),
      path.join(fixtures, 'seed-data.sql')
    ])
  ]));
});

Troubleshooting

File Structure

Recommended test file organization:

my-module/
  __tests__/
    __fixtures__/
      schema.sql
      seed-data.sql
    my-feature.test.ts
  deploy/
  revert/
  verify/
  pgpm.plan

References

For related skills:

• Docker container management: See references/docker.md
• Environment variables: See references/env.md
• GraphQL codegen: See constructive-graphql-codegen skill

PGPM Troubleshooting

Quick fixes for common pgpm, PostgreSQL, and testing issues.

When to Apply

Use this skill when encountering:

• Connection errors to PostgreSQL
• Docker-related issues
• Environment variable problems
• Transaction aborted errors in tests
• Deployment failures

PostgreSQL Connection Issues

Docker Not Running

Symptom:

Cannot connect to the Docker daemon

Solution: 1. Start Docker Desktop 2. Wait for it to fully initialize 3. Then run pgpm commands

PostgreSQL Not Accepting Connections

Symptom:

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed

Solution:

# Start PostgreSQL container
pgpm docker start

# Load environment variables
eval "$(pgpm env)"

# Verify connection
psql -c "SELECT version();"

Wrong Port or Host

Symptom:

connection refused

Solution:

# Check current environment
echo $PGHOST $PGPORT

# Reload environment
eval "$(pgpm env)"

# Verify settings
pgpm env

Environment Variable Issues

PGHOST Not Set

Symptom:

PGHOST not set

or

could not connect to server: No such file or directory

Solution:

# Load pgpm environment
eval "$(pgpm env)"

Permanent fix - add to shell config:

# Add to ~/.bashrc or ~/.zshrc
eval "$(pgpm env)"

Environment Not Persisting

Symptom: Environment variables reset after each command

Cause: Running eval $(pgpm env) in a subshell or script

Solution: Run in current shell:

# Correct - runs in current shell
eval "$(pgpm env)"

# Wrong - runs in subshell
bash -c 'eval "$(pgpm env)"'

Testing Issues

Tests Fail to Connect

Symptom: Tests time out or fail with connection errors

Solution:

# 1. Start PostgreSQL
pgpm docker start

# 2. Load environment
eval "$(pgpm env)"

# 3. Bootstrap users (run once)
pgpm admin-users bootstrap --yes

# 4. Run tests
pnpm test

Current Transaction Is Aborted

Symptom:

current transaction is aborted, commands ignored until end of transaction block

Cause: An error occurred in the transaction, and PostgreSQL marks the entire transaction as aborted. All subsequent queries fail until the transaction ends.

Solution: Use savepoints when testing operations that should fail:

// Before the expected failure
const point = 'my_savepoint';
await db.savepoint(point);

// Operation that should fail
await expect(
  db.query('INSERT INTO restricted_table ...')
).rejects.toThrow(/permission denied/);

// After the failure - rollback to savepoint
await db.rollback(point);

// Now you can continue using the connection
const result = await db.query('SELECT 1');

Pattern for multiple failures:

it('tests multiple failure scenarios', async () => {
  // First failure
  const point1 = 'first_failure';
  await db.savepoint(point1);
  await expect(db.query('...')).rejects.toThrow();
  await db.rollback(point1);

  // Second failure
  const point2 = 'second_failure';
  await db.savepoint(point2);
  await expect(db.query('...')).rejects.toThrow();
  await db.rollback(point2);

  // Continue with passing assertions
  const result = await db.query('SELECT 1');
  expect(result.rows[0]).toBeDefined();
});

Tests Interfering with Each Other

Symptom: Tests pass individually but fail when run together

Cause: Missing or incorrect beforeEach/afterEach hooks

Solution:

beforeEach(async () => {
  await pg.beforeEach();
  await db.beforeEach();
});

afterEach(async () => {
  await db.afterEach();
  await pg.afterEach();
});

Deployment Issues

Module Not Found

Symptom:

Error: Module 'mymodule' not found

Solution: 1. Ensure you're in a pgpm workspace (has pgpm.json) 2. Check module is in packages/ directory 3. Verify module has .control file

Dependency Not Found

Symptom:

Error: Change 'module:path/to/change' not found

Solution: 1. Check the referenced module exists 2. Verify the change path matches exactly 3. Check -- requires: comment syntax:

   -- requires: other_module:schemas/other/tables/table

Deploy Order Wrong

Symptom: Foreign key or reference errors during deployment

Solution: 1. Check pgpm.plan for correct order 2. Verify -- requires: comments in deploy files 3. Regenerate plan if needed:

   pgpm plan

Docker Issues

Container Won't Start

Symptom:

Error starting container

Solution:

# Stop any existing containers
pgpm docker stop

# Remove old containers
docker rm -f pgpm-postgres

# Start fresh
pgpm docker start

Port Already in Use

Symptom:

port 5432 is already in use

Solution:

# Find what's using the port
lsof -i :5432

# Either stop that process or use a different port
# Edit docker-compose.yml to use different port

Volume Permission Issues

Symptom:

Permission denied on volume mount

Solution:

# Remove old volumes
docker volume rm pgpm_data

# Restart
pgpm docker start

Quick Reference

Getting Help

If issues persist: 1. Check pgpm version: pgpm --version 2. Check Docker status: docker ps 3. Check PostgreSQL logs: docker logs pgpm-postgres 4. Verify environment: pgpm env

References

• Related reference: references/docker.md for Docker management
• Related reference: references/env.md for environment configuration
• Related skill: pgsql-test-exceptions for transaction handling

PGPM Workspaces

Create and manage pgpm workspaces for modular PostgreSQL development. Workspaces bring npm-style modularity to database development.

When to Apply

Use this skill when:

• Starting a new modular database project
• Creating a pgpm workspace structure
• Initializing database modules
• Setting up a pnpm monorepo for database packages

Quick Start

Create a Workspace

pgpm init workspace

Enter workspace name when prompted:

? Enter workspace name: my-database-project

This creates a complete pnpm monorepo:

my-database-project/
├── docker-compose.yml
├── pgpm.json
├── lerna.json
├── LICENSE
├── Makefile
├── package.json
├── packages/
├── pnpm-workspace.yaml
├── README.md
└── tsconfig.json

Install Dependencies

cd my-database-project
pnpm install

Create a Module

Inside the workspace:

pgpm init

Enter module details:

? Enter module name: pets
? Select extensions: uuid-ossp, plpgsql

This creates:

packages/pets/
├── pets.control
├── pgpm.plan
├── deploy/
├── revert/
└── verify/

Workspace vs Module

Workspace: Top-level directory containing your entire project. Has pgpm.json and packages/ directory. Like an npm project root.

Module: Self-contained database package inside the workspace. Has its own pgpm.plan, .control file, and migration directories. Like an individual npm package.

Key Files

pgpm.json (Workspace Config)

{
  "packages": ["packages/*"]
}

Points pgpm to your modules directory.

module.control (Module Metadata)

# pets.control
comment = 'Pet adoption module'
default_version = '0.0.1'
requires = 'uuid-ossp,plpgsql'

Declares module name, description, version, and dependencies.

pgpm.plan (Migration Plan)

%syntax-version=1.0.0
%project=pets
%uri=pets

schemas/pets 2025-11-14T00:00:00Z Author <author@example.com>
schemas/pets/tables/pets [schemas/pets] 2025-11-14T00:00:00Z Author <author@example.com>

Tracks all changes in deployment order.

Common Commands

Environment Setup

Before deploying, ensure PostgreSQL is running and connection variables are loaded.

See references/docker.md for starting PostgreSQL and references/env.md for loading environment variables.

# Verify connection
psql -c "SELECT version();"

# Bootstrap database users (run once)
pgpm admin-users bootstrap --yes

Deploy a Module

cd packages/pets
pgpm deploy --database pets_dev --createdb --yes

pgpm: 1. Creates the database if needed 2. Resolves dependencies 3. Deploys changes in order 4. Tracks deployment in pgpm_migrate schema

Module Structure Best Practices

Organize changes hierarchically:

deploy/
└── schemas/
    └── app/
        ├── schema.sql
        ├── tables/
        │   └── users.sql
        ├── functions/
        │   └── create_user.sql
        └── triggers/
            └── updated_at.sql

Use nested paths:

pgpm add schemas/app/schema
pgpm add schemas/app/tables/users --requires schemas/app/schema
pgpm add schemas/app/functions/create_user --requires schemas/app/tables/users

Troubleshooting

References

• Related reference: references/docker.md for Docker management
• Related reference: references/env.md for environment configuration
• Related reference: references/changes.md for authoring database changes