Required Companion Skill

IMPORTANT: Always use this skill together with postgresql skill.

• This skill defines WHERE to put the code (migrations.ts vs procedures.ts)
• The postgresql skill defines HOW to write the SQL statements

Read .claude/skills/postgresql/SKILL.md before writing any SQL.

When to Use

migrations.ts - Schema Changes (DDL)

Use for database schema modifications:

• ALTER TABLE (add/drop/modify columns)
• CREATE/DROP INDEX
• Add constraints (UNIQUE, FOREIGN KEY, CHECK)
• Add rowId pattern to existing tables
• Any structural change to tables

procedures.ts - Programmable Objects

Use for stored logic and automation:

• Stored procedures (PROCEDURE)
• Functions (FUNCTION)
• Triggers (TRIGGER)
• Sequences (via FUNCTION that creates them)

Critical Patterns

File Structure

Migration Object Structure

typescript
1{
2  id: 'UUID',           // Generate with crypto.randomUUID()
3  name: string,         // English description: "Verb + Object + Table"
4  version: string,      // Semver matching package.json, e.g., '0.0.7'
5  sort: number,         // Execution order within version
6  upScript: `SQL`,      // Pure PostgreSQL SQL in template literal
7  downScript: `SQL`,    // Exact inverse of upScript
8}

Procedure Object Structure

typescript
1{
2  id: 'UUID',
3  name: string,                    // Descriptive name
4  type: ToolsProcedureType,        // PROCEDURE | FUNCTION | TRIGGER
5  version: string,
6  sort: number,
7  upScript: `SQL`,                 // Pure SQL: CREATE OR REPLACE...
8  downScript: `SQL`,               // Pure SQL: DROP...
9}

SQL Conventions

Naming Conventions

Decision Tree

What do you need?
│
├─ Schema/Structure change (DDL)?
│   └─ YES → migrations.ts
│       ├─ ALTER TABLE (add/drop/modify columns)
│       ├─ CREATE/DROP INDEX
│       ├─ ADD CONSTRAINT
│       └─ Any table structure change
│
└─ Programmable logic?
    └─ YES → procedures.ts
        ├─ Stored procedure (no return)     → type: PROCEDURE
        ├─ Function (returns value/table)   → type: FUNCTION
        ├─ Trigger + trigger function       → type: TRIGGER
        └─ Sequence (created via function)  → type: FUNCTION

Code Examples

Migration: Add Column

typescript
1{
2  id: '98978f96-b617-469f-b80c-2a30c5516f47',
3  name: 'Add defaultRedirection to IamRole',
4  version: '0.0.6',
5  sort: 8,
6  upScript: `
7    ALTER TABLE public."IamRole" ADD COLUMN "defaultRedirection" VARCHAR(2046) NULL;
8  `,
9  downScript: `
10    ALTER TABLE public."IamRole" DROP COLUMN IF EXISTS "defaultRedirection";
11  `,
12}

Migration: Add rowId (Aurora Standard Pattern)

typescript
1{
2  id: 'bf177b46-6671-410b-bf49-4ce97c29884e',
3  name: 'Add rowId to MessageOutbox',
4  version: '0.0.5',
5  sort: 5,
6  upScript: `
7    ALTER TABLE public."MessageOutbox" ADD COLUMN "rowId" BIGINT GENERATED BY DEFAULT AS IDENTITY;
8    WITH ordered AS (
9        SELECT id AS uuid_pk, ROW_NUMBER() OVER (ORDER BY "createdAt", id) AS rn FROM public."MessageOutbox"
10    )
11    UPDATE public."MessageOutbox" t
12        SET "rowId" = o.rn
13    FROM ordered o
14        WHERE t.id = o.uuid_pk
15            AND t."rowId" IS NULL;
16
17    SELECT setval(pg_get_serial_sequence('public."MessageOutbox"','rowId'), (SELECT MAX("rowId") FROM public."MessageOutbox"), true);
18
19    ALTER TABLE public."MessageOutbox" ALTER COLUMN "rowId" SET NOT NULL;
20    CREATE UNIQUE INDEX message_outbox_row_id ON public."MessageOutbox" USING btree ("rowId");
21  `,
22  downScript: `
23    DROP INDEX IF EXISTS "message_outbox_row_id";
24    ALTER TABLE public."MessageOutbox" DROP COLUMN IF EXISTS "rowId";
25  `,
26}

Procedure: Basic

typescript
1{
2  id: '1cd0c79e-b83b-4ebf-b112-063669703cdc',
3  name: 'insert_user',
4  type: ToolsProcedureType.PROCEDURE,
5  version: '0.0.1',
6  sort: 1,
7  upScript: `
8CREATE OR REPLACE PROCEDURE insert_user(name_input VARCHAR, age_input INTEGER)
9LANGUAGE plpgsql
10AS $$
11BEGIN
12    INSERT INTO users (name, age) VALUES (name_input, age_input);
13END;
14$$;
15  `,
16  downScript: `DROP PROCEDURE IF EXISTS insert_user(VARCHAR, INTEGER);`,
17}

Commands

bash
1# Generate UUID for new entry
2uuidgen | tr '[:upper:]' '[:lower:]'
3
4# Or in Node.js
5node -e "console.log(crypto.randomUUID())"
6
7# Check current package version
8jq -r '.version' package.json

Resources

• Templates: See assets/templates.ts for copy-paste templates
• Migrations: See src/assets/tools/migrations.ts for real examples
• Procedures: See src/assets/tools/procedures.ts for procedure examples