Database Reset Staging Skill

Purpose: Full database reset for staging environment - drops all tables and lets migrations rebuild.

When to Use:

• Schema changes requiring clean slate
• Migration conflicts with existing tables
• Database corruption or inconsistencies
• After major refactoring

When NOT to Use:

• Just need fresh seed data (use selective delete instead, see database guide)
• Production database (NEVER - this is staging only)

Background Documentation: See /docs/guides-setup/database-setup.md (Staging Database Management section) for context and manual procedures.

🚨 CRITICAL WARNINGS

This skill performs DESTRUCTIVE operations:

• ❌ ALL data in staging database will be DELETED
• ❌ All tables will be DROPPED (not schemas - managed DB limitation)
• ❌ Cannot be undone
• ✅ ONLY affects staging database (pgbouncer-staging)
• ✅ Migrations will rebuild tables automatically

Note: DigitalOcean managed databases don't allow dropping the public schema (owned by doadmin). This skill drops all tables owned by witchcity_staging user instead.

Prerequisites:

• Staging code already deployed (use staging-deploy skill first)
• Database backup if needed (though staging data is expendable)

How to Use This Skill

Executable Script: execute.sh

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

# Skip confirmation prompt (for automation)
SKIP_CONFIRMATION=true bash .claude/skills/database-reset-staging/execute.sh

What the script does:

1. Shows pre-flight information (purpose, when/when NOT to use, destructive operation warnings)
2. Requires confirmation before proceeding (skippable with env var)
3. Validates prerequisites:
   • SSH key accessible
   • PostgreSQL client installed (psql)
   • Server connectivity
4. Retrieves database credentials from staging server (from .env.staging)
5. Stops API container
6. Drops all tables owned by witchcity_staging user (CASCADE)
7. Starts containers (migrations run automatically)
8. Waits for database initialization
9. Verifies tables rebuilt
10. Runs health check
11. Reports summary

Script includes CRITICAL 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

ssh witchcity@104.131.165.14 'cat /opt/witchcityrope/staging/.env.staging | grep STAGING_DB_CONNECTION_STRING'

Manual table drop: Connect to database and execute:

-- Generate DROP statements for all tables owned by witchcity_staging
SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;'
FROM pg_tables
WHERE schemaname = 'public' AND tableowner = 'witchcity_staging';

-- Then execute the generated statements

Then: Restart staging containers via SSH:

ssh witchcity@104.131.165.14 'cd /opt/witchcityrope/staging && docker compose -f docker-compose.staging.yml up -d --force-recreate api'

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 staging containers: Use restart-dev-containers skill
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: restart-dev-containers skill
2. Ensure latest code deployed: staging-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.Staging.json has SeedData: true
• Check environment configuration on server
• Manual trigger: Restart API container with restart-dev-containers skill

Integration with Process

Typical workflow:

1. Make schema changes locally
2. Test migrations locally
3. Deploy code: Use staging-deploy skill
4. Reset database: Use THIS skill
5. Verify: Use restart-dev-containers skill to check logs

Version History

• 2025-12-09: Fixed to drop tables instead of schemas (DigitalOcean managed DB limitation)
  • witchcity_staging user cannot drop public schema (owned by doadmin)
  • Now drops all tables owned by witchcity_staging with CASCADE
• 2025-11-05: Created as automation wrapper for staging database reset
• Extracted from: docs/functional-areas/deployment/staging-deployment-guide.md
• Complements: docs/guides-setup/database-setup.md

Remember: This skill is for staging only. Never use on production. Always use staging-deploy skill first to ensure latest code is deployed.