Introduction
I built a script to merge duplicate vehicle blacklist records left behind by an earlier refactor. A blacklist entry could already support multiple sites, but there was no script to consolidate the duplicates. What was missing was the cleanup operation, not multi-site support.
The script groups records by an uppercase-normalized vehicle registration mark (VRM), chooses an existing row as the parent, and bulk-reassigns images from the other rows to that parent. The initial execution hit a transaction timeout and rolled back. After changes to the image update and transaction timeout, the script ran successfully. The reported site run finished in approximately 11 minutes and significantly reduced duplicate groups.
That result isn't a guarantee of complete cleanup. The evidence doesn't establish that every duplicate disappeared, that every site had the same result, or that the revised timeout would be enough for larger runs. I can support the implementation and the reported execution, but not those broader claims.
The Rule That Shipped
I grouped duplicates by the vrm field normalized to uppercase, without including site in the grouping key. Within each group, the row with the earliest createdAt becomes the parent. If rows share that timestamp, the lowest id wins. Images attached to non-parent rows are bulk-reassigned to the selected parent.
The grouping rule is separate from candidate selection. Matching VRMs from different sites can belong to the same parent-selection group if those rows are in the candidate set. The evidence doesn't say whether the reported run selected records from all sites or only from the named site's dataset. A run associated with one site isn't proof of a database-wide scan.
The transaction timeout was increased to accommodate the bulk image update. The new value isn't recorded, so I can't give a specific duration.
Uppercasing only establishes case normalization. There's no evidence of additional handling for spaces, punctuation, or malformed registrations. The parent-selection rule also doesn't document how every other field or relationship is reconciled; it tells us which row wins.
Worked Example
The reported worked case was the run against the South Point site's blacklist duplicate records. It completed successfully in approximately 11 minutes and significantly reduced duplicate groups. Exact before-and-after counts aren't available.
The rule starts with the records supplied to that run. Their VRMs are normalized to uppercase, and matching values form groups. In each duplicate group, the earliest createdAt determines the parent, with the lowest id breaking a timestamp tie. Images attached to the other rows are then bulk-reassigned to that parent.
South Point identifies the reported dataset; it isn't an extra grouping field. If the candidate set included matching rows from another site, those rows would join the same normalized-VRM group and could become the parent under the same ordering rule. The evidence doesn't establish whether any such cross-site candidates were included. I therefore can't identify a particular winning row or say that the run consolidated every site's copy of a registration.
The supported result is that the merge finished successfully, the implemented image handling reassigned images to selected parents, and duplicate groups were significantly reduced. The evidence doesn't show that the remaining duplicate-group count was zero.
Challenges and Tradeoffs
Grouping by VRM rather than VRM plus site fits a model that supports multiple sites on one blacklist entry. Including site in the key would keep otherwise matching registrations in separate groups. That's a consequence of the rule, not evidence of my personal reason for choosing it. Within a candidate group, the rule provides no site-specific exception.
Oldest-row selection makes parent choice precise: it favors the earliest-created row rather than the newest copy. That could be read as a policy of preserving the earliest record, but the evidence doesn't establish why oldest was preferred. Choosing the newest row would be a different conflict rule, not another way to implement this one.
Execution raised a separate problem. The image reassignment hit a transaction timeout and rolled back. The applied changes used bulk image reassignment and a longer timeout, after which the run succeeded. That sequence supports the fix for the reported execution; it doesn't show that timeout risk has gone away.
Smaller, per-group transactions are one possible alternative. They would change the commit boundaries and add commit overhead, but there's no comparison showing whether they would make this workload faster or slower. They would also need a clear approach to partial completion and retries. The available evidence doesn't establish those behaviors for the shipped script.
Verification Process
I keep execution evidence separate from questions about persisted state. After the timeout rollback, I asked an AI assistant about rollback behavior and the possibility of stale data. That conversation wasn't a database inspection. It doesn't establish whether stale data existed or whether any persisted inconsistency was repaired.
After the bulk-image-update and timeout changes, the script ran without reported errors. The South Point run finished in approximately 11 minutes and significantly reduced duplicate groups. Those observations support successful execution of that run, but they don't amount to a complete data-integrity audit.
I haven't verified exact duplicate-group counts before and after the merge, whether residual duplicates remained, or whether every other site had the same error-free result and timing. The exact increased timeout is also unknown, and its headroom for larger workloads remains unverified.
A stronger verification record would document the candidate-selection scope, count groups before and after execution using the same uppercase-normalized rule, and check that affected images reference the intended parents. Resolving the stale-data question would require direct inspection after the failed transaction. These are checks still needed, not checks I can claim were completed.
Conclusion
I implemented a merge rule that groups records by uppercase-normalized VRM, selects the parent by earliest createdAt with lowest id as the tiebreaker, and bulk-reassigns images to that parent. After the timeout increase and image-update changes, the reported run completed in approximately 11 minutes with significantly fewer duplicate groups.
That's the supported result, not proof of complete consolidation or an end to rollback risk. The candidate-selection scope, residual duplicates, persisted state after the failed transaction, other-site outcomes, and capacity for larger runs remain unresolved. Those limits matter when judging what the successful run demonstrated.