shawn
908c67a26c
- Cleared all MSFS-derived (address-geocoded) GPS - Only real field GPS (28 assets from photo EXIF) remains - Documented backup locations and clean-slate process Refs #46
15 KiB
MSFS Data Import — End-to-End Pipeline
How Microsoft Dynamics 365 Field Service data was extracted, merged with Cantaloupe data, and imported into the Canteen Asset Tracker.
Date: 2026-05-28 (initial), 2026-05-28 (clean-slate reset) Assets in DB: 9,636 Assets with real GPS: 28 (field-collected, not address-geocoded) GPS backup:
/tmp/gps_restore.sql(28 UPDATE statements by machine_id) Pre-import backup:assets.db.20260528_225934.pre-msfs-importNote: All GPS from MSFS (address-geocoded) was cleared during the clean-slate reset. Only real field GPS (from technician photo EXIF) remains.
1. Extraction from Dynamics 365
The Dynamics 365 Field Service mobile app (Android) on a rooted Pixel 4a was the data source. The offline SQLite databases stored on the device contain full schema snapshots — msdyn_customerasset, msdyn_workorder, account, bookableresourcebooking, and 167+ other tables.
1.1 Phone → Disk
# Pull the main offline DB from the device
adb shell "su -c cp /data/data/com.microsoft.crm.crmphone.fieldServices/databases/msfs_backup.db /sdcard/"
adb pull /sdcard/msfs_backup.db data-samples/
# 1.7 GB SQLite database, pulled 2026-05-28
1.2 Dynamics 365 API Extraction (Live)
A Service Principal application was registered in Entra ID (same tenant as Field Service) with client credentials flow. The dynamics_token.py manager auto-refreshes tokens stored in pass.
# Get a token and pull equipment, accounts, work orders via Dataverse WebAPI
python3 dynamics_token.py # Outputs Bearer token
python3 query_booking_gps.py # Booking GPS from live API
Results from live API:
| Entity | Records |
|---|---|
Equipment (msdyn_customerasset) |
14,440 |
Accounts (account) |
5,305 |
Work Orders (msdyn_workorder) |
15,531 |
1.3 Photo Extraction
3,703 technician photos were extracted from the offline DB's annotation / activitymimeattachment binary blobs to disk at web/static/photos/. These are actual field photos taken by technicians on their phones, containing:
- EXIF GPS from the phone camera (176 photos)
- ConnectID stickers and barcode images
- Work order photos of machines at customer sites
2. Merge (MSFS + Canteen)
2.1 The Merge Script
standalone-merge joins MSFS and Canteen records by serial number (hsl_serialnumber). No other common key exists.
Source tables from MSFS:
msdyn_customerasset— equipment records with serials, GPS, manufacturer, model, ConnectIDmsdyn_workorder— work order history linked to assetsaccount— customer account details with geocoded addresses
Source from Canteen:
- Old
assets.db(1,848 records from Cantaloupe CSV import)
2.2 Join Logic
# standalone-merge → merge()
all_serials = set(msfs_assets) | set(canteen_assets)
for serial in sorted(all_serials):
if msfs and canteen: match = "joined" # Both sources have this asset
elif msfs: match = "msfs_only" # Only MSFS knows about it
else: match = "canteen_only" # Only Cantaloupe import has it
Fields are prefixed by source: msfs_* for MS Field Service fields, canteen_* for Cantaloupe import fields. Work orders are attached per-asset.
2.3 Merge Results
| Metric | Count |
|---|---|
| Total merged records | 10,038 |
| Joined (both sources, matched by serial) | 1,832 |
| MSFS-only (no Cantaloupe match) | 8,204 |
| Canteen-only (no MSFS match) | 2 |
| Work orders linked to assets | 9,652 |
| Assets with work orders | 3,199 |
| Accounts resolved | 3,486 |
| Customers resolved | 284 |
| Locations resolved | 364 |
Timing: The "MSFS-only" dominance means the old Cantaloupe import had very limited coverage. Most of the 8,204 MSFS-only assets are from Dynamics-only equipment not managed in the Cantaloupe system.
3. Field Mapping
3.1 GPS Resolution (Priority Order)
When importing into the assets table, GPS coordinates are resolved in this order:
- MSFS latitude/longitude from
msdyn_customerasset(if present) - Canteen latitude/longitude from old imports (if MSFS missing)
- If both missing →
NULL(no GPS — 9,608 assets fall here)
The GPS in msdyn_customerasset itself is geocoded account addresses, not real device GPS. Real EXIF GPS from technician photos would need the OCR pipeline (see §4).
3.2 Field Mapping Spec
assets column |
Source | MSFS field |
|---|---|---|
machine_id |
msfs_machine_id → canteen_machine_id | Extracted from hsl_equipmentid suffix |
serial_number |
serial_number | hsl_serialnumber |
name |
msfs_name → canteen_name | msdyn_name |
make |
msfs_manufacturer | hsl_manufacturertext |
model |
msfs_dex_model | hsl_dexmodel |
latitude / longitude |
msfs → canteen | msdyn_latitude / msdyn_longitude |
address |
account.address | From account table |
connect_id |
msfs_connect_id | hsl_connectid |
canteen_connect_guid |
msfs_canteen_connect_guid | hsl_canteenconnectguid |
manufacturer |
msfs_manufacturer | hsl_manufacturertext |
equipment_id |
msfs_equipment_id | hsl_equipmentid |
company |
canteen_company | Old Cantaloupe import |
category |
canteen_category | Old Cantaloupe import |
install_date |
canteen → msfs | hsl_opendate |
3.3 Output JSON
web/static/data/merged-assets.json — 10,038 records (159 MB) with full field mapping, work order attachments, account/customer/location resolution, and GPS coordinates.
4. OCR & Photo EXIF GPS Pipeline
4.1 Problem
- 9,608 of 9,636 assets have no real GPS
- Existing GPS is geocoded addresses (account-based, not actual machine location)
- 176 photos contain real EXIF GPS from technician phone cameras
4.2 Approach
The plan in docs/plans/2026-05-28-photo-exif-gps-pipeline.md outlines a multi-pass OCR pipeline:
- Tesseract OCR on all 3,703 photos — 4-tier cascade (scales, preprocessing modes, PSM variants)
- Vision API fallback (
mimo-v2-omni) for Tesseract failures - Machine ID extraction from ConnectID stickers (
XXXXX-YYYYYY), serial plates, barcodes - Cross-reference OCR results with photo EXIF GPS
- Write high-confidence updates to
assets.dbwithgps_source = 'photo_exif'
Current status: 201 photos OCR'd as proof of concept (5.4%), 14 unique machine IDs identified.
4.3 Key Scripts
| Script | Purpose |
|---|---|
ocr_local.py |
Tesseract 4-tier local OCR cascade |
ocr_batch.py |
Vision API batch OCR (OpenCode Go) |
ocr_mapping.py |
OCR results → machine ID → canteen asset mapping |
consolidate_gps.py |
Multi-source GPS consolidation |
_ocr_analyze.py |
OCR failure analysis |
standalone-merge |
MSFS → Canteen merge |
5. Import Script
import_msfs.py
The stand-alone import script located at ~/projects/canteen-asset-tracker/import_msfs.py performs:
- Backup existing
assets.db→assets.db.{timestamp}.pre-msfs-import - Read
merged-assets.json(10,038 records) - Create fresh
assets.dbwith full v2 schema (18 tables, indexes, triggers) - Map merged fields to assets table columns per the mapping spec above
- Deduplicate by
machine_idwith priority: joined > msfs_only > canteen_only - Insert categories, customers, locations from merged data
- Insert default users (admin/technician)
- Verify with table/row/index counts
Run
cd ~/projects/canteen-asset-tracker
python3 import_msfs.py
DB Stats After Import
Assets total: 9,636
Assets with GPS: 28
Categories: 21
Customers: 284
Locations: 364
Users: 2 (admin, tech)
Table count: 18
Index count: 6
DB size: ~11 MB (WAL mode)
6. Extraction DB (Live Route Planning)
The extraction DB is linked from the canteen asset tracker server for live work order and route optimization queries:
# server.py → _get_extraction_db()
EXTRACTION_DB = (
Path.home()
/ "projects/ms-field-service-extraction"
/ "data-samples/msfs_backup"
/ "fieldservicecanteen.crm.dynamics.com_9fc6c50c-b097-f011-b4cb-7ced8d1b15a2_data.db"
)
Five endpoints use this:
| Endpoint | Purpose |
|---|---|
GET /api/workorders/search |
Paginated search by name/account/city |
POST /api/workorders/lookup |
Bulk lookup by work order ID/name |
GET /api/workorders/today |
Today's active bookings by technician |
GET /api/workorders/technicians |
Distinct technician list |
POST /api/route/optimize |
TSP route optimization (nearest-neighbor + 2-opt) |
7. File Locations
| Artifact | Path |
|---|---|
| MSFS backup DB | ~/projects/ms-field-service-extraction/data-samples/msfs_backup/ |
| Merged JSON | ~/projects/ms-field-service-extraction/web/static/data/merged-assets.json |
| Live API data | data-samples/dynamics_equipment.json (14,440), dynamics_accounts.json (5,305), dynamics_workorders.json (15,531) |
| Photos | ~/projects/ms-field-service-extraction/web/static/photos/ (3,703 files) |
| OCR results | web/static/data/ocr_results.json |
| Merge script | ~/projects/ms-field-service-extraction/standalone-merge |
| Import script | ~/projects/canteen-asset-tracker/import_msfs.py |
| Canteen SQLite DB | ~/projects/canteen-asset-tracker/assets.db |
| Pre-import backup | assets.db.20260528_214316.pre-msfs-import |
| GPS consolidation | ~/projects/ms-field-service-extraction/consolidate_gps.py |
| GPS consolidated JSON | web/static/data/gps_consolidated_update.json |
| Data catalog | ~/projects/ms-field-service-extraction/DATA_CATALOG.md |
| Photo EXIF GPS plan | docs/plans/2026-05-28-photo-exif-gps-pipeline.md |
8. Architecture Diagram
┌─────────────────────────────────────┐
│ Rooted Pixel 4a │
│ (Shawn Canada's device) │
│ MS Field Service Mobile App │
│ Offline SQLite (1.7 GB, 167+ tbls) │
└──────────────┬──────────────────────┘
│ adb pull
▼
┌─────────────────────────────────────┐
│ MSFS Backup DB │
│ data-samples/msfs_backup/ │
│ - msdyn_customerasset (14K rows) │
│ - msdyn_workorder (15K rows) │
│ - account (5K rows) │
│ - annotation (photos, 3.7K blobs) │
└──────────┬──────────────────────────┘
│
┌────────────────┼────────────────────┐
▼ ▼ ▼
┌─────────────────┐ ┌─────────────┐ ┌─────────────────┐
│ photo extraction │ │ merge │ │ Dynamics API │
│ 3,703 JPEGs │ │ standalone │ │ (Service Prin.) │
│ EXIF GPS → 176 │ │ -merge │ │ 14K equipment │
│ → OCR pipeline │ │ serial join │ │ 15K work orders │
└────────┬─────────┘ └──────┬──────┘ └─────────────────┘
│ │
▼ ▼
┌─────────────────────────────────────────────┐
│ merged-assets.json │
│ 10,038 records │
│ 1,832 joined / 8,204 MSFS-only │
│ 9,652 work orders linked │
└──────────────────┬──────────────────────────┘
│ import_msfs.py
▼
┌─────────────────────────────────────────────┐
│ assets.db │
│ 9,636 assets (was 1,848) │
│ 28 with GPS │
│ 18 tables │
│ 6 indexes │
└──────────────────┬──────────────────────────┘
│ server.py links for
│ live route/work order
▼
┌─────────────────────────────────────────────┐
│ Canteen Asset Tracker App │
│ → Find Asset (barcode/OCR lookup) │
│ → Map (Leaflet, GPS pins) │
│ → Route (TSP optimization, extraction DB) │
│ → Nav (OSRM driving/walking) │
└─────────────────────────────────────────────┘
9. Future Work
The Photo EXIF GPS Pipeline (see docs/plans/2026-05-28-photo-exif-gps-pipeline.md) is designed to add real GPS to the remaining 9,608 assets:
| Task | Description | Status |
|---|---|---|
| 0 | Diagnose OCR bottleneck (why only 14/3,703 photos yield IDs) | ⬜ |
| 1 | Fix OCR regex for ConnectID, serial, barcode formats | ⬜ |
| 2 | Batch OCR all 3,703 photos with Tesseract (all photos, not just GPS) | ⬜ |
| 3 | Vision API OCR on Tesseract failures | ⬜ |
| 4 | Multi-source disambiguation (timestamps + location) | ⬜ |
| 5 | Push verified GPS into assets.db with source tracking |
⬜ |
| 6 | Verify and visualize on map | ⬜ |
Each photo with EXIF GPS that can be matched to a machine ID gives us a real device GPS coordinate — far more accurate than the current geocoded addresses.