chess-baackend / sql /create_tables.sql
Magar077's picture
Clean deploy for HF
f0c0e92
Raw History Blame Contribute Delete
5.63 kB
-- Create tables for chess backend
-- Run this against the `chess_db` database
-- users
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
email TEXT,
first_name TEXT,
last_name TEXT,
password_hash TEXT,
profile_image TEXT,
bio TEXT,
fcm_token TEXT,
friends JSONB,
friend_requests JSONB,
profile JSONB,
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE users ADD COLUMN IF NOT EXISTS profile_image TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS bio TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS fcm_token TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS friends JSONB DEFAULT '[]'::jsonb;
ALTER TABLE users ADD COLUMN IF NOT EXISTS friend_requests JSONB DEFAULT '[]'::jsonb;
ALTER TABLE users ADD COLUMN IF NOT EXISTS profile JSONB;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_seen TIMESTAMPTZ;
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();
-- messages
CREATE TABLE IF NOT EXISTS messages (
id TEXT PRIMARY KEY,
sender TEXT NOT NULL,
receiver TEXT NOT NULL,
message_type TEXT,
text TEXT,
media_url TEXT,
thumbnail_url TEXT,
reply_to_id TEXT,
status TEXT DEFAULT 'sent',
is_read BOOLEAN DEFAULT FALSE,
reactions JSONB,
metadata JSONB,
timestamp TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE messages ALTER COLUMN id TYPE TEXT USING id::text;
ALTER TABLE messages ALTER COLUMN id DROP DEFAULT;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS media_url TEXT;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS thumbnail_url TEXT;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS reply_to_id TEXT;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'sent';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS is_read BOOLEAN DEFAULT FALSE;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS reactions JSONB DEFAULT '{}'::jsonb;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS metadata JSONB;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS timestamp TIMESTAMPTZ;
DROP TABLE IF EXISTS group_call_history;
DROP TABLE IF EXISTS group_messages;
DROP TABLE IF EXISTS groups;
-- groups
CREATE TABLE IF NOT EXISTS groups (
id TEXT PRIMARY KEY,
name TEXT,
avatar TEXT,
created_by TEXT,
admins JSONB,
members JSONB,
created_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ,
last_message TEXT,
last_message_time TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS group_messages (
id TEXT PRIMARY KEY,
group_id TEXT NOT NULL,
sender TEXT NOT NULL,
text TEXT,
message_type TEXT,
media_url TEXT,
thumbnail_url TEXT,
timestamp TIMESTAMPTZ,
seen_by JSONB,
FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS group_call_history (
id TEXT PRIMARY KEY,
group_id TEXT NOT NULL,
started_by TEXT,
call_type TEXT,
participants JSONB,
status TEXT,
started_at TIMESTAMPTZ,
ended_at TIMESTAMPTZ,
FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS stories (
id TEXT PRIMARY KEY,
username TEXT NOT NULL,
media_url TEXT,
thumbnail_url TEXT,
media_type TEXT,
timestamp TIMESTAMPTZ,
viewers JSONB,
reactions JSONB,
reaction_details JSONB
);
CREATE TABLE IF NOT EXISTS notes (
id TEXT PRIMARY KEY,
username TEXT NOT NULL,
text_content TEXT,
media_url TEXT,
thumbnail_url TEXT,
media_type TEXT,
timestamp TIMESTAMPTZ,
viewers JSONB
);
CREATE TABLE IF NOT EXISTS auth_token_blocklist (
jti TEXT PRIMARY KEY,
token_type TEXT,
revoked_at TIMESTAMPTZ,
expires_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS password_reset_tokens (
token TEXT PRIMARY KEY,
username TEXT NOT NULL,
expires_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS game_rooms (
room_id TEXT PRIMARY KEY,
room_data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE IF NOT EXISTS audit_events (
id BIGSERIAL PRIMARY KEY,
event_type TEXT NOT NULL,
payload JSONB,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE IF NOT EXISTS media_files (
id TEXT PRIMARY KEY,
category TEXT NOT NULL,
filename TEXT NOT NULL,
content_type TEXT,
data BYTEA NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
-- payments table for tournament/in-app purchases
CREATE TABLE IF NOT EXISTS payments (
id BIGSERIAL PRIMARY KEY,
pid TEXT UNIQUE NOT NULL,
user_id TEXT,
tournament_id TEXT,
amount NUMERIC(12,2) NOT NULL,
currency TEXT DEFAULT 'NPR',
status TEXT DEFAULT 'pending', -- pending, paid, failed
esewa_ref_id TEXT,
raw_response JSONB,
created_at TIMESTAMPTZ DEFAULT now(),
verified_at TIMESTAMPTZ
);
-- tournaments
CREATE TABLE IF NOT EXISTS tournaments (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
game_type TEXT NOT NULL, -- 'chess' or 'ludo'
entry_fee NUMERIC(12,2) DEFAULT 0,
max_players INT DEFAULT 2,
owner TEXT,
status TEXT DEFAULT 'open', -- open, closed, running, finished
metadata JSONB,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE IF NOT EXISTS tournament_participants (
id BIGSERIAL PRIMARY KEY,
tournament_id TEXT NOT NULL REFERENCES tournaments(id) ON DELETE CASCADE,
user_id TEXT NOT NULL,
status TEXT DEFAULT 'pending', -- pending, paid, joined
payment_pid TEXT,
joined_at TIMESTAMPTZ
);
-- Performance Indexes for Messaging Optimization
CREATE INDEX IF NOT EXISTS idx_messages_conversation ON messages(sender, receiver, timestamp);
CREATE INDEX IF NOT EXISTS idx_messages_sync ON messages(receiver, timestamp);
CREATE INDEX IF NOT EXISTS idx_group_messages_query ON group_messages(group_id, timestamp);
-- removed non-immutable partial index