LeafData License Data Scramble

This frustrating issue continues to haunt the Akerna / LeafData system in Washington. The reuse of their primary identifiers global_id for modified license codes.

By reusing their Primary Keys for what are functionally new licenses in the system is not good. Additionally, they are factoring out the old license code into a new global_id.

What this means is that data, records on file, their own records, the State records become invalidated by their own systems.

The Symptom

For example License with global_id = WAWA1.MMD3 was created in this system at launch (2018-02-01). This ID was linked to code:R413314 and at the time named "2020 SOLUTIONS ON THE GUIDE".

From this time many, many licensees send materials to R413314. Nearly all of the licensees operate using either the name or license number of their clients. These license numbers are specified by the LCB and well known in the industry.

Many months pass, and during this time, it's name changes ("BLUE GUIDE LLC") and changes again ("2020 SOLUTIONS GUIDE MERIDIAN") and again ("2020 SOLUTIONS ON THE GUIDE"). And the whole time suppliers are, naturally, selling to this retailer.

However, what is happening to their paperwork on file in the system? Due to the way that LeafData operates, all of the old business-to-business sales (aka: "Manifests") have changed too. That is, when a Manifest was printed from the system in January 2019 it would read one name, but printing in April of that same year would yield a different name. The Manifest is just linked to this Primary Key (global_id).

No big deal? Their name just changed and it's still the same business. However, in laws of Washington folks are required to keep records, lots of records for their cannabis business. And now their paper records wouldn't match a re-print from the system. Which is maybe harmless when it's just a simple name change.

Critical Changes

And this is where it gets a little more bumpy. Some of the data is a little more critical -- that is License Number and Address. And the LeafData process re-uses this same primary key, when these other critical data-points change.

For two years sales were made to WAWA1.MMD3 as R413314 in the system. And two years of printouts and revenue reports show that license number, the one the Government cares about and is required on all cannabis business transaction paperworks. A Manifest to License R413314 from October 2020 now prints out with R355469 in December 2020. The Name and Address is different as well!

Any folks reporting on the underlying data will now have reports change!! And it changes in the underlying LeafData system and their reporting as well.

That is, pulling transaction reports for R413314 in one month would suddenly show $0 the next month. Unless one is manually keeping track of these license number shifts.

Secondary Primary Key Reuse

For these cannabis businesses, and the government agency, the License Number is effectively a primary key. And when this shift is happening, a license number is being shifted on the same global_id what occurs is that a new record is introduced with the old license number. In this case WAWA1.MM2BT was created to represent R413314 when WAWA1.MMD3 was shifted to R355469.

None of the old transactions selling to R413314 (as WAWA1.MMD3) were updated to point to R413314 as WAWA1.MM2BT. All the old transactions to R413314 as WAWA1.MMD3 now show R355469, the re-prints will be much different! There was no effort made by the system to migrate the transactions to the new identifier.

Reality is that the seller sold to R413314 which is now closed, but the system now shows transactions to R355469.

It hurt itself in it's confusion

Another situation is if a license key like this is reused when the record moves to a different city or county. Business, in Washington, must report revenues in the city -- for cannabis this means where the delivery is made. If a business is in Shoreline but then moves a little south to Seattle and retains the same global_id then the sellers LeafData system will now have sales in Seattle vs where they actually took place at the time: Shoreline. This little discrepancy has a very real tax-burden shift for the business doing the sales.

Better Options

One method, which is very popular is to not try to reuse primary keys. And this system is a place where there are effectively two: the system global_id and the government license number. This creates an environment where the primary identifier must be one that represents the license at that point. So, when an address, name or license number for a business shifts it requires that a new identifier be generated and then only the records that matter would migrate forward (LeafData would operate this migration). This way, all the historical transaction details would retain accurate information at the time of their sale.

Another option is to snapshot the license details onto the transaction record at the time it's created. This would allow license records to mutate in odd ways, while reporting would able to work against point-in-time accuracy.

Resolution

NONE!! It's well known that the LeafData system will not be getting an code improvements. Developers just need to be aware that sometimes, when pulling up old transactions from this system it won't match the reference paperwork because of this issue. Also, it's important if licensees are getting reviewed by their enforcement team to understand why some printouts may not match what the system currently show.