CAS API Subscription Error

Slate Instance	TUP
Requestor/Reporter	Helen Williams
TTS Staff	Helen Williams
Date	10/24/2023
Status	Complete
Bug Description	Change to TUSMGP CNAME/subdomain changed the CAS/Liaison Service Account username and broke the API subscriptions.

Issue:

TUSMGP requested a change to the CNAME/sub-domain for their database (from phpd.admissions.tufts.edu to tusmgp.admissions.tufts.edu) When this change went into effect on 8/25/2023, the CAS/Liaison service account username was also changed in their instance of Slate (from liaison_service@phpd.admissions.tufts.edu to liaison_service@tusmgp.admissions.tufts.edu). This caused the existing API subscriptions to fail and data to stop importing between the CAS and Slate.

After running the solution below, we also discovered that we had four applicants for the DPT-Boston program who did not have applications in Slate. All 4 had started their application 6/20-21, which meant it was likely they had been a problem before this specific issue. Most likely when the subscription was setup at the beginning of the cycle, those in progress files were missed by the process to replay the in progress subscription.

Solution:

Existing API subscriptions were deleted, the subscription configurations updated with the correct email address for the sftpUser, the subscriptions re-created, and then replays made for each subscription to cover time between when the files stopped sending (roughly 8/25/2023 at 4:45 PM EST) to when each subscription was restarted.

Re-created subscriptions for In Progress (program selected) applications first, immediately ran a replay for each. This was necessary because if a record came over in a subsequent subscription data file before it came through the In Progress file, an application record would not exist in Slate and it would not update properly. The In Progress data subscription is what creates the application for the record.

DPT-SEA

{
    "subscription": {
        "id": 8014,
        "notificationEmailAddress": "tts-slate-team@elist.tufts.edu",
        "expirationDate": "2025-08-28",
        "createdBy": "TuftsUni",
        "updatedBy": "TuftsUni",
        "userLevel": "institution",
        "disabled": false,
        "createdDate": "2023-08-29 17:16:24",
        "updatedDate": "2023-08-29 17:16:24",
        "subscriptionDetails": [
            {
                "id": 48215,
                "event": "application.program.selected",
                "responseType": "fullResponse",
                "responseLevel": "program",
                "responseOptions": "expand=all,includeNulls=true,contentType=text/csv,columnSeparator=COMMA,csvHeaderTruncationSize=4,csvShortName=question",
                "destinationType": "SFTP",
                "createdBy": "TuftsUni",
                "updatedBy": "TuftsUni",
                "apiVersion": "v2",
                "disabled": false,
                "createdDate": "2023-08-29 17:16:24",
                "updatedDate": "2023-08-29 17:16:24",
                "sftpDestination": {
                    "id": 12073,
                    "sftpHost": "ft.technolutions.net",
                    "sftpPort": 22,
                    "sftpBaseDirectory": "/incoming/liaison/PTCAS/data/",
                    "sftpPathTemplate": "PTCAS-app-started_<instanceId>_<organizationId>_<programId>_<applicationId>_<casApplicantId>.csv",
                    "sftpUser": "liaison_service@tusmgp.admissions.tufts.edu",
                    "sftpPassword": "2717fef375e24a1ab89a376a86cbe358",
                    "createdBy": "TuftsUni",
                    "updatedBy": "TuftsUni",
                    "createdDate": "2023-08-29 17:16:24",
                    "updatedDate": "2023-08-29 17:16:24"
                }
            }
        ]
    }
}

{
    "replay": {
        "id": 8232,
        "status": "PENDING",
        "fromDate": "2023-08-25 17:45:00",
        "toDate": "2023-08-29 17:00:00",
        "createdDate": "2023-08-29 17:17:11",
        "updatedDate": "2023-08-29 17:17:11",
        "subscription": {
            "subscriptionId": 8014
        }
    }
}

{
    "subscription": {
        "id": 8017,
        "notificationEmailAddress": "tts-slate-team@elists.tufts.edu",
        "expirationDate": "2025-08-29",
        "createdBy": "TuftsUni",
        "updatedBy": "TuftsUni",
        "userLevel": "institution",
        "disabled": false,
        "createdDate": "2023-08-30 10:08:43",
        "updatedDate": "2023-08-30 10:08:43",
        "subscriptionDetails": [
            {
                "id": 48218,
                "event": "application.program.received",
                "responseType": "fullResponse",
                "responseLevel": "program",
                "responseOptions": "expand=all,includeNulls=true,contentType=text/csv,columnSeparator=COMMA,csvHeaderTruncationSize=4,csvShortName=question",
                "destinationType": "SFTP",
                "createdBy": "TuftsUni",
                "updatedBy": "TuftsUni",
                "apiVersion": "v2",
                "disabled": false,
                "createdDate": "2023-08-30 10:08:43",
                "updatedDate": "2023-08-30 10:08:43",
                "sftpDestination": {
                    "id": 12076,
                    "sftpHost": "ft.technolutions.net",
                    "sftpPort": 22,
                    "sftpBaseDirectory": "/incoming/liaison/PTCAS/data",
                    "sftpPathTemplate": "PTCAS-app-s-<instanceId>_<organizationId>_<programId>_<applicationId>_<casApplicantId>.csv",
                    "sftpUser": "liaison_service@tusmgp.admissions.tufts.edu",
                    "sftpPassword": "2717fef375e24a1ab89a376a86cbe358",
                    "createdBy": "TuftsUni",
                    "updatedBy": "TuftsUni",
                    "createdDate": "2023-08-30 10:08:43",
                    "updatedDate": "2023-08-30 10:08:43"
                }
            },
            {
                "id": 48219,
                "event": "application.program.completed",
                "responseType": "fullResponse",
                "responseLevel": "program",
                "responseOptions": "expand=all,includeNulls=true,contentType=text/csv,columnSeparator=COMMA,csvHeaderTruncationSize=4,csvShortName=question",
                "destinationType": "SFTP",
                "createdBy": "TuftsUni",
                "updatedBy": "TuftsUni",
                "apiVersion": "v2",
                "disabled": false,
                "createdDate": "2023-08-30 10:08:43",
                "updatedDate": "2023-08-30 10:08:43",
                "sftpDestination": {
                    "id": 12077,
                    "sftpHost": "ft.technolutions.net",
                    "sftpPort": 22,
                    "sftpBaseDirectory": "/incoming/liaison/PTCAS/data",
                    "sftpPathTemplate": "PTCAS-app-s-<instanceId>_<organizationId>_<programId>_<applicationId>_<casApplicantId>.csv",
                    "sftpUser": "liaison_service@tusmgp.admissions.tufts.edu",
                    "sftpPassword": "2717fef375e24a1ab89a376a86cbe358",
                    "createdBy": "TuftsUni",
                    "updatedBy": "TuftsUni",
                    "createdDate": "2023-08-30 10:08:43",
                    "updatedDate": "2023-08-30 10:08:43"
                }
            },
            {
                "id": 48220,
                "event": "application.program.verified",
                "responseType": "fullResponse",
                "responseLevel": "program",
                "responseOptions": "expand=all,includeNulls=true,contentType=text/csv,columnSeparator=COMMA,csvHeaderTruncationSize=4,csvShortName=question",
                "destinationType": "SFTP",
                "createdBy": "TuftsUni",
                "updatedBy": "TuftsUni",
                "apiVersion": "v2",
                "disabled": false,
                "createdDate": "2023-08-30 10:08:43",
                "updatedDate": "2023-08-30 10:08:43",
                "sftpDestination": {
                    "id": 12078,
                    "sftpHost": "ft.technolutions.net",
                    "sftpPort": 22,
                    "sftpBaseDirectory": "/incoming/liaison/PTCAS/data",
                    "sftpPathTemplate": "PTCAS-app-s-<instanceId>_<organizationId>_<programId>_<applicationId>_<casApplicantId>.csv",
                    "sftpUser": "liaison_service@tusmgp.admissions.tufts.edu",
                    "sftpPassword": "2717fef375e24a1ab89a376a86cbe358",
                    "createdBy": "TuftsUni",
                    "updatedBy": "TuftsUni",
                    "createdDate": "2023-08-30 10:08:43",
                    "updatedDate": "2023-08-30 10:08:43"
                }
            }
        ]
    }
}

DPT-PHX

DPT-BOS

Applications in SOPHAS but not in Slate:

2716721476	Megan	Hasenfratz	NOT IN SLATE	Complete	Submitted 8/28 and Completed 8/28 but program added 6/21 -- possibly no IP app?
5636806312	Gianna	Buckley	NOT IN SLATE	Verified
7681966544	Paige	Luong	NOT IN SLATE	Verified
1494492140	Shannon	McKean	NOT IN SLATE	Verified
5015004038	Katherine	Snow	NOT IN SLATE	Verified

Applications in Slate with incorrect CAS Status:

Person CAS/Liaison Person ID	Application CAS/Liaison Application ID	SOPHAS App Status	Application CAS/Liaison Application Status	Application Status	Created Date	Application Start Term/Year
8811325641	8811325641-349019	Verified	Complete	Awaiting Materials	07/05/2023	Summer 2024
6165511055	6165511055-349019	Received	In Progress	Awaiting Submission	06/16/2023	Summer 2024
1632638824	1632638824-349019	Verified	In Progress	Awaiting Submission	06/25/2023	Summer 2024
3467889319	3467889319-349019	Verified	In Progress	Awaiting Submission	06/17/2023	Summer 2024
3305359106	3305359106-349019	Verified	In Progress	Awaiting Submission	07/12/2023	Summer 2024
9251391771	9251391771-349019	Verified	In Progress	Awaiting Submission	07/27/2023	Summer 2024
2242019818	2242019818-349019	Complete	In Progress	Awaiting Submission	06/28/2023	Summer 2024

8811325641	Kevin	O'Neil	Complete	Verified
2242019818	Riley	Hayden	In Progress	Complete
6165511055	Angela	Yang	In Progress	Received
3305359106	Andrea	Jaeger	In Progress	Verified
9251391771	Evan	Kalminov	In Progress	Verified
3467889319	Rachel	Zhang	In Progress	Verified
1632638824	Amanda	Zygmont	In Progress	Verified