Write Model

Overview

This skill creates database models that follow the Epic three-layer architecture. Models belong to the Infrastructure layer and provide Active Record wrappers over Drizzle tables.

Architecture Context

Backend: Actions call models
            |
            v
Infrastructure: Models (database operations)
                  |
                  v
              Drizzle ORM -> Database

Models:

• Run on the server (Infrastructure layer)
• Thin Active Record wrappers over Drizzle tables
• Handle ALL database operations
• May only be imported by Backend layer (Actions, Routes)
• NEVER imported by Frontend

Model Location

shared/models/
  model-name.ts

Class Specification Format

Follow the Epic Class specification format from docs/Epic.md:

# ModelName

[Description of what this model manages]

## Properties
- db: Database

## Methods
- findAll(): Promise<ModelName[]>
- findById(id: string): Promise<ModelName | null>
- findByUserId(userId: string): Promise<ModelName[]>
- create(data: NewModelName): Promise<ModelName>
- update(id: string, data: Partial<NewModelName>): Promise<ModelName | null>
- delete(id: string): Promise<boolean>

Implementation Pattern

import { db } from '@/db/client';
import { modelNameTable, InsertModelName, SelectModelName } from '@/db/schema';
import { eq, and, isNull, desc } from 'drizzle-orm';

// Export the type for use in other files
export type ModelName = SelectModelName;

// Export the model object with all methods
export const ModelName = {
  async findAll(): Promise<ModelName[]> {
    return await db
      .select()
      .from(modelNameTable)
      .where(isNull(modelNameTable.deletedAt))
      .orderBy(desc(modelNameTable.createdAt));
  },

  async findById(id: string): Promise<ModelName | null> {
    const results = await db
      .select()
      .from(modelNameTable)
      .where(
        and(
          eq(modelNameTable.id, id),
          isNull(modelNameTable.deletedAt)
        )
      )
      .limit(1);
    return results[0] || null;
  },

  async findByUserId(userId: string): Promise<ModelName[]> {
    return await db
      .select()
      .from(modelNameTable)
      .where(
        and(
          eq(modelNameTable.userId, userId),
          isNull(modelNameTable.deletedAt)
        )
      )
      .orderBy(desc(modelNameTable.createdAt));
  },

  async create(
    data: Omit<InsertModelName, 'id' | 'createdAt' | 'updatedAt'>
  ): Promise<ModelName> {
    const id = crypto.randomUUID();
    const now = new Date();

    const [created] = await db
      .insert(modelNameTable)
      .values({
        ...data,
        id,
        createdAt: now,
        updatedAt: now,
      })
      .returning();

    return created;
  },

  async update(
    id: string,
    data: Partial<Omit<InsertModelName, 'id' | 'createdAt'>>
  ): Promise<ModelName | null> {
    const [updated] = await db
      .update(modelNameTable)
      .set({
        ...data,
        updatedAt: new Date(),
      })
      .where(
        and(
          eq(modelNameTable.id, id),
          isNull(modelNameTable.deletedAt)
        )
      )
      .returning();

    return updated || null;
  },

  async delete(id: string): Promise<boolean> {
    // Soft delete
    const result = await db
      .update(modelNameTable)
      .set({
        deletedAt: new Date(),
        updatedAt: new Date(),
      })
      .where(eq(modelNameTable.id, id));

    return result.rowsAffected > 0;
  },
};

Standard Methods

Every model should include:

Key Patterns

1. Soft Delete Handling

// Always filter out deleted records
.where(isNull(modelNameTable.deletedAt))

// Soft delete implementation
async delete(id: string): Promise<boolean> {
  const result = await db
    .update(modelNameTable)
    .set({ deletedAt: new Date(), updatedAt: new Date() })
    .where(eq(modelNameTable.id, id));
  return result.rowsAffected > 0;
}

2. ID and Timestamp Generation

const id = crypto.randomUUID();
const now = new Date();

await db.insert(table).values({
  ...data,
  id,
  createdAt: now,
  updatedAt: now,
});

3. User-Scoped Queries

async findByUserId(userId: string): Promise<ModelName[]> {
  return await db
    .select()
    .from(table)
    .where(and(
      eq(table.userId, userId),
      isNull(table.deletedAt)
    ));
}

4. Custom Query Methods

async findActiveByUserId(userId: string): Promise<ModelName[]> {
  return await db
    .select()
    .from(table)
    .where(and(
      eq(table.userId, userId),
      eq(table.status, 'active'),
      isNull(table.deletedAt)
    ));
}

Constraints

• Export both TypeScript type AND methods object
• Handle soft deletes if schema has deletedAt
• Generate UUIDs for new records
• Set createdAt and updatedAt automatically
• User-scoped resources need findByUserId
• NEVER import from Frontend or Backend layers

Checklist

Before finalizing a model:

• Schema alignment: matches Drizzle schema exactly
• Type safety: all types properly defined and exported
• Soft delete: queries filter out deletedAt when present
• User scoping: findByUserId for user-owned resources
• Timestamps: createdAt/updatedAt handled automatically
• Consistency: patterns match existing models

Example Specification

# Project

Manages project records for user workspaces.

## Properties
- db: Database

## Methods
- findAll(): Promise<Project[]>
- findById(id: string): Promise<Project | null>
- findByUserId(userId: string): Promise<Project[]>
- findByNameAndUser(name: string, userId: string): Promise<Project | null>
- create(data: NewProject): Promise<Project>
- update(id: string, data: Partial<NewProject>): Promise<Project | null>
- delete(id: string): Promise<boolean>

## Relationships
- Composes: Database