Database Schema - Studley AI

Overview

Studley AI uses PostgreSQL as its database with Supabase for authentication and real-time features. The schema is organized into several key areas: authentication, user data, content generation, feedback, and integrations.

Schema Diagram

auth.users (Supabase)
    |
    ├─── user_profiles
    ├─── user_folders
    ├─── user_library_items
    ├─── ai_chat_sessions
    └─── workspace_documents

users (Custom Auth)
    |
    ├─── accounts
    ├─── sessions
    ├─── credit_usage
    ├─── generations
    ├─── quiz_results
    ├─── flashcard_results
    └─── writing_results

Studley AI uses a hybrid authentication system with both Supabase auth.users and a custom users table. New features use Supabase auth, while legacy features use the custom table.

Core Tables

users

Custom user authentication table:

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  name TEXT,
  email TEXT UNIQUE NOT NULL,
  "emailVerified" TIMESTAMP,
  password TEXT NOT NULL,
  image TEXT,
  credits INTEGER DEFAULT 5000,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

idx_users_email on email

Key Features:

Default 5,000 credits for new users
Email uniqueness enforced
Timestamps for created/updated tracking

user_profiles

Extended user profile information (Supabase auth):

CREATE TABLE IF NOT EXISTS public.user_profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name text,
  avatar_url text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  UNIQUE(user_id)
);

Features:

Links to Supabase auth.users
Stores profile pictures in Vercel Blob
One profile per user (enforced by UNIQUE constraint)

Authentication Tables

accounts

OAuth and external authentication providers:

CREATE TABLE IF NOT EXISTS accounts (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "userId" TEXT NOT NULL,
  type TEXT NOT NULL,
  provider TEXT NOT NULL,
  "providerAccountId" TEXT NOT NULL,
  refresh_token TEXT,
  access_token TEXT,
  expires_at INTEGER,
  token_type TEXT,
  scope TEXT,
  id_token TEXT,
  session_state TEXT,
  FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE(provider, "providerAccountId")
);

Indexes:

idx_accounts_userId on userId

sessions

Active user sessions:

CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "sessionToken" TEXT UNIQUE NOT NULL,
  "userId" TEXT NOT NULL,
  expires TIMESTAMP NOT NULL,
  FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE
);

Indexes:

idx_sessions_userId on userId
idx_sessions_sessionToken on sessionToken

verification_tokens

Email verification tokens:

CREATE TABLE IF NOT EXISTS verification_tokens (
  identifier TEXT NOT NULL,
  token TEXT UNIQUE NOT NULL,
  expires TIMESTAMP NOT NULL,
  UNIQUE(identifier, token)
);

User Content Tables

user_library_items

Stores all saved user content:

CREATE TABLE IF NOT EXISTS public.user_library_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  folder_id uuid REFERENCES public.user_folders(id) ON DELETE SET NULL,
  item_type text NOT NULL, -- 'quiz', 'flashcard', 'study_guide', 'writing_prompt'
  title text NOT NULL,
  topic text,
  content jsonb NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

Item Types:

quiz - Generated quizzes
flashcard - Flashcard sets
study_guide - Study guides
writing_prompt - Writing prompts

user_folders

Organizational folders for library items:

CREATE TABLE IF NOT EXISTS public.user_folders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name text NOT NULL,
  description text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

generations

Generation history tracking:

CREATE TABLE IF NOT EXISTS generations (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "userId" TEXT NOT NULL,
  type TEXT NOT NULL,
  topic TEXT NOT NULL,
  content TEXT NOT NULL,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE
);

Indexes:

idx_generations_userId on userId

Credit System Tables

credit_usage

Detailed credit transaction log:

CREATE TABLE IF NOT EXISTS credit_usage (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "userId" TEXT NOT NULL,
  amount INTEGER NOT NULL,
  type TEXT NOT NULL,
  description TEXT,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE
);

Transaction Types:

deduction - Credits spent on generations
purchase - Credits purchased
bonus - Free credits awarded
refund - Credits refunded

Indexes:

idx_credit_usage_userId on userId

Results Tables

quiz_results

Quiz completion results:

CREATE TABLE quiz_results (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  quiz_item_id UUID REFERENCES study_items(id) ON DELETE SET NULL,
  score NUMERIC NOT NULL,
  total_questions INTEGER NOT NULL,
  correct_count INTEGER NOT NULL DEFAULT 0,
  partial_count INTEGER NOT NULL DEFAULT 0,
  incorrect_count INTEGER NOT NULL DEFAULT 0,
  accuracy NUMERIC NOT NULL DEFAULT 0,
  avg_time_per_question NUMERIC NOT NULL DEFAULT 0,
  best_streak INTEGER NOT NULL DEFAULT 0,
  answers JSONB,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
);

Indexes:

quiz_results_user_id_idx on user_id
quiz_results_quiz_item_id_idx on quiz_item_id

flashcard_results

Flashcard session results:

CREATE TABLE flashcard_results (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  flashcard_item_id UUID REFERENCES study_items(id) ON DELETE SET NULL,
  known_count INTEGER NOT NULL DEFAULT 0,
  unknown_count INTEGER NOT NULL DEFAULT 0,
  total_cards INTEGER NOT NULL,
  cards_status JSONB,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
);

Indexes:

flashcard_results_user_id_idx on user_id
flashcard_results_flashcard_item_id_idx on flashcard_item_id

writing_results

Writing prompt completion results:

CREATE TABLE writing_results (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  writing_item_id UUID REFERENCES study_items(id) ON DELETE SET NULL,
  overall_score NUMERIC NOT NULL DEFAULT 0,
  total_prompts INTEGER NOT NULL,
  prompt_responses JSONB,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
);

Indexes:

writing_results_user_id_idx on user_id
writing_results_writing_item_id_idx on writing_item_id

AI Workspace Tables

ai_chat_sessions

AI tutor chat sessions:

CREATE TABLE IF NOT EXISTS public.ai_chat_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  session_name TEXT NOT NULL DEFAULT 'New Study Session',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  last_message_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE
);

Indexes:

idx_ai_chat_sessions_user_id on user_id
idx_ai_chat_sessions_updated on updated_at DESC

ai_chat_messages

Individual chat messages:

CREATE TABLE IF NOT EXISTS public.ai_chat_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
  content TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  metadata JSONB DEFAULT '{}',
  FOREIGN KEY (session_id) REFERENCES public.ai_chat_sessions(id) ON DELETE CASCADE
);

Indexes:

idx_ai_chat_messages_session_id on session_id
idx_ai_chat_messages_created on created_at

workspace_documents

Uploaded documents and notes:

CREATE TABLE IF NOT EXISTS public.workspace_documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  session_id UUID,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  document_type TEXT NOT NULL CHECK (document_type IN ('note', 'pdf', 'text', 'image')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES public.ai_chat_sessions(id) ON DELETE SET NULL
);

Indexes:

idx_workspace_documents_user_id on user_id
idx_workspace_documents_session_id on session_id

shared_quizzes

Temporary quiz sharing:

CREATE TABLE IF NOT EXISTS shared_quizzes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  quiz_id TEXT NOT NULL UNIQUE,
  topic TEXT NOT NULL,
  quiz_data JSONB NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  expires_at TIMESTAMP WITH TIME ZONE,
  access_count INTEGER DEFAULT 0
);

Indexes:

idx_shared_quizzes_quiz_id on quiz_id
idx_shared_quizzes_expires_at on expires_at

shared_materials

Persistent material sharing:

CREATE TABLE IF NOT EXISTS shared_materials (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  material_id UUID NOT NULL REFERENCES study_items(id) ON DELETE CASCADE,
  owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  shared_with_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  public_token TEXT UNIQUE,
  public_access_enabled BOOLEAN DEFAULT false,
  allow_resharing BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

idx_shared_materials_material_id on material_id
idx_shared_materials_owner_id on owner_id
idx_shared_materials_shared_with_user_id on shared_with_user_id
idx_shared_materials_public_token on public_token

shared_sessions

Real-time collaboration sessions:

CREATE TABLE IF NOT EXISTS shared_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id TEXT NOT NULL UNIQUE,
  quiz_data JSONB NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  expires_at TIMESTAMP WITH TIME ZONE
);

Admin Tables

admin_config

Site configuration:

CREATE TABLE IF NOT EXISTS public.admin_config (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  config_key TEXT NOT NULL UNIQUE,
  config_value JSONB NOT NULL,
  updated_by TEXT,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Indexes:

idx_admin_config_key on config_key

admin_notifications

Platform-wide announcements:

CREATE TABLE IF NOT EXISTS public.admin_notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  message text NOT NULL,
  enabled boolean DEFAULT false,
  created_at timestamp with time zone DEFAULT now(),
  created_by text,
  expires_at timestamp with time zone
);

Feedback Tables

bug_reports

User-submitted bug reports:

CREATE TABLE IF NOT EXISTS bug_reports (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  preferred_name TEXT NOT NULL,
  severity TEXT NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
  error_code TEXT,
  has_error_code BOOLEAN DEFAULT true,
  description TEXT NOT NULL,
  browser_info TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'in_progress', 'resolved', 'closed')),
  admin_notes TEXT
);

Indexes:

idx_bug_reports_created_at on created_at DESC
idx_bug_reports_status on status

user_feedback

General user feedback:

CREATE TABLE IF NOT EXISTS user_feedback (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  preferred_name TEXT NOT NULL,
  feedback_type TEXT NOT NULL CHECK (feedback_type IN ('general', 'feature_request', 'improvement', 'other')),
  message TEXT NOT NULL,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'reviewed', 'implemented', 'closed')),
  admin_notes TEXT
);

Indexes:

idx_user_feedback_created_at on created_at DESC
idx_user_feedback_status on status

Notifications Table

User-to-user notifications:

CREATE TABLE IF NOT EXISTS notifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  sender_id UUID REFERENCES users(id) ON DELETE SET NULL,
  material_id UUID REFERENCES study_items(id) ON DELETE SET NULL,
  type TEXT NOT NULL CHECK (type IN ('share', 'live_invite', 'comment')),
  title TEXT NOT NULL,
  message TEXT NOT NULL,
  read BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP
);

Indexes:

idx_notifications_user_id on user_id
idx_notifications_sender_id on sender_id
idx_notifications_material_id on material_id
idx_notifications_read on (user_id, read)
idx_notifications_created_at on created_at DESC

Integration Tables

Clever Integration

clever_accounts

CREATE TABLE clever_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  clever_id TEXT UNIQUE NOT NULL,
  district_id TEXT,
  school_id TEXT,
  role TEXT NOT NULL,
  raw_data JSONB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

classrooms

CREATE TABLE public.classrooms (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  teacher_id uuid NOT NULL,
  clever_section_id text NOT NULL UNIQUE,
  name text NOT NULL,
  subject text,
  grade text,
  period text,
  raw_clever_data jsonb,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  FOREIGN KEY (teacher_id) REFERENCES public.users(id) ON DELETE CASCADE
);

Indexes:

idx_classrooms_teacher_id on teacher_id
idx_classrooms_clever_section_id on clever_section_id

students

CREATE TABLE public.students (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  classroom_id uuid NOT NULL,
  clever_student_id text NOT NULL UNIQUE,
  first_name text,
  last_name text,
  email text,
  raw_clever_data jsonb,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  FOREIGN KEY (classroom_id) REFERENCES public.classrooms(id) ON DELETE CASCADE
);

Indexes:

idx_students_classroom_id on classroom_id
idx_students_clever_student_id on clever_student_id

Rate Limiting

generation_rate_limits

CREATE TABLE IF NOT EXISTS generation_rate_limits (
  id BIGSERIAL PRIMARY KEY,
  identifier TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

Indexes:

idx_rate_limit_identifier_time on (identifier, created_at DESC)

Extensions

Required PostgreSQL extensions:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

Provides:

gen_random_uuid() for UUID generation
Cryptographic functions

Next Steps

RLS Policies

Learn about Row Level Security policies

Migrations

Running and managing database migrations

User Management

Query and manage user data

Admin Dashboard

Return to admin overview

Administration

Database

​Overview

​Schema Diagram

​Core Tables

​users

​user_profiles

​Authentication Tables

​accounts

​sessions

​verification_tokens

​User Content Tables

​user_library_items

​user_folders

​generations

​Credit System Tables

​credit_usage

​Results Tables

​quiz_results

​flashcard_results

​writing_results

​AI Workspace Tables

​ai_chat_sessions

​ai_chat_messages

​workspace_documents

​Sharing Tables

​shared_quizzes

​shared_materials

​shared_sessions

​Admin Tables

​admin_config

​admin_notifications

​Feedback Tables

​bug_reports

​user_feedback

​Notifications Table

​Integration Tables

​Clever Integration

​clever_accounts

​classrooms

​students

​Rate Limiting

​generation_rate_limits

​Extensions

​Next Steps

RLS Policies

Migrations

User Management

Admin Dashboard

Build docs developers (and LLMs) love

Overview

Schema Diagram

Core Tables

users

user_profiles

Authentication Tables

accounts

sessions

verification_tokens

User Content Tables

user_library_items

user_folders

generations

Credit System Tables

credit_usage

Results Tables

quiz_results

flashcard_results

writing_results

AI Workspace Tables

ai_chat_sessions

ai_chat_messages

workspace_documents

Sharing Tables

shared_quizzes

shared_materials

shared_sessions

Admin Tables

admin_config

admin_notifications

Feedback Tables

bug_reports

user_feedback

Notifications Table

Integration Tables

Clever Integration

clever_accounts

classrooms

students

Rate Limiting

generation_rate_limits

Extensions

Next Steps