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
stored25h agotop 80 of 2,218
Neo4j query shapes on master over the last 5d, grouped by plan hash.
Click a column to sort; p95 drift (late-half ÷ early-half of the window) ranks the queries getting slower.
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
205
1.93 s
12.29 s
22.93 s
flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? …
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_3 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
chat-agent
1
2.10 s
4.85 s
2.10 s
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
2.26 s
4.85 s
2.26 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 ) MATCH (wo:WorkOrder {tildaid:c.id}) WHERE wo.study_id = st.tildaid 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 ) MATCH (wo:WorkOrder {tildaid:c.id}) WHERE wo.study_id = st.tildaid 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
5
2.09 s
4.75 s
3.57 s
flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) W…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_3 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
chat-agent
3
700 ms
4.55 s
2.01 s
flat
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) MATCH (r:Recommendation) WHERE r.doc_id = d.tildaid AND NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO…
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) MATCH (r:Recommendation) WHERE r.doc_id = d.tildaid AND NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (u: User) 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 } WITH r, ai, o, d, dv, zone, section, artifact, subArtifact, u, study, site, siteNumber, attributesJson, // 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?t emit irrelevant rows (e.g. country on a site-scoped rec). recommendedValue: CASE WHEN r.category = ? AND recAllowed IS NOT NULL THEN apoc.convert.toJson(apoc.map.fromPairs([k IN keys(recMap) WHERE k IN recAllowed | [k, recMap[k]]])) ELSE coalesce(CASE WHEN coalesce(r.data_formatted, ?) <> ? THEN r.data_formatted END, r.data, ?) END, // originalRecommendation = the AI?Document Scope????Document Metadata?{}??Document Scope???Document Naming??TMF Artifact???.???.??? ??Sub Artifact??Document Scope??country???site?????Document Metadata??Document Metadata?????{}??????{}??{}??Document Scope?????country???site??????Document Naming???????TMF Artifact????????.???.??? ??Sub Artifact???????Document Format?Document Summary?Quality Control?PHI Issues?Confirm?Fix It????' } ) YIELD node RETURN node
writedashrsvc
7
391 ms
3.95 s
2.44 s
flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) W…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_3 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
data_monitor
1
1.86 s
1.95 s
1.86 s
flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? …
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) 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 // 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 ) 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 } RETURN checkIdx, User, Organization, Study, Site, Patient
ontologysvc
1
1.15 s
1.95 s
1.15 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 AND b.status <> ? OPTIONAL MATCH (b)-[:…
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 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 OR (wc.status = ? AND wc.last_response IS NOT NULL AND coalesce(wc.resolved_at, wc.status_updated_at) IS NOT NULL AND wc.last_response > coalesce(wc.resolved_at, wc.status_updated_at) AND wc.last_response > coalesce(wc.late_reply_ack_at, ?))) 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 OR (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, ?))) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(wStudy:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(wSite:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(wCreator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ASSIGNEE]->(wAssignee:User) // Resolve EventScope for Event-type child WOs (null for Standard-type) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_EVENT_SCOPE]->(wES:EventScope) // Look up the Site node only for Site-scoped events; guards against null wES and non-Site scopes. // wES.scopeId is a site tildaid (UUID) for Site scopes, country name for Country, ? for Trial. OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ? OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key}) OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_TASK]->(wTask:WorkOrderTask) WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, w, wStudy, wSite, wCreator, wAssignee, wES, wES_Site, wPartner, wMember, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_pending_count, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_resolved_count // ENG-?: order children in the DB so children_limit keeps the first N in // DISPLAY order. collect() below preserves incoming row order. // The PARENT keyset terms MUST lead. This ORDER BY runs in a WITH stage AFTER // the parent ORDER BY/LIMIT, and a later ORDER BY re-sorts the whole row stream // — ordering on the child value alone silently re-ranked the CONFIGS by their // child?s QUERY order, not display order: the backward // reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC, (w.createdat IS NULL), w.createdat DESC, w.tildaid WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, collect(DISTINCT CASE WHEN w IS NOT NULL THEN { tildaid: w.tildaid, work_order_id: w.work_order_id, title: w.title, work_order_type: w.work_order_type, status: w.status, priority: w.priority, creator_id: w.creator_id, assigned_to_id: w.assigned_to_id, site_id: w.site_id, study_id: w.study_id, document_ids: w.document_ids, contact_list: w.contact_list, cc_list: w.cc_list, goal: w.goal, due_date: w.due_date, last_reachout: w.last_reachout, last_response: w.last_response, resolved_at: w.resolved_at, need_attention_reason: w.need_attention_reason, late_reply: (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, ?)), accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
writevirtual-assistant
1
1.15 s
1.95 s
1.15 s
flat
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) MATCH (r:Recommendation) WHERE r.doc_id = d.tildaid AND NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO…
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) MATCH (r:Recommendation) WHERE r.doc_id = d.tildaid AND NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (u: User) 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 } WITH r, ai, o, d, dv, zone, section, artifact, subArtifact, u, study, site, siteNumber, attributesJson, // 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?t emit irrelevant rows (e.g. country on a site-scoped rec). recommendedValue: CASE WHEN r.category = ? AND recAllowed IS NOT NULL THEN apoc.convert.toJson(apoc.map.fromPairs([k IN keys(recMap) WHERE k IN recAllowed | [k, recMap[k]]])) ELSE coalesce(CASE WHEN coalesce(r.data_formatted, ?) <> ? THEN r.data_formatted END, r.data, ?) END, // originalRecommendation = the AI?Document Scope????Document Metadata?{}??Document Scope???Document Naming??TMF Artifact???.???.??? ??Sub Artifact??Document Scope??country???site?????Document Metadata??Document Metadata?????{}??????{}??{}??Document Scope?????country???site??????Document Naming???????TMF Artifact????????.???.??? ??Sub Artifact???????Document Format?Document Summary?Quality Control?PHI Issues?Confirm?Fix It????' } ) YIELD node RETURN node
writedashrsvc
1
1.73 s
1.95 s
1.73 s
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
23
350 ms
1.92 s
3.23 s
flat
match (task:Task) where task.tildaid in $lists.taskIds with distinct task as t call apoc.do.case([ t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContex…
MATCH (doc:Document) WHERE doc.tildaid in $lists.documentIds0 WITH collect(DISTINCT doc) as documents, count(DISTINCT doc) as total UNWIND documents as doc WITH doc, total, null as vector MATCH (org:Organization{tildaid:$policyOrgId0}) CAL…
MATCH (doc:Document) WHERE doc.tildaid in $lists.documentIds0 WITH collect(DISTINCT doc) as documents, count(DISTINCT doc) as total UNWIND documents as doc WITH doc, total, null as vector MATCH (org:Organization{tildaid:$policyOrgId0}) CALL { WITH org, doc UNWIND [doc.studyId] AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study?tildaScale?ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly' ELSE docAccessLevel END AS docAccessLevel CALL {return null as patientId, null as patientName, null as subjectId}CALL { return null as site, ? as siteNumber, [] as piNames }CALL { return null as visit }CALL { return null as user }CALL { return null as uploadedBy }CALL { return null as docType }CALL { return null as signedByUsers , null as reviewDate } WITH distinct total, doc, docObj, vector, owned, study, org, docAccessLevel, isSigner, patientId, patientName, subjectId, site, siteNumber, piNames, visit, user, uploadedBy, docType, signedByUsers, reviewDate order by doc.createdat asc , doc.tildaid asc skip $skip0 CALL { return null as workOrders, false as woContact, null as latestAIRun, null as findingCount, null as queriesCount, null as confirmedCount, null as resolvedCount }CALL { return null as tags } CALL { return null as path, null as fold, null as collection, null as stateTags1 } CALL { return null as primaryFolder } CALL { return null as listingFolders } CALL { return null as stateTags2, null as stagingLabels } CALL { return [] as reviews} CALL { return null as templateDetails} CALL { return null as versions } CALL { with doc optional match (doc)-[:HAS]-(ann:DocumentAnnotation) optional match (ann)-[:ANNOTATION_SIGNER]-(ds:DocumentSigner) optional match (ann)-[:ANNOTATION_FIELD]-(df:DocumentField) optional match (ann)-[:ALL_CHANGELOG]->(cl:ChangeLog) with doc, ann, ds, df, cl order by cl.createdat asc with doc, ann, apoc.convert.fromJsonList(ann.signers) as signersOld, collect(distinct ds{.*}) as signers, collect(distinct df{.*}) as fields, collect(distinct cl{.*}) as auditLog with doc, ann, auditLog, case when size(signers) <> ? then signers when size(signers) = ? and signersOld is null then [] else signersOld end as signers1, case when fields is null then [] else fields end as fields1 with doc, ann{.*, signers: signers1, fields: fields1} as annotationSpec, case when doc.template = false then ann{.*, signers: signers1, fields: fields1} else null end as annotationVal return annotationSpec, annotationVal } CALL { return [] as queries } CALL { WITH docObj, owned OPTIONAL MATCH (docObj)<-[:REFERS_TO]-(docShare:DocumentShare)-[:DOC_SHARED_TO_ORG]->(otherOrg:Organization) WHERE owned CALL { WITH docShare, docObj OPTIONAL MATCH (docShare)-[signedByRel:SIGNED_BY]->(signedBy:User) WHERE (docObj.docKind = ? OR coalesce(signedByRel.majorVersion, docObj.majorVersion, ?) = coalesce(head([(docObj)-[:DOCUMENT_VERSION]->(vv:DocumentVersion{tildaid: $reviewedVersionId0}) | vv.majorVersion]), docObj.majorVersion, ?)) WITH COLLECT(DISTINCT({id: signedBy.tildaid, name: signedBy.name, profilePicURL: signedBy.profilePicURL})) as signedByUsers, count(signedBy) as signersCount, max(signedByRel.createdat) as reviewDate RETURN CASE WHEN signersCount<>? THEN signedByUsers ELSE [] END as signedByUsers, reviewDate } CALL { WITH docShare OPTIONAL MATCH (docShare)-[:ALL_CHANGELOG]->(c:ChangeLog) WHERE c.level IS NULL OR c.level = ? OPTIONAL MATCH (e:ESignature{tildaid:c.blob}) WITH CASE WHEN NOT c is NULL THEN {changeLog: c{.*}, eSignature: e{.*}} END as clog order by c.createdat asc RETURN collect(clog) as changeLog } WITH CASE WHEN NOT otherOrg is NULL THEN {readerOrg: otherOrg.name, readerOrgId: otherOrg.tildaid, readerOrgAlias: otherOrg.alias, status: docShare.ownerStatus, signedByUsers: signedByUsers, reviewDate: reviewDate, changeLog: changeLog} END as shareEntry RETURN collect(shareEntry) as sharedWith } CALL { WITH docObj RETURN CASE WHEN (docObj)-[:REFERS_TO]->(:Document) THEN docObj{.*} ELSE null END as currentShare } CALL { return null as docAttributes } CALL { return null as notesCount } CALL { return null as eventId, null as eventName, null as deliverable, null as eventScope } CALL { return null as copiedFrom } CALL { return [] as copiedTo, ? as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { return null as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
2
540 ms
1.90 s
1.07 s
flat
CALL () { MATCH (tt:Training) WHERE tt.tildaid IN $trainingIds AND (coalesce(tt.siteScope, ?) = $studyTeamScope) AND (tt.orgId <> ? AND tt.orgId = $ownerOrgId) RETURN collect(tt.tildaid) AS teamTrainingIds } MATCH (u:User) WHERE u.tildaid …
CALL () { MATCH (tt:Training) WHERE tt.tildaid IN $trainingIds AND (coalesce(tt.siteScope, ?) = $studyTeamScope) AND (tt.orgId <> ? AND tt.orgId = $ownerOrgId) RETURN collect(tt.tildaid) AS teamTrainingIds } MATCH (u:User) WHERE u.tildaid IN $userIds CALL (u) { OPTIONAL MATCH (ss:Site{delpending:false})-[:PLAN_DEFINITION_TO_SITE]-(spd:DslPlanDefinition{delpending:false, studyId:$studyId})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(sssa:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_ACTOR]->(sa:Actor{scopeId:$studyId, state:$activeActorState, delpending:false})-[:FOR_USER]->(u:User) WHERE sa.orgId = ss.orgId AND NOT EXISTS { (ss)-[:SITE_TO_ORGANIZATION]->(siteOrg:Organization) WHERE siteOrg.deltimestamp <> ? } AND ($viewerSiteOrgId = ? OR ss.orgId = $viewerSiteOrgId) WITH ss, spd, sssa, sa ORDER BY ss.name, ss.tildaid, sssa.createdat, sssa.tildaid WITH ss, spd, collect(head(sssa.roles)) AS rolesAtSite, collect(EXISTS { (sa)-[:IS_PRIMARY_ROLE]->(pr:Role) WHERE pr.name IN sssa.roles }) AS primaryAtSite ORDER BY ss.name, ss.tildaid RETURN collect(CASE WHEN ss IS NULL THEN null ELSE {siteId:ss.tildaid, siteName:ss.name, siteNumber:spd.siteNumber, role:head(rolesAtSite), isPrimary:true IN primaryAtSite} END) AS sites, reduce(acc = [], rs IN collect(rolesAtSite) | acc + rs) AS siteRoles } CALL (u, teamTrainingIds) { WITH u, teamTrainingIds WHERE size(teamTrainingIds) > ? MATCH (:Study{tildaid:$studyId})-[:STUDY_TO_ACTOR]->(actor:Actor{orgId:$ownerOrgId, scopeId:$studyId, state:$activeActorState, delpending:false})-[:FOR_USER]->(u:User) WHERE coalesce(u.status, ?) <> $deletedUserStatus AND EXISTS { MATCH (actor)-[:APPOINTED_AS]->(tr:Role) WHERE tr.name <> $supportRole } AND ($anyRole OR EXISTS { MATCH (actor)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> $supportRole AND r.name IN $roles } OR ($byLabel AND EXISTS { MATCH (oa:Actor{orgId:$ownerOrgId, scopeId:$ownerOrgId, userId:u.tildaid, delpending:false})-[:ACTOR_TO_MEMBER_LABEL]->(ml:MemberLabel{delpending:false})<-[:TRAINING_TO_MEMBER_LABEL]-(:Training{tildaid:$labelTrainingId}) WHERE oa.state <> $inactiveActorState })) AND EXISTS { MATCH (hta:TrainingAssignment{userId:u.tildaid, deltimestamp:?}) WHERE hta.trainingId IN teamTrainingIds AND (hta.state = $assignedState OR EXISTS { (hta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) } RETURN reduce(acc = [], rs IN collect([(actor)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> $supportRole | r.name]) | acc + rs) AS studyRoles } CALL (u) { MATCH (oa:Actor{orgId:$labelOrgId, scopeId:$labelOrgId, userId:u.tildaid, delpending:false})-[:ACTOR_TO_MEMBER_LABEL]->(ml:MemberLabel{delpending:false}) WHERE oa.state <> $inactiveActorState WITH DISTINCT ml.name AS labelName ORDER BY labelName RETURN collect(labelName) AS memberLabels } MATCH (t:Training{scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE pa.status IN $completedStatuses AND NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END AND t.status = $activeStatus AND (NOT ((coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType)) OR (t.orgId <> ? AND t.orgId = $callerOrgId)) AND ta.trainingId IN $trainingIds AND ta.userId IN $userIds CALL (pa) { OPTIONAL MATCH (pa)-[:BELONGS_TO_ACTIVITY]-(tpAct:ActivityV2) OPTIONAL MATCH (pa)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH pa, tpAct, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH pa, tpAct, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax RETURN tpFailed = ? AND (pa.status = ? OR coalesce(tpAct.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(tpAct.passingScore, ?) * tpTotalMax) AS passed } CALL (pa) { OPTIONAL MATCH (act:ActivityV2{tildaid: pa.activityId}) RETURN act.version AS activityVersion LIMIT ? } RETURN u.tildaid, u.name, u.email, t.tildaid, t.name, pa.tildaid, pa.status, pa.createdat, pa.dueDate, sites, siteRoles + studyRoles, [(pa)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?, ?] | [CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason, cl.who]] AS completions, passed, [(pa)-[:ALL_CHANGELOG]->(acl:ChangeLog) WHERE acl.type = ? AND acl.newValue IN [?, ?] | [acl.createdat, acl.who]] AS assignments, ta.source, pa.activityId, activityVersion, ta.state, t.validUntil, t.validUntilUnits, memberLabels ORDER BY u.name, u.tildaid, t.name, pa.createdat DESC
execsvc
2
660 ms
1.90 s
1.27 s
flat
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:P…
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId) RETURN ta, training, null AS study UNION MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?}) WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false}) WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) RETURN ta, training, study } CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, study, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
execsvc
2
545 ms
1.90 s
1.09 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,lead-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)…
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,lead-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 WITH * WHERE o.orgType = ? MATCH (o)-[:ORGANIZATION_TO_LEAD]->(l:Lead {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=Study policies=study-lead-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_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 ) MATCH (st)<-[:LEAD_TO_STUDY]-(l:Lead {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-lead-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_2 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) MATCH (st)<-[:LEAD_TO_STUDY]-(l:Lead {tildaid:c.id}) 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
patientsvc
2
740 ms
1.90 s
1.41 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,lead-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)…
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,lead-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 WITH * WHERE o.orgType = ? MATCH (o)-[:ORGANIZATION_TO_LEAD]->(l:Lead {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=Study policies=study-lead-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_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 ) MATCH (st)<-[:LEAD_TO_STUDY]-(l:Lead {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-lead-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_2 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) MATCH (st)<-[:LEAD_TO_STUDY]-(l:Lead {tildaid:c.id}) 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
patientsvc
2
636 ms
1.90 s
1.24 s
flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) W…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_3 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
chat-agent
2
912 ms
1.90 s
1.82 s
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.event_id = $event_id OPTION…
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.event_id = $event_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, need_attention_reason: w.need_attention_reason, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
writevirtual-assistant
6
649 ms
1.90 s
1.31 s
flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE COALESCE(tracksRel.state, ?) in $userDecisions MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PA…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE COALESCE(tracksRel.state, ?) in $userDecisions MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVENT]->(e) WHERE es.tildaid in $scopeIds OPTIONAL MATCH (wo:WorkOrder)-[woClaim:WO_CLAIMS_PLACEHOLDER]->(ph) WHERE woClaim.is_current = true WITH e, ph, d, es, tracksRel, head(collect(wo)) AS wo OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND matchDoc.collection = ? THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as matchesExpectedCollected, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND (matchDoc.collection IS NULL OR matchDoc.collection <> ?) THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as pendingMatchesCollected WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, [m IN matchesExpectedCollected WHERE m IS NOT NULL] as matchesExpected, [m IN pendingMatchesCollected WHERE m IS NOT NULL] as pendingMatches WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchesExpected) > ? AND SIZE(pendingMatches) = ? THEN ? WHEN SIZE(matchesExpected) > ? OR SIZE(pendingMatches) > ? THEN ? ELSE ? END as matchStatus OPTIONAL MATCH (ph)-[renewalRel:RENEWAL_OF]->(renewalSrcCandidate:Document) WHERE coalesce(renewalRel.primary, false) = true AND coalesce(renewalSrcCandidate.archived, false) = false WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, head(collect(renewalSrcCandidate)) AS renewalSrc OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study) OPTIONAL MATCH (site:Site {tildaid: es.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, renewalSrc, COALESCE(head(collect(site.name)), ?) AS siteName, COALESCE(head(collect(pd.siteNumber)), ?) AS siteNumber ORDER BY d.name ASC, ph.fileName ASC WITH collect({ placeholderId: ph.tildaid, placeholderName: ph.fileName, deliverableId: d.tildaid, deliverableName: d.name, eventScopeId: es.tildaid, scopeType: es.scopeType, country: CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END, siteNumber: siteNumber, siteName: siteName, dueDate: es.dueDate, matchStatus: matchStatus, userDecision: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR es.status = ?, origin: COALESCE(tracksRel.origin, ?), feedback: COALESCE(tracksRel.feedback, ?), reason: COALESCE(tracksRel.reason, ?), removalHasAcceptedDocument: COALESCE(tracksRel.removalHasAcceptedDocument, false), workOrder: CASE WHEN wo IS NOT NULL THEN {tildaid: wo.tildaid, work_order_id: wo.work_order_id, status: wo.status} ELSE null END, renewalSource: CASE WHEN renewalSrc IS NOT NULL THEN {documentId: renewalSrc.tildaid, documentName: renewalSrc.fileName} ELSE null END, acceptedMatch: CASE WHEN acceptedDoc IS NOT NULL THEN { documentId: acceptedDoc.tildaid, displayId: acceptedDoc.displayId, contentType: acceptedDoc.contentType, documentName: acceptedDoc.fileName, documentDate: CASE WHEN acceptedDoc.createdat IS NOT NULL THEN datetime({epochMillis: acceptedDoc.createdat}) ELSE null END, isAccepted: true, matchedBy: COALESCE(head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | amr.matchedBy ]), ?), matchedAt: head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | CASE WHEN amr.createdat IS NOT NULL THEN datetime({epochMillis: amr.createdat}) ELSE null END ]), acceptedBy: acceptedRel.acceptedBy, acceptedAt: CASE WHEN acceptedRel.createdat IS NOT NULL THEN datetime({epochMillis: acceptedRel.createdat}) ELSE null END, docAttributes: [ (acceptedDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: acceptedDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } ELSE null END, matchesExpected: matchesExpected, pendingMatches: pendingMatches }) as allRows WITH allRows, [r IN allRows WHERE r.matchStatus = $rowMatchStatus] as filteredRows WITH allRows, filteredRows, { total: size(allRows), pending: size([pp IN allRows WHERE pp.userDecision IN [?, ?]]), pendingForDelete: size([pp IN allRows WHERE pp.userDecision = ?]), accepted: size([pp IN allRows WHERE pp.userDecision = ?]), rejected: size([pp IN allRows WHERE pp.userDecision = ?]), incomplete: size([pp IN allRows WHERE pp.matchStatus = ?]), potentialMatchFound: size([pp IN allRows WHERE pp.matchStatus = ?]), matchExpected: size([pp IN allRows WHERE pp.matchStatus = ?]), matched: size([pp IN allRows WHERE pp.matchStatus = ?]), finalized: size([pp IN allRows WHERE pp.matchStatus = ?]), completion: {required: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]), completed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]), aiSuggested: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), inStaging: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), noMatchFound: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), notConfirmed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND pp.userDecision = ? AND NOT pp.matchStatus = ?]), removed: size([pp IN allRows WHERE (pp.userDecision = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) / size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]))) END, isComplete: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) > ? AND size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) = size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)])}, matchedWithNew: size([pp IN allRows WHERE pp.matchStatus = ? AND (size(pp.pendingMatches) > ? OR size(pp.matchesExpected) > ?)]) } as progress RETURN filteredRows[$pageSkip..$pageEnd] as placeholders, size(filteredRows) as total, progress
docsvc
7
495 ms
1.88 s
1.20 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 ) MATCH (wo:WorkOrder) WHERE wo.study_id = st.tildaid 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 ) MATCH (wo:WorkOrder) WHERE wo.study_id = st.tildaid 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
feedsvc
5
898 ms
1.88 s
1.89 s
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
25
380 ms
1.88 s
3.62 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
3
448 ms
1.85 s
1.29 s
flat
match (task:Task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(…
match (task:Task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) match (task)-[:BELONGS_TO]->(SInbox:Inbox{type:?})-[:MANAGED_BY]->(Study:Study{name:$scopeStudyName})-[:STUDY_TO_ORG]->(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) and task.category in $lists.scopeCategories and task.state in $lists.scopeStates with task match (task)-[:WITH_CONTEXT]->() where task.state <> ? optional match (task)-[:BELONGS_TO]-(SInbox:Inbox{type:?})-[:MANAGED_BY]-(UserStudy:Study)-[:STUDY_TO_ORG]-(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) with task, SInbox, UserStudy where (SInbox IS NULL) OR (SInbox IS NOT NULL AND exists((:User{email:$scopeUserEmail})-[:FOR_USER]-(:Actor)-[:STUDY_TO_ACTOR]-(UserStudy))) with 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 order by t.category limit $pageLimit
tusker
3
402 ms
1.85 s
1.07 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,lead-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)…
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,lead-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 WITH * WHERE o.orgType = ? MATCH (o)-[:ORGANIZATION_TO_LEAD]->(l:Lead {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=Study policies=study-lead-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_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 ) MATCH (st)<-[:LEAD_TO_STUDY]-(l:Lead {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-lead-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_2 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) MATCH (st)<-[:LEAD_TO_STUDY]-(l:Lead {tildaid:c.id}) 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
patientsvc
5
271 ms
1.75 s
1.17 s
flat
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActi…
MATCH (t:Training{tildaid:$trainingId, scopeId:$studyId, delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{deltimestamp:?})-[:TRAINING_ASSIGNMENT_TO_USER]->(u:User) MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE pa.status IN $completedStatuses AND NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END AND ta.trainingId = $trainingId AND ta.userId IN $userIds AND (NOT ((coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType)) OR (t.orgId <> ? AND t.orgId = $callerOrgId)) CALL (u) { OPTIONAL MATCH (u)<-[:FOR_USER]-(actor:Actor{orgId:$orgId, scopeId:$orgId, userId:u.tildaid, deltimestamp:?, state:$activeActorState, delpending:false})-[:ORGANIZATION_TO_ACTOR]-(:Organization{tildaid:$orgId}) WHERE coalesce(u.status, ?) <> $deletedUserStatus AND coalesce(u.email, ?) <> $supportUserEmail AND EXISTS { MATCH (actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole AND ($anyMember OR size($roles) = ? OR r.name IN $roles) } AND ($anyMember OR ((size($roles) > ? OR $labelsNarrow) AND (NOT $labelsNarrow OR EXISTS { MATCH (actor)-[:ACTOR_TO_MEMBER_LABEL]->(l:MemberLabel{delpending:false})<-[:TRAINING_TO_MEMBER_LABEL]-(:Training{tildaid:$labelTrainingId}) }))) RETURN [] AS sites, reduce(acc = [], rs IN collect([(actor)-[:APPOINTED_AS]->(r:Role) WHERE coalesce(r.external, false) = false AND r.name <> $supportRole | r.name]) | acc + rs) AS siteRoles } CALL (pa) { OPTIONAL MATCH (pa)-[:BELONGS_TO_ACTIVITY]-(tpAct:ActivityV2) OPTIONAL MATCH (pa)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH pa, tpAct, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH pa, tpAct, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH pa, tpAct, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (pa.status = ? OR coalesce(tpAct.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(tpAct.passingScore, ?) * tpTotalMax) AS passed, tpScores AS scoreValues, tpMaxScores AS maxScoreValues, tpQuizzes AS quizzes } CALL (pa, passed) { OPTIONAL MATCH (pa)-[:PARTICIPANT_DOCUMENTSET]-(pds:ParticipantDocumentSet)-[:BELONGS_TO_DOCUMENTSET]-(ds:DocumentSetV2) WHERE passed AND ds.tag = $finalResultTag OPTIONAL MATCH (pds)-[:PARTICIPANT_DOC_OVERLAY]-(:ParticipantDocOverlay)-[:BELONGS_TO]-(certDoc:Document{archived:false}) WITH pa, passed, certDoc ORDER BY certDoc.createdat DESC LIMIT ? OPTIONAL MATCH (pa)-[:TRAINING_CERTIFICATE]->(up:Document{archived:false}) RETURN CASE WHEN NOT passed THEN null WHEN pa.status = ? THEN up.tildaid ELSE certDoc.tildaid END AS certDocId LIMIT ? } OPTIONAL MATCH (pa)-[:TAIL_CHANGELOG]->(tailCl:ChangeLog{type:?, newValue:?}) CALL (pa) { OPTIONAL MATCH (act:ActivityV2{tildaid: pa.activityId}) RETURN act.version AS activityVersion LIMIT ? } RETURN u.tildaid, u.name, u.email, pa.tildaid, pa.status, pa.createdat, pa.dueDate, [(pa)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?, ?] | CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END], null AS col8, null AS col9, null AS col10, null AS col11, null AS col12, ta.tildaid, ta.source, ta.delivery, sites, passed, certDocId, tailCl.reason, tailCl.who, [(pa)-[:ALL_CHANGELOG]->(acl:ChangeLog) WHERE acl.type = ? AND acl.newValue IN [?, ?] | [acl.createdat, acl.who]], siteRoles, pa.activityId, activityVersion, ta.state, scoreValues, maxScoreValues, quizzes ORDER BY u.name, u.tildaid, pa.createdat DESC
execsvc
6
215 ms
1.70 s
1.28 s
flat
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(a…
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) WITH ph, e, es, del, acceptedDoc, coalesce(acceptedDoc, ph) AS d OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (siteFromEvent: Site{tildaid: es.scopeId}) WHERE es.scopeType = ? OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) where da.key in [?, ?, ?, ?] OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: User) OPTIONAL MATCH (study) - [:STUDY_TO_ACTOR] - (actor: Actor{userId: uploader.tildaid}) WITH d, e, es, del, b, study, b2, coalesce(site, siteFromEvent) AS site, o, head(collect(da)) as da, currentFolder, currentSubArtifact, stagingInfo, artifact, section, zone, collection, subArtifact, uploader, ph, acceptedDoc, head(collect(actor)) as actor CALL apoc.create.vNode([?], { tildaid: ph.tildaid, createdat: ph.createdat, updatedat: ph.updatedat, documentId: acceptedDoc.tildaid, documentCreatedAt: acceptedDoc.createdat, documentUpdatedAt: acceptedDoc.updatedat, orgName: o.name, orgId: o.tildaid, orgAlias: o.alias, studyName: study.name, studyId: study.tildaid, siteName: site.name, siteId: site.tildaid, documentStatus: d.status, country: CASE WHEN es.scopeType = ? THEN coalesce(es.scopeId, d.country) ELSE d.country END, languages: coalesce(apoc.convert.toJson(d.languages), ?), fileName: d.fileName, uploadedByName: uploader.name, uploadedById: uploader.tildaid, collection: d.collection, stateTag: d.stateTag, documentExpiryDate: da.value, zone: zone.name, zoneId: zone.tildaid, zoneNumber: case when zone.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) end, section: section.name, sectionId: section.tildaid, sectionNumber: case when section.number is null then null else apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) end, artifact: artifact.name, artifactId: artifact.tildaid, artifactNumber: case when artifact.number is null then null else apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) end, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, eventId: e.tildaid, eventCreatedAt: e.createdat, eventType: e.type, eventName: e.name, eventStatus: e.status, eventScopeId: es.tildaid, eventScopeName: es.name, scopeType: es.scopeType, eventScopeDecision: es.decision, deliverableId: del.tildaid, deliverableName: del.name, deliverableLocationId: del.locationId, deliverableLocationType: del.locationType, deliverableType: del.type, roles: actor.roles }) YIELD node return node
writedashrsvc
8
611 ms
1.60 s
1.21 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
writedashrsvc
9
137 ms
1.55 s
1.16 s
flat
match (task:Task) where task.tildaid in $lists.taskIds with distinct task as t call apoc.do.case([ t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContex…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
3
530 ms
975 ms
578 ms
flat
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FRO…
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl)-[:PARTICIPANT_CONTROL]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw UNION ALL WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw } WITH DISTINCT study, t, ptc, pw MATCH (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(o:Organization) MATCH (ptc)-[:BELONGS_TO_TASKCONFIG]-(tc:TaskConfig) OPTIONAL MATCH (t)-[:BELONGS_TO]-(taskInbox:Inbox{type:?})--(taskUser:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:Actor{delpending:false})-[:FOR_USER]-(user:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(scopeSite:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(o) WITH DISTINCT study, t, o, tc, taskInbox, taskUser, user, scopeSite SKIP $skip LIMIT $limit WITH collect({study:study, t:t, o:o, tc:tc, taskInbox:taskInbox, taskUser:taskUser, user:user, scopeSite:scopeSite}) AS rows, collect(DISTINCT study) AS studies, collect(DISTINCT o) AS orgs, collect(DISTINCT scopeSite) AS sites CALL { WITH studies UNWIND studies AS study OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(r:Role) WHERE r.name IN [?, ?, ?] RETURN study.tildaid AS studyId, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS piTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS smoTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS raTildaid } WITH rows, orgs, sites, collect({studyId: studyId, pi: piTildaid, smo: smoTildaid, ra: raTildaid}) AS studyRoles CALL { WITH orgs UNWIND orgs AS o OPTIONAL MATCH (o)-[:ORGANIZATION_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN o.tildaid AS orgId, collect(DISTINCT u.tildaid) AS adminTildaid } WITH rows, sites, studyRoles, collect({orgId: orgId, admin: adminTildaid}) AS orgRoles CALL { WITH sites UNWIND (CASE WHEN size(sites) = ? THEN [null] ELSE sites END) AS scopeSite OPTIONAL MATCH (scopeSite)-[:STAFF_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN scopeSite.tildaid AS siteId, collect(DISTINCT u.tildaid) AS siteAdminTildaid } WITH rows, studyRoles, orgRoles, collect({siteId: siteId, admin: siteAdminTildaid}) AS siteRoles UNWIND rows AS row WITH row, [x IN studyRoles WHERE x.studyId = row.study.tildaid][?] AS sr, [x IN orgRoles WHERE x.orgId = row.o.tildaid][?] AS orr, [x IN siteRoles WHERE row.scopeSite IS NOT NULL AND x.siteId = row.scopeSite.tildaid][?] AS str RETURN DISTINCT row.t AS task, apoc.convert.toJson({userTildaid: row.user.tildaid, userName: row.user.name, PITildaid: sr.pi, studyOwnerTildaid: sr.smo, regAnalystTildaid: sr.ra, orgAdminTildaid: orr.admin, studyName: row.study.name, siteName: row.scopeSite.name, siteAdminTildaid: CASE WHEN row.scopeSite IS NULL THEN [] ELSE str.admin END}) AS details, row.tc.who AS dsnWho, row.o.alias AS orgAlias, row.taskInbox, row.taskUser.tildaid AS taskUserId
tusker
1
545 ms
975 ms
545 ms
flat
MATCH (ssa:StudySiteActor {delpending:false}) WHERE ssa.location IS NULL OR ssa.location = ? SET ssa.location = ?
MATCH (ssa:StudySiteActor {delpending:false}) WHERE ssa.location IS NULL OR ssa.location = ? SET ssa.location = ?
writeexecsvc
1
996 ms
975 ms
996 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
845 ms
975 ms
845 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
1
597 ms
975 ms
597 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.event_id = $event_id OPTION…
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.event_id = $event_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, need_attention_reason: w.need_attention_reason, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
writevirtual-assistant
8
482 ms
967 ms
817 ms
flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE COALESCE(tracksRel.state, ?) in $userDecisions MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PA…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE COALESCE(tracksRel.state, ?) in $userDecisions MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVENT]->(e) WHERE es.tildaid in $scopeIds OPTIONAL MATCH (wo:WorkOrder)-[woClaim:WO_CLAIMS_PLACEHOLDER]->(ph) WHERE woClaim.is_current = true WITH e, ph, d, es, tracksRel, head(collect(wo)) AS wo OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND matchDoc.collection = ? THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as matchesExpectedCollected, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND (matchDoc.collection IS NULL OR matchDoc.collection <> ?) THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as pendingMatchesCollected WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, [m IN matchesExpectedCollected WHERE m IS NOT NULL] as matchesExpected, [m IN pendingMatchesCollected WHERE m IS NOT NULL] as pendingMatches WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchesExpected) > ? AND SIZE(pendingMatches) = ? THEN ? WHEN SIZE(matchesExpected) > ? OR SIZE(pendingMatches) > ? THEN ? ELSE ? END as matchStatus OPTIONAL MATCH (ph)-[renewalRel:RENEWAL_OF]->(renewalSrcCandidate:Document) WHERE coalesce(renewalRel.primary, false) = true AND coalesce(renewalSrcCandidate.archived, false) = false WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, head(collect(renewalSrcCandidate)) AS renewalSrc OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study) OPTIONAL MATCH (site:Site {tildaid: es.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, renewalSrc, COALESCE(head(collect(site.name)), ?) AS siteName, COALESCE(head(collect(pd.siteNumber)), ?) AS siteNumber ORDER BY d.name ASC, ph.fileName ASC WITH collect({ placeholderId: ph.tildaid, placeholderName: ph.fileName, deliverableId: d.tildaid, deliverableName: d.name, eventScopeId: es.tildaid, scopeType: es.scopeType, country: CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END, siteNumber: siteNumber, siteName: siteName, dueDate: es.dueDate, matchStatus: matchStatus, userDecision: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR es.status = ?, origin: COALESCE(tracksRel.origin, ?), feedback: COALESCE(tracksRel.feedback, ?), reason: COALESCE(tracksRel.reason, ?), removalHasAcceptedDocument: COALESCE(tracksRel.removalHasAcceptedDocument, false), workOrder: CASE WHEN wo IS NOT NULL THEN {tildaid: wo.tildaid, work_order_id: wo.work_order_id, status: wo.status} ELSE null END, renewalSource: CASE WHEN renewalSrc IS NOT NULL THEN {documentId: renewalSrc.tildaid, documentName: renewalSrc.fileName} ELSE null END, acceptedMatch: CASE WHEN acceptedDoc IS NOT NULL THEN { documentId: acceptedDoc.tildaid, displayId: acceptedDoc.displayId, contentType: acceptedDoc.contentType, documentName: acceptedDoc.fileName, documentDate: CASE WHEN acceptedDoc.createdat IS NOT NULL THEN datetime({epochMillis: acceptedDoc.createdat}) ELSE null END, isAccepted: true, matchedBy: COALESCE(head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | amr.matchedBy ]), ?), matchedAt: head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | CASE WHEN amr.createdat IS NOT NULL THEN datetime({epochMillis: amr.createdat}) ELSE null END ]), acceptedBy: acceptedRel.acceptedBy, acceptedAt: CASE WHEN acceptedRel.createdat IS NOT NULL THEN datetime({epochMillis: acceptedRel.createdat}) ELSE null END, docAttributes: [ (acceptedDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: acceptedDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } ELSE null END, matchesExpected: matchesExpected, pendingMatches: pendingMatches }) as allRows WITH allRows, [r IN allRows WHERE r.matchStatus = $rowMatchStatus] as filteredRows WITH allRows, filteredRows, { total: size(allRows), pending: size([pp IN allRows WHERE pp.userDecision IN [?, ?]]), pendingForDelete: size([pp IN allRows WHERE pp.userDecision = ?]), accepted: size([pp IN allRows WHERE pp.userDecision = ?]), rejected: size([pp IN allRows WHERE pp.userDecision = ?]), incomplete: size([pp IN allRows WHERE pp.matchStatus = ?]), potentialMatchFound: size([pp IN allRows WHERE pp.matchStatus = ?]), matchExpected: size([pp IN allRows WHERE pp.matchStatus = ?]), matched: size([pp IN allRows WHERE pp.matchStatus = ?]), finalized: size([pp IN allRows WHERE pp.matchStatus = ?]), completion: {required: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]), completed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]), aiSuggested: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), inStaging: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), noMatchFound: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), notConfirmed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND pp.userDecision = ? AND NOT pp.matchStatus = ?]), removed: size([pp IN allRows WHERE (pp.userDecision = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) / size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]))) END, isComplete: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) > ? AND size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) = size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)])}, matchedWithNew: size([pp IN allRows WHERE pp.matchStatus = ? AND (size(pp.pendingMatches) > ? OR size(pp.matchesExpected) > ?)]) } as progress RETURN filteredRows[$pageSkip..$pageEnd] as placeholders, size(filteredRows) as total, progress
docsvc
10
389 ms
958 ms
835 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.event_id = $event_id OPTION…
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.event_id = $event_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, need_attention_reason: w.need_attention_reason, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
writevirtual-assistant
2
394 ms
950 ms
779 ms
flat
MATCH (ctx:Study)<-[:PLAN_CONTEXT]-(ep:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) WHERE ctx.tildaid IN $contextIds WITH DISTINCT e OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(statusScope:EventScope) WITH e, statusScope WHERE (statusScope IS…
MATCH (ctx:Study)<-[:PLAN_CONTEXT]-(ep:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) WHERE ctx.tildaid IN $contextIds WITH DISTINCT e OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(statusScope:EventScope) WITH e, statusScope WHERE (statusScope IS NULL AND e.status = $status) OR statusScope.status in $scopeStatuses WITH DISTINCT e WHERE e.isTemplate = $isTemplate WITH DISTINCT e ORDER BY e.createdat DESC SKIP $skip LIMIT $limit OPTIONAL MATCH (e)<-[:EVENTS]-(ep:EventPlan)-[:PLAN_CONTEXT]->(study) WITH e, study CALL { WITH e, study // Get details for each scope in the event OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(scope:EventScope) WHERE (scope IS NULL AND e.status = $status) OR scope.status in $scopeStatuses OPTIONAL MATCH (site:Site {tildaid: scope.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition{type:?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH scope, CASE WHEN scope.scopeType = ? THEN site.name ELSE ? END as siteName, CASE WHEN scope.scopeType = ? THEN COALESCE(pd.siteNumber, ?) ELSE ? END as siteNumber OPTIONAL MATCH (scope)-[:APPLICABLE_TO]->(scopeDeliverable:Deliverable) CALL { // Get details of each deliverable applicable to the scope WITH scope, scopeDeliverable OPTIONAL MATCH (scopeDeliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document)-[:SCOPED_TO]->(scope) CALL { // Get match status for each placeholder WITH ph, tracksRel, scope OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH ph, tracksRel, scope, acceptedDoc, [m IN collect(matchDoc) WHERE m IS NOT NULL] as matchDocs WITH ph, tracksRel, scope, acceptedDoc, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchDocs) > ? AND all(m IN matchDocs WHERE m.collection = ?) THEN ? WHEN SIZE(matchDocs) > ? THEN ? ELSE ? END as matchStatus RETURN CASE WHEN ph IS NULL THEN null ELSE {tildaid: ph.tildaid, fileName: ph.fileName, status: matchStatus, placeholderState: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR scope.status = ?} END as deliverablePlaceholder } WITH collect(DISTINCT deliverablePlaceholder) as deliverablePlaceholders WITH [d IN deliverablePlaceholders WHERE d IS NOT NULL] as allPlaceholders RETURN allPlaceholders as deliverablePlaceholders, { total: size(allPlaceholders), pending: size([pp IN allPlaceholders WHERE pp.placeholderState IN [?, ?]]), pendingForDelete: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), accepted: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), rejected: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), incomplete: size([pp IN allPlaceholders WHERE pp.status = ?]), potentialMatchFound: size([pp IN allPlaceholders WHERE pp.status = ?]), matchExpected: size([pp IN allPlaceholders WHERE pp.status = ?]), matched: size([pp IN allPlaceholders WHERE pp.status = ?]), finalized: size([pp IN allPlaceholders WHERE pp.status = ?]), completion: {required: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]), completed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]), aiSuggested: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), inStaging: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), noMatchFound: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), notConfirmed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND pp.placeholderState = ? AND NOT pp.status = ?]), removed: size([pp IN allPlaceholders WHERE (pp.placeholderState = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) / size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]))) END, isComplete: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) > ? AND size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) = size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)])} } as deliverableStats } // Aggregate all deliverables in the scope WITH scope, siteName, siteNumber, deliverableStats WITH distinct scope, siteName, siteNumber, { total: sum(deliverableStats.total), pending: sum(deliverableStats.pending), pendingForDelete: sum(deliverableStats.pendingForDelete), accepted: sum(deliverableStats.accepted), rejected: sum(deliverableStats.rejected), incomplete: sum(deliverableStats.incomplete), potentialMatchFound: sum(deliverableStats.potentialMatchFound), matchExpected: sum(deliverableStats.matchExpected), matched: sum(deliverableStats.matched), finalized: sum(deliverableStats.finalized), completion: {required: sum(deliverableStats.completion.required), completed: sum(deliverableStats.completion.completed), aiSuggested: sum(deliverableStats.completion.aiSuggested), inStaging: sum(deliverableStats.completion.inStaging), noMatchFound: sum(deliverableStats.completion.noMatchFound), notConfirmed: sum(deliverableStats.completion.notConfirmed), removed: sum(deliverableStats.completion.removed), percentComplete: CASE WHEN sum(deliverableStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(deliverableStats.completion.completed) / sum(deliverableStats.completion.required))) END, isComplete: sum(deliverableStats.completion.required) > ? AND sum(deliverableStats.completion.completed) = sum(deliverableStats.completion.required)} } as scopeStats RETURN distinct scope{.*, siteName: siteName, siteNumber: siteNumber} as scope, scopeStats } CALL { // Collect summary for all deliverable in the event WITH e OPTIONAL MATCH (e)-[:REQUIRES_RESOLUTION_OF]->(eventDel:Deliverable) WITH collect(eventDel) as deliverables, [d IN collect(distinct eventDel) WHERE d.warning IS NOT NULL AND d.warning <> ? | ??\? + d.warning] as deliverableWarnings RETURN { trialDeliverables: SIZE([d in deliverables WHERE d.applyToTrial = true]), countryDeliverables: SIZE([d in deliverables WHERE d.applyToCountries = true]), siteDeliverables: SIZE([d in deliverables WHERE d.applyToSites = true]) } as deliverableCounts, deliverableWarnings } CALL { // Fetch createdBy WITH e OPTIONAL MATCH (e)-[:ALL_CHANGELOG]->(cl:ChangeLog{operation:?}) WITH cl ORDER BY cl.createdat ASC limit ? OPTIONAL MATCH (u:User{tildaid:cl.changedBy}) return {id: u.tildaid, name: u.name, profilePicURL: u.profilePicURL} as createdBy } // Aggregate all scopes and all deliverables in the event WITH e, study, createdBy, deliverableCounts, deliverableWarnings, collect(scope) as eventScopes, { total: sum(scopeStats.total), pending: sum(scopeStats.pending), pendingForDelete: sum(scopeStats.pendingForDelete), accepted: sum(scopeStats.accepted), rejected: sum(scopeStats.rejected), incomplete: sum(scopeStats.incomplete), potentialMatchFound: sum(scopeStats.potentialMatchFound), matchExpected: sum(scopeStats.matchExpected), matched: sum(scopeStats.matched), finalized: sum(scopeStats.finalized), completion: {required: sum(scopeStats.completion.required), completed: sum(scopeStats.completion.completed), aiSuggested: sum(scopeStats.completion.aiSuggested), inStaging: sum(scopeStats.completion.inStaging), noMatchFound: sum(scopeStats.completion.noMatchFound), notConfirmed: sum(scopeStats.completion.notConfirmed), removed: sum(scopeStats.completion.removed), percentComplete: CASE WHEN sum(scopeStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(scopeStats.completion.completed) / sum(scopeStats.completion.required))) END, isComplete: sum(scopeStats.completion.required) > ? AND sum(scopeStats.completion.completed) = sum(scopeStats.completion.required)} } as eventProgress WITH e, study, createdBy, deliverableCounts, deliverableWarnings, eventProgress, eventScopes, { totalCountries: SIZE([s IN eventScopes WHERE s.scopeType = ?]), totalSites: SIZE([s IN eventScopes WHERE s.scopeType = ?]), countries: [s IN eventScopes WHERE s.scopeType = ? | {name: s.scopeId, id: s.scopeId}], sites: [s IN eventScopes WHERE s.scopeType = ? | {name: s.siteName, id: s.scopeId, siteNumber: s.siteNumber}] } as scopeSummary CALL { WITH e OPTIONAL MATCH (woc:WorkOrderConfig)-[:WORK_ORDER_CONFIG_TO_EVENT]->(e) RETURN woc.tildaid as workOrderConfigId LIMIT ? } RETURN e{.*, createdBy: createdBy, warnings: deliverableWarnings, scopeSummary: scopeSummary, progress: eventProgress, workOrderConfigId: workOrderConfigId, contextId: COALESCE(study.tildaid, ?), contextType: CASE WHEN study IS NULL THEN ? ELSE head(labels(study)) END, contextName: COALESCE(study.name, ?), aiState: CASE WHEN SIZE(eventScopes) = ? THEN ? WHEN SIZE([s IN eventScopes WHERE s.aiState <> ? AND s.aiState IS NOT NULL]) = ? THEN ? ELSE ? END, trialDeliverables: deliverableCounts.trialDeliverables, countryDeliverables: deliverableCounts.countryDeliverables, siteDeliverables: deliverableCounts.siteDeliverables} as result
writedocsvc
2
347 ms
950 ms
693 ms
flat
match (a:Actor) where a.tildaid in $actorIds optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(sp:StudySiteActor{delpending:false})- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_SITE]-(s:Site) call { with …
match (a:Actor) where a.tildaid in $actorIds optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(sp:StudySiteActor{delpending:false})- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_SITE]-(s:Site) call { with a,s,pd,sp match (r:Role) where r.name in sp.roles optional match (a)-[primaryRole:IS_PRIMARY_ROLE]-(r) with collect(distinct {name:r.name,displayName:r.displayName,assignDuty:r.assignDuty, tag:r.tags, isPrimary: CASE WHEN primaryRole IS NOT NULL THEN true ELSE false END}) as roleList,s,pd,a,sp optional match (sp)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]- (studySiteDuty:StudySiteDuty{removed:false,delpending:false})- [:DUTY_TO_STUDYSITEDUTY]-(duty:Duty) WITH a,s,pd,sp,duty,studySiteDuty,roleList ORDER BY duty.number with a,s,pd,sp,collect({duty: duty, studySiteDuty: studySiteDuty}) as duties,roleList with a,collect({duties:duties,siteId:s.tildaid,siteName:s.name,siteNumber:pd.siteNumber,country:s.country,roles:roleList,location:sp.location,ssaId:sp.tildaid}) as sites return collect({tildaid:a.tildaid, sites: sites}) as s1 } with s1,a call { with a with a optional match(a)-[:APPOINTED_AS]-(r:Role) optional match(a)-[primaryRole:IS_PRIMARY_ROLE]-(r) with collect(distinct {name:r.name,displayName:r.displayName,assignDuty:r.assignDuty,tag:r.tags,isPrimary: CASE WHEN primaryRole IS NOT NULL THEN true ELSE false END}) as roleList,a optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(siteActor:StudySiteActor{delpending:false})- [:STUDYSITEDUTY_TO_STUDYSITEACTOR]-(studySiteDuty:StudySiteDuty{removed:false,delpending:false})- [:DUTY_TO_STUDYSITEDUTY]-(duty:Duty) WITH a, duty, studySiteDuty, roleList ORDER BY duty.number with a,collect({duty: duty, studySiteDuty: studySiteDuty}) as duties,roleList with a,collect({duties:duties,roles:roleList}) as sites return collect({tildaid:a.tildaid, sites: sites}) as s2 } return case when size(s1)>? then s1 else s2 end
execsvc
2
562 ms
950 ms
634 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
382 ms
950 ms
760 ms
flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
6
462 ms
925 ms
806 ms
flat
match (task:Task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(…
match (task:Task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) match (task)-[:BELONGS_TO]->(SInbox:Inbox{type:?})-[:MANAGED_BY]->(Study:Study{name:$scopeStudyName})-[:STUDY_TO_ORG]->(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) and task.category in $scopeCategories and task.state in $scopeStates with task match (task)-[:WITH_CONTEXT]->() where task.state <> ? optional match (task)-[:BELONGS_TO]-(SInbox:Inbox{type:?})-[:MANAGED_BY]-(UserStudy:Study)-[:STUDY_TO_ORG]-(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) with task, SInbox, UserStudy where (SInbox IS NULL) OR (SInbox IS NOT NULL AND exists((:User{email:$scopeUserEmail})-[:FOR_USER]-(:Actor)-[:STUDY_TO_ACTOR]-(UserStudy))) with distinct task return count(task)
tusker
3
344 ms
925 ms
965 ms
flat
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(a…
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) WITH ph, e, es, del, acceptedDoc, coalesce(acceptedDoc, ph) AS d OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (siteFromEvent: Site{tildaid: es.scopeId}) WHERE es.scopeType = ? OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) where da.key in [?, ?, ?, ?] OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: User) OPTIONAL MATCH (study) - [:STUDY_TO_ACTOR] - (actor: Actor{userId: uploader.tildaid}) WITH d, e, es, del, b, study, b2, coalesce(site, siteFromEvent) AS site, o, head(collect(da)) as da, currentFolder, currentSubArtifact, stagingInfo, artifact, section, zone, collection, subArtifact, uploader, ph, acceptedDoc, head(collect(actor)) as actor CALL apoc.create.vNode([?], { tildaid: ph.tildaid, createdat: ph.createdat, updatedat: ph.updatedat, documentId: acceptedDoc.tildaid, documentCreatedAt: acceptedDoc.createdat, documentUpdatedAt: acceptedDoc.updatedat, orgName: o.name, orgId: o.tildaid, orgAlias: o.alias, studyName: study.name, studyId: study.tildaid, siteName: site.name, siteId: site.tildaid, documentStatus: d.status, country: CASE WHEN es.scopeType = ? THEN coalesce(es.scopeId, d.country) ELSE d.country END, languages: coalesce(apoc.convert.toJson(d.languages), ?), fileName: d.fileName, uploadedByName: uploader.name, uploadedById: uploader.tildaid, collection: d.collection, stateTag: d.stateTag, documentExpiryDate: da.value, zone: zone.name, zoneId: zone.tildaid, zoneNumber: case when zone.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) end, section: section.name, sectionId: section.tildaid, sectionNumber: case when section.number is null then null else apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) end, artifact: artifact.name, artifactId: artifact.tildaid, artifactNumber: case when artifact.number is null then null else apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) end, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, eventId: e.tildaid, eventCreatedAt: e.createdat, eventType: e.type, eventName: e.name, eventStatus: e.status, eventScopeId: es.tildaid, eventScopeName: es.name, scopeType: es.scopeType, eventScopeDecision: es.decision, deliverableId: del.tildaid, deliverableName: del.name, deliverableLocationId: del.locationId, deliverableLocationType: del.locationType, deliverableType: del.type, roles: actor.roles }) YIELD node return node
writedashrsvc
6
379 ms
925 ms
895 ms
flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE ph.tildaid = $placeholderId OPTIONAL MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVEN…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE ph.tildaid = $placeholderId OPTIONAL MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVENT]->(e) OPTIONAL MATCH (wo:WorkOrder)-[woClaim:WO_CLAIMS_PLACEHOLDER]->(ph) WHERE woClaim.is_current = true WITH e, ph, d, es, tracksRel, head(collect(wo)) AS wo OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND matchDoc.collection = ? THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as matchesExpectedCollected, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND (matchDoc.collection IS NULL OR matchDoc.collection <> ?) THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as pendingMatchesCollected WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, [m IN matchesExpectedCollected WHERE m IS NOT NULL] as matchesExpected, [m IN pendingMatchesCollected WHERE m IS NOT NULL] as pendingMatches WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchesExpected) > ? AND SIZE(pendingMatches) = ? THEN ? WHEN SIZE(matchesExpected) > ? OR SIZE(pendingMatches) > ? THEN ? ELSE ? END as matchStatus OPTIONAL MATCH (ph)-[renewalRel:RENEWAL_OF]->(renewalSrcCandidate:Document) WHERE coalesce(renewalRel.primary, false) = true AND coalesce(renewalSrcCandidate.archived, false) = false WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, head(collect(renewalSrcCandidate)) AS renewalSrc OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study) OPTIONAL MATCH (site:Site {tildaid: es.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, renewalSrc, COALESCE(head(collect(site.name)), ?) AS siteName, COALESCE(head(collect(pd.siteNumber)), ?) AS siteNumber ORDER BY d.name ASC, ph.fileName ASC WITH collect({ placeholderId: ph.tildaid, placeholderName: ph.fileName, deliverableId: d.tildaid, deliverableName: d.name, eventScopeId: es.tildaid, scopeType: es.scopeType, country: CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END, siteNumber: siteNumber, siteName: siteName, dueDate: es.dueDate, matchStatus: matchStatus, userDecision: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR es.status = ?, origin: COALESCE(tracksRel.origin, ?), feedback: COALESCE(tracksRel.feedback, ?), reason: COALESCE(tracksRel.reason, ?), removalHasAcceptedDocument: COALESCE(tracksRel.removalHasAcceptedDocument, false), workOrder: CASE WHEN wo IS NOT NULL THEN {tildaid: wo.tildaid, work_order_id: wo.work_order_id, status: wo.status} ELSE null END, renewalSource: CASE WHEN renewalSrc IS NOT NULL THEN {documentId: renewalSrc.tildaid, documentName: renewalSrc.fileName} ELSE null END, acceptedMatch: CASE WHEN acceptedDoc IS NOT NULL THEN { documentId: acceptedDoc.tildaid, displayId: acceptedDoc.displayId, contentType: acceptedDoc.contentType, documentName: acceptedDoc.fileName, documentDate: CASE WHEN acceptedDoc.createdat IS NOT NULL THEN datetime({epochMillis: acceptedDoc.createdat}) ELSE null END, isAccepted: true, matchedBy: COALESCE(head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | amr.matchedBy ]), ?), matchedAt: head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | CASE WHEN amr.createdat IS NOT NULL THEN datetime({epochMillis: amr.createdat}) ELSE null END ]), acceptedBy: acceptedRel.acceptedBy, acceptedAt: CASE WHEN acceptedRel.createdat IS NOT NULL THEN datetime({epochMillis: acceptedRel.createdat}) ELSE null END, docAttributes: [ (acceptedDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: acceptedDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } ELSE null END, matchesExpected: matchesExpected, pendingMatches: pendingMatches }) as placeholders RETURN placeholders
docsvc
4
149 ms
900 ms
592 ms
flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
14
395 ms
883 ms
620 ms
flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
5
425 ms
875 ms
701 ms
flat
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(a…
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) WITH ph, e, es, del, acceptedDoc, coalesce(acceptedDoc, ph) AS d OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (siteFromEvent: Site{tildaid: es.scopeId}) WHERE es.scopeType = ? OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) where da.key in [?, ?, ?, ?] OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: User) OPTIONAL MATCH (study) - [:STUDY_TO_ACTOR] - (actor: Actor{userId: uploader.tildaid}) WITH d, e, es, del, b, study, b2, coalesce(site, siteFromEvent) AS site, o, head(collect(da)) as da, currentFolder, currentSubArtifact, stagingInfo, artifact, section, zone, collection, subArtifact, uploader, ph, acceptedDoc, head(collect(actor)) as actor CALL apoc.create.vNode([?], { tildaid: ph.tildaid, createdat: ph.createdat, updatedat: ph.updatedat, documentId: acceptedDoc.tildaid, documentCreatedAt: acceptedDoc.createdat, documentUpdatedAt: acceptedDoc.updatedat, orgName: o.name, orgId: o.tildaid, orgAlias: o.alias, studyName: study.name, studyId: study.tildaid, siteName: site.name, siteId: site.tildaid, documentStatus: d.status, country: CASE WHEN es.scopeType = ? THEN coalesce(es.scopeId, d.country) ELSE d.country END, languages: coalesce(apoc.convert.toJson(d.languages), ?), fileName: d.fileName, uploadedByName: uploader.name, uploadedById: uploader.tildaid, collection: d.collection, stateTag: d.stateTag, documentExpiryDate: da.value, zone: zone.name, zoneId: zone.tildaid, zoneNumber: case when zone.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) end, section: section.name, sectionId: section.tildaid, sectionNumber: case when section.number is null then null else apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) end, artifact: artifact.name, artifactId: artifact.tildaid, artifactNumber: case when artifact.number is null then null else apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) end, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, eventId: e.tildaid, eventCreatedAt: e.createdat, eventType: e.type, eventName: e.name, eventStatus: e.status, eventScopeId: es.tildaid, eventScopeName: es.name, scopeType: es.scopeType, eventScopeDecision: es.decision, deliverableId: del.tildaid, deliverableName: del.name, deliverableLocationId: del.locationId, deliverableLocationType: del.locationType, deliverableType: del.type, roles: actor.roles }) YIELD node return node
writedashrsvc
96
66 ms
867 ms
1.02 s
flat
MATCH (root:GraphNode{tildaid:$graphId}) OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2) WITH root, collect(DISTINCT var) as graphVars MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNo…
MATCH (root:GraphNode{tildaid:$graphId}) OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2) WITH root, collect(DISTINCT var) as graphVars MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNode) WHERE size(rr) = ? OR rr[-?].path[?] = root.tildaid OPTIONAL MATCH (gnode)-[:GRAPH_NODE_TO_PAGE{graphid:root.tildaid}]->(page:GraphPage) OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{graphid:$relPropId}]->(refNode:GraphNode) WHERE gnode.objtype = ? OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_QUESTION{graphid:$relPropId}]->(question:QuestionV2) WHERE refNode.objtype = ? WITH root, gnode, page, refNode, case when refNode.objtype = ? then question when refNode.objtype = ? then refNode else null end as question, rr, graphVars OPTIONAL MATCH (question)-[:QUESTION_TO_QUESTION_SPEC]->(questionSpec:QuestionSpec) OPTIONAL MATCH (gnode)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]- (nodeBranchCfg:BranchConfig)-[:BRANCH_TO_BRANCH_CONFIG{graphid:$relPropId}]- (nodeBranch:BranchV2) OPTIONAL MATCH (gnode)-[cfgRel:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]- (:BranchConfig)-[:ACTIVATION_RULE_TO_BRANCH_CONFIG{graphid:cfgRel.graphid}]- (:ActivationRule)-[:ACTIVATION_RULE_TO_CHAIN{graphid:cfgRel.graphid}]- (nodeChain:ActivationRuleChain) OPTIONAL MATCH (gnode)-[:BRANCH_TO_BRANCH_CONFIG{graphid:root.tildaid, graphid:$relPropId}]->(cfg:BranchConfig) WHERE gnode.objtype = ? OPTIONAL MATCH (cfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]->(cfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[rel:QUESTION_TO_AUTO_COMPUTE_CFG|QUESTION_TO_CORRECTNESS_CHECK{graphid:$relPropId}]->(acc:AutoComputeConfig) OPTIONAL MATCH (acc)-[:AUTO_COMPUTE_TO_BRANCH_CONFIG]->(acBranchCfg:BranchConfig) OPTIONAL MATCH (acBranchCfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]->(acCfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[aqRel:QUESTION_TO_AUTO_QUERY{graphid:$relPropId}]->(aqcfg:AutoQueryConfig{enabled:true}) WHERE aqRel.sourceprotocolid IS NULL OPTIONAL MATCH (aqcfg)-[:AUTO_QUERY_TO_BRANCH_CONFIG]->(aqBranchCfg:BranchConfig) WITH gnode, page, refNode, cfg, rr, questionSpec, question, CASE WHEN size(rr) > ? THEN rr[-?].path ELSE [root.tildaid] END as nodePath, CASE WHEN size(rr) > ? THEN rr[-?].flags ELSE ? END as nodeFlags, CASE WHEN size(rr) > ? THEN rr[-?].who ELSE [] END as nodeWho, CASE WHEN type(rel) = ? THEN true ELSE false END as isCorrectnessCheckConfig, collect(DISTINCT cfgRef.tildaid) as cfgRefIds, collect(DISTINCT nodeBranch.tildaid) as branchIds, collect(DISTINCT nodeChain.tildaid) as activationChainIds, acc, acBranchCfg, collect(DISTINCT acCfgRef.tildaid) as autoComputeBranchRefIds, graphVars, collect(DISTINCT CASE WHEN aqcfg IS NOT NULL THEN {aqcfg: aqcfg, aqBranchCfg: aqBranchCfg} END) as autoQueryConfigs WITH gnode as node, page, refNode, cfg as branchCfg, cfgRefIds as branchRefIds, questionSpec, question, branchIds, activationChainIds, nodePath as path, gnode.objtype as objType, nodeFlags as flags, nodeWho as who, acc as autoComputeConfig, acBranchCfg as autoComputeBranchCfg, autoComputeBranchRefIds, autoQueryConfigs, isCorrectnessCheckConfig, graphVars RETURN {node:node, page:page, refNode:refNode, questionSpec:questionSpec, question:question, branchCfg:branchCfg, branchRefIds:branchRefIds, path:path, objType:objType, flags:flags, who:who, branchIds:branchIds, activationChainIds:activationChainIds, autoComputeConfig:autoComputeConfig, autoComputeBranchCfg:autoComputeBranchCfg, autoComputeBranchRefIds:autoComputeBranchRefIds, autoQueryConfigs:autoQueryConfigs, isCorrectnessCheckConfig:isCorrectnessCheckConfig, graphVars:graphVars}
execsvc
6
122 ms
850 ms
715 ms
flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
7
384 ms
825 ms
536 ms
flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
8
392 ms
800 ms
701 ms
flat
MATCH (doc:Document)-[:FILED_UNDER_SUBARTIFACT]->(:SubArtifact {tildaid: $subartifact_id}) WHERE doc.studyId = $study_id AND doc.status <> ? AND NOT EXISTS((:Deliverable)-[:TRACKS_PLACEHOLDER]->(doc)) AND doc.archived = false AND NOT EXIST…
MATCH (doc:Document)-[:FILED_UNDER_SUBARTIFACT]->(:SubArtifact {tildaid: $subartifact_id}) WHERE doc.studyId = $study_id AND doc.status <> ? AND NOT EXISTS((:Deliverable)-[:TRACKS_PLACEHOLDER]->(doc)) AND doc.archived = false AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:ACCEPTED_DOCUMENT]->(doc)) AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:MATCHING_DOCUMENT]->(doc)) AND (doc.tmfLevel = ? AND doc.country = $scope_id) RETURN DISTINCT doc.tildaid AS documentId, doc.fileName AS documentName, doc.tmfLevel AS tmfLevel, doc.siteId AS siteId, doc.country AS country, doc.createdat AS createdat, coalesce(doc.identity, ?) AS identity UNION MATCH (doc:Document)-[:HAS_STAGING_INFO]->(sti:DocumentStagingInfo) WHERE sti.subArtifactId = $subartifact_id AND doc.studyId = $study_id AND doc.status <> ? AND NOT EXISTS((:Deliverable)-[:TRACKS_PLACEHOLDER]->(doc)) AND doc.archived = false AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:ACCEPTED_DOCUMENT]->(doc)) AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:MATCHING_DOCUMENT]->(doc)) AND (doc.tmfLevel = ? AND doc.country = $scope_id) RETURN DISTINCT doc.tildaid AS documentId, doc.fileName AS documentName, doc.tmfLevel AS tmfLevel, doc.siteId AS siteId, doc.country AS country, doc.createdat AS createdat, coalesce(doc.identity, ?) AS identity
virtual-assistant
8
100 ms
800 ms
713 ms
flat
MATCH (d:Document) -[:DOCUMENT_VERSION] - (dv: DocumentVersion {tildaid: $id}) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (d…
MATCH (d:Document) -[:DOCUMENT_VERSION] - (dv: DocumentVersion {tildaid: $id}) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: 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, ?)}) CALL { WITH dv OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da: DocAttribute) WITH collect([coalesce(da.key, ?), coalesce(da.value, ?)]) AS attributePairs WITH apoc.map.fromPairs(attributePairs) AS attributesMap WITH apoc.convert.toJson(attributesMap) AS attributesJson RETURN attributesJson } WITH d, dv, o, study, site, zone, section, artifact, subArtifact, uploader, attributesJson CALL apoc.create.vNode([?], { documentId: d.tildaid, orgId: o.tildaid, orgName: o.name, orgAlias: o.alias, studyId: study.tildaid, studyName: study.name, siteId: COALESCE(site.tildaid, ?), siteName: COALESCE(site.name, ?), zone: zone.name, zoneId: zone.tildaid, section: section.name, sectionId: section.tildaid, artifact: artifact.name, artifactId: artifact.tildaid, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, documentMetadata: attributesJson, uploadedByName: uploader.name, uploadedById: uploader.tildaid, createdat: d.createdat, updatedat: dv.updatedat, artifactNumber: apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?), subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, tmfLevel: d.tmfLevel, country: d.country, language: d.language, fileName: d.fileName } ) YIELD node RETURN node
writedashrsvc
8
110 ms
800 ms
773 ms
flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId}) CALL { // Collect summary for each deliverable in the event WITH e OPTIONAL MATCH (e)-[:REQUIRES_RESOLUTION_OF]->(eventDel:Deliverable) RETURN CASE WHEN eventDel IS NOT NULL THEN eventDel{…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId}) CALL { // Collect summary for each deliverable in the event WITH e OPTIONAL MATCH (e)-[:REQUIRES_RESOLUTION_OF]->(eventDel:Deliverable) RETURN CASE WHEN eventDel IS NOT NULL THEN eventDel{.*} ELSE null END as eventDeliverable } WITH e, collect(distinct eventDeliverable) as deliverables, [d IN collect(distinct eventDeliverable) WHERE d.warning IS NOT NULL AND d.warning <> ? | ??\? + d.warning] as deliverableWarnings OPTIONAL MATCH (e)<-[:EVENTS]-(ep:EventPlan)-[:PLAN_CONTEXT]->(study) WITH e, deliverables, deliverableWarnings, study CALL { // Get details for each scope in the event WITH e, study OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(scope:EventScope) OPTIONAL MATCH (site:Site {tildaid: scope.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition{type:?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, scope, CASE WHEN scope.scopeType = ? THEN site.name ELSE ? END as siteName, CASE WHEN scope.scopeType = ? THEN COALESCE(pd.siteNumber, ?) ELSE ? END as siteNumber OPTIONAL MATCH (scope)-[:APPLICABLE_TO]->(scopeDeliverable:Deliverable) CALL { // Get details of each deliverable applicable to the scope WITH scope, scopeDeliverable OPTIONAL MATCH (scopeDeliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document)-[:SCOPED_TO]->(scope) CALL { // Get match status for each placeholder WITH ph, tracksRel, scope OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH ph, tracksRel, scope, acceptedDoc, [m IN collect(matchDoc) WHERE m IS NOT NULL] as matchDocs WITH ph, tracksRel, scope, acceptedDoc, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchDocs) > ? AND all(m IN matchDocs WHERE m.collection = ?) THEN ? WHEN SIZE(matchDocs) > ? THEN ? ELSE ? END as matchStatus RETURN CASE WHEN ph IS NULL THEN null ELSE {tildaid: ph.tildaid, fileName: ph.fileName, status: matchStatus, placeholderState: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR scope.status = ?} END as deliverablePlaceholder } WITH collect(DISTINCT deliverablePlaceholder) as deliverablePlaceholders WITH [d IN deliverablePlaceholders WHERE d IS NOT NULL] as allPlaceholders RETURN allPlaceholders as deliverablePlaceholders, { total: size(allPlaceholders), pending: size([pp IN allPlaceholders WHERE pp.placeholderState IN [?, ?]]), pendingForDelete: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), accepted: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), rejected: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), incomplete: size([pp IN allPlaceholders WHERE pp.status = ?]), potentialMatchFound: size([pp IN allPlaceholders WHERE pp.status = ?]), matchExpected: size([pp IN allPlaceholders WHERE pp.status = ?]), matched: size([pp IN allPlaceholders WHERE pp.status = ?]), finalized: size([pp IN allPlaceholders WHERE pp.status = ?]), completion: {required: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]), completed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]), aiSuggested: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), inStaging: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), noMatchFound: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), notConfirmed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND pp.placeholderState = ? AND NOT pp.status = ?]), removed: size([pp IN allPlaceholders WHERE (pp.placeholderState = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) / size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]))) END, isComplete: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) > ? AND size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) = size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)])} } as deliverableStats } // Aggregate all deliverables in the scope WITH e, scope, siteName, siteNumber, deliverableStats, CASE WHEN scopeDeliverable IS NOT NULL THEN scopeDeliverable{.*, trackedDocuments: [doc IN deliverablePlaceholders WHERE doc.tildaid IS NOT NULL], progress: deliverableStats } ELSE null END as deliverableDetails WITH distinct e, scope, siteName, siteNumber, collect(distinct deliverableDetails) as deliverablesInScope, { total: sum(deliverableStats.total), pending: sum(deliverableStats.pending), pendingForDelete: sum(deliverableStats.pendingForDelete), accepted: sum(deliverableStats.accepted), rejected: sum(deliverableStats.rejected), incomplete: sum(deliverableStats.incomplete), potentialMatchFound: sum(deliverableStats.potentialMatchFound), matchExpected: sum(deliverableStats.matchExpected), matched: sum(deliverableStats.matched), finalized: sum(deliverableStats.finalized), completion: {required: sum(deliverableStats.completion.required), completed: sum(deliverableStats.completion.completed), aiSuggested: sum(deliverableStats.completion.aiSuggested), inStaging: sum(deliverableStats.completion.inStaging), noMatchFound: sum(deliverableStats.completion.noMatchFound), notConfirmed: sum(deliverableStats.completion.notConfirmed), removed: sum(deliverableStats.completion.removed), percentComplete: CASE WHEN sum(deliverableStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(deliverableStats.completion.completed) / sum(deliverableStats.completion.required))) END, isComplete: sum(deliverableStats.completion.required) > ? AND sum(deliverableStats.completion.completed) = sum(deliverableStats.completion.required)} } as scopeStats CALL { // Fetch rejected deliverables for this scope WITH scope OPTIONAL MATCH (scope)-[r:REJECTED_FROM]->(rejectedDel:Deliverable) RETURN CASE WHEN rejectedDel IS NOT NULL THEN { tildaid: rejectedDel.tildaid, name: rejectedDel.name, reason: r.reason, rejectedAt: r.createdat } ELSE null END as rejectedDeliverable } WITH distinct e, scope, siteName, siteNumber, deliverablesInScope, scopeStats, collect(distinct rejectedDeliverable) as rejectedDeliverables RETURN distinct scope{.*, deliverables: deliverablesInScope, progress: scopeStats, aiNotes: CASE WHEN scope.aiNotes IS NULL OR scope.aiNotes = ? THEN e.aiNotes ELSE scope.aiNotes END, decision: COALESCE(scope.decision, ?), rejectedDeliverables: [r IN rejectedDeliverables WHERE r IS NOT NULL], siteName: siteName, siteNumber: siteNumber} as scopeDetails, scopeStats } // Aggregate all scopes and all deliverables in the event WITH e, study, deliverables, deliverableWarnings, collect(scopeDetails) as eventScopes, { total: sum(scopeStats.total), pending: sum(scopeStats.pending), pendingForDelete: sum(scopeStats.pendingForDelete), accepted: sum(scopeStats.accepted), rejected: sum(scopeStats.rejected), incomplete: sum(scopeStats.incomplete), potentialMatchFound: sum(scopeStats.potentialMatchFound), matchExpected: sum(scopeStats.matchExpected), matched: sum(scopeStats.matched), finalized: sum(scopeStats.finalized), completion: {required: sum(scopeStats.completion.required), completed: sum(scopeStats.completion.completed), aiSuggested: sum(scopeStats.completion.aiSuggested), inStaging: sum(scopeStats.completion.inStaging), noMatchFound: sum(scopeStats.completion.noMatchFound), notConfirmed: sum(scopeStats.completion.notConfirmed), removed: sum(scopeStats.completion.removed), percentComplete: CASE WHEN sum(scopeStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(scopeStats.completion.completed) / sum(scopeStats.completion.required))) END, isComplete: sum(scopeStats.completion.required) > ? AND sum(scopeStats.completion.completed) = sum(scopeStats.completion.required)} } as eventProgress WITH e, study, deliverables, deliverableWarnings, eventScopes, eventProgress, { totalCountries: size([s IN eventScopes WHERE s.scopeType = ?]), totalSites: size([s IN eventScopes WHERE s.scopeType = ?]), countries: [s IN eventScopes WHERE s.scopeType = ? | {name: s.scopeId, id: s.scopeId}], sites: [s IN eventScopes WHERE s.scopeType = ? | {name: s.siteName, id: s.scopeId, siteNumber: s.siteNumber}] } as scopeSummary CALL { // Fetch all ChangeLogs for the event WITH e // Audit items load through GET /changelogs; the event carries its newest ? rows at most. OPTIONAL MATCH (e)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE coalesce(cl.groupRole, ?) <> ? WITH cl ORDER BY cl.createdat DESC LIMIT ? WITH cl ORDER BY cl.createdat ASC RETURN collect(cl{.*}) as changeLogs } CALL { WITH e OPTIONAL MATCH (woc:WorkOrderConfig)-[:WORK_ORDER_CONFIG_TO_EVENT]->(e) RETURN woc.tildaid as workOrderConfigId LIMIT ? } RETURN e{.*, warnings: deliverableWarnings, deliverables: deliverables, scopeSummary: scopeSummary, eventScopes: eventScopes, progress: eventProgress, changeLogs: changeLogs, workOrderConfigId: workOrderConfigId, contextId: COALESCE(study.tildaid, ?), contextType: CASE WHEN study IS NULL THEN ? ELSE head(labels(study)) END, contextName: COALESCE(study.name, ?), aiState: CASE WHEN SIZE(eventScopes) = ? THEN ? WHEN SIZE([s IN eventScopes WHERE s.aiState <> ? AND s.aiState IS NOT NULL]) = ? THEN ? ELSE ? END} as result
docsvc
9
109 ms
775 ms
577 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
9
78 ms
775 ms
616 ms
flat
MATCH (subRec:Recommendation {category: ?})-[:RECOMMENDATION_TO_DOCUMENT]->(doc:Document) WHERE subRec.data = $subartifact_name AND doc.studyId = $study_id AND doc.status <> ? AND NOT EXISTS((:Deliverable)-[:TRACKS_PLACEHOLDER]->(doc)) AND…
MATCH (subRec:Recommendation {category: ?})-[:RECOMMENDATION_TO_DOCUMENT]->(doc:Document) WHERE subRec.data = $subartifact_name AND doc.studyId = $study_id AND doc.status <> ? AND NOT EXISTS((:Deliverable)-[:TRACKS_PLACEHOLDER]->(doc)) AND doc.archived = false AND doc.collection = ? AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:ACCEPTED_DOCUMENT]->(doc)) AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:MATCHING_DOCUMENT]->(doc)) AND NOT EXISTS((doc)-[:FILED_UNDER_SUBARTIFACT]->(:SubArtifact)) OPTIONAL MATCH (doc)-[:HAS_STAGING_INFO]->(sti:DocumentStagingInfo) WITH doc, sti WHERE sti IS NULL OR sti.subArtifactId IS NULL OR sti.subArtifactId = ? WITH doc MATCH (doc)<-[:RECOMMENDATION_TO_DOCUMENT]-(allSubRec:Recommendation {category: ?}) WITH doc, allSubRec ORDER BY allSubRec.createdat DESC WITH doc, collect(allSubRec)[?] AS latestSubRec WHERE latestSubRec.data = $subartifact_name MATCH (doc)<-[:RECOMMENDATION_TO_DOCUMENT]-(allArtRec:Recommendation {category: ?}) WITH doc, allArtRec ORDER BY allArtRec.createdat DESC WITH doc, collect(allArtRec)[?] AS latestArtRec WHERE latestArtRec.data = $artifact_name OR latestArtRec.data ENDS WITH $artifact_name OPTIONAL MATCH (doc)<-[:RECOMMENDATION_TO_DOCUMENT]-(scopeRec:Recommendation {category: ?}) WHERE scopeRec.data_formatted IS NOT NULL WITH doc, scopeRec ORDER BY scopeRec.createdat DESC WITH doc, collect(scopeRec)[?] AS latestScopeRec WITH doc, CASE WHEN latestScopeRec IS NOT NULL AND latestScopeRec.data_formatted IS NOT NULL THEN apoc.convert.fromJsonMap(latestScopeRec.data_formatted) ELSE null END AS scopeData WHERE CASE WHEN scopeData IS NOT NULL THEN (($scope_type = ? AND scopeData.scope = ?) OR ($scope_type = ? AND scopeData.scope = ? AND scopeData.country = $scope_id) OR ($scope_type = ? AND scopeData.scope = ? AND scopeData.siteId = $scope_id)) ELSE true END RETURN DISTINCT doc.tildaid AS documentId, doc.fileName AS documentName, doc.tmfLevel AS tmfLevel, doc.siteId AS siteId, doc.country AS country, coalesce(doc.identity, ?) AS identity
virtual-assistant
26
105 ms
675 ms
811 ms
flat
MATCH (doc:Document)-[:FILED_UNDER_SUBARTIFACT]->(:SubArtifact {tildaid: $subartifact_id}) WHERE doc.studyId = $study_id AND doc.status <> ? AND NOT EXISTS((:Deliverable)-[:TRACKS_PLACEHOLDER]->(doc)) AND doc.archived = false AND NOT EXIST…
MATCH (doc:Document)-[:FILED_UNDER_SUBARTIFACT]->(:SubArtifact {tildaid: $subartifact_id}) WHERE doc.studyId = $study_id AND doc.status <> ? AND NOT EXISTS((:Deliverable)-[:TRACKS_PLACEHOLDER]->(doc)) AND doc.archived = false AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:ACCEPTED_DOCUMENT]->(doc)) AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:MATCHING_DOCUMENT]->(doc)) AND (doc.tmfLevel = ? AND doc.siteId = $scope_id) RETURN DISTINCT doc.tildaid AS documentId, doc.fileName AS documentName, doc.tmfLevel AS tmfLevel, doc.siteId AS siteId, doc.country AS country, doc.createdat AS createdat, coalesce(doc.identity, ?) AS identity UNION MATCH (doc:Document)-[:HAS_STAGING_INFO]->(sti:DocumentStagingInfo) WHERE sti.subArtifactId = $subartifact_id AND doc.studyId = $study_id AND doc.status <> ? AND NOT EXISTS((:Deliverable)-[:TRACKS_PLACEHOLDER]->(doc)) AND doc.archived = false AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:ACCEPTED_DOCUMENT]->(doc)) AND NOT EXISTS((:Document {tildaid: $placeholder_id})-[:MATCHING_DOCUMENT]->(doc)) AND (doc.tmfLevel = ? AND doc.siteId = $scope_id) RETURN DISTINCT doc.tildaid AS documentId, doc.fileName AS documentName, doc.tmfLevel AS tmfLevel, doc.siteId AS siteId, doc.country AS country, doc.createdat AS createdat, coalesce(doc.identity, ?) AS identity
virtual-assistant
18
39 ms
550 ms
661 ms
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 ) MATCH (wo:WorkOrder) WHERE wo.study_id = st.tildaid 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 ) MATCH (wo:WorkOrder) WHERE wo.study_id = st.tildaid 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
feedsvc
1
206 ms
485 ms
206 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) MATCH (u:User)-[:FOR_USER]-(sa:Actor{orgId:org.tildaid})-[:ST…
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) MATCH (u:User)-[:FOR_USER]-(sa:Actor{orgId:org.tildaid})-[:STUDY_TO_ACTOR]- (s:Study{tildaid:$studyId}) WHERE sa.state <> ? 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})-[orgStudy]-(s:Study{tildaid:$studyId})<-[:PLAN_DEFINITION_TO_STUDY] -(pd:DslPlanDefinition{delpending:false,type:?})-[:PLAN_DEFINITION_TO_SITE]->(si:Site) OPTIONAL MATCH (s)-[:STUDY_TO_ACTOR]->(pi:Actor)-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd), (pi)-[:FOR_USER]->(piu:User) WHERE pi.state <> ? AND piu.status <> ? AND ANY(role IN ssa.roles WHERE role = ?) WITH org, orgStudy, s, pd, si, collect(DISTINCT CASE WHEN piu IS NULL THEN NULL WHEN piu.name IS NOT NULL AND piu.name <> ? THEN piu.name ELSE ? + piu.email + ? END) AS piNames WHERE true AND (NOT orgStudy.inherited OR (orgStudy.inherited AND (org)<-[:SITE_TO_ORGANIZATION]-(si))) WITH CASE WHEN si IS NOT NULL THEN {Name: si.name, Id: si.tildaid, SiteNumber: pd.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) MATCH (p)<-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]->(s:Study{tildaid:$studyId})WHERE coalesce(p.status, ?) <> ? CALL { RETURN null AS patient } CALL { WITH p, cp WITH p, cp, CASE WHEN cp IS NOT NULL THEN {Name: cp.subjectid, Id: p.tildaid} END AS subject order by subject.Name RETURN 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
1
249 ms
485 ms
249 ms
flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
2
409 ms
485 ms
412 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) AND o.alias = $alias WITH supportedIds, o{.*, supported: o.tildaid IN supportedIds} AS org WITH supportedIds, collect(org) AS orgs WITH supportedIds, size(orgs) AS totalCount, orgs 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
1
330 ms
485 ms
330 ms
flat
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FRO…
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl)-[:PARTICIPANT_CONTROL]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw UNION ALL WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw } WITH DISTINCT study, t, ptc, pw MATCH (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(o:Organization) MATCH (ptc)-[:BELONGS_TO_TASKCONFIG]-(tc:TaskConfig) OPTIONAL MATCH (t)-[:BELONGS_TO]-(taskInbox:Inbox{type:?})--(taskUser:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:Actor{delpending:false})-[:FOR_USER]-(user:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(scopeSite:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(o) WITH DISTINCT study, t, o, tc, taskInbox, taskUser, user, scopeSite SKIP $skip LIMIT $limit WITH collect({study:study, t:t, o:o, tc:tc, taskInbox:taskInbox, taskUser:taskUser, user:user, scopeSite:scopeSite}) AS rows, collect(DISTINCT study) AS studies, collect(DISTINCT o) AS orgs, collect(DISTINCT scopeSite) AS sites CALL { WITH studies UNWIND studies AS study OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(r:Role) WHERE r.name IN [?, ?, ?] RETURN study.tildaid AS studyId, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS piTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS smoTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS raTildaid } WITH rows, orgs, sites, collect({studyId: studyId, pi: piTildaid, smo: smoTildaid, ra: raTildaid}) AS studyRoles CALL { WITH orgs UNWIND orgs AS o OPTIONAL MATCH (o)-[:ORGANIZATION_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN o.tildaid AS orgId, collect(DISTINCT u.tildaid) AS adminTildaid } WITH rows, sites, studyRoles, collect({orgId: orgId, admin: adminTildaid}) AS orgRoles CALL { WITH sites UNWIND (CASE WHEN size(sites) = ? THEN [null] ELSE sites END) AS scopeSite OPTIONAL MATCH (scopeSite)-[:STAFF_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN scopeSite.tildaid AS siteId, collect(DISTINCT u.tildaid) AS siteAdminTildaid } WITH rows, studyRoles, orgRoles, collect({siteId: siteId, admin: siteAdminTildaid}) AS siteRoles UNWIND rows AS row WITH row, [x IN studyRoles WHERE x.studyId = row.study.tildaid][?] AS sr, [x IN orgRoles WHERE x.orgId = row.o.tildaid][?] AS orr, [x IN siteRoles WHERE row.scopeSite IS NOT NULL AND x.siteId = row.scopeSite.tildaid][?] AS str RETURN DISTINCT row.t AS task, apoc.convert.toJson({userTildaid: row.user.tildaid, userName: row.user.name, PITildaid: sr.pi, studyOwnerTildaid: sr.smo, regAnalystTildaid: sr.ra, orgAdminTildaid: orr.admin, studyName: row.study.name, siteName: row.scopeSite.name, siteAdminTildaid: CASE WHEN row.scopeSite IS NULL THEN [] ELSE str.admin END}) AS details, row.tc.who AS dsnWho, row.o.alias AS orgAlias, row.taskInbox, row.taskUser.tildaid AS taskUserId
tusker
1
227 ms
485 ms
227 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
1
326 ms
485 ms
326 ms
flat
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. // Staged with a barrier, not one comma-joined MATCH: the da->r<-sa, da->sa // cycle made planning cost far more than executing the query. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o) WHERE st.srcorgid = o.tildaid AND da.state <> ? WITH DISTINCT da MATCH (da)-[:DELEGATED_FROM]->(sa:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE sa.state <> ? MATCH (da)-[:APPOINTED_AS]->(r:Role) WHERE (sa)-[:APPOINTED_AS]->(r) AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, name: s.name, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
onboardingsvc
1
201 ms
485 ms
201 ms
flat
MATCH (ssa:StudySiteActor {delpending:false}) WHERE ssa.deltimestamp IS NULL SET ssa.deltimestamp = ?
MATCH (ssa:StudySiteActor {delpending:false}) WHERE ssa.deltimestamp IS NULL SET ssa.deltimestamp = ?
writeexecsvc
1
206 ms
485 ms
206 ms
flat
MATCH (s:Study)-[r:STUDY_TO_ACTOR]->(a:Actor) WHERE r.applyToStudyRoleFlag = false MATCH (s)-[r2:STUDY_TO_ACTOR]->(a) WHERE id(r2) <> id(r) DELETE r
MATCH (s:Study)-[r:STUDY_TO_ACTOR]->(a:Actor) WHERE r.applyToStudyRoleFlag = false MATCH (s)-[r2:STUDY_TO_ACTOR]->(a) WHERE id(r2) <> id(r) DELETE r
writeexecsvc
1
441 ms
485 ms
441 ms
flat
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
docsvc
1
489 ms
485 ms
489 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.firstname) contains tolower($phiFinder1) ) or to…
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.firstname) contains tolower($phiFinder1) ) or tolower(phi.mobilephone) contains tolower($phiFinder2) 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
292 ms
485 ms
292 ms
flat
match(org:Organization{tildaid:$leadOrgId}) with org match(lead:Lead)-[]-(org) with org, lead match(patient:Patient)<-[:LEAD_TO_PATIENT]-(lead) where lead.tildaid=$leadId with org, patient, lead match(patient)-[:HAS_PROTECTED_DATA_IN]->(ph…
match(org:Organization{tildaid:$leadOrgId}) with org match(lead:Lead)-[]-(org) with org, lead match(patient:Patient)<-[:LEAD_TO_PATIENT]-(lead) where lead.tildaid=$leadId with org, patient, lead match(patient)-[:HAS_PROTECTED_DATA_IN]->(phi:Phi) with * optional match(lead)-[:LEAD_TO_ADVOCATE]-(adv:Advocate) with * match(patient)-[]-(mg:MedicalGroup) with * match(lead)-[:LEAD_TO_STUDY]->(study:Study{tildaid:lead.studyid}) with * optional match(lead)-[]-(task:Task) with * optional match(advUser:User{tildaid:adv.userid}) with * optional match(lead)-[]-(:Task)-[]-(:Inbox{type:?})-[]-(assigned:User) with * optional match(formV2:FormInstance)-[:SCREENER_TO_LEAD]->(lead) with * optional match(form:ParticipantForm)-[:PARTICIPANTFORM_TO_PATIENT]->(patient)call{with * optional match (lead)-[:LEAD_TO_PATIENT]->(patient)<-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]-(s:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(study) where lead.status=? return cp order by cp.createdat desc limit ?} return patient, lead, phi, mg, study, task, adv, advUser, assigned, formV2, form, case cp.status when ?, ? then cp end, org limit $leadLimit
patientsvc
1
339 ms
485 ms
339 ms
flat
UNWIND $taskIds AS tid MATCH (task:Task{tildaid:tid})- [:WITH_CONTEXT]->(pv:ParticipantVisit)<-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)- [:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)…
UNWIND $taskIds AS tid MATCH (task:Task{tildaid:tid})- [:WITH_CONTEXT]->(pv:ParticipantVisit)<-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)- [:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ACTOR]-> (a:Actor)-[:IS_PRIMARY_ROLE]->(r:Role{name:$role}) MATCH (a)-[:FOR_USER]->(user:User) WITH tid, collect(distinct user.email) as emails WHERE size(emails) = ? RETURN tid, emails[?]
tusker
1
330 ms
485 ms
330 ms
flat
match (o:Organization{alias:$org}) optional match (:User{email:$userEmail})<-[:FOR_USER]-(:Actor)<-[:STUDY_TO_ACTOR]-(us:Study) with o, collect(distinct us) as userStudies match (o)<-[:MANAGED_BY]-(:Inbox{type:?})<-[:BELONGS_TO]-(task:Task…
match (o:Organization{alias:$org}) optional match (:User{email:$userEmail})<-[:FOR_USER]-(:Actor)<-[:STUDY_TO_ACTOR]-(us:Study) with o, collect(distinct us) as userStudies match (o)<-[:MANAGED_BY]-(:Inbox{type:?})<-[:BELONGS_TO]-(task:Task{state:?})-[:BELONGS_TO]->(i:Inbox{type:?}) where exists { (task)-[:WITH_CONTEXT]->() } optional match (task)-[:BELONGS_TO]->(si:Inbox{type:?})-[:MANAGED_BY]->(st:Study)-[:STUDY_TO_ORG]->(o) with i, task, si, (st in userStudies) as isMember with i, task, count(si) as nInbox, sum(case when isMember then ? else ? end) as nMember where nInbox = ? or nMember > ? WITH i, count(distinct task) AS taskCount return { inbox: i, studyName: null, siteName: null, count: taskCount} AS result UNION ALL match (o:Organization{alias:$org}) optional match (:User{email:$userEmail})<-[:FOR_USER]-(:Actor)<-[:STUDY_TO_ACTOR]-(us:Study) with o, collect(distinct us) as userStudies match (:User{email:$userEmail})-[:FOR_USER]-(:Actor)-[:STAFF_TO_ACTOR]-(site:Site)<-[:MANAGED_BY]-(i:Inbox{type:?})<-[:BELONGS_TO]-(task:Task{state:?}) where exists { (task)-[:WITH_CONTEXT]->() } and exists { (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(o) } optional match (task)-[:BELONGS_TO]->(si:Inbox{type:?})-[:MANAGED_BY]->(st:Study)-[:STUDY_TO_ORG]->(o) with i, site, task, si, (st in userStudies) as isMember with i, site, task, count(si) as nInbox, sum(case when isMember then ? else ? end) as nMember where nInbox = ? or nMember > ? WITH i, site, count(distinct task) AS taskCount return { inbox: i, studyName: null, siteName: site.name, count: taskCount} AS result UNION ALL match (u:User{email:$userEmail})-[:FOR_USER]-(:Actor)-[:STUDY_TO_ACTOR]-(s:Study)-[:STUDY_TO_ORG]->(:Organization{alias:$org}) WITH DISTINCT s match (s)<-[:MANAGED_BY]-(i:Inbox{type:?})<-[:BELONGS_TO]-(task:Task{state:?}) where exists { (task)-[:WITH_CONTEXT]->() } WITH i, s, count(task) AS taskCount return { inbox: i, studyName: s.name, siteName: null, count: taskCount} as result
tusker
2
375 ms
485 ms
403 ms
flat
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(trim(phi.firstname)) contains tolower($phiSearch) or tolower(trim(phi.lastname)) contains tolower($phiSearch…
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) where (tolower(trim(phi.firstname)) contains tolower($phiSearch) or tolower(trim(phi.lastname)) contains tolower($phiSearch) or tolower(trim(phi.firstname))+?+tolower(trim(phi.lastname)) contains tolower($phiSearch) or tolower(trim(phi.email)) contains tolower($phiSearch) or tolower(trim(phi.mobilephone)) contains tolower($phiSearch) or tolower(trim(phi.homephone)) contains tolower($phiSearch) or toString(p.ufid) contains tolower($phiSearch)) 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
421 ms
485 ms
421 ms
flat
UNWIND $userIds AS uid MATCH (u:User{tildaid:uid}) MATCH (u)<-[:FOR_USER]-(actor:Actor{orgId:$orgId, scopeId:$orgId, userId:u.tildaid, deltimestamp:?, state:$activeActorState, delpending:false})-[:ORGANIZATION_TO_ACTOR]-(:Organization{tild…
UNWIND $userIds AS uid MATCH (u:User{tildaid:uid}) 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}) }))) AND ($viewerSiteOrgId = ? OR actor.orgId = $viewerSiteOrgId) WITH u, actor ORDER BY u.tildaid, actor.tildaid RETURN u.tildaid, u.name, u.email, 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 roles
execsvc
3
183 ms
477 ms
298 ms
flat
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FRO…
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl)-[:PARTICIPANT_CONTROL]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw UNION ALL WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw } WITH DISTINCT study, t, ptc, pw MATCH (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(o:Organization) MATCH (ptc)-[:BELONGS_TO_TASKCONFIG]-(tc:TaskConfig) OPTIONAL MATCH (t)-[:BELONGS_TO]-(taskInbox:Inbox{type:?})--(taskUser:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:Actor{delpending:false})-[:FOR_USER]-(user:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(scopeSite:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(o) WITH DISTINCT study, t, o, tc, taskInbox, taskUser, user, scopeSite SKIP $skip LIMIT $limit WITH collect({study:study, t:t, o:o, tc:tc, taskInbox:taskInbox, taskUser:taskUser, user:user, scopeSite:scopeSite}) AS rows, collect(DISTINCT study) AS studies, collect(DISTINCT o) AS orgs, collect(DISTINCT scopeSite) AS sites CALL { WITH studies UNWIND studies AS study OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(r:Role) WHERE r.name IN [?, ?, ?] RETURN study.tildaid AS studyId, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS piTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS smoTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS raTildaid } WITH rows, orgs, sites, collect({studyId: studyId, pi: piTildaid, smo: smoTildaid, ra: raTildaid}) AS studyRoles CALL { WITH orgs UNWIND orgs AS o OPTIONAL MATCH (o)-[:ORGANIZATION_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN o.tildaid AS orgId, collect(DISTINCT u.tildaid) AS adminTildaid } WITH rows, sites, studyRoles, collect({orgId: orgId, admin: adminTildaid}) AS orgRoles CALL { WITH sites UNWIND (CASE WHEN size(sites) = ? THEN [null] ELSE sites END) AS scopeSite OPTIONAL MATCH (scopeSite)-[:STAFF_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN scopeSite.tildaid AS siteId, collect(DISTINCT u.tildaid) AS siteAdminTildaid } WITH rows, studyRoles, orgRoles, collect({siteId: siteId, admin: siteAdminTildaid}) AS siteRoles UNWIND rows AS row WITH row, [x IN studyRoles WHERE x.studyId = row.study.tildaid][?] AS sr, [x IN orgRoles WHERE x.orgId = row.o.tildaid][?] AS orr, [x IN siteRoles WHERE row.scopeSite IS NOT NULL AND x.siteId = row.scopeSite.tildaid][?] AS str RETURN DISTINCT row.t AS task, apoc.convert.toJson({userTildaid: row.user.tildaid, userName: row.user.name, PITildaid: sr.pi, studyOwnerTildaid: sr.smo, regAnalystTildaid: sr.ra, orgAdminTildaid: orr.admin, studyName: row.study.name, siteName: row.scopeSite.name, siteAdminTildaid: CASE WHEN row.scopeSite IS NULL THEN [] ELSE str.admin END}) AS details, row.tc.who AS dsnWho, row.o.alias AS orgAlias, row.taskInbox, row.taskUser.tildaid AS taskUserId
tusker
3
216 ms
477 ms
363 ms
flat
MATCH (:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_ACTOR]-(actor:Actor{scopeId:$orgId, state:$activeActorState, delpending:false})-[:FOR_USER]->(u:User) WHERE coalesce(u.status, ?) <> $deletedUserStatus AND coalesce(u.email, ?) <> $su…
MATCH (:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_ACTOR]-(actor:Actor{scopeId:$orgId, state:$activeActorState, delpending:false})-[:FOR_USER]->(u:User) 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}) }))) AND ($viewerSiteOrgId = ? OR actor.orgId = $viewerSiteOrgId) AND NOT EXISTS { MATCH (:TrainingAssignment{trainingId:$trainingId, userId:u.tildaid, deltimestamp:?, state:$assignedState}) } RETURN count(DISTINCT u.tildaid)
execsvc
2
118 ms
470 ms
228 ms
flat
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
docsvc
2
178 ms
470 ms
277 ms
flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
2
189 ms
470 ms
239 ms
flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
2
191 ms
470 ms
219 ms
flat
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FRO…
MATCH (study:Study )-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) AND EXISTS((study)-[:STUDY_TO_ORG{inherited:false}]-(:Organization{orgType:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl)-[:PARTICIPANT_CONTROL]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw UNION ALL WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw } WITH DISTINCT study, t, ptc, pw MATCH (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(o:Organization) MATCH (ptc)-[:BELONGS_TO_TASKCONFIG]-(tc:TaskConfig) OPTIONAL MATCH (t)-[:BELONGS_TO]-(taskInbox:Inbox{type:?})--(taskUser:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:Actor{delpending:false})-[:FOR_USER]-(user:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(scopeSite:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(o) WITH DISTINCT study, t, o, tc, taskInbox, taskUser, user, scopeSite SKIP $skip LIMIT $limit WITH collect({study:study, t:t, o:o, tc:tc, taskInbox:taskInbox, taskUser:taskUser, user:user, scopeSite:scopeSite}) AS rows, collect(DISTINCT study) AS studies, collect(DISTINCT o) AS orgs, collect(DISTINCT scopeSite) AS sites CALL { WITH studies UNWIND studies AS study OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(r:Role) WHERE r.name IN [?, ?, ?] RETURN study.tildaid AS studyId, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS piTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS smoTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS raTildaid } WITH rows, orgs, sites, collect({studyId: studyId, pi: piTildaid, smo: smoTildaid, ra: raTildaid}) AS studyRoles CALL { WITH orgs UNWIND orgs AS o OPTIONAL MATCH (o)-[:ORGANIZATION_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN o.tildaid AS orgId, collect(DISTINCT u.tildaid) AS adminTildaid } WITH rows, sites, studyRoles, collect({orgId: orgId, admin: adminTildaid}) AS orgRoles CALL { WITH sites UNWIND (CASE WHEN size(sites) = ? THEN [null] ELSE sites END) AS scopeSite OPTIONAL MATCH (scopeSite)-[:STAFF_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN scopeSite.tildaid AS siteId, collect(DISTINCT u.tildaid) AS siteAdminTildaid } WITH rows, studyRoles, orgRoles, collect({siteId: siteId, admin: siteAdminTildaid}) AS siteRoles UNWIND rows AS row WITH row, [x IN studyRoles WHERE x.studyId = row.study.tildaid][?] AS sr, [x IN orgRoles WHERE x.orgId = row.o.tildaid][?] AS orr, [x IN siteRoles WHERE row.scopeSite IS NOT NULL AND x.siteId = row.scopeSite.tildaid][?] AS str RETURN DISTINCT row.t AS task, apoc.convert.toJson({userTildaid: row.user.tildaid, userName: row.user.name, PITildaid: sr.pi, studyOwnerTildaid: sr.smo, regAnalystTildaid: sr.ra, orgAdminTildaid: orr.admin, studyName: row.study.name, siteName: row.scopeSite.name, siteAdminTildaid: CASE WHEN row.scopeSite IS NULL THEN [] ELSE str.admin END}) AS details, row.tc.who AS dsnWho, row.o.alias AS orgAlias, row.taskInbox, row.taskUser.tildaid AS taskUserId
tusker
2
192 ms
470 ms
299 ms
flat
MATCH (doc:Document{tildaid:$docId}), (org:Organization{alias:$orgAlias}) WHERE (doc)-[:DOC_TO_ORG]->(org) OR (doc)<-[:REFERS_TO]-(:DocumentShare)-[:DOC_SHARED_TO_ORG]->(org) OPTIONAL MATCH (doc)-[:REVIEW_IN]-(t:Task)-[:BELONGS_TO]->(i:Inb…
MATCH (doc:Document{tildaid:$docId}), (org:Organization{alias:$orgAlias}) WHERE (doc)-[:DOC_TO_ORG]->(org) OR (doc)<-[:REFERS_TO]-(:DocumentShare)-[:DOC_SHARED_TO_ORG]->(org) OPTIONAL MATCH (doc)-[:REVIEW_IN]-(t:Task)-[:BELONGS_TO]->(i:Inbox{type:?})-[:OWNED_BY]-(assignedTo:User) WHERE NOT doc.status IN [?, ?] AND NOT t.state = ? WITH org, doc, collect(assignedTo.tildaid) as reviewers OPTIONAL MATCH (st:Study{tildaid:doc.studyId}) CALL { WITH doc WITH doc where doc.patientId <> ? OPTIONAL MATCH (p:Patient{tildaid:doc.patientId}) OPTIONAL MATCH (p)<-[:PATIENT_REPRESENTATIVE_TO_PATIENT]-(pr:PatientRepresentative) OPTIONAL MATCH (u:User) where (u)-[:USER_TO_PATIENT]-(p) or (u)-[:USER_TO_PATIENT_REPRESENTATIVE]-(pr) return u UNION WITH doc, org WITH doc, org where doc.studyId <> ? OPTIONAL MATCH (:Study{tildaid:doc.studyId})-[:STUDY_TO_ACTOR]-(sta2:Actor{orgId:org.tildaid})-[:FOR_USER]-(stu2:User) WHERE sta2.state<>? RETURN stu2 as u UNION WITH doc, org WITH doc, org where doc.studyId = ? OPTIONAL MATCH (org)-[:ORGANIZATION_TO_ACTOR]-(ua:Actor)-[:FOR_USER]-(uu:User) WHERE ua.state<>? AND ua.roles contains ? RETURN uu as u } with distinct u, COALESCE(u.firstName,?) + ? + COALESCE(u.lastName ,?) as name where u.email <> ? return u order by toLower(name)
docsvc
2
170 ms
470 ms
338 ms
flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
2
222 ms
470 ms
322 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).