40 lines
1.4 KiB
SQL
40 lines
1.4 KiB
SQL
-- DeepRes initial schema
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
tg_id BIGINT PRIMARY KEY,
|
|
username TEXT DEFAULT '',
|
|
name TEXT DEFAULT '',
|
|
stars_balance INT DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS researches (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
tg_id BIGINT REFERENCES users(tg_id) ON DELETE CASCADE,
|
|
query TEXT NOT NULL,
|
|
mode TEXT NOT NULL CHECK (mode IN ('fast', 'deep')),
|
|
status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'done', 'failed')),
|
|
stars_cost INT DEFAULT 0,
|
|
summary TEXT,
|
|
report_path TEXT,
|
|
sources JSONB,
|
|
error_message TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
completed_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
tg_id BIGINT REFERENCES users(tg_id) ON DELETE CASCADE,
|
|
amount INT NOT NULL,
|
|
type TEXT NOT NULL CHECK (type IN ('purchase', 'research', 'bonus', 'refund')),
|
|
description TEXT DEFAULT '',
|
|
telegram_charge_id TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_researches_tg_id ON researches(tg_id);
|
|
CREATE INDEX IF NOT EXISTS idx_researches_created_at ON researches(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_tg_id ON transactions(tg_id);
|