Issue: Orphaned Sizing & Calculation Results

Problem Statement

When components are deleted from the SLD canvas, their associated sizing_results and calculation_results database records remain as orphans. This leads to data accumulation where a project can have 0 components but still retain 17+ sizing results and 33+ calculation results from previously deleted components.

User-reported evidence: Project with 0 components showed 17 sizing results and 33 calculation results in debug export.

Root Cause Analysis

Database Schema Design

Both sizing_results and calculation_results tables have a polymorphic relationship with components:

1. `sizing_results` table (src/db/schema/sizing-results-schema.ts):

• Has componentId (uuid) and componentType (enum) columns
• Only has a foreign key to projects table (with cascade delete)
• No FK constraint to any component table (bus, cable, transformer, etc.)

1. `calculation_results` table (src/db/schema/calculation-results-schema.ts):

• Same pattern: componentId and componentType columns
• FK only to projects, scenarios, and study_runs tables
• No FK constraint to component tables

Why No FK Constraint?

The componentId field references multiple different tables (buses, cables, transformers, generators, loads, utility_feeds, protection_devices) based on the componentType value. PostgreSQL doesn't support polymorphic foreign keys, so there's no automatic cascade delete when a component is removed.

Missing Cleanup Code

The deleteAllComponents function in use-project-components.ts deleted all component types but did not clean up:

• sizing_results records
• calculation_results records

Additionally, individual component deletions (single component delete) also don't clean up these tables.

Relevant Files

Schema Files

• src/db/schema/sizing-results-schema.ts - Sizing results table definition
• src/db/schema/calculation-results-schema.ts - Calculation results table definition

Server Actions

• src/actions/db/calculation-results-actions.ts - Calculation results CRUD operations
• src/actions/db/sizing-results-actions.ts - NEW FILE - Sizing results delete operations
• src/actions/sizing/run-sizing-actions.ts - Creates sizing results (for context)

Client Hooks

• src/lib/hooks/use-project-components.ts - Main hook for component CRUD, contains deleteAllComponents

Component Delete Actions (for individual deletes)

• src/actions/db/_factory/action-factory.ts - Factory pattern for component CRUD actions

What Was Implemented

1. New Bulk Delete Action for Calculation Results

File: src/actions/db/calculation-results-actions.ts

Added deleteAllProjectCalculationResultsAction(projectId):

export async function deleteAllProjectCalculationResultsAction(
  projectId: string
): Promise<ActionState<{ deletedCount: number }>>

2. New Sizing Results Actions File

File: src/actions/db/sizing-results-actions.ts (NEW)

Created with two functions:

// Delete all sizing results for a specific component
export async function deleteComponentSizingResultsAction(
  componentId: string
): Promise<ActionState<void>>

// Delete all sizing results for a project (bulk)
export async function deleteAllProjectSizingResultsAction(
  projectId: string
): Promise<ActionState<{ deletedCount: number }>>

3. Updated deleteAllComponents in Hook

File: src/lib/hooks/use-project-components.ts

Modified deleteAllComponents to call cleanup actions after deleting components:

// Clean up orphaned sizing and calculation results
await Promise.all([
  deleteAllProjectSizingResultsAction(projectId),
  deleteAllProjectCalculationResultsAction(projectId),
]);

What Was NOT Implemented (Potential Gap)

Individual component deletes do not yet clean up associated results.

When a user deletes a single component (not "delete all"), the orphaned sizing/calculation results for that specific component are not removed. This would require:

1. Modifying the delete action in action-factory.ts to also call:

• deleteComponentSizingResultsAction(componentId)
• deleteComponentCalculationResultsAction(componentId) (already exists)

1. OR modifying deleteComponentMutation in use-project-components.ts to call these after successful delete.

Verification Steps

Test 1: Bulk Delete All Components

1. Create a project with multiple components (buses, cables, loads, transformers)
2. Run power flow calculation (creates calculation_results)
3. Trigger auto-sizing on loads (creates sizing_results)
4. Open debug panel and verify sizing/calculation results exist
5. Click "Delete All Components"
6. Verify in debug panel that sizing_results and calculation_results are now empty

Test 2: Database Verification

-- Check for orphaned sizing results (componentId doesn't exist in any component table)
SELECT sr.id, sr.component_id, sr.component_type
FROM sizing_results sr
WHERE sr.component_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM buses WHERE id = sr.component_id)
  AND NOT EXISTS (SELECT 1 FROM cables WHERE id = sr.component_id)
  AND NOT EXISTS (SELECT 1 FROM transformers WHERE id = sr.component_id)
  AND NOT EXISTS (SELECT 1 FROM generators WHERE id = sr.component_id)
  AND NOT EXISTS (SELECT 1 FROM loads WHERE id = sr.component_id)
  AND NOT EXISTS (SELECT 1 FROM utility_feeds WHERE id = sr.component_id)
  AND NOT EXISTS (SELECT 1 FROM protection_devices WHERE id = sr.component_id);

-- Same for calculation_results
SELECT cr.id, cr.component_id, cr.component_type
FROM calculation_results cr
WHERE cr.component_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM buses WHERE id = cr.component_id)
  AND NOT EXISTS (SELECT 1 FROM cables WHERE id = cr.component_id)
  AND NOT EXISTS (SELECT 1 FROM transformers WHERE id = cr.component_id)
  AND NOT EXISTS (SELECT 1 FROM generators WHERE id = cr.component_id)
  AND NOT EXISTS (SELECT 1 FROM loads WHERE id = cr.component_id)
  AND NOT EXISTS (SELECT 1 FROM utility_feeds WHERE id = cr.component_id)
  AND NOT EXISTS (SELECT 1 FROM protection_devices WHERE id = cr.component_id);

Test 3: Single Component Delete (Expected to still have orphans)

1. Create a load component
2. Run sizing (creates sizing_result)
3. Delete the single load component
4. Check if sizing_result still exists for that componentId (it will - this is the gap)

Questions for Verification

1. Does deleteAllComponents now properly clean up both tables?
2. Should individual component deletes also trigger cleanup? (Currently they don't)
3. Are there any edge cases where sizing/calculation results should persist after component deletion?
4. Should we add a scheduled cleanup job for any orphans that slip through?

Build Status

• ✅ Lint passes
• ✅ Build passes
• ⚠️ Not yet tested in running application