docs/backend-doc/10-database-schema.md

Database Schema Reference

Overview

The application uses SQLite as the primary database with a Supabase-compatible query interface for easy future migration.

• Database Location: ./data/app.db (configurable via DATABASE_PATH env var)
• Technology: SQLite with better-sqlite3 driver
• Features:
  • Automatic schema initialization on first access
  • Foreign key constraints enforced
  • Automatic camelCase ↔ snake_case conversion
  • Supabase-compatible query builder interface

Tables

Users Table

Stores user account information.

Table Name: users

Field	Type	Description	Constraints
id	TEXT	Unique user identifier (UUID)	PRIMARY KEY
username	TEXT	Username for authentication	NOT NULL, UNIQUE
created_at	TEXT	Account creation timestamp (ISO 8601)	NOT NULL

Indexes:

• idx_users_username on username (for fast username lookups)

TypeScript Model: User in src/lib/types/models.ts

---

Conversations Table

Stores conversation metadata and settings.

Table Name: conversations

Field	Type	Description	Constraints
id	TEXT	Unique conversation identifier (UUID)	PRIMARY KEY
user_id	TEXT	Owner's user ID	NOT NULL, FOREIGN KEY → users(id)
student_prompt_id	TEXT	Student personality template ID (e.g., 'grade_3', 'coach_direct')	NOT NULL
coach_prompt_id	TEXT	Coach personality type ('empathetic', 'structured', 'balanced')	NOT NULL
title	TEXT	Conversation title	NULL
title_source	TEXT	Title generation source ('auto', 'auto-locked', 'user')	DEFAULT 'auto'
summary	TEXT	Auto-generated summary from previous conversations	NULL
system_prompt	TEXT	Stored system prompt (for graceful degradation if template removed)	NULL
student_last_response_id	TEXT	OpenAI Responses API response ID for student continuity	NULL
student_last_response_id_timestamp	TEXT	Timestamp for student response ID expiration	NULL
coach_last_response_id	TEXT	OpenAI Responses API response ID for coach continuity	NULL
coach_last_response_id_timestamp	TEXT	Timestamp for coach response ID expiration	NULL
user_to_student_message_count	INTEGER	Count of user→student messages (for coach ad trigger)	DEFAULT 0
coach_ad_first_shown_at	TEXT	Timestamp when coach ad was first shown	NULL
source_conversation_id	TEXT	ID of conversation this was branched from	NULL
branched_from_message_id	TEXT	ID of message where branch was created	NULL
created_at	TEXT	Creation timestamp (ISO 8601)	NOT NULL
updated_at	TEXT	Last update timestamp (ISO 8601)	NOT NULL
last_active_at	TEXT	Last activity timestamp (for sorting)	NOT NULL

Foreign Keys:

• user_id REFERENCES users(id) ON DELETE CASCADE

Indexes:

• idx_conversations_user_id on user_id (for user's conversations)
• idx_conversations_last_active on last_active_at DESC (for sorting by activity)
• idx_conversations_student_prompt_id on student_prompt_id (for template lookups)

Derived Fields (NOT Stored):

• studentName - Derived from student_prompt_id using prompt templates
• coachName - Derived from coach_prompt_id using coach personas
• These fields are added to API responses via object spread for backward compatibility

TypeScript Model: Conversation in src/lib/types/models.ts

Important Notes:

• title_source:
  • 'auto' - Waiting for 15 messages before generating title
  • 'auto-locked' - Title generated once and locked
  • 'user' - User manually edited the title
• system_prompt enables conversations to continue working even if student_prompt_id template is removed
• Response IDs expire after a certain time (checked via *_timestamp fields)

---

Messages Table

Stores all chat messages in conversations.

Table Name: messages

Field	Type	Description	Constraints
id	TEXT	Unique message identifier (UUID)	PRIMARY KEY
conversation_id	TEXT	Parent conversation ID	NOT NULL, FOREIGN KEY → conversations(id)
role	TEXT	Message role ('user', 'assistant', 'system')	NOT NULL
speaker	TEXT	Who is speaking ('student', 'coach')	NOT NULL
content	TEXT	Message text content	NOT NULL
response_id	TEXT	OpenAI Responses API response ID	NULL
timestamp	TEXT	Message timestamp (ISO 8601)	NOT NULL
quoted_text	TEXT	Selected text being replied to	NULL
quoted_message_id	TEXT	ID of message being replied to	NULL
is_historical	INTEGER	Whether message is from branched conversation (0/1)	DEFAULT 0

Foreign Keys:

• conversation_id REFERENCES conversations(id) ON DELETE CASCADE

Indexes:

• idx_messages_conversation_id on conversation_id (for fetching conversation messages)
• idx_messages_timestamp on timestamp (for chronological ordering)

TypeScript Model: Message in src/lib/types/models.ts

Important Notes:

• role values:
  • 'user' - Message from teacher
  • 'assistant' - Response from student or coach
  • 'system' - System message (context information)
• speaker values:
  • 'student' - Student is speaking
  • 'coach' - Coach is speaking
• is_historical:
  • Stored as INTEGER in SQLite (0 or 1)
  • Automatically converted to boolean (true/false) in API responses
  • Marks messages copied from branched conversations as read-only context

---

Sessions Table

Stores authentication sessions.

Table Name: sessions

Field	Type	Description	Constraints
id	TEXT	Unique session identifier (UUID)	PRIMARY KEY
user_id	TEXT	User ID for this session	NOT NULL, FOREIGN KEY → users(id)
token	TEXT	Session token	NOT NULL, UNIQUE
expires_at	TEXT	Expiration timestamp (ISO 8601)	NOT NULL
created_at	TEXT	Creation timestamp (ISO 8601)	NOT NULL

Foreign Keys:

• user_id REFERENCES users(id) ON DELETE CASCADE

Indexes:

• idx_sessions_token on token (for token lookups)
• idx_sessions_user_id on user_id (for user's sessions)
• idx_sessions_expires_at on expires_at (for cleanup queries)

TypeScript Model: Session in src/lib/types/models.ts

---

Database Features

CamelCase ↔ snake_case Conversion

The database client automatically converts between JavaScript camelCase and SQL snake_case:

JavaScript (Application):

{ userId: "123", createdAt: "2025-01-01" }

SQL (Database):

{ user_id: "123", created_at: "2025-01-01" }

This conversion is transparent to application code.

Supabase-Compatible Query Interface

The database client implements a query builder interface compatible with Supabase:

// This works with both SQLite and Supabase
await db.from('users')
  .select()
  .eq('username', 'john')
  .single()

This enables easy migration from SQLite to Supabase in the future.

Foreign Key Enforcement

Foreign key constraints are enforced by the database:

• Deleting a user cascades to their conversations and sessions
• Deleting a conversation cascades to its messages
• Invalid foreign keys are rejected

Auto-Generated UUIDs

All tables use UUID primary keys that are automatically generated when not provided.

---

Important Implementation Notes

Derived Fields

The following fields are derived at runtime and NOT stored in the database:

• studentName - Derived from student_prompt_id by looking up the prompt template
• coachName - Derived from coach_prompt_id by looking up the coach persona

Why? This ensures names always match the source of truth (prompt templates) and reduces data redundancy.

API Behavior: These fields are added to API responses via object spread operators for backward compatibility.

Code Location: See conversation repository (src/lib/repositories/conversation-repository.ts)

Boolean Field Handling

SQLite stores booleans as integers (0/1), but API responses use proper JSON booleans (true/false).

Example - isHistorical field:

• Database storage: 0 or 1 (INTEGER)
• API response: true or false (boolean)
• Conversion: Handled by normalizeMessage() in MessageRepository

Why? Database implementation details (SQLite's integer representation) should not leak into API contracts. Consumers expect standard JSON types.

Code Location: See src/lib/repositories/message-repository.ts

Template Graceful Degradation

When a student personality template is removed from student-prompts.ts:

• Existing conversations continue working using the stored system_prompt field
• New conversations are blocked (validation prevents creation with removed template)
• Warning logs are generated when deprecated templates are used

This allows safe removal of templates without breaking existing conversations.

---

Migration Path to Supabase

The database client is designed for easy migration to Supabase:

1. Query Interface: Already Supabase-compatible
2. Schema: Can be directly translated to PostgreSQL
3. Code Changes: Minimal - swap SQLiteClient for SupabaseClient
4. Data Migration: Export from SQLite → Import to PostgreSQL

Location: Database client interface at src/lib/db/database-client.interface.ts

---

Code References

TypeScript Models

• Path: src/lib/types/models.ts
• Models: User, Conversation, Message, Session

Database Client

• Path: src/lib/db/
• Files:
  • database-client.interface.ts - Abstract interface
  • sqlite-client.ts - SQLite implementation
  • index.ts - Singleton initialization and schema SQL

Repositories

• Path: src/lib/repositories/
• Files:
  • user-repository.ts - User CRUD operations
  • conversation-repository.ts - Conversation CRUD operations
  • message-repository.ts - Message CRUD operations
  • session-repository.ts - Session CRUD operations

---

Database Configuration

Environment Variable:

DATABASE_PATH=./data/app.db  # Default location

Initialization:

• Database and schema are auto-created on first access
• No manual migration steps required
• Tables created with IF NOT EXISTS (idempotent)

Singleton Pattern:

• Single database connection shared across the application
• Accessed via getDatabase() from src/lib/db/index.ts