FitTrack/supabase_setup.sql at main · Karushna/FitTrack · GitHub

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
-- Run this in Supabase SQL Editor.

create table if not exists profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  name text not null,
  email text not null,
  location text default 'Colombo, Sri Lanka',
  created_at timestamp with time zone default now()
);

create table if not exists activities (
  id bigint generated always as identity primary key,
  user_id uuid references profiles(id) on delete cascade,
  user_name text not null,
  type text not null,
  distance_km double precision default 0,
  minutes integer default 0,
  image_url text,
  created_at timestamp with time zone default now()
);

create table if not exists routes (
  id bigint generated always as identity primary key,
  user_id uuid references profiles(id) on delete cascade,
  name text not null,
  location text,
  distance_km double precision default 0,
  difficulty text default 'Easy',
  image_url text,
  created_at timestamp with time zone default now()
);

create table if not exists challenges (
  id bigint generated always as identity primary key,
  user_id uuid references profiles(id) on delete cascade,
  title text not null,
  target_km double precision default 10,
  progress_km double precision default 0,
  created_at timestamp with time zone default now()
);

create table if not exists challenge_members (
  id bigint generated always as identity primary key,
  challenge_id bigint references challenges(id) on delete cascade,
  user_id uuid references profiles(id) on delete cascade,
  created_at timestamp with time zone default now(),
  unique(challenge_id, user_id)
);

alter table profiles enable row level security;
alter table activities enable row level security;
alter table routes enable row level security;
alter table challenges enable row level security;
alter table challenge_members enable row level security;

drop policy if exists "profiles_select_own" on profiles;
drop policy if exists "profiles_insert_own" on profiles;
drop policy if exists "profiles_update_own" on profiles;

create policy "profiles_select_own"
on profiles for select
using (auth.uid() = id);

create policy "profiles_insert_own"
on profiles for insert
with check (auth.uid() = id);

create policy "profiles_update_own"
on profiles for update
using (auth.uid() = id);

drop policy if exists "activities_select_authenticated" on activities;
drop policy if exists "activities_insert_own" on activities;
drop policy if exists "activities_update_own" on activities;
drop policy if exists "activities_delete_own" on activities;

create policy "activities_select_authenticated"
on activities for select
using (auth.role() = 'authenticated');

create policy "activities_insert_own"
on activities for insert
with check (auth.uid() = user_id);

create policy "activities_update_own"
on activities for update
using (auth.uid() = user_id);

create policy "activities_delete_own"
on activities for delete
using (auth.uid() = user_id);

drop policy if exists "routes_select_authenticated" on routes;
drop policy if exists "routes_insert_own" on routes;
drop policy if exists "routes_update_own" on routes;
drop policy if exists "routes_delete_own" on routes;

create policy "routes_select_authenticated"
on routes for select
using (auth.role() = 'authenticated');

create policy "routes_insert_own"
on routes for insert
with check (auth.uid() = user_id);

create policy "routes_update_own"
on routes for update
using (auth.uid() = user_id);

create policy "routes_delete_own"
on routes for delete
using (auth.uid() = user_id);

drop policy if exists "challenges_select_authenticated" on challenges;
drop policy if exists "challenges_insert_own" on challenges;
drop policy if exists "challenges_update_authenticated" on challenges;
drop policy if exists "challenges_delete_own" on challenges;

create policy "challenges_select_authenticated"
on challenges for select
using (auth.role() = 'authenticated');

create policy "challenges_insert_own"
on challenges for insert
with check (auth.uid() = user_id);

create policy "challenges_update_authenticated"
on challenges for update
using (auth.role() = 'authenticated');

create policy "challenges_delete_own"
on challenges for delete
using (auth.uid() = user_id);

drop policy if exists "challenge_members_select_authenticated" on challenge_members;
drop policy if exists "challenge_members_insert_own" on challenge_members;
drop policy if exists "challenge_members_delete_own" on challenge_members;

create policy "challenge_members_select_authenticated"
on challenge_members for select
using (auth.role() = 'authenticated');

create policy "challenge_members_insert_own"
on challenge_members for insert
with check (auth.uid() = user_id);

create policy "challenge_members_delete_own"
on challenge_members for delete
using (auth.uid() = user_id);