| name | data-model-design |
| description | Design entities, relationships, and manage the migration lifecycle. Use when planning a data model, designing entities, choosing relationship patterns, adding cross-module references, or managing database migrations. Triggers on "design entity", "data model", "add entity", "database schema", "migration", "relationship", "many-to-many", "junction table", "foreign key", "jsonb", "add column". |
Data Model Design
Design entities, relationships, and manage the migration lifecycle following Open Mercato conventions.
Table of Contents
- Design Workflow
- Entity Design
- Field Types
- Relationship Patterns
- Cross-Module References
- Migration Lifecycle
- Advanced Patterns
- Sensitive Data and Encryption Maps
- Anti-Patterns
1. Design Workflow
When the developer describes data requirements:
- Clarify entities — What are the distinct "things" being stored?
- Clarify fields — What data does each entity hold?
- Clarify relationships — How do entities relate? (1:1, 1:N, N:M, cross-module?)
- Choose patterns — Select the right pattern for each relationship
- Generate — Create entity files, validators, and migrations
- Verify — Check migration output, test queries
2. Entity Design
Standard Entity Template
Define entities in src/modules/<module_id>/data/entities.ts. Standalone apps keep the module's entity classes together there unless the file becomes large enough that a split is justified.
import { Entity, Enum, Index, PrimaryKey, Property } from '@mikro-orm/decorators/legacy'
import { v4 } from 'uuid'
@Entity({ tableName: '<entities>' })
export class <Entity> {
@PrimaryKey({ type: 'uuid' })
id: string = v4()
@Index()
@Property({ type: 'uuid' })
organization_id!: string
@Index()
@Property({ type: 'uuid' })
tenant_id!: string
@Property({ type: 'boolean', default: true })
is_active: boolean = true
@Property({ type: 'timestamptz' })
created_at: Date = new Date()
@Property({ type: 'timestamptz', onUpdate: () => new Date() })
updated_at: Date = new Date()
@Property({ type: 'timestamptz', nullable: true })
deleted_at: Date | null = null
}
Required Columns (Every Tenant-Scoped Entity)
| Column | Type | Purpose | Indexed |
|---|
id | uuid | Primary key (v4 auto-generated) | PK |
organization_id | uuid | Tenant organization scope | Yes |
tenant_id | uuid | Tenant scope | Yes |
is_active | boolean | Soft active/inactive flag | No |
created_at | timestamptz | Creation timestamp | No |
updated_at | timestamptz | Last update (auto) | No |
deleted_at | timestamptz? | Soft delete timestamp | No |
3. Field Types
Type Selection Guide
| Data | MikroORM Type | PostgreSQL Type | Decorator |
|---|
| Short text (name, title) | varchar | varchar(255) | @Property({ type: 'varchar', length: 255 }) |
| Long text (description, notes) | text | text | @Property({ type: 'text' }) |
| Integer | int | integer | @Property({ type: 'int' }) |
| Decimal (money, quantity) | decimal | numeric(precision,scale) | @Property({ type: 'decimal', precision: 10, scale: 2 }) |
| Boolean | boolean | boolean | @Property({ type: 'boolean', default: false }) |
| UUID reference | uuid | uuid | @Property({ type: 'uuid' }) |
| Date only | date | date | @Property({ type: 'date' }) |
| Date + time | timestamptz | timestamptz | @Property({ type: 'timestamptz' }) |
| Enum | varchar | varchar | @Enum({ items: () => MyEnum }) |
| Flexible JSON | jsonb | jsonb | @Property({ type: 'jsonb', nullable: true }) |
| Array of strings | jsonb | jsonb | @Property({ type: 'jsonb', default: '[]' }) |
| Email | varchar | varchar(320) | @Property({ type: 'varchar', length: 320 }) |
| URL | text | text | @Property({ type: 'text' }) |
| Phone | varchar | varchar(50) | @Property({ type: 'varchar', length: 50 }) |
When to Use JSONB
Use jsonb when:
- Schema is flexible/user-defined (custom field values, metadata, tags)
- Data is read as a whole, not queried by individual fields
- Nesting is natural (address objects, configuration maps)
Avoid jsonb when:
- You need to query, filter, or sort by individual fields — use proper columns
- Data has a fixed, well-known schema — use columns for type safety
- You need referential integrity — FKs can't point into JSONB
Enum Pattern
export enum OrderStatus {
DRAFT = 'draft',
PENDING = 'pending',
CONFIRMED = 'confirmed',
SHIPPED = 'shipped',
DELIVERED = 'delivered',
CANCELLED = 'cancelled',
}
@Enum({ items: () => OrderStatus })
status: OrderStatus = OrderStatus.DRAFT
Nullable Fields
@Property({ type: 'varchar', length: 255, nullable: true })
notes: string | null = null
@Property({ type: 'varchar', length: 255 })
name!: string
4. Relationship Patterns
One-to-Many (Same Module)
Parent entity has many children. Use @ManyToOne / @OneToMany decorators only within the same module.
@Entity({ tableName: 'categories' })
export class Category {
@PrimaryKey({ type: 'uuid' })
id: string = v4()
@Property({ type: 'varchar', length: 255 })
name!: string
@OneToMany(() => Product, product => product.category)
products = new Collection<Product>(this)
}
@Entity({ tableName: 'products' })
export class Product {
@PrimaryKey({ type: 'uuid' })
id: string = v4()
@ManyToOne(() => Category)
category!: Category
}
Many-to-Many (Same Module)
Use a junction (pivot) table.
@Entity({ tableName: 'product_tags' })
export class ProductTag {
@PrimaryKey({ type: 'uuid' })
id: string = v4()
@Index()
@Property({ type: 'uuid' })
product_id!: string
@Index()
@Property({ type: 'uuid' })
tag_id!: string
@Index()
@Property({ type: 'uuid' })
organization_id!: string
@Index()
@Property({ type: 'uuid' })
tenant_id!: string
@Property({ type: 'timestamptz' })
created_at: Date = new Date()
}
Junction table rules:
- Always include
organization_id and tenant_id
- Index both FK columns
- Include
created_at for audit trail
- Add extra columns if the relationship has attributes (e.g.,
quantity, sort_order)
One-to-One (Same Module)
@Entity({ tableName: 'user_profiles' })
export class UserProfile {
@PrimaryKey({ type: 'uuid' })
id: string = v4()
@Index({ unique: true })
@Property({ type: 'uuid' })
user_id!: string
@Property({ type: 'text', nullable: true })
bio: string | null = null
}
Self-Referencing (Tree/Hierarchy)
@Entity({ tableName: 'categories' })
export class Category {
@PrimaryKey({ type: 'uuid' })
id: string = v4()
@Property({ type: 'uuid', nullable: true })
parent_id: string | null = null
@Property({ type: 'varchar', length: 255 })
name!: string
@Property({ type: 'text', default: '' })
path: string = ''
@Property({ type: 'int', default: 0 })
depth: number = 0
}
5. Cross-Module References
Critical rule: NO ORM relationships (@ManyToOne, @OneToMany) between entities in different modules.
Pattern: FK ID Only
@Entity({ tableName: 'tickets' })
export class Ticket {
@Index()
@Property({ type: 'uuid' })
customer_id!: string
@Index()
@Property({ type: 'uuid', nullable: true })
assigned_to: string | null = null
}
Fetching Related Data
To display related data from another module, use a Response Enricher (see system-extension skill):
const enricher: ResponseEnricher = {
id: 'tickets.customer-name',
targetEntity: 'tickets.ticket',
async enrichMany(records, context) {
const customerIds = [...new Set(records.map(r => r.customer_id).filter(Boolean))]
const customers = await em.find(Person, { id: { $in: customerIds } })
const nameMap = new Map(customers.map(c => [c.id, c.name]))
return records.map(r => ({
...r,
_tickets: { customerName: nameMap.get(r.customer_id) ?? null },
}))
},
}
Why No ORM Relations Across Modules?
- Module isolation — modules must be independently deployable and ejectable
- Circular dependencies — ORM relations create tight coupling between modules
- Schema ownership — each module owns its entities; cross-module ORM relations blur ownership
- Extension system — UMES enrichers provide the same capability without coupling
6. Migration Lifecycle
Creating a Migration
yarn db:generate
yarn db:migrate
Migration Best Practices
- Review every migration — auto-generated doesn't mean correct
- Check for unintended changes — sometimes generators pick up unrelated diffs
- Do not commit unrelated generated migrations — delete them from the diff
- Scoped manual SQL is allowed when generator churn is unrelated, but the migration and
.snapshot-open-mercato.json must still describe the same post-change schema
- Update
.snapshot-open-mercato.json — it is the baseline that prevents duplicate future migrations
- New columns should have defaults — prevents breaking existing rows
- Never rename columns — add new column, migrate data, remove old column (across releases)
- Never drop tables — soft delete or archive first
Adding a Column to Existing Entity
@Property({ type: 'varchar', length: 100, default: '' })
new_field: string = ''
@Property({ type: 'varchar', length: 100, nullable: true })
new_field: string | null = null
Then:
yarn db:generate
yarn db:migrate
Removing a Column
Don't remove columns in a single step. Instead:
- Stop writing to the column (remove from validators and forms)
- Make the column nullable if it isn't already
- In a later release, drop the column via migration
7. Advanced Patterns
Polymorphic References
When an entity can reference different types:
@Entity({ tableName: 'comments' })
export class Comment {
@PrimaryKey({ type: 'uuid' })
id: string = v4()
@Index()
@Property({ type: 'varchar', length: 100 })
target_type!: string
@Index()
@Property({ type: 'uuid' })
target_id!: string
@Property({ type: 'text' })
body!: string
}
Ordered Collections
When items have a user-defined order:
@Entity({ tableName: 'checklist_items' })
export class ChecklistItem {
@PrimaryKey({ type: 'uuid' })
id: string = v4()
@Index()
@Property({ type: 'uuid' })
checklist_id!: string
@Property({ type: 'int' })
sort_order!: number
@Property({ type: 'varchar', length: 255 })
title!: string
}
Soft Delete Pattern
All entities already include deleted_at. To implement soft delete:
entity.deleted_at = new Date()
entity.is_active = false
await em.flush()
const items = await em.find(Entity, {
organization_id: orgId,
deleted_at: null,
})
Audit/History Table
For tracking changes to an entity:
@Entity({ tableName: 'ticket_history' })
export class TicketHistory {
@PrimaryKey({ type: 'uuid' })
id: string = v4()
@Index()
@Property({ type: 'uuid' })
ticket_id!: string
@Property({ type: 'uuid' })
changed_by!: string
@Property({ type: 'varchar', length: 50 })
action!: string
@Property({ type: 'jsonb', nullable: true })
previous_values: Record<string, unknown> | null = null
@Property({ type: 'jsonb', nullable: true })
new_values: Record<string, unknown> | null = null
@Index()
@Property({ type: 'uuid' })
organization_id!: string
@Index()
@Property({ type: 'uuid' })
tenant_id!: string
@Property({ type: 'timestamptz' })
created_at: Date = new Date()
}
8. Sensitive Data and Encryption Maps
When the developer asks for "we need this column encrypted", "store this securely", "this is PII", "GDPR", or "encryption at rest" — and whenever you are designing a column that will hold names, addresses, contact information, free-text notes about people, integration credentials, secrets, or any data subject to a data-processing agreement — use the framework's encryption-maps mechanism. Do NOT hand-roll AES, raw crypto.subtle, custom KMS calls, or "TODO encrypt later" stubs.
The mechanism gives you:
- Per-tenant Data Encryption Keys (DEKs) resolved through the configured KMS (Vault by default, env-fallback in dev).
- Declarative, per-entity, per-field encryption with optional deterministic-hash sibling columns for equality lookups (for example login by email).
- Boot-time auto-application: every enabled module's
defaultEncryptionMaps is collected during auth:setup and applied when TENANT_DATA_ENCRYPTION=yes.
- A
findWithDecryption / findOneWithDecryption read API that transparently decrypts on read.
When encryption is mandatory
| Field example | Encrypt? |
|---|
| First name, last name, preferred name | Yes |
| Email, phone | Yes — usually with a hashField for lookups |
| Postal address (line 1/2, city, region, postal code, country) | Yes |
| Free-text comments / notes / activity bodies that mention people | Yes |
| Integration secrets, API keys, OAuth tokens, webhook signing keys | Yes |
| Document numbers (tax IDs, national IDs) | Yes |
| Status enums, counters, timestamps, FKs, currency codes | No |
| Public catalog metadata (product titles for a public storefront) | Usually no |
If you are unsure, default to encrypting and confirm with the user — re-introducing encryption later requires a backfill, but turning it off later is a single map edit.
Declare the map in <module>/encryption.ts
import type { ModuleEncryptionMap } from '@open-mercato/shared/modules/encryption'
export const defaultEncryptionMaps: ModuleEncryptionMap[] = [
{
entityId: '<module_id>:<entity>',
fields: [
{ field: 'first_name' },
{ field: 'last_name' },
{ field: 'phone' },
{ field: 'email', hashField: 'email_hash' },
],
},
]
export default defaultEncryptionMaps
Read with decryption — never raw em.find
import { findWithDecryption, findOneWithDecryption } from '@open-mercato/shared/lib/encryption/find'
const records = await findWithDecryption(em, '<Entity>', filter, undefined, { tenantId, organizationId })
const single = await findOneWithDecryption(em, '<Entity>', { id }, undefined, { tenantId, organizationId })
Calling em.find on an encrypted column returns ciphertext, breaks search, and silently leaks bug surface. The findWithDecryption family is the one entry point.
Apply maps to existing tenants
yarn mercato entities seed-encryption --tenant <tenantId> [--organization <orgId>]
New tenants pick up the maps automatically during auth:setup. Toggling the Encrypted flag on a custom field via the admin UI also only applies to data written after the change — backfill historical plaintext rows by running yarn mercato entities rotate-encryption-key --tenant <tenantId> --org <organizationId> (without --old-key it skips already-encrypted fields and just encrypts plaintext). Use yarn mercato entities decrypt-database to roll back. For full UI flows and CLI options see https://docs.open-mercato.dev/user-guide/encryption.
Vector search caveat
The vector module stores raw embeddings unencrypted in the vector store (e.g. pgvector). Even though the source text is decrypted only transiently to compute embeddings, treat the embeddings as sensitive: avoid embedding raw high-sensitivity text and rely on disk-level / managed-database encryption-at-rest for the vector column.
Environment switches
TENANT_DATA_ENCRYPTION=yes|no (default yes) — set to no to run the hooks as no-op (validation still applies).
TENANT_DATA_ENCRYPTION_DEBUG=yes — log map evaluation, KMS calls, cache hits.
VAULT_ADDR / VAULT_TOKEN / VAULT_KV_PATH — HashiCorp Vault KMS configuration.
TENANT_DATA_ENCRYPTION_FALLBACK_KEY — local/dev fallback key when Vault is unavailable. In dev, AUTH_SECRET / NEXTAUTH_SECRET is used as a last resort; production falls back to noop KMS.
9. Anti-Patterns
| Anti-Pattern | Problem | Correct Pattern |
|---|
@ManyToOne across modules | Tight coupling, breaks module isolation | Store FK as uuid column, use enrichers |
| Storing computed values | Stale data, maintenance burden | Compute on read via enrichers or queries |
Using any for JSONB fields | No type safety | Define a Zod schema, use z.infer |
| Blindly committing all generated migrations | Captures unrelated snapshot drift | Keep only scoped SQL and update the matching snapshot |
| Manual migration SQL without snapshot update | Future yarn db:generate recreates the same migration | Update .snapshot-open-mercato.json in the same change |
| Renaming columns | Breaks existing data/queries | Add new column, migrate data, drop old |
Missing organization_id | Cross-tenant data leaks | Always include and index |
Using varchar without length | Defaults vary by DB | Always specify length |
| Storing arrays as comma-separated strings | Can't query, no integrity | Use jsonb arrays or junction tables |
| UUID FK without index | Slow joins | Always @Index() on FK columns |
| Nullable required fields | Data integrity issues | Use ! assertion for required, null for optional |
Hand-rolled AES / crypto.subtle / custom KMS for sensitive columns | Per-tenant key isolation, hash lookups, key rotation, and admin UI all break | Declare <module>/encryption.ts with defaultEncryptionMaps; let the framework manage DEKs and Vault |
Reading encrypted columns with raw em.find / em.findOne | Returns ciphertext, breaks search, silent data corruption | Use findWithDecryption / findOneWithDecryption with { tenantId, organizationId } |
| Storing PII as plaintext "for now" / TODO comments | GDPR violation, leaks at rest, expensive backfill later | Encrypt from day one; toggling later only protects new writes |
Encrypting an email column without a hashField | Login / equality lookups stop working | Declare a sibling hashField (e.g. email_hash) in the encryption map and add the matching varchar column |
Rules
- MUST include
organization_id and tenant_id on all tenant-scoped entities
- MUST include standard columns (
id, created_at, updated_at, deleted_at, is_active)
- MUST use UUID v4 for primary keys
- MUST index all FK columns and
organization_id / tenant_id
- MUST create or keep a scoped migration after entity changes and update
.snapshot-open-mercato.json
- MUST review generated migration before applying
- MUST NOT commit unrelated migrations emitted by
yarn db:generate
- MUST NOT run
yarn db:migrate without explicit user confirmation
- MUST use
nullable: true with = null default for optional fields
- MUST specify
length on all varchar columns
- MUST NOT use ORM relationship decorators across module boundaries
- MUST NOT rename or drop columns in a single release
- MUST declare encrypted columns in
<module>/encryption.ts exporting defaultEncryptionMaps: ModuleEncryptionMap[], and read them via findWithDecryption / findOneWithDecryption from @open-mercato/shared/lib/encryption/find — see section 8
- MUST NOT hand-roll AES / KMS calls or store sensitive columns as plaintext "for now" — use the encryption-maps mechanism in section 8
- Use
jsonb for flexible/nested data, proper columns for queryable/sortable data
- Use junction tables for many-to-many relationships
- Derive TypeScript types from Zod schemas, never duplicate type definitions