jdescopingtool/DOCUMENTATION/Architecture/Database.md

Database

The application uses SQL Server for the local cache database. Schema is managed using DbUp, with versioned SQL scripts embedded in the application.

DbUp Overview

DbUp is a .NET library for deploying changes to SQL Server databases. It tracks which scripts have been executed in a SchemaVersions table and runs new scripts in alphabetical order.

Key benefits:

• Schema defined as code (versioned SQL scripts)
• Automatic migration on startup
• Idempotent - safe to run multiple times
• Simple, well-tested library

Project Structure

JdeScoping.Database/
├── JdeScoping.Database.csproj
├── DatabaseMigrator.cs           # Entry point for migrations
└── Scripts/
    ├── 001_CreateSearchTable.sql
    ├── 002_CreateDataUpdateTable.sql
    ├── 003_CreateWorkOrderTables.sql
    ├── 004_CreateLotTables.sql
    ├── 005_CreateReferenceTables.sql
    └── ...

Script Naming Convention

Scripts are named with a numeric prefix for ordering:

NNN_DescriptiveName.sql

• NNN: Zero-padded number (001, 002, etc.)
• DescriptiveName: Brief description of what the script does
• Scripts run in alphabetical order (numeric prefix ensures correct order)

DatabaseMigrator Implementation

using DbUp;
using Microsoft.Extensions.Configuration;

namespace JdeScoping.Database;

public class DatabaseMigrator
{
    private readonly string _connectionString;

    public DatabaseMigrator(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("SqlServer")
            ?? throw new InvalidOperationException("SqlServer connection string not configured");
    }

    public DatabaseUpgradeResult Migrate()
    {
        EnsureDatabase.For.SqlDatabase(_connectionString);

        var upgrader = DeployChanges.To
            .SqlDatabase(_connectionString)
            .WithScriptsEmbeddedInAssembly(typeof(DatabaseMigrator).Assembly)
            .WithTransaction()
            .LogToConsole()
            .Build();

        return upgrader.PerformUpgrade();
    }
}

Embedding Scripts as Resources

Scripts are embedded in the assembly by configuring the project file:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <EmbeddedResource Include="Scripts\*.sql" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="dbup-sqlserver" Version="5.*" />
  </ItemGroup>
</Project>

Running Migrations on Startup

Migrations run early in application startup, before other services are configured:

// In Program.cs
var builder = WebApplication.CreateBuilder(args);

// Run database migrations first
var migrator = new DatabaseMigrator(builder.Configuration);
var result = migrator.Migrate();

if (!result.Successful)
{
    Console.WriteLine($"Database migration failed: {result.Error}");
    return 1;
}

// Continue with normal startup...
builder.Host.UseWindowsService();

Core Tables

The scoping tool cache database includes these primary tables:

Table	Purpose
Search	User search requests, status, criteria (JSON), and results (Excel as VARBINARY)
DataUpdate	Tracks last sync timestamp per data type
WorkOrder_Curr	Current work orders from JDE
WorkOrder_Hist	Historical work orders from JDE
LotUsage_Curr	Current lot usage from CMS
LotUsage_Hist	Historical lot usage from CMS
MisData_Curr	Current MIS data (Status='Current') from JDE
MisData_Hist	Historical MIS data (Status='BackLevel') from JDE
Lot	Lot reference data
Item	Item master reference data
WorkCenter	Work center reference data
JdeUser	Operator reference data
ProfitCenter	Profit center reference data
SchemaVersions	DbUp tracking table (auto-created)

Views

View	Purpose
MisData	UNION ALL of MisData_Curr and MisData_Hist for unified MIS data access

Search Criteria Extraction Functions

The database includes SQL functions that extract filter criteria from the Search.Criteria JSON column. These functions enable the query builder to pass only a SearchId parameter, with all filter extraction happening in SQL Server.

Scalar Functions (3)

Function	Returns	Extracts
fn_GetSearchMinimumDt	DATETIME2	$.MinimumDt
fn_GetSearchMaximumDt	DATETIME2	$.MaximumDt
fn_GetSearchExtractMisData	BIT	$.ExtractMisData

Table-Valued Functions (8)

Function	Returns	Extracts
fn_GetSearchWorkOrders	WorkOrderNumber	$.WorkOrderNumbers array
fn_GetSearchItemNumbers	ItemNumber	$.ItemNumbers array
fn_GetSearchProfitCenters	Code	$.ProfitCenters array
fn_GetSearchWorkCenters	Code	$.WorkCenters array
fn_GetSearchOperatorIDs	OperatorID	$.OperatorIDs array
fn_GetSearchComponentLots	LotNumber, ItemNumber	$.ComponentLotNumbers array
fn_GetSearchPartOperations	ItemNumber, OperationNumber, MisNumber, MisRevision	$.PartOperations array

Validation Procedure

Procedure	Purpose
usp_ValidateSearchCriteria	Validates search exists and has valid JSON criteria

Error codes: 50001 (not found), 50002 (no criteria), 50003 (invalid JSON)

Example Migration Scripts

001_CreateSearchTable.sql

CREATE TABLE [dbo].[Search] (
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [UserId] NVARCHAR(50) NOT NULL,
    [UserDisplayName] NVARCHAR(100) NULL,
    [Criteria] NVARCHAR(MAX) NOT NULL,
    [Status] INT NOT NULL DEFAULT 0,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    [StartedAt] DATETIME2 NULL,
    [CompletedAt] DATETIME2 NULL,
    [ResultCount] INT NULL,
    [Results] VARBINARY(MAX) NULL,
    [ErrorMessage] NVARCHAR(MAX) NULL
);

CREATE INDEX [IX_Search_Status] ON [dbo].[Search] ([Status]);
CREATE INDEX [IX_Search_UserId] ON [dbo].[Search] ([UserId]);

002_CreateDataUpdateTable.sql

CREATE TABLE [dbo].[DataUpdate] (
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [TableName] NVARCHAR(100) NOT NULL,
    [UpdateType] NVARCHAR(20) NOT NULL,
    [LastUpdated] DATETIME2 NOT NULL,
    [RecordCount] INT NULL,
    [Status] NVARCHAR(20) NOT NULL DEFAULT 'Completed'
);

CREATE UNIQUE INDEX [IX_DataUpdate_TableName_Type]
    ON [dbo].[DataUpdate] ([TableName], [UpdateType]);

Development vs Production

The same migration scripts run in all environments. For development with file-based data sources, the cache tables are still created but populated from JSON/CSV files instead of Oracle.

Adding New Migrations

1. Create a new SQL file with the next number prefix
2. Write idempotent SQL (use IF NOT EXISTS where appropriate)
3. Build and run - DbUp picks up new embedded scripts automatically

-- Example: 006_AddNewColumn.sql
IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID('dbo.Search') AND name = 'Priority'
)
BEGIN
    ALTER TABLE [dbo].[Search] ADD [Priority] INT NOT NULL DEFAULT 0;
END

Related Documentation

• Overview
• Solution Structure
• Configuration
• Data Flow