Auto-Repair Engine (Detect Broken Foreign Keys, Orphan Records, Mismatched Totals)

1. Introduction

Enterprise databases rarely stay clean forever.

Foreign keys get orphaned, totals fall out of sync, reference values disappear, and distributed systems occasionally write partial or inconsistent updates.

Instead of relying only on manual fixes or scripts written after failures, teams can build an Auto-Repair Engine — a system that continuously detects, explains, and optionally fixes data integrity issues.

This article explains how to design such a system architecture-first, database-second, and automation-third.

2. Why Do Databases Get Dirty?

Typical scenarios include:

Cause	Example
Partial writes during network/API failures	Order header saved, items failed
Soft deletes without cascades	User deleted, tokens remain
Eventual consistency lag	Stock update out of sync with reservations
Bad imports or migrations	Legacy data missing references
Application bugs	Wrong totals or duplicate relations

A repair engine exists to automatically detect and fix these issues before they impact workflows, reporting, or analytics.

3. Core Design Philosophy

A good Auto-Repair Engine must be:

Non-destructive → Prefer logging over modifying.
Auditable → Every fix must be traceable.
Configurable → Some repairs are auto-safe, others require approval.
Scheduler-based → Should run periodically or on demand.
Rules-driven → Fix logic shouldn't be hardcoded.

This means the engine cannot be just SQL scripts — it must be metadata-driven.

4. Architecture Overview

                   ┌─────────────────────┐
                   │  Rule Metadata DB   │
                   └───────┬─────────────┘
                           │
                   ┌───────▼──────────────┐
                   │   Detector Engine    │
                   └───────┬──────────────┘
                           │
         ┌───────────┬─────┴─────┬────────────┐
         │           │           │            │
 ┌───────▼───────┐ ┌─▼────────┐ ┌▼──────────┐ ┌───────────────┐
 │Orphan Checker │ │Totals Fix│ │DuplicateChk│ │ReferentialChk │
 └───────┬───────┘ └─────┬────┘ └──────┬─────┘ └─────┬─────────┘
         │                │             │              │
         └───────────┬────┴─────┬──────┴────┬─────────┘
                     │          │          │
               ┌─────▼──────────▼──────────▼───────┐
               │     Repair Action Processor        │
               └───────────┬────────────────────────┘
                           │
                 ┌─────────▼─────────┐
                 │ Audit & Reporting  │
                 └────────────────────┘

5. The Rule Model

Each rule defines:

Field	Example
CheckType	`OrphanFK`
Table	`PurchaseOrderLine`
ReferenceTable	`PurchaseOrder`
Severity	Warning / Critical
FixStrategy	`SET NULL`, `DELETE`, `RECREATE`, `RECALCULATE`
RunMode	DetectOnly, AutoFix, ManualApproval

Example record

INSERT INTO RepairRules
(RuleName, CheckType, TableName, ReferenceTable, FixStrategy, RunMode)
VALUES
('POL Orphans', 'OrphanFK', 'PurchaseOrderLine', 'PurchaseOrder', 'DELETE', 'ManualApproval');

6. Detection Engine Patterns

A) Orphaned Foreign Keys

SELECT pol.*FROM PurchaseOrderLine pol
LEFT JOIN PurchaseOrder po ON po.Id = pol.POId
WHERE po.Id IS NULL;

B) Mismatched Totals

SELECT o.Id, o.Total, SUM(l.Amount) AS Calculated
FROM Orders o
JOIN OrderLine l ON l.OrderId = o.Id
GROUP BY o.Id, o.Total
HAVING o.Total <> SUM(l.Amount);

C) Broken Many-to-Many

SELECT map.*FROM UserRole map
LEFT JOIN Users u ON u.Id = map.UserId
LEFT JOIN Roles r ON r.Id = map.RoleId
WHERE u.Id IS NULL OR r.Id IS NULL;

7. Repair Execution Strategies

Scenario	Recommended Fix
Orphan rows in weak tables	Delete
Orphan rows in strong entities	Reassign parent
Totals mismatch	Recalculate
Duplicate data	Keep latest + archive others
Missing reference	Auto-create placeholder

Every action should create before/after snapshots.

Example

UPDATE Orders
SET Total = Calc.Calculated
OUTPUT deleted.*, inserted.*INTO RepairAudit
FROM (
    SELECT o.Id, SUM(l.Amount) AS Calculated
    FROM Orders o
    JOIN OrderLine l ON l.OrderId = o.Id
    GROUP BY o.Id
) Calc
WHERE Orders.Id = Calc.Id AND Orders.Total <> Calc.Calculated;

8. User Interaction Model

Mode 1 — Auto Fix → Safe rules run silently.
Mode 2 — Semi-Automatic → Generate fix scripts for approval UI.
Mode 3 — Fully Manual → Only report.

9. Scheduling Strategy

Run light checks hourly.
Run heavy consistency checks nightly.
Run full integrity scan during maintenance windows.

10. Reporting & Observability

Outputs must include:

Number of violations.
Repair actions taken.
Before/after comparisons.
Rules that failed or need tuning.

Export options:

PDF Summary
Excel detail sheet
Web UI with drilldown

11. Conclusion

A well-designed Auto-Repair Engine improves reliability, reduces support workload, and ensures data integrity across growing systems — especially in multi-tenant and distributed architectures.

It evolves into a self-healing data platform, where the database detects and fixes issues proactively instead of waiting for users to complain.