The Challenge

The organization operated a regulatory submissions platform that had grown over several years to support eCTD publishing, document management, review, and planning workflows across multiple regions. The platform’s core data layer relied on purpose-built database tables — separate schemas for submissions, products, substances, dossier mappings, and application metadata. Each table had its own stored procedures, entity mappings, and query logic.

This architecture worked during the platform’s initial growth phase. But as the product expanded to support new object types — grouped submissions, compliance domains, submission plans, and AI-powered features — the per-table approach created compounding technical constraints.

The Approach

Rather than continuing to extend the legacy table structure, the engineering team implemented a platform data layer modeled on a wide-column architecture. A single universal table provides storage for all object types using a configurable field mapping system. Object type definitions, field registries, and column mappings are maintained as configuration data, allowing new object types to be introduced without database schema changes.

The migration followed a methodical path: each of the 15 legacy data access paths was individually analyzed, mapped to the new platform layer, and migrated with full backward compatibility. The legacy stored procedures were replaced by a centralized data reader and writer that handle all object types through a consistent API. Field mapping logic — translating between application-level field names and physical database columns — was centralized in a single configuration layer rather than distributed across stored procedures.

Critical to the migration’s success was the decision to execute it without a hard cutover. The platform reader and writer coexisted with legacy data paths during the transition period, allowing individual modules to migrate independently while the overall system remained operational.

What Was Delivered

The migration also resolved several latent data integrity issues. Column mapping collisions — where two logical fields mapped to the same physical column — were identified and corrected. Key casing inconsistencies between save and read operations were standardized. SELECT queries that previously used wildcard column selection were replaced with explicit column lists, eliminating unexpected behavior when table schemas were extended.

Key Outcomes

Why It Worked

• Incremental migration path: Migrating one data access path at a time allowed continuous validation without system-wide risk. Each migration was independently testable and reversible
• Centralized field mapping: Moving field-to-column translation from distributed stored procedures to a single configuration layer eliminated an entire category of mapping inconsistency bugs
• No-downtime coexistence: Legacy and platform data paths operated simultaneously during transition, ensuring that production operations continued without interruption
• Explicit column selection: Replacing SELECT * patterns with explicit column lists prevented unexpected behavior as the universal table was extended with new columns for new object types
• Caching strategy: Tenant-scoped field mapping caches ensured that the additional abstraction layer did not introduce query performance degradation

Looking Ahead

The platform data layer now serves as the foundation for all new feature development. Compliance domain management, AI-powered document classification results, semantic search indexes, and submission plan structures all store and retrieve data through the same platform reader and writer infrastructure. The architecture supports the organization’s roadmap without requiring further database schema changes — new capabilities are delivered as object type configurations rather than engineering projects.

For regulatory operations teams, the migration was largely invisible — existing workflows continued without interruption. The impact became apparent in the pace of new feature delivery: capabilities that previously required multi-sprint database engineering work now ship as part of standard feature development cycles.