Migrating Aiga records
Background[edit | edit source]
This is a placeholder to store service-specific info on migrating contact data from Aiga to Kotahi.
Principles[edit | edit source]
As at January 2025, the trend in Kotahi service configuration is to define a small number of custom activities (eg. requisition, assessment/goal plan) and leave a generic narrative case note to catch almost everything else.
This narrative case note is broadly equivalent to a 'Contact' in Aiga; the biggest functional difference being there are three data points for a contact in Kotahi (type, method, location) and only one in Aiga.
In terms of migration principles, we are electing to Keep It Simple and use only the narrative case note as a target for migrated Aiga data. This will inherently require a loose mapping from Aiga contact types, to the three elements used in Kotahi.
In terms of scope, we are migrating only Aiga contacts for migrated clients. We have only been pulling across clients which are current at the time of cutting over the service/contract to Kotahi.[1] There is therefore a known corpus of clients and contact data which will never be migrated to Kotahi, but which will however remain available within either a moribund Aiga app or a SQL backup in the event the app is decommissioned.
We will also migrate attachments, or documents, for in-scope clients and this will be clarified further below.
Approach[edit | edit source]
Service Mapping[edit | edit source]
For each service, Kotahi make available a template specifying the activities and fields available to migrate into.
As noted above, we generally want to only migrate into a narrative case note, unless there is a really good reason to do otherwise. The relevant sheet in this template will specify each field and the datatype it must conform to. This should be used to plan the approach in SQL to extract the necessary data.
Sheets that are also relevant will, at minimum, be:
- Notes. Metadata and actual narrative content around contacts recorded in Aiga.
- Users. Basic metadata about users who have entered historic notes or uploaded documents. Current staff members will have already been switched over to Kotahi, so this is mostly useful for creating placeholder accounts for staff who have left so they can be identified as authors in Kotahi.
- Attachments. Specifies filename relating to each migrated client, as well as the Aiga Client ID and a place for an alternative 'Display name' and any comments.[2]
SQL definition[edit | edit source]
Example SQL definitions for the TTA service are shown below. Comments are included for clarification.
Notes[edit | edit source]
select
null as ID, --kotahi client id
n.ClientID as ClientId, --internal AIGA client id
NoteDate as ActivityStart,
n.DateCreated as EnteredDate,
n.DateModified as UpdatedDate,
null as ClientPresent, --not accessible as a field from AIGA
0 as IsConfidential, --not accessible as a field from AIGA
null as DurationAsMinutes, --not accessible as a field from AIGA
--s.StaffMemberName as CompletedById,
n.UserID as CompletedById,
null as Notes,
--32 as ActivityId,
ContactTypeID as ActivityId, --internal AIGA contact type id
case
when n.ContactTypeID in (2,26,38) then 'Phone Call'
when n.ContactTypeID in (3,4,19,27,37,40,41,42,68,77,97) then 'Face to Face'
when n.ContactTypeID in (31) then 'Email'
else NULL
end as ContactMethod, --mapping of internal AIGA contact types to ones we have set up and available in Kotahi
case
when n.ContactTypeID in (5,68,82,99) then 'Administration'
when n.ContactTypeID in (13) then 'Advocacy'
when n.ContactTypeID in (15) then 'Assessment'
when n.ContactTypeID in (18) then 'Did Not Attend'
when n.ContactTypeID in (21) then 'Internally Referred'
when n.ContactTypeID in (24,29) then 'Externally Referral'
when n.ContactTypeID in (36,72,86,89) then 'Case review'
when n.ContactTypeID in (40) then 'Workshop'
when n.ContactTypeID in (41) then 'Family conference'
when n.ContactTypeID in (70) then 'Cold call visits'
else NULL
end as ContactType, --mapping of internal AIGA contact types to ones we have set up and available in Kotahi
null as FileUpload, --not accessible as a field from AIGA
Comments as Notes_,
null as TupaiaAssessmentNotifyUser, --not accessible as a field from AIGA
null as ContactDuration, --not accessible as a field from AIGA
case
when n.ContactTypeID in (4) then 'Home / Whanau Visit'
when n.ContactTypeID in (42) then 'Social Agency'
else null
end as ContactLocation --mapping of internal AIGA contact types to ones we have set up and available in Kotahi
from vClientNotesDocuments n
join
vEpisodeContracts ec
on n.EpisodeContractID = ec.EpisodeContractID
join
vClients c
on n.ClientID = c.ClientID
join
vStaffMember s
on n.UserID = s.StaffMemberID
where
ec.ContractID in (
68 --TTA contract
)
and
ec.EpisodeContractEndDate is null --active TTA clients only
and
n.DocumentPath is null --not an attachment
Please note and pay attention here to the loose mapping that is done in the SQL to conform Aiga contact types to Kotahi activity fields.
Users[edit | edit source]
select
distinct
s.UserID,
s.EmailAddress as Email,
s.Firstname,
s.Surname as LastName,
case
when s.enabled = 1 then 'TRUE' --yes these are strings, SQL server doesn't have an actual boolean type 🤣
else 'FALSE'
end as Active
from vClientNotesDocuments n
join
vEpisodeContracts ec
on n.EpisodeContractID = ec.EpisodeContractID
join
vClients c
on n.ClientID = c.ClientID
join
vSec_Users s
on n.UserID = s.UserID
where
ec.ContractID in (
68 --TTA contract
)
and
ec.EpisodeContractEndDate is null --active TTA clients only
and
n.DocumentPath is null --not an attachment
Note that table vSec_Users should be used rather than the vStaffMember view because the view does not offer what we need here.
Attachments[edit | edit source]
select
right(DocumentPath,len(DocumentPath)-12) as FileName,
null as DisplayName,
DocumentTypeID as ActivityId, --internal AIGA document type id
n.ClientID as ClientId, --internal AIGA client id
Comments as Notes
from vClientNotesDocuments n
join
vEpisodeContracts ec
on n.EpisodeContractID = ec.EpisodeContractID
join
vClients c
on n.ClientID = c.ClientID
join
vStaffMember s
on n.UserID = s.StaffMemberID
where
ec.ContractID in (
68 --TTA contract
)
and
ec.EpisodeContractEndDate is null --active TTA clients only
and
n.DocumentPath is not null --is an attachment
Note that the list of attachments this SQL provides should be consistent with the actual attachments corpus provided to Kotahi - ie, file names should match so they can be allocated to the right client and service.
Export[edit | edit source]
Result sets from above SQL should be saved as CSV and pushed to a subfolder in the Kotahi Teams environment.[3]
Since all of this work has to be done on the actual Aiga VM, this is not necessarily as easy as it sounds. Firstly, SQL runs should ideally be done outside of business hours so as not to compromise Aiga app performance. Second, just using SSMS and doing something basic like exporting to CSV is usually excruciating. As a final gripe, we did draft some logic to parse out a user-friendly display name for each file but it turns out that SQL server doesn't even respect regex so that's just... wow.
Attachments[edit | edit source]
The counterpart to pulling the attachments metadata, is to actually pull and zip the documents themselves. These need to be saved up in the same subfolder as the CSV sets, so that they can be moved to Kotahi Azure storage and linked to our client records.
This step has a few parts:
Create a CSV of file names[edit | edit source]
A file containing the required file names.
This can be obtained by running SQL (from § Attachments). Obviously, only the filename part is required. However please also note that the CSV file used for this part MUST include a header row.<ref>Using the provided example code, the header should be FilePath. PowerShell will give you an error if it doesn't have one, but of course the provided error would leave you with absolutely no idea that this is in fact the problem.
Powershell file[edit | edit source]
Containing logic to find files and copy them to target location. Example code:
# Define the source and destination directories
$sourceDirectory = "[source dir containing documents]"
$destinationDirectory = "[target subfolder]"
# Import the CSV file containing the file paths
$filePaths = Import-Csv -Path "[full path to CSV file]"
# Loop through each file path and copy the file
foreach ($file in $filePaths) {
$sourceFile = Join-Path -Path $sourceDirectory -ChildPath $file.FilePath
$destinationFile = Join-Path -Path $destinationDirectory -ChildPath (Split-Path -Path $file.FilePath -Leaf)
# Copy the file
Copy-Item -Path $sourceFile -Destination $destinationFile -Force
}
Batch file[edit | edit source]
Batch file is required to execute a powershell script, for some god-forsaken reason. Example code:
@echo off
PowerShell -ExecutionPolicy Bypass -File "[Full path to PowerShell file]"
pause
The -ExecutionPolicy Bypass flag apparently allows the script to run without being blocked by the PowerShell execution policy, whatever that means. If only Windows had a grown-up permissions system, eh.
References[edit | edit source]
- ↑ Please refer to Cutover dates.
- ↑ Documents in Aiga are often accompanied by a comment, so this is a useful feature particularly since the file names themselves are generally complete nonsense.
- ↑ One subfolder per team/service.