Design a duplicate-safe callback migration

Build one normalized identity set

Fast answer: callbacks are usually tied to lead records, while imported files are often keyed by phone number. Normalize country code and punctuation once, then resolve each phone to the intended lead before planning a write.

A phone number that looks identical to a human can still fail to match: a leading country code, a stray extension digit, or punctuation left over from a spreadsheet export all change the literal string VICIdial's own search compares against. Decide and document one normalized shape for every input before the first lookup, and apply it identically to both the migration input and whatever VICIdial's own phone_number field actually stores, rather than normalizing only one side.

Ambiguous numbers belong in a review queue. Never let a last-row-wins rule silently choose between duplicate leads.

See VICIdial terminology for complete beginners: users, phones, campaigns and leads for the rest of this glossary.

SELECT phone_number, COUNT(*) AS lead_rowsFROM vicidial_listWHERE phone_number IN ('<PHONE_A>','<PHONE_B>')GROUP BY phone_numberHAVING COUNT(*) > 1;

• Country code, spacing and punctuation are normalized identically on both the input file and the field VICIdial's own search compares against.
• Every ambiguous phone number is quarantined in a review queue, not resolved by a default rule.
• The normalized shape you chose is written down once, so a second engineer applying the same migration months later reproduces it exactly.

Prepare a tiny input before a large migration

Start with three synthetic cases: one unique callback needing a move, one already owned by the target agent and one deliberately ambiguous phone. This small set proves the reject and no-op branches as well as the happy path.

A fourth case is worth adding once the first three pass: the same phone number, but represented differently between your input file and VICIdial's own stored value — an extra leading digit, or punctuation the export tool left in. That case exercises your normalization step specifically, separately from the identity-resolution logic the other three already cover, and it is the case most migrations skip until it causes a real mismatch.

Use opaque fixture labels in the public worksheet. The example values explain the columns; they are not real agent IDs, lead IDs or phone numbers.

input_ref,normalized_phone,lead_match,current_owner,proposed_owner,planfixture-01,PHONE_A,unique,AGENT_OLD,AGENT_NEW,updatefixture-02,PHONE_B,unique,AGENT_NEW,AGENT_NEW,no-opfixture-03,PHONE_C,ambiguous,unknown,AGENT_NEW,reject

Run VICIdial's own dry run before building anything custom

update_lead no_update=Y checks whether the lead search finds a match. It does not verify the owner, schedule or identity of a callback, and it does not provide a conditional per-callback update contract.

The documented callback_type and callback_user fields describe callback effects associated with a selected lead. Before using them, inspect the installed implementation’s update-versus-add behavior and whether it can target the intended scheduled record without affecting another. A lead ID alone is insufficient for the CB501/CB502 case above.

If the supported interface cannot select the exact callback identity and preserve the other schedule, mark this input UNSUPPORTED and hand the owner the two record identities, expected before/after state and missing API capability. Do not fall back to direct SQL writes, delete/recreate callbacks or assume that a second request is idempotent.

function=update_lead  &phone_number=<PHONE_NUMBER>  &search_method=PHONE_NUMBER  &no_update=Y  &callback_type=USERONLY  &callback_user=<AGENT_USER>

You should seeThe existence check is one planning input. A callback move remains blocked until the supported write’s exact target and effects are independently established.

Read every intended callback and the untouched sibling

Use the independent read-only client before planning a write and again after any separately approved supported operation. Read the intended callback identity and the same-lead sibling, not only campaign totals.

For the fictional CB501 move, the accepted result changes only CB501’s assigned user while retaining its schedule/state and leaving CB502 byte-for-byte equivalent in the reviewed fields. A campaign status/recipient total could stay identical after a wrong-owner write; it is a secondary reconciliation only.

SHOW COLUMNS FROM vicidial_callbacks;SELECT callback_id,lead_id,list_id,campaign_id,user,recipient,callback_time,status,lead_statusFROM vicidial_callbacksWHERE campaign_id='LAB01' AND lead_id=900001 AND callback_id IN (501,502)ORDER BY callback_id;

You should seeThe plan ends with a targetable supported operation plus exact independent verification, or a precise unsupported-operation handoff.

• Input=changed+no-op+rejected/unsupported, with one deterministic plan per callback identity.
• Every accepted changed row matches its exact intended after-state; every untouched sibling retains its recorded state.
• Before reversal, compare current owner/schedule/status with the accepted after-state; a concurrent change blocks automatic rollback.

Stop cleanly and reverse only proved changes

Stop the worker on authentication errors, ambiguous responses, count mismatch or a duplicate discovered after a write. Do not continue from the next row until an independent read has classified the prior operation.

Rollback from the immutable ledger in reverse operation order using the same supported, idempotent path. Re-read each callback after reversal and retain only sanitized reconciliation totals in the incident record.

Rollback is itself a write. An existence-only no_update check cannot reveal later changes: independently re-read the current owner, schedule and status against the ledger before reversing a row. Stop when they differ or cannot be verified.

• Changed plus no-op plus rejected must equal the input count.
• Every reversed item must match its recorded original owner and schedule state.
• Unknown outcomes block dependent work until independently resolved.