Skip to main content

ADR-006: Schema Migration — Preserve Initially, Modernize Incrementally

Status: Accepted Date: 2026-03-16

Context​

The legacy database has ~170 tables with pscat_/rmcat_/pxcat_ prefixes, accumulated over ~30 years of PowerBuilder development. Many tables lack foreign keys, audit columns, and modern naming conventions. The team considered whether to rewrite the schema from scratch or preserve it.

Options considered:

  • Greenfield schema: Design a completely new schema based on domain models. Requires a data migration layer.
  • Preserve and extend: Keep the existing schema, add missing features (FKs, audit columns, soft deletes). Rename later.
  • Hybrid: Preserve core tables, redesign peripheral tables.

Decision​

Preserve the existing schema initially. This enables side-by-side validation against the desktop app (both reading/writing the same database). Add proper foreign keys, audit columns (CreatedAt, UpdatedAt, CreatedBy, UpdatedBy), and soft deletes. Rename tables and restructure in a later phase after the coexistence period.

Key approach:

  • EF Core database-first scaffolding maps the existing schema to C# entities
  • Entity classes use modern C# naming (PascalCase properties) with column mappings to legacy names
  • New columns are added via EF Core migrations (additive, never breaking)
  • Views provide clean interfaces over legacy table structures where needed

Consequences​

Positive:

  • Immediate scaffolding: EF Core database-first works on day one — no manual entity creation
  • Side-by-side validation: Desktop app and SaaS app can read/write the same database, enabling calculation comparison
  • Lower risk: No big-bang schema migration. Changes are incremental and reversible.
  • Coexistence support: Desktop app continues to work against the same schema during transition

Negative:

  • Ugly entity names initially: C# entities map to pscat_loan, rmcat_trade, etc. until renaming phase
  • Missing relationships: Many FK relationships are enforced in PB code, not the database — must be discovered and added carefully
  • Deferred modernization: The "rename later" phase adds future work and risk
  • Schema constraints from coexistence: Cannot remove or rename columns that the desktop app depends on until coexistence ends