Query
MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig)
WHERE b.delpending = false AND (b.study_id IN $lists.accessible_study_ids OR coalesce(b.study_id, ?) = ?) AND b.status <> ?
OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(wc:WorkOrder)
WHERE wc.delpending = false AND wc.status <> ? AND wc.assigned_to_id = $assigned_to_id
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.assigned_to_id = $assigned_to_id
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
Optimizer suggestions — proposals from claude-opus-5-5, 1d ago; suggests only, a human applies
index Index WorkplanInstanceMilestoneItem lookup keys so the workplan OPTIONAL MATCH can seek instead of scan
The issue`OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone)-[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem) WHERE (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id)` is not tied to `b` by any relationship. It is joined only through an OR across two properties. If either property has no index, the planner cannot run an index seek for each side of the OR. It falls back to a label scan of WorkPlanInstance or WorkplanInstanceMilestoneItem plus expansion for each of up to $fetch_limit config rows (or a Cartesian product joined afterwards). That is likely the largest share of the ~1.7 s.
The fixAdd a lookup index on cfgMilestoneItemId, and make sure tildaid on the same label is also indexed: `CREATE CONSTRAINT wpi_milestone_item_tildaid IF NOT EXISTS FOR (n:WorkplanInstanceMilestoneItem) REQUIRE n.tildaid IS UNIQUE`, or a plain index if values are not unique. Once both sides of the OR are indexed, the Neo4j 5 planner can answer the OR with a union of two NodeIndexSeeks on wpItem, then expand backwards to the milestone and instance. The other option is scanning every workplan instance for every config.
Add this index
CREATE INDEX wpi_milestone_item_cfg_id IF NOT EXISTS FOR (n:WorkplanInstanceMilestoneItem) ON (n.cfgMilestoneItemId)
Expected: For configs where wp_object_id is set, this step should change from work that grows with the total WorkPlanInstance/item count to a few index seeks per config row. That could plausibly remove a large part of the 1.6–1.9 s, but this is unverified. The statement ran only once in 7 days, so the total saving is small unless call volume grows. · Evidence: PROFILE the statement before and after. Before: look for NodeByLabelScan on WorkPlanInstance or WorkplanInstanceMilestoneItem, or a CartesianProduct/ValueHashJoin, with high db hits. After: look for NodeIndexSeek (inside a Union/Distinct) on WorkplanInstanceMilestoneItem(cfgMilestoneItemId) and (tildaid), with db hits dropping in proportion. Also run SHOW INDEXES to check which of the two properties is already covered.
index Unique constraint on Partner.tildaid (and Actor.tildaid) for the per-child recipient_key lookups
The issue`OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key})` and `OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User)` run once per child WorkOrder row, after the child fan-out. Neither is anchored by a relationship. Without an index or constraint on tildaid, each one is a full NodeByLabelScan of Partner or Actor for every child row, so the cost grows as configs × children × label size.
The fixCreate a uniqueness constraint (which comes with a backing range index) on Partner.tildaid. Do the same for Actor: `CREATE CONSTRAINT actor_tildaid IF NOT EXISTS FOR (a:Actor) REQUIRE a.tildaid IS UNIQUE`. If duplicate tildaid values exist, constraint creation will fail. In that case use a plain `CREATE INDEX ... ON (n.tildaid)` instead.
Add this index
CREATE CONSTRAINT partner_tildaid IF NOT EXISTS FOR (p:Partner) REQUIRE p.tildaid IS UNIQUE
Expected: Each per-row lookup should become one NodeUniqueIndexSeek instead of a label scan. With many child WOs per page, that could plausibly cut hundreds of milliseconds, but this is unverified. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES to see whether Partner.tildaid and Actor.tildaid are already covered. In PROFILE, look for NodeByLabelScan(Partner) / NodeByLabelScan(Actor) changing to NodeUniqueIndexSeek, with fewer db hits on those operators.
index Unique constraint on Site.tildaid for the EventScope site resolution (and check Organization.tildaid)
The issue`OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ?` looks up a Site by property for every child row, with no relationship anchor. If Site.tildaid has no index, this is a Site label scan for each row. The query also starts with `MATCH (o:Organization {tildaid: $org_id})`. If Organization.tildaid is not constrained, the first step is a full Organization scan too.
The fixCreate a uniqueness constraint on Site.tildaid so the scopeId lookup becomes a unique index seek. Also check that Organization.tildaid is covered: `CREATE CONSTRAINT organization_tildaid IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE`. Most schemas already have this. Add it only if SHOW CONSTRAINTS shows it is missing.
Add this index
CREATE CONSTRAINT site_tildaid IF NOT EXISTS FOR (s:Site) REQUIRE s.tildaid IS UNIQUE
Expected: If Site.tildaid is unindexed today, the Site lookup should change from a label scan per child row to one seek per row. If it is already indexed, nothing changes. The size of the benefit depends on Site count and how many children are Event-type. · Evidence: Check SHOW CONSTRAINTS for Site and Organization tildaid. In PROFILE, look for NodeByLabelScan(Site) or NodeByLabelScan(Organization) being replaced by NodeUniqueIndexSeek, and for a lower p95 on later executions.
Status: proposed · all suggestions
No rollups for this shape in the window.