Database Migrations

BANA uses two complementary migration systems:

Drizzle Kit CLI -- Generates and applies DDL migration SQL from schema definitions (managed in @nx/core)
Seed Migration Framework -- Runs seed data processes via bootstrapMigration() (used by each service package)

Source: packages/core/src/migrations/

Technology Stack

Tool	Version	Purpose
Drizzle ORM	^0.45.1	Type-safe query builder and schema definitions
Drizzle Kit	^0.31.8	Migration CLI tool (generate SQL, apply to database)
PostgreSQL	Latest	Database engine
Bun	>=1.3.8	Runtime for CLI scripts

Directory Structure

packages/core/
├── src/
│   ├── models/
│   │   └── schemas/
│   │       ├── common.ts               # Shared pgSchema objects, column generators
│   │       ├── public/                  # Public schema models (30)
│   │       ├── pricing/                 # Pricing schema models (7)
│   │       ├── allocation/              # Allocation schema models (4)
│   │       ├── sale/                    # Sale schema models (2)
│   │       ├── inventory/               # Inventory schema models (8)
│   │       ├── finance/                 # Finance schema models (3)
│   │       └── payment/                 # Payment schema models (1)
│   ├── migrations/
│   │   ├── migrators/
│   │   │   ├── cli.ts                  # Migration CLI entry point
│   │   │   ├── all.ts                  # Config: all schemas combined
│   │   │   ├── public.ts              # Config: public schema only
│   │   │   ├── pricing.ts            # Config: pricing schema only
│   │   │   ├── allocation.ts          # Config: allocation schema only
│   │   │   ├── sale.ts               # Config: sale schema only
│   │   │   ├── inventory.ts          # Config: inventory schema only
│   │   │   ├── finance.ts            # Config: finance schema only
│   │   │   ├── payment.ts            # Config: payment schema only
│   │   │   ├── identity.ts           # Config: identity schema only
│   │   │   ├── ledger.ts             # Config: ledger schema only
│   │   │   └── outreach.ts           # Config: outreach schema only
│   │   └── drizzle/                    # Generated migration output
│   │       ├── public/                 # Public schema SQL + meta/
│   │       ├── pricing/                # Pricing schema SQL + meta/
│   │       ├── allocation/             # Allocation schema SQL + meta/
│   │       ├── sale/                   # Sale schema SQL + meta/
│   │       ├── inventory/              # Inventory schema SQL + meta/
│   │       ├── finance/                # Finance schema SQL + meta/
│   │       ├── payment/                # Payment schema SQL + meta/
│   │       ├── identity/               # Identity schema SQL + meta/
│   │       ├── ledger/                 # Ledger schema SQL + meta/
│   │       └── outreach/              # Outreach schema SQL + meta/
│   └── helpers/
│       ├── bootstraps/
│       │   └── migration.ts            # bootstrapMigration() helper
│       └── migration/
│           ├── helper.ts               # MigrationHelper class
│           ├── process-loader.ts       # createMigrationProcessLoader()
│           └── types.ts                # TMigrationProcess, TMigrationHooks
└── package.json                        # db:generate, db:migrate scripts

Drizzle Kit CLI

Overview

The CLI tool at src/migrations/migrators/cli.ts wraps Drizzle Kit to support per-schema migration management. It requires RUN_MODE=migrate as a safety guard.

Usage

bash

bun run src/migrations/migrators/cli.ts <command> [schema] [options]

Commands

Command	Description
`generate`	Generate migration SQL files by comparing schema definitions against the database
`migrate`	Apply pending migrations to the database

Arguments

Argument	Description
`schema`	Target schema name: `public`, `pricing`, `allocation`, `sale`, `inventory`, `finance`, `payment`, `identity`, `ledger`, `outreach`
`--all`	Run the command for all 7 schemas sequentially

Safety Guard: RUN_MODE

The CLI requires the RUN_MODE environment variable to be set to migrate. This prevents accidental migration execution:

bash

# Without RUN_MODE -- will fail
bun run src/migrations/migrators/cli.ts generate public
# Error: RUN_MODE environment variable must be set to "migrate"

# With RUN_MODE -- will succeed
RUN_MODE=migrate bun run src/migrations/migrators/cli.ts generate public

Examples

bash

# Generate migration SQL for a single schema
RUN_MODE=migrate bun run src/migrations/migrators/cli.ts generate public
RUN_MODE=migrate bun run src/migrations/migrators/cli.ts generate sale

# Apply migrations for a single schema
RUN_MODE=migrate bun run src/migrations/migrators/cli.ts migrate inventory
RUN_MODE=migrate bun run src/migrations/migrators/cli.ts migrate finance

# Generate migrations for ALL schemas
RUN_MODE=migrate bun run src/migrations/migrators/cli.ts generate --all

# Apply migrations for ALL schemas
RUN_MODE=migrate bun run src/migrations/migrators/cli.ts migrate --all

Package.json Scripts

The @nx/core package provides shorthand scripts with RUN_MODE=migrate pre-configured:

json

{
  "db:generate": "RUN_MODE=migrate bun run src/migrations/migrators/cli.ts generate",
  "db:migrate": "RUN_MODE=migrate bun run src/migrations/migrators/cli.ts migrate",
  "db:generate:all": "RUN_MODE=migrate bun run src/migrations/migrators/cli.ts generate --all",
  "db:migrate:all": "RUN_MODE=migrate bun run src/migrations/migrators/cli.ts migrate --all"
}

Usage with bun run:

bash

cd packages/core

# Single schema
bun run db:generate public
bun run db:migrate sale

# All schemas
bun run db:generate:all
bun run db:migrate:all

Per-Schema Configuration

Each schema has its own Drizzle Kit config file in src/migrations/migrators/. The configs follow a consistent pattern:

Config Pattern

typescript

// src/migrations/migrators/{schema}.ts
import 'dotenv-flow/config';

import { applicationEnvironment, int, LoggerFactory } from '@venizia/ignis';
import { defineConfig } from 'drizzle-kit';
import { EnvironmentKeys, PostgresSchemas } from './../../common';

const migrate = () => {
  const databaseConfigs = {
    host: applicationEnvironment.get<string>(EnvironmentKeys.APP_ENV_POSTGRES_HOST),
    port: int(applicationEnvironment.get<number>(EnvironmentKeys.APP_ENV_POSTGRES_PORT)),
    database: applicationEnvironment.get<string>(EnvironmentKeys.APP_ENV_POSTGRES_DATABASE),
    user: applicationEnvironment.get<string>(EnvironmentKeys.APP_ENV_POSTGRES_USERNAME),
    password: applicationEnvironment.get<string>(EnvironmentKeys.APP_ENV_POSTGRES_PASSWORD),
    ssl: false,
  };

  return defineConfig({
    dialect: 'postgresql',
    out: './src/migrations/drizzle/{schema}',         // Output directory per schema
    schema: ['./src/models/schemas/{schema}/**/schema.ts',
             './src/models/schemas/{schema}/common.ts'],  // Schema source files
    dbCredentials: databaseConfigs,
    schemaFilter: [PostgresSchemas.{SCHEMA}],         // Filter to this schema only
  });
};

export default migrate();

Config Summary

Config File	Schema Filter	Output Directory	Schema Source
`public.ts`	`public`	`drizzle/public/`	`schemas/public/**/schema.ts`
`pricing.ts`	`pricing`	`drizzle/pricing/`	`schemas/pricing/**/schema.ts`
`allocation.ts`	`allocation`	`drizzle/allocation/`	`schemas/allocation/**/schema.ts`
`sale.ts`	`sale`	`drizzle/sale/`	`schemas/sale/**/schema.ts`
`inventory.ts`	`inventory`	`drizzle/inventory/`	`schemas/inventory/**/schema.ts`
`finance.ts`	`finance`	`drizzle/finance/`	`schemas/finance/**/schema.ts`
`payment.ts`	`payment`	`drizzle/payment/`	`schemas/payment/**/schema.ts`
`identity.ts`	`identity`	`drizzle/identity/`	`schemas/identity/**/schema.ts`
`ledger.ts`	`ledger`	`drizzle/ledger/`	`schemas/ledger/**/schema.ts`
`outreach.ts`	`outreach`	`drizzle/outreach/`	`schemas/outreach/**/schema.ts`
`all.ts`	All 10 schemas	`drizzle/` (root)	`dist/models/schemas/*.js`

Note: The all.ts config differs from single-schema configs. It uses compiled .js files from dist/ and filters all 10 schemas, outputting to the root drizzle/ directory.

Key Differences: Single vs All

Aspect	Single Schema Config	All Config (`all.ts`)
Schema source	TypeScript source files (`./src/models/schemas/{schema}/**/schema.ts`)	Compiled JavaScript (`./dist/models/schemas/*.js`)
Output directory	Per-schema subdirectory (`drizzle/{schema}/`)	Root drizzle directory (`drizzle/`)
Schema filter	Single schema	All 7 schemas
Build required	No (reads `.ts` directly)	Yes (reads compiled `.js`)

Migration Workflow

Migration Output

Each schema directory contains generated SQL files and a journal for tracking:

src/migrations/drizzle/{schema}/
├── 0000_initial_migration.sql
├── 0001_add_new_column.sql
├── 0002_create_table.sql
└── meta/
    └── _journal.json

Journal File

The meta/_journal.json tracks migration history:

json

{
  "version": "7",
  "dialect": "postgresql",
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1705708800000,
      "tag": "0000_initial_migration",
      "breakpoints": true
    }
  ]
}

Environment Variables

All migrator configs read database credentials from the environment via dotenv-flow:

Variable	Required	Description
`RUN_MODE`	Yes	Must be `migrate` (safety guard)
`APP_ENV_POSTGRES_HOST`	Yes	PostgreSQL host
`APP_ENV_POSTGRES_PORT`	Yes	PostgreSQL port
`APP_ENV_POSTGRES_DATABASE`	Yes	Database name
`APP_ENV_POSTGRES_USERNAME`	Yes	Database username
`APP_ENV_POSTGRES_PASSWORD`	Yes	Database password

Environment files are loaded via dotenv-flow/config at the top of each config file.

Seed Data Migrations

Beyond DDL schema changes, each service package can define seed data processes that insert or update initial records (roles, configurations, default categories, etc.). This is managed by the MigrationHelper framework in @nx/core.

bootstrapMigration()

The bootstrapMigration() helper boots the application, registers the MigrationRepository, and runs all seed processes:

typescript

// packages/{package}/src/migrate.ts
import { bootstrapMigration } from '@nx/core';
import { Application } from './application';
import { getMigrationProcesses } from './migrations/processes/migration-process';

bootstrapMigration({
  ApplicationClass: Application,
  getMigrationProcesses,
})
  .then(() => {
    process.exit(0);
  })
  .catch(err => {
    console.error('Cannot migrate database schema', err);
    process.exit(1);
  });

createMigrationProcessLoader()

Defines which seed processes to load via dynamic imports:

typescript

// packages/{package}/src/migrations/processes/migration-process.ts
import { createMigrationProcessLoader } from '@nx/core';

export const getMigrationProcesses = createMigrationProcessLoader({
  seedPaths: [
    'identity-0001-seed-roles',
    'identity-0002-seed-users',
    'identity-0003-seed-table-configurations',
    'identity-0004-seed-mail-configurations',
  ],
  importFn: path => import(`../processes/${path}.js`),
});

TMigrationProcess

Each seed process implements the TMigrationProcess type:

typescript

type TMigrationProcess = {
  name: string;                                                    // Unique process name
  migrateFn: (opts: { context: BaseApplication }) => ValueOrPromise<void>;  // Migration logic
  cleanFn?: (opts: { context: BaseApplication }) => ValueOrPromise<void>;   // Optional cleanup
  options?: {
    alwaysRun?: boolean;  // If true, runs even if previously completed
  };
};

Seed Process Example

typescript

// packages/identity/src/migrations/processes/identity-0001-seed-roles.ts
import { INameI18n, TMigrationProcess } from '@nx/core';
import { RoleRepository } from '@/repositories';

const defaultRoles: Array<{ identifier: string; name: INameI18n; priority: number }> = [
  { identifier: '999-super-admin', name: { en: 'Super Admin', vi: 'Sieu Quan Tri Vien' }, priority: 999 },
  { identifier: '998-admin', name: { en: 'Admin', vi: 'Quan Tri Vien' }, priority: 998 },
  // ...
];

const migrationProcess: TMigrationProcess = {
  name: __filename.slice(__dirname.length + 1),
  migrateFn: async ({ context: application }) => {
    const roleRepository = application.get<RoleRepository>({
      key: 'repositories.RoleRepository',
    });

    for (const role of defaultRoles) {
      const existing = await roleRepository.findOne({
        filter: { where: { identifier: role.identifier } },
      });

      if (!existing) {
        await roleRepository.create({ data: role });
      } else {
        await roleRepository.updateById({ id: existing.id, data: role });
      }
    }
  },
};

export default migrationProcess;

MigrationHelper

The MigrationHelper class manages the complete seed migration workflow:

Cleanup phase -- Runs cleanFn for each process (optional)
Migration phase -- Runs migrateFn for each process, tracking status in the Migration table

Key behaviors:

Behavior	Description
Idempotent execution	Each process is recorded in the `public.Migration` table. Already-completed processes are skipped
`alwaysRun` override	Processes with `options.alwaysRun = true` run every time, even if previously completed
Status tracking	Each process is recorded as `SUCCESS` or `FAIL` in the Migration table
Lifecycle hooks	Supports `beforeCleanup`, `afterCleanup`, `beforeMigration`, `afterMigration`, `onMigrationSuccess`, `onMigrationError` hooks
Sequential execution	Processes run in order (array position determines execution order)

Packages Using Seed Migrations

Package	Seed Processes
`@nx/identity`	`identity-0001-seed-roles`, `identity-0002-seed-users`, `identity-0003-seed-table-configurations`, `identity-0004-seed-mail-configurations`, `identity-0005-seed-permissions`, `identity-0006-seed-sms-configurations`
`@nx/commerce`	`commerce-0001-seed-tax-types`, `commerce-0002-seed-permissions`, `commerce-0003-seed-typesense-embedding-configs`
`@nx/finance`	`finance-0001-seed-categories`, `finance-0002-seed-permissions`
`@nx/inventory`	`inventory-0001-seed-inventory-tracking-types`, `inventory-0002-seed-permissions`
`@nx/payment`	`payment-0001-seed-vnpay-qr-mms-configuration`, `payment-0002-seed-vnpay-phone-pos-configuration`, `payment-0003-seed-permissions`
`@nx/pricing`	`pricing-0001-seed-permissions`
`@nx/sale`	`sale-0001-seed-permissions`
`@nx/signal`	`signal-0001-seed-permissions`
`@nx/ledger`	`ledger-0001-seed-permissions`
`@nx/outreach`	(no seeds yet)
`@nx/search`	(no seeds yet)

Running Seed Migrations

Each package provides a migrate:dev script:

bash

cd packages/identity
bun run migrate:dev    # Runs with .env.development

This executes src/migrate.ts which calls bootstrapMigration().

Complete Migration Workflow

When setting up a new environment or adding schema changes, follow this sequence:

Step-by-Step

Modify schema definitions in packages/core/src/models/schemas/{schema}/

Generate migration SQL from packages/core/:

bash

bun run db:generate public     # or: bun run db:generate:all

Review the generated SQL in src/migrations/drizzle/{schema}/

Apply DDL migrations to the database:

bash

bun run db:migrate public      # or: bun run db:migrate:all

Run seed migrations for each service package that needs data:

bash

cd packages/identity && bun run migrate:dev
cd packages/payment && bun run migrate:dev

Commit schema files, generated SQL, and seed processes together

Troubleshooting

RUN_MODE Error

[cli] Error: RUN_MODE environment variable must be set to "migrate"

The CLI requires RUN_MODE=migrate. Use the package.json scripts (bun run db:generate, bun run db:migrate) which set this automatically.

Schema Not Found

Error: Unknown schema "order"
Available schemas: public, pricing, allocation, sale, inventory, finance, payment

Ensure you use a valid schema name from PostgresSchemas.SCHEME_SET. The order schema was renamed to sale.

Migration Not Detecting Changes

Schema source files are TypeScript -- ensure the schema glob patterns match your file locations
For the all.ts config, a build is required first (bun run rebuild) since it reads from dist/
Verify the correct schemaFilter is set in the config

Connection Errors

Check environment variables are set correctly. The configs load from dotenv-flow, which reads .env.development (or .env.local):

bash

# Verify environment
cat .env.development | grep APP_ENV_POSTGRES

Database Overview -- Schema summary, common columns, shared types
Public Schema -- 30 models in the public schema
Pricing Schema -- 7 pricing models
Allocation Schema -- 4 allocation models
Sale Schema -- 2 sale order models
Inventory Schema -- 8 inventory models
Finance Schema -- 3 finance models
Payment Schema -- 1 webhook config model
Outreach Schema -- 2 outreach models
Entity Relationship Diagram -- Comprehensive ERD for all models

Client Guide

Providers

Invoice Types

Database Migrations ​

Technology Stack ​

Directory Structure ​

Drizzle Kit CLI ​

Overview ​

Usage ​

Commands ​

Arguments ​

Safety Guard: RUN_MODE ​

Examples ​

Package.json Scripts ​

Per-Schema Configuration ​

Config Pattern ​

Config Summary ​

Key Differences: Single vs All ​

Migration Workflow ​

Migration Output ​

Journal File ​

Environment Variables ​

Seed Data Migrations ​

bootstrapMigration() ​

createMigrationProcessLoader() ​

TMigrationProcess ​

Seed Process Example ​

MigrationHelper ​

Packages Using Seed Migrations ​

Running Seed Migrations ​

Complete Migration Workflow ​

Step-by-Step ​

Troubleshooting ​

RUN_MODE Error ​

Schema Not Found ​

Migration Not Detecting Changes ​

Connection Errors ​

Related Documentation ​

Database Migrations

Technology Stack

Directory Structure

Drizzle Kit CLI

Overview

Usage

Commands

Arguments

Safety Guard: RUN_MODE

Examples

Package.json Scripts

Per-Schema Configuration

Config Pattern

Config Summary

Key Differences: Single vs All

Migration Workflow

Migration Output

Journal File

Environment Variables

Seed Data Migrations

bootstrapMigration()

createMigrationProcessLoader()

TMigrationProcess

Seed Process Example

MigrationHelper

Packages Using Seed Migrations

Running Seed Migrations

Complete Migration Workflow

Step-by-Step

Troubleshooting

RUN_MODE Error

Schema Not Found

Migration Not Detecting Changes

Connection Errors

Related Documentation