Should be handled similarly to Concept constraints.

API

Overall, these endpoints will start with api/admin/{resource}.

Concepts

• Browse tree.
• Get by Id.
• Put by Id.
• Post.
• Delete by Id.

Concepts -- Browse tree

Need to implement a server passed, admin flag in all stored procedures that depend on sp_FilterConceptsByContraint to allow reuse of existing Concept treetop endpoints.

• sp_GetChildConceptsByParentId
• sp_GetConceptById
• sp_GetConceptHintsBySearchTerms
• sp_GetConceptsByIds
• sp_GetConceptsBySearchTerms
• sp_GetConceptsByUIds
• sp_GetParentConceptsByChildIds
• sp_GetRootConcepts
• sp_GetRootsPanelFilters

Problem

As of 3.1.1, an API service restart is required to respect any network.Endpoint changes made in the database. For performance, this data is maintained in a thread-safe in-process cache.

Proposal

Add functionality to administrative surface area that impacts the database and cache directly, allowing configurations of network endpoints to added/removed/modified in the running service.

Solution Components

API

• api/admin/network
• GET api/admin/network/endpoint --> get all endpoints.
• DELETE api/admin/network/endpoint/{id} --> delete an endpoint.
• PUT api/admin/network/endpoint/{id} --> update endpoint, receive new definition.
• POST api/admin/network/endpoint --> create new endpoint, receive complete new definition.
• POST api/admin/network/identity --> update the nodes network identity.

UI

• Add Network Admin subpane to Admin Panel
• Allow color, name, abbreviation, description editing
• Allow add/edit/remove of network endpoints

Features in set

The following features are part of an effort to give greater control and composability to the way Leaf federates with other instances. These features are orthogonal:

• Gateway runtime.
• Interrogator/Responder endpoint roles.
• Quarantine functionality.

These features will be part of the 3.1 release.

Gateway

In some configurations, it would be desirable to allow an instance of Leaf to exist in a way such that it does not actually own any clinical data. In this case, a coordinating center for example, may need full network access to other member nodes without appearing as a data providing node itself.

We propose

• Adding an appsettings.json Runtime configuration flag that would cause the API to run in gateway mode, removing the cohort counting and dataset enrichment endpoints, api/cohort....
• Adding a Runtime prop to the models/NetworkIdentity type.
• Adding a field to the api/network/respondents API endpoint.
• Updating CohortController to derive from API.Controllers.Base.MaybeController and passing in the RuntimeMode options.

Interrogator/Responder

At the moment, federated data querying in Leaf is reciprocal. We propose to split this bi-directional flow up into opposing uni-directional flows. This would allow an instance of Leaf to separately configure which endpoints it would like to query (respondent) and which endpoints it would like to answer (interrogator). Semantically, a node's API will respond to its interrogators, a node's users will receiver answers from its responders.

We propose:

• Adding two columns to the network.Endpoint table, IsInterrogator and IsResponder.
• Update network.sp_GetEndpoints to return new flags.
• Creating facades over the INetworkEndpointCache, called INetworkResponderCacheReader and INetworkInterrogatorCacheReader. These implementations would ensure that only interrogators/responders are return from queries.
• API.Jwt.JwtKeyResolver should be changed to receive an INetworkInterrogatorCacheReader. This will cause authentication to fail for users coming from nodes not configured as responders.

Issues:

• For the time being, until the network configuration functionality is added to the admin panel, Leaf will need to be restarted on add/remove/change of network.Endpoints. Eventually, the admin panel will update the cache en route to updating the database such that no restart will be needed if configuration is done through the admin panel.

Quarantine

Some nodes will, inevitably, wish to restrict users to local access only regardless of their state of federation. Note that identified data is a. unavailable over the network, b. access is governed explicitly by membership to a specific group or role. Leaf defaults to de-identification only for safety. Quarantining will be enabled by default, meaning users must be explicitly granted the permission to query federated nodes. In small federations, you can skip this step by assigning the Users group to the Federated role. Additionally, this makes it harder for one institution to negatively impact its federation through poor training or mismanagement.

We propose:

• Adding a new Authorization:[SAML2|ActiveDirectory]:RolesMapping entry called Federated.
• Add Federated Enum variant to Model.Authorization.RoleMask.
• Add this role to the RoleMask calculation in API.Authorization.IFederatedEntitlementProvider implementations.
• Encode this role into Jwt in API.Jwt.JwtProvider.GetRoles if present.
• Update API.Middleware.Federation.RejectIdentifiedFederationMiddleware to RejectInvalidFederatedUserMiddleware. Add check for user is not institutional and not quarantined, reject with 403 if quarantined.
• Add UI hint to describe your current role set. Optionally explain what the quarantine is.

FHIR templates can't cover all possible kinds of data, and there are a number of use cases that would benefit from dynamic datasets, in other words datasets whose columns are defined by an admin instead of adhering to a FHIR dataset interface.

Dynamic dataset configuration will require settings for:

• IsLongitidunal - indicator for whether there are potentially more than one row per patient. If true, the dataset should require the EncounterId be returned.
• SqlDateField - if IsLongitudinal is true, a date field must also be defined.
• SqlStringValueField - if IsLongitudinal is true, a string value field must also be defined. This field is passed to the client for data aggregation.
• SqlNumericValueField - if IsLongitudinal is true, a numeric value field may also be defined. This field is passed to the client for data aggregation.
• Fields - a JSON object array matching how FHIR shapes are stored in ref.Shape, with each object listing {"name":"exampleFieldName","type":"String","phi":false,"mask":false,"required":true}. Alternatively, we could also normalize this as it's own DB table.

Server Touch Points

DB

• Add DynamicConfiguration field to app.DatasetQuery. JSON representation of above configuration data.
• Add DynamicConfiguration field to second return table of app.sp_GetDatasetContextById. It should only be populated if Shape is the Dynamic value.

API

• Add DynamicConfiguration property to DatasetQueryRecord and DatasetQuery.
• Add JSON SerDe functionality to DatasetQueryRecord to enable conversion of configuration to and from database storage formats.
• Add DynamicConfigurationValidator to enforce rules above.
• Add any required pre-query validation to DatasetCompilerValidationContextProvider.GetContextState.
• Create DynamicDataset derivation of ShapedDataset, will use a Dictionary<string, object> to contain data elements.
• Services.Cohort.DatasetExecutor needs to be Dynamic Dataset aware.
• Model.Cohort.ShapedDatasetSchema needs to be able to take a DynamicConfiguration and produce a schema.
• Model.Anonymization needs a dynamic dataset anonymizer impl.
• Services.Cohort.DatasetMarshaller needs a Dynamic DatasetMarshaller impl.
• Model.Cohort.Dataset.Results should be changed to Dictionary<string, IEnumerable<object>> instead. Give ShapedDataset an object Result() method that returns this. Dynamic dataset should override and unroll the dictionary into an ExpandoObject.

There are many cases not covered by FHIR resources of data sites would want to show users. Particularly if a site is not in a federated environment (and thus there isn't a need to have dataset shapes match others') this should be allowed.

Non-standard datasets must indicate:

• They either 1 row per patient or 1-to-many
• The expected data type of each field (JSON?)
• Fields which have PHI (JSON?)

Problem

The client version number can be found at https://github.com/uwrit/leaf/blob/master/src/ui-client/package.json, and the server version can be found at https://github.com/uwrit/leaf/blob/master/src/server/Directory.Build.props, but the database version is not stored anywhere.

It's possible therefore for an admin to lose track of what version the DB is at, and not know how to migrate to the latest version of Leaf.

Proposal

Add a database table with a single role designed to simply track the current version for admin reference. This would be updated in the migration SQL script for each new version.

This issue serves as a forum for comments, questions, and general discussion about Leaf deployment as documented in docs/DEPLOY.

Please feel free to let us know how the documentation can be improved to help new institutions roll Leaf out.

Ensure each table has the appropriate metadata fields, and all stored procedures pass that information along.

Candidates:
Created datetime
CreatedBy nvarchar(1000)
Updated datetime
UpdatedBy datetime

We don't currently mention needing the .NET Core SDK for builds or using a separate build server, so we should document this.

Deprecated legacy column.

• Religion? - horizontal bar
• Language by Ethnicity? - vertical stacked bar

[I]DatasetQueryService and [I]DemographicQueryService are too clever.

The context state calculation and logging should be lifted up into a model type. Something like [Dataset|Demographic]ContextValidator([Dataset|Demographic]CompilerContext) --> CompilerValidationContext.

Move non-compiler CRUD methods to different interface.

The goal here is to make the required types of interaction with the database more obvious, and to better isolate network activity from standard Leaf business logic, like validating a CompilerContext in this case.

We have to drop and recreate (with a name) any pk, fk, constraint, etc, that was created without a name and had one auto-generated in the database.

This will prevent a whole class of bugs and issues from happening during database changes in future upgrades.

Problem

In gateway mode, the client is not currently able to save a query.

This is because saved queries require a queryId, which is generated on initial query run. However because the gateway node is never queried directly, it does not generate a queryId for the cohort.

The actual error happens here https://github.com/uwrit/leaf/blob/master/src/ui-client/src/services/queryApi.ts#L52.

The user should be able to add an optional description to give context to a query. This will likely be more useful with query sharing.

Fixed in #41 . This was due to the anchorDate variable being incorrectly derived from the panel.DateFilter. rather than subPanel.JoinSequence.DateIncrementType, likely an oversight from the original port from v2.

Use case: user wants to keep current saved query, but copy it and modify.

We would need to:

1. Generate a new Guid (queryId), as the client still only has the old one.
2. Copy the cohort and constraints in DB.
3. Insert a new row in app.Query

This will be an iterative and incremental process.

Goal

API.Controllers should not directly rely on service interfaces. Instead they should rely on Model types that in turn rely on service interfaces and do the work of orchestrating those services and business logic together. This keeps the answer of "What does Leaf do?" well defined and in a single project in the solution.

Example

This issue serves as a forum for feature and bug fix requests, ideas, and general discussion around the administration panel in Leaf. We want to make it as easy as possible to maintain Leaf instances and welcome your thoughts.

• Numeric filter
• Custom dates
• Sequential dates

Fixed in #41

Problem

One thing that has always been tricky in Leaf (and other cohort discovery tools, for that matter) has been allowing users to create queries that join Concepts that are potentially related by many-to-many relationships intuitively. The most obvious example of the need for this is microbiology labs, where we need to allow users to create a query joining a micro test result to it's respective specimen.

These data elements are generally in different SQL tables and may contain the same FK, which is how a SQL writer would join them. We can't use In the Same Encounter to achieve this, because in a given encounter a patient may have multiple micro specimens drawn and multiple organisms identified, and it's critical we match the appropriate specimen to the right result.

So. In the past this was achieved with the In the Same Event sequence join, which used an arbitrarily defined SQL column as the Event Column on both sides, which kind of worked, in that it wrote the correct SQL.

To users though this was completely opaque, and it was unclear why this worked or how they knew whether two Concepts were part of the same 'event'.

Proposed Solution

Ideally we would want the UI 'event' to be dynamic, and the microbiology example above could look something like this:

i.e., Staph aureaus -> In the Same Microbiology Test -> Specimen was Blood

And users could define this relationship in the query with the UI like this:

This could work by each Concept having optional SqlFieldEventId and UiDisplayEventName properties, and the SqlFieldEventId values between two or more Concepts could be compared to determine if they are of the same 'event'.

If the SqlFieldEventIds differed, we would show red squigglies:

The top-most, first Concept with a SqlFieldEventId dropped in the panel would define the possible event. Its UiDisplayEventName would be shown as an option in the 'same encounter, within +/- ...' dropdown.

The great part is that this should be straightforward to implement. The SQL compiler already understands In the Same Event queries, so the changes would involve:

1. Adding the ConceptSqlEvent table, its PK, and an FK to ConceptSqlSet.
2. Adding logic in the client and updating models.
3. Updating server API models and adding Admin CRUD API calls for ConceptSqlEvents.

The DB tables for this would look like this:

With the ConceptSqlEvent table and its PK and the FK to it being new. The Concept model would need to include two new fields,SqlFieldEventId = ConceptSqlEvent.Id and ConceptSqlEvent.UiDisplayEventName, which would be returned in app.sp_HydrateConceptsByIds by an additional LEFT JOIN.

The above micro example would look like this in the ConceptSqlEvent table:

Reported by @WeberLab.

Problem

After clicking 'Learn More' for a given concept, the container

https://github.com/uwrit/leaf/blob/master/src/server/Model/Schema/LeafType.cs

Refactor configuration struct format to be more intuitive and less flat.

TODOs:

• PreflightResourceChecker
  • pull user based if switch to internal impls
  • log output of each public function before returning if failed.
• QueryManager

Reported by @WeberLab

Problem

The current Leaf documentation indicates that admins should run sp_CalculatePatientCountByConcept in order to auto-calculate the patient counts. While this is true, it's also erroneous because that stored procedure is called by the wrapper procedure, sp_CalculatePatientCounts, which loops through each concept to produce the count calculation.

Solution

This is fixed in 3.2, and the documentation will be updated.

Problem

As of 3.1.1, there is no way to manage dataset definitions without interacting with the database directly.

Proposal

Add functionality to administrative surface area that allows users to add/remove/modify dataset query definitions in real-time through the standard admin panel.

Solution Components

API

Dataset

• GET api/admin/dataset/{id}
• PUT api/admin/dataset/{id}
• DELETE api/admin/dataset/{id}
• POST api/admin/dataset

Dataset Category

• GET api/admin/datasetcategory
• DELETE api/admin/datasetcategory/{id} --> only if free of dependents, return conflict info otherwise
• PUT api/admin/datasetcategory/{id}
• POST api/admin/datasetcategory

Demographics

• GET api/admin/demographics
• PUT api/admin/demographics

UI

• Add dataset subpane to Admin Panel
• Add/Update/Edit Dataset Category
• Add/Update/Edit Dataset

Problem

Currently, DatasetExecutor and DemographicExecutor service implementations are responsible for selecting when to and then calling the anonymization code. We should refactor the Executor implementations and interfaces so that the model remains in control of this stage and future implementations don't need to be aware that this even happens.

Problem

Cohorts of a relatively small size (e.g., 10 or less) have been demonstrated to be at greater risk of potential re-identification, especially as an increasing number of query criteria narrows the potential search space of a would-be re-identifier. Tools like i2b2 track the patient IDs returned by a given user and session, and patients frequently reappearing in their queries of a small size are 'fuzzed' such that the full number of patients truly in the cohort is shifted to further obfuscate and prevent re-identification. SHRINE takes this a further step by including a +/- range, in addition to the number.

Leaf does not currently do this, and as the tool is expanded in use to a greater number of sites - some federating with multiple additional organizations - some additional fuzzing capabilities should be added in.

This is further complicated by the fact that an important feature of Leaf is the ability to immediately retrieve row-level data for a cohort (cached after the cohort count query).

Proposed Solution

Clinical DBs have wacky dates, and the way we had handled this in the past (see pic below) was by

• Making null dates a ?
• Grouping dates prior to 1995 as <1995
• Grouping dates farther in the future than the current year as >2019

We can devise a different way of handling this than allowing strings for the Year, but I'm inclined to change this from int to a string unless we have other options.

The relevant model is here https://github.com/uwrit/leaf/blob/master/src/server/Model/Compiler/ConceptPatientYearCount.cs#L11.

Note that changing this will also require we filter out non-numeric Year values prior to computing the estimated query cost and sorting the panels here https://github.com/uwrit/leaf/blob/master/src/server/Model/Compiler/CteCohortQueryContext.cs#L37 before SQL compilation.

Summary

Recently we added the admin flag to allow admins to see all Concepts, regardless of whether they meet user or group criteria constraints for Concepts. This works as expected.

However, if an admin tries to then run a query using the Constrained Concept, the Preflight Check fails, and thus the query fails.

I believe I traced this to [app].[fn_FilterConceptsByConstraint], which returns the Concept as disallowed, and thus the isAuthorized flag is set to 0. Please verify.

Reproduction steps:

1. Run the below script (ConceptId is 'Procedures' root)

  INSERT INTO [LeafDB].[auth].[ConceptConstraint]
  SELECT '1c05433e-f36b-1410-812b-00ffffffffff', 1, '[email protected]'

2. Try to run a query with Procedures or any descendants.

Suggested fix

Perhaps we should weave the @isadmin flag into the Preflight checks as well.

Summary

Constraining concepts and queries works as expected, but preflight checks fail if a Concept is constrained by both a User and a Group, and the user/admin meets one criteria but not the other.

Reproduction steps

1. Run the below script (ConceptId is 'Procedures' root)

INSERT INTO [LeafDB].[auth].[ConceptConstraint]
SELECT '1c05433e-f36b-1410-812b-00ffffffffff', 1, '[email protected]'

INSERT INTO [LeafDB].[auth].[ConceptConstraint]
SELECT '1c05433e-f36b-1410-812b-00ffffffffff', 2, '[email protected]'

2. Try to run a query with Procedures or any descendants.

Suggested fix

It looks like the issue lies with the fact that we UNION the 2 possible constraints.
https://github.com/uwrit/leaf/blob/master/src/db/obj/app.sp_FilterConceptsByConstraint.StoredProcedure.sql#L59

-- Identify any requested Ids that are disallowed by constraint anywhere in their ancestry.
    DECLARE @disallowed app.ResourceIdTable;
    INSERT INTO @disallowed
    SELECT DISTINCT
        a.Base
    FROM @ancestry a
    JOIN auth.ConceptConstraint c on a.[Current] = c.ConceptId and c.ConstraintId = 1 -- User Constrained
    WHERE @user NOT IN (
        SELECT ConstraintValue
        FROM auth.ConceptConstraint
        WHERE ConceptId = c.ConceptId
        AND c.ConstraintId = 1
    )
    UNION
    SELECT DISTINCT
        a.Base
    FROM @ancestry a
    JOIN auth.ConceptConstraint c on a.[Current] = c.ConceptId and c.ConstraintId = 2 -- Group Constrained
    WHERE NOT EXISTS (
        SELECT 1 FROM @groups WHERE [Group] = c.ConstraintValue
    );

Maybe we could pivot this instead:

-- Identify any requested Ids that are disallowed by constraint anywhere in their ancestry.
DECLARE @disallowed app.ResourceIdTable;
;WITH constrained AS
	(
		SELECT c.ConceptId, c.ConstraintId, c.ConstraintValue
		FROM auth.ConceptConstraint c
		WHERE EXISTS (SELECT 1 FROM @ancestry a WHERE a.[Current] = c.ConceptId)
	)
, permitted AS
(
	SELECT 
		a.Base
	  , a.[Current]
	  , HasConstraint = CASE WHEN EXISTS 
                       (SELECT 1 FROM constrained c 
			WHERE c.ConceptId = a.[Current])
				      THEN 1 ELSE 0 END
	  , UserPermitted = CASE WHEN EXISTS 
                       (SELECT 1 FROM constrained c 
			WHERE c.ConceptId = a.[Current] 
				    AND c.ConstraintId = 1 
				    AND c.ConstraintValue = @user)
				      THEN 1 ELSE 0 END
	  , GroupPermitted = CASE WHEN EXISTS 
                       (SELECT 1 FROM constrained c 
			WHERE c.ConceptId = a.[Current] 
				     AND c.ConstraintId = 2 
				     AND c.ConstraintValue IN (SELECT g.[Group] FROM @groups g))
				      THEN 1 ELSE 0 END
	FROM @ancestry a
)

INSERT INTO @disallowed
SELECT p.Base
FROM permitted p
WHERE p.HasConstraint = 1
	  AND (p.UserPermitted = 0 AND p.GroupPermitted = 0)

We can experiment with this or a different approach, but this basic flow appears to work on my dev instance.

As reported by @sgchoe.

When the Patient List or Visualization panes error (usually when the Basic Demographics dataset is not defined or has invalid SQL) the UI is not correctly indicating this. Specifically the cohort network instance is marked as IN_ERROR but the top-level cohort state is stuck in LOADING.

In the previous UW-specific implementation of Leaf, Leaf allowed users to query for the first or final time a concept was found for a patient (i.e. the 'index' event):

This is a feature which is requested or asked about somewhat frequently, and should probably be re-added in the new version.

Note: re-adding this should probably be done after the SQL compiler is refactored.

Summary
On heavily skewed result sets, the left side numbers of the At a Glance bars are getting cut off.

Reproduction Steps
Simple query like:
Any patients who Identify as Black or African American.

Returns 6171 patients, of which 5898 are living and 273 are deceased.

Run query, go to Visualize. The 5 digit is cut-off. Resize the window does not help.

https://github.com/uwrit/leaf/blob/master/src/db/obj/app.sp_GetDatasetQueries.StoredProcedure.sql

• Expand ConstraintValue fields to 1000 chars
• Update @groups type to 1000 chars in db
• Add scope at initial JWT creation time

Extremely minor and low priority, but it would be nice to be able to dismiss the Saved confirmation dialog, particularly early on when many concepts may be being created/edited. Thanks.

Problem

When initially setting up a Leaf instance it may be useful for admins to have a checklist pane to know what still needs to be done to setup. This should be easier in v3.2, but currently there is no summary screen to understand remaining steps (this assumes the API, DB, etc. are all up and running and the admin can navigate to the app, of course).

Proposal

Add an admin checklist/instance summary pane within the app Admin Panel, perhaps having it be the default pane if anything is misconfigured.

Checklist items:

• Concept SQL Sets created.
• Concepts created.
• Demographics dataset SQL configured.
• Other Datasets created.
• Others...