Multi-System Data Reconciliation Pattern

var DataReconciliationEngine = Class.create();
DataReconciliationEngine.prototype = {
    initialize: function() {
        this.auditTable = 'x_reconcile_audit';
        this.precedenceTable = 'x_reconcile_precedence';
    },

    reconcileRecord: function(tableName, entityId, incomingData, sourceSystem) {
        var goldenRecord = new GlideRecord(tableName);
        if (!goldenRecord.get(entityId)) {
            gs.error('Golden record not found: ' + entityId);
            return false;
        }

        var precedenceRules = this._getPrecedenceRules(tableName);
        var changes = [];
        var hasChanges = false;

        // Process each incoming field
        for (var field in incomingData) {
            var incomingValue = incomingData[field];
            var currentValue = goldenRecord.getValue(field);
            var currentSource = this._getCurrentSource(tableName, entityId, field);
            var fieldPrecedence = precedenceRules[field] || [];
            
            // Determine if this source should win
            var shouldUpdate = this._shouldUpdateField(sourceSystem, currentSource, fieldPrecedence, incomingValue, currentValue);
            
            if (shouldUpdate) {
                // Create audit record before change
                this._createAuditRecord(tableName, entityId, field, currentValue, incomingValue, currentSource, sourceSystem);
                
                goldenRecord.setValue(field, incomingValue);
                this._updateSourceTracking(tableName, entityId, field, sourceSystem);
                
                changes.push({
                    field: field,
                    oldValue: currentValue,
                    newValue: incomingValue,
                    source: sourceSystem
                });
                hasChanges = true;
            }
        }

        if (hasChanges) {
            goldenRecord.update();
            gs.info('Reconciled ' + changes.length + ' fields for ' + entityId + ' from ' + sourceSystem);
        }

        return {
            updated: hasChanges,
            changes: changes
        };
    },

    _getPrecedenceRules: function(tableName) {
        var rules = {};
        var gr = new GlideRecord(this.precedenceTable);
        gr.addQuery('table_name', tableName);
        gr.query();
        
        while (gr.next()) {
            var fieldName = gr.getValue('field_name');
            if (!rules[fieldName]) {
                rules[fieldName] = [];
            }
            rules[fieldName].push({
                system: gr.getValue('source_system'),
                priority: gr.getValue('priority')
            });
        }
        
        // Sort by priority (lower number = higher priority)
        for (var field in rules) {
            rules[field].sort(function(a, b) { return a.priority - b.priority; });
        }
        
        return rules;
    },

    _shouldUpdateField: function(sourceSystem, currentSource, precedence, incomingValue, currentValue) {
        // Always update if no current value
        if (!currentValue && incomingValue) {
            return true;
        }
        
        // Don't update if values are the same
        if (incomingValue == currentValue) {
            return false;
        }
        
        // Check precedence rules
        var sourcePriority = this._getSystemPriority(sourceSystem, precedence);
        var currentPriority = this._getSystemPriority(currentSource, precedence);
        
        // Lower priority number wins
        return sourcePriority < currentPriority;
    },

    _getSystemPriority: function(system, precedence) {
        for (var i = 0; i < precedence.length; i++) {
            if (precedence[i].system == system) {
                return precedence[i].priority;
            }
        }
        return 999; // Default low priority for unknown systems
    },

    _getCurrentSource: function(tableName, entityId, field) {
        var gr = new GlideRecord('x_reconcile_sources');
        gr.addQuery('table_name', tableName);
        gr.addQuery('entity_id', entityId);
        gr.addQuery('field_name', field);
        gr.query();
        
        if (gr.next()) {
            return gr.getValue('source_system');
        }
        return 'unknown';
    },

    _updateSourceTracking: function(tableName, entityId, field, sourceSystem) {
        var gr = new GlideRecord('x_reconcile_sources');
        gr.addQuery('table_name', tableName);
        gr.addQuery('entity_id', entityId);
        gr.addQuery('field_name', field);
        gr.query();
        
        if (gr.next()) {
            gr.setValue('source_system', sourceSystem);
            gr.setValue('last_updated', new GlideDateTime());
            gr.update();
        } else {
            gr.initialize();
            gr.setValue('table_name', tableName);
            gr.setValue('entity_id', entityId);
            gr.setValue('field_name', field);
            gr.setValue('source_system', sourceSystem);
            gr.insert();
        }
    },

    _createAuditRecord: function(tableName, entityId, field, oldValue, newValue, oldSource, newSource) {
        var audit = new GlideRecord(this.auditTable);
        audit.initialize();
        audit.setValue('table_name', tableName);
        audit.setValue('entity_id', entityId);
        audit.setValue('field_name', field);
        audit.setValue('old_value', oldValue || '');
        audit.setValue('new_value', newValue || '');
        audit.setValue('old_source', oldSource);
        audit.setValue('new_source', newSource);
        audit.setValue('reconciled_at', new GlideDateTime());
        audit.setValue('reconciled_by', gs.getUserID());
        audit.insert();
    },

    type: 'DataReconciliationEngine'
};