Firestore query error: Error 9 Failed precondition

Question

I have trouble getting this Firebase Function to work, except in Firebase Emulator.

Here's the error message. It doesn't mention anything about indexes, but I still suspect that incorrect index is to blame.

"Unhandled error Error: 9 FAILED_PRECONDITION: 
    at callErrorFromStatus (/workspace/node_modules/@grpc/grpc-js/build/src/call.js:31:19)
    at Object.onReceiveStatus (/workspace/node_modules/@grpc/grpc-js/build/src/client.js:357:73)
    at Object.onReceiveStatus (/workspace/node_modules/@grpc/grpc-js/build/src/client-interceptors.js:323:181)
    at /workspace/node_modules/@grpc/grpc-js/build/src/resolving-call.js:94:78
    at process.processTicksAndRejections (node:internal/process/task_queues:77:11)
for call at
    at ServiceClientImpl.makeServerStreamRequest (/workspace/node_modules/@grpc/grpc-js/build/src/client.js:340:32)
    at ServiceClientImpl.<anonymous> (/workspace/node_modules/@grpc/grpc-js/build/src/make-client.js:105:19)
    at /workspace/node_modules/@google-cloud/firestore/build/src/v1/firestore_client.js:227:29
    at /workspace/node_modules/google-gax/build/src/streamingCalls/streamingApiCaller.js:38:28
    at /workspace/node_modules/google-gax/build/src/normalCalls/timeout.js:44:16
    at Object.request (/workspace/node_modules/google-gax/build/src/streamingCalls/streaming.js:130:40)
    at makeRequest (/workspace/node_modules/retry-request/index.js:141:28)
    at retryRequest (/workspace/node_modules/retry-request/index.js:109:5)
    at StreamProxy.setStream (/workspace/node_modules/google-gax/build/src/streamingCalls/streaming.js:121:37)
    at StreamingApiCaller.call (/workspace/node_modules/google-gax/build/src/streamingCalls/streamingApiCaller.js:54:16)
Caused by: Error
    at Query._get (/workspace/node_modules/@google-cloud/firestore/build/src/reference.js:1738:23)
    at Query.get (/workspace/node_modules/@google-cloud/firestore/build/src/reference.js:1726:21)
    at /workspace/timesheet/management.js:219:28
    at /workspace/node_modules/firebase-functions/lib/common/onInit.js:33:16
    at fixedLen (/workspace/node_modules/firebase-functions/lib/v1/providers/https.js:77:47)
    at /workspace/node_modules/firebase-functions/lib/common/providers/https.js:458:32
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 9,
  details: '',
  metadata: Metadata {
    internalRepr: Map(1) { 'x-debug-tracking-id' => [Array] },
    options: {}
  }
}"

Here is the function in question. I put comments in parts where I've made troubleshooting attempts.

const filterTimesheetWorklogs = (worklogs, uid) => {
  return worklogs.filter(worklog => worklog.project.timesheetApprovers.includes(uid))
}

const isSpawnAllApproved = async (virtualTimesheetId, uid) => {
  // All timesheets with the same virtualTimesheetId with uid as one of the approvers are approved
  const collectionGroup = admin.firestore().collectionGroup("timesheets")
  const query = collectionGroup
    .where("virtualTimesheetId", "==", virtualTimesheetId)

  const snap = await query.get()

  const authorizedDocs = snap.docs.filter(async doc => {
    // const projectRef = doc.ref.parent.parent
    // const projectSnap = await projectRef.get()
    // const projectData = projectSnap.data()

    const { projectId } = doc.data()
    const projectCollectionGroup = admin.firestore().collectionGroup("projects")
    const projectQuery = projectCollectionGroup.where("timesheetApprovers", "array-contains", uid)
    const projectSnap = await projectQuery.get()
    const projectData = projectSnap.docs
      .map(doc => ({ id: doc.id, ...doc.data() }))
      .find(project => project.id === projectId)
    
    return projectData.timesheetApprovers.includes(uid)
  })

  return authorizedDocs.every(doc => {
    const data = doc.data()
    return data.status === "approved"
  })
}

const getAuthorizedVirtualTimesheets = functions.https.onCall(async (data, context) => {
  const { filter: { start, end }, companyId } = data;
  const { uid, token: { roles = {} } } = context.auth

  if ([
    Object.keys(roles).includes(companyId),
    roles[companyId].includes("timesheet approver") || roles[companyId].includes("admin"),
  ].some(v => !v)) {
    throw new functions.https.HttpsError("permission-denied", "Unauthorized access.");
  }

  const collectionGroup = admin.firestore().collectionGroup("virtualTimesheets")
  const query = collectionGroup
    // I've tried removing these two `.where`'s, error persists
    .where("createdAt", ">=", dayjs(start).startOf("day").toDate())
    .where("createdAt", "<=", dayjs(end).endOf("day").toDate())
  
  const snap = await query.get()

  // I've tried removing this entire statement and just return
  const docs = await Promise.all(snap.docs
    .map(async doc => ({
      id: doc.id,
      ...doc.data(),
      worklogs: filterTimesheetWorklogs(doc.data().worklogs, uid),
      status: (await isSpawnAllApproved(doc.id, uid)) ? "approved" : null,
    })))
  return docs
})

Other troubleshooting attempts:

• Created a composite index as described below (doesn't work):
  • Collection ID: virtualTimesheets
  • Fields:
    1. createdAt ASC
    2. createdAt DESC
  • Scope: Collection group
• Removing both the where clauses and the Promise.all statement (the error disappears).

Answer 1

For me it was a missing index. I followed this example, where you can build your query on the firestore's query builder.

Step-1 To know which query is causing the issue look for queries around the Stack mentioned after "Caused By: Error" like in the above asked question.

It seems to be at or around --> at /workspace/timesheet/management.js:219:28.

Step-2 Run the exact query on the Query Builder on firestore.

Step-3 You will get an Error mentioning that you need to create an Index. Create that index and you are done.

https://github.com/googleapis/nodejs-firestore/issues/1866#issuecomment-1793570113