Skip to main content

Velox Data Developer Guide

This guide is for developers joining the VxData database repository. VxData is the SQL Server database used for business transaction data processed by Velox.

Repository Layout​

  • 1. Database\ - fresh-build SQL scripts for creating the VxData database from scratch.
  • 1. Database\_Master.sql - SQLCMD include script that runs the fresh-build scripts in order.
  • 1. Database\Table_*.sql - table creation scripts.
  • 1. Database\Function_*.sql - helper functions and setup functions.
  • 1. Database\View_Core.sql - core views.
  • 1. Database\Trigger_Core.sql - core triggers.
  • 1. Database\Index_Core.sql - indexes.
  • 1. Database\To do\ - draft/planning SQL, not production upgrade SQL.
  • 2. Upgrades\ - versioned upgrade scripts for existing databases.
  • 3. Velox Schema\ - VxXML schema files representing business transactions.
  • 4. Velox JSON\ - JSON schema/example files corresponding to the VxXML/data model direction.
  • Demo\ - demo and test-data scripts.
  • Creating data manually ie parties\ and Deleting records\ - operator/development helper scripts.
  • velox-kb\VeloxPKB\docs\velox-data\ - PKB/developer documentation for this repository.
  • _UPDATE DB INSTALL FOLDER.bat - syncs database and upgrade scripts into the install repository.
  • _UPDATE SCHEMA INSTALL FOLDER.bat - syncs VxXML schemas into install and local ProgramData.
  • _UPDATE JSON INSTALL FOLDER.bat - syncs JSON schemas into install and local ProgramData.

Build Process​

There is no compiled build. The repository is validated by running SQL scripts against SQL Server and by keeping schemas synchronized.

For a fresh database:

  1. Open 1. Database\_Master.sql in SQL Server Management Studio or another SQLCMD-aware SQL tool.
  2. Confirm the target database is correct.
  3. Enable SQLCMD mode.
  4. Set the :setvar Dir value to the folder containing the database scripts.
  5. Execute the script.
  6. Review messages and errors before running any custom scripts.

Fresh-build scripts are destructive by design. Do not run 1. Database scripts against an existing or customer database unless the task explicitly asks for that and the target has been confirmed.

For existing databases:

  1. Use the scripts under 2. Upgrades.
  2. Apply only the upgrade path needed for the current database version.
  3. Preserve customer data.
  4. Confirm each script completes before marking the database as upgraded.
  5. Run validation queries after structural or data migration changes.

Debugging​

Useful debugging tools:

  • SQL Server Management Studio.
  • SQLCMD mode for _Master.sql.
  • SQL Server execution messages and error output.
  • System_Version table to understand current database version.
  • Foreign key, index, and row-count queries when validating upgrades.
  • The consuming applications: velox, velox-web, and velox-web2.

When debugging schema issues:

  1. Identify whether the failure is in config data, VxData schema, EF mappings, or Delphi SQL.
  2. Reproduce against a development database.
  3. Check whether the affected table is represented in VxXML or JSON schema files.
  4. Check whether web EF models need to be regenerated or updated.
  5. Add upgrade SQL if the change must apply to existing databases.

Coding Standards Inferred From The Project​

  • Use Microsoft SQL Server syntax.
  • Preserve existing naming conventions for tables, columns, keys, indexes, and constraints.
  • Keep SQL readable and explicit.
  • Do not rename columns, tables, stored procedures, or fields without approval.
  • Do not change schema unless explicitly requested.
  • When adding or changing schema in 1. Database, add the equivalent upgrade path in 2. Upgrades.
  • Keep VxXML and VxJSON assets aligned when schema changes affect represented business transactions.
  • Avoid destructive operations unless there is an explicit migration path or explicit approval.
  • Do not run demo, cleanup, or draft scripts as part of normal database upgrades.

Typical Workflow​

For a table/schema change:

  1. Read the current table script under 1. Database.
  2. Read related upgrade scripts to understand the existing pattern.
  3. Update the fresh-build table/function/view/index script.
  4. Add the corresponding upgrade SQL to the current unpublished upgrade file, or start a new version file if the current version has been published.
  5. Update VxXML and VxJSON assets if the business transaction shape changed.
  6. Check dependent code in velox, velox-web, and velox-web2.
  7. Run against a development database.
  8. Validate data preservation, constraints, indexes, and version state.

For a schema sync to install artifacts:

  1. Confirm the source scripts are correct.
  2. Run the relevant _UPDATE ... INSTALL FOLDER.bat script only when the task is about install/release sync.
  3. Review the copied files in Install.
  4. Do not hand-edit copied install artifacts as the primary source.

Common Pitfalls​

  • _Master.sql requires SQLCMD mode.
  • _Master.sql has a hard-coded :setvar Dir example that may not match a checkout path.
  • Fresh-build scripts can drop/recreate objects and should not be used as upgrades.
  • Some upgrade scripts update version rows near the start; verify completion before relying on the version.
  • GO separates batches, so transaction scope is not automatic across an entire upgrade file.
  • DROP, DELETE, and table rebuild operations need customer-data review.
  • Manual upgrade instructions in comments can be missed.
  • Demo and helper scripts can look production-like; confirm before running.
  • Web EF models and Velox runtime SQL can break if schema changes are not coordinated.

Important Concepts​

VxData​

VxData is the user/business transaction database. It stores standardized business transaction data processed by Velox integrations.

Fresh Build​

Fresh-build scripts create the database structure from scratch. They are not a safe migration path for an existing customer database.

Upgrade Script​

Upgrade scripts apply schema and data changes to existing databases. They must preserve customer data and leave the database in a version state that matches the completed changes.

VxXML​

VxXML files under 3. Velox Schema represent business transaction shapes that Velox uses around data integration.

VxJSON​

JSON files under 4. Velox JSON are JSON representations or examples aligned with the VxData/VxXML model direction.

Install Sync​

The install repository contains copied database, schema, and JSON artifacts. Source changes should be made here first, then synced to Install.

Unclear Areas​

  • The official database upgrade runner is unclear from this repository.
  • The supported SQL Server compatibility level is not fully documented here beyond project guidance that Velox uses SQL Server 2025.
  • The exact publication boundary between current and next upgrade files must be confirmed with the project owner.