Query
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???/') as folderPath, folderConstraints
RETURN {id: fold.tildaid, name: fold.name, number: fold.number, folderType: fold.folderType, folderScope: fold.scope, folderScopeId: fold.scopeId, path: folderPath, mandatoryFields: [], optionalFields: [], subArtifactId: COALESCE(subArtifact.tildaid, ?), subArtifactName: COALESCE(subArtifact.name, ?), constraints: CASE WHEN folderConstraints IS NOT NULL THEN folderConstraints{.*} ELSE null END} AS primaryFolder }
CALL {
return null as listingFolders }
CALL {
return null as stateTags2, null as stagingLabels }
CALL {
return [] as reviews}
CALL {
return null as templateDetails}
CALL {
with doc, docObj
CALL {
with doc, docObj
with doc, docObj
where doc.primaryVersionId = ? or doc.primaryVersionId is null
optional match (doc)-[:UPLOADED_BY]->(uploader:User)
return {tildaid: doc.tildaid, fileName: doc.fileName, originalFileName: doc.originalFileName, contentType: doc.contentType, date: doc.date, dateType: doc.dateType, versionName: doc.versionName, rejectReason: doc.rejectReason, language: doc.language, languages: doc.languages, needsAttention: doc.needsAttention, needsAttentionReasons: doc.needsAttentionReasons, trackActiveDuplicates: doc.trackActiveDuplicates, versionNumber: doc.versionNumber, majorVersion: doc.majorVersion, minorVersion: doc.minorVersion, generation: toString(doc.generation), cksum: doc.cksum, size: doc.size, status: doc.status, stateTag: doc.stateTag, pages: doc.pages, uploadMethod: doc.uploadMethod, changeLog: [], createdAt: doc.createdat, renderedCompletedAt: doc.renderedCompletedAt, renderedFieldsRevision: doc.renderedFieldsRevision, renderedSignersRevision: doc.renderedSignersRevision, tmfFiledAt: coalesce(doc.tmfFiledAt, ?), uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} as version
union
with doc
optional match (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion)
where $versionsSeesPreRedaction0 or coalesce(dv.preRedaction, false) = false or dv.tildaid = doc.primaryVersionId
optional match (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv)
optional match (dv)-[:UPLOADED_BY]->(uploader:User)
optional match (dv)-[:PROMOTED_FROM]->(psrc:Document)
return case when dv is null then null else {tildaid: dv.tildaid, fileName: dv.fileName, originalFileName: dv.originalFileName, contentType: dv.contentType, date: dv.date, dateType: doc.dateType, versionName: dv.versionName, rejectReason: dv.rejectReason, language: dv.language, languages: dv.languages, needsAttention: dv.needsAttention, needsAttentionReasons: dv.needsAttentionReasons, trackActiveDuplicates: dv.trackActiveDuplicates, versionNumber: dv.versionNumber, majorVersion: dv.majorVersion, minorVersion: dv.minorVersion, documentSourceId: ds.tildaid, promotedFromDocumentId: psrc.tildaid, generation: toString(dv.generation), linearizedPdfUrl: dv.linearizedPdfUrl, linearizedPdfStatus: dv.linearizedPdfStatus, needsProcessedPdf: dv.needsProcessedPdf, langDetectionStatus: dv.langDetectionStatus, translatedPdfUrl: dv.translatedPdfUrl, translatedPdfStatus: dv.translatedPdfStatus, cksum: dv.cksum, size: dv.size, status: dv.status, stateTag: dv.stateTag, pages: dv.pages, uploadMethod: dv.uploadMethod, changeLog: [], url: dv.url, createdAt: dv.createdat, renderedCompletedAt: dv.renderedCompletedAt, renderedFieldsRevision: dv.renderedFieldsRevision, renderedSignersRevision: dv.renderedSignersRevision, preRedaction: coalesce(dv.preRedaction, false), tmfFiledAt: coalesce(dv.tmfFiledAt, ?), uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} end as version // TODO: [Major/Minor Versioning] Once client supports minor version UX, update ordering to: // }
with doc, version
order by version.majorVersion, version.minorVersion // This will display versions as ?.?, ?.?, ?.? instead of flat ?, ?, ? }
with doc, version
order by version.versionNumber
where $versionsVersionId0 IS NULL OR version.tildaid = $versionsVersionId0
return collect(version) as versions }
CALL {
return null as annotationSpec, null as annotationVal }
CALL {
return [] as queries }
CALL {
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 {
WITH doc
MATCH (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion{tildaid: coalesce($docAttrVersionId0, doc.primaryVersionId)})
MATCH (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv)
OPTIONAL MATCH (ds)-[:HAS_ATTRIBUTE]->(da:DocAttribute)
OPTIONAL MATCH (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition)
RETURN collect(DISTINCT da{.*, name: COALESCE(dad.name, ?), description: COALESCE(dad.description, ?), documentSourceId: ds.tildaid}) as docAttributes }
CALL {
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 {
WITH docObj
OPTIONAL MATCH (docObj)<-[:INCLUDES]-(currentFolder:Folder{folderType:?})
WHERE currentFolder.scope IN [?, ?]
OPTIONAL MATCH (docObj)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder)
OPTIONAL MATCH (docObj)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo)
WITH CASE WHEN stagingInfo IS NOT NULL THEN {artifactId: stagingInfo.folderId, subArtifactId: stagingInfo.subArtifactId, stagingInfoId: stagingInfo.tildaid} WHEN currentFolder.level = ? THEN {artifactId: currentFolder.tildaid, subArtifactId: COALESCE(currentSubArtifact.tildaid, ?), stagingInfoId: ?} ELSE {artifactId: ?, subArtifactId: ?, stagingInfoId: ?} END as tmfInfo
WITH {folderId: tmfInfo.artifactId, subArtifactId: tmfInfo.subArtifactId, stagingInfoId: tmfInfo.stagingInfoId} as tmfCategorization
RETURN CASE WHEN tmfCategorization.folderId=? THEN null ELSE tmfCategorization END as tmfCategorization }
with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo
WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total
return document, total
Optimizer suggestions — proposals from claude-opus-5-5, 1d ago; suggests only, a human applies
index Make sure the Document.tildaid entry point uses an index seek
The issueThe first clause, `MATCH (doc:Document) WHERE doc.tildaid IN $lists.documentIds0`, sets the size of everything after it. If Document.tildaid has no constraint or index, Neo4j has to scan every Document node and test each one against the list before any of the per-document subqueries run. The other entry points (Organization, Study and User by tildaid) are probably key lookups already, so this is the one most likely to cost real time. Low priority: the statement ran only once in 7 days, for 1.2 s total.
The fixCheck SHOW CONSTRAINTS / SHOW INDEXES for a uniqueness constraint on (:Document {tildaid}). tildaid looks like the service's node key, so it probably exists already. If it doesn't, add the constraint. The planner can then turn the IN list into one seek per id instead of a full label scan. Do the same check for Organization, Study and User tildaid, since this query also looks those up by tildaid.
Add this index
CREATE CONSTRAINT document_tildaid_unique IF NOT EXISTS FOR (d:Document) REQUIRE d.tildaid IS UNIQUE
Expected: If the constraint is missing, the first step goes from a full scan of Document nodes (cost grows with the size of the label) to roughly |documentIds0| seeks, which could save a large part of the ~1.2 s. If the constraint already exists, nothing changes. · Evidence: Run EXPLAIN/PROFILE on the statement. Today, look for NodeByLabelScan + Filter on Document; after the change, look for NodeUniqueIndexSeek(Locking) or NodeIndexSeek on Document(tildaid). Compare db hits on that first operator.
rewrite Build the study/actor/policy context once per study, not once per document
The issueThe query collects the documents, UNWINDs them, matches Organization again, and then runs `CALL { WITH org, doc UNWIND [doc.studyId] ... }` for every document row. That call re-resolves the Study, the STUDY_TO_ACTOR → FOR_USER actor, the site memberships (StudySiteActor → DslPlanDefinition → Site), the StudyCountryActor memberships, and the DocumentAccessPolicy EXISTS checks. Documents in one request usually share one or a few studies, so this heavy multi-hop work repeats N times with the same result. The `collect(DISTINCT doc)` / `count` / `UNWIND` at the start, and matching `org` per row, also add rows without changing the result.
The fixMatch Organization once at the top. Compute the distinct study ids first (`WITH collect(DISTINCT doc.studyId) AS studyIds`), run the study/actor/sites/countries/policies block once per distinct studyId, and put the results in a map keyed by study id. Then UNWIND the documents and look up each one's context in that map. The inline comment about `studyById` suggests this was intended, but the CALL still takes `doc` as input. I've left out a full rewritten statement because the statement text I received is cut off mid-query, so I can't produce a complete, correct rewrite. Apply the change to the full source in docsvc.
Expected: The study-context work drops from N documents to the number of distinct studies. For a batch of tens of documents from one study, that part of the plan should shrink by about that factor, depending on how much of the 1.2 s it accounts for. · Evidence: In PROFILE, compare the row counts going into the STUDY_TO_ACTOR / STUDYSITEACTOR_TO_ACTOR / POLICY_TO_STUDY expands with the number of distinct studyIds. After the change they should match the distinct count, not the document count. Then check the end-to-end time on the same id list.
rewrite Limit the variable-length INCLUDES ancestor traversal
The issue`OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder)` runs once per document and walks every INCLUDES path up the folder tree. If a folder can be reached by more than one path (shared folders), the number of paths grows quickly. Then `collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder)` collapses them back down in memory, so the extra paths cost time and add nothing.
The fixKeep the upper bound as small as the TMF hierarchy allows. If folders have exactly one parent, switch to a single directed path, e.g. `MATCH p=(docFolder)<-[:INCLUDES*0..N]-(root:Folder) WHERE NOT ()-[:INCLUDES]->(root)` and use `nodes(p)`, so each document gets one path instead of every path. Another option is to store the ancestor id list on the Folder when it is created or moved and read that list directly. As with the suggestion above, this needs to be applied to the full source because the statement I received is cut off.
Expected: Fewer rows out of the var-length expand and less memory for DISTINCT/collect. A small gain if the folder trees are shallow and each folder has one parent; a larger one if folders are shared. · Evidence: In PROFILE, compare the rows out of the VarLengthExpand(Into/All) on INCLUDES with the number of distinct ancestor folders. If rows are much higher, many redundant paths are being walked.
Status: proposed · all suggestions
No rollups for this shape in the window.