Aurora SQL Migrations Management

---

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