Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 30 days▾
Ends now. Anything up to this cluster's 30-day retention.
Start and end times
Cancel
Interpreted in UTC (change it at the right of this bar). Earliest available: 2026-10-19 16:54:31.
› →|
updated 1d ago
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

stored 30h ago top 80 of 904

Neo4j query shapes on nexus over the last 30d, grouped by plan hash. Click a column to sort; p95 drift (late-half ÷ early-half of the window) ranks the queries getting slower.

Columns
Views
Export CSV
Query shape Count Avg p95 ▾ Max p95 drift
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 (u:User{tildaid:$user}), (o:Organization{delpending:false, alias:$org}) CALL (u, o) { OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND c…
MATCH (u:User{tildaid:$user}), (o:Organization{delpending:false, alias:$org}) CALL (u, o) { 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 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:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND ? 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 (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 (u, o) { CALL (u, o) { MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? // Required pipeline barrier: without it the actor match re-expands // once per study in the org (?.0M -> 32k db hits, measured in Go). 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, 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. WITH s, {studyId: s.tildaid, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} 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} as rc
virtual-assistant
2 1.03 s 4.70 s 2.04 s flat
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, 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}
docsvc
4 738 ms 4.40 s 2.94 s 0.0× ↓
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
write virtual-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 (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=work-order-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AN…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=work-order-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=work-order-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 } RETURN checkIdx, User, Organization, Study, Site, Patient
virtual-assistant
2 544 ms 1.90 s 1.08 s flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=OrganizationSMO policies=lead-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZ…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=OrganizationSMO policies=lead-edit 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 WITH * WHERE o.orgType = ? 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=Study policies=study-patient-document-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_1 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-patient-document-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_2 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 } RETURN checkIdx, User, Organization, Study, Site, Patient
docsvc
2 684 ms 1.90 s 1.37 s 0.0× ↓
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, 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}
docsvc
2 924 ms 1.90 s 1.85 s flat
MATCH (d:Document {tildaid: $id}) MATCH (r:Recommendation)-[:RECOMMENDATION_TO_DOCUMENT]->(d) WHERE NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization)…
MATCH (d:Document {tildaid: $id}) MATCH (r:Recommendation)-[:RECOMMENDATION_TO_DOCUMENT]->(d) WHERE NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (u: User) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (dv:DocumentVersion{tildaid: d.primaryVersionId}) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) CALL { WITH d, site OPTIONAL MATCH (study:Study{tildaid: d.studyId})<-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition{delpending:false, type:?})-[:PLAN_DEFINITION_TO_SITE]->(site) RETURN COALESCE(pd.siteNumber, ?) AS siteNumber } CALL { WITH dv OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(docAttr: DocAttribute) WITH collect([coalesce(docAttr.key, ?), coalesce(docAttr.value, ?)]) AS attributePairs WITH apoc.map.fromPairs(attributePairs) AS attributesMap WITH apoc.convert.toJson(attributesMap) AS attributesJson RETURN attributesJson } // Latest state-change ChangeLog on the version — gives us the user // who triggered the doc into its current state. Merge both event // types (Staging + StateTag) since the BE treats them as the same // documentState concept (mirrors the Python migration script). CALL { WITH dv OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]-(dvcl:ChangeLog) WHERE dvcl.type IN [?, ?, ?] AND (dvcl.newValue CONTAINS ? OR dvcl.newValue CONTAINS ?) WITH dvcl ORDER BY dvcl.createdat DESC LIMIT ? OPTIONAL MATCH (changer:User {tildaid: dvcl.changedBy}) WHERE dvcl IS NOT NULL RETURN coalesce(dvcl.changedBy, ?) as statePromotedBy, coalesce(changer.name, ?) as statePromotedByName } WITH r, ai, o, d, dv, zone, section, artifact, subArtifact, u, study, site, siteNumber, attributesJson, statePromotedBy, statePromotedByName, // Document Scope key stripping: reduce the rec JSON envelope to the keys // valid for its OWN scope so irrelevant keys (e.g. country on a site rec) // don?scope?country?scope?siteName?siteId?siteNumber?scope?scope???{}?{}?????{}?{?{?{????DRecommendation????s ORIGINAL prediction (orig_data). // Same Document Scope extraction so this column lines up with // recommendedValue / actualValue. originalRecommendation: coalesce(r.orig_data, ?), recommendationCount: CASE r.category WHEN ? THEN size(keys(apoc.map.clean( apoc.convert.fromJsonMap(coalesce(r.data_formatted, ?)), [], [?]))) WHEN ? THEN apoc.coll.max([?, size([k IN keys(recMap) WHERE (recAllowed IS NULL OR k IN recAllowed) AND coalesce(toString(recMap[k]), ?) <> ?])]) ELSE ? END, actualValue: CASE r.category WHEN ? THEN coalesce(d.fileName, ?) WHEN ? THEN apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + coalesce(artifact.name, ?) WHEN ? THEN coalesce(subArtifact.name, ?) WHEN ? THEN apoc.convert.toJson( CASE toLower(coalesce(d.tmfLevel, ?)) WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), country: coalesce(d.country, ?)} WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), siteNumber: siteNumber} ELSE {scope: toLower(coalesce(d.tmfLevel, ?))} END) WHEN ? THEN attributesJson ELSE ? END, // accuracyCount: for Document Metadata, the number of keys whose // recommended value matches the actual value (?–N out of // recommendationCount). For all other categories, ? or ? (mirrors // ? or ? for other categories. NULL when not yet actioned. accuracyCount: CASE r.category WHEN ? THEN size([k IN keys(apoc.map.clean(apoc.convert.fromJsonMap(coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?)), [], [?])) WHERE apoc.map.clean(apoc.convert.fromJsonMap(coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?)), [], [?])[k] = apoc.map.clean(apoc.convert.fromJsonMap(coalesce(attributesJson, ?)), [], [?])[k]]) WHEN ? THEN size([k IN keys(cmpMap) WHERE (cmpAllowed IS NULL OR k IN cmpAllowed) AND coalesce(toString(cmpMap[k]), ?) <> ? AND toLower(coalesce(toString(cmpMap[k]), ?)) = toLower(coalesce(toString((CASE toLower(coalesce(d.tmfLevel, ?)) WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), country: coalesce(d.country, ?)} WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), siteNumber: siteNumber} ELSE {scope: toLower(coalesce(d.tmfLevel, ?))} END)[k]), ?))]) ELSE CASE (CASE r.category WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = coalesce(d.fileName, ?) WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = ( apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + coalesce(artifact.name, ?)) WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = coalesce(subArtifact.name, ?) ELSE CASE WHEN r.category IN [?, ?, ?, ?] THEN r.user_action = ? OR (r.user_action = ? AND coalesce(r.comment, ?) = ?) END END) WHEN true THEN ? WHEN false THEN ? ELSE null END END, comment: r.comment, statePromotedBy: statePromotedBy, statePromotedByName: statePromotedByName } ) YIELD node RETURN node
write dashrsvc
2 522 ms 1.90 s 1.04 s flat
MATCH (d:Document {tildaid: $id}) MATCH (r:Recommendation)-[:RECOMMENDATION_TO_DOCUMENT]->(d) WHERE NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization)…
MATCH (d:Document {tildaid: $id}) MATCH (r:Recommendation)-[:RECOMMENDATION_TO_DOCUMENT]->(d) WHERE NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (u: User) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (dv:DocumentVersion{tildaid: d.primaryVersionId}) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) CALL { WITH d, site OPTIONAL MATCH (study:Study{tildaid: d.studyId})<-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition{delpending:false, type:?})-[:PLAN_DEFINITION_TO_SITE]->(site) RETURN COALESCE(pd.siteNumber, ?) AS siteNumber } CALL { WITH dv OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(docAttr: DocAttribute) WITH collect([coalesce(docAttr.key, ?), coalesce(docAttr.value, ?)]) AS attributePairs WITH apoc.map.fromPairs(attributePairs) AS attributesMap WITH apoc.convert.toJson(attributesMap) AS attributesJson RETURN attributesJson } // Latest state-change ChangeLog on the version — gives us the user // who triggered the doc into its current state. Merge both event // types (Staging + StateTag) since the BE treats them as the same // documentState concept (mirrors the Python migration script). CALL { WITH dv OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]-(dvcl:ChangeLog) WHERE dvcl.type IN [?, ?, ?] AND (dvcl.newValue CONTAINS ? OR dvcl.newValue CONTAINS ?) WITH dvcl ORDER BY dvcl.createdat DESC LIMIT ? OPTIONAL MATCH (changer:User {tildaid: dvcl.changedBy}) WHERE dvcl IS NOT NULL RETURN coalesce(dvcl.changedBy, ?) as statePromotedBy, coalesce(changer.name, ?) as statePromotedByName } WITH r, ai, o, d, dv, zone, section, artifact, subArtifact, u, study, site, siteNumber, attributesJson, statePromotedBy, statePromotedByName, // Document Scope key stripping: reduce the rec JSON envelope to the keys // valid for its OWN scope so irrelevant keys (e.g. country on a site rec) // don?scope?country?scope?siteName?siteId?siteNumber?scope?scope???{}?{}?????{}?{?{?{????DRecommendation????s ORIGINAL prediction (orig_data). // Same Document Scope extraction so this column lines up with // recommendedValue / actualValue. originalRecommendation: coalesce(r.orig_data, ?), recommendationCount: CASE r.category WHEN ? THEN size(keys(apoc.map.clean( apoc.convert.fromJsonMap(coalesce(r.data_formatted, ?)), [], [?]))) WHEN ? THEN apoc.coll.max([?, size([k IN keys(recMap) WHERE (recAllowed IS NULL OR k IN recAllowed) AND coalesce(toString(recMap[k]), ?) <> ?])]) ELSE ? END, actualValue: CASE r.category WHEN ? THEN coalesce(d.fileName, ?) WHEN ? THEN apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + coalesce(artifact.name, ?) WHEN ? THEN coalesce(subArtifact.name, ?) WHEN ? THEN apoc.convert.toJson( CASE toLower(coalesce(d.tmfLevel, ?)) WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), country: coalesce(d.country, ?)} WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), siteNumber: siteNumber} ELSE {scope: toLower(coalesce(d.tmfLevel, ?))} END) WHEN ? THEN attributesJson ELSE ? END, // accuracyCount: for Document Metadata, the number of keys whose // recommended value matches the actual value (?–N out of // recommendationCount). For all other categories, ? or ? (mirrors // ? or ? for other categories. NULL when not yet actioned. accuracyCount: CASE r.category WHEN ? THEN size([k IN keys(apoc.map.clean(apoc.convert.fromJsonMap(coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?)), [], [?])) WHERE apoc.map.clean(apoc.convert.fromJsonMap(coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?)), [], [?])[k] = apoc.map.clean(apoc.convert.fromJsonMap(coalesce(attributesJson, ?)), [], [?])[k]]) WHEN ? THEN size([k IN keys(cmpMap) WHERE (cmpAllowed IS NULL OR k IN cmpAllowed) AND coalesce(toString(cmpMap[k]), ?) <> ? AND toLower(coalesce(toString(cmpMap[k]), ?)) = toLower(coalesce(toString((CASE toLower(coalesce(d.tmfLevel, ?)) WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), country: coalesce(d.country, ?)} WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), siteNumber: siteNumber} ELSE {scope: toLower(coalesce(d.tmfLevel, ?))} END)[k]), ?))]) ELSE CASE (CASE r.category WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = coalesce(d.fileName, ?) WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = ( apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + coalesce(artifact.name, ?)) WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = coalesce(subArtifact.name, ?) ELSE CASE WHEN r.category IN [?, ?, ?, ?] THEN r.user_action = ? OR (r.user_action = ? AND coalesce(r.comment, ?) = ?) END END) WHEN true THEN ? WHEN false THEN ? ELSE null END END, comment: r.comment, statePromotedBy: statePromotedBy, statePromotedByName: statePromotedByName } ) YIELD node RETURN node
write dashrsvc
2 795 ms 1.90 s 1.59 s flat
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Bind…
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (fm:FolderManager{scopeId:d.studyId})-[:HAS]->(dad:DocAttributeDefinition) where dad.name = ? or dad.name = ? OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da: DocAttribute{key: dad.code}) OPTIONAL MATCH (dv) - [:UPLOADED_BY] - (u: User) OPTIONAL MATCH (d)<-[:INCOMING_EMAIL_TO_DOCUMENT]-(ie:IncomingEmail) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) // Who last changed the state. The state itself is never read from the changelog: a // message from docsvc carries it, and a message without it takes the version?stateTag?stagingStatus??????DDocument?????.???.?????.???.???.??') end, docDate: da.value, submissionDelayDays: CASE WHEN da.value IS NULL THEN NULL ELSE toString( toInteger(ceil((d.createdat - datetime(da.value).epochMillis)/?)) ) END, collection: d.collection, stateTag: dv.stateTag, needsAttentionReasons: dv.needsAttentionReasons, newStatus: newStatus, changedBy: changedBy, changedByName: changedByName, patientId: d.patientId, fileName: d.fileName, documentMetadata: attributesJson, emailSubject: ie.subject, emailMessageId: ie.message_id } ) YIELD node RETURN node
write dashrsvc
2 534 ms 1.90 s 1.06 s 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-document-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-document-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 WITH u, o, c // scope=Study policies=study-document-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 ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
docsvc
3 357 ms 1.85 s 1.07 s 0.0× ↓
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Study policies=study-document-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-document-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 WITH u, o, c // scope=Study policies=study-document-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 ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
docsvc
3 438 ms 1.85 s 1.31 s 0.0× ↓
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Organization policies=org-resources MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANI…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Organization policies=org-resources MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_0 MATCH (o)-[:ORGANIZATION_TO_PATIENT]->(p2:Patient {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-resources MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) MATCH (o)-[:ORGANIZATION_TO_PATIENT]->(p2:Patient {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
patientsvc
7 191 ms 1.65 s 1.17 s 0.1× ↓
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
write docsvc
1 537 ms 975 ms 537 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-edit,org-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:OR…
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-edit,org-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_0 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-edit,org-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, ?), ?) 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=Site policies=site-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 WITH DISTINCT u, o, a, c MATCH (o)<-[:SITE_TO_ORGANIZATION]-(si:Site)-[:STAFF_TO_ACTOR]->(a) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, si AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
onboardingsvc
1 736 ms 975 ms 736 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)
write docsvc
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
write virtual-assistant
1 663 ms 975 ms 663 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 879 ms 975 ms 879 ms flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Organization policies=org-work-order-edit,org-work-order-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]-…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Organization policies=org-work-order-edit,org-work-order-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_0 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-work-order-edit,org-work-order-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) 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
virtual-assistant
2 356 ms 950 ms 710 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 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, ?), ?) 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
docsvc
3 215 ms 925 ms 642 ms flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
7 258 ms 912 ms 895 ms 0.0× ↓
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 (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.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
4 246 ms 900 ms 671 ms flat
match(a:FormV2{tildaid:$formId})-[r:GRAPH_NEXT*?..{graphid:$formId, path:[$formId]}]->(b) with r[-?] as rel, b match(prev:GraphNode)-[:GRAPH_NEXT{graphid:$formId, path:[$formId]}]->(b) with b, rel, prev optional match(b)-[]-(qs:QuestionSpe…
match(a:FormV2{tildaid:$formId})-[r:GRAPH_NEXT*?..{graphid:$formId, path:[$formId]}]->(b) with r[-?] as rel, b match(prev:GraphNode)-[:GRAPH_NEXT{graphid:$formId, path:[$formId]}]->(b) with b, rel, prev optional match(b)-[]-(qs:QuestionSpec)-[]-(q:QuestionV2) optional match(b)-[rb:BRANCH_TO_BRANCH_CONFIG{graphid:$formId}]-(cfg:BranchConfig) where rb.protocolid=$protocolId with * optional match(b)-[:ACTION_TO_ACTION_CONFIG{graphid:$formId}]->(actionCfg:ActionConfig) with * optional match(b)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:$protocolId}]->(refObj:GraphNode) optional match(b)-[:QUESTION_TO_AUTO_COMPUTE_CFG{graphid:$formId, protocolid:$protocolId}]->(acCfg:AutoComputeConfig) optional match(b)-[:QUESTION_TO_CORRECTNESS_CHECK{graphid:$formId, protocolid:$protocolId}]->(ccCfg:AutoComputeConfig) optional match(b)-[:GRAPH_NODE_REF_TO_QUESTION|GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:$protocolId}]->(q2:QuestionV2) optional match(b)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:$protocolId}]->(rs:ResponseSet) optional match(q2)-[:QUESTION_TO_QUESTION_SPEC]->(q2qs:QuestionSpec) optional match(rs)<-[:QUESTION_SPEC_TO_RESPONSE_SET]-(rsqs:QuestionSpec) with *, null as acBranchCfg, null as ccBranchCfg with *, ? as refCount optional match(b)-[:GRAPH_NODE_TO_ACTIVATION_RULE_CHAIN{graphid:$formId}]->(chain:ActivationRuleChain) with refCount, b, rel, prev, q, qs, cfg, actionCfg, refObj, acCfg, q2, q2qs, rs, rsqs, acBranchCfg, ccCfg, ccBranchCfg, chain return distinct b, rel, prev.tildaid, q, exists((b)<-[:GRAPH_NEXT|FORM_INSTANCE_TO_FORM]-()), cfg, actionCfg, refObj, acCfg, q2, rs, refCount, acBranchCfg, ccCfg, ccBranchCfg, chain.tildaid, chain.name, qs, q2qs, rsqs
studiosvc
4 229 ms 900 ms 905 ms flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
7 122 ms 825 ms 833 ms flat
MATCH (currentUser:User{deltimestamp:?,deltimestamp:?,email:$email}) WHERE (currentUser.isDummy IS NULL OR currentUser.isDummy <> true) OPTIONAL MATCH (supportUser:User{email:$supportUserEmail}) OPTIONAL MATCH (targetOrg:Organization{delpe…
MATCH (currentUser:User{deltimestamp:?,deltimestamp:?,email:$email}) WHERE (currentUser.isDummy IS NULL OR currentUser.isDummy <> true) OPTIONAL MATCH (supportUser:User{email:$supportUserEmail}) OPTIONAL MATCH (targetOrg:Organization{delpending:false, alias:$orgAlias}) // A deactivated (Inactive) org actor is not a membership (ENG-?). OPTIONAL MATCH (currentUser)-[:FOR_USER]-(orgActor:Actor{delpending:false})-[:ORGANIZATION_TO_ACTOR]-(targetOrg) WHERE coalesce(orgActor.state, ?) <> ? OPTIONAL MATCH (currentUser)-[supports:SUPPORTS_ORG]-(targetOrg) WITH *, false AS repOf // Evaluated only for non-members; members are decided by orgActor alone. WITH *, CASE WHEN orgActor IS NULL THEN EXISTS { MATCH (currentUser)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(targetOrg), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = targetOrg.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) } ELSE false END as delegated RETURN CASE WHEN orgActor is not null THEN currentUser WHEN delegated THEN currentUser WHEN repOf THEN currentUser WHEN supports is not null THEN coalesce(supportUser, currentUser) ELSE currentUser END as user, CASE WHEN orgActor is not null OR delegated OR repOf OR supports is not null THEN targetOrg ELSE null END as org, currentUser as tokenUser
dashql
15 122 ms 812 ms 900 ms 0.0× ↓
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
12 73 ms 700 ms 537 ms 39.5× ↑
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
13 67 ms 675 ms 833 ms 0.0× ↓
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
42 98 ms 650 ms 819 ms 0.2× ↓
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) WHERE (ta.state = $assignedState OR EXISTS { (ta)-[:PAR…
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) WHERE (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) CALL (u) { OPTIONAL MATCH (u)<-[:FOR_USER]-(actor:Actor{orgId:$orgId, scopeId:$orgId, userId:u.tildaid, deltimestamp:?, state:$activeActorState, delpending:false})-[:ORGANIZATION_TO_ACTOR]-(:Organization{tildaid:$orgId}) WHERE coalesce(u.status, ?) <> $deletedUserStatus AND coalesce(u.email, ?) <> $supportUserEmail AND EXISTS { MATCH (actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole AND ($anyMember OR size($roles) = ? OR r.name IN $roles) } AND ($anyMember OR ((size($roles) > ? OR $labelsNarrow) AND (NOT $labelsNarrow OR EXISTS { MATCH (actor)-[:ACTOR_TO_MEMBER_LABEL]->(l:MemberLabel{delpending:false})<-[:TRAINING_TO_MEMBER_LABEL]-(:Training{tildaid:$labelTrainingId}) }))) RETURN [] AS sites, reduce(acc = [], rs IN collect([(actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole | r.name]) | acc + rs) AS siteRoles } UNWIND siteRoles AS role WITH DISTINCT role WHERE role IS NOT NULL AND role <> ? RETURN role ORDER BY role
execsvc
2 410 ms 485 ms 411 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
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg optional match(p)-[r:USER_TO_PATIENT]-(u:User) with u.tildaid as …
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg optional match(p)-[r:USER_TO_PATIENT]-(u:User) with u.tildaid as patUserId, phi, p, mg optional match(p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with patUserId, phi, p, mg, collect(cond.tildaid) as condIds with * optional match(p)-[:BINDER_FOR]-(:Binder{binderType:?, scope:?})-[:INCLUDES]-(doc:Document{docTypeName:?})-[:BELONGS_TO]-(pdo:ParticipantDocOverlay{participantReviewed:true}) with count(pdo) as pdoCount, phi, p, mg, condIds, patUserId with * optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId}) with * match(phi)-[]-(ehrPhi:EhrPhi) return phi, p, mg, ehrPhi, condIds, patUserId, pdoCount, adv limit $pgLimit
patientsvc
1 368 ms 485 ms 368 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 flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study??ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess?? ?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 (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
write virtual-assistant
2 135 ms 470 ms 263 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)
write docsvc
2 171 ms 470 ms 308 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 CASE WHEN clog IS NULL OR clog.oldValue IS NULL OR NOT (clog.oldValue STARTS WITH ?) THEN ? ELSE coalesce( apoc.convert.fromJsonMap(clog.oldValue)[?], apoc.convert.fromJsonMap(clog.oldValue)[?], ? ) END AS originalFileName } SET dv.originalFileName = originalFileName RETURN count(dv)
write docsvc
2 134 ms 470 ms 253 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
write docsvc
2 132 ms 470 ms 263 ms flat
// Query by participantActivityId for single activity lookup // For bulk (visit start), this will be called for each activity MATCH (pa:ParticipantActivity {tildaid: $id}) MATCH (cp:CarePlan)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]->(pv:Parti…
// Query by participantActivityId for single activity lookup // For bulk (visit start), this will be called for each activity MATCH (pa:ParticipantActivity {tildaid: $id}) MATCH (cp:CarePlan)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]->(pv:ParticipantVisit)-[:PARTICIPANT_ACTIVITY]->(pa) MATCH (pa)-[:BELONGS_TO_ACTIVITY]->(a:ActivityV2) OPTIONAL MATCH (pv)-[:BELONGS_TO_VISIT]->(v2:VisitV2) OPTIONAL MATCH (pv)-[:BELONGS_TO_VISIT]->(v3:FormV2) MATCH (cp)-[:RESEARCH_SUBJECT]->(p:Patient) MATCH (cp)-[:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]->(s:Study) OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) OPTIONAL MATCH (protocol:ProtocolVersionV2 {tildaid: pv.protocolId}) OPTIONAL MATCH (org:Organization {tildaid: s.srcorgid}) WITH pa, a, pv, coalesce(v2, v3) as v, cp, p, pd, s, site, protocol, org CALL apoc.create.vNode([?], { createdat: pa.createdat, updatedat: pa.updatedat, participantActivityId: pa.tildaid, activityId: a.tildaid, activityName: a.title, activityType: a.activityType, participantVisitId: pv.tildaid, visitId: v.tildaid, visitName: v.title, careplanId: cp.tildaid, subjectId: cp.subjectid, siteId: site.tildaid, siteName: site.name, siteNumber: pd.siteNumber, studyId: s.tildaid, protocolId: protocol.tildaid, protocolName: protocol.title, protocolVersion: protocol.version, protocolSMOVersion: protocol.smoversion, orgId: org.tildaid, orgAlias: org.alias, status: pa.status, edcStatus: pa.edcStatus } ) YIELD node RETURN node
write dashrsvc
2 108 ms 470 ms 216 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 ) 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,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 ) 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
2 122 ms 470 ms 241 ms flat
match(s:Study{tildaid:$studyId})-[r]-(p:DslPlanDefinition{delpending:false})-[:SOA_TO_PLAN_DEFINITION]-(:ScheduleOfActivities) where p.sourceDataCapture is null or p.sourceDataCapture <> $sourceDataCapture with p match(p)-[r]-(site:Site)-[…
match(s:Study{tildaid:$studyId})-[r]-(p:DslPlanDefinition{delpending:false})-[:SOA_TO_PLAN_DEFINITION]-(:ScheduleOfActivities) where p.sourceDataCapture is null or p.sourceDataCapture <> $sourceDataCapture with p match(p)-[r]-(site:Site)-[:SITE_TO_SMO]->(smo:Smo{orgId:$orgId}) with site return site, ?.? as distance
enrollsvc
2 176 ms 470 ms 350 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
3 136 ms 455 ms 405 ms flat
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.firstname) contains tolower($phiFinder0) )with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg opti…
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.firstname) contains tolower($phiFinder0) )with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg optional match(p)-[r:USER_TO_PATIENT]-(u:User) with u.tildaid as patUserId, phi, p, mg optional match(p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with patUserId, phi, p, mg, collect(cond.tildaid) as condIds with * optional match(p)-[:BINDER_FOR]-(:Binder{binderType:?, scope:?})-[:INCLUDES]-(doc:Document{docTypeName:?})-[:BELONGS_TO]-(pdo:ParticipantDocOverlay{participantReviewed:true}) with count(pdo) as pdoCount, phi, p, mg, condIds, patUserId with * optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId}) with * match(phi)-[]-(ehrPhi:EhrPhi) return phi, p, mg, ehrPhi, condIds, patUserId, pdoCount, adv limit $pgLimit
patientsvc
5 68 ms 425 ms 275 ms flat
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.lastname) contains tolower($phiFinder0) and tolower(phi.dob) contains tolower($phiFinder1) and tolower(p…
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.lastname) contains tolower($phiFinder0) and tolower(phi.dob) contains tolower($phiFinder1) and tolower(phi.firstname) contains tolower($phiFinder2) ) or tolower(phi.mobilephone) contains tolower($phiFinder3) with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg optional match(p)-[r:USER_TO_PATIENT]-(u:User) with u.tildaid as patUserId, phi, p, mg optional match(p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with patUserId, phi, p, mg, collect(cond.tildaid) as condIds with * optional match(p)-[:BINDER_FOR]-(:Binder{binderType:?, scope:?})-[:INCLUDES]-(doc:Document{docTypeName:?})-[:BELONGS_TO]-(pdo:ParticipantDocOverlay{participantReviewed:true}) with count(pdo) as pdoCount, phi, p, mg, condIds, patUserId with * optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId}) with * match(phi)-[]-(ehrPhi:EhrPhi) return phi, p, mg, ehrPhi, condIds, patUserId, pdoCount, adv limit $pgLimit
patientsvc
6 51 ms 410 ms 297 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
write dashrsvc
7 60 ms 395 ms 410 ms 0.0× ↓
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
write dashrsvc
7 34 ms 395 ms 227 ms flat
match(a:Study{tildaid:$studyId})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) optional match (a)-[PLAN_DEFINITION_TO_STUDY]-(sitePD:DslPlanDefinition{type:?,delpending:false}) optional match (sitePD)-[PLAN_DEFINITION_TO_ADOPTED_PROTO…
match(a:Study{tildaid:$studyId})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) optional match (a)-[PLAN_DEFINITION_TO_STUDY]-(sitePD:DslPlanDefinition{type:?,delpending:false}) optional match (sitePD)-[PLAN_DEFINITION_TO_ADOPTED_PROTOCOL_VERSIONS]-(pv2:ProtocolVersionV2) return r.inherited, count(sitePD), CASE WHEN pv2 IS NOT NULL THEN true ELSE false END AS isStudyV2
enrollsvc
23 39 ms 385 ms 286 ms flat
MATCH(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_PATIENT]-(p:Patient{tildaid:$id}) OPTIONAL MATCH (p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with p, collect(cond.tildaid) as condIds OPTIONAL MATCH (p)-[:HAS_PROTECT…
MATCH(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_PATIENT]-(p:Patient{tildaid:$id}) OPTIONAL MATCH (p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with p, collect(cond.tildaid) as condIds OPTIONAL MATCH (p)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) OPTIONAL MATCH (p)<-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]-(:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(si:Site) WITH p, phi,condIds, cp, st, si ORDER BY st.name ASC WITH p, phi,condIds, collect({cp: cp, st: st, site:si}) AS cpStudyPairs OPTIONAL MATCH (s:Study)<-[:LEAD_TO_STUDY]-(l:Lead)-[:LEAD_TO_PATIENT]->(p) WITH p, phi,condIds, cpStudyPairs, l, s ORDER BY s.name ASC WITH p, phi,condIds, cpStudyPairs, collect({l: l, s: s}) AS lStudyPairs // Combine studyInfo and leadStudyInfo, ignoring leadStudyInfo if studyId matches WITH p, phi,condIds, cpStudyPairs, lStudyPairs, [pair IN cpStudyPairs WHERE pair.st IS NOT NULL OR pair.cp IS NOT NULL | { id: pair.st.tildaid, name: pair.st.name, sponsor: pair.st.sponsor, careplanId: pair.cp.tildaid, cpstatus: pair.cp.status, site: pair.site.name, subjectid: pair.cp.subjectid, isLocked: coalesce(pair.st.isLocked, false) } ] AS studyInfo, [pair IN lStudyPairs WHERE pair.s IS NOT NULL AND NOT pair.s.tildaid IN [cpPair IN cpStudyPairs WHERE cpPair.st IS NOT NULL | cpPair.st.tildaid] | { id: pair.s.tildaid, name: pair.s.name, sponsor: pair.s.sponsor, lstatus: pair.l.status, leadId: pair.l.tildaid, isLocked: coalesce(pair.s.isLocked, false) } ] AS leadStudyInfo WITH p, phi, condIds,studyInfo + leadStudyInfo AS combinedStudyInfo CALL apoc.create.vNode( [?], { patientId: p.tildaid, preferredLanguage: p.preferredlanguage, contactpreference: p.contactpreference, timepreference: p.timepreference, gender: p.sex, phiId: phi.tildaid, dob: phi.dob, firstname: phi.firstname, middlename: phi.middlename, lastname: phi.lastname, nickname: phi.nickname, suffix: phi.suffix, preferredname: phi.preferredname, address1: phi.address1, address2: phi.address2, city: phi.city, state: phi.state, country: phi.country, zippostalcode: phi.zippostalcode, latitude: phi.latitude, longitude: phi.longitude, email: phi.email, homephone: phi.homephone, workphone: phi.workphone, mobilephone: phi.mobilephone, primaryphonetype: phi.primaryphonetype, secondaryphonetype: phi.secondaryphonetype, emergencycontactname: phi.emergencycontactname, emergencycontactphonenumber: phi.emergencycontactphonenumber, emergencycontactrelation: phi.emergencycontactrelation, emergencycontactemail: phi.emergencycontactemail, hasStudyPartner: phi.hasstudypartner, studyPartnerName: phi.studypartnername, studyPartnerRelation: phi.studypartnerrelation, studyPartnerIsEmergencyContact: phi.studypartnerisemergencycontact, studyPartnerAddress1: phi.studypartneraddress1, studyPartnerAddress2: phi.studypartneraddress2, studyPartnerCity: phi.studypartnercity, studyPartnerState: phi.studypartnerstate, studyPartnerZipCode: phi.studypartnerzipcode, studyPartnerCountry: phi.studypartnercountry, studyPartnerEmail: phi.studypartneremail, studyPartnerPhone: phi.studypartnerphone, interestedconditionids: condIds, studyInfo: combinedStudyInfo }) YIELD node AS vNodes WITH COLLECT(vNodes) as profiles UNWIND profiles AS allprofiles RETURN allprofiles
write patientsvc
8 42 ms 380 ms 259 ms 0.2× ↓
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.firstname) contains tolower($phiFinder0) and tolower(phi.lastname) contains tolower($phiFinder1) and tol…
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(phi.firstname) contains tolower($phiFinder0) and tolower(phi.lastname) contains tolower($phiFinder1) and tolower(phi.dob) contains tolower($phiFinder2) ) or tolower(phi.mobilephone) contains tolower($phiFinder3) with phi, p match(p)-[]-(mg:MedicalGroup) with phi, p, mg optional match(p)-[r:USER_TO_PATIENT]-(u:User) with u.tildaid as patUserId, phi, p, mg optional match(p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) with patUserId, phi, p, mg, collect(cond.tildaid) as condIds with * optional match(p)-[:BINDER_FOR]-(:Binder{binderType:?, scope:?})-[:INCLUDES]-(doc:Document{docTypeName:?})-[:BELONGS_TO]-(pdo:ParticipantDocOverlay{participantReviewed:true}) with count(pdo) as pdoCount, phi, p, mg, condIds, patUserId with * optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId}) with * match(phi)-[]-(ehrPhi:EhrPhi) return phi, p, mg, ehrPhi, condIds, patUserId, pdoCount, adv limit $pgLimit
patientsvc
8 50 ms 380 ms 248 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
8 123 ms 380 ms 239 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
write dashrsvc
13 28 ms 305 ms 345 ms 0.0× ↓
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DE…
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
15 48 ms 275 ms 349 ms flat
MATCH (u:User) WHERE u.email <> $supportEmail AND u.status<>$deletedStatus AND (u)-[:USER_TO_PATIENT]->(:Patient) MATCH (o:Organization) WHERE o.alias = $orgAlias MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]…
MATCH (u:User) WHERE u.email <> $supportEmail AND u.status<>$deletedStatus AND (u)-[:USER_TO_PATIENT]->(:Patient) MATCH (o:Organization) WHERE o.alias = $orgAlias MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o)OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role)WITH DISTINCT u, COLLECT(DISTINCT r.name) AS roles, null as isBlinded, COLLECT(DISTINCT CASE WHEN r.listed = true THEN r.displayName END) AS displayRoles RETURN DISTINCT (u{.*,role:roles,displayRoles:displayRoles,isBlinded:isBlinded})
tusker
15 22 ms 275 ms 205 ms flat
MATCH (t:Training{status:$active, delpending:false}) WHERE t.reassignBeforeExpiryDays > ? AND t.validUntil > ? OPTIONAL MATCH (o:Organization{tildaid:t.orgId}) WITH t, coalesce(o.alias, CASE WHEN coalesce(t.orgId, ?) = ? THEN head([(:Study…
MATCH (t:Training{status:$active, delpending:false}) WHERE t.reassignBeforeExpiryDays > ? AND t.validUntil > ? OPTIONAL MATCH (o:Organization{tildaid:t.orgId}) WITH t, coalesce(o.alias, CASE WHEN coalesce(t.orgId, ?) = ? THEN head([(:Study{tildaid:t.scopeId})-[:STUDY_TO_ORG]-(p:Organization) WHERE p.orgType IN $privileged | p.alias]) END) AS ownerAlias CALL (t) { OPTIONAL MATCH (t)-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{state:$assigned, deltimestamp:?}) CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(cur:ParticipantActivity{deltimestamp:?}) WHERE NOT EXISTS { (cur)-[:NEXT_PARTICIPANT_ACTIVITY]->() } RETURN cur ORDER BY cur.createdat DESC LIMIT ? } WITH ta, cur WHERE cur.status IN $completedStatuses RETURN collect([ta.userId, reduce(latest = ?, ts IN [(cur)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?, ?] | CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END] | CASE WHEN ts > latest THEN ts ELSE latest END)]) AS completions } RETURN t, ownerAlias, completions
execsvc
1 197 ms 195 ms 197 ms flat
MATCH (proto:ProtocolVersionV2)-[:PROTOCOL_TO_FORM]->(form:FormV2) WHERE proto.state = ? AND proto.supportsactivityposition = true AND (proto.draftpublishedformmigrationfailed IS NULL OR proto.draftpublishedformmigrationfailed = false) AND…
MATCH (proto:ProtocolVersionV2)-[:PROTOCOL_TO_FORM]->(form:FormV2) WHERE proto.state = ? AND proto.supportsactivityposition = true AND (proto.draftpublishedformmigrationfailed IS NULL OR proto.draftpublishedformmigrationfailed = false) AND form.state IN [?, ?] AND form.tildaid <> proto.tildaid RETURN proto.tildaid, form.tildaid, form.type ORDER BY proto.tildaid, form.tildaid LIMIT ?
studiosvc
1 127 ms 195 ms 127 ms flat
match(:User{email:$staffEmail})-[]-(:Staff)-[:W2026_45 | W2026_46]-(r:ResourceAppointment) where (isEmpty(r.type) or r.type=?) optional match (r)-[:RESOURCE_APPOINTMENTS]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(:CarePlan)-[…
match(:User{email:$staffEmail})-[]-(:Staff)-[:W2026_45 | W2026_46]-(r:ResourceAppointment) where (isEmpty(r.type) or r.type=?) optional match (r)-[:RESOURCE_APPOINTMENTS]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(:CarePlan)-[:RESEARCH_SUBJECT]-(:Patient)-[:HAS_PROTECTED_DATA_IN]-(p:Phi) optional match (v)-[]-(s:Site)-[:SITE_TO_SMO]->(smo:Smo)-[:IS_AN_ORG]->(o:Organization{alias:$orgAlias}) optional match (v)-[:VISIT_APPOINTMENT_TO_CONFERENCE_DATA]-(c:ConferenceData) return r,p,c,v,s.tildaid
schedsvc
1 119 ms 195 ms 119 ms 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)
write docsvc
2 152 ms 195 ms 166 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)
write docsvc
2 110 ms 195 ms 119 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
write onboardingsvc
1 153 ms 195 ms 153 ms flat
MATCH (pv:ParticipantVisit {tildaid: $id})-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) MATCH (pv)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)-[:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)- [:…
MATCH (pv:ParticipantVisit {tildaid: $id})-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) MATCH (pv)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)-[:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_STUDY]-(s:Study) OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) OPTIONAL MATCH (site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (cp)-[:RESEARCH_SUBJECT]-(p:Patient) CALL apoc.create.vNode([?], { siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), studyId: s.tildaid, studyName: s.name, orgId: coalesce(org.tildaid, ?), orgName: coalesce(org.name, ?), orgAlias: coalesce(org.alias, ?), createdat: pv.createdat, updatedat: pv.updatedat, branchLevel: pv.branchLevel, pre: pv.pre, migrationReady: pv.migrationReady, sex: p.sex, ethnicity: p.ethnicity, carePlanStatus: cp.status, pvStatus: pv.status, edcStatus: pv.edcStatus } ) YIELD node return node
write dashrsvc
1 117 ms 195 ms 117 ms flat
MATCH(va:VisitAppointment {tildaid: $id})-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(cp:CarePlan)-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]- (study:Study) OPTIONAL MATCH…
MATCH(va:VisitAppointment {tildaid: $id})-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(cp:CarePlan)-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]- (study:Study) OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) OPTIONAL MATCH (site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(org:Organization) CALL apoc.create.vNode([?], { startTime: va.startTime, endTime: va.endTime, state: va.state, visitId: va.visitId, carePlanId: va.carePlanId, visitTitle: va.visitTitle, visitName: va.visitName, remote: va.remote, attendees: va.attendees, owner: va.owner, siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), studyId: study.tildaid, studyName: study.name, orgId: coalesce(org.tildaid, ?), orgName: coalesce(org.name, ?), orgAlias: coalesce(org.alias, ?), createdat: va.createdat, updatedat: va.updatedat } ) YIELD node return node
write dashrsvc
1 131 ms 195 ms 131 ms flat
MATCH (pv:ParticipantVisit {tildaid: $id})-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) MATCH (pv)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)-[:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)- [:…
MATCH (pv:ParticipantVisit {tildaid: $id})-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) MATCH (pv)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)-[:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_STUDY]-(s:Study) OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) OPTIONAL MATCH (site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (cp)-[:RESEARCH_SUBJECT]-(p:Patient) CALL apoc.create.vNode([?], { siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), studyId: s.tildaid, studyName: s.name, orgId: coalesce(org.tildaid, ?), orgName: coalesce(org.name, ?), orgAlias: coalesce(org.alias, ?), createdat: pv.createdat, updatedat: pv.updatedat, branchLevel: pv.branchLevel, pre: pv.pre, migrationReady: pv.migrationReady, sex: p.sex, ethnicity: p.ethnicity, carePlanStatus: cp.status, pvStatus: pv.status, edcStatus: pv.edcStatus } ) YIELD node return node
write dashrsvc
1 151 ms 195 ms 151 ms flat
MATCH(lead:Lead {tildaid: $id}) MATCH (lead)-[:LEAD_TO_STUDY]-(study:Study) match (lead)-[:LEAD_TO_PATIENT]->(patient:Patient) MATCH(org:Organization) WHERE org.tildaid=study.srcorgid OPTIONAL MATCH (study)-[:PLAN_DEFINITION_TO_STUDY]-(dsl…
MATCH(lead:Lead {tildaid: $id}) MATCH (lead)-[:LEAD_TO_STUDY]-(study:Study) match (lead)-[:LEAD_TO_PATIENT]->(patient:Patient) MATCH(org:Organization) WHERE org.tildaid=study.srcorgid OPTIONAL MATCH (study)-[:PLAN_DEFINITION_TO_STUDY]-(dsl:DslPlanDefinition)-[:SOA_TO_PLAN_DEFINITION]-(:ScheduleOfActivities)-[:EXECUTE_SOA]-(:CarePlan)-[:RESEARCH_SUBJECT]-(patient) OPTIONAL MATCH (dsl)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) OPTIONAL MATCH (lead)-[:LEAD_TO_ADVOCATE]-(adv:Advocate)-[:ADV_TO_PORTAL_ORG]-(advOrg:AdvOrg) OPTIONAL MATCH (user:User{tildaid: adv.userid}) OPTIONAL MATCH (recruiterOrg:Organization{tildaid: adv.orgid}) CALL apoc.create.vNode([?], { studyName: study.name, orgId: org.tildaid, orgName: org.name, orgAlias: org.alias, siteId: site.tildaid, siteName: site.name, createdat: lead.createdat, updatedat: lead.updatedat, sourceType: lead.sourcetype, status: lead.status, leadId: lead.tildaid, patientId: lead.patientid, studyId: lead.studyid, recruiterOrg: recruiterOrg.name, recruiter: user.name, sex: patient.sex, ethnicity: patient.ethnicity } ) YIELD node RETURN node
write dashrsvc
1 184 ms 195 ms 184 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 135 ms 195 ms 135 ms flat
match(a:Study{delpending:false,status:$status})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) where a.tildaid in $studyScope with a optional match(a)-[scope:STUDY_TO_ACTOR]->(actor:Actor{orgId:$orgId,userId:$userId,delpending:false})-…
match(a:Study{delpending:false,status:$status})-[r:STUDY_TO_ORG]->(b:Organization{tildaid:$orgId}) where a.tildaid in $studyScope with a optional match(a)-[scope:STUDY_TO_ACTOR]->(actor:Actor{orgId:$orgId,userId:$userId,delpending:false})-[APPOINTED_AS]-(role:Role) with a, role with a, collect(role.displayName) as roles return count(a)
enrollsvc
1 187 ms 195 ms 187 ms flat
MATCH (pd:DslPlanDefinition{tildaid: $id}) MATCH (pd)-[:PLAN_DEFINITION_TO_WORKFLOW]->(workflow:WorkflowV2) OPTIONAL MATCH (workflow)-[:ALL_CHANGELOG]->(cl:ChangeLog) WITH workflow, pd, cl ORDER BY cl.createdat ASC WITH workflow, pd, head(…
MATCH (pd:DslPlanDefinition{tildaid: $id}) MATCH (pd)-[:PLAN_DEFINITION_TO_WORKFLOW]->(workflow:WorkflowV2) OPTIONAL MATCH (workflow)-[:ALL_CHANGELOG]->(cl:ChangeLog) WITH workflow, pd, cl ORDER BY cl.createdat ASC WITH workflow, pd, head(collect(cl)) as cl OPTIONAL MATCH (u:User{tildaid:cl.who}) WITH workflow, pd, CASE WHEN u IS NOT NULL THEN u.name ELSE workflow.createdBy END as user OPTIONAL MATCH (workflow)-[r:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS|PLAN_DEFINITION_TO_PUBLISHED_WORKFLOWS]- (sitePD:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study{tildaid:pd.studyId}) OPTIONAL MATCH (sitePD)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) RETURN DISTINCT workflow, user, collect({site:site, r:type(r)})
regsvc
1 122 ms 195 ms 122 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
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
2 92 ms 190 ms 144 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)) RETURN DISTINCT u.tildaid AS id, u.name AS name ORDER BY name, id SKIP $skip LIMIT $limit
execsvc
4 96 ms 190 ms 191 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)) RETURN count(DISTINCT u.tildaid)
execsvc
4 69 ms 190 ms 145 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
write virtual-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
MATCH (t1:Task{tildaid:$id})-[:CREATED_FROM_SPEC] -(ptc1:ParticipantTaskConfig)-[:BELONGS_TO_TASKCONFIG] -(tc1:TaskConfig)-[:GRAPH_NEXT]-(tl1:TaskList) -[:GRAPH_NODE_REF_TO_GRAPH_NODE]-(gnr1:GraphNodeRef)-[:GRAPH_NEXT]-(c:ControlV2) RETURN…
MATCH (t1:Task{tildaid:$id})-[:CREATED_FROM_SPEC] -(ptc1:ParticipantTaskConfig)-[:BELONGS_TO_TASKCONFIG] -(tc1:TaskConfig)-[:GRAPH_NEXT]-(tl1:TaskList) -[:GRAPH_NODE_REF_TO_GRAPH_NODE]-(gnr1:GraphNodeRef)-[:GRAPH_NEXT]-(c:ControlV2) RETURN DISTINCT t1 as t UNION ALL MATCH (t2:Task{tildaid:$id1})-[:CREATED_FROM_SPEC] -(ptc2:ParticipantTaskConfig)-[:BELONGS_TO_TASKCONFIG] -(tc2:TaskConfig)-[:GRAPH_NEXT]-(tl2:TaskList) -[:GRAPH_NODE_REF_TO_GRAPH_NODE]-(gnr2:GraphNodeRef)-[:GRAPH_NEXT]-(act:ActivityV2) RETURN DISTINCT t2 as t UNION ALL MATCH (t3:Task{tildaid:$id2})-[:CREATED_FROM_SPEC] -(ptc3:ParticipantTaskConfig)-[:BELONGS_TO_TASKCONFIG] -(tc3:TaskConfig)-[:GRAPH_NEXT]-(tl3:TaskList) -[:GRAPH_NODE_REF_TO_GRAPH_NODE]-(gnr3:GraphNodeRef)-[:GRAPH_NEXT]-(todo:TodoV2) RETURN DISTINCT t3 as t
regsvc
2 90 ms 190 ms 179 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
write virtual-assistant
3 41 ms 185 ms 103 ms flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study?Basic??ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess??Inactive' AND sst.tildaid IN studyIdsInScope AND (doc1.siteId = ? OR (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(:DslPlanDefinition{delpending:false,type:?})- [:PLAN_DEFINITION_TO_SITE]-(:Site{tildaid:doc1.siteId})) RETURN doc1 as doc UNION WITH doc1, studyIdsInScope, directAdmit WITH doc1, studyIdsInScope WHERE NOT directAdmit AND (NOT $nonSiteOnlyPatients0 OR doc1.patientId = ?) AND doc1.siteId <> ? MATCH (s:Site{tildaid:doc1.siteId, delpending:false})-[:PLAN_DEFINITION_TO_SITE]- (pd:DslPlanDefinition{delpending:false,type:?})-[:PLAN_DEFINITION_TO_STUDY]->(sst:Study) WHERE sst.tildaid IN studyIdsInScope RETURN doc1 as doc UNION WITH doc1, directAdmit WITH doc1 WHERE directAdmit RETURN doc1 as doc} WITH DISTINCT doc WITH doc, coalesce(doc.archived, false) as isArchived RETURN count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as readyForTmf, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as needsCorrection, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as needsAttention, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as draft, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as aiReview, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as manual, count(CASE WHEN isArchived THEN ? END) as archived
docsvc
3 51 ms 185 ms 127 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).