Refactor Work Order Indexing to Support FerretDB Limitations #72

New issue

Closed

opened 2025-10-30 12:07:29 +00:00 by GFMpt13 · 2 comments

GFMpt13 commented

2025-10-30 12:07:29 +00:00

(Migrated from github.com)

Problem
The current index creation script for the workOrders collection is failing on our FerretDB instance. The goal is to create a unique index on vehicleId that only applies to "active" work orders (e.g., "Draft", "InProgress", etc.).

Our initial attempts failed with MongoServerError[CannotCreateIndex]: unsupported expression:

Using $in: partialFilterExpression: { status: { $in: [...] } } - Failed
Using $or: partialFilterExpression: { $or: [{ status: ... }] } - Failed

This is a known limitation of FerretDB (and its underlying DocumentDB backend), which does not support $in or $or operators in a partial filter.

Solution
We are moving forward with Solution 2 (as documented in create_workOrder_indexes.js). This involves a schema change and a new, supported index.

This approach moves the complex logic from the database index into the application, resulting in a simple, robust, and supported index.

Action Items

Schema Migration:

Add a new boolean field isActive to the workOrders collection.
Run a one-time script to update all existing documents:
Set isActive: true where status is one of ["Draft", "AwaitingApproval", "Approved", "AwaitingParts", "InProgress"].
Set isActive: false where status is one of ["Completed", "Cancelled", "Invoiced", ...].

Backend Logic Update:

Modify all C.R.U.D. operations for workOrders.
Any code that creates or updates a status field must also set the corresponding isActive boolean value (e.g., if status becomes "Completed", isActive must become false).

Database Index Update:

Remove any old/failing versions of the vehicleId_1 index.
Add the new, supported index command to the deployment script:

db.workOrders.createIndex(
{ vehicleId: 1 },
{
unique: true,
partialFilterExpression: {
isActive: true
}
}
);

**Problem** The current index creation script for the workOrders collection is failing on our FerretDB instance. The goal is to create a unique index on vehicleId that only applies to "active" work orders (e.g., "Draft", "InProgress", etc.). Our initial attempts failed with MongoServerError[CannotCreateIndex]: unsupported expression: 1. Using $in: partialFilterExpression: { status: { $in: [...] } } - Failed 2. Using $or: partialFilterExpression: { $or: [{ status: ... }] } - Failed This is a known limitation of FerretDB (and its underlying DocumentDB backend), which does not support $in or $or operators in a partial filter. **Solution** We are moving forward with Solution 2 (as documented in create_workOrder_indexes.js). This involves a schema change and a new, supported index. This approach moves the complex logic from the database index into the application, resulting in a simple, robust, and supported index. Action Items 1. Schema Migration: - Add a new boolean field isActive to the workOrders collection. - Run a one-time script to update all existing documents: Set isActive: true where status is one of ["Draft", "AwaitingApproval", "Approved", "AwaitingParts", "InProgress"]. Set isActive: false where status is one of ["Completed", "Cancelled", "Invoiced", ...]. 2. Backend Logic Update: - Modify all C.R.U.D. operations for workOrders. - Any code that creates or updates a status field must also set the corresponding isActive boolean value (e.g., if status becomes "Completed", isActive must become false). 3. Database Index Update: - Remove any old/failing versions of the vehicleId_1 index. - Add the new, supported index command to the deployment script: db.workOrders.createIndex( { vehicleId: 1 }, { unique: true, partialFilterExpression: { isActive: true } } );

GFMpt13 commented

2025-10-30 12:25:00 +00:00

(Migrated from github.com)

The updated schema:

The updated schema: ![Image](https://github.com/user-attachments/assets/442a5282-a2dc-43f2-a4aa-cffa2ccac9da)

GFMpt13 commented

2025-10-30 13:11:03 +00:00

(Migrated from github.com)

Documentation was also updated in /docs

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

AndreFerreira5/starranja#72

No description provided.

Rows
Columns