mysql refresh openboxes

Hi all,

I have a little question. I’m writing a small portal that I want to link to the mysql database of openboxes. So far so good… but when I withdrawn an item in product_availability it doesn’t get refreshed in Openboxes it self.

How often does openboxes refresh the data from the mysql database?

That’s a great question. So the table is updated by an internal OB process that gets triggered under certain conditions.

To solve your problem in the short term you have a few options:

  • invoke an API that’ll trigger the refresh manually after your changes are deemed successful
  • add your own triggers to the database (this would be more difficult because you would need to write SQL to update the product availability table

Both of these options will likely require more discussion so let me know you have any questions.

You could also modify your portal to talk to OpenBoxes through our API and that would help you to avoid having to do either of thr workarounds above.

The first option would be ok. I could trigger it, but what is the correct api URL to trigger a mysql refresh?

So there’s no endpoint to trigger a refresh (yet) so we’ll need to add one. However, there are two ways you can trigger a refresh indirectly

(1) The simplest, but most brute force option would be to use the “Refresh caches” feature available from the user context menu.

To do this programmatically, you’d need to authenticate via the API and then send a GET request for the flush cache.


Note: This is not really an API endpoint - it’s just a URL that exposes a feature used within the application itself. This will trigger a lot of unwanted requests since the response will be a redirect to the dashboard. In addition, this triggers a refresh of product availability for all products across all locations. If your portal application is only doing an update for a single product then this will be overkill and may lead to performance degradation if you are sending lots of these requests throughout the day.

(2) The second approach would require you to POST an no-op update to the Product API. This should generate a RefreshProductAvailabilityEvent for that product. This will trigger the refresh for that product within the current location.


An example using

curl --location --request POST 'https://<your-server>/openboxes/api/products/:productId' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Cookie: JSESSIONID=<cookie-returned-by-login>' \
--data-raw '{ lastUpdated: "09/13/2022 11:51:00" }'

In order to post to either the Flush Cache URL or the Product API you’ll need to authenticate before sending the request.

In a future version we could add a webhook to allow external applications to send requests for a product availability refresh for different scenarios:

  • one product at a single location
  • one product across all locations
  • all products at a single location
  • all products across all locations

It would be good to know more about what your portal is doing in order to facilitate this.

I just noticed that updates to Product domain objects do not trigger a refresh of the product availability table. So you would need to send a POST request to either the Location or Inventory Item API instead.

The following request will trigger a refresh of the product availability table for all products at the given location.

curl --location --request POST 'https://<your-server>/openboxes/api/locations/:locationId' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Cookie: JSESSIONID=<cookie-returned-by-login>' \
--data-raw '{ "lastUpdated": "09/13/2022 11:51:00" }'

The following request will trigger a refresh for a product (the one associated with the given inventory item) across all locations.

curl --location --request POST 'https://<your-server>/openboxes/api/generic/inventoryItem/:inventoryItemId' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Cookie: JSESSIONID=<cookie-returned-by-login>' \
--data-raw '{ "lastUpdated": "09/13/2022 11:51:00" }'