Designing a Job That Auto-Fixes Data Mismatches (Stock, Quantity, Totals)

Data mismatches are one of the most common problems in inventory-based systems.
Fields like Quantity, QuantityOnHand, QuantityAvailable, QuantityReserved, and QuantityIssued often fall out of sync after years of operations, faulty transactions, partial rollbacks, or integration errors.

This article explains how to design an automatic background job (SQL + .NET) that continuously scans mismatches and fixes them safely.

All headings are intentionally kept small, as per your preference.

1. Problem Overview

Typical mismatches include:

Quantity ≠ QuantityOnHand + QuantityReserved
Negative stock values
Reserved > QuantityAvailable
Totals not matching child tables (e.g., transactions)
Missing or duplicate ledger entries

A robust job must handle:

Detect
Compare
Recalculate
Fix
Log the fix
Notify

2. Target Tables and Fields

Example: StockLine table

Field	Meaning
Quantity	Total quantity in stock line
QuantityOnHand	Physical available quantity
QuantityIssued	Quantity issued
QuantityReserved	Quantity reserved
QuantityAvailable	Derived: QuantityOnHand - Reserved

3. Architecture Overview

.NET Background Service or SQL Job
        ↓
Mismatch Detection SP (SQL)
        ↓
Fix Processor (SQL or .NET)
        ↓
Audit Log
        ↓
Email / Dashboard Notification

4. ER Diagram (Simplified)

StockLine (PK: StockLineId)
 |
 |--- StockTransactions (PK: TransactionId, FK: StockLineId)
 |
 |--- Reservations (PK: ReservationId, FK: StockLineId)

5. Workflow / Flowchart (Auto-Fix Job)

Start
  ↓
Scan mismatches (SP)
  ↓
For each mismatch:
  ↓
Set correct values
  ↓
Save fix into AuditFixLog
  ↓
Notify via email/dashboard
  ↓
End

6. SQL Procedure – Detect Mismatches

CREATE PROCEDURE usp_DetectStockMismatches
ASBEGIN
    SELECT 
        SL.StockLineId,
        SL.Quantity,
        SL.QuantityOnHand,
        SL.QuantityReserved,
        SL.QuantityIssued,
        SL.QuantityAvailable,

        (SL.QuantityOnHand - SL.QuantityReserved) AS ExpectedAvailable,
        (SL.Quantity - SL.QuantityIssued) AS ExpectedOnHand
    FROM StockLine SL
    WHERE 
        SL.QuantityAvailable <> (SL.QuantityOnHand - SL.QuantityReserved)
        OR SL.QuantityOnHand <> (SL.Quantity - SL.QuantityIssued)
        OR SL.Quantity < 0
        OR SL.QuantityOnHand < 0
        OR SL.QuantityReserved < 0;
END

7. SQL Procedure – Auto Fix the Mismatch

CREATE PROCEDURE usp_FixStockMismatch
    @StockLineId BIGINTASBEGIN
    DECLARE @Qty INT, @Issued INT, @Reserved INT;

    SELECT 
        @Qty = Quantity,
        @Issued = QuantityIssued,
        @Reserved = QuantityReserved
    FROM StockLine
    WHERE StockLineId = @StockLineId;

    DECLARE @CorrectOnHand INT = @Qty - @Issued;
    DECLARE @CorrectAvailable INT = @CorrectOnHand - @Reserved;

    -- Log before fix
    INSERT INTO StockFixLog(StockLineId, OldData, NewData, FixedAt)
    SELECT
        @StockLineId,
        (SELECT * FROM StockLine WHERE StockLineId = @StockLineId FOR JSON AUTO),
        NULL,
        GETDATE();

    -- Apply fix
    UPDATE StockLine
    SET
        QuantityOnHand = @CorrectOnHand,
        QuantityAvailable = @CorrectAvailable
    WHERE StockLineId = @StockLineId;

    -- Log after fix
    UPDATE StockFixLog
    SET NewData = (SELECT * FROM StockLine WHERE StockLineId = @StockLineId FOR JSON AUTO)
    WHERE StockLineId = @StockLineId AND NewData IS NULL;
END

8. Background Job (ASP.NET Core)

public class AutoFixJob : BackgroundService
{
    private readonly IServiceProvider _provider;

    public AutoFixJob(IServiceProvider provider)
    {
        _provider = provider;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            await RunFixProcess();
            await Task.Delay(TimeSpan.FromMinutes(30), stoppingToken);
        }
    }

    private async Task RunFixProcess()
    {
        using var scope = _provider.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();

        var mismatches = await db.StockMismatches.FromSqlRaw(
            "EXEC usp_DetectStockMismatches"
        ).ToListAsync();

        foreach (var row in mismatches)
        {
            await db.Database.ExecuteSqlRawAsync(
                "EXEC usp_FixStockMismatch {0}", row.StockLineId);
        }
    }
}

9. Sample Dashboard Mockup (for Monitoring)

-----------------------------------------------------
| Stock Auto-Fix Dashboard                           |
-----------------------------------------------------
| Total Mismatches Today: 32                         |
| Auto-Fixed Successfully: 30                        |
| Failed Fixes: 2                                    |
-----------------------------------------------------
| Recent Fix Log                                     |
| StockLineId | Issue                | FixedAt       |
|--------------------------------------------------- |
| 55012        | Negative Available   | 12:15 PM      |
| 55013        | Wrong OnHand Calc    | 12:17 PM      |
-----------------------------------------------------

10. Scheduler Options

Choose any of the following:

Windows Task Scheduler running a .NET console app
Hangfire Recurring Job
Quartz.NET
SQL Server Agent Job
Kubernetes CronJob

11. Best Practices

Always log before and after fix
Never delete transaction history
Use JSON logs for full record snapshot
Send changes to monitoring team daily
Add a “DryRun mode” to check mismatches without fixing
Backup before running global fix operations

12. Production-Level Enhancements

Add a threshold (e.g., do not fix if variance > 500 qty)
Auto-email summary report
Archive log older than 90 days
Add validation rules per product type
Add “Manual Fix Required” queue in dashboard