SQL Database Helper

Databases

File	Purpose	When to use
db.sqlite3	Development database	Writing/testing new features, running the dev server
db-prod.sqlite3	Copy of production database	Exploring real data, investigating bugs, verifying assumptions about data shape
db-test.sqlite3	Unit test database (blank + migrations, created automatically by test runs)	Unit tests only

db-prod.sqlite3 can be modified if the task requires it. Never modify db-copy.sqlite3 — it is the untouched backup.

Schema

All table definitions live in app/db/tables.ts. Read this file first to understand available tables, columns, and their types. Key conventions:

• Every table has a numeric id primary key (type number)
• Booleans are stored as 0/1 (type DBBoolean)

Exploring data

When the user needs to explore or query existing data, use sqlite3 CLI commands against the appropriate database.

Important: Always quote table and column names with double quotes ("TableName", "columnName") because the schema uses camelCase naming and some names are reserved SQL keywords.

# Explore production data
sqlite3 db-prod.sqlite3 'SELECT * FROM "User" LIMIT 5;'

# Check schema of a table
sqlite3 db-prod.sqlite3 '.schema "User"'

# Count rows
sqlite3 db-prod.sqlite3 'SELECT COUNT(*) FROM "User";'

# Development database
sqlite3 db.sqlite3 'SELECT * FROM "User" LIMIT 5;'