Decoupling a 14-Year Monolith: How We Replaced Fragile Spreadsheets with a Custom ERP Without 1 Second of Downtime

Enterprise modernization fails most often not at the keyboard, but at the cutover.

When TGI Enterprise came to Kumasoft, their manufacturing and distribution operations were running on a 14-year-old on-premise relational database coupled with over 300 macro-heavy Excel workbooks. Every morning, six warehouse managers manually exported yesterday’s invoice tables, re-keyed pallet quantities into dispatch sheets, and prayed nobody overwrote cell formulas. A single locked table during end-of-month reconciliations would freeze order processing across three distribution centers.

The executive mandate was clear: replace the entire stack with a modern, cloud-native ERP. But there was a non-negotiable constraint: zero planned downtime during operational hours, zero missed shipments, and absolute financial ledger parity.

Here is the exact architectural playbook, production code, and hard-learned war stories from that cutover.

Why “Big Bang” Rewrites are Suicidal in Manufacturing

The standard vendor pitch for enterprise ERP modernization is the “Big Bang” cutover: spend nine months writing a new platform, freeze operations over a long holiday weekend, run an overnight database migration script, and switch DNS records Monday morning at 6 AM.

In manufacturing and supply chain logistics, this approach almost always creates catastrophic failure:

1. Hidden Business Logic in Stored Procedures: Decade-old triggers, undocumented check constraints, and manual stored procedures have baked-in business rules that no living employee fully remembers.
2. Schema Drift During Long Cycles: By month six of the rewrite, the legacy production schema has inevitably changed to accommodate seasonal promotions or tax adjustments.
3. Rollback Impossibility: Once transactions take place on the new system Monday morning, rolling back to the legacy database requires writing an emergency reverse-migration pipeline while orders fail.

We chose the Strangler Fig Architecture with Bi-Directional Event Synchronization. We ran the legacy system and the new Kumasoft ERP concurrently for 42 days, ensuring every order, pallet allocation, and invoice existed simultaneously in both databases before deprecating the legacy server.

System Topology & Data Flow

[ Legacy Clients / Barcode Scanners ]
               │
               ▼
   [ Legacy On-Premises DB ]
               │
               ▼ (WAL Replication)
      [ Debezium CDC Engine ]
               │
               ▼ (JSON Events)
     [ Apache Kafka Event Bus ] ──────┐
               │                      │
               ▼                      ▼
  [ Kumasoft Ingestion Worker ]  [ Audit & Reconciliation Daemon ]
               │                      ▲
               ▼                      │
     [ Cloud PostgreSQL 16 ] ────────┘
               │
               ▼
[ Modern Kumasoft ERP Web & Mobile ]

Phase 1: Real-Time Change Data Capture (CDC)

Rather than writing fragile application-level triggers into the legacy database, we attached a Change Data Capture (CDC) engine directly to the database Write-Ahead Log (WAL). Every INSERT, UPDATE, and DELETE on inventory and invoice tables was published to a Kafka cluster within 80 milliseconds.

// Kafka Ingestion Consumer with Idempotency Key Validation
import { Kafka } from "kafkajs";
import { db } from "@/lib/db";
import { sql } from "drizzle-orm";

interface CDCInventoryEvent {
  schema: string;
  table: string;
  op: "c" | "u" | "d"; // create, update, delete
  before: { sku: string; stock_qty: number; warehouse_id: string } | null;
  after: { sku: string; stock_qty: number; warehouse_id: string };
  ts_ms: number;
}

export async function consumeInventoryCDC(event: CDCInventoryEvent) {
  const idempotencyKey = `cdc_${event.table}_${event.after.sku}_${event.ts_ms}`;

  await db.transaction(async (tx) => {
    // 1. Enforce atomic idempotency in Postgres
    const alreadyProcessed = await tx.execute(
      sql`SELECT 1 FROM sync_audit_log WHERE key = ${idempotencyKey} FOR UPDATE SKIP LOCKED`
    );

    if (alreadyProcessed.rowCount > 0) {
      return; // Deduplicate redelivered Kafka frames
    }

    // 2. Upsert modern normalized ledger
    await tx.execute(sql`
      INSERT INTO inventory_balances (sku, warehouse_id, available_quantity, updated_at)
      VALUES (${event.after.sku}, ${event.after.warehouse_id}, ${event.after.stock_qty}, NOW())
      ON CONFLICT (sku, warehouse_id) 
      DO UPDATE SET 
        available_quantity = EXCLUDED.available_quantity,
        updated_at = NOW()
      WHERE inventory_balances.updated_at <= TO_TIMESTAMP(${event.ts_ms} / 1000.0);
    `);

    // 3. Mark processed in audit ledger
    await tx.execute(sql`
      INSERT INTO sync_audit_log (key, processed_at) VALUES (${idempotencyKey}, NOW());
    `);
  });
}

Phase 2: Resolving the Out-of-Order Delivery Trap

In distributed systems, networks drop and Kafka partitions can deliver state changes slightly out of chronological order. If an order reservation event (qty: 85) arrives 15 milliseconds after an order cancellation event (qty: 100), naive writes cause phantom inventory discrepancies.

To eliminate this, we embedded PostgreSQL Advisory Locks combined with epoch watermarking:

1. Every inventory balance record stores the exact microsecond timestamp of the last legacy transaction.
2. In-flight writes are conditionally committed only if incoming_ts > stored_ts.
3. If an older packet arrives late, it is diverted to an anomalous audit table rather than overwriting fresh data.

The Dual-Write Shadow Period

For 42 continuous days, we ran Shadow Writes:

• All production orders continued to flow through legacy barcodes and ERP terminal screens.
• The CDC pipeline mirrored the orders into the new Kumasoft platform.
• An automated verification daemon ran every midnight, comparing row counts, floating-point invoice totals, and inventory allocations between the legacy on-prem database and the cloud PostgreSQL instance.

Over 1.4 million transactions were processed during this shadow phase. The reconciliation daemon flagged a grand total of 14 discrepancies—all 14 turned out to be silent floating-point rounding bugs in the legacy system’s decade-old invoice calculation routines.

-- Midnight Reconciliation Query Excerpt
SELECT 
  legacy.sku,
  legacy.legacy_stock,
  modern.available_quantity AS modern_stock,
  ABS(legacy.legacy_stock - modern.available_quantity) AS delta
FROM legacy_inventory_mirror legacy
JOIN inventory_balances modern 
  ON legacy.sku = modern.sku AND legacy.warehouse_id = modern.warehouse_id
WHERE legacy.legacy_stock <> modern.available_quantity;

The Final Cutover: 12 Minutes, Zero Errors

On the night of March 28, the team conducted the final switch:

1. 22:00: Switched warehouse RF scanner terminals to point to the new Kumasoft edge GraphQL API.
2. 22:04: Flushed remaining Kafka CDC queues (0 pending lag).
3. 22:08: Turned on the outbound sync pipeline from the cloud ERP to legacy billing systems (allowing accounting to view orders until quarterly closing).
4. 22:12: Completed cutover sanity tests.

When the morning shift arrived at 06:00, order dispatch times dropped from an average of 4.2 minutes per pallet to under 38 seconds.

Measured Business Outcomes

• +55% Operational Efficiency: Warehouse teams ceased all manual spreadsheet reconciliations.
• 0 Seconds Production Downtime: Not a single distribution line paused during the 42-day transition.
• 100% Data Integrity: $42M in annualized product volume transitioned with zero lost line items or ledger imbalances.
• Sub-Second Search: Order history lookups across 10 years of data reduced from 45-second table scans to 18-millisecond indexed queries.