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. 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

import { Entity, Property, PrimaryKey, Index, Enum } from '@mikro-orm/core'
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 or create entity files
# 2. Generate migration
yarn db:generate

# 3. Review the generated migration
# Check src/modules/<module_id>/migrations/Migration_YYYYMMDD_HHMMSS.ts

# 4. Apply migration (confirm with user first)
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. New columns should have defaults — prevents breaking existing rows
4. Never rename columns — add new column, migrate data, remove old column (across releases)
5. 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   # Creates ALTER TABLE ADD COLUMN migration
yarn db:migrate    # Applies it

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. 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 run yarn db:generate after entity changes, never hand-write migrations
• MUST review generated migration before applying
• 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 NOT store sensitive data without encryption (use findWithDecryption)
• 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