generate-data-model

name	generate-data-model
description	ORM and data model generation patterns. Core file covers Node.js (Prisma/Drizzle/TypeORM). Runtime-specific files: python.md (SQLAlchemy/Django ORM), dotnet.md (EF Core), go.md (GORM/sqlc).

Data Model Generator

Convert your architecture blueprint's database schema into production-ready ORM code with migrations, indexes, and security policies.

Runtime-specific implementations: For non-Node.js backends, read the matching file:

• Python/FastAPI/Django: skills/generate-data-model/python.md
• .NET: skills/generate-data-model/dotnet.md
• Go: skills/generate-data-model/go.md

Perfect for: Project setup, database initialization, schema-first development, team synchronization

---

When to Use This Skill

Use this skill when you need to:

• Convert blueprint database schema to Prisma/Drizzle/TypeORM code
• Generate migration files from architecture design
• Create type-safe database access layer
• Implement Row-Level Security policies
• Set up database indexes for performance
• Share schema with frontend/backend teams
• Initialize database for new project

Input: domain.entities[] from SDL (primary) — check solution.sdl.yaml first; if absent, check sdl/data.yaml or the relevant sdl/ module — with fallback to manifest shared types and blueprint Section 4 Output: ORM schema files, migration files, seed data

---

Supported ORMs

1. Prisma (Recommended for Next.js, Node.js)

Output: prisma/schema.prisma, prisma/migrations/

Why Prisma:

• Best TypeScript support (auto-generated types)
• Excellent developer experience (Prisma Studio GUI)
• Works with PostgreSQL, MySQL, SQLite, MongoDB
• Automatic migration generation
• Built-in connection pooling

Example Output:

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Tenant {
  id        String   @id @default(cuid())
  name      String
  domain    String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  users     User[]
  tickets   Ticket[]

  @@index([domain])
  @@map("tenants")
}

model User {
  id         String   @id @default(cuid())
  tenantId   String
  email      String
  name       String?
  role       UserRole @default(AGENT)
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  // Relationships
  tenant     Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  tickets    Ticket[] @relation("AssignedTickets")

  @@unique([tenantId, email])
  @@index([tenantId])
  @@index([email])
  @@map("users")
}

enum UserRole {
  ADMIN
  AGENT
  VIEWER
}

model Ticket {
  id          String       @id @default(cuid())
  tenantId    String
  title       String
  description String       @db.Text
  status      TicketStatus @default(OPEN)
  priority    Priority     @default(MEDIUM)
  assigneeId  String?
  createdBy   String
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt

  // Relationships
  tenant      Tenant       @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  assignee    User?        @relation("AssignedTickets", fields: [assigneeId], references: [id])
  comments    Comment[]

  @@index([tenantId, status])
  @@index([assigneeId])
  @@index([createdAt])
  @@map("tickets")
}

enum TicketStatus {
  OPEN
  IN_PROGRESS
  RESOLVED
  CLOSED
}

enum Priority {
  LOW
  MEDIUM
  HIGH
  URGENT
}

Prisma — Soft Delete (required on every model)

Add deletedAt to every model and a transparent Prisma middleware that filters deleted records automatically:

model Tenant {
  // ... existing fields ...
  deletedAt DateTime?           // ← add to every model
  @@index([deletedAt])          // ← index for filter performance
}

// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Transparent soft-delete filter — applies to all findMany/findFirst/findUnique
prisma.$use(async (params, next) => {
  const modelsWithSoftDelete = ['Tenant', 'User', 'Ticket']; // list all models
  if (modelsWithSoftDelete.includes(params.model ?? '')) {
    if (['findMany', 'findFirst', 'findUnique', 'count'].includes(params.action)) {
      params.args.where = { ...params.args.where, deletedAt: null };
    }
    if (['delete'].includes(params.action)) {
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
    if (['deleteMany'].includes(params.action)) {
      params.action = 'updateMany';
      params.args.data = { deletedAt: new Date() };
    }
  }
  return next(params);
});

export default prisma;

See skills/production-hardening/SKILL.md Pattern 8 for the full implementation including unique constraint handling.

---

2. Drizzle ORM (Recommended for Edge, Serverless)

Output: db/schema.ts, db/migrations/

Why Drizzle:

• Lightweight (perfect for serverless/edge)
• SQL-like syntax (easier for SQL developers)
• Works with Vercel Postgres, Neon, PlanetScale
• TypeScript-first with excellent type inference

Example Output:

// db/schema.ts
import { pgTable, text, timestamp, pgEnum, index, uniqueIndex } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const userRoleEnum = pgEnum('user_role', ['ADMIN', 'AGENT', 'VIEWER']);
export const ticketStatusEnum = pgEnum('ticket_status', ['OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED']);
export const priorityEnum = pgEnum('priority', ['LOW', 'MEDIUM', 'HIGH', 'URGENT']);

export const tenants = pgTable('tenants', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: text('name').notNull(),
  domain: text('domain').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  domainIdx: uniqueIndex('tenants_domain_idx').on(table.domain),
}));

export const users = pgTable('users', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  tenantId: text('tenant_id').notNull().references(() => tenants.id, { onDelete: 'cascade' }),
  email: text('email').notNull(),
  name: text('name'),
  role: userRoleEnum('role').default('AGENT').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  tenantEmailIdx: uniqueIndex('users_tenant_email_idx').on(table.tenantId, table.email),
  tenantIdx: index('users_tenant_idx').on(table.tenantId),
  emailIdx: index('users_email_idx').on(table.email),
}));

export const tickets = pgTable('tickets', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  tenantId: text('tenant_id').notNull().references(() => tenants.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
  description: text('description').notNull(),
  status: ticketStatusEnum('status').default('OPEN').notNull(),
  priority: priorityEnum('priority').default('MEDIUM').notNull(),
  assigneeId: text('assignee_id').references(() => users.id),
  createdBy: text('created_by').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  tenantStatusIdx: index('tickets_tenant_status_idx').on(table.tenantId, table.status),
  assigneeIdx: index('tickets_assignee_idx').on(table.assigneeId),
  createdAtIdx: index('tickets_created_at_idx').on(table.createdAt),
}));

// Relations
export const tenantsRelations = relations(tenants, ({ many }) => ({
  users: many(users),
  tickets: many(tickets),
}));

export const usersRelations = relations(users, ({ one, many }) => ({
  tenant: one(tenants, { fields: [users.tenantId], references: [tenants.id] }),
  assignedTickets: many(tickets),
}));

export const ticketsRelations = relations(tickets, ({ one, many }) => ({
  tenant: one(tenants, { fields: [tickets.tenantId], references: [tenants.id] }),
  assignee: one(users, { fields: [tickets.assigneeId], references: [users.id] }),
  comments: many(comments),
}));

Drizzle — Soft Delete (required on every table)

Add deletedAt to every table and a withSoftDelete query helper:

// db/schema.ts — add to every table
export const tenants = pgTable('tenants', {
  // ... existing columns ...
  deletedAt: timestamp('deleted_at'),           // ← add to every table
}, (table) => ({
  // ... existing indexes ...
  deletedAtIdx: index('tenants_deleted_at_idx').on(table.deletedAt),
}));

// db/helpers.ts
import { isNull } from 'drizzle-orm';

export function withSoftDelete<T extends { deletedAt: unknown }>(table: T) {
  return isNull(table.deletedAt);
}

// Usage in queries:
// db.select().from(tenants).where(withSoftDelete(tenants))
// Never call .delete() — use: db.update(tenants).set({ deletedAt: new Date() }).where(eq(tenants.id, id))

---

3. TypeORM (For Enterprise, Java-like patterns)

Output: src/entities/, src/migrations/

Why TypeORM:

• Mature, battle-tested in enterprise
• Decorator-based (familiar to Java/Spring developers)
• Advanced features (multi-database, replication)
• Works with TypeScript and JavaScript

Soft Delete (TypeORM)

Use the built-in @DeleteDateColumn decorator — TypeORM's SoftDelete() and SoftRemove() methods set this column automatically, and all find* queries transparently exclude soft-deleted rows.

// src/entities/base.entity.ts
import { CreateDateColumn, DeleteDateColumn, PrimaryGeneratedColumn, UpdateDateColumn } from 'typeorm';

export abstract class BaseEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  @DeleteDateColumn()           // ← soft delete column; null = active row
  deletedAt: Date | null;
}

// src/entities/user.entity.ts
import { Column, Entity, Index, ManyToOne } from 'typeorm';
import { BaseEntity } from './base.entity';

@Entity('users')
export class User extends BaseEntity {
  @Column()
  email: string;

  @Column({ nullable: true })
  name: string | null;
}

Soft delete usage:

// Soft-delete (sets deletedAt, excluded from future finds automatically)
await userRepository.softDelete(id);
// or
await userRepository.softRemove(user);

// Hard-delete (permanent — avoid unless legally required)
await userRepository.delete(id);

// Restore
await userRepository.restore(id);

// Query including soft-deleted rows (for admin views)
await userRepository.find({ withDeleted: true });

Migration: add deleted_at TIMESTAMP NULL to every entity table via TypeORM CLI migrations.

4. SQL (Raw migrations)

Output: db/migrations/001_initial_schema.sql

Why SQL:

• Maximum control and flexibility
• No ORM overhead
• Works with any language/framework
• Easier to review and optimize

---

How It Works

Step 1: Resolve Entity Inventory

Primary source: domain.entities[] from SDL — the authoritative entity list. Each entry is a PascalCase entity name (e.g. User, Order, Product).

SDL location (check in order):

1. solution.sdl.yaml at project root — use if present
2. sdl/ directory — if solution.sdl.yaml is absent, read sdl/README.md first, then sdl/data.yaml (or whichever module contains domain:)
3. Never read a file named sdl.yaml — legacy filename, treat as solution.sdl.yaml

Fallback order (if domain.entities[] is absent from SDL):

1. _state.json.entities — already-extracted entity summaries
2. Shared types from the manifest (shared.types[])
3. Blueprint Section 4 markdown (legacy format, shown below for reference)

The data: section of SDL describes database infrastructure (type, hosting, indexes) — never extract entity names from it.

When using blueprint markdown as fallback, extract from Section 4: Database Schema:

## Database Schema

### Entities

#### Tenant
- `id` (UUID, PK)
- `name` (String, required)
- `domain` (String, unique, required)
- `created_at` (Timestamp)
- `updated_at` (Timestamp)

**Relationships**:
- Has many Users
- Has many Tickets

**Indexes**:
- `domain` (unique)

#### User
- `id` (UUID, PK)
- `tenant_id` (UUID, FK → Tenant, required)
- `email` (String, required)
- `name` (String, optional)
- `role` (Enum: ADMIN, AGENT, VIEWER, default: AGENT)
- `created_at` (Timestamp)
- `updated_at` (Timestamp)

**Relationships**:
- Belongs to Tenant
- Has many Tickets (as assignee)

**Indexes**:
- `(tenant_id, email)` (unique composite)
- `tenant_id`
- `email`

**Constraints**:
- ON DELETE CASCADE (when tenant deleted, delete users)

Step 2: Detect ORM from Tech Stack

Check Section 3: Tech Stack Decisions for ORM choice:

• Next.js + Vercel → Prisma (default)
• Edge/Serverless → Drizzle
• Enterprise/Java background → TypeORM
• Not specified → Ask user or default to Prisma

Step 3: Convert Schema to ORM Syntax

For each entity:

1. Map data types: Blueprint types → ORM types

   • UUID → @id @default(cuid()) (Prisma) or text('id').primaryKey() (Drizzle)
   • String → String or text()
   • Timestamp → DateTime or timestamp()
   • Enum → enum or pgEnum()

2. Add relationships: Foreign keys, one-to-many, many-to-many

   • tenant_id FK → Tenant → @relation(fields: [tenantId], references: [id])

3. Add indexes: Performance optimization

   • Single column: @@index([column])
   • Composite: @@index([col1, col2])
   • Unique: @@unique([column])

4. Add constraints: Cascades, checks, defaults

   • onDelete: Cascade for multi-tenant isolation
   • @default(now()) for timestamps
   • @default(AGENT) for enums

Step 4: Generate Row-Level Security Policies

If multi-tenant B2B SaaS detected, add RLS:

PostgreSQL RLS (SQL):

-- Enable RLS on all tables
ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own tenant's data
CREATE POLICY tenant_isolation_policy ON users
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation_policy ON tickets
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Admins can see all tenants (override policy)
CREATE POLICY admin_override_policy ON users
  FOR ALL
  TO admin_role
  USING (true);

Prisma/Drizzle Middleware (app-level RLS):

// prisma/middleware.ts
import { Prisma } from '@prisma/client';

export function tenantMiddleware(tenantId: string) {
  return Prisma.defineExtension((client) => {
    return client.$extends({
      query: {
        $allModels: {
          async $allOperations({ args, query, model }) {
            // Automatically add tenantId filter to all queries
            if ('where' in args) {
              args.where = { ...args.where, tenantId };
            }
            if ('data' in args && model !== 'Tenant') {
              args.data = { ...args.data, tenantId };
            }
            return query(args);
          },
        },
      },
    });
  });
}

// Usage:
const db = prisma.$extends(tenantMiddleware(currentTenantId));

Step 5: Generate Seed Data

Create prisma/seed.ts or db/seed.ts:

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  // Create demo tenant
  const tenant = await prisma.tenant.create({
    data: {
      name: 'Acme Corp',
      domain: 'acme.example.com',
    },
  });

  // Create admin user
  const admin = await prisma.user.create({
    data: {
      tenantId: tenant.id,
      email: '[e-mail]',
      name: 'Admin User',
      role: 'ADMIN',
    },
  });

  // Create sample ticket
  await prisma.ticket.create({
    data: {
      tenantId: tenant.id,
      title: 'Welcome to the system',
      description: 'This is a sample ticket',
      status: 'OPEN',
      priority: 'LOW',
      createdBy: admin.id,
    },
  });

  console.log('✅ Database seeded successfully');
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Add to package.json:

{
  "scripts": {
    "db:seed": "tsx prisma/seed.ts"
  },
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Step 6: Generate Initial Migration

Prisma:

npx prisma migrate dev --name init
# Creates: prisma/migrations/20260207000000_init/migration.sql

Drizzle:

npx drizzle-kit generate:pg
# Creates: db/migrations/0000_initial_schema.sql

Raw SQL:

-- db/migrations/001_initial_schema.sql

-- Create enums
CREATE TYPE user_role AS ENUM ('ADMIN', 'AGENT', 'VIEWER');
CREATE TYPE ticket_status AS ENUM ('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED');
CREATE TYPE priority AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'URGENT');

-- Create tables
CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  domain TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX tenants_domain_idx ON tenants(domain);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  name TEXT,
  role user_role NOT NULL DEFAULT 'AGENT',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(tenant_id, email)
);

CREATE INDEX users_tenant_idx ON users(tenant_id);
CREATE INDEX users_email_idx ON users(email);

[... more tables ...]

-- Enable Row-Level Security
ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;

-- RLS Policies
CREATE POLICY tenant_isolation_policy ON users
  FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

[... more policies ...]

---

Output Format

When invoked, generate:

🗄️  Generating data model from blueprint...

✅ Detected ORM: Prisma (from Next.js tech stack)
✅ Parsed database schema (5 entities, 12 relationships)
   - Entities: Tenant, User, Ticket, Comment, Attachment
   - Relationships: 8 one-to-many, 4 many-to-many
   - Indexes: 15 (performance optimized)
   - Constraints: 8 cascade deletes

✅ Generated prisma/schema.prisma (287 lines)
✅ Generated prisma/seed.ts (sample data)
✅ Generated prisma/middleware.ts (RLS for multi-tenancy)
✅ Generated db/rls-policies.sql (PostgreSQL RLS)

📦 Next steps to initialize database:

1. Install Prisma:
   npm install prisma @prisma/client
   npm install -D tsx

2. Generate Prisma Client:
   npx prisma generate

3. Run initial migration:
   npx prisma migrate dev --name init

4. Seed database with sample data:
   npm run db:seed

5. Open Prisma Studio (database GUI):
   npx prisma studio

🔒 Multi-tenancy configured:
- All queries automatically scoped by tenant_id
- Row-Level Security policies enabled
- Cascade deletes configured
- Sample middleware for app-level isolation

---

Features

Type Safety

Prisma:

// Auto-generated types from schema
import { PrismaClient, User, Ticket, TicketStatus } from '@prisma/client';

const db = new PrismaClient();

// Type-safe queries
const user: User = await db.user.findUnique({
  where: { id: '123' },
  include: { tickets: true }, // Type-safe includes
});

// Type-safe creates
const ticket: Ticket = await db.ticket.create({
  data: {
    tenantId: 'tenant-123',
    title: 'New ticket',
    status: TicketStatus.OPEN, // Enum type
    priority: 'HIGH', // Type error if invalid
  },
});

Performance Indexes

Automatically add indexes for:

• Foreign keys (always indexed)
• Composite unique constraints (tenant_id + email)
• Frequently queried columns (status, created_at)
• Timestamp-based queries (created_at DESC for pagination)

Migration Management

Version control for schema changes:

prisma/migrations/
├── 20260207_init/
│   └── migration.sql
├── 20260214_add_priority/
│   └── migration.sql
└── 20260221_add_attachments/
    └── migration.sql

Rollback support:

# Rollback last migration
npx prisma migrate resolve --rolled-back 20260221_add_attachments

# Reset database (dev only)
npx prisma migrate reset

Seed Data Templates

Generate realistic seed data based on product type:

• B2B SaaS: Multiple tenants, users, workspaces
• E-commerce: Products, orders, customers
• Social Platform: Users, posts, comments, likes
• Ticketing: Tickets, comments, attachments

---

Error Handling

If database schema section missing:

• Action: Error with guidance
• Example: "❌ No database schema found in blueprint. Run /architect:blueprint first."

If ORM not detected:

• Action: Prompt user to choose
• Options: "1) Prisma (recommended), 2) Drizzle, 3) TypeORM, 4) Raw SQL"

If relationships ambiguous:

• Action: Make reasonable assumptions, document in comments
• Example: // Assuming one-to-many: Tenant has many Users

If enum values not specified:

• Action: Use common defaults
• Example: TicketStatus = OPEN, IN_PROGRESS, RESOLVED, CLOSED

---

Success Criteria

A successful data model generation should:

• ✅ Include all entities from blueprint
• ✅ Preserve all relationships (FK, one-to-many, many-to-many)
• ✅ Add performance indexes on foreign keys and common queries
• ✅ Include RLS policies for multi-tenant apps
• ✅ Generate seed data for testing
• ✅ Be type-safe (TypeScript types generated)
• ✅ Support migrations (version-controlled schema changes)
• ✅ Include cascade deletes where appropriate
• ✅ Follow ORM best practices
• ✅ Be production-ready (no placeholder/TODO code)

---

Examples

Example 1: Prisma (Default)

/architect:generate-data-model

# Output:
# ✅ Generated prisma/schema.prisma
# ✅ Generated prisma/seed.ts
# ✅ Generated prisma/middleware.ts (RLS)

Example 2: Drizzle (Edge/Serverless)

/architect:generate-data-model --orm=drizzle

# Output:
# ✅ Generated db/schema.ts
# ✅ Generated db/seed.ts
# ✅ Generated drizzle.config.ts

Example 3: Raw SQL

/architect:generate-data-model --orm=sql

# Output:
# ✅ Generated db/migrations/001_initial_schema.sql
# ✅ Generated db/seed.sql
# ✅ Generated db/rls-policies.sql

Example 4: With Sample Data

/architect:generate-data-model --seed

# Output:
# ✅ Generated schema + seed data
# ✅ Created 3 demo tenants, 10 users, 25 tickets