database-schema-generator

name	database-schema-generator
description	Generate PostgreSQL/Supabase database schemas from natural language descriptions. Use when creating new databases, adding tables to existing projects, or designing data models. Includes RLS policies, TypeScript types, and migration files.
license	MIT

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)