data-model
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.
When & Why to Use This Skill
This Claude skill automates the end-to-end data model development workflow, enabling developers to quickly scaffold database schemas, Zod validation layers, business entities, and oRPC API routes. It ensures architectural consistency and significantly reduces boilerplate code when adding new features or entities to a TypeScript-based full-stack application.
Use Cases
- Rapidly scaffolding new database entities (e.g., 'Campaigns', 'BlogPosts') with pre-configured ULID primary keys and multi-tenant workspace logic.
- Ensuring end-to-end type safety by generating shared Zod schemas that synchronize data validation between the frontend and backend.
- Standardizing API development by automatically creating oRPC routes integrated with workspace middleware and role-based access control (RBAC).
- Streamlining database management by automating the creation of Drizzle ORM schemas and the subsequent generation of SQL migrations.
- Maintaining project architecture by auto-updating domain index files and following the 'Ent' business logic encapsulation pattern.
| 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/foundation/schemas/src/{entity-name}.sql.tspackages/core/src/domain/{domain}/entity/Ent{EntityName}.tspackages/shared/src/{domain}/{entity-name}.tspackages/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:
- Add custom fields to schema:
packages/foundation/schemas/src/{entity-name}.sql.ts - Generate migration:
cd packages/foundation/schemas && pnpm db generate - 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
| 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 { 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
| 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
pgEnumandz.enumdefinitions - 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 lintpasses
Reference Files
| Layer | Example File |
|---|---|
| Schema | packages/foundation/schemas/src/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 |