Data Storage and Archiving Internals

This staff-only article explains how the Tai TMS stores its data across several separate databases, how reporting is served from a read-only copy, how the shipment-locations service owns its own database (and the main database no longer holds location rows), how aged activity data is archived through a single write path, and how the platform handles brief database hiccups.

Overview

The Tai TMS does not keep everything in one database. For each tenant, the platform separates its data into several purpose-built databases so that heavy, high-volume, or historical data does not slow down the day-to-day operational system. There is a main transactional database for live work, a separate database for logs, a separate archive database for aged-out historical activity, and a read-only reporting copy used to run reports without loading the live system. The standalone service that owns shipment location visibility has its own database, separate from the main one, and the main database's own location tables have been dropped. Older activity data is periodically moved into the archive to keep the operational database lean, and the platform automatically retries brief, transient database errors so that momentary blips do not become user-visible failures. This article is internal context for staff who support data, reporting, and archiving; it is not customer-facing.

How it works

The platform uses distinct databases, each with its own data and its own purpose:

• Main (operational) database. The live, transactional system of record. This is where active shipments, billing, and everyday work live and are read and written throughout the day.
• Logging database. A separate, high-volume store for activity, trace, and API logs. Keeping logs out of the main database prevents log volume from competing with operational work.
• Archive database. Cold storage for aged-out shipment activity records, plus older location history that was archived before the location service took over.
• Reporting (read-only) copy. Reports run against a read-only copy of the data rather than the live operational database. This lets reporting run heavy queries without slowing down the transactional system.
• Shipment-locations database (service-owned). The shipment-locations service, the standalone service that was carved out of the monolith for location visibility and geocoding, has its own database per environment, sitting beside the main tenant database rather than sharing it. It owns the shipment-location table and is the only store of location rows. The older postal-code reference table stays in the main database.
• Background-job store. The scheduler that runs background jobs keeps its own queue in a separate SQL database. It holds job state, not business data.

Shipment locations are no longer in the main database. The cutover to the location service is complete. The main database's own location table, its pending-change table, and the change-tracking trigger were dropped in a September 2026 migration, and the synchronization jobs that fed the service were retired. The migration cannot be reversed. The main application now reaches location data only over the service's HTTP interface (default timeout 30 seconds). What remains in the main database is a one-row-per-shipment "last known location" table used by search grids and report columns. It is maintained by the main application from the service's write results and is cleaned up nightly: rows for shipments that are no longer in transit or out for delivery and that have not been modified for more than a year are deleted. Any report or script that still reads the dropped location tables will fail; use the last-known-location table or the service instead.

New tables in the main database. Beyond the long-standing schema, the main database has gained tables that back several newer capabilities: the public-API rate-limit configuration and per-key and per-organization request counts; the EDI-automation tables (inbound EDI files, the shipment-automation actions taken from them, and a backfill cursor); a bulk-process table; an action-event-type lookup used by the shipment action log; carrier contact notes; and a table for document-automation details. The EDI-automation, bulk-process, and document-automation tables mirror older tables in the logging database, and they start their identity values at high floors. For EDI files and shipment automation logs, readers switch to the main-database copy only when both the double-write and read-from-main settings are on. For document automation, the logging-database copy is still the working store and the only one read. Every document-automation write goes to it first and, when the document-automation double-write setting is on, is mirrored to the main-database copy. A background sync job, which runs whether or not that setting is on, brings the main-database copy into line every 5 minutes: a trigger on the logging table records each changed document, the job re-copies those documents and removes main-database copies whose logging row was deleted, and it then copies any history still missing. These are ordinary operational tables in the main database, not separate databases.

Archiving old activity. On a schedule, a background process moves older shipment activity-log records out of the main operational database and into the archive. Each run takes up to 30,000 records that are more than six months old, oldest first, and sends them to the archive service. Only after the archive service confirms success does the job clear any staff-assignment references to those records, remove the matching pinned-activity rows, and delete the records from the main database. If the archive call fails, nothing is deleted. The job is protected against overlapping runs. This is the mechanism that keeps the operational database from growing without bound while preserving the history in the archive. There is no equivalent job for location history; location rows are held by the location service, and any location rows that were archived before the cutover can still be read.

The archive write path is now a single path. The way archived records are written was modernized. A standalone archive service (a separate .NET solution with its own data layer and API) now receives all archived activity logs. An earlier feature flag that chose between the new service and the older direct write has been removed, along with the older direct write path, so the archive service is the only way archived activity logs are written. The service writes to the same physical archive database that the older data-access layer was built on, so the switch changed how records are written, not where they live. The service accepts bulk creation of records and lookups by shipment, requires an authenticated caller who belongs to an allowed group, and accepts large request bodies (up to 200 MB). When someone opens an old shipment's activity log or location history, the back-office screens read the archived records on demand through the main application, which calls the archive service.

Two data-access layers, one archive database. The older data-access layer still defines the archive tables and owns one of the two migration histories for them, but nothing outside the migration runner queries it any more. The archive service has its own migration history. Schema changes to the archive must stay consistent across both histories, and the service is built to adopt a schema the older layer created rather than recreate it.

Automatic retry of transient errors. Cloud databases occasionally return brief, transient errors, for example during a momentary reconfiguration of the database service, or when a failover-group secondary endpoint briefly rejects logins. The Tai TMS is configured to automatically retry these transient failures rather than surfacing them as errors, including the specific conditions that occur when the database is being rescaled, reconfigured, or reconnected behind the scenes. This retry behavior smooths over short-lived interruptions without user intervention. It does not apply inside a user-started multi-statement transaction, where the retry loop is skipped.

Business rules & limits

• Data is split across several databases by purpose. Main operational data, logs, archived history, and reporting each have their own store, and the shipment-locations service has its own database as well. They are not interchangeable, and each holds a distinct slice of the data. The built-in type guards prevent writing a logging record to the main database by mistake, so do not work around them.
• Reporting reads from a read-only copy. The reporting database is a read replica; it is used for reading only and is never the place to write data or expect changes to be applied. It exists so reporting workloads do not burden the live operational database.
• The main database holds no location rows. Location data lives in the service's own per-environment database and is reached over its API. There is no double-write and no change-tracking queue any more, so there is only one copy of a shipment's location history. A location write that fails in the service fails for the caller; there is no transaction spanning the service and the main database.
• The last-known-location table is a cache with a one-year retention. It holds the latest in-transit position per shipment for grids and reports and is pruned for shipments that are no longer moving and have not been touched for a year.
• Archiving is a move, not a copy-and-keep. Aged activity records are sent to the archive and then removed from the operational database, and only after the archive confirms success. A run handles up to 30,000 records, so a large backlog drains over several runs.
• The archive has a single write path. All archived activity logs go through the archive service. There is no flag to fall back to the older write path, so troubleshooting archiving means looking at the service and the job's call to it.
• Document-automation details are read from the logging database. The main-database copy is a mirror kept current by an optional double-write and by a sync job every 5 minutes that runs regardless of the setting, so between runs it can lag the logging copy.
• Only activity logs are archived by the job. Location history is not moved by any job. Older archived location rows remain readable.
• No optimistic concurrency. The data layer does not use row-version columns, so when two people save the same record the last writer wins.
• Bulk operations skip validation. Bulk inserts, merges, updates, and deletes bypass the normal save path, so field-level validation does not run; only the database's own constraints, checks, unique indexes, and triggers apply. A too-long value surfaces as a database truncation error.
• Transient database errors are retried automatically. Short-lived database errors, such as those from a momentary reconfiguration, rescale, or failover-group reconnection of the database service, are retried automatically. A brief blip should therefore not require manual intervention; persistent errors, on the other hand, indicate a real problem.
• The logging store handles plain (non-Unicode) text. The logging database is optimized for high-volume plain text. Storing characters outside its supported character set in a log record can result in those characters not being preserved exactly.
• Stored procedures are re-created on every migration. The migration run re-executes the full set of create-or-alter procedure scripts each time, so a procedure that was dropped in one migration but is still listed in the seed list comes back on the next run. A procedure is retired only when its seed entry is removed as well.

Frequently asked questions

Does the Tai TMS keep all of a tenant's data in a single database?

No. The Tai TMS separates each tenant's data across several databases by purpose: a main operational database for live transactional work, a separate logging database for high-volume activity and API logs, an archive database for aged-out historical records, and a read-only reporting copy used to run reports. In addition, the shipment-locations service keeps its own database. Splitting the data this way keeps heavy or historical workloads from slowing down day-to-day operations.

Why does the Tai TMS use a separate reporting database?

The Tai TMS runs reports against a read-only copy (a read replica) of the data instead of the live operational database so that large, heavy report queries do not compete with everyday transactional work. Because it is read-only, the reporting database is never used to write data or apply changes; it exists purely to serve reads for reporting.

Where does shipment location data live now?

Shipment location visibility (where a load has been, plus geocoding) lives in a standalone service that has its own database per environment, separate from the main tenant database. The main database's own location tables were dropped in September 2026 after the cutover, so there is a single copy. The main application reaches the service over HTTP and keeps only a small last-known-location table for search grids and reports. A report or script that still reads the old location tables will fail.

How does the Tai TMS keep its operational database from growing too large?

In the Tai TMS, a background process periodically moves older shipment activity-log records out of the main operational database and into the archive. Each run takes up to 30,000 records older than about six months, sends them to the archive service, and deletes them from the operational database only after the archive confirms success. This keeps the live system lean while preserving the history.

What changed about how the Tai TMS writes archived data?

The archive job now always sends records to the standalone archive service. The earlier setting that chose between the service and the older direct write has been removed, along with the older write path, so there is only one way archived activity logs are written. The service stores into the same archive database as before, so the data's location did not change. If archiving looks stuck, look at the archive service and the job's call to it rather than at a setting.

Why can I still see archived location history for an old shipment if locations are no longer archived?

Location rows that were archived before the location service took over are still in the archive database, and the back-office location history can read them on demand through the archive service. No job archives new location history; current location history is held by the location service.

What happens in the Tai TMS when the database returns a brief, transient error?

The Tai TMS is configured to automatically retry transient database errors rather than surfacing them as failures. This includes short-lived conditions that can occur when the underlying cloud database is being rescaled or reconfigured, and when a failover-group secondary endpoint briefly rejects a login. As a result, a momentary database blip is typically smoothed over without any user action; only persistent errors indicate a genuine underlying problem.

Are the operational, logging, archive, reporting, and shipment-locations databases interchangeable?

No. In the Tai TMS each of these databases holds a distinct slice of data and serves a specific purpose: live transactional work, logs, archived history, read-only reporting, and shipment-location visibility respectively. They are separate stores with separate connections, and data belonging to one is not simply available from another; this separation is intentional and part of how the platform stays performant.