agmission/Development/server/docs/DATA_EXPORT_API_DESIGN.md

Data Export API — Design & Implementation Guide

Single Source of Truth: This is the canonical document for Data Export API design, implementation status, and next steps in this branch.

Branch: data-export-api
Date: April 10, 2026
Status: Phase A complete — Phase B in progress

Change Log

Date	Update
2026-04-14	Added ApiKeyServices and ExportUnits frozen constants to helpers/constants.js; wired throughout models and controllers. Added service field to ApiKey, units field to ExportJob, US unit conversion support to async export.
2026-04-10	Marked this file as the single source of truth for Data Export API design and implementation tracking.
2026-04-10	Consolidated documentation into this file and removed duplicate summary document.

---

1. Overview

The Data Export API allows authorised external systems (data warehouses, Power BI, ArcGIS) to pull mission data from AgMission on demand or on a scheduled basis. It exposes the same data already shown in the web application's Data Playback screen, served through a versioned REST API authenticated with API keys.

Two functional areas:

1. REST API (/api/v1/) — session summaries, per-point GPS trace, spray-area polygons, async bulk export
2. UI enhancement — improved Job List filter controls (order number, date range) and an API Key management screen in the web app settings

---

2. Architecture

2.1 Request Flow & Authentication Architecture

sequenceDiagram
    participant External as External System
    participant API as Express Server
    participant Auth as checkApiKey Middleware
    participant DB as ApiKey DB
    participant Handler as Route Handler
    
    External->>API: GET /api/v1/jobs/:id/sessions
    External->>API: Header X-API-Key
    API->>Auth: req.headers x-api-key
    Auth->>Auth: Extract prefix first 8 chars
    Auth->>DB: Find by prefix active=true
    DB-->>Auth: ApiKey candidates
    Auth->>Auth: bcrypt.compare plainKey vs keyHash
    Auth->>Auth: On match set req.uid
    Auth->>Handler: next with req.uid set
    Handler->>Handler: ownerJob verify req.uid
    Handler-->>External: JSON response

Web UI caller (JWT-authenticated, unchanged):

graph LR
    A[Web App] -->|Bearer token| B[checkUser Middleware]
    B -->|Verify JWT| C[req.uid set]
    C -->|api/keys routes| D[Key Management]
    D -->|CRUD ops| E[ApiKey Model]

2.2 Data Model Hierarchy

graph TD
    Job[Job model]
    App[App - Session data]
    AppFile[AppFile - Metadata]
    AppDetail[AppDetail - GPS points]
    ExportJob[ExportJob - Export tracker]
    ApiKey[ApiKey - Authentication]
    
    Job -->|has many| App
    App -->|has many| AppFile
    AppFile -->|has many| AppDetail
    Job -.->|triggers| ExportJob
    Job -.->|auth via| ApiKey
    App -.->|derived from| AppDetail
    style Job fill:#e1f5ff
    style App fill:#f3e5f5
    style AppFile fill:#fff3e0
    style AppDetail fill:#fce4ec
    style ExportJob fill:#e8f5e9
    style ApiKey fill:#f1f8e9

Fields summary:

• Job: jobId, byPuid, rptOp, weatherInfo, sprayAreas
• App: avgSpraySpeed, totalSprayed, totalSprayTime, totalFlightTime
• AppFile: meta (operator, appRate, fcName, sprOnLag), totalSprayed, totalSprayTime
• AppDetail: gpsTime, lat, lon, grSpeed, lminApp, swath, sprayStat, windSpd, temp, humid
• ExportJob: owner, jobId, format, status, filePath, expiresAt
• ApiKey: owner, keyHash, prefix, active, lastUsedAt

2.3 Route Prefix Strategy

Prefix	Auth	Purpose
/api/v1/	X-API-Key header (new checkApiKey)	Public data export endpoints
/api/keys	Authorization: Bearer (existing checkUser)	Key management for web UI
All other /api/...	Authorization: Bearer (existing checkUser)	Existing application routes — unchanged

The /api/v1/ path is added to the checkUser bypass whitelist (in isSecuredRoute()) so the existing JWT middleware skips these routes.

2.4 Async Export Lifecycle

stateDiagram-v2
    [*] --> pending: POST /export
    pending --> processing: async generate
    processing --> ready: success
    processing --> error: fail I/O error
    ready --> pending: download cleanup
    error --> [*]: TTL expiry
    ready --> [*]: 24h TTL
    pending --> [*]: TTL index

Lifecycle details:

• pending: ExportJob created and returned to caller; caller polls GET /exports/:id
• processing: Streams AppDetail cursor to CSV or GeoJSON format (memory-efficient)
• ready: File written to disk at filePath, TTL (expiresAt) set and ready for download
• error: Error message recorded, awaits manual retry via queue or TTL cleanup
• Cleanup: After file download completes, filePath cleared and status reset to pending for potential re-download

---

3. New Files

Backend

File	Status	Purpose
model/api_key.js	✅ Done	ApiKey Mongoose model
model/export_job.js	✅ Done	ExportJob tracking model
middlewares/app_validator.js	✅ Done	Added checkApiKey function + whitelist entry
routes/api_pub.js	✅ Done	/api/v1/ route definitions
routes/api_keys.js	✅ Done	/api/keys route definitions
routes/index.js	✅ Done	Registers api_pub and api_keys
controllers/api_key.js	✅ Done	createKey, listKeys, revokeKey
controllers/api_pub.js	✅ Done	getSessions, getSessionRecords, getAreas
controllers/api_export.js	✅ Done	triggerExport, getExportStatus, downloadExport
scripts/migrate_avg_spray_speed.js	✅ Done	One-time back-fill for existing jobs

Modified Files (existing)

File	Change
model/application.js	Added avgSpraySpeed: Number field
workers/job_worker.js	Accumulates avgSpraySpeed during file import at lines ~528, ~944–1090, ~1309–1381

Frontend (pending)

File	Status	Purpose
job-list.component.ts/.html	⬜ Pending	Add orderNumber filter input
src/app/settings/api-keys/	⬜ Pending	API Key management feature module

---

4. Model Designs

4.1 ApiKey (model/api_key.js)

Field	Type	Notes
owner	ObjectId → User	The applicator this key authorises
label	String	Human-readable name (max 100 chars)
prefix	String	First 8 chars of plain key — stored clear-text for O(1) candidate lookup
keyHash	String	bcryptjs hash of the full plain key — plain key never stored
service	ApiKeyServices	Which service the key grants access to: 'data_export' (default) or 'partner_api'
active	Boolean	Revoke by setting false
managedBy	'owner' | 'admin'	Who created the key
createdAt	Date
lastUsedAt	Date	Updated async (fire-and-forget) — no added request latency

Key lookup flow: prefix → find candidates → bcrypt.compare(incomingKey, candidate.keyHash) → match → set req.uid = key.owner.

Limit: 10 active keys per owner (enforced in createKey).

4.2 ExportJob (model/export_job.js)

Field	Type	Notes
owner	ObjectId → User	Scoped to requesting applicator
jobId	Number	AgMission job ID
format	'csv' | 'geojson'	Requested output format
interval	Number | null	GPS point thinning in seconds; null = all points
units	ExportUnits	Output measurement system: 'metric' (default) or 'us'
status	'pending' | 'processing' | 'ready' | 'error'	Lifecycle state
filePath	String	Absolute path on disk (set when ready)
errorMsg	String	Populated on error
createdAt	Date
expiresAt	Date	MongoDB TTL index — document auto-deleted after expiry

Files are written to env.TEMP_DIR. TTL defaults to 24 hours (EXPORT_TTL_HOURS env var).

---

5. API Endpoint Reference

5.1 Authentication

All /api/v1/ requests require:

X-API-Key: <full 64-char hex key>

No Authorization header needed. On failure the middleware returns 401.

---

5.2 GET /api/v1/jobs/:jobId/sessions

Returns one summary record per uploaded application file ("session") for the job.

Response shape (per session):

{
  "sessionId": "...",
  "fileName": "2507140724SatlocG4.log",
  "startDateTime": "2025-07-14T10:24:00Z",
  "endDateTime": "2025-07-14T11:05:42Z",
  "totalFlightTime_s": 2462,
  "totalSprayTime_s": 1840,
  "totalTurnTime_s": 622,
  "totalSprayed_ha": 48.3,
  "totalSprayMat": 120.5,
  "totalSprayMatUnit": 3,
  "avgSpraySpeed_ms": 14.2,
  "matType": "wet",
  "appRate": 2.5,
  "appRateUnit": "L/ha",
  "flowController": "SatLoc G4",
  "sprayOnLag_s": 0.2,
  "sprayOffLag_s": 0.15,
  "pulsesPerLiter": 1800,
  "mappedArea_ha": 50.0,
  "overSprayedPct": -3.4,
  "sprayZoneName": "Field A North",
  "sprayZoneArea_ha": 25.0,
  "pilotId": "...",
    "pilotName": "João Silva",
  "aircraftName": "Agrinova 01",
  "aircraftTailNumber": "PR-XYZ",
  "assignedDate": "2025-07-13T18:00:00Z",
  "sessionPilotName": "João Silva",
  "reportConfirmed": true,
  "areaSize_ha": 50.0,
  "coverage_ha": 48.3,
  "appRateConfirmed": 2.5,
  "sprayVolume": 120.75,
  "useActualVolume": false,
  "actualVolume": null,
  "effectiveVolume": 120.75,
    "useCustomWeather": false,
    "weather": null
}

Output field definitions (sessions endpoint)

Response envelope fields:

Field	Type	Required	Description
jobId	number	✓	Numeric job identifier from the URL path.
clientId	string | null	—	Client account ObjectId (the applicator's customer this job was performed for).
clientName	string | null	—	Client account name.
mappedArea_ha	number | null	—	Sum of all planned spray polygon areas for the job.
reportConfirmed	boolean	✓	True when report settings are confirmed (rptOp.coverage != null).
areaSize_ha	number | null	—	Confirmed area size, or fallback mapped area when not confirmed.
coverage_ha	number | null	—	Confirmed coverage, or fallback total sprayed area across sessions.
appRate	number | null	—	Confirmed app rate, or first-session fallback app rate.
appRateConfirmed	number | null	—	Confirmed app rate only; null when not confirmed.
sprayVolume	number | null	—	coverage_ha × appRate when both are numeric.
useActualVolume	boolean	✓	True when applicator selected actual volume override.
actualVolume	number | null	—	Manual override volume when useActualVolume=true.
effectiveVolume	number | null	—	Authoritative volume: actual or calculated spray volume.
useCustomWeather	boolean	✓	True when custom weather was manually entered.
weather	object | null	—	Weather block when custom weather exists; otherwise null.
customWeather	object | null	—	Removed — was alias of weather; use weather.
data	array	✓	Array of per-session summary records.

Per-session fields in data[]:

| Field | Type | Required | Description | |---|---|---| | sessionId | string | ✓ | Session identifier (App._id). | | fileName | string | null | — | Session file name from App.fileName. | | startDateTime | string | null | — | Session start datetime (ISO 8601 UTC). | | endDateTime | string | null | — | Session end datetime (ISO 8601 UTC). | | totalFlightTime_s | number | null | — | Total flight time in seconds. | | totalSprayTime_s | number | null | — | Total spray time in seconds. | | totalTurnTime_s | number | null | — | Total turn time in seconds. | | totalSprayed_ha | number | null | — | Total sprayed area in hectares. | | totalSprayMat | number | null | — | Total sprayed material amount. | | totalSprayMatUnit | string | null | — | Spray material unit label (e.g. "lit", "kg") — decoded from raw code via rateUnitString(). | | avgSpraySpeed_ms | number | null | — | Average spray speed in m/s. | | sprayZoneName | string | null | — | Zone/area name from AppFile.meta.areaOrZone. | | sprayZoneArea_ha | number | null | — | Zone area in hectares from AppFile.meta.sprCoverage[1]. | | appRate | number | null | — | Session target app rate from file metadata. | | appRateUnit | string | null | — | App rate unit label from job setting (canonical, matches top-level). | | matType | string | null | — | Material type (for example wet/dry). | | flowController | string | — | Flow controller name from file metadata. 'No FC' when absent or when the value is 'none' (case-insensitive), matching the playback display. | | sprayOnLag_s | number | null | — | Spray-on lag in seconds. | | sprayOffLag_s | number | null | — | Spray-off lag in seconds. | | pulsesPerLiter | number | null | — | Pulses-per-liter. | | files | array | ✓ | Session file list: [{ fileId, name }]. | | sessionPilotName | string | null | — | Pilot name recorded inside the imported data file. | | pilotId | string | null | — | Assigned pilot identifier from job operator relation. | | pilotName | string | null | — | Pilot name (assigned pilot on the job record). | | aircraftName | string | null | — | Assigned aircraft display name. | | aircraftTailNumber | string | null | — | Assigned aircraft tail number. | | assignedDate | string | null | — | Latest assignment timestamp (ISO 8601 UTC). | | reportConfirmed | boolean | ✓ | Repeated from top-level for row-level convenience. | | areaSize_ha | number | null | — | Repeated confirmed/fallback area size. | | coverage_ha | number | null | — | Repeated confirmed/fallback coverage. | | appRateConfirmed | number | null | — | Repeated confirmed app rate. | | sprayVolume | number | null | — | Repeated spray volume. | | volumeUnit | string | null | — | Volume unit label (e.g. "lit", "kg"). | | useActualVolume | boolean | ✓ | Repeated actual-volume toggle. | | actualVolume | number | null | — | Repeated actual volume override. | | effectiveVolume | number | null | — | Repeated effective volume. |

reportConfirmed Fallback Logic Diagram:

flowchart TD
    A{Is rptOp.coverage<br/>defined?}
    A -->|Yes| B["reportConfirmed=true"]
    A -->|No| C["reportConfirmed=false"]
    
    B --> D["Use Report Settings<br/>values"]
    C --> E["Compute from raw<br/>data"]
    
    D --> F{useActualVol?}
    E --> G{useActualVol?}
    
    F -->|Yes| H["effective=actual"]
    F -->|No| I["effective=coverage*rate"]
    
    G -->|Yes| J["effective=computed"]
    G -->|No| K["effective=computed"]
    
    H --> L["Confirmed block"]
    I --> L
    J --> M["Fallback block"]
    K --> M

Field	reportConfirmed: true	reportConfirmed: false
areaSize_ha	Job.rptOp.areaSize	Sum of sprayAreas[].properties.area
coverage_ha	Job.rptOp.coverage	Sum of App.totalSprayed
appRate	Job.rptOp.appRate	AppFile.meta.appRate (first session)
sprayVolume	coverage × appRate (from rptOp)	Same formula using fallback values
effectiveVolume	actualVol if useActualVol, else sprayVolume	sprayVolume (fallback)
weather fields	Job.weatherInfo.* when useCustWI=true	omitted

When reportConfirmed: false, re-fetch this record after the applicator confirms in Report Settings.

---

5.3 GET /api/v1/jobs/:jobId/sessions/:fileId/records

Per-point GPS trace records, cursor-paginated. Uses the same paginateWithCursor helper as the existing filesdata_post.

Query parameters:

Param	Default	Description
after	—	Cursor (_id of last record received) — preferred by customer requirements
startingAfter	—	Cursor (_id of last record received)
limit	500	Max records per page (hard cap: 2000)
interval	—	Return one record per N seconds of GPS time (e.g. 1, 5, 10)
fm	false	Set fm=true to include Flight Master/AgDisp FM fields (see below). Off by default — only for customers with FM-enabled equipment.

Field groups per record:

GPS Data: timeUtc, lat, lon, utmX, utmY, alt, grSpeed, heading, xTrack, lockedLine, hdop, satsInView, correctionId, waasId, sprayStat

Application Info: flowRateApplied, flowRateRequired, appRateRequired, appRateApplied*, swathWidth, boomPressure_psi, sprayOnLag_s†, sprayOffLag_s†, pulsesPerLiter†, rpm[]

MET: windSpeed_kt, windDir_deg, temp_c, humidity_pct

Compatibility aliases returned by implementation for existing consumers:

• None — aliases were removed; this is a new API with no existing consumers.

Output field definitions (records endpoint)

Response envelope fields:

Field	Type	Required	Description
data	array	✓	Array of per-point records after filtering/thinning.
has_more	boolean	✓	True when additional pages exist.
last_id	string | null	—	Cursor value for the next page (null on last page).
total_count	number | undefined	—	Optional total count when pagination helper provides it.

Per-record fields in data[]:

Field	Type	Required	Description
timeUtc	string | null	—	GPS timestamp formatted as ISO 8601 UTC.
gpsTime	number | null	—	Raw GPS epoch seconds.
lat	number | null	—	Latitude (WGS84 decimal degrees).
lon	number | null	—	Longitude (WGS84 decimal degrees).
utmX	number | null	—	UTM X coordinate in meters.
utmY	number | null	—	UTM Y coordinate in meters.
alt	number | null	—	Altitude in meters.
grSpeed	number | null	—	Ground speed in m/s.
heading	number | null	—	Aircraft heading in degrees.
xTrack	number | null	—	Cross-track error in meters.
lockedLine	number | null	—	Locked line index from guidance data.
hdop	number | null	—	Horizontal dilution of precision.
satsInView	number | null	—	Decoded satellites in view.
correctionId	number | null	—	Decoded correction identifier.
waasId	number | null	—	Decoded WAAS identifier when available.
sprayStat	number | null	—	Spray state (3 is filtered out before response).
flowRateApplied	number | null	—	Applied flow rate (L/min).
flowRateRequired	number | null	—	Required flow rate (L/min).
appRateRequired	number | null	—	Required app rate from source data.
appRateApplied	number | null	—	Computed app rate applied, null on zero-division.
swathWidth	number | null	—	Swath width in meters.
boomPressure_psi	number | null	—	Boom pressure in PSI.
sprayOnLag_s	number | null	—	Session constant, repeated per record.
sprayOffLag_s	number | null	—	Session constant, repeated per record.
pulsesPerLiter	number | null	—	Session constant.
rpm	array | null	—	RPM array from raw data.
windSpeed_kt	number | null	—	Wind speed in knots (converted from m/s on output to match playback display).
windDir_deg	number | null	—	Wind direction in degrees.
temp_c	number | null	—	Temperature in Celsius.
humidity_pct	number | null	—	Relative humidity percentage.

* appRateApplied is the only computed field: lminApp / (grSpeed × swath) × 10000. Returns null when grSpeed = 0 or swath = 0.
† Session constants from AppFile.meta — same value repeated on every record for flat-file consumers.

FM fields (included only when ?fm=true is set):

Field	Type	DB source	Description
sprayHeight_m	number | null	sprayHeight	Target spray height in metres (AgDisp).
driftX_m	number | null	driftX	Lateral drift offset X in metres (AgDisp).
driftY_m	number | null	driftY	Lateral drift offset Y in metres (AgDisp).
depositX_m	number | null	depositX	Deposit offset X in metres (AgDisp).
depositY_m	number | null	depositY	Deposit offset Y in metres (AgDisp).
radarAlt_m	number | null	radarAlt	Radar altimeter reading in metres.
laserAlt_m	number | null	raserAlt ¹	Laser altimeter reading in metres.

¹ The source DB field is named raserAlt (schema typo). The API exposes it as laserAlt_m with the correct name.

Record Decoding Transformation Pipeline:

graph LR
    A[Raw AppDetail] --> B[Interval Thinning]
    B --> C[Decode GPS Fields]
    C --> D[Compute appRateApplied]
    D --> E[Inject Session Meta]
    E --> F[Filter sprayStat=3]
    F --> G[Format ISO 8601 UTC]
    G --> H[Return API Record]
    
    style A fill:#fce4ec
    style B fill:#f3e5f5
    style C fill:#e8eaf6
    style D fill:#f3e5f5
    style E fill:#e0f2f1
    style F fill:#fce4ec
    style G fill:#fff9c4
    style H fill:#c8e6c9

Decoding rules:

• satsInView: raw satsIn > 99 → satsIn − 100
• correctionId: raw tslu > 100 → tslu − 100
• waasId: only set when calcodeFreq in range 20001–29999 → calcodeFreq − 20000
• sprayStat === 3 (spray segment START marker) is filtered out — it anchors the start position for the next area/distance computation but carries no application measurement; only spray-on records (sprayStat !== 3 && sprayStat > 0) represent actual application data
• appRateApplied = lminApp / (grSpeed × swath) × 10000; null when grSpeed or swath = 0

---

5.4 Public API Endpoint Architecture

graph LR
    subgraph External[External Callers]
        PBI[Power BI]
        ARCGIS[ArcGIS]
        DW[Data Warehouse]
    end
    
    subgraph PublicAPI[Public API /api/v1]
        SESSIONS[GET /sessions]
        RECORDS[GET /records]
        AREAS[GET /areas]
        TRIGEXP[POST /export]
        POLLEXP[GET /export-status]
        DOWNLOAD[GET /download]
    end
    
    subgraph Internal[Backend Models]
        APP[(App)]
        APPFILE[(AppFile)]
        APPDETAIL[(AppDetail)]
        EXPORTJOB[(ExportJob)]
    end
    
    PBI --> SESSIONS
    ARCGIS --> AREAS
    DW --> DOWNLOAD
    
    SESSIONS --> APP
    RECORDS --> APPDETAIL
    AREAS --> APP
    TRIGEXP --> EXPORTJOB
    POLLEXP --> EXPORTJOB
    DOWNLOAD --> EXPORTJOB
    
    SESSIONS --> APPFILE
    RECORDS --> APPFILE
    
    style External fill:#e3f2fd
    style PublicAPI fill:#f3e5f5
    style Internal fill:#e8f5e9

5.5 GET /api/v1/jobs/:jobId/areas

Returns the planned spray-area polygons as a GeoJSON FeatureCollection.

Output field definitions (areas endpoint)

Field	Type	Required	Description
type	string	✓	Always FeatureCollection.
jobId	number	✓	Numeric job identifier from path.
features	array	✓	Array of polygon features from planned spray areas.

Per-feature fields in features[]:

Field	Type	Required	Description
type	string	✓	Always Feature.
properties.name	string | null	—	Spray area name.
properties.appRate	number | null	—	Planned app rate for the area.
properties.area_ha	number | null	—	Planned area size in hectares.
properties.type	string | null	—	Area type metadata when present.
geometry	object | null	—	GeoJSON polygon geometry copied from job.sprayAreas.

Only implement / expose once customer confirms this is needed for ArcGIS layer import (pending).

---

5.6 Async Export

Trigger:

POST /api/v1/jobs/:jobId/export
Body: { "format": "csv", "interval": 1, "units": "us" }
→ 202 { "exportId": "...", "status": "pending", "units": "us" }

Body parameters:

Parameter	Required	Values	Default
format	Yes	'csv', 'geojson'	—
interval	No	seconds (e.g. 1, 5)	null (all points)
units	No	'metric' (ExportUnits.METRIC), 'us' (ExportUnits.US)	'metric'
fm	No	true / false	false — include Flight Master/AgDisp FM fields

Poll:

GET /api/v1/exports/:exportId
→ { "status": "processing" }   (repeat)
→ { "status": "ready", "downloadUrl": "/api/v1/exports/:id/download" }
→ { "status": "error", "errorMsg": "..." }

Download:

GET /api/v1/exports/:exportId/download
→ streams file with Content-Disposition: attachment

Output field definitions (export endpoints)

POST /api/v1/jobs/:jobId/export response fields (HTTP 202):

Field	Type	Required	Description
exportId	string	✓	Export tracker identifier.
status	string	✓	Initial export status (pending).
format	string	✓	Selected format (csv or geojson).
units	string	✓	Selected units (metric or us).
createdAt	string	✓	Export tracker creation timestamp (ISO 8601 UTC).

GET /api/v1/exports/:exportId response fields:

Field	Type	Required	Description
exportId	string	✓	Export tracker identifier.
status	string	✓	pending, processing, ready, or error.
format	string	✓	Export format.
units	string	✓	Export units mode.
createdAt	string	✓	Creation timestamp.
expiresAt	string | null	—	Expiry timestamp for downloaded file cleanup.
error	string | null	—	Error message when generation fails.
downloadUrl	string | undefined	—	Present only when status is ready.

GET /api/v1/exports/:exportId/download response:

Item	Value
Body	Streamed file content (CSV or GeoJSON).
Content-Type	text/csv or application/geo+json.
Content-Disposition	Attachment filename with format extension.

CSV structure: one row per AppDetail record. All raw trace fields plus job/session header columns (jobId, orderNumber, jobName, clientId, clientName, sessionId, fileName, pilotName) repeated on every row — no joins required for Power BI or data warehouse import. Column headers include unit suffix when units='us' (e.g. groundSpeed_mph vs groundSpeed_ms, temp_f vs temp_c).

US unit conversions (units='us'):

Metric field	US field	Factor
alt_m	alt_ft	× 3.28084
groundSpeed_ms	groundSpeed_mph	× 2.23694
crossTrackError_m	crossTrackError_ft	× 3.28084
swathWidth_m	swathWidth_ft	× 3.28084
flowRateApplied_Lmin	flowRateApplied_galMin	× 0.264172
flowRateRequired_Lmin	flowRateRequired_galMin	× 0.264172
appRateRequired_Lha	appRateRequired_galAc	× 0.10694
appRateApplied_Lha	appRateApplied_galAc	× 0.10694
windSpeed_kt	windSpeed_mph	× 1.15078 (kt → mph)
temp_c	temp_f	× 9/5 + 32
boomPressure_psi	boomPressure_psi	already PSI — no conversion

Implementation: Node.js Transform stream over AppDetail cursor → writes to env.TEMP_DIR. Keeps memory flat regardless of file size. interval thinning applied identically to the records endpoint.

---

5.7 Key Management Endpoints (Web UI, JWT-authenticated)

Method	Path	Description
GET	/api/keys	List active keys for the signed-in applicator
POST	/api/keys	Create a key — returns full plain key once in the response
DELETE	/api/keys/:keyId	Revoke a key (sets active: false)

Key management body (POST /api/keys):

{ "label": "Power BI Prod", "service": "data_export" }

service is optional and defaults to 'data_export'. Valid values are defined in ApiKeyServices in helpers/constants.js.

Admin users may append ?ownerId=<ObjectId> or include ownerId in the POST body to manage keys for another account.

---

6. avgSpraySpeed — Storage Strategy

Rather than computing average spray speed on demand (which would require scanning all AppDetail records for every session summary request), it is computed once at import time and stored in App.avgSpraySpeed.

Accumulation logic in job_worker.js:

// Per GPS point during file parsing (in importDataFiles, per-file pass):
// sprayStat=3 is the segment START marker — saves prevPos for next area calc but is
// not an actual spray record; exclude it from speed averaging.
if (record.sprayStat !== 3 && record.sprayStat > 0 && utils.isNumber(record.grSpeed)) {
  totalSpeedAcc += record.grSpeed;
  spraySpeedCount++;
}
// At end of file:
importInfo.avgSpraySpeed = spraySpeedCount > 0 ? totalSpeedAcc / spraySpeedCount : null; // m/s

One-time back-fill: scripts/migrate_avg_spray_speed.js — iterates existing App docs via cursor, re-scans their AppDetail records, bulk-writes the value. Safe to run on production (cursor-based, low memory, progress logging every 100 docs).

---

7. Frontend Design

7.1 Job List Filter Enhancement (Step 3 — pending)

File: src/app/job/job-list/job-list.component.ts

Add an orderNumber text filter control to the existing filter bar alongside client, status, and date pickers. Wire into the existing Job.Fetch() NgRx action that calls jobService.loadJobs(). Minor backend check: ensure searchJobs_post / getJobs_get accepts orderNumber as a partial-match filter.

7.2 API Key Management UI (Step 8 — pending)

New lazy-loaded feature module following the same NgRx pattern as PartnerListComponent / ClientListComponent.

Structure:

src/app/settings/api-keys/
  api-keys.module.ts
  api-keys-routing.module.ts
  api-keys-list/
    api-keys-list.component.ts
    api-keys-list.component.html
  store/
    api-key.actions.ts
    api-key.reducer.ts
    api-key.effects.ts
  services/
    api-key.service.ts

UX flow:

1. PrimeNG p-table listing keys — columns: Label, Prefix, Created, Last Used, Status
2. "Generate Key" button → calls POST /api/keys → shows full key in a p-dialog with copy-to-clipboard — key masked after dialog is closed, never retrievable again
3. "Revoke" button per row → p-confirmDialog → calls DELETE /api/keys/:id
4. Admin view: additional applicator selector (p-dropdown) to manage keys on behalf of any account

---

8. Implementation Status

Step	Feature	Status	Notes
1	App.avgSpraySpeed — model field + import worker + migration script	✅ Done	model/application.js, workers/job_worker.js, scripts/migrate_avg_spray_speed.js
2	ApiKey model + checkApiKey middleware + /api/keys CRUD	✅ Done	model/api_key.js, middlewares/app_validator.js, routes/api_keys.js, controllers/api_key.js. ApiKeyServices frozen constant controls valid service values.
3	Job List UI filter enhancements	⬜ Pending	Frontend only — job-list.component
4	GET /api/v1/jobs/:id/sessions — session summary	✅ Done	controllers/api_pub.js getSessions
5	GET /api/v1/jobs/:id/sessions/:fid/records — raw trace	✅ Done	controllers/api_pub.js getSessionRecords
6	GET /api/v1/jobs/:id/areas — spray-area GeoJSON	✅ Done	controllers/api_pub.js getAreas — awaiting customer confirmation to expose
7	Async export (POST /export, GET /exports/:id, download)	✅ Done	model/export_job.js, controllers/api_export.js. ExportUnits frozen constant controls valid units values; US unit conversions applied at output time.
8	API Key management UI (Angular)	⬜ Pending	New settings/api-keys feature module
9	Sandbox seeding script	⬜ Pending	scripts/seed_sandbox.js
—	Tests	⬜ Pending	checkApiKey unit tests, session summary integration tests

---

9. Key Design Decisions

Decision	Rationale
Separate checkApiKey middleware (not extending checkUser)	Zero risk to existing JWT-protected routes; req.uid set identically so all ownership filters work unchanged
prefix stored clear-text in ApiKey	O(1) candidate row lookup before expensive bcrypt.compare; prefix alone is not usable as a key
ApiKeyServices frozen constant for service enum	Single source of truth in helpers/constants.js; adding a new service type requires editing the constant only — model and controller stay in sync via Object.values()
ExportUnits frozen constant for units enum	Same principle — consistent with project convention for all enumeric text constants
avgSpraySpeed stored at import, not computed on demand	Session summary endpoint must never touch AppDetail (billion-scale collection); O(1) read from App model
Cursor pagination on AppDetail._id	Consistent with existing filesdata_post pattern; no skip-based offset that degrades on large collections
interval thinning on both records endpoint and export	Consistent behaviour; reduces Power BI payload for overview queries; daily batch export at 17:00 can use interval=1 to shrink CSV size significantly
reportConfirmed boolean + always-populated fallback	Consumer's data warehouse always has a usable record; can upsert when field flips to true
Async export with TTL (ExportJob.expiresAt + MongoDB TTL index)	Files self-clean after 24 hours; no manual housekeeping job needed
CSV columns include job/session header repeated per row	Direct Power BI / warehouse import without requiring a separate join step
Unit conversion at output time, not at storage	Raw data stored in metric throughout; conversion applied in recordToRow() with unit-labelled column headers so output is self-documenting

---

10. Constraints & Notes

• All API responses use metric units by default (ha, m/s, L/min, L/ha, Kg/ha, °C, metres). Callers may request US customary output via units: 'us' on the export endpoint — see Section 5.6.
• All dates/times are ISO 8601 UTC strings.
• Coordinates are WGS84 decimal degrees (EPSG:4326) — numerically equivalent to SIRGAS 2000 (EPSG:4674) for Brazil.
• AppDetail.sprayStat === 3 is a spray segment START marker — it records the anchor position (UTM X/Y, swath, line number) that the worker uses to compute the area of the next spray segment. It carries no application measurement and is filtered from all public API responses. Spray-on application records are sprayStat > 0 && sprayStat !== 3.
• AppDetail.raserAlt (typo in source schema) is exposed as laserAlt_m in the API.
• rpm[] array semantics differ between liquid and dry material types — consumers must use matType from the session summary to interpret correctly.
• Pending: customer confirmation on whether GET /api/v1/jobs/:id/areas (spray-area GeoJSON) is required for their ArcGIS workflow — endpoint is implemented but not yet scheduled for release.