gh-igpastor-sng-claude-marketplace-plugins-sngular-backend/commands/sng-model.md

# Create Database Model Command

You are helping the user create a database model with proper relationships, validation, and migrations following Sngular's backend best practices.

## Instructions

1. **Detect the ORM/database tool**:
   - TypeORM (TypeScript/Node.js)
   - Prisma (TypeScript/Node.js)
   - Sequelize (JavaScript/TypeScript)
   - Mongoose (MongoDB)
   - SQLAlchemy (Python)
   - Django ORM (Python)
   - GORM (Go)
   - Other

2. **Determine database type**:
   - PostgreSQL
   - MySQL/MariaDB
   - MongoDB
   - SQLite
   - SQL Server
   - Other

3. **Ask for model details**:
   - Model name (e.g., User, Product, Order)
   - Fields/attributes with types
   - Validation rules
   - Relationships to other models
   - Indexes needed
   - Timestamps (created_at, updated_at)
   - Soft deletes needed

4. **Identify relationships**:
   - One-to-One
   - One-to-Many
   - Many-to-Many
   - Self-referential

## Implementation Tasks

### 1. Create Model Class/Schema

```typescript
// TypeORM Example
import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn, UpdateDateColumn, ManyToOne, OneToMany } from 'typeorm'

@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string

  @Column({ unique: true })
  email: string

  @Column()
  name: string

  @Column({ nullable: true })
  avatar?: string

  @Column({ default: true })
  isActive: boolean

  @CreateDateColumn()
  createdAt: Date

  @UpdateDateColumn()
  updatedAt: Date

  // Relationships
  @OneToMany(() => Post, post => post.author)
  posts: Post[]

  @ManyToOne(() => Role, role => role.users)
  role: Role
}
```

### 2. Add Validation

```typescript
import { IsEmail, IsString, MinLength, MaxLength, IsOptional } from 'class-validator'

export class CreateUserDto {
  @IsEmail()
  email: string

  @IsString()
  @MinLength(2)
  @MaxLength(100)
  name: string

  @IsString()
  @IsOptional()
  avatar?: string
}
```

### 3. Create Migration

```typescript
// TypeORM Migration
import { MigrationInterface, QueryRunner, Table, TableForeignKey } from 'typeorm'

export class CreateUsersTable1234567890 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'users',
        columns: [
          {
            name: 'id',
            type: 'uuid',
            isPrimary: true,
            generationStrategy: 'uuid',
            default: 'uuid_generate_v4()',
          },
          {
            name: 'email',
            type: 'varchar',
            isUnique: true,
          },
          {
            name: 'name',
            type: 'varchar',
          },
          {
            name: 'avatar',
            type: 'varchar',
            isNullable: true,
          },
          {
            name: 'is_active',
            type: 'boolean',
            default: true,
          },
          {
            name: 'role_id',
            type: 'uuid',
            isNullable: true,
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'now()',
          },
          {
            name: 'updated_at',
            type: 'timestamp',
            default: 'now()',
          },
        ],
      }),
      true,
    )

    // Add foreign key
    await queryRunner.createForeignKey(
      'users',
      new TableForeignKey({
        columnNames: ['role_id'],
        referencedColumnNames: ['id'],
        referencedTableName: 'roles',
        onDelete: 'SET NULL',
      }),
    )

    // Add indexes
    await queryRunner.createIndex('users', {
      name: 'IDX_USER_EMAIL',
      columnNames: ['email'],
    })
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable('users')
  }
}
```

### 4. Create Repository/Service

```typescript
// Repository pattern
import { Repository } from 'typeorm'
import { User } from './user.entity'

export class UserRepository extends Repository<User> {
  async findByEmail(email: string): Promise<User | null> {
    return this.findOne({ where: { email } })
  }

  async findActiveUsers(): Promise<User[]> {
    return this.find({
      where: { isActive: true },
      relations: ['role', 'posts'],
    })
  }

  async createUser(data: CreateUserDto): Promise<User> {
    const user = this.create(data)
    return this.save(user)
  }
}
```

## Prisma Example

```typescript
// schema.prisma
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  avatar    String?
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  posts     Post[]
  role      Role?    @relation(fields: [roleId], references: [id])
  roleId    String?

  @@index([email])
  @@map("users")
}

model Post {
  id        String   @id @default(uuid())
  title     String
  content   String
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  author    User     @relation(fields: [authorId], references: [id])
  authorId  String

  @@map("posts")
}

model Role {
  id    String @id @default(uuid())
  name  String @unique
  users User[]

  @@map("roles")
}
```

## Mongoose Example (MongoDB)

```typescript
import mongoose, { Schema, Document } from 'mongoose'

export interface IUser extends Document {
  email: string
  name: string
  avatar?: string
  isActive: boolean
  roleId?: mongoose.Types.ObjectId
  createdAt: Date
  updatedAt: Date
}

const UserSchema = new Schema<IUser>(
  {
    email: {
      type: String,
      required: true,
      unique: true,
      lowercase: true,
      trim: true,
      validate: {
        validator: (v: string) => /\S+@\S+\.\S+/.test(v),
        message: 'Invalid email format',
      },
    },
    name: {
      type: String,
      required: true,
      minlength: 2,
      maxlength: 100,
    },
    avatar: {
      type: String,
    },
    isActive: {
      type: Boolean,
      default: true,
    },
    roleId: {
      type: Schema.Types.ObjectId,
      ref: 'Role',
    },
  },
  {
    timestamps: true,
  },
)

// Indexes
UserSchema.index({ email: 1 })
UserSchema.index({ isActive: 1, createdAt: -1 })

// Virtual populate
UserSchema.virtual('posts', {
  ref: 'Post',
  localField: '_id',
  foreignField: 'authorId',
})

// Methods
UserSchema.methods.toJSON = function () {
  const obj = this.toObject()
  delete obj.__v
  return obj
}

export const User = mongoose.model<IUser>('User', UserSchema)
```

## Best Practices

### 1. Naming Conventions
- Use singular names for models (User, not Users)
- Use camelCase for field names in code
- Use snake_case for database column names
- Prefix foreign keys with table name (user_id, not just id)

### 2. Data Types
- Use UUID for primary keys
- Use ENUM for fixed sets of values
- Use appropriate numeric types (int, bigint, decimal)
- Use TEXT for unlimited length strings
- Use JSONB for flexible data (PostgreSQL)

### 3. Relationships
- Always define both sides of relationships
- Use appropriate cascade options (CASCADE, SET NULL, RESTRICT)
- Index foreign key columns
- Consider soft deletes for important data

### 4. Indexes
- Index columns used in WHERE clauses
- Index foreign key columns
- Create composite indexes for multi-column queries
- Don't over-index (impacts write performance)

### 5. Validation
- Validate at both model and database level
- Use appropriate constraints (NOT NULL, UNIQUE, CHECK)
- Validate data types and formats
- Implement custom validators for complex rules

### 6. Timestamps
- Always include created_at and updated_at
- Consider deleted_at for soft deletes
- Use database-level defaults (now())

### 7. Security
- Never store passwords in plain text
- Hash sensitive data
- Use appropriate field types for sensitive data
- Implement row-level security where needed

## Files to Create

1. **Entity/Model file**: Model definition
2. **DTO files**: Data transfer objects for validation
3. **Migration file**: Database schema changes
4. **Repository file**: Data access methods (if applicable)
5. **Seed file**: Sample data for development/testing
6. **Tests**: Model and repository tests

## Testing Example

```typescript
import { User } from './user.entity'
import { AppDataSource } from './data-source'

describe('User Model', () => {
  beforeAll(async () => {
    await AppDataSource.initialize()
  })

  afterAll(async () => {
    await AppDataSource.destroy()
  })

  it('creates a user with valid data', async () => {
    const user = User.create({
      email: 'test@example.com',
      name: 'Test User',
    })

    await user.save()

    expect(user.id).toBeDefined()
    expect(user.email).toBe('test@example.com')
    expect(user.createdAt).toBeInstanceOf(Date)
  })

  it('enforces unique email constraint', async () => {
    await User.create({ email: 'duplicate@example.com', name: 'User 1' }).save()

    await expect(
      User.create({ email: 'duplicate@example.com', name: 'User 2' }).save()
    ).rejects.toThrow()
  })

  it('validates email format', async () => {
    const user = User.create({ email: 'invalid-email', name: 'Test User' })

    await expect(user.save()).rejects.toThrow()
  })
})
```

## Common Relationship Patterns

### One-to-Many
```typescript
// One user has many posts
@OneToMany(() => Post, post => post.author)
posts: Post[]

@ManyToOne(() => User, user => user.posts)
author: User
```

### Many-to-Many
```typescript
// Users can have many roles, roles can have many users
@ManyToMany(() => Role, role => role.users)
@JoinTable({ name: 'user_roles' })
roles: Role[]

@ManyToMany(() => User, user => user.roles)
users: User[]
```

### Self-Referential
```typescript
// User can have a manager who is also a User
@ManyToOne(() => User, user => user.subordinates)
manager: User

@OneToMany(() => User, user => user.manager)
subordinates: User[]
```

Ask the user: "What database model would you like to create?"