name: database-sqlite-ops
description: SQLite database management for interviewer-roster application. Use when managing database lifecycle tasks including migrations, seeding, and resets. Trigger phrases include "run database migration", "seed the database", "reset database", "setup interviewer database", or any database management operations.

Database SQLite Operations

Expert database management skill for the interviewer-roster SQLite database. Handles all database lifecycle tasks including schema migrations, data seeding, and complete database resets.

Overview

This skill provides comprehensive SQLite database management capabilities for the interviewer-roster application. It manages the database file located at server/data/interviewer-roster.db and provides three core operations through npm scripts:

• Migration - Create database schema and table structure
• Seeding - Populate database with initial/test data
• Reset - Complete database refresh (migrate + seed)

All operations must be executed from the server/ directory to ensure correct context and file path resolution.

When to Use This Skill

Use this skill when you need to:
- Set up the database for the first time
- Create database schema after cloning the repository
- Populate the database with seed data for development
- Reset the database to a clean state
- Troubleshoot database issues by recreating from scratch
- Prepare the database for testing
- Restore the database to a known good state

Trigger phrases:
- "run database migration"
- "seed the database"
- "reset the interviewer database"
- "setup database schema"
- "populate database with seed data"
- "run db:reset"
- "initialize the database"

Core Capabilities

1. Database Migration

Command: npm run db:migrate

What it does:
- Creates the database file if it doesn't exist
- Sets up the schema structure
- Creates all required tables
- Establishes relationships and constraints
- Applies any pending schema changes

When to use:
- Fresh installation or clone
- After pulling schema changes
- When database file is missing
- To create an empty database

Example workflow:

cd server
npm run db:migrate

Expected outcome:
- Database file created at server/data/interviewer-roster.db
- All tables created with proper schema
- Indexes and constraints applied
- Database ready for data insertion

2. Database Seeding

Command: npm run db:seed

What it does:
- Inserts initial data into tables
- Populates reference data
- Creates test/development data
- Sets up default users/configurations

When to use:
- After running migrations on empty database
- To add development/test data
- To restore sample data after testing
- When database has schema but no data

Example workflow:

cd server
npm run db:seed

Expected outcome:
- Tables populated with seed data
- Sample interviewers created
- Test events added
- Development users configured
- Database ready for application use

Important: Seeding requires the schema to exist. Always run db:migrate before db:seed on a fresh database.

3. Database Reset (Recommended)

Command: npm run db:reset

What it does:
- Performs a complete database refresh
- Runs migration to create schema
- Runs seeding to populate data
- Ensures clean, consistent state

When to use:
- Recommended for most scenarios
- Getting started with development
- After pulling major changes
- Database corruption or inconsistency
- Before running tests
- Quick reset to known good state

Example workflow:

cd server
npm run db:reset

Expected outcome:
- Fresh database created
- Schema fully initialized
- Seed data populated
- Database ready for immediate use

Why this is recommended:
- Single command for complete setup
- Guarantees consistency
- Faster than running commands separately
- Reduces human error
- Idempotent operation (safe to run multiple times)

Database Schema

Tables

The interviewer-roster database includes these core tables:

interviewers

Stores interviewer information and availability.

Key fields:
- id - Primary key
- name - Interviewer name
- email - Contact email
- skills - Technical skills/expertise
- availability - Calendar availability
- created_at - Record creation timestamp
- updated_at - Last modification timestamp

events

Tracks interview events and scheduling.

Key fields:
- id - Primary key
- interviewer_id - Foreign key to interviewers
- candidate_name - Candidate being interviewed
- scheduled_time - Interview date/time
- duration - Interview duration (minutes)
- status - Event status (scheduled, completed, cancelled)
- created_at - Record creation timestamp

audit_logs

Maintains audit trail for database operations.

Key fields:
- id - Primary key
- table_name - Affected table
- operation - CRUD operation type
- user_id - User who performed operation
- timestamp - Operation timestamp
- changes - JSON of changes made

users

Application user accounts and permissions.

Key fields:
- id - Primary key
- username - Login username
- email - User email
- role - User role (admin, interviewer, viewer)
- created_at - Account creation timestamp
- last_login - Last login timestamp

Execution Context

Working Directory Requirement

CRITICAL: All database commands must be executed from the server/ directory.

Correct:

cd server
npm run db:reset

Incorrect:

# ❌ Running from project root
npm run db:reset  # Won't work - wrong directory

Why this matters:
- Database file path is relative: data/interviewer-roster.db
- Migration scripts expect server/ as working directory
- Seed data files are located relative to server/
- Running from wrong directory will fail or create database in wrong location

Database File Location

Path: server/data/interviewer-roster.db

Directory structure:

interviewer-roster/
├── server/
│   ├── data/
│   │   └── interviewer-roster.db    # SQLite database file
│   ├── migrations/                   # Schema migration files
│   ├── seeds/                        # Seed data files
│   └── package.json                  # Contains npm scripts
└── ...

Important notes:
- The data/ directory must exist before running migrations
- SQLite will create the .db file automatically
- Database file should be excluded from version control (add to .gitignore)

Workflow Examples

First-Time Setup

When cloning the repository or setting up for the first time:

# Navigate to server directory
cd server

# Install dependencies (if not already done)
npm install

# Reset database (creates schema + seeds data)
npm run db:reset

Expected output:

> db:reset
> npm run db:migrate && npm run db:seed

> db:migrate
✓ Database schema created
✓ 4 tables created
✓ Indexes applied

> db:seed
✓ Seed data loaded
✓ 10 interviewers added
✓ 25 events created
✓ 3 users configured

Development Workflow

During active development when you need a fresh database:

cd server

# Quick reset to clean state
npm run db:reset

# Start development server
npm run dev

Testing Workflow

Before running tests to ensure clean state:

cd server

# Reset database
npm run db:reset

# Run tests
npm test

Migration-Only Workflow

When you need an empty database without seed data:

cd server

# Create schema only
npm run db:migrate

# Manually add specific data or run application
npm run dev

Troubleshooting Workflow

When database is corrupted or inconsistent:

cd server

# Option 1: Full reset (recommended)
npm run db:reset

# Option 2: Manual cleanup
rm data/interviewer-roster.db
npm run db:reset

Common Scenarios

Scenario 1: "Database not found" Error

Problem: Application can't find database file

Solution:

cd server
npm run db:reset

Why it works: Creates database file in correct location with proper schema and data.

Scenario 2: Schema Changes After Git Pull

Problem: Pulled code with schema changes, database out of sync

Solution:

cd server
npm run db:reset

Why it works: Recreates database with latest schema from migration files.

Scenario 3: Need Empty Database

Problem: Want schema but no seed data

Solution:

cd server
npm run db:migrate

Why it works: Creates schema without running seed scripts.

Scenario 4: Database Exists, Need Fresh Data

Problem: Schema is correct, just need fresh seed data

Solution:

cd server
# Delete existing data (if needed)
# Then run seeding
npm run db:seed

Caution: Seeding may fail if data already exists. For guaranteed fresh state, use db:reset.

Scenario 5: Testing Requires Clean State

Problem: Tests need predictable database state

Solution:

cd server
npm run db:reset
npm test

Why it works: Reset ensures consistent starting point for tests.

Best Practices

DO ✅

• Always run commands from server/ directory
• Use db:reset for most scenarios (it's idempotent)
• Add data/*.db to .gitignore (database files shouldn't be versioned)
• Run db:reset after pulling major changes
• Reset database before running test suites
• Document any custom seed data requirements
• Keep migration files in version control

DON'T ❌

• Don't run database commands from project root
• Don't manually edit the database file
• Don't commit database files to git
• Don't skip migrations when seeding
• Don't run seeding twice without reset (may cause duplicates)
• Don't modify migration files after they've been applied

Verification Steps

After running database operations, verify success:

Check Database File Exists

cd server
ls -lh data/interviewer-roster.db

Expected: File exists with size > 0 bytes

Verify Tables Created

cd server
sqlite3 data/interviewer-roster.db ".tables"

Expected output:

audit_logs    events        interviewers  users

Check Table Row Counts

cd server
sqlite3 data/interviewer-roster.db "
  SELECT 'interviewers' as table_name, COUNT(*) as count FROM interviewers
  UNION ALL
  SELECT 'events', COUNT(*) FROM events
  UNION ALL
  SELECT 'users', COUNT(*) FROM users;
"

Expected output:

interviewers|10
events|25
users|3

Verify Schema Structure

cd server
sqlite3 data/interviewer-roster.db ".schema interviewers"

Expected: Complete CREATE TABLE statement with all columns and constraints

Troubleshooting

Issue: "npm command not found"

Cause: Not running from server/ directory or dependencies not installed

Fix:

cd server
npm install
npm run db:reset

Issue: "ENOENT: no such file or directory"

Cause: data/ directory doesn't exist

Fix:

cd server
mkdir -p data
npm run db:reset

Issue: Database locked

Cause: Another process has database open

Fix:

cd server
# Close any applications using the database
# Then retry
npm run db:reset

Issue: Permission denied

Cause: Insufficient file system permissions

Fix:

cd server
chmod 755 data
npm run db:reset

Issue: Seed data duplicated

Cause: Running db:seed multiple times without reset

Fix:

cd server
# Use reset instead of seed alone
npm run db:reset

Security Considerations

Database File Protection

Location: server/data/interviewer-roster.db

Security measures:
- Add to .gitignore to prevent committing sensitive data
- Ensure data/ directory has appropriate permissions (755)
- Database file should be readable only by application user
- Use environment variables for production database paths
- Never commit database files with real user data

Seed Data

Development/Test only:
- Seed data is for development and testing
- Never use seed data in production
- Seed scripts may contain hardcoded passwords/tokens for testing
- Keep production data separate from seed data

Migration Safety

Best practices:
- Review migration scripts before running
- Keep migrations in version control
- Test migrations on development database first
- Backup production database before migrating
- Use transactions in migration scripts

Resources

References

See references/sqlite-operations.md for:
- Detailed SQLite command reference
- Advanced query examples
- Database optimization techniques
- Backup and restore procedures
- Schema migration best practices

Related Skills

• fullstack-dev - For application development context
• fastify-expert - For backend API integration with database

External Documentation

• SQLite Documentation
• Better-SQLite3 (Node.js driver)
• Database Migration Patterns

Quick Reference

Remember: Always cd server first! 🚀

Philosophy: Simple, repeatable database management. One command (db:reset) handles most scenarios. Always work from the correct directory. Keep it clean, keep it simple.