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