Orphaned lists in campaign_lists when list is deleted while campaign is draft

[ermoi]

Bug Description

When a list is deleted while a campaign in draft status is still associated with it, the campaign_lists table retains the association with a null or invalid list_id, causing errors when trying to update or manage the campaign's lists.

Steps to Reproduce

1. Create a list (e.g., "Test List", ID: 10)
2. Create a campaign in draft status
3. Associate the campaign with "Test List"
4. Delete "Test List" from Lists management
5. Try to edit the draft campaign and update its associated lists

Expected Behavior

When a list is deleted:

• If campaign is draft: Remove the association from campaign_lists
• OR: Prevent list deletion if associated with draft campaigns
• OR: Show warning about associated campaigns

Actual Behavior

• The row in campaign_lists remains with orphaned reference
• list_id becomes null or points to non-existent list
• list_name remains but has no valid reference
• Attempting to update campaign lists fails with error
• Cannot remove the orphaned list from campaign

Database Evidence

listmonk=# select * from campaign_lists where campaign_id=29;
 id  | campaign_id | list_id |                            list_name                             
-----+-------------+---------+------------------------------------------------------------------
 390 |          29 |         | Empresas PDTO _FITUR Zona Empresas
 380 |          29 |         | Admon. Públicas - Patronatos
 250 |          29 |         | Mesas Fijas FITUR 3 - Admon Publica-Patronatos Aytos
 437 |          29 |      12 | Mesas Fijas 2025_asistentes
 466 |          29 |      22 | Mesas Fijas Producto convocatoria 2026
 463 |          29 |      20 | Entidades_PTT_Aytos _Salas y empresas FITUR
 461 |          29 |      14 | Admon. Públicas - Delegaciones Territoriales (Correos Genéricos)

Shows campaigns with invalid list references.

Error Messages

When trying to delete all campaign lists:

Problems with id

When trying to update add campaign lists:

Keeps the deleted list

Environment

• Listmonk version: v5.1.0 (30846f8 2025-09-09)
• Database: PostgreSQL
• Deployment: Docker

Proposed Solutions

Option 1: Cascade delete (Recommended)

Add foreign key constraint with CASCADE:

ALTER TABLE campaign_lists 
ADD CONSTRAINT fk_campaign_lists_list_id 
FOREIGN KEY (list_id) 
REFERENCES lists(id) 
ON DELETE CASCADE;

Option 2: Prevent deletion

Check for draft campaign associations before allowing list deletion:

-- Check before delete
SELECT COUNT(*) 
FROM campaign_lists cl
INNER JOIN campaigns c ON cl.campaign_id = c.id
WHERE cl.list_id = ? AND c.status = 'draft';

Show error: "Cannot delete list. It's associated with X draft campaign(s)."

Option 3: Cleanup on campaign edit

When editing campaign, automatically remove orphaned list associations.

Workaround

Manually clean orphaned associations:

DELETE FROM campaign_lists 
WHERE list_id NOT IN (SELECT id FROM lists);

Impact

• Cannot update draft campaigns with deleted lists
• Database integrity compromised
• User confusion (seeing deleted list names)
• Potential data inconsistency

Additional Notes

This affects only draft campaigns. Once a campaign is sent/finished, the list association should be preserved for reporting purposes even if list is deleted.