Asset Sync from MSFS Extraction

After every MSFS data pull, sync_assets_to_canteen.py syncs customer asset data from the extraction DB into the Canteen Asset Tracker's assets.db.

How it works

The script is called automatically from run_full_sync() in pull_msfs_data.py as Phase 5 of the sync pipeline:

Load extraction DB (msdyn_customerasset + account tables)
Index existing assets.db records by serial_number and connect_id
Match each extraction asset by serial_number → update or create
Fallback match by connect_id if serial doesn't match

Field Update Policies

Policy	Fields	Behavior
Always Sync	connect_id, equipment_id, canteen_connect_guid, barcode, manufacturer, model, route_name, install_date, device	Always overwritten with MSFS data (source-of-truth)
Only if Empty	company, customer_name, place, address	Only filled if currently NULL/empty (user enrichment)
Name Smart	name	Only updated if current name is a bare numeric machine ID (Cantaloupe default). User-set descriptive names preserved.
Never Sync	latitude, longitude	GPS is never touched — preserved from user check-ins only
Set All	All fields	Set for new assets regardless

Matching Logic

Primary key: serial_number ↔ hsl_serialnumber (10,854 extraction assets with serials)
Fallback: connect_id ↔ hsl_connectid (all extraction assets have connect_id)
Deduplication: If multiple extraction records share the same serial, the most recent (by createdon DESC) is used

First Run Stats

Metric	Before	After	Change
Total assets	9,343	10,143	+800
With company	7,349	9,376	+2,027
With model	~1,200	10,142	+8,942
With manufacturer	~900	10,119	+9,219
With equipment_id	7,488	10,119	+2,631
With route_name	0	1,783	+1,783
With place	5,647	7,743	+2,096

Running Manually

cd ~/projects/ms-field-service-extraction
python3 web/sync_assets_to_canteen.py           # Dry run
python3 web/sync_assets_to_canteen.py --apply   # Write changes
python3 web/sync_assets_to_canteen.py --apply --stats  # + detailed stats

New Asset machine_ids

MSFS-only assets (not previously in assets.db) get their equipment_id as their machine_id:

VM-05912-0000095122 (777 assets)
CE-14586-0000590768 (6 assets)
ME-35086-0000068001 (4 assets)
MP-35090-0000027289 (3 assets)
NV-522301 (10 assets)