From JDBC to AI-ready data: migrating without breaking existing consumers

Start with the promise the old system makes

A JDBC connection can conceal a surprisingly large contract. A consuming application may depend on column names, decimal precision, transaction isolation, the interpretation of an empty string, or the order of rows that its query never explicitly ordered. Moving the same tables to a new database does not preserve all those expectations. I would begin a modernization effort by cataloging observable behavior, then deciding which behavior must survive and which needs a deliberate version change.

This design study follows a hypothetical order-processing system into a Postgres operational database and a Redshift analytical projection. Existing Java consumers continue reading through a compatibility interface while new APIs use explicit resource contracts. The objective is not to translate every possible SQL statement at a network gateway. It is to establish a small, testable migration surface and move ownership through it without losing changes or quietly changing business meaning.

Separate compatibility, meaning, and routing

I would give the adapter three distinct responsibilities. Protocol compatibility handles driver behavior, authentication, connection management, and supported request shapes. Semantic translation maps old identifiers and representations into the new domain contract. Routing selects the authoritative destination for a request. These responsibilities can share a deployment initially, but they need separate tests. Otherwise an operational routing change can unexpectedly alter how a business value is interpreted.

Consider an order status recorded as a single legacy character. Converting that value into a named state is easy until two legacy values collapse into the same new state. A reverse migration can no longer reconstruct the original record. The mapping therefore needs a declared loss policy and, where necessary, preservation of the raw source value. Decimal money should have a stated currency and precision. A timestamp should identify its timezone assumptions and whether it represents an event, an ingestion, or an update.

For arbitrary legacy SQL, a narrow API adapter may not be a suitable replacement. Read-only compatibility views can preserve some query shapes; a controlled driver or endpoint change may preserve others. I would inventory actual queries and test the chosen approach against them. Unsupported writes should fail explicitly. Silently forwarding an unfamiliar statement to a different storage engine creates a much larger migration problem than a visible compatibility error.

Make every migrated change identifiable

Full-load copying establishes a starting dataset; change capture keeps that dataset moving toward the source. AWS DMS documents both full-load-plus-CDC and CDC-only tasks, as well as engine-specific capture requirements. Its guidance also makes clear that CDC is not a zero-latency replication guarantee. I would treat source position, target application position, and reconciliation state as operational data that the migration itself owns.

A conceptual event envelope appears below. The source position is intentionally opaque: a PostgreSQL log sequence number is not interchangeable with a position from another engine. Event identity must remain stable across retries, and the consumer must apply a change and record its receipt in the same local transaction where possible. This provides a practical idempotency boundary without claiming that every component executes exactly once.

Deletes need explicit representation. A consumer that only upserts changed rows may leave removed records indefinitely in its target. Out-of-order delivery also needs a policy: compare source versions within a defined partition or ordering domain, and reject stale mutations when that comparison is valid. Wall-clock timestamps alone are a weak substitute for a source ordering contract.

1{
2  "event_id": "orders:partition-0:position-1842",
3  "source": "legacy-orders",
4  "source_position": "opaque-source-offset",
5  "entity_id": "order-104",
6  "operation": "upsert",
7  "schema_version": 2,
8  "payload": {
9    "amount_minor": 12500,
10    "currency": "USD",
11    "status": "accepted",
12    "legacy_status": "A"
13  }
14}

Reconcile business meaning before switching traffic

Row counts are a useful first check, but matching counts can hide both missing and duplicated identities. I would reconcile primary-key sets, version distributions, null patterns, and aggregates whose semantics are stable across systems. A currency total should be grouped by currency; comparing mixed-currency sums would manufacture confidence from a meaningless number. Canonical row hashes can help isolate discrepancies only after normalization rules are defined.

Reconciliation should run against a comparable boundary. If the source is changing while the target is catching up, a difference may reflect time rather than corruption. Record the snapshot or source watermark being compared, the applicable normalization version, and the categories of excluded rows. For large tables, partition the comparison so an investigator can narrow a mismatch to a manageable range without repeating the entire migration.

I would also replay representative application reads against both systems in a shadow mode that cannot trigger external side effects. Compare business outputs, not only raw serialization. A different JSON key order is harmless; a missing canceled order may be significant. Maintain a discrepancy ledger with an owner and disposition. A migration gate based on that ledger is more informative than a dashboard whose only state is green.

Cut over one authority at a time

The most difficult moment is the transfer of write authority. Two writable systems can accept mutually incompatible updates even when replication appears healthy. I would prefer a bounded ownership transition: drain or pause the relevant writes, establish the final source boundary, verify that the target has applied it, switch the routing epoch, and resume through the new owner. The exact mechanism depends on availability requirements and the systems involved.

If uninterrupted dual writing is unavoidable, the design needs conflict resolution, partial-failure handling, and a recovery log before it needs a launch date. A successful write to one database and a timeout from another leaves an ambiguous state. Retrying both is not automatically safe. An outbox tied to the authoritative database transaction can make downstream projection intent durable, but consumers still need idempotency and reconciliation.

Rollback deserves its own rehearsal. Switching a connection string back is only a rollback while the old system can correctly represent and receive the new writes. Once the new schema admits previously impossible states, returning to the old application may require a compensating migration. I would document that point of no return and test a recovery path with deliberately interrupted cutovers.

Keep operational truth separate from analytical convenience

Postgres would own the current order state in this proposed design. Redshift would receive a governed projection suitable for analytical queries, with a visible freshness boundary. AWS documents important differences between Redshift and PostgreSQL despite their related SQL heritage. I would not assume that a query, constraint, or transaction pattern tested on one has identical behavior on the other.

The same discipline applies when the data becomes input to embeddings or ML features. A vector index needs source identity, version, deletion handling, and authorization metadata. A training table needs a documented observation time and label availability time. Neither becomes an authoritative substitute for the operational record merely because it is convenient to query. Modernization earns its value when those boundaries become clearer than they were before.

My acceptance package would contain the compatibility corpus, schema mappings, replay manifest, reconciliation results, cutover procedure, and rollback rehearsal. No performance outcome is claimed here. The proposal is testable: a migration should preserve the declared contract under retries, delayed changes, deletions, and interrupted ownership transfer. If it cannot do that, adding an AI consumer only gives the underlying ambiguity another place to spread.