Repository navigation
Expand file tree
/
Copy pathsupabase-schema.sql
More file actions
378 lines (378 loc) · 15.4 KB
/
Copy pathsupabase-schema.sql
File metadata and controls
378 lines (378 loc) · 15.4 KB
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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
-- ============================================
-- StudyFlow Database Schema
-- Run this in your Supabase SQL Editor:
-- https://supabase.com/dashboard/project/_/sql
-- ============================================
-- Enable UUID extension (usually already enabled)
create extension if not exists "uuid-ossp";
-- ============================================
-- 1. PROFILES TABLE
-- ============================================
create table if not exists public.profiles (
id uuid references auth.users on delete cascade primary key,
full_name text,
avatar_url text,
study_hours_goal integer default 4,
timezone text default 'UTC',
created_at timestamptz default now(),
updated_at timestamptz default now()
);
alter table public.profiles enable row level security;
create policy "Users can view own profile" on public.profiles for
select using (auth.uid() = id);
create policy "Users can insert own profile" on public.profiles for
insert with check (auth.uid() = id);
create policy "Users can update own profile" on public.profiles for
update using (auth.uid() = id);
-- Auto-create profile on user signup
create or replace function public.handle_new_user() returns trigger as $$ begin
insert into public.profiles (id, full_name, avatar_url)
values (
new.id,
new.raw_user_meta_data->>'full_name',
new.raw_user_meta_data->>'avatar_url'
);
return new;
end;
$$ language plpgsql security definer;
-- Drop trigger if exists and recreate
drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
after
insert on auth.users for each row execute procedure public.handle_new_user();
-- ============================================
-- 2. SUBJECTS TABLE
-- ============================================
create table if not exists public.subjects (
id uuid default uuid_generate_v4() primary key,
user_id uuid references auth.users on delete cascade not null,
name text not null,
color text default '#3b82f6',
icon text default 'book',
priority integer default 3 check (
priority >= 1
and priority <= 5
),
target_hours_per_week numeric default 5,
description text,
syllabus_url text,
syllabus_text text,
created_at timestamptz default now(),
updated_at timestamptz default now()
);
alter table public.subjects enable row level security;
create policy "Users can view own subjects" on public.subjects for
select using (auth.uid() = user_id);
create policy "Users can insert own subjects" on public.subjects for
insert with check (auth.uid() = user_id);
create policy "Users can update own subjects" on public.subjects for
update using (auth.uid() = user_id);
create policy "Users can delete own subjects" on public.subjects for delete using (auth.uid() = user_id);
-- ============================================
-- 3. STUDY SESSIONS TABLE
-- ============================================
create table if not exists public.study_sessions (
id uuid default uuid_generate_v4() primary key,
user_id uuid references auth.users on delete cascade not null,
subject_id uuid references public.subjects on delete
set null,
title text not null,
description text,
start_time timestamptz not null,
end_time timestamptz not null,
duration_minutes integer generated always as (
extract(
epoch
from (end_time - start_time)
) / 60
) stored,
is_completed boolean default false,
is_ai_generated boolean default false,
notes text,
created_at timestamptz default now(),
updated_at timestamptz default now()
);
alter table public.study_sessions enable row level security;
create policy "Users can view own sessions" on public.study_sessions for
select using (auth.uid() = user_id);
create policy "Users can insert own sessions" on public.study_sessions for
insert with check (auth.uid() = user_id);
create policy "Users can update own sessions" on public.study_sessions for
update using (auth.uid() = user_id);
create policy "Users can delete own sessions" on public.study_sessions for delete using (auth.uid() = user_id);
-- ============================================
-- 4. TASKS TABLE
-- ============================================
create table if not exists public.tasks (
id uuid default uuid_generate_v4() primary key,
user_id uuid references auth.users on delete cascade not null,
subject_id uuid references public.subjects on delete
set null,
session_id uuid references public.study_sessions on delete
set null,
title text not null,
description text,
due_date timestamptz,
priority integer default 3 check (
priority >= 1
and priority <= 5
),
estimated_minutes integer default 30,
is_completed boolean default false,
completed_at timestamptz,
created_at timestamptz default now(),
updated_at timestamptz default now()
);
alter table public.tasks enable row level security;
create policy "Users can view own tasks" on public.tasks for
select using (auth.uid() = user_id);
create policy "Users can insert own tasks" on public.tasks for
insert with check (auth.uid() = user_id);
create policy "Users can update own tasks" on public.tasks for
update using (auth.uid() = user_id);
create policy "Users can delete own tasks" on public.tasks for delete using (auth.uid() = user_id);
-- ============================================
-- 5. POMODORO SESSIONS TABLE
-- ============================================
create table if not exists public.pomodoro_sessions (
id uuid default uuid_generate_v4() primary key,
user_id uuid references auth.users on delete cascade not null,
subject_id uuid references public.subjects on delete
set null,
task_id uuid references public.tasks on delete
set null,
duration_minutes integer default 25,
break_minutes integer default 5,
started_at timestamptz not null,
completed_at timestamptz,
is_completed boolean default false,
created_at timestamptz default now()
);
alter table public.pomodoro_sessions enable row level security;
create policy "Users can view own pomodoros" on public.pomodoro_sessions for
select using (auth.uid() = user_id);
create policy "Users can insert own pomodoros" on public.pomodoro_sessions for
insert with check (auth.uid() = user_id);
create policy "Users can update own pomodoros" on public.pomodoro_sessions for
update using (auth.uid() = user_id);
create policy "Users can delete own pomodoros" on public.pomodoro_sessions for delete using (auth.uid() = user_id);
-- ============================================
-- 6. AI CHAT MESSAGES TABLE
-- ============================================
create table if not exists public.ai_chat_messages (
id uuid default uuid_generate_v4() primary key,
user_id uuid references auth.users on delete cascade not null,
role text not null check (role in ('user', 'assistant')),
content text not null,
created_at timestamptz default now()
);
alter table public.ai_chat_messages enable row level security;
create policy "Users can view own messages" on public.ai_chat_messages for
select using (auth.uid() = user_id);
create policy "Users can insert own messages" on public.ai_chat_messages for
insert with check (auth.uid() = user_id);
create policy "Users can delete own messages" on public.ai_chat_messages for delete using (auth.uid() = user_id);
-- ============================================
-- 7. STUDY GOALS TABLE
-- ============================================
create table if not exists public.study_goals (
id uuid default uuid_generate_v4() primary key,
user_id uuid references auth.users on delete cascade not null,
subject_id uuid references public.subjects on delete
set null,
title text not null,
target_date timestamptz,
target_hours numeric,
progress_hours numeric default 0,
is_completed boolean default false,
created_at timestamptz default now(),
updated_at timestamptz default now()
);
alter table public.study_goals enable row level security;
create policy "Users can view own goals" on public.study_goals for
select using (auth.uid() = user_id);
create policy "Users can insert own goals" on public.study_goals for
insert with check (auth.uid() = user_id);
create policy "Users can update own goals" on public.study_goals for
update using (auth.uid() = user_id);
create policy "Users can delete own goals" on public.study_goals for delete using (auth.uid() = user_id);
-- ============================================
-- 8. INDEXES for performance
-- ============================================
create index if not exists idx_subjects_user_id on public.subjects(user_id);
create index if not exists idx_study_sessions_user_id on public.study_sessions(user_id);
create index if not exists idx_study_sessions_start_time on public.study_sessions(start_time);
create index if not exists idx_tasks_user_id on public.tasks(user_id);
create index if not exists idx_tasks_is_completed on public.tasks(is_completed);
create index if not exists idx_pomodoro_sessions_user_id on public.pomodoro_sessions(user_id);
create index if not exists idx_ai_chat_messages_user_id on public.ai_chat_messages(user_id);
create index if not exists idx_study_goals_user_id on public.study_goals(user_id);
-- ============================================
-- 9. Insert profile for existing users (if any)
-- ============================================
insert into public.profiles (id, full_name)
select id,
raw_user_meta_data->>'full_name'
from auth.users
where id not in (
select id
from public.profiles
) on conflict (id) do nothing;
-- ============================================
-- 10. STORAGE BUCKET for syllabus PDFs
-- ============================================
insert into storage.buckets (
id,
name,
public,
file_size_limit,
allowed_mime_types
)
values (
'syllabi',
'syllabi',
false,
10485760,
-- 10MB limit
array ['application/pdf']
) on conflict (id) do nothing;
-- Storage policies: users can manage their own files
create policy "Users can upload syllabi" on storage.objects for
insert with check (
bucket_id = 'syllabi'
and auth.uid()::text = (storage.foldername(name)) [1]
);
create policy "Users can view own syllabi" on storage.objects for
select using (
bucket_id = 'syllabi'
and auth.uid()::text = (storage.foldername(name)) [1]
);
create policy "Users can delete own syllabi" on storage.objects for delete using (
bucket_id = 'syllabi'
and auth.uid()::text = (storage.foldername(name)) [1]
);
create policy "Users can update own syllabi" on storage.objects for
update using (
bucket_id = 'syllabi'
and auth.uid()::text = (storage.foldername(name)) [1]
);
-- ============================================
-- 11. NOTES DOCUMENTS TABLE
-- ============================================
create table if not exists public.notes_documents (
id uuid default uuid_generate_v4() primary key,
user_id uuid references auth.users on delete cascade not null,
title text not null,
file_name text,
file_path text,
file_url text,
extracted_text text,
created_at timestamptz default now(),
updated_at timestamptz default now()
);
alter table public.notes_documents enable row level security;
create policy "Users can view own notes" on public.notes_documents for
select using (auth.uid() = user_id);
create policy "Users can insert own notes" on public.notes_documents for
insert with check (auth.uid() = user_id);
create policy "Users can update own notes" on public.notes_documents for
update using (auth.uid() = user_id);
create policy "Users can delete own notes" on public.notes_documents for delete using (auth.uid() = user_id);
create index if not exists idx_notes_documents_user_id on public.notes_documents(user_id);
-- ============================================
-- 12. STORAGE BUCKET for notes PDFs
-- ============================================
insert into storage.buckets (
id,
name,
public,
file_size_limit,
allowed_mime_types
)
values (
'notes',
'notes',
false,
10485760,
-- 10MB limit
array ['application/pdf']
) on conflict (id) do nothing;
create policy "Users can upload notes" on storage.objects for
insert with check (
bucket_id = 'notes'
and auth.uid()::text = (storage.foldername(name)) [1]
);
create policy "Users can view own notes" on storage.objects for
select using (
bucket_id = 'notes'
and auth.uid()::text = (storage.foldername(name)) [1]
);
create policy "Users can delete own notes" on storage.objects for delete using (
bucket_id = 'notes'
and auth.uid()::text = (storage.foldername(name)) [1]
);
create policy "Users can update own notes" on storage.objects for
update using (
bucket_id = 'notes'
and auth.uid()::text = (storage.foldername(name)) [1]
);
-- ============================================
-- 13. EXAMS TABLE
-- ============================================
create table if not exists public.exams (
id uuid default uuid_generate_v4() primary key,
user_id uuid references auth.users on delete cascade not null,
subject_id uuid references public.subjects on delete cascade not null,
title text not null,
exam_date timestamptz not null,
exam_duration_minutes integer default 120,
confidence_level integer default 3 check (
confidence_level >= 1
and confidence_level <= 5
),
notes text,
created_at timestamptz default now(),
updated_at timestamptz default now()
);
alter table public.exams enable row level security;
create policy "Users can view own exams" on public.exams for
select using (auth.uid() = user_id);
create policy "Users can insert own exams" on public.exams for
insert with check (auth.uid() = user_id);
create policy "Users can update own exams" on public.exams for
update using (auth.uid() = user_id);
create policy "Users can delete own exams" on public.exams for delete using (auth.uid() = user_id);
create index if not exists idx_exams_user_id on public.exams(user_id);
create index if not exists idx_exams_subject_id on public.exams(subject_id);
create index if not exists idx_exams_exam_date on public.exams(exam_date);
-- ============================================
-- 14. REVISION SESSIONS TABLE (for spaced repetition)
-- ============================================
create table if not exists public.revision_sessions (
id uuid default uuid_generate_v4() primary key,
user_id uuid references auth.users on delete cascade not null,
exam_id uuid references public.exams on delete cascade not null,
subject_id uuid references public.subjects on delete cascade not null,
study_session_id uuid references public.study_sessions on delete
set null,
revision_round integer default 1 check (
revision_round >= 1
and revision_round <= 3
),
scheduled_date timestamptz not null,
is_completed boolean default false,
completed_at timestamptz,
created_at timestamptz default now(),
updated_at timestamptz default now()
);
alter table public.revision_sessions enable row level security;
create policy "Users can view own revisions" on public.revision_sessions for
select using (auth.uid() = user_id);
create policy "Users can insert own revisions" on public.revision_sessions for
insert with check (auth.uid() = user_id);
create policy "Users can update own revisions" on public.revision_sessions for
update using (auth.uid() = user_id);
create policy "Users can delete own revisions" on public.revision_sessions for delete using (auth.uid() = user_id);
create index if not exists idx_revision_sessions_user_id on public.revision_sessions(user_id);
create index if not exists idx_revision_sessions_exam_id on public.revision_sessions(exam_id);
create index if not exists idx_revision_sessions_scheduled_date on public.revision_sessions(scheduled_date);