Database Operations Command

Manage database operations for the ExFabrica Agentic Factory project, including migrations, seeding, backup, restore, and schema management using Drizzle ORM and PostgreSQL.

Usage

/db-operations <operation> [--env=<environment>] [--force]

Parameters

• operation (required): Database operation to perform
  • migrate - Run pending database migrations
  • migrate:create - Create a new migration file
  • migrate:rollback - Rollback the last migration
  • seed - Seed database with test/demo data
  • reset - Drop all tables and recreate schema
  • backup - Create database backup
  • restore - Restore from backup
  • status - Show migration and database status
  • validate - Validate schema against models
• --env (optional): Target environment (dev, test, production). Defaults to current environment.
• --force (optional): Force operation without confirmation (use with caution)

Operations

1. Migrations

Run Migrations

/db-operations migrate

Applies all pending migrations to the database in order.

Process:

1. Check database connection
2. Retrieve list of applied migrations
3. Identify pending migrations
4. Execute migrations in sequence
5. Record successful migrations
6. Rollback on error

Output:

🗃️  Running Database Migrations
================================

Database: exfabrica_af_dev
Environment: development

Pending migrations:
  ✓ 001_create_users_table.sql
  ✓ 002_create_projects_table.sql
  ✓ 003_add_user_roles.sql

All migrations completed successfully! (3.2s)

Create New Migration

/db-operations migrate:create add_organizations_table

Creates a new migration file with timestamp.

Generated File: apps/backend/drizzle/migrations/20251029123456_add_organizations_table.sql

Rollback Migration

/db-operations migrate:rollback

Rolls back the most recently applied migration.

⚠️ WARNING: Use with extreme caution in production!

2. Database Seeding

Seed Development Data

/db-operations seed

Populates the database with test/demo data for development.

Seed Data Includes:

• Test user accounts (admin, regular users)
• Sample projects and workflows
• Demo organizations
• Test API keys
• Sample data for each entity type

Process:

1. Check if database is empty or can be seeded
2. Clear existing seed data (if applicable)
3. Insert seed data in dependency order
4. Verify data integrity
5. Report seeded record counts

Output:

🌱 Seeding Database
===================

Environment: development

Seeding data:
  ✓ Users: 10 records created
  ✓ Organizations: 3 records created
  ✓ Projects: 15 records created
  ✓ Workflows: 23 records created
  ✓ API Keys: 5 records created

Database seeded successfully! (2.7s)

Test Accounts:
- admin@exfabrica.com / Admin123!
- user@exfabrica.com / User123!

3. Database Reset

/db-operations reset

⚠️ DESTRUCTIVE OPERATION: Drops all tables and recreates the schema.

Confirmation Required (unless --force is used):

⚠️  WARNING: This will delete ALL data in the database!

Database: exfabrica_af_dev
Environment: development

Are you sure you want to continue? (yes/no):

Process:

1. Request explicit confirmation
2. Create backup before reset (unless skipped)
3. Drop all tables in reverse dependency order
4. Recreate schema from migrations
5. Optionally seed with fresh data

Use Cases:

• Reset development database to clean state
• Clear test database between test runs
• Fix corrupted schema during development

4. Database Backup

/db-operations backup

Creates a full backup of the current database.

Backup Location: backups/db/exfabrica_af_dev_20251029_123456.sql

Backup Includes:

• Complete schema (tables, indexes, constraints)
• All data
• Sequences and serial values
• Custom types and functions

Process:

1. Generate backup filename with timestamp
2. Use pg_dump to create SQL backup
3. Compress backup file (gzip)
4. Verify backup integrity
5. Report backup location and size

Output:

💾 Creating Database Backup
===========================

Database: exfabrica_af_dev
Backup file: backups/db/exfabrica_af_dev_20251029_123456.sql.gz

✓ Backup created successfully
✓ Size: 12.4 MB (compressed)
✓ Records: ~45,000

Backup location:
  c:\Users\nicol\Source\ExFabrica\ExFabrica\EAF\backups\db\exfabrica_af_dev_20251029_123456.sql.gz

5. Database Restore

/db-operations restore backups/db/exfabrica_af_dev_20251029_123456.sql.gz

Restores database from a backup file.

⚠️ WARNING: This will replace all current data!

Process:

1. Verify backup file exists and is valid
2. Request confirmation
3. Create safety backup of current state
4. Drop existing schema (optional)
5. Restore from backup file
6. Verify restoration success

6. Database Status

/db-operations status

Shows comprehensive database and migration status.

Output:

📊 Database Status
==================

Connection: ✓ Connected
Database: exfabrica_af_dev
Host: localhost:5432
Version: PostgreSQL 15.3

Migrations:
  Applied: 23
  Pending: 0
  Last migration: 20251028_add_workflow_permissions.sql

Schema:
  Tables: 12
  Indexes: 34
  Constraints: 28

Data Summary:
  Users: 145 records
  Projects: 67 records
  Workflows: 234 records
  Organizations: 8 records

Size:
  Database: 124 MB
  Largest table: workflows (45 MB)

Health: ✓ All checks passed

7. Schema Validation

/db-operations validate

Validates that the database schema matches Drizzle ORM model definitions.

Checks:

• All models have corresponding tables
• Column types match model definitions
• Indexes are correctly created
• Foreign key constraints are in place
• Default values are set correctly

Output:

✓ All models validated successfully
⚠ Warning: Index 'idx_users_email' missing on users table
⚠ Warning: Column 'updated_at' type mismatch (expected: timestamptz, found: timestamp)

Environment Configuration

Development Environment

# .env.development
DATABASE_URL=postgresql://dev:devpass@localhost:5432/exfabrica_af_dev

Test Environment

# .env.test
DATABASE_URL=postgresql://test:testpass@localhost:5442/exfabrica_af_test

Production Environment

# .env.production
DATABASE_URL=postgresql://prod:prodpass@db.example.com:5432/exfabrica_af_prod

Examples

Development Workflow

# Start fresh with clean database
/db-operations reset --force
/db-operations seed

# Make schema changes in code
# Generate migration
/db-operations migrate:create add_new_feature

# Apply migration
/db-operations migrate

# Verify
/db-operations status

Production Deployment

# Create backup before migration
/db-operations backup --env=production

# Apply migrations
/db-operations migrate --env=production

# Verify
/db-operations status --env=production

Testing Setup

# Reset test database
/db-operations reset --env=test --force

# Seed with test data
/db-operations seed --env=test

# Run tests
/test-all backend

Safety Features

Production Safeguards

• ⚠️ Explicit confirmation required for destructive operations
• 🔒 Automatic backup before reset or rollback in production
• ✅ Validation before applying migrations
• 📝 Detailed logging of all operations
• 🔄 Rollback capability for failed migrations

Transaction Safety

• All migrations run within transactions
• Automatic rollback on error
• State consistency guaranteed

Backup Retention

• Automatic backups retained for 30 days
• Manual backups retained indefinitely
• Cleanup command for old backups

Migration Best Practices

Creating Migrations

1. Use descriptive names

   /db-operations migrate:create add_workflow_approvals
   

2. Make migrations reversible Include both up and down migrations:

   -- Up
   CREATE TABLE approvals (...);
   
   -- Down
   DROP TABLE approvals;
   

3. Test migrations locally first

   /db-operations migrate --env=dev
   /db-operations migrate:rollback --env=dev
   

4. Keep migrations small and focused

   • One logical change per migration
   • Easier to review and rollback

Migration Dependencies

Drizzle ORM ensures migrations run in order:

001_create_users_table.sql
002_create_organizations_table.sql
003_add_user_organization_relation.sql (depends on 001 and 002)

Troubleshooting

Cannot Connect to Database

Error: Connection refused at localhost:5432

Solution: Ensure PostgreSQL is running

docker compose up -d postgres

Migration Failed

Error: Migration 003_add_column.sql failed

Solution: Check migration SQL syntax and rollback

/db-operations migrate:rollback
# Fix migration file
/db-operations migrate

Schema Mismatch

Error: Table 'users' does not match model definition

Solution: Run validation and create corrective migration

/db-operations validate
/db-operations migrate:create fix_users_schema

Seed Data Conflicts

Error: Duplicate key violation on users.email

Solution: Clear existing seed data first

/db-operations reset --force
/db-operations seed

Backup File Corrupted

Error: Invalid backup file format

Solution: Use a different backup or restore from automated backups

ls backups/db/automated/
/db-operations restore backups/db/automated/latest.sql.gz

CI/CD Integration

Azure DevOps Pipeline

- task: Script@1
  displayName: 'Database Migrations'
  inputs:
    script: |
      /db-operations backup --env=$(ENVIRONMENT)
      /db-operations migrate --env=$(ENVIRONMENT)
      /db-operations validate --env=$(ENVIRONMENT)

Pre-Deployment Checklist

✅ Backup created ✅ Migrations tested in staging ✅ Schema validated ✅ Rollback plan prepared

Drizzle ORM Schema Example

// apps/backend/src/database/schema/users.schema.ts
import { pgTable, serial, varchar, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  password: varchar('password', { length: 255 }).notNull(),
  firstName: varchar('first_name', { length: 100 }),
  lastName: varchar('last_name', { length: 100 }),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

Related Commands

• /test-all backend - Test database operations
• /deploy - Deploy with database migrations
• /analyze-code - Check migration file quality

Advanced Operations

Custom Seed Data

Create custom seed files in apps/backend/drizzle/seeds/:

// custom-seed.ts
export async function seed(db: Database) {
  await db.insert(users).values([
    { email: 'custom@example.com', password: 'hashed' }
  ]);
}

Partial Migrations

Run specific migration range:

/db-operations migrate --from=001 --to=005

Multi-Database Operations

Operate on multiple databases:

/db-operations migrate --all-environments

---

Note: Always test database operations in development before applying to staging or production. Keep backups and maintain a rollback plan for production changes.