The data model of a compliant operation — and why spreadsheets fail it

Ask most operators how their compliance data is structured and the honest answer is: it isn't, really. It's a set of spreadsheets, a lab portal, a dispensing system, and a shared drive, each holding a piece of the picture, none of them designed to relate to each other.

That's not a criticism of the people running the operation. Spreadsheets are genuinely excellent tools — for tabular data that doesn't need to prove its own history. Compliance data is a different animal. It needs to capture relationships, sequences, and immutability, none of which a spreadsheet was built to do.

What needs capturing at every custody transfer

A compliant data model for a licensed cannabis operation needs, at minimum, the following captured at every point custody changes:

• Batch identity — a unique, regulator-compliant ID, assigned once and never reassigned.
• Timestamp — the moment of transfer, sealed so it can't be edited retrospectively without the seal breaking.
• Actor identity — who initiated and who received the transfer, tied to a real accountable person or role, not a generic department.
• Location — where the batch physically was and is now, including facility and, where relevant, jurisdiction.
• Lab linkage — the certificate of analysis relevant to that batch state, cross-referenced, not filed separately.
• Genealogy — the parent batch or batches, and any child batches created by splitting or combining.

That's six relational fields, minimum, per event, for every event in a batch's life. A spreadsheet can technically hold all six as columns. What it can't do is enforce that they're always populated, always linked correctly to the right parent record, and never silently edited after the fact.

Why spreadsheets fail specifically

The failure mode isn't dramatic. It's mundane, and that's what makes it dangerous. A cell gets overwritten because someone corrected what they thought was a typo. A row gets deleted during a "cleanup." A new tab gets created for this quarter's batches and the cross-reference back to last quarter's parent batch quietly breaks. None of these are malicious. All of them break the chain the moment they happen, and none of them leave a visible trace that anything broke.

A spreadsheet can't tell the difference between "this figure was always 42" and "this figure was edited to 42 last Tuesday." A compliant data model has to make that difference visible, every time.

Multiply that by however many operators, dispensaries, and facilities are contributing to the same batch's history, and the odds that every cell, every tab, every cross-reference stayed intact for the batch's entire life — cultivation through dispensing or export — get worse with every additional hand that touches the record.

What a proper model buys you

A data model purpose-built for custody, rather than adapted from a general-purpose spreadsheet, changes what's possible operationally. Queries that would take an afternoon of manual cross-referencing — "show me every batch this lab certified in the last quarter," "trace every descendant of this parent batch," "list every custody event this facility logged this week" — become instant, because the relationships were captured as relationships, not reconstructed from flat rows after the fact.

It also changes what a regulator can ask and expect an immediate answer to. The gap between "we'll have that for you by Friday" and "here it is" is the gap between a data model that was designed for compliance and one that's being asked to do a job it was never built for.

The multi-facility version of the same problem

The spreadsheet model degrades further the moment more than one facility or more than one jurisdiction is involved. Each site tends to accumulate its own version of the same tracking spreadsheet, with its own tab conventions, its own naming quirks, its own person who happens to know where things are. Reconciling those independently-evolved spreadsheets into one coherent picture of a multi-site batch's history is exactly the kind of manual, error-prone exercise that a relational data model exists to make unnecessary.

The problem compounds again across jurisdictions, because each jurisdiction's format expectations differ, and a spreadsheet has no native concept of "the same underlying data, presented in three regulator-specific shapes." Each format becomes its own manual export, maintained separately, drifting slowly out of sync with the others every time someone updates one but not the rest.

What "relational" actually buys, concretely

It's worth being specific about what changes when custody data is modelled relationally rather than kept as flat rows. A batch record that properly links to its lab certificate, its custody events, and its genealogy means a single contamination flag on a lab result can automatically surface every batch, every facility, and every downstream transfer that certificate touches — without anyone needing to know in advance which spreadsheets to check.

It means an auditor's question about a specific actor — "show me every transfer this facility manager approved in the last quarter" — is answerable as a filter on existing data, not a research project. And it means that when a regulator's format requirements change, as they periodically do, the underlying data doesn't need to be re-entered into a new template; only the export layer needs to change, because the relationships were captured once, correctly, at the source.

The tooling question isn't really about spreadsheets versus software. It's about whether the underlying data model can represent relationships, sequence, and immutability as first-class properties — or whether it's forcing a fundamentally relational, temporal problem into a flat, editable grid, and hoping the discipline of the people using it makes up the difference.

Discipline helps. It isn't a substitute for a data model that makes the compliant answer the only answer the system can give.