abcnuts/database-schema-generator

Database Schema Generator

Generate production-ready PostgreSQL/Supabase database schemas from natural language descriptions using AI.

Overview

This skill transforms natural language descriptions into complete, production-ready database schemas with:

• PostgreSQL/Supabase SQL - Clean, executable SQL
• Row Level Security (RLS) - Security policies by default
• TypeScript Types - Auto-generated from schema
• Migration Files - Version-controlled schema changes
• Best Practices - UUIDs, timestamps, indexes, constraints
• Validation - Syntax and structure checking
• Direct Application - Apply to Supabase via MCP

When to Use This Skill

• Creating a new database for a project
• Adding tables to an existing database
• Designing data models from requirements
• Learning database design patterns
• Generating TypeScript types from database schema
• Creating migration files for version control

Quick Start

Generate a Schema

python3 scripts/generate_schema.py "A blog with users, posts, and comments"

Generate with TypeScript Types

python3 scripts/generate_schema.py "E-commerce with products and orders" --output-format types

Apply to Supabase

# Generate schema
python3 scripts/generate_schema.py "Task management app" --output-file schema.sql

# Validate
python3 scripts/validate_schema.py schema.sql

# Apply to Supabase
python3 scripts/apply_schema.py schema.sql --project-id YOUR_PROJECT_ID

Workflow

Step 1: Describe Your Database

Write a natural language description of what you need:

Simple:

"A blog with users, posts, and comments"

Detailed:

"A task management app with:
- Users with roles (admin, member)
- Projects that users can join
- Tasks within projects with assignees
- Comments on tasks
- File attachments on tasks"

Domain-Specific:

"An e-commerce platform with:
- Products with variants (size, color)
- Shopping cart
- Orders with line items
- Payment tracking
- Inventory management"

Step 2: Generate Schema

Run the generator:

python3 scripts/generate_schema.py "<your description>" --output-file schema.sql

Options:

• --output-format sql - Raw SQL (default)
• --output-format supabase - Supabase SQL Editor format
• --output-format migration - Migration file format
• --output-format types - TypeScript types only
• --include-rls - Include RLS policies (default: true)
• --no-rls - Exclude RLS policies
• --include-seed - Include seed data
• --output-file <path> - Save to file

Step 3: Validate Schema

Check for errors and best practices:

python3 scripts/validate_schema.py schema.sql

The validator checks:

• ✅ SQL syntax
• ✅ Balanced parentheses
• ✅ Common patterns (timestamps, UUIDs, indexes)
• ✅ Best practices (RLS, foreign keys)
• ✅ Table and index structure

Step 4: Apply to Supabase

Apply the schema to your Supabase database:

python3 scripts/apply_schema.py schema.sql --project-id YOUR_PROJECT_ID

Options:

• --dry-run - Show what would be executed without running
• --confirm - Skip confirmation prompt

Step 5: Generate TypeScript Types

Generate TypeScript types for your frontend:

python3 scripts/generate_schema.py "<description>" --output-format types --output-file types.ts

Examples

Example 1: Simple Blog

Description:

"A blog with users, posts, and comments"

Generated Schema (excerpt):

CREATE TABLE users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- Indexes
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- RLS Policies
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view published posts"
  ON posts FOR SELECT
  USING (status = 'published');

Example 2: E-Commerce

Description:

"E-commerce with products, orders, and customers"

Command:

python3 scripts/generate_schema.py "E-commerce with products, orders, and customers" \
  --include-seed \
  --output-file ecommerce_schema.sql

Example 3: SaaS Application

Description:

"A SaaS app with:
- Organizations (workspaces)
- Users belong to organizations with roles
- Projects within organizations
- Tasks within projects
- Billing and subscriptions per organization"

Command:

python3 scripts/generate_schema.py "A SaaS app with organizations, users with roles, projects, tasks, and billing" \
  --output-format migration \
  --output-file migrations/001_initial_schema.sql

Output Formats

SQL (Default)

Clean, executable PostgreSQL SQL:

CREATE TABLE users (...);
CREATE INDEX idx_users_email ON users(email);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

Supabase

Includes Supabase-specific header and extensions:

-- Supabase SQL Editor
-- Paste this into the Supabase SQL Editor and run

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (...);

Migration

Version-controlled migration file format:

-- Migration: E-commerce schema
-- Created: 2026-02-08 14:30:00

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (...);

TypeScript Types

Type-safe TypeScript interfaces:

export interface User {
  id: string;
  email: string;
  full_name: string | null;
  created_at: Date;
  updated_at: Date;
}

export interface Post {
  id: string;
  user_id: string;
  title: string;
  content: string | null;
  status: 'draft' | 'published';
  created_at: Date;
  updated_at: Date;
}

Templates

Base Schema Template

Use templates/base_schema.sql for common patterns:

• Users table with Supabase Auth integration
• Timestamps with auto-update triggers
• Soft delete support
• RLS policy examples
• Index patterns
• Foreign key patterns

Copy patterns from the template into your own schemas.

Best Practices

The generator follows these best practices automatically:

1. Use UUIDs for Primary Keys

id UUID PRIMARY KEY DEFAULT gen_random_uuid()

Why: Security, no collisions, client-side generation

2. Include Timestamps

created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()

Why: Audit trail, debugging, sorting

3. Add Indexes on Foreign Keys

CREATE INDEX idx_posts_user_id ON posts(user_id);

Why: Join performance

4. Use Check Constraints for Enums

status TEXT CHECK (status IN ('draft', 'published', 'archived'))

Why: Data validation at database level

5. Enable Row Level Security

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Why: Security by default (Supabase requirement)

6. Use Soft Deletes

deleted_at TIMESTAMPTZ

Why: Data recovery, audit trail

7. Add Foreign Key Constraints

user_id UUID REFERENCES users(id) ON DELETE CASCADE

Why: Data integrity, automatic cleanup

Common Patterns

User-Owned Resources

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  ...
);

-- RLS: Users can only access their own posts
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

Many-to-Many Relationships

CREATE TABLE post_tags (
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Hierarchical Data (Nested Comments)

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_id UUID REFERENCES comments(id) ON DELETE CASCADE,
  ...
);

Status Tracking

status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed'))

Validation

The validator checks for:

Errors (Must Fix)

• Empty SQL file
• Unbalanced parentheses
• Invalid syntax

Warnings (Should Fix)

• Missing semicolons
• No CREATE TABLE statements

Recommendations (Consider)

• Add timestamps (created_at, updated_at)
• Use UUIDs for primary keys
• Add indexes for performance
• Add foreign key constraints
• Enable Row Level Security

Applying to Supabase

Via SQL Editor (Manual)

1. Generate schema:

   python3 scripts/generate_schema.py "..." --output-format supabase
   

2. Copy output

3. Paste into Supabase SQL Editor

4. Run

Via MCP (Automated)

1. Generate schema:

   python3 scripts/generate_schema.py "..." --output-file schema.sql
   

2. Apply to Supabase:

   python3 scripts/apply_schema.py schema.sql --project-id YOUR_PROJECT_ID
   

3. Verify in Supabase dashboard

Troubleshooting

Issue: "Invalid SQL syntax"

Solution: Run validator to identify specific errors:

python3 scripts/validate_schema.py schema.sql

Issue: "MCP command failed"

Solution: Check Supabase project ID and MCP configuration:

manus-mcp-cli tool list --server supabase

Issue: "RLS policies not working"

Solution: Verify RLS is enabled:

ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

Issue: "Foreign key constraint violation"

Solution: Ensure parent records exist before inserting child records, or use ON DELETE CASCADE.

Advanced Usage

Custom Prompts

For more control, modify the SYSTEM_PROMPT in scripts/generate_schema.py:

SYSTEM_PROMPT = """You are an expert database architect...

Additional requirements:
- Use JSONB for flexible data
- Add full-text search indexes
- Include audit triggers
"""

Combining with Existing Schemas

Generate new tables and merge with existing schema:

# Generate new tables
python3 scripts/generate_schema.py "Add notifications table" --output-file new_tables.sql

# Review and merge manually
cat existing_schema.sql new_tables.sql > combined_schema.sql

Migration Workflow

1. Generate initial schema:

   python3 scripts/generate_schema.py "..." --output-format migration --output-file migrations/001_initial.sql
   

2. Apply to database:

   python3 scripts/apply_schema.py migrations/001_initial.sql --project-id YOUR_PROJECT_ID
   

3. For changes, generate new migration:

   python3 scripts/generate_schema.py "Add notifications" --output-format migration --output-file migrations/002_add_notifications.sql
   

Reference Files

• references/schema_patterns.md - Common database patterns and best practices
• templates/base_schema.sql - Reusable schema patterns

Success Criteria

✅ Schema generates without errors
✅ Validation passes
✅ Schema applies to Supabase successfully
✅ RLS policies work as expected
✅ TypeScript types match database schema
✅ Indexes improve query performance

Time Savings

• Manual schema design: 1-2 hours
• With this skill: 5-10 minutes
• Savings: ~1.5 hours per database

Next Steps

After generating your schema:

1. ✅ Apply to Supabase
2. ✅ Generate TypeScript types
3. ✅ Test RLS policies
4. ✅ Add seed data (if needed)
5. ✅ Build API endpoints (use api-endpoint-builder skill)
6. ✅ Write tests (use testing-framework skill)