Database Cleanup Reference Guide

Strategic Intelligence Hub D1 Database

This document provides the correct order for cleaning up data in the strategic-intelligence-hub D1 database while respecting foreign key constraints.

---

Database Schema Overview

Core Application Tables (DO NOT DELETE)

These tables contain the application structure and should never be cleaned:

• industries - Industry definitions and configurations
• onboarding_flows - Workflow templates
• flow_steps - Step definitions within workflows
• step_questions - Question templates for each step
• user_terms_acceptance - Legal compliance tracking

Client Data Tables (SAFE TO CLEAN)

These tables contain user-submitted data and can be cleaned:

• client_profiles - User company and contact information
• orchestrator_submissions - Complete profile submissions to processing system
• client_responses - Individual question responses
• client_objectives - Client business objectives
• client_compliance - Client compliance requirements
• report_assets - Generated reports and documents
• orchestrator_processing_log - Processing execution logs

---

Foreign Key Relationships

Dependency Chain (Child → Parent)

report_assets → orchestrator_submissions (via tracking_id)
orchestrator_processing_log → orchestrator_submissions (via tracking_id)
orchestrator_submissions → client_profiles (via client_id)
client_responses → client_profiles (via client_id)
client_objectives → client_profiles (via client_id)  
client_compliance → client_profiles (via client_id)

Visual Dependency Tree

client_profiles (ROOT)
├── orchestrator_submissions
│   ├── report_assets
│   └── orchestrator_processing_log
├── client_responses
├── client_objectives
└── client_compliance

---

Cleanup Procedures

Option 1: Remove Test Data Only (Safest)

Step 1: Preview What Will Be Deleted

-- Preview test records before deletion
SELECT 
  cp.id, 
  cp.company_name, 
  cp.email, 
  cp.created_at,
  'TEST RECORD' as reason
FROM client_profiles cp 
WHERE 
  LOWER(cp.company_name) LIKE '%test%' 
  OR LOWER(cp.company_name) LIKE '%deployment%'
  OR LOWER(cp.company_name) LIKE '%decoupling%'  
  OR LOWER(cp.email) LIKE '%test%'
  OR LOWER(cp.email) LIKE '%deployment%'
ORDER BY cp.created_at DESC;

Step 2: Execute Cleanup (Correct Order)

# 1. Delete child records first
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM report_assets WHERE tracking_id IN (SELECT tracking_id FROM orchestrator_submissions WHERE client_id IN (SELECT id FROM client_profiles WHERE LOWER(company_name) LIKE '%test%' OR LOWER(email) LIKE '%test%'));"

# 2. Delete processing logs
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM orchestrator_processing_log WHERE tracking_id IN (SELECT tracking_id FROM orchestrator_submissions WHERE client_id IN (SELECT id FROM client_profiles WHERE LOWER(company_name) LIKE '%test%' OR LOWER(email) LIKE '%test%'));"

# 3. Delete submissions
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM orchestrator_submissions WHERE client_id IN (SELECT id FROM client_profiles WHERE LOWER(company_name) LIKE '%test%' OR LOWER(email) LIKE '%test%');"

# 4. Delete other client data
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_responses WHERE client_id IN (SELECT id FROM client_profiles WHERE LOWER(company_name) LIKE '%test%' OR LOWER(email) LIKE '%test%');"

wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_objectives WHERE client_id IN (SELECT id FROM client_profiles WHERE LOWER(company_name) LIKE '%test%' OR LOWER(email) LIKE '%test%');"

wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_compliance WHERE client_id IN (SELECT id FROM client_profiles WHERE LOWER(company_name) LIKE '%test%' OR LOWER(email) LIKE '%test%');"

# 5. Finally delete parent records
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_profiles WHERE LOWER(company_name) LIKE '%test%' OR LOWER(email) LIKE '%test%';"

Option 2: Complete Client Data Reset (Nuclear)

Step 1: Create Backup

wrangler d1 export strategic-intelligence-hub --output=db-backup-$(date +%Y%m%d-%H%M%S).sql --remote

Step 2: Execute Complete Cleanup (Correct Order)

# Delete in dependency order (children first, parents last)
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM report_assets;"
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM orchestrator_processing_log;"
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM orchestrator_submissions;"
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_responses;"
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_objectives;"
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_compliance;"
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_profiles;"

Option 3: Date-Based Cleanup

Remove Records Older Than X Days

# Example: Remove records older than 30 days
wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM report_assets WHERE tracking_id IN (SELECT tracking_id FROM orchestrator_submissions WHERE DATE(submitted_at) < DATE('now', '-30 days'));"

wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM orchestrator_processing_log WHERE tracking_id IN (SELECT tracking_id FROM orchestrator_submissions WHERE DATE(submitted_at) < DATE('now', '-30 days'));"

wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM orchestrator_submissions WHERE DATE(submitted_at) < DATE('now', '-30 days');"

wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_responses WHERE client_id IN (SELECT id FROM client_profiles WHERE DATE(created_at) < DATE('now', '-30 days'));"

wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_objectives WHERE client_id IN (SELECT id FROM client_profiles WHERE DATE(created_at) < DATE('now', '-30 days'));"

wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_compliance WHERE client_id IN (SELECT id FROM client_profiles WHERE DATE(created_at) < DATE('now', '-30 days'));"

wrangler d1 execute strategic-intelligence-hub --remote --command="DELETE FROM client_profiles WHERE DATE(created_at) < DATE('now', '-30 days');"

---

Verification Commands

Check Record Counts

SELECT 
  (SELECT COUNT(*) FROM client_profiles) as client_profiles_count,
  (SELECT COUNT(*) FROM orchestrator_submissions) as submissions_count,
  (SELECT COUNT(*) FROM client_responses) as responses_count,
  (SELECT COUNT(*) FROM client_objectives) as objectives_count,
  (SELECT COUNT(*) FROM client_compliance) as compliance_count,
  (SELECT COUNT(*) FROM report_assets) as assets_count,
  (SELECT COUNT(*) FROM orchestrator_processing_log) as processing_log_count;

Check Database Size

SELECT 
  name,
  (SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=sqlite_master.name) as table_exists,
  CASE 
    WHEN name IN ('client_profiles', 'orchestrator_submissions', 'client_responses', 'client_objectives', 'client_compliance', 'report_assets', 'orchestrator_processing_log') 
    THEN 'CLIENT_DATA' 
    ELSE 'SYSTEM_DATA' 
  END as data_type
FROM sqlite_master 
WHERE type='table' 
ORDER BY data_type, name;

List Recent Records

-- Check most recent submissions (should be empty after cleanup)
SELECT id, company_name, email, created_at 
FROM client_profiles 
ORDER BY created_at DESC 
LIMIT 10;

---

Critical Rules

DO NOT DELETE THESE TABLES

• industries
• onboarding_flows
• flow_steps
• step_questions
• user_terms_acceptance
• d1_migrations
• sqlite_sequence
• _cf_METADATA
• _cf_KV

ALWAYS DELETE IN THIS ORDER

1. report_assets (references orchestrator_submissions)
2. orchestrator_processing_log (references orchestrator_submissions)
3. orchestrator_submissions (references client_profiles)
4. client_responses (references client_profiles)
5. client_objectives (references client_profiles)
6. client_compliance (references client_profiles)
7. client_profiles (root table)

NEVER

• Delete system/configuration tables
• Delete in reverse order (will cause FK constraint errors)
• Skip the backup step for complete resets
• Delete without verifying results

---

Emergency Recovery

If You Accidentally Delete System Tables

# Stop immediately and restore from backup
wrangler d1 import strategic-intelligence-hub --file=db-backup-YYYYMMDD-HHMMSS.sql --remote

If Foreign Key Errors Occur

The error usually means you're trying to delete a parent before its children. Follow the correct order above.

---

Quick Commands Reference

# List all databases
wrangler d1 list

# Create backup
wrangler d1 export strategic-intelligence-hub --output=backup.sql --remote

# Execute SQL command
wrangler d1 execute strategic-intelligence-hub --remote --command="SQL_HERE"

# Import from backup
wrangler d1 import strategic-intelligence-hub --file=backup.sql --remote

# Check table structure
wrangler d1 execute strategic-intelligence-hub --remote --command="SELECT name FROM sqlite_master WHERE type='table';"

---

Last Updated: August 31, 2025
Database: strategic-intelligence-hub
Environment: Production (--remote flag required)