Repository navigation
Expand file tree
/
Copy pathsupabase-complete-schema.sql
More file actions
417 lines (374 loc) · 14.1 KB
/
Copy pathsupabase-complete-schema.sql
File metadata and controls
417 lines (374 loc) · 14.1 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
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
-- =====================================================
-- COMPLETE DATABASE RESET AND SETUP
-- =====================================================
-- This script completely resets the database and creates everything fresh
-- Run this SINGLE script to fix all conflicts
-- =====================================================
-- STEP 1: DROP EVERYTHING (Clean Slate)
-- =====================================================
-- Drop all views first (they depend on tables)
DROP VIEW IF EXISTS file_with_access CASCADE;
DROP VIEW IF EXISTS folder_with_access CASCADE;
-- Drop all functions
DROP FUNCTION IF EXISTS get_accessible_files(text) CASCADE;
DROP FUNCTION IF EXISTS get_accessible_folders(text) CASCADE;
DROP FUNCTION IF EXISTS get_files_in_folder(uuid, text) CASCADE;
DROP FUNCTION IF EXISTS get_folder_path(uuid) CASCADE;
DROP FUNCTION IF EXISTS get_emergency_files_for_responder(text) CASCADE;
DROP FUNCTION IF EXISTS sync_emergency_file_status(integer, integer, text, boolean) CASCADE;
-- Drop all tables (in reverse dependency order)
DROP TABLE IF EXISTS emergency_file_access CASCADE;
DROP TABLE IF EXISTS folder_access CASCADE;
DROP TABLE IF EXISTS file_access CASCADE;
DROP TABLE IF EXISTS folders CASCADE;
DROP TABLE IF EXISTS files CASCADE;
-- Drop storage objects first, then bucket
DELETE FROM storage.objects WHERE bucket_id = 'secure-files';
DELETE FROM storage.buckets WHERE id = 'secure-files';
-- =====================================================
-- STEP 2: CREATE STORAGE BUCKET
-- =====================================================
INSERT INTO storage.buckets (id, name, public)
VALUES ('secure-files', 'secure-files', false)
ON CONFLICT (id) DO NOTHING;
-- =====================================================
-- STEP 3: CREATE CORE TABLES
-- =====================================================
-- Files table
CREATE TABLE files (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
uploader_address TEXT NOT NULL,
file_name TEXT NOT NULL,
file_path TEXT NOT NULL UNIQUE,
file_size BIGINT NOT NULL,
file_type TEXT NOT NULL,
contract_file_id BIGINT,
file_hash TEXT NOT NULL,
folder_id UUID,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- File access table
CREATE TABLE file_access (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
file_id UUID REFERENCES files(id) ON DELETE CASCADE,
wallet_address TEXT NOT NULL,
encrypted_key TEXT NOT NULL,
granted_by TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(file_id, wallet_address)
);
-- Folders table
CREATE TABLE folders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
parent_folder_id UUID REFERENCES folders(id) ON DELETE CASCADE,
creator_address TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(name, parent_folder_id, creator_address)
);
-- Folder access table
CREATE TABLE folder_access (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
folder_id UUID REFERENCES folders(id) ON DELETE CASCADE,
wallet_address TEXT NOT NULL,
granted_by TEXT NOT NULL,
permission TEXT NOT NULL DEFAULT 'read',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(folder_id, wallet_address)
);
-- Emergency file access table
CREATE TABLE emergency_file_access (
id SERIAL PRIMARY KEY,
file_id UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,
contract_file_id BIGINT NOT NULL,
owner_address TEXT NOT NULL,
is_emergency_accessible BOOLEAN NOT NULL DEFAULT false,
marked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(file_id)
);
-- =====================================================
-- STEP 4: CREATE INDEXES
-- =====================================================
-- File indexes
CREATE INDEX idx_files_uploader ON files(uploader_address);
CREATE INDEX idx_files_contract_id ON files(contract_file_id);
CREATE INDEX idx_files_created_at ON files(created_at);
CREATE INDEX idx_files_folder ON files(folder_id);
-- File access indexes
CREATE INDEX idx_file_access_file_id ON file_access(file_id);
CREATE INDEX idx_file_access_wallet ON file_access(wallet_address);
-- Folder indexes
CREATE INDEX idx_folders_creator ON folders(creator_address);
CREATE INDEX idx_folders_parent ON folders(parent_folder_id);
CREATE INDEX idx_folders_created_at ON folders(created_at);
-- Folder access indexes
CREATE INDEX idx_folder_access_folder_id ON folder_access(folder_id);
CREATE INDEX idx_folder_access_wallet ON folder_access(wallet_address);
-- Emergency access indexes
CREATE INDEX idx_emergency_file_access_owner ON emergency_file_access(owner_address);
CREATE INDEX idx_emergency_file_access_contract_id ON emergency_file_access(contract_file_id);
CREATE INDEX idx_emergency_file_access_accessible ON emergency_file_access(is_emergency_accessible);
-- =====================================================
-- STEP 5: CREATE VIEWS
-- =====================================================
-- File with access view
CREATE VIEW file_with_access AS
SELECT
f.*,
COALESCE(
json_agg(
json_build_object(
'wallet_address', fa.wallet_address,
'granted_by', fa.granted_by,
'granted_at', fa.created_at
)
) FILTER (WHERE fa.wallet_address IS NOT NULL),
'[]'::json
) as shared_with
FROM files f
LEFT JOIN file_access fa ON f.id = fa.file_id
GROUP BY f.id, f.uploader_address, f.file_name, f.file_path, f.file_size, f.file_type, f.contract_file_id, f.file_hash, f.folder_id, f.created_at, f.updated_at;
-- =====================================================
-- STEP 6: CREATE FUNCTIONS
-- =====================================================
-- Get accessible files function
CREATE OR REPLACE FUNCTION get_accessible_files(wallet_addr TEXT)
RETURNS TABLE (
id UUID,
uploader_address TEXT,
file_name TEXT,
file_path TEXT,
file_size BIGINT,
file_type TEXT,
contract_file_id BIGINT,
file_hash TEXT,
folder_id UUID,
created_at TIMESTAMP WITH TIME ZONE,
updated_at TIMESTAMP WITH TIME ZONE,
access_type TEXT,
encrypted_key TEXT
) AS $$
BEGIN
RETURN QUERY
-- Files uploaded by the user
SELECT
f.id, f.uploader_address, f.file_name, f.file_path, f.file_size,
f.file_type, f.contract_file_id, f.file_hash, f.folder_id, f.created_at, f.updated_at,
'owner'::TEXT as access_type,
fa.encrypted_key
FROM files f
LEFT JOIN file_access fa ON f.id = fa.file_id AND fa.wallet_address = wallet_addr
WHERE f.uploader_address = wallet_addr
UNION
-- Files shared with the user
SELECT
f.id, f.uploader_address, f.file_name, f.file_path, f.file_size,
f.file_type, f.contract_file_id, f.file_hash, f.folder_id, f.created_at, f.updated_at,
'shared'::TEXT as access_type,
fa.encrypted_key
FROM files f
INNER JOIN file_access fa ON f.id = fa.file_id
WHERE fa.wallet_address = wallet_addr AND f.uploader_address != wallet_addr;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Get accessible folders function
CREATE OR REPLACE FUNCTION get_accessible_folders(wallet_addr TEXT)
RETURNS TABLE (
id UUID,
name TEXT,
parent_folder_id UUID,
creator_address TEXT,
created_at TIMESTAMP WITH TIME ZONE,
updated_at TIMESTAMP WITH TIME ZONE,
access_type TEXT,
permission TEXT,
folder_path TEXT
) AS $$
BEGIN
RETURN QUERY
-- Folders created by the user
SELECT
f.id, f.name, f.parent_folder_id, f.creator_address,
f.created_at, f.updated_at,
'owner'::TEXT as access_type,
'admin'::TEXT as permission,
get_folder_path(f.id) as folder_path
FROM folders f
WHERE f.creator_address = wallet_addr
UNION
-- Folders shared with the user
SELECT
f.id, f.name, f.parent_folder_id, f.creator_address,
f.created_at, f.updated_at,
'shared'::TEXT as access_type,
fa.permission,
get_folder_path(f.id) as folder_path
FROM folders f
INNER JOIN folder_access fa ON f.id = fa.folder_id
WHERE fa.wallet_address = wallet_addr AND f.creator_address != wallet_addr;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Get files in folder function
CREATE OR REPLACE FUNCTION get_files_in_folder(folder_uuid UUID, wallet_addr TEXT)
RETURNS TABLE (
id UUID,
uploader_address TEXT,
file_name TEXT,
file_path TEXT,
file_size BIGINT,
file_type TEXT,
contract_file_id BIGINT,
file_hash TEXT,
created_at TIMESTAMP WITH TIME ZONE,
updated_at TIMESTAMP WITH TIME ZONE,
access_type TEXT,
encrypted_key TEXT,
folder_id UUID
) AS $$
BEGIN
RETURN QUERY
-- Files uploaded by the user in this folder
SELECT
f.id, f.uploader_address, f.file_name, f.file_path, f.file_size,
f.file_type, f.contract_file_id, f.file_hash, f.created_at, f.updated_at,
'owner'::TEXT as access_type,
fa.encrypted_key,
f.folder_id
FROM files f
LEFT JOIN file_access fa ON f.id = fa.file_id AND fa.wallet_address = wallet_addr
WHERE f.folder_id = folder_uuid AND f.uploader_address = wallet_addr
UNION
-- Files shared with the user in this folder
SELECT
f.id, f.uploader_address, f.file_name, f.file_path, f.file_size,
f.file_type, f.contract_file_id, f.file_hash, f.created_at, f.updated_at,
'shared'::TEXT as access_type,
fa.encrypted_key,
f.folder_id
FROM files f
INNER JOIN file_access fa ON f.id = fa.file_id
WHERE f.folder_id = folder_uuid AND fa.wallet_address = wallet_addr AND f.uploader_address != wallet_addr;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Get folder path function
CREATE OR REPLACE FUNCTION get_folder_path(folder_uuid UUID)
RETURNS TEXT AS $$
DECLARE
folder_record RECORD;
path_parts TEXT[] := '{}';
current_id UUID := folder_uuid;
BEGIN
WHILE current_id IS NOT NULL LOOP
SELECT name, parent_folder_id INTO folder_record
FROM folders
WHERE id = current_id;
IF NOT FOUND THEN
EXIT;
END IF;
path_parts := array_prepend(folder_record.name, path_parts);
current_id := folder_record.parent_folder_id;
END LOOP;
RETURN array_to_string(path_parts, '/');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Get emergency files for responder function
CREATE OR REPLACE FUNCTION get_emergency_files_for_responder(responder_addr TEXT)
RETURNS TABLE (
id UUID,
file_name TEXT,
file_path TEXT,
file_size BIGINT,
file_type TEXT,
file_hash TEXT,
created_at TIMESTAMP WITH TIME ZONE,
uploader_address TEXT,
contract_file_id BIGINT,
is_emergency_accessible BOOLEAN,
marked_at TIMESTAMP WITH TIME ZONE
) AS $$
BEGIN
RETURN QUERY
SELECT
f.id,
f.file_name,
f.file_path,
f.file_size,
f.file_type,
f.file_hash,
f.created_at,
f.uploader_address,
f.contract_file_id,
efa.is_emergency_accessible,
efa.marked_at
FROM files f
INNER JOIN emergency_file_access efa ON f.id = efa.file_id
WHERE efa.is_emergency_accessible = true
AND LOWER(f.uploader_address) != LOWER(responder_addr); -- Exclude files owned by the responder
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Sync emergency file status function
CREATE OR REPLACE FUNCTION sync_emergency_file_status(
p_file_id UUID,
p_contract_file_id BIGINT,
p_owner_address TEXT,
p_is_emergency BOOLEAN
) RETURNS VOID AS $$
BEGIN
INSERT INTO emergency_file_access (
file_id,
contract_file_id,
owner_address,
is_emergency_accessible,
marked_at,
updated_at
) VALUES (
p_file_id,
p_contract_file_id,
LOWER(p_owner_address),
p_is_emergency,
NOW(),
NOW()
)
ON CONFLICT (file_id)
DO UPDATE SET
is_emergency_accessible = p_is_emergency,
updated_at = NOW();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- =====================================================
-- STEP 7: GRANT PERMISSIONS
-- =====================================================
-- Grant execute permissions on all functions
GRANT EXECUTE ON FUNCTION get_accessible_files(TEXT) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION get_accessible_folders(TEXT) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION get_files_in_folder(UUID, TEXT) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION get_folder_path(UUID) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION get_emergency_files_for_responder(TEXT) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION sync_emergency_file_status(UUID, BIGINT, TEXT, BOOLEAN) TO anon, authenticated;
-- =====================================================
-- STEP 8: DISABLE RLS (Web3 Compatible)
-- =====================================================
-- Disable RLS for Web3 compatibility
ALTER TABLE files DISABLE ROW LEVEL SECURITY;
ALTER TABLE file_access DISABLE ROW LEVEL SECURITY;
ALTER TABLE folders DISABLE ROW LEVEL SECURITY;
ALTER TABLE folder_access DISABLE ROW LEVEL SECURITY;
ALTER TABLE emergency_file_access DISABLE ROW LEVEL SECURITY;
-- =====================================================
-- SETUP COMPLETE!
-- =====================================================
DO $$
BEGIN
RAISE NOTICE '✅ Complete Database Reset and Setup Complete!';
RAISE NOTICE '📊 Tables created: files, file_access, folders, folder_access, emergency_file_access';
RAISE NOTICE '👁️ Views created: file_with_access, folder_with_access';
RAISE NOTICE '🔧 Functions created: get_accessible_files, get_accessible_folders, get_files_in_folder, get_folder_path';
RAISE NOTICE '🚨 Emergency functions: get_emergency_files_for_responder, sync_emergency_file_status';
RAISE NOTICE '🗂️ Storage bucket: secure-files';
RAISE NOTICE '🔒 Security: RLS disabled for Web3 compatibility';
RAISE NOTICE '⚡ Emergency access: Blockchain-database sync ready';
RAISE NOTICE '🚀 Ready to use with Blaut application!';
END $$;