Skip to main content

Velox Data Architecture

This document is based on inspection of the velox-data repository. Where behavior is not visible from scripts or repository instructions, it is marked as unclear.

Overall Architecture​

velox-data is the source repository for the VxData database, also called the Velox Data Platform. It defines the SQL Server database used to standardise business transaction data processed by Velox and used by the Velox web front ends.

The repository contains four main architecture layers:

  • SQL Server database creation scripts.
  • SQL Server upgrade scripts.
  • VxXML .xsd schemas for business transaction/message formats.
  • Early JSON examples/representations for selected messages.

The database is a relational staging and operational data model for trading-partner transactions such as orders, ASNs, invoices, shipments, remittances, adjustments, bookings, pre-advices, container moves, parties, products, addresses, events, issues, mappings, users, and web hooks.

Solution and Project Structure​

There is no application solution file. The repository is organized as data definition artifacts:

  • 1. Database: source-of-truth SQL scripts to create VxData from scratch.
  • 2. Upgrades: versioned SQL upgrade scripts for existing databases.
  • 3. Velox Schema: XML Schema (.xsd) definitions for VxXML messages.
  • 4. Velox JSON: JSON examples or future JSON representations of VxXML messages.
  • Demo: scripts for demo/test data and log cleanup.
  • Creating data manually ie parties: manual-data support scripts. Exact intended workflow is unclear.
  • Deleting records: delete/cleanup support scripts. Exact intended workflow is unclear.

Copy scripts mirror source content into install/runtime locations:

  • _UPDATE DB INSTALL FOLDER.bat copies database and upgrade scripts into Install\ProgramData\install-veloxdb.
  • _UPDATE SCHEMA INSTALL FOLDER.bat copies XSD files into Install\ProgramData\install-veloxschema and C:\ProgramData\Velox\Schema\Velox.
  • _UPDATE JSON INSTALL FOLDER.bat copies JSON files into Install\ProgramData\install-veloxschemajson and C:\ProgramData\Velox\Schema\VeloxJSON.

Startup Sequence​

This repository has no executable startup sequence.

The closest equivalent is the database creation sequence in 1. Database\_Master.sql:

  1. Enable SQLCMD mode in SQL Server Management Studio.
  2. Set Dir to the database script folder, usually C:\ProgramData\Velox\Data\Velox\Database\.
  3. Run Function_DropConstraints.sql.
  4. Create system/base tables such as Tally, VeloxFlow, System_Version, Cache, Interface, Application, Partner, Event, Source, User, ASP.NET Identity tables, settings, address, party, issue, mapping, and other shared tables.
  5. Create transaction/product/domain tables such as product, order, ASN, shipment, invoice, remittance, adjustment, container, booking, and pre-advice.
  6. Create views, helper functions, role functions, core functions, foreign-key tidy-up scripts, and triggers.

Initial customer setup is partly represented by 1. Database\_Default Setup Script.sql, which updates placeholder company/application/partner/party values and a web URL. How this script is parameterized during a real customer install is unclear.

Upgrade execution is script-based. The repository contains upgrade files from Upgrade v1.1.sql through Upgrade v1.18 - tidy.sql, plus zzz TO DO.sql. The orchestrating process for choosing and applying upgrade files is not implemented in this repository.

Purpose of Each Major Area​

  • 1. Database: authoritative create-from-scratch schema for VxData.
  • 2. Upgrades: incremental schema/data changes for already-installed databases.
  • 3. Velox Schema: XML contracts for business messages, including common reusable types and transaction-specific schemas.
  • 4. Velox JSON: JSON examples/representations for messages such as shipment, shipment response, shipment tracking, POD, price response, and mapping data.
  • Demo: demo data and date-roll scripts.
  • Root batch files: copy database/schema/JSON artifacts to install/runtime folders.

Key Frameworks and Libraries​

Visible technologies are:

  • Microsoft SQL Server, identified in repository guidance as SQL Server 2025.
  • T-SQL.
  • SQLCMD include syntax (:r, :setvar) in _Master.sql.
  • XML Schema (.xsd) using the Velox.XML namespace.
  • JSON message examples.
  • Windows batch and robocopy for mirroring artifacts.
  • Excel specification files for POD and Shipment.

No application framework or runtime library is present in this repository.

Configuration Loading​

There is no runtime configuration loader in this repository.

Configuration-like artifacts include:

  • SQLCMD variable Dir in _Master.sql.
  • Placeholders in _Default Setup Script.sql, such as company code/name and web URL.
  • Hardcoded source/target paths in batch copy scripts.
  • Settings data seeded in table scripts such as Table_Setting.sql.
  • Interface, application, source, status, type, and event seed data in table scripts.

Actual runtime loading of database connection configuration happens in the Velox application repositories, not here.

Logging​

The repository does not implement application logging.

The database model includes operational event/issue/log-like structures:

  • Transaction event tables such as Shipment_Event, Order_Event, Invoice_Event, ASN_Event, and similar domain-specific event tables.
  • Transaction issue tables such as Shipment_Issue, Order_Issue, Invoice_Issue, and similar domain-specific issue tables.
  • Interchange, Interchange_Event, and Interchange_Issue.
  • Helper views such as TableCount, TableSize, IndexFragmentation, and MissingIndexes.

How Velox writes to and reads from these tables at runtime is defined in the application repositories and is not fully visible here.

Database Access Architecture​

The database is organized around domain table families:

  • Core/reference: Application, Interface, Partner, Source, Setting, Status, Tally, AutoNumber, VeloxFlow.
  • Security/web identity: ASP.NET Identity tables, User, User_Party.
  • Party/address/product reference data: Party, party role tables, address tables, country/region/zone/city/suburb, product/uom/price tables.
  • Operational transactions: order, ASN, invoice, shipment, POD, tracking, remittance, adjustment, booking, pre-advice, container move.
  • Integration support: web hook, upload, mapping, interchange, event, issue.

Common structural patterns visible in scripts:

  • Most business tables use integer Num keys.
  • Many tables include GUID Id values for external/reference use.
  • Code/name/description fields support business lookup.
  • Transaction headers, lines, events, and issues are separated into related tables.
  • Foreign keys connect transactions to application, partner, party, source, status/type, product, event, and issue tables.
  • Default values such as getdate(), newid(), booleans, and statuses are applied in SQL.
  • Index scripts are separated into Index_Core.sql.
  • Triggers prevent duplicate unhandled event rows for multiple transaction families and create product quantity rows when products/parties are inserted.

Plugin/Module Architecture​

There is no code plugin architecture in this repository.

Database tables such as VeloxFlow, Interface, Setting, WebHook, and mapping/event/issue tables provide configuration and integration points consumed by Velox applications. The exact module/plugin semantics are implemented in velox and velox-web, not here.

Flow Execution Architecture​

This repository does not execute flows.

It stores data that flow execution can read or write:

  • Transaction data tables.
  • Event tables that appear to represent work/status notifications.
  • Issue tables for validation and processing problems.
  • Web hook tables/files for inbound file/API processing.
  • VeloxFlow, which maps web-facing actions to Velox flow/action identifiers.

The exact lifecycle from inbound file/API payload to database rows to Velox flow execution is unclear from this repository alone.

Serialization Formats​

Visible formats include:

  • SQL scripts.
  • XML Schema (.xsd) in Velox.XML.
  • JSON examples/representations.
  • Excel specification workbooks.
  • Batch scripts.
  • Markdown/instruction text.

Common.xsd defines the common MessageType, interface enumerations, address types, temperature/uom/source types, decimal restrictions, and string length restrictions. Transaction schemas such as Shipment.xsd extend the common message and define nested message structures such as manifest, shipment, and shipment line.

Major Design Patterns Used​

  • SQL script-per-domain table grouping.
  • Master SQLCMD orchestration script for fresh database creation.
  • Separate upgrade scripts for installed database evolution.
  • Domain header/line/event/issue table families.
  • Lookup/type/status tables for controlled business values.
  • Foreign-key driven relational integrity.
  • Helper functions/views/triggers separated from table creation.
  • Mirroring from source repository to install/runtime folders using batch scripts.

Areas of Technical Debt​

  • Upgrade application appears manual/script-based; no repository-local migration runner was found.
  • Upgrade file names mix semantic versions with lexicographic ordering risk, for example v1.10 and v1.2.
  • Hardcoded local paths in batch copy scripts make the copy process machine/workspace dependent.
  • 4. Velox JSON is described in guidance as future JSON representation work, so JSON coverage is incomplete.
  • Some folders are named as ad hoc operational tasks, such as manual creation and deleting records, making their production status unclear.
  • The install copies under Install\ProgramData can drift if copy scripts are not run consistently.

Questions About the Architecture​

  • What tool/process records which upgrade script versions have been applied to a customer VxData database?
  • What is the official version number currently represented by the create-from-scratch scripts?
  • Which JSON files are authoritative contracts versus examples?
  • Should every XSD schema have an equivalent JSON schema or sample?
  • Which demo/manual/delete scripts are safe for production use, and which are internal-only?