generate-database-migration-rls.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
# Generate Database Migration with RLS Create a comprehensive PostgreSQL migration for Supabase with the following requirements: ## Requirements ### 1. Table Schema **Create tables with:**- UUID primary keys using `gen_random_uuid()`- Foreign key constraints with appropriate ON DELETE actions- NOT NULL constraints where appropriate- Default values for common fields- CHECK constraints for data validation- UNIQUE constraints for unique fields **Standard columns to include:**```sqlid UUID PRIMARY KEY DEFAULT gen_random_uuid()user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADEcreated_at TIMESTAMPTZ DEFAULT NOW()updated_at TIMESTAMPTZ DEFAULT NOW()``` ### 2. Indexes Create indexes for:- Foreign keys- Fields used in WHERE clauses- Fields used in ORDER BY clauses- Composite indexes for common queries- GIN indexes for JSONB/array columns- Full-text search indexes if needed ### 3. Row Level Security (RLS) **Enable RLS:**```sqlALTER TABLE table_name ENABLE ROW LEVEL SECURITY;``` **Create policies for:**- Public read access (if applicable)- Authenticated user access- User-specific data access (users can only access their own data)- Admin access (admins can access all data)- Role-based access control **Common policy patterns:**```sql-- Public readCREATE POLICY "Public can view" ON table_name FOR SELECT USING (true); -- User owns recordCREATE POLICY "Users can manage own records" ON table_name FOR ALL USING (auth.uid() = user_id); -- Admin accessCREATE POLICY "Admin full access" ON table_name FOR ALL USING ( (SELECT is_admin FROM users WHERE id = auth.uid()) );``` ### 4. Triggers and Functions **Updated_at trigger:**```sqlCREATE OR REPLACE FUNCTION update_updated_at_column()RETURNS TRIGGER AS $$BEGIN NEW.updated_at = NOW(); RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER update_table_name_updated_at BEFORE UPDATE ON table_name FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();``` **Additional triggers:**- Automatic slug generation- Data validation- Cascade operations- Audit logging- Counter updates ### 5. Migration File Structure ```sql-- ============================================-- Migration: YYYYMMDDHHMMSS_description-- Purpose: Brief description of changes-- ============================================ -- Step 1: Create tables-- Step 2: Create indexes-- Step 3: Enable RLS-- Step 4: Create policies-- Step 5: Create triggers-- Step 6: Insert seed data (optional) -- Rollback instructions (as comments)``` ## Example Request ```Table: blog_postsColumns: id, user_id, title, slug, content, tags[], published, created_at, updated_atAccess: Public can read published posts, users can CRUD their own posts, admins can do everythingFeatures: Auto-update updated_at, unique slug, tag indexing``` ## Deliverables 1. **Migration file** (`supabase/migrations/YYYYMMDDHHMMSS_create_table.sql`) - Complete table definitions - All indexes - RLS policies - Triggers and functions 2. **Documentation** - Schema diagram (text format) - Access control matrix - Index usage explanation 3. **Rollback script** (as comments) - Steps to reverse the migration - Order of operations ## Best Practices - Use descriptive constraint names- Add comments for complex logic- Keep migrations atomic (one logical change per file)- Test rollback procedures- Document breaking changes- Use IF NOT EXISTS where appropriate- Follow naming conventions: - Tables: snake_case, plural - Columns: snake_case - Indexes: idx_table_column - Foreign keys: fk_table_column - Constraints: check_table_column Generate production-ready SQL that follows PostgreSQL and Supabase best practices.
MarkdownMarkdown