Designing a Legacy Asset Finance Database: 4GL Migration to SQL Server 2019

The DemoBank Finance Business Context

DemoBank Finance Ltd was founded in 2003 and operates in the UK, Ireland, and the Netherlands. Their core products are:

• Hire Purchase (HP) — customer pays instalments, owns the asset at the end. Common for vehicles.
• Finance Lease — the finance company retains ownership; residual value is tracked and the lessee can arrange sale at term end.
• Operating Lease — shorter term, maintenance costs tracked, asset returned. Common for commercial equipment.

Their legacy Progress OpenEdge system handled everything from contract origination to payment allocation, arrears management, and regulatory reporting for FCA-regulated consumer contracts. The system was stable but unscalable — report runs that took 4 hours on Progress ran in under 3 minutes on SQL Server, but only after the migration team understood the constraints baked into the original design.

The Ten Design Rules That Drive Everything

Before looking at individual tables, you need to understand the ten constraints that shaped every column definition. These come from the requirement to maintain COBOL VISION file compatibility for the overnight batch layer during the transition period.

These rules are not poor engineering — they are the rational output of a system that had to run on both Progress OpenEdge and SQL Server simultaneously during a two-year parallel operation period. Once parallel operation ends, Phase 2 modernisation can replace all of them.

The 18-Table Schema Overview

The schema is split into four domains:

• Contract domain (6 tables) — the heart of the system: contract header, payment schedule, payments received, status audit, rate history, notes
• Customer domain (4 tables) — customer master, addresses, contacts, credit assessments
• Asset domain (4 tables) — asset master, asset types, insurance, valuations
• Reference / System (4 tables) — status codes, product types, sequence management, audit log

Complete DDL — All 18 Tables

The script below creates the full DemoBank Finance schema on SQL Server 2019 with compatibility level 150. Run it in a fresh database — all tables are created in the dbo schema. Comments mark every intended foreign key relationship that the application enforces in code.

-- ============================================================
-- DemoBank Finance Ltd — Legacy Database Schema
-- SQL Server 2019 (compatibility level 150)
-- COBOL VISION compatible: CHAR fields, INT dates, no FKs,
-- no NULLs, DECIMAL(13,2) amounts, app-managed sequences
-- ============================================================

USE DemoBankDB;
GO

ALTER DATABASE DemoBankDB SET COMPATIBILITY_LEVEL = 150;
GO

-- ============================================================
-- SYSTEM TABLES (create first — other tables depend on them
--               in application code, not in DB constraints)
-- ============================================================

-- SYS_SEQUENCE: application-managed ID sequences
-- Replaces IDENTITY columns throughout the schema.
-- Application does: UPDATE SYS_SEQUENCE SET SQ_NEXT_VAL = SQ_NEXT_VAL + SQ_INCREMENT
--                   WHERE SQ_TABLE_NAME = 'CONT_HDR';
-- then SELECTs SQ_NEXT_VAL as the new ID.
CREATE TABLE SYS_SEQUENCE (
    SQ_TABLE_NAME   CHAR(20)  NOT NULL,   -- table this sequence serves
    SQ_NEXT_VAL     INT       NOT NULL DEFAULT 1,
    SQ_INCREMENT    INT       NOT NULL DEFAULT 1,
    SQ_LAST_UPDATED INT       NOT NULL DEFAULT 0, -- YYYYMMDD
    SQ_UPDATED_BY   CHAR(20)  NOT NULL DEFAULT ' ',
    CONSTRAINT PK_SYS_SEQUENCE PRIMARY KEY (SQ_TABLE_NAME)
);

-- SYS_AUDIT_LOG: system-wide change audit trail
-- Partitioned by AL_YEAR in production (partition function not shown here).
CREATE TABLE SYS_AUDIT_LOG (
    AL_LOG_ID       INT           NOT NULL,  -- from SYS_SEQUENCE('SYS_AUDIT_LOG')
    AL_TABLE_NAME   CHAR(20)      NOT NULL DEFAULT ' ',
    AL_ROW_ID       INT           NOT NULL DEFAULT 0,
    AL_ACTION       CHAR(1)       NOT NULL DEFAULT 'U', -- I=Insert U=Update D=Delete
    AL_FIELD_NAME   CHAR(30)      NOT NULL DEFAULT ' ',
    AL_OLD_VALUE    CHAR(200)     NOT NULL DEFAULT ' ',
    AL_NEW_VALUE    CHAR(200)     NOT NULL DEFAULT ' ',
    AL_CHANGED_BY   CHAR(20)      NOT NULL DEFAULT ' ',
    AL_CHANGE_DATE  INT           NOT NULL DEFAULT 0,   -- YYYYMMDD
    AL_CHANGE_TIME  CHAR(8)       NOT NULL DEFAULT ' ', -- HH:MM:SS
    AL_SESSION_ID   CHAR(40)      NOT NULL DEFAULT ' ',
    AL_YEAR         SMALLINT      NOT NULL DEFAULT 2024, -- partition key
    CONSTRAINT PK_SYS_AUDIT_LOG PRIMARY KEY (AL_LOG_ID)
);

-- ============================================================
-- REFERENCE TABLES
-- ============================================================

-- REF_STATUS: status code definitions
-- Codes used across contracts, customers, assets
CREATE TABLE REF_STATUS (
    RS_STATUS_CODE  CHAR(2)   NOT NULL,   -- AC=Active CL=Closed AR=Arrears
                                          -- WO=Written-Off SU=Suspended
                                          -- PD=Paid-Up  MT=Matured
    RS_DESCRIPTION  CHAR(40)  NOT NULL DEFAULT ' ',
    RS_DOMAIN       CHAR(10)  NOT NULL DEFAULT ' ', -- CONTRACT CUSTOMER ASSET
    RS_ACTIVE_FLAG  CHAR(1)   NOT NULL DEFAULT 'Y',
    RS_SORT_ORDER   SMALLINT  NOT NULL DEFAULT 0,
    CONSTRAINT PK_REF_STATUS PRIMARY KEY (RS_STATUS_CODE, RS_DOMAIN)
);

-- REF_PROD_TYPE: product type codes
-- HP=Hire Purchase, FL=Finance Lease, OL=Operating Lease, BL=Balloon Lease
CREATE TABLE REF_PROD_TYPE (
    PT_PROD_CODE    CHAR(2)   NOT NULL,
    PT_DESCRIPTION  CHAR(50)  NOT NULL DEFAULT ' ',
    PT_OWNERSHIP    CHAR(1)   NOT NULL DEFAULT 'N', -- Y=customer owns at end
    PT_RESIDUAL_FLG CHAR(1)   NOT NULL DEFAULT 'N', -- Y=residual value tracked
    PT_MAINT_FLG    CHAR(1)   NOT NULL DEFAULT 'N', -- Y=maintenance cost tracked
    PT_FCA_REQD     CHAR(1)   NOT NULL DEFAULT 'N', -- Y=FCA consumer credit regs apply
    PT_ACTIVE_FLAG  CHAR(1)   NOT NULL DEFAULT 'Y',
    CONSTRAINT PK_REF_PROD_TYPE PRIMARY KEY (PT_PROD_CODE)
);

-- ============================================================
-- CUSTOMER DOMAIN
-- ============================================================

-- CUST_MAST: customer master record
-- Covers both companies (CM_CUST_TYPE='CO') and individuals ('IN')
CREATE TABLE CUST_MAST (
    CM_CUST_ID      INT           NOT NULL,  -- from SYS_SEQUENCE('CUST_MAST')
    CM_CUST_TYPE    CHAR(2)       NOT NULL DEFAULT 'CO', -- CO=Company IN=Individual
    CM_COMPANY_NAME CHAR(60)      NOT NULL DEFAULT ' ',
    CM_TITLE        CHAR(10)      NOT NULL DEFAULT ' ', -- Mr Mrs Ms Dr etc.
    CM_FIRST_NAME   CHAR(30)      NOT NULL DEFAULT ' ',
    CM_LAST_NAME    CHAR(40)      NOT NULL DEFAULT ' ',
    CM_TRADING_AS   CHAR(60)      NOT NULL DEFAULT ' ',
    CM_REG_NUMBER   CHAR(20)      NOT NULL DEFAULT ' ', -- Companies House number
    CM_VAT_NUMBER   CHAR(15)      NOT NULL DEFAULT ' ',
    CM_DATE_OF_BIRTH INT          NOT NULL DEFAULT 0,   -- YYYYMMDD (individuals only)
    CM_COUNTRY_CODE CHAR(2)       NOT NULL DEFAULT 'GB', -- ISO 3166-1 alpha-2
    CM_STATUS       CHAR(2)       NOT NULL DEFAULT 'AC', -- FK to REF_STATUS (app-enforced)
    CM_CREDIT_LIMIT DECIMAL(13,2) NOT NULL DEFAULT 0,
    CM_CREDIT_GRADE CHAR(2)       NOT NULL DEFAULT '  ', -- AA A1 B1 C1 etc.
    CM_SIC_CODE     CHAR(5)       NOT NULL DEFAULT ' ',  -- UK SIC 2007
    CM_CREATED_DATE INT           NOT NULL DEFAULT 0,   -- YYYYMMDD
    CM_CREATED_BY   CHAR(20)      NOT NULL DEFAULT ' ',
    CM_MODIFIED_DATE INT          NOT NULL DEFAULT 0,
    CM_MODIFIED_BY  CHAR(20)      NOT NULL DEFAULT ' ',
    CM_FCA_CONSENT  CHAR(1)       NOT NULL DEFAULT 'N', -- GDPR/FCA marketing consent
    CM_ACTIVE_FLAG  CHAR(1)       NOT NULL DEFAULT 'Y',
    CONSTRAINT PK_CUST_MAST PRIMARY KEY (CM_CUST_ID)
);

-- CUST_ADDR: customer addresses (multiple types per customer)
-- CA_ADDR_TYPE: RE=Registered CO=Correspondence TR=Trading PR=Previous
CREATE TABLE CUST_ADDR (
    CA_ADDR_ID      INT      NOT NULL, -- from SYS_SEQUENCE('CUST_ADDR')
    CA_CUST_ID      INT      NOT NULL DEFAULT 0, -- FK to CUST_MAST.CM_CUST_ID (app-enforced)
    CA_ADDR_TYPE    CHAR(2)  NOT NULL DEFAULT 'RE',
    CA_ADDR_LINE1   CHAR(40) NOT NULL DEFAULT ' ',
    CA_ADDR_LINE2   CHAR(40) NOT NULL DEFAULT ' ',
    CA_ADDR_LINE3   CHAR(40) NOT NULL DEFAULT ' ',
    CA_CITY         CHAR(30) NOT NULL DEFAULT ' ',
    CA_COUNTY       CHAR(30) NOT NULL DEFAULT ' ',
    CA_POSTCODE     CHAR(10) NOT NULL DEFAULT ' ',
    CA_COUNTRY_CODE CHAR(2)  NOT NULL DEFAULT 'GB',
    CA_FROM_DATE    INT      NOT NULL DEFAULT 0,  -- YYYYMMDD
    CA_TO_DATE      INT      NOT NULL DEFAULT 0,  -- 0 = current address
    CA_PRIMARY_FLAG CHAR(1)  NOT NULL DEFAULT 'N',
    CONSTRAINT PK_CUST_ADDR PRIMARY KEY (CA_ADDR_ID)
);

-- CUST_CONTACT: contact persons at each customer
CREATE TABLE CUST_CONTACT (
    CC_CONTACT_ID   INT      NOT NULL, -- from SYS_SEQUENCE('CUST_CONTACT')
    CC_CUST_ID      INT      NOT NULL DEFAULT 0, -- FK to CUST_MAST.CM_CUST_ID (app-enforced)
    CC_TITLE        CHAR(10) NOT NULL DEFAULT ' ',
    CC_FIRST_NAME   CHAR(30) NOT NULL DEFAULT ' ',
    CC_LAST_NAME    CHAR(40) NOT NULL DEFAULT ' ',
    CC_ROLE         CHAR(30) NOT NULL DEFAULT ' ', -- Director Finance-Dir Accts-Payable etc.
    CC_PHONE        CHAR(20) NOT NULL DEFAULT ' ',
    CC_MOBILE       CHAR(20) NOT NULL DEFAULT ' ',
    CC_EMAIL        CHAR(80) NOT NULL DEFAULT ' ',
    CC_PRIMARY_FLAG CHAR(1)  NOT NULL DEFAULT 'N',
    CC_ACTIVE_FLAG  CHAR(1)  NOT NULL DEFAULT 'Y',
    CC_CREATED_DATE INT      NOT NULL DEFAULT 0,
    CONSTRAINT PK_CUST_CONTACT PRIMARY KEY (CC_CONTACT_ID)
);

-- CUST_CREDIT: credit assessment records
-- One row per assessment event — credit scores and decisions logged over time
CREATE TABLE CUST_CREDIT (
    CX_ASSESS_ID    INT           NOT NULL, -- from SYS_SEQUENCE('CUST_CREDIT')
    CX_CUST_ID      INT           NOT NULL DEFAULT 0, -- FK to CUST_MAST (app-enforced)
    CX_ASSESS_DATE  INT           NOT NULL DEFAULT 0,  -- YYYYMMDD
    CX_AGENCY       CHAR(10)      NOT NULL DEFAULT ' ', -- EXPERIAN EQUIFAX CALLCRED
    CX_SCORE        SMALLINT      NOT NULL DEFAULT 0,
    CX_DECISION     CHAR(2)       NOT NULL DEFAULT '  ', -- AP=Approved DE=Declined RE=Refer
    CX_CREDIT_LIMIT DECIMAL(13,2) NOT NULL DEFAULT 0,
    CX_GRADE        CHAR(2)       NOT NULL DEFAULT '  ',
    CX_ASSESSED_BY  CHAR(20)      NOT NULL DEFAULT ' ',
    CX_NOTES        CHAR(200)     NOT NULL DEFAULT ' ',
    CONSTRAINT PK_CUST_CREDIT PRIMARY KEY (CX_ASSESS_ID)
);

-- ============================================================
-- ASSET DOMAIN
-- ============================================================

-- ASST_TYPE: asset type reference lookup
CREATE TABLE ASST_TYPE (
    AT_TYPE_CODE    CHAR(4)  NOT NULL, -- VCAR VHGV VPLANT MACH PROP EQUIP etc.
    AT_DESCRIPTION  CHAR(50) NOT NULL DEFAULT ' ',
    AT_CATEGORY     CHAR(10) NOT NULL DEFAULT ' ', -- VEHICLE PROPERTY EQUIPMENT
    AT_DEPR_METHOD  CHAR(2)  NOT NULL DEFAULT 'SL', -- SL=Straight-line RB=Reducing-balance
    AT_DEPR_RATE    DECIMAL(5,2) NOT NULL DEFAULT 0, -- annual % depreciation
    AT_RESIDUAL_PCT DECIMAL(5,2) NOT NULL DEFAULT 0, -- default residual value %
    AT_ACTIVE_FLAG  CHAR(1)  NOT NULL DEFAULT 'Y',
    CONSTRAINT PK_ASST_TYPE PRIMARY KEY (AT_TYPE_CODE)
);

-- ASST_MAST: asset master — every financed asset
CREATE TABLE ASST_MAST (
    AM_ASSET_ID     INT           NOT NULL, -- from SYS_SEQUENCE('ASST_MAST')
    AM_TYPE_CODE    CHAR(4)       NOT NULL DEFAULT '    ', -- FK to ASST_TYPE (app-enforced)
    AM_CUST_ID      INT           NOT NULL DEFAULT 0,      -- FK to CUST_MAST (app-enforced)
    AM_DESCRIPTION  CHAR(80)      NOT NULL DEFAULT ' ',
    AM_MAKE         CHAR(30)      NOT NULL DEFAULT ' ',
    AM_MODEL        CHAR(40)      NOT NULL DEFAULT ' ',
    AM_SERIAL_NO    CHAR(30)      NOT NULL DEFAULT ' ',
    AM_REG_NUMBER   CHAR(15)      NOT NULL DEFAULT ' ', -- vehicle registration or equiv.
    AM_YEAR_MFG     SMALLINT      NOT NULL DEFAULT 0,
    AM_COST_PRICE   DECIMAL(13,2) NOT NULL DEFAULT 0,
    AM_RESIDUAL_VAL DECIMAL(13,2) NOT NULL DEFAULT 0,
    AM_LOCATION_CD  CHAR(5)       NOT NULL DEFAULT ' ', -- site/depot code
    AM_COUNTRY_CODE CHAR(2)       NOT NULL DEFAULT 'GB',
    AM_STATUS       CHAR(2)       NOT NULL DEFAULT 'AC', -- FK to REF_STATUS (app-enforced)
    AM_PURCHASE_DATE INT          NOT NULL DEFAULT 0,   -- YYYYMMDD
    AM_DISPOSAL_DATE INT          NOT NULL DEFAULT 0,   -- YYYYMMDD; 0 = not disposed
    AM_ACTIVE_FLAG  CHAR(1)       NOT NULL DEFAULT 'Y',
    AM_CREATED_DATE INT           NOT NULL DEFAULT 0,
    AM_CREATED_BY   CHAR(20)      NOT NULL DEFAULT ' ',
    CONSTRAINT PK_ASST_MAST PRIMARY KEY (AM_ASSET_ID)
);

-- ASST_INSUR: insurance records per asset
CREATE TABLE ASST_INSUR (
    AI_INSUR_ID     INT           NOT NULL, -- from SYS_SEQUENCE('ASST_INSUR')
    AI_ASSET_ID     INT           NOT NULL DEFAULT 0, -- FK to ASST_MAST (app-enforced)
    AI_INSURER      CHAR(50)      NOT NULL DEFAULT ' ',
    AI_POLICY_NO    CHAR(30)      NOT NULL DEFAULT ' ',
    AI_COVER_TYPE   CHAR(10)      NOT NULL DEFAULT ' ', -- COMP TPFT TPO
    AI_INSURED_VAL  DECIMAL(13,2) NOT NULL DEFAULT 0,
    AI_PREMIUM_ANN  DECIMAL(13,2) NOT NULL DEFAULT 0,  -- annual premium
    AI_FROM_DATE    INT           NOT NULL DEFAULT 0,  -- YYYYMMDD
    AI_TO_DATE      INT           NOT NULL DEFAULT 0,  -- YYYYMMDD
    AI_RENEWAL_DATE INT           NOT NULL DEFAULT 0,  -- YYYYMMDD
    AI_ACTIVE_FLAG  CHAR(1)       NOT NULL DEFAULT 'Y',
    CONSTRAINT PK_ASST_INSUR PRIMARY KEY (AI_INSUR_ID)
);

-- ASST_VALUAT: periodic asset valuations
CREATE TABLE ASST_VALUAT (
    AV_VALUAT_ID    INT           NOT NULL, -- from SYS_SEQUENCE('ASST_VALUAT')
    AV_ASSET_ID     INT           NOT NULL DEFAULT 0, -- FK to ASST_MAST (app-enforced)
    AV_VALUAT_DATE  INT           NOT NULL DEFAULT 0,  -- YYYYMMDD
    AV_METHOD       CHAR(10)      NOT NULL DEFAULT ' ', -- GLASS CAP MARKET INTERNAL
    AV_MARKET_VAL   DECIMAL(13,2) NOT NULL DEFAULT 0,
    AV_BOOK_VAL     DECIMAL(13,2) NOT NULL DEFAULT 0,
    AV_FORCED_VAL   DECIMAL(13,2) NOT NULL DEFAULT 0,  -- forced-sale value
    AV_VALUATED_BY  CHAR(30)      NOT NULL DEFAULT ' ',
    AV_NOTES        CHAR(200)     NOT NULL DEFAULT ' ',
    CONSTRAINT PK_ASST_VALUAT PRIMARY KEY (AV_VALUAT_ID)
);

-- ============================================================
-- CONTRACT DOMAIN
-- ============================================================

-- CONT_HDR: contract header — the central record
-- Every HP, Finance Lease, and Operating Lease is one row here.
CREATE TABLE CONT_HDR (
    CH_CONT_ID      INT           NOT NULL, -- from SYS_SEQUENCE('CONT_HDR')
    CH_CONT_REF     CHAR(12)      NOT NULL DEFAULT ' ', -- human-readable ref e.g. CL-2024-00001
    CH_PROD_CODE    CHAR(2)       NOT NULL DEFAULT '  ', -- FK to REF_PROD_TYPE (app-enforced)
    CH_CUST_ID      INT           NOT NULL DEFAULT 0,   -- FK to CUST_MAST (app-enforced)
    CH_ASSET_ID     INT           NOT NULL DEFAULT 0,   -- FK to ASST_MAST (app-enforced)
    CH_STATUS       CHAR(2)       NOT NULL DEFAULT 'AC', -- FK to REF_STATUS (app-enforced)
    CH_CURRENCY     CHAR(3)       NOT NULL DEFAULT 'GBP',
    CH_FINANCE_AMT  DECIMAL(13,2) NOT NULL DEFAULT 0,   -- amount financed
    CH_DEPOSIT      DECIMAL(13,2) NOT NULL DEFAULT 0,
    CH_BALLOON_AMT  DECIMAL(13,2) NOT NULL DEFAULT 0,   -- HP balloon or FL residual
    CH_RATE_TYPE    CHAR(2)       NOT NULL DEFAULT 'FX', -- FX=Fixed VA=Variable TR=Tracker
    CH_INTEREST_RT  DECIMAL(7,4)  NOT NULL DEFAULT 0,   -- annual interest rate
    CH_APR          DECIMAL(7,4)  NOT NULL DEFAULT 0,   -- annual percentage rate (FCA)
    CH_TERM_MONTHS  SMALLINT      NOT NULL DEFAULT 0,
    CH_PAYMENT_FREQ CHAR(2)       NOT NULL DEFAULT 'MO', -- MO=Monthly QU=Quarterly AN=Annual
    CH_PAYMENT_DAY  TINYINT       NOT NULL DEFAULT 1,   -- day of month for payment
    CH_FIRST_PMT_DT INT           NOT NULL DEFAULT 0,   -- YYYYMMDD
    CH_LAST_PMT_DT  INT           NOT NULL DEFAULT 0,   -- YYYYMMDD
    CH_MATURITY_DT  INT           NOT NULL DEFAULT 0,   -- YYYYMMDD; contract end date
    CH_START_DATE   INT           NOT NULL DEFAULT 0,   -- YYYYMMDD
    CH_ARREARS_BAND CHAR(4)       NOT NULL DEFAULT '    ', -- 0-30 31-60 61-90 90PL
    CH_ARREARS_AMT  DECIMAL(13,2) NOT NULL DEFAULT 0,
    CH_TOTAL_PAID   DECIMAL(13,2) NOT NULL DEFAULT 0,
    CH_OUTSTANDING  DECIMAL(13,2) NOT NULL DEFAULT 0,   -- outstanding capital balance
    CH_EARLY_SETT   CHAR(1)       NOT NULL DEFAULT 'N', -- Y=early settlement requested
    CH_SETT_METHOD  CHAR(3)       NOT NULL DEFAULT '   ', -- R78=Rule of 78 ACT=Actuarial
    CH_FCA_REGULATED CHAR(1)      NOT NULL DEFAULT 'N', -- Y=FCA consumer credit
    CH_INTRODUCER   CHAR(10)      NOT NULL DEFAULT ' ', -- broker/dealer code
    CH_BRANCH_CODE  CHAR(5)       NOT NULL DEFAULT ' ',
    CH_COUNTRY_CODE CHAR(2)       NOT NULL DEFAULT 'GB',
    CH_CREATED_DATE INT           NOT NULL DEFAULT 0,
    CH_CREATED_BY   CHAR(20)      NOT NULL DEFAULT ' ',
    CH_MODIFIED_DATE INT          NOT NULL DEFAULT 0,
    CH_MODIFIED_BY  CHAR(20)      NOT NULL DEFAULT ' ',
    CONSTRAINT PK_CONT_HDR PRIMARY KEY (CH_CONT_ID)
);

-- CONT_SCHED: payment schedule, versioned
-- When a payment schedule is revised (rate change, restructure) a new version is added.
-- CH_VERSION: 1 = original schedule, 2 = first revision, etc.
CREATE TABLE CONT_SCHED (
    CS_SCHED_ID     INT           NOT NULL, -- from SYS_SEQUENCE('CONT_SCHED')
    CS_CONT_ID      INT           NOT NULL DEFAULT 0,   -- FK to CONT_HDR (app-enforced)
    CS_VERSION      SMALLINT      NOT NULL DEFAULT 1,
    CS_INSTALMENT_NO SMALLINT     NOT NULL DEFAULT 0,   -- instalment sequence: 1,2,3...N
    CS_DUE_DATE     INT           NOT NULL DEFAULT 0,   -- YYYYMMDD
    CS_CAPITAL_AMT  DECIMAL(13,2) NOT NULL DEFAULT 0,
    CS_INTEREST_AMT DECIMAL(13,2) NOT NULL DEFAULT 0,
    CS_TOTAL_AMT    DECIMAL(13,2) NOT NULL DEFAULT 0,   -- capital + interest
    CS_OPENING_BAL  DECIMAL(13,2) NOT NULL DEFAULT 0,
    CS_CLOSING_BAL  DECIMAL(13,2) NOT NULL DEFAULT 0,
    CS_STATUS       CHAR(2)       NOT NULL DEFAULT 'PE', -- PE=Pending PA=Paid OV=Overdue
    CS_PAID_DATE    INT           NOT NULL DEFAULT 0,   -- YYYYMMDD; 0 = not yet paid
    CS_PAID_AMT     DECIMAL(13,2) NOT NULL DEFAULT 0,
    CONSTRAINT PK_CONT_SCHED PRIMARY KEY (CS_SCHED_ID)
);

-- CONT_PAYMNT: payments received and allocated
-- A single bank receipt can allocate across multiple schedule instalments.
CREATE TABLE CONT_PAYMNT (
    CP_PMT_ID       INT           NOT NULL, -- from SYS_SEQUENCE('CONT_PAYMNT')
    CP_CONT_ID      INT           NOT NULL DEFAULT 0,   -- FK to CONT_HDR (app-enforced)
    CP_SCHED_ID     INT           NOT NULL DEFAULT 0,   -- FK to CONT_SCHED (app-enforced)
    CP_RECEIPT_ID   INT           NOT NULL DEFAULT 0,   -- FK to bank receipt (not in scope)
    CP_PMT_DATE     INT           NOT NULL DEFAULT 0,   -- YYYYMMDD
    CP_VALUE_DATE   INT           NOT NULL DEFAULT 0,   -- YYYYMMDD (bank cleared date)
    CP_AMOUNT       DECIMAL(13,2) NOT NULL DEFAULT 0,
    CP_CAPITAL_ALLOC DECIMAL(13,2) NOT NULL DEFAULT 0,
    CP_INTEREST_ALLOC DECIMAL(13,2) NOT NULL DEFAULT 0,
    CP_CHARGE_ALLOC DECIMAL(13,2) NOT NULL DEFAULT 0,  -- late payment charges
    CP_METHOD       CHAR(4)       NOT NULL DEFAULT 'DD', -- DD=Direct Debit CH=Cheque
                                                         -- TT=Telegraphic TR=Transfer
    CP_NARRATIVE    CHAR(60)      NOT NULL DEFAULT ' ',
    CP_REVERSAL_FLG CHAR(1)       NOT NULL DEFAULT 'N', -- Y=this payment was reversed
    CP_REVERSED_BY  INT           NOT NULL DEFAULT 0,   -- CP_PMT_ID of reversal entry
    CP_POSTED_BY    CHAR(20)      NOT NULL DEFAULT ' ',
    CP_POSTED_DATE  INT           NOT NULL DEFAULT 0,
    CONSTRAINT PK_CONT_PAYMNT PRIMARY KEY (CP_PMT_ID)
);

-- CONT_STATUS: status change audit log
-- Every time a contract status changes, one row is added here.
CREATE TABLE CONT_STATUS (
    CT_STATUS_ID    INT      NOT NULL, -- from SYS_SEQUENCE('CONT_STATUS')
    CT_CONT_ID      INT      NOT NULL DEFAULT 0, -- FK to CONT_HDR (app-enforced)
    CT_FROM_STATUS  CHAR(2)  NOT NULL DEFAULT '  ', -- FK to REF_STATUS (app-enforced)
    CT_TO_STATUS    CHAR(2)  NOT NULL DEFAULT '  ', -- FK to REF_STATUS (app-enforced)
    CT_CHANGE_DATE  INT      NOT NULL DEFAULT 0,   -- YYYYMMDD
    CT_REASON_CODE  CHAR(5)  NOT NULL DEFAULT '     ',
    CT_REASON_TEXT  CHAR(100) NOT NULL DEFAULT ' ',
    CT_CHANGED_BY   CHAR(20) NOT NULL DEFAULT ' ',
    CONSTRAINT PK_CONT_STATUS PRIMARY KEY (CT_STATUS_ID)
);

-- CONT_RATE_HIS: interest rate change history
-- Applies to variable-rate and tracker-rate contracts only.
CREATE TABLE CONT_RATE_HIS (
    CR_RATE_ID      INT           NOT NULL, -- from SYS_SEQUENCE('CONT_RATE_HIS')
    CR_CONT_ID      INT           NOT NULL DEFAULT 0, -- FK to CONT_HDR (app-enforced)
    CR_EFFECTIVE_DT INT           NOT NULL DEFAULT 0,  -- YYYYMMDD
    CR_OLD_RATE     DECIMAL(7,4)  NOT NULL DEFAULT 0,
    CR_NEW_RATE     DECIMAL(7,4)  NOT NULL DEFAULT 0,
    CR_BOE_BASE_RT  DECIMAL(7,4)  NOT NULL DEFAULT 0,  -- Bank of England base rate
    CR_MARGIN       DECIMAL(7,4)  NOT NULL DEFAULT 0,  -- spread above base rate
    CR_TRIGGER      CHAR(10)      NOT NULL DEFAULT ' ', -- BOE-CHANGE ANNUAL MANUAL
    CR_CHANGED_BY   CHAR(20)      NOT NULL DEFAULT ' ',
    CR_CHANGE_DATE  INT           NOT NULL DEFAULT 0,
    CONSTRAINT PK_CONT_RATE_HIS PRIMARY KEY (CR_RATE_ID)
);

-- CONT_NOTE: free-text notes on contracts
-- Notes are plain text, appended over time — never updated, never deleted.
CREATE TABLE CONT_NOTE (
    CN_NOTE_ID      INT       NOT NULL, -- from SYS_SEQUENCE('CONT_NOTE')
    CN_CONT_ID      INT       NOT NULL DEFAULT 0, -- FK to CONT_HDR (app-enforced)
    CN_NOTE_DATE    INT       NOT NULL DEFAULT 0,  -- YYYYMMDD
    CN_NOTE_TIME    CHAR(8)   NOT NULL DEFAULT ' ', -- HH:MM:SS
    CN_NOTE_TYPE    CHAR(5)   NOT NULL DEFAULT '     ', -- ARRS LEGAL COMPL ADMIN QUERY
    CN_NOTE_TEXT    CHAR(500) NOT NULL DEFAULT ' ',
    CN_CREATED_BY   CHAR(20)  NOT NULL DEFAULT ' ',
    CN_VISIBLE_EXT  CHAR(1)   NOT NULL DEFAULT 'N', -- Y=visible to external parties
    CONSTRAINT PK_CONT_NOTE PRIMARY KEY (CN_NOTE_ID)
);

GO

-- ============================================================
-- INDEXES
-- Non-clustered indexes on the most common join and lookup columns
-- ============================================================

-- Contract header: customer, asset, status, reference
CREATE INDEX IX_CONT_HDR_CUST    ON CONT_HDR (CH_CUST_ID);
CREATE INDEX IX_CONT_HDR_ASSET   ON CONT_HDR (CH_ASSET_ID);
CREATE INDEX IX_CONT_HDR_STATUS  ON CONT_HDR (CH_STATUS);
CREATE INDEX IX_CONT_HDR_REF     ON CONT_HDR (CH_CONT_REF);
CREATE INDEX IX_CONT_HDR_MATURITY ON CONT_HDR (CH_MATURITY_DT);

-- Payment schedule: contract, due date, status
CREATE INDEX IX_CONT_SCHED_CONT  ON CONT_SCHED (CS_CONT_ID, CS_VERSION, CS_INSTALMENT_NO);
CREATE INDEX IX_CONT_SCHED_DUE   ON CONT_SCHED (CS_DUE_DATE, CS_STATUS);

-- Payments: contract, payment date
CREATE INDEX IX_CONT_PAYMNT_CONT ON CONT_PAYMNT (CP_CONT_ID, CP_PMT_DATE);

-- Status log: contract
CREATE INDEX IX_CONT_STATUS_CONT ON CONT_STATUS (CT_CONT_ID, CT_CHANGE_DATE);

-- Customer: name, status
CREATE INDEX IX_CUST_MAST_NAME   ON CUST_MAST (CM_LAST_NAME, CM_FIRST_NAME);
CREATE INDEX IX_CUST_MAST_STATUS ON CUST_MAST (CM_STATUS);

-- Customer address: customer
CREATE INDEX IX_CUST_ADDR_CUST   ON CUST_ADDR (CA_CUST_ID);

-- Asset: type, status, serial
CREATE INDEX IX_ASST_MAST_TYPE   ON ASST_MAST (AM_TYPE_CODE, AM_STATUS);
CREATE INDEX IX_ASST_MAST_SERIAL ON ASST_MAST (AM_SERIAL_NO);
CREATE INDEX IX_ASST_MAST_REG    ON ASST_MAST (AM_REG_NUMBER);

-- Asset insurance: asset
CREATE INDEX IX_ASST_INSUR_ASSET ON ASST_INSUR (AI_ASSET_ID, AI_ACTIVE_FLAG);

-- Audit log: table + row + year
CREATE INDEX IX_AUDIT_TABLE_ROW  ON SYS_AUDIT_LOG (AL_TABLE_NAME, AL_ROW_ID, AL_YEAR);

GO

Seed Data — Reference Tables and Sequences

After creating the tables, populate the reference tables and initialise the sequence table. This seed data reflects the real DemoBank Finance product set and status codes.

-- ============================================================
-- SEED DATA
-- ============================================================

-- Status codes (used across contracts, customers, assets)
INSERT INTO REF_STATUS VALUES ('AC', 'Active',             'CONTRACT', 'Y', 1);
INSERT INTO REF_STATUS VALUES ('CL', 'Closed',             'CONTRACT', 'Y', 2);
INSERT INTO REF_STATUS VALUES ('AR', 'In Arrears',         'CONTRACT', 'Y', 3);
INSERT INTO REF_STATUS VALUES ('WO', 'Written Off',        'CONTRACT', 'Y', 4);
INSERT INTO REF_STATUS VALUES ('SU', 'Suspended',          'CONTRACT', 'Y', 5);
INSERT INTO REF_STATUS VALUES ('PD', 'Paid Up Early',      'CONTRACT', 'Y', 6);
INSERT INTO REF_STATUS VALUES ('MT', 'Matured',            'CONTRACT', 'Y', 7);
INSERT INTO REF_STATUS VALUES ('AC', 'Active',             'CUSTOMER', 'Y', 1);
INSERT INTO REF_STATUS VALUES ('IN', 'Inactive',           'CUSTOMER', 'Y', 2);
INSERT INTO REF_STATUS VALUES ('BL', 'Blacklisted',        'CUSTOMER', 'Y', 3);
INSERT INTO REF_STATUS VALUES ('AC', 'Active / On Hire',   'ASSET',    'Y', 1);
INSERT INTO REF_STATUS VALUES ('OF', 'Off Hire',           'ASSET',    'Y', 2);
INSERT INTO REF_STATUS VALUES ('DS', 'Disposed',           'ASSET',    'Y', 3);
INSERT INTO REF_STATUS VALUES ('RP', 'Repossessed',        'ASSET',    'Y', 4);

-- Product types
INSERT INTO REF_PROD_TYPE VALUES ('HP', 'Hire Purchase',           'Y', 'N', 'N', 'Y');
INSERT INTO REF_PROD_TYPE VALUES ('FL', 'Finance Lease',           'N', 'Y', 'N', 'N');
INSERT INTO REF_PROD_TYPE VALUES ('OL', 'Operating Lease',         'N', 'N', 'Y', 'N');
INSERT INTO REF_PROD_TYPE VALUES ('BL', 'Balloon Hire Purchase',   'Y', 'Y', 'N', 'Y');
INSERT INTO REF_PROD_TYPE VALUES ('CB', 'Contract Hire',           'N', 'N', 'Y', 'N');
INSERT INTO REF_PROD_TYPE VALUES ('PL', 'Personal Contract Plan',  'Y', 'Y', 'N', 'Y');

-- Asset types
INSERT INTO ASST_TYPE VALUES ('VCAR',  'Passenger Car',             'VEHICLE',   'RB', 25.00, 30.00, 'Y');
INSERT INTO ASST_TYPE VALUES ('VLCV',  'Light Commercial Vehicle',  'VEHICLE',   'RB', 20.00, 25.00, 'Y');
INSERT INTO ASST_TYPE VALUES ('VHGV',  'Heavy Goods Vehicle (HGV)', 'VEHICLE',   'RB', 15.00, 20.00, 'Y');
INSERT INTO ASST_TYPE VALUES ('MACH',  'Industrial Machinery',      'EQUIPMENT', 'SL', 10.00, 10.00, 'Y');
INSERT INTO ASST_TYPE VALUES ('MPIT',  'Mining / Plant Equipment',  'EQUIPMENT', 'SL',  8.00, 15.00, 'Y');
INSERT INTO ASST_TYPE VALUES ('PROP',  'Commercial Property',       'PROPERTY',  'SL',  2.00, 60.00, 'Y');
INSERT INTO ASST_TYPE VALUES ('AGRI',  'Agricultural Equipment',    'EQUIPMENT', 'RB', 12.00, 20.00, 'Y');
INSERT INTO ASST_TYPE VALUES ('TECH',  'Technology / IT Equipment', 'EQUIPMENT', 'SL', 33.00,  5.00, 'Y');

-- Sequences (one row per table that uses app-managed IDs)
INSERT INTO SYS_SEQUENCE VALUES ('CONT_HDR',       400001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('CONT_SCHED',    2800001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('CONT_PAYMNT',  15000001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('CONT_STATUS',   2500001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('CONT_RATE_HIS',  400001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('CONT_NOTE',     1000001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('CUST_MAST',       50001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('CUST_ADDR',       80001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('CUST_CONTACT',    70001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('CUST_CREDIT',     35001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('ASST_MAST',      120001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('ASST_INSUR',     110001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('ASST_VALUAT',    300001, 1, 20260101, 'MIGRATION');
INSERT INTO SYS_SEQUENCE VALUES ('SYS_AUDIT_LOG',90000001, 1, 20260101, 'MIGRATION');

GO

Sample Records — A Live HP Contract

The following INSERT statements create a complete example: a customer (Hargreaves Transport Ltd), a financed HGV asset, and a 48-month Hire Purchase contract with a partial payment schedule. This is the kind of data you would see in the first wave of the migration.

-- Customer: Hargreaves Transport Ltd (company)
INSERT INTO CUST_MAST VALUES (
    1001,                         -- CM_CUST_ID
    'CO',                         -- CM_CUST_TYPE: Company
    'HARGREAVES TRANSPORT LTD   ',-- CM_COMPANY_NAME (padded to 60)
    '   ',                        -- CM_TITLE (not used for companies)
    '              ',             -- CM_FIRST_NAME
    '                       ',    -- CM_LAST_NAME
    'HARGREAVES HAULAGE         ',-- CM_TRADING_AS
    '09142871            ',       -- CM_REG_NUMBER (Companies House)
    'GB912 3456 78      ',        -- CM_VAT_NUMBER
    0,                            -- CM_DATE_OF_BIRTH (not applicable)
    'GB',                         -- CM_COUNTRY_CODE
    'AC',                         -- CM_STATUS: Active
    500000.00,                    -- CM_CREDIT_LIMIT
    'A1',                         -- CM_CREDIT_GRADE
    '49410',                      -- CM_SIC_CODE: Freight road transport
    20190315,                     -- CM_CREATED_DATE
    'MIGRATION          ',        -- CM_CREATED_BY
    20260101,                     -- CM_MODIFIED_DATE
    'MIGRATION          ',        -- CM_MODIFIED_BY
    'Y',                          -- CM_FCA_CONSENT
    'Y'                           -- CM_ACTIVE_FLAG
);

-- Asset: MAN TGX 18.510 HGV
INSERT INTO ASST_MAST VALUES (
    5001,                         -- AM_ASSET_ID
    'VHGV',                       -- AM_TYPE_CODE
    1001,                         -- AM_CUST_ID
    'MAN TGX 18.510 4X2 BLS                                         ',
    'MAN                           ',
    'TGX 18.510 4X2 BLS                      ',
    'MAN-510-2021-UK-001           ',         -- AM_SERIAL_NO
    'BT21 XYZ       ',            -- AM_REG_NUMBER
    2021,                         -- AM_YEAR_MFG
    115000.00,                    -- AM_COST_PRICE
    23000.00,                     -- AM_RESIDUAL_VAL
    'LEED ',                      -- AM_LOCATION_CD (Leeds depot)
    'GB',
    'AC',
    20210315,                     -- AM_PURCHASE_DATE
    0,                            -- AM_DISPOSAL_DATE: not disposed
    'Y',
    20210315,
    'MIGRATION          '
);

-- Contract: 48-month HP on the HGV, fixed rate 6.75%
INSERT INTO CONT_HDR VALUES (
    3001,                         -- CH_CONT_ID
    'CL-2021-003001  ',           -- CH_CONT_REF (padded to 12)
    'HP',                         -- CH_PROD_CODE
    1001,                         -- CH_CUST_ID
    5001,                         -- CH_ASSET_ID
    'AC',                         -- CH_STATUS
    'GBP',                        -- CH_CURRENCY
    92000.00,                     -- CH_FINANCE_AMT (cost minus deposit)
    23000.00,                     -- CH_DEPOSIT
    0.00,                         -- CH_BALLOON_AMT (no balloon on this contract)
    'FX',                         -- CH_RATE_TYPE: Fixed
    6.7500,                       -- CH_INTEREST_RT: 6.75% p.a.
    7.3200,                       -- CH_APR
    48,                           -- CH_TERM_MONTHS
    'MO',                         -- CH_PAYMENT_FREQ: Monthly
    15,                           -- CH_PAYMENT_DAY: 15th of month
    20210415,                     -- CH_FIRST_PMT_DT
    20250315,                     -- CH_LAST_PMT_DT
    20250315,                     -- CH_MATURITY_DT
    20210315,                     -- CH_START_DATE
    '    ',                       -- CH_ARREARS_BAND: not in arrears
    0.00,                         -- CH_ARREARS_AMT
    0.00,                         -- CH_TOTAL_PAID (will be updated by payments)
    92000.00,                     -- CH_OUTSTANDING: initial balance
    'N',                          -- CH_EARLY_SETT
    '   ',                        -- CH_SETT_METHOD
    'N',                          -- CH_FCA_REGULATED (commercial vehicle)
    'BROKN1    ',                 -- CH_INTRODUCER: broker code
    'LEED ',                      -- CH_BRANCH_CODE
    'GB',
    20210315,                     -- CH_CREATED_DATE
    'ORIGINATE           ',
    20210315,
    'ORIGINATE           '
);

-- First two schedule instalments
INSERT INTO CONT_SCHED VALUES (10001, 3001, 1, 1,  20210415, 1429.16, 517.50, 1946.66, 92000.00, 90570.84, 'PA', 20210415, 1946.66);
INSERT INTO CONT_SCHED VALUES (10002, 3001, 1, 2,  20210515, 1437.20, 509.46, 1946.66, 90570.84, 89133.64, 'PA', 20210515, 1946.66);

-- First two payments
INSERT INTO CONT_PAYMNT VALUES (20001, 3001, 10001, 0, 20210415, 20210416, 1946.66, 1429.16, 517.50, 0.00, 'DD', 'DIRECT DEBIT HARGREAVES TRANS               ', 'N', 0, 'AUTOPOST           ', 20210416);
INSERT INTO CONT_PAYMNT VALUES (20002, 3001, 10002, 0, 20210515, 20210517, 1946.66, 1437.20, 509.46, 0.00, 'DD', 'DIRECT DEBIT HARGREAVES TRANS               ', 'N', 0, 'AUTOPOST           ', 20210517);

GO

Working With INT Dates and CHAR Booleans

The most operationally painful part of the legacy schema is date arithmetic. Here are the standard patterns used in DemoBank Finance stored procedures and reports:

-- Convert INT date (YYYYMMDD) to a real DATE for display or comparison
SELECT
    CH_CONT_REF,
    CONVERT(DATE, CAST(CH_START_DATE AS CHAR(8)), 112) AS ContractStart,
    CONVERT(DATE, CAST(CH_MATURITY_DT AS CHAR(8)), 112) AS MaturityDate,
    DATEDIFF(DAY,
        CONVERT(DATE, CAST(CH_START_DATE  AS CHAR(8)), 112),
        CONVERT(DATE, CAST(CH_MATURITY_DT AS CHAR(8)), 112)
    ) AS ContractDays
FROM CONT_HDR
WHERE CH_STATUS = 'AC';

-- Convert today's date to INT format for queries against INT date columns
DECLARE @TodayInt INT = CAST(FORMAT(GETDATE(), 'yyyyMMdd') AS INT);

-- Find all contracts that matured before today but are still AC (data quality check)
SELECT CH_CONT_ID, CH_CONT_REF, CH_MATURITY_DT
FROM   CONT_HDR
WHERE  CH_STATUS = 'AC'
  AND  CH_MATURITY_DT > 0
  AND  CH_MATURITY_DT < @TodayInt;

-- Filter on CHAR booleans — always use single char literal, padded correctly
SELECT * FROM CONT_HDR WHERE CH_FCA_REGULATED = 'Y';
SELECT * FROM CUST_MAST WHERE CM_ACTIVE_FLAG  = 'Y' AND CM_FCA_CONSENT = 'Y';

-- Arrears contracts with arrears band not blank
SELECT
    CH_CONT_ID,
    CH_CONT_REF,
    RTRIM(CH_ARREARS_BAND) AS ArrearsBand,
    CH_ARREARS_AMT
FROM CONT_HDR
WHERE CH_STATUS = 'AR'
  AND RTRIM(CH_ARREARS_BAND) <> '';

-- Monthly payment run: find all active contracts with payment due today
DECLARE @PaymentDayInt INT = CAST(FORMAT(GETDATE(), 'yyyyMMdd') AS INT);
SELECT
    CH_CONT_ID,
    CH_CONT_REF,
    CS_SCHED_ID,
    CS_TOTAL_AMT
FROM CONT_HDR h
JOIN CONT_SCHED s ON s.CS_CONT_ID = h.CH_CONT_ID
WHERE h.CH_STATUS = 'AC'
  AND s.CS_STATUS = 'PE'
  AND s.CS_DUE_DATE = @PaymentDayInt;

Application-Managed Sequences: How They Work and Where They Break

The SYS_SEQUENCE pattern is the Progress 4GL equivalent of an IDENTITY column. Every application that inserts a new record calls a shared procedure to fetch the next ID:

-- Standard pattern: get the next ID for any table
-- Called by application before every INSERT
CREATE OR ALTER PROCEDURE dbo.usp_GetNextID
    @TableName CHAR(20),
    @NextID    INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE SYS_SEQUENCE
    SET    SQ_NEXT_VAL     = SQ_NEXT_VAL + SQ_INCREMENT,
           SQ_LAST_UPDATED = CAST(FORMAT(GETDATE(),'yyyyMMdd') AS INT),
           SQ_UPDATED_BY   = SYSTEM_USER
    WHERE  SQ_TABLE_NAME   = @TableName;

    SELECT @NextID = SQ_NEXT_VAL
    FROM   SYS_SEQUENCE
    WHERE  SQ_TABLE_NAME = @TableName;
END;
GO

-- Usage
DECLARE @NewContID INT;
EXEC dbo.usp_GetNextID 'CONT_HDR', @NewContID OUTPUT;
-- Now INSERT CONT_HDR using @NewContID

The problem is concurrency. Under high load, multiple sessions call usp_GetNextID simultaneously for the same table. The UPDATE takes a row-level lock on the SYS_SEQUENCE row — but because the UPDATE and SELECT are two separate statements, a second caller can read a stale value between them. The correct fix is to use UPDATE ... OUTPUT in a single atomic statement:

-- Safer version using OUTPUT clause (atomic UPDATE + read)
CREATE OR ALTER PROCEDURE dbo.usp_GetNextID_Safe
    @TableName CHAR(20),
    @NextID    INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE SYS_SEQUENCE
    SET    SQ_NEXT_VAL     = SQ_NEXT_VAL + SQ_INCREMENT,
           SQ_LAST_UPDATED = CAST(FORMAT(GETDATE(),'yyyyMMdd') AS INT)
    OUTPUT INSERTED.SQ_NEXT_VAL INTO @ResultTable(ID)
    WHERE  SQ_TABLE_NAME = @TableName;

    -- even cleaner: use a table variable to capture OUTPUT
    DECLARE @T TABLE (NewID INT);
    UPDATE SYS_SEQUENCE
    SET    SQ_NEXT_VAL = SQ_NEXT_VAL + SQ_INCREMENT
    OUTPUT INSERTED.SQ_NEXT_VAL INTO @T(NewID)
    WHERE  SQ_TABLE_NAME = @TableName;

    SELECT @NextID = NewID FROM @T;
END;
GO

What's Next in the DemoBank Finance Series

This article covered the full schema DDL, the ten design rules from COBOL VISION compatibility, and the core operational patterns for working with INT dates, CHAR booleans, and app-managed sequences. The rest of the series covers:

• Article 2 — COBOL VISION Files and SQL Server: The three COBOL copybooks (CONT-HDR.cpy, CUST-MAST.cpy, ASST-MAST.cpy), how they map to SQL Server column definitions, and how to validate record-length compatibility.
• Article 3 — No Foreign Keys: Application-Managed Relationships: How Progress 4GL enforced referential integrity in application code, what breaks when you migrate without it, and the orphaned-record audit queries that find violations.
• Article 4 — The Migration Plan: Phased cutover strategy, SSIS pipeline design, and the data quality issues found in every real 4GL migration.
• Article 5 — Before vs After: Performance, developer productivity, report latency, and compliance improvements after Phase 2 modernisation.

The full schema DDL from this article is self-contained — you can run it on any SQL Server 2019+ instance to build a working DemoBank Finance test environment and follow along with the rest of the series.

Frequently Asked Questions

Why use CHAR instead of VARCHAR throughout?

COBOL VISION files are fixed-length records. Each record in the VISION file has a fixed byte size determined by summing all field lengths. If any field were VARCHAR, the record length would be variable and the VISION compatibility layer — which reads at fixed byte offsets — would break. All strings stay as CHAR until VISION compatibility is retired in Phase 2.

What is the Rule of 78 for early settlement?

The Rule of 78 is a method of calculating an interest rebate when a hire purchase customer settles early. It distributes the total interest charge across the term in a weighted sum: more interest is charged in early months and less in later months (because the outstanding balance is highest at the start). The rebate is the proportion of interest not yet "earned" by the lender. It is still widely used in UK consumer credit and is referenced in the CH_SETT_METHOD field.

Why are the sequence starting values so high in the seed data?

The starting values reflect post-migration IDs. The real data for 85,000+ contracts and 15 million+ payments was migrated first; the sequence table is initialised to one past the highest ID already in the data, so new records inserted after go-live never collide with migrated records.