Migration and Seeding Tool

The migration and seeding tool can be used to migrate the EWA database, seed locations and configure the EWA system.

Before use

The tool needs a connection string to the MSSQL database it should migrate/insert data to.

Configuration by version

Version	Configuration File
Below 21.10	`Bliksund.EWA.MigrationTool.exe.config`
21.10 and later	`appsettings.json`

Command Timeout Configuration (v40.2.7+)

Starting from version 40.2.7, you can configure the command timeout for database migration operations in appsettings.json:

{
  "ConnectionStrings": {
    "PEPJDatabaseConnection": "your-connection-string"
  },
  "CommandTimeoutSeconds": 600
}

Property	Type	Default	Description
`CommandTimeoutSeconds`	int	600	The maximum time (in seconds) to wait for a migration command to execute before timing out.

Important notes:

The default value is 600 seconds (10 minutes), which is suitable for most migrations.
If a value lower than the default is configured, the tool will automatically use the default value (600 seconds) and log a warning.
Increase this value if you have large database or complex migrations that may take longer to complete.

Usage

The tool can be invoked via the command line with command line options or it can be run interactively by double-clicking the Bliksund.EWA.MigrationTool.exe file in file explorer.

Command line options

Option	Description
`--help`	Display help text for the tool.
`-m`, `--migrate`	Migrates the database to the newest version
`-s`, `--seed`	Seeds all JSON files from the Data folder into the database
`--seed-files <file> [<file>...]`	Seeds the specified files. Bare filenames are resolved from the `Data/` folder; full paths are used as-is. Example: `--seed-files Location.json /data/Checklists.json`
`--seed-folder <path>`	Seeds all JSON files found in the specified folder. Example: `--seed-folder /data/seed`
`-v`, `--validate`	Validates that the Data folder contains the required JSON files for seeding
`--openid`	Use OpenID Connect for authentication and authorization in the system. Configuration should be set in file "OpenIdConnectConfiguration.json"
`--role-access-mapping`	Update the role to access right mapping that the system should use for authorization. Configuration should be set in file "RoleToAccessMapping.json"
`--pdf-configuration`	Update or inserts the pdf configuration settings. Configuration should be set in file "PdfConfiguration.json"
`--department-id-mapping`	Inserts or updates the Department ID mapping configuration which is used for OpenIdConnect with departments as single claim and rolepicker display values. Configuration should be set in file "DepartmentIdMapping.json"
`--named-location-seed`	Inserts the named location categories that are not in the database yet. It never changes or deletes a category, because categories are edited in Insight. Configuration should be set in file "NamedLocationSeed.json"; the image holds an empty placeholder and each deployment supplies its own file. Runs before `--run-converter`. See the NamedLocationSeed.json section below.
`--run-converter`	Runs a specific converter. The converter name should be provided after this argument.
`--address-lookup [path]`	Loads the address lookup data (`AddressLookup.dacpac`, or its folder; default: the `Data/` folder) when its data version changed. Skipped with a warning when the file is missing or SQL Server has no Full-Text Search. Takes several minutes; only address search is unavailable meanwhile.

Seeding files from custom paths

--seed-files accepts both bare filenames (resolved from the Data/ folder) and full file paths. This is useful for files that are not bundled in the tool's Data/ folder, such as the checklist-related files (Checklists.json, ChecklistPhases.json, ChecklistInstances.json, FormConfigurations.json, FormConfigurationSections.json, FormBranchingConditions.json, DboChecklists.json, DboChecklistResources.json).

Running --seed does not attempt to seed any of these files since they are not present in the Data/ folder.

Examples

To re-run data conversions, or resume a conversion that was interrupted, the --run-converter option can be used. The name of the converter to run should be provided after the option.

.\Bliksund.EWA.MigrationTool.exe --run-converter CreateRecordConfMissionTypeTable

To seed checklist files from a custom location (e.g. files produced by an automated test setup), pass the full paths to --seed-files:

.\Bliksund.EWA.MigrationTool.exe --seed-files C:\data\Checklists.json C:\data\FormConfigurations.json

Bare filenames and full paths can be mixed freely:

.\Bliksund.EWA.MigrationTool.exe --seed-files Location.json C:\data\DboChecklists.json C:\data\DboChecklistResources.json

Interactive use

When using the tool interactively, the following menu will be displayed in a console window after startup:

Menu Item	Description
List	Enter menu to list pending, deployed and all migrations for the current version
Migrate	Migrate the database up to the current version
Seed	Seed data from the JSON files in Data folder to the database
Validate Data	Validates that the Data folder contains the required JSON files for seeding
System Configuration	Opens the system configuration menu
Help	Display help text
Exit	Exits the program

Menu Item	Description
Pending	List pending migrations
Deployed	List deployed migrations
All	List all migrations

Menu Item	Description
Authentication and Authorization	Configure which authentication and authorization method the system should use
Role to Access Rights Mapping	Insert or update the role to access rights mapping configuration that the system should use for authorization
Seed PDF configuration	Insert or update the PDF configuration object that the system should use when generating PDFs
Department ID Mapping	Inserts or updates the Department ID mapping configuration which is used for OpenIdConnect with departments as single claim and rolepicker display values

Configuration files

OpenIdConnectConfiguration.json

The OpenIdConnectConfiguration file consists of two objects with the same internal structure; InsightConfiguration and ClientConfiguration.

The InsightConfiguration object is used by the Insight component, while the ClientConfiguration object is used by the ClientAPI component.

Property	Type	IsRequired	Default value	Description
ClientId	string	Yes		The "client_id" part of the authentication flow
ClientSecret	string	Yes		The "client_secret" part of the authentication flow
Authority	string	Yes		Url to the Authority that should be used when making OpenIdConnect calls
SSNClaimType	string	Yes		Claim type of the claim that should be used for getting the "social security number" for the user. Can be any claim that will uniquely identify a user
NameClaimType	string	Yes		Claim type of the claim that should be used for getting the user's full name
UserIdClaimType	string	Yes		Claim type of the claim that should be used for getting the user's "UserId"
WorkplacesClaimType	string	Yes		Claim type of the claim(s) that should be used for getting the user's list of department level workplaces
OrganizationClaimType	string	Yes		Claim type of the claim(s) that should be used for getting the user's list of organizations. Ignored if `"DepartmentsAsSingleClaim" = true`
ValidAudiences	list<string>	Yes		List of valid audiences
AdditionalScopes	list<string>	No	[]	List of additional scopes to use
AppRoleClaimType	string	No	"roles"	Claim type of the claim(s) that should be used for getting the user's list of roles
ValidIssuers	list<string>	No		List of valid issuers. Uses authority as valid issuer if property is missing or value is null
DepartmentsAsSingleClaim	bool	No	false	Tells the system to treat the value in the WorkplacesClaimType claim as a semi-colon separated list, and to use configured DepartmentIdMapping to build the user's organization hierarchy tree
RolePickerEnabled	bool	No	false	Is only used in InsightConfiguration. Enables a role picker in Insight which requires the user's to pick Department and Role before being able to use the rest of Insight
DepartmentsClaimSeparator	string	No	";"	The separator used to separate department id values from the WorkplacesClaimType claim when DepartmentsAsSingleClaim is true
AllowedRedirectUris	list<string>	No	[]	Is only used in ClientConfiguration. The approve list (whitelist) of redirect URIs that ClientHub accepts as an authentication callback target. Supports `*` as a wildcard. If the property is missing or the list is empty, ClientHub falls back to the built-in default URI only. See Seeding the redirect URI approve list

OpenIdConnectConfiguration.json - Example

{
  "InsightConfiguration": {
    "ClientId": "4a821d50-11a1-483f-8782-e865ab3033a1",
    "ClientSecret": "[REDACTED]",
    "Authority": "https://login.microsoftonline.com/73935988-7e75-46c2-8cc6-d2d91985f67b/v2.0",
    "SSNClaimType": "extn.ssn",
    "NameClaimType": "name",
    "UserIdClaimType": "preferred_username",
    "WorkplacesClaimType": "groups",
    "OrganizationClaimType": "extn.OrganizationIdentification",
    "ValidAudiences": [
      "4a821d50-11a1-483f-8782-e865ab3033a1"
    ],
    "RolePickerEnabled": true,
    "DepartmentsAsSingleClaim": false
  },
  "ClientConfiguration": {
    "ClientId": "4a821d50-11a1-483f-8782-e865ab3033a1",
    "ClientSecret": "[REDACTED]",
    "Authority": "https://login.microsoftonline.com/73935988-7e75-46c2-8cc6-d2d91985f67b/v2.0",
    "SSNClaimType": "extn.ssn",
    "NameClaimType": "name",
    "UserIdClaimType": "preferred_username",
    "WorkplacesClaimType": "groups",
    "OrganizationClaimType": "extn.OrganizationIdentification",
    "ValidAudiences": [
      "4a821d50-11a1-483f-8782-e865ab3033a1"
    ],
    "RolePickerEnabled": true,
    "DepartmentsAsSingleClaim": false,
    "AllowedRedirectUris": [
      "ewa://login-user*",
      "https://myclient.example.com/callback",
      "https://anotherclient.example.com/*"
    ]
  }
}

Seeding the redirect URI approve list

The approve list (whitelist) of redirect URIs that ClientHub accepts as an authentication callback target is seeded into the database as part of the OpenID Connect configuration, using this tool.

How it works

The approve list is the AllowedRedirectUris property of the ClientConfiguration object. ClientHub only reads it from ClientConfiguration; setting it on InsightConfiguration has no effect.
Running the tool with --openid reads Configuration/OpenIdConnectConfiguration.json and writes the whole configuration, approve list included, to the database. There is no separate argument for the approve list.
In the container image, the file can be replaced at runtime through the OpenIdConnectConfiguration environment variable, which takes either raw JSON or base64-encoded JSON (prefixed base64: or b64_). See Container.
ClientHub reads the configuration at startup, so it must be restarted before a reseeded approve list takes effect.

Matching rules

Pattern	Matches
`https://myclient.example.com/callback`	That exact URI, compared case-insensitively
`ewa://login-user*`	Any URI starting with `ewa://login-user`, for example `ewa://login-user?id=42&name=ada`
`https://anotherclient.example.com/*`	Any URI under that host
`*`	Every redirect URI. Do not use outside local development

* may appear anywhere in a pattern and matches any sequence of characters, including /, ? and &. All other characters are matched literally.

If AllowedRedirectUris is missing or empty, ClientHub allows one URI only:

ewa://login-user?id={__userId__}&name={__userName__}&cookie={__cookieValue__}&timeout={__timeout__}

Example - adding allowed redirect URIs

To allow ewa://login-user*, https://myclient.example.com/callback and https://anotherclient.example.com/*, add the property to ClientConfiguration:

{
  "ClientConfiguration": {
    "ClientId": "4a821d50-11a1-483f-8782-e865ab3033a1",
    "ClientSecret": "[REDACTED]",
    "Authority": "https://login.microsoftonline.com/73935988-7e75-46c2-8cc6-d2d91985f67b/v2.0",
    "SSNClaimType": "extn.ssn",
    "NameClaimType": "name",
    "UserIdClaimType": "preferred_username",
    "WorkplacesClaimType": "groups",
    "OrganizationClaimType": "extn.OrganizationIdentification",
    "ValidAudiences": [
      "4a821d50-11a1-483f-8782-e865ab3033a1"
    ],
    "AllowedRedirectUris": [
      "ewa://login-user*",
      "https://myclient.example.com/callback",
      "https://anotherclient.example.com/*"
    ]
  }
}

Then seed it. From the command line:

.\Bliksund.EWA.MigrationTool.exe --openid

Or, in the container, supply the file through the environment variable and pass --openid in ARGUMENTS:

env:
  - name: OpenIdConnectConfiguration
    value: "base64:<base64-encoded-json>"
  - name: ARGUMENTS
    value: "--openid"

The graphical route is described in Configuration of Authentication and Authorization.

RoleToAccessMapping.json

List of objects which tells the system which access rights are given by which roles.

Definition of available access rights can be found in Access Rights document.

Property	Type	IsRequired	Description
Role	string	Yes	Id/value of a role that the user's can have in their token after login
AccessRights	list<string>	Yes	List of access rights that should be given by the Role
DisplayName	string	No	The display name to use for the role in the role picker in Insight if it's enabled

RoleToAccessMapping - Example

[
    {
        "Role": "sys_role_journalregistration",
        "DisplayName": "Journalregistrering",
        "AccessRights": [
            "OrganizationClient",
            "UserJournalView",
            "UserJournalEdit",
            "UserJournalPrint",
            "UserJournalComplete"
        ]
    },
    {
        "Role": "sys_role_patient_complaint_handler",
        "DisplayName": "Klagesakshåndtering",
        "AccessRights": [
            "OrganizationJournalView",
            "OrganizationJournalPrint",
            "OrganizationJournalDelete",
            "BlacklistWhitelist",
            "OrganizationAuditLog",
            "OrganizationMainWithMedicalData"
        ]
    },
]

DepartmentIdMapping.json

List of objects which tells the system which organization id a department belongs to, and what the name of the department is.

This list is used by the access control middleware to construct the user's organization hierarchy when DepartmentsAsSingleClaim is enabled, and to display more user-friendly names for departments in the role picker.

Property	Type	IsRequired	Description
DepartmentId	string	Yes	Id of the department. Usually a RESH-ID in Norway
DepartmentName	string	Yes	Name of the department
OrganizationId	string	Yes	Id of the organization that the department belongs to

DepartmentIdMapping - Example

[
    {
        "DepartmentId": "9ccda3e6-320a-4504-9386-97b2117ec744",
        "DepartmentName": "Cardiology outpatient clinic",
        "OrganizationId": "100000001",
    },
    {
        "DepartmentId": "f45eaef5-1e97-4a15-95c3-6d77a689b9ac",
        "DepartmentName": "Grimstad emergency room",
        "OrganizationId": "100000001",
    }
]

NamedLocationSeed.json

One file per installation, read by two steps: --named-location-seed reads categories, and the NamedLocationsCopy converter reads the other three properties. The image holds an empty placeholder, and each deployment supplies its own file.

Property	Type	IsRequired	Default value	Description
defaultCountry	string	For the copy		ISO 3166-1 alpha-2 country every copied location gets, for example `NO`
categories	array	For the seed	`[]`	The categories the installation starts with, see below
legacyTypeMap	object	For the copy		Old place tag value to category `keyName`, for example `"Somatisk sykehus": "SomaticHospital"`
genericNames	array	For the copy	`[]`	Names that make an old row under a mapped tag generic, with or without an address, for example `Sykehus`

Each category:

Property	Type	IsRequired	Default value	Description
keyName	string	Yes		The English name in PascalCase, for example `SomaticHospital`: ASCII letters and digits, starting with a letter, at most 64 characters. Unique and never changed; the database gives the category its number
names	object	Yes		The name in each language, keyed by culture name. An English name is required. The seed writes each name to the translation service under the language alone (`en-GB` is stored as `en`), in module `Location.Category` with the `keyName` as its key
codes	array	No	`[]`	Codes in other systems, each `{ "system": "Hl7", "code": "HOSP" }`. The systems are `Ambustat`, `Hl7`, `ElsObjectType` and `OsmTag`
isDefault	bool	No	`false`	The installation's Default category. At most one

The seed is insert-only: a category whose keyName is already in the register, deleted or not, is never changed, and a name the translation service already has in that language is never overwritten, because categories and their names are edited in Insight after the first run.

Copying dbo.Locations

--run-converter NamedLocationsCopy copies every row of dbo.Locations into the named location register. Run it together with --named-location-seed, which always runs first: the copy stops if a category it maps to is missing.

Add it to a tenant's arguments only in the release that stops the old Insight page writing dbo.Locations. An edit there after the copy adds an old row to a GUID that is already in the register, the copy skips it, the after-check fails, and so does every later migration Job.

Every LocationGuid becomes one named location, with a revision for each old row. The register gives each revision a new Id: records link a location by GUID and revision, not by the old row id. Generic entries such as "Sykehjem", generic names such as "Sykehus" (with an address or not), district names and consultation boards are copied too, but deactivated, so they stay out of the Insight list, the search and the device sync. The kind of the newest row of a GUID decides. A row with a blank name is sorted as if it were named after its place tag.
Every copied location gets the copy time from the database clock as its last change, so a device that synced before the copy still gets it. Its creation time and the revision dates stay the old ones.
The place tag picks the category through legacyTypeMap. A district name gets OtherPlace, and the copy stops if a district name needs it and the register has none. A consultation board, and a place under a tag that is not in the map, get no category.
Old revision numbers are kept. Only when two rows of one GUID share a number is that GUID numbered again by creation time.
Re-runs are insert-only: only GUIDs not in the register yet are read and copied, so a re-run never overwrites an edit made in Insight, and after the first run a deployment only runs the checks. A GUID that fails is rolled back and logged, and the others go on.
Text is trimmed, as Insight trims it on save. A blank old name takes the place tag as its name, or Location <old row id> if the tag is blank too, and goes on the review list.
Every revision gets the seed's defaultCountry, except a row under the county tag Sverige or Danmark, which gets SE or DK.
The log shows a report for the rows it copies (counts, tag values and old row ids, never names or addresses) and ends with two after-checks: every old GUID has at least as many revisions as old rows, and every named location has a current revision. A failed GUID or a failed check fails the migration Job.

NamedLocationSeed - Example

{
    "defaultCountry": "NO",
    "categories": [
        {
            "keyName": "SomaticHospital",
            "names": { "en-GB": "Somatic hospital", "nb-NO": "Somatisk sykehus" },
            "codes": [ { "system": "Hl7", "code": "HOSP" }, { "system": "OsmTag", "code": "amenity=hospital" } ]
        }
    ],
    "legacyTypeMap": { "Somatisk sykehus": "SomaticHospital" },
    "genericNames": [ "Sykehus" ]
}

Migration and Seeding Tool

Before use

Configuration by version

Command Timeout Configuration (v40.2.7+)

Usage

Command line options

Seeding files from custom paths

Examples

Interactive use

List menu

System Configuration menu

Configuration files

OpenIdConnectConfiguration.json

OpenIdConnectConfiguration.json - Example

Seeding the redirect URI approve list

How it works

Matching rules

Example - adding allowed redirect URIs

RoleToAccessMapping.json

RoleToAccessMapping - Example

DepartmentIdMapping.json

DepartmentIdMapping - Example

NamedLocationSeed.json

Copying dbo.Locations

NamedLocationSeed - Example

Before use​

Configuration by version​

Command Timeout Configuration (v40.2.7+)​

Usage​

Command line options​

Seeding files from custom paths​

Examples​

Interactive use​

List menu​

System Configuration menu​

Configuration files​

OpenIdConnectConfiguration.json​

OpenIdConnectConfiguration.json - Example​

Seeding the redirect URI approve list​

How it works​

Matching rules​

Example - adding allowed redirect URIs​

RoleToAccessMapping.json​

RoleToAccessMapping - Example​

DepartmentIdMapping.json​

DepartmentIdMapping - Example​

NamedLocationSeed.json​

Copying dbo.Locations​

NamedLocationSeed - Example​

Before use

Configuration by version

Command Timeout Configuration (v40.2.7+)

Usage

Command line options

Seeding files from custom paths

Examples

Interactive use

List menu

System Configuration menu

Configuration files

OpenIdConnectConfiguration.json

OpenIdConnectConfiguration.json - Example

Seeding the redirect URI approve list

How it works

Matching rules

Example - adding allowed redirect URIs

RoleToAccessMapping.json

RoleToAccessMapping - Example

DepartmentIdMapping.json

DepartmentIdMapping - Example

NamedLocationSeed.json

Copying dbo.Locations

NamedLocationSeed - Example