Database Guide

A comprehensive guide for managing databases in the EPIC application using Prisma ORM and SQLite.

Quick Reference
Prisma Commands
Common Workflows
Troubleshooting
Database Schema Overview
SQLite Tips
Python Databases
- Query History Database

Quick Reference

Task	Command
Sync schema to database	`npx prisma db push`
Generate Prisma Client	`npx prisma generate`
Open database GUI	`npx prisma studio`
Create migration	`npx prisma migrate dev --name <name>`
Reset database	`npx prisma migrate reset`
View schema	`npx prisma format`

Prisma Commands

Schema Sync

Sync your schema.prisma changes directly to the database (development only):

cd backend
npx prisma db push

When to use: Quick development iterations when you don't need migration history.

What it does:

Reads prisma/schema.prisma
Compares with current database state
Applies changes directly to SQLite
Does NOT create migration files

Migrations

Create a proper migration with version history:

cd backend
npx prisma migrate dev --name add_plot_thread

When to use: Production-ready changes that need version control.

What it does:

Creates a SQL migration file in prisma/migrations/
Applies the migration to the database
Regenerates Prisma Client

Client Generation

Regenerate the Prisma Client after schema changes:

cd backend
npx prisma generate

When to use: After any schema modification.

What it does:

Reads schema.prisma
Generates TypeScript types in node_modules/.prisma/client
Updates autocomplete and type checking

Database Inspection

Open Prisma Studio to visually browse and edit data:

cd backend
npx prisma studio

Opens a web interface at http://localhost:5555 where you can:

View all tables and records
Add, edit, delete records
Filter and search data
See relationships between tables

Common Workflows

Adding a New Model

Edit the schema (backend/prisma/schema.prisma):

model PlotThread {
  id          String   @id @default(cuid())
  novelId     String
  name        String
  description String?
  status      String   @default("active")
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  novel       Novel    @relation(fields: [novelId], references: [id], onDelete: Cascade)
}

Sync to database:

npx prisma db push

Regenerate client:

npx prisma generate

Restart TypeScript Server (VS Code):
- Press Ctrl+Shift+P
- Type "TypeScript: Restart TS Server"
- Select and wait for restart
Restart backend server:

# Ctrl+C to stop, then:
npm run dev

Modifying an Existing Model

Edit the schema - add/remove/modify fields
For development (quick):

npx prisma db push
npx prisma generate

For production (with migration):

npx prisma migrate dev --name describe_your_change

Restart TS Server and backend

Resetting the Database

Warning: This deletes ALL data!

cd backend
npx prisma migrate reset

What it does:

Drops the database
Recreates from migrations
Runs seed script (if configured)

For a fresh start without migrations:

# Delete the database file
rm backend/epic.db

# Recreate from schema
npx prisma db push

Troubleshooting

TypeScript Types Not Updating

Symptoms:

Red squiggly lines on Prisma model properties
Autocomplete not showing new fields
"Property does not exist" errors

Solution:

Regenerate Prisma Client:

npx prisma generate

Restart TypeScript Server:
- Ctrl+Shift+P → "TypeScript: Restart TS Server"
If still not working, close and reopen VS Code
Nuclear option - clear node_modules cache:

rm -rf node_modules/.prisma
npx prisma generate

Database Lock Issues

Symptoms:

"Database is locked" error
Prisma commands hang

Solution:

Close Prisma Studio if running
Stop the backend server
Close any SQLite viewers (DB Browser, etc.)
Try the command again

On Windows, you may need to:

# Find and kill processes using the database
tasklist | findstr node
taskkill /PID <pid> /F

Migration Conflicts

Symptoms:

"Migration failed" errors
Schema drift warnings

Solution:

Check current state:

npx prisma migrate status

Reset if development:

npx prisma migrate reset

Mark as resolved (if you manually fixed):

npx prisma migrate resolve --applied <migration_name>

Database Schema Overview

SQLite Tips

Direct Database Access

You can query the SQLite database directly:

# Using sqlite3 CLI
sqlite3 backend/epic.db

# List tables
.tables

# Show table schema
.schema Novel

# Run a query
SELECT * FROM Novel LIMIT 5;

# Exit
.quit

Database Location

Environment	Location
Development	`backend/epic.db`
Vector Store	`aiservice/chroma_db/`

Backup Database

# Simple copy
cp backend/epic.db backend/epic_backup.db

# With timestamp
cp backend/epic.db "backend/epic_$(date +%Y%m%d_%H%M%S).db"

View Database Size

# File size
ls -lh backend/epic.db

# Table sizes (in SQLite)
sqlite3 backend/epic.db "SELECT name, SUM(pgsize) as size FROM dbstat GROUP BY name ORDER BY size DESC;"

Prisma Schema Best Practices

Use Cascading Deletes

model Book {
  novelId String
  novel   Novel @relation(fields: [novelId], references: [id], onDelete: Cascade)
}

Add Indexes for Performance

model CodexEntry {
  novelId      String
  categoryType String

  @@index([novelId])
  @@index([novelId, categoryType])
}

Use Descriptive Field Names

// Good
model PlotThread {
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// Avoid
model PlotThread {
  created DateTime
  updated DateTime
}

Document Complex Relations

/// Represents a plot thread that spans multiple chapters
model PlotThread {
  /// Unique identifier
  id          String   @id @default(cuid())
  /// The novel this thread belongs to
  novelId     String
  /// Display name of the thread
  name        String
  /// Current state: "active", "resolved", "abandoned"
  status      String   @default("active")
}

Python Databases

The Python AI service (aiservice/) uses separate SQLite databases for analytics and testing.

Query History Database

Tracks queries made through the test interface for analytics and debugging.

Location: ~/.epic_engine/query_history.db

On Windows: C:\Users\<username>\.epic_engine\query_history.db

Schema:

Column	Type	Description
id	INTEGER	Primary key
timestamp	TEXT	ISO timestamp
query	TEXT	The question asked
novel_id	TEXT	Novel being queried
retriever_type	TEXT	hybrid/advanced/rvrg
model	TEXT	LLM model used
answer_length	INTEGER	Response length in chars
total_time_ms	REAL	Total query time
prompt_tokens	INTEGER	Input tokens used
completion_tokens	INTEGER	Output tokens used
total_tokens	INTEGER	Total tokens used
estimated_cost	REAL	Estimated API cost
rating	TEXT	User rating (up/down)

Viewing the Database:

# Using sqlite3 CLI
sqlite3 ~/.epic_engine/query_history.db

# List tables
.tables

# View all queries
SELECT * FROM queries;

# View recent queries with cost
SELECT timestamp, query, model, total_tokens, estimated_cost
FROM queries
ORDER BY timestamp DESC
LIMIT 10;

# Exit
.quit

Alternative Tools:

DB Browser for SQLite - GUI tool from https://sqlitebrowser.org/
VS Code SQLite Extension - View .db files directly in VS Code

Python one-liner:

python -c "import sqlite3; c=sqlite3.connect('query_history.db'); print([r for r in c.execute('SELECT * FROM queries')])"

Resetting the Database:

# Simply delete the file
rm ~/.epic_engine/query_history.db

# On Windows
del %USERPROFILE%\.epic_engine\query_history.db

The database will be recreated automatically on next query.

Database Guide

Database Guide

Table of Contents

Quick Reference

Prisma Commands

Schema Sync

Migrations

Client Generation

Database Inspection

Common Workflows

Adding a New Model

Modifying an Existing Model

Resetting the Database

Troubleshooting

TypeScript Types Not Updating

Database Lock Issues

Migration Conflicts

Database Schema Overview

SQLite Tips

Direct Database Access

Database Location

Backup Database

View Database Size

Prisma Schema Best Practices

Use Cascading Deletes

Add Indexes for Performance

Use Descriptive Field Names

Document Complex Relations

Python Databases

Query History Database