Shipment Locations Service Internals

This staff-facing article describes how the shipment-locations capability of the Tai TMS runs as its own standalone service with its own database, how it geocodes full street addresses, how the main TMS calls it, how the "latest location" is chosen, and the access and deployment model behind it.

Overview

The shipment-locations capability has been fully extracted from the main TMS application and runs as its own standalone service. It owns two things: shipment location visibility (the history of where a shipment or load has been — origin, in-transit points, destination) and geocoding (turning addresses and coordinates into map coordinates and readable location descriptions).

The extraction is complete. The standalone service is now the only store of shipment location rows. The old in-process implementation inside the main application has been deleted, and the main application's own location tables, the database trigger that fed a pending-changes queue, and the background jobs that synchronized the two sides have all been removed. The earlier migration arrangement — two implementations running side by side, double-writing, and a queue-based sync — no longer exists. The main application now reaches the service only over HTTP.

The service has its own database, one per environment, sitting beside the main application's database rather than inside it. It exposes an internal HTTP interface for location reads and writes and for geocoding. It is not customer-facing; it is called by the main TMS on the customer's behalf.

How it works

Its own database per environment. The service reads and writes a database that belongs to it, separate from the main application's database. Production, beta, and demo each have a dedicated locations database next to the corresponding main-application database, and developers use a local one. The only table the service owns is the shipment-location history. The postal-code reference data and the "last known location" cache table stay owned by the main application and are intentionally not modeled by the service.

How the main TMS calls the service. The main application calls the service directly over HTTP, using a bearer token for each call, through client components that are composed behind wrapper services. The wrapper services add the behavior that belongs to the main application: publishing a "location updated" event, writing shipment activity-log entries, and maintaining the last-known-location cache. The HTTP client is configurable: a base URL, a request timeout (30 seconds by default, overridable by setting), and a bearer-token provider. Bulk merge, bulk delete, and bulk geocode requests are sent in chunks of 2,000 shipments or rows, and the service itself deletes in chunks of 50 shipments. A "not found" response from the service is translated into an empty result rather than an error; any other non-success response or transport failure becomes an exception that carries the request and response detail. These client calls run synchronously over the underlying asynchronous calls, which carries a deadlock risk if ever invoked from a context that has a synchronization context.

Geocoding via a maps provider, using the full street address. The service geocodes through Google Maps. It geocodes the full street address directly — it does not look a location up by postal-code centroid — so a complete address is placed at its actual position rather than at the center of a ZIP code. It can also reverse-geocode raw coordinates back into a readable "city, state postal code country" description. A live location update that arrives as coordinates is reverse-geocoded by the main application's wrapper, which then writes it to the service as an in-transit point. When a pickup or delivery stop is added or its address is edited, the wrapper geocodes the stop's address and adds the point (it first requires the stop's postal code to be found in the main application's postal-code data for that country, and silently does nothing otherwise); for a first-pickup or last-drop stop, an edited address first deletes the old origin or destination point.

No geocode cache. The service used to keep a private geocode cache and resolve addresses to a ZIP centroid. That cache table and all of its logic have been removed, and its migration drops the table. The service now geocodes the street address each time. The only reuse is that, when a location text has already been geocoded on any shipment (a non-informational entry with coordinates), the service reuses that stored coordinate rather than calling the maps provider again. There is no standalone cache to warm, inspect, or invalidate.

Write behavior. There is a single write mode now. Adding a location optionally applies a time-based duplicate check (the caller supplies a window in minutes) and then inserts. The bulk merge endpoint upserts on the natural key of shipment, location text, and location time. Neither path uses the old main-application location identifier any more; it was removed from every request and write path, and the leftover column and its filtered unique index only cover rows from before the cutover.

Duplicate detection. When a window is supplied, the service compares the new entry with the newest existing entry for the same shipment, matching on coordinates when both are present and otherwise on the location text. If the new entry's time is earlier than that match's time plus the window, the new entry is dropped and the matched entry is returned instead (so an out-of-order older reading is dropped too). The window is chosen by the caller: the standard update path (manual entry, carrier tracking, electronic messages, the public API, check calls, and similar) uses 5 minutes; driver tracking and the DAT marketplace feed use 4 minutes; one live-position feed passes zero, which only drops out-of-order older readings; and the reverse-geocoded "current location" path passes no window at all. With no window the service always inserts. Duplicate checking is the only guard against duplicates, because the database has no unique constraint on the natural key.

Reads follow shipment stage. When asked for the stage-aware latest location, the service returns: for shipments before in-transit (quote through dispatched), the geocoded origin; for in-transit or out-for-delivery, the newest entry across all point types; for delivered or complete, the geocoded destination; and for canceled, nothing. This stage-aware read is what the main application uses for outbound webhook and file-feed updates. Other reads — such as shipment detail lookups through the public API and carrier check-call handling — ask for the newest entry regardless of type.

Geocoding on read. When locations are read with stop context, the service geocodes any entry that has text but no coordinates (grouping by text so each distinct text is geocoded once), and it compares the origin and destination stops' addresses with the stored endpoint entries, geocoding the stop address when it does not match and the stored entry has not already failed. If there is no stored endpoint entry it inserts one using the stop's scheduled date, or 8 a.m. today when the stop has none.

Carrier tracking feeds locations. Each carrier tracking check that the main application applies writes an in-transit point labeled as coming from the carrier connection, dated by the carrier's last-updated time, else the actual delivery time, else the current time (UTC). A tracking result with neither location text nor coordinates is not sent to the service at all.

Business rules & limits

Own database, not the main application's. The service has its own database per environment. It owns only the shipment-location history table. The postal-code reference data (which has many other consumers) and the separate "last known location" cache table remain owned by the main application; the service does not model them. Follow-on actions such as event notifications, activity logging, and last-known-location updates are performed by the main application's wrapper components, not by the service.
No double-write and no sync queue. Every location read and write goes through the service over HTTP. There is no longer a main-application copy of the location tables, a change-tracking trigger, a pending-changes queue, or a synchronization job. A large data fix therefore no longer queues behind a sync backlog; it simply goes through the bulk merge endpoint.
No geocode cache. The former geocode cache table and its logic have been removed. The service geocodes the full street address every time; the only reuse is matching an existing stored coordinate for the same location text. Do not look for cache-first behavior or a cache to invalidate.
Geocoding uses the full street address. Addresses are resolved at street level, not by postal-code centroid, so a complete address is placed at its actual location. The maps provider is Google Maps. Only the United States, Canada, and Mexico are recognized: a blank country defaults to the United States, and a result in any other country is labeled as the United States. An address that reduces to nothing but a country is not geocoded.
Latest location depends on how it is asked. The stage-aware latest location (origin before transit, newest in transit, destination when delivered or complete, nothing when canceled) differs from the plain newest-entry read. The bulk "latest locations" read has two forms: without a cutoff it returns the newest entry per shipment with no filtering; with a cutoff it excludes origin and destination points and requires a recent, successfully geocoded entry (coordinates present and no prior failed geocoding attempt). A separate bulk read returns only the newest in-transit entry with coordinates per shipment, and it is the source for the main application's last-known-location reads.
"Newest" is defined two ways. Most reads, the duplicate check, and delete-by-text use the highest location time; the remaining-miles update, the recent-geocode pass, and the location-history read use the most recently created entry. With a back-filled or future-dated entry these point at different rows.
Access model. Every endpoint is protected by a single global policy that requires both an authenticated internal token and membership in an approved group. The token is a bearer token issued by the corporate Azure Active Directory identity provider; a valid token whose group membership is not on the approved list is rejected. The exceptions are the anonymous liveness probes (the root address, a health address, and a second health route), which return a simple healthy response with no authentication, and the API documentation pages, which are only exposed when explicitly enabled (on in beta and developer settings, off in production and demo). A separate authenticated health check also confirms database connectivity. Authentication can be bypassed only in a development environment, and only when an explicit development setting is turned on; it is never bypassed outside development.
Database migrations are applied out-of-process. The service does not migrate its own database on startup in deployed environments. A separate migration application is run as a deployment step to apply pending schema changes; the migrate-on-startup setting is switched on only in the developer configuration. This keeps schema changes decoupled from service startup and under deployment control. The migration chain also includes the step that drops the old geocode cache table.
Failed geocodes are marked and not auto-retried. When an on-read geocode of an existing entry finds nothing, the service records a failed-attempt marker on that entry (and on every entry that shares the same text), and the automatic re-geocode paths then skip it. A permanently bad address will therefore not be retried automatically; it must be corrected to resolve. For a first-pickup or last-drop stop, editing the stop's address deletes the old endpoint point and geocodes the new address from scratch, which is what actually fixes it. Only "no result" counts as a failed attempt: a maps-provider error is logged and surfaces as a server error (HTTP 500) with no marker set. The bulk recent-geocode pass leaves an unresolved row unchanged without setting a marker, so it will try again on a later pass.
Coordinate precision and text length. Coordinates are stored to six decimal places; more precise values are truncated to that precision by the storage format. The location text is truncated to 255 characters.
Deletes. A delete request must specify exactly one of a point type or a location text. Delete-by-text removes only in-transit entries with that text for the shipment and returns the newest remaining entry; delete-by-type removes all entries of that type. The main application's endpoint-deletion helper only honors origin and destination.
Updating remaining miles. The remaining-miles update changes only the miles value on the most recently created entry for the shipment; it does not touch the text, coordinates, or type.
Bulk operations. The bulk merge de-duplicates its input on the natural key keeping the last, loads the matching existing rows, updates all the data columns (including local time, remaining miles, and driver status) on a match, and inserts otherwise; rows with no location text always insert. The older low-volume batch upsert silently ignores local time, remaining miles, and driver status, so it should not be used when those matter. Despite the name, bulk operations are ordinary tracked database work: a commercial bulk-data library is still referenced and its license is still loaded at startup, but no current code path calls it.
Local guard in the main application. The main application does not call the service for a location that has neither coordinates nor location text, because the service would reject it as an invalid request (HTTP 400). The service itself also rejects such a request and requires a location time and a point type on every add.
Last-known-location staging is in the main application. After a modifying write, the wrapper updates the main application's last-known-location cache. The cached location text is set only when the written point is an in-transit entry with non-blank text, so origin, destination, and informational points and stop-address geocoding can never become the last known location, and a null value never erases a stored one. A remaining-miles update writes miles only. Deleting by text removes the cache row when nothing remains, or re-stages from the newest remaining entry. Retention of these cache rows is a background cleanup job.
Time handling is not uniform. Reverse-geocode and current-location writes are stamped in UTC, carrier-tracking points default to UTC now, the geocode-and-add path for stops defaults a missing time to local time, and endpoint entries created on read default to 8 a.m. server-local today. Staff investigating time discrepancies should be aware of this offset-versus-local mix.
Errors. Any unhandled error in the service is logged and returned as a generic HTTP 500 problem response with a trace identifier.

Frequently asked questions

Where does the shipment-locations capability actually run, and against which database?

It runs as its own standalone service with its own database, provisioned one per environment (production, beta, and demo each have a dedicated locations database, and developers use a local one), sitting beside — not inside — the main application's database. The service is now the only store of shipment location rows; the in-process copy in the main application and its tables have been removed.

Is there still a geocode cache?

No. The former geocode cache table and all of its read/write logic have been removed. The service now geocodes the full street address every time rather than resolving to a postal-code centroid. The only reuse is that a location being added or resolved can pick up a coordinate already stored on an existing row with the same text. There is nothing to warm, inspect, or invalidate.

How does a location entered in the main TMS get into the service's database?

Directly over HTTP. The main application's wrapper calls the service as it saves (and for GPS-style updates it reverse-geocodes first, then writes an in-transit point). There is no longer a double-write, a change-tracking trigger, a pending-changes queue, or a synchronization job, so there is no backlog that can delay live updates.

How does the duplicate check work, and what window is used?

When the caller supplies a window, the service compares a new entry with the newest existing entry for the same shipment (matching on coordinates when present, otherwise on the location text) and drops the new entry when its time is earlier than that entry's time plus the window. Most callers use 5 minutes, driver tracking and the DAT feed use 4, one live-position feed passes zero (so only out-of-order older readings are dropped), and the reverse-geocoded current-location path passes no window, which always inserts.

Which "latest location" does a given screen or feed show?

It depends on the caller. The stage-aware read — origin before transit, newest update while in transit or out for delivery, destination when delivered or complete, nothing when canceled — is used for outbound webhook and file-feed updates. Shipment detail lookups through the public API and check-call handling simply take the newest entry regardless of type. The "last known location" column in shipment lists and reports comes from the main application's cache, which only ever holds in-transit entries that have location text.

How is access to the service controlled?

Every endpoint except the liveness probes (and the documentation pages when enabled) requires an authenticated bearer token from the corporate Azure Active Directory identity provider and membership in an approved group. A valid token without approved group membership is rejected. Authentication can only be disabled in a development environment via an explicit setting, never in production.

How are database schema changes applied?

Out-of-process. In deployed environments the service does not migrate itself on startup; a separate migration application is run as a deployment step to apply pending schema changes. This keeps schema changes under deployment control and decoupled from service startup.

Why does a bad address stop getting map coordinates even after time passes?

Because the service marks an entry whose address failed to geocode, and the automatic re-geocode paths then skip it, so it is not retried indefinitely. Resolving it requires correcting the address — editing a first-pickup or last-drop stop's address deletes the old endpoint point and geocodes the new one — because time alone will not cause a retry. A maps-provider error is different: it fails the request with a server error and does not set the marker.

Which data does the service own versus the main application?

The service owns the shipment-location history table in its own database. The main application keeps ownership of the postal-code reference data (shared by many consumers) and of a separate "last known location" cache table that the service intentionally does not model. Follow-on actions, such as event notifications, activity-log entries, and last-known-location updates, are performed by the main application's wrapper components.

What happens when the service returns "not found" to the main application?

The main application's HTTP client translates a "not found" response into an empty result rather than surfacing it as an error, so callers treat a missing shipment or location as simply having no data. Any other non-success response or a transport failure is raised as an error that carries the request and response details.

Why did a carrier tracking update not create a location point?

The main application only sends a point to the service when the tracking result has location text or coordinates; a result with neither is skipped. A point that is within the duplicate window of an identical recent point for the same shipment is also dropped. When a carrier reports a location with no dates, the point is stamped with the current time rather than an empty date.