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

code

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

code

shared/models/
  model-name.ts

Class Specification Format

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

markdown

# 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

typescript

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:

Method	Description
`findAll()`	Returns all non-deleted records
`findById(id)`	Returns single record or null
`findByUserId(userId)`	Returns user-owned records
`create(data)`	Creates new record with UUID
`update(id, data)`	Updates record, returns updated or null
`delete(id)`	Soft deletes record

Key Patterns

1. Soft Delete Handling

typescript

// 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

typescript

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

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

3. User-Scoped Queries

typescript

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

typescript

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

markdown

# 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