What is the inventory units table used for?

[Nate Lowrie]

Is the inventory units model meant to be an inventory ledger? If not, what's it used for and will it play nice with a ledger table?  If I have an extension that manages inventory/purchase orders, can I use inventory units to manage the manipulation of inventory counts when receiving stock/do a work order/performing a count?

My main goal is integrating the purchase order/inventory management app I have in place with Spree.  The inventory side requires a ledger and I am hoping that I can utilize an existing model for orders side of a ledger.

Regards,

Nate

[Ryan Bigg]

Each inventory unit record tracks one "physical" item in an order. It's used to track individual items against an order, similar to how line_items does it, except inventory units are for each individual item, rather than for each unique grouping of items. The inventory_units table is only used for tracking items that have already been ordered by people, not items that could *potentially* be ordered in.

Spree doesn't have a way to track stock receipt at this time. It's something that, I think, would be useful. For instance, people would want to know that last week they received a shipment of 20 items and now they're all gone, so this week they better order more. That kind of thing.

I don't think you would be able to use inventory_units for the purpose you want to.

---

Now for a "short" rant about inventory tracking in Spree:

When you're receiving stock, you're going to be incrementing the count_on_hand for the product or variant. I personally am not happy with this workflow for a number of reasons.

First: when you receive stock, I assume that you would know how much of that stock you're receiving. Let's say you receive 20 Blue Superman T-Shirts. Therefore, you're wanting to add 20 to the total of Blue Superman T-Shirts you already have in stock. To do that, you go into the product/variant admin screen, find the amount is 39 and then do some mental arithmetic and determine that 20+39=59, unless you're having a really bad day and then it's some other number.

Secondly: after you enter this number and hit enter, if someone else as either a) edited this number in the admin screen too for whatever reason or b) bought the product from the store, you're going to have a bad time because (and this is only recently), you'll have an ActiveRecord::StaleObjectError raised and that's no fun. This is because you'll be attempting to update the product/variant record with a new count_on_hand for an old lock_version, and AR will be all "nah-uhhhh" and then you may cry. 

Thirdly: If two people buy the same product/variant at precisely the same second, Spree will query the Variant record, find the count_on_hand value and then decrement it *in Ruby and then save that value back to the database*. If you have two Ruby processes doing this at the same time, chances are the count is going to be off-by-1'd, which can lead to more drama. This problem could probably be solved by using serialisable transactions within the database... but I don't know whether or not that's 100% correct because I've not had time to look into it.

---

So.

Inventory is not a completely solved problem within Spree... yet. It's something that would be good to work  on, but with the smallness of our team and the plethora of other things we've got going on, we haven't had time to look into improving how inventory is dealt with by Spree. imo, it needs fixing and therefore, one day, it'll get it.

Good luck.

--
 
 

[Nate Lowrie]

On Tuesday, January 22, 2013 6:08:50 PM UTC-5, Ryan Bigg wrote:

Each inventory unit record tracks one "physical" item in an order. It's used to track individual items against an order, similar to how line_items does it, except inventory units are for each individual item, rather than for each unique grouping of items. The inventory_units table is only used for tracking items that have already been ordered by people, not items that could *potentially* be ordered in.

Spree doesn't have a way to track stock receipt at this time. It's something that, I think, would be useful. For instance, people would want to know that last week they received a shipment of 20 items and now they're all gone, so this week they better order more. That kind of thing.

I don't think you would be able to use inventory_units for the purpose you want to.

Ok.  That make sense.  

 

---

Now for a "short" rant about inventory tracking in Spree:

When you're receiving stock, you're going to be incrementing the count_on_hand for the product or variant. I personally am not happy with this workflow for a number of reasons.

First: when you receive stock, I assume that you would know how much of that stock you're receiving. Let's say you receive 20 Blue Superman T-Shirts. Therefore, you're wanting to add 20 to the total of Blue Superman T-Shirts you already have in stock. To do that, you go into the product/variant admin screen, find the amount is 39 and then do some mental arithmetic and determine that 20+39=59, unless you're having a really bad day and then it's some other number.

Secondly: after you enter this number and hit enter, if someone else as either a) edited this number in the admin screen too for whatever reason or b) bought the product from the store, you're going to have a bad time because (and this is only recently), you'll have an ActiveRecord::StaleObjectError raised and that's no fun. This is because you'll be attempting to update the product/variant record with a new count_on_hand for an old lock_version, and AR will be all "nah-uhhhh" and then you may cry. 

Thirdly: If two people buy the same product/variant at precisely the same second, Spree will query the Variant record, find the count_on_hand value and then decrement it *in Ruby and then save that value back to the database*. If you have two Ruby processes doing this at the same time, chances are the count is going to be off-by-1'd, which can lead to more drama. This problem could probably be solved by using serialisable transactions within the database... but I don't know whether or not that's 100% correct because I've not had time to look into it.

 

---

So.

Inventory is not a completely solved problem within Spree... yet. It's something that would be good to work  on, but with the smallness of our team and the plethora of other things we've got going on, we haven't had time to look into improving how inventory is dealt with by Spree. imo, it needs fixing and therefore, one day, it'll get it.

 

I agree with you on the workflow issues.  I think I am going to give it a shot.  Here is the way that I am envisioning this:

There is an inventory ledger model that is responsible for keeping track of all of the debits and credits for all variants.  When an order is placed, an entry will go in for each SKU with a debit of the item quantity.  When you receive stock, the system records an entry for each SKU with a credit of the item quantity.  With this model, you have the capacity to easily add ledger entries from other processes like counts, manual adjustments, work orders, etc.  When a ledger entry is entered, we kick off a delayed job to update that particular sku by using the combined sum of all the records in the ledger for that particular sku.  By using the sum, we have a complete history and eliminate problems 1, 2, and 3 that you mentioned.

Could I get some feedback on the approach?  Is it something more suited to an extension?  Anyone have a better approach or tweaks to this one?

Regards,

Nate

[Chris]

This would still leave you with a race condition. You could
technically be out of stock because a customer has purchased your last
item, but while you are waiting for the delayed job to update the
counts, you could have another user attempt to purchase the same
product. Yes, your ledger system would be able to tell you the
correct number of units, unfortunately that correct number would end
up negative.

It can't be done properly relying on the application alone to update
the database count. You need the database to be able to perform
atomic operations on the count. If you pull the database entry into
an object, update the count, then push it back to the database, you
will eventually hit the race condition. Unless of course you lock the
database table or row while you perform the full operation (lock the
table, pull the count, verify that the number requested exists,
decrement the count by the number purchased, unlock the table). The
same would be true for adding inventory.

Just my $0.02.

Chris

> --
>
>

[Ryan Bigg]

I *think* the serializable isolation level for transactions might fix this problem: Section 13.2.3 here: http://www.postgresql.org/docs/9.1/static/transaction-iso.html

--

[Chris]

For guys like me using Postgres that might be ok, but what about guys
running MySQL with myisam tables? Unless things have changed (I
haven't used MySQL for a while), real transaction capability didn't
exist.

Chris

[Ryan Bigg]

Then you're plum outta luck then. If MySQL can't get their stuff together, then there's nothing we can do about it.

--

[Chris]

MySQL has transactional support if you use InnoDB for your tables.
You might just need to properly document that they can run into
problems if their database doesn't actually support transactions
(SQLite) or they are using a table format that doesn't support it.

Chris

> --
>
>

[Brian Quinn]

Nate - FYI we're working a big refactor of shipping and inventory for the 2.0 release.

We'll essentially be including your stock ledger functionality (we're currently calling it Stock Movements), it will also include optional multi-warehouse support, and checkout shipment splitting.

We'll probably do a blog post with the details once the design has stabilised.

--

Brian Quinn

Co-Founder, CTO

Spree Commerce, Inc.

http://spreecommerce.com

--

[Nate Lowrie]

On Thursday, January 24, 2013 7:28:46 AM UTC-5, Brian Quinn wrote:

Nate - FYI we're working a big refactor of shipping and inventory for the 2.0 release.

We'll essentially be including your stock ledger functionality (we're currently calling it Stock Movements), it will also include optional multi-warehouse support, and checkout shipment splitting.

We'll probably do a blog post with the details once the design has stabilised.

Great. Sounds like I'll probably just wait for the 2.0 release. For future reference, where is the planned roadmap and feature set for 2.0 documented?

Regards,

Nate

[Ryan Bigg]

It isn't documented anywhere currently. The work that's going into https://github.com/spree/spree/issues/2225, the internationalization support and the inventory tracking are the major features right now.

We'll be building a more comprehensive set of release notes when we are closer to releasing it.

--
 
 

[Tiago Motta Jorge]

Guys,

   I'm implemeting a functionality to help manage our stock without the worry of concurrency. Unfortunatelly, I'm still using Spree 1.3.x, with no time to do the upgrade to 2.x just yet. So, reading some stuff on the internet, it appears that ActiveRecord now has a method to do atomic updates, which is called "update_counters" (http://www.alfreddd.com/2011/01/atomic-increment-in-rails.html). So, I'm planning to use this in my increment/decrement stock functionality:

Spree::Variant.update_counters variant_id, :count_on_hand => desired_quantity

   I'm also planning to override Spree::Variant.increment! and decrement! do use this method above, to eliminate the worry of concurrent buyers. And also, after implemeting my stock management functionality, I'll not allow the users to set the count_on_hand manually through the admin interface anymore. ;)

   What do you think? Any concern on this solution? Shall this solve all Spree 1.3.x stock 'worries'?

Best!

Tiago.

CTO - basico.com

[Ryan Bigg]

Tiago: That should indeed solve the stock worries. Very nice find :)

--

Ryan Bigg

Community Manager

Spree Commerce, Inc.

[Tiago Motta Jorge]

COOL!! Thx, Ryan!

Best!
Tiago.