Legacy Database Views and Sync

VIDEO TRANSCRIPT | Recorded: 2022-01-27 | Verify against current system state

Abstract¶

Technical architecture explanation of how legacy AANP applications maintain compatibility with Aptify. When migrating to Aptify, existing tables were converted to views that query Aptify data directly. A sync process (SQL job) runs every 5 minutes to maintain stub records in legacy tables for foreign key requirements. Explains the naming convention (__legacy suffix) and the sync script logic.

Key Procedures¶

• Understand view architecture: tables renamed to __legacy, views created with original names
• Views query Aptify data directly (VW_ prefix)
• Sync process creates stub records in contact_data__legacy
• Run sync job in production automatically every 5 minutes
• Manually run sync script in QA/UAT when needed
• Troubleshoot sync failures using SQL job history

Notable Statements¶

• 0:00:19 "We didn't move everything into Aptify. We moved our basic contact information and our login information, orders, that type of thing"
• 0:00:55 "That was a little overwhelming. So what we came up with was a way to keep our current applications up and running by changing tables that existed in A&PDB into views"
• 0:01:23 "It doesn't really care if it's a view or a table... it just needs to know that there's an object called that that it can read into"
• 0:02:26 "The only spot that really mattered to us was conference reg"
• 0:03:06 "The pattern we followed was to rename specific tables as underscore underscore legacy"
• 0:04:26 "They couldn't be added into this table contact data because this is now a view"
• 0:05:14 "It's a script that runs every five minutes. It is a SQL job. It's called Aptify Data Sync" [ACTIONABLE]
• 0:05:34 "In QA and in UAT, we don't have access to SQL jobs. So you have to run this script manually"
• 0:06:48 "In Aptify, we reseeded the identity so that all Aptify companies will start at five million or more"
• 0:09:17 "Contact data contained objects that were both persons and companies. But then person data specifically was in the individual table"
• 0:11:43 "This is all in a transaction. So even if it fails... it's going to roll back"

Systems & Configurations¶

Systems Mentioned¶

• Aptify (source of truth for person/company data)
• SQL Server (jobs, views, sync process)
• A&PDB (legacy database)
• Entity Framework (ORM compatibility)
• Hibernate (ORM compatibility)
• Conference Registration (required update for writes)

Specific Configurations¶

Key Tables¶

Credentials/Access Mentioned¶

• SQL Server access for job management
• Remote access for QA/UAT script execution

Vendor Contacts Mentioned¶

None mentioned in this recording.

Errors & Troubleshooting¶

• Issue: Sync job fails repeatedly
• Cause: Database down or timeout
• Resolution: Check SQL job history for failures, run script manually to debug

• Timestamp: 0:10:38

• Issue: Duplicate email addresses cause insert failure

• Cause: Multiple records with same email
• Resolution: Script includes handling to prevent failure
• Timestamp: 0:08:28

Transcript Gaps & Quality Notes¶

• Technical architecture overview (13 minutes)
• Critical for understanding legacy system maintenance
• Conference registration update was only major code change needed
• Transaction-based sync prevents data corruption
• Email alerts configured for sync failures