Database Overview

The @nx/core package defines all database models across 14 PostgreSQL schemas with a total of 151 entities. All packages share a single PostgresCoreDataSource backed by Drizzle ORM with the node-postgres driver.

Quick Links

Section	Description
Public Schema	Core domain models: users, products, merchants, organizers (25 entities)
Pricing Schema	Fares, fare sets, fare rules, costs, taxes (9 entities)
Allocation Schema	Event seating: plans, layouts, zones, units (4 entities)
Sale Schema	Sale orders and sale order items (21 entities)
Inventory Schema	Stock management: items, locations, tracking, purchase orders (17 entities)
Finance Schema	Finance wallets, categories, transactions, payment integrations (6 entities)
Payment Schema	Reserved namespace (0 entities)
Outreach Schema	Inquiries, newsletter subscribers (2 entities)
ERD	Entity-Relationship Diagram (Mermaid)
Migrations	Migration CLI, Drizzle Kit integration, seed data

Schema Summary

Schema	Entities	Description
`helpdesk`	30	Tickets, agents, tags, comments, SLA, knowledge base
`public`	26	Core domain: users, products, merchants, organizers, configurations
`inventory`	17	Stock management: items, locations, stocks, tracking, purchase orders, vendors
`sale`	21	Sale orders, order items, checks, kitchen tickets
`invoice`	9	Invoices, invoice items, tax info, e-invoice integration
`pricing`	9	Fares, fare sets, fare rules, costs, taxes, tax sets, tax types
`identity`	8	User credentials, identifiers, profiles, mail verification tokens
`ledger`	8	Ledger generation jobs, generated documents
`finance`	6	Finance wallets, categories, transactions, payment integrations
`tax`	6	Tax definitions, tax groups, tax rules
`licensing`	5	Policies, policy features, licenses, activations, license events
`allocation`	4	Event seating: layouts, plans, units, zones
`outreach`	2	Inquiries, newsletter subscribers
`payment`	0	Reserved namespace (PaymentIntegration lives in the `finance` schema)
Total	151

Schema distribution across the database:

All 14 schemas are defined in the PostgresSchemas class:

Source: packages/core/src/common/constants.ts

typescript

export class PostgresSchemas {
  static readonly PUBLIC = 'public';
  static readonly PRICING = 'pricing';
  static readonly ALLOCATION = 'allocation';
  static readonly SALE = 'sale';
  static readonly INVENTORY = 'inventory';
  static readonly FINANCE = 'finance';
  static readonly PAYMENT = 'payment';
  static readonly INVOICE = 'invoice';
  static readonly LEDGER = 'ledger';
  static readonly IDENTITY = 'identity';
  static readonly LICENSING = 'licensing';
  static readonly OUTREACH = 'outreach';
  static readonly TAX = 'tax';
  static readonly HELPDESK = 'helpdesk';

  static readonly SCHEME_SET = new Set([
    this.PUBLIC, this.PRICING, this.ALLOCATION,
    this.SALE, this.INVENTORY, this.FINANCE, this.PAYMENT,
    this.INVOICE, this.LEDGER, this.IDENTITY, this.LICENSING,
    this.OUTREACH, this.TAX, this.HELPDESK,
  ]);
}

DataSource

All packages connect to the same PostgreSQL database via PostgresCoreDataSource. It uses the node-postgres driver with Drizzle ORM and auto-discovers models from @repository bindings.

Source: packages/core/src/datasources/postgres-core.datasource.ts

typescript

@datasource({ driver: 'node-postgres' })
export class PostgresCoreDataSource extends BaseDataSource<IPostgresDataSourceSettings> {
  override configure(): ValueOrPromise<void> {
    const schema = this.getSchema(); // Auto-discovers models from @repository bindings
    this.pool = new Pool(this.settings);
    this.connector = drizzle({ client: this.pool, schema });
  }

  override getConnectionString(): ValueOrPromise<string> {
    const { host, port, database, user, password, useSSL } = this.settings;
    return `postgresql://${user}:${password}@${host}:${port}/${database}?sslmode=${
      useSSL ? 'require' : 'disable'
    }`;
  }
}

Environment Variables

Variable	Description
`APP_ENV_POSTGRES_HOST`	Database host
`APP_ENV_POSTGRES_PORT`	Database port
`APP_ENV_POSTGRES_DATABASE`	Database name
`APP_ENV_POSTGRES_USERNAME`	Database user
`APP_ENV_POSTGRES_PASSWORD`	Database password

For more details on data sources in the IGNIS framework, see the IGNIS DataSources Reference.

Common Column Definitions

All models share a common set of columns generated by generateCommonColumnDefs(), defined in packages/core/src/models/schemas/common.ts.

generateCommonColumnDefs()

This function produces the following columns for every model:

Column	Type	Description
`id`	`text` (PK)	Snowflake ID primary key
`created_at`	`timestamptz`	Row creation timestamp
`modified_at`	`timestamptz`	Last modification timestamp
`deleted_at`	`timestamptz`	Soft-delete timestamp (`null` = active)
`metadata`	`jsonb`	Flexible metadata field (typed per model)

typescript

export const generateCommonColumnDefs = <Metadata extends AnyType = AnyType>() => {
  return {
    ...generateIdColumnDefs({ id: { dataType: 'string' } }),
    ...generateTzColumnDefs({
      deleted: { enable: true, columnName: 'deleted_at', withTimezone: true },
    }),
    metadata: jsonb().$type<Metadata>(),
  };
};

The id column uses the IGNIS generateIdColumnDefs helper with string data type. IDs are generated using the Snowflake algorithm via IdGenerator.

The generateTzColumnDefs helper produces createdAt and modifiedAt timestamps, with an optional deletedAt column for soft-delete support.

isoTimestamp Custom Type

Drizzle's built-in timestamp types have incompatible behaviors:

mode: 'date' requires Date objects (not JSON-friendly)
mode: 'string' returns PostgreSQL format instead of ISO 8601

The isoTimestamp custom type solves this by accepting ISO strings from the frontend and converting PostgreSQL timestamps back to ISO 8601 format for consistent API responses.

typescript

export const isoTimestamp = (name: string, config?: { withTimezone?: boolean }) =>
  customType<{ data: string; driverData: string }>({
    dataType() {
      return `timestamp${config?.withTimezone ? ' with time zone' : ''}`;
    },
    toDriver(value: string): string {
      return value; // Pass the ISO string directly
    },
    fromDriver(value: string): string {
      const date = new Date(value);
      if (isNaN(date.getTime())) {
        throw getError({
          message: `Invalid date string: ${value}`,
          statusCode: HTTP.ResultCodes.RS_5.InternalServerError,
        });
      }
      return date.toISOString(); // Always returns ISO 8601
    },
  })(name);

Additional Column Helpers

Several models also use these IGNIS-provided column generators:

Helper	Columns Produced	Used By
`generateUserAuditColumnDefs()`	`created_by`, `modified_by`	Organizer, Merchant, Configuration, SaleOrder, PurchaseOrder, etc.
`generatePrincipalColumnDefs()`	`principal_id`, `principal_type`	UserMapping, UserRole, ProductInfo, ProductIdentifier, TaxSet, etc.
`generateDataTypeColumnDefs()`	`data_type`, `t_value`, `n_value`, `bo_value`, `b_value`, `j_value`	FareRule, Configuration, Variant, UserConfiguration, Settings

Shared Types

INameI18n

Internationalized name fields stored as JSONB with optional English and Vietnamese translations.

typescript

export interface INameI18n {
  en?: string;
  vi?: string;
}

export const generateI18nColumnDef = (name: string) => jsonb(name).$type<INameI18n>();

Used for: categories, roles, organizers, merchants, products, fare names, inventory locations, finance wallets, and more.

ILocation

Physical address and coordinates stored as JSONB.

typescript

export interface ILocation {
  main: string;
  sub?: string;
  long?: string;
  lat?: string;
  postCode?: string;
}

Used for: organizers, merchants, devices, inventory locations, vendors.

ISocial

Social media links stored as JSONB with extensible key-value pairs.

typescript

export interface ISocial {
  facebook?: string;
  twitter?: string;
  instagram?: string;
  linkedin?: string;
  [key: string]: string | undefined;
}

Used for: organizers.

Schema Directory Structure

packages/core/src/models/schemas/
├── index.ts                        # Barrel export (all schemas)
├── common.ts                       # Shared types, column defs, schema objects
├── public/                         # public schema (26 entities)
│   ├── user/                       # User, UserProfile, UserIdentifier, etc.
│   ├── role/                       # Role
│   ├── permission/                 # Permission, PermissionMapping
│   ├── organizer/                  # Organizer
│   ├── merchant/                   # Merchant, MerchantIdentifier
│   ├── product/                    # Product, ProductInfo, ProductVariant, etc.
│   ├── category/                   # Category
│   ├── sale-channel/               # SaleChannel, SaleChannelProduct
│   ├── configuration/              # Configuration
│   ├── settings/                   # Settings
│   ├── device/                     # Device
│   ├── meta-link/                  # MetaLink
│   ├── campaign/                   # Campaign
│   ├── quota/                      # Quota
│   ├── email-verification/         # EmailVerification
│   └── migration/                  # Migration
├── helpdesk/                       # helpdesk schema (30 entities)
├── inventory/                      # inventory schema (17 entities)
├── sale/                           # sale schema (21 entities)
├── invoice/                        # invoice schema (9 entities)
├── pricing/                        # pricing schema (9 entities)
├── identity/                       # identity schema (8 entities)
├── ledger/                         # ledger schema (8 entities)
├── finance/                        # finance schema (6 entities)
├── tax/                            # tax schema (6 entities)
├── licensing/                      # licensing schema (5 entities)
├── allocation/                     # allocation schema (4 entities)
├── outreach/                       # outreach schema (2 entities)
└── payment/                        # payment schema (0 entities)

Each model directory contains:

schema.ts -- Drizzle table definition (columns, indexes, constraints)
model.ts -- Entity class with @model decorator, relations, and Zod schemas
index.ts -- Barrel export

IGNIS Framework References

IGNIS DataSources -- BaseDataSource, @datasource decorator
IGNIS Models -- BaseEntity, @model decorator, relations
IGNIS Repositories -- BaseRepository, @repository decorator, CRUD operations

Providers

Invoice Types

Database Overview ​

Quick Links ​

Schema Summary ​

DataSource ​

Environment Variables ​

Common Column Definitions ​

generateCommonColumnDefs() ​

isoTimestamp Custom Type ​

Additional Column Helpers ​

Shared Types ​

INameI18n ​

ILocation ​

ISocial ​

Schema Directory Structure ​

IGNIS Framework References ​

Database Overview

Quick Links

Schema Summary

DataSource

Environment Variables

Common Column Definitions

generateCommonColumnDefs()

isoTimestamp Custom Type

Additional Column Helpers

Shared Types

INameI18n

ILocation

ISocial

Schema Directory Structure

IGNIS Framework References