Public Schema

The public schema contains 30 models covering the core business domain. Models are defined using pgTable() from Drizzle ORM and use the shared generateCommonColumnDefs() for id, createdAt, modifiedAt, deletedAt, and metadata columns.

Source: packages/core/src/models/schemas/public/

User Management

The following entity relationship diagram shows the connections between user and authorization models:

User

Core user entity representing all system users.

Column	Type	Constraints	Description
id	text	PK	Snowflake ID
realm	text	DEFAULT ''	User realm/tenant
status	text	NOT NULL	User status (from CommonStatuses)
type	text	NOT NULL	User type (SYSTEM, LINKED)
activated_at	timestamptz		Account activation time
last_login_at	timestamptz		Last login timestamp
parent_id	text		Parent user reference
+ common columns			createdAt, modifiedAt, deletedAt, metadata

Uses extraUserColumns({ idType: 'string' }) from IGNIS.

UserProfile

Extended user information (one-to-one with User).

Column	Type	Constraints	Description
emails	text[]	DEFAULT '{}'	Array of email addresses
phones	text[]	DEFAULT '{}'	Array of phone numbers
first_name	text		Given name
last_name	text		Family name
birthday	isoTimestamp		Birth date (ISO 8601 string)
locale	text	DEFAULT 'en-US'	Locale preference
user_id	text	NOT NULL	FK to User
+ common columns

UserIdentifier

Multiple identification methods for users.

Column	Type	Constraints	Description
scheme	text	NOT NULL	Identifier type (USERNAME, EMAIL, PHONE_NUMBER, USER_NUMBER, NX_AUTH)
identifier	text	NOT NULL	Identifier value
verified	boolean	NOT NULL, DEFAULT false	Verification status
user_id	text	NOT NULL	FK to User
+ common columns

Unique constraint: (scheme, identifier)

UserCredential

Authentication credentials (passwords, OAuth tokens).

Column	Type	Constraints	Description
scheme	text	NOT NULL	Credential type (BASIC, TWO_FA, OAUTH, OAUTH2)
credential	text	NOT NULL	Hashed credential value
user_id	text	NOT NULL	FK to User
+ common columns

UserConfiguration

Per-user configuration settings using the data-type column pattern.

Column	Type	Constraints	Description
name	text		Configuration name
code	text	NOT NULL	Configuration code (e.g., PREFERENCES)
description	text		Description
group	text	NOT NULL	Configuration group
user_id	text	NOT NULL	FK to User
+ data-type columns			dataType, tValue, nValue, boValue, bValue, jValue
+ common columns

UserMapping

Maps users to organizations and merchants using polymorphic references.

Column	Type	Constraints	Description
principal_id	text		Polymorphic target ID (e.g., organizer or merchant ID)
principal_type	text		Polymorphic target type (e.g., Organizer, Merchant)
user_id	text	NOT NULL	FK to User
+ common columns

UserRole

Associates users with roles using polymorphic scoping.

Column	Type	Constraints	Description
principal_id	text		Scope ID (e.g., organizer ID for scoped roles)
principal_type	text		Scope type
user_id	text	NOT NULL	FK to User
+ common columns

Authorization

Role

Role definitions for RBAC with i18n support.

Column	Type	Constraints	Description
identifier	text	NOT NULL, UNIQUE	Role code (e.g., 999-super-admin)
priority	integer	NOT NULL	Priority level (higher = more privileged)
status	text	NOT NULL	Role status
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
+ common columns

Uses extraRoleColumns() from IGNIS. Predefined roles:

Role	Identifier	Priority
Super Admin	999-super-admin	999
Admin	998-admin	998
Operator	997-operator	997
Organizer Owner	899-organizer-owner	899
Employee	898-employee	898

Permission

Permission definitions for access control.

Column	Type	Constraints	Description
code	text	NOT NULL, UNIQUE	Permission code
name	text	NOT NULL	Permission name
subject	text	NOT NULL	Resource subject
p_type	text	NOT NULL	Permission type
action	text	NOT NULL	Allowed action
scope	text	NOT NULL	Permission scope
parent_id	text		Parent permission FK
+ common columns

Uses extraPermissionColumns({ idType: 'string' }) from IGNIS.

PermissionMapping

Maps permissions to roles (many-to-many join table).

Column	Type	Constraints	Description
+ permission mapping columns			From extraPermissionMappingColumns({ idType: 'string' })
+ common columns

Organization

The following entity relationship diagram shows the connections between organization and product models:

Organizer

Top-level business organization entity with legal, contact, and location details.

Column	Type	Constraints	Description
slug	text	NOT NULL, UNIQUE	URL-friendly identifier
status	text	NOT NULL, DEFAULT ACTIVATED	Organizer status
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., O_20260216_...)
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
tax_number	text		Tax identification number
registration_number	text		Business registration number
registration_type	text		Registration type (VN_HOUSEHOLD_BUSINESS, VN_PRIVATE_ENTERPRISE, etc.)
registration_date	isoTimestamp		Registration date
license_number	text		Business license number
vat_number	text		VAT number
contact_name	text		Primary contact name
contact_phone	text		Contact phone
contact_email	text		Contact email
website	text		Website URL
location	jsonb (ILocation)		Physical address and coordinates
social	jsonb (ISocial)		Social media links
parent_id	text		Parent organizer FK
created_by	text		Creator user ID
modified_by	text		Last modifier user ID
+ common columns

Merchant

Store/branch entity under an organizer.

Column	Type	Constraints	Description
slug	text	NOT NULL	URL-friendly identifier (unique per organizer)
status	text	NOT NULL, DEFAULT ACTIVATED	Merchant status
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., M_20260216_...)
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
currency	text	NOT NULL	Currency code (e.g., VND)
type	text	NOT NULL	Merchant type (DEFAULT, TICKET, FNB, THEATER)
date_from	isoTimestamp		Operating start date
date_to	isoTimestamp		Operating end date
location	jsonb (ILocation)		Physical address and coordinates
parent_id	text		Parent merchant FK
organizer_id	text	NOT NULL	FK to Organizer
+ common columns

Unique constraint: (slug, organizer_id)

MerchantIdentifier

External identifiers for merchants (e.g., payment provider merchant IDs).

Column	Type	Constraints	Description
scheme	text	NOT NULL	Identifier scheme (SYSTEM, SLUG, VNPAY_QR_MMS, VNPAY_PHONE_POS, VNPAY_SMART_POS)
identifier	text	NOT NULL	Identifier value
merchant_id	text	NOT NULL	FK to Merchant
+ common columns

Unique constraint: (scheme, identifier)

Product

Product

Main product entity.

Column	Type	Constraints	Description
slug	text	NOT NULL	URL-friendly identifier (unique per merchant)
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., P_20260216_...)
status	text	NOT NULL, DEFAULT ACTIVATED	Product status
merchant_id	text	NOT NULL	FK to Merchant
category_id	text		FK to Category
parent_id	text		Parent product FK
+ common columns

Unique constraint: (slug, merchant_id)

ProductInfo

Extended product details using polymorphic reference pattern.

Column	Type	Constraints	Description
principal_id	text		Polymorphic owner ID (Product or ProductVariant ID)
principal_type	text		Polymorphic owner type
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
+ common columns

ProductIdentifier

Multiple identification schemes for products (SKU, barcode, QR code).

Column	Type	Constraints	Description
principal_id	text		Polymorphic owner ID
principal_type	text		Polymorphic owner type
scheme	text	NOT NULL	Scheme (SYSTEM, SLUG, SKU, BARCODE, QRCODE)
identifier	text	NOT NULL	Identifier value
+ common columns

Unique constraint: (scheme, identifier)

ProductVariant

Product variations with costing and allocation support.

Column	Type	Constraints	Description
slug	text	NOT NULL	URL-friendly identifier
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., PV_20260216_...)
status	text	NOT NULL, DEFAULT NEW	Variant status (NEW, ACTIVATED, DEACTIVATED, ARCHIVED)
date_from	isoTimestamp		Availability start date
date_to	isoTimestamp		Availability end date
cost	decimal(15,4)		Unit cost
costing_methods	text	DEFAULT TIME_BASED	Costing method (TIME_BASED, FIFO, LIFO, AVERAGE)
product_id	text	NOT NULL	FK to Product
allocation_plan_id	text		FK to AllocationPlan
allocation_layout_id	text		FK to AllocationLayout
allocation_zone_id	text		FK to AllocationZone
allocation_unit_id	text		FK to AllocationUnit
+ common columns

ProductVariantMapping

Maps product variants to variant attributes using polymorphic references.

Column	Type	Constraints	Description
principal_id	text		Polymorphic ID
principal_type	text		Polymorphic type
product_variant_id	text	NOT NULL	FK to ProductVariant
+ common columns

Variant

Variant attribute definitions (e.g., Size, Color).

Column	Type	Constraints	Description
principal_type	text		Owner type
principal_id	text		Owner ID
code	text	NOT NULL	Variant code
+ data-type columns			dataType, tValue, nValue, boValue, bValue, jValue
+ common columns

Category

Product categorization with i18n support.

Column	Type	Constraints	Description
status	text	NOT NULL, DEFAULT UNKNOWN	Category status
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
is_addon	boolean	NOT NULL, DEFAULT false	Whether this is an addon category
merchant_id	text	NOT NULL	FK to Merchant
+ common columns

Commerce

The following entity relationship diagram shows the connections between commerce and system models:

SaleChannel

Sales channels for distributing products.

Column	Type	Constraints	Description
slug	text	NOT NULL	URL-friendly identifier (unique per merchant)
status	text	NOT NULL, DEFAULT ACTIVATED	Channel status
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., S_20260216_...)
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
merchant_id	text	NOT NULL	FK to Merchant
parent_id	text		Parent channel FK
+ common columns

Unique constraint: (slug, merchant_id)

SaleChannelProduct

Join table linking products to sale channels.

Column	Type	Constraints	Description
product_id	text	NOT NULL	FK to Product
sale_channel_id	text	NOT NULL	FK to SaleChannel
+ common columns

Quota

Quantity limits for campaigns.

Column	Type	Constraints	Description
quantity	integer	NOT NULL	Available quantity
status	text	NOT NULL, DEFAULT UNKNOWN	Quota status
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
campaign_id	text	NOT NULL	FK to Campaign
+ common columns

Campaign

Promotional campaigns.

Column	Type	Constraints	Description
status	text	NOT NULL, DEFAULT UNKNOWN	Campaign status
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
date_from	timestamptz		Campaign start date
date_to	timestamptz		Campaign end date
merchant_id	text	NOT NULL	FK to Merchant
+ common columns

System

Configuration

System and integration configuration with encrypted credential support.

Column	Type	Constraints	Description
code	text	NOT NULL	Configuration code
name	text		Configuration name
description	text		Description
group	text	NOT NULL	Group (SYSTEM, TABLE, INTEGRATION)
status	text	DEFAULT ACTIVATED	Configuration status
environment	text		Environment context
credential	text		Encrypted credential (hidden, never returned)
principalId	text		Owner entity ID
principalType	text		Owner entity type
created_by	text		Creator user ID
modified_by	text		Last modifier user ID
+ data-type columns			dataType, tValue, nValue, boValue, bValue, jValue
+ common columns

Unique constraint: (group, code, principalId, principalType, environment, deletedAt)

Indexes: (group, code, environment), (group, principalType), GIN on metadata

Settings

Key-value settings using polymorphic ownership and data-type storage.

Column	Type	Constraints	Description
principal_id	text		Owner entity ID
principal_type	text		Owner entity type
created_by	text		Creator
modified_by	text		Last modifier
+ data-type columns			dataType, tValue, nValue, boValue, bValue, jValue
+ common columns

Unique constraint: (principalId, principalType, deletedAt)

Device

POS devices and terminals with hardware/software info.

Column	Type	Constraints	Description
status	text	NOT NULL, DEFAULT NEW	Device status (NEW, ACTIVATED, DEACTIVATED, SUSPENDED, ARCHIVED)
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., D_20260216_...)
code	text	UNIQUE	Short code
type	text	NOT NULL	Device type (POS_TERMINAL, MOBILE_POS, TABLET, BARCODE_SCANNER, etc.)
location	jsonb (ILocation)		Physical location
organizer_id	text	NOT NULL	FK to Organizer
merchant_id	text		FK to Merchant
hardware_info	jsonb		IDeviceHardwareInfo (manufacturer, model, serialNumber, etc.)
software_info	jsonb		IDeviceSoftwareInfo (os, osVersion, appVersion, etc.)
last_activity_at	isoTimestamp		Last activity timestamp
activated_at	isoTimestamp		Activation timestamp
deactivated_at	isoTimestamp		Deactivation timestamp
pin	text		Device PIN
purchase_date	isoTimestamp		Purchase date
warranty_expires_at	isoTimestamp		Warranty expiration
last_maintenance_at	isoTimestamp		Last maintenance
next_maintenance_at	isoTimestamp		Next scheduled maintenance
maintenance_notes	text		Maintenance notes
vendor	text		Device vendor
+ common columns

MetaLink

Media/file metadata for assets stored in Minio or local disk.

Column	Type	Constraints	Description
id	text	PK	Snowflake ID
bucket_name	text	NOT NULL, indexed	Storage bucket
object_name	text	NOT NULL, indexed	Object name in storage
link	text	NOT NULL	Access URL (presigned)
mimetype	text	NOT NULL	MIME type
size	integer	NOT NULL	File size in bytes
etag	text		Entity tag
metadata	jsonb		Additional metadata
storage_type	text	NOT NULL, indexed	Storage type (minio, disk)
is_synced	boolean	NOT NULL, DEFAULT false, indexed	Sync status
principal_type	text		Owner entity type
principal_id	text		Owner entity ID
created_at	timestamptz		Creation time
modified_at	timestamptz		Last modification

Note: MetaLink uses generateIdColumnDefs + generateTzColumnDefs directly (no deletedAt).

Migration

Tracks seed data migration execution.

Column	Type	Constraints	Description
id	text/serial	PK	Auto-increment or generated ID
name	text	NOT NULL	Migration name
status	text	NOT NULL	Migration status (from MigrationStatuses)
created_at	timestamptz		Execution time
modified_at	timestamptz		Last update

Note: Uses generateIdColumnDefs() (without string option) and generateTzColumnDefs().

EmailVerification

Email verification codes and tokens with rate limiting.

Column	Type	Constraints	Description
verification_code	varchar	NOT NULL, indexed	6-digit verification code
code_generated_at	timestamptz	NOT NULL	Code generation time
code_expires_at	timestamptz	NOT NULL, indexed	Code expiration (10 min)
code_attempts	integer	NOT NULL, DEFAULT 0	Failed attempt count (max 3)
verification_token	varchar	NOT NULL, indexed	32-byte verification token
token_generated_at	timestamptz	NOT NULL	Token generation time
token_expires_at	timestamptz	NOT NULL, indexed	Token expiration (24 hours)
last_code_sent_at	timestamptz	NOT NULL	Last code send time
resend_count	integer	NOT NULL, DEFAULT 0	Resend count (max 5/day)
daily_resend_reset_at	timestamptz	NOT NULL	Daily reset timestamp
locked_until	timestamptz		Lockout expiration (15 min)
is_verified	boolean	NOT NULL, DEFAULT false	Verification status
verified_at	timestamptz		Verification timestamp
verification_method	varchar		Method used (code or token)
verification_ip	varchar		IP address used
user_identifier_id	text	NOT NULL, indexed	FK to UserIdentifier
+ common columns

Auto-Generated Identifiers

Most entities use auto-generated identifiers with the format {PREFIX}_{YYYYMMDD}_{SNOWFLAKE_ID}:

Entity	Prefix	Example
Organizer	O_	O_20260216_1234567890
Merchant	M_	M_20260216_1234567890
Product	P_	P_20260216_1234567890
ProductVariant	PV_	PV_20260216_1234567890
SaleChannel	S_	S_20260216_1234567890
Device	D_	D_20260216_1234567890

Related Documentation

• Database Overview -- Schema summary, common columns, shared types
• Entity Relationship Diagram -- Comprehensive ERD for all 55 models
• 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
• Migrations -- DDL migrations and seed data framework