File size: 2,948 Bytes
8d66edb |
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 |
-- Supabase Database Schema for TaijiChat
-- Execute this SQL in your Supabase project to create the required tables
-- Users table
-- Stores user information and token quota
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
hf_user_id TEXT UNIQUE NOT NULL,
hf_username TEXT NOT NULL,
email TEXT,
token_quota INTEGER DEFAULT 100000,
tokens_used INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_login TIMESTAMP WITH TIME ZONE,
is_active BOOLEAN DEFAULT TRUE
);
-- Usage logs table
-- Stores comprehensive logs of every query with token usage and errors
CREATE TABLE IF NOT EXISTS usage_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
hf_user_id TEXT NOT NULL,
timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
query_text TEXT NOT NULL,
prompt_tokens INTEGER DEFAULT 0,
completion_tokens INTEGER DEFAULT 0,
total_tokens INTEGER DEFAULT 0,
model TEXT,
response_text TEXT,
error_message TEXT,
conversation_history JSONB,
is_image_response BOOLEAN DEFAULT FALSE,
image_path TEXT
);
-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_users_hf_id ON users(hf_user_id);
CREATE INDEX IF NOT EXISTS idx_users_active ON users(is_active);
CREATE INDEX IF NOT EXISTS idx_logs_user_id ON usage_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_logs_hf_user_id ON usage_logs(hf_user_id);
CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON usage_logs(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_logs_error ON usage_logs(error_message) WHERE error_message IS NOT NULL;
-- Create a view for user statistics
CREATE OR REPLACE VIEW user_stats AS
SELECT
u.id,
u.hf_user_id,
u.hf_username,
u.token_quota,
u.tokens_used,
u.token_quota - u.tokens_used AS tokens_remaining,
ROUND(100.0 * u.tokens_used / NULLIF(u.token_quota, 0), 2) AS usage_percentage,
COUNT(l.id) AS total_queries,
COUNT(CASE WHEN l.error_message IS NOT NULL THEN 1 END) AS error_count,
MAX(l.timestamp) AS last_query_time
FROM users u
LEFT JOIN usage_logs l ON u.id = l.user_id
GROUP BY u.id, u.hf_user_id, u.hf_username, u.token_quota, u.tokens_used;
-- Enable Row Level Security (RLS) - Optional, uncomment if needed
-- ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE usage_logs ENABLE ROW LEVEL SECURITY;
-- Create policies for RLS (if needed)
-- CREATE POLICY "Users can view own data" ON users FOR SELECT USING (hf_user_id = auth.jwt() ->> 'sub');
-- CREATE POLICY "Users can view own logs" ON usage_logs FOR SELECT USING (hf_user_id = auth.jwt() ->> 'sub');
COMMENT ON TABLE users IS 'Stores user authentication and token quota information';
COMMENT ON TABLE usage_logs IS 'Logs every query with token usage, response, and errors';
COMMENT ON VIEW user_stats IS 'Provides aggregated statistics for each user';
|