Database Guide

All BANA services share a single PostgreSQL database (nx_seller) with 11 named schemas containing 72 tables. Schema definitions live in @nx/core — individual service packages don't define their own tables.

Schema Overview

Schema	Tables	Owner Service	Purpose
public	15	identity, commerce	Users, roles, permissions, merchants, organizers, products, categories, variants, devices, sale channels, configurations
identity	8	identity	User credentials, identifiers, profiles, mail verification tokens
pricing	11	pricing	Fares, fare rules, costs, taxes, price evaluation results
sale	9	sale	Sale orders, order items, checks, kitchen tickets
inventory	12	inventory	Stock locations, stock items, purchase orders, stock movements
allocation	4	commerce	Event seating, venue layouts
finance	3	finance	Wallets, transaction records, finance categories
payment	1	payment	Webhook configurations
ledger	2	ledger	Ledger generation jobs, generated documents
licensing	5	licensing	Policies, policy features, licenses, activations, license events
outreach	2	outreach	Inquiries, newsletter subscribers

All schema definitions are in packages/core/src/models/schemas/{schema}/.

Column Enrichers

@nx/core provides column definition helpers that standardize table structure.

generateCommonColumnDefs()

The default for most tables. Adds 5 columns:

Column	Type	Nullable	Default	Notes
id	text	No	crypto.randomUUID()	Snowflake ID, primary key
created_at	timestamp(tz)	No	now()	Auto-set on insert
modified_at	timestamp(tz)	No	now()	Auto-updated on every change
deleted_at	timestamp(tz)	Yes	—	Soft delete marker
metadata	jsonb	Yes	—	Flexible metadata

import { generateCommonColumnDefs } from '../../common';

export const MySchema = publicSchema.table('MyTable', {
  ...generateCommonColumnDefs(),
  name: text('name').notNull(),
});

generateIdColumnDefs()

When you need just the ID without timestamps:

Option	Column type	Default
{ id: { dataType: 'string' } }	text PK, crypto.randomUUID()	Most common
{ id: { dataType: 'number' } }	integer PK, GENERATED ALWAYS AS IDENTITY	Auto-increment
{ id: { dataType: 'big-number' } }	bigint PK, GENERATED ALWAYS AS IDENTITY	Large sequences

generateTzColumnDefs()

Timestamp columns with configurable soft-delete:

// With soft delete (default)
generateTzColumnDefs({ deleted: { enable: true } })
// → createdAt, modifiedAt, deletedAt

// Without soft delete
generateTzColumnDefs({ deleted: { enable: false } })
// → createdAt, modifiedAt only

generateDataTypeColumnDefs()

Polymorphic value columns for tables that store multiple data types (used by PolicyFeature):

Column	Type	Purpose
data_type	text	Type discriminator (e.g. 'BOOLEAN', 'NUMBER', 'TEXT', 'JSON')
n_value	double precision	Numeric values
t_value	text	Text values
bo_value	boolean	Boolean values
j_value	jsonb	JSON object values
b_value	bytea	Binary data

Defining a Table

Follow this pattern in packages/core/src/models/schemas/{schema}/{entity}/schema.ts:

import { text, jsonb, index, foreignKey, unique } from 'drizzle-orm/pg-core';
import { generateCommonColumnDefs, isoTimestamp } from '../../common';
import { mySchema } from '../common';

const TABLE_NAME = 'MyEntity';

export const MyEntitySchema = mySchema.table(
  TABLE_NAME,
  {
    ...generateCommonColumnDefs(),
    name: text('name').notNull(),
    status: text('status').notNull().default('activated'),
    parentId: text('parent_id'),
    data: jsonb('data').$type<{ key: string }>(),
  },
  def => [
    index('IDX_MyEntity_status').on(def.status),
    foreignKey({
      columns: [def.parentId],
      foreignColumns: [ParentSchema.id],
      name: 'FK_MyEntity_parentId_Parent_id',
    }).onDelete('cascade'),
    unique('UQ_MyEntity_name').on(def.name),
  ],
);

Type exports

Every schema should export these companion types:

export type TMyEntity = TTableObject<typeof MyEntitySchema>;        // select type
export type TMyEntityPersist = TTableInsert<typeof MyEntitySchema>; // insert type
export const MyEntitySelectSchema = createSelectSchema(MyEntitySchema);
export const MyEntityInsertSchema = createInsertSchema(MyEntitySchema);
export const MyEntityUpdateSchema = MyEntityInsertSchema.partial();

Soft-Deletable Repository

SoftDeletableRepository from @nx/core wraps Drizzle and automatically:

• Filters out deleted_at IS NOT NULL rows on find, findOne, findById
• Implements deleteById as UPDATE SET deleted_at = now() (not a hard DELETE)

import { SoftDeletableRepository } from '@nx/core';

@repository({ dataSource: PostgresCoreDataSource, model: MyEntity })
export class MyRepository extends SoftDeletableRepository<
  typeof MyEntitySchema,
  TMyEntity,
  TMyEntityPersist
> {}

For entities that should be hard-deleted (e.g. LicenseEvent), use the base Repository class instead, and define the schema with generateTzColumnDefs({ deleted: { enable: false } }).

Transactions

const tx = await this.repository.beginTransaction();

try {
  // All operations share the same transaction
  await this.repository.create({ data, options: { transaction: tx } });
  await this.otherRepository.updateById({ id, data, options: { transaction: tx } });
  await tx.commit();
} catch (err) {
  await tx.rollback();
  throw err;
}

Row-level locking for concurrency safety:

const row = await this.repository.findOne({
  filter: { where: { id } },
  options: {
    transaction: tx,
    lock: { strength: LockStrengths.UPDATE },  // SELECT ... FOR UPDATE
  },
});

Migration Workflow

Migrations are managed by Drizzle Kit with a custom CLI wrapper in @nx/core.

Generate a migration

# Single schema
make db-generate schema=public

# All schemas
make db-generate-all

This runs drizzle-kit generate with the schema-specific config from packages/core/src/migrations/migrators/{schema}.ts. Generated SQL files go to packages/core/src/migrations/drizzle/{schema}/.

Apply migrations

# Single schema
make db-migrate schema=public

# All schemas
make db-migrate-all

# Per-package (runs package-level seeds too)
make db-migrate-package-dev package=identity

db-migrate runs drizzle-kit migrate which applies unapplied SQL files in order. db-migrate-package-dev also runs the package's own seed migrations (permissions, default configs).

Per-package seed migrations

Each package can define seed migrations in src/migrations/processes/:

// packages/licensing/src/migrations/processes/licensing-0001-seed-permissions.ts
const migrationProcess: TMigrationProcess = {
  options: { alwaysRun: true },  // re-apply every startup
  name: __filename.slice(__dirname.length + 1),
  migrateFn: async ({ context: application }) => {
    const permRepo = application.get<PermissionRepository>({ key: 'repositories.PermissionRepository' });
    for (const permission of LicensingPermissions) {
      const existing = await permRepo.findOne({ filter: { where: { code: permission.code } } });
      if (!existing) await permRepo.create({ data: permission });
      else await permRepo.updateById({ id: existing.id, data: permission });
    }
  },
};

These run when the service starts (via bootstrapMigration()) or when you call bun run migrate:dev.

Migration config structure

Each schema has its own Drizzle config file:

packages/core/src/migrations/
├── migrators/
│   ├── cli.ts                 # CLI entrypoint (generate | migrate [--all])
│   ├── public.ts              # drizzle-kit config for public schema
│   ├── pricing.ts             # drizzle-kit config for pricing schema
│   ├── allocation.ts
│   ├── sale.ts
│   ├── inventory.ts
│   ├── finance.ts
│   ├── payment.ts
│   ├── ledger.ts
│   ├── identity.ts
│   ├── licensing.ts
│   └── outreach.ts
└── drizzle/
    ├── public/                # Generated SQL migration files
    ├── pricing/
    ├── ...

Adding a new table (step by step)

1. Create the schema file in packages/core/src/models/schemas/{schema}/{entity}/schema.ts
2. Create the model file in packages/core/src/models/schemas/{schema}/{entity}/model.ts
3. Export from the schema index in packages/core/src/models/schemas/{schema}/index.ts
4. Generate migration: make db-generate schema={schema}
5. Review the SQL in packages/core/src/migrations/drizzle/{schema}/
6. Apply: make db-migrate schema={schema}
7. Create a repository in the owning package: packages/{package}/src/repositories/
8. Register the repository in Application.configureRepositories()

Naming Conventions

Element	Convention	Example
Table name	PascalCase	PolicyFeature
Column name	snake_case	policy_id, created_at
Index	IDX_{Table}_{column}	IDX_License_status
Foreign key	FK_{Table}_{column}_{RefTable}_{refColumn}	FK_License_policyId_Policy_id
Unique constraint	UQ_{Table}_{columns}	UQ_Activation_licenseId_fingerprint
Schema variable	{name}Schema	licensingSchema

Makefile Database Targets

Target	Usage
make db-generate schema=<name>	Generate SQL for one schema
make db-generate-all	Generate SQL for all 11 schemas
make db-migrate schema=<name>	Apply migrations for one schema
make db-migrate-all	Apply all migrations
make db-migrate-package-dev package=<name>	Run package-level seeds in dev
make db-migrate-ledger-dev	Shortcut for ledger

Schema names: public, pricing, allocation, sale, inventory, finance, payment, ledger, identity, licensing, outreach

Related Pages

Page	Description
Architecture	Schema-to-service mapping
IGNIS Patterns	Model pattern, soft-delete repository
Build System	All db-* Makefile targets
Core — Database	Detailed ERD and per-schema documentation