Agent skill

aurora-tools-scripts

SQL migrations and stored procedures management in the tools bounded context. Trigger: Creating migrations, procedures, functions, triggers, or sequences for PostgreSQL.

View SKILL.md on GitHub Repository
Stars 163
Forks 31

Install this agent skill to your Project

npx add-skill https://github.com/majiayu000/claude-skill-registry/tree/main/skills/data/aurora-tools-scripts

Metadata

Additional technical details for this skill

author
aurora
version
1.0

SKILL.md

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 for copy-paste templates
  • Migrations: See src/assets/tools/migrations.ts for real examples
  • Procedures: See src/assets/tools/procedures.ts for procedure examples

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

majiayu000/claude-skill-registry

agent-ops-spec

Manage specification documents in .agent/specs/. Use when user provides requirements, acceptance criteria, or feature descriptions that need to be tracked and validated against implementation.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-state

Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-spec

Manage specification documents in .agent/specs/. Use when user provides requirements, acceptance criteria, or feature descriptions that need to be tracked and validated against implementation.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-testing

Test strategy, execution, and coverage analysis. Use when designing tests, running test suites, or analyzing test results beyond baseline checks.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-testing

Test strategy, execution, and coverage analysis. Use when designing tests, running test suites, or analyzing test results beyond baseline checks.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-state

Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.

163 31
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results