Automating Stock Reconciliation Across 4 Tables

Every week, operations teams spent 2 days reconciling stock. Transfers between stores, restocking from warehouse, inventory adjustments. All done manually by cross-referencing the database against spreadsheets and fixing discrepancies by hand.

104 hours a month. For a process that was fundamentally data entry and arithmetic.

Problem

The inventory system used 4 related tables: inv_location (physical stock per location), sales_inventory (running totals), sales_history (historical records), and parent records that aggregated totals across child rows.

A stock transfer required updating rows in all 4 tables in the correct order. If someone updated inv_location but not sales_inventory, the totals were wrong. If they updated the parent total before finishing the child rows, the aggregate was off.

Doing this manually across 100 stores meant constant small errors that accumulated and required weekly correction cycles.

There was no audit trail. When a discrepancy appeared, tracing it back to the source was guesswork.

Solution

A transactional reconciliation process that updates all 4 tables in the correct order within a single database transaction.

When a transfer or restock comes in, the system:

• Validates the transfer data before any writes
• Updates inv_location for the source and destination
• Adjusts sales_inventory running totals
• Writes to sales_history with a timestamp and reference number
• Recalculates the parent aggregate totals
• Commits or rolls back the entire operation as a unit

ACID transaction integrity means all 4 tables update together or none of them do. There is no state where one table reflects the transfer and another does not.

Audit logging captures every operation with a reference number, timestamp, user ID, and before/after values. When a discrepancy shows up now, tracing it takes seconds.

Result

Weekly reconciliation effort dropped from 2 days to under 1 hour. The remaining hour is for reviewing the automated log, not manual data entry.

Monthly labor savings across the operations team are over 100 hours.

Data consistency across the 4 tables is reliable. The discrepancy rate dropped to near zero because the system enforces correct update order on every operation.