inventory_version not always incremented?

markguertinmarkguertin Posts: 59Member
Hi guys, I took James' advice and moved my inventory tracking to use the inventory_version but I've run into a bug.  I spent a good bit of time going through all my logs and it seems like there are times when the inventory_version is not always being incremented or it's somehow being incremented out of sequence.  Let me give you an example.

I've setup my local database to track the last inventory_version that was pulled (across any item).  On the next run I search for products with an inventory_version >= the last version.  But somewhere along the lines I'm missing some inventory updates that happen via POs.  If I then pull a full update (instead of just search since the last revision), which takes 8-10 minutes to run (see my other thread about this) I get the complete picture, but when following along doing the incremental updates using inventory_version as suggested it seems to miss certain items.  

In coordinating with our purchasers today I think we might have tracked down a bug in Lightspeed.  Best guess is that if a PO is edited after the fact it might not be pushing an increment on inventory_version (haven't been able to fully confirm this but looking at the SKUs affected this seems to be the case).

Is there any way you guys can test this at your end or suggest other reasons why inventory_version is getting out of sync?  Something in the process somewhere is not incrementing as my logs don't show any misses or errors so something is slipping through the cracks. 

Help! :)

15 comments

  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    @markguertin I did some tests, but in every case where the inventory quantity of the product changes, the inventory_version is incremented.

    What kind of edits are they making to the POs?
    James Ratcliffe
    API Integrations Specialist
    Lightspeed HQ
  • markguertinmarkguertin Posts: 59Member
    Not sure exactly what kind of edits they are making or if that is 100% where things are falling down, but there's definitely some PO changes or creation that are not being picked up by searching by inventory_version.  The only way I've been able to pick them up was to do a full search again.  If I can track it down tighter I will let you know.

    Has there been any comments or progress on getting a faster way of pulling all of the inventory?  Between these two issues it's a bit of a show stopper for us right now.  For now I'm doing incremental updates throughout the day and doing full updates overnight (and hope that they don't time out).
  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    We've never seen a case where a product's inventory quantity changed and its inventory_version didn't. Unless we can reproduce this, there isn't much we can do.

    Development decided not to work on the response time from the product_inventory endpoint now because there are already faster ways to get the same data, i.e. from the products endpoint or from the product search endpoint.

    The search endpoint will probably be much faster. Here's a search payload that would be equivalent to a call to product_inventory:
    <search>
      <search_query>
        <columns>
          <column id="lsserver.search.column.id"/>
          <column id="lsserver.search.column.code"/>
          <column id="lsserver.search.column.inventory"/>
          <column id="lsserver.search.column.quantity_reserved"/>
          <column id="lsserver.search.column.quantity_in_warehouses"/>
          <column id="lsserver.search.column.quantity_ordered_for_customers"/>
          <column id="lsserver.search.column.quantity_ordered_for_stock"/>
          <column id="lsserver.search.column.quantity_total"/>
          <column id="lsserver.search.column.inventory_version"/>
        </columns>
      </search_query>
    </search>

    James Ratcliffe
    API Integrations Specialist
    Lightspeed HQ
  • markguertinmarkguertin Posts: 59Member
    Thanks, I'll give that a try and see how it works out. 

    If that call is the equivalent couldn't it just be done on the backend to replace the current one that takes so long to run?  I'm happy to do the search myself but in terms of remaining backward compatible and having things that work well it might be worth investigating this option. 

    I'm sure the issue is reproducible as I've seen it happen a half dozen times now over the last 2 weeks, but I just don't have the time it would take to coordinate through 13 stores, dozens of purchasers, running before and after tests on each PO submission, etc, to see where it's failing to increment.  Especially since I don't have any way of checking the actual inventory_version value directly and would have to write special tools to do lookups to figure out when it does and doesn't change ... 
  • markguertinmarkguertin Posts: 59Member
    Definitely much faster.  Guess there's no way to get in_transit from search?  :(  -- sadly I need that value so I might not be able to use this method.
  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    Sadly, you can't get the in_transit quantity through search. Development has a ticket to add this and other fields to the search, but there's no ETA on that right now.

    The other option is to use the product endpoint. The inventory quantities (including in_transit) included in each product as a sub-object. This is a few times slower than the search, but still much faster than product_inventory.
    James Ratcliffe
    API Integrations Specialist
    Lightspeed HQ
  • markguertinmarkguertin Posts: 59Member
    Hmm ok, I will investigate this and see if it's reasonable for us to use.  I think for now I might stick with what I've got going on as I don't want to spend too much more time going backwards with this one.
  • markguertinmarkguertin Posts: 59Member
    Did some digging into this and we definitely can't use it as the /api/product endpoint doesn't provide the UPC.  Looking back at my notes this is why I had to abandon it in the first place and why we had to move to the /api/products_search endpoint, but that is also missing crucial data (in_transit).

    So let me outline where things stand right now for my company's needs and the current API offerings:

    1. The /api/product endpoint is semi slow, it provides complete product inventory information but it does not provide complete product information (missing UPC, which IMHO is a bug that needs to be addressed).
    2. The /api/product_inventory endpoint is unusably slow but seems to be the only endpoint that provides the full set of inventory details
    3. The /api/product_search endpoint is fast but is missing information (in_transit)

    There does not seem to be a way to properly get the full product and inventory details without some fairly messy hoop jumping for API users here.

    As I don't have the man hours (not only for myself but involving over a dozen people) it would take to try and reproduce the bugs where inventory_version is missing details my current workarounds feel like an interim solution at best that I'm going to have to come back and completely rewrite for a third (fourth?) time in the near future once things get fixed/added to the API.  Currently I am having to use both the product_search endpoint and maintain my own inventory_version for continued lookups combined with the much more expensive product_inventory endpoint overnight (and hope they don't time out, 2 of 13 did last night).  It also means that I have to tell my end users that the product inventory may not always be correct and I can't tell them for sure at any point that it is 100% correct, which makes everyone look bad in this situation.

    It seems that I'm going to have to constantly monitor and revisit this solution as things continue to evolve with the API endpoints.  Some already feel abandoned (like product_inventory which you state above the developers don't want to put the effort into resolving, which makes me feel like it's going away and sadly I am currently relying on but can't use often due to it's glacier like speed).

    If I may be blunt I would suggest some internal auditing of this whole messy situation to get this resolved properly and to move forward with some complete endpoints that run in a reasonable amount of time that can be locked down and will be stable going forward. 
  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    1. Most product fields, including the UPC, are only returned when you request a specific product by ID:
    /api/products/1/
    2. I've triple-checked, and there are no fields returned by /api/product_inventory/ that aren't returned by /api/products/.

    3. This is definitely a big omission from the search endpoint. I'm talking to Development about the possibility of adding it.


    The incrementing of the inventory_version is a database trigger on any writes to the inventory table, so there's not much to audit there. I won't say that it's impossible that the inventory_version isn't working correctly in all cases, but it's so unlikely that it isn't worth spending time investigating unless we have a concrete example or steps to reproduce the issue.

    I agree that the /api/product_inventory/ endpoint shouldn't be that slow, but all the same data is available in the /api/product/ endpoint, so you can just use that endpoint instead if you want to do a full sync.

    I would still recommend using the search endpoint since it lets you filter based on the inventory_version, but it's up to you. One option would be to use the search endpoint to see which products have had inventory changes, then get each product to get the quantity in transit and any other data that you can't get from the search endpoint.

    We have a ticket with dev to add the in-transit inventory and other fields to the search endpoint. This won't be a breaking change, just adding more available fields.
    James Ratcliffe
    API Integrations Specialist
    Lightspeed HQ
  • markguertinmarkguertin Posts: 59Member
    "1. Most product fields, including the UPC, are only returned when you request a specific product by ID:"

    Or from the search endpoint (which is where I currently get them from).  The problem here is what I need to do does not scale from the current API offerings and the approaches you are suggesting.  I really, really need to be able to pull a specific subset of data from single calls which is not currently possible.  Let me explain a bit of the reason why this is important and give you some numbers so you understand why this is a big issue for our needs.

    If I called /api/products/1/ as you suggest to retrieve the UPC it would require a little over 238,000 API calls (between 17k and 23k x 13 stores) + 13 local database updates. If UPC was added to the 'partially rendered' call from /api/products it would instead only require 13 API calls + 13 local database updates.

    Instead I have to currently use a hybrid approach, pulling some of the data from one endpoint and some of the data from another endpoint.  This also means that I need to split the main products table into two separate tables and do much more complex database calls when it comes time for my app to get the data back out (using joins to put that data back together).  It's not feasible to use two separate endpoints in this sort of manner and keep the data in a single table for the same reasons as above but in reverse.  Doing 13 API calls and then 238k local database updates (which is MUCH faster than API calls which currently top out for me at 1-2 calls per second) is still far too expensive in terms of I/O.


    2.  You are correct that I can get the same information from both of those endpoints, but my solution has now been built to use both /api/products_inventory and the search endpoints, which I was directed to use on earlier posts and support requests when I realized that I couldn't get UPC data from the main /api/products endpoint (without having to iterate through product by product) ... so now having to go back and rewrite to use /api/products again is a huge waste of time as all three of those endpoints use different data structures.

    I've put some checks in place to try and find out where the inventory_version potentially hiccups.  Maybe it won't happen again and maybe it will, but the odds of finding it about a half dozen times by chance reporting in a couple of weeks tells me that there are bugs on side or the other and I've gone over my logs with a fine tooth comb and can't see any missis on my end.  I just can't figure out any other reason, aside maybe from my other post about differences between the On Order values that could account for the variations that I have seen, especially considering that each time when I did either a fresh full sync or manually dropped the inventory_version back an arbitrary amount and pulling from the search endpoint again it brought the numbers back to matching what the app showed.

    If the inventory_version is a trigger on writes to product inventory then maybe the issue here is that there is either a PO modification or possibly an order creation or modification that is not changing the actual inventory levels (and not triggering the increment, therefore not showing up via search).  My specific issue is with coming_for_stock -- not with inventory levels of received items.  Items that have been added in POs were not showing up as coming_for_stock using the suggested search endpoint and relying on inventory_version to show me which products had been updated since I last polled the inventory levels.  It happened at least 6 times that I know of in a couple of weeks.

    Without being able to see the actual lightspeed database backend all I can do is guess and/or spend a lot of time chasing my tail and trying to recreate this type of potential issue and then run a ton of reporting and do a bunch of manual comparisons on each minor change to try and reverse engineer where it might have happened.  The likelihood of me being able to catch something like this in action is next to nil, let alone pinpoint exactly what caused it and provide you a way to recreate it... but with the checks I've put in place I can at least figure out if/when they happen now and what potential SKUs have been affected. Maybe I can at least narrow it down a bit and figure out which of the 50-100 changes that happen across a day might have been responsible once I know exactly what SKUs have been affected in a timely manner (and if the purchasers and managers remember what they did the previous day on any of the specific POs that might have triggered it).

    We do have ongoing and reproducible PO bugs (I will check with our store managers to make sure they have been reported), which makes them suspect in my eyes.

    I'll be very much looking forward to having in_transit available from search, because then if I can rely on doing delta updates using inventory_version it might make my life a lot easier in terms of what I need to do with the data on my app!
  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    I didn't know you were looking for changes to the coming for stock/customers quantities. Those changes won't trigger an update to the product's inventory_version. Changes to available, reserved, warehouses and in-transit inventory will update the inventory_version.

    To find changes to coming for stock/customers, the easiest solution would be to pull all products and compare (like you were originally doing).
    James Ratcliffe
    API Integrations Specialist
    Lightspeed HQ
  • markguertinmarkguertin Posts: 59Member
    Ok thanks.  Good to know I'm not losing my mind and glad we got it figured out as to why it's not pulling those changes.

    Looking forward to having in_transit available in search!  Then I can re-code my app one more time and hopefully not have to revisit.
  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    I have a ticket on hold to let you know when we add that, but I don't know how long it will take.
    James Ratcliffe
    API Integrations Specialist
    Lightspeed HQ
  • markguertinmarkguertin Posts: 59Member
    perfect, thanks.
  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    You're welcome.
    James Ratcliffe
    API Integrations Specialist
    Lightspeed HQ
Sign In or Register to comment.