How do I install the Database Design skill?

Run npx skillmds@latest add jmsktm/database-design in your terminal (requires Node.js), paste this page's agent-chat prompt into Claude, Cursor, or any MCP-connected agent, or download the SKILL.md file and copy it into your agent's skills directory.

What does the Database Design skill do?

Expert guide for database schema design, Supabase/PostgreSQL best practices, RLS policies, and optimizations. Use when designing tables, relationships, or implementing data models. It is listed under Coding & Dev Tools on SkillMD.

Is Database Design safe to use?

This skill has not completed SkillMD's automated safety review yet. SkillMD never runs a skill's scripts for you; review the SKILL.md before installing.

Which AI agents work with Database Design?

This skill is tagged as working with Claude Code, Claude.ai, OpenAI Codex. SKILL.md is an open format, so most agents that read a skills directory can load it too.

Is Database Design free to use?

Yes. Installing skills from SkillMD is free, and the skill stays under its author's original license.

Who published Database Design?

jmsktm (@jmsktm) published this skill. Their other Agent Skills are listed on their SkillMD profile.

Database Design by jmsktm — Agent Skill

Name: Database Design
Author: jmsktm

Database Design Skill

Comprehensive guide for designing efficient, scalable database schemas for Next.js applications with Supabase and PostgreSQL. From table design and relationships to Row Level Security policies and query optimization, this skill covers everything needed for robust data modeling.

Design normalized schemas, implement secure RLS policies, optimize query performance with proper indexing, and build maintainable data models that scale with your application.

Core Workflows

Workflow 1: Schema Design from Requirements

Purpose: Translate business requirements into database schema

Steps:

Identify entities and relationships
Define primary and foreign keys
Choose appropriate data types
Add constraints and defaults
Create indexes for performance

Implementation:

-- Correct data types
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT NOT NULL,                    -- Use TEXT, not VARCHAR
  age INTEGER CHECK (age >= 0),
  balance DECIMAL(10, 2),                 -- For money
  is_active BOOLEAN DEFAULT true,
  metadata JSONB,                         -- Use JSONB for JSON
  created_at TIMESTAMPTZ DEFAULT NOW()    -- Use TIMESTAMPTZ
);

Workflow 2: Common Relationship Patterns

Purpose: Implement proper database relationships

One-to-Many:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

Many-to-Many:

CREATE TABLE students (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL
);

CREATE TABLE courses (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL
);

CREATE TABLE enrollments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  student_id UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(student_id, course_id)
);

CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);

Self-Referencing (Tree):

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  parent_id UUID REFERENCES comments(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_comments_parent ON comments(parent_id);

Workflow 3: Row Level Security (RLS)

Purpose: Secure data access at the database level

Implementation:

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

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

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

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

-- Users can delete their own data
CREATE POLICY "Users can delete own posts"
ON posts FOR DELETE
USING (auth.uid() = user_id);

-- Public read, authenticated write
CREATE POLICY "Public can read posts"
ON posts FOR SELECT
USING (true);

CREATE POLICY "Authenticated users can create posts"
ON posts FOR INSERT
WITH CHECK (auth.role() = 'authenticated');

-- Role-based access
CREATE POLICY "Admins have full access"
ON posts FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM users
    WHERE id = auth.uid() AND role = 'admin'
  )
);

Workflow 4: Indexes for Performance

Purpose: Optimize query performance

Implementation:

-- Single Column Index
CREATE INDEX idx_users_email ON users(email);

-- Composite Index
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);

-- Partial Index
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Full-Text Search Index
CREATE INDEX idx_posts_search ON posts
USING gin(to_tsvector('english', title || ' ' || content));

-- Query with full-text search
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || content)
@@ plainto_tsquery('search term');

Workflow 5: Advanced Patterns

Purpose: Implement common database patterns

Soft Deletes:

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_not_deleted ON posts(id) WHERE deleted_at IS NULL;

CREATE POLICY "Users see non-deleted posts"
ON posts FOR SELECT
USING (deleted_at IS NULL);

Audit Trail:

CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  table_name TEXT NOT NULL,
  record_id UUID NOT NULL,
  action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  old_data JSONB,
  new_data JSONB,
  user_id UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, user_id)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE NULL END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) ELSE NULL END,
    auth.uid()
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER posts_audit
AFTER INSERT OR UPDATE OR DELETE ON posts
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

Auto-update Timestamps:

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Quick Reference

Task	SQL Pattern
Create table	`CREATE TABLE name (columns)`
Add column	`ALTER TABLE name ADD COLUMN col TYPE`
Add index	`CREATE INDEX name ON table(columns)`
Enable RLS	`ALTER TABLE name ENABLE ROW LEVEL SECURITY`
Create policy	`CREATE POLICY name ON table FOR action USING (condition)`
Create function	`CREATE FUNCTION name() RETURNS type AS $$ ... $$`
Create trigger	`CREATE TRIGGER name AFTER/BEFORE action ON table`

Migrations with Supabase

# Create Migration
npx supabase migration new create_posts_table

-- supabase/migrations/20240101000000_create_posts_table.sql

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own posts"
ON posts FOR SELECT
USING (auth.uid() = user_id);

# Apply Migration
npx supabase db push

Querying with Supabase

// Select all
const { data } = await supabase.from('posts').select('*')

// Select specific columns
const { data } = await supabase.from('posts').select('id, title')

// Filter
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('user_id', userId)

// Join tables
const { data } = await supabase
  .from('posts')
  .select(`
    *,
    users (
      id,
      name,
      email
    )
  `)

// Count
const { count } = await supabase
  .from('posts')
  .select('*', { count: 'exact', head: true })

// Pagination
const { data } = await supabase
  .from('posts')
  .select('*')
  .range(0, 9)

// Order
const { data } = await supabase
  .from('posts')
  .select('*')
  .order('created_at', { ascending: false })

Best Practices

Use UUIDs: Better for distributed systems and security
Add Timestamps: Always include created_at and updated_at
Enable RLS: On all tables by default
Index Foreign Keys: Create indexes for all foreign keys
Use TIMESTAMPTZ: Not TIMESTAMP for timezone awareness
Use TEXT: Not VARCHAR in PostgreSQL
Use JSONB: Not JSON for better performance
Normalize First: Denormalize only for performance
Migrations Only: Never modify schema directly in production
Test RLS Policies: Verify with different user contexts
Use Transactions: For related operations
Document Schema: Maintain ERD diagrams

Constraints

-- Primary Key
id UUID PRIMARY KEY DEFAULT uuid_generate_v4()

-- Foreign Key
user_id UUID REFERENCES users(id) ON DELETE CASCADE

-- Unique
email TEXT UNIQUE NOT NULL

-- Check
price DECIMAL CHECK (price > 0)
status TEXT CHECK (status IN ('active', 'inactive'))

-- Not Null
name TEXT NOT NULL

Dependencies

# Supabase CLI for migrations
npm install -D supabase

# Generate types from schema
npx supabase gen types typescript --local > types/database.ts

Error Handling

Foreign Key Violations: Handle gracefully in application
Unique Constraints: Provide user-friendly error messages
Check Constraints: Validate in application before insert
RLS Errors: Return 404 instead of 403 for security
Connection Limits: Use connection pooling

Performance Tips

Use connection pooling (PgBouncer)
Add covering indexes for common queries
Use LIMIT and cursor pagination
Avoid SELECT * in production
Use prepared statements
Monitor slow queries with pg_stat_statements
Vacuum tables regularly
Consider table partitioning for large datasets

When to Use This Skill

Invoke this skill when:

Designing new database tables
Setting up relationships
Creating RLS policies
Optimizing query performance
Implementing soft deletes
Setting up audit trails
Writing migrations
Debugging database issues
Choosing data types
Creating indexes

Database Design

File contents

Database Design Skill

Core Workflows

Workflow 1: Schema Design from Requirements

Workflow 2: Common Relationship Patterns

Workflow 3: Row Level Security (RLS)

Workflow 4: Indexes for Performance

Workflow 5: Advanced Patterns

Quick Reference

Migrations with Supabase

Querying with Supabase

Best Practices

Constraints

Dependencies

Error Handling

Performance Tips

When to Use This Skill

Frequently asked questions

Database Design

File contents

Database Design Skill

Core Workflows

Workflow 1: Schema Design from Requirements

Workflow 2: Common Relationship Patterns

Workflow 3: Row Level Security (RLS)

Workflow 4: Indexes for Performance

Workflow 5: Advanced Patterns

Quick Reference

Migrations with Supabase

Querying with Supabase

Best Practices

Constraints

Dependencies

Error Handling

Performance Tips

When to Use This Skill

Related skills

Frequently asked questions

How do I install the Database Design skill?

What does the Database Design skill do?

Is Database Design safe to use?

Which AI agents work with Database Design?

Is Database Design free to use?

Who published Database Design?