# 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

```mermaid
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):**

```mermaid
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

```mermaid
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

```mermaid
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):**

```json
{
  "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:**

```mermaid
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:**

```mermaid
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

```mermaid
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`):**
```json
{ "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`:**
```javascript
// 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.