controls-web/ai_agents/db_agent/context/glossary.md

SugarScale MSSQL Glossary

Use this glossary to translate operator language into database columns for the SQL agent.

Core Tables

• dbo.SugarLoadData: Per-load grain deliveries recorded at the truck scale. Each row represents a truck entering/exiting the facility with weight measurements and logistics metadata.
• dbo.SugarVehicles: Reference table for registered trucks/trailers and their default properties (company, RFID tag, tare weight).
• dbo.SugarParams: System parameters; currently stores a single StartTime indicating when the scale captured data begins.

Common Terms

• Load → dbo.SugarLoadData

  • SugarLoadID_Pk: Unique identifier for a scale transaction (primary key).
  • VehicleId_Fk: Truck or vehicle identifier referencing dbo.SugarVehicles.VehicleId_Pk.
  • TrailerTypeID: Trailer classification (e.g., hopper, dump). Stored as free text; standardize before analytics when possible.
  • TCompanyName: Hauling company name as captured at the kiosk.
  • DriverID: Driver badge or license; alphanumeric string up to 500 chars.
  • LoadingLocation: Where the load originated (hopper, wh1, wh2, etc.).
  • Destination: Planned delivery point or storage yard.
  • DateIn: Timestamp when the vehicle weighed in (in local plant timezone).
  • DateOut: Timestamp when the vehicle weighed out or exited.
  • ScaleWT: Raw indicator from the scale prior to adjustments (integer pounds).
  • GrossWt: Gross weight measured on exit, in pounds.
  • TareIN: Tare weight observed on entry (empty truck), in pounds.
  • TareWt: Tare weight applied for net calculations; may be sourced from default or reweigh.
  • Tons: Net payload in short tons (calculated from gross-tare).
  • LoadType: Category flag (e.g., sugar, molasses). Align with scheduling terminology.
  • Alt_Barcode_Scan: Secondary barcode capture used for redundancy; 50-character limit.

• Vehicle → dbo.SugarVehicles

  • VehicleId_Pk: Unique truck identifier (matches VehicleId_Fk).
  • TCompanyName: Default operator/contractor name for the vehicle record.
  • RFID: RFID tag assigned to the truck for automatic gate reads.
  • Tare: Stored tare weight (decimal, pounds). Use when SugarLoadData.TareWt is null.
  • LastTareTime: Timestamp of the last verified tare measurement.
  • DefaultDest: Common destination code used to prefill load forms.

• System Start → dbo.SugarParams.StartTime

  • Indicates the earliest reliable timestamp in the SugarLoadData feed; use when bounding historical reports.

Business Notes

• Weight fields are stored in pounds except Tons, which is short tons (lbs / 2000). Convert before aggregating if downstream systems expect metric tons.
• DateIn and DateOut are expected to be in local plant time without timezone metadata; adjust to UTC when integrating with other systems.
• Null VehicleId_Fk rows usually indicate walk-on loads or manual overrides; treat carefully when joining to SugarVehicles.
• LoadType and TrailerTypeID are free text and may have inconsistent casing; normalize to upper case when filtering.