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:

`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.)

`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),
]);

Current Individual And Batch Delete Coverage

Individual component deletes now clean up rows tied to the deleted component in src/actions/db/_factory/action-factory.ts before deleting the component row:

component_connections where the component is either endpoint
calculation_results for the component ID
scenario_component_overrides for the component ID
sizing_results for the component ID

Batch component deletes also clean up project-scoped calculation and sizing results for the deleted component IDs in src/actions/db/component-batch-delete-actions.ts.

Verification Steps

Test 1: Bulk Delete All Components

Create a project with multiple components (buses, cables, loads, transformers)
Run power flow calculation (creates calculation_results)
Trigger auto-sizing on loads (creates sizing_results)
Open debug panel and verify sizing/calculation results exist
Click "Delete All Components"
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

Create a load component
Run sizing (creates sizing_result)
Delete the single load component
Verify no sizing_result, calculation_result, or scenario override rows remain for that componentId

Questions for Verification

Does deleteAllComponents now properly clean up both tables?
Do all component-type-specific delete actions still route through the shared factory cleanup path?
Are there any edge cases where sizing/calculation results should persist after component deletion?
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