-
Notifications
You must be signed in to change notification settings - Fork 5
/
schema.sql
291 lines (260 loc) · 15 KB
/
schema.sql
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
-- create extension "uuid-ossp" with schema extensions;
/**
* USERS
* Note: This table contains user data. Users should only be able to update their own data.
*/
create type public.user_status as enum ('BASIC', 'VERIFIED');
create type public.page_link_preference as enum ('PATH', 'SUBDOMAIN', 'CUSTOM');
create table if not exists public.users (
-- UUID from auth.users
id uuid primary key references auth.users on delete cascade,
email varchar(255) unique not null,
username varchar(20) unique not null,
full_name varchar(100),
biography varchar(120),
avatar_url text default 'https://luvvzhalwfofocddxfrk.supabase.in/storage/v1/object/public/avatars/default-avatar.jpg'::text,
status user_status default 'BASIC'::public.user_status,
page_link page_link_preference default 'PATH'::public.page_link_preference,
is_banned boolean default false,
onboarding_process boolean default true,
inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
constraint username_length check (char_length(username) >= 3)
);
alter table public.users enable row level security;
create policy "Allow public read-only access." on public.users for select using (true);
create policy "Can update own user data." on public.users for update using (auth.uid() = id);
create unique index users_username_idx on public.users(username);
/**
* This trigger automatically creates a user entry when a new user signs up via Supabase Auth.
*/
create or replace function public.handle_new_user()
returns trigger as $$
declare
_username text;
begin
-- check to see if 'user_name` is not null, if is assign a random 'user_name'
-- used for external providers that do not offer the 'user_name' by default
if (new.raw_user_meta_data->>'user_name') is null then
_username := extensions.hash_encode(123456789, new.email, 10);
else
_username := new.raw_user_meta_data->>'user_name';
end if;
insert into public.users (id, email, username, full_name, avatar_url)
values (new.id, new.email, _username, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();
/**
* This trigger automatically updates `public.users.email` when a user changes his
* email via Supabase Auth.
*/
create or replace function public.handle_email_update()
returns trigger as $$
begin
update public.users set email = new.email
where id = new.id;
return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_email_change
after update on auth.users
for each row when (old.email is distinct from new.email)
execute procedure public.handle_email_update();
/**
* This fn checks the current password of an auth.user before updating the new password. The fn
* is required because Supabase Auth API doesn't provide a way to know what the current password
* of a user is. Used with `supabaseClient.rpc()`
*/
create or replace function change_user_password(current_plain_password varchar, new_plain_password varchar)
returns json
as $$
DECLARE
_uid uuid; -- for checking 'User is not found'
user_id uuid; -- to store the user id from the request
BEGIN
-- not empty
IF (new_plain_password = '') IS NOT FALSE THEN
RAISE EXCEPTION 'New password is empty!';
-- minimum 8 chars
ELSIF char_length(new_plain_password) < 8 THEN
RAISE EXCEPTION 'New password must be at least 8 characters long!';
-- maximum 8 chars
ELSIF char_length(new_plain_password) > 40 THEN
RAISE EXCEPTION 'New password must be at most 40 characters long!';
END IF;
-- Get user by his current auth.uid and current password
user_id := auth.uid();
SELECT id INTO _uid
FROM auth.users
WHERE id = user_id
AND encrypted_password =
crypt(current_plain_password::text, auth.users.encrypted_password);
-- Check the currect password
IF NOT FOUND THEN
RAISE EXCEPTION 'Old password is incorrect!';
END IF;
-- Then set the new password
UPDATE auth.users SET
encrypted_password =
crypt(new_plain_password, gen_salt('bf'))
WHERE id = user_id;
RETURN '{"data":true}';
END;
$$ language plpgsql security definer;
/**
* This function deletes the current user and all it's data by cascading
* to all the tables that references `auth.users.id`
*/
create or replace function delete_user_account() returns void as $$
delete from auth.users where id = auth.uid();
$$ language sql security definer;
/**
* Themes
* Note: This table contains page themes. Users should only be able to view.
*/
create type public.theme_kind as enum ('SYSTEM', 'CUSTOM');
create type public.theme_state as enum ('PUBLISHED', 'PRIVATE');
create table if not exists public.themes (
id uuid primary key default uuid_generate_v4(),
name varchar(50) unique not null,
style jsonb not null,
kind theme_kind default 'SYSTEM'::public.theme_kind,
state theme_state default 'PUBLISHED'::public.theme_state,
owner uuid,
inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
constraint owner_theme_key foreign key(owner) references public.users(id) on delete cascade
);
alter table public.themes enable row level security;
create policy "Allow public read-only access." on public.themes for select using (true);
create policy "Can insert when authenticated." on public.themes for insert with check (auth.role() = 'authenticated');
/**
* PAGES
* Note: This table contains page data. Users should only be able to update the table and the public
* should only have read-only access.
*/
create type public.page_link_position as enum ('BOTTOM', 'TOP');
create table if not exists public.pages (
id uuid primary key default uuid_generate_v4(),
-- UUID from public.users, cascading
user_id uuid not null,
theme varchar(50) default 'Cirrus'::text,
title varchar(60),
subdomain varchar(50) unique not null,
custom_domain varchar(180) unique,
seo_title varchar(55),
seo_description varchar(180),
integrations jsonb not null,
nsfw_content boolean default false not null,
show_branding boolean default true not null,
social_link_position page_link_position default 'TOP'::public.page_link_position,
inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
constraint user_id_page_key foreign key(user_id) references public.users(id) on delete cascade,
constraint theme_page_key foreign key(theme) references public.themes(name) on delete set null
);
alter table public.pages enable row level security;
create policy "Allow public read-only access." on public.pages for select using (true);
create policy "Can update own page data." on public.pages for update using (auth.uid() = user_id);
/**
* This trigger automatically creates a page entry when a new user is created in public.users
*/
create or replace function public.handle_new_page()
returns trigger as $$
begin
insert into public.pages (user_id, title, subdomain, integrations)
values (new.id, 'My Page', new.username, '{"google_analytics_id":null}');
return new;
end;
$$ language plpgsql security definer;
create trigger on_public_user_insert
after insert on public.users
for each row execute procedure public.handle_new_page();
/**
* This trigger automatically updates `public.pages.subdomain` when a user changes his
* username.
*/
create or replace function public.handle_page_subdomain_update()
returns trigger as $$
begin
update public.pages set subdomain = new.username
where user_id = new.id;
return new;
end;
$$ language plpgsql security definer;
create trigger on_user_username_change
after update on public.users
for each row when (old.username is distinct from new.username)
execute procedure public.handle_page_subdomain_update();
/**
* Links
* Note: This table contains page links. Users should only be able to view and update their own links.
*/
create type public.link_kind as enum ('LINK', 'EMBED');
create table if not exists public.links (
id uuid primary key default uuid_generate_v4(),
-- UUID from public.users, cascading
user_id uuid not null,
title varchar(50) not null,
url text not null,
thumbnail_url text,
visible boolean default true not null,
metadata jsonb,
display_order int default 9999,
total_clicks int default 0,
kind link_kind default 'LINK'::public.link_kind,
inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
constraint user_id_link_key foreign key(user_id) references public.users(id) on delete cascade
);
alter table public.links enable row level security;
create index links_userId_idx on public.links(user_id);
create policy "Allow public read-only access." on public.links for select using (true);
create policy "Can update own link data" on public.links for update using (auth.uid() = user_id);
create policy "Can delete own link data" on public.links for delete using (auth.uid() = user_id);
create policy "Can insert when authenticated" on public.links for insert with check (auth.role() = 'authenticated');
/**
* Updates all rows order according to the passed in JSON. Wraps update...from syntax
*/
create or replace function update_links_order(payload json) returns setof public.links as $$
update public.links as l set display_order = x.display_order
from (
select id, display_order from json_populate_recordset(null::public.links, payload)
) as x
where l.id = x.id
returning l.*;
$$ language sql;
/**
* Storage
* Note: This sets up storage. Anyone can view avatars, but only authenticated can upload!
*/
insert into storage.buckets (id, name)
values ('avatars', 'avatars');
create policy "Allow public read-only access."
on storage.objects for select
using ( bucket_id = 'avatars' );
create policy "Can upload when authenticated"
on storage.objects for insert
with check ( bucket_id = 'avatars' and auth.role() = 'authenticated' );
create policy "Can delete it's own avatar"
on storage.objects for delete
using ( bucket_id = 'avatars' and auth.uid()::text = (storage.foldername(name))[1]);
/**
* Default Inserts
* Note: Following section contains default inserts for the DB
*/
insert into public.themes (name, kind, state, style)
values
('Cirrus', 'SYSTEM', 'PUBLISHED', '{"button":{"css":"background-color: hsl(300 20.0% 99.0%); border-width: 0px; border-color: hsl(300 20.0% 99.0%); border-radius: 9999px; box-shadow: 0px 6px 14px -6px rgba(24, 39, 75, 0.12), 0px 10px 32px -4px rgba(24, 39, 75, 0.1), inset 0px 0px 2px 1px rgba(24, 39, 75, 0.05);"},"background":{"css":"background-color: hsl(300 20.0% 99.0%);"},"text":{"css":"color: hsl(0 0% 0%);"},"font":{"css":"font-weight: 400; font-family: Poppins, sans-serif;","font_size_lead":"font-size: 20px;","font_size_text":"font-size: 16px;","font_size_button":"font-size: 15px;"}}'),
('Minimal Blue', 'SYSTEM', 'PUBLISHED', '{"button":{"css":"background-color: transparent; border-width: 2px; border-color: hsl(252 4.0% 57.3%); border-radius: 9999px;"},"background":{"css":"background-color: hsl(209 95.0% 90.1%);"},"text":{"css":"color: hsl(216, 22%, 27%);"},"font":{"css":"font-weight: 400;","font_size_lead":"font-size: 21px;","font_size_text":"font-size: 17px;","font_size_button":"font-size: 16px;"}}'),
('Minimal Green', 'SYSTEM', 'PUBLISHED', '{"button":{"css":"background-color: transparent; border-width: 2px; border-color: hsl(252 4.0% 57.3%); border-radius: 9999px;"},"background":{"css":"background-color: hsl(122 42.6% 86.5%);"},"text":{"css":"color: hsl(216, 22%, 27%);"},"font":{"css":"font-weight: 400;","font_size_lead":"font-size: 21px;","font_size_text":"font-size: 17px;","font_size_button":"font-size: 16px;"}}'),
('Minimal Orange', 'SYSTEM', 'PUBLISHED', '{"button":{"css":"background-color: transparent; border-width: 2px; border-color: hsl(252 4.0% 57.3%); border-radius: 9999px;"},"background":{"css":"background-color: hsl(25 100% 88.2%);"},"text":{"css":"color: hsl(216, 22%, 27%);"},"font":{"css":"font-weight: 400;","font_size_lead":"font-size: 21px;","font_size_text":"font-size: 17px;","font_size_button":"font-size: 16px;"}}'),
('Carbon', 'SYSTEM', 'PUBLISHED', '{"button":{"css":"background-color: hsl(243 4.9% 18.8%); border-width: 1px; border-color: hsl(245 4.9% 25.4%); border-radius: 6px;"},"background":{"css":"background-color: hsl(240 5.1% 11.6%);"},"text":{"css":"color: hsl(300 20.0% 99.0%);"},"font":{"css":"font-weight: 500;","font_size_lead":"font-size: 21px;","font_size_text":"font-size: 17px;","font_size_button":"font-size: 16px;"}}'),
('Retro', 'SYSTEM', 'PUBLISHED', '{"button":{"css":"background-color: hsl(25, 100%, 95%); border-width: 3px; border-color: hsl(216, 22%, 27%); border-radius: 5px;"},"background":{"css":"background-color: hsl(25, 100%, 95%);"},"text":{"css":"color: hsl(216, 22%, 27%);"},"font":{"css":"font-weight: 400; font-family: VT323, monospace;","font_size_lead":"font-size: 24px;","font_size_text":"font-size: 20px;","font_size_button":"font-size: 19px;"}}'),
('New York', 'SYSTEM', 'PUBLISHED', '{"button":{"css":"background-color: transparent; border-width: 3px; border-color: hsl(300 20.0% 99.0%); border-radius: 4px;"},"background":{"css":"background: hsla(347, 92%, 85%, 1) linear-gradient(0deg, hsla(347, 92%, 85%, 1) 0%, hsla(251, 82%, 70%, 1) 100%);"},"text":{"css":"color: hsl(300 20.0% 99.0%);"},"font":{"css":"font-weight: 500;","font_size_lead":"font-size: 21px;","font_size_text":"font-size: 17px;","font_size_button":"font-size: 16px;"}}'),
('Cypher', 'SYSTEM', 'PUBLISHED', '{"button":{"css":"background-color: transparent; border-width: 3px; border-color: hsl(300 20.0% 99.0%); border-radius: 5px;"},"background":{"css":"background: hsla(192, 80%, 51%, 1) linear-gradient(180deg, hsla(192, 80%, 51%, 1) 0%, hsla(355, 85%, 63%, 1) 100%);"},"text":{"css":"color: hsl(300 20.0% 99.0%);"},"font":{"css":"font-weight: 500;","font_size_lead":"font-size: 21px;","font_size_text":"font-size: 17px;","font_size_button":"font-size: 16px;"}}'),
('Polymorph', 'SYSTEM', 'PUBLISHED', '{"button":{"css":"background-color: rgba(196, 196, 196, 0.01); border-width: 0px; border-color: hsl(300 20.0% 99.0%); border-radius: 9999px; box-shadow: inset 0px 17.7895px 25.5438px -16.421px rgba(255, 255, 255, 0.2), inset 0px -5.92982px 4.10526px -6.38596px rgba(255, 255, 255, 0.3), inset 0px 3.19298px 5.01754px -1.82456px #FFFFFF, inset 0px -37.4035px 31.0175px -29.193px rgba(96, 68, 145, 0.3), inset 0px 44.7017px 45.614px -21.8947px rgba(202, 172, 255, 0.14), inset 0px 1.82456px 8.21052px rgba(154, 146, 210, 0.3), inset 0px 0.45614px 18.2456px rgba(227, 222, 255, 0.12);"},"background":{"css":"background: hsla(202, 98%, 20%, 1) linear-gradient(180deg, hsla(202, 98%, 20%, 1) 0%, hsla(176, 30%, 56%, 1) 100%);"},"text":{"css":"color: hsl(300 20.0% 99.0%);"},"font":{"css":"font-weight: 500; font-family: Orbitron, sans-serif;","font_size_lead":"font-size: 19px;","font_size_text":"font-size: 15px;","font_size_button":"font-size: 14px;"}}');