I think I found your issue. We had this exact problem last year and it took us weeks to track down. The root cause was the serial registration order relative to the batch split event timing in the genealogy tracking system.
Here’s what’s happening: When you split a batch, the system creates the child batch records first, then attempts to reassign serials to the new batches. However, the genealogy report generation uses a stored procedure that queries based on batch creation timestamp and serial assignment timestamp. If serials are assigned BEFORE the batch hierarchy relationship is fully committed, they get orphaned in the report query.
The fix involves three steps addressing all the focus areas:
1. Batch Split Event Timing:
Modify your batch split workflow to ensure the parent-child relationship is committed before serial reassignment:
-- Add explicit commit after hierarchy creation
INSERT INTO genealogy_parent_child (parent_batch, child_batch, split_time)
VALUES ('BATCH-001', 'BATCH-001-A', CURRENT_TIMESTAMP);
COMMIT;
-- Then proceed with serial reassignment
2. Serial Registration Order:
Update the event processor configuration to enforce serial registration sequencing:
In event_processor.properties:
genealogy.batch.split.wait_for_hierarchy=true
genealogy.serial.assignment.verify_parent=true
genealogy.event.sequence.enforce=true
This ensures serial assignments wait for the batch hierarchy to be fully established.
3. Genealogy Report Generation:
Modify your report query to use a LEFT JOIN instead of INNER JOIN when pulling serial data:
SELECT b.batch_id, b.parent_batch, s.serial_number, s.registration_time
FROM genealogy_batches b
LEFT JOIN genealogy_serials s ON b.batch_id = s.batch_id
LEFT JOIN genealogy_parent_child pc ON b.batch_id = pc.child_batch
WHERE b.batch_id IN (SELECT batch_id FROM batch_operations WHERE operation_date >= ?)
ORDER BY pc.split_time, s.registration_time;
The LEFT JOIN ensures child batches appear even if there’s a timing issue with serial assignment. You should also add a data validation step in your report generation job:
// Validate serial count before finalizing report
int expectedSerials = getExpectedSerialCount(batchId);
int reportedSerials = reportData.getSerialCount(batchId);
if (reportedSerials < expectedSerials) {
logger.warn("Incomplete serial data for batch: " + batchId);
// Trigger retry or alert
}
Additional Recommendations:
- Enable detailed logging in the genealogy tracking module to capture the exact sequence of batch split and serial assignment events
- Add a database trigger to validate that serial assignments only occur after batch hierarchy records exist
- Implement a post-split validation routine that runs 60 seconds after batch splits to verify all serials are properly linked
- Consider adding a genealogy_audit table to track the complete sequence of operations for troubleshooting
After implementing these changes, run a test batch split and monitor the event processor logs closely. You should see the hierarchy creation complete before any serial reassignments occur. The report should then capture all serials consistently.
Let me know if you need help with the specific SQL modifications or event processor configuration changes.