LeafData Primary Key Data Scramble

This is the SECOND issue with LeafData License Scramble.

The LeafData system makes identifiers for all items in the system by constructing them from the License Code and a serial number.

WAJ425xxx.INQNFHM

That seems OK, I guess. But what happens when the License Code changes? Let's take a look.

WAJ425xxx to WAM425xxx

This license was updated by either the LCB or LeafData (they point fingers at each other, neither party will assume responsibility). All that has changed was that single letter in the license code, from J to M.

At the time of the switch, WAJ425xxx had 280 active Inventory Lots and a few active B2B Sales. Every Lot, Product, Variety and B2B Sale transaction had been recorded in the system with the WAJ425xxx prefix on them. Which, of course, follows the standard pattern from LeafData.

However, now that the license code has changed, LeafData will then modify this primary identifiers. BUT!! They don't do it all at once; they don't repair or update their data. What happens is that these Global IDs are re-written on each time an object is updated.

Data Errors Introduced

The biggest error is that B2B Sale items that are in-transit starting with WAJ425xxx now become WAM425xxx prefixed items. But they were already in-transit to the retailer. And this means, when the materials land at the retailer the labels and barcode identifier on the package are not the same as what left the supply-side license facility. LeafData has scrambled and invalidated these primary keys.

A second issue is that any/all active Inventory Lots will have their primary identifier modified after any data operation. That is an existing item, say WAJ425xxx.IN1234 will be updated/modified and then it's global primary key becomes WAJ425xxx.IN1234. So, this requires, at least a re-labeling. If this is packaged goods, this means re-labeling every package (eg: 2000 packaged joint tubes).

A third issue is that these object identifiers are used for Lots (and Plants) but also for their dependent objects: Product and Variety and Section. This means that ALL of a licensees data is invalid. That is, they won't be able to update lot WAJ425xxx.IN1234 to become WAM425xxx.IN1234 becuse this API call needs Product, Variety and Section identifiers. But all those Product, Section and Variety identifiers still start with WAJ425xxx -- which is not valid anymore because WAJ425xxx isn't valid, they need to use WAM425xxx. So, after this license switch all Variety, Section and Product objects need to be re-saved/updated via the API to rewrite their IDs -- so that then the Lot (and Plant) identifiers can be updated.

Oh, and you can't just fix these IDs client side (eg locally re-write the prefix) because all the objects in the LeafData system have the old prefix still on them. So it MUST be done via API to submit the object with the old ID and then get the new ID back out and update records as necessary.

And since LeafData is so bloody slow, this means the process takes a long time. And if the licensee is not using a third party integrator they'd have to find and fix all these manually.

And since all your identifiers have change the licensee must re-sticker every lot and every package. This means many hours of work for the licensee.

Fixology

There are a few methods for partly resolving these issues.

No Active Data

For licensees DO NOT have any active B2B transactions in the pipe when the license code is being switched. This means pausing sales and/or sale-data-entry for a few days while waiting for LCB and/or LeafData to make the switch. This part is pretty hard to do, for business operational reasons. Additionally, timing this process is awkward because when there is not announced or predictable timing for the license recode.

Ignore Global ID Prefix

For software integrators, you'll want to ignore the prefix on all the object identifiers for matching lookup. In fact, you can base36 decode part of the object identifer to find the LeafData database internal Primary Key and use that if you wanted. Serial based primary keys are a "Bad Idea" but at least they are consistent.

Recode Scripts

Also for software integrators, you'll need to have some re-write scripts. Via API update all Sections (Areas), Variety (Strain) and Product (Inventory Type) objects first, then update Lots.

External ID

Many of the objects stored in LeafData allow for an external_id attribute. This value should be used to be able to positively identify an object in the local system. This value will persist across global_id modifications.

Why Not Fix LeafData?

Sure, LeafData could fix this issue too. They could un-do the stupid idea of putting the license prefix on their global_id. Or they could make their own script to update the objects in the system.

However, the LCB has stated on multiple occasions that they do not have confidence in LeafData to make these repairs. In fact, the LCB has decided to freeze the system because it's so fragile.

These bugs, and all the others, remain to the determent of every single participant in the Washington State cannabis industry. And of course these issues affect LeafData in Pennsylvania.