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
stored2d agotop 80 of 711
Neo4j query shapes on nexus over the last 3d, grouped by plan hash.
Click a column to sort; p95 drift (late-half ÷ early-half of the window) ranks the queries getting slower.
MATCH (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
1
2.42 s
4.85 s
2.42 s
flat
MATCH (doc0:Document{tildaid:$f0}) MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org, doc0 UNWIND [x IN $lists.allowedStudies0 WHERE x = doc0.studyId] AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An…
MATCH (doc0:Document{tildaid:$f0}) MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org, doc0 UNWIND [x IN $lists.allowedStudies0 WHERE x = doc0.studyId] 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?tildaScale??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 { WITH doc OPTIONAL MATCH (doc)<-[:PROMOTED_FROM]-(tv:DocumentVersion)<-[:DOCUMENT_VERSION]-(tgt:Document) WITH CASE WHEN tgt IS NULL THEN null ELSE { documentId: tgt.tildaid, targetVersionId: tv.tildaid, version: COALESCE(tv.versionName, toString(tv.majorVersion)) } END as promotedTo RETURN promotedTo LIMIT ? } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { WITH docObj OPTIONAL MATCH (docObj)<-[:INCLUDES]-(currentFolder:Folder{folderType:?}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (docObj)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (docObj)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) WITH CASE WHEN stagingInfo IS NOT NULL THEN {artifactId: stagingInfo.folderId, subArtifactId: stagingInfo.subArtifactId, stagingInfoId: stagingInfo.tildaid} WHEN currentFolder.level = ? THEN {artifactId: currentFolder.tildaid, subArtifactId: COALESCE(currentSubArtifact.tildaid, ?), stagingInfoId: ?} ELSE {artifactId: ?, subArtifactId: ?, stagingInfoId: ?} END as tmfInfo WITH tmfInfo.artifactId as artifactId, tmfInfo.subArtifactId as subArtifactId, tmfInfo.stagingInfoId as stagingInfoId OPTIONAL MATCH (artifact:Folder{tildaid:artifactId})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{name:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(allSubArtifacts:SubArtifact) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:subArtifactId}) OPTIONAL MATCH (subArtifact)-[:HAS]->(subArtifactConstraints:FolderConstraints) OPTIONAL MATCH (artifact)-[:HAS]->(artifactConstraints:FolderConstraints) WITH artifactId as folderId, subArtifactId, stagingInfoId, CASE WHEN zone.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+ ? + zone.name, ?) ELSE ? END as zone, CASE WHEN section.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+ ? + section.name, ?) ELSE ? END as section, CASE WHEN artifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+ ? + artifact.name, ?) ELSE ? END as artifact, CASE WHEN subArtifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+?+apoc.text.lpad(toString(subArtifact.number), ?, ?)+ ? + subArtifact.name, ?) ELSE ? END as subArtifact, COALESCE(subArtifact.name, ?) as subArtifactName, COALESCE(subArtifactConstraints, artifactConstraints) as constraints, NOT(COUNT(allSubArtifacts) > ? AND subArtifactId = ?) as subArtifactValid OPTIONAL MATCH (constraints)-[:HAS_ATTRIBUTE_CONSTRAINT]->(dac:DocAttributeConstraint) WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, constraints, COLLECT(DISTINCT CASE WHEN dac IS NOT NULL THEN {id: dac.tildaid, key: dac.key, order: dac.order, isMandatory: dac.isMandatory, description: dac.description} ELSE NULL END) as attributeConstraintsList WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, CASE WHEN constraints IS NOT NULL THEN { id: constraints.tildaid, mandatory: constraints.mandatory, tmfLevel: constraints.tmfLevel, attributeConstraints: [attr IN attributeConstraintsList WHERE attr IS NOT NULL | attr] } ELSE null END as constraints WITH {folderId: folderId, subArtifactId: subArtifactId, stagingInfoId: stagingInfoId, zone: zone, section: section, artifact: artifact, subArtifact: subArtifact, subArtifactName: subArtifactName, subArtifactValid: subArtifactValid, path: apoc.text.join([x IN [zone, section, artifact] WHERE x <> ? | x], ?), constraints: constraints} as tmfCategorization RETURN CASE WHEN tmfCategorization.folderId=? THEN null ELSE tmfCategorization END as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
1
4.37 s
4.85 s
4.37 s
flat
MATCH (doc0:Document{tildaid:$f0}) MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org, doc0 UNWIND [x IN $lists.allowedStudies0 WHERE x = doc0.studyId] AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An…
MATCH (doc0:Document{tildaid:$f0}) MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org, doc0 UNWIND [x IN $lists.allowedStudies0 WHERE x = doc0.studyId] 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?tildaScale??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 ({doc: doc, location: location, docObj: docObj, owned: owned, study: study, org: org, vector: vector, docAccessLevel: docAccessLevel}) as row WITH collect(DISTINCT row) as rows, count(DISTINCT row) as total unwind rows as row WITH row.doc as doc, row.location as location, row.docObj as docObj, row.owned as owned, row.study as study, row.org as org, row.vector as vector, row.docAccessLevel as docAccessLevel, total order by doc.createdat asc, doc.tildaid asc skip $skip0 WITH *, EXISTS { (doc)-[:HAS]-(:DocumentAnnotation)-[:ANNOTATION_SIGNER]-(:DocumentSigner{userId:$signerUserId3}) } AS isSigner CALL {return null as patientId, null as patientName, null as subjectId}CALL { return null as site, ? as siteNumber, [] as piNames }CALL { return null as visit }CALL { return null as user }CALL { return null as uploadedBy }CALL { return null as docType }CALL { return null as signedByUsers , null as reviewDate } WITH distinct total, doc, docObj, vector, owned, study, org, docAccessLevel, isSigner, patientId, patientName, subjectId, site, siteNumber, piNames, visit, user, uploadedBy, docType, signedByUsers, reviewDate CALL { return null as workOrders, false as woContact, null as latestAIRun, null as findingCount, null as queriesCount, null as confirmedCount, null as resolvedCount }CALL { return null as tags } CALL { return null as path, null as fold, null as collection, null as stateTags1 } CALL { return null as primaryFolder } CALL { return null as listingFolders } CALL { return null as stateTags2, null as stagingLabels } CALL { return [] as reviews} CALL { return null as templateDetails} CALL { with doc, docObj CALL { with doc, docObj with doc, docObj where doc.primaryVersionId = ? or doc.primaryVersionId is null optional match (doc)-[:UPLOADED_BY]->(uploader:User) return {tildaid: doc.tildaid, fileName: doc.fileName, originalFileName: doc.originalFileName, contentType: doc.contentType, date: doc.date, dateType: doc.dateType, versionName: doc.versionName, rejectReason: doc.rejectReason, language: doc.language, languages: doc.languages, needsAttention: doc.needsAttention, needsAttentionReasons: doc.needsAttentionReasons, trackActiveDuplicates: doc.trackActiveDuplicates, versionNumber: doc.versionNumber, majorVersion: doc.majorVersion, minorVersion: doc.minorVersion, generation: toString(doc.generation), cksum: doc.cksum, size: doc.size, status: doc.status, stateTag: doc.stateTag, pages: doc.pages, uploadMethod: doc.uploadMethod, changeLog: [], createdAt: doc.createdat, renderedCompletedAt: doc.renderedCompletedAt, renderedFieldsRevision: doc.renderedFieldsRevision, renderedSignersRevision: doc.renderedSignersRevision, uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} as version union with doc optional match (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion) where $versionsSeesPreRedaction0 or coalesce(dv.preRedaction, false) = false or dv.tildaid = doc.primaryVersionId optional match (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv) optional match (dv)-[:UPLOADED_BY]->(uploader:User) optional match (dv)-[:PROMOTED_FROM]->(psrc:Document) return case when dv is null then null else {tildaid: dv.tildaid, fileName: dv.fileName, originalFileName: dv.originalFileName, contentType: dv.contentType, date: dv.date, dateType: doc.dateType, versionName: dv.versionName, rejectReason: dv.rejectReason, language: dv.language, languages: dv.languages, needsAttention: dv.needsAttention, needsAttentionReasons: dv.needsAttentionReasons, trackActiveDuplicates: dv.trackActiveDuplicates, versionNumber: dv.versionNumber, majorVersion: dv.majorVersion, minorVersion: dv.minorVersion, documentSourceId: ds.tildaid, promotedFromDocumentId: psrc.tildaid, generation: toString(dv.generation), linearizedPdfUrl: dv.linearizedPdfUrl, linearizedPdfStatus: dv.linearizedPdfStatus, needsProcessedPdf: dv.needsProcessedPdf, langDetectionStatus: dv.langDetectionStatus, translatedPdfUrl: dv.translatedPdfUrl, translatedPdfStatus: dv.translatedPdfStatus, cksum: dv.cksum, size: dv.size, status: dv.status, stateTag: dv.stateTag, pages: dv.pages, uploadMethod: dv.uploadMethod, changeLog: [], url: dv.url, createdAt: dv.createdat, renderedCompletedAt: dv.renderedCompletedAt, renderedFieldsRevision: dv.renderedFieldsRevision, renderedSignersRevision: dv.renderedSignersRevision, preRedaction: coalesce(dv.preRedaction, false), uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} end as version // TODO: [Major/Minor Versioning] Once client supports minor version UX, update ordering to: // } with doc, version order by version.majorVersion, version.minorVersion // This will display versions as ?.?, ?.?, ?.? instead of flat ?, ?, ? } with doc, version order by version.versionNumber where $versionsVersionId0 IS NULL OR version.tildaid = $versionsVersionId0 return collect(version) as versions } CALL { return null as annotationSpec, null as annotationVal } CALL { return [] as queries } CALL { return null as sharedWith, null as currentShare } CALL { WITH doc MATCH (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion{tildaid: coalesce($docAttrVersionId0, doc.primaryVersionId)}) MATCH (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv) OPTIONAL MATCH (ds)-[:HAS_ATTRIBUTE]->(da:DocAttribute) OPTIONAL MATCH (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) RETURN collect(DISTINCT da{.*, name: COALESCE(dad.name, ?), description: COALESCE(dad.description, ?), documentSourceId: ds.tildaid}) as docAttributes } CALL { WITH doc OPTIONAL MATCH (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion{tildaid: coalesce($notesVersionId0, doc.primaryVersionId)})-[:HAS_NOTE]->(n:DocumentNote{archived: false}) RETURN count(n) AS notesCount } CALL { return null as eventId, null as eventName, null as deliverable, null as eventScope } CALL { return null as copiedFrom } CALL { return [] as copiedTo, ? as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { return null as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
1
2.12 s
4.85 s
2.12 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 MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? …
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) 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 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
chat-agent
1
1.90 s
1.95 s
1.90 s
flat
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND b.study_id = $study_id AND b.study_id IN $lists.accessible_study_ids OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_…
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND b.study_id = $study_id AND b.study_id IN $lists.accessible_study_ids OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(wc:WorkOrder) WHERE wc.delpending = false AND wc.status <> ? WITH b, count(wc) AS _nonarchived_count WHERE b.status <> ? OR _nonarchived_count > ? OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_STUDY]->(study:Study) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CREATOR]->(creator:User) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) WITH b, study, creator, tmpl, b.createdat AS _sort_key ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC LIMIT $fetch_limit WITH b, study, creator, tmpl, _sort_key // Resolve WorkPlanInstance id/name for workplan-created configs (wp_object_id set). // Mirrors get_workplan_info_for_work_order_config: cfg.wp_object_id -> milestone item // (by tildaid or cfgMilestoneItemId) -> WorkPlanInstance. Non-workplan configs yield null. OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone) -[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem:WorkplanInstanceMilestoneItem) WHERE coalesce(b.wp_object_id, ?) <> ? AND (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id) WITH b, study, creator, tmpl, _sort_key, head(collect(wpInstance.tildaid)) AS workplan_id, head(collect(wpInstance.name)) AS workplan_name OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(w:WorkOrder) WHERE w.delpending = false AND w.status <> ? OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(wStudy:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(wSite:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(wCreator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ASSIGNEE]->(wAssignee:User) // Resolve EventScope for Event-type child WOs (null for Standard-type) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_EVENT_SCOPE]->(wES:EventScope) // Look up the Site node only for Site-scoped events; guards against null wES and non-Site scopes. // wES.scopeId is a site tildaid (UUID) for Site scopes, country name for Country, ? for Trial. OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ? OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key}) OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_TASK]->(wTask:WorkOrderTask) WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, w, wStudy, wSite, wCreator, wAssignee, wES, wES_Site, wPartner, wMember, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_pending_count, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_resolved_count // ENG-?: order children in the DB so children_limit keeps the first N in // DISPLAY order. collect() below preserves incoming row order. // The PARENT keyset terms MUST lead. This ORDER BY runs in a WITH stage AFTER // the parent ORDER BY/LIMIT, and a later ORDER BY re-sorts the whole row stream // — ordering on the child value alone silently re-ranked the CONFIGS by their // child?s QUERY order, not display order: the backward // reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC, (w.createdat IS NULL), w.createdat DESC, w.tildaid WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, collect(DISTINCT CASE WHEN w IS NOT NULL THEN { tildaid: w.tildaid, work_order_id: w.work_order_id, title: w.title, work_order_type: w.work_order_type, status: w.status, priority: w.priority, creator_id: w.creator_id, assigned_to_id: w.assigned_to_id, site_id: w.site_id, study_id: w.study_id, document_ids: w.document_ids, contact_list: w.contact_list, cc_list: w.cc_list, goal: w.goal, due_date: w.due_date, last_reachout: w.last_reachout, last_response: w.last_response, resolved_at: w.resolved_at, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
writevirtual-assistant
1
1.65 s
1.95 s
1.65 s
flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
1
1.45 s
1.95 s
1.45 s
flat
MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId}) call (org,st) { match (org)-[:STUDY_TO_ORG]-(st)-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) whe…
MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId}) call (org,st) { match (org)-[:STUDY_TO_ORG]-(st)-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) where (org.orgType=? or org.orgType=?) and pd.delpending=false return pd, site union match (org)-[:IS_AN_ORG]-(:Smo)-[:SITE_TO_SMO]-(site:Site)-[:PLAN_DEFINITION_TO_SITE]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(st) where pd.delpending=false return pd,site } WITH pd, site AS s MATCH (p:Patient)-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd) WHERE cp.tildaid = $dashCareplanId call { with cp call { with cp match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) return pa, pv union with cp match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(cp) return pa, null as pv union with cp match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) return pa, pv union with cp match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]-(cp) return pa, null as pv } call { with pa MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) WHERE coalesce(q.supersededAt, ?) = ? return q union with pa MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(:ParticipantTable)-[:PARTICIPANT_TABLE]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) where (q.delpending is null or q.delpending = false) AND coalesce(q.supersededAt, ?) = ? return q } return q, pa, pv, null as doc union with cp, p call { with cp, p match (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2)-[:BINDER_FOR]-(vb:Binder)-[:INCLUDES]-(doc:Document)-[:INCLUDES]-(pb:Binder)-[:BINDER_FOR]-(p) MATCH (q:Query)-[:HAS_QUERY]-(doc) return doc, pv, q } return q, null as pa, pv, doc } MATCH (task:Task)-[:WITH_CONTEXT]-(q) WHERE q.state = $mqState AND s.name IN $siteNames optional match (pa)-[:BELONGS_TO_ACTIVITY]-(act:Activity|ActivityV2) optional match (pv)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) match (p)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi)OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(study:Study{tildaid:$dashStudyId})-[:STUDY_TO_ACTOR]->(studyActor:Actor)-[:FOR_USER]->(requestingUser:User{tildaid:$dashRequestingUserId}) WHERE studyActor.state<>? WITH task, q, s, act, v, cp, phi, study, head(collect(studyActor)) AS studyActor OPTIONAL MATCH (q)-[:ALL_CHANGELOG]-(cl:ChangeLog) optional match (task)-[:BELONGS_TO]->(i:Inbox)-[:OWNED_BY]->(u:User)WITH task,q,s,act,v,cp.subjectid as subjectid, phi,study,studyActor,collect(distinct u.name) as assignedToUsers, cl ORDER BY cl.createdat ASC WITH task,q,s,act,v,phi,subjectid,assignedToUsers,study,studyActor, COLLECT(cl) AS changeLogs,count(case when cl.type=? or (cl.type=? and cl.reason <> ?) then ? end) as comments, duration.inDays(datetime({epochMillis: q.createdat}), datetime()).days AS durationDays WITH task,q,s,act,v,phi,subjectid,assignedToUsers,study,studyActor, changeLogs, comments, durationDays, reduce(d = ?, cl IN changeLogs | CASE WHEN cl.type = ? AND cl.newValue = ? THEN cl.createdat ELSE d END) AS resolvedAt WITH {task: task, q: q, s: s, act: act, v: v, phi: phi, subjectid: subjectid, assignedToUsers: assignedToUsers, study: study, studyActor: studyActor, changeLogs: changeLogs, comments: comments, durationDays: durationDays} as entry WITH entry WITH entry.q.tildaid as queryId, collect(entry) as entriesPerQuery WITH count(queryId) as countOfN, collect(head(entriesPerQuery)) as rows UNWIND rows as entry WITH countOfN, entry order by entry.q.createdat desc RETURN distinct entry.q, entry.s, entry.study, entry.studyActor, entry.changeLogs, entry.durationDays, countOfN, apoc.create.vNode([?], {taskId: entry.task.tildaid, subject: entry.phi.firstname + ? + entry.phi.lastname, subjectid: entry.subjectid, visitTitle: entry.v.title, activityTitle: entry.act.title, type: entry.q.type, comments: entry.comments, assignedTo: entry.assignedToUsers, blindedAccess: entry.act.blindedAccess}) as dashboardInfo limit $pgLimit
writeexecsvc
3
435 ms
1.85 s
1.29 s
flat
MATCH (d:Document) WHERE coalesce(d.docOriginMethod, ?) = ? WITH d LIMIT $batchSize CALL { WITH d OPTIONAL MATCH (d)-[:DOCUMENT_VERSION]->(dv:DocumentVersion) WITH d, dv ORDER BY dv.majorVersion, dv.minorVersion, dv.createdat WITH d, head(…
MATCH (d:Document) WHERE coalesce(d.docOriginMethod, ?) = ? WITH d LIMIT $batchSize CALL { WITH d OPTIONAL MATCH (d)-[:DOCUMENT_VERSION]->(dv:DocumentVersion) WITH d, dv ORDER BY dv.majorVersion, dv.minorVersion, dv.createdat WITH d, head(collect(dv)) AS firstVer OPTIONAL MATCH (firstVer)-[:UPLOADED_BY]->(vu:User) OPTIONAL MATCH (d)-[:UPLOADED_BY]->(du:User) OPTIONAL MATCH (d)<-[:INCOMING_EMAIL_TO_DOCUMENT]-(ie:IncomingEmail) WITH d, firstVer, coalesce(head(collect(vu)), head(collect(du))) AS u, head(collect(ie)) AS ie OPTIONAL MATCH (firstVer)-[:ALL_CHANGELOG]->(c:ChangeLog) WHERE firstVer.uploadMethod = $emailMethod AND c.description CONTAINS $emailUploadPhrase WITH d, firstVer, u, ie, [g IN collect(apoc.text.regexGroups(c.description, $senderPattern)) WHERE size(g) > ? AND g[?][?] <> $botEmail | g[?][?]] AS textSenders WITH d, u, ie, textSenders, trim(coalesce(ie.sender, ?)) AS emailSender, CASE WHEN firstVer IS NOT NULL THEN firstVer.uploadMethod ELSE d.uploadMethod END AS rawMethod, CASE WHEN coalesce(firstVer.createdat, ?) > ? THEN firstVer.createdat ELSE coalesce(d.createdat, ?) END AS firstAt WITH d, u, ie, textSenders, emailSender, firstAt, CASE WHEN emailSender <> ? THEN $emailMethod WHEN rawMethod IN $uploadMethods THEN rawMethod WHEN d.docKind = $liveKind THEN $liveMethod ELSE $manualMethod END AS method RETURN method, CASE WHEN emailSender <> ? THEN emailSender WHEN size(textSenders) > ? THEN textSenders[?] WHEN method = $emailMethod AND coalesce(u.email, ?) = $botEmail THEN ? WHEN trim(coalesce(u.name, ?)) <> ? THEN trim(u.name) ELSE trim(coalesce(u.email, ?)) END AS sender, CASE WHEN emailSender <> ? AND coalesce(ie.received_at_ms, ?) > ? THEN ie.received_at_ms ELSE firstAt END AS at } SET d.docOriginMethod = method, d.docOriginSender = sender, d.docOriginAt = at RETURN count(d)
writedocsvc
3
670 ms
1.85 s
1.71 s
0.1× ↓
MATCH (root:GraphNode{tildaid:$graphId}) OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2) WITH root, collect(DISTINCT var) as graphVars MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNo…
MATCH (root:GraphNode{tildaid:$graphId}) OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2) WITH root, collect(DISTINCT var) as graphVars MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNode) WHERE size(rr) = ? OR rr[-?].path[?] = root.tildaid OPTIONAL MATCH (gnode)-[:GRAPH_NODE_TO_PAGE{graphid:root.tildaid}]->(page:GraphPage) OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{graphid:$relPropId}]->(refNode:GraphNode) WHERE gnode.objtype = ? OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_QUESTION{graphid:$relPropId}]->(question:QuestionV2) WHERE refNode.objtype = ? WITH root, gnode, page, refNode, case when refNode.objtype = ? then question when refNode.objtype = ? then refNode else null end as question, rr, graphVars OPTIONAL MATCH (question)-[:QUESTION_TO_QUESTION_SPEC]->(questionSpec:QuestionSpec) OPTIONAL MATCH (gnode)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]- (nodeBranchCfg:BranchConfig)-[:BRANCH_TO_BRANCH_CONFIG{graphid:$relPropId}]- (nodeBranch:BranchV2) OPTIONAL MATCH (gnode)-[cfgRel:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]- (:BranchConfig)-[:ACTIVATION_RULE_TO_BRANCH_CONFIG{graphid:cfgRel.graphid}]- (:ActivationRule)-[:ACTIVATION_RULE_TO_CHAIN{graphid:cfgRel.graphid}]- (nodeChain:ActivationRuleChain) OPTIONAL MATCH (gnode)-[:BRANCH_TO_BRANCH_CONFIG{graphid:root.tildaid, graphid:$relPropId}]->(cfg:BranchConfig) WHERE gnode.objtype = ? OPTIONAL MATCH (cfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]->(cfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[rel:QUESTION_TO_AUTO_COMPUTE_CFG|QUESTION_TO_CORRECTNESS_CHECK{graphid:$relPropId}]->(acc:AutoComputeConfig) OPTIONAL MATCH (acc)-[:AUTO_COMPUTE_TO_BRANCH_CONFIG]->(acBranchCfg:BranchConfig) OPTIONAL MATCH (acBranchCfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]->(acCfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[aqRel:QUESTION_TO_AUTO_QUERY{graphid:$relPropId}]->(aqcfg:AutoQueryConfig{enabled:true}) WHERE aqRel.sourceprotocolid IS NULL OPTIONAL MATCH (aqcfg)-[:AUTO_QUERY_TO_BRANCH_CONFIG]->(aqBranchCfg:BranchConfig) WITH gnode, page, refNode, cfg, rr, questionSpec, question, CASE WHEN size(rr) > ? THEN rr[-?].path ELSE [root.tildaid] END as nodePath, CASE WHEN size(rr) > ? THEN rr[-?].flags ELSE ? END as nodeFlags, CASE WHEN size(rr) > ? THEN rr[-?].who ELSE [] END as nodeWho, CASE WHEN type(rel) = ? THEN true ELSE false END as isCorrectnessCheckConfig, collect(DISTINCT cfgRef.tildaid) as cfgRefIds, collect(DISTINCT nodeBranch.tildaid) as branchIds, collect(DISTINCT nodeChain.tildaid) as activationChainIds, acc, acBranchCfg, collect(DISTINCT acCfgRef.tildaid) as autoComputeBranchRefIds, graphVars, collect(DISTINCT CASE WHEN aqcfg IS NOT NULL THEN {aqcfg: aqcfg, aqBranchCfg: aqBranchCfg} END) as autoQueryConfigs WITH gnode as node, page, refNode, cfg as branchCfg, cfgRefIds as branchRefIds, questionSpec, question, branchIds, activationChainIds, nodePath as path, gnode.objtype as objType, nodeFlags as flags, nodeWho as who, acc as autoComputeConfig, acBranchCfg as autoComputeBranchCfg, autoComputeBranchRefIds, autoQueryConfigs, isCorrectnessCheckConfig, graphVars RETURN {node:node, page:page, refNode:refNode, questionSpec:questionSpec, question:question, branchCfg:branchCfg, branchRefIds:branchRefIds, path:path, objType:objType, flags:flags, who:who, branchIds:branchIds, activationChainIds:activationChainIds, autoComputeConfig:autoComputeConfig, autoComputeBranchCfg:autoComputeBranchCfg, autoComputeBranchRefIds:autoComputeBranchRefIds, autoQueryConfigs:autoQueryConfigs, isCorrectnessCheckConfig:isCorrectnessCheckConfig, graphVars:graphVars}
execsvc
5
249 ms
1.75 s
1.23 s
flat
match(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType}) where (exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId}))) and not form.state=? with org, count(form) as allCount optional match…
match(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType}) where (exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId}))) and not form.state=? with org, count(form) as allCount optional match(org)-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType, state:?}) where exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId})) with org, allCount, count(form) as publishedCount optional match(org)-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType, state:?}) where exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId})) with org, allCount, publishedCount, count(form) as draftCount optional match(org)-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType, state:?}) where exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId})) return allCount, publishedCount, draftCount, count(form) as archivedCount
studiosvc
1
583 ms
975 ms
583 ms
flat
MATCH (n:Folder|SubArtifact) WHERE n.isArchived IS NULL SET n.isArchived = false RETURN count(n)
MATCH (n:Folder|SubArtifact) WHERE n.isArchived IS NULL SET n.isArchived = false RETURN count(n)
writedocsvc
1
757 ms
975 ms
757 ms
flat
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND (b.study_id IN $lists.accessible_study_ids OR coalesce(b.study_id, ?) = ?) OPTIONAL MATCH (b)-[:WORK_ORDER_C…
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND (b.study_id IN $lists.accessible_study_ids OR coalesce(b.study_id, ?) = ?) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(wc:WorkOrder) WHERE wc.delpending = false AND wc.status <> ? WITH b, count(wc) AS _nonarchived_count WHERE b.status <> ? OR _nonarchived_count > ? OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_STUDY]->(study:Study) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CREATOR]->(creator:User) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) WITH b, study, creator, tmpl, b.createdat AS _sort_key ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC LIMIT $fetch_limit WITH b, study, creator, tmpl, _sort_key // Resolve WorkPlanInstance id/name for workplan-created configs (wp_object_id set). // Mirrors get_workplan_info_for_work_order_config: cfg.wp_object_id -> milestone item // (by tildaid or cfgMilestoneItemId) -> WorkPlanInstance. Non-workplan configs yield null. OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone) -[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem:WorkplanInstanceMilestoneItem) WHERE coalesce(b.wp_object_id, ?) <> ? AND (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id) WITH b, study, creator, tmpl, _sort_key, head(collect(wpInstance.tildaid)) AS workplan_id, head(collect(wpInstance.name)) AS workplan_name OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(w:WorkOrder) WHERE w.delpending = false AND w.status <> ? OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(wStudy:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(wSite:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(wCreator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ASSIGNEE]->(wAssignee:User) // Resolve EventScope for Event-type child WOs (null for Standard-type) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_EVENT_SCOPE]->(wES:EventScope) // Look up the Site node only for Site-scoped events; guards against null wES and non-Site scopes. // wES.scopeId is a site tildaid (UUID) for Site scopes, country name for Country, ? for Trial. OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ? OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key}) OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_TASK]->(wTask:WorkOrderTask) WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, w, wStudy, wSite, wCreator, wAssignee, wES, wES_Site, wPartner, wMember, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_pending_count, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_resolved_count // ENG-?: order children in the DB so children_limit keeps the first N in // DISPLAY order. collect() below preserves incoming row order. // The PARENT keyset terms MUST lead. This ORDER BY runs in a WITH stage AFTER // the parent ORDER BY/LIMIT, and a later ORDER BY re-sorts the whole row stream // — ordering on the child value alone silently re-ranked the CONFIGS by their // child?s QUERY order, not display order: the backward // reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC, (w.createdat IS NULL), w.createdat DESC, w.tildaid WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, collect(DISTINCT CASE WHEN w IS NOT NULL THEN { tildaid: w.tildaid, work_order_id: w.work_order_id, title: w.title, work_order_type: w.work_order_type, status: w.status, priority: w.priority, creator_id: w.creator_id, assigned_to_id: w.assigned_to_id, site_id: w.site_id, study_id: w.study_id, document_ids: w.document_ids, contact_list: w.contact_list, cc_list: w.cc_list, goal: w.goal, due_date: w.due_date, last_reachout: w.last_reachout, last_response: w.last_response, resolved_at: w.resolved_at, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
writevirtual-assistant
1
663 ms
975 ms
663 ms
flat
MATCH (a:WorkOrderActivity)-[:WORK_ORDER_ACTIVITY_TO_DOCUMENT]->(d:Document) WHERE d.docOriginMethod = $emailMethod AND NOT coalesce(d.docOriginSender, ?) CONTAINS ? AND (a.activity_type = $replyType OR (a.activity_type = $chatType AND a.s…
MATCH (a:WorkOrderActivity)-[:WORK_ORDER_ACTIVITY_TO_DOCUMENT]->(d:Document) WHERE d.docOriginMethod = $emailMethod AND NOT coalesce(d.docOriginSender, ?) CONTAINS ? AND (a.activity_type = $replyType OR (a.activity_type = $chatType AND a.source = $chatSource)) OPTIONAL MATCH (u:User {tildaid: a.user_id}) WITH d, a, trim(CASE WHEN coalesce(a.sender, ?) CONTAINS ? THEN a.sender ELSE coalesce(u.email, ?) END) AS sender WHERE sender CONTAINS ? AND sender <> $botEmail WITH d, sender ORDER BY a.createdat WITH d, head(collect(sender)) AS sender LIMIT $batchSize SET d.docOriginSender = sender RETURN count(d)
writedocsvc
3
372 ms
925 ms
897 ms
0.2× ↓
MATCH (pa:ParticipantActivity{tildaid:$pmParticipantActivityId}) -[:BELONGS_TO_ACTIVITY]-(a:ActivityV2{tildaid:$pmActivityId}) // LEVEL ?: Participant objects (Questionnaire, DocumentSet, CheckList, TaskList) OPTIONAL MATCH (pa)-[:PARTICIP…
MATCH (pa:ParticipantActivity{tildaid:$pmParticipantActivityId}) -[:BELONGS_TO_ACTIVITY]-(a:ActivityV2{tildaid:$pmActivityId}) // LEVEL ?: Participant objects (Questionnaire, DocumentSet, CheckList, TaskList) OPTIONAL MATCH (pa)-[:PARTICIPANT_QUESTIONNAIRE|PARTICIPANT_DOCUMENTSET|PARTICIPANT_CHECKLIST|PARTICIPANT_TASKLIST]-(pObj1)- [:BELONGS_TO_QUESTIONNAIRE|BELONGS_TO_DOCUMENTSET|BELONGS_TO_CHECKLIST|BELONGS_TO_TASKLIST]-(obj1) WHERE (pObj1.delpending is null or pObj1.delpending <> true) // LEVEL ?: Sub-objects (Questions, Tables, DocUploads, Todos, TaskConfigs, ESign, Timer, Media, DocOverlay) OPTIONAL MATCH (pObj1)-[:PARTICIPANT_QUESTION|PARTICIPANT_TABLE|PARTICIPANT_DOC_UPLOAD|PARTICIPANT_DOC_OVERLAY|PARTICIPANT_TODO|PARTICIPANT_TASKCONFIG|PARTICIPANT_ESIGN|PARTICIPANT_TIMER|PARTICIPANT_MEDIA]-(pSubObj1)- [:BELONGS_TO_QUESTION|BELONGS_TO_TABLE|BELONGS_TO_DOC_UPLOAD|BELONGS_TO_DOC_OVERLAY|BELONGS_TO_TODOV2|BELONGS_TO_TASKCONFIG|BELONGS_TO_ESIGN|BELONGS_TO_TIMER|BELONGS_TO_MEDIA]-(subObj1) WHERE (pSubObj1.delpending is null or pSubObj1.delpending <> true) // LEVEL ?: For tables, get their questions and docuploads (one more level deep) OPTIONAL MATCH (pSubObj1)-[:PARTICIPANT_QUESTION|PARTICIPANT_DOC_UPLOAD]-(pSubSubObj1)- [:BELONGS_TO_QUESTION|BELONGS_TO_DOC_UPLOAD]-(subSubObj1) WHERE pSubObj1:ParticipantTable and (pSubSubObj1.delpending is null or pSubSubObj1.delpending <> true) // Fetch metadata for Level ? (table sub-objects) - counts plus the latest changelog, // which the visit PDF prints as the answer?Draft?s Object Access (freeze/lock) changelogs separately from the per-cell changeLogCount OPTIONAL MATCH (hdmObj)-[:ALL_CHANGELOG]-(oaClog:ChangeLog{type:?}) WITH pa, a, obj1, pObj1, pSubObj1, subObj1, hdmObj, creatorUser, fallbackUser, pSubSubObjs, count(distinct oaClog) as rowObjAccessCount // Collect hdmObj with pSubSubObjs into hdmpSubSubObj WITH DISTINCT pa, a, obj1, pObj1, pSubObj1, subObj1, CASE WHEN size(pSubSubObjs) > ? THEN { pSubSubObjs: pSubSubObjs, hdmObj: hdmObj, objectAccessCount: rowObjAccessCount } ELSE null END as hdmpSubSubObj WITH DISTINCT pa, a, obj1, pObj1, pSubObj1, subObj1,collect(hdmpSubSubObj) as hdmpSubSubObjs // Fetch metadata for Level ? OPTIONAL MATCH (pSubObj1)-[:ALL_CHANGELOG]-(cl2:ChangeLog) WHERE cl2.type IN $pdClogTypes OPTIONAL MATCH (pSubObj1)-[:HAS_QUERY]-(q2:Query) WHERE q2.delpending = false AND q2.state <> ? OPTIONAL MATCH (pSubObj1)-[:BELONGS_TO]-(doc2:Document{archived:false}) OPTIONAL MATCH (doc2)-[:TAIL_CHANGELOG]-(docCL2:ChangeLog) OPTIONAL MATCH (doc2)-[:HAS_QUERY]-(dq2:Query) WHERE NOT dq2.state IN [?] // Aggregate Level ? WITH DISTINCT pa, a, obj1, pObj1, pSubObj1, subObj1, hdmpSubSubObjs, COUNT(DISTINCT dq2) AS doc2QueryCount, COUNT(DISTINCT cl2) AS clog2Count, COUNT(DISTINCT q2) AS query2Count, reduce(l = null, c IN COLLECT(DISTINCT cl2) | CASE WHEN l IS NULL OR c.createdat > l.createdat THEN c ELSE l END) AS lastClog2, doc2, docCL2 ORDER BY docCL2.createdat DESC WITH pa, a, obj1.tildaid AS obj1Id, obj1.title as obj1Title, pObj1, pSubObj1, subObj1, hdmpSubSubObjs, clog2Count, query2Count, lastClog2, CASE WHEN doc2 IS NOT NULL THEN { id: doc2.tildaid, docKind: doc2.docKind, versionNumber: doc2.versionNumber, fileName: doc2.fileName, uploadedBy: docCL2.who, uploadedAt: docCL2.createdat, size: doc2.size, status: doc2.status, pages: doc2.pages, docType: doc2.docTypeName, contentType: doc2.contentType, queryCount: doc2QueryCount } END as opDoc2 With pa, a, obj1Id, obj1Title, pObj1, pSubObj1, subObj1, hdmpSubSubObjs, clog2Count, query2Count, lastClog2, COLLECT(opDoc2) as opdocs2 WITH pa, a, obj1Id, obj1Title, pObj1, COLLECT({ subObj1Id: subObj1.tildaid, subObj1Title: subObj1.title, pSubObj1: pSubObj1, label: labels(pSubObj1)[?], hdmpSubSubObjs: hdmpSubSubObjs, docs: opdocs2, changeLogCount: clog2Count, changeLogs: CASE WHEN lastClog2 IS NULL THEN [] ELSE [lastClog2] END, queryCount: query2Count }) AS pSubObjs WITH DISTINCT pa, a, pObj1, obj1Id, obj1Title, pSubObjs RETURN {pa:pa, a:a, pObj1:pObj1, obj1Id:obj1Id, obj1Title:obj1Title, pSubObjs:pSubObjs}
writeexecsvc
4
148 ms
900 ms
584 ms
flat
match (task:Task) where task.tildaid in $lists.taskIds with distinct task as t call apoc.do.case([ t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContex…
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 }) AND (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) 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
152 ms
875 ms
753 ms
flat
MATCH (t:Training) WHERE t.tildaid IN $trainingIds AND t.scopeId = $studyId AND t.delpending = false OPTIONAL MATCH (t)-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{state:$assignedState, deltimestamp:?}) WHERE $viewerOrgId = ? OR (ta.…
MATCH (t:Training) WHERE t.tildaid IN $trainingIds AND t.scopeId = $studyId AND t.delpending = false OPTIONAL MATCH (t)-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{state:$assignedState, deltimestamp:?}) WHERE $viewerOrgId = ? OR (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) WITH t, ta, [(ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity{deltimestamp:?}) WHERE NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY]->() } | [pa.createdat, pa.status, pa.dueDate]] AS attempts WITH t, collect(ta.userId) AS userIds, collect(reduce(latest = [], x IN attempts | CASE WHEN size(latest) = ? OR x[?] > latest[?] THEN x ELSE latest END)) AS taAttempts WITH collect({id:t.tildaid, trainees:size(userIds), siteUserIds:CASE WHEN (coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType) THEN [] ELSE userIds END, completed:size([a IN taAttempts WHERE size(a) > ? AND a[?] IN $completedStatuses]), overdue:size([a IN taAttempts WHERE size(a) > ? AND NOT a[?] IN $completedStatuses AND a[?] > ? AND a[?] < $nowMillis])}) AS page CALL (page) { UNWIND page AS row UNWIND row.siteUserIds AS uid WITH DISTINCT uid MATCH (u:User{tildaid:uid}) OPTIONAL MATCH (cs:Site{delpending:false})-[:PLAN_DEFINITION_TO_SITE]-(cpd:DslPlanDefinition{delpending:false, studyId:$studyId})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(cssa:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_ACTOR]->(ca:Actor{scopeId:$studyId, state:$activeActorState, delpending:false})-[:FOR_USER]->(u:User) WHERE ca.orgId = cs.orgId AND NOT EXISTS { (cs)-[:SITE_TO_ORGANIZATION]->(siteOrg:Organization) WHERE siteOrg.deltimestamp <> ? } AND ($viewerSiteOrgId = ? OR cs.orgId = $viewerSiteOrgId) WITH uid, collect(DISTINCT cs.tildaid) AS siteIds RETURN apoc.map.fromPairs(collect([uid, siteIds])) AS sitesByUser } UNWIND page AS row RETURN row.id, row.trainees, row.completed, row.overdue, size(apoc.coll.toSet(apoc.coll.flatten([uid IN row.siteUserIds | coalesce(sitesByUser[uid], [])])))
execsvc
5
136 ms
875 ms
660 ms
flat
with datetime($start) as start, datetime($end) as end match (task:Task)-[:BELONGS_TO]->(inbox:Inbox{tildaid:$inboxId}) match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(:Organization{alias:$org}) where (task.due_before >= start …
with datetime($start) as start, datetime($end) as end match (task:Task)-[:BELONGS_TO]->(inbox:Inbox{tildaid:$inboxId}) match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(:Organization{alias:$org}) where (task.due_before >= start and task.due_before <= end and task.state in $lists.states) with task order by task.due_before limit ? with distinct task as t call apoc.do.case([ t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext IN [?, ?, ?], ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ? ], ?, {t: t}) yield value return value, {inbox: {InboxStudy: head([(t)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(ist:Study) | ist])}} as inboxStudy
tusker
7
115 ms
825 ms
537 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
12
97 ms
700 ms
620 ms
0.1× ↓
MATCH (w:WorkOrder {tildaid: $wo}) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(s:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(site:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(creator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_AS…
MATCH (w:WorkOrder {tildaid: $wo}) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(s:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(site:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(creator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ASSIGNEE]->(assignee:User) OPTIONAL MATCH (resolver:User {tildaid: w.resolved_by}) OPTIONAL MATCH (lateAcker:User {tildaid: w.late_reply_ack_by}) OPTIONAL MATCH (parentCfg:WorkOrderConfig {tildaid: w.parent_id}) OPTIONAL MATCH (event:Event {tildaid: w.event_id}) OPTIONAL MATCH (partner:Partner {tildaid: w.recipient_key}) OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(member:User) WITH w, s, site, creator, assignee, resolver, lateAcker, parentCfg, event, partner, member, head([(w)-[:WORK_ORDER_TO_EVENT_SCOPE]->(scope:EventScope) | scope]) AS es OPTIONAL MATCH (es_site:Site {tildaid: es.scopeId}) WHERE es.scopeType = ? RETURN s.name AS studyName, s.protocolnumber AS protocolNumber, COALESCE(site.name, null) AS siteName, head([(s)-[:PLAN_DEFINITION_TO_STUDY]-(pd:DslPlanDefinition {delpending: false})-[:PLAN_DEFINITION_TO_SITE]-(site) | pd.siteNumber]) AS site_number, COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?) AS creatorName, COALESCE(assignee.firstName + ? + assignee.lastName, assignee.email, ?) AS assigneeName, CASE WHEN w.resolved_by = ? THEN ? ELSE COALESCE(resolver.firstName + ? + resolver.lastName, resolver.email) END AS resolvedByName, COALESCE(lateAcker.firstName + ? + lateAcker.lastName, lateAcker.email) AS lateReplyAckByName, parentCfg.title AS parentTitle, event.name AS eventLabel, // Same fragments as WorkOrderConfigRepository.get_child_work_orders / list_work_order_configs; // a standalone WO (no parent config) has neither. CASE WHEN parentCfg IS NULL THEN null WHEN es IS NOT NULL THEN CASE toLower(es.scopeType) WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? ELSE es.scopeType END WHEN coalesce(parentCfg.study_id, ?) = ? THEN ? WHEN toLower(coalesce(parentCfg.entity_type, ?)) = ? THEN ? WHEN toLower(coalesce(parentCfg.entity_type, ?)) = ? THEN ? ELSE ? END AS entityType, CASE WHEN parentCfg IS NULL THEN null WHEN es IS NOT NULL THEN CASE es.scopeType WHEN ? THEN COALESCE(es_site.name, es.name, es.scopeId) WHEN ? THEN COALESCE(es.scopeId, es.name) ELSE COALESCE(s.name, w.study_id) END WHEN w.recipient_key IS NOT NULL AND parentCfg.entity_type = ? THEN coalesce(CASE WHEN partner.name <> ? THEN partner.name END, ?) WHEN w.recipient_key IS NOT NULL AND parentCfg.entity_type = ? THEN coalesce(CASE WHEN trim(coalesce(member.firstName, ?) + ? + coalesce(member.lastName, ?)) <> ? THEN trim(coalesce(member.firstName, ?) + ? + coalesce(member.lastName, ?)) WHEN member.name <> ? THEN member.name WHEN member.email <> ? THEN member.email END, ?) WHEN site IS NOT NULL THEN COALESCE(site.name, w.site_id) ELSE ? END AS entityName, [(w)-[:WORK_ORDER_TO_CONTACT]-(c:User) WHERE c.deltimestamp = ? | {email: c.email, name: c.name}] AS contacts
virtual-assistant
14
56 ms
650 ms
758 ms
flat
MATCH (o:Organization)-[:ORGANIZATION_TO_ACTOR]->(a:Actor)-[:FOR_USER]->(user:User),(a)-[:APPOINTED_AS]->(r:Role) MATCH (user)<-[:FOR_USER]-(studyActor:Actor{orgId: o.tildaid})<-[:STUDY_TO_ACTOR]-(st:Study) WHERE true AND st.tildaid IN $st…
MATCH (o:Organization)-[:ORGANIZATION_TO_ACTOR]->(a:Actor)-[:FOR_USER]->(user:User),(a)-[:APPOINTED_AS]->(r:Role) MATCH (user)<-[:FOR_USER]-(studyActor:Actor{orgId: o.tildaid})<-[:STUDY_TO_ACTOR]-(st:Study) WHERE true AND st.tildaid IN $studyFilterIds AND NOT o.tildaid=$curOrgId AND (:Organization{tildaid:$curOrgId})<-[:STUDY_TO_ORG]-(st)-[:STUDY_TO_ORG]->(o) AND r.name in [$memberRole] WITH o, a, user, COLLECT(DISTINCT r.name) AS roles RETURN user{.*, orgName: o.name, orgId: o.tildaid, roles: roles}
match(org:Organization{tildaid:$id})-[:ORGANIZATION_TO_FORM]->(proto:FormV2{tildaid:$protocolId}) match(proto)-[r:PROTOCOL_TO_GRAPH_NODE_REF]->(ref:GraphNodeRef)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:proto.tildaid}]->(act:ActivityV2) where size(r.path) >= ? return ref.tildaid as refId, act.tildaid as activityId, r.path[-?] as visitId
studiosvc
1
202 ms
485 ms
202 ms
flat
MATCH (d:Document) WHERE d.displayId IS NULL RETURN DISTINCT d.ownerOrgId
MATCH (d:Document) WHERE d.displayId IS NULL RETURN DISTINCT d.ownerOrgId
docsvc
1
262 ms
485 ms
262 ms
flat
MATCH (d:Deliverable) WHERE coalesce(d.tmfTildaId, ?) <> ? AND (d.locationPath IS NULL OR d.locationPath = ?) MATCH (n:Folder|SubArtifact {tildaid: d.tmfTildaId}) WHERE EXISTS { (:FolderManager {deltimestamp: ?})-[:INCLUDES*?..?]->(n) } RE…
MATCH (d:Deliverable) WHERE coalesce(d.tmfTildaId, ?) <> ? AND (d.locationPath IS NULL OR d.locationPath = ?) MATCH (n:Folder|SubArtifact {tildaid: d.tmfTildaId}) WHERE EXISTS { (:FolderManager {deltimestamp: ?})-[:INCLUDES*?..?]->(n) } RETURN DISTINCT d.tildaid AS id, d.tmfTildaId AS tmfId LIMIT $limit
docsvc
1
281 ms
485 ms
281 ms
flat
MATCH (d:Deliverable) WHERE coalesce(d.tmfTildaId, ?) <> ? AND (d.locationPath IS NULL OR d.locationPath = ?) MATCH (n:Folder|SubArtifact {tildaid: d.tmfTildaId}) WHERE EXISTS { (:FolderManager {deltimestamp: ?})-[:INCLUDES*?..?]->(n) } RE…
MATCH (d:Deliverable) WHERE coalesce(d.tmfTildaId, ?) <> ? AND (d.locationPath IS NULL OR d.locationPath = ?) MATCH (n:Folder|SubArtifact {tildaid: d.tmfTildaId}) WHERE EXISTS { (:FolderManager {deltimestamp: ?})-[:INCLUDES*?..?]->(n) } RETURN DISTINCT d.tildaid AS id, d.tmfTildaId AS tmfId LIMIT $limit
docsvc
1
218 ms
485 ms
218 ms
flat
match (s:Study {tildaid: $studyId})-[:STUDY_TO_ACTOR]-(actor:Actor {tildaid: $actorId})-[:FOR_USER]-(user:User)--(i:Inbox)-[:BELONGS_TO]-(t:Task) where t.state in [?, ?] match (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(s) match (t)-[:BELONG…
match (s:Study {tildaid: $studyId})-[:STUDY_TO_ACTOR]-(actor:Actor {tildaid: $actorId})-[:FOR_USER]-(user:User)--(i:Inbox)-[:BELONGS_TO]-(t:Task) where t.state in [?, ?] match (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(s) match (t)-[:BELONGS_TO]-(ui:Inbox)-[:OWNED_BY]-(:User) with distinct i, t, count(distinct ui) as usersForTask return i, collect({task: t, usersForTask: usersForTask}) as tasksWithUsers;
tusker
1
497 ms
485 ms
497 ms
flat
MATCH (d:Document) WHERE d.displayId IS NULL RETURN DISTINCT d.ownerOrgId
MATCH (d:Document) WHERE d.displayId IS NULL RETURN DISTINCT d.ownerOrgId
docsvc
1
334 ms
485 ms
334 ms
flat
WITH $`t` as `t` optional match (t)-[:WITH_CONTEXT]->(cp:CarePlan) optional match (cp)-[:EXECUTE_SOA]-(s:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition{delpending:false})-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) option…
WITH $`t` as `t` optional match (t)-[:WITH_CONTEXT]->(cp:CarePlan) optional match (cp)-[:EXECUTE_SOA]-(s:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition{delpending:false})-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) optional match (p:Patient{tildaid:cp.participantId}) optional match (phi:Phi{tildaid:p.phiid}) optional match (t)-[:CREATED_BY]->(u:User) optional match (t)-[:BELONGS_TO]->(i:Inbox)-[:OWNED_BY]->(au:User) optional match (pd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) optional match (t)-[:CREATED_BY]-(cu:User) with t, st, u, collect(au) as auCollected, cp, site, p, phi, cu as createdBy, pd.siteNumber as siteNumber return {Task: t, Study: st, Phi: phi, User: u, AssignedUser: auCollected, Site: {tildaid: site.tildaid, name: site.name, city: site.city, country: site.country, siteNumber: siteNumber}, Patient: p, CreatedBy: createdBy, CarePlan: cp}
tusker
2
123 ms
470 ms
245 ms
flat
CALL { MATCH (org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_ACTOR]->(oa:Actor)-[:FOR_USER]->(u:User) WHERE oa.state <> ? AND true AND NOT (u)-[:USER_TO_PATIENT]-(:Patient) WITH u, CASE WHEN u IS NOT NULL AND u.name = ? THEN ? + u.ema…
CALL { MATCH (org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_ACTOR]->(oa:Actor)-[:FOR_USER]->(u:User) WHERE oa.state <> ? AND true AND NOT (u)-[:USER_TO_PATIENT]-(:Patient) WITH u, CASE WHEN u IS NOT NULL AND u.name = ? THEN ? + u.email + ? ELSE u.name END as name WITH CASE WHEN u IS NOT NULL THEN {Name: name, Id: u.tildaid} ELSE NULL END AS user order by name WITH collect(DISTINCT user) AS users, count(DISTINCT user) AS userCount WITH userCount, users UNWIND (CASE users WHEN [] then [null] else users end) AS user WITH userCount, user skip $skip limit $limit RETURN userCount, collect(user) AS users } CALL {MATCH (org:Organization{tildaid:$orgId})<-[:SITE_TO_ORGANIZATION]-(si:Site) WITH org, si, [] AS piNames WHERE true WITH CASE WHEN si IS NOT NULL THEN {Name: si.name, Id: si.tildaid, SiteNumber: ?, PINames: piNames} ELSE NULL END AS site order by site.Name WITH collect(DISTINCT site) AS sites, count(DISTINCT site) AS siteCount WITH siteCount, sites UNWIND (CASE sites WHEN [] then [null] else sites end) AS site WITH siteCount, site skip $skip limit $limit RETURN siteCount, collect(site) AS sites } CALL { MATCH (org:Organization{tildaid:$orgId})<-[:STUDY_TO_ORG]-(s:Study) WHERE s.tildaid in $studyScope AND true MATCH (s)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:org.tildaid})-[:FOR_USER]-(u:User {tildaid: $requesterId}) WITH u, s, CASE WHEN studyActor.isBlinded IS NOT NULL THEN CASE WHEN studyActor.isBlinded = true THEN [ {Name: ?, Id: ?}, {Name: ?, Id: ?}, {Name: ?, Id: ?} ] WHEN studyActor.isBlinded = false THEN [ {Name: ?, Id: ?}, {Name: ?, Id: ?} ] END ELSE [] END AS blindedAccessScopes WITH { Name: s.name, Id: s.tildaid, blindedAccessScopes: CASE WHEN s.isBlinded IS NULL OR s.isBlinded = false THEN [] ELSE blindedAccessScopes END } AS study ORDER BY study.Name WITH collect(DISTINCT study) AS studies, count(DISTINCT study) AS studyCount WITH studyCount, studies UNWIND (CASE studies WHEN [] then [null] else studies end) AS study WITH studyCount, study skip $skip limit $limit RETURN studyCount, collect(study) AS studies } CALL { MATCH (org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_PATIENT]->(p:Patient)-[:HAS_PROTECTED_DATA_IN]->(phi:Phi) WHERE coalesce(p.status, ?) <> ? CALL { RETURN null AS patient } CALL { RETURN null AS subject } WITH collect(DISTINCT patient) AS patients, count(DISTINCT patient) AS patientCount, collect(DISTINCT subject) AS subjects, count(DISTINCT subject) AS subjectCount WITH subjectCount, subjects, patientCount, patients UNWIND (CASE patients WHEN [] then [null] else patients end) AS patient WITH subjectCount, subjects, patientCount, patient skip $skip limit $limit WITH subjectCount, subjects, patientCount, collect(patient) AS patients WITH patientCount, patients, subjectCount, subjects UNWIND (CASE subjects WHEN [] then [null] else subjects end) AS subject WITH patientCount, patients, subjectCount, subject skip $skip limit $limit RETURN patientCount, patients, subjectCount, collect(subject) AS subjects } WITH users, sites, studies, patients, subjects, {studies:studyCount, sites:siteCount, patients:patientCount, subjects: subjectCount, users:userCount} AS counts return distinct {studies:studies, sites:sites, patients:patients, subjects:subjects, users:users, counts: counts}
docsvc
2
149 ms
470 ms
287 ms
0.0× ↓
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?? ?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
4
235 ms
470 ms
481 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
2
184 ms
470 ms
319 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
2
218 ms
470 ms
369 ms
flat
MATCH (doc:Document{tildaid:$docId}), (o:Organization{tildaid:$orgId}) WHERE doc.status <> ? OPTIONAL MATCH (doc)<-[:REFERS_TO]-(docShare:DocumentShare)-[:DOC_SHARED_TO_ORG]->(o) WITH doc, docShare, o, CASE WHEN (doc)-[:DOC_TO_ORG]->(o) TH…
MATCH (doc:Document{tildaid:$docId}), (o:Organization{tildaid:$orgId}) WHERE doc.status <> ? OPTIONAL MATCH (doc)<-[:REFERS_TO]-(docShare:DocumentShare)-[:DOC_SHARED_TO_ORG]->(o) WITH doc, docShare, o, CASE WHEN (doc)-[:DOC_TO_ORG]->(o) THEN [doc, doc, null] WHEN docShare IS NOT NULL THEN [docShare, doc, docShare] ELSE [null, null, null] END as result WITH result[?] as docObj, result[?] as d, result[?] as docShare WITH d, docObj, docShare CALL { WITH d OPTIONAL MATCH (d)-[templateUse:USES_TEMPLATE]->(tpl:Document)-[:DOC_TO_ORG]-(tplOrg:Organization) with tpl, tplOrg, case when d.generation = templateUse.documentGeneration then templateUse.templateGenerationUsed else ? end as tplGen return tplGen, tpl, tplOrg } optional match (d)-[:HAS]-(ann:DocumentAnnotation) CALL { WITH d OPTIONAL MATCH (d)-[:DOCUMENT_VERSION]->(dv:DocumentVersion{tildaid:d.primaryVersionId}) OPTIONAL MATCH (d)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv) RETURN CASE WHEN dv IS NOT NULL THEN dv{.*, documentSourceId: ds.tildaid} ELSE d{.*} END as version, ds as documentSource } CALL { WITH d, ann optional match (ann)-[:ANNOTATION_SIGNER]-(ds:DocumentSigner) optional match (ann)-[:ANNOTATION_FIELD]-(df:DocumentField) optional match (ann)-[:ALL_CHANGELOG]->(cl:ChangeLog) with d, ann, ds, df, cl order by cl.createdat asc with d, ann, apoc.convert.fromJsonList(ann.signers) as signersOld, collect(distinct ds{.*}) as signers, collect(distinct df{.*}) as fields , collect(distinct cl{.*}) as auditLog with d, ann, auditLog, case when size(signers) <> ? then signers when size(signers) = ? and signersOld is null then [] else signersOld end as signers1, case when fields is null then [] else fields end as fields1 return ann{.*, signers: signers1, fields: fields1} as annotationSpec, case when d.template = false then ann{.*, signers: signers1, fields: fields1} else null end as annotationVal } CALL { WITH d OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder) WHERE b.binderType = ? return apoc.text.join(collect (b.tag), ?) as tags } CALL { WITH docObj OPTIONAL MATCH (docObj)<-[:INCLUDES]-(fold:Folder{folderType: ?}) WHERE fold.scope IN [?,?] OPTIONAL MATCH (fold)-[:INCLUDES]->(subArt:SubArtifact)<-[:FILED_UNDER_SUBARTIFACT]-(docObj) RETURN {folderId: fold.tildaid, subArtifactId: COALESCE(subArt.tildaid, ?)} as folderDetails } CALL { WITH docObj OPTIONAL MATCH (docObj)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) RETURN stagingInfo } CALL { WITH documentSource OPTIONAL MATCH (documentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) RETURN collect(distinct da{.*, documentSourceId: documentSource.tildaid}) as docAttributes } RETURN distinct d{.*, status: docObj.status}, tags, tpl, tplGen, tplOrg, folderDetails, annotationSpec, annotationVal, docShare, stagingInfo, docAttributes, version, documentSource
docsvc
2
224 ms
470 ms
446 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
3
88 ms
455 ms
263 ms
248.7× ↑
unwind $nodeIds as objid optional match(b:GraphNode{tildaid:objid}) with b optional match(b)-[]-(qs1:QuestionSpec)-[]-(qvar1:VariableV2) optional match(qs1)-[]-(q1:QuestionV2)optional match(b)-[r:GRAPH_NODE_REF_TO_GRAPH_NODE]->(obj2:Respon…
unwind $nodeIds as objid optional match(b:GraphNode{tildaid:objid}) with b optional match(b)-[]-(qs1:QuestionSpec)-[]-(qvar1:VariableV2) optional match(qs1)-[]-(q1:QuestionV2)optional match(b)-[r:GRAPH_NODE_REF_TO_GRAPH_NODE]->(obj2:ResponseSet|QuestionV2) where r.protocolid=$protocolId with b, qs1, qvar1, q1, obj2 optional match(b)-[r:GRAPH_NODE_REF_TO_QUESTION]->(q2:QuestionV2) where r.protocolid=$protocolId with * optional match(obj2)-[]-(qs2:QuestionSpec)-[]-(qvar2:VariableV2) with * optional match(q2)-[]-(qs3:QuestionSpec)-[]-(qvar3:VariableV2) with * with {var1:qvar1, obj:b, qs1:qs1, q1:q1, q2:q2, var2:qvar2, obj2:obj2, qs2:qs2, qs3:qs3, var3: qvar3, questionnaire:null} as obj with collect(obj) as objs optional match(a:GraphNode{tildaid:$graphId})-[:GRAPH_TO_VARIABLE{graphid:$graphId, path:[$graphId]}]-(var:VariableV2) where var.type in [?, ?] with *, case when not var is null then {var1:var, obj:null, qs1: null, q1: null, q2:null, var2:null, obj2:null, qs2:null, qs3:null, var3:null, questionnaire:null} else null end as obj2, objs with collect(obj2) as obj2s, objs with objs, obj2s with objs+obj2s as objList unwind objList as obj with distinct obj as row return row.var1, row.obj, row.qs, row.q1, row.q2, row.var2, row.obj2, row.questionnaire, row.qs2, row.qs3, row.var3
studiosvc
7
134 ms
447 ms
479 ms
flat
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND (b.study_id IN $lists.accessible_study_ids OR coalesce(b.study_id, ?) = ?) AND b.status <> ? OPTIONAL MATCH …
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND (b.study_id IN $lists.accessible_study_ids OR coalesce(b.study_id, ?) = ?) AND b.status <> ? OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(wc:WorkOrder) WHERE wc.delpending = false AND wc.status <> ? AND wc.status IN $lists.child_statuses WITH b, count(wc) AS _match_count WHERE _match_count > ? OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_STUDY]->(study:Study) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CREATOR]->(creator:User) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) WITH b, study, creator, tmpl, b.createdat AS _sort_key ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC LIMIT $fetch_limit WITH b, study, creator, tmpl, _sort_key // Resolve WorkPlanInstance id/name for workplan-created configs (wp_object_id set). // Mirrors get_workplan_info_for_work_order_config: cfg.wp_object_id -> milestone item // (by tildaid or cfgMilestoneItemId) -> WorkPlanInstance. Non-workplan configs yield null. OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone) -[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem:WorkplanInstanceMilestoneItem) WHERE coalesce(b.wp_object_id, ?) <> ? AND (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id) WITH b, study, creator, tmpl, _sort_key, head(collect(wpInstance.tildaid)) AS workplan_id, head(collect(wpInstance.name)) AS workplan_name OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(w:WorkOrder) WHERE w.delpending = false AND w.status <> ? AND w.status IN $lists.child_statuses OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(wStudy:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(wSite:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(wCreator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ASSIGNEE]->(wAssignee:User) // Resolve EventScope for Event-type child WOs (null for Standard-type) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_EVENT_SCOPE]->(wES:EventScope) // Look up the Site node only for Site-scoped events; guards against null wES and non-Site scopes. // wES.scopeId is a site tildaid (UUID) for Site scopes, country name for Country, ? for Trial. OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ? OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key}) OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_TASK]->(wTask:WorkOrderTask) WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, w, wStudy, wSite, wCreator, wAssignee, wES, wES_Site, wPartner, wMember, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_pending_count, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_resolved_count // ENG-?: order children in the DB so children_limit keeps the first N in // DISPLAY order. collect() below preserves incoming row order. // The PARENT keyset terms MUST lead. This ORDER BY runs in a WITH stage AFTER // the parent ORDER BY/LIMIT, and a later ORDER BY re-sorts the whole row stream // — ordering on the child value alone silently re-ranked the CONFIGS by their // child?s QUERY order, not display order: the backward // reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC, (w.createdat IS NULL), w.createdat DESC, w.tildaid WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, collect(DISTINCT CASE WHEN w IS NOT NULL THEN { tildaid: w.tildaid, work_order_id: w.work_order_id, title: w.title, work_order_type: w.work_order_type, status: w.status, priority: w.priority, creator_id: w.creator_id, assigned_to_id: w.assigned_to_id, site_id: w.site_id, study_id: w.study_id, document_ids: w.document_ids, contact_list: w.contact_list, cc_list: w.cc_list, goal: w.goal, due_date: w.due_date, last_reachout: w.last_reachout, last_response: w.last_response, resolved_at: w.resolved_at, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
writevirtual-assistant
5
96 ms
425 ms
263 ms
2.7× ↑
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 }) AND (NOT ((coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType)) OR (t.orgId <> ? AND t.orgId = $callerOrgId)) AND (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) RETURN DISTINCT u.tildaid AS id, u.name AS name ORDER BY name, id SKIP $skip LIMIT $limit
execsvc
5
46 ms
425 ms
229 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 }) AND (NOT ((coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType)) OR (t.orgId <> ? AND t.orgId = $callerOrgId)) AND (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) RETURN count(DISTINCT u.tildaid)
execsvc
5
42 ms
425 ms
207 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
6
55 ms
410 ms
298 ms
flat
MATCH (w:WorkOrder {tildaid: $work_order_id}) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (w)-[r:WORK_ORDER_TO_DOCUMENT]->(doc:Document) OPTIONAL MATCH (doc)<-[:RECOMMENDATION_TO_DOCUMENT]-(rec:Recom…
MATCH (w:WorkOrder {tildaid: $work_order_id}) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (w)-[r:WORK_ORDER_TO_DOCUMENT]->(doc:Document) OPTIONAL MATCH (doc)<-[:RECOMMENDATION_TO_DOCUMENT]-(rec:Recommendation) WHERE toLower(coalesce(rec.category,?)) = ? AND toLower(coalesce(rec.status,?)) <> ? AND (rec.isIgnoredBySupport IS NULL OR rec.isIgnoredBySupport = false) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(act:WorkOrderActivity)-[:WORK_ORDER_ACTIVITY_TO_DOCUMENT]->(doc) WITH w, org, doc, r, rec, act WITH w, org, doc, r, COLLECT(DISTINCT { rec_id: rec.rec_id, category: rec.category, status: rec.status, severity: rec.severity, explanation:coalesce(rec.data, rec.explanation), user_action: rec.user_action, from_todo_discard: coalesce(rec.data_formatted, ?) CONTAINS ? }) AS recommendations, coalesce(doc.rejectReason, ?) AS rejectReason, ANY(x IN COLLECT(rec) WHERE toLower(coalesce(x.status,?)) IN [?, ?] AND toLower(coalesce(x.severity, ?)) <> ? ) AS has_qc_issues, COLLECT(DISTINCT { activity_id: act.tildaid, activity_type: act.activity_type, createdat: act.createdat }) AS doc_activities WITH org, COLLECT(DISTINCT { tildaid: doc.tildaid, fileName: doc.fileName, contentType: doc.contentType, template: doc.template, has_fillable_annotation: EXISTS { (doc)-[:HAS]->(:DocumentAnnotation) }, direction: coalesce(r.direction, null), // ENG-?: agents answer ? from this, not from status. collection: doc.collection, createdat: coalesce(doc.createdat, doc.created_at, r.createdat, r.created_at, null), document_family_id: coalesce(r.document_family_id, null), rejectReason: rejectReason, recommendations: recommendations, status: coalesce(r.status, CASE WHEN doc.tildaid IS NULL THEN null WHEN rejectReason <> ? THEN ? WHEN has_qc_issues THEN ? ELSE ? END ), // SUP-?: project the hold metadata alongside the status. Without it // GET /work-orders/{id}, the WorkOrder SSE payload and the agent // context all showed status ? with hold_reason null — the // reviewer could see a document was parked but never why, and the // agent had no reason text to mention informationally. hold_reason: r.hold_reason, held_at: r.held_at, // SUP-?: drives `site_announced` in the agent?On Hold?no?yes' END) ELSE null END, activities: doc_activities, sentat: r.sentat }) AS documents RETURN org, documents
virtual-assistant
14
23 ms
290 ms
218 ms
flat
UNWIND $taskIds AS tid MATCH (task:Task{tildaid:tid})- [:WITH_CONTEXT]->(pv:ParticipantVisit)<-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)- [:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)…
UNWIND $taskIds AS tid MATCH (task:Task{tildaid:tid})- [:WITH_CONTEXT]->(pv:ParticipantVisit)<-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)- [:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ACTOR]-> (a:Actor)-[:IS_PRIMARY_ROLE]->(r:Role{name:$role}) MATCH (a)-[:FOR_USER]->(user:User) WITH tid, collect(distinct user.email) as emails WHERE size(emails) = ? RETURN tid, emails[?]
tusker
15
60 ms
275 ms
268 ms
3.3× ↑
match (u:User{email:$userEmail})<-[:OWNED_BY]-(inbox:Inbox{type:?})<-[:BELONGS_TO]-(task:Task{state:?}) match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(:Organization{alias:$org}) match (task)-[:WITH_CONTEXT]->() return count(d…
match (u:User{email:$userEmail})<-[:OWNED_BY]-(inbox:Inbox{type:?})<-[:BELONGS_TO]-(task:Task{state:?}) match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(:Organization{alias:$org}) match (task)-[:WITH_CONTEXT]->() return count(distinct task)
tusker
1
128 ms
195 ms
128 ms
flat
match(org:Organization{tildaid:$id})-[:ORGANIZATION_TO_FORM]->(proto:FormV2{tildaid:$protocolId}) match(proto)-[rp:GRAPH_NEXT*?..{graphid:$protocolId1}]->(pref:GraphNode) where rp[-?].path[?]=proto.tildaid and pref.objtype=? match(pref)-[:…
match(org:Organization{tildaid:$id})-[:ORGANIZATION_TO_FORM]->(proto:FormV2{tildaid:$protocolId}) match(proto)-[rp:GRAPH_NEXT*?..{graphid:$protocolId1}]->(pref:GraphNode) where rp[-?].path[?]=proto.tildaid and pref.objtype=? match(pref)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:$protocolId2}]->(visit:VisitV2) optional match(pref)-[:ANCHORED_TO{protocolid:$protocolId2}]->(ar:GraphNodeRef) with visit, min(ar.tildaid) as anchorRefId return visit, anchorRefId, head([(:GraphNodeRef{tildaid:anchorRefId})-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:$protocolId2}]->(av:VisitV2) | [av.tildaid, CASE WHEN coalesce(av.title, ?) <> ? THEN av.title ELSE av.name END]]) as anchorVisit
studiosvc
1
132 ms
195 ms
132 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
1
105 ms
195 ms
105 ms
flat
MATCH (dv:DocumentVersion) WHERE dv.originalFileName IS NULL WITH dv LIMIT $batchSize OPTIONAL MATCH (d:Document)-[:DOCUMENT_VERSION]->(dv) CALL { WITH dv, d OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]->(vch:ChangeLog) WHERE vch.oldValue CONTAINS…
MATCH (dv:DocumentVersion) WHERE dv.originalFileName IS NULL WITH dv LIMIT $batchSize OPTIONAL MATCH (d:Document)-[:DOCUMENT_VERSION]->(dv) CALL { WITH dv, d OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]->(vch:ChangeLog) WHERE vch.oldValue CONTAINS ? WITH dv, d, collect(vch) AS vchs OPTIONAL MATCH (d)-[:ALL_CHANGELOG]->(dch:ChangeLog) WHERE d IS NOT NULL AND dch.oldValue CONTAINS ? AND NOT EXISTS { MATCH (d)-[:DOCUMENT_VERSION]->(prev:DocumentVersion) WHERE coalesce(prev.versionNumber, ?) < coalesce(dv.versionNumber, ?) } WITH vchs, collect(dch) AS dchs WITH [ch IN (vchs + dchs) WHERE ch IS NOT NULL] AS chs UNWIND CASE WHEN size(chs) = ? THEN [null] ELSE chs END AS ch WITH ch ORDER BY ch.createdat ASC, CASE WHEN ch.oldValue CONTAINS ? THEN ? ELSE ? END WITH head(collect(ch)) AS clog RETURN clog.tildaid AS clogId, clog.oldValue AS oldValue } RETURN dv.tildaid, clogId, oldValue
docsvc
1
138 ms
195 ms
138 ms
flat
match(a:Study{delpending:false})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) with a optional match(a)-[scope:STUDY_TO_ACTOR]->(actor:Actor{orgId:$orgId,userId:$userId,delpending:false})-[APPOINTED_AS]-(role:Role) with a, role return…
match(a:Study{delpending:false})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) with a optional match(a)-[scope:STUDY_TO_ACTOR]->(actor:Actor{orgId:$orgId,userId:$userId,delpending:false})-[APPOINTED_AS]-(role:Role) with a, role return a, collect(role.displayName) as roles, COUNT { (a)-[:PLAN_DEFINITION_TO_STUDY]-(:DslPlanDefinition{type:?,delpending:false}) } as siteCount skip $sbSkip limit $sbLimit
enrollsvc
1
110 ms
195 ms
110 ms
flat
MATCH (o:Organization{tildaid:$scopeId})-[scope:ORGANIZATION_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? AND NOT r.name = ? AND (r.exte…
MATCH (o:Organization{tildaid:$scopeId})-[scope:ORGANIZATION_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? AND NOT r.name = ? AND (r.external IS NULL OR r.external = false) with distinct o,a,r,u,scope with o,a,u,scope,r order by r.displayName with o,a,u,scope,collect(distinct case when r.listed = true then r.displayName end) as displayRoles with o,a,u,scope,displayRoles order by a.createdat, a.tildaid limit $qpLimit RETURN apoc.create.vNode([?], { id: a.tildaid, role: a.roles, displayRoles: CASE WHEN size(displayRoles) = ? THEN [?] ELSE displayRoles END, state: a.state, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, userEmail: u.email, userId: u.tildaid, userName: u.name, userContact: u.contact, lastActivity: u.lastActivity, profilePicURL: u.profilePicURL, userTitle: u.title, createdat: scope.createdat, isBlinded: a.isBlinded , Title: scope.Title, labelIds: [(a)-[:ACTOR_TO_MEMBER_LABEL]->(ml:MemberLabel{delpending:false}) | ml.tildaid] }) as node
writeonboardingsvc
1
153 ms
195 ms
153 ms
flat
match (j:Job)-[:JOB_TO_JOBSTATE]-(js:JobState) WHERE NOT js.status=$doneStatus and j.stopped=false return j
match (j:Job)-[:JOB_TO_JOBSTATE]-(js:JobState) WHERE NOT js.status=$doneStatus and j.stopped=false return j
notifsvc
3
128 ms
192 ms
169 ms
flat
WITH $`t` as `t` optional match (t)-[:WITH_CONTEXT]->(:StudySiteActor) optional match (t)-[:BELONGS_TO]->(i:Inbox)-[:OWNED_BY]->(au:User) optional match (t)-[:BELONGS_TO]->(:Inbox)-[:MANAGED_BY]->(st:Study) optional match (t)-[:BELONGS_TO]…
WITH $`t` as `t` optional match (t)-[:WITH_CONTEXT]->(:StudySiteActor) optional match (t)-[:BELONGS_TO]->(i:Inbox)-[:OWNED_BY]->(au:User) optional match (t)-[:BELONGS_TO]->(:Inbox)-[:MANAGED_BY]->(st:Study) optional match (t)-[:BELONGS_TO]->(:Inbox)-[:MANAGED_BY]->(site:Site) optional match (t)-[:CREATED_BY]-(cu:User) with t, collect(au) as auCollected, st, site, cu as createdBy return {Task: t, AssignedUser: auCollected, Study: st, Site: site, CreatedBy: createdBy}
tusker
2
50 ms
190 ms
100 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
2
89 ms
190 ms
101 ms
0.5× ↓
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND (b.study_id IN $lists.accessible_study_ids OR coalesce(b.study_id, ?) = ?) AND b.status <> ? OPTIONAL MATCH …
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND (b.study_id IN $lists.accessible_study_ids OR coalesce(b.study_id, ?) = ?) AND b.status <> ? OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(wc:WorkOrder) WHERE wc.delpending = false AND wc.status <> ? AND wc.status IN $lists.child_statuses WITH b, count(wc) AS _match_count WHERE _match_count > ? OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_STUDY]->(study:Study) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CREATOR]->(creator:User) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) WITH b, study, creator, tmpl, b.createdat AS _sort_key ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC LIMIT $fetch_limit WITH b, study, creator, tmpl, _sort_key // Resolve WorkPlanInstance id/name for workplan-created configs (wp_object_id set). // Mirrors get_workplan_info_for_work_order_config: cfg.wp_object_id -> milestone item // (by tildaid or cfgMilestoneItemId) -> WorkPlanInstance. Non-workplan configs yield null. OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone) -[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem:WorkplanInstanceMilestoneItem) WHERE coalesce(b.wp_object_id, ?) <> ? AND (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id) WITH b, study, creator, tmpl, _sort_key, head(collect(wpInstance.tildaid)) AS workplan_id, head(collect(wpInstance.name)) AS workplan_name OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(w:WorkOrder) WHERE w.delpending = false AND w.status <> ? AND w.status IN $lists.child_statuses OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(wStudy:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(wSite:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(wCreator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ASSIGNEE]->(wAssignee:User) // Resolve EventScope for Event-type child WOs (null for Standard-type) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_EVENT_SCOPE]->(wES:EventScope) // Look up the Site node only for Site-scoped events; guards against null wES and non-Site scopes. // wES.scopeId is a site tildaid (UUID) for Site scopes, country name for Country, ? for Trial. OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ? OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key}) OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_TASK]->(wTask:WorkOrderTask) WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, w, wStudy, wSite, wCreator, wAssignee, wES, wES_Site, wPartner, wMember, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_pending_count, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_resolved_count // ENG-?: order children in the DB so children_limit keeps the first N in // DISPLAY order. collect() below preserves incoming row order. // The PARENT keyset terms MUST lead. This ORDER BY runs in a WITH stage AFTER // the parent ORDER BY/LIMIT, and a later ORDER BY re-sorts the whole row stream // — ordering on the child value alone silently re-ranked the CONFIGS by their // child?s QUERY order, not display order: the backward // reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC, (w.createdat IS NULL), w.createdat DESC, w.tildaid WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, collect(DISTINCT CASE WHEN w IS NOT NULL THEN { tildaid: w.tildaid, work_order_id: w.work_order_id, title: w.title, work_order_type: w.work_order_type, status: w.status, priority: w.priority, creator_id: w.creator_id, assigned_to_id: w.assigned_to_id, site_id: w.site_id, study_id: w.study_id, document_ids: w.document_ids, contact_list: w.contact_list, cc_list: w.cc_list, goal: w.goal, due_date: w.due_date, last_reachout: w.last_reachout, last_response: w.last_response, resolved_at: w.resolved_at, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
writevirtual-assistant
2
108 ms
190 ms
196 ms
flat
MATCH (u:User {tildaid: $userId}) OPTIONAL MATCH (u)-[:SUPPORTS_ORG]-(so:Organization) WITH collect(DISTINCT so.tildaid) AS supportedIds MATCH (o:Organization) WHERE o.delpending = false AND (NOT $hideManaged OR coalesce(o.status, ?) <> $m…
MATCH (u:User {tildaid: $userId}) OPTIONAL MATCH (u)-[:SUPPORTS_ORG]-(so:Organization) WITH collect(DISTINCT so.tildaid) AS supportedIds MATCH (o:Organization) WHERE o.delpending = false AND (NOT $hideManaged OR coalesce(o.status, ?) <> $managedStatus) 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
58 ms
190 ms
110 ms
flat
UNWIND $userIds AS uid MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{studyId:$studyId, deltimestamp:?}) WHERE (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivit…
UNWIND $userIds AS uid MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{studyId:$studyId, deltimestamp:?}) WHERE (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) AND (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) MATCH (training:Training)-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, 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 (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
execsvc
3
51 ms
185 ms
146 ms
flat
MATCH (p:Patient {tildaid: $patientId})-[:LEAD_TO_PATIENT]-(l:Lead)-[:LEAD_TO_STUDY]-(st:Study) -[:STUDY_TO_ORG]-(o:Organization) with distinct o order by o.name return o.twilioNumber,o.contactPhone limit ?
MATCH (p:Patient {tildaid: $patientId})-[:LEAD_TO_PATIENT]-(l:Lead)-[:LEAD_TO_STUDY]-(st:Study) -[:STUDY_TO_ORG]-(o:Organization) with distinct o order by o.name return o.twilioNumber,o.contactPhone limit ?
notifsvc
3
82 ms
185 ms
193 ms
1.9× ↑
UNWIND $changeLogs AS log MATCH (obj:StudySiteActor{tildaid:log.scopeId}) OPTIONAL MATCH (obj)-[r:TAIL_CHANGELOG]-(tcl:ChangeLog) CREATE (cl:ChangeLog { tildaid: randomUUID(), createdat: timestamp(), scopeId: log.scopeId, type: log.type, c…
UNWIND $changeLogs AS log MATCH (obj:StudySiteActor{tildaid:log.scopeId}) OPTIONAL MATCH (obj)-[r:TAIL_CHANGELOG]-(tcl:ChangeLog) CREATE (cl:ChangeLog { tildaid: randomUUID(), createdat: timestamp(), scopeId: log.scopeId, type: log.type, changedBy: log.changedBy, who: log.who, email: log.email, oldValue: log.oldValue, newValue: log.newValue, operation: log.operation, blob: log.blob, reason: log.reason, description: log.description, scribedForUserID: log.scribedForUserID, category: log.category, level: toInteger(log.level) }) DELETE r MERGE (obj)-[r1:TAIL_CHANGELOG]->(cl) ON CREATE SET r1.tildaid = randomUUID(), r1.createdat = timestamp() ON MATCH SET r1.updatedat = timestamp() MERGE (obj)-[r2:ALL_CHANGELOG]->(cl) ON CREATE SET r2.tildaid = randomUUID(), r2.createdat = timestamp() ON MATCH SET r2.updatedat = timestamp()
writeexecsvc
3
37 ms
185 ms
104 ms
flat
MATCH (cfg:WorkplanCfg)-[:WORKPLAN_CFG_TO_INSTANCE]->(wp:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone)-[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(item:WorkplanInstanceMilestoneItem) WHERE coalesce(item.depende…
MATCH (cfg:WorkplanCfg)-[:WORKPLAN_CFG_TO_INSTANCE]->(wp:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone)-[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(item:WorkplanInstanceMilestoneItem) WHERE coalesce(item.dependencyResolutionRequestedAt, ?) > ? AND $nowMs - item.dependencyResolutionRequestedAt >= $windowMs AND wp.state <> ? AND NOT EXISTS { MATCH (ctx:WorkPlanContext{workplanConfigId: wp.tildaid, contextType:?}) } OPTIONAL MATCH (org:Organization {tildaid: cfg.orgid}) RETURN DISTINCT wp.tildaid AS id, cfg.tildaid AS cfgId, coalesce(org.alias, ?) AS orgAlias LIMIT $limit
workplansvc
5
22 ms
175 ms
106 ms
flat
MATCH (a:WorkOrderActivity {tildaid: $id}) WHERE a.activity_type <> ? OPTIONAL MATCH (wo:WorkOrder)-[:WORK_ORDER_TO_ACTIVITY]->(a) WITH a, head(collect(wo)) AS wo OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTION…
MATCH (a:WorkOrderActivity {tildaid: $id}) WHERE a.activity_type <> ? OPTIONAL MATCH (wo:WorkOrder)-[:WORK_ORDER_TO_ACTIVITY]->(a) WITH a, head(collect(wo)) AS wo OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_SITE]->(site:Site) // Template lineage of the parent WO (parent_id -> WorkOrderConfig -> WorkOrderTemplate), denormalized. OPTIONAL MATCH (:WorkOrderConfig {tildaid: wo.parent_id})-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) CALL apoc.create.vNode([?], { tildaid: a.tildaid, workOrderActivityId: a.tildaid, workOrderId: a.work_order_id, userId: a.user_id, activityType: a.activity_type, status: a.status, source: a.source, content: a.content, original_content: a.original_content, reasoning: a.reasoning, content_type: a.content_type, body_plain: a.body_plain, body_html: a.body_html, stripped_text: a.stripped_text, stripped_html: a.stripped_html, extra_text: a.extra_text, extra_html: a.extra_html, message_id: a.message_id, sender: a.sender, from_ai_teammate_email: a.from_ai_teammate_email, from_ai_teammate_name: a.from_ai_teammate_name, attachmentInfo: a.attachmentInfo, ignoreType: a.ignoreType, auto_send_enabled: a.auto_send_enabled, documents_pending: a.documents_pending, processing_status: a.processing_status, retry_count: a.retry_count, last_attempt_at: a.last_attempt_at, openai_response_id: a.openai_response_id, errors: a.errors, questions: a.questions, answers: a.answers, answered_at: a.answered_at, agent_run_completed_at: a.agent_run_completed_at, timestamp: a.timestamp, createdat: a.createdat, updatedat: a.updatedat, // Parent work order context: immutable created timestamp (epoch millis) + business id, denormalized // so time-to-response style metrics can be computed from the activities table alone. workOrderCreatedAt: wo.createdat, workOrderBusinessId: wo.work_order_id, workOrderName: wo.title, templateId: tmpl.tildaid, templateName: tmpl.title, docIds: a.docIds, processedDocIds: a.processedDocIds, activity_contact_list: a.activity_contact_list, activity_cc_list: a.activity_cc_list, delpending: a.delpending, deltimestamp: a.deltimestamp, holdbitmap: a.holdbitmap, orgId: coalesce(org.tildaid, wo.org_id), orgName: org.name, orgAlias: org.alias, studyId: coalesce(study.tildaid, wo.study_id), studyName: study.name, siteId: coalesce(site.tildaid, wo.site_id), siteName: site.name } ) YIELD node RETURN node
writedashrsvc
5
30 ms
175 ms
145 ms
flat
MATCH (root:GraphNode{tildaid:$graphId})-[:GRAPH_TO_ACTIVATION_RULE_CHAIN{graphid:$graphId}]->(chain:ActivationRuleChain) MATCH (rule:ActivationRule)-[ruleRel:ACTIVATION_RULE_TO_CHAIN{graphid:$graphId}]->(chain) MATCH (rule)-[:ACTIVATION_R…
MATCH (root:GraphNode{tildaid:$graphId})-[:GRAPH_TO_ACTIVATION_RULE_CHAIN{graphid:$graphId}]->(chain:ActivationRuleChain) MATCH (rule:ActivationRule)-[ruleRel:ACTIVATION_RULE_TO_CHAIN{graphid:$graphId}]->(chain) MATCH (rule)-[:ACTIVATION_RULE_TO_BRANCH_CONFIG{graphid:$graphId}]->(brConfig:BranchConfig) OPTIONAL MATCH (brConfig)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropValue}]->(cfgRef:GraphNodeRef) WITH chain, rule, ruleRel, brConfig, collect(DISTINCT cfgRef.tildaid) as branchCfgRefIds MATCH (ref:GraphNode)-[:GRAPH_NODE_TO_ACTIVATION_RULE_CHAIN{graphid:$graphId}]->(chain) OPTIONAL MATCH (ref)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{graphid:$relPropValue}]->(gnode:GraphNode) WHERE ref.objtype = ? OPTIONAL MATCH (ref)-[:GRAPH_NODE_REF_TO_QUESTION{graphid:$relPropValue}]->(question:QuestionV2) WHERE gnode.objtype = ? RETURN {chain:chain, rule:rule, ruleType:ruleRel.type, branchConfig:brConfig, branchCfgRefIds:branchCfgRefIds, refNodeId:CASE WHEN gnode.objtype = ? THEN question.tildaid ELSE coalesce(gnode.tildaid, ref.tildaid) END}
execsvc
5
35 ms
175 ms
174 ms
flat
unwind $nodeIds as objid optional match(b:GraphNode{tildaid:objid}) with b optional match(b)-[]-(qs1:QuestionSpec)-[]-(qvar1:VariableV2) optional match(qs1)-[]-(q1:QuestionV2)optional match(b)-[r:GRAPH_NODE_REF_TO_GRAPH_NODE]->(obj2:Respon…
unwind $nodeIds as objid optional match(b:GraphNode{tildaid:objid}) with b optional match(b)-[]-(qs1:QuestionSpec)-[]-(qvar1:VariableV2) optional match(qs1)-[]-(q1:QuestionV2)optional match(b)-[r:GRAPH_NODE_REF_TO_GRAPH_NODE]->(obj2:ResponseSet|QuestionV2) where r.protocolid=$protocolId with b, qs1, qvar1, q1, obj2 optional match(b)-[r:GRAPH_NODE_REF_TO_QUESTION]->(q2:QuestionV2) where r.protocolid=$protocolId with * optional match(obj2)-[]-(qs2:QuestionSpec)-[]-(qvar2:VariableV2) with * optional match(q2)-[]-(qs3:QuestionSpec)-[]-(qvar3:VariableV2) with * with {var1:qvar1, obj:b, qs1:qs1, q1:q1, q2:q2, var2:qvar2, obj2:obj2, qs2:qs2, qs3:qs3, var3: qvar3, questionnaire:null} as obj with collect(obj) as objs optional match(a:GraphNode{tildaid:$graphId})-[:GRAPH_TO_VARIABLE{graphid:$graphId, path:[$graphId]}]-(var:VariableV2) where var.type in [?, ?] with *, case when not var is null then {var1:var, obj:null, qs1: null, q1: null, q2:null, var2:null, obj2:null, qs2:null, qs3:null, var3:null, questionnaire:null} else null end as obj2, objs with collect(obj2) as obj2s, objs with objs, obj2s with objs+obj2s as objList unwind objList as obj with distinct obj as row return row.var1, row.obj, row.qs, row.q1, row.q2, row.var2, row.obj2, row.questionnaire, row.qs2, row.qs3, row.var3
studiosvc
6
34 ms
170 ms
179 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
6
63 ms
170 ms
102 ms
1.9× ↑
with datetime($start) as start, datetime($end) as end match (task:Task)-[:BELONGS_TO]->(inbox:Inbox{tildaid:$inboxId}) match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(:Organization{alias:$org}) where (task.due_before >= start …
with datetime($start) as start, datetime($end) as end match (task:Task)-[:BELONGS_TO]->(inbox:Inbox{tildaid:$inboxId}) match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(:Organization{alias:$org}) where (task.due_before >= start and task.due_before <= end and task.state in $lists.states) with task order by task.due_before limit ? with distinct task as t call apoc.do.case([ t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext IN [?, ?, ?], ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ? ], ?, {t: t}) yield value return value, {inbox: {InboxStudy: head([(t)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(ist:Study) | ist])}} as inboxStudy
tusker
6
31 ms
170 ms
152 ms
0.2× ↓
unwind $nodeIds as objid optional match(b:GraphNode{tildaid:objid}) with b optional match(b)-[]-(qs1:QuestionSpec)-[]-(qvar1:VariableV2) optional match(qs1)-[]-(q1:QuestionV2)optional match(b)-[r:GRAPH_NODE_REF_TO_GRAPH_NODE]->(obj2:Respon…
unwind $nodeIds as objid optional match(b:GraphNode{tildaid:objid}) with b optional match(b)-[]-(qs1:QuestionSpec)-[]-(qvar1:VariableV2) optional match(qs1)-[]-(q1:QuestionV2)optional match(b)-[r:GRAPH_NODE_REF_TO_GRAPH_NODE]->(obj2:ResponseSet|QuestionV2) where r.protocolid=$protocolId with b, qs1, qvar1, q1, obj2 optional match(b)-[r:GRAPH_NODE_REF_TO_QUESTION]->(q2:QuestionV2) where r.protocolid=$protocolId with * optional match(obj2)-[]-(qs2:QuestionSpec)-[]-(qvar2:VariableV2) with * optional match(q2)-[]-(qs3:QuestionSpec)-[]-(qvar3:VariableV2) with * with {var1:qvar1, obj:b, qs1:qs1, q1:q1, q2:q2, var2:qvar2, obj2:obj2, qs2:qs2, qs3:qs3, var3: qvar3, questionnaire:null} as obj with collect(obj) as objs optional match(a:GraphNode{tildaid:$graphId})-[:GRAPH_TO_VARIABLE{graphid:$graphId, path:[$graphId]}]-(var:VariableV2) where var.type in [?, ?] with *, case when not var is null then {var1:var, obj:null, qs1: null, q1: null, q2:null, var2:null, obj2:null, qs2:null, qs3:null, var3:null, questionnaire:null} else null end as obj2, objs with collect(obj2) as obj2s, objs with objs, obj2s with objs+obj2s as objList unwind objList as obj with distinct obj as row return row.var1, row.obj, row.qs, row.q1, row.q2, row.var2, row.obj2, row.questionnaire, row.qs2, row.qs3, row.var3
studiosvc
7
28 ms
165 ms
186 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
19 ms
155 ms
152 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
12
46 ms
140 ms
132 ms
2.0× ↑
MATCH (st:Study {tildaid: $study_id})-[:USES]->(:FolderManager)-[:INCLUDES]->(tmf:Folder {name:?}) WHERE tmf.level = ? MATCH (tmf)-[:INCLUDES*?..?]->(d:Document) RETURN DISTINCT d.tildaid AS doc_id
MATCH (st:Study {tildaid: $study_id})-[:USES]->(:FolderManager)-[:INCLUDES]->(tmf:Folder {name:?}) WHERE tmf.level = ? MATCH (tmf)-[:INCLUDES*?..?]->(d:Document) RETURN DISTINCT d.tildaid AS doc_id
virtual-assistant
14
14 ms
130 ms
144 ms
flat
UNWIND $cfgIds AS cfgId MATCH (cfg:BranchConfig {tildaid: cfgId}) OPTIONAL MATCH (cfg)-[:BRANCH_CG_ALL_NEXT*?..]->(gAll:BranchConditionGroup)-[:CG_TO_CONDITION*?..]->(allCond:BranchCondition) WITH cfgId, collect(DISTINCT {kind:?, g:gAll, c…
UNWIND $cfgIds AS cfgId MATCH (cfg:BranchConfig {tildaid: cfgId}) OPTIONAL MATCH (cfg)-[:BRANCH_CG_ALL_NEXT*?..]->(gAll:BranchConditionGroup)-[:CG_TO_CONDITION*?..]->(allCond:BranchCondition) WITH cfgId, collect(DISTINCT {kind:?, g:gAll, c:allCond}) AS allItems OPTIONAL MATCH (cfg2:BranchConfig {tildaid: cfgId})-[:BRANCH_CG_ANY_NEXT*?..]->(gAny:BranchConditionGroup)-[:CG_TO_CONDITION*?..]->(anyCond:BranchCondition) WITH cfgId, allItems, collect(DISTINCT {kind:?, g:gAny, c:anyCond}) AS anyItems WITH cfgId, allItems + anyItems AS items UNWIND items AS item WITH cfgId, item.kind AS kind, item.g AS g, item.c AS c WHERE c IS NOT NULL OPTIONAL MATCH (ref:GraphNodeRef {tildaid:c.refid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION {protocolid:$protocolId}]->(q:QuestionV2) RETURN cfgId AS cfgId, kind AS groupKind, coalesce(g.tildaid, ?) AS groupId, coalesce(g.matchall, false) AS groupMatchAll, coalesce(c.tildaid, ?) AS conditionId, coalesce(c.refid, ?) AS refId, coalesce(c.variableid, ?) AS variableId, coalesce(c.operator, ?) AS operator, coalesce(c.choices, ?) AS choices, coalesce(c.choicevaluesjson, ?) AS choiceValuesJson, coalesce(c.matchall, false) AS conditionMatchAll, coalesce(head([(:GraphNodeRef {tildaid: c.visitrefid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE {protocolid: $protocolId}]->(v:VisitV2) | v.tildaid]), c.visitid, ?) AS visitId, coalesce(head([(:GraphNodeRef {tildaid: c.activityrefid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE {protocolid: $protocolId}]->(a:ActivityV2) | a.tildaid]), c.activityid, ?) AS activityId, coalesce(head([(:GraphNodeRef {tildaid: c.questionnairerefid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE {protocolid: $protocolId}]->(qnr:QuestionnaireV2) | qnr.tildaid]), c.questionnaireid, ?) AS questionnaireId, coalesce(c.tableid, ?) AS tableId, coalesce(q.tildaid, ?) AS questionId
execsvc
32
13 ms
120 ms
194 ms
flat
match(a:Study{delpending:false,status:$status})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) where tolower(a.name) contains tolower($searchStr) with a optional match(a)-[scope:STUDY_TO_ACTOR]->(actor:Actor{orgId:$orgId,userId:$userId…
match(a:Study{delpending:false,status:$status})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) where tolower(a.name) contains tolower($searchStr) 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
1
65 ms
98 ms
65 ms
flat
match(a:Study{delpending:false,status:$status})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) with a optional match(a)-[scope:STUDY_TO_ACTOR]->(actor:Actor{orgId:$orgId,userId:$userId,delpending:false})-[APPOINTED_AS]-(role:Role) with…
match(a:Study{delpending:false,status:$status})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) with a optional match(a)-[scope:STUDY_TO_ACTOR]->(actor:Actor{orgId:$orgId,userId:$userId,delpending:false})-[APPOINTED_AS]-(role:Role) with a, role return a, collect(role.displayName) as roles, COUNT { (a)-[:PLAN_DEFINITION_TO_STUDY]-(:DslPlanDefinition{type:?,delpending:false}) } as siteCount skip $sbSkip limit $sbLimit
enrollsvc
1
86 ms
98 ms
86 ms
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
1
76 ms
98 ms
76 ms
flat
WITH $`t` as `t` optional match (t)-[:CREATED_BY]->(u:User) optional match (t)-[:BELONGS_TO]->(i:Inbox)-[:OWNED_BY]->(au:User) optional match (t)-[:CREATED_BY]-(cu:User) with t, u, collect(au) as auCollected, cu as createdBy return {Task: …
WITH $`t` as `t` optional match (t)-[:CREATED_BY]->(u:User) optional match (t)-[:BELONGS_TO]->(i:Inbox)-[:OWNED_BY]->(au:User) optional match (t)-[:CREATED_BY]-(cu:User) with t, u, collect(au) as auCollected, cu as createdBy return {Task: t, User: u, AssignedUser: auCollected, CreatedBy: createdBy}
tusker
1
65 ms
98 ms
65 ms
flat
WITH $`t` as `t` optional match (t)-[:WITH_CONTEXT]->(pv:ParticipantVisit) optional match (cp:CarePlan{tildaid: pv.carePlanId})-[:EXECUTE_SOA]->(s:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition{delpending:false})-[:…
WITH $`t` as `t` optional match (t)-[:WITH_CONTEXT]->(pv:ParticipantVisit) optional match (cp:CarePlan{tildaid: pv.carePlanId})-[:EXECUTE_SOA]->(s:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition{delpending:false})-[:PLAN_DEFINITION_TO_STUDY]->(st:Study) optional match (pd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) optional match (p:Patient{tildaid: cp.participantId}) optional match (phi:Phi{tildaid: p.phiid}) optional match (pv)-[:BELONGS_TO_VISIT]->(v) where v:Visit or v:VisitV2 optional match (t)-[:CREATED_BY]->(u:User) optional match (t)-[:BELONGS_TO]->(i:Inbox)- [:OWNED_BY]->(au:User) optional match (t)-[:CREATED_BY]-(cu:User) with t, st, u, collect(au) as auCollected, pv, site, v, p, phi, cu as createdBy, pd.siteNumber as siteNumber, cp return {Task: t, Study: st, Phi: phi, Visit: v, Site: {tildaid: site.tildaid, name: site.name, city: site.city, country: site.country, siteNumber: siteNumber}, User: u, AssignedUser: auCollected, Patient: p, CreatedBy: createdBy, CarePlan: cp}
tusker
1
60 ms
98 ms
60 ms
flat
WITH ? as total, null as vector MATCH (doc:Document) WHERE doc.tildaid in $lists.mf0 MATCH (org:Organization{tildaid:$policyOrgId0}) CALL { RETURN {} AS studyById, [] AS studyIdsInScope } CALL { with org, doc WITH org, doc as doc0 match (o…
WITH ? as total, null as vector MATCH (doc:Document) WHERE doc.tildaid in $lists.mf0 MATCH (org:Organization{tildaid:$policyOrgId0}) CALL { RETURN {} AS studyById, [] AS studyIdsInScope } CALL { with org, doc WITH org, doc as doc0 match (org)<-[:DOC_TO_ORG]-(doc0:Document) RETURN doc0 as doc1, doc0 as docObj, null as docShare, true as owned, null as location union with org, doc WITH org, doc as doc0 match (org)<-[:DOC_SHARED_TO_ORG]-(docObj:DocumentShare)-[:REFERS_TO]->(doc0:Document) RETURN doc0 as doc1, docObj, docObj as docShare, false as owned, null as location union with org, doc WITH org, doc as doc0 match (:Organization {alias:?})-[:HAS_SYSTEM_TEMPLATE]->(doc0:Document) where not (doc0)-[:DOC_TO_ORG]->(org) RETURN doc0 as doc1, doc0 as docObj, null as docShare, false as owned, null as location } CALL { with org, doc1 OPTIONAL MATCH (study:Study{tildaid:doc1.studyId}) OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$pickOrgId0})-[:FOR_USER]->(:User{tildaid:$pickUserId0}) RETURN study, studyActor, [] as policies, [] as userSiteIds, [] as userCountryIds, ? as defaultAccessLevel } CALL { RETURN ? AS docAccessLevel } CALL { WITH doc1 RETURN EXISTS { (doc1)-[:HAS]-(:DocumentAnnotation)-[:ANNOTATION_SIGNER]-(:DocumentSigner{userId:$signerUserId0}) } AS isSigner } WITH total, vector, doc1 as doc, docObj, owned, study, org, isSigner, CASE WHEN $isNonMember0 AND docAccessLevel = ? THEN ? ELSE docAccessLevel END AS docAccessLevel WHERE (docAccessLevel <> ? OR isSigner) CALL { WITH doc OPTIONAL MATCH (doc)<-[:INCLUDES]-(:Binder{scope:?})-[]-(patient:Patient)-[:HAS_PROTECTED_DATA_IN]->(phi:Phi) OPTIONAL MATCH (patient)<-[:RESEARCH_SUBJECT]->(cp:CarePlan{studyId:doc.studyId})RETURN DISTINCT patient.tildaid as patientId, CASE WHEN $ctxPhi0 THEN trim(COALESCE(phi.firstname ,?) + ? + COALESCE(phi.lastname ,?)) ELSE ? END as patientName, cp.subjectid as subjectId}OPTIONAL MATCH (doc)<-[:INCLUDES]-(:Binder{scope:?})-[:BINDER_FOR]-(site:Site) CALL { WITH doc, site OPTIONAL MATCH (study0:Study{tildaid:doc.studyId})<-[:PLAN_DEFINITION_TO_STUDY]- (pd0:DslPlanDefinition{delpending:false,type:?})-[:PLAN_DEFINITION_TO_SITE]->(site) OPTIONAL MATCH (pd0)<-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(ssa0:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_ACTOR]-(pi0:Actor)-[:FOR_USER]->(piu0:User) WHERE pi0.state <> ? AND piu0.status <> ? AND ANY(role IN ssa0.roles WHERE role = ?) AND EXISTS { (study0)-[:STUDY_TO_ACTOR]->(pi0) } WITH COALESCE(pd0.siteNumber, ?) AS siteNumber, [n IN collect(DISTINCT CASE WHEN piu0 IS NULL THEN NULL WHEN piu0.name IS NOT NULL AND piu0.name <> ? THEN piu0.name ELSE ? + piu0.email + ? END) WHERE n IS NOT NULL] AS piNames RETURN siteNumber, piNames } OPTIONAL MATCH (doc)<-[:INCLUDES]-(visitBinder:Binder)-[:BINDER_FOR]-(visit) where visitBinder.scope in [?, ?] OPTIONAL MATCH (doc)<-[:INCLUDES]-(:Binder{scope:?})-[:BINDER_FOR]-(user:User) CALL { WITH doc OPTIONAL MATCH (doc)-[:DOCUMENT_VERSION]->(version:DocumentVersion{tildaid: coalesce($ctxVersionId0, doc.primaryVersionId)}) OPTIONAL MATCH (doc)-[:DOCUMENT_VERSION]->(majorDv:DocumentVersion{majorVersion: version.majorVersion, minorVersion: ?}) WITH version, majorDv ORDER BY majorDv.versionNumber ASC LIMIT ? OPTIONAL MATCH (majorDv)-[:UPLOADED_BY]->(uploadedBy:User) RETURN uploadedBy } CALL { WITH doc OPTIONAL MATCH (docType:DocTypeDetails{name:doc.docTypeName}) return docType }CALL { with docObj OPTIONAL MATCH (docObj) -[signedByRel:SIGNED_BY]-> (signedBy:User) WITH COLLECT(DISTINCT({id: signedBy.tildaid, name: signedBy.name, profilePicURL: signedBy.profilePicURL})) as signedByUsers, count(signedBy) as signersCount, max(signedByRel.createdat) as reviewDate RETURN CASE WHEN signersCount<>? THEN signedByUsers ELSE [] END AS signedByUsers , reviewDate } WITH distinct total, doc, docObj, vector, owned, study, org, docAccessLevel, isSigner, patientId, patientName, subjectId, site, siteNumber, piNames, visit, user, uploadedBy, docType, signedByUsers, reviewDate CALL { WITH doc MATCH (doc)<-[r:WORK_ORDER_TO_DOCUMENT]-(wo:WorkOrder) WITH doc, {tildaid: wo.tildaid, work_order_id: wo.work_order_id, status: wo.status, documentStatus: COALESCE(r.status, ?)} as workOrder return collect (distinct workOrder) as workOrders} CALL { WITH doc RETURN EXISTS { MATCH (doc)-[:WORK_ORDER_TO_DOCUMENT]-(w:WorkOrder)-[:WORK_ORDER_TO_CONTACT]-(u:User {tildaid: $woContactId0}) WHERE w.deltimestamp = ? AND u.deltimestamp = ? } as woContact } CALL { with docObj optional match (latestAIRun:AIRun{run_type:?})-[:AI_RUN_TO_DOCUMENT_LATEST]->(docObj) with docObj, latestAIRun order by latestAIRun.createdat desc limit ? return case when latestAIRun is not null then {tildaid: latestAIRun.tildaid, run_type: latestAIRun.run_type, status: latestAIRun.status} when docObj.aiEnabled = true then {status: ?} end as latestAIRun } CALL { with docObj optional match (docObj)<-[:RECOMMENDATION_TO_DOCUMENT]-(rec:Recommendation) return count(DISTINCT CASE WHEN rec IS NOT NULL THEN rec END) AS findingCount, count(DISTINCT CASE WHEN rec.category IN [?, ?] THEN rec END) AS queriesCount, count(DISTINCT CASE WHEN rec.status = ? THEN rec END) AS confirmedCount, count(DISTINCT CASE WHEN rec.status = ? THEN rec END) AS resolvedCount } CALL { with doc OPTIONAL MATCH (doc)<-[:INCLUDES]-(b:Binder{binderType:?}) return apoc.text.join(collect (b.tag), ?) as tags } CALL { WITH doc, docObj OPTIONAL MATCH (fold:Folder{delpending:false, folderType:?})-[:INCLUDES]->(docObj) WHERE (fold.scope=? OR fold.scope=?) OPTIONAL MATCH path = (collection:Folder{level:?})-[:INCLUDES*?..?]->(fold) RETURN path, fold, collection, CASE WHEN COALESCE(doc.collection, ?) <> ? THEN [COALESCE(doc.collection, ?)] ELSE [] END as stateTags1 } CALL { WITH docObj, path, fold, collection OPTIONAL MATCH (fold)-[:HAS]->(foldFc:FolderConstraints) OPTIONAL MATCH (fold)-[:INCLUDES]->(subArtifact:SubArtifact)<-[:FILED_UNDER_SUBARTIFACT]-(docObj) OPTIONAL MATCH (subArtifact)-[:HAS]->(subFc:FolderConstraints) WITH collection, fold, subArtifact, [n IN nodes(path) | CASE WHEN n.number=? THEN n.name ELSE apoc.text.lpad(toString(COALESCE(n.number, ?)), ?, ?)+ ?+n.name END] AS nodeIds, CASE WHEN subFc IS NOT NULL THEN subFc ELSE foldFc END as folderConstraints WITH collection, fold, subArtifact, apoc.text.join(nodeIds, ?) as folderPath, folderConstraints RETURN {id: fold.tildaid, name: fold.name, number: fold.number, folderType: fold.folderType, folderScope: fold.scope, folderScopeId: fold.scopeId, path: folderPath, mandatoryFields: [], optionalFields: [], subArtifactId: COALESCE(subArtifact.tildaid, ?), subArtifactName: COALESCE(subArtifact.name, ?), constraints: CASE WHEN folderConstraints IS NOT NULL THEN folderConstraints{.*} ELSE null END} AS primaryFolder } CALL { WITH docObj optional match (pf:Folder{delpending:false})-[:INCLUDES]->(docObj) WHERE pf.scope=? OR pf.scope=? OR (pf.scope=? AND pf.scopeId=$listingScopeId0) optional match path = (:Folder{level:?})-[:INCLUDES*?..?]->(pf) with pf, [n IN nodes(path) | CASE WHEN n.number=? THEN n.name ELSE apoc.text.lpad(toString(COALESCE(n.number, ?)), ?, ?)+ ?+n.name END] AS nodeIds with pf, apoc.text.join(nodeIds, ?) as folderPath with count(pf) as folderCount, collect(distinct({id: pf.tildaid, name: pf.name, folderType: pf.folderType, folderScope: pf.scope, folderScopeId: pf.scopeId, path: folderPath, mandatoryFields: [], optionalFields: []})) as folders return case when folderCount=? THEN [] ELSE folders END as listingFolders }CALL { with doc WITH doc, [doc.stateTag] AS stateTags2 RETURN stateTags2, stateTags2 as stagingLabels } CALL { WITH docObj OPTIONAL MATCH (docObj)-[:REVIEW_IN]->(rt:Task{category:?}) OPTIONAL MATCH (rt)-[:BELONGS_TO]->(i:Inbox{type:?})-[:OWNED_BY]-(assignee:User) WITH rt, CASE WHEN COUNT(assignee) > ? THEN collect({tildaid: assignee.tildaid, email: assignee.email, name: assignee.name, profilePicURL: assignee.profilePicURL, inboxId: i.tildaid}) ELSE [] END as assignees WITH CASE WHEN rt IS NULL THEN NULL ELSE {createdat: rt.createdat, taskId: rt.tildaid, category: rt.category, status: rt.state, assignees: assignees, closedBy: rt.closedBy, title: rt.title, description: rt.description} END as review ORDER BY review.createdat DESC RETURN collect(distinct(review)) as reviews } CALL { WITH doc // A regular source an instance was made from (ENG-?) is not shown as its template. optional match (doc)-[templateUse:USES_TEMPLATE]->(template:Document {template: true})-[:DOC_TO_ORG]-(torg:Organization) return case when not template is null then {doc: template{.*}, org: torg{.*}, orgId: torg.tildaid, tildaid: template.tildaid, currentGeneration: toString(template.generation), generationInUse: toString(templateUse.templateGenerationUsed)} else null end as templateDetails } CALL { with doc, docObj CALL { with doc, docObj with doc, docObj where doc.primaryVersionId = ? or doc.primaryVersionId is null optional match (docObj)-[:ALL_CHANGELOG]->(c:ChangeLog) optional match(e: ESignature{tildaid:c.blob}) optional match (doc)-[:UPLOADED_BY]->(uploader:User) with doc, uploader, case when not c is null then {changeLog: c{.*}, eSignature: e{.*}} end as clog order by c.createdat asc with doc, uploader, collect(clog) as changeLog return {tildaid: doc.tildaid, fileName: doc.fileName, originalFileName: doc.originalFileName, contentType: doc.contentType, date: doc.date, dateType: doc.dateType, versionName: doc.versionName, rejectReason: doc.rejectReason, language: doc.language, languages: doc.languages, needsAttention: doc.needsAttention, needsAttentionReasons: doc.needsAttentionReasons, trackActiveDuplicates: doc.trackActiveDuplicates, versionNumber: doc.versionNumber, majorVersion: doc.majorVersion, minorVersion: doc.minorVersion, generation: toString(doc.generation), cksum: doc.cksum, size: doc.size, status: doc.status, stateTag: doc.stateTag, pages: doc.pages, uploadMethod: doc.uploadMethod, changeLog: changeLog, createdAt: doc.createdat, renderedCompletedAt: doc.renderedCompletedAt, renderedFieldsRevision: doc.renderedFieldsRevision, renderedSignersRevision: doc.renderedSignersRevision, uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} as version union with doc optional match (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion) where $versionsSeesPreRedaction0 or coalesce(dv.preRedaction, false) = false or dv.tildaid = doc.primaryVersionId optional match (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv) optional match (dv)-[:UPLOADED_BY]->(uploader:User) optional match (dv)-[:ALL_CHANGELOG]->(c:ChangeLog) optional match(e: ESignature{tildaid:c.blob}) optional match (dv)-[:PROMOTED_FROM]->(psrc:Document) with doc, dv, ds, psrc, uploader, case when not c is null then {changeLog: c{.*}, eSignature: e{.*}} end as clog order by c.createdat asc with doc, dv, ds, psrc, uploader, collect(clog) as changeLog return case when dv is null then null else {tildaid: dv.tildaid, fileName: dv.fileName, originalFileName: dv.originalFileName, contentType: dv.contentType, date: dv.date, dateType: doc.dateType, versionName: dv.versionName, rejectReason: dv.rejectReason, language: dv.language, languages: dv.languages, needsAttention: dv.needsAttention, needsAttentionReasons: dv.needsAttentionReasons, trackActiveDuplicates: dv.trackActiveDuplicates, versionNumber: dv.versionNumber, majorVersion: dv.majorVersion, minorVersion: dv.minorVersion, documentSourceId: ds.tildaid, promotedFromDocumentId: psrc.tildaid, generation: toString(dv.generation), linearizedPdfUrl: dv.linearizedPdfUrl, linearizedPdfStatus: dv.linearizedPdfStatus, needsProcessedPdf: dv.needsProcessedPdf, langDetectionStatus: dv.langDetectionStatus, translatedPdfUrl: dv.translatedPdfUrl, translatedPdfStatus: dv.translatedPdfStatus, cksum: dv.cksum, size: dv.size, status: dv.status, stateTag: dv.stateTag, pages: dv.pages, uploadMethod: dv.uploadMethod, changeLog: changeLog, url: dv.url, createdAt: dv.createdat, renderedCompletedAt: dv.renderedCompletedAt, renderedFieldsRevision: dv.renderedFieldsRevision, renderedSignersRevision: dv.renderedSignersRevision, preRedaction: coalesce(dv.preRedaction, false), uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} end as version // TODO: [Major/Minor Versioning] Once client supports minor version UX, update ordering to: // } with doc, version order by version.majorVersion, version.minorVersion // This will display versions as ?.?, ?.?, ?.? instead of flat ?, ?, ? } with doc, version order by version.versionNumber where $versionsVersionId0 IS NULL OR version.tildaid = $versionsVersionId0 return collect(version) as versions } CALL { with doc optional match (doc)-[:HAS]-(ann:DocumentAnnotation) optional match (ann)-[:ANNOTATION_SIGNER]-(ds:DocumentSigner) optional match (ann)-[:ANNOTATION_FIELD]-(df:DocumentField) optional match (ann)-[:ALL_CHANGELOG]->(cl:ChangeLog) with doc, ann, ds, df, cl order by cl.createdat asc with doc, ann, apoc.convert.fromJsonList(ann.signers) as signersOld, collect(distinct ds{.*}) as signers, collect(distinct df{.*}) as fields, collect(distinct cl{.*}) as auditLog with doc, ann, auditLog, case when size(signers) <> ? then signers when size(signers) = ? and signersOld is null then [] else signersOld end as signers1, case when fields is null then [] else fields end as fields1 with doc, ann{.*, signers: signers1, fields: fields1} as annotationSpec, case when doc.template = false then ann{.*, signers: signers1, fields: fields1} else null end as annotationVal return annotationSpec, annotationVal } CALL { with doc optional match (doc)-[p:HAS_QUERY]-(q:Query) WHERE (NOT $queriesNonMember0 OR q.externalFlag = true) AND (doc.ownerOrgId = $queryOrgId0 OR q.orgId = $queryOrgId0) with case when not q is null then {query: q{.*}} end as query return collect(distinct query) as queries } CALL { WITH docObj, owned OPTIONAL MATCH (docObj)<-[:REFERS_TO]-(docShare:DocumentShare)-[:DOC_SHARED_TO_ORG]->(otherOrg:Organization) WHERE owned CALL { WITH docShare OPTIONAL MATCH (docShare)-[signedByRel:SIGNED_BY]->(signedBy:User) WITH COLLECT(DISTINCT({id: signedBy.tildaid, name: signedBy.name, profilePicURL: signedBy.profilePicURL})) as signedByUsers, count(signedBy) as signersCount, max(signedByRel.createdat) as reviewDate RETURN CASE WHEN signersCount<>? THEN signedByUsers ELSE [] END as signedByUsers, reviewDate } CALL { WITH docShare OPTIONAL MATCH (docShare)-[:ALL_CHANGELOG]->(c:ChangeLog) WHERE c.level IS NULL OR c.level = ? OPTIONAL MATCH (e:ESignature{tildaid:c.blob}) WITH CASE WHEN NOT c is NULL THEN {changeLog: c{.*}, eSignature: e{.*}} END as clog order by c.createdat asc RETURN collect(clog) as changeLog } WITH CASE WHEN NOT otherOrg is NULL THEN {readerOrg: otherOrg.name, readerOrgId: otherOrg.tildaid, readerOrgAlias: otherOrg.alias, status: docShare.ownerStatus, signedByUsers: signedByUsers, reviewDate: reviewDate, changeLog: changeLog} END as shareEntry RETURN collect(shareEntry) as sharedWith } CALL { WITH docObj RETURN CASE WHEN (docObj)-[:REFERS_TO]->(:Document) THEN docObj{.*} ELSE null END as currentShare } CALL { WITH doc MATCH (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion{tildaid: coalesce($docAttrVersionId0, doc.primaryVersionId)}) MATCH (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv) OPTIONAL MATCH (ds)-[:HAS_ATTRIBUTE]->(da:DocAttribute) OPTIONAL MATCH (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) RETURN collect(DISTINCT da{.*, name: COALESCE(dad.name, ?), description: COALESCE(dad.description, ?), documentSourceId: ds.tildaid}) as docAttributes } CALL { WITH doc OPTIONAL MATCH (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion{tildaid: coalesce($notesVersionId0, doc.primaryVersionId)})-[:HAS_NOTE]->(n:DocumentNote{archived: false}) RETURN count(n) AS notesCount } CALL { return null as eventId, null as eventName, null as deliverable, null as eventScope } CALL { WITH docObj, owned OPTIONAL MATCH (docObj)-[cf:COPIED_FROM]->(src:Document) WHERE owned OPTIONAL MATCH (srcFolder:Folder{delpending:false, folderType:?})-[:INCLUDES]->(src) WHERE (srcFolder.scope=? OR srcFolder.scope=?) OPTIONAL MATCH srcFolderPath = (:Folder{level:?})-[:INCLUDES*?..?]->(srcFolder) WITH cf, src, CASE WHEN srcFolder IS NOT NULL THEN apoc.text.join([n IN nodes(srcFolderPath) | CASE WHEN n.number=? THEN n.name ELSE apoc.text.lpad(toString(COALESCE(n.number, ?)), ?, ?)+ ?+n.name END], ?) ELSE ? END as srcFolderPath WITH cf, src, head(collect(srcFolderPath)) AS srcFolderPath RETURN CASE WHEN cf IS NULL THEN null ELSE { documentId: src.tildaid, collection: src.collection, folderPath: srcFolderPath, kind: cf.kind, stale: cf.sourceVersionId <> src.primaryVersionId, sourceVersion: COALESCE(src.versionName, toString(src.majorVersion)) } END as copiedFrom } CALL { WITH docObj, owned OPTIONAL MATCH (child:Document)-[ct:COPIED_FROM]->(docObj) WHERE owned OPTIONAL MATCH (childFolder:Folder{delpending:false, folderType:?})-[:INCLUDES]->(child) WHERE (childFolder.scope=? OR childFolder.scope=?) OPTIONAL MATCH childFolderPath = (:Folder{level:?})-[:INCLUDES*?..?]->(childFolder) OPTIONAL MATCH (st:Study{tildaid:child.studyId}) WITH docObj, ct, child, st, CASE WHEN childFolder IS NOT NULL THEN apoc.text.join([n IN nodes(childFolderPath) | CASE WHEN n.number=? THEN n.name ELSE apoc.text.lpad(toString(COALESCE(n.number, ?)), ?, ?)+ ?+n.name END], ?) 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 { WITH doc OPTIONAL MATCH (doc)<-[:PROMOTED_FROM]-(tv:DocumentVersion)<-[:DOCUMENT_VERSION]-(tgt:Document) WITH CASE WHEN tgt IS NULL THEN null ELSE { documentId: tgt.tildaid, targetVersionId: tv.tildaid, version: COALESCE(tv.versionName, toString(tv.majorVersion)) } END as promotedTo RETURN promotedTo LIMIT ? } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { WITH docObj OPTIONAL MATCH (docObj)<-[:INCLUDES]-(currentFolder:Folder{folderType:?}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (docObj)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (docObj)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) WITH CASE WHEN stagingInfo IS NOT NULL THEN {artifactId: stagingInfo.folderId, subArtifactId: stagingInfo.subArtifactId, stagingInfoId: stagingInfo.tildaid} WHEN currentFolder.level = ? THEN {artifactId: currentFolder.tildaid, subArtifactId: COALESCE(currentSubArtifact.tildaid, ?), stagingInfoId: ?} ELSE {artifactId: ?, subArtifactId: ?, stagingInfoId: ?} END as tmfInfo WITH tmfInfo.artifactId as artifactId, tmfInfo.subArtifactId as subArtifactId, tmfInfo.stagingInfoId as stagingInfoId OPTIONAL MATCH (artifact:Folder{tildaid:artifactId})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{name:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(allSubArtifacts:SubArtifact) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:subArtifactId}) OPTIONAL MATCH (subArtifact)-[:HAS]->(subArtifactConstraints:FolderConstraints) OPTIONAL MATCH (artifact)-[:HAS]->(artifactConstraints:FolderConstraints) WITH artifactId as folderId, subArtifactId, stagingInfoId, CASE WHEN zone.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+ ? + zone.name, ?) ELSE ? END as zone, CASE WHEN section.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+ ? + section.name, ?) ELSE ? END as section, CASE WHEN artifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+ ? + artifact.name, ?) ELSE ? END as artifact, CASE WHEN subArtifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+?+apoc.text.lpad(toString(subArtifact.number), ?, ?)+ ? + subArtifact.name, ?) ELSE ? END as subArtifact, COALESCE(subArtifact.name, ?) as subArtifactName, COALESCE(subArtifactConstraints, artifactConstraints) as constraints, NOT(COUNT(allSubArtifacts) > ? AND subArtifactId = ?) as subArtifactValid OPTIONAL MATCH (constraints)-[:HAS_ATTRIBUTE_CONSTRAINT]->(dac:DocAttributeConstraint) WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, constraints, COLLECT(DISTINCT CASE WHEN dac IS NOT NULL THEN {id: dac.tildaid, key: dac.key, order: dac.order, isMandatory: dac.isMandatory, description: dac.description} ELSE NULL END) as attributeConstraintsList WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, CASE WHEN constraints IS NOT NULL THEN { id: constraints.tildaid, mandatory: constraints.mandatory, tmfLevel: constraints.tmfLevel, attributeConstraints: [attr IN attributeConstraintsList WHERE attr IS NOT NULL | attr] } ELSE null END as constraints WITH {folderId: folderId, subArtifactId: subArtifactId, stagingInfoId: stagingInfoId, zone: zone, section: section, artifact: artifact, subArtifact: subArtifact, subArtifactName: subArtifactName, subArtifactValid: subArtifactValid, path: apoc.text.join([x IN [zone, section, artifact] WHERE x <> ? | x], ?), constraints: constraints} as tmfCategorization RETURN CASE WHEN tmfCategorization.folderId=? THEN null ELSE tmfCategorization END as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
1
57 ms
98 ms
57 ms
flat
match(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType}) where ((exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId})))) and (form.activityType is null or not form.activityType in $exclude…
match(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType}) where ((exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId})))) and (form.activityType is null or not form.activityType in $excludeActivityTypes) and not form.state=? with org, count(form) as allCount optional match(org)-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType, state:?}) where exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId})) with org, allCount, count(form) as publishedCount optional match(org)-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType, state:?}) where exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId})) with org, allCount, publishedCount, count(form) as draftCount optional match(org)-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType, state:?}) where exists((form)<-[:PROTOCOL_TO_FORM]-(:FormV2{tildaid:$protocolId})) return allCount, publishedCount, draftCount, count(form) as archivedCount
studiosvc
1
52 ms
98 ms
52 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
1
73 ms
98 ms
73 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
2
76 ms
98 ms
86 ms
flat
WITH {} as docStats MATCH (org:Organization{tildaid:$orgId0}) UNWIND $collectionRows0 AS collectionInfo OPTIONAL MATCH (org)-[:USES]->(fm:FolderManager{name: collectionInfo.fmName, scopeId: collectionInfo.scopeId}) OPTIONAL MATCH (fm)-[:IN…
WITH {} as docStats MATCH (org:Organization{tildaid:$orgId0}) UNWIND $collectionRows0 AS collectionInfo OPTIONAL MATCH (org)-[:USES]->(fm:FolderManager{name: collectionInfo.fmName, scopeId: collectionInfo.scopeId}) OPTIONAL MATCH (fm)-[:INCLUDES]->(fmFolder:Folder{name: collectionInfo.name, level: ?}) OPTIONAL MATCH (actor:Actor{tildaid: collectionInfo.actorId})<-[:BELONGS_TO]-(actorFolder:Folder) WHERE collectionInfo.actorId <> ? WITH collectionInfo, COALESCE(fmFolder, actorFolder) as f0, docStats, ? as parentPrefix WHERE f0 IS NOT NULL AND coalesce(f0.isArchived, false) = false CALL { WITH docStats, collectionInfo, f0, parentPrefix // Match immediate children OPTIONAL MATCH (f0)-[:INCLUDES]->(f1:Folder) WHERE coalesce(f1.isArchived, false) = false WITH docStats, collectionInfo, f0, f1, CASE WHEN parentPrefix = ? THEN apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?) ELSE parentPrefix + ? + apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?) END AS childPrefix CALL { WITH docStats, collectionInfo, f1, childPrefix // Match immediate children OPTIONAL MATCH (f1)-[:INCLUDES]->(f2:Folder) WHERE coalesce(f2.isArchived, false) = false WITH docStats, collectionInfo, f1, f2, CASE WHEN childPrefix = ? THEN apoc.text.lpad(toString(COALESCE(f2.number, ?)), ?, ?) ELSE childPrefix + ? + apoc.text.lpad(toString(COALESCE(f2.number, ?)), ?, ?) END AS childPrefix CALL { WITH docStats, collectionInfo, f2, childPrefix // Match immediate children OPTIONAL MATCH (f2)-[:INCLUDES]->(f3:Folder) WHERE coalesce(f3.isArchived, false) = false WITH docStats, collectionInfo, f2, f3, CASE WHEN childPrefix = ? THEN apoc.text.lpad(toString(COALESCE(f3.number, ?)), ?, ?) ELSE childPrefix + ? + apoc.text.lpad(toString(COALESCE(f3.number, ?)), ?, ?) END AS childPrefix CALL { WITH docStats, collectionInfo, f3, childPrefix // Match immediate children OPTIONAL MATCH (f3)-[:INCLUDES]->(sa:SubArtifact) WHERE coalesce(sa.isArchived, false) = false // Build SubArtifact nodes (leaf level) WITH sa, docStats WHERE sa IS NOT NULL OPTIONAL MATCH (sa)-[:HAS]->(fc:FolderConstraints) WITH {name: sa.name, id: sa.tildaid, number: sa.number, isArchived: sa.isArchived, tmfLevel: COALESCE(fc.tmfLevel, [])} as saNode RETURN collect(saNode) as saNodes } // Build f3 nodes with children WITH docStats, collectionInfo, f3, childPrefix, saNodes WHERE f3 IS NOT NULL WITH { name: CASE WHEN collectionInfo.name = ? THEN childPrefix + ? + f3.name ELSE f3.name END, id: f3.tildaid, scope: f3.scope, type: f3.folderType, isInternal: f3.isInternal, isArchived: f3.isArchived, count: ?, number: f3.number, path: f3.name, folders: [], documents: [], mandatoryFields: [], optionalFields: [], subArtifacts: apoc.coll.sortMaps(saNodes, ?) } as f3Node RETURN collect(f3Node) as f3Nodes } // Build f2 nodes with children WITH docStats, collectionInfo, f2, childPrefix, f3Nodes WHERE f2 IS NOT NULL WITH { name: CASE WHEN collectionInfo.name = ? THEN childPrefix + ? + f2.name ELSE f2.name END, id: f2.tildaid, scope: f2.scope, type: f2.folderType, isInternal: f2.isInternal, isArchived: f2.isArchived, count: ?, number: f2.number, path: f2.name, folders: apoc.coll.sortMaps(f3Nodes, ?), documents: [], mandatoryFields: [], optionalFields: [], subArtifacts: [] } as f2Node RETURN collect(f2Node) as f2Nodes } // Build f1 nodes with children WITH docStats, collectionInfo, f1, childPrefix, f2Nodes WHERE f1 IS NOT NULL WITH { name: CASE WHEN collectionInfo.name = ? THEN childPrefix + ? + f1.name ELSE f1.name END, id: f1.tildaid, scope: f1.scope, type: f1.folderType, isInternal: f1.isInternal, isArchived: f1.isArchived, count: ?, number: f1.number, path: f1.name, folders: apoc.coll.sortMaps(f2Nodes, ?), documents: [], mandatoryFields: [], optionalFields: [], subArtifacts: [] } as f1Node RETURN collect(f1Node) as f1Nodes } RETURN collectionInfo.name as collectionName, { name: f0.name, id: f0.tildaid, scope: f0.scope, type: f0.folderType, isInternal: f0.isInternal, isArchived: f0.isArchived, count: ? , path: f0.name, folders: apoc.coll.sortMaps(f1Nodes, ?), mandatoryFields: [], optionalFields: [], documents: [], subArtifacts: []} as f0Node ORDER BY toLower(collectionInfo.name)
docsvc
1
68 ms
98 ms
68 ms
flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <>…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) WITH * WHERE st.tildaid = c.id RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) WITH * WHERE st.tildaid = c.id RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 MATCH (o)<-[:STUDY_TO_ORG]-(st:Study {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_3 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) MATCH (o)<-[:STUDY_TO_ORG]-(st:Study {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
enrollsvc
1
61 ms
98 ms
61 ms
flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Study policies=study-config-edit,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 WITH u, o, c // 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:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_3 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
enrollsvc
2
56 ms
98 ms
57 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}
onboardingsvc
1
84 ms
98 ms
84 ms
flat
MATCH (s:Study {tildaid: $studyId}) CALL { WITH s WITH s WHERE size($actorIds) > ? OR size($roles) > ? MATCH (s)-[:STUDY_TO_ACTOR]->(a:Actor {delpending: false}) WHERE (a.tildaid IN $actorIds OR EXISTS { MATCH (a)-[:APPOINTED_AS]->(r:Role)…
MATCH (s:Study {tildaid: $studyId}) CALL { WITH s WITH s WHERE size($actorIds) > ? OR size($roles) > ? MATCH (s)-[:STUDY_TO_ACTOR]->(a:Actor {delpending: false}) WHERE (a.tildaid IN $actorIds OR EXISTS { MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name IN $roles }) AND (a.state IS NULL OR a.state <> ?) MATCH (a)-[:FOR_USER]->(u:User) OPTIONAL MATCH (co:ClinOp {deltimestamp: ?, orgId: a.orgId})-[:FOR_USER]-(u) RETURN u, CASE WHEN coalesce(co.actorEmail, ?) <> ? THEN co.actorEmail ELSE u.email END AS email UNION WITH s WITH s WHERE size($emails) > ? MATCH (u:User) WHERE toLower(u.email) IN $emails AND coalesce(u.deltimestamp, ?) = ? RETURN u, u.email AS email } WITH u, email WHERE coalesce(email, ?) <> ? AND coalesce(u.status, ?) <> ? AND coalesce(u.isDummy, false) = false WITH u, email ORDER BY CASE WHEN email = u.email THEN ? ELSE ? END WITH u, collect(DISTINCT email)[?] AS email CALL { WITH u MATCH (d:Document {studyId: $studyId})-[:UPLOADED_BY]->(u) WHERE coalesce(d.delpending, false) = false AND coalesce(d.template, false) = false WITH d, CASE WHEN d.archived = true OR d.stateTag = ? THEN ? WHEN d.stateTag = ? THEN ? WHEN d.stateTag = ? THEN ? WHEN d.stateTag = ? THEN ? WHEN d.stateTag = ? THEN ? END AS event WHERE event IN $events WITH event, d ORDER BY d.updatedat DESC WITH event, count(d) AS total, collect({id: d.tildaid, name: d.fileName, displayId: d.displayId, orgAlias: [(d)-[:DOC_TO_ORG]->(o:Organization) | o.alias][?]})[..$limit] AS documents RETURN collect({event: event, total: total, documents: documents}) AS sections } RETURN u.tildaid AS userId, u.firstName AS firstName, email, toLower(u.email) AS userEmail, sections
virtual-assistant
1
68 ms
98 ms
68 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).