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
stored33h agotop 80 of 1,217
Neo4j query shapes on production over the last 30d, grouped by plan hash.
Click a column to sort; p95 drift (late-half ÷ early-half of the window) ranks the queries getting slower.
with $protocolId as protocolId, $careplanId as careplanId, $visitRefId as visitRefId, $activityRefId as activityRefId, $pvId as pvId, null as pwId, $paId as paId, $pqnrId as pqnrId, null as ptId, $hdmClassId as hdmClassId call(careplanId, …
with $protocolId as protocolId, $careplanId as careplanId, $visitRefId as visitRefId, $activityRefId as activityRefId, $pvId as pvId, null as pwId, $paId as paId, $pqnrId as pqnrId, null as ptId, $hdmClassId as hdmClassId call(careplanId, protocolId, pwId, pvId, paId, pqnrId, ptId, hdmClassId) { match (vref:GraphNodeRef{tildaid:?})-[rv:GRAPH_NODE_REF_TO_GRAPH_NODE]->(v:VisitV2)-[:BELONGS_TO_VISIT]-(pv:ParticipantVisit{carePlanId:careplanId}) match (aref:GraphNodeRef{tildaid:?})-[ra:GRAPH_NODE_REF_TO_GRAPH_NODE]->(a:ActivityV2)-[:BELONGS_TO_ACTIVITY]-(pa:ParticipantActivity{carePlanId:careplanId}) match (qref:GraphNodeRef{tildaid:?})-[rq:GRAPH_NODE_REF_TO_GRAPH_NODE]->(q:QuestionnaireV2)-[:BELONGS_TO_QUESTIONNAIRE]-(pqnr:ParticipantQuestionnaire{carePlanId:careplanId}) match(ref:GraphNodeRef{tildaid:?})-[rpq:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION] ->(qn:QuestionV2)-[:BELONGS_TO_QUESTION]-(pqn:ParticipantQuestion{carePlanId:careplanId, hdmClassId:hdmClassId})-[:PARTICIPANT_QUESTION]-(pqnr)-[:PARTICIPANT_QUESTIONNAIRE]-(pa)-[:PARTICIPANT_ACTIVITY]-(pv)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan{tildaid:careplanId}) with case when pqn.editStatus = ? then {v: pqn.editValue, l: pqn.editValues} else {v: pqn.value, l: pqn.values} end as pqnAns with case when size(coalesce(pqnAns.l, [])) > ? and apoc.text.join(pqnAns.l, ?) = coalesce(pqnAns.v, ?) then pqnAns.l when coalesce(pqnAns.v, ?) = ? then [] else apoc.coll.toSet(split(pqnAns.v, ?) + [pqnAns.v]) end as pqnValues return distinct pqnValues as ans1 } call(careplanId, protocolId, pwId, pvId, paId, pqnrId, ptId, hdmClassId) { match(ref:GraphNodeRef{tildaid:?})-[rpq:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION] ->(qn:QuestionV2)-[:BELONGS_TO_QUESTION]-(pqn:ParticipantQuestion{carePlanId:careplanId, hdmClassId:hdmClassId})-[:PARTICIPANT_QUESTION]-(pqnr:ParticipantQuestionnaire{tildaid:pqnrId}) with case when pqn.editStatus = ? then {v: pqn.editValue, l: pqn.editValues} else {v: pqn.value, l: pqn.values} end as pqnAns with case when size(coalesce(pqnAns.l, [])) > ? and apoc.text.join(pqnAns.l, ?) = coalesce(pqnAns.v, ?) then pqnAns.l when coalesce(pqnAns.v, ?) = ? then [] else apoc.coll.toSet(split(pqnAns.v, ?) + [pqnAns.v]) end as pqnValues return distinct pqnValues as ans2 } return ((( size(apoc.coll.intersection(apoc.coll.sort(ans1), apoc.coll.sort(ans2))) > ?)))
execsvc
18,566
5.77 s
22.81 s
41.69 s
flat
with $protocolId as protocolId, $careplanId as careplanId, $visitRefId as visitRefId, $activityRefId as activityRefId, $pvId as pvId, null as pwId, $paId as paId, $pqnrId as pqnrId, null as ptId, $hdmClassId as hdmClassId call(careplanId, …
with $protocolId as protocolId, $careplanId as careplanId, $visitRefId as visitRefId, $activityRefId as activityRefId, $pvId as pvId, null as pwId, $paId as paId, $pqnrId as pqnrId, null as ptId, $hdmClassId as hdmClassId call(careplanId, protocolId, pwId, pvId, paId, pqnrId, ptId, hdmClassId) { match (vref:GraphNodeRef{tildaid:?})-[rv:GRAPH_NODE_REF_TO_GRAPH_NODE]->(v:VisitV2)-[:BELONGS_TO_VISIT]-(pv:ParticipantVisit{carePlanId:careplanId}) match (aref:GraphNodeRef{tildaid:?})-[ra:GRAPH_NODE_REF_TO_GRAPH_NODE]->(a:ActivityV2)-[:BELONGS_TO_ACTIVITY]-(pa:ParticipantActivity{carePlanId:careplanId}) match (qref:GraphNodeRef{tildaid:?})-[rq:GRAPH_NODE_REF_TO_GRAPH_NODE]->(q:QuestionnaireV2)-[:BELONGS_TO_QUESTIONNAIRE]-(pqnr:ParticipantQuestionnaire{carePlanId:careplanId}) match(ref:GraphNodeRef{tildaid:?})-[rpq:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION] ->(qn:QuestionV2)-[:BELONGS_TO_QUESTION]-(pqn:ParticipantQuestion{carePlanId:careplanId, hdmClassId:hdmClassId})-[:PARTICIPANT_QUESTION]-(pqnr)-[:PARTICIPANT_QUESTIONNAIRE]-(pa)-[:PARTICIPANT_ACTIVITY]-(pv)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan{tildaid:careplanId}) with case when pqn.editStatus = ? then {v: pqn.editValue, l: pqn.editValues} else {v: pqn.value, l: pqn.values} end as pqnAns with case when size(coalesce(pqnAns.l, [])) > ? and apoc.text.join(pqnAns.l, ?) = coalesce(pqnAns.v, ?) then pqnAns.l when coalesce(pqnAns.v, ?) = ? then [] else apoc.coll.toSet(split(pqnAns.v, ?) + [pqnAns.v]) end as pqnValues return distinct pqnValues as ans1 } call(careplanId, protocolId, pwId, pvId, paId, pqnrId, ptId, hdmClassId) { match(ref:GraphNodeRef{tildaid:?})-[rpq:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION] ->(qn:QuestionV2)-[:BELONGS_TO_QUESTION]-(pqn:ParticipantQuestion{carePlanId:careplanId, hdmClassId:hdmClassId})-[:PARTICIPANT_QUESTION]-(pqnr:ParticipantQuestionnaire{tildaid:pqnrId}) with case when pqn.editStatus = ? then {v: pqn.editValue, l: pqn.editValues} else {v: pqn.value, l: pqn.values} end as pqnAns with case when size(coalesce(pqnAns.l, [])) > ? and apoc.text.join(pqnAns.l, ?) = coalesce(pqnAns.v, ?) then pqnAns.l when coalesce(pqnAns.v, ?) = ? then [] else apoc.coll.toSet(split(pqnAns.v, ?) + [pqnAns.v]) end as pqnValues return distinct pqnValues as ans2 } return ((( size(apoc.coll.intersection(apoc.coll.sort(ans1), apoc.coll.sort(ans2))) > ?)))
execsvc
1,461
3.49 s
18.65 s
36.33 s
flat
MATCH (d:Document)-[:DOC_TO_ORG]->(o:Organization) WHERE d.status IN $statuses OPTIONAL MATCH (d)-[:DOCUMENT_VERSION]->(v:DocumentVersion) WITH d, o, v ORDER BY v.createdat DESC WITH d, o, collect(v)[?] AS latest RETURN o.alias AS org, d.t…
MATCH (d:Document)-[:DOC_TO_ORG]->(o:Organization) WHERE d.status IN $statuses OPTIONAL MATCH (d)-[:DOCUMENT_VERSION]->(v:DocumentVersion) WITH d, o, v ORDER BY v.createdat DESC WITH d, o, collect(v)[?] AS latest RETURN o.alias AS org, d.tildaid AS document_id, latest.tildaid AS version_id, d.fileName AS name, d.status AS status
(internal)
1
4.99 s
4.85 s
4.99 s
flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) W…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read 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 ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Study policies=study-config-edit,study-config-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:?}) 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 ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // 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 RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION // 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:?}) 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, ?), ?) 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
exportsvc
1
2.20 s
4.85 s
2.20 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
3
1.05 s
4.55 s
3.11 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
6
794 ms
4.10 s
3.23 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? ?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
9
324 ms
3.65 s
2.35 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? ?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
11
334 ms
3.35 s
2.81 s
flat
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
2
575 ms
1.90 s
1.14 s
flat
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActi…
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE pa.status IN $completedStatuses AND NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END AND ta.trainingId = $trainingId AND ta.userId IN $userIds AND (NOT ((coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType)) OR (t.orgId <> ? AND t.orgId = $callerOrgId)) CALL (u) { OPTIONAL MATCH (u)<-[:FOR_USER]-(actor:Actor{orgId:$orgId, scopeId:$orgId, userId:u.tildaid, deltimestamp:?, state:$activeActorState, delpending:false})-[:ORGANIZATION_TO_ACTOR]-(:Organization{tildaid:$orgId}) WHERE coalesce(u.status, ?) <> $deletedUserStatus AND coalesce(u.email, ?) <> $supportUserEmail AND EXISTS { MATCH (actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole AND ($anyMember OR size($roles) = ? OR r.name IN $roles) } AND ($anyMember OR ((size($roles) > ? OR $labelsNarrow) AND (NOT $labelsNarrow OR EXISTS { MATCH (actor)-[:ACTOR_TO_MEMBER_LABEL]->(l:MemberLabel{delpending:false})<-[:TRAINING_TO_MEMBER_LABEL]-(:Training{tildaid:$labelTrainingId}) }))) RETURN [] AS sites, reduce(acc = [], rs IN collect([(actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole | r.name]) | acc + rs) AS siteRoles } CALL (pa) { OPTIONAL MATCH (pa)-[:BELONGS_TO_ACTIVITY]-(tpAct:ActivityV2) OPTIONAL MATCH (pa)-[: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 pa, tpAct, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH pa, tpAct, 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, reduce(acc = [], v IN collect([x IN tpVals WHERE x[?] = ? | x[?]]) | acc + v) AS tpScores, reduce(acc = [], v IN collect([x IN tpVals WHERE x[?] = ? | x[?]]) | acc + v) AS tpMaxScores RETURN tpFailed = ? AND (pa.status = ? OR coalesce(tpAct.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(tpAct.passingScore, ?) * tpTotalMax) AS passed, tpScores AS scoreValues, tpMaxScores AS maxScoreValues } CALL (pa, passed) { OPTIONAL MATCH (pa)-[:PARTICIPANT_DOCUMENTSET]-(pds:ParticipantDocumentSet)-[:BELONGS_TO_DOCUMENTSET]-(ds:DocumentSetV2) WHERE passed AND ds.tag = $finalResultTag OPTIONAL MATCH (pds)-[:PARTICIPANT_DOC_OVERLAY]-(:ParticipantDocOverlay)-[:BELONGS_TO]-(certDoc:Document{archived:false}) WITH pa, passed, certDoc ORDER BY certDoc.createdat DESC LIMIT ? OPTIONAL MATCH (pa)-[:TRAINING_CERTIFICATE]->(up:Document{archived:false}) RETURN CASE WHEN NOT passed THEN null WHEN pa.status = ? THEN up.tildaid ELSE certDoc.tildaid END AS certDocId LIMIT ? } OPTIONAL MATCH (pa)-[:TAIL_CHANGELOG]->(tailCl:ChangeLog{type:?, newValue:?}) CALL (pa) { OPTIONAL MATCH (act:ActivityV2{tildaid: pa.activityId}) RETURN act.version AS activityVersion LIMIT ? } RETURN u.tildaid, u.name, u.email, pa.tildaid, pa.status, pa.createdat, pa.dueDate, [(pa)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?, ?] | CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END], null AS col8, null AS col9, null AS col10, null AS col11, null AS col12, ta.tildaid, ta.source, ta.delivery, sites, passed, certDocId, tailCl.reason, tailCl.who, [(pa)-[:ALL_CHANGELOG]->(acl:ChangeLog) WHERE acl.type = ? AND acl.newValue IN [?, ?] | [acl.createdat, acl.who]], siteRoles, pa.activityId, activityVersion, ta.state, scoreValues, maxScoreValues ORDER BY u.name, u.tildaid, pa.createdat DESC
execsvc
3
412 ms
1.85 s
1.23 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, count(CASE WHEN NOT isArchived AND coalesce(doc.stateTag, ?) <> ? THEN ? END) as all
docsvc
8
233 ms
1.60 s
1.41 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, count(CASE WHEN NOT isArchived AND coalesce(doc.stateTag, ?) <> ? THEN ? END) as all
docsvc
11
146 ms
1.45 s
1.04 s
flat
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // ha…
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // has not reached its trigger: a ZIP member still being extracted, or an // upload whose file has not landed. Starting one here races the writer // that will certify it. AND d.status = $certified_status AND NOT EXISTS { (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(:AIRun) } RETURN d, null AS latest_ai UNION MATCH (ai:AIRun)-[:AI_RUN_TO_DOCUMENT_LATEST]->(d:Document) WHERE ai.status IN [?, ?] AND d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? AND d.status IN $retry_statuses WITH DISTINCT d MATCH (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(run:AIRun) WITH d, run ORDER BY COALESCE(run.updatedat, run.createdat) DESC WITH d, collect(run)[?] AS latest_ai // The cap applies to both statuses: each backlog retry bumps auto_retry_count on // the run (and refreshes updatedat, which keeps an aborted run inside the adopt // path?Staging?Pre-Staging'] } MATCH (d)-[:DOC_TO_ORG]-(o:Organization{orgType:?}) WITH DISTINCT d, o, latest_ai WITH d RETURN count(d) AS total
docproc
4,188
617 ms
976 ms
1.19 s
flat
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // ha…
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // has not reached its trigger: a ZIP member still being extracted, or an // upload whose file has not landed. Starting one here races the writer // that will certify it. AND d.status = $certified_status AND NOT EXISTS { (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(:AIRun) } RETURN d, null AS latest_ai UNION MATCH (ai:AIRun)-[:AI_RUN_TO_DOCUMENT_LATEST]->(d:Document) WHERE ai.status IN [?, ?] AND d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? AND d.status IN $retry_statuses WITH DISTINCT d MATCH (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(run:AIRun) WITH d, run ORDER BY COALESCE(run.updatedat, run.createdat) DESC WITH d, collect(run)[?] AS latest_ai // The cap applies to both statuses: each backlog retry bumps auto_retry_count on // the run (and refreshes updatedat, which keeps an aborted run inside the adopt // path?Staging?Pre-Staging'] } MATCH (d)-[:DOC_TO_ORG]-(o:Organization{orgType:?}) WITH DISTINCT d, o, latest_ai WITH d RETURN count(d) AS total
docproc
4,123
748 ms
975 ms
1.28 s
flat
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // ha…
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // has not reached its trigger: a ZIP member still being extracted, or an // upload whose file has not landed. Starting one here races the writer // that will certify it. AND d.status = $certified_status AND NOT EXISTS { (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(:AIRun) } RETURN d, null AS latest_ai UNION MATCH (ai:AIRun)-[:AI_RUN_TO_DOCUMENT_LATEST]->(d:Document) WHERE ai.status IN [?, ?] AND d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? AND d.status IN $retry_statuses WITH DISTINCT d MATCH (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(run:AIRun) WITH d, run ORDER BY COALESCE(run.updatedat, run.createdat) DESC WITH d, collect(run)[?] AS latest_ai // The cap applies to both statuses: each backlog retry bumps auto_retry_count on // the run (and refreshes updatedat, which keeps an aborted run inside the adopt // path?Staging?Pre-Staging'] } MATCH (d)-[:DOC_TO_ORG]-(o:Organization{orgType:?}) WITH DISTINCT d, o, latest_ai WITH d.tildaid AS doc_id, o.tildaid AS org_id, o.alias AS org_alias, d.primaryVersionId AS version_id, d.studyId AS study_id, COALESCE(latest_ai.auto_retry_count, ?) AS current_retry_count, // null when latest_ai IS NULL — a document enabled but never run has no // originating request to recover, and a sentinel user is the honest answer there. latest_ai.request_info AS request_info, latest_ai.tildaid AS ai_run_id, d OPTIONAL MATCH (d)-[:UPLOADED_BY]->(uploader:User) WITH doc_id, org_id, org_alias, version_id, study_id, current_retry_count, request_info, ai_run_id, head(collect(uploader.email)) AS uploaded_by RETURN doc_id, org_id, org_alias, version_id, study_id, current_retry_count, request_info, ai_run_id, uploaded_by ORDER BY doc_id LIMIT $limit
docproc
618
749 ms
975 ms
923 ms
flat
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // ha…
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // has not reached its trigger: a ZIP member still being extracted, or an // upload whose file has not landed. Starting one here races the writer // that will certify it. AND d.status = $certified_status AND NOT EXISTS { (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(:AIRun) } RETURN d, null AS latest_ai UNION MATCH (ai:AIRun)-[:AI_RUN_TO_DOCUMENT_LATEST]->(d:Document) WHERE ai.status IN [?, ?] AND d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? AND d.status IN $retry_statuses WITH DISTINCT d MATCH (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(run:AIRun) WITH d, run ORDER BY COALESCE(run.updatedat, run.createdat) DESC WITH d, collect(run)[?] AS latest_ai // The cap applies to both statuses: each backlog retry bumps auto_retry_count on // the run (and refreshes updatedat, which keeps an aborted run inside the adopt // path?Staging?Pre-Staging'] } MATCH (d)-[:DOC_TO_ORG]-(o:Organization{orgType:?}) WITH DISTINCT d, o, latest_ai WITH d.tildaid AS doc_id, o.tildaid AS org_id, o.alias AS org_alias, d.primaryVersionId AS version_id, d.studyId AS study_id, COALESCE(latest_ai.auto_retry_count, ?) AS current_retry_count, // null when latest_ai IS NULL — a document enabled but never run has no // originating request to recover, and a sentinel user is the honest answer there. latest_ai.request_info AS request_info, latest_ai.tildaid AS ai_run_id, d OPTIONAL MATCH (d)-[:UPLOADED_BY]->(uploader:User) WITH doc_id, org_id, org_alias, version_id, study_id, current_retry_count, request_info, ai_run_id, head(collect(uploader.email)) AS uploaded_by RETURN doc_id, org_id, org_alias, version_id, study_id, current_retry_count, request_info, ai_run_id, uploaded_by ORDER BY doc_id LIMIT $limit
docproc
657
612 ms
975 ms
955 ms
flat
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // ha…
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // has not reached its trigger: a ZIP member still being extracted, or an // upload whose file has not landed. Starting one here races the writer // that will certify it. AND d.status = $certified_status AND NOT EXISTS { (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(:AIRun) } RETURN d, null AS latest_ai UNION MATCH (ai:AIRun)-[:AI_RUN_TO_DOCUMENT_LATEST]->(d:Document) WHERE ai.status IN [?, ?] AND d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? AND d.status IN $retry_statuses WITH DISTINCT d MATCH (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(run:AIRun) WITH d, run ORDER BY COALESCE(run.updatedat, run.createdat) DESC WITH d, collect(run)[?] AS latest_ai // The cap applies to both statuses: each backlog retry bumps auto_retry_count on // the run (and refreshes updatedat, which keeps an aborted run inside the adopt // path?Staging?Pre-Staging'] } MATCH (d)-[:DOC_TO_ORG]-(o:Organization{orgType:?}) WITH DISTINCT d, o, latest_ai WITH d RETURN count(d) AS total
docproc
948
764 ms
975 ms
944 ms
flat
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // ha…
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // has not reached its trigger: a ZIP member still being extracted, or an // upload whose file has not landed. Starting one here races the writer // that will certify it. AND d.status = $certified_status AND NOT EXISTS { (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(:AIRun) } RETURN d, null AS latest_ai UNION MATCH (ai:AIRun)-[:AI_RUN_TO_DOCUMENT_LATEST]->(d:Document) WHERE ai.status IN [?, ?] AND d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? AND d.status IN $retry_statuses WITH DISTINCT d MATCH (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(run:AIRun) WITH d, run ORDER BY COALESCE(run.updatedat, run.createdat) DESC WITH d, collect(run)[?] AS latest_ai // The cap applies to both statuses: each backlog retry bumps auto_retry_count on // the run (and refreshes updatedat, which keeps an aborted run inside the adopt // path?Staging?Pre-Staging'] } MATCH (d)-[:DOC_TO_ORG]-(o:Organization{orgType:?}) WITH DISTINCT d, o, latest_ai WITH d RETURN count(d) AS total
docproc
870
604 ms
975 ms
778 ms
flat
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // ha…
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // has not reached its trigger: a ZIP member still being extracted, or an // upload whose file has not landed. Starting one here races the writer // that will certify it. AND d.status = $certified_status AND NOT EXISTS { (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(:AIRun) } RETURN d, null AS latest_ai UNION MATCH (ai:AIRun)-[:AI_RUN_TO_DOCUMENT_LATEST]->(d:Document) WHERE ai.status IN [?, ?] AND d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? AND d.status IN $retry_statuses WITH DISTINCT d MATCH (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(run:AIRun) WITH d, run ORDER BY COALESCE(run.updatedat, run.createdat) DESC WITH d, collect(run)[?] AS latest_ai // The cap applies to both statuses: each backlog retry bumps auto_retry_count on // the run (and refreshes updatedat, which keeps an aborted run inside the adopt // path?Staging?Pre-Staging'] } MATCH (d)-[:DOC_TO_ORG]-(o:Organization{orgType:?}) WITH DISTINCT d, o, latest_ai WITH d.tildaid AS doc_id, o.tildaid AS org_id, o.alias AS org_alias, d.primaryVersionId AS version_id, d.studyId AS study_id, COALESCE(latest_ai.auto_retry_count, ?) AS current_retry_count, // null when latest_ai IS NULL — a document enabled but never run has no // originating request to recover, and a sentinel user is the honest answer there. latest_ai.request_info AS request_info, latest_ai.tildaid AS ai_run_id, d OPTIONAL MATCH (d)-[:UPLOADED_BY]->(uploader:User) WITH doc_id, org_id, org_alias, version_id, study_id, current_retry_count, request_info, ai_run_id, head(collect(uploader.email)) AS uploaded_by RETURN doc_id, org_id, org_alias, version_id, study_id, current_retry_count, request_info, ai_run_id, uploaded_by ORDER BY doc_id LIMIT $limit
docproc
126
599 ms
975 ms
701 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?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?')) OR EXISTS { (doc1)-[:HAS]-(:DocumentAnnotation)-[:ANNOTATION_SIGNER]-(:DocumentSigner{userId:$signerUserId1}) }) AND (studyActor.isBlinded IS NULL OR doc1.blindedAccess = ? OR (studyActor.isBlinded = true AND (doc1.blindedAccess IN [?, ?])) OR (studyActor.isBlinded = false AND (doc1.blindedAccess IN [?, ?])))) WITH *, (($orgDocs0 AND doc1.studyId = ? AND doc1.patientId = ?) OR (doc1.studyId = ? AND true) OR CASE WHEN $orgDocs0 THEN EXISTS { (doc1)-[:BELONGS_TO]->(org) } ELSE false END) AS directAdmit CALL { WITH doc1, directAdmit WITH doc1 WHERE NOT directAdmit AND $withSigner0 AND EXISTS { (doc1)-[:HAS]-(:DocumentAnnotation)-[:ANNOTATION_SIGNER]-(:DocumentSigner{userId:$signerUserId2}) } 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
674 ms
975 ms
674 ms
flat
MATCH (ctx:Study)<-[:PLAN_CONTEXT]-(ep:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) WHERE ctx.tildaid IN $contextIds WITH DISTINCT e OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(statusScope:EventScope) WITH e, statusScope WHERE (statusScope IS…
MATCH (ctx:Study)<-[:PLAN_CONTEXT]-(ep:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) WHERE ctx.tildaid IN $contextIds WITH DISTINCT e OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(statusScope:EventScope) WITH e, statusScope WHERE (statusScope IS NULL AND e.status = $status) OR statusScope.status in $scopeStatuses WITH DISTINCT e WHERE e.isTemplate = $isTemplate WITH DISTINCT e ORDER BY e.createdat DESC SKIP $skip LIMIT $limit OPTIONAL MATCH (e)<-[:EVENTS]-(ep:EventPlan)-[:PLAN_CONTEXT]->(study) WITH e, study CALL { WITH e, study // Get details for each scope in the event OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(scope:EventScope) WHERE (scope IS NULL AND e.status = $status) OR scope.status in $scopeStatuses OPTIONAL MATCH (site:Site {tildaid: scope.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition{type:?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH 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 scope, siteName, siteNumber, deliverableStats WITH distinct scope, siteName, siteNumber, { 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 RETURN distinct scope{.*, siteName: siteName, siteNumber: siteNumber} as scope, scopeStats } CALL { // Collect summary for all deliverable in the event WITH e OPTIONAL MATCH (e)-[:REQUIRES_RESOLUTION_OF]->(eventDel:Deliverable) WITH collect(eventDel) as deliverables, [d IN collect(distinct eventDel) WHERE d.warning IS NOT NULL AND d.warning <> ? | ??\? + d.warning] as deliverableWarnings RETURN { trialDeliverables: SIZE([d in deliverables WHERE d.applyToTrial = true]), countryDeliverables: SIZE([d in deliverables WHERE d.applyToCountries = true]), siteDeliverables: SIZE([d in deliverables WHERE d.applyToSites = true]) } as deliverableCounts, deliverableWarnings } CALL { // Fetch createdBy WITH e OPTIONAL MATCH (e)-[:ALL_CHANGELOG]->(cl:ChangeLog{operation:?}) WITH cl ORDER BY cl.createdat ASC limit ? OPTIONAL MATCH (u:User{tildaid:cl.changedBy}) return {id: u.tildaid, name: u.name, profilePicURL: u.profilePicURL} as createdBy } // Aggregate all scopes and all deliverables in the event WITH e, study, createdBy, deliverableCounts, deliverableWarnings, collect(scope) 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, createdBy, deliverableCounts, deliverableWarnings, eventProgress, eventScopes, { 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 { WITH e OPTIONAL MATCH (woc:WorkOrderConfig)-[:WORK_ORDER_CONFIG_TO_EVENT]->(e) RETURN woc.tildaid as workOrderConfigId LIMIT ? } RETURN e{.*, createdBy: createdBy, warnings: deliverableWarnings, scopeSummary: scopeSummary, progress: eventProgress, 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, trialDeliverables: deliverableCounts.trialDeliverables, countryDeliverables: deliverableCounts.countryDeliverables, siteDeliverables: deliverableCounts.siteDeliverables} as result
writedocsvc
1
780 ms
975 ms
780 ms
flat
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // ha…
CALL { MATCH (d:Document) WHERE d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? // Certification is what triggers processing, so anything not yet certified // has not reached its trigger: a ZIP member still being extracted, or an // upload whose file has not landed. Starting one here races the writer // that will certify it. AND d.status = $certified_status AND NOT EXISTS { (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(:AIRun) } RETURN d, null AS latest_ai UNION MATCH (ai:AIRun)-[:AI_RUN_TO_DOCUMENT_LATEST]->(d:Document) WHERE ai.status IN [?, ?] AND d.delpending = false AND d.aiEnabled = true AND d.studyId <> ? AND d.primaryVersionId IS NOT NULL AND d.primaryVersionId <> ? AND d.status IN $retry_statuses WITH DISTINCT d MATCH (d)<-[:AI_RUN_TO_DOCUMENT_LATEST]-(run:AIRun) WITH d, run ORDER BY COALESCE(run.updatedat, run.createdat) DESC WITH d, collect(run)[?] AS latest_ai // The cap applies to both statuses: each backlog retry bumps auto_retry_count on // the run (and refreshes updatedat, which keeps an aborted run inside the adopt // path?Staging?Pre-Staging'] } MATCH (d)-[:DOC_TO_ORG]-(o:Organization{orgType:?}) WITH DISTINCT d, o, latest_ai WITH d.tildaid AS doc_id, o.tildaid AS org_id, o.alias AS org_alias, d.primaryVersionId AS version_id, d.studyId AS study_id, COALESCE(latest_ai.auto_retry_count, ?) AS current_retry_count, // null when latest_ai IS NULL — a document enabled but never run has no // originating request to recover, and a sentinel user is the honest answer there. latest_ai.request_info AS request_info, latest_ai.tildaid AS ai_run_id, d OPTIONAL MATCH (d)-[:UPLOADED_BY]->(uploader:User) WITH doc_id, org_id, org_alias, version_id, study_id, current_retry_count, request_info, ai_run_id, head(collect(uploader.email)) AS uploaded_by RETURN doc_id, org_id, org_alias, version_id, study_id, current_retry_count, request_info, ai_run_id, uploaded_by ORDER BY doc_id LIMIT $limit
docproc
97
761 ms
975 ms
796 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
1
770 ms
975 ms
770 ms
flat
MATCH (u:User {tildaid: $userId}) OPTIONAL MATCH (u)-[:SUPPORTS_ORG]-(so:Organization) WITH collect(DISTINCT so.tildaid) AS supportedIds CALL () { MATCH (cro:Organization)-[:HAS_CLINSITE]->(:ClinSite {deltimestamp: ?})- [:CLINSITE_FOR_SITE…
MATCH (u:User {tildaid: $userId}) OPTIONAL MATCH (u)-[:SUPPORTS_ORG]-(so:Organization) WITH collect(DISTINCT so.tildaid) AS supportedIds CALL () { MATCH (cro:Organization)-[:HAS_CLINSITE]->(:ClinSite {deltimestamp: ?})- [:CLINSITE_FOR_SITE]->(s:Site)-[:SITE_TO_ORGANIZATION]->(smo:Organization) WHERE s.delpending = false AND smo.delpending = false AND smo.orgType = $smoType AND smo.status = $managedStatus AND (toLower(smo.name) CONTAINS toLower($search) OR toLower(smo.alias) CONTAINS toLower($search)) RETURN collect(DISTINCT cro.tildaid) AS smoMatchIds } MATCH (o:Organization) WHERE o.delpending = false AND (NOT $hideManaged OR coalesce(o.status, ?) <> $managedStatus) AND (toLower(o.name) CONTAINS toLower($search) OR toLower(o.alias) CONTAINS toLower($search) OR o.tildaid IN smoMatchIds) WITH supportedIds, o{.*, supported: o.tildaid IN supportedIds} AS org order by org.name WITH supportedIds, collect(org) AS orgs WITH supportedIds, size(orgs) AS totalCount, orgs[$qpSliceSkip..$qpSliceEnd] AS paginatedOrgs CALL (paginatedOrgs, supportedIds) { UNWIND range(?, size(paginatedOrgs) - ?) AS i WITH i, paginatedOrgs[i] AS x CALL (x) { MATCH (:Organization {tildaid: x.tildaid})-[r:HAS_LICENSE]->(l:License) WHERE coalesce(l.delpending, false) = false WITH r, l ORDER BY l.key RETURN collect({ key: l.key, startDate: coalesce(r.startDate, ?), endDate: coalesce(r.endDate, ?), active: NOT coalesce(r.revoked, false) AND (coalesce(r.startDate, ?) = ? OR r.startDate <= $today) AND (coalesce(r.endDate, ?) = ? OR r.endDate >= $today) }) AS licenses } CALL (x, supportedIds) { MATCH (:Organization {tildaid: x.tildaid})-[:HAS_CLINSITE]->(:ClinSite {deltimestamp: ?})- [:CLINSITE_FOR_SITE]->(s:Site)-[:SITE_TO_ORGANIZATION]->(smo:Organization) WHERE s.delpending = false AND smo.delpending = false AND smo.orgType = $smoType AND smo.status = $managedStatus WITH DISTINCT smo, s ORDER BY s.name WITH smo, collect({tildaid: s.tildaid, name: s.name}) AS sites ORDER BY smo.name RETURN collect({tildaid: smo.tildaid, alias: smo.alias, name: smo.name, supported: smo.tildaid IN supportedIds, sites: sites}) AS managedSmos } WITH i, x, licenses, managedSmos ORDER BY i RETURN collect(x{.tildaid, .name, .orgType, .alias, .status, .createdat, .supported, licenseEnabled: $licenseGatingGlobal OR $licenseGating IN coalesce(x.supportedFeatures, []), licenses: licenses, managedSmos: managedSmos}) AS pageOrgs } RETURN {totalCount: totalCount, organizations: pageOrgs} AS response
onboardingsvc
2
379 ms
950 ms
650 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
37
139 ms
769 ms
669 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
10
66 ms
750 ms
624 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
10
80 ms
750 ms
753 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
10
79 ms
750 ms
742 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
10
86 ms
750 ms
819 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
15
267 ms
625 ms
584 ms
flat
MATCH (proto:FormV2 {tildaid: $protoId})-[pr:PROTOCOL_TO_GRAPH_NODE_REF]->(ref:GraphNodeRef) MATCH (ref)-[:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION {protocolid: $protoId}]->(target) WHERE (target:QuestionV2 OR target:Respons…
MATCH (proto:FormV2 {tildaid: $protoId})-[pr:PROTOCOL_TO_GRAPH_NODE_REF]->(ref:GraphNodeRef) MATCH (ref)-[:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION {protocolid: $protoId}]->(target) WHERE (target:QuestionV2 OR target:ResponseSet OR target:FormV2) AND target.tildaid IN $targetIds RETURN target.tildaid AS tid, ref.tildaid AS refId, pr.path AS path
studiosvc
2
462 ms
485 ms
469 ms
flat
MATCH(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_PATIENT]-(p:Patient{tildaid:$id}) OPTIONAL MATCH (p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with p, collect(cond.tildaid) as condIds OPTIONAL MATCH (p)-[:HAS_PROTECT…
MATCH(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_PATIENT]-(p:Patient{tildaid:$id}) OPTIONAL MATCH (p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with p, collect(cond.tildaid) as condIds OPTIONAL MATCH (p)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) OPTIONAL MATCH (p)<-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]-(:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(si:Site) WITH p, phi,condIds, cp, st, si ORDER BY st.name ASC WITH p, phi,condIds, collect({cp: cp, st: st, site:si}) AS cpStudyPairs OPTIONAL MATCH (s:Study)<-[:LEAD_TO_STUDY]-(l:Lead)-[:LEAD_TO_PATIENT]->(p) WITH p, phi,condIds, cpStudyPairs, l, s ORDER BY s.name ASC WITH p, phi,condIds, cpStudyPairs, collect({l: l, s: s}) AS lStudyPairs // Combine studyInfo and leadStudyInfo, ignoring leadStudyInfo if studyId matches WITH p, phi,condIds, cpStudyPairs, lStudyPairs, [pair IN cpStudyPairs WHERE pair.st IS NOT NULL OR pair.cp IS NOT NULL | { id: pair.st.tildaid, name: pair.st.name, sponsor: pair.st.sponsor, careplanId: pair.cp.tildaid, cpstatus: pair.cp.status, site: pair.site.name, subjectid: pair.cp.subjectid, isLocked: coalesce(pair.st.isLocked, false) } ] AS studyInfo, [pair IN lStudyPairs WHERE pair.s IS NOT NULL AND NOT pair.s.tildaid IN [cpPair IN cpStudyPairs WHERE cpPair.st IS NOT NULL | cpPair.st.tildaid] | { id: pair.s.tildaid, name: pair.s.name, sponsor: pair.s.sponsor, lstatus: pair.l.status, leadId: pair.l.tildaid, isLocked: coalesce(pair.s.isLocked, false) } ] AS leadStudyInfo WITH p, phi, condIds,studyInfo + leadStudyInfo AS combinedStudyInfo CALL apoc.create.vNode( [?], { patientId: p.tildaid, preferredLanguage: p.preferredlanguage, contactpreference: p.contactpreference, timepreference: p.timepreference, gender: p.sex, phiId: phi.tildaid, dob: phi.dob, firstname: phi.firstname, middlename: phi.middlename, lastname: phi.lastname, nickname: phi.nickname, suffix: phi.suffix, preferredname: phi.preferredname, address1: phi.address1, address2: phi.address2, city: phi.city, state: phi.state, country: phi.country, zippostalcode: phi.zippostalcode, latitude: phi.latitude, longitude: phi.longitude, email: phi.email, homephone: phi.homephone, workphone: phi.workphone, mobilephone: phi.mobilephone, primaryphonetype: phi.primaryphonetype, secondaryphonetype: phi.secondaryphonetype, emergencycontactname: phi.emergencycontactname, emergencycontactphonenumber: phi.emergencycontactphonenumber, emergencycontactrelation: phi.emergencycontactrelation, emergencycontactemail: phi.emergencycontactemail, hasStudyPartner: phi.hasstudypartner, studyPartnerName: phi.studypartnername, studyPartnerRelation: phi.studypartnerrelation, studyPartnerIsEmergencyContact: phi.studypartnerisemergencycontact, studyPartnerAddress1: phi.studypartneraddress1, studyPartnerAddress2: phi.studypartneraddress2, studyPartnerCity: phi.studypartnercity, studyPartnerState: phi.studypartnerstate, studyPartnerZipCode: phi.studypartnerzipcode, studyPartnerCountry: phi.studypartnercountry, studyPartnerEmail: phi.studypartneremail, studyPartnerPhone: phi.studypartnerphone, interestedconditionids: condIds, studyInfo: combinedStudyInfo }) YIELD node AS vNodes WITH COLLECT(vNodes) as profiles UNWIND profiles AS allprofiles RETURN allprofiles
writepatientsvc
1
259 ms
485 ms
259 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
276 ms
485 ms
276 ms
flat
MATCH (act:FormV2) WHERE act.type = ? AND (act.scopeid = ? OR act.scopeid IS NULL) AND (act.supportsglobalactivitybottomup IS NULL OR act.supportsglobalactivitybottomup = false) RETURN act.tildaid ORDER BY act.tildaid LIMIT ?
MATCH (act:FormV2) WHERE act.type = ? AND (act.scopeid = ? OR act.scopeid IS NULL) AND (act.supportsglobalactivitybottomup IS NULL OR act.supportsglobalactivitybottomup = false) RETURN act.tildaid ORDER BY act.tildaid LIMIT ?
studiosvc
20
290 ms
485 ms
445 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
6
271 ms
485 ms
360 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?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 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, count(CASE WHEN NOT isArchived AND coalesce(doc.stateTag, ?) <> ? THEN ? END) as all
docsvc
9
250 ms
485 ms
262 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
309 ms
479 ms
443 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
191 ms
477 ms
283 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
3
244 ms
477 ms
360 ms
flat
MATCH (u:User {tildaid: $userId}) OPTIONAL MATCH (u)-[:SUPPORTS_ORG]-(so:Organization) WITH collect(DISTINCT so.tildaid) AS supportedIds CALL () { MATCH (cro:Organization)-[:HAS_CLINSITE]->(:ClinSite {deltimestamp: ?})- [:CLINSITE_FOR_SITE…
MATCH (u:User {tildaid: $userId}) OPTIONAL MATCH (u)-[:SUPPORTS_ORG]-(so:Organization) WITH collect(DISTINCT so.tildaid) AS supportedIds CALL () { MATCH (cro:Organization)-[:HAS_CLINSITE]->(:ClinSite {deltimestamp: ?})- [:CLINSITE_FOR_SITE]->(s:Site)-[:SITE_TO_ORGANIZATION]->(smo:Organization) WHERE s.delpending = false AND smo.delpending = false AND smo.orgType = $smoType AND smo.status = $managedStatus AND (toLower(smo.name) CONTAINS toLower($search) OR toLower(smo.alias) CONTAINS toLower($search)) RETURN collect(DISTINCT cro.tildaid) AS smoMatchIds } MATCH (o:Organization) WHERE o.delpending = false AND (NOT $hideManaged OR coalesce(o.status, ?) <> $managedStatus) AND (toLower(o.name) CONTAINS toLower($search) OR toLower(o.alias) CONTAINS toLower($search) OR o.tildaid IN smoMatchIds) WITH supportedIds, o{.*, supported: o.tildaid IN supportedIds} AS org order by org.name WITH supportedIds, collect(org) AS orgs WITH supportedIds, size(orgs) AS totalCount, orgs[$qpSliceSkip..$qpSliceEnd] AS paginatedOrgs CALL (paginatedOrgs, supportedIds) { UNWIND range(?, size(paginatedOrgs) - ?) AS i WITH i, paginatedOrgs[i] AS x CALL (x) { MATCH (:Organization {tildaid: x.tildaid})-[r:HAS_LICENSE]->(l:License) WHERE coalesce(l.delpending, false) = false WITH r, l ORDER BY l.key RETURN collect({ key: l.key, startDate: coalesce(r.startDate, ?), endDate: coalesce(r.endDate, ?), active: NOT coalesce(r.revoked, false) AND (coalesce(r.startDate, ?) = ? OR r.startDate <= $today) AND (coalesce(r.endDate, ?) = ? OR r.endDate >= $today) }) AS licenses } CALL (x, supportedIds) { MATCH (:Organization {tildaid: x.tildaid})-[:HAS_CLINSITE]->(:ClinSite {deltimestamp: ?})- [:CLINSITE_FOR_SITE]->(s:Site)-[:SITE_TO_ORGANIZATION]->(smo:Organization) WHERE s.delpending = false AND smo.delpending = false AND smo.orgType = $smoType AND smo.status = $managedStatus WITH DISTINCT smo, s ORDER BY s.name WITH smo, collect({tildaid: s.tildaid, name: s.name}) AS sites ORDER BY smo.name RETURN collect({tildaid: smo.tildaid, alias: smo.alias, name: smo.name, supported: smo.tildaid IN supportedIds, sites: sites}) AS managedSmos } WITH i, x, licenses, managedSmos ORDER BY i RETURN collect(x{.tildaid, .name, .orgType, .alias, .status, .createdat, .supported, licenseEnabled: $licenseGatingGlobal OR $licenseGating IN coalesce(x.supportedFeatures, []), licenses: licenses, managedSmos: managedSmos}) AS pageOrgs } RETURN {totalCount: totalCount, organizations: pageOrgs} AS response
onboardingsvc
2
219 ms
470 ms
310 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
8
254 ms
470 ms
403 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
12
278 ms
470 ms
470 ms
flat
MATCH (proto:FormV2 {tildaid: $protoId})-[pr:PROTOCOL_TO_GRAPH_NODE_REF]->(ref:GraphNodeRef) MATCH (ref)-[:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION {protocolid: $protoId}]->(target) WHERE (target:QuestionV2 OR target:Respons…
MATCH (proto:FormV2 {tildaid: $protoId})-[pr:PROTOCOL_TO_GRAPH_NODE_REF]->(ref:GraphNodeRef) MATCH (ref)-[:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION {protocolid: $protoId}]->(target) WHERE (target:QuestionV2 OR target:ResponseSet OR target:FormV2) AND target.tildaid IN $targetIds RETURN target.tildaid AS tid, ref.tildaid AS refId, pr.path AS path
studiosvc
9
210 ms
466 ms
277 ms
flat
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) WHERE (ta.state = $assignedState OR EXISTS { (ta)-[:PAR…
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) WHERE (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) CALL (u) { OPTIONAL MATCH (u)<-[:FOR_USER]-(actor:Actor{orgId:$orgId, scopeId:$orgId, userId:u.tildaid, deltimestamp:?, state:$activeActorState, delpending:false})-[:ORGANIZATION_TO_ACTOR]-(:Organization{tildaid:$orgId}) WHERE coalesce(u.status, ?) <> $deletedUserStatus AND coalesce(u.email, ?) <> $supportUserEmail AND EXISTS { MATCH (actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole AND ($anyMember OR size($roles) = ? OR r.name IN $roles) } AND ($anyMember OR ((size($roles) > ? OR $labelsNarrow) AND (NOT $labelsNarrow OR EXISTS { MATCH (actor)-[:ACTOR_TO_MEMBER_LABEL]->(l:MemberLabel{delpending:false})<-[:TRAINING_TO_MEMBER_LABEL]-(:Training{tildaid:$labelTrainingId}) }))) RETURN [] AS sites, reduce(acc = [], rs IN collect([(actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole | r.name]) | acc + rs) AS siteRoles } UNWIND siteRoles AS role WITH DISTINCT role WHERE role IS NOT NULL AND role <> ? RETURN role ORDER BY role
execsvc
3
123 ms
455 ms
365 ms
flat
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) WHERE (ta.state = $assignedState OR EXISTS { (ta)-[:PAR…
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) WHERE (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) CALL (u) { OPTIONAL MATCH (u)<-[:FOR_USER]-(actor:Actor{orgId:$orgId, scopeId:$orgId, userId:u.tildaid, deltimestamp:?, state:$activeActorState, delpending:false})-[:ORGANIZATION_TO_ACTOR]-(:Organization{tildaid:$orgId}) WHERE coalesce(u.status, ?) <> $deletedUserStatus AND coalesce(u.email, ?) <> $supportUserEmail AND EXISTS { MATCH (actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole AND ($anyMember OR size($roles) = ? OR r.name IN $roles) } AND ($anyMember OR ((size($roles) > ? OR $labelsNarrow) AND (NOT $labelsNarrow OR EXISTS { MATCH (actor)-[:ACTOR_TO_MEMBER_LABEL]->(l:MemberLabel{delpending:false})<-[:TRAINING_TO_MEMBER_LABEL]-(:Training{tildaid:$labelTrainingId}) }))) RETURN [] AS sites, reduce(acc = [], rs IN collect([(actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole | r.name]) | acc + rs) AS siteRoles } UNWIND siteRoles AS role WITH DISTINCT role WHERE role IS NOT NULL AND role <> ? RETURN role ORDER BY role
execsvc
5
59 ms
425 ms
285 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
9
25 ms
365 ms
215 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
9
108 ms
365 ms
264 ms
flat
with $protocolId as protocolId, $careplanId as careplanId, null as visitRefId, $activityRefId as activityRefId, null as pvId, null as pwId, $paId as paId, $pqnrId as pqnrId, $ptId as ptId, $hdmClassId as hdmClassId call(careplanId, protoco…
with $protocolId as protocolId, $careplanId as careplanId, null as visitRefId, $activityRefId as activityRefId, null as pvId, null as pwId, $paId as paId, $pqnrId as pqnrId, $ptId as ptId, $hdmClassId as hdmClassId call(careplanId, protocolId, pwId, pvId, paId, pqnrId, ptId, hdmClassId) { match (vref:GraphNodeRef{tildaid:?})-[rv:GRAPH_NODE_REF_TO_GRAPH_NODE]->(v:VisitV2)-[:BELONGS_TO_VISIT]-(pv:ParticipantVisit{carePlanId:careplanId}) match (aref:GraphNodeRef{tildaid:?})-[ra:GRAPH_NODE_REF_TO_GRAPH_NODE]->(a:ActivityV2)-[:BELONGS_TO_ACTIVITY]-(pa:ParticipantActivity{carePlanId:careplanId}) match (qref:GraphNodeRef{tildaid:?})-[rq:GRAPH_NODE_REF_TO_GRAPH_NODE]->(q:QuestionnaireV2)-[:BELONGS_TO_QUESTIONNAIRE]-(pqnr:ParticipantQuestionnaire{carePlanId:careplanId}) match(ref:GraphNodeRef{tildaid:?})-[rpq:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION] ->(qn:QuestionV2)-[:BELONGS_TO_QUESTION]-(pqn:ParticipantQuestion{carePlanId:careplanId})-[:PARTICIPANT_QUESTION]-(pqnr)-[:PARTICIPANT_QUESTIONNAIRE]-(pa)-[:PARTICIPANT_ACTIVITY]-(pv)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan{tildaid:careplanId}) with case when pqn.editStatus = ? then {v: pqn.editValue, l: pqn.editValues} else {v: pqn.value, l: pqn.values} end as pqnAns with case when size(coalesce(pqnAns.l, [])) > ? and apoc.text.join(pqnAns.l, ?) = coalesce(pqnAns.v, ?) then pqnAns.l when coalesce(pqnAns.v, ?) = ? then [] else apoc.coll.toSet(split(pqnAns.v, ?) + [pqnAns.v]) end as pqnValues return distinct pqnValues as ans1 } call(careplanId, protocolId, pwId, pvId, paId, pqnrId, ptId, hdmClassId) { match(ref:GraphNodeRef{tildaid:?})-[rpq:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION] ->(qn:QuestionV2)-[:BELONGS_TO_QUESTION]-(pqn:ParticipantQuestion{carePlanId:careplanId, hdmClassId:hdmClassId})-[:PARTICIPANT_QUESTION]-(pt:ParticipantTable{tildaid:ptId}) with case when pqn.editStatus = ? then {v: pqn.editValue, l: pqn.editValues} else {v: pqn.value, l: pqn.values} end as pqnAns with case when size(coalesce(pqnAns.l, [])) > ? and apoc.text.join(pqnAns.l, ?) = coalesce(pqnAns.v, ?) then pqnAns.l when coalesce(pqnAns.v, ?) = ? then [] else apoc.coll.toSet(split(pqnAns.v, ?) + [pqnAns.v]) end as pqnValues return distinct pqnValues as ans2 } return ((( ans1[?] > ans2[?]) ))
execsvc
179
87 ms
359 ms
293 ms
flat
MATCH (act:FormV2) WHERE act.type = ? AND (act.scopeid = ? OR act.scopeid IS NULL) AND (act.supportsglobalactivitybottomup IS NULL OR act.supportsglobalactivitybottomup = false) RETURN act.tildaid ORDER BY act.tildaid LIMIT ?
MATCH (act:FormV2) WHERE act.type = ? AND (act.scopeid = ? OR act.scopeid IS NULL) AND (act.supportsglobalactivitybottomup IS NULL OR act.supportsglobalactivitybottomup = false) RETURN act.tildaid ORDER BY act.tildaid LIMIT ?
studiosvc
10
168 ms
350 ms
201 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
17
183 ms
245 ms
248 ms
flat
MATCH (o:Organization {tildaid: $org_id})<-[:RECOMMENDATION_TO_ORGANIZATION]-(r:Recommendation) WHERE EXISTS((r)-[:RECOMMENDATION_TO_DOCUMENT]->(:Document {tildaid: $doc_id})) AND r.category = $category RETURN count(r) as total
MATCH (o:Organization {tildaid: $org_id})<-[:RECOMMENDATION_TO_ORGANIZATION]-(r:Recommendation) WHERE EXISTS((r)-[:RECOMMENDATION_TO_DOCUMENT]->(:Document {tildaid: $doc_id})) AND r.category = $category RETURN count(r) as total
virtual-assistant
87
171 ms
239 ms
275 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
22
135 ms
197 ms
2.34 s
flat
MATCH (doc:Document)-[:DOC_TO_ORG]->(org:Organization) WHERE doc.stateTag = ? AND doc.aiEnabled = true AND doc.delpending = false AND doc.collection = ? AND toLower(org.alias) = toLower($orgAlias) RETURN count(doc) AS queue_size
MATCH (doc:Document)-[:DOC_TO_ORG]->(org:Organization) WHERE doc.stateTag = ? AND doc.aiEnabled = true AND doc.delpending = false AND doc.collection = ? AND toLower(org.alias) = toLower($orgAlias) RETURN count(doc) AS queue_size
studioai-production
164
119 ms
196 ms
230 ms
flat
MATCH (doc:Document)-[:DOC_TO_ORG]->(org:Organization) WHERE doc.stateTag = ? AND doc.aiEnabled = true AND doc.delpending = false AND doc.collection = ? AND toLower(org.alias) = toLower($orgAlias) RETURN count(doc) AS queue_size
MATCH (doc:Document)-[:DOC_TO_ORG]->(org:Organization) WHERE doc.stateTag = ? AND doc.aiEnabled = true AND doc.delpending = false AND doc.collection = ? AND toLower(org.alias) = toLower($orgAlias) RETURN count(doc) AS queue_size
studioai-production
252
119 ms
195 ms
178 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
9
158 ms
195 ms
175 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
18
140 ms
195 ms
179 ms
flat
MATCH (doc:Document)-[:DOC_TO_ORG]->(org:Organization) WHERE doc.stateTag = ? AND doc.aiEnabled = true AND doc.delpending = false AND doc.collection = ? AND toLower(org.alias) = toLower($orgAlias) RETURN count(doc) AS queue_size
MATCH (doc:Document)-[:DOC_TO_ORG]->(org:Organization) WHERE doc.stateTag = ? AND doc.aiEnabled = true AND doc.delpending = false AND doc.collection = ? AND toLower(org.alias) = toLower($orgAlias) RETURN count(doc) AS queue_size
studioai-production
255
135 ms
195 ms
157 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
188 ms
195 ms
188 ms
flat
MATCH (doc:Document)-[:DOC_TO_ORG]->(org:Organization) WHERE doc.stateTag = ? AND doc.aiEnabled = true AND doc.delpending = false AND doc.collection = ? AND toLower(org.alias) = toLower($orgAlias) RETURN count(doc) AS queue_size
MATCH (doc:Document)-[:DOC_TO_ORG]->(org:Organization) WHERE doc.stateTag = ? AND doc.aiEnabled = true AND doc.delpending = false AND doc.collection = ? AND toLower(org.alias) = toLower($orgAlias) RETURN count(doc) AS queue_size
studioai-production
167
138 ms
195 ms
180 ms
flat
MATCH (o:Organization {tildaid: $org_id})<-[:RECOMMENDATION_TO_ORGANIZATION]-(r:Recommendation) WHERE EXISTS((r)-[:RECOMMENDATION_TO_DOCUMENT]->(:Document {tildaid: $doc_id})) AND r.category = $category // Get document name, site info, and…
MATCH (o:Organization {tildaid: $org_id})<-[:RECOMMENDATION_TO_ORGANIZATION]-(r:Recommendation) WHERE EXISTS((r)-[:RECOMMENDATION_TO_DOCUMENT]->(:Document {tildaid: $doc_id})) AND r.category = $category // Get document name, site info, and study info for derived properties OPTIONAL MATCH (r)-[:RECOMMENDATION_TO_DOCUMENT]->(d:Document) OPTIONAL MATCH (d)-[:INCLUDES]-(:Binder)-[:BINDER_FOR]-(s:Site) OPTIONAL MATCH (d)-[:INCLUDES]-(:Binder)-[:BINDER_FOR]-(study:Study) WITH r, d, s, study ORDER BY CASE r.category WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? ELSE ? END ASC SKIP $skip LIMIT $limit // Create virtual node with basic properties plus documentName, siteName, siteId, contentType, and location fields CALL apoc.create.vNode( [?], { tildaid: r.tildaid, rec_id: COALESCE(r.rec_id, substring(r.tildaid, ?, ?)), doc_id: r.doc_id, user_action: r.user_action, status: r.status, category: r.category, data: r.data, data_formatted: CASE WHEN r.data_formatted IS NOT NULL AND r.data_formatted <> ? THEN apoc.convert.fromJsonMap(r.data_formatted) ELSE null END, data_internal: r.data_internal, explanation: r.explanation, comment: r.comment, comment_annotated_by: r.comment_annotated_by, comment_annotated_at: r.comment_annotated_at, createdat: r.createdat, updatedat: r.updatedat, type: r.type, severity: r.severity, // Add derived properties from related nodes documentName: COALESCE(d.fileName, ?), siteName: COALESCE(s.name, null), siteId: COALESCE(s.tildaid, null), studyId: COALESCE(study.tildaid, null), studyName: COALESCE(study.name, null), contentType: COALESCE(d.contentType, null), // Add location fields for QC recommendations locationType: r.locationType, pageIndex: r.pageIndex, xCoordinate: r.xCoordinate, yCoordinate: r.yCoordinate, // Add support flag isIgnoredBySupport: COALESCE(r.isIgnoredBySupport, false) } ) YIELD node AS recommendationNode RETURN recommendationNode
writevirtual-assistant
87
169 ms
195 ms
186 ms
flat
MATCH (t:Training{status:$active, delpending:false}) WHERE t.reassignBeforeExpiryDays > ? AND t.validUntil > ? OPTIONAL MATCH (o:Organization{tildaid:t.orgId}) WITH t, coalesce(o.alias, CASE WHEN coalesce(t.orgId, ?) = ? THEN head([(:Study…
MATCH (t:Training{status:$active, delpending:false}) WHERE t.reassignBeforeExpiryDays > ? AND t.validUntil > ? OPTIONAL MATCH (o:Organization{tildaid:t.orgId}) WITH t, coalesce(o.alias, CASE WHEN coalesce(t.orgId, ?) = ? THEN head([(:Study{tildaid:t.scopeId})-[:STUDY_TO_ORG]-(p:Organization) WHERE p.orgType IN $privileged | p.alias]) END) AS ownerAlias CALL (t) { OPTIONAL MATCH (t)-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{state:$assigned, deltimestamp:?}) CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(cur:ParticipantActivity{deltimestamp:?}) WHERE NOT EXISTS { (cur)-[:NEXT_PARTICIPANT_ACTIVITY]->() } RETURN cur ORDER BY cur.createdat DESC LIMIT ? } WITH ta, cur WHERE cur.status IN $completedStatuses RETURN collect([ta.userId, reduce(latest = ?, ts IN [(cur)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?, ?] | CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END] | CASE WHEN ts > latest THEN ts ELSE latest END)]) AS completions } RETURN t, ownerAlias, completions
execsvc
1
180 ms
195 ms
180 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
7
129 ms
195 ms
155 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
6
125 ms
195 ms
137 ms
flat
SHOW DATABASES YIELD name
SHOW DATABASES YIELD name
(internal)
1
198 ms
195 ms
198 ms
flat
MATCH (u:User)<-[:WORK_ORDER_TO_CONTACT]-(w:WorkOrder)-[:WORK_ORDER_TO_ORGANIZATION]->(o:Organization) WHERE u.email=? AND w.source_type=? OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(a:WorkOrderActivity) WHERE a.activity_type=? WITH o, …
MATCH (u:User)<-[:WORK_ORDER_TO_CONTACT]-(w:WorkOrder)-[:WORK_ORDER_TO_ORGANIZATION]->(o:Organization) WHERE u.email=? AND w.source_type=? OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(a:WorkOrderActivity) WHERE a.activity_type=? WITH o, w, a ORDER BY a.createdat WITH o.alias AS org, w.tildaid AS wo, left(w.title,?) AS t, w.status AS st, w.reminder_count AS rc, w.createdat AS c, collect(a.createdat) AS sends RETURN org, wo, t, st, rc, c, sends ORDER BY c
(internal)
1
109 ms
195 ms
109 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
5
129 ms
195 ms
136 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
3
165 ms
195 ms
170 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}
schedsvc
1
104 ms
195 ms
104 ms
flat
MATCH (p:Policy)-[:IS_ASSIGNED_TO]->(r:Role) WHERE coalesce(p.state, ?) IN [?, ?] OPTIONAL MATCH (r)<-[:MIMICS]-(m:Role) OPTIONAL MATCH (p)-[:APPLICABLE_IN]->(s:Scope {version:$scopeVersion}) UNWIND [r.name, m.name] as roleName WITH p, s, …
MATCH (p:Policy)-[:IS_ASSIGNED_TO]->(r:Role) WHERE coalesce(p.state, ?) IN [?, ?] OPTIONAL MATCH (r)<-[:MIMICS]-(m:Role) OPTIONAL MATCH (p)-[:APPLICABLE_IN]->(s:Scope {version:$scopeVersion}) UNWIND [r.name, m.name] as roleName WITH p, s, apoc.text.join(collect(DISTINCT roleName), ?) as roleList RETURN { name: p.name, roleList: roleList, objectTypeList: p.objectTypeList, allowedOps: p.allowedOps, scope: s.name } as policy
chat-agent
1
107 ms
195 ms
107 ms
flat
MERGE (a:Emr{deltimestamp:$a_deltimestamp,emrname:$a_emrname,emrversion:$a_emrversion,integrationtype:$a_integrationtype}) ON CREATE SET a.createdat = timestamp(), a.tildaid = randomUUID() ON MATCH SET a.updatedat = timestamp() SET a.holdb…
MERGE (a:Emr{deltimestamp:$a_deltimestamp,emrname:$a_emrname,emrversion:$a_emrversion,integrationtype:$a_integrationtype}) ON CREATE SET a.createdat = timestamp(), a.tildaid = randomUUID() ON MATCH SET a.updatedat = timestamp() SET a.holdbitmap=$a_holdbitmap,a.delpending=$a_delpending RETURN a.tildaid
writepatientsvc
1
178 ms
195 ms
178 ms
flat
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg optional match(p)-[r:USER_TO_PATIENT]-(u:User) with u.tildaid as …
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) 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 order by p.lastcontacted desc limit $pgLimit
patientsvc
1
171 ms
195 ms
171 ms
flat
MATCH (o:Organization {tildaid: $org_id})<-[:RECOMMENDATION_TO_ORGANIZATION]-(r:Recommendation) WHERE EXISTS((r)-[:RECOMMENDATION_TO_DOCUMENT]->(:Document {tildaid: $doc_id})) AND r.category = $category // Get document name, site info, and…
MATCH (o:Organization {tildaid: $org_id})<-[:RECOMMENDATION_TO_ORGANIZATION]-(r:Recommendation) WHERE EXISTS((r)-[:RECOMMENDATION_TO_DOCUMENT]->(:Document {tildaid: $doc_id})) AND r.category = $category // Get document name, site info, and study info for derived properties OPTIONAL MATCH (r)-[:RECOMMENDATION_TO_DOCUMENT]->(d:Document) OPTIONAL MATCH (d)-[:INCLUDES]-(:Binder)-[:BINDER_FOR]-(s:Site) OPTIONAL MATCH (d)-[:INCLUDES]-(:Binder)-[:BINDER_FOR]-(study:Study) WITH r, d, s, study ORDER BY CASE r.category WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? ELSE ? END ASC SKIP $skip LIMIT $limit // Create virtual node with basic properties plus documentName, siteName, siteId, contentType, and location fields CALL apoc.create.vNode( [?], { tildaid: r.tildaid, rec_id: COALESCE(r.rec_id, substring(r.tildaid, ?, ?)), doc_id: r.doc_id, user_action: r.user_action, status: r.status, category: r.category, data: r.data, data_formatted: CASE WHEN r.data_formatted IS NOT NULL AND r.data_formatted <> ? THEN apoc.convert.fromJsonMap(r.data_formatted) ELSE null END, data_internal: r.data_internal, explanation: r.explanation, comment: r.comment, comment_annotated_by: r.comment_annotated_by, comment_annotated_at: r.comment_annotated_at, createdat: r.createdat, updatedat: r.updatedat, type: r.type, severity: r.severity, // Add derived properties from related nodes documentName: COALESCE(d.fileName, ?), siteName: COALESCE(s.name, null), siteId: COALESCE(s.tildaid, null), studyId: COALESCE(study.tildaid, null), studyName: COALESCE(study.name, null), contentType: COALESCE(d.contentType, null), // Add location fields for QC recommendations locationType: r.locationType, pageIndex: r.pageIndex, xCoordinate: r.xCoordinate, yCoordinate: r.yCoordinate, // Add support flag isIgnoredBySupport: COALESCE(r.isIgnoredBySupport, false) } ) YIELD node AS recommendationNode RETURN recommendationNode
writevirtual-assistant
72
106 ms
195 ms
136 ms
flat
MATCH (o:Organization {tildaid: $org_id})<-[:RECOMMENDATION_TO_ORGANIZATION]-(r:Recommendation) WHERE EXISTS((r)-[:RECOMMENDATION_TO_DOCUMENT]->(:Document {tildaid: $doc_id})) AND r.category = $category RETURN count(r) as total
MATCH (o:Organization {tildaid: $org_id})<-[:RECOMMENDATION_TO_ORGANIZATION]-(r:Recommendation) WHERE EXISTS((r)-[:RECOMMENDATION_TO_DOCUMENT]->(:Document {tildaid: $doc_id})) AND r.category = $category RETURN count(r) as total
virtual-assistant
72
105 ms
194 ms
133 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
4
145 ms
193 ms
194 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
7
112 ms
191 ms
144 ms
flat
match (a:Actor) where a.tildaid in $actorIds optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(sp:StudySiteActor{delpending:false})- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_SITE]-(s:Site) call { with …
match (a:Actor) where a.tildaid in $actorIds optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(sp:StudySiteActor{delpending:false})- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_SITE]-(s:Site) call { with a,s,pd,sp match (r:Role) where r.name in sp.roles optional match (a)-[primaryRole:IS_PRIMARY_ROLE]-(r) with collect(distinct {name:r.name,displayName:r.displayName,assignDuty:r.assignDuty, tag:r.tags, isPrimary: CASE WHEN primaryRole IS NOT NULL THEN true ELSE false END}) as roleList,s,pd,a,sp optional match (sp)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]- (studySiteDuty:StudySiteDuty{removed:false,delpending:false})- [:DUTY_TO_STUDYSITEDUTY]-(duty:Duty) WITH a,s,pd,sp,duty,studySiteDuty,roleList ORDER BY duty.number with a,s,pd,sp,collect({duty: duty, studySiteDuty: studySiteDuty}) as duties,roleList with a,collect({duties:duties,siteId:s.tildaid,siteName:s.name,siteNumber:pd.siteNumber,country:s.country,roles:roleList,location:sp.location,ssaId:sp.tildaid}) as sites return collect({tildaid:a.tildaid, sites: sites}) as s1 } with s1,a call { with a with a optional match(a)-[:APPOINTED_AS]-(r:Role) optional match(a)-[primaryRole:IS_PRIMARY_ROLE]-(r) with collect(distinct {name:r.name,displayName:r.displayName,assignDuty:r.assignDuty,tag:r.tags,isPrimary: CASE WHEN primaryRole IS NOT NULL THEN true ELSE false END}) as roleList,a optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(siteActor:StudySiteActor{delpending:false})- [:STUDYSITEDUTY_TO_STUDYSITEACTOR]-(studySiteDuty:StudySiteDuty{removed:false,delpending:false})- [:DUTY_TO_STUDYSITEDUTY]-(duty:Duty) WITH a, duty, studySiteDuty, roleList ORDER BY duty.number with a,collect({duty: duty, studySiteDuty: studySiteDuty}) as duties,roleList with a,collect({duties:duties,roles:roleList}) as sites return collect({tildaid:a.tildaid, sites: sites}) as s2 } return case when size(s1)>? then s1 else s2 end
execsvc
2
89 ms
190 ms
174 ms
flat
match(a:Study{delpending:false,status:$status})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) where a.tildaid in $studyScope with a optional match(a)-[scope:STUDY_TO_ACTOR]->(actor:Actor{orgId:$orgId,userId:$userId,delpending:false})-…
match(a:Study{delpending:false,status:$status})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) where a.tildaid in $studyScope with a optional match(a)-[scope:STUDY_TO_ACTOR]->(actor:Actor{orgId:$orgId,userId:$userId,delpending:false})-[APPOINTED_AS]-(role:Role) with a, role with a, collect(role.displayName) as roles return count(a)
enrollsvc
2
50 ms
190 ms
100 ms
flat
MATCH(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_PATIENT]-(p:Patient{tildaid:$id}) OPTIONAL MATCH (p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with p, collect(cond.tildaid) as condIds OPTIONAL MATCH (p)-[:HAS_PROTECT…
MATCH(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_PATIENT]-(p:Patient{tildaid:$id}) OPTIONAL MATCH (p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with p, collect(cond.tildaid) as condIds OPTIONAL MATCH (p)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) OPTIONAL MATCH (p)<-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]-(:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(si:Site) WITH p, phi,condIds, cp, st, si ORDER BY st.name ASC WITH p, phi,condIds, collect({cp: cp, st: st, site:si}) AS cpStudyPairs OPTIONAL MATCH (s:Study)<-[:LEAD_TO_STUDY]-(l:Lead)-[:LEAD_TO_PATIENT]->(p) WITH p, phi,condIds, cpStudyPairs, l, s ORDER BY s.name ASC WITH p, phi,condIds, cpStudyPairs, collect({l: l, s: s}) AS lStudyPairs // Combine studyInfo and leadStudyInfo, ignoring leadStudyInfo if studyId matches WITH p, phi,condIds, cpStudyPairs, lStudyPairs, [pair IN cpStudyPairs WHERE pair.st IS NOT NULL OR pair.cp IS NOT NULL | { id: pair.st.tildaid, name: pair.st.name, sponsor: pair.st.sponsor, careplanId: pair.cp.tildaid, cpstatus: pair.cp.status, site: pair.site.name, subjectid: pair.cp.subjectid, isLocked: coalesce(pair.st.isLocked, false) } ] AS studyInfo, [pair IN lStudyPairs WHERE pair.s IS NOT NULL AND NOT pair.s.tildaid IN [cpPair IN cpStudyPairs WHERE cpPair.st IS NOT NULL | cpPair.st.tildaid] | { id: pair.s.tildaid, name: pair.s.name, sponsor: pair.s.sponsor, lstatus: pair.l.status, leadId: pair.l.tildaid, isLocked: coalesce(pair.s.isLocked, false) } ] AS leadStudyInfo WITH p, phi, condIds,studyInfo + leadStudyInfo AS combinedStudyInfo CALL apoc.create.vNode( [?], { patientId: p.tildaid, preferredLanguage: p.preferredlanguage, contactpreference: p.contactpreference, timepreference: p.timepreference, gender: p.sex, phiId: phi.tildaid, dob: phi.dob, firstname: phi.firstname, middlename: phi.middlename, lastname: phi.lastname, nickname: phi.nickname, suffix: phi.suffix, preferredname: phi.preferredname, address1: phi.address1, address2: phi.address2, city: phi.city, state: phi.state, country: phi.country, zippostalcode: phi.zippostalcode, latitude: phi.latitude, longitude: phi.longitude, email: phi.email, homephone: phi.homephone, workphone: phi.workphone, mobilephone: phi.mobilephone, primaryphonetype: phi.primaryphonetype, secondaryphonetype: phi.secondaryphonetype, emergencycontactname: phi.emergencycontactname, emergencycontactphonenumber: phi.emergencycontactphonenumber, emergencycontactrelation: phi.emergencycontactrelation, emergencycontactemail: phi.emergencycontactemail, hasStudyPartner: phi.hasstudypartner, studyPartnerName: phi.studypartnername, studyPartnerRelation: phi.studypartnerrelation, studyPartnerIsEmergencyContact: phi.studypartnerisemergencycontact, studyPartnerAddress1: phi.studypartneraddress1, studyPartnerAddress2: phi.studypartneraddress2, studyPartnerCity: phi.studypartnercity, studyPartnerState: phi.studypartnerstate, studyPartnerZipCode: phi.studypartnerzipcode, studyPartnerCountry: phi.studypartnercountry, studyPartnerEmail: phi.studypartneremail, studyPartnerPhone: phi.studypartnerphone, interestedconditionids: condIds, studyInfo: combinedStudyInfo }) YIELD node AS vNodes WITH COLLECT(vNodes) as profiles UNWIND profiles AS allprofiles RETURN allprofiles
writepatientsvc
2
94 ms
190 ms
185 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
4
87 ms
190 ms
156 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
74 ms
185 ms
142 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).