- Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathdatabase-setup.sql
More file actions
Latest commit
88 lines (79 loc) · 3.59 KB
/
Copy pathdatabase-setup.sql
File metadata and controls
88 lines (79 loc) · 3.59 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
-- ============================================================
-- STEP 1: Clean up any existing tables
-- ============================================================
DROPTABLE IF EXISTS public.notes CASCADE;
DROPTABLE IF EXISTS public.notebooks CASCADE;
DROPTABLE IF EXISTS public.profiles CASCADE;
DROPTRIGGER IF EXISTS on_auth_user_created ONauth.users;
DROPFUNCTION IF EXISTS public.handle_new_user();
DROPFUNCTION IF EXISTS public.update_updated_at_column();
-- ============================================================
-- STEP 2: Create tables (user_id is TEXT = Google's user ID)
-- ============================================================
CREATETABLEpublic.profiles (
id TEXTPRIMARY KEY,
email TEXT,
full_name TEXT,
avatar_url TEXT,
created_at TIMESTAMP WITH TIME ZONENOT NULL DEFAULT now(),
updated_at TIMESTAMP WITH TIME ZONENOT NULL DEFAULT now()
);
CREATETABLEpublic.notebooks (
id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
user_id TEXTNOT NULLREFERENCESpublic.profiles(id) ON DELETE CASCADE,
title TEXTNOT NULL,
sort_order INTEGER DEFAULT 0,
is_archived BOOLEANNOT NULL DEFAULT false,
is_deleted BOOLEANNOT NULL DEFAULT false,
deleted_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONENOT NULL DEFAULT now(),
updated_at TIMESTAMP WITH TIME ZONENOT NULL DEFAULT now()
);
CREATETABLEpublic.notes (
id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
notebook_id UUID NOT NULLREFERENCESpublic.notebooks(id) ON DELETE CASCADE,
user_id TEXTNOT NULLREFERENCESpublic.profiles(id) ON DELETE CASCADE,
title TEXTNOT NULL,
content TEXT DEFAULT '',
is_archived BOOLEANNOT NULL DEFAULT false,
is_deleted BOOLEANNOT NULL DEFAULT false,
deleted_at TIMESTAMP WITH TIME ZONE,
sort_order INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONENOT NULL DEFAULT now(),
updated_at TIMESTAMP WITH TIME ZONENOT NULL DEFAULT now()
);
-- ============================================================
-- STEP 3: Indexes
-- ============================================================
CREATEINDEXidx_notebooks_user_idONpublic.notebooks(user_id);
CREATEINDEXidx_notebooks_archivedONpublic.notebooks(user_id, is_archived);
CREATEINDEXidx_notebooks_deletedONpublic.notebooks(user_id, is_deleted);
CREATEINDEXidx_notes_notebook_idONpublic.notes(notebook_id);
CREATEINDEXidx_notes_user_idONpublic.notes(user_id);
CREATEINDEXidx_notes_archivedONpublic.notes(user_id, is_archived);
CREATEINDEXidx_notes_deletedONpublic.notes(user_id, is_deleted);
-- ============================================================
-- STEP 4: Auto-update timestamps
-- ============================================================
CREATE OR REPLACEFUNCTIONpublic.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at= now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATETRIGGERupdate_profiles_updated_at
BEFORE UPDATEONpublic.profiles
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATETRIGGERupdate_notebooks_updated_at
BEFORE UPDATEONpublic.notebooks
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATETRIGGERupdate_notes_updated_at
BEFORE UPDATEONpublic.notes
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
-- ============================================================
-- STEP 5: Disable RLS (no Supabase Auth = no auth.uid())
-- ============================================================
ALTERTABLEpublic.profiles DISABLE ROW LEVEL SECURITY;
ALTERTABLEpublic.notebooks DISABLE ROW LEVEL SECURITY;
ALTERTABLEpublic.notes DISABLE ROW LEVEL SECURITY;