Database Reset Production Skill

Purpose: Full database schema reset for PRODUCTION environment - drops all schemas and lets migrations rebuild.

⚠️ EXTREME CAUTION: THIS IS FOR PRODUCTION DATABASE ⚠️

When to Use:

• CRITICAL schema changes that cannot be migrated
• Database corruption beyond repair
• After explicit approval from stakeholders
• During planned maintenance window

When NOT to Use:

• You're not 100% certain this is necessary
• Production has live user data (will be permanently deleted)
• You haven't taken a complete database backup
• This is during business hours
• You haven't notified all stakeholders

Background Documentation: See

/docs/guides-setup/database-setup.md

(Production Database Management section) for context and manual procedures.

🚨 CRITICAL WARNINGS

This skill performs DESTRUCTIVE operations:

• ❌ ALL PRODUCTION DATA WILL BE PERMANENTLY DELETED
• ❌ User accounts, events, payments, vetting records - EVERYTHING
• ❌ All

  public

  ,

  cms

  , AND

  hangfire

  schemas will be DROPPED
• ❌ CANNOT BE UNDONE
• ✅ ONLY affects production database (

  witchcityrope_production

  )
• ✅ Migrations will rebuild schema automatically
• ✅ All users will need to re-register

Prerequisites:

• COMPLETE DATABASE BACKUP VERIFIED
• Production code already deployed (use

  production-deploy

  skill first)
• Stakeholder approval documented
• Maintenance window scheduled
• Rollback plan prepared

---

How to Use This Skill

Executable Script:

execute.sh

# From project root - with DOUBLE confirmation prompt
bash .claude/skills/database-reset-production/execute.sh

# Skip confirmation prompt (for automation - USE WITH EXTREME CAUTION)
SKIP_CONFIRMATION=true bash .claude/skills/database-reset-production/execute.sh

What the script does:

1. Shows pre-flight information with EXTREME warnings
2. Requires DOUBLE confirmation before proceeding:
   • First: Type "DELETE PRODUCTION DATA" exactly
   • Second: Type "yes" to final confirmation
3. Validates prerequisites:
   • SSH key accessible
   • PostgreSQL client installed (psql)
   • Server connectivity
4. Retrieves database credentials from production server
5. Stops production containers
6. Drops all database schemas (public + cms + hangfire)
7. Recreates public schema
8. Starts containers (migrations run automatically)
9. Waits for database initialization
10. Verifies schema rebuild
11. Runs health check
12. Reports summary

Script includes MULTIPLE safety warnings - this is a DESTRUCTIVE operation that CANNOT be undone.

---

Manual Override (Emergency Only)

If skill fails, manual steps:

Prerequisites: Get DB credentials from server first

Manual schema drop: Connect to database and execute:

DROP SCHEMA IF EXISTS public CASCADE;
CREATE SCHEMA public;
DROP SCHEMA IF EXISTS cms CASCADE;
DROP SCHEMA IF EXISTS hangfire CASCADE;

Then: Restart production containers manually

---

Common Issues & Solutions

Issue: psql command not found

Cause: PostgreSQL client not installed locally

Solution:

# Ubuntu/Debian
sudo apt install postgresql-client

# macOS
brew install postgresql

Issue: Connection timeout

Cause: Firewall or network issue

Solution:

1. Verify server is accessible:

   ssh witchcity@104.131.165.14

2. Check production containers status
3. Verify database port is open (25060)

Issue: Migrations fail after reset

Cause: Old migration state or code/DB mismatch

Solution:

1. Check API logs
2. Ensure latest code deployed:

   production-deploy

   skill
3. Verify no lingering tables: Run query to list all tables

Issue: Seed data not populating

Cause: Seed condition not met

Solution:

• API only seeds if

  appsettings.Production.json

  has

  SeedData: true

• Check environment configuration on server
• Manual trigger: Restart API container

---

Integration with Process

Typical workflow:

1. BACKUP PRODUCTION DATABASE (verify backup is restorable)
2. Schedule maintenance window
3. Notify all stakeholders
4. Deploy code: Use

   production-deploy

   skill
5. Place maintenance page
6. Reset database: Use THIS skill
7. Verify migrations and seed data
8. Test critical endpoints
9. Remove maintenance page
10. Notify stakeholders

---

Security Considerations

Database User:

witchcity_production

• Environment-specific maintenance user
• Has full permissions on

  witchcityrope_production

  database ONLY
• Cannot affect other databases
• Follows principle of least privilege

Connection String: Uses keyword-value format

• Format:

  Host=...;Port=...;Database=...;Username=...;Password=...;

• Retrieved from

  .env.production

  on server
• Never stored in version control

---

Version History

• 2025-11-24: Created as automation wrapper for production database reset
• Mirrors:

  database-reset-staging

  skill with enhanced safety
• Complements:

  docs/guides-setup/database-setup.md

---

Remember:

• This skill is for PRODUCTION - USE WITH EXTREME CAUTION
• Always take complete database backup first
• Always use

  production-deploy

  skill first to ensure latest code is deployed
• Always notify stakeholders before and after
• Never use during business hours without explicit approval