---

name: data_model description: Adding new data models - schema, shared zod, entity pattern, and oRPC routes. Use when user asks to create a new entity, model, or feature that needs database tables.

Data Model Development

This skill covers the full workflow for adding new data models to OpenPromo, from database schema to API routes.

Quick Scaffold Workflow

1. Run the Scaffold Script

python .claude/skills/data_model/scripts/scaffold.py <EntityName> <domain>

# Examples:
python .claude/skills/data_model/scripts/scaffold.py Campaign marketing
python .claude/skills/data_model/scripts/scaffold.py BlogPost content --dry-run

This generates all 4 files:

• packages/core/src/schemas/{entity-name}.sql.ts
• packages/core/src/domain/{domain}/entity/Ent{EntityName}.ts
• packages/shared/src/{domain}/{entity-name}.ts
• packages/dash/worker/src/orpc/routes/{entity-name}.ts

2. Post-Scaffold Steps

The script auto-updates all index files (entity, domain, shared, oRPC). After running:

1. Add custom fields to schema: packages/core/src/schemas/{entity-name}.sql.ts
2. Generate migration: cd packages/core && pnpm db generate
3. Run lint: pnpm lint

---

Architecture Overview

┌─────────────────────────────────────────────────────────────────┐
│  Step 1: Schema (packages/core/src/schemas/{name}.sql.ts)       │
│  - Drizzle table definitions, enums, JSONB types                │
└────────────────────────────┬────────────────────────────────────┘
                             │
┌────────────────────────────▼────────────────────────────────────┐
│  Step 2: Shared (packages/shared/src/{domain}/index.ts)         │
│  - Zod schemas for API contracts, shared across FE/BE           │
└────────────────────────────┬────────────────────────────────────┘
                             │
┌────────────────────────────▼────────────────────────────────────┐
│  Step 3: Entity (packages/core/src/domain/{domain}/entity/)     │
│  - Business logic encapsulation using Ent pattern               │
└────────────────────────────┬────────────────────────────────────┘
                             │
┌────────────────────────────▼────────────────────────────────────┐
│  Step 4: oRPC Routes (packages/dash/worker/src/orpc/routes/)    │
│  - API endpoints with workspace middleware                      │
└────────────────────────────┬────────────────────────────────────┘
                             │
┌────────────────────────────▼────────────────────────────────────┐
│  Step 5: Generate Migration                                     │
│  - cd packages/core && pnpm db generate                         │
└─────────────────────────────────────────────────────────────────┘

---

Reference: Schema Patterns

Location: packages/core/src/schemas/{name}.sql.ts

Key Patterns

Pattern	Usage
...id	ULID primary key with auto-generation
...workspaceID	Multi-tenant workspace foreign key
...timestamps	createdAt/updatedAt with auto-update
ulid("field_id")	ULID foreign key reference
pgEnum + z.enum	Type-safe enums in DB and TypeScript
jsonb().$type<T>()	Typed JSONB columns
uniqueIndex	Unique constraints
index	Query performance indexes

Foreign Key References

import { examplesTable } from "./examples.sql";

export const exampleItemsTable = pgTable("example_items", {
  ...id,
  ...workspaceID,
  ...timestamps,
  exampleId: ulid("example_id")
    .references(() => examplesTable.id, { onDelete: "cascade" })
    .notNull(),
});

---

Reference: Entity Patterns

Location: packages/core/src/domain/{domain}/entity/Ent{Name}.ts

Pattern	Usage
extends Ent<T>	Base class for serialization
static type	Type identifier for serialization
Schemas()	Input validation schemas
fn(schema, handler)	Schema-validated methods
Actor.workspaceID()	Get current workspace from context
fromID	Workspace-scoped lookup
fromIDSystem	System lookup (no workspace check)
toJSON()	Return raw data for API responses

---

Reference: oRPC Patterns

Location: packages/dash/worker/src/orpc/routes/{name}.ts

Workspace Role Mappers

Role	Permission Level
workspaceRoleMappers.viewer	Read-only access
workspaceRoleMappers.editor	Create/update access
workspaceRoleMappers.admin	Full access including delete

---

Common Patterns

Pagination

static list = fn(z.object({
  page: z.number().int().positive().default(1),
  pageSize: z.number().int().positive().max(100).default(20),
}), async (input) => {
  const workspaceId = Actor.workspaceID();
  const offset = (input.page - 1) * input.pageSize;

  const items = await db()
    .select()
    .from(table)
    .where(eq(table.workspaceId, workspaceId))
    .orderBy(desc(table.createdAt))
    .limit(input.pageSize)
    .offset(offset);

  return items.map((i) => new EntItem(i));
});

Upsert Pattern

static upsert = fn(schema, async (input) => {
  const workspaceId = Actor.workspaceID();

  const [existing] = await db()
    .select()
    .from(table)
    .where(and(
      eq(table.workspaceId, workspaceId),
      eq(table.uniqueField, input.uniqueField)
    ))
    .limit(1);

  if (existing) {
    const [updated] = await db()
      .update(table)
      .set({ ...input })
      .where(eq(table.id, existing.id))
      .returning();
    return { item: new EntItem(updated), isNew: false };
  }

  const [created] = await db()
    .insert(table)
    .values({ workspaceId, ...input })
    .returning();
  return { item: new EntItem(created), isNew: true };
});

Soft Delete

// In schema
deletedAt: timestamp(),

// In entity
async archive(): Promise<void> {
  await db()
    .update(table)
    .set({ deletedAt: new Date() })
    .where(eq(table.id, this.data.id));
}

// In list queries - exclude soft-deleted
const conditions = [
  eq(table.workspaceId, Actor.workspaceID()),
  isNull(table.deletedAt),
];

---

Checklist

• Schema file created with ...id, ...workspaceID, ...timestamps
• Enums have both pgEnum and z.enum definitions
• JSONB types have zod schemas
• Proper indexes defined for queries
• Types exported with $inferSelect, $inferInsert
• Shared zod schemas created
• Entity class with static type, Schemas(), fromID, list, toJSON
• Entity exported from domain index
• oRPC routes use createWorkspaceInputSchema
• Routes use appropriate workspaceRoleMappers
• Router registered in orpc index
• Migration generated with pnpm db generate
• pnpm lint passes

---

Reference Files

Layer	Example File
Schema	packages/core/src/schemas/radar.sql.ts
Entity	packages/core/src/domain/radar/entity/EntRadarSource.ts
oRPC Route	packages/dash/worker/src/orpc/routes/radar.ts
Shared	packages/shared/src/content/index.ts