Data Model Design

Data Model Design

Design entities, relationships, and manage the migration lifecycle following Open Mercato conventions.

Table of Contents

1. Design Workflow
2. Entity Design
3. Field Types
4. Relationship Patterns
5. Cross-Module References
6. Migration Lifecycle
7. Advanced Patterns
8. Sensitive Data and Encryption Maps
9. Anti-Patterns

1. Design Workflow

When the developer describes data requirements:

1. Clarify entities — What are the distinct "things" being stored?
2. Clarify fields — What data does each entity hold?
3. Clarify relationships — How do entities relate? (1:1, 1:N, N:M, cross-module?)
4. Choose patterns — Select the right pattern for each relationship
5. Generate — Create entity files, validators, and migrations
6. 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

  // --- Domain fields ---
  // (see Field Types section)

  // --- Standard columns ---
  @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)

3. Field Types

Type Selection Guide

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

// Optional field — nullable
@Property({ type: 'varchar', length: 255, nullable: true })
notes: string | null = null

// Required field — not nullable (default)
@Property({ type: 'varchar', length: 255 })
name!: string  // Use ! for required fields set during creation

4. Relationship Patterns

One-to-Many (Same Module)

Parent entity has many children. Use @ManyToOne / @OneToMany decorators only within the same module.

// Parent: Category
@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)
  // ...standard columns
}

// Child: Product
@Entity({ tableName: 'products' })
export class Product {
  @PrimaryKey({ type: 'uuid' })
  id: string = v4()

  @ManyToOne(() => Category)
  category!: Category
  // ...standard columns
}

Many-to-Many (Same Module)

Use a junction (pivot) table.

// Junction table entity
@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  // FK to User entity

  // Profile-specific fields
  @Property({ type: 'text', nullable: true })
  bio: string | null = null
  // ...standard columns
}

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  // Self-reference

  @Property({ type: 'varchar', length: 255 })
  name!: string

  // Optional: materialized path for efficient tree queries
  @Property({ type: 'text', default: '' })
  path: string = ''  // e.g., '/root-id/parent-id/this-id'

  @Property({ type: 'int', default: 0 })
  depth: number = 0
  // ...standard columns
}

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 {
  // Reference to customer in another module — just a UUID column
  @Index()
  @Property({ type: 'uuid' })
  customer_id!: string  // FK to customers.person — NO @ManyToOne

  // Reference to assigned user in auth module
  @Index()
  @Property({ type: 'uuid', nullable: true })
  assigned_to: string | null = null  // FK to auth.user
}

Fetching Related Data

To display related data from another module, use a Response Enricher (see system-extension skill):

// data/enrichers.ts
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))]
    // Fetch customer names via API or direct query
    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?

1. Module isolation — modules must be independently deployable and ejectable
2. Circular dependencies — ORM relations create tight coupling between modules
3. Schema ownership — each module owns its entities; cross-module ORM relations blur ownership
4. Extension system — UMES enrichers provide the same capability without coupling

6. Migration Lifecycle

Creating a Migration

# 1. Modify src/modules/<module_id>/data/entities.ts
# 2. Probe/generate migration
yarn db:generate

# 3. Review the generated migration or use it as the baseline for scoped manual SQL
# Check src/modules/<module_id>/migrations/Migration_YYYYMMDD_HHMMSS.ts

# 4. Update src/modules/<module_id>/migrations/.snapshot-open-mercato.json
# 5. Apply migration only after explicit user confirmation
yarn db:migrate

Migration Best Practices

1. Review every migration — auto-generated doesn't mean correct
2. Check for unintended changes — sometimes generators pick up unrelated diffs
3. Do not commit unrelated generated migrations — delete them from the diff
4. 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
5. Update .snapshot-open-mercato.json — it is the baseline that prevents duplicate future migrations
6. New columns should have defaults — prevents breaking existing rows
7. Never rename columns — add new column, migrate data, remove old column (across releases)
8. Never drop tables — soft delete or archive first

Adding a Column to Existing Entity

// Add to entity with a default value
@Property({ type: 'varchar', length: 100, default: '' })
new_field: string = ''

// Or nullable for optional fields
@Property({ type: 'varchar', length: 100, nullable: true })
new_field: string | null = null

Then:

yarn db:generate   # Probes/creates ALTER TABLE ADD COLUMN migration
yarn db:migrate    # Applies it only after explicit user confirmation

Removing a Column

Don't remove columns in a single step. Instead:

1. Stop writing to the column (remove from validators and forms)
2. Make the column nullable if it isn't already
3. 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()

  // Polymorphic reference
  @Index()
  @Property({ type: 'varchar', length: 100 })
  target_type!: string  // 'tickets.ticket', 'orders.order', etc.

  @Index()
  @Property({ type: 'uuid' })
  target_id!: string  // UUID of the referenced entity

  @Property({ type: 'text' })
  body!: string
  // ...standard columns
}

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  // 0, 1, 2, 3...

  @Property({ type: 'varchar', length: 255 })
  title!: string
  // ...standard columns
}

Soft Delete Pattern

All entities already include deleted_at. To implement soft delete:

// In API handlers or commands:
entity.deleted_at = new Date()
entity.is_active = false
await em.flush()

// In queries — filter out deleted records:
const items = await em.find(Entity, {
  organization_id: orgId,
  deleted_at: null,  // Exclude soft-deleted
})

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  // User who made the change

  @Property({ type: 'varchar', length: 50 })
  action!: string  // 'created', 'updated', 'status_changed'

  @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

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>',  // matches the entity's table id (colon-separated)
    fields: [
      { field: 'first_name' },
      { field: 'last_name' },
      { field: 'phone' },
      // Sibling deterministic hash for equality lookups (e.g. login by email).
      // Add a matching `<field>_hash varchar` column to the entity.
      { 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'

// Signature: (em, entityName, where, options?, scope?). MikroORM FindOptions go in slot 4
// (pass `undefined` if you have none), the decryption scope `{ tenantId, organizationId }` in slot 5.
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

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