Modernization of a decades-old child support legacy system
A modern data-management system for multi-user collaboration
The child support services department of a state government in the United States has a legacy system that is over two decades old. The system is built on traditional technologies and databases. It provides child support services such as monetary support from noncustodial parent, spousal support for divorced couples, alimony payments, meeting and payment schedules and much more. The primary entities/parties in this system include the child (called the dependent party), custodial party, non-custodial party, the case files, relatives, attorneys and department staff. It is a highly complex system with multiple entity relationships, records of case details that runs into hundreds of millions, payment receipts, payments scheduling and more. The legacy database uses flat files for storage without master and transaction table structures that can ensure consistency and normalization.
The company wants to modernize the system with relational database at the backend and modern architecture to improve services to its citizens. They use Salesforce as their frontend and want to decouple it from the backend in order to future-proof the system. To achieve this and to deliver better integrated services to its citizens and other stakeholders, they would like to build an API layer in front of its backend system. Considering the sensitive and mission-critical nature of the data and services, the company wants to maintain and run the legacy and modernized system in parallel after migration. They want them to run parallel for a period of two years before completely moving to the new system.
The Vatsa Solution: A modern data-management system for multi-user collaboration
Vatsa created SQL Server Integration Services (SSIS) packages to pull data from legacy database to the new relational database. The solution that was built consists of data migration as a one-time activity as well as packages for incremental migrations to run the two backend databases in parallel. The incremental migrations also keep the two systems consistent and updated with validations and regular fixes. Temporary SQL Server tables were created as intermediate storage and the data was transformed into normalized relational entities on a third SQL Server. Vatsa developed APIs for runtime updates and CRUD operations on the backend database. The system used JSON format for data exchange between the frontend and the API layer. Automation testing was developed to test the APIs throughout the project as well as after the migration. They ensured data accuracy and consistency during the runtime phase. Vatsa performed load testing on the APIs to provide detailed reports and identify performance optimization opportunities and apply indexing tactics.
Vatsa used behaviour-driven development (BDD) methodology to identify the procedures to test and optimize. The project required months of planning to devise the right target schemas and creating test packages and stored procedures. Being a decades-old data backend system that went through multiple hands for data entry and management, a large part of the master data such as city names had changed or stored in a non-standard way (e.g. as code names, as short names, full names, etc.). This made creating and iterating on multiple test migration packages a crucial activity. Errors in each entity migration were recorded in a separate error table associated with each entity to manage unmatched and erroneous fields. In addition, database views were used for comparison of missing mandatory data, duplicate data, bad records, etc.
The architecture and the solution handled further complexity due to the fact that the new database that was supposed to be Microsoft SQL Server was later planned to be replaced with PostgreSQL in the software’s future roadmap. Vatsa developed migration packages and a third API implementation for PostgreSQL in addition to legacy data storage and MS MSQL Server database. The data exchange format developed adhered to the National Information Exchange Model (NIEM), an XML-based information exchange framework in the United States to enhance collaborative partnership of agencies and organizations across the government and with private industry.
As of this writing, Vatsa has maintained the system for a period of one year post the database migration. We continue to ensure consistency and simultaneous updates between all the three backend data sources.
Results:
Vatsa’s extensive expertise in data management helped the company execute a complex migration of hundreds of millions of records. Vatsa continues to help the company with data migration as well as simultaneous data updates across legacy and modernized backend systems while ensuring data accuracy and consistency. Specific benefits to the department, the end users and other stakeholders include:
- A modernized department that is ready to seamlessly and efficiently integrate additional complexity as the American societal and political environment further evolves.
- The system now supports new-age and more complex workflows with more automation and easy interface with front-end and other peer systems due to a modern and scalable API interface.
- A hugely efficient, modernized and robust backend system that now handles huge volumes of data.
- Minimal error rate and zero loss of data during migration as well during runtime CRUD operations due to the use of error tables that ensured complete audit trail and data accuracy.
Technology and Tools:
Android (java), iOS (Objective C), MVC, BLE, OBD, ELM 327, SQLite, REST API, GraphQL, Sentry, Google Analytics, XCTest, JUnit
Learn how you can achieve similar results for your business.