Architecting API Facades Over Legacy Databases: Bi-Directional Write Patterns and Data Integrity

An API facade over a legacy database exposes modern REST or gRPC endpoints while bypassing outdated application layers. To maintain data integrity during bi-directional writes, architects use Change Data Capture (CDC) with the Transactional Outbox pattern, enforce optimistic locking via shadow columns or LSN checks, and re-implement critical business rules in the API layer rather than relying on database triggers.

The Direct Database Facade Trap: Why Raw SQL Wrapped in REST Fails

Engineers often start modernizing an enterprise system by pointing an auto-generating API tool (like PostgREST, Hasura, or custom ORM models) straight at an existing MySQL or SQL Server database. On paper, this delivers a CRUD API in an afternoon. In practice, it breaks data integrity within a week.

Legacy databases rarely rely purely on relational constraints for domain logic. Over 10 to 20 years, business rules bleed into the layer above the database—stored inside C# monoliths, Java Enterprise Beans, COBOL programs, or hidden database triggers.

When your new API facade executes a plain UPDATE orders SET status = 'CANCELLED' WHERE id = 4821;, it bypasses the implicit side effects engineered into the original application:

• Bypassed Side Effects: The legacy monolith updated three satellite tables, emitted an audit entry, recalculated customer credit limits, and triggered an inventory sync on every status change.
• Dirty Reads and Phantom Overwrites: The legacy application uses low transaction isolation levels (such as READ UNCOMMITTED to prevent lock contention), while your API expects READ COMMITTED or REPEATABLE READ.
• Missing Invariants: Nullable columns that "everyone knows" must contain a valid string because the legacy UI enforced it at the form level, not via a NOT NULL DB constraint.

If you are running legacy modernization projects, treating an old schema as a simple object storage engine leads to corrupted audit logs and broken downstream batch runs. You are not building an API over a database; you are building an API over an undocumented state machine.

Architecture Patterns for Bi-Directional Write Synchronization

When both the legacy application and the new API facade write to the same database simultaneously, dual writes are the most dangerous path. If the API writes to the database and then attempts to emit an event to Kafka or HTTP-sync to an external service, one of those operations will eventually fail. The system enters a split-brain state.

The Transactional Outbox Pattern

To eliminate non-atomic dual writes, use the Transactional Outbox pattern. When the API facade updates a row in the legacy schema, it writes an event payload to a dedicated outbox table within the same database transaction.

BEGIN TRANSACTION;

-- Perform write on legacy table
UPDATE customer_account 
SET credit_limit = 50000.00 
WHERE account_id = 'ACC-9021';

-- Record event in outbox table within same ACID boundary
INSERT INTO api_outbox_events (
    event_id, 
    aggregate_type, 
    aggregate_id, 
    payload, 
    created_at
) VALUES (
    gen_random_uuid(), 
    'CustomerAccount', 
    'ACC-9021', 
    '{"account_id": "ACC-9021", "credit_limit": 50000.00, "updated_by": "API_FACADE"}', 
    NOW()
);

COMMIT;

A log tailer like Debezium streams events out of the api_outbox_events table by reading the database Write-Ahead Log (WAL) or transaction log. This ensures guaranteed at-least-once delivery of API actions to downstream services without slowing down the primary write path or running dual-write risks.

Change Data Capture for Legacy-Initiated Writes

To detect writes made directly by the legacy monolith, configure Debezium directly on the primary legacy tables.

When the legacy application updates a row, Debezium extracts the pre-image and post-image from the database log sequence number (LSN) and publishes the mutation event to Kafka. The API facade or read-model caching layer consumes this event to invalidate local caches or trigger downstream modern workflows.

Handling Data Integrity: Optimistic Locking and Shadow Columns

Legacy schemas rarely include strict version numbers or reliable ISO-8601 updated_at timestamps. If the legacy monolith updates a row while the API facade is handling a request for the same row, last-write-wins (LWW) will silently overwrite data.

Strategy 1: Schema Mutation (Adding Version Columns)

If you can safely alter the legacy schema, add a dedicated concurrency column:

• PostgreSQL/MySQL: Add an integer column version INT DEFAULT 0 and increment it on every update.
• SQL Server: Add a native ROWVERSION (timestamp) column, which automatically updates its 8-byte binary value on every row mutation.

Your API executes updates with a strict optimistic concurrency check:

UPDATE inventory_item 
SET quantity = 142, version = version + 1
WHERE id = 8819 AND version = 4;

If the row count returned is 0, a concurrent write occurred (either from the legacy app or another API worker). The API facade must rollback, fetch the current state, apply domain logic, and retry.

Strategy 2: Content Hashing (Zero Schema Changes)

When database alterations are forbidden due to vendor lock-in or strict DBA controls, compute a hash of the target columns on read. Use this hash as an ETag header in your REST facade.

// Generate ETag from row state
func ComputeETag(id int, name string, balance float64) string {
    data := fmt.Sprintf("%d:%s:%.2f", id, name, balance)
    hash := sha256.Sum256([]byte(data))
    return hex.EncodeToString(hash[:])
}

When the client issues a PUT or PATCH request with the If-Match header, the facade issues a conditional update verifying that the target values match the state the client read:

UPDATE customer_balances
SET balance = 1250.50
WHERE id = 1092 
  AND MD5(CONCAT(balance, ':', status)) = 'e4d909c290d0fb1ca068ffaddf22cbd0';

Implementation: Setting Up Debezium, Kafka, and OpenAPI Facades

Setting up a resilient API facade requires explicit plumbing between the database engine, the log capture process, and the endpoint layer.

Debezium PostgreSQL Connector Configuration

Below is a production-ready Debezium connector JSON configuration targeting a legacy PostgreSQL instance. It isolates mutations and routes them to a Kafka broker with strict transactional guarantees.

{
  "name": "legacy-postgres-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",
    "database.hostname": "legacy-db.internal.net",
    "database.port": "5432",
    "database.user": "cdc_debezium",
    "database.password": "${file:/secrets/db.properties:cdc_password}",
    "database.dbname": "production_legacy",
    "database.server.name": "legacy_pg",
    "plugin.name": "pgoutput",
    "table.include.list": "public.orders,public.api_outbox_events",
    "tombstones.on.delete": "false",
    "decimal.handling.mode": "double",
    "slot.name": "debezium_api_facade_slot"
  }
}

High-Throughput Proxying in Rust or Go

When building the API layer that consumes these database reads and outbox streams, performance matters. P99 latency overhead for the facade should remain under 5ms.

If your team is evaluating lower-level languages for this proxy layer, read our evaluation on whether you should rewrite in Rust for systems where memory footprint and predictable concurrency are paramount.

// Go implementation of an optimistic locking update retry handler
func UpdateAccountBalance(ctx context.Context, db *sql.DB, accountID string, delta float64) error {
    maxRetries := 3
    for i := 0; i < maxRetries; i++ {
        tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
        if err != nil {
            return err
        }

        var balance float64
        var version int
        err = tx.QueryRowContext(ctx, 
            "SELECT balance, version FROM accounts WHERE id = $1", accountID).Scan(&balance, &version)
        if err != nil {
            tx.Rollback()
            return err
        }

        newBalance := balance + delta
        if newBalance < 0 {
            tx.Rollback()
            return errors.New("insufficient funds")
        }

        res, err := tx.ExecContext(ctx, 
            "UPDATE accounts SET balance = $1, version = version + 1 WHERE id = $2 AND version = $3",
            newBalance, accountID, version)
        if err != nil {
            tx.Rollback()
            return err
        }

        rows, _ := res.RowsAffected()
        if rows == 1 {
            return tx.Commit()
        }

        // Conflict: Roll back transaction and attempt retry loop
        tx.Rollback()
        time.Sleep(time.Duration(20*(i+1)) * time.Millisecond)
    }
    return errors.New("concurrent modification limit exceeded")
}

Performance Comparison: Facade Architectures Under Load

Choosing the wrong pattern introduces severe query overhead, locking contention, or sync delays. Below is a breakdown of four primary API facade patterns evaluated under high-throughput workloads (10,000+ writes/sec).

Handling Schema Drift and Stored Procedure Dependencies

Modernizing a legacy system requires accounting for legacy database drift. Schema modifications happen without warning when a third-party vendor updates the legacy software or an emergency fix is deployed straight to production by database administrators.

Auditing Dependency Chains

Before building write endpoints, map all underlying stored procedure and trigger dependencies. Running explicit dependency checks prevents API writes from causing cascading deadlocks or unhandled exceptions.

On Microsoft SQL Server, run this metadata query to extract hidden execution chains attached to your target table:

SELECT 
    referencing_entity_name = o.name,
    referencing_type = o.type_desc,
    referenced_entity_name = sm.referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
JOIN sys.objects AS o ON sed.referencing_id = o.object_id
JOIN sys.sql_modules AS sm ON sed.referencing_id = sm.object_id
WHERE sed.referenced_id = OBJECT_ID('dbo.orders');

Isolating Legacy Triggers

If a legacy table uses heavy database triggers (AFTER UPDATE, FOR INSERT), those triggers run inside the transaction context of your API facade calls.

If a trigger takes 400ms to run because it sends a synchronous mail notification or updates ten historical tables, your API endpoint latency will degrade to >400ms.

To neutralize trigger performance penalties without breaking legacy features:

1. Refactor the Trigger: Convert slow, synchronous trigger logic into an asynchronous background worker triggered by CDC outbox events.
2. Use Context Flags: If you cannot modify the trigger, configure your API connection pool to set a session variable (e.g., SET LOCAL app.context = 'api_facade'). Modify the trigger code to exit immediately if app.context is detected, moving responsibility for those side effects to your modern microservices.

What This Means for Your Team

Exposing an API facade over an operational legacy database is a core tactic in system modernization, but wrapping a database driver in HTTP handlers is not enough.

To execute this architecture successfully:

• Avoid dual-write anti-patterns: Never issue separate HTTP syncs or message bus publishes alongside database updates without a local transactional outbox.
• Enforce optimistic concurrency: Use version columns, native database row versions, or payload hashing to prevent last-write-wins bugs between your API and legacy workers.
• Decouple side-effects via CDC: Deploy Debezium or similar log tailers to asynchronously sync reads and notify downstream services without adding latency to primary transactions.

If you are replacing a legacy monolith or designing an event-driven facade over a high-throughput database, our engineering teams build these systems routinely. Contact NextGen Coding Company to talk directly with a staff engineer.