canteen-seed-import/seed-data/reference_handbook_filled.md

reference_handbook

🌱 Seed Data Import — Reference Handbook

This handbook documents all extraction rules, logic, and reference data for the canteen seed data import pipeline.

Sections marked FILL_IN need your domain expertise. Sections marked ✅ READY I’ve pre-filled from data analysis.

---

1. Customer Column → Company/Domain

Status: ✅ READY

The Customer column in the seed data maps directly to the company field in the database. It’s already clean — just the business name. No parsing needed.

Disney Customers (prefix D-)

Customers starting with D- are Disney properties. These map to disney_park:

Customer prefix / pattern	Disney Park	Category
D-Magic Kingdom	magic-kingdom	Park
D-Epcot	epcot	Park
D-Hollywood Studios	hollywood-studios	Park
D-Animal Kingdom	animal-kingdom	Park
D-Disney Springs	disney-springs	Park
D-ContemporaryHotel	resort	Resort
D-POLYNESIAN RESORT	resort	Resort
D-Port Orleans	resort	Resort
D-CORONADO SPRINGS	resort	Resort
D-ART OF ANIMATION	resort	Resort
D-POP CENTURY	resort	Resort
D-All Star ...	resort	Resort
D-DISNEY WORLD SS	office	Support services

FILL_IN: Add any Disney customer patterns I missed:

---

2. Place Column → Location Fields

Status: ✅ READY (patterns identified)

The Place column has two main patterns — the extraction engine must detect which pattern applies and parse accordingly.

Pattern A: Simple — ~90% of rows

Format: Venue Name-Venue Detail (dash-separated, repetitive)

Examples:

Sygma - Vending-Sygma - Vending Breakroom
Imperial Dade-Imperial Dade- Breakroom
Ancora Apt.-Ancora Apt. Breakroom
Delamarre-Delamarre Breakroom
BMW Service-BMW Breakroom
Kisselback Ford-Kisselback Ford Breakroom
Sam's Club-Sam's Club
BREAK AREA-BREAK AREA
Building 1180-Building 1180

Rule: Take the last meaningful segment (after the last dash). Strip:

• Breakroom, Br, Break Room, BREAKROOM, BREAK AREA
• Vending Area, Vending
• Trailing whitespace and punctuation

Pattern B: Complex Disney — ~10% of rows

Format: PersonName - Address-G-Zone FLOOR

Examples:

Todd J - 901 Tinberline Dr-G-REAR 6TH FL
Carrianne C - 1251 Riversida Dr-G-PO RIVERSIDE 80S
Justin M - 1536  Buena Vista-Team Disney North Win
Penny D - 3520 Ft Wilderness-G-BUS STOP
Sarah W - 4401 Floridian Way-G-BUILDING 9 LOBBY
Brenda G - 2101 Epcot Resorts-Hotel by Room 1290
Jeremy B - 1960 Broadway-2nd FL Brkrm Vacat Club D

Extraction steps:

1. Strip person name prefix: <FirstName> <Initial> or <FirstName> <LastInit> -

2. Strip address segment (number + street name)

3. Extract floor info: NTH FL, Nth floor, FL N, 1ST FL, etc.

4. Extract Building info: B N, Bldg N, Mermaid N, etc

5. Whatever remains is the clean place name

Edge Cases

FL8108-Vending Area                          → place="FL8108", no venue
1st FL Wing-1st Fl Breakroom                 → floor="1st Fl", place="Wing"
Epcot-Imagination                            → place="Imagination"
C-MK EASTGATE SECURITY-Be Our Guest          → place="Be Our Guest"
Southern Tech University:Sun Life-Sun Life   → place="Sun Life" (remove university prefix)

Floor Extraction Patterns

Pattern	Example	Normalized
NTH FL	6TH FL	6th Floor
Nth floor	7th floor	7th Floor
FL N	FL 2	Floor 2
NST FL	1ST FL	1st Floor

FILL_IN: Add any special Place patterns or corrections you know about:

---

3. GPS Derivation for Multi-Floor Machines

Status: ⚠️ NEEDS FILL_IN

Base GPS Strategy

For machines with no GPS data, we need to derive coordinates:

1. Use existing GPS from other machines at the same address

Floor Offset Rules

When a base GPS coordinate is found for the building, adjust for floor:

FILL_IN: What offset values should we use per floor?

• Does the lat/lng change measurably per floor? No, Machines are usually in the same area directly above. Usually, not always

GPS Priority

When multiple machines exist at the same address:

1. Use existing GPS from the DB (field-collected is authoritative)
2. Look for other machines on the same floor, on floors above or below, and in the same building
3. Never overwrite existing GPS (preserve policy)

---

4. Serial Number Validation & OCR Correction

Status: ✅ READY (data analyzed)

Standard Serial Formats

Format	Count	Example	Notes
All digits (10-12 chars)	1,220	168020939, 471011958	Crane standard format
Starts with 1 (10+ digits)	175	1-16018993, 112034419	AMS/Crane prefixed
Starts with 222	various	222001280013	Newer Crane format
Crane model-prefixed	various	472-052119, 449-011276	Model + serial
All digits shorter	various	124473010109	USI/Mercato format

Likely OCR Errors Found

Machine ID	Raw Serial	Suspected Correct	Pattern
60017	15S491811573	155491811573	S → 5
60025	15S491811581	155491811581	S → 5
60028	I5s331807595	155331807595	I → 1, s → 5
60032	I5SS331807593	1555331807593	I5SS → 1555

Known Character Confusions

Mistyped	Should Be	Context
S	5	In numeric serials — S looks like 5 in some fonts
s	5	Same — lowercase s scanned as 5
I	1	Capital I looks like 1
O	0	Letter O vs zero
B	8	B scanned as 8 (less common)
G	6	G scanned as 6 (less common)

FILL_IN: Add any other character confusions you’ve seen:

• ___________ → ___________
• ___________ → ___________

Placeholder / Invalid Serials

Machine ID	Serial	Action
60031	520	Flag — likely incomplete or placeholder
68194	123	Flag — clear placeholder
99660	00	Flag — clear placeholder

FILL_IN: How should we handle flagged serials? clear it.

---

Unknown Machine Identification by Serial

FILL_IN: How can we identify an unknown machine from its serial number?

• What do different serial prefixes mean? We can look at known good serials and compare with makes/models to determine structure
• Can we cross-reference with Make/Model/Type? We look at known good machines, and look at their serials to figure out structure
• Any known serial → machine type mappings?

---

---

5. Type/Class/Make/Model Consolidation

Status: ✅ READY (data analyzed)

The Priority Rule

The Type column is the best source for Class/Make/Model. Parse it first, then fill gaps from the standalone columns.

Type Column Format

CLASS (MAKE MODEL) — e.g., Snack (AMS Sensit 3)

Extraction:

"Snack (AMS Sensit 3)"
    → Class = "Snack"
    → Make  = "AMS"
    → Model = "Sensit 3"

"GF Food (Crane 472)"
    → Class = "Food"
    → Make  = "Crane"
    → Model = "472"

"Bev (Royal GIII)"
    → Class = "Bev"
    → Make  = "Royal"
    → Model = "GIII"

"Unknown"
    → All three = "Unknown" (leave as-is)

Make sure that if the Class, make, model, etc has a GF prefix then strip it.

Type Patterns Found

Type Pattern	Count	Class	Make	Model
Bev (Royal GIII)	299	Bev	Royal	GIII
Snack (Crane Merchant Media)	287	Snack	Crane	Merchant Media
Bev (Vendo 621/721/821)	235	Bev	Vendo	621/721/821
GF Food (Crane 472)	151	Food	Crane	472
Snack (Crane 15x/16x)	76	Snack	Crane	15x/16x
Unknown	67	Unknown	Unknown	Unknown
GF Bev (DN 200E)	49	Bev	DN	200E
GF Bev (DN 5800)	35	GF Bev	DN	5800
Snack (Crane 186)	34	Snack	Crane	186
GF Bev (DN BevMax 4)	34	GF Bev	DN	BevMax 4
Bev (DN 501E/600E/276E)	34	Bev	DN	501E/600E/276E
Snack (Crane 472)	26	Snack	Crane	472
Bev (DN 276E)	25	Bev	DN	276E
GF Bev (DN 3800 BevMax 4)	20	GF Bev	DN	3800 BevMax 4
GF Bev (DN Baby BevMax)	19	GF Bev	DN	Baby BevMax
Bev (DN 501E)	18	Bev	DN	501E
GF Bev (DN BevMax)	18	GF Bev	DN	BevMax
Snack/Bev (Crane 472)	16	Snack/Bev	Crane	472
Snack (Crane 187)	16	Snack	Crane	187
NATIONAL - 168 SERIES	16	Snack	Crane	168
VENDO - 721	14	Bev	Vendo	721
Snack (AMS 3561)	12	Snack	AMS	3561
Snack (USI Mercato)	10	Snack	USI	Mercato
Snack (VE)	10	Snack	VE	Unknown
NATIONAL - 186	10	Snack	Crane	186
NATIONAL - 187	10	Snack	Crane	187

Special Parse Cases

Raw Type	Rule
NATIONAL - 168 SERIES	Class=Snack, Make=Crane (National=Crane brand), Model=168
VENDO - 721	Class=Bev (if Type doesn’t have Class), Make=Vendo, Model=721
Snack (no parens)	Class=Snack, check Make/Model columns for fill
Bev (no parens)	Class=Bev, check Make/Model columns
Unknown	Leave all Unknown

Filling Unknowns

After parsing Type, check the standalone Make and Model columns to fill gaps. If Type-based Make is empty but standalone Make has a value, use it. If both are empty/Unknown, flag for review.

FILL_IN: Are there any Type→Make/Model mappings I’ve missed?

Make Normalization

FILL_IN: Consolidate these make variants:

Raw Value	Normalized To
Crane, Crane Co, Crane Nat, NATIONAL	Crane
DN, Dixie Narco	DN
Royal, Royal Vendors	Royal
Vendo, Vendo Co	Vendo
USI, U Select It	USI
AMS, Automatic Merchandising	AMS
VE, Vending Equipment	___________
AP, Automatic Products	___________
Faz, Fas	___________

Class Normalization

Raw Value	Normalized To
Snack, Snack/Food	Snack
Bev	Bev
GF Food	GF Food
GF Bev	GF Bev
Snack/Bev	Snack/Bev
Food	GF Food — verify this

FILL_IN: Any other class consolidations?

---

6. Remote Pricing Status — Meanings

Status: ✅ READY (data analyzed)

Distribution

Status	Count	Meaning
On	614	Remote pricing is active and working normally
Action Required	718	Remote pricing needs attention — may have failed, needs configuration, or other issue
Incompatible	516	Machine does not support remote pricing (older model, EEEPROM Upgrade Required)

FILL_IN: Clarify what each status actually means in practice:

On — Working

Action Required — No Dex Passcode / Bad Coil Count

Incompatible — achine does not support remote pricing (older model, EEEPROM Upgrade Required

Are there any other status values not present in this data?

---

---

7. Telemetry ID → Card Reader Decoder

Status: ✅ READY (data analyzed)

Telemetry Provider Patterns

Telemetry ID Pattern	Provider	Card Reader Type
VJ... (ePort)	ePort (USI/Crane)	ePort telemetry module
K3CT... (ePort)	ePort (Crane)	ePort telemetry module
VK... (ePort)	ePort	ePort telemetry module
... (NAYAX)	Nayax	Nayax card reader
... (CMS)	Crane Merchandising Systems	CMS telemetry/reader
All-numeric (no suffix)	Unknown	Unknown/other

Distribution

Provider	Count
ePort	924
CMS	628
Nayax	275
Other/unknown	21

FILL_IN: More detail on what each provider means for card readers:

ePort —Looknuo cantaloupe devices_______________________________________________________________

NAYAX — look up nayax devices_______________________________________________________________

CMS — Crane integrated and external solutions_________________________________________________________________

FILL_IN: Do these map to specific credit card reader models?

Provider	Reader Brand	Reader Model
ePort	Cantaloupe___________	___________
NAYAX	Nayax___________	___________
CMS	Crane___________	___________

---

8. Alert Meanings

Status: ✅ READY (data analyzed)

Alert Types Found

Alert Text	Count	Severity	Meaning
(empty)	majority	none	No alerts
This machine has been scheduled for service on Monday, May 25, 2026	many	⚠️ info	Scheduled maintenance
This machine is scheduled for service today	several	⚠️ info	Same-day service scheduled
Out of touch for 19 hours - contact Crane Merchandising Systems for assistance	several	🔴 critical	Telemetry offline > 19 hours
Out of touch for 19 hours... (combined with service notice)	few	🔴 critical	Multiple issues

FILL_IN: Categorize these alerts properly. What severity should each get?

Alert	Severity (Info/Warning/Critical)	Display in app?
Scheduled service in future	___________	___________
Scheduled service today	___________	___________
Out of touch (telemetry lost)	___________	___________
Out of touch + service needed	___________	___________

FILL_IN: Are there other alert patterns that could appear?

---

---

9. Sales Priority Scoring

Status: ⚠️ NEEDS INPUT

The current import_seed.csv has no sales data columns — they’re marked as “Deleted” in the header map.

Available Alternative Signals

Without sales data, we can score priority using:

Signal	What it tells us	Priority hint
Class = "GF Food"	Perishable food — needs frequent service	Higher priority
Class = "GF Bev"	Perishable drinks — needs frequent service	Higher priority
Remote Pricing = "Action Required"	Needs technical attention	Higher priority
Alerts = "Out of touch"	Telemetry down	Higher priority
Customer = Disney	High-traffic, high-visibility	Higher priority
Type = "Unknown"	Unknown machine — needs identification	Lower priority

FILL_IN: How do you want to score priority?

Criteria	Priority (Low / Mid / High)
GF Food or GF Bev class	___________
Action Required pricing	___________
Critical alert (out of touch)	___________
Disney property	___________
Active pricing (On) + Snack class	___________
Unknown Type/Make/Model	___________
Incompatible pricing	___________

FILL_IN: Do you have sales data from a separate export? Where does it come from?

---

FILL_IN: If sales data is available, what are the priority thresholds?

Annual Sales Range	Priority
$_______ - $_______	Low
$_______ - $_______	Mid
$_______+	High

---

10. Asset List & Detail Screen Spec

Status: ⚠️ NEEDS FILL_IN

Asset List Card

Each asset card in the main app list should show:

FILL_IN: What fields belong on the asset card?

• Machine ID
• Name
• Make / Model
• Location (place, building, floor)
• Priority badge (low/mid/high)
• Status badge (active/maintenance/retired)
• Remote Pricing status
• Disney park badge
• Alert indicator
• GPS coordinates badge (has/don’t have)
• Last contact / last dex time
• Card reader type
• Other: _______________

FILL_IN: Priority badge colors?

Priority	Color
Low	___________
Mid	___________
High	___________

Asset Detail Screen

Full detail view should show:

FILL_IN: Section layout for the detail screen (order and contents):

FILL_IN: Which sections should be editable?

FILL_IN: Should the approval workflow fields be editable here too?

---

---

11. Update Policy — Per-Field

Status: ⚠️ NEEDS FILL_IN

For each field, specify how updates from new seed data should be handled:

DB Column	Source CSV Column	Update Policy	Notes
machine_id	Machine ID	preserve	Primary key, never changes
serial_number	Serial Number	___________
name	— (generated)	___________
make	Type→Make (parsed)	___________
model	Type→Model (parsed)	___________
class*)	Type→Class (parsed)	___________
company	Customer	___________
place	Place (parsed)	___________
building_name	Place (parsed)	___________
building_number	Place (parsed)	___________
floor	Place (parsed)	___________
room	Place (parsed)	___________
trailer_number	Place (parsed)	___________
address	Address	___________
location_area	City	___________
latitude	— (derived)	preserve	Field-collected is authoritative
longitude	— (derived)	preserve	Field-collected is authoritative
photo_path	—	preserve	Never overwrite
dex_report_date	Last Dex Report Time	___________
install_date	Added Date	___________
deployed	—	___________
pulled_date	—	___________
disney_park	Customer (parsed)	___________
remote_pricing_status	Remote Pricing	___________
alerts	Alerts	___________
telemetry_provider	Telemetry ID (parsed)	___________
card_reader_brand	Telemetry ID (parsed)	___________
card_reader_model	Telemetry ID (parsed)	___________
priority	— (scored)	___________
prepick_group	Prepick Group	___________

• class is not a current DB column — needs to be added via migration.

Policy values: auto (always overwrite), approval (queue for review), preserve (never touch)

FILL_IN: Add or change any policies above.

---

Appendix: Column Mapping

Raw Excel → CSV → DB Mapping

Xlsx Column	CSV Column	DB Column	Notes
Device	Telemetry ID	—	Parsed to provider/brand
Location	Location	—	Redundant (Customer + Address)
Asset ID	Machine ID	machine_id	Primary key
Place	Place	place + derived fields	Main extraction target
Type	Type	—	Parsed to Class/Make/Model
City	City	location_area	Direct map
Address	Address	address	Direct map
Last Contact Time	Last Contact Time	—	Freshness indicator
Last Dex Report Time	Last Dex Report Time	dex_report_date
Prepick Group	Prepick Group	—	Routing info
Customer	Customer	company	Direct map
Route	Route	—	Routing info
State	State	—
Postal Code	Postal Code	—
Serial Number	Serial Number	serial_number	Validate + correct
Class	Class	—	Source for class
Make	Make	make (fallback)	Type column takes priority
Model	Model	model (fallback)	Type column takes priority
Added Date	Added Date	install_date
Status	Remote Pricing	—	Direct to app
Alerts	Alerts	—	Parsed to structured alerts

FILL_IN: Any missing columns or corrections?

---