database_schema.sql

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