File size: 5,436 Bytes
9853b20 cdc96c8 9853b20 cdc96c8 9853b20 | 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 | -- Drop existing tables if they exist (safe teardown for re-creating DB)
DROP TABLE IF EXISTS room_messages CASCADE;
DROP TABLE IF EXISTS logs CASCADE;
DROP TABLE IF EXISTS codes CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS rooms CASCADE;
-- Enable necessary extensions (ensure gen_random_uuid is available)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Rooms table (created first because other tables reference it)
CREATE TABLE rooms (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR,
description VARCHAR(255),
status INTEGER DEFAULT 1,
date_created TIMESTAMP DEFAULT NOW(),
date_last_message TIMESTAMP,
updated_at TIMESTAMP DEFAULT NOW(),
creator_id UUID
);
-- Users table
-- id MUST match auth.users.id (no default) to keep auth as single source of truth
CREATE TABLE users (
id UUID PRIMARY KEY,
email VARCHAR UNIQUE NOT NULL,
token VARCHAR UNIQUE NOT NULL DEFAULT gen_random_uuid()::text,
status INTEGER DEFAULT 1,
fname VARCHAR NOT NULL,
sname VARCHAR NOT NULL,
sex INTEGER,
nickname VARCHAR,
birth_date DATE,
photo_url VARCHAR,
lang VARCHAR DEFAULT 'cs',
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- Codes table (create without FK references to avoid dependency ordering issues)
CREATE TABLE codes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
code VARCHAR(5) UNIQUE NOT NULL,
linked_to UUID, -- will add self-FK constraint below
used INTEGER,
user_id UUID, -- will add FK constraint below
room_id UUID, -- will add FK constraint below
pin_hash VARCHAR,
session_hash VARCHAR,
date_first TIMESTAMP,
date_last TIMESTAMP,
used_count INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT NOW()
);
-- Room messages table (create without FK references to avoid ordering issues)
CREATE TABLE room_messages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
room_id UUID, -- FK added below
code_id UUID, -- FK added below
user_id UUID, -- FK added below
content TEXT NOT NULL,
image_url VARCHAR,
created_at TIMESTAMP DEFAULT NOW()
);
-- Logs table
CREATE TABLE logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
created_at TIMESTAMP DEFAULT NOW(),
lang VARCHAR,
ip VARCHAR,
user_agent VARCHAR,
module VARCHAR,
operation VARCHAR,
data JSONB,
error TEXT,
level VARCHAR
);
-- Create indexes
CREATE INDEX idx_codes_code ON codes(code);
CREATE INDEX idx_codes_used ON codes(used);
CREATE INDEX idx_codes_room_id ON codes(room_id);
CREATE INDEX idx_room_messages_room_id ON room_messages(room_id);
CREATE INDEX idx_room_messages_created_at ON room_messages(created_at);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_logs_created_at ON logs(created_at);
-- Add foreign key constraints after all tables exist (avoids "relation does not exist" errors)
-- Ensure profile id references auth.users(id) for consistency
ALTER TABLE users
ADD CONSTRAINT fk_users_auth FOREIGN KEY (id) REFERENCES auth.users(id) ON DELETE CASCADE;
-- codes foreign keys
ALTER TABLE codes
ADD CONSTRAINT fk_codes_linked_to FOREIGN KEY (linked_to) REFERENCES codes(id);
ALTER TABLE codes
ADD CONSTRAINT fk_codes_user FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE codes
ADD CONSTRAINT fk_codes_room FOREIGN KEY (room_id) REFERENCES rooms(id);
-- Make creator relation explicit
ALTER TABLE rooms
ADD CONSTRAINT fk_rooms_creator FOREIGN KEY (creator_id) REFERENCES users(id);
-- room_messages foreign keys
ALTER TABLE room_messages
ADD CONSTRAINT fk_room_messages_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE;
ALTER TABLE room_messages
ADD CONSTRAINT fk_room_messages_code FOREIGN KEY (code_id) REFERENCES codes(id);
ALTER TABLE room_messages
ADD CONSTRAINT fk_room_messages_user FOREIGN KEY (user_id) REFERENCES users(id);
-- Logs (no FKs)
-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE rooms ENABLE ROW LEVEL SECURITY;
ALTER TABLE room_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE logs ENABLE ROW LEVEL SECURITY;
-- RLS policies for users
DROP POLICY IF EXISTS "Users can view own profile" ON users;
CREATE POLICY "Users can view own profile" ON users
FOR SELECT USING (auth.uid()::text = id::text);
DROP POLICY IF EXISTS "Users can insert own profile" ON users;
CREATE POLICY "Users can insert own profile" ON users
FOR INSERT WITH CHECK (auth.role() = 'service_role' OR auth.uid()::text = id::text);
DROP POLICY IF EXISTS "Users can update own profile" ON users;
CREATE POLICY "Users can update own profile" ON users
FOR UPDATE USING (auth.uid()::text = id::text) WITH CHECK (auth.uid()::text = id::text);
DROP POLICY IF EXISTS "Users can delete own profile" ON users;
CREATE POLICY "Users can delete own profile" ON users
FOR DELETE USING (auth.uid()::text = id::text);
-- Logs: allow service role only
DROP POLICY IF EXISTS "Logs service role only" ON logs;
CREATE POLICY "Logs service role only" ON logs
USING (auth.role() = 'service_role')
WITH CHECK (auth.role() = 'service_role');
-- Note: service role bypasses RLS; use service role for migrations and admin operations.
-- Additional policies for other tables should reference auth.uid() similarly where needed.
|