Database Guide

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

Schema Overview

Schema	Entities	Owner Service	Purpose
helpdesk	30	helpdesk	Tickets, agents, tags, comments, SLA, knowledge base
public	26	identity, commerce	Users, roles, permissions, merchants, organizers, products, categories, variants, devices, sale channels, configurations
inventory	17	inventory	Stock locations, stock items, purchase orders, stock movements
sale	21	sale	Sale orders, order items, checks, kitchen tickets
invoice	9	invoice	Invoices, invoice items, tax info, e-invoice integration
pricing	9	pricing	Fares, fare rules, costs, taxes, price evaluation results
identity	8	identity	User credentials, identifiers, profiles, mail verification tokens
ledger	8	ledger	Ledger generation jobs, generated documents
finance	6	finance	Wallets, transaction records, finance categories, payment integrations
tax	6	taxation	Tax definitions, tax groups, tax rules
licensing	5	licensing	Policies, policy features, licenses, activations, license events
allocation	4	commerce	Event seating, venue layouts
outreach	2	outreach	Inquiries, newsletter subscribers
payment	0	payment	Reserved namespace (PaymentIntegration lives in the finance schema)

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()	Primary key; $defaultFn fallback (a schema may opt into a Snowflake id via IdGenerator)
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 env=development

# All schemas
make db-generate-all env=development

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 env=development

# All schemas
make db-migrate-all env=development

# Per-package (runs package-level seeds too)

# Equivalent shortcut form, one target per package
make db-migrate-identity-dev

db-migrate runs drizzle-kit migrate which applies unapplied SQL files in order. db-migrate-<pkg>-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
│   ├── identity.ts            # one config per schema (14 total)
│   ├── pricing.ts
│   ├── allocation.ts
│   ├── sale.ts
│   ├── inventory.ts
│   ├── finance.ts
│   ├── tax.ts
│   ├── payment.ts
│   ├── ledger.ts
│   ├── invoice.ts
│   ├── licensing.ts
│   ├── helpdesk.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} env={env}
5. Review the SQL in packages/core/src/migrations/drizzle/{schema}/
6. Apply: make db-migrate schema={schema} env={env}
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> env=<env>	Generate SQL for one schema
make db-generate-all env=development	Generate SQL for all 14 schemas
make db-migrate schema=<name> env=<env>	Apply migrations for one schema
make db-migrate-all env=development	Apply all migrations
make db-migrate-<pkg>-dev	Same, as a per-package target (e.g. db-migrate-ledger-dev)

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

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