TellaAI

Database Schema

Complete data models and relationships in SophieAI

Database Schema

SophieAI uses PostgreSQL with Prisma ORM for data management. This document outlines all models, relationships, and database structure.

Database Technology: PostgreSQL 14+ with Prisma ORM for type-safe database access and migrations.

Quick Database Setup

Install Dependencies

# Using Homebrew
brew install postgresql
brew services start postgresql

# Verify installation
psql --version
# Install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Verify installation
psql --version
# Run PostgreSQL in Docker
docker run --name sophieai-postgres \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=sophieai \
  -p 5432:5432 \
  -d postgres:14

# Verify container is running
docker ps | grep sophieai-postgres

Create Database

Create Database and User
CREATE DATABASE sophieai;
CREATE USER sophieai_user WITH PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE sophieai TO sophieai_user;

-- Enable required extensions
\c sophieai
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
# Connect to Docker PostgreSQL
docker exec -it sophieai-postgres psql -U postgres -d sophieai

# Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

Extensions Required: The uuid-ossp extension is needed for UUID generation, and pg_trgm is required for full-text search capabilities.

Configure Environment

Set your database URL in the .env file:

.env
DATABASE_URL="postgresql://sophieai_user:your_secure_password@localhost:5432/sophieai"

Security: Use strong passwords in production and consider using connection pooling for better performance.

Run Migrations

Apply the database schema:

# Generate Prisma client
bunx prisma generate

# Apply migrations
bunx prisma migrate dev

# Seed database (optional)
bunx prisma db seed

Verification: Run bunx prisma studio to open the database browser and verify your schema.

Database Models Overview

Primary Entity Models:

  • User: Authentication and user management
  • Course: Academic course organization
  • Note: Notes with transcription support
  • Document: File uploads and processing

These models form the foundation of the SophieAI platform and contain the primary user data.

AI-Generated Content:

  • FlashcardSet: Flashcard collections
  • Flashcard: Individual flashcards
  • Quiz: Quiz containers with metadata
  • Question: Individual quiz questions
  • StudyGuide: AI-generated study materials

These models store AI-generated educational content based on user notes and documents.

Platform Features:

  • ChatSession: AI chat conversations
  • ChatMessage: Individual chat messages
  • CalendarEvent: Calendar integration
  • ImportJob: Background job tracking
  • Notification: User notifications

These models support platform features and system operations.

Key Relationships:

  • Users own all content (cascading deletes)
  • Courses organize related content
  • Study materials link to source notes/documents
  • Chat sessions can be course-specific
  • Jobs track async operations

All models follow consistent relationship patterns for data integrity.

Core Models

Study Materials Models

FlashcardSet Model:

model FlashcardSet {
  id          String  @id @default(cuid())
  title       String
  description String?
  difficulty  Difficulty
  tags        String[] @default([])
  color       String   @default("blue")
  userId      String
  courseId    String?
  noteId      String?
  documentId  String?

  // Relationships
  user       User        @relation(fields: [userId], references: [id], onDelete: Cascade)
  course     Course?     @relation(fields: [courseId], references: [id], onDelete: SetNull)
  note       Note?       @relation(fields: [noteId], references: [id], onDelete: SetNull)
  document   Document?   @relation(fields: [documentId], references: [id], onDelete: SetNull)
  flashcards Flashcard[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Individual Flashcard:

model Flashcard {
  id             String       @id @default(cuid())
  question       String
  answer         String
  hint           String?
  explanation    String?
  difficulty     Difficulty
  flashcardSetId String

  flashcardSet FlashcardSet @relation(fields: [flashcardSetId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Quiz Container:

model Quiz {
  id          String     @id @default(cuid())
  title       String
  description String?
  difficulty  Difficulty
  timeLimit   Int?
  totalPoints Int
  userId      String
  courseId    String?
  noteId      String?
  documentId  String?

  // Relationships
  user          User           @relation(fields: [userId], references: [id], onDelete: Cascade)
  course        Course?        @relation(fields: [courseId], references: [id], onDelete: SetNull)
  note          Note?          @relation(fields: [noteId], references: [id], onDelete: SetNull)
  document      Document?      @relation(fields: [documentId], references: [id], onDelete: SetNull)
  questions     Question[]
  quizResponses QuizResponse[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Question Model:

model Question {
  id            String       @id @default(cuid())
  type          QuestionType
  question      String
  options       String?
  correctAnswer String
  explanation   String?
  hint          String?
  difficulty    Difficulty
  points        Int
  quizId        String

  quiz Quiz @relation(fields: [quizId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

StudyGuide Model:

model StudyGuide {
  id         String  @id @default(cuid())
  title      String
  content    String
  noteId     String?
  courseId   String?
  documentId String?
  userId     String

  // Relationships
  note     Note?     @relation(fields: [noteId], references: [id], onDelete: SetNull)
  course   Course?   @relation(fields: [courseId], references: [id], onDelete: SetNull)
  document Document? @relation(fields: [documentId], references: [id], onDelete: SetNull)
  user     User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Features:

  • AI-generated comprehensive study materials
  • Links to source notes or documents
  • Rich text content support
  • Course association for organization

AI Generation: All study materials are automatically generated from source content using AI models, with manual editing capabilities.

System Models

ChatSession Model:

Chat Session Container
model ChatSession {
  id       String  @id @default(cuid())
  title    String
  courseId String?
  userId   String

  // Relationships
  course   Course?       @relation(fields: [courseId], references: [id], onDelete: SetNull)
  user     User          @relation(fields: [userId], references: [id], onDelete: Cascade)
  messages ChatMessage[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

ChatMessage Model:

Individual Chat Messages
model ChatMessage {
  id            String      @id @default(cuid())
  content       String
  messageType   MessageType
  references    String?
  chatSessionId String

  chatSession ChatSession @relation(fields: [chatSessionId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

CalendarEvent Model:

Calendar Integration
model CalendarEvent {
  id               String    @id @default(cuid())
  title            String
  description      String?
  startDate        DateTime
  endDate          DateTime?
  eventType        EventType
  location         String?
  priority         Priority?
  color            String
  googleEventId    String?
  googleCalendarId String?
  courseId         String?
  documentId       String?
  userId           String

  // Relationships
  course   Course?   @relation(fields: [courseId], references: [id], onDelete: SetNull)
  document Document? @relation(fields: [documentId], references: [id], onDelete: SetNull)
  user     User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Features:

  • Google Calendar synchronization
  • Multiple event types (assignments, exams, lectures)
  • Priority levels and color coding
  • Auto-generation from documents (syllabi)

ImportJob Model:

Background Job Tracking
model ImportJob {
  id           String       @id @default(cuid())
  source       ImportSource
  sourceUrl    String?
  title        String?
  status       JobStatus
  progress     Int
  errorMessage String?
  completedAt  DateTime?
  userId       String
  noteId       String?      @unique

  // Relationships
  user User  @relation(fields: [userId], references: [id], onDelete: Cascade)
  note Note? @relation(fields: [noteId], references: [id])

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Job Processing:

  • File upload processing
  • YouTube video transcription
  • Audio file conversion
  • Progress tracking and error handling

Session Model:

Better-auth Sessions
model Session {
  id        String   @id @default(cuid())
  expiresAt DateTime
  token     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  ipAddress String?
  userAgent String?
  userId    String

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Account Model:

OAuth Account Linking
model Account {
  id                String  @id @default(cuid())
  accountId         String
  providerId        String
  userId            String
  accessToken       String?
  refreshToken      String?
  idToken           String?
  accessTokenExpiresAt DateTime?
  refreshTokenExpiresAt DateTime?
  scope             String?
  password          String?
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([providerId, accountId])
}

Enums and Types

Database Relationships

Relationship Patterns: The schema follows consistent patterns with cascading deletes for user-owned content and null set for optional associations.

Core Relationships

  • User owns all content with cascading deletes
  • Course serves as organizational container
  • Notes and Documents are source material for AI generation
  • Study materials link back to their source content

Data Integrity Features

  • Foreign Key Constraints: Ensure referential integrity
  • Cascading Deletes: Clean up orphaned records
  • Unique Constraints: Prevent duplicate data
  • Null Set Relations: Preserve data when optional relations are removed

Search and Indexing

The database includes specialized models for search functionality:

Search Index Model
model DocumentSearchIndex {
  id         String @id @default(cuid())
  documentId String
  content    String
  pageNumber Int?
  chunkIndex Int
  embedding  String?

  document Document @relation(fields: [documentId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Database Complete: This schema supports the full SophieAI platform with AI-powered features, file processing, search capabilities, and integrated study tools.

iOS Subscriptions (Apple)

Configure App Store subscriptions, server notifications, and confirm purchase flow.