name: aurora-tools-scripts description: > SQL migrations and stored procedures management in the tools bounded context. Trigger: Creating migrations, procedures, functions, triggers, or sequences for PostgreSQL. license: MIT metadata: author: aurora version: '1.0' allowed-tools: Read, Edit, Write, Glob, Grep, Bash

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

| File | Purpose | | -------------------------------- | ----------------------------------------------------------- | | src/assets/tools/migrations.ts | Schema changes only: ALTER TABLE, CREATE INDEX, DDL | | src/assets/tools/procedures.ts | Programmable objects: procedures, functions, triggers | | src/@api/graphql.ts | Types and ToolsProcedureType enum |

Migration Object Structure

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

Procedure Object Structure

{
  id: 'UUID',
  name: string,                    // Descriptive name
  type: ToolsProcedureType,        // PROCEDURE | FUNCTION | TRIGGER
  version: string,
  sort: number,
  upScript: `SQL`,                 // Pure SQL: CREATE OR REPLACE...
  downScript: `SQL`,               // Pure SQL: DROP...
}

SQL Conventions

| Rule | Example | | ------------------------------ | -------------------- | | Table names use double quotes | "TableName" | | Always prefix with schema | public."TableName" | | Use IF EXISTS/IF NOT EXISTS | Idempotent scripts | | Environment vars interpolation | ${process.env.VAR} |

Naming Conventions

| Element | Pattern | Example | | ---------- | ---------------------- | ------------------------- | | Functions | set_{entity}_{field} | set_load_order_code | | Triggers | trg_{function_name} | trg_set_load_order_code | | Sequences | {purpose}_seq | load_order_code_seq | | Indexes | {table_snake}_{col} | message_outbox_row_id | | Local vars | v_ prefix | v_code | | Counters | t_ prefix | t_counter |

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

{
  id: '98978f96-b617-469f-b80c-2a30c5516f47',
  name: 'Add defaultRedirection to IamRole',
  version: '0.0.6',
  sort: 8,
  upScript: `
    ALTER TABLE public."IamRole" ADD COLUMN "defaultRedirection" VARCHAR(2046) NULL;
  `,
  downScript: `
    ALTER TABLE public."IamRole" DROP COLUMN IF EXISTS "defaultRedirection";
  `,
}

Migration: Add rowId (Aurora Standard Pattern)

{
  id: 'bf177b46-6671-410b-bf49-4ce97c29884e',
  name: 'Add rowId to MessageOutbox',
  version: '0.0.5',
  sort: 5,
  upScript: `
    ALTER TABLE public."MessageOutbox" ADD COLUMN "rowId" BIGINT GENERATED BY DEFAULT AS IDENTITY;
    WITH ordered AS (
        SELECT id AS uuid_pk, ROW_NUMBER() OVER (ORDER BY "createdAt", id) AS rn FROM public."MessageOutbox"
    )
    UPDATE public."MessageOutbox" t
        SET "rowId" = o.rn
    FROM ordered o
        WHERE t.id = o.uuid_pk
            AND t."rowId" IS NULL;

    SELECT setval(pg_get_serial_sequence('public."MessageOutbox"','rowId'), (SELECT MAX("rowId") FROM public."MessageOutbox"), true);

    ALTER TABLE public."MessageOutbox" ALTER COLUMN "rowId" SET NOT NULL;
    CREATE UNIQUE INDEX message_outbox_row_id ON public."MessageOutbox" USING btree ("rowId");
  `,
  downScript: `
    DROP INDEX IF EXISTS "message_outbox_row_id";
    ALTER TABLE public."MessageOutbox" DROP COLUMN IF EXISTS "rowId";
  `,
}

Procedure: Basic

{
  id: '1cd0c79e-b83b-4ebf-b112-063669703cdc',
  name: 'insert_user',
  type: ToolsProcedureType.PROCEDURE,
  version: '0.0.1',
  sort: 1,
  upScript: `
CREATE OR REPLACE PROCEDURE insert_user(name_input VARCHAR, age_input INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO users (name, age) VALUES (name_input, age_input);
END;
$$;
  `,
  downScript: `DROP PROCEDURE IF EXISTS insert_user(VARCHAR, INTEGER);`,
}

Commands

# Generate UUID for new entry
uuidgen | tr '[:upper:]' '[:lower:]'

# Or in Node.js
node -e "console.log(crypto.randomUUID())"

# Check current package version
jq -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

Aurora SQL Migrations Management