Cascade Impact Simulator

Cascade Impact Simulator

Interactive visualization tool for previewing deletion cascade impacts before execution.

Overview

The Cascade Impact Simulator is a powerful feature that provides a theoretical, non-destructive preview of what will happen when you delete rows or tables with foreign key relationships. It helps prevent accidental data loss by showing exactly which rows will be affected by cascade operations.

Key Features:

• 🎯 Interactive Graph Visualization - ReactFlow-powered dependency graph
• 🔍 Detailed Impact Analysis - Row counts, cascade depth, severity warnings
• 📊 Multi-Format Export - CSV, JSON, Text, and PDF reports with graph
• 🛡️ Non-Destructive - Theoretical simulation only, no actual deletion
• 🔄 Circular Dependency Detection - Identifies and warns about cycles

When to Use

Use the Cascade Impact Simulator when:

✅ Deleting rows with foreign key relationships
✅ Dropping tables that other tables reference
✅ Need to understand cascade depth and scope
✅ Want to document deletion impact for compliance
✅ Unsure about CASCADE behavior
✅ Working with complex database schemas

Accessing the Simulator

From Table View (Row Deletion)

When viewing table data:

1. Find the row(s) you want to delete
2. Click the delete button/icon
3. In the delete confirmation dialog, click "Simulate Cascade Impact"
4. Simulator opens with analysis for that specific row

From Database View (Table Deletion)

When viewing database tables:

1. Select table(s) to delete
2. Click "Delete Selected"
3. In the confirmation dialog, click "Simulate Cascade Impact"
4. Simulator opens with analysis for entire table deletion

From Bulk Delete Operations

When bulk deleting:

1. Select multiple tables or rows
2. Click delete action
3. Each item shows a "Simulate" button
4. Click to analyze specific item's impact

Interface Components

Main View

┌────────────────────────────────────────────────────┐
│ Cascade Impact Simulator               [Export ▼] │
├────────────────────────────────────────────────────┤
│                                                    │
│  Impact Summary                  ┌───────────────┐│
│  • Total Affected Rows: 203      │               ││
│  • Maximum Depth: 2              │  Interactive  ││
│  • Tables Affected: 3            │     Graph     ││
│  • Warnings: 1 high severity     │  Visualization││
│                                  │               ││
│  ⚠️ Warning: High Impact         └───────────────┘│
│  Deletion will cascade to 202                      │
│  additional rows across 2 tables                   │
│                                                    │
│  [View Detailed Report]  [Close]                   │
└────────────────────────────────────────────────────┘

Impact Summary Card

Shows key metrics at a glance:

┌──────────────────────────────────┐
│ Impact Summary                   │
├──────────────────────────────────┤
│ Total Affected Rows: 203         │
│ Maximum Cascade Depth: 2         │
│ Tables Affected: 3               │
│ Cascade Paths: 2                 │
│                                  │
│ ⚠️ 1 high severity warning       │
└──────────────────────────────────┘

Metrics Explained:

Total Affected Rows:

• Includes the target row(s)/table
• Plus all cascaded deletions
• Sum across all affected tables

Maximum Cascade Depth:

• How many "levels" of cascades
• Depth 0: Target only
• Depth 1: Direct dependencies
• Depth 2+: Cascades of cascades

Tables Affected:

• Number of distinct tables impacted
• Includes target table

Cascade Paths:

• Number of distinct cascade relationships
• Each foreign key with CASCADE creates a path

Interactive Graph Visualization

Node Types:

┌─────────────┐
│   posts     │  ← Red: Source (being deleted)
│   1 row     │
└─────────────┘

┌─────────────┐
│  comments   │  ← Yellow: CASCADE deletion
│  87 rows    │
└─────────────┘

┌─────────────┐
│   likes     │  ← Blue: SET NULL (not deleted)
│  15 rows    │
└─────────────┘

┌─────────────┐
│   tags      │  ← Gray: RESTRICT (blocks deletion)
│  3 rows     │
└─────────────┘

Color Coding:

• 🔴 Red - Source table/row being deleted
• 🟡 Yellow - CASCADE: Will be deleted
• 🔵 Blue - SET NULL: Will be nullified
• ⚪ Gray - RESTRICT/NO ACTION: Blocks deletion

Edge Labels:

• Show ON DELETE action (CASCADE, SET NULL, etc.)
• Arrow direction shows dependency flow
• Dashed lines for SET NULL actions

Graph Controls:

• Zoom - Mouse wheel or +/- buttons
• Pan - Click and drag background
• Center - Click "Fit View" button
• Minimap - Overview of full graph (bottom-right corner)

Detailed Report

Expand to see complete analysis:

┌──────────────────────────────────────────────────┐
│ Affected Tables                                  │
├──────────────────────────────────────────────────┤
│ posts                                            │
│ • Action: DELETE (source)                        │
│ • Rows Before: 1                                 │
│ • Rows After: 0                                  │
│ • Depth: 0                                       │
│                                                  │
│ comments                                         │
│ • Action: CASCADE                                │
│ • Rows Before: 87                                │
│ • Rows After: 0                                  │
│ • Depth: 1                                       │
│ • ⚠️ High impact: 87 rows deleted                │
│                                                  │
│ likes                                            │
│ • Action: CASCADE                                │
│ • Rows Before: 15                                │
│ • Rows After: 0                                  │
│ • Depth: 1                                       │
└──────────────────────────────────────────────────┘

Warning System

Warnings are color-coded by severity:

🔴 High Severity:

• Large number of rows affected (>100)
• Deep cascade chains (depth >3)
• RESTRICT blocking deletion

🟡 Medium Severity:

• Moderate row count (20-100)
• Multiple cascade paths
• SET NULL affecting many rows

🟢 Low Severity:

• Small row counts (<20)
• Simple cascade (depth 1-2)
• No blocking constraints

Example Warnings:

⚠️ High Impact (Red)
Deletion will cascade to 152 rows in table 'comments'

⚠️ Circular Dependency (Yellow)
Tables 'users' and 'profiles' have circular foreign keys

⚠️ Constraint Violation (Red)
Table 'audit_logs' has RESTRICT constraint preventing deletion

How It Works

Simulation Process

Step 1: Analyze Foreign Keys

-- For each affected table
PRAGMA foreign_key_list(table_name);

Step 2: Build Dependency Graph

• Identifies all foreign key relationships
• Maps CASCADE, SET NULL, RESTRICT, NO ACTION
• Creates directed graph of dependencies

Step 3: Calculate Row Counts

-- For each dependent table
SELECT COUNT(*) FROM dependent_table
WHERE foreign_key_column = target_value;

Step 4: Traverse Graph Recursively

• Follows CASCADE relationships
• Calculates depth at each level
• Detects circular dependencies

Step 5: Generate Warnings

• Analyzes row counts
• Checks cascade depth
• Identifies constraints

Theoretical vs. Actual

Important: The simulator performs a theoretical analysis:

✅ What It Does:

• Analyzes schema relationships
• Counts affected rows
• Predicts cascade behavior
• Shows what would happen

❌ What It Doesn't Do:

• Actually delete data
• Execute SQL commands
• Modify database
• Guarantee exact results (triggers may affect actual behavior)

Algorithm Details

Graph Traversal:

1. Start at source node (target row/table)
2. Find all outbound foreign keys (tables this references)
3. Find all inbound foreign keys (tables that reference this)
4. For each CASCADE relationship:
   a. Count affected rows
   b. Add to affected list
   c. Recursively traverse from that node
5. Detect cycles (visited nodes)
6. Calculate maximum depth
7. Generate warnings based on metrics

Circular Dependency Handling:

If node already visited:
  - Mark as circular dependency
  - Don't traverse again (prevent infinite loop)
  - Warn user about cycle

Export Formats

CSV Export

Tabular format for spreadsheet analysis:

Table,Action,Rows Before,Rows After,Depth,Column,Referenced Table
posts,DELETE,1,0,0,,,
comments,CASCADE,87,0,1,post_id,posts
likes,CASCADE,15,0,1,post_id,posts

Use Cases:

• Import into Excel/Google Sheets
• Data analysis
• Reporting
• Archiving

JSON Export

Machine-readable format:

{
  "targetTable": "posts",
  "whereClause": "id = 42",
  "totalAffectedRows": 103,
  "maxDepth": 1,
  "cascadePaths": [
    {
      "sourceTable": "posts",
      "targetTable": "comments",
      "action": "CASCADE",
      "depth": 1,
      "affectedRows": 87,
      "column": "post_id"
    }
  ],
  "affectedTables": [...],
  "warnings": [...],
  "timestamp": "2024-11-03T10:30:00Z"
}

Use Cases:

• API integration
• Automated processing
• Custom analysis tools
• Version control

Text Report

Human-readable summary:

Cascade Impact Analysis
Generated: 2024-11-03 10:30:00

Target: posts (id = 42)
═══════════════════════════════════════

SUMMARY
-------
Total Affected Rows: 103
Maximum Cascade Depth: 1
Tables Affected: 3
Cascade Paths: 2

AFFECTED TABLES
---------------
1. posts
   Action: DELETE (source)
   Rows: 1 → 0
   Depth: 0

2. comments
   Action: CASCADE
   Rows: 87 → 0
   Depth: 1
   ⚠️  High impact: 87 rows deleted

3. likes
   Action: CASCADE
   Rows: 15 → 0
   Depth: 1

WARNINGS
--------
⚠️  High Impact: Deletion will cascade to 102 additional rows

Use Cases:

• Documentation
• Audit trails
• Email reports
• Review before deletion

PDF Report

Professional report with graph visualization:

Includes:

• Cover page with summary
• Embedded graph visualization (screenshot)
• Detailed table breakdown
• Warnings section
• Timestamp and metadata

Use Cases:

• Compliance documentation
• Management reports
• Archival records
• Change request documentation

Usage Workflows

Workflow 1: Safe Row Deletion

Scenario: Delete a user account

Steps:

1. Navigate to users table
2. Find user row to delete
3. Click delete button
4. Click "Simulate Cascade Impact"
5. Review graph:
   • See all related posts, comments, likes
   • Check cascade depth
   • Note total affected rows
6. Review warnings
7. Export PDF for records (optional)
8. If acceptable, proceed with deletion
9. If not, cancel and clean up dependencies first

Workflow 2: Table Deletion Analysis

Scenario: Drop an unused table

Steps:

1. Navigate to database
2. Select table to drop
3. Click "Delete"
4. Click "Simulate Cascade Impact"
5. Check for:
   • Unexpected dependencies
   • RESTRICT constraints blocking deletion
   • Circular references
6. Review affected tables
7. Export report for team review
8. Proceed or adjust foreign keys first

Workflow 3: Impact Documentation

Scenario: Document major schema change

Steps:

1. Simulate deletion impact
2. Export to PDF
3. Attach to change request
4. Get approval from stakeholders
5. Execute deletion
6. Archive simulation report

Workflow 4: Dependency Discovery

Scenario: Understand table relationships

Steps:

1. Simulate deletion of sample row
2. View graph visualization
3. Understand dependency chain
4. Export JSON for documentation
5. Cancel deletion (was just for analysis)

Real-World Examples

Example 1: Blog Post Deletion

Target: Delete blog post (id = 42)

Simulation Results:

Total Affected: 203 rows
Depth: 2

Cascade Path 1:
posts → comments (87 rows)

Cascade Path 2:
posts → likes (15 rows)

Cascade Path 3:
comments → comment_likes (101 rows)

Decision: Proceed, but export comments first for potential restoration.

Example 2: User Account Deletion

Target: Delete user (id = 123)

Simulation Results:

Total Affected: 1,245 rows
Depth: 3

⚠️  RESTRICT Constraint Found!
Table: invoices
Cannot delete: RESTRICT on user_id

Recommendation: Archive invoices or change foreign key to SET NULL

Decision: Cancel deletion, adjust schema first.

Example 3: Cleanup Operation

Target: Delete old test data (status = 'test')

Simulation Results:

Total Affected: 47 rows
Depth: 1

All CASCADE, no warnings

Decision: Safe to proceed with bulk deletion.

Best Practices

Always Simulate Before Deleting

Never delete without simulation when:

• Table has foreign keys
• Deleting multiple rows
• Working with production data
• Unfamiliar with schema

Export Reports for Important Operations

Export when:

• Deleting large amounts of data
• Making schema changes
• Need audit trail
• Compliance requirements

Review Warnings Carefully

High severity warnings require:

• Extra attention
• Stakeholder approval
• Backup before proceeding
• Consideration of alternatives

Use for Schema Documentation

Benefits:

• Visualize relationships
• Understand dependencies
• Onboard new developers
• Plan schema changes

Test in Development First

Workflow:

1. Simulate in development environment
2. Review and adjust
3. Document expected behavior
4. Simulate in production
5. Compare results
6. Proceed if consistent

Limitations

Triggers Not Included

Note: The simulation does not account for:

• BEFORE DELETE triggers
• AFTER DELETE triggers
• Complex trigger logic

Impact: Actual deletion may differ if triggers modify behavior.

Workaround: Review triggers manually and document separately.

Views Not Analyzed

Views depending on deleted data are not shown in simulation.

Workaround: Check view definitions manually:

SELECT sql FROM sqlite_master WHERE type = 'view';

Application-Level Cascades

If your application code handles cascades (not database), simulator won't detect them.

Workaround: Document application-level logic separately.

Performance on Large Tables

Simulating deletion on very large tables (millions of rows) may be slow due to COUNT queries.

Optimization: Consider sampling for very large datasets.

Troubleshooting

Graph Not Displaying

Cause: ReactFlow library not loaded or rendering error.

Solution:

1. Refresh page
2. Check browser console for errors
3. Try different browser
4. Report issue if persistent

Incorrect Row Counts

Cause: Counts cached or database changed.

Solution:

1. Re-run simulation
2. Ensure database not modified during simulation
3. Check for active transactions

Circular Dependency Warning

Cause: Tables reference each other (legitimate or design issue).

Example:

-- users references profiles
CREATE TABLE users (..., profile_id INT, FOREIGN KEY (profile_id) REFERENCES profiles(id));

-- profiles references users
CREATE TABLE profiles (..., user_id INT, FOREIGN KEY (user_id) REFERENCES users(id));

Solution:

• Review schema design
• Consider breaking circular reference
• Document intentional circles

Export Fails

Cause: Large dataset or browser memory limit.

Solution:

1. Try different export format
2. Close other browser tabs
3. Use CSV instead of PDF for large reports
4. Export in smaller chunks

API Endpoint

Simulate Cascade Impact

POST /api/tables/:dbId/simulate-cascade
Content-Type: application/json

{
  "targetTable": "posts",
  "whereClause": "id = 42"  // Optional, omit for table deletion
}

Response:

{
  "targetTable": "posts",
  "whereClause": "id = 42",
  "totalAffectedRows": 103,
  "maxDepth": 1,
  "cascadePaths": [...],
  "affectedTables": [...],
  "warnings": [...],
  "circularDependencies": []
}

See API Reference for complete documentation.

Future Enhancements

Planned features:

• What-If Analysis - Test different deletion strategies
• Undo Simulation - Preview restoration after deletion
• Historical Reports - Track deletion patterns over time
• Batch Simulation - Analyze multiple deletions at once

Next Steps

• Foreign Key Dependencies - Understanding relationships
• Table Operations - Delete operations
• Database Management - Schema management
• API Reference - Simulation API details

---

Need Help? See Troubleshooting or open an issue.