Skip to main content

Velox Data Repository TODO

Static review performed on 2026-07-05.

Scope:

  • Reviewed SQL Server schema scripts, upgrade scripts, functions, triggers, and database documentation.
  • Did not modify schema or data scripts.
  • Did not execute scripts against a database.
  • Stability focus was on upgrade safety, destructive operations, repeatability, and operational maintainability.

High Risk​

Base database scripts are destructive if run against an existing customer database​

Files:

  • 1. Database\_Master.sql
  • 1. Database\Table_*.sql
  • 1. Database\Function_DropConstraints.sql

Evidence:

  • _Master.sql is a SQLCMD include script that runs table, function, trigger, and index scripts in sequence.
  • Many table scripts use drop/recreate patterns.
  • Function_DropConstraints.sql iterates constraints and executes dynamic drop statements.

Risk:

  • Running the base build scripts against the wrong database can delete customer data.
  • Constraint removal before table rebuilds increases blast radius.
  • The script is suitable for fresh database creation, but it needs strong guardrails if operators can access it.

Suggested direction:

  • Clearly label base scripts as fresh-build only.
  • Add preflight checks that verify database name, environment, expected empty state, and operator intent.
  • Keep production upgrades separate from destructive rebuild scripts.

Upgrade scripts are not transactionally safe​

Folder:

  • 2. Upgrades

Evidence:

  • Upgrade scripts contain many batches separated by GO.
  • The reviewed scripts do not use a consistent transaction, XACT_ABORT, or failure-handling wrapper.
  • Version updates can appear near the start of a script, such as Upgrade v1.15 - EDI2.sql.

Risk:

  • A failed upgrade can leave the schema partially changed.
  • If the version row is updated before all changes complete, future upgrade detection can believe the database is newer than it really is.
  • Partial upgrades are especially risky for Velox because services and web front ends depend on stable shared schema.

Suggested direction:

  • Add an upgrade runner or convention that records start/end state, failures, and required manual steps.
  • Move version stamping to the end of each successful upgrade where practical.
  • Add idempotency and preflight checks around destructive or data-migration steps.

Upgrade scripts include destructive drops and large structural changes​

Folder:

  • 2. Upgrades

Evidence:

  • Several upgrade scripts drop tables or rebuild areas of the model.
  • Examples include webhook, shipment issue, POD/tracking, event type, party code, and mapping-related upgrades.

Risk:

  • Dropped tables may contain customer data that needs migration, archival, or explicit confirmation.
  • Re-running an upgrade after a partial failure can fail or cause additional data loss.

Suggested direction:

  • Document whether each dropped object is temporary, obsolete, or migrated elsewhere.
  • Add data-preservation notes to the upgrade scripts.
  • For high-risk changes, include verification queries before and after the upgrade.

Medium Risk​

Manual upgrade prerequisites are embedded in comments​

Files:

  • 2. Upgrades\Upgrade v1.15 - EDI2.sql
  • Other large upgrade scripts with comment-led instructions

Evidence:

  • Some upgrade scripts include notes for manual configuration or operational steps.
  • Those notes are not enforced by the script itself.

Risk:

  • Operators can miss required setup steps.
  • Services may start against a schema that is structurally upgraded but operationally incomplete.

Suggested direction:

  • Move manual prerequisites into an upgrade checklist.
  • Add preflight queries where possible.
  • Make missing prerequisites fail early with a clear error.

Dynamic SQL and cursor-based constraint cleanup are broad​

File:

  • 1. Database\Function_DropConstraints.sql

Evidence:

  • The script uses a cursor over constraints and executes generated SQL.
  • It does not appear to be scoped to a single subsystem.

Risk:

  • Broad cleanup can remove more than intended if run in the wrong database.
  • Cursor/dynamic SQL behavior can be hard to review and hard to reverse.

Suggested direction:

  • Keep the script restricted to fresh-build workflows.
  • Add explicit object/schema filters if the script must remain available.

Large table scripts are difficult to review safely​

Files:

  • 1. Database\Table_Issue.sql
  • 1. Database\Table_Shipment.sql
  • 1. Database\Table_Order.sql
  • 1. Database\Table_Party.sql
  • Large upgrade scripts under 2. Upgrades

Evidence:

  • Several table and upgrade scripts are hundreds to thousands of lines long.
  • Schema, indexes, defaults, constraints, and occasionally migration logic live together.

Risk:

  • Important changes can be missed during review.
  • Merge conflicts can be hard to resolve.
  • Small schema edits have a high chance of affecting web, service, and reporting behavior.

Suggested direction:

  • Keep logical object boundaries clear.
  • Add generated schema diff artifacts or review checklists for large upgrades.
  • Cross-reference affected application areas in upgrade comments.

SQLCMD path assumptions are environment-specific​

File:

  • 1. Database\_Master.sql

Evidence:

  • _Master.sql defines a hard-coded directory variable such as C:\ProgramData\Velox\Data\Velox\Database\.

Risk:

  • Running scripts from a different checkout or server layout can fail or include the wrong files.
  • Installation/update scripts must keep paths synchronized.

Suggested direction:

  • Document expected execution directory and SQLCMD usage.
  • Prefer relative include paths where possible.

Low Risk​

To-do and demo SQL files create search noise​

Folders/files:

  • 1. Database\To do
  • 2. Upgrades\zzz TO DO.sql
  • Demo/manual cleanup scripts

Evidence:

  • The repository contains scripts that appear unfinished, experimental, or operator-specific.

Risk:

  • Maintainers may confuse draft SQL with production upgrade SQL.
  • Search results include obsolete ideas.

Suggested direction:

  • Clearly mark draft scripts as non-production.
  • Move historical notes into documentation if they should not be executable.

Runtime thread safety is mostly outside this repository​

Evidence:

  • This repository contains database scripts rather than long-running application code.

Risk:

  • No in-process thread safety issue was identified here.
  • Database concurrency still matters because Velox services and web apps can execute against the schema concurrently.

Suggested direction:

  • Document transaction expectations for tables touched by service actions and web workflows.
  • Add indexes and constraints based on observed concurrent workloads.

Duplicate Code Areas​

  • Repeated table drop/create boilerplate.
  • Repeated upgrade patterns for object recreation.
  • Repeated version and reference-data changes across upgrade scripts.
  • Similar event/type/status table patterns.

Dead Code / Stub Areas​

  • 2. Upgrades\zzz TO DO.sql appears non-production.
  • 1. Database\To do appears to contain draft or planning SQL.
  • Any demo/manual delete scripts should remain clearly separated from installable upgrade scripts.

Thread Safety / Concurrency Concerns​

  • Database concurrency is the main concern rather than application thread safety.
  • Upgrade scripts should not run while VeloxService, VeloxAPIService, or web front ends are active unless explicitly designed for online migration.
  • Tables used by file/message processing need careful locking, indexing, and transaction design, but static SQL review alone does not prove runtime behavior.

32-bit / 64-bit Concerns​

  • No direct pointer-size issue exists in SQL scripts.
  • Operational bitness still matters through SQL Server drivers used by Velox binaries.
  • Scripts should document required SQL Server version/features so 32-bit/64-bit client differences do not hide compatibility problems.

Performance Concerns​

  • Large upgrades can hold locks for long periods.
  • Rebuilding tables or dropping constraints can block production services.
  • Cursor/dynamic SQL cleanup can be slow on large databases.
  • Missing preflight row counts and timing estimates make maintenance-window planning difficult.

Areas Difficult to Maintain​

  • Large monolithic upgrade scripts.
  • Fresh-build scripts that are destructive by design.
  • Manual upgrade instructions embedded in comments.
  • Schema shared by Delphi services, API service, and web applications.

Open Questions​

  • Is there an official upgrade runner, or are scripts executed manually?
  • Are base database scripts ever run on customer databases after first install?
  • What rollback strategy is expected after a failed upgrade?
  • Which SQL Server versions are supported?
  • Are services always stopped before upgrades?