Data Model Design

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 })