WorldCrafter Database Setup

Version: 2.0.0 Last Updated: 2025-01-09

This skill provides tools and guidance for setting up database tables with Prisma ORM, implementing Row-Level Security (RLS) policies, and managing database migrations in WorldCrafter.

Skill Metadata

Related Skills:

• worldcrafter-feature-builder - Use after database is created to build UI layer
• worldcrafter-auth-guard - Use to implement RLS policies and auth checks
• worldcrafter-test-generator - Use to test database operations

Example Use Cases:

• "Create a BlogPost table with title, content, and author" → Generates Prisma model, creates migration, sets up RLS policies for author-only access
• "Add a Comment model that belongs to posts" → Creates Comment model with post relationship, migration, and RLS for authenticated users
• "Add a tagging system for worlds and characters" → Creates Tag and EntityTag models with polymorphic relationships, migration, and RLS policies
• "Create activity logging for all CRUD operations" → Creates ActivityLog model with action tracking, migration, and RLS for read access

When to Use This Skill

Use this skill when:

• Adding new database tables to the application
• Creating relationships between existing tables
• Setting up Row-Level Security (RLS) policies
• Running database migrations
• Syncing schema changes to test database
• Implementing database triggers
• Troubleshooting database schema issues

Database Setup Process

Phase 1: Design Database Schema

1. Understand data requirements

   • What data needs to be stored?
   • What are the relationships with existing tables?
   • What fields are required vs optional?
   • What constraints are needed (unique, foreign keys)?
   • Reference references/prisma-patterns.md for common patterns

2. Plan Row-Level Security

   • Who should be able to read this data?
   • Who should be able to create/update/delete?
   • Reference references/rls-policies.md for policy templates

Phase 2: Create Prisma Model

Manual Approach:

1. Open prisma/schema.prisma
2. Add new model following WorldCrafter conventions:
   • Model name: PascalCase (e.g., BlogPost)
   • Table name: snake_case with @@map("table_name")
   • Fields: camelCase in model, snake_case in DB with @map("field_name")
   • Always include id, createdAt, updatedAt

Automated Approach: Use the scaffolding script to generate the model interactively:

python .claude/skills/worldcrafter-database-setup/scripts/generate_model.py

This will:

• Prompt for model name
• Generate model with standard fields
• Add to schema.prisma
• Follow all WorldCrafter conventions

Reference assets/templates/model-template.prisma for examples.

Phase 3: Create Database Migration

Development workflow (quick iteration):

npx prisma db push

• Pushes schema directly to database
• No migration file created
• Good for rapid prototyping

Production workflow (recommended):

npx prisma migrate dev --name add_table_name

• Creates migration file in prisma/migrations/
• Maintains migration history
• Regenerates Prisma Client automatically
• Required for production deployments

Migration naming conventions:

• add_users_table - New table
• add_user_role_field - New field
• update_users_constraints - Constraint changes
• add_users_rls_policies - RLS policies

Phase 4: Set Up Row-Level Security (RLS)

RLS is critical for security - it enforces database-level access control so users can only access their own data.

Generate RLS policies:

python .claude/skills/worldcrafter-database-setup/scripts/generate_rls.py <table_name>

This generates SQL for:

• Enabling RLS on the table
• Common policy templates (read own, write own, etc.)
• Saves to prisma/migrations/sql/rls_<table_name>.sql

Apply RLS policies:

npm run db:rls

Manual RLS setup:

1. Create SQL file in prisma/migrations/<timestamp>_add_<table>_rls/migration.sql
2. Add RLS policies (reference references/rls-policies.md)
3. Run npm run db:rls to apply

Common RLS patterns (from references/rls-policies.md):

-- Enable RLS
ALTER TABLE public.table_name ENABLE ROW LEVEL SECURITY;

-- Users can read own data
CREATE POLICY "Users can read own data"
  ON public.table_name
  FOR SELECT
  USING (auth.uid() = user_id);

-- Users can update own data
CREATE POLICY "Users can update own data"
  ON public.table_name
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Users can insert own data
CREATE POLICY "Users can insert own data"
  ON public.table_name
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Public read, authenticated write
CREATE POLICY "Anyone can read"
  ON public.table_name
  FOR SELECT
  USING (true);

CREATE POLICY "Authenticated users can create"
  ON public.table_name
  FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = user_id);

Phase 5: Sync to Test Database

After creating migrations, sync to test database:

npm run db:test:sync

Or with seeding:

npm run db:test:sync -- --seed

This ensures integration tests run against the latest schema.

Phase 6: Verify and Test

1. Verify schema changes:

   npx prisma studio
   

   Opens GUI to view database structure

2. Verify Prisma Client types:

   import { prisma } from '@/lib/prisma'
   
   // TypeScript should autocomplete new model
   const result = await prisma.yourNewModel.findMany()
   

3. Test RLS policies:

   • Create integration test
   • Verify users can only access their own data
   • Test all CRUD operations

4. Run type checking:

   npm run build
   

Common Database Patterns

Basic Model

model BlogPost {
  id        String   @id @default(cuid())
  title     String
  content   String
  published Boolean  @default(false)
  authorId  String   @map("author_id")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  author User @relation(fields: [authorId], references: [id], onDelete: Cascade)

  @@map("blog_posts")
}

One-to-Many Relationship

model User {
  id    String @id
  posts BlogPost[] // One user has many posts
}

model BlogPost {
  id       String @id @default(cuid())
  authorId String @map("author_id")

  author User @relation(fields: [authorId], references: [id], onDelete: Cascade)

  @@map("blog_posts")
}

Many-to-Many Relationship

model Post {
  id   String @id @default(cuid())
  tags PostTag[]
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts PostTag[]
}

// Junction table
model PostTag {
  postId String @map("post_id")
  tagId  String @map("tag_id")

  post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag  Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([postId, tagId])
  @@map("post_tags")
}

Enum Fields

enum UserRole {
  USER
  ADMIN
  MODERATOR
}

model User {
  id   String   @id
  role UserRole @default(USER)
}

JSON Fields

model UserSettings {
  id       String @id @default(cuid())
  userId   String @unique @map("user_id")
  metadata Json?  // Optional JSON field

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("user_settings")
}

Database Scripts Reference

Generate Model (Interactive)

python .claude/skills/worldcrafter-database-setup/scripts/generate_model.py

Generate RLS Policies

python .claude/skills/worldcrafter-database-setup/scripts/generate_rls.py <table_name>

Sync Databases

python .claude/skills/worldcrafter-database-setup/scripts/sync_databases.py

Prisma Commands Reference

# Push schema to dev database (no migration file)
npx prisma db push

# Create migration
npx prisma migrate dev --name migration_name

# Apply migrations to production
npx prisma migrate deploy

# Reset database (WARNING: deletes all data)
npx prisma migrate reset

# Regenerate Prisma Client
npx prisma generate

# Open Prisma Studio GUI
npx prisma studio

# Format schema file
npx prisma format

# Validate schema
npx prisma validate

WorldCrafter Database Commands

# Apply RLS policies
npm run db:rls

# Sync schema to test database
npm run db:test:sync

# Push schema to test database
npm run db:test:push

# Seed test database
npm run db:test:seed

# Open Prisma Studio
npm run db:studio

Troubleshooting

Migration fails

• Check syntax in prisma/schema.prisma
• Run npx prisma validate
• Check database connection strings in .env
• Ensure DIRECT_DATABASE_URL (port 5432) is used for migrations

Type errors after schema changes

• Run npx prisma generate to regenerate client
• Restart TypeScript server in IDE
• Run npm run build to verify

RLS policies not working

• Verify RLS is enabled: ALTER TABLE ... ENABLE ROW LEVEL SECURITY
• Check policy conditions match your use case
• Test with psql or Supabase SQL editor
• Verify auth.uid() is returning expected user ID

Test database out of sync

• Run npm run db:test:sync to resync
• Check .env.test has correct database URL
• Verify test database is separate from dev database

Reference Files

• references/prisma-patterns.md - Detailed Prisma schema patterns
• references/worldcrafter-complete-schema.md - Complete WorldCrafter PRD schema (relationships, tags, comments, activity logs, versions, members, collections, wiki, bookmarks)
• references/rls-policies.md - RLS policy templates and examples (includes all WorldCrafter tables)
• references/migration-workflow.md - Migration best practices
• references/related-skills.md - How this skill works with other WorldCrafter skills
• assets/templates/model-template.prisma - Model templates
• assets/templates/rls-template.sql - RLS SQL templates

Skill Orchestration

This skill is typically the FIRST step in feature development, providing the data layer foundation.

Common Workflows

Database-First Feature Development:

1. worldcrafter-database-setup (this skill) - Create tables, migrations, RLS policies
2. worldcrafter-feature-builder - Build UI layer with forms and Server Actions
3. worldcrafter-auth-guard - Enhance auth checks (RLS provides base security)

Schema Evolution:

1. worldcrafter-database-setup (this skill) - Modify existing schema
2. Update existing feature code to use new schema
3. worldcrafter-test-generator - Add tests for schema changes

Data Model Only:

1. worldcrafter-database-setup (this skill) - Create data model
2. UI built later when needed

When Claude Should Use Multiple Skills

Claude will orchestrate database-setup with other skills when:

• User wants a "complete feature" → database-setup first, then feature-builder
• User mentions "authentication" AND "database" → database-setup for tables, auth-guard for protection
• User wants "database with tests" → database-setup first, then test-generator

Example orchestration:

User: "Create a blog post system with user authentication"

Claude's workflow:
1. worldcrafter-database-setup (this skill):
   - Create BlogPost model with authorId
   - Add RLS: users can only edit their own posts
   - Generate migration

2. worldcrafter-feature-builder:
   - Create blog post form
   - Create Server Actions using BlogPost model
   - Add validation

3. worldcrafter-auth-guard:
   - Add auth checks to blog routes
   - Ensure only authenticated users can create posts

Skill Selection Guidance

Choose this skill when:

• User explicitly mentions "database", "table", "model", "schema"
• User describes data that needs to be stored
• Feature needs persistent data storage
• User asks about RLS or migrations

Choose worldcrafter-feature-builder instead when:

• User wants complete feature without mentioning database specifically
• Feature-builder can create simple features and call database-setup if needed

Use this skill FIRST when:

• Building new feature from scratch
• Data model must be designed before UI
• RLS policies need to be planned upfront

Success Criteria

A complete database setup includes:

• ✅ Prisma model with proper naming conventions
• ✅ Migration file created and applied
• ✅ RLS policies enabled and configured
• ✅ Test database synced
• ✅ Prisma Client regenerated
• ✅ Type checking passes (npm run build)
• ✅ Integration tests verify RLS policies work
• ✅ Database visible in Prisma Studio