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
| create table public.books (
user_id uuid not null,
book_hash text not null,
format text null,
title text null,
source_title text null,
author text null,
"group" text null,
tags text[] null,
metadata text null,
created_at timestamp with time zone null default now(),
updated_at timestamp with time zone null default now(),
deleted_at timestamp with time zone null,
uploaded_at timestamp with time zone null,
progress integer[] null,
group_id text null,
group_name text null,
constraint books_pkey primary key (user_id, book_hash),
constraint books_user_id_fkey foreign KEY (user_id) references auth.users (id) on delete CASCADE
) TABLESPACE pg_default;
ALTER TABLE public.books ENABLE ROW LEVEL SECURITY;
CREATE POLICY select_books ON public.books
FOR SELECT to authenticated USING ((select auth.uid()) = user_id);
CREATE POLICY insert_books ON public.books
FOR INSERT to authenticated WITH CHECK ((select auth.uid()) = user_id);
CREATE POLICY update_books ON public.books
FOR UPDATE to authenticated USING ((select auth.uid()) = user_id);
CREATE POLICY delete_books ON public.books
FOR DELETE to authenticated USING ((select auth.uid()) = user_id);
create table public.book_configs (
user_id uuid not null,
book_hash text not null,
location text null,
progress jsonb null,
search_config jsonb null,
view_settings jsonb null,
created_at timestamp with time zone null default now(),
updated_at timestamp with time zone null default now(),
deleted_at timestamp with time zone null,
constraint book_configs_pkey primary key (user_id, book_hash),
constraint book_configs_user_id_fkey foreign KEY (user_id) references auth.users (id) on delete CASCADE
) TABLESPACE pg_default;
ALTER TABLE public.book_configs ENABLE ROW LEVEL SECURITY;
CREATE POLICY select_book_configs ON public.book_configs
FOR SELECT to authenticated USING ((select auth.uid()) = user_id);
CREATE POLICY insert_book_configs ON public.book_configs
FOR INSERT to authenticated WITH CHECK ((select auth.uid()) = user_id);
CREATE POLICY update_book_configs ON public.book_configs
FOR UPDATE to authenticated USING ((select auth.uid()) = user_id);
CREATE POLICY delete_book_configs ON public.book_configs
FOR DELETE to authenticated USING ((select auth.uid()) = user_id);
create table public.book_notes (
user_id uuid not null,
book_hash text not null,
id text not null,
type text null,
cfi text null,
text text null,
style text null,
color text null,
note text null,
created_at timestamp with time zone null default now(),
updated_at timestamp with time zone null default now(),
deleted_at timestamp with time zone null,
constraint book_notes_pkey primary key (user_id, book_hash, id),
constraint book_notes_user_id_fkey foreign KEY (user_id) references auth.users (id) on delete CASCADE
) TABLESPACE pg_default;
ALTER TABLE public.book_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY select_book_notes ON public.book_notes
FOR SELECT to authenticated USING ((select auth.uid()) = user_id);
CREATE POLICY insert_book_notes ON public.book_notes
FOR INSERT to authenticated WITH CHECK ((select auth.uid()) = user_id);
CREATE POLICY update_book_notes ON public.book_notes
FOR UPDATE to authenticated USING ((select auth.uid()) = user_id);
CREATE POLICY delete_book_notes ON public.book_notes
FOR DELETE to authenticated USING ((select auth.uid()) = user_id);
create table public.files (
id uuid not null default gen_random_uuid (),
user_id uuid not null,
book_hash text null,
file_key text not null,
file_size bigint not null,
created_at timestamp with time zone null default now(),
deleted_at timestamp with time zone null,
constraint files_pkey primary key (id),
constraint files_file_key_key unique (file_key),
constraint files_user_id_fkey foreign KEY (user_id) references auth.users (id) on delete CASCADE
) TABLESPACE pg_default;
create index idx_files_user_id_deleted_at
on public.files (user_id, deleted_at);
create index idx_files_file_key
on public.files (file_key);
create index idx_files_file_key_deleted_at
on public.files (file_key, deleted_at);
alter table public.files enable row level security;
create policy "Users can insert their own files"
on public.files
for insert
with check (
auth.uid() = user_id
);
create policy "Users can view their own active files"
on public.files
for select
using (
auth.uid() = user_id and deleted_at is null
);
create policy "Users can soft-delete their own files"
on public.files
for update
using (
auth.uid() = user_id
)
with check (
deleted_at is null or deleted_at > now()
);
create policy "Users can delete their own files permanently"
on public.files
for delete
using (
auth.uid() = user_id
);
|
