database-changes
IdeaForge中创建数据库架构变更。触发器:创建迁移、添加表/列、修改列类型、添加索引、使用JSONB、使用pgvector。基于文件的迁移,使用原始SQL,无ORM。
📖 中文导读:IdeaForge中创建数据库架构变更。触发器:创建迁移、添加表/列、修改列类型、添加索引、使用JSONB、使用pgvector。基于文件的迁移,使用原始SQL,无ORM。 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。
name: database-changes description: 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.
Database Migrations
Location: backend/db/migrations/
Create Migration
- Create
NNN_description.sql(next sequential number) - Write idempotent SQL
- 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
- Update
backend/src/types/index.ts- add interface properties - Update
backend/src/repositories/*Repository.ts- update mapFromDb
Gotchas
- Always IF NOT EXISTS - idempotent migrations
- No rollback - fix failures manually
- Check state first -
\d tablenamein psql - JSONB for flexible data - evolving structures