Database Migration Guide

Procedures for migrating, upgrading, and managing database schema changes in Axiom Genesis.

Overview

Axiom Genesis uses a patch-based migration system that tracks applied changes in the app_patch_tracker table. This ensures consistent schema across environments.

Migration Architecture

axiom-genesis-db/
├── mssql/
│   └── patches/
│       └── v1.0.0/
│           ├── index.json          # Migration manifest
│           ├── tables/             # Table definitions
│           ├── indexes/            # Index definitions
│           ├── views/              # View definitions
│           └── functions/          # Stored procedures
├── postgres/
│   └── patches/
│       └── v1.0.0/
│           └── [same structure]
└── src/
    ├── scripts/
    │   ├── apply.ts               # Apply migrations
    │   ├── status.ts              # Check migration status
    │   └── rollback.ts            # Rollback migrations
    └── core/
        ├── patch-runner.ts        # Migration executor
        └── tracker.ts             # Patch tracking

Running Migrations

Prerequisites

Database connection configured in environment
Node.js 18+ installed
Database user has DDL permissions

Apply Migrations

# Navigate to database project
cd axiom-genesis-db

# Apply all pending migrations
npm run apply

# Apply with interactive mode (prompts for connection)
npm run apply -- --interactive

# Apply specific version
npm run apply -- --version v1.0.0

# Dry run (show what would be applied)
npm run apply -- --dry-run

Check Status

# Show migration status
npm run status

# Show status from database tracker
npm run status -- --from-db

# Show detailed status
npm run status -- --verbose

Rollback

# Rollback last migration
npm run rollback

# Rollback specific version
npm run rollback -- --version v1.0.0

# Interactive rollback
npm run rollback -- --interactive

Migration Manifest (index.json)

Each version has an index.json defining the migration phases:

{
  "version": "1.0.0",
  "description": "Initial schema setup",
  "phases": [
    {
      "name": "tables",
      "description": "Create core tables",
      "files": [
        "tables/app_tenants.sql",
        "tables/app_users.sql",
        "tables/app_roles.sql"
      ]
    },
    {
      "name": "indexes",
      "description": "Create indexes",
      "files": [
        "indexes/idx_app_users.sql"
      ]
    },
    {
      "name": "views",
      "description": "Create views",
      "files": [
        "views/vw_user_roles.sql"
      ]
    }
  ]
}

Patch Tracking

Migrations are tracked in app_patch_tracker:

SELECT pht_version, pht_category, pht_status, pht_applied_at
FROM app_patch_tracker
WHERE pht_category = 'RADICS_CFG'
ORDER BY pht_applied_at DESC;

Column	Description
`pht_version`	Patch version (e.g., "1.0.0")
`pht_category`	Database category (RADICS_CFG, RADICS_DATA)
`pht_status`	Status (pending, applied, failed, rolled_back)
`pht_phases`	JSON array of executed phases
`pht_applied_at`	Timestamp of application
`pht_duration_ms`	Execution duration in milliseconds
`pht_error_message`	Error details if failed

Writing Migrations

Table Creation

-- tables/app_example.sql
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'app_example')
BEGIN
    CREATE TABLE app_example (
        exm_id            BIGINT IDENTITY(1,1) PRIMARY KEY,
        exm_tenant_id     BIGINT NOT NULL,
        exm_code          NVARCHAR(100) NOT NULL,
        exm_name          NVARCHAR(200),
        exm_status        INT DEFAULT 1,
        exm_is_deleted    TINYINT DEFAULT 0,
        exm_audit_data    json NULL,
        
        CONSTRAINT FK_example_tenant 
            FOREIGN KEY (exm_tenant_id) 
            REFERENCES app_tenants(tnt_id)
    );
    
    PRINT 'Created table: app_example';
END
ELSE
BEGIN
    PRINT 'Table already exists: app_example';
END
GO

Adding Columns

-- patches/v1.1.0/alterations/add_example_column.sql
IF NOT EXISTS (
    SELECT * FROM sys.columns 
    WHERE object_id = OBJECT_ID('app_example') 
      AND name = 'exm_new_column'
)
BEGIN
    ALTER TABLE app_example
    ADD exm_new_column NVARCHAR(500) NULL;
    
    PRINT 'Added column: exm_new_column to app_example';
END
GO

Creating Indexes

-- indexes/idx_app_example.sql
IF NOT EXISTS (
    SELECT * FROM sys.indexes 
    WHERE name = 'idx_example_tenant_code'
)
BEGIN
    CREATE INDEX idx_example_tenant_code
    ON app_example(exm_tenant_id, exm_code)
    WHERE exm_is_deleted = 0;
    
    PRINT 'Created index: idx_example_tenant_code';
END
GO

Multi-Database Migrations

MSSQL vs PostgreSQL

Maintain parallel migration files for each database:

mssql/patches/v1.0.0/tables/app_example.sql    -- MSSQL syntax
postgres/patches/v1.0.0/tables/app_example.sql -- PostgreSQL syntax

Key Differences

Feature	MSSQL	PostgreSQL
Auto-increment	`BIGINT IDENTITY(1,1)`	`BIGSERIAL`
JSON type	`json`	`JSONB` (recommended)
Boolean	`TINYINT`	`SMALLINT` or `BOOLEAN`
String	`NVARCHAR(n)`	`VARCHAR(n)`
Conditional	`IF NOT EXISTS`	`IF NOT EXISTS` clause

Environment-Specific Migrations

Configuration

# Development
export DB_HOST=localhost
export DB_NAME=RADICS_CFG
export DB_USER=sa
export DB_PASSWORD=your_password

# Production (use Key Vault)
export DB_CONNECTION_STRING="@Microsoft.KeyVault(SecretUri=...)"

Running Against Different Environments

# Development
npm run apply -- --env development

# Staging
npm run apply -- --env staging

# Production (requires confirmation)
npm run apply -- --env production --confirm

Rollback Procedures

Automatic Rollback

If a migration fails, the system attempts automatic rollback:

Migration v1.0.0 failed at phase: indexes
Attempting automatic rollback...
Rolling back phase: tables
Rollback complete.

Manual Rollback

For manual rollback of specific changes:

-- 1. Check current state
SELECT * FROM app_patch_tracker WHERE pht_version = '1.0.0';

-- 2. Manually revert changes
DROP TABLE IF EXISTS app_example;
DROP INDEX IF EXISTS idx_example_tenant_code;

-- 3. Update tracker
UPDATE app_patch_tracker
SET pht_status = 'rolled_back',
    pht_rollback_at = GETDATE()
WHERE pht_version = '1.0.0';

Best Practices

DO

✅ Use idempotent scripts (IF NOT EXISTS)
✅ Include rollback scripts for complex changes
✅ Test migrations on a copy of production data
✅ Use transactions where possible
✅ Document breaking changes
✅ Version control all migration files

DON'T

❌ Modify already-applied migration files
❌ Delete columns without data migration
❌ Run migrations during peak hours
❌ Skip migration testing
❌ Use DROP TABLE without backup

Troubleshooting

Migration Stuck

-- Check for blocking queries
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0;

-- Kill blocking session if safe
KILL <session_id>;

Patch Tracker Corrupted

-- Reset patch tracker entry
UPDATE app_patch_tracker
SET pht_status = 'pending',
    pht_error_message = NULL
WHERE pht_version = '1.0.0';

Connection Timeout

# Increase timeout
npm run apply -- --timeout 600000  # 10 minutes

Overview​

Migration Architecture​

Running Migrations​

Prerequisites​

Apply Migrations​

Check Status​

Rollback​

Migration Manifest (index.json)​

Patch Tracking​

Writing Migrations​

Table Creation​

Adding Columns​

Creating Indexes​

Multi-Database Migrations​

MSSQL vs PostgreSQL​

Key Differences​

Environment-Specific Migrations​

Configuration​

Running Against Different Environments​

Rollback Procedures​

Automatic Rollback​

Manual Rollback​

Best Practices​

DO​

DON'T​

Troubleshooting​

Migration Stuck​

Patch Tracker Corrupted​

Connection Timeout​

See Also​