Optimising a DynamoDB table for MES production items

I'm designing a new AppSync model for a Manufacturing Executing System and one of the most important access patterns is "Get all items in a given production date interval" and "Get all items in location X in a given production date interval", more specifically for a calendar/planning view (with date range).

type Project @model {
 id: ID!
 name: String
 items: [Item] @hasMany
}

type Item @model {
 id: ID!
 ref: String
 projectID: ID
 location: String
 productionDate: AWSDate
}

I've been trying different composite pk/sk keys and indexes but mostly my problem relies on the high frequency that an item can change its production date or location, making it harder to be a pk or sk (maybe I'm wrong here?).

One idea that occurred to me was to delete the item and rewrite it every time the productionDate or location changed, that way I would be able to keep similar production dates together (using a partition key, also items with productionDate < today will be less accessible as intended)

Is there a way to design this model to make these access patterns more efficient and avoid scans?

For context, I'm using AWS amplify for this prototype

Wanting to do a request based on time can be tricky in DynamoDB. One way to do it is to use a GSI where you place a static value as partition key, and date as the sort key:

Now when you do a Query for all of the items since Jan 2024:

SELECT * FROM myTable.myGsi WHERE GSIPK=1 AND GSISK BETWEEN '2024-01-01T00:00:000Z' AND '2024-04-10T14:00:000Z'

That is now efficient. There are some caveats to this design, and I explain it all in detail in this blog post.