Hugging Face's logo

Spaces:
HonzaH
/
Letschat
Sleeping

App Files Community
Letschat / src /db /final_supabase_schema_2025_12_09.sql
HonzaH's picture
HonzaH
Upload 191 files
cdc96c8 verified
Raw History Blame Contribute Delete
5.8 kB
-- Final Supabase schema and migration
-- Generated: 2025-12-09T19:57:19.113Z
-- Intended for Supabase (auth schema present). Drops existing objects and recreates schema.
-- === DROP ===
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;
-- === EXTENSIONS ===
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- === ROOMS ===
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 ===
-- 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 ===
CREATE TABLE codes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
code VARCHAR(5) UNIQUE NOT NULL,
linked_to UUID,
used INTEGER,
user_id UUID, -- optional: user who claimed the code
room_id UUID, -- room to which code belongs
pin_hash VARCHAR,
session_hash VARCHAR,
date_first TIMESTAMP,
date_last TIMESTAMP,
used_count INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT NOW()
);
-- === ROOM MESSAGES ===
CREATE TABLE room_messages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
room_id UUID,
code_id UUID,
user_id UUID,
content TEXT NOT NULL,
image_url VARCHAR,
created_at TIMESTAMP DEFAULT NOW()
);
-- === LOGS ===
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
);
-- === 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);
-- === FOREIGN KEYS ===
ALTER TABLE users
ADD CONSTRAINT fk_users_auth FOREIGN KEY (id) REFERENCES auth.users(id) ON DELETE CASCADE;
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);
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);
-- Make creator relation explicit
ALTER TABLE rooms
ADD CONSTRAINT fk_rooms_creator FOREIGN KEY (creator_id) REFERENCES users(id);
-- === RLS / POLICIES ===
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;
-- Example policies (adapt per project security model)
-- Users: view/insert/update own profile
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);
-- Logs: 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');
-- === BACKFILL MIGRATION ===
-- Populate rooms.creator_id from codes.user_id where available.
-- Strategy: use earliest code.date_first for the room that has user_id set.
WITH primary_claim AS (
SELECT room_id, user_id, MIN(date_first) as first_date
FROM codes
WHERE user_id IS NOT NULL AND room_id IS NOT NULL
GROUP BY room_id, user_id
)
UPDATE rooms r
SET creator_id = p.user_id
FROM (
SELECT room_id, user_id FROM (
SELECT room_id, user_id, ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY first_date ASC) rn
FROM (
SELECT room_id, user_id, MIN(date_first) AS first_date
FROM codes
WHERE user_id IS NOT NULL AND room_id IS NOT NULL
GROUP BY room_id, user_id
) t
) s WHERE rn = 1
) p
WHERE r.id = p.room_id AND r.creator_id IS NULL;
-- Note: after running this script, verify data and consider manual corrections for rooms without claimed codes.
-- End of script