@avvale/aurora-tools-scripts

---
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

```typescript
{
  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

```typescript
{
  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

```typescript
{
  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)

```typescript
{
  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

```typescript
{
  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

```bash
# 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](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