create-migration

When to Use

Use this skill when:

• Adding new database tables
• Adding or removing columns
• Modifying constraints or indexes
• Any database schema change is required

Step 1: Generate Migration Timestamp

GNU/Linux:

date +%s%3N

macOS/BSD (alternative):

python3 -c "import time; print(int(time.time() * 1000))"

Note: The date +%s%3N command works on GNU/Linux systems but not on macOS/BSD due to differences in the date command. macOS users should use the Python one-liner instead.

Example output: 1753957404053

Step 2: Create Migration Directory

Create the migration folder with the timestamp:

mkdir -p backend/migrations/default/{timestamp}_{action}_{description}

Replace:

• {timestamp} with the generated timestamp
• {action} with the action type (e.g., add_column, remove_column, create_table)
• {description} with a snake_case description (e.g., scheduled_at_to_mail_log)

Step 3: Create up.sql

Create up.sql with the schema changes:

Adding a Column

ALTER TABLE "public"."TableName" ADD COLUMN "columnName" text;

Removing a Column

ALTER TABLE "public"."TableName" DROP COLUMN "columnName";

Creating a Table

CREATE TABLE "public"."TableName" (
  "id" serial NOT NULL,
  "name" text NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "updated_at" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);
COMMENT ON TABLE "public"."TableName" IS 'Brief description of the table purpose';

Note: Always include a COMMENT ON TABLE statement to document the table's purpose. This helps maintain database documentation and makes the schema more understandable for future developers.

Step 4: Create down.sql

Create down.sql to reverse the changes:

If up.sql adds a column

ALTER TABLE "public"."TableName" DROP COLUMN "columnName";

If up.sql removes a column

ALTER TABLE "public"."TableName" ADD COLUMN "columnName" text;

If up.sql creates a table

DROP TABLE "public"."TableName";

Step 5: Update Hasura Metadata (If Needed)

For new tables or relationships, create/update metadata files in: backend/metadata/databases/default/tables/

See the database-table-creation.md rule for metadata templates.

Step 6: Regenerate TypeScript Types (MANDATORY)

After ANY schema change that affects GraphQL, regenerate types using the regenerate-types skill.

Note: Hasura must be running at http://localhost:8080/v1/graphql for this to work. See the regenerate-types skill for the complete command and troubleshooting steps.

Naming Conventions

• Tables: PascalCase (UserProfile, CourseEnrollment)
• Columns: camelCase (firstName, organizationId)
• Timestamps: snake_case (created_at, updated_at)
• Foreign keys: {tableName}Id (userId, courseId)
• Migration folders: {timestamp}_{action}_{description} (1753957404053_add_column_status_to_mail_log)

Checklist

Before considering a schema-change task complete:

• [ ] Migration up.sql created with correct SQL
• [ ] Migration down.sql created to reverse changes
• [ ] Hasura metadata updated (if adding tables/relationships)
• [ ] TypeScript types regenerated
• [ ] GraphQL queries updated (if field names changed)