database-changes

Holo00's avatarfrom Holo00

Make database schema changes in IdeaForge. Triggers: create migration, add table/column, modify column type, add index, use JSONB, use pgvector. File-based migrations with raw SQL, no ORM.

⭐0stars🔀0forks📁View on GitHub🕐Updated Nov 28, 2025
[SQL and Databases]

When & Why to Use This Skill

This Claude skill automates and standardizes database schema migrations for the IdeaForge platform. It enables developers to efficiently create idempotent SQL scripts for adding tables, columns, and indexes, while supporting advanced PostgreSQL features like JSONB and pgvector. By integrating post-migration steps such as updating TypeScript interfaces and repository mappings, it ensures end-to-end data consistency and reduces manual errors in the development workflow.

Use Cases

  • Scenario 1: Initializing new database tables with idempotent SQL patterns to ensure safe deployment across different environments.
  • Scenario 2: Extending existing schemas by adding new columns or indexes without disrupting current data structures using 'IF NOT EXISTS' logic.
  • Scenario 3: Implementing AI-driven features by configuring pgvector extensions and managing high-dimensional embedding columns for vector search.
  • Scenario 4: Transitioning to flexible data storage by incorporating JSONB columns to handle evolving metadata and unstructured data requirements.
  • Scenario 5: Maintaining full-stack type safety by identifying and applying necessary updates to TypeScript interfaces and repository mapping logic following a schema change.
namedatabase-changes
descriptionMake database schema changes in IdeaForge. Triggers: create migration, add table/column, modify column type, add index, use JSONB, use pgvector. File-based migrations with raw SQL, no ORM.

Database Migrations

Location: backend/db/migrations/

Create Migration

  1. Create NNN_description.sql (next sequential number)
  2. Write idempotent SQL
  3. Run npm run db:migrate

Patterns

-- New table
CREATE TABLE IF NOT EXISTS user_preferences (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  theme VARCHAR(50) DEFAULT 'light',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add column
ALTER TABLE ideas ADD COLUMN IF NOT EXISTS new_field TEXT;

-- JSONB column
ALTER TABLE ideas ADD COLUMN IF NOT EXISTS quick_notes JSONB;

-- Index
CREATE INDEX IF NOT EXISTS idx_ideas_domain ON ideas(domain);

-- pgvector
CREATE EXTENSION IF NOT EXISTS vector;
ALTER TABLE ideas ADD COLUMN IF NOT EXISTS embedding vector(1536);

After Migration

  1. Update backend/src/types/index.ts - add interface properties
  2. Update backend/src/repositories/*Repository.ts - update mapFromDb

Gotchas

  • Always IF NOT EXISTS - idempotent migrations
  • No rollback - fix failures manually
  • Check state first - \d tablename in psql
  • JSONB for flexible data - evolving structures