Spaces:

taboola-cz
/

sel-chat-coach

Running

┌─────────────────────────────────────────┐
│         Application Code                │
│    (API Routes, Services, etc.)         │
└────────────────┬────────────────────────┘
                 │
┌────────────────▼────────────────────────┐
│         Repository Layer                │
│  UserRepo, ConversationRepo, etc.       │
└────────────────┬────────────────────────┘
                 │
┌────────────────▼────────────────────────┐
│      DatabaseClient Interface           │
│   (Unified query builder API)           │
└────────────┬───────────────┬────────────┘
             │               │
    ┌────────▼──────┐   ┌───▼──────────┐
    │ SQLiteClient  │   │ SupabaseClient│
    │ (better-      │   │ (pg + @supa-  │
    │  sqlite3)     │   │  base/js)     │
    └───────────────┘   └───────────────┘

Code Location: src/lib/db/

database-client.interface.ts - Interface definition
sqlite-client.ts - SQLite implementation
supabase-client.ts - Supabase implementation
case-converters.ts - camelCase ↔ snake_case utilities
index.ts - Provider selection and initialization

Repository Pattern

Five repositories abstract database operations:

UserRepository - User CRUD operations
ConversationRepository - Conversation CRUD + concurrency control
MessageRepository - Message CRUD + boolean normalization
SessionRepository - Authentication sessions
PromptTemplateRepository - Prompt template management

Code Location: src/lib/repositories/

Pattern:

import { getDatabase } from '@/lib/db';

class UserRepository {
  private db = getDatabase();

  async findByUsername(username: string): Promise<User | null> {
    const result = await this.db
      .from<User>('users')
      .select()
      .eq('username', username)
      .single();
    return result.data;
  }
}

Key Benefit: Repositories are completely unaware of which database provider is active.

Provider Selection

Environment Configuration

# Choose provider (default: sqlite)
DATABASE_PROVIDER=sqlite      # or 'supabase'

Initialization Logic (src/lib/db/index.ts:107-135):

if (DATABASE_PROVIDER === 'supabase') {
  // Initialize Supabase client
  dbInstance = new SupabaseDatabaseClient({
    supabaseUrl: SUPABASE_URL,
    supabaseServiceRoleKey: SUPABASE_SERVICE_ROLE_KEY,
    supabaseDbUrl: SUPABASE_DB_URL,
  });
} else {
  // Initialize SQLite client (default)
  dbInstance = new SQLiteClient(DATABASE_PATH);
  dbInstance.initializeSchema(SCHEMA_SQL);
}

Singleton Pattern

Single database connection per application
Accessed via getDatabase() from src/lib/db/index.ts
Lazy initialization on first call
Test helpers: closeDatabase(), resetDatabase()

SQLite Provider

Configuration

Environment Variables:

DATABASE_PROVIDER=sqlite                 # Default if not set
DATABASE_PATH=./data/app.db             # Default location

Required Dependencies:

better-sqlite3 - Synchronous SQLite driver

Initialization Behavior

When the application starts with SQLite:

Directory Creation (src/lib/db/index.ts:132-135)

const dbDir = path.dirname(DATABASE_PATH);
fs.mkdirSync(dbDir, { recursive: true });

WAL Mode Enabled (src/lib/db/sqlite-client.ts:22)
```
this.db.pragma('journal_mode = WAL');
```
- Benefits: Better concurrent read performance
- Files Created:
  - app.db - Main database file
  - app.db-wal - Write-Ahead Log (temporary)
  - app.db-shm - Shared memory file (temporary)
Foreign Keys Enabled (src/lib/db/sqlite-client.ts:23)
```
this.db.pragma('foreign_keys = ON');
```
Schema Creation (src/lib/db/sqlite-client.ts:27-29)
- Executes schema SQL with IF NOT EXISTS clauses
- Idempotent (safe to run multiple times)
- Creates all tables and indexes

Query Builder Implementation

Class: SQLiteQueryBuilder (src/lib/db/sqlite-client.ts:154-369)

Features:

Builds SQL strings dynamically
Parameterized queries (prevents SQL injection)
Auto-converts camelCase to snake_case
Chainable methods: select(), insert(), update(), delete(), eq(), neq(), gt(), gte(), lt(), lte(), like(), in(), order(), limit()
Terminal methods: single() (one result), execute() (array results)

Example Query Building:

// Application code
await db.from('users')
  .select()
  .eq('username', 'alice')
  .single();

// Generated SQL
SELECT * FROM users WHERE username = ? LIMIT 1

// Parameters: ['alice']

Atomic Operations

Method: atomicIncrement() (src/lib/db/sqlite-client.ts:135-149)

Purpose: Race-condition-safe counter updates

Implementation:

UPDATE table
SET column = COALESCE(column, 0) + ?
WHERE id = ?

Usage Example:

await db.atomicIncrement('conversations', 'userToStudentMessageCount', conversationId, 1);

Why Atomic?

Read-modify-write happens in single SQL statement
No race conditions even with concurrent requests
Database handles locking internally

Prompt Template Seeding

Manual Seeding Required:

npm run db:seed

What it does:

Runs src/scripts/seed-prompts.ts
Reads TypeScript prompt files (src/lib/prompts/)
Inserts into prompt_templates table
Uses INSERT OR REPLACE (idempotent)

When to run:

After initial setup
After modifying prompt templates in TypeScript files
After deleting database file

Performance Characteristics

Strengths:

Zero network latency (file-based)
Fast for read-heavy workloads (WAL mode)
Simple deployment (single file)
No external dependencies

Limitations:

Single writer at a time (write serialization)
File-locked (one process only, not multi-server)
Not suitable for distributed systems

Best For:

Single-server deployments
Development and testing
Low to medium traffic (<100 req/s)
Applications without horizontal scaling needs

File Structure

./data/
├── app.db          # Main database file (all tables and data)
├── app.db-wal      # Write-Ahead Log (temporary, auto-managed)
└── app.db-shm      # Shared memory (temporary, auto-managed)

Important Notes:

.db-wal and .db-shm files are checkpoint files
Created/deleted automatically by SQLite
Safe to ignore in version control (add to .gitignore)
Only .db file needs backup

Supabase Provider

Configuration

Environment Variables:

DATABASE_PROVIDER=supabase

# Required credentials (all three must be set)
SUPABASE_URL=https://xxx.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJ...
SUPABASE_DB_URL=postgresql://postgres:password@db.xxx.supabase.co:5432/postgres

Alternative Naming:

SUPABASE_DB_URL or SUPABASE_DATABASE_URL (both work)

Required Dependencies:

@supabase/supabase-js - Supabase JavaScript client
pg - PostgreSQL client library

Why Service Role Key?

Service Role Key vs Anon Key:

Feature	Service Role Key	Anon Key
Bypasses RLS	✅ Yes	❌ No
Admin privileges	✅ Yes	❌ No
Schema creation	✅ Yes	❌ No
Safe for client-side	❌ No	✅ Yes

Why we need it:

Backend needs admin privileges to create schema
Must bypass Row Level Security (RLS) policies
Used only server-side, never exposed to clients

Where to find it:

Supabase Dashboard → Settings → API → "service_role" key

Initialization Behavior

When the application starts with Supabase:

Dual Client Setup (src/lib/db/supabase-client.ts:23-59)
- SupabaseClient - For query builder API (@supabase/supabase-js)
- PgClient - For raw SQL and schema initialization (pg)

PostgreSQL Connection (src/lib/db/supabase-client.ts:47-56)

this.pgClient = new pg.Client({ connectionString: supabaseDbUrl });
await this.pgClient.connect();

BigInt Type Handling (src/lib/db/supabase-client.ts:60-62)
```
pgTypes.setTypeParser(20, value => parseInt(value, 10));
```
- PostgreSQL returns bigint as strings
- Parser converts to JavaScript numbers
- Affects all integer columns
Schema Creation (src/lib/db/supabase-client.ts:64-65)
- Executes schema SQL with IF NOT EXISTS clauses
- Idempotent (safe to run multiple times)
- Creates all tables and indexes
Automatic Prompt Seeding (src/lib/db/supabase-client.ts:67-88)
- Runs automatically during initialization (no manual step!)
- Reads TypeScript prompt files (src/lib/prompts/)
- Inserts student prompts (9 personalities) + coach prompts (3 personas)
- Uses ON CONFLICT (id) DO NOTHING (idempotent)
- promptsSeeded flag prevents duplicate seeding

Query Builder Implementation

Class: SupabaseQueryBuilder (src/lib/db/supabase-client.ts:232-423)

Features:

Wraps Supabase.js API
Stores filters as functions, applies lazily
Auto-converts camelCase to snake_case
Identical API to SQLite query builder

Example Query Building:

// Application code (identical to SQLite)
await db.from('users')
  .select()
  .eq('username', 'alice')
  .single();

// Supabase API calls (translated internally)
supabase.from('users')
  .select()
  .eq('username', 'alice')
  .single()

Placeholder Conversion

Challenge: SQL placeholder syntax differs between SQLite and PostgreSQL.

Provider	Placeholder Style	Example
SQLite	`?`	`SELECT * FROM users WHERE id = ?`
PostgreSQL	`$1, $2, $3`	`SELECT * FROM users WHERE id = $1`

Solution: Automatic conversion (src/lib/db/supabase-client.ts:90-104)

// Application code (uses SQLite-style placeholders)
await db.raw('UPDATE users SET name = ? WHERE id = ?', ['Alice', '123'])

// Converted to PostgreSQL style internally
UPDATE users SET name = $1 WHERE id = $2
// Parameters: ['Alice', '123']

Validation:

Counts ? placeholders in SQL
Verifies parameter array length matches
Throws error if mismatch detected

Atomic Operations

Method: atomicIncrement() (src/lib/db/supabase-client.ts:144-168)

Implementation (identical to SQLite):

UPDATE table
SET column = COALESCE(column, 0) + $1
WHERE id = $2

Usage (identical to SQLite):

await db.atomicIncrement('conversations', 'userToStudentMessageCount', conversationId, 1);

Prompt Template Seeding

Automatic Seeding (no manual steps!):

Runs during SupabaseDatabaseClient initialization
Happens BEFORE application is ready to use
Idempotent (safe to restart server)
promptsSeeded flag prevents duplicate seeding

Manual Seeding (optional, for updates):

npm run db:seed

Seeding Logic (src/lib/db/supabase-client.ts:31-88):

async seedPromptTemplates() {
  if (this.promptsSeeded) return; // Prevent duplicate seeding

  // Load student prompts (9 personalities)
  const studentPrompts = Object.entries(studentPromptsMap).map(([id, config]) => ({
    id,
    type: 'student',
    name: config.name,
    // ... other fields
  }));

  // Load coach prompts (3 personas)
  const coachPrompts = Object.entries(coachPersonas).map(([id, persona]) => ({
    id,
    type: 'coach',
    name: persona.name,
    // ... other fields
  }));

  // Insert all prompts (idempotent)
  await this.pgClient.query(`
    INSERT INTO prompt_templates (id, type, name, ...)
    VALUES ${placeholders}
    ON CONFLICT (id) DO NOTHING
  `, values);

  this.promptsSeeded = true;
}

Performance Characteristics

Strengths:

Handles concurrent writes (PostgreSQL)
Connection pooling (via Supabase)
Horizontal scaling (multi-server ready)
Managed backups and replication
Geographic distribution

Limitations:

Network latency (~10-50ms per query)
External dependency (requires internet)
More complex troubleshooting
Cost considerations (paid service)

Best For:

Multi-server deployments
Production environments
High traffic applications (>100 req/s)
Applications requiring horizontal scaling
Teams wanting managed database infrastructure

Case Conversion System

The Problem

JavaScript Conventions: camelCase

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

SQL Conventions: snake_case

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

The Solution

Transparent bidirectional conversion (src/lib/db/case-converters.ts)

Functions:

toSnakeCase(str) - Convert string: userId → user_id
toCamelCase(str) - Convert string: user_id → userId
objectToSnakeCase(obj) - Recursive object conversion (for inserts/updates)
objectToCamelCase(obj) - Recursive conversion (for query results)

Example Flow:

// 1. Application inserts record (camelCase)
await db.from('users').insert({
  userId: '123',
  userName: 'alice',
  createdAt: '2025-01-01'
}).execute();

// 2. Conversion to snake_case (internal)
INSERT INTO users (user_id, user_name, created_at)
VALUES ('123', 'alice', '2025-01-01')

// 3. Application queries record
const result = await db.from('users').select().eq('userId', '123').single();

// 4. Conversion back to camelCase (internal)
result.data = {
  userId: '123',
  userName: 'alice',
  createdAt: '2025-01-01'
}

Smart Detection:

Skips conversion if already in target format
Preserves non-convertible fields (e.g., id stays id)
Handles nested objects recursively

UUID Auto-Generation

Feature: ensureId() function (src/lib/db/case-converters.ts:67-73)

Purpose: Auto-generates UUID for records without id field

// Application code (no id provided)
await db.from('users').insert({ username: 'alice' }).execute();

// Internal (UUID auto-generated)
await db.from('users').insert({
  id: '550e8400-e29b-41d4-a716-446655440000', // Auto-generated
  username: 'alice'
}).execute();

Query Builder API

Common Patterns

Both SQLite and Supabase support identical query builder API.

SELECT Queries

// Single record
const user = await db.from('users')
  .select()
  .eq('id', '123')
  .single();

// Multiple records
const users = await db.from('users')
  .select()
  .execute();

// With filters
const activeConversations = await db.from('conversations')
  .select()
  .eq('userId', '123')
  .gte('lastActiveAt', yesterday)
  .order('lastActiveAt', { ascending: false })
  .limit(10)
  .execute();

// Complex filters
const messages = await db.from('messages')
  .select()
  .eq('conversationId', 'abc')
  .neq('role', 'system')
  .in('speaker', ['student', 'coach'])
  .execute();

INSERT Queries

// Single insert
await db.from('users')
  .insert({ username: 'alice' })
  .execute();

// Batch insert
await db.from('users')
  .insert([
    { username: 'alice' },
    { username: 'bob' },
    { username: 'charlie' }
  ])
  .execute();

// Auto-generated IDs
// If 'id' field is missing, UUID is auto-generated
await db.from('users')
  .insert({ username: 'alice' }) // id auto-generated
  .execute();

UPDATE Queries

// Simple update
await db.from('users')
  .update({ username: 'bob' })
  .eq('id', '123')
  .execute();

// Conditional update
await db.from('conversations')
  .update({ titleSource: 'auto-locked' })
  .eq('id', conversationId)
  .eq('titleSource', 'auto') // Only update if currently 'auto'
  .execute();

// Multiple filters
await db.from('sessions')
  .update({ expiresAt: newExpiry })
  .eq('userId', userId)
  .lt('expiresAt', now)
  .execute();

DELETE Queries

// Single delete
await db.from('users')
  .delete()
  .eq('id', '123')
  .execute();

// Conditional delete
await db.from('sessions')
  .delete()
  .lt('expiresAt', now)
  .execute();

// Multiple filters
await db.from('messages')
  .delete()
  .eq('conversationId', conversationId)
  .eq('isHistorical', true)
  .execute();

Raw SQL Queries

For complex operations not supported by query builder:

// Use ? placeholders (works for both SQLite and Supabase)
const result = await db.raw(
  'UPDATE users SET name = ? WHERE id = ?',
  ['Alice', '123']
);

// SQLite: Executes as-is
// Supabase: Converts to 'UPDATE users SET name = $1 WHERE id = $2'

// Complex query example
const stats = await db.raw(`
  SELECT
    COUNT(*) as total,
    COUNT(CASE WHEN role = 'user' THEN 1 END) as user_messages
  FROM messages
  WHERE conversation_id = ?
`, [conversationId]);

Parameter Validation:

Counts ? placeholders in SQL
Verifies parameter array length matches
Throws error if mismatch detected

Error Handling Pattern

All queries return { data, error } object:

const result = await db.from('users')
  .select()
  .eq('username', 'alice')
  .single();

if (result.error) {
  console.error('Query failed:', result.error);
  return;
}

// Use result.data safely
const user = result.data;

Why this pattern?

No exceptions thrown (explicit error handling)
Type-safe (TypeScript knows data might be null)
Consistent across both providers

Atomic Operations

For race-condition-safe updates:

// Counter increment (atomic)
await db.atomicIncrement(
  'conversations',
  'userToStudentMessageCount',
  conversationId,
  1 // Increment by 1
);

// SQL executed:
// UPDATE conversations
// SET user_to_student_message_count = COALESCE(user_to_student_message_count, 0) + 1
// WHERE id = ?

// Conditional update (atomic check-and-set)
const result = await db.raw(
  'UPDATE conversations SET status = ? WHERE id = ? AND status = ?',
  ['generating', id, 'auto']
);

// Returns number of affected rows
// 0 = condition not met (status was not 'auto')
// 1 = update successful

Use Cases:

Message counters (e.g., userToStudentMessageCount)
Status transitions (e.g., 'auto' → 'generating')
Preventing duplicate operations
Optimistic locking

Data Normalization

Boolean Field Handling

Challenge: SQLite stores booleans as integers (0/1), but APIs should return JSON booleans (true/false).

Solution: Repository-level normalization

Example - isHistorical field (src/lib/repositories/message-repository.ts:114-121):

// Database storage (SQLite)
{ is_historical: 1 }  // INTEGER

// Repository normalization
private normalizeMessage(msg: Message): Message {
  return {
    ...msg,
    isHistorical: msg.isHistorical ? true : false, // Convert to boolean
  };
}

// API response (proper JSON)
{ isHistorical: true }  // boolean

Why This Matters:

Database implementation details shouldn't leak into API contracts
API consumers expect standard JSON types
Maintains proper architectural boundaries
Works identically for both SQLite and Supabase

CLAUDE.md Reference: "Testing Philosophy: Intention is Critical" section documents the decision rationale.

Derived Fields

Pattern: Some fields are NOT stored in database, but derived at runtime.

Example - studentName and coachName (src/lib/repositories/conversation-repository.ts):

// Database schema
conversations {
  student_prompt_id: 'grade_3',  // Stored
  coach_prompt_id: 'empathetic',  // Stored
  // studentName: NOT STORED
  // coachName: NOT STORED
}

// API response
{
  studentPromptId: 'grade_3',
  coachPromptId: 'empathetic',
  studentName: '小三學生',        // Derived from prompt template
  coachName: '同理心教練',        // Derived from coach persona
}

Why Derived?

Names always match current prompt templates (source of truth)
Reduces data redundancy
No migration needed when prompt names change
Fields added via object spread for backward compatibility

Implementation (src/lib/repositories/conversation-repository.ts:70-94):

const conversations = await this.db.from<Conversation>('conversations')
  .select()
  .eq('userId', userId)
  .execute();

// Derive names from prompt templates
for (const conv of conversations.data) {
  const studentConfig = await promptService.getStudentConfig(conv.studentPromptId);
  const coachPersona = await promptService.getCoachPrompt(conv.coachPromptId);

  conv.studentName = studentConfig.name;
  conv.coachName = coachPersona.name;
}

Prompt Management Integration

PromptService

Location: src/lib/services/prompt-service.ts

Features:

5-minute TTL cache - Reduces database queries
Graceful fallback - Falls back to TypeScript files if database empty
Database-first - Checks PromptTemplateRepository before fallback
Validation methods - isValidStudentId(), isValidCoachId()
Cache invalidation - invalidateCache() for real-time updates

Architecture:

┌─────────────────────────────────┐
│      API Routes / Services      │
└────────────┬────────────────────┘
             │
┌────────────▼────────────────────┐
│       PromptService             │
│   (5-minute TTL cache)          │
└────┬───────────────────┬────────┘
     │                   │
     │ Database-first    │ Fallback
     │                   │
┌────▼──────────────┐   ┌▼────────────────────┐
│ PromptTemplate    │   │ TypeScript Files    │
│ Repository        │   │ (student-prompts.ts │
│ (Database)        │   │  coach-prompts.ts)  │
└───────────────────┘   └─────────────────────┘

Prompt Seeding Flow

TypeScript → Database:

Source of Truth: TypeScript files (src/lib/prompts/)
- student-prompts.ts - 9 student personalities
- coach-prompts.ts - 3 coach personas
Seeding:
- SQLite: Manual via npm run db:seed
- Supabase: Automatic during initialization
Database Storage: prompt_templates table
Application Usage: Reads from database (with TypeScript fallback)

Usage Examples

import { getPromptService } from '@/lib/services/prompt-service';

const promptService = getPromptService();

// Get student prompt with tools
const systemPrompt = await promptService.getStudentPrompt(
  'grade_1',
  ['calculate', 'save_note']
);

// Get student config for API response
const config = await promptService.getStudentConfig('grade_1');
// Returns: { name: '小一學生', description: '...', ... }

// Get coach persona
const coach = await promptService.getCoachPrompt('empathetic');
// Returns: { name: '同理心教練', systemPrompt: '...' }

// Validate IDs
const isValid = await promptService.isValidStudentId('grade_1');
// Returns: true/false

Graceful Degradation

What happens when a prompt template is removed?

Existing conversations: Continue working
- Use stored system_prompt field in conversations table
- Warning logged: "Using stored systemPrompt for removed template"
New conversations: Blocked
- Validation prevents creation with removed template
- Returns 400 error: "Invalid student personality"
Fallback Behavior:
- If prompt not in database → Check TypeScript files
- If not in TypeScript files → Return error
- Logs warning for debugging

Code Reference: src/app/api/conversations/[conversationId]/message/route.ts:104-115

Troubleshooting

SQLite Issues

Error: "database is locked"

Cause: Another process has exclusive write lock

Solution:

# Stop all processes using the database
pkill -f "npm run dev"
lsof -ti:3000 | xargs kill -9 2>/dev/null || true

# Restart dev server
npm run dev

Prevention: WAL mode reduces likelihood (already enabled)

Error: "unable to open database file"

Cause: Missing directory or permissions

Solution:

# Check directory exists and is writable
ls -la ./data/

# Create directory if missing
mkdir -p ./data

# Check permissions
chmod 755 ./data

Note: Directory should auto-create via fs.mkdirSync(), check file permissions if this fails.

Missing data after restart

Cause: Using in-memory database (:memory:)

Solution: Check DATABASE_PATH is file path, not :memory:

# .env.local
DATABASE_PATH=./data/app.db  # ✅ File path
# DATABASE_PATH=:memory:     # ❌ In-memory (data lost on restart)

Unexpected `.db-wal` and `.db-shm` files

Cause: Normal behavior (WAL mode)

Explanation:

.db-wal - Write-Ahead Log (temporary checkpoint file)
.db-shm - Shared memory (temporary index file)
Created/deleted automatically by SQLite
Safe to ignore in version control (add to .gitignore)

Action: No action needed, these are temporary files.

Supabase Issues

Error: "Supabase configuration missing"

Cause: Missing environment variables

Solution: Verify all three required variables are set:

# Check environment variables
echo $SUPABASE_URL
echo $SUPABASE_SERVICE_ROLE_KEY
echo $SUPABASE_DB_URL

# .env.local must have all three
SUPABASE_URL=https://xxx.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJ...
SUPABASE_DB_URL=postgresql://postgres:password@db.xxx.supabase.co:5432/postgres

Common Mistake: Setting only SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY but forgetting SUPABASE_DB_URL.

Error: "Connection timeout"

Cause: Invalid connection string or network issues

Solution:

Verify Supabase project is active (check dashboard)

Check connection string format:

postgresql://postgres:[PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres

Test connection with psql client:
```
psql "$SUPABASE_DB_URL"
```

Network Issues:

Check firewall allows port 5432
Verify internet connectivity
Try from different network

Error: "permission denied for schema public"

Cause: Using anon key instead of service role key

Solution: Use service role key from Supabase dashboard

Where to find:

Go to Supabase Dashboard
Settings → API
Copy "service_role" key (NOT "anon" key!)

Why service role?

Bypasses Row Level Security (RLS)
Has admin privileges for schema creation
Required for backend operations

Prompt templates not seeding

Cause: Database already has data (seeding is idempotent)

Check:

-- In Supabase SQL Editor
SELECT COUNT(*) FROM prompt_templates;
-- Expected: 12 (9 students + 3 coaches)

Solution:

Tables created but seeding failed → Check server logs
Data already exists → Seeding skipped (intended behavior)
Want to re-seed → Run npm run db:seed (overwrites existing)

General Issues

Changes not reflecting in database

Cause: PromptService cache (5-minute TTL)

Solution:

# Option 1: Wait 5 minutes
# Option 2: Restart server
npm run dev

# Option 3: Add cache invalidation (in code)
import { getPromptService } from '@/lib/services/prompt-service';
const promptService = getPromptService();
promptService.invalidateCache();

Schema changes not applied

SQLite Solution:

# Delete database and restart (recreates with new schema)
rm -f ./data/app.db
npm run dev

Supabase Solution:

-- Run migration script manually in Supabase SQL Editor
-- Example: Add new column
ALTER TABLE conversations ADD COLUMN new_field TEXT;

Recommendation: Create migration scripts in src/scripts/migrate-*.ts for production.

Test failures after database changes

Cause: Dirty test environment (stale data, multiple processes)

Solution: Clean environment before testing

# Kill background processes
pkill -f "npm run dev" || true
pkill -f "playwright test" || true
lsof -ti:3000 | xargs kill -9 2>/dev/null || true

# Clean database
rm -f ./data/app.db  # SQLite
# Or truncate tables in Supabase

# Run tests
npm run test:api

Performance Comparison

SQLite vs Supabase

Aspect	SQLite	Supabase (PostgreSQL)
Latency	~0.1-1ms (file I/O)	~10-50ms (network)
Concurrent Writes	Serialized (one at a time)	Parallel (MVCC)
Concurrent Reads	Excellent (WAL mode)	Excellent (MVCC)
Horizontal Scaling	❌ Single server only	✅ Multi-server ready
Setup Complexity	✅ Zero (file-based)	⚠️ Requires Supabase account
External Dependencies	✅ None	⚠️ Internet required
Backups	Manual (copy file)	Automatic (Supabase managed)
Cost	✅ Free	⚠️ Paid (after free tier)
Best For	Dev, single-server	Production, multi-server

When to Choose SQLite

✅ Use SQLite when:

Single-server deployment
Low to medium traffic (<100 req/s)
Development and testing
Simple deployment (no external dependencies)
Cost-sensitive (free)

❌ Don't use SQLite when:

Multi-server deployment (file-locked)
High traffic (>100 req/s)
Need geographic distribution
Require managed backups

When to Choose Supabase

✅ Use Supabase when:

Multi-server deployment
High traffic (>100 req/s)
Production environment
Need managed infrastructure
Want automatic backups/replication
Geographic distribution

❌ Don't use Supabase when:

Single-server deployment (overkill)
Local development (extra latency)
Cost-sensitive (paid service)
Need offline-first (requires internet)

Migration Between Providers

SQLite → Supabase

Preparation:

Set up Supabase project (see CLAUDE.md "Supabase Setup Guide")
Verify schema compatibility (both use same schema SQL)

Migration Steps:

Export data from SQLite:

# Option 1: SQL dump
sqlite3 ./data/app.db .dump > dump.sql

# Option 2: CSV export per table
sqlite3 ./data/app.db <<EOF
.headers on
.mode csv
.output users.csv
SELECT * FROM users;
.output conversations.csv
SELECT * FROM conversations;
.output messages.csv
SELECT * FROM messages;
.output sessions.csv
SELECT * FROM sessions;
.output prompt_templates.csv
SELECT * FROM prompt_templates;
EOF

Import data to Supabase:

# Using psql (PostgreSQL command-line tool)
psql "$SUPABASE_DB_URL" < dump.sql

# Or use Supabase dashboard → Table Editor → Import CSV

Update environment variables:

# .env.local
DATABASE_PROVIDER=supabase
SUPABASE_URL=https://xxx.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJ...
SUPABASE_DB_URL=postgresql://...

Restart application:

npm run dev

Verify migration:

# Check data integrity
curl http://localhost:3000/api/conversations \
  -H "Authorization: Bearer YOUR_TOKEN"

Supabase → SQLite

Use Case: Downgrade from production to local development

Steps:

Export data from Supabase:

# Using pg_dump
pg_dump "$SUPABASE_DB_URL" --data-only --inserts > dump.sql

Import data to SQLite:

# Convert PostgreSQL syntax to SQLite (manual or script)
# Then import
sqlite3 ./data/app.db < dump.sql

Update environment variables:

# .env.local
DATABASE_PROVIDER=sqlite
DATABASE_PATH=./data/app.db

Restart application:

npm run dev

Code References

Database Client

Interface: src/lib/db/database-client.interface.ts
SQLite Implementation: src/lib/db/sqlite-client.ts
Supabase Implementation: src/lib/db/supabase-client.ts
Case Converters: src/lib/db/case-converters.ts
Provider Selection: src/lib/db/index.ts

Repositories

User: src/lib/repositories/user-repository.ts
Conversation: src/lib/repositories/conversation-repository.ts
Message: src/lib/repositories/message-repository.ts
Session: src/lib/repositories/session-repository.ts
PromptTemplate: src/lib/repositories/prompt-template-repository.ts

Services

PromptService: src/lib/services/prompt-service.ts

Scripts

Seed Prompts: src/scripts/seed-prompts.ts

Additional Resources

Supabase Resources

Dashboard: https://supabase.com/dashboard
Documentation: https://supabase.com/docs
Project Dashboard: https://supabase.com/dashboard (select your project after login)

SQLite Resources

Documentation: https://www.sqlite.org/docs.html
WAL Mode: https://www.sqlite.org/wal.html
better-sqlite3: https://github.com/WiseLibs/better-sqlite3

Best Practices

Development Workflow

Use SQLite for local development:

DATABASE_PROVIDER=sqlite
DATABASE_PATH=./data/app.db

Test with both providers before deploying:

# Test SQLite
DATABASE_PROVIDER=sqlite npm run test:api

# Test Supabase (separate test project)
DATABASE_PROVIDER=supabase npm run test:api

Use Supabase for production:

DATABASE_PROVIDER=supabase
# ... Supabase credentials

Schema Evolution

Define schema in src/lib/db/index.ts (SCHEMA_SQL)
Create migration scripts in src/scripts/migrate-*.ts
Test migrations locally (SQLite)
Apply migrations to production (Supabase SQL Editor)

Repository Pattern

Never import database client directly in API routes
Always use repositories for data access
Add normalization logic in repositories (not API routes)

Good:

// API route
import { ConversationRepository } from '@/lib/repositories';
const repo = new ConversationRepository();
const conversation = await repo.findById(id);

Bad:

// API route
import { getDatabase } from '@/lib/db';
const db = getDatabase();
const result = await db.from('conversations').select().eq('id', id).single();

Atomic Operations

Use atomic operations for:

Counters (message counts, etc.)
Status transitions
Conditional updates
Race-condition-sensitive operations

Example:

// ✅ Atomic (safe)
await db.atomicIncrement('conversations', 'messageCount', id, 1);

// ❌ Non-atomic (race condition)
const conv = await db.from('conversations').select().eq('id', id).single();
await db.from('conversations').update({ messageCount: conv.data.messageCount + 1 }).eq('id', id).execute();

Error Handling

Always check error before using data:

const result = await db.from('users').select().eq('id', id).single();

if (result.error) {
  console.error('Database error:', result.error);
  return NextResponse.json({ error: 'Not found' }, { status: 404 });
}

// Safe to use result.data
const user = result.data;

Summary

The database integration provides a production-ready abstraction layer that:

✅ Supports two providers (SQLite and Supabase) with zero code changes ✅ Transparent case conversion (camelCase ↔ snake_case) ✅ Repository pattern for clean separation of concerns ✅ Atomic operations for race-condition safety ✅ Graceful fallback for prompt templates ✅ Type-safe throughout with TypeScript

Key Takeaways:

Use SQLite for development, Supabase for production
Repositories abstract database operations
Query builder API is identical for both providers
Automatic prompt seeding for Supabase (manual for SQLite)
WAL mode creates temporary files for SQLite (expected)
Service role key required for Supabase (admin privileges)

Database Integration Guide: SQLite and Supabase

Overview

Architecture

Database Abstraction Layer

Repository Pattern

Provider Selection

Environment Configuration

Singleton Pattern

SQLite Provider

Configuration

Initialization Behavior

Query Builder Implementation

Atomic Operations

Prompt Template Seeding

Performance Characteristics

File Structure

Supabase Provider

Configuration

Why Service Role Key?

Initialization Behavior

Query Builder Implementation

Placeholder Conversion

Atomic Operations

Prompt Template Seeding

Performance Characteristics

Case Conversion System

The Problem

The Solution

UUID Auto-Generation

Query Builder API

Common Patterns

SELECT Queries

INSERT Queries

UPDATE Queries

DELETE Queries

Raw SQL Queries

Error Handling Pattern

Atomic Operations

Data Normalization

Boolean Field Handling

Derived Fields

Prompt Management Integration

PromptService

Prompt Seeding Flow

Usage Examples

Graceful Degradation

Troubleshooting

SQLite Issues

Error: "database is locked"

Error: "unable to open database file"

Missing data after restart

Unexpected .db-wal and .db-shm files

Supabase Issues

Error: "Supabase configuration missing"

Error: "Connection timeout"

Error: "permission denied for schema public"

Prompt templates not seeding

General Issues

Changes not reflecting in database

Schema changes not applied

Test failures after database changes

Performance Comparison

SQLite vs Supabase

When to Choose SQLite

When to Choose Supabase

Migration Between Providers

SQLite → Supabase

Supabase → SQLite

Code References

Database Client

Repositories

Services

Scripts

Additional Resources

Related Documentation

Supabase Resources

SQLite Resources

Best Practices

Development Workflow

Schema Evolution

Unexpected `.db-wal` and `.db-shm` files