Shipment Event Archives

FreightCake keeps shipment lifecycle events in shipment_events for active operational use and archives cold rows to S3 for FMCSA record retention. The monthly archive job writes newline-delimited JSON to:

s3://freightcake-{env}-audit-archive/shipment_events/year=YYYY/month=MM/run=RUN_ID/batch-0001.ndjson

Every run writes a SOC-style control row to audit_archive_runs with the cutoff timestamp, archive URI, exported row count, pruned row count, final status, and any error message.

Schedule

The API worker registers a repeatable BullMQ job named shipment-events.monthly on the audit-archive queue. It runs at 04:00 UTC on the first day of every month when SHIPMENT_EVENT_ARCHIVE_BUCKET is configured.

Rows older than SHIPMENT_EVENT_HOT_RETENTION_DAYS are exported to S3, then pruned from RDS after the S3 write succeeds. The default hot retention window is 90 days.

Environment

Required in deployed worker environments:

SHIPMENT_EVENT_ARCHIVE_BUCKET=freightcake-{env}-audit-archive

Optional:

SHIPMENT_EVENT_ARCHIVE_PREFIX=shipment_events
SHIPMENT_EVENT_HOT_RETENTION_DAYS=90
SHIPMENT_EVENT_ARCHIVE_BATCH_SIZE=10000

The worker uses the standard pod AWS identity and S3_REGION.

Verify A Run

Use the database to verify the control row:

select
  id,
  status,
  cutoff_at,
  archive_uri,
  exported_row_count,
  pruned_row_count,
  error_message,
  started_at,
  completed_at
from audit_archive_runs
where archive_type = 'shipment_events'
order by created_at desc
limit 10;

Use S3 to confirm batch objects exist under the recorded archive_uri:

aws s3 ls s3://freightcake-dev-audit-archive/shipment_events/year=YYYY/month=MM/run=RUN_ID/

Query Archived Events

For ad hoc inspection, pull a specific object and filter it with jq:

aws s3 cp s3://freightcake-dev-audit-archive/shipment_events/year=YYYY/month=MM/run=RUN_ID/batch-0001.ndjson - \
  | jq 'select(.shipmentId == 12345)'

For recurring analysis, register the prefix as an external table in Athena or load the NDJSON into a temporary table. The stable fields are id, entity, entityId, shipmentId, eventType, fromStatus, toStatus, actorUserId, actorSystem, source, sourceRef, payload, and createdAt.

Failure Handling

If a run fails, the worker marks the control row FAILED and leaves the error in error_message. Batches are pruned only after their corresponding S3 object is written successfully, so rerunning the job is safe. Review the failed run, verify the archive bucket permissions, then retry by enqueueing shipment-events.monthly on the audit-archive queue.