wulf-pulse/docs/TICKET_SYNC_FIX.md

Ticket Sync Foreign Key Constraint Fix

Problem Identified

The ticket sync was failing with this error:

Database error: insert or update on table "tickets" violates foreign key constraint "tickets_assigned_resource_id_fkey"

Root Cause

• Tickets in Autotask reference assigned_resource_id values that don't exist in the local resources table
• This happens because:
  1. Some resources may be deleted/inactive in Autotask but still referenced by old tickets
  2. Resource sync may be incomplete
  3. Autotask may have data inconsistencies
  4. The foreign key constraint was too strict (not deferrable, not nullable)

Why Chunking Wasn't the Solution

The initial implementation added monthly chunking thinking it was a timeout issue. However, the actual problem was a data integrity constraint violation, not a timeout. The sync ran for ~575 seconds (9.5 minutes) before failing, which indicates it was processing data but hit a constraint violation.

Solution Implemented

1. Database Migration (008_relax_tickets_resource_constraints.sql)

Changes:

• Dropped strict foreign key constraints on:

  • tickets.assigned_resource_id
  • tickets.first_response_assigned_resource_id
  • tickets.first_response_initiating_resource_id
  • tasks.assigned_resource_id

• Made all resource ID columns nullable

• Re-added constraints as deferrable with ON DELETE SET NULL:

  • Allows tickets to exist even if referenced resource doesn't exist
  • Sets resource IDs to NULL if the resource is deleted
  • DEFERRABLE INITIALLY DEFERRED allows constraint checking at transaction end

To Run Migration:

# From host
docker exec pulse-app psql $DATABASE_URL -f /app/migrations/008_relax_tickets_resource_constraints.sql

# Or use the script
docker exec pulse-app bash /app/scripts/run-migration-008.sh

2. Application-Level Validation (entity-sync.ts)

Added Resource Validation:

• New method getValidResourceIds() fetches all valid resource IDs from database
• Before inserting tickets, validates all resource references
• Invalid resource IDs are set to null instead of causing insert failure
• Validation is cached for chunked sync to avoid repeated database queries

Benefits:

• Prevents constraint violations before they happen
• Logs which tickets have invalid resource references
• Allows tickets to sync even with missing resource data
• Maintains data integrity by nullifying invalid references

3. Enhanced Logging

Added detailed logging for:

• Number of tickets with invalid resource references
• Specific ticket IDs with invalid assignments
• Cache size for valid resource IDs
• Validation warnings per chunk (for chunked sync)

Files Modified

1. /opt/stacks/pulse/migrations/008_relax_tickets_resource_constraints.sql (NEW)

   • Database schema changes

2. /opt/stacks/pulse/lib/services/entity-sync.ts

   • Added cachedValidResourceIds property
   • Added getValidResourceIds() method
   • Added resource validation for regular ticket sync
   • Added resource validation for chunked ticket sync

3. /opt/stacks/pulse/scripts/run-migration-008.sh (NEW)

   • Helper script to run migration

4. /opt/stacks/pulse/docs/TICKET_SYNC_FIX.md (THIS FILE)

   • Documentation

Testing Steps

1. Run the Migration

docker exec pulse-app psql $DATABASE_URL -f /app/migrations/008_relax_tickets_resource_constraints.sql

2. Verify Constraints

-- Check that constraints are now deferrable
SELECT 
    conname, 
    contype, 
    condeferrable, 
    condeferred
FROM pg_constraint 
WHERE conrelid = 'tickets'::regclass 
AND conname LIKE '%resource%';

Expected output should show condeferrable = true for resource constraints.

3. Test Ticket Sync

# Sync tickets for last 1 year
curl -X POST http://localhost:3000/api/sync/entity \
  -H "Content-Type: application/json" \
  -d '{
    "entities": ["tickets"],
    "yearsBack": 1,
    "triggeredBy": "test"
  }'

4. Monitor Logs

docker logs -f pulse-app

Look for:

• [tickets] Cached X valid resource IDs
• [tickets] Ticket XXXXX: Invalid assigned_resource_id YYYYY, setting to null
• [tickets] Nullified X invalid resource references
• Successful completion without constraint violations

5. Verify Data

-- Check tickets with null assigned_resource_id
SELECT COUNT(*) 
FROM tickets 
WHERE assigned_resource_id IS NULL;

-- Check for any orphaned resource references (should be 0 after validation)
SELECT COUNT(*) 
FROM tickets t
WHERE t.assigned_resource_id IS NOT NULL
AND NOT EXISTS (
    SELECT 1 FROM resources r 
    WHERE r.id = t.assigned_resource_id
);

Expected Behavior After Fix

Before Fix

• ❌ Ticket sync fails completely with foreign key constraint error
• ❌ No tickets are synced
• ❌ Error occurs after processing data for ~9 minutes

After Fix

• ✅ Ticket sync completes successfully
• ✅ Tickets with invalid resource IDs have those fields set to NULL
• ✅ Warnings logged for tickets with invalid references
• ✅ All valid tickets are synced to database
• ✅ Partial data is preserved (ticket exists, just without resource assignment)

Performance Considerations

Resource ID Validation

• Fetches all resource IDs once at start of sync
• Cached in memory for duration of sync
• O(1) lookup time using Set data structure
• Minimal performance impact

For Large Datasets

If you have 10,000+ resources:

• Memory usage: ~80KB (10,000 IDs × 8 bytes)
• Lookup time: O(1) constant time
• No significant performance impact

Chunked Sync Still Useful

While chunking wasn't the solution to the foreign key issue, it's still beneficial for:

• Large date ranges: Breaking 5+ years into monthly chunks
• Progress visibility: Seeing which months are being processed
• Partial recovery: If one month fails, others still succeed
• Memory management: Processing smaller batches at a time

The chunked sync now includes the same resource validation logic.

Future Improvements

1. Periodic Resource Sync: Schedule regular resource syncs before ticket syncs
2. Orphan Detection: Regular job to identify and report tickets with null resource IDs
3. Resource Reconciliation: Tool to match tickets with missing resources to valid alternatives
4. Constraint Monitoring: Alert when high percentage of tickets have null resource IDs

Rollback Plan

If issues occur, rollback by:

-- Remove deferrable constraints
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_assigned_resource_id_fkey;

-- Re-add strict constraint (will fail if orphaned references exist)
ALTER TABLE tickets 
  ADD CONSTRAINT tickets_assigned_resource_id_fkey 
  FOREIGN KEY (assigned_resource_id) REFERENCES resources(id) 
  ON DELETE SET NULL;

Note: Rollback may fail if orphaned references exist. Clean them first:

UPDATE tickets 
SET assigned_resource_id = NULL 
WHERE assigned_resource_id IS NOT NULL 
AND NOT EXISTS (SELECT 1 FROM resources WHERE id = tickets.assigned_resource_id);

Summary

The ticket sync failure was caused by strict foreign key constraints on resource references, not by timeouts. The fix involves:

1. Database level: Making constraints deferrable and lenient
2. Application level: Validating and nullifying invalid resource references before insert
3. Logging: Detailed warnings about data quality issues

This allows tickets to sync successfully even when resource data is incomplete or inconsistent, while maintaining visibility into data quality issues.