Database Schema
Relevant source files
The following files were used as context for generating this wiki page:
The ClassMate database schema is designed to support a multi-tenant, AI-powered meeting assistant. It utilizes a hybrid storage approach: relational storage for user management and session metadata, and vector storage for semantic search and AI retrieval. The architecture is built to scale from local development using SQLite to production environments using PostgreSQL with the pgvector extension.
The schema primary focuses on capturing audio session metadata, managing AI-generated transcripts and summaries, handling user authentication and OAuth integrations (Google/Microsoft), and storing vectorized representations of content for the study assistant features.
Core Relational Schema
The core relational database manages the lifecycle of recording sessions and the resulting AI artifacts. It tracks user details, session statuses, and granular transcript chunks.
Entity Relationship Diagram
The following diagram illustrates the relationships between users, their recording sessions, and the associated transcription data.
Session and Transcription Tables
The sessions table is the central repository for meeting data, storing both metadata (duration, status) and the final AI-generated outputs (summary, key points).
| Field | Type | Description |
|---|---|---|
id | String (PK) | Unique session identifier. |
user_id | String | Foreign key to the owner of the session. |
status | String | Current state (e.g., recording, processing, completed). |
total_chunks | Integer | Total number of audio chunks expected. |
transcript | Text | Full aggregated text of the session. |
summary | Text | AI-generated summary of the meeting. |
key_points | Text (JSON) | JSON string containing extracted main points. |
action_items | Text (JSON) | JSON string containing tasks identified by AI. |
Sources: ai_backend/database.py:16-33
To support real-time processing and large audio files, the system breaks transcripts into chunks stored in the transcript_chunks table.
| Field | Type | Description |
|---|---|---|
id | String (PK) | Unique chunk identifier. |
session_id | String | Reference to the parent session. |
chunk_index | Integer | Sequence order for reassembly. |
text | Text | Transcribed content of the specific audio chunk. |
confidence | Float | AI model's confidence score for the transcription. |
Sources: ai_backend/database.py:35-45
AI & Vector Memory Schema
For semantic search and Q&A capabilities, ClassMate uses a vector storage system. In production, this utilizes the pgvector extension for high-performance similarity searches.
Vector Storage Architecture
The system attempts to initialize the vector extension and add a high-dimensional vector column to the storage table.
Vector Documents
The vector_documents table stores various types of content (transcripts, summaries, notes) along with their embeddings.
| Field | Type | Description |
|---|---|---|
id | String (PK) | UUID for the document. |
content | Text | The raw text being indexed. |
content_type | String | Type: transcript, summary, note, or flashcard. |
embedding | Vector(1536) | Semantic representation (Postgres only). |
metadata_json | Text | JSON blob for additional context. |
Sources: ai_backend/agents/vector_store.py:23-33, ai_backend/agents/vector_store.py:82-84
Infrastructure & Connectivity
The database is deployed via Docker and managed through Terraform on AWS. The production environment utilizes a managed RDS PostgreSQL instance.
Connection Configuration
The system defaults to SQLite for local development but switches to PostgreSQL in production via environment variables.
| Environment Variable | Default Value | Description |
|---|---|---|
DATABASE_URL | sqlite:///./classmate.db | Main database connection string. |
POSTGRES_DB | classmate | Database name in production. |
REDIS_URL | redis://localhost:6379/0 | Used for task queuing and caching. |
Sources: ai_backend/database.py:9, docker-compose.prod.yml:100-101
AWS Infrastructure (Terraform)
In production, the database is isolated within a private subnet and protected by specific security groups.
- VPC Subnet: Private (
10.0.2.0/24) for RDS. - Engine: PostgreSQL 15.4.
- Security: Port 5432 restricted to API server traffic only.
- Storage: Encrypted GP2 volumes with 7-day backup retention.
Sources: infra/terraform/main.tf:109-147
Conclusion
The ClassMate database schema provides a robust foundation for AI-driven education tools. By separating transient transcription chunks from session metadata and incorporating a dedicated vector store, the system efficiently handles both real-time data ingestion and complex semantic retrieval. The design ensures portability between local development and cloud-scale production environments.