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/                  # Shared pgSchema objects + column generators (no tables)
│   │       ├── public/                  # Public schema entities (25)
│   │       ├── helpdesk/                # Helpdesk schema entities (30)
│   │       ├── inventory/               # Inventory schema entities (17)
│   │       ├── sale/                    # Sale schema entities (16)
│   │       ├── invoice/                 # Invoice schema entities (9)
│   │       ├── pricing/                 # Pricing schema entities (9)
│   │       ├── identity/                # Identity schema entities (8)
│   │       ├── ledger/                  # Ledger schema entities (8)
│   │       ├── finance/                 # Finance schema entities (6)
│   │       ├── tax/                     # Tax schema entities (6)
│   │       ├── licensing/               # Licensing schema entities (5)
│   │       ├── allocation/              # Allocation schema entities (4)
│   │       ├── outreach/                # Outreach schema entities (2)
│   │       └── payment/                 # Payment schema entities (0, reserved)
│   ├── migrations/
│   │   ├── migrators/
│   │   │   ├── cli.ts                  # Migration CLI entry point (handles --all flag)
│   │   │   ├── allocation.ts          # Config: allocation schema only
│   │   │   ├── finance.ts             # Config: finance schema only
│   │   │   ├── helpdesk.ts            # Config: helpdesk schema only
│   │   │   ├── identity.ts            # Config: identity schema only
│   │   │   ├── inventory.ts           # Config: inventory schema only
│   │   │   ├── invoice.ts             # Config: invoice schema only
│   │   │   ├── ledger.ts              # Config: ledger schema only
│   │   │   ├── licensing.ts           # Config: licensing schema only
│   │   │   ├── outreach.ts            # Config: outreach schema only
│   │   │   ├── payment.ts             # Config: payment schema only
│   │   │   ├── pricing.ts             # Config: pricing schema only
│   │   │   ├── public.ts              # Config: public schema only
│   │   │   ├── sale.ts                # Config: sale schema only
│   │   │   └── tax.ts                 # Config: tax schema only
│   │   └── drizzle/                    # Generated migration output
│   │       ├── allocation/             # Allocation schema SQL + meta/
│   │       ├── finance/                # Finance schema SQL + meta/
│   │       ├── helpdesk/               # Helpdesk schema SQL + meta/
│   │       ├── identity/               # Identity schema SQL + meta/
│   │       ├── inventory/              # Inventory schema SQL + meta/
│   │       ├── invoice/                # Invoice schema SQL + meta/
│   │       ├── ledger/                 # Ledger schema SQL + meta/
│   │       ├── licensing/              # Licensing schema SQL + meta/
│   │       ├── outreach/               # Outreach schema SQL + meta/
│   │       ├── payment/                # Payment schema SQL + meta/
│   │       ├── pricing/                # Pricing schema SQL + meta/
│   │       ├── public/                 # Public schema SQL + meta/
│   │       ├── sale/                   # Sale schema SQL + meta/
│   │       └── tax/                    # Tax 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: `allocation`, `finance`, `helpdesk`, `identity`, `inventory`, `invoice`, `ledger`, `licensing`, `outreach`, `payment`, `pricing`, `public`, `sale`, `tax`
`--all`	Run the command for all 14 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
`allocation.ts`	`allocation`	`drizzle/allocation/`	`schemas/allocation/**/schema.ts`
`finance.ts`	`finance`	`drizzle/finance/`	`schemas/finance/**/schema.ts`
`helpdesk.ts`	`helpdesk`	`drizzle/helpdesk/`	`schemas/helpdesk/**/schema.ts`
`identity.ts`	`identity`	`drizzle/identity/`	`schemas/identity/**/schema.ts`
`inventory.ts`	`inventory`	`drizzle/inventory/`	`schemas/inventory/**/schema.ts`
`invoice.ts`	`invoice`	`drizzle/invoice/`	`schemas/invoice/**/schema.ts`
`ledger.ts`	`ledger`	`drizzle/ledger/`	`schemas/ledger/**/schema.ts`
`licensing.ts`	`licensing`	`drizzle/licensing/`	`schemas/licensing/**/schema.ts`
`outreach.ts`	`outreach`	`drizzle/outreach/`	`schemas/outreach/**/schema.ts`
`payment.ts`	`payment`	`drizzle/payment/`	`schemas/payment/**/schema.ts`
`pricing.ts`	`pricing`	`drizzle/pricing/`	`schemas/pricing/**/schema.ts`
`public.ts`	`public`	`drizzle/public/`	`schemas/public/**/schema.ts`
`sale.ts`	`sale`	`drizzle/sale/`	`schemas/sale/**/schema.ts`
`tax.ts`	`tax`	`drizzle/tax/`	`schemas/tax/**/schema.ts`

Note: payment.ts additionally globs MQ-Pay's compiled schema from third-parties/mq-pay/dist/... in addition to the schemas/payment/ source.

The `--all` Flag

The --all flag runs the command for every schema in sequence. cli.ts resolves the full set from PostgresSchemas.SCHEME_SET (all 14 schemas) and invokes each schema's own config, writing to its respective drizzle/{schema}/ directory.

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: '900_admin', name: { en: 'Admin', vi: 'Quan Tri Vien' }, priority: 900 },
  // ...
];

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: allocation, finance, helpdesk, identity, inventory, invoice, ledger, licensing, outreach, payment, pricing, public, sale, tax

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
The --all flag iterates each per-schema config (reading .ts directly); no special build is required
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 -- 26 entities in the public schema
Pricing Schema -- 9 pricing entities
Allocation Schema -- 4 allocation entities
Sale Schema -- 21 sale entities
Inventory Schema -- 17 inventory entities
Finance Schema -- 6 finance entities
Payment Schema -- 0 entities (reserved namespace)
Outreach Schema -- 2 outreach entities
Entity Relationship Diagram -- Comprehensive ERD for all models

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 ​

The --all Flag ​

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

The `--all` Flag

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