data-model

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/foundation/schemas/src/{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/foundation/schemas/src/{entity-name}.sql.ts
2. Generate migration: cd packages/foundation/schemas && pnpm db generate
3. Run lint: pnpm lint

Architecture Overview

┌─────────────────────────────────────────────────────────────────────┐
│  Step 1: Schema (packages/foundation/schemas/src/{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/foundation/schemas && pnpm db generate                │
└──────────────────────────────────────────────────────────────────────┘

Reference: Schema Patterns

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

Key Patterns

Foreign Key References

import { id, timestamps, ulid } from "@database/types";
import { pgTable } from "drizzle-orm/pg-core";
import { examplesTable } from "./examples.sql";
import { workspaceID } from "./workspaces.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

Reference: oRPC Patterns

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

Workspace Role Mappers

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