Spaces:

taijichat
/

chat

Sleeping

chat / database_schema.sql

Add authentication, token quota tracking, and comprehensive usage logging

8d66edb about 1 year ago

2.95 kB

-- 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';

	-- 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';