Schema Design Skill

Design comprehensive, normalized database schemas for Supabase applications.

Purpose

Create well-structured database schemas following best practices for normalization, relationships, constraints, and indexing.

When to Use

• User describes data requirements
• Requests database schema design
• Needs entity relationship modeling
• Asks about table structure
• Plans new feature requiring data storage

Instructions

1. Gather Requirements

   • Identify all entities
   • Understand relationships
   • Determine data constraints
   • Plan for future growth

2. Design Tables

   • Choose appropriate column types
   • Add NOT NULL constraints
   • Define CHECK constraints
   • Include timestamps

3. Map Relationships

   • One-to-many with foreign keys
   • Many-to-many with junction tables
   • Self-referential if needed

4. Add Indexes

   • Primary keys (automatic)
   • Foreign keys
   • Frequently queried columns
   • Composite indexes for multi-column queries

5. Implement RLS

   • Enable on all tables
   • Create policies for each operation
   • Test policy effectiveness

6. Generate Migration

   • Complete SQL DDL
   • Include all constraints
   • Add helpful comments

Example Output

-- Users and Posts Schema
-- =======================

CREATE TABLE public.users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  username TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,

  CONSTRAINT username_length CHECK (char_length(username) >= 3)
);

CREATE TABLE public.posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,

  CONSTRAINT title_length CHECK (char_length(title) >= 3)
);

CREATE INDEX idx_posts_author ON public.posts(author_id);
CREATE INDEX idx_posts_published ON public.posts(published, created_at DESC)
  WHERE published = true;

ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Published posts viewable by all"
  ON public.posts FOR SELECT
  USING (published = true);

Output Format

1. Complete schema SQL
2. ER diagram description
3. Explanation of design decisions
4. Migration file