Skip to main content

​
Overview

The database schema is built on PostgreSQL via Supabase, with TypeScript types auto-generated from the schema. The design follows relational database best practices with foreign key constraints and enum types for data integrity.

​
Type Generation

Database types are automatically generated using the Supabase CLI: 
npm run gen-types
This creates database.types.ts with TypeScript interfaces for all tables, views, functions, and enums.

​
Core Tables

​
historicalFigures

Stores information about historical figures available for conversation.
​
id
string (uuid)
required
Primary key, auto-generated UUID
​
name
string
required
Full name of the historical figure (e.g., “Albert Einstein”)
​
description
string
required
Brief description of the figure’s role or contribution
​
bio
string
required
Extended biography with historical context and achievements
​
category
enum (categories)
required
Classification: ‘scientists’, ‘artists’, ‘philosophers’, ‘leaders’, or ‘others’
​
dateOfBirth
string
required
Birth date in ISO format
​
dateOfDeath
string
required
Death date in ISO format
​
notableWork
string
required
Comma-separated list of major works or achievements
​
imageUrl
string
required
URL to profile image or portrait
​
voiceId
string
required
ElevenLabs voice ID for voice synthesis
​
createdAt
string (timestamp)
default:"now()"
Record creation timestamp
Relationships: Referenced by quizzes and quizQuestions

​
quizzes

Tracks quiz sessions for each user-figure interaction.
​
id
string (uuid)
required
Primary key, auto-generated UUID
​
userId
string (uuid)
required
Foreign key to Supabase auth.users
​
historicalFigureId
string (uuid)
required
Foreign key to historicalFigures.id
​
type
enum (quizType)
required
Quiz generation method: ‘ai’ (AI-generated) or ‘manual’ (pre-defined)
​
created_at
string (timestamp)
default:"now()"
Quiz creation timestamp
​
updated_at
string (timestamp)
default:"now()"
Last update timestamp
Relationships:
  • Foreign key: historicalFigureId → historicalFigures.id
  • Referenced by: quizQuestions, feedbacks

​
quizQuestions

Stores individual questions for each quiz.
​
id
string (uuid)
required
Primary key, auto-generated UUID
​
quizId
string (uuid)
Foreign key to quizzes.id (nullable for pre-generated questions)
​
historicalFigureId
string (uuid)
required
Foreign key to historicalFigures.id
​
question
string
required
The question text to be asked during the quiz
​
created_at
string (timestamp)
default:"now()"
Question creation timestamp
​
updated_at
string (timestamp)
default:"now()"
Last update timestamp
Relationships:
  • Foreign key: quizId → quizzes.id
  • Foreign key: historicalFigureId → historicalFigures.id

​
feedbacks

Stores AI-generated feedback for completed conversations.
​
id
string (uuid)
required
Primary key, auto-generated UUID
​
userId
string (uuid)
required
Foreign key to Supabase auth.users
​
quizId
string (uuid)
required
Foreign key to quizzes.id
​
totalScore
number
required
Overall performance score (0-100)
​
categoryScores
Json
required
Array of category-specific scores and comments. Structure:
{
  name: string,      // e.g., "Communication Skills"
  score: number,     // 0-100
  comment: string    // Detailed feedback
}[]
​
strengths
Json
required
Array of strings highlighting user’s strong points
​
areasForImprovement
Json
required
Array of strings identifying areas needing improvement
​
finalAssessment
string
required
Overall summary and recommendations
​
createdAt
string (timestamp)
default:"now()"
Feedback generation timestamp
Relationships:
  • Foreign key: quizId → quizzes.id

​
Enums

​
categories

Classification for historical figures: 
type categories = 'scientists' | 'artists' | 'philosophers' | 'leaders' | 'others'
Usage: Used in historicalFigures.category to categorize figures and customize conversation prompts.

​
quizType

Quiz generation method: 
type quizType = 'ai' | 'manual'
  • ai: Questions generated dynamically by AI
  • manual: Pre-defined questions from database

​
difficulty_type

Difficulty levels (defined but not currently used): 
type difficulty_type = 'easy' | 'medium' | 'hard'

​
mood_type

Mood classification (defined but not currently used): 
type mood_type = 'positive' | 'neutral' | 'negative'

​
Composite Types

​
feedback_category_score

Structure for individual category scores in feedback: 
type feedback_category_score = {
  name: string | null
  score: number | null
  comment: string | null
}
Used in: feedbacks.categoryScores as a JSON array

​
TypeScript Usage

​
Type Helpers

The schema exports several utility types: 
import { Tables, TablesInsert, TablesUpdate, Enums } from '@/database.types'

// Get row type for a table
type HistoricalFigure = Tables<'historicalFigures'>

// Get insert type (with defaults and optionals)
type NewQuiz = TablesInsert<'quizzes'>

// Get update type (all fields optional)
type QuizUpdate = TablesUpdate<'quizzes'>

// Access enum values
type Category = Enums<'categories'>

​
Supabase Client Typing

import { Database } from '@/database.types'
import { createBrowserClient } from '@supabase/ssr'

const supabase = createBrowserClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// Fully typed queries
const { data } = await supabase
  .from('historicalFigures')
  .select('*')
  .eq('category', 'scientists')

​
Database Relationships Diagram

historicalFigures (1) ──────┐
       │                    │
       │ (1:N)              │ (1:N)
       │                    │
       ▼                    ▼
   quizzes (N)         quizQuestions (N)
       │                    │
       │ (1:N)              │ (N:1)
       │                    │
       ▼                    ▼
  feedbacks (N)        quizzes (1)

auth.users (Supabase) ─┬─> quizzes
                       └─> feedbacks

​
Query Examples

​
Get Historical Figure with Quiz Count

const { data } = await supabase
  .from('historicalFigures')
  .select('*, quizzes(count)')
  .eq('category', 'scientists')

​
Get Quiz with Questions and Feedback

const { data } = await supabase
  .from('quizzes')
  .select(`
    *,
    historicalFigures(*),
    quizQuestions(*),
    feedbacks(*)
  `)
  .eq('userId', userId)
  .single()

​
Insert Feedback with Relationships

const { data } = await supabase
  .from('feedbacks')
  .insert({
    userId: user.id,
    quizId: quiz.id,
    totalScore: 85,
    categoryScores: [
      { name: 'Communication Skills', score: 90, comment: 'Clear and articulate' },
      { name: 'Technical Knowledge', score: 80, comment: 'Good understanding' }
    ],
    strengths: ['Active listening', 'Thoughtful responses'],
    areasForImprovement: ['More technical depth needed'],
    finalAssessment: 'Strong performance overall'
  })
  .select()
  .single()

​
Row Level Security

Supabase RLS policies should be configured to:
  • historicalFigures: Public read access
  • quizzes: Users can only access their own quizzes
  • quizQuestions: Read access based on associated quiz permissions
  • feedbacks: Users can only access their own feedback

Build docs developers (and LLMs) love

Get started for freeTalk to us