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.