Design system Capture Coding Policy Approve Storage

Data model · Version 1

One report, four legal entities, one immutable reference each.

The model that replaces the Excel workbook. An employee files one report per calendar month; a single report charges lines to any combination of the active entities. Every line is paid out of pocket and reimbursed — there are no cards and no bank feeds in this model.

14 tables 4 entities 19 cost centres 9 categories 2 tax jurisdictions Money in integer cents

01 · Proof

Nine criteria, executed against the real model.

Not a description of the criteria — the harness builds a report, submits it, renames an entity underneath it and reports what actually happened. Criterion 3 re-codes CANNAWAY and MERA, then checks that every reference already issued is still byte-identical.

02 · Structure

Fifteen tables, and what holds them together.

schema.sql is the authoritative contract — constraints and triggers, not conventions. js/model.js enforces the same invariants in the browser so the prototype cannot create a row the database would reject.

Table Holds Key relationships

03 · Entities

First-class, and deliberately still on the legacy names.

Exactly as Finance's current books record them. Avana Labs Inc. (prefix LAB) is divested and deliberately absent.
Picker label Code Prefix Cost centre Default tax Status

The picker, as every screen must render it

Selecting an entity that requires a cost centre reveals the field below.

04 · Cost centres

Nineteen CannaWay centres — eight of them Manitoba.

Modelled per-entity so any other entity can adopt cost centres later as seed data. A centre's jurisdiction overrides its entity default, which is the whole mechanism behind criterion 5.

Names are stored exactly as supplied. Four have no site code — Finance has not issued one.
Code Name Resolves to Rate

05 · invoice_ref

Issued once, stored as a string, never recomputed.

entity.invoice_prefix plus a zero-padded sequence, scoped per (report, entity) — M01, CCC01, CAN01, GL01. Finance will re-map entity codes and prefixes at handoff, and every reference already printed into a receipt filename in their archive has to survive that untouched.

While DRAFT

Provisional

Deleting a line renumbers the remaining lines of that (report, entity) and reissues their references. Nothing has reached Finance, so closing the gap is safe. This is the only window in which a reference may change.

At submission

Frozen

invoice_ref_frozen_at is stamped. From then on the reference, sequence, entity and the issue-time snapshots are immutable, and a submitted line cannot be deleted — a gap must never appear after the fact.

Forever after

Independent of the taxonomy

Renaming or re-coding an entity is an UPDATE on one row of entity. Nothing reads through to a reference, so nothing can move it. A retired prefix is never re-pointed or reused.

Issue-time snapshots

A line also stores entity_prefix_at_issue and entity_legal_name_at_issue. A stamped PDF generated in June carries the legal name that was current in June; without the snapshot, regenerating it after a July rename would produce a document that disagrees with the one already filed.

06 · Attachments

The employee never marks up or renames a receipt.

No handwritten reference code, no filename convention, no manual indexing. They upload the raw file; the system assigns and tracks the identifier. The receipt-to-line link is a foreign key, never a mark on the image.

ORIGINAL

The audit artifact

Never modified, never overwritten, never stamped, never re-encoded, no baked-in rotation. Integrity is verified by SHA-256 checksum against the stored bytes. Triggers block both mutation and deletion.

DERIVED_STAMPED

A separate PDF

Generated alongside, linked by derived_from_attachment_id, carrying a footer band with the reference, entity legal name, expense date, vendor, claimed gross and employee name. Finance gets a visibly-referenced document without anyone writing on paper.

canonical_filename

Export-time metadata

<FirstName><LastInitial>-<YYYYMMDD>-<invoice_ref> — so Finance still recognises the pattern they use today. original_filename is preserved unchanged alongside it.

07 · Partial claims

A receipt is frequently only partly claimable.

net_amount, tax_amount and gross_amount always hold the claimed figures. receipt_total_gross holds the printed total, so the difference stays permanently visible and auditable — and a line can never claim more than its receipt shows.

The six claim bases, and what each one requires.
claim_basis Means Also required

08 · Open

What was not supplied, left empty rather than invented.

Each of these is a data change once Finance provides the value. Where a missing value would silently produce a wrong number — a mileage rate, a commute offset — validation blocks submission instead of defaulting to a guess.

Not in this version, by instruction