data-model-design

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

Ir para a instalação

Repositório: open-mercato/official-modules
Última atividade na origem: 8 de maio de 2026 às 06:03
Idioma detectado do SKILL.md: inglês
Estrelas: 12
Forks: 10

Opções de instalação

Por padrão, está selecionado o prompt que primeiro revisa a origem. Você pode mudar para um comando direto ou baixar uma cópia local.

Revise os arquivos de origem

Leia o SKILL.md e os arquivos complementares exibidos pelo SkillsMP antes de decidir se vai instalar.

Voltar à pesquisa Voltar à instalação

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
}

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

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

export enum OrderStatus {
  DRAFT = 'draft',
  PENDING = 'pending',
  CONFIRMED = 'confirmed',
  SHIPPED = 'shipped',
  DELIVERED = 'delivered',
  CANCELLED = 'cancelled',
}

@Enum({ items: () => OrderStatus })
status: OrderStatus = OrderStatus.DRAFT

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

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

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

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

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

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

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

# 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

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

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

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

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

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

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

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

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

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

yarn mercato entities seed-encryption --tenant <tenantId> [--organization <orgId>]

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