Write database models following the Epic architecture patterns. Use when creating Active Record models for database tables with CRUD operations. Triggers on "create a model", "add a model", or "write a model for".
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.
Backend: Actions call models
|
v
Infrastructure: Models (database operations)
|
v
Drizzle ORM -> Database
Models:
shared/models/
model-name.ts
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>
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;
},
};
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 |
// 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;
}
const id = crypto.randomUUID();
const now = new Date();
await db.insert(table).values({
...data,
id,
createdAt: now,
updatedAt: now,
});
async findByUserId(userId: string): Promise<ModelName[]> {
return await db
.select()
.from(table)
.where(and(
eq(table.userId, userId),
isNull(table.deletedAt)
));
}
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)
));
}
deletedAtcreatedAt and updatedAt automaticallyfindByUserIdBefore finalizing a model:
# 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