Wire set sync fails to clear old certificate data when service records change

[rhythmatician]

Problem Description

The wire set certificate sync is only partially updating records when assets receive new service records in Qualer. Old traceability numbers and wire roll certificate numbers persist despite the service date and other fields being updated, causing data inconsistencies.

Evidence

Query showing 8 affected records with mismatched traceability numbers:

SELECT * FROM public.wire_set_certs 
WHERE right(traceability_number, 4) != to_char(service_date::date, 'MMYY');

Example: Asset J00SET (ID 1235660)

• Database: service_date = 2025-07-30, traceability = J00SET0725
• Qualer: service_date = 2025-10-31, should be J00SET1025
• Result: Traceability suffix (0725) doesn't match service date (10/25)

Affected Assets

All 8 assets showing this pattern have newer service records in Qualer that weren't fully synced:

Asset Tag	DB Service Date	DB Traceability	Qualer Service Date	Expected Traceability
J600SET	2025-04-08	J600SET0425	2025-10-08	J600SET1025
J00SET	2025-07-30	J00SET0725	2025-10-31	J00SET1025
K900SET	2025-07-23	K900SET0725	2025-10-24	K900SET1025
K200SET	2025-04-18	K200SET0425	2025-10-01	K200SET1025
K800SET	2025-04-16	K800SET0425	2025-10-14	K800SET1025
K400SET	2025-04-18	K400SET0425	2025-10-24	K400SET1025
K700SET	2025-01-10	K700SET0125	2025-10-29	K700SET1025
J700SET	2024-12-20	J700SET1224	2025-10-30	J700SET1025

Root Cause

Two sequential bugs in utils/sync/wire_sets/__init__.py function _update_wire_set_from_record():

Bug #1: New service record detection happens after update

The code tried to check if a service record was new after already updating the asset_service_record_id:

# Lines 120-124: Updates asset_service_record_id to NEW value
if _set_if_changed(
    wire_set_cert,
    "asset_service_record_id",
    record.asset_service_record_id,
):
    changes_made = True

# Line 138: Tries to save "previous" ID, but it's already the NEW ID!
previous_service_record_id = wire_set_cert.asset_service_record_id  # BUG: Already updated!
new_service_record = _is_new_service_record(wire_set_cert, record)  # Always returns False

Result: _is_new_service_record() always returns False because it compares the new ID against itself.

Bug #2: Certificate fields cleared after being updated

Even if bug #1 were fixed, the code clears certificate fields after updating them:

# Updates service_date
if _set_if_changed(wire_set_cert, "service_date", record.service_date):
    changes_made = True  # Sets to 2025-10-31

# Then clears it!
if new_service_record:
    if _clear_certificate_fields(wire_set_cert):  # Sets service_date back to None
        changes_made = True

Result: Basic fields like service_date would get cleared if the new service record detection worked.

Impact

• Data Integrity: Traceability numbers don't match service dates
• Business Logic: Downstream processes relying on traceability numbers get stale data
• User Confusion: Certificates appear to be from old service dates
• Silent Failure: Sync reports success (records_updated: 4) but data remains incorrect

Solution

Reorder operations in _update_wire_set_from_record():

1. ✅ Save previous service record ID before any updates
2. ✅ Check if it's a new service record (comparing old vs new)
3. ✅ Clear certificate fields if new record detected
4. ✅ Then update all fields from new service record

This ensures:

• New service records are correctly detected
• Old certificate data is cleared before new data is set
• Traceability numbers and wire roll certs get re-parsed from new certificates

Verification

Running sync after fix should update all 8 affected records with:

• Correct new service dates
• Cleared traceability numbers (to be re-parsed from new certificates)
• Cleared wire roll cert numbers (to be re-parsed from new certificates)
• New certificate GUIDs and document names

Files Changed

• utils/sync/wire_sets/__init__.py - Fixed update logic in _update_wire_set_from_record()
• utils/sync/wire_sets/tests/test_new_service_record_detection.py - Added comprehensive tests

Testing

Created test suite verifying:

• ✅ New service record detection works correctly
• ✅ Certificate fields are cleared when service records change
• ✅ Basic fields are updated after clearing (not before)
• ✅ All edge cases handled (initial records, same records, newer records)

[rhythmatician]

The Root Cause of Half-Synced States

Current problematic flow:

1. process_wire_set() updates the WireSetCert object in memory
2. Calls upsert_wire_set_cert() which commits to DB immediately
3. If an error occurs after the commit (e.g., in parsing, logging, etc.), the DB has partial data
4. The error gets caught by ThreadPoolExecutor, but the DB transaction is already committed