Logistics System Database Patches

This document describes the database patch scripts used in the Lnder Logistics application. These patches are executed during application migration to update database structure, migrate data, or populate missing information.

---

1. Logistics Tracking Alert Log Migration

Purpose

This patch migrates tracking alert log data from the Logistics Tracking child table into the Logistics Tracking Alert Log doctype.

The migration is required to normalize alert data and improve performance by storing alert logs in a dedicated table.

Technical Workflow

1. Retrieve logistics tracking documents created within the last 10 days.
2. Iterate through each document’s logistics_tracking_log child table.
3. Generate a unique alert log name using the naming pattern:

   TDALT-.#####

4. Extract tracking data such as:
   • Timestamp
   • Latitude
   • Longitude
   • Speed
   • Vehicle status
   • Distance travelled
   • Alert flags
5. Prepare bulk insert rows for the new doctype.
6. Insert all records into Logistics Tracking Alert Log using frappe.db.bulk_insert().

Benefits

• Improves database normalization
• Reduces load on main tracking document
• Optimizes alert query performance

---

2. Telemetry Attachment Folder Creation

Purpose

Creates a dedicated folder named Telemetry_Attachment in the Frappe File system to store telemetry-related attachments.

Technical Workflow

1. Check if a folder named Telemetry_Attachment exists.
2. If the folder does not exist:
   • Create a new File document
   • Mark it as a folder
   • Set parent folder as Home
3. Insert the record directly using db_insert().
4. Commit database transaction.

Reason for Direct DB Insert

Using db_insert() avoids triggering file upload hooks such as S3 upload handlers.

---

3. Destination Safe Zone Time Update

Purpose

Updates delivery delay calculations for completed logistics trips.

The patch recalculates the time when the vehicle entered the destination safe zone.

Technical Workflow

1. Fetch Logistics Tracking records where:
   • Trip status is Completed
   • Expected start time is within the last day
2. Load each document.
3. Execute the method:

   update_delivery_delay()

4. Save the updated document.
5. Commit the database changes.

---

4. Trip Metrics Recalculation Patch

Purpose

Ensures that trip metrics values are populated for logistics tracking records that have missing calculations.

Metrics Updated

• Trip Start Odometer
• Trip End Odometer
• Odometer inside Safe Zone
• Fuel Consumption
• Trip Mileage

Technical Workflow

1. Fetch logistics tracking documents within the last two days.
2. Filter records where any trip metric fields are missing.
3. Queue background jobs using:

   frappe.enqueue()

4. Execute the background method:

   update_trip_metrics_value()

5. Run:
   • update_trip_metrics() → calculates trip metrics
   • set_missing_values() → fills missing derived values
6. Save the updated document.

Why Background Jobs

Trip metric calculation can be resource intensive. Running them asynchronously prevents migration timeout issues.

---

5. Vehicle Coordinate Telemetry Migration

Purpose

This patch retrieves telemetry data for logistics trips and stores simplified vehicle route coordinates in the tracking document.

Use Case

Used to populate route history for vehicles where telemetry data was not previously stored.

Technical Workflow

1. Fetch logistics tracking documents where:
   • Vehicle coordinates already exist
   • Vehicle make is not TATA MOTORS LTD
   • Trip started within the last 35 days
2. Reset the existing vehicle coordinates field.
3. Queue background jobs for telemetry retrieval.

Telemetry Retrieval Process

1. Determine tracking time window.
2. Call external telemetry provider via:

   intangles_connector.get_vehicle_location()

3. Convert epoch timestamps to datetime.
4. Extract telemetry fields:
   • Latitude
   • Longitude
   • Speed
   • Timestamp

Route Processing

The telemetry coordinates are simplified using the route simplification method:

simplify_route_points()

This reduces the number of stored coordinates while preserving the overall route geometry.

Data Storage

The processed coordinates are stored in the vehicle_coordinates field as JSON.

---

Summary

These database patches ensure the integrity and performance of the Logistics Tracking system by:

• Migrating alert logs to a dedicated table
• Creating required file system folders
• Recalculating delivery timing data
• Populating missing trip metrics
• Importing and simplifying telemetry route data

All heavy operations are executed asynchronously using Frappe background workers to prevent migration failures.