Sample data. This database was generated by scripts/seed_dev_db.py —
every service, deploy, person and figure on this page is synthetic. Names are
sample.*@seed.invalid and image tags are prefixed SEED-.
Nothing here happened.
Query trends 80
stored23h agotop 80 of 1,155
Neo4j query shapes on test26 over the last 3d, grouped by plan hash.
Click a column to sort; p95 drift (late-half ÷ early-half of the window) ranks the queries getting slower.
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <>…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) WITH * WHERE st.tildaid = c.id RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) WITH * WHERE st.tildaid = c.id RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 MATCH (o)<-[:STUDY_TO_ORG]-(st:Study {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_3 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) MATCH (o)<-[:STUDY_TO_ORG]-(st:Study {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
enrollsvc
1
2.21 s
4.85 s
2.21 s
flat
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
dashql
1
2.94 s
4.85 s
2.94 s
flat
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
dashql
6
1.17 s
4.70 s
2.85 s
0.0× ↓
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
dashql
2
1.75 s
4.70 s
3.51 s
flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study?Basic??ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess??Inactive' AND sst.tildaid IN studyIdsInScope AND (doc1.siteId = ? OR (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(:DslPlanDefinition{delpending:false,type:?})- [:PLAN_DEFINITION_TO_SITE]-(:Site{tildaid:doc1.siteId})) RETURN doc1 as doc UNION WITH doc1, studyIdsInScope, directAdmit WITH doc1, studyIdsInScope WHERE NOT directAdmit AND (NOT $nonSiteOnlyPatients0 OR doc1.patientId = ?) AND doc1.siteId <> ? MATCH (s:Site{tildaid:doc1.siteId, delpending:false})-[:PLAN_DEFINITION_TO_SITE]- (pd:DslPlanDefinition{delpending:false,type:?})-[:PLAN_DEFINITION_TO_STUDY]->(sst:Study) WHERE sst.tildaid IN studyIdsInScope RETURN doc1 as doc UNION WITH doc1, directAdmit WITH doc1 WHERE directAdmit RETURN doc1 as doc} WITH DISTINCT doc WITH doc, coalesce(doc.archived, false) as isArchived RETURN count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as readyForTmf, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as needsCorrection, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as needsAttention, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as draft, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as aiReview, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as manual, count(CASE WHEN isArchived THEN ? END) as archived
docsvc
1
1.10 s
1.95 s
1.10 s
flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study??ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess??Inactive? ?Received?Quality Control?PHI Issues???/???/???/') ELSE ? END as childFolderPath WITH docObj, ct, child, st, head(collect(childFolderPath)) AS childFolderPath WITH child, CASE WHEN ct IS NULL THEN null ELSE { documentId: child.tildaid, studyId: COALESCE(child.studyId, ?), studyName: COALESCE(st.name, ?), folderPath: childFolderPath, kind: ct.kind, stale: ct.sourceVersionId <> docObj.primaryVersionId, version: COALESCE(child.versionName, toString(child.majorVersion)) } END as copiedToEntry RETURN collect(copiedToEntry) as copiedTo, count(DISTINCT child) as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { return null as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
1
1.90 s
1.95 s
1.90 s
flat
match (task:Task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where (Organization.alias = $scopeOrgAlias) match (task)-[:BELONGS_TO]->(SInbox:Inbox{type:?})-[:MANAGED_BY]->(Study:Study{name:$scopeStudyName})-[:STUDY_TO_ORG]-…
match (task:Task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where (Organization.alias = $scopeOrgAlias) match (task)-[:BELONGS_TO]->(SInbox:Inbox{type:?})-[:MANAGED_BY]->(Study:Study{name:$scopeStudyName})-[:STUDY_TO_ORG]->(Organization) where (Organization.alias = $scopeOrgAlias) with task match (task)-[:WITH_CONTEXT]->() where task.state <> ? optional match (task)-[:BELONGS_TO]-(SInbox:Inbox{type:?})-[:MANAGED_BY]-(UserStudy:Study)-[:STUDY_TO_ORG]-(Organization) where (Organization.alias = $scopeOrgAlias) with task, SInbox, UserStudy where (SInbox IS NULL) OR (SInbox IS NOT NULL AND exists((:User{email:$scopeUserEmail})-[:FOR_USER]-(:Actor)-[:STUDY_TO_ACTOR]-(UserStudy))) WITH collect(DISTINCT task) AS tasks WITH tasks, size(tasks) AS taskCount CALL { WITH tasks UNWIND tasks AS task RETURN collect(DISTINCT task.state) AS states, collect(DISTINCT task.category) AS categories, collect(DISTINCT coalesce(task.priority, ?)) AS priorities } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:CREATED_BY]-(cu:User) RETURN collect(DISTINCT {id: cu.tildaid, name: cu.name}) AS createdby } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:BELONGS_TO]->(:Inbox)-[:OWNED_BY]->(au:User) RETURN collect(DISTINCT {id: au.tildaid, name: au.name}) AS assignees } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(ist:Study) OPTIONAL MATCH (task)-[:WITH_CONTEXT]->(cst:Study) RETURN collect(DISTINCT ist.name) + collect(DISTINCT cst.name) AS studiesShallow } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(isite:Site) RETURN collect(DISTINCT isite.name) AS sitesShallow } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:WITH_CONTEXT|CREATED_FROM_SPEC]->(vctx) OPTIONAL MATCH (vctx)-[:BELONGS_TO_VISIT]->(v1) WHERE v1:Visit OR v1:VisitV2 OPTIONAL MATCH (vctx)<-[:OBJ_TO_ADVERSE_EVENT|PROTOCOL_DEVIATION|PARTICIPANT_ACTIVITY|PARTICIPANT_VISIT_TO_ALL_PARTICIPANT_PREVISIT|PARTICIPANT_VISIT_TO_ALL_PARTICIPANT_POSTVISIT]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]->(v2) WHERE v2:Visit OR v2:VisitV2 OPTIONAL MATCH (vctx)-[:BUDGET_REPORT_TO_PARTICIPANT_VISIT]->(:ParticipantVisit)-[:BELONGS_TO_VISIT]->(v3) WHERE v3:Visit OR v3:VisitV2 OPTIONAL MATCH (:ParticipantQuestion{tildaid: vctx.scopeId})<-[:PARTICIPANT_QUESTION]-(:ParticipantQuestionnaire)<-[:PARTICIPANT_QUESTIONNAIRE]-(:ParticipantActivity)<-[:PARTICIPANT_ACTIVITY]-(:ParticipantVisit)-[:BELONGS_TO_VISIT]->(v4) WHERE v4:Visit OR v4:VisitV2 OPTIONAL MATCH (vctx)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_PRE_POST_VISIT_TO_ALL_PARTICIPANT_TASKS]-(:ParticipantPrePostVisit)<-[:PARTICIPANT_VISIT_TO_ALL_PARTICIPANT_PREVISIT|PARTICIPANT_VISIT_TO_ALL_PARTICIPANT_POSTVISIT]-(:ParticipantVisit)-[:BELONGS_TO_VISIT]->(v5) WHERE v5:Visit OR v5:VisitV2 RETURN collect(DISTINCT v1.title) + collect(DISTINCT v2.title) + collect(DISTINCT v3.title) + collect(DISTINCT v4.title) + collect(DISTINCT v5.title) AS visits } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:WITH_CONTEXT|CREATED_FROM_SPEC]->(ctx) OPTIONAL MATCH (ctx)-[:LEAD_TO_STUDY]->(lst:Study) OPTIONAL MATCH (ctx)-[:LEAD_TO_PATIENT]->(lp:Patient) OPTIONAL MATCH (ctx)-[:BUDGET_REPORT_TO_PARTICIPANT_VISIT]->(brpv:ParticipantVisit) OPTIONAL MATCH (ctx)-[:PARTICIPANT_TABLE]->(ptab:ParticipantTable) OPTIONAL MATCH (ctx)-[:PLAN_DEFINITION_TO_STUDY]->(dst:Study) WHERE ctx.delpending = false OPTIONAL MATCH (ctx)-[:PLAN_DEFINITION_TO_SITE]->(dsite:Site) WHERE ctx.delpending = false OPTIONAL MATCH (ctx)<-[:STUDY_TO_ACTOR]-(ast:Study) OPTIONAL MATCH (ctx)<-[:STAFF_TO_ACTOR]-(asite:Site{status:?}) OPTIONAL MATCH (ctx)-[:WORKPLAN_TO_STUDY]->(wst:Study) OPTIONAL MATCH (ctx)-[:WORKPLAN_TO_SITE]->(wsite:Site) WITH ctx, lst, lp, dst, dsite, ast, asite, wst, wsite, coalesce(ctx.carePlanId, ctx.careplanId, CASE WHEN ctx:CarePlan THEN ctx.tildaid END, brpv.carePlanId, ptab.carePlanId) AS cpid OPTIONAL MATCH (cp:CarePlan{tildaid: cpid})-[:EXECUTE_SOA]->(:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition{delpending:false})-[:PLAN_DEFINITION_TO_STUDY]->(pst:Study) OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]->(psite:Site) OPTIONAL MATCH (p:Patient{tildaid: cp.participantId}) WITH lst, dst, ast, wst, pst, dsite, asite, wsite, psite, coalesce(p, lp, CASE WHEN ctx:Patient THEN ctx END) AS pat OPTIONAL MATCH (phi:Phi{tildaid: pat.phiid}) RETURN collect(DISTINCT pst.name) + collect(DISTINCT lst.name) + collect(DISTINCT dst.name) + collect(DISTINCT ast.name) + collect(DISTINCT wst.name) AS studiesDeep, collect(DISTINCT psite.name) + collect(DISTINCT dsite.name) + collect(DISTINCT asite.name) + collect(DISTINCT wsite.name) AS sitesDeep, collect(DISTINCT {id: phi.tildaid, fullname: phi.fullname, firstname: phi.firstname, middlename: phi.middlename, lastname: phi.lastname, suffix: phi.suffix}) AS subjects } RETURN { taskCount: taskCount, states: states, categories: categories, priorities: priorities, createdby: createdby, assignees: assignees, studies: studiesShallow + studiesDeep, sites: sitesShallow + sitesDeep, visits: visits, subjects: subjects } AS facets
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
18
196 ms
1.55 s
1.13 s
flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
5
618 ms
975 ms
679 ms
flat
MATCH (study:Study {tildaid: $studyId})-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:P…
MATCH (study:Study {tildaid: $studyId})-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl)-[:PARTICIPANT_CONTROL]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw UNION ALL WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw } WITH DISTINCT study, t, ptc, pw MATCH (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(o:Organization) MATCH (ptc)-[:BELONGS_TO_TASKCONFIG]-(tc:TaskConfig) OPTIONAL MATCH (t)-[:BELONGS_TO]-(taskInbox:Inbox{type:?})--(taskUser:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:Actor{delpending:false})-[:FOR_USER]-(user:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(scopeSite:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(o) WITH DISTINCT study, t, o, tc, taskInbox, taskUser, user, scopeSite SKIP $skip LIMIT $limit WITH collect({study:study, t:t, o:o, tc:tc, taskInbox:taskInbox, taskUser:taskUser, user:user, scopeSite:scopeSite}) AS rows, collect(DISTINCT study) AS studies, collect(DISTINCT o) AS orgs, collect(DISTINCT scopeSite) AS sites CALL { WITH studies UNWIND studies AS study OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(r:Role) WHERE r.name IN [?, ?, ?] RETURN study.tildaid AS studyId, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS piTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS smoTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS raTildaid } WITH rows, orgs, sites, collect({studyId: studyId, pi: piTildaid, smo: smoTildaid, ra: raTildaid}) AS studyRoles CALL { WITH orgs UNWIND orgs AS o OPTIONAL MATCH (o)-[:ORGANIZATION_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN o.tildaid AS orgId, collect(DISTINCT u.tildaid) AS adminTildaid } WITH rows, sites, studyRoles, collect({orgId: orgId, admin: adminTildaid}) AS orgRoles CALL { WITH sites UNWIND (CASE WHEN size(sites) = ? THEN [null] ELSE sites END) AS scopeSite OPTIONAL MATCH (scopeSite)-[:STAFF_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN scopeSite.tildaid AS siteId, collect(DISTINCT u.tildaid) AS siteAdminTildaid } WITH rows, studyRoles, orgRoles, collect({siteId: siteId, admin: siteAdminTildaid}) AS siteRoles UNWIND rows AS row WITH row, [x IN studyRoles WHERE x.studyId = row.study.tildaid][?] AS sr, [x IN orgRoles WHERE x.orgId = row.o.tildaid][?] AS orr, [x IN siteRoles WHERE row.scopeSite IS NOT NULL AND x.siteId = row.scopeSite.tildaid][?] AS str RETURN DISTINCT row.t AS task, apoc.convert.toJson({userTildaid: row.user.tildaid, userName: row.user.name, PITildaid: sr.pi, studyOwnerTildaid: sr.smo, regAnalystTildaid: sr.ra, orgAdminTildaid: orr.admin, studyName: row.study.name, siteName: row.scopeSite.name, siteAdminTildaid: CASE WHEN row.scopeSite IS NULL THEN [] ELSE str.admin END}) AS details, row.tc.who AS dsnWho, row.o.alias AS orgAlias, row.taskInbox, row.taskUser.tildaid AS taskUserId
tusker
1
617 ms
975 ms
617 ms
flat
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:P…
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId) RETURN ta, training, null AS study UNION MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?}) WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false}) WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) RETURN ta, training, study } CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, study, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
execsvc
1
815 ms
975 ms
815 ms
flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
1
633 ms
975 ms
633 ms
flat
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:P…
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId) RETURN ta, training, null AS study UNION MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?}) WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false}) WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) RETURN ta, training, study } CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, study, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
1
811 ms
975 ms
811 ms
flat
MATCH (o:Organization{tildaid:$id})-[:USES]->(fm:FolderManager{tildaid:$id1}) OPTIONAL MATCH (fm)-[:INCLUDES]->(f0:Folder{folderType:?}) OPTIONAL MATCH (f0)-[:INCLUDES]->(f1:Folder) OPTIONAL MATCH (f1)-[:INCLUDES]->(f2:Folder) OPTIONAL MAT…
MATCH (o:Organization{tildaid:$id})-[:USES]->(fm:FolderManager{tildaid:$id1}) OPTIONAL MATCH (fm)-[:INCLUDES]->(f0:Folder{folderType:?}) OPTIONAL MATCH (f0)-[:INCLUDES]->(f1:Folder) OPTIONAL MATCH (f1)-[:INCLUDES]->(f2:Folder) OPTIONAL MATCH (f2)-[:INCLUDES]->(f3:Folder) OPTIONAL MATCH (f3)-[:INCLUDES]->(sa:SubArtifact) OPTIONAL MATCH (f3)-[:HAS]->(f3Constraint:FolderConstraints) OPTIONAL MATCH (sa)-[:HAS]->(saConstraint:FolderConstraints) OPTIONAL MATCH (f3Constraint)-[:HAS_ATTRIBUTE_CONSTRAINT]->(f3Dac:DocAttributeConstraint) OPTIONAL MATCH (saConstraint)-[:HAS_ATTRIBUTE_CONSTRAINT]->(saDac:DocAttributeConstraint) WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, f3Dac, saDac WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, COLLECT(DISTINCT CASE WHEN f3Dac IS NOT NULL THEN {id: f3Dac.tildaid, key: f3Dac.key, order: f3Dac.order, description: f3Dac.description, isMandatory: f3Dac.isMandatory} ELSE NULL END) as f3Constraints, COLLECT(DISTINCT CASE WHEN saDac IS NOT NULL THEN {id: saDac.tildaid, key: saDac.key, order: saDac.order, description: saDac.description, isMandatory: saDac.isMandatory} ELSE NULL END) as saConstraints WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, COUNT(sa) as saNodeCount, [attr IN f3Constraints WHERE attr IS NOT NULL] as f3ConstraintsFiltered, [attr IN saConstraints WHERE attr IS NOT NULL] as saConstraintsFiltered UNWIND CASE WHEN size(f3ConstraintsFiltered) > ? THEN f3ConstraintsFiltered ELSE [null] END as f3Attr WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, saConstraintsFiltered, f3Attr WHERE f3Attr IS NOT NULL OR size(f3ConstraintsFiltered) = ? WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, saConstraintsFiltered, f3Attr ORDER BY COALESCE(f3Attr.order, ?) WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, saConstraintsFiltered, [attr IN COLLECT(f3Attr) WHERE attr IS NOT NULL] as f3ConstraintList UNWIND CASE WHEN size(saConstraintsFiltered) > ? THEN saConstraintsFiltered ELSE [null] END as saAttr WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, f3ConstraintList, saAttr WHERE saAttr IS NOT NULL OR size(saConstraintsFiltered) = ? WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, f3ConstraintList, saAttr ORDER BY COALESCE(saAttr.order, ?) WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, f3ConstraintList, [attr IN COLLECT(saAttr) WHERE attr IS NOT NULL] as saConstraintList WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, f3ConstraintList, saConstraintList, {name: sa.name, id: sa.tildaid, parentFolderId: f3.tildaid, number: sa.number, description: sa.description, aiInstructions: CASE WHEN $includeAIInstructions THEN sa.aiInstructions ELSE null END, risk: sa.risk, isArchived: sa.isArchived, constraints: CASE WHEN saConstraint IS NOT NULL THEN { id: saConstraint.tildaid, mandatory: saConstraint.mandatory, tmfLevel: saConstraint.tmfLevel, attributeConstraints: CASE WHEN size(saConstraintList) > ? THEN saConstraintList ELSE [] END } ELSE NULL END} as saNode ORDER BY toLower(saNode.name) WITH f0, f1, f2, f3, f3Constraint, saNodeCount, f3ConstraintList, COLLECT(saNode) as saNodes WITH f0, f1, f2, COUNT(f3) as f3NodeCount, {name: f3.name, number: f3.number, id: f3.tildaid, parentFolderId: f2.tildaid, level: f3.level, description: f3.description, aiInstructions: CASE WHEN $includeAIInstructions THEN f3.aiInstructions ELSE null END, risk: f3.risk, scope: f3.scope, type: f3.folderType, folderManagerId: f3.folderManagerId, doctypes: f3.doctypes, isInternal: f3.isInternal, isArchived: f3.isArchived, path: apoc.text.join([f0.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+f1.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+ apoc.text.lpad(toString(COALESCE(f2.number, ?)), ?, ?)+ ?+f2.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+ apoc.text.lpad(toString(COALESCE(f2.number, ?)), ?, ?)+ ?+apoc.text.lpad(toString(COALESCE(f3.number, ?)), ?, ?)+ ?+f3.name], ?), folders:[], documents: [], mandatoryFields: [], optionalFields: [], subArtifacts: CASE WHEN saNodeCount=? THEN [] ELSE saNodes END, constraints: CASE WHEN f3Constraint IS NOT NULL THEN { id: f3Constraint.tildaid, mandatory: f3Constraint.mandatory, tmfLevel: f3Constraint.tmfLevel, attributeConstraints: CASE WHEN size(f3ConstraintList) > ? THEN f3ConstraintList ELSE [] END } ELSE NULL END} as f3Node ORDER BY f3Node.number WITH f0, f1, f2, f3NodeCount, COLLECT(f3Node) as f3Nodes WITH f0, f1, COUNT(f2) as f2NodeCount, {name: f2.name, number: f2.number, id: f2.tildaid, parentFolderId: f1.tildaid, level: f2.level, description: f2.description, aiInstructions: CASE WHEN $includeAIInstructions THEN f2.aiInstructions ELSE null END, risk: f2.risk, scope: f2.scope, type: f2.folderType, folderManagerId: f2.folderManagerId, doctypes: f2.doctypes, isInternal: f2.isInternal, isArchived: f2.isArchived, path: apoc.text.join([f0.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+f1.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+ apoc.text.lpad(toString(COALESCE(f2.number, ?)), ?, ?)+ ?+f2.name], ?), folders: CASE WHEN f3NodeCount=? THEN [] ELSE f3Nodes END, documents: [], mandatoryFields: [], optionalFields: [], subArtifacts: []} as f2Node ORDER BY f2Node.number WITH f0, f1, f2NodeCount, COLLECT(f2Node) as f2Nodes WITH f0, f1, COUNT(f1) as f1NodeCount, {name: f1.name, number: f1.number, id: f1.tildaid, parentFolderId: f0.tildaid, level: f1.level, description: f1.description, aiInstructions: CASE WHEN $includeAIInstructions THEN f1.aiInstructions ELSE null END, risk: f1.risk, scope: f1.scope, type: f1.folderType, folderManagerId: f1.folderManagerId, doctypes: f1.doctypes, isInternal: f1.isInternal, isArchived: f1.isArchived, path: apoc.text.join([f0.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+f1.name], ?), folders: CASE WHEN f2NodeCount=? THEN [] ELSE f2Nodes END, documents: [], mandatoryFields: [], optionalFields: [], subArtifacts: []} as f1Node ORDER BY f1Node.number WITH f0, f1NodeCount, COLLECT(f1Node) as f1Nodes RETURN DISTINCT { name: f0.name, number: f0.number, id: f0.tildaid, level: f0.level, description: f0.description, aiInstructions: CASE WHEN $includeAIInstructions THEN f0.aiInstructions ELSE null END, risk: f0.risk, scope: f0.scope, type: f0.folderType, folderManagerId: f0.folderManagerId, doctypes: f0.doctypes, isInternal: f0.isInternal, isArchived: f0.isArchived, path: f0.name, folders: CASE WHEN f1NodeCount=? THEN [] ELSE f1Nodes END, mandatoryFields: [], optionalFields: [], documents: [], subArtifacts: [] } as f0Node ORDER BY toLower(f0Node.name)
docsvc
1
520 ms
975 ms
520 ms
flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
1
748 ms
975 ms
748 ms
flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
4
679 ms
967 ms
907 ms
flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
24
162 ms
967 ms
1.13 s
flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId}) CALL { // Collect summary for each deliverable in the event WITH e OPTIONAL MATCH (e)-[:REQUIRES_RESOLUTION_OF]->(eventDel:Deliverable) RETURN CASE WHEN eventDel IS NOT NULL THEN eventDel{…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId}) CALL { // Collect summary for each deliverable in the event WITH e OPTIONAL MATCH (e)-[:REQUIRES_RESOLUTION_OF]->(eventDel:Deliverable) RETURN CASE WHEN eventDel IS NOT NULL THEN eventDel{.*} ELSE null END as eventDeliverable } WITH e, collect(distinct eventDeliverable) as deliverables, [d IN collect(distinct eventDeliverable) WHERE d.warning IS NOT NULL AND d.warning <> ? | ??\? + d.warning] as deliverableWarnings OPTIONAL MATCH (e)<-[:EVENTS]-(ep:EventPlan)-[:PLAN_CONTEXT]->(study) WITH e, deliverables, deliverableWarnings, study CALL { // Get details for each scope in the event WITH e, study OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(scope:EventScope) OPTIONAL MATCH (site:Site {tildaid: scope.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition{type:?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, scope, CASE WHEN scope.scopeType = ? THEN site.name ELSE ? END as siteName, CASE WHEN scope.scopeType = ? THEN COALESCE(pd.siteNumber, ?) ELSE ? END as siteNumber OPTIONAL MATCH (scope)-[:APPLICABLE_TO]->(scopeDeliverable:Deliverable) CALL { // Get details of each deliverable applicable to the scope WITH scope, scopeDeliverable OPTIONAL MATCH (scopeDeliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document)-[:SCOPED_TO]->(scope) CALL { // Get match status for each placeholder WITH ph, tracksRel, scope OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH ph, tracksRel, scope, acceptedDoc, [m IN collect(matchDoc) WHERE m IS NOT NULL] as matchDocs WITH ph, tracksRel, scope, acceptedDoc, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchDocs) > ? AND all(m IN matchDocs WHERE m.collection = ?) THEN ? WHEN SIZE(matchDocs) > ? THEN ? ELSE ? END as matchStatus RETURN CASE WHEN ph IS NULL THEN null ELSE {tildaid: ph.tildaid, fileName: ph.fileName, status: matchStatus, placeholderState: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR scope.status = ?} END as deliverablePlaceholder } WITH collect(DISTINCT deliverablePlaceholder) as deliverablePlaceholders WITH [d IN deliverablePlaceholders WHERE d IS NOT NULL] as allPlaceholders RETURN allPlaceholders as deliverablePlaceholders, { total: size(allPlaceholders), pending: size([pp IN allPlaceholders WHERE pp.placeholderState IN [?, ?]]), pendingForDelete: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), accepted: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), rejected: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), incomplete: size([pp IN allPlaceholders WHERE pp.status = ?]), potentialMatchFound: size([pp IN allPlaceholders WHERE pp.status = ?]), matchExpected: size([pp IN allPlaceholders WHERE pp.status = ?]), matched: size([pp IN allPlaceholders WHERE pp.status = ?]), finalized: size([pp IN allPlaceholders WHERE pp.status = ?]), completion: {required: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]), completed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]), aiSuggested: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), inStaging: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), noMatchFound: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), notConfirmed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND pp.placeholderState = ? AND NOT pp.status = ?]), removed: size([pp IN allPlaceholders WHERE (pp.placeholderState = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) / size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]))) END, isComplete: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) > ? AND size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) = size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)])} } as deliverableStats } // Aggregate all deliverables in the scope WITH e, scope, siteName, siteNumber, deliverableStats, CASE WHEN scopeDeliverable IS NOT NULL THEN scopeDeliverable{.*, trackedDocuments: [doc IN deliverablePlaceholders WHERE doc.tildaid IS NOT NULL], progress: deliverableStats } ELSE null END as deliverableDetails WITH distinct e, scope, siteName, siteNumber, collect(distinct deliverableDetails) as deliverablesInScope, { total: sum(deliverableStats.total), pending: sum(deliverableStats.pending), pendingForDelete: sum(deliverableStats.pendingForDelete), accepted: sum(deliverableStats.accepted), rejected: sum(deliverableStats.rejected), incomplete: sum(deliverableStats.incomplete), potentialMatchFound: sum(deliverableStats.potentialMatchFound), matchExpected: sum(deliverableStats.matchExpected), matched: sum(deliverableStats.matched), finalized: sum(deliverableStats.finalized), completion: {required: sum(deliverableStats.completion.required), completed: sum(deliverableStats.completion.completed), aiSuggested: sum(deliverableStats.completion.aiSuggested), inStaging: sum(deliverableStats.completion.inStaging), noMatchFound: sum(deliverableStats.completion.noMatchFound), notConfirmed: sum(deliverableStats.completion.notConfirmed), removed: sum(deliverableStats.completion.removed), percentComplete: CASE WHEN sum(deliverableStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(deliverableStats.completion.completed) / sum(deliverableStats.completion.required))) END, isComplete: sum(deliverableStats.completion.required) > ? AND sum(deliverableStats.completion.completed) = sum(deliverableStats.completion.required)} } as scopeStats CALL { // Fetch rejected deliverables for this scope WITH scope OPTIONAL MATCH (scope)-[r:REJECTED_FROM]->(rejectedDel:Deliverable) RETURN CASE WHEN rejectedDel IS NOT NULL THEN { tildaid: rejectedDel.tildaid, name: rejectedDel.name, reason: r.reason, rejectedAt: r.createdat } ELSE null END as rejectedDeliverable } WITH distinct e, scope, siteName, siteNumber, deliverablesInScope, scopeStats, collect(distinct rejectedDeliverable) as rejectedDeliverables RETURN distinct scope{.*, deliverables: deliverablesInScope, progress: scopeStats, aiNotes: CASE WHEN scope.aiNotes IS NULL OR scope.aiNotes = ? THEN e.aiNotes ELSE scope.aiNotes END, decision: COALESCE(scope.decision, ?), rejectedDeliverables: [r IN rejectedDeliverables WHERE r IS NOT NULL], siteName: siteName, siteNumber: siteNumber} as scopeDetails, scopeStats } // Aggregate all scopes and all deliverables in the event WITH e, study, deliverables, deliverableWarnings, collect(scopeDetails) as eventScopes, { total: sum(scopeStats.total), pending: sum(scopeStats.pending), pendingForDelete: sum(scopeStats.pendingForDelete), accepted: sum(scopeStats.accepted), rejected: sum(scopeStats.rejected), incomplete: sum(scopeStats.incomplete), potentialMatchFound: sum(scopeStats.potentialMatchFound), matchExpected: sum(scopeStats.matchExpected), matched: sum(scopeStats.matched), finalized: sum(scopeStats.finalized), completion: {required: sum(scopeStats.completion.required), completed: sum(scopeStats.completion.completed), aiSuggested: sum(scopeStats.completion.aiSuggested), inStaging: sum(scopeStats.completion.inStaging), noMatchFound: sum(scopeStats.completion.noMatchFound), notConfirmed: sum(scopeStats.completion.notConfirmed), removed: sum(scopeStats.completion.removed), percentComplete: CASE WHEN sum(scopeStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(scopeStats.completion.completed) / sum(scopeStats.completion.required))) END, isComplete: sum(scopeStats.completion.required) > ? AND sum(scopeStats.completion.completed) = sum(scopeStats.completion.required)} } as eventProgress WITH e, study, deliverables, deliverableWarnings, eventScopes, eventProgress, { totalCountries: size([s IN eventScopes WHERE s.scopeType = ?]), totalSites: size([s IN eventScopes WHERE s.scopeType = ?]), countries: [s IN eventScopes WHERE s.scopeType = ? | {name: s.scopeId, id: s.scopeId}], sites: [s IN eventScopes WHERE s.scopeType = ? | {name: s.siteName, id: s.scopeId, siteNumber: s.siteNumber}] } as scopeSummary CALL { // Fetch all ChangeLogs for the event WITH e // Audit items load through GET /changelogs; the event carries its newest ? rows at most. OPTIONAL MATCH (e)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE coalesce(cl.groupRole, ?) <> ? WITH cl ORDER BY cl.createdat DESC LIMIT ? WITH cl ORDER BY cl.createdat ASC RETURN collect(cl{.*}) as changeLogs } CALL { WITH e OPTIONAL MATCH (woc:WorkOrderConfig)-[:WORK_ORDER_CONFIG_TO_EVENT]->(e) RETURN woc.tildaid as workOrderConfigId LIMIT ? } RETURN e{.*, warnings: deliverableWarnings, deliverables: deliverables, scopeSummary: scopeSummary, eventScopes: eventScopes, progress: eventProgress, changeLogs: changeLogs, workOrderConfigId: workOrderConfigId, contextId: COALESCE(study.tildaid, ?), contextType: CASE WHEN study IS NULL THEN ? ELSE head(labels(study)) END, contextName: COALESCE(study.name, ?), aiState: CASE WHEN SIZE(eventScopes) = ? THEN ? WHEN SIZE([s IN eventScopes WHERE s.aiState <> ? AND s.aiState IS NOT NULL]) = ? THEN ? ELSE ? END} as result
docsvc
2
308 ms
950 ms
609 ms
flat
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(a…
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) WITH ph, e, es, del, acceptedDoc, coalesce(acceptedDoc, ph) AS d OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (siteFromEvent: Site{tildaid: es.scopeId}) WHERE es.scopeType = ? OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) where da.key in [?, ?, ?, ?] OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: User) OPTIONAL MATCH (study) - [:STUDY_TO_ACTOR] - (actor: Actor{userId: uploader.tildaid}) WITH d, e, es, del, b, study, b2, coalesce(site, siteFromEvent) AS site, o, head(collect(da)) as da, currentFolder, currentSubArtifact, stagingInfo, artifact, section, zone, collection, subArtifact, uploader, ph, acceptedDoc, head(collect(actor)) as actor CALL apoc.create.vNode([?], { tildaid: ph.tildaid, createdat: ph.createdat, updatedat: ph.updatedat, documentId: acceptedDoc.tildaid, documentCreatedAt: acceptedDoc.createdat, documentUpdatedAt: acceptedDoc.updatedat, orgName: o.name, orgId: o.tildaid, orgAlias: o.alias, studyName: study.name, studyId: study.tildaid, siteName: site.name, siteId: site.tildaid, documentStatus: d.status, country: CASE WHEN es.scopeType = ? THEN coalesce(es.scopeId, d.country) ELSE d.country END, languages: coalesce(apoc.convert.toJson(d.languages), ?), fileName: d.fileName, uploadedByName: uploader.name, uploadedById: uploader.tildaid, collection: d.collection, stateTag: d.stateTag, documentExpiryDate: da.value, zone: zone.name, zoneId: zone.tildaid, zoneNumber: case when zone.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) end, section: section.name, sectionId: section.tildaid, sectionNumber: case when section.number is null then null else apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) end, artifact: artifact.name, artifactId: artifact.tildaid, artifactNumber: case when artifact.number is null then null else apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) end, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, eventId: e.tildaid, eventCreatedAt: e.createdat, eventType: e.type, eventName: e.name, eventStatus: e.status, eventScopeId: es.tildaid, eventScopeName: es.name, scopeType: es.scopeType, eventScopeDecision: es.decision, deliverableId: del.tildaid, deliverableName: del.name, deliverableLocationId: del.locationId, deliverableLocationType: del.locationType, deliverableType: del.type, roles: actor.roles }) YIELD node return node
writedashrsvc
3
300 ms
925 ms
894 ms
flat
MATCH (study:Study {tildaid: $studyId})-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]…
MATCH (study:Study {tildaid: $studyId})-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl)-[:PARTICIPANT_CONTROL]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw UNION ALL WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw } WITH DISTINCT study, t, ptc, pw MATCH (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(o:Organization) MATCH (ptc)-[:BELONGS_TO_TASKCONFIG]-(tc:TaskConfig) OPTIONAL MATCH (t)-[:BELONGS_TO]-(taskInbox:Inbox{type:?})--(taskUser:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:Actor{delpending:false})-[:FOR_USER]-(user:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(scopeSite:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(o) WITH DISTINCT study, t, o, tc, taskInbox, taskUser, user, scopeSite SKIP $skip LIMIT $limit WITH collect({study:study, t:t, o:o, tc:tc, taskInbox:taskInbox, taskUser:taskUser, user:user, scopeSite:scopeSite}) AS rows, collect(DISTINCT study) AS studies, collect(DISTINCT o) AS orgs, collect(DISTINCT scopeSite) AS sites CALL { WITH studies UNWIND studies AS study OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(r:Role) WHERE r.name IN [?, ?, ?] RETURN study.tildaid AS studyId, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS piTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS smoTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS raTildaid } WITH rows, orgs, sites, collect({studyId: studyId, pi: piTildaid, smo: smoTildaid, ra: raTildaid}) AS studyRoles CALL { WITH orgs UNWIND orgs AS o OPTIONAL MATCH (o)-[:ORGANIZATION_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN o.tildaid AS orgId, collect(DISTINCT u.tildaid) AS adminTildaid } WITH rows, sites, studyRoles, collect({orgId: orgId, admin: adminTildaid}) AS orgRoles CALL { WITH sites UNWIND (CASE WHEN size(sites) = ? THEN [null] ELSE sites END) AS scopeSite OPTIONAL MATCH (scopeSite)-[:STAFF_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN scopeSite.tildaid AS siteId, collect(DISTINCT u.tildaid) AS siteAdminTildaid } WITH rows, studyRoles, orgRoles, collect({siteId: siteId, admin: siteAdminTildaid}) AS siteRoles UNWIND rows AS row WITH row, [x IN studyRoles WHERE x.studyId = row.study.tildaid][?] AS sr, [x IN orgRoles WHERE x.orgId = row.o.tildaid][?] AS orr, [x IN siteRoles WHERE row.scopeSite IS NOT NULL AND x.siteId = row.scopeSite.tildaid][?] AS str RETURN DISTINCT row.t AS task, apoc.convert.toJson({userTildaid: row.user.tildaid, userName: row.user.name, PITildaid: sr.pi, studyOwnerTildaid: sr.smo, regAnalystTildaid: sr.ra, orgAdminTildaid: orr.admin, studyName: row.study.name, siteName: row.scopeSite.name, siteAdminTildaid: CASE WHEN row.scopeSite IS NULL THEN [] ELSE str.admin END}) AS details, row.tc.who AS dsnWho, row.o.alias AS orgAlias, row.taskInbox, row.taskUser.tildaid AS taskUserId
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
3
251 ms
925 ms
709 ms
flat
MATCH (root:GraphNode{tildaid:$graphId}) OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2) WITH root, collect(DISTINCT var) as graphVars MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNo…
MATCH (root:GraphNode{tildaid:$graphId}) OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2) WITH root, collect(DISTINCT var) as graphVars MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNode) WHERE size(rr) = ? OR rr[-?].path[?] = root.tildaid OPTIONAL MATCH (gnode)-[:GRAPH_NODE_TO_PAGE{graphid:root.tildaid}]->(page:GraphPage) OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:$relPropId}]->(refNode:GraphNode) WHERE gnode.objtype = ? OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_QUESTION{protocolid:$relPropId}]->(question:QuestionV2) WHERE refNode.objtype = ? WITH root, gnode, page, refNode, case when refNode.objtype = ? then question when refNode.objtype = ? then refNode else null end as question, rr, graphVars OPTIONAL MATCH (question)-[:QUESTION_TO_QUESTION_SPEC]->(questionSpec:QuestionSpec) OPTIONAL MATCH (gnode)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{protocolid:$relPropId}]- (nodeBranchCfg:BranchConfig)-[:BRANCH_TO_BRANCH_CONFIG{protocolid:$relPropId}]- (nodeBranch:BranchV2) OPTIONAL MATCH (gnode)-[cfgRel:BRANCH_CFG_TO_GRAPH_NODE_REF{protocolid:$relPropId}]- (:BranchConfig)-[:ACTIVATION_RULE_TO_BRANCH_CONFIG{graphid:cfgRel.graphid}]- (:ActivationRule)-[:ACTIVATION_RULE_TO_CHAIN{graphid:cfgRel.graphid}]- (nodeChain:ActivationRuleChain) OPTIONAL MATCH (gnode)-[:BRANCH_TO_BRANCH_CONFIG{graphid:root.tildaid, protocolid:$relPropId}]->(cfg:BranchConfig) WHERE gnode.objtype = ? OPTIONAL MATCH (cfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{protocolid:$relPropId}]->(cfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[rel:QUESTION_TO_AUTO_COMPUTE_CFG|QUESTION_TO_CORRECTNESS_CHECK{protocolid:$relPropId}]->(acc:AutoComputeConfig) OPTIONAL MATCH (acc)-[:AUTO_COMPUTE_TO_BRANCH_CONFIG]->(acBranchCfg:BranchConfig) OPTIONAL MATCH (acBranchCfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{protocolid:$relPropId}]->(acCfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[:QUESTION_TO_AUTO_QUERY{protocolid:$relPropId}]->(aqcfg:AutoQueryConfig{enabled:true}) OPTIONAL MATCH (aqcfg)-[:AUTO_QUERY_TO_BRANCH_CONFIG]->(aqBranchCfg:BranchConfig) WITH gnode, page, refNode, cfg, rr, questionSpec, question, CASE WHEN size(rr) > ? THEN rr[-?].path ELSE [root.tildaid] END as nodePath, CASE WHEN size(rr) > ? THEN rr[-?].flags ELSE ? END as nodeFlags, CASE WHEN size(rr) > ? THEN rr[-?].who ELSE [] END as nodeWho, CASE WHEN type(rel) = ? THEN true ELSE false END as isCorrectnessCheckConfig, collect(DISTINCT cfgRef.tildaid) as cfgRefIds, collect(DISTINCT nodeBranch.tildaid) as branchIds, collect(DISTINCT nodeChain.tildaid) as activationChainIds, acc, acBranchCfg, collect(DISTINCT acCfgRef.tildaid) as autoComputeBranchRefIds, graphVars, collect(DISTINCT CASE WHEN aqcfg IS NOT NULL THEN {aqcfg: aqcfg, aqBranchCfg: aqBranchCfg} END) as autoQueryConfigs WITH gnode as node, page, refNode, cfg as branchCfg, cfgRefIds as branchRefIds, questionSpec, question, branchIds, activationChainIds, nodePath as path, gnode.objtype as objType, nodeFlags as flags, nodeWho as who, acc as autoComputeConfig, acBranchCfg as autoComputeBranchCfg, autoComputeBranchRefIds, autoQueryConfigs, isCorrectnessCheckConfig, graphVars RETURN {node:node, page:page, refNode:refNode, questionSpec:questionSpec, question:question, branchCfg:branchCfg, branchRefIds:branchRefIds, path:path, objType:objType, flags:flags, who:who, branchIds:branchIds, activationChainIds:activationChainIds, autoComputeConfig:autoComputeConfig, autoComputeBranchCfg:autoComputeBranchCfg, autoComputeBranchRefIds:autoComputeBranchRefIds, autoQueryConfigs:autoQueryConfigs, isCorrectnessCheckConfig:isCorrectnessCheckConfig, graphVars:graphVars}
execsvc
6
263 ms
925 ms
880 ms
flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
6
186 ms
925 ms
543 ms
flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
3
288 ms
925 ms
850 ms
flat
// First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDe…
// First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDefinition{type:?}) match (sitePd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]->(w:WorkflowV2)<-[:TRIGGER_TO_WORKFLOW]-(t:Trigger{scope:?}) match (sitePd)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (sitePd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) match (scopeUser)-[:STUDYSITEACTOR_TO_ACTOR]->(actor:Actor{delpending:false})<-[:STUDY_TO_ACTOR]-(st) where not exists((scopeUser)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) return distinct scopeUser.tildaid as scopeTildaid, t.scope as workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, scopeUser.roles as roles UNION // Second part for StudySite scope match (t:Trigger{scope:?})-[:TRIGGER_TO_WORKFLOW]->(w:WorkflowV2) match (w)<-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]-(scopeSite:DslPlanDefinition{type:?}) where not exists((scopeSite)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) with distinct t.scope as workflowScope, scopeSite match (scopeSite)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (scopeSite)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) return distinct scopeSite.tildaid as scopeTildaid, workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, null as roles
writedashql
38
196 ms
910 ms
3.06 s
0.0× ↓
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Organization policies=org-resources MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANI…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Organization policies=org-resources MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_0 MATCH (o)-[:ORGANIZATION_TO_PATIENT]->(p2:Patient {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-resources MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) MATCH (o)-[:ORGANIZATION_TO_PATIENT]->(p2:Patient {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
patientsvc
4
229 ms
900 ms
887 ms
flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
5
422 ms
875 ms
518 ms
flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
6
152 ms
850 ms
824 ms
flat
match (pv:ParticipantVisit)-[:BELONGS_TO_PARTICIPANT_VISIT]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(c:CarePlan)-[:RESEARCH_SUBJECT] -(p:Patient)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) where c.status IN [?,?] and v.startTime >= …
match (pv:ParticipantVisit)-[:BELONGS_TO_PARTICIPANT_VISIT]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(c:CarePlan)-[:RESEARCH_SUBJECT] -(p:Patient)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) where c.status IN [?,?] and v.startTime >= datetime({epochMillis:$day1Start}) and v.startTime <= datetime({epochMillis:$day1End}) and coalesce(v.reminderStatus, ?) = ? match (c)-[:EXECUTE_SOA]-(s:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION] -(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(si:Site)-[:SITE_TO_SMO]->(smo:Smo)-[:IS_AN_ORG]->(o:Organization) return v,phi,si,p,o
schedsvc
6
151 ms
850 ms
785 ms
flat
// First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDe…
// First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDefinition{type:?}) match (sitePd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]->(w:WorkflowV2)<-[:TRIGGER_TO_WORKFLOW]-(t:Trigger{scope:?}) match (sitePd)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (sitePd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) match (scopeUser)-[:STUDYSITEACTOR_TO_ACTOR]->(actor:Actor{delpending:false})<-[:STUDY_TO_ACTOR]-(st) where not exists((scopeUser)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) return distinct scopeUser.tildaid as scopeTildaid, t.scope as workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, scopeUser.roles as roles UNION // Second part for StudySite scope match (t:Trigger{scope:?})-[:TRIGGER_TO_WORKFLOW]->(w:WorkflowV2) match (w)<-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]-(scopeSite:DslPlanDefinition{type:?}) where not exists((scopeSite)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) with distinct t.scope as workflowScope, scopeSite match (scopeSite)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (scopeSite)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) return distinct scopeSite.tildaid as scopeTildaid, workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, null as roles
regsvc
9
78 ms
775 ms
643 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
12
89 ms
700 ms
561 ms
flat
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(a…
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) WITH ph, e, es, del, acceptedDoc, coalesce(acceptedDoc, ph) AS d OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (siteFromEvent: Site{tildaid: es.scopeId}) WHERE es.scopeType = ? OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) where da.key in [?, ?, ?, ?] OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: User) OPTIONAL MATCH (study) - [:STUDY_TO_ACTOR] - (actor: Actor{userId: uploader.tildaid}) WITH d, e, es, del, b, study, b2, coalesce(site, siteFromEvent) AS site, o, head(collect(da)) as da, currentFolder, currentSubArtifact, stagingInfo, artifact, section, zone, collection, subArtifact, uploader, ph, acceptedDoc, head(collect(actor)) as actor CALL apoc.create.vNode([?], { tildaid: ph.tildaid, createdat: ph.createdat, updatedat: ph.updatedat, documentId: acceptedDoc.tildaid, documentCreatedAt: acceptedDoc.createdat, documentUpdatedAt: acceptedDoc.updatedat, orgName: o.name, orgId: o.tildaid, orgAlias: o.alias, studyName: study.name, studyId: study.tildaid, siteName: site.name, siteId: site.tildaid, documentStatus: d.status, country: CASE WHEN es.scopeType = ? THEN coalesce(es.scopeId, d.country) ELSE d.country END, languages: coalesce(apoc.convert.toJson(d.languages), ?), fileName: d.fileName, uploadedByName: uploader.name, uploadedById: uploader.tildaid, collection: d.collection, stateTag: d.stateTag, documentExpiryDate: da.value, zone: zone.name, zoneId: zone.tildaid, zoneNumber: case when zone.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) end, section: section.name, sectionId: section.tildaid, sectionNumber: case when section.number is null then null else apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) end, artifact: artifact.name, artifactId: artifact.tildaid, artifactNumber: case when artifact.number is null then null else apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) end, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, eventId: e.tildaid, eventCreatedAt: e.createdat, eventType: e.type, eventName: e.name, eventStatus: e.status, eventScopeId: es.tildaid, eventScopeName: es.name, scopeType: es.scopeType, eventScopeDecision: es.decision, deliverableId: del.tildaid, deliverableName: del.name, deliverableLocationId: del.locationId, deliverableLocationType: del.locationType, deliverableType: del.type, roles: actor.roles }) YIELD node return node
writedashrsvc
110
61 ms
656 ms
870 ms
flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
28
192 ms
488 ms
717 ms
flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
1
319 ms
485 ms
319 ms
flat
MATCH (wo:WorkOrder {tildaid: $id}) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_SITE]->(site:Site) // Parent WorkOrderC…
MATCH (wo:WorkOrder {tildaid: $id}) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_SITE]->(site:Site) // Parent WorkOrderConfig (WorkOrder.parent_id -> WorkOrderConfig). Null for standalone WOs. // Carries the ENG-? ? timing, denormalized onto each child WO below. OPTIONAL MATCH (cfg:WorkOrderConfig {tildaid: wo.parent_id}) // Template lineage via the same config. Null for ad-hoc configs (no template). OPTIONAL MATCH (cfg)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) CALL apoc.create.vNode([?], { tildaid: wo.tildaid, workOrderId: wo.work_order_id, workOrderType: wo.work_order_type, title: wo.title, status: wo.status, priority: wo.priority, goal: wo.goal, emailSubject: wo.email_subject, summary: wo.summary, forms: wo.forms, creatorId: wo.creator_id, assignedToId: wo.assigned_to_id, resolvedBy: wo.resolved_by, parentId: wo.parent_id, eventId: wo.event_id, templateId: tmpl.tildaid, templateName: tmpl.title, threadId: wo.thread_id, rfcMsgId: wo.rfc_msgid, createdat: wo.createdat, updatedat: wo.updatedat, lastReachout: wo.last_reachout, lastResponse: wo.last_response, dueDate: wo.due_date, resolvedAt: wo.resolved_at, statusUpdatedAt: wo.status_updated_at, activatedAt: wo.activated_at, // ENG-? ? timing, denormalized from the parent WorkOrderConfig (cfg). // configActivatedAt is distinct from activatedAt above (the child WO?t trip Neo4j?RECEIVED?RECEIVED?Received?Accepted?SENT?RECEIVED?RECEIVED' | wd]), contactList: wo.contact_list, ccList: wo.cc_list, delpending: wo.delpending, deltimestamp: wo.deltimestamp, holdbitmap: wo.holdbitmap, orgId: coalesce(org.tildaid, wo.org_id), orgName: org.name, orgAlias: org.alias, studyId: coalesce(study.tildaid, wo.study_id), studyName: study.name, siteId: coalesce(site.tildaid, wo.site_id), siteName: site.name } ) YIELD node RETURN node
writedashrsvc
1
201 ms
485 ms
201 ms
flat
MATCH (a:WorkOrderActivity {tildaid: $id}) WHERE a.activity_type <> ? OPTIONAL MATCH (wo:WorkOrder)-[:WORK_ORDER_TO_ACTIVITY]->(a) WITH a, head(collect(wo)) AS wo OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTION…
MATCH (a:WorkOrderActivity {tildaid: $id}) WHERE a.activity_type <> ? OPTIONAL MATCH (wo:WorkOrder)-[:WORK_ORDER_TO_ACTIVITY]->(a) WITH a, head(collect(wo)) AS wo OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_SITE]->(site:Site) // Template lineage of the parent WO (parent_id -> WorkOrderConfig -> WorkOrderTemplate), denormalized. OPTIONAL MATCH (:WorkOrderConfig {tildaid: wo.parent_id})-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) CALL apoc.create.vNode([?], { tildaid: a.tildaid, workOrderActivityId: a.tildaid, workOrderId: a.work_order_id, userId: a.user_id, activityType: a.activity_type, status: a.status, source: a.source, content: a.content, original_content: a.original_content, reasoning: a.reasoning, content_type: a.content_type, body_plain: a.body_plain, body_html: a.body_html, stripped_text: a.stripped_text, stripped_html: a.stripped_html, extra_text: a.extra_text, extra_html: a.extra_html, message_id: a.message_id, sender: a.sender, from_ai_teammate_email: a.from_ai_teammate_email, from_ai_teammate_name: a.from_ai_teammate_name, attachmentInfo: a.attachmentInfo, ignoreType: a.ignoreType, auto_send_enabled: a.auto_send_enabled, documents_pending: a.documents_pending, processing_status: a.processing_status, retry_count: a.retry_count, last_attempt_at: a.last_attempt_at, openai_response_id: a.openai_response_id, errors: a.errors, questions: a.questions, answers: a.answers, answered_at: a.answered_at, agent_run_completed_at: a.agent_run_completed_at, timestamp: a.timestamp, createdat: a.createdat, updatedat: a.updatedat, // Parent work order context: immutable created timestamp (epoch millis) + business id, denormalized // so time-to-response style metrics can be computed from the activities table alone. workOrderCreatedAt: wo.createdat, workOrderBusinessId: wo.work_order_id, workOrderName: wo.title, templateId: tmpl.tildaid, templateName: tmpl.title, docIds: a.docIds, processedDocIds: a.processedDocIds, activity_contact_list: a.activity_contact_list, activity_cc_list: a.activity_cc_list, delpending: a.delpending, deltimestamp: a.deltimestamp, holdbitmap: a.holdbitmap, orgId: coalesce(org.tildaid, wo.org_id), orgName: org.name, orgAlias: org.alias, studyId: coalesce(study.tildaid, wo.study_id), studyName: study.name, siteId: coalesce(site.tildaid, wo.site_id), siteName: site.name } ) YIELD node RETURN node
writedashrsvc
1
240 ms
485 ms
240 ms
flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE COALESCE(tracksRel.state, ?) in $userDecisions OPTIONAL MATCH (ph)-[:SCOPED_TO]->(es:EventSc…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE COALESCE(tracksRel.state, ?) in $userDecisions OPTIONAL MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVENT]->(e) OPTIONAL MATCH (wo:WorkOrder)-[woClaim:WO_CLAIMS_PLACEHOLDER]->(ph) WHERE woClaim.is_current = true WITH e, ph, d, es, tracksRel, head(collect(wo)) AS wo OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND matchDoc.collection = ? THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as matchesExpectedCollected, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND (matchDoc.collection IS NULL OR matchDoc.collection <> ?) THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as pendingMatchesCollected WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, [m IN matchesExpectedCollected WHERE m IS NOT NULL] as matchesExpected, [m IN pendingMatchesCollected WHERE m IS NOT NULL] as pendingMatches WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchesExpected) > ? AND SIZE(pendingMatches) = ? THEN ? WHEN SIZE(matchesExpected) > ? OR SIZE(pendingMatches) > ? THEN ? ELSE ? END as matchStatus OPTIONAL MATCH (ph)-[renewalRel:RENEWAL_OF]->(renewalSrcCandidate:Document) WHERE coalesce(renewalRel.primary, false) = true AND coalesce(renewalSrcCandidate.archived, false) = false WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, head(collect(renewalSrcCandidate)) AS renewalSrc OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study) OPTIONAL MATCH (site:Site {tildaid: es.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, renewalSrc, COALESCE(head(collect(site.name)), ?) AS siteName, COALESCE(head(collect(pd.siteNumber)), ?) AS siteNumber ORDER BY d.name ASC, ph.fileName ASC WITH collect({ placeholderId: ph.tildaid, placeholderName: ph.fileName, deliverableId: d.tildaid, deliverableName: d.name, eventScopeId: es.tildaid, scopeType: es.scopeType, country: CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END, siteNumber: siteNumber, siteName: siteName, dueDate: es.dueDate, matchStatus: matchStatus, userDecision: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR es.status = ?, origin: COALESCE(tracksRel.origin, ?), feedback: COALESCE(tracksRel.feedback, ?), reason: COALESCE(tracksRel.reason, ?), removalHasAcceptedDocument: COALESCE(tracksRel.removalHasAcceptedDocument, false), workOrder: CASE WHEN wo IS NOT NULL THEN {tildaid: wo.tildaid, work_order_id: wo.work_order_id, status: wo.status} ELSE null END, renewalSource: CASE WHEN renewalSrc IS NOT NULL THEN {documentId: renewalSrc.tildaid, documentName: renewalSrc.fileName} ELSE null END, acceptedMatch: CASE WHEN acceptedDoc IS NOT NULL THEN { documentId: acceptedDoc.tildaid, displayId: acceptedDoc.displayId, contentType: acceptedDoc.contentType, documentName: acceptedDoc.fileName, documentDate: CASE WHEN acceptedDoc.createdat IS NOT NULL THEN datetime({epochMillis: acceptedDoc.createdat}) ELSE null END, isAccepted: true, matchedBy: COALESCE(head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | amr.matchedBy ]), ?), matchedAt: head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | CASE WHEN amr.createdat IS NOT NULL THEN datetime({epochMillis: amr.createdat}) ELSE null END ]), acceptedBy: acceptedRel.acceptedBy, acceptedAt: CASE WHEN acceptedRel.createdat IS NOT NULL THEN datetime({epochMillis: acceptedRel.createdat}) ELSE null END, docAttributes: [ (acceptedDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: acceptedDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } ELSE null END, matchesExpected: matchesExpected, pendingMatches: pendingMatches }) as allRows WITH allRows, allRows as filteredRows WITH allRows, filteredRows, { total: size(allRows), pending: size([pp IN allRows WHERE pp.userDecision IN [?, ?]]), pendingForDelete: size([pp IN allRows WHERE pp.userDecision = ?]), accepted: size([pp IN allRows WHERE pp.userDecision = ?]), rejected: size([pp IN allRows WHERE pp.userDecision = ?]), incomplete: size([pp IN allRows WHERE pp.matchStatus = ?]), potentialMatchFound: size([pp IN allRows WHERE pp.matchStatus = ?]), matchExpected: size([pp IN allRows WHERE pp.matchStatus = ?]), matched: size([pp IN allRows WHERE pp.matchStatus = ?]), finalized: size([pp IN allRows WHERE pp.matchStatus = ?]), completion: {required: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]), completed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]), aiSuggested: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), inStaging: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), noMatchFound: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), notConfirmed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND pp.userDecision = ? AND NOT pp.matchStatus = ?]), removed: size([pp IN allRows WHERE (pp.userDecision = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) / size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]))) END, isComplete: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) > ? AND size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) = size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)])}, matchedWithNew: size([pp IN allRows WHERE pp.matchStatus = ? AND (size(pp.pendingMatches) > ? OR size(pp.matchesExpected) > ?)]) } as progress RETURN filteredRows[$pageSkip..$pageEnd] as placeholders, size(filteredRows) as total, progress
docsvc
1
204 ms
485 ms
204 ms
flat
match(:User{email:$staffEmail})-[]-(:Staff)-[:W2026_46 | W2026_47]-(r:ResourceAppointment) where (isEmpty(r.type) or r.type=?) optional match (r)-[:RESOURCE_APPOINTMENTS]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(:CarePlan)-[…
match(:User{email:$staffEmail})-[]-(:Staff)-[:W2026_46 | W2026_47]-(r:ResourceAppointment) where (isEmpty(r.type) or r.type=?) optional match (r)-[:RESOURCE_APPOINTMENTS]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(:CarePlan)-[:RESEARCH_SUBJECT]-(:Patient)-[:HAS_PROTECTED_DATA_IN]-(p:Phi) optional match (v)-[]-(s:Site)-[:SITE_TO_SMO]->(smo:Smo)-[:IS_AN_ORG]->(o:Organization{alias:$orgAlias}) optional match (v)-[:VISIT_APPOINTMENT_TO_CONFERENCE_DATA]-(c:ConferenceData) return r,p,c,v,s.tildaid
schedsvc
1
276 ms
485 ms
276 ms
flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
3
437 ms
485 ms
479 ms
flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study??ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess??Inactive? ?Received?Quality Control?PHI Issues???/???/???/') ELSE ? END as childFolderPath WITH docObj, ct, child, st, head(collect(childFolderPath)) AS childFolderPath WITH child, CASE WHEN ct IS NULL THEN null ELSE { documentId: child.tildaid, studyId: COALESCE(child.studyId, ?), studyName: COALESCE(st.name, ?), folderPath: childFolderPath, kind: ct.kind, stale: ct.sourceVersionId <> docObj.primaryVersionId, version: COALESCE(child.versionName, toString(child.majorVersion)) } END as copiedToEntry RETURN collect(copiedToEntry) as copiedTo, count(DISTINCT child) as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { return null as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
3
381 ms
485 ms
386 ms
flat
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
docsvc
8
248 ms
476 ms
423 ms
flat
UNWIND $rows AS row MATCH (t:Task{tildaid:row.id}) SET t.dist_miss_count = row.count, t.dist_next_retry_at = row.nextRetryAt, t.dist_deferred = row.deferred
UNWIND $rows AS row MATCH (t:Task{tildaid:row.id}) SET t.dist_miss_count = row.count, t.dist_next_retry_at = row.nextRetryAt, t.dist_deferred = row.deferred
writetusker
2
180 ms
470 ms
255 ms
flat
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
docsvc
4
182 ms
470 ms
304 ms
flat
MATCH(lead:Lead {tildaid: $id}) MATCH (lead)-[:LEAD_TO_STUDY]-(study:Study) match (lead)-[:LEAD_TO_PATIENT]->(patient:Patient) MATCH(org:Organization) WHERE org.tildaid=study.srcorgid OPTIONAL MATCH (study)-[:PLAN_DEFINITION_TO_STUDY]-(dsl…
MATCH(lead:Lead {tildaid: $id}) MATCH (lead)-[:LEAD_TO_STUDY]-(study:Study) match (lead)-[:LEAD_TO_PATIENT]->(patient:Patient) MATCH(org:Organization) WHERE org.tildaid=study.srcorgid OPTIONAL MATCH (study)-[:PLAN_DEFINITION_TO_STUDY]-(dsl:DslPlanDefinition)-[:SOA_TO_PLAN_DEFINITION]-(:ScheduleOfActivities)-[:EXECUTE_SOA]-(:CarePlan)-[:RESEARCH_SUBJECT]-(patient) OPTIONAL MATCH (dsl)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) OPTIONAL MATCH (lead)-[:LEAD_TO_ADVOCATE]-(adv:Advocate)-[:ADV_TO_PORTAL_ORG]-(advOrg:AdvOrg) OPTIONAL MATCH (user:User{tildaid: adv.userid}) OPTIONAL MATCH (recruiterOrg:Organization{tildaid: adv.orgid}) CALL apoc.create.vNode([?], { studyName: study.name, orgId: org.tildaid, orgName: org.name, orgAlias: org.alias, siteId: site.tildaid, siteName: site.name, createdat: lead.createdat, updatedat: lead.updatedat, sourceType: lead.sourcetype, status: lead.status, leadId: lead.tildaid, patientId: lead.patientid, studyId: lead.studyid, recruiterOrg: recruiterOrg.name, recruiter: user.name, sex: patient.sex, ethnicity: patient.ethnicity } ) YIELD node RETURN node
writedashrsvc
2
124 ms
470 ms
247 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
2
141 ms
470 ms
240 ms
flat
// The site???s log win date authority over this roster. UNWIND (CASE WHEN size(candNums) = ? THEN [null] ELSE candNums END) AS n OPTIONAL MATCH (pdAmb:DslPlanDefinition {studyId: $studyId})-[:PLAN_DEFINITION_TO_SITE]-(sAmb:Site) WHERE n I…
// The site???s log win date authority over this roster. UNWIND (CASE WHEN size(candNums) = ? THEN [null] ELSE candNums END) AS n OPTIONAL MATCH (pdAmb:DslPlanDefinition {studyId: $studyId})-[:PLAN_DEFINITION_TO_SITE]-(sAmb:Site) WHERE n IS NOT NULL AND trim(coalesce(pdAmb.siteNumber, ?)) = n AND coalesce(pdAmb.delpending, false) = false AND coalesce(sAmb.delpending, false) = false WITH n, size(collect(DISTINCT sAmb)) AS nSites // collect() drops nulls, so an ambiguous or unmatched number falls out here. WITH collect(CASE WHEN nSites = ? THEN n END) AS siteNums MATCH (d:Document {studyId: $studyId}) // coalesce(), not bare `= false` like DocumentFetcher.fetch: this list must not // silently drop a document that lacks the archived property entirely. WHERE coalesce(d.delpending, false) = false AND coalesce(d.archived, false) = false // An EDL placeholder (?) has no file to read. AND NOT EXISTS { (d)<-[:TRACKS_PLACEHOLDER]-() } AND ( d.siteId = $siteId OR ( (d.siteId IS NULL OR d.siteId = ?) AND trim(split(coalesce(d.fileName, ?), ?)[?]) <> ? AND trim(split(coalesce(d.fileName, ?), ?)[?]) IN siteNums ) ) OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(saT:SubArtifact) OPTIONAL MATCH (d)<-[:RECOMMENDATION_TO_DOCUMENT]-(saR:Recommendation) WHERE saR.category = ? AND coalesce(saR.delpending, false) = false WITH d, saT, saR ORDER BY coalesce(saR.createdat, ?) DESC WITH d, saT, head(collect(saR)) AS lastSaR WITH d, coalesce(saT.name, lastSaR.data) AS subArtifactName WHERE subArtifactName IN $doaNames // The metadata agent?Document Metadata??' WITH d, subArtifactName, md ORDER BY coalesce(md.createdat, ?) DESC WITH d, subArtifactName, head(collect(md)) AS lastMd RETURN d.tildaid AS docId, subArtifactName AS title, d.fileName AS fileName, d.primaryVersionId AS primaryVersionId, lastMd.data_formatted AS metadataJson, d.cksum AS cksum, coalesce(d.createdat, ?) AS createdAt
writeontologysvc
2
144 ms
470 ms
274 ms
flat
// The site???s log win date authority over this roster. UNWIND (CASE WHEN size(candNums) = ? THEN [null] ELSE candNums END) AS n OPTIONAL MATCH (pdAmb:DslPlanDefinition {studyId: $studyId})-[:PLAN_DEFINITION_TO_SITE]-(sAmb:Site) WHERE n I…
// The site???s log win date authority over this roster. UNWIND (CASE WHEN size(candNums) = ? THEN [null] ELSE candNums END) AS n OPTIONAL MATCH (pdAmb:DslPlanDefinition {studyId: $studyId})-[:PLAN_DEFINITION_TO_SITE]-(sAmb:Site) WHERE n IS NOT NULL AND trim(coalesce(pdAmb.siteNumber, ?)) = n AND coalesce(pdAmb.delpending, false) = false AND coalesce(sAmb.delpending, false) = false WITH n, size(collect(DISTINCT sAmb)) AS nSites // collect() drops nulls, so an ambiguous or unmatched number falls out here. WITH collect(CASE WHEN nSites = ? THEN n END) AS siteNums MATCH (d:Document {studyId: $studyId}) // coalesce(), not bare `= false` like DocumentFetcher.fetch: this list must not // silently drop a document that lacks the archived property entirely. WHERE coalesce(d.delpending, false) = false AND coalesce(d.archived, false) = false AND ( d.siteId = $siteId OR ( (d.siteId IS NULL OR d.siteId = ?) AND trim(split(coalesce(d.fileName, ?), ?)[?]) <> ? AND trim(split(coalesce(d.fileName, ?), ?)[?]) IN siteNums ) ) OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(saT:SubArtifact) OPTIONAL MATCH (d)<-[:RECOMMENDATION_TO_DOCUMENT]-(saR:Recommendation) WHERE saR.category = ? AND coalesce(saR.delpending, false) = false WITH d, saT, saR ORDER BY coalesce(saR.createdat, ?) DESC WITH d, saT, head(collect(saR)) AS lastSaR WITH d, coalesce(saT.name, lastSaR.data) AS subArtifactName WHERE subArtifactName IN $doaNames // The metadata agent?Document Metadata??' WITH d, subArtifactName, md ORDER BY coalesce(md.createdat, ?) DESC WITH d, subArtifactName, head(collect(md)) AS lastMd RETURN d.tildaid AS docId, subArtifactName AS title, d.fileName AS fileName, d.primaryVersionId AS primaryVersionId, lastMd.data_formatted AS metadataJson, d.cksum AS cksum, coalesce(d.createdat, ?) AS createdAt
writeontologysvc
2
148 ms
470 ms
280 ms
flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
7
174 ms
465 ms
323 ms
flat
match (pd:DslPlanDefinition{tildaid: $pdId}) match (org:Organization{tildaid: $orgId})-[:STUDY_TO_ORG]-(st:Study{tildaid: $studyId}) call (org,st,pd) { match (pd)-[:PLAN_DEFINITION_TO_ACTIVITY]->(av:ActivityV2 ) where av.deltimestamp = ? a…
match (pd:DslPlanDefinition{tildaid: $pdId}) match (org:Organization{tildaid: $orgId})-[:STUDY_TO_ORG]-(st:Study{tildaid: $studyId}) call (org,st,pd) { match (pd)-[:PLAN_DEFINITION_TO_ACTIVITY]->(av:ActivityV2 ) where av.deltimestamp = ? and av.activityType = $activityType return null as site, null as r, av union match (st)-[:PLAN_DEFINITION_TO_STUDY]-(sitepd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) match (sitepd)-[r:PLAN_DEFINITION_TO_ADOPTED_ACTIVITIES|PLAN_DEFINITION_TO_PUBLISHED_ACTIVITIES|PLAN_DEFINITION_TO_PARENT_PUBLISHED_ACTIVITIES]-(av:ActivityV2 ) where av.deltimestamp = ? and av.activityType = $activityType return site, r, av } optional match (av)-[:ALL_CHANGELOG]->(cl:ChangeLog) WITH av, site, r, cl ORDER BY cl.createdat asc WITH av, site, r, head(collect(cl)) as cl optional match (u:User{tildaid:cl.who}) with av, site, r, case when u is not null then u.name else head(av.userNames) end as user RETURN distinct av, user, collect({site:site, r:type(r)})
execsvc
5
87 ms
462 ms
216 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
schedsvc
5
133 ms
462 ms
325 ms
flat
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, name: s.name, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
studiosvc
3
155 ms
455 ms
370 ms
flat
MATCH(lead:Lead {tildaid: $id}) MATCH (lead)-[:LEAD_TO_STUDY]-(study:Study) match (lead)-[:LEAD_TO_PATIENT]->(patient:Patient) MATCH(org:Organization) WHERE org.tildaid=study.srcorgid OPTIONAL MATCH (study)-[:PLAN_DEFINITION_TO_STUDY]-(dsl…
MATCH(lead:Lead {tildaid: $id}) MATCH (lead)-[:LEAD_TO_STUDY]-(study:Study) match (lead)-[:LEAD_TO_PATIENT]->(patient:Patient) MATCH(org:Organization) WHERE org.tildaid=study.srcorgid OPTIONAL MATCH (study)-[:PLAN_DEFINITION_TO_STUDY]-(dsl:DslPlanDefinition)-[:SOA_TO_PLAN_DEFINITION]-(:ScheduleOfActivities)-[:EXECUTE_SOA]-(:CarePlan)-[:RESEARCH_SUBJECT]-(patient) OPTIONAL MATCH (dsl)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) OPTIONAL MATCH (lead)-[:LEAD_TO_ADVOCATE]-(adv:Advocate)-[:ADV_TO_PORTAL_ORG]-(advOrg:AdvOrg) OPTIONAL MATCH (user:User{tildaid: adv.userid}) OPTIONAL MATCH (recruiterOrg:Organization{tildaid: adv.orgid}) CALL apoc.create.vNode([?], { studyName: study.name, orgId: org.tildaid, orgName: org.name, orgAlias: org.alias, siteId: site.tildaid, siteName: site.name, createdat: lead.createdat, updatedat: lead.updatedat, sourceType: lead.sourcetype, status: lead.status, leadId: lead.tildaid, patientId: lead.patientid, studyId: lead.studyid, recruiterOrg: recruiterOrg.name, recruiter: user.name, sex: patient.sex, ethnicity: patient.ethnicity } ) YIELD node RETURN node
writedashrsvc
3
70 ms
455 ms
206 ms
flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
3
191 ms
455 ms
377 ms
flat
MATCH (d:Document {tildaid: $docId}) // An archived DOA log is not the site?Sub Artifact??saT??-????s roster is worse than not running. WITH d, saT, lastSaR, s, fnPrefix, collect(DISTINCT s2) AS fallbackSites WITH d, saT, lastSaR, fnPrefix…
MATCH (d:Document {tildaid: $docId}) // An archived DOA log is not the site?Sub Artifact??saT??-????s roster is worse than not running. WITH d, saT, lastSaR, s, fnPrefix, collect(DISTINCT s2) AS fallbackSites WITH d, saT, lastSaR, fnPrefix, CASE WHEN s IS NOT NULL THEN s WHEN size(fallbackSites) = ? THEN fallbackSites[?] ELSE null END AS site OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {studyId: d.studyId}) WHERE coalesce(pd.delpending, false) = false RETURN coalesce(saT.name, lastSaR.data) AS subArtifactName, d.studyId AS studyId, d.ownerOrgId AS ownerOrgId, site.tildaid AS siteId, coalesce(pd.siteNumber, fnPrefix) AS siteNumber, d.primaryVersionId AS primaryVersionId LIMIT ?
ontologysvc
3
123 ms
455 ms
366 ms
flat
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
docsvc
10
152 ms
450 ms
455 ms
flat
MATCH (n:Site {tildaid: $siteId})-[hs:HAS_STAFF]-(s:Staff)-[:IS_USER]-(u:User)<-[:FOR_USER]-(a:Actor)<-[:STUDY_TO_ACTOR]-(:Study {tildaid: $studyId}) WHERE u.status <> ? AND a.state <> ? MATCH (a)-[:APPOINTED_AS]->(r:Role) OPTIONAL MATCH (…
MATCH (n:Site {tildaid: $siteId})-[hs:HAS_STAFF]-(s:Staff)-[:IS_USER]-(u:User)<-[:FOR_USER]-(a:Actor)<-[:STUDY_TO_ACTOR]-(:Study {tildaid: $studyId}) WHERE u.status <> ? AND a.state <> ? MATCH (a)-[:APPOINTED_AS]->(r:Role) OPTIONAL MATCH (a)-[isPrimary:IS_PRIMARY_ROLE]->(r) OPTIONAL MATCH (a)-[:STUDYSITEACTOR_TO_ACTOR]-(sp:StudySiteActor {delpending: false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(n) OPTIONAL MATCH (sp)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]-(duty:StudySiteDuty) WHERE duty.startDate <= datetime({epochMillis:$startTime}) AND (duty.endDate IS NULL OR duty.endDate >= datetime({epochMillis:$endDate})) RETURN s.tildaid AS id, u.name AS Name, u.email AS Email, s.capabilities AS skillSetList, collect({role: r.name, isPrimary: CASE WHEN isPrimary IS NOT NULL THEN true ELSE false END}) AS Roles, hs.workingWeekDays AS WorkingWeekDaysStr, u.tildaid AS userDBId
schedsvc
4
112 ms
440 ms
355 ms
flat
CALL { MATCH (org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_ACTOR]->(oa:Actor)-[:FOR_USER]->(u:User) WHERE oa.state <> ? AND true AND NOT (u)-[:USER_TO_PATIENT]-(:Patient) WITH u, CASE WHEN u IS NOT NULL AND u.name = ? THEN ? + u.ema…
CALL { MATCH (org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_ACTOR]->(oa:Actor)-[:FOR_USER]->(u:User) WHERE oa.state <> ? AND true AND NOT (u)-[:USER_TO_PATIENT]-(:Patient) WITH u, CASE WHEN u IS NOT NULL AND u.name = ? THEN ? + u.email + ? ELSE u.name END as name WITH CASE WHEN u IS NOT NULL THEN {Name: name, Id: u.tildaid} ELSE NULL END AS user order by name WITH collect(DISTINCT user) AS users, count(DISTINCT user) AS userCount WITH userCount, users UNWIND (CASE users WHEN [] then [null] else users end) AS user WITH userCount, user skip $skip limit $limit RETURN userCount, collect(user) AS users } CALL {MATCH (org:Organization{tildaid:$orgId})<-[:SITE_TO_ORGANIZATION]-(si:Site) WITH org, si, [] AS piNames WHERE true WITH CASE WHEN si IS NOT NULL THEN {Name: si.name, Id: si.tildaid, SiteNumber: ?, PINames: piNames} ELSE NULL END AS site order by site.Name WITH collect(DISTINCT site) AS sites, count(DISTINCT site) AS siteCount WITH siteCount, sites UNWIND (CASE sites WHEN [] then [null] else sites end) AS site WITH siteCount, site skip $skip limit $limit RETURN siteCount, collect(site) AS sites } CALL { MATCH (org:Organization{tildaid:$orgId})<-[:STUDY_TO_ORG]-(s:Study) WHERE s.tildaid in $studyScope AND true MATCH (s)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:org.tildaid})-[:FOR_USER]-(u:User {tildaid: $requesterId}) WITH u, s, CASE WHEN studyActor.isBlinded IS NOT NULL THEN CASE WHEN studyActor.isBlinded = true THEN [ {Name: ?, Id: ?}, {Name: ?, Id: ?}, {Name: ?, Id: ?} ] WHEN studyActor.isBlinded = false THEN [ {Name: ?, Id: ?}, {Name: ?, Id: ?} ] END ELSE [] END AS blindedAccessScopes WITH { Name: s.name, Id: s.tildaid, blindedAccessScopes: CASE WHEN s.isBlinded IS NULL OR s.isBlinded = false THEN [] ELSE blindedAccessScopes END } AS study ORDER BY study.Name WITH collect(DISTINCT study) AS studies, count(DISTINCT study) AS studyCount WITH studyCount, studies UNWIND (CASE studies WHEN [] then [null] else studies end) AS study WITH studyCount, study skip $skip limit $limit RETURN studyCount, collect(study) AS studies } CALL { MATCH (org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_PATIENT]->(p:Patient)-[:HAS_PROTECTED_DATA_IN]->(phi:Phi) WHERE coalesce(p.status, ?) <> ? CALL { RETURN null AS patient } CALL { RETURN null AS subject } WITH collect(DISTINCT patient) AS patients, count(DISTINCT patient) AS patientCount, collect(DISTINCT subject) AS subjects, count(DISTINCT subject) AS subjectCount WITH subjectCount, subjects, patientCount, patients UNWIND (CASE patients WHEN [] then [null] else patients end) AS patient WITH subjectCount, subjects, patientCount, patient skip $skip limit $limit WITH subjectCount, subjects, patientCount, collect(patient) AS patients WITH patientCount, patients, subjectCount, subjects UNWIND (CASE subjects WHEN [] then [null] else subjects end) AS subject WITH patientCount, patients, subjectCount, subject skip $skip limit $limit RETURN patientCount, patients, subjectCount, collect(subject) AS subjects } WITH users, sites, studies, patients, subjects, {studies:studyCount, sites:siteCount, patients:patientCount, subjects: subjectCount, users:userCount} AS counts return distinct {studies:studies, sites:sites, patients:patients, subjects:subjects, users:users, counts: counts}
docsvc
5
69 ms
425 ms
288 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
5
143 ms
425 ms
262 ms
flat
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.dob) contains tolower($phiFinder0) and tolower(phi.firstname) contains tolower($phiFinder1) and tolower(…
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.dob) contains tolower($phiFinder0) and tolower(phi.firstname) contains tolower($phiFinder1) and tolower(phi.lastname) contains tolower($phiFinder2) ) or tolower(phi.email) contains tolower($phiFinder3) with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg optional match(p)-[r:USER_TO_PATIENT]-(u:User) with u.tildaid as patUserId, phi, p, mg optional match(p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with patUserId, phi, p, mg, collect(cond.tildaid) as condIds with * optional match(p)-[:BINDER_FOR]-(:Binder{binderType:?, scope:?})-[:INCLUDES]-(doc:Document{docTypeName:?})-[:BELONGS_TO]-(pdo:ParticipantDocOverlay{participantReviewed:true}) with count(pdo) as pdoCount, phi, p, mg, condIds, patUserId with * optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId}) with * match(phi)-[]-(ehrPhi:EhrPhi) return phi, p, mg, ehrPhi, condIds, patUserId, pdoCount, adv limit $pgLimit
patientsvc
5
57 ms
425 ms
246 ms
flat
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.firstname) contains tolower($phiFinder0) )with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg opti…
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.firstname) contains tolower($phiFinder0) )with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg optional match(p)-[r:USER_TO_PATIENT]-(u:User) with u.tildaid as patUserId, phi, p, mg optional match(p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with patUserId, phi, p, mg, collect(cond.tildaid) as condIds with * optional match(p)-[:BINDER_FOR]-(:Binder{binderType:?, scope:?})-[:INCLUDES]-(doc:Document{docTypeName:?})-[:BELONGS_TO]-(pdo:ParticipantDocOverlay{participantReviewed:true}) with count(pdo) as pdoCount, phi, p, mg, condIds, patUserId with * optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId}) with * match(phi)-[]-(ehrPhi:EhrPhi) return phi, p, mg, ehrPhi, condIds, patUserId, pdoCount, adv limit $pgLimit
patientsvc
6
63 ms
410 ms
291 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
8
120 ms
380 ms
219 ms
flat
UNWIND $taskIds AS tid MATCH (task:Task{tildaid:tid})- [:WITH_CONTEXT]->(pv:ParticipantVisit)<-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)- [:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)…
UNWIND $taskIds AS tid MATCH (task:Task{tildaid:tid})- [:WITH_CONTEXT]->(pv:ParticipantVisit)<-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)- [:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ACTOR]-> (a:Actor)-[:IS_PRIMARY_ROLE]->(r:Role{name:$role}) MATCH (a)-[:FOR_USER]->(user:User) WITH tid, collect(distinct user.email) as emails WHERE size(emails) = ? RETURN tid, emails[?]
tusker
9
75 ms
365 ms
481 ms
flat
match (pv:ParticipantVisit)-[:BELONGS_TO_PARTICIPANT_VISIT]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(c:CarePlan)-[:RESEARCH_SUBJECT] -(p:Patient)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) where c.status IN [?,?] and v.startTime >= …
match (pv:ParticipantVisit)-[:BELONGS_TO_PARTICIPANT_VISIT]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(c:CarePlan)-[:RESEARCH_SUBJECT] -(p:Patient)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) where c.status IN [?,?] and v.startTime >= datetime({epochMillis:$day1Start}) and v.startTime <= datetime({epochMillis:$day1End}) and coalesce(v.reminderStatus, ?) = ? match (c)-[:EXECUTE_SOA]-(s:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION] -(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(si:Site)-[:SITE_TO_SMO]->(smo:Smo)-[:IS_AN_ORG]->(o:Organization) return v,phi,si,p,o
schedsvc
29
47 ms
365 ms
603 ms
flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE d.tildaid in $deliverableIds AND COALESCE(tracksRel.state, ?) in $userDecisions MATCH (ph)-[…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE d.tildaid in $deliverableIds AND COALESCE(tracksRel.state, ?) in $userDecisions MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVENT]->(e) WHERE es.tildaid in $scopeIds OPTIONAL MATCH (wo:WorkOrder)-[woClaim:WO_CLAIMS_PLACEHOLDER]->(ph) WHERE woClaim.is_current = true WITH e, ph, d, es, tracksRel, head(collect(wo)) AS wo OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND matchDoc.collection = ? THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as matchesExpectedCollected, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND (matchDoc.collection IS NULL OR matchDoc.collection <> ?) THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as pendingMatchesCollected WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, [m IN matchesExpectedCollected WHERE m IS NOT NULL] as matchesExpected, [m IN pendingMatchesCollected WHERE m IS NOT NULL] as pendingMatches WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchesExpected) > ? AND SIZE(pendingMatches) = ? THEN ? WHEN SIZE(matchesExpected) > ? OR SIZE(pendingMatches) > ? THEN ? ELSE ? END as matchStatus OPTIONAL MATCH (ph)-[renewalRel:RENEWAL_OF]->(renewalSrcCandidate:Document) WHERE coalesce(renewalRel.primary, false) = true AND coalesce(renewalSrcCandidate.archived, false) = false WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, head(collect(renewalSrcCandidate)) AS renewalSrc OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study) OPTIONAL MATCH (site:Site {tildaid: es.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, renewalSrc, COALESCE(head(collect(site.name)), ?) AS siteName, COALESCE(head(collect(pd.siteNumber)), ?) AS siteNumber ORDER BY d.name ASC, ph.fileName ASC WITH collect({ placeholderId: ph.tildaid, placeholderName: ph.fileName, deliverableId: d.tildaid, deliverableName: d.name, eventScopeId: es.tildaid, scopeType: es.scopeType, country: CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END, siteNumber: siteNumber, siteName: siteName, dueDate: es.dueDate, matchStatus: matchStatus, userDecision: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR es.status = ?, origin: COALESCE(tracksRel.origin, ?), feedback: COALESCE(tracksRel.feedback, ?), reason: COALESCE(tracksRel.reason, ?), removalHasAcceptedDocument: COALESCE(tracksRel.removalHasAcceptedDocument, false), workOrder: CASE WHEN wo IS NOT NULL THEN {tildaid: wo.tildaid, work_order_id: wo.work_order_id, status: wo.status} ELSE null END, renewalSource: CASE WHEN renewalSrc IS NOT NULL THEN {documentId: renewalSrc.tildaid, documentName: renewalSrc.fileName} ELSE null END, acceptedMatch: CASE WHEN acceptedDoc IS NOT NULL THEN { documentId: acceptedDoc.tildaid, displayId: acceptedDoc.displayId, contentType: acceptedDoc.contentType, documentName: acceptedDoc.fileName, documentDate: CASE WHEN acceptedDoc.createdat IS NOT NULL THEN datetime({epochMillis: acceptedDoc.createdat}) ELSE null END, isAccepted: true, matchedBy: COALESCE(head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | amr.matchedBy ]), ?), matchedAt: head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | CASE WHEN amr.createdat IS NOT NULL THEN datetime({epochMillis: amr.createdat}) ELSE null END ]), acceptedBy: acceptedRel.acceptedBy, acceptedAt: CASE WHEN acceptedRel.createdat IS NOT NULL THEN datetime({epochMillis: acceptedRel.createdat}) ELSE null END, docAttributes: [ (acceptedDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: acceptedDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } ELSE null END, matchesExpected: matchesExpected, pendingMatches: pendingMatches }) as allRows WITH allRows, [r IN allRows WHERE r.matchStatus = $rowMatchStatus] as filteredRows WITH allRows, filteredRows, { total: size(allRows), pending: size([pp IN allRows WHERE pp.userDecision IN [?, ?]]), pendingForDelete: size([pp IN allRows WHERE pp.userDecision = ?]), accepted: size([pp IN allRows WHERE pp.userDecision = ?]), rejected: size([pp IN allRows WHERE pp.userDecision = ?]), incomplete: size([pp IN allRows WHERE pp.matchStatus = ?]), potentialMatchFound: size([pp IN allRows WHERE pp.matchStatus = ?]), matchExpected: size([pp IN allRows WHERE pp.matchStatus = ?]), matched: size([pp IN allRows WHERE pp.matchStatus = ?]), finalized: size([pp IN allRows WHERE pp.matchStatus = ?]), completion: {required: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]), completed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]), aiSuggested: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), inStaging: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), noMatchFound: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), notConfirmed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND pp.userDecision = ? AND NOT pp.matchStatus = ?]), removed: size([pp IN allRows WHERE (pp.userDecision = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) / size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]))) END, isComplete: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) > ? AND size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) = size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)])}, matchedWithNew: size([pp IN allRows WHERE pp.matchStatus = ? AND (size(pp.pendingMatches) > ? OR size(pp.matchesExpected) > ?)]) } as progress RETURN filteredRows[$pageSkip..$pageEnd] as placeholders, size(filteredRows) as total, progress
docsvc
13
21 ms
305 ms
242 ms
flat
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.firstname) contains tolower($phiFinder0) and tolower(phi.lastname) contains tolower($phiFinder1) and tol…
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.firstname) contains tolower($phiFinder0) and tolower(phi.lastname) contains tolower($phiFinder1) and tolower(phi.dob) contains tolower($phiFinder2) ) or tolower(phi.email) contains tolower($phiFinder3) with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg optional match(p)-[r:USER_TO_PATIENT]-(u:User) with u.tildaid as patUserId, phi, p, mg optional match(p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with patUserId, phi, p, mg, collect(cond.tildaid) as condIds with * optional match(p)-[:BINDER_FOR]-(:Binder{binderType:?, scope:?})-[:INCLUDES]-(doc:Document{docTypeName:?})-[:BELONGS_TO]-(pdo:ParticipantDocOverlay{participantReviewed:true}) with count(pdo) as pdoCount, phi, p, mg, condIds, patUserId with * optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId}) with * match(phi)-[]-(ehrPhi:EhrPhi) return phi, p, mg, ehrPhi, condIds, patUserId, pdoCount, adv limit $pgLimit
patientsvc
16
42 ms
260 ms
280 ms
flat
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FRO…
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl)-[:PARTICIPANT_CONTROL]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw UNION ALL WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw } WITH DISTINCT study, t, ptc, pw MATCH (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(o:Organization) MATCH (ptc)-[:BELONGS_TO_TASKCONFIG]-(tc:TaskConfig) OPTIONAL MATCH (t)-[:BELONGS_TO]-(taskInbox:Inbox{type:?})--(taskUser:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:Actor{delpending:false})-[:FOR_USER]-(user:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(scopeSite:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(o) WITH DISTINCT study, t, o, tc, taskInbox, taskUser, user, scopeSite SKIP $skip LIMIT $limit WITH collect({study:study, t:t, o:o, tc:tc, taskInbox:taskInbox, taskUser:taskUser, user:user, scopeSite:scopeSite}) AS rows, collect(DISTINCT study) AS studies, collect(DISTINCT o) AS orgs, collect(DISTINCT scopeSite) AS sites CALL { WITH studies UNWIND studies AS study OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(r:Role) WHERE r.name IN [?, ?, ?] RETURN study.tildaid AS studyId, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS piTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS smoTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS raTildaid } WITH rows, orgs, sites, collect({studyId: studyId, pi: piTildaid, smo: smoTildaid, ra: raTildaid}) AS studyRoles CALL { WITH orgs UNWIND orgs AS o OPTIONAL MATCH (o)-[:ORGANIZATION_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN o.tildaid AS orgId, collect(DISTINCT u.tildaid) AS adminTildaid } WITH rows, sites, studyRoles, collect({orgId: orgId, admin: adminTildaid}) AS orgRoles CALL { WITH sites UNWIND (CASE WHEN size(sites) = ? THEN [null] ELSE sites END) AS scopeSite OPTIONAL MATCH (scopeSite)-[:STAFF_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN scopeSite.tildaid AS siteId, collect(DISTINCT u.tildaid) AS siteAdminTildaid } WITH rows, studyRoles, orgRoles, collect({siteId: siteId, admin: siteAdminTildaid}) AS siteRoles UNWIND rows AS row WITH row, [x IN studyRoles WHERE x.studyId = row.study.tildaid][?] AS sr, [x IN orgRoles WHERE x.orgId = row.o.tildaid][?] AS orr, [x IN siteRoles WHERE row.scopeSite IS NOT NULL AND x.siteId = row.scopeSite.tildaid][?] AS str RETURN DISTINCT row.t AS task, apoc.convert.toJson({userTildaid: row.user.tildaid, userName: row.user.name, PITildaid: sr.pi, studyOwnerTildaid: sr.smo, regAnalystTildaid: sr.ra, orgAdminTildaid: orr.admin, studyName: row.study.name, siteName: row.scopeSite.name, siteAdminTildaid: CASE WHEN row.scopeSite IS NULL THEN [] ELSE str.admin END}) AS details, row.tc.who AS dsnWho, row.o.alias AS orgAlias, row.taskInbox, row.taskUser.tildaid AS taskUserId
tusker
1
104 ms
195 ms
104 ms
flat
MATCH (w:WorkOrder {tildaid: $work_order_id})-[:WORK_ORDER_TO_ACTIVITY]->(a:WorkOrderActivity) WHERE ?=? AND coalesce(a.ignoreType,?) <> ? OPTIONAL MATCH (d:Document) WHERE d.tildaid IN COALESCE(a.docIds, []) WITH w, a, COLLECT(DISTINCT {t…
MATCH (w:WorkOrder {tildaid: $work_order_id})-[:WORK_ORDER_TO_ACTIVITY]->(a:WorkOrderActivity) WHERE ?=? AND coalesce(a.ignoreType,?) <> ? OPTIONAL MATCH (d:Document) WHERE d.tildaid IN COALESCE(a.docIds, []) WITH w, a, COLLECT(DISTINCT {tildaid: d.tildaid, fileName: d.fileName, contentType: d.contentType, originalFileName: d.originalFileName, displayId: d.displayId, template: d.template, reviewStrategy: d.reviewStrategy, has_fillable_annotation: CASE WHEN a.status = ? AND a.activity_type IN [?, ?] AND d IS NOT NULL THEN EXISTS { (d)-[:HAS]->(:DocumentAnnotation) } END, annotation_completed: CASE WHEN a.status = ? AND a.activity_type IN [?, ?] AND d IS NOT NULL AND d.reviewStrategy = ? THEN EXISTS { MATCH (d)-[:HAS]->(an:DocumentAnnotation) WHERE an.completedAt > ? } END, wo_copy: CASE WHEN a.status = ? AND a.activity_type IN [?, ?] AND d IS NOT NULL AND w IS NOT NULL THEN d.tildaid IN coalesce(w.template_copy_document_ids, []) END}) as documents // Compute latest AI_MSG draft id per work order for gating REGENERATE OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(ai:WorkOrderActivity) WHERE ai.activity_type = ? AND coalesce(ai.ignoreType,?) <> ? // any status: a newer SENT/QUEUED must supersede an older FindingsChanged draft, else REGENERATE stays stuck; snapshots never count WITH w, a, documents, max(coalesce(ai.updatedat, ai.createdat)) AS latest_any_ts, collect({id: ai.tildaid, ts: coalesce(ai.updatedat, ai.createdat), status: ai.status, ignoreType: ai.ignoreType}) AS ai_msgs WITH a, documents, CASE WHEN size(ai_msgs) = ? THEN false ELSE any(x IN ai_msgs WHERE x.id = a.tildaid AND x.ts = latest_any_ts) END AS is_latest_draft // ENG-?: collect the ordered version chain for this draft?AI_MSG?AI_MSG' AND v.draft_family_id = a.draft_family_id WITH a, documents, is_latest_draft, v ORDER BY v.draft_version ASC WITH a, documents, is_latest_draft, collect(v.tildaid) AS version_ids RETURN a, CASE WHEN size(documents) > ? AND documents[?].tildaid IS NOT NULL THEN documents ELSE [] END AS documents, is_latest_draft, version_ids ORDER BY a.createdat DESC SKIP $skip LIMIT $limit
virtual-assistant
1
137 ms
195 ms
137 ms
flat
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
dashql
1
112 ms
195 ms
112 ms
flat
MATCH (:Study {tildaid: $studyId})-[:STUDY_TO_ACTOR]->(a:Actor)-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WHERE site.tildaid IN $site…
MATCH (:Study {tildaid: $studyId})-[:STUDY_TO_ACTOR]->(a:Actor)-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WHERE site.tildaid IN $siteIds AND coalesce(pd.delpending, false) = false AND coalesce(ssa.delpending, false) = false AND ? IN coalesce(ssa.roles, []) AND coalesce(a.state, ?) <> ? MATCH (a)-[:FOR_USER]->(u:User) WHERE coalesce(u.status, ?) <> ? WITH site.tildaid AS siteId, CASE WHEN trim(coalesce(u.name, ?)) <> ? THEN trim(u.name) WHEN trim(coalesce(u.fullname, ?)) <> ? THEN trim(u.fullname) ELSE trim(coalesce(a.actorName, ?)) END AS name WHERE name <> ? RETURN siteId, min(name) AS name
feedsvc
1
117 ms
195 ms
117 ms
flat
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
schedsvc
1
147 ms
195 ms
147 ms
flat
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND (b.study_id IN $lists.accessible_study_ids OR coalesce(b.study_id, ?) = ?) AND b.status <> ? OPTIONAL MATCH …
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND (b.study_id IN $lists.accessible_study_ids OR coalesce(b.study_id, ?) = ?) AND b.status <> ? OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(wc:WorkOrder) WHERE wc.delpending = false AND wc.status <> ? AND wc.status IN $lists.child_statuses WITH b, count(wc) AS _match_count WHERE _match_count > ? OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_STUDY]->(study:Study) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CREATOR]->(creator:User) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) WITH b, study, creator, tmpl, b.createdat AS _sort_key ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC LIMIT $fetch_limit WITH b, study, creator, tmpl, _sort_key // Resolve WorkPlanInstance id/name for workplan-created configs (wp_object_id set). // Mirrors get_workplan_info_for_work_order_config: cfg.wp_object_id -> milestone item // (by tildaid or cfgMilestoneItemId) -> WorkPlanInstance. Non-workplan configs yield null. OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone) -[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem:WorkplanInstanceMilestoneItem) WHERE coalesce(b.wp_object_id, ?) <> ? AND (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id) WITH b, study, creator, tmpl, _sort_key, head(collect(wpInstance.tildaid)) AS workplan_id, head(collect(wpInstance.name)) AS workplan_name OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(w:WorkOrder) WHERE w.delpending = false AND w.status <> ? AND w.status IN $lists.child_statuses OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(wStudy:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(wSite:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(wCreator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ASSIGNEE]->(wAssignee:User) // Resolve EventScope for Event-type child WOs (null for Standard-type) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_EVENT_SCOPE]->(wES:EventScope) // Look up the Site node only for Site-scoped events; guards against null wES and non-Site scopes. // wES.scopeId is a site tildaid (UUID) for Site scopes, country name for Country, ? for Trial. OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ? OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key}) OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_TASK]->(wTask:WorkOrderTask) WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, w, wStudy, wSite, wCreator, wAssignee, wES, wES_Site, wPartner, wMember, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_pending_count, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_resolved_count // ENG-?: order children in the DB so children_limit keeps the first N in // DISPLAY order. collect() below preserves incoming row order. // The PARENT keyset terms MUST lead. This ORDER BY runs in a WITH stage AFTER // the parent ORDER BY/LIMIT, and a later ORDER BY re-sorts the whole row stream // — ordering on the child value alone silently re-ranked the CONFIGS by their // child?s QUERY order, not display order: the backward // reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC, (w.createdat IS NULL), w.createdat DESC, w.tildaid WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, collect(DISTINCT CASE WHEN w IS NOT NULL THEN { tildaid: w.tildaid, work_order_id: w.work_order_id, title: w.title, work_order_type: w.work_order_type, status: w.status, priority: w.priority, creator_id: w.creator_id, assigned_to_id: w.assigned_to_id, site_id: w.site_id, study_id: w.study_id, document_ids: w.document_ids, contact_list: w.contact_list, cc_list: w.cc_list, goal: w.goal, due_date: w.due_date, last_reachout: w.last_reachout, last_response: w.last_response, resolved_at: w.resolved_at, need_attention_reason: w.need_attention_reason, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
writevirtual-assistant
1
123 ms
195 ms
123 ms
flat
MATCH (wo:WorkOrder {tildaid: $id}) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_SITE]->(site:Site) // Parent WorkOrderC…
MATCH (wo:WorkOrder {tildaid: $id}) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_SITE]->(site:Site) // Parent WorkOrderConfig (WorkOrder.parent_id -> WorkOrderConfig). Null for standalone WOs. // Carries the ENG-? ? timing, denormalized onto each child WO below. OPTIONAL MATCH (cfg:WorkOrderConfig {tildaid: wo.parent_id}) // Template lineage via the same config. Null for ad-hoc configs (no template). OPTIONAL MATCH (cfg)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) CALL apoc.create.vNode([?], { tildaid: wo.tildaid, workOrderId: wo.work_order_id, workOrderType: wo.work_order_type, title: wo.title, status: wo.status, priority: wo.priority, goal: wo.goal, emailSubject: wo.email_subject, summary: wo.summary, forms: wo.forms, creatorId: wo.creator_id, assignedToId: wo.assigned_to_id, resolvedBy: wo.resolved_by, parentId: wo.parent_id, eventId: wo.event_id, templateId: tmpl.tildaid, templateName: tmpl.title, threadId: wo.thread_id, rfcMsgId: wo.rfc_msgid, createdat: wo.createdat, updatedat: wo.updatedat, lastReachout: wo.last_reachout, lastResponse: wo.last_response, dueDate: wo.due_date, resolvedAt: wo.resolved_at, statusUpdatedAt: wo.status_updated_at, activatedAt: wo.activated_at, // ENG-? ? timing, denormalized from the parent WorkOrderConfig (cfg). // configActivatedAt is distinct from activatedAt above (the child WO?t trip Neo4j?RECEIVED?RECEIVED?Received?Accepted?SENT?RECEIVED?RECEIVED' | wd]), contactList: wo.contact_list, ccList: wo.cc_list, delpending: wo.delpending, deltimestamp: wo.deltimestamp, holdbitmap: wo.holdbitmap, orgId: coalesce(org.tildaid, wo.org_id), orgName: org.name, orgAlias: org.alias, studyId: coalesce(study.tildaid, wo.study_id), studyName: study.name, siteId: coalesce(site.tildaid, wo.site_id), siteName: site.name } ) YIELD node RETURN node
writedashrsvc
1
167 ms
195 ms
167 ms
flat
match (p:Phi)-[:HAS_PROTECTED_DATA_IN]-(pa:Patient)-[:RESEARCH_SUBJECT]-(cp:CarePlan) -[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(v:VisitAppointment)-[:W2026_46 | W2026_47]-(s:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]-(o:Organiza…
match (p:Phi)-[:HAS_PROTECTED_DATA_IN]-(pa:Patient)-[:RESEARCH_SUBJECT]-(cp:CarePlan) -[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(v:VisitAppointment)-[:W2026_46 | W2026_47]-(s:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]-(o:Organization{alias:$orgAlias}) match (v)-[:BELONGS_TO_PARTICIPANT_VISIT|OLD_APPT_BELONGS_TO_PARTICIPANT_VISIT]-(pv:ParticipantVisit) optional match(v)-[:VISIT_APPOINTMENT_TO_CONFERENCE_DATA]-(c:ConferenceData) return v,p,c,pv,s.tildaid,cp.subjectid,cp.status
schedsvc
1
134 ms
195 ms
134 ms
flat
MATCH (:Organization {tildaid: $org_id})<-[:WORK_ORDER_TO_ORGANIZATION]-(w:WorkOrder) WHERE w.delpending = false AND (w.tildaid = $work_order_id OR w.work_order_id = $work_order_id) AND (w.study_id IN $accessible_study_ids OR coalesce(w.st…
MATCH (:Organization {tildaid: $org_id})<-[:WORK_ORDER_TO_ORGANIZATION]-(w:WorkOrder) WHERE w.delpending = false AND (w.tildaid = $work_order_id OR w.work_order_id = $work_order_id) AND (w.study_id IN $accessible_study_ids OR coalesce(w.study_id, ?) = ?) AND EXISTS { MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(m:WorkOrderActivity) WHERE m.activity_type IN $email_types AND (m.activity_type <> ? OR coalesce(m.status, ?) IN [?, ?]) AND (m.activity_type <> ? OR coalesce(m.status, ?) IN [?]) AND coalesce(m.ignoreType, ?) <> ? } RETURN count(w) AS total
virtual-assistant
1
114 ms
195 ms
114 ms
flat
MATCH (:Organization {tildaid: $org_id})<-[:WORK_ORDER_TO_ORGANIZATION]-(w:WorkOrder) WHERE w.delpending = false AND (w.tildaid = $work_order_id OR w.work_order_id = $work_order_id) AND (w.study_id IN $accessible_study_ids OR coalesce(w.st…
MATCH (:Organization {tildaid: $org_id})<-[:WORK_ORDER_TO_ORGANIZATION]-(w:WorkOrder) WHERE w.delpending = false AND (w.tildaid = $work_order_id OR w.work_order_id = $work_order_id) AND (w.study_id IN $accessible_study_ids OR coalesce(w.study_id, ?) = ?) AND EXISTS { MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(m:WorkOrderActivity) WHERE m.activity_type IN $email_types AND (m.activity_type <> ? OR coalesce(m.status, ?) IN [?, ?]) AND (m.activity_type <> ? OR coalesce(m.status, ?) IN [?]) AND coalesce(m.ignoreType, ?) <> ? } MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(a:WorkOrderActivity) WHERE a.activity_type IN $email_types AND (a.activity_type <> ? OR coalesce(a.status, ?) IN [?, ?]) AND (a.activity_type <> ? OR coalesce(a.status, ?) IN [?]) AND coalesce(a.ignoreType, ?) <> ? WITH w, count(a) AS email_count, max(coalesce(a.timestamp, a.createdat)) AS last_email_at_ms, min(coalesce(a.timestamp, a.createdat)) AS first_email_at_ms, sum(CASE WHEN a.activity_type = ? AND a.status = ? THEN ? ELSE ? END) AS draft_count WITH w, email_count, last_email_at_ms, first_email_at_ms, draft_count ORDER BY last_email_at_ms DESC, w.tildaid DESC SKIP $skip LIMIT $limit // The newest email in the thread — what the list pane previews. Its body is // truncated here, not in Python: that is the point of a summaries-only page. CALL (w) { MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(le:WorkOrderActivity) WHERE le.activity_type IN $email_types AND (le.activity_type <> ? OR coalesce(le.status, ?) IN [?, ?]) AND (le.activity_type <> ? OR coalesce(le.status, ?) IN [?]) AND coalesce(le.ignoreType, ?) <> ? RETURN le ORDER BY coalesce(le.timestamp, le.createdat) DESC, le.tildaid DESC LIMIT ? } // Every From address in the thread, in chronological order. Deduped in Python, where // preserving first-seen order is one call; Cypher has no ordered set without APOC. CALL (w) { MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(pa:WorkOrderActivity) WHERE pa.activity_type IN $email_types AND (pa.activity_type <> ? OR coalesce(pa.status, ?) IN [?, ?]) AND (pa.activity_type <> ? OR coalesce(pa.status, ?) IN [?]) AND coalesce(pa.ignoreType, ?) <> ? WITH pa ORDER BY coalesce(pa.timestamp, pa.createdat) ASC, pa.tildaid ASC RETURN collect(CASE WHEN pa.activity_type = ? THEN coalesce(pa.sender, ?) ELSE coalesce(pa.from_ai_teammate_email, ?) END) AS from_addresses } OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(site:Site) RETURN w, email_count, last_email_at_ms, first_email_at_ms, draft_count, from_addresses, study.name AS study_name, site.name AS site_name, // The site NUMBER lives on the study?U_REPLY???U_REPLY???[]?[]??null??']), ?, $snippet_chars) AS last_snippet ORDER BY last_email_at_ms DESC, w.tildaid DESC
writevirtual-assistant
1
192 ms
195 ms
192 ms
flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study??ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess??Inactive? ?Received?Quality Control?PHI Issues???/???/???/') ELSE ? END as childFolderPath WITH docObj, ct, child, st, head(collect(childFolderPath)) AS childFolderPath WITH child, CASE WHEN ct IS NULL THEN null ELSE { documentId: child.tildaid, studyId: COALESCE(child.studyId, ?), studyName: COALESCE(st.name, ?), folderPath: childFolderPath, kind: ct.kind, stale: ct.sourceVersionId <> docObj.primaryVersionId, version: COALESCE(child.versionName, toString(child.majorVersion)) } END as copiedToEntry RETURN collect(copiedToEntry) as copiedTo, count(DISTINCT child) as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { return null as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
1
137 ms
195 ms
137 ms
flat
p95 drift compares the query's p95 in the recent half of the window to the earlier half — ≥1.2×
means it's slowing down. Click a row for its p50/p95 latency trend over time. Latency percentiles come from
per-bucket histograms (no individual queries stored).