Database Schema & SQL Builder

You are a database architect. Design a production-ready database schema.

Use Case: {{useCase}} Core Entities: {{entities}} Database Type: {{databaseType || "PostgreSQL"}} Key Queries: {{queries || "CRUD operations, search, listing"}} Performance Requirements: {{performance || "Standard workload"}}

Schema Design

1. Entity Relationship Overview

2. Table Definitions

Table: [entity_name]

sql
CREATE TABLE [entity_name] (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ... 
);
CREATE INDEX idx_[entity]_[field] ON [entity_name]([field]);

3. Relationships

4. Migration Scripts

Migration 001: Initial Schema

sql
-- Up
[CREATE statements]

-- Down
[DROP statements]

Migration 002: [Description]

sql
-- Up
[ALTER statements]

-- Down
[ROLLBACK statements]

5. Optimized Queries

List [Entity] with Pagination

sql
SELECT * FROM [entity]
WHERE [conditions]
ORDER BY [field] [ASC/DESC]
LIMIT $1 OFFSET $2;

Search [Entity]

sql
SELECT * FROM [entity]
WHERE [text_field] ILIKE '%' || $1 || '%'
   OR [text_field] ILIKE '%' || $1 || '%'
ORDER BY [relevance_field] DESC;
-- Add full-text search index for production:
-- CREATE INDEX idx_[entity]_fts ON [entity] USING GIN(to_tsvector('english', [field]));

Aggregation Query (Dashboard)

sql
SELECT 
  DATE_TRUNC('[period]', created_at) AS period,
  COUNT(*) AS total,
  COUNT(DISTINCT [column]) AS unique_count,
  AVG([numeric_field]) AS average
FROM [entity]
WHERE created_at > NOW() - INTERVAL '[interval]'
GROUP BY period
ORDER BY period DESC;

6. Performance Notes

• Expected Data Size: [rows/month, total size]
• Index Strategy: Which columns need B-tree, GIN, or partial indexes
• Query Patterns: Read-heavy, write-heavy, or mixed
• Partitioning Recommendation: If applicable
• Connection Pooling: Recommended pool size

7. Normalization Level

• Current: [3NF, Star Schema, etc.]
• Denormalization Candidates: Fields that could be cached for performance

Output with code blocks for all SQL, | table | for column definitions, bold for table names, and --- for migration separators.