44 lines
1.6 KiB
Markdown
44 lines
1.6 KiB
Markdown
---
|
|
name: db-schema
|
|
description: Design a database schema for an entity — tables, relationships, indexes, migration, and access patterns.
|
|
disable-model-invocation: true
|
|
argument-hint: "[entity-name]"
|
|
context: fork
|
|
agent: backend-architect
|
|
---
|
|
|
|
# Design Database Schema
|
|
|
|
Design the database schema for entity `$ARGUMENTS`.
|
|
|
|
## Steps
|
|
|
|
1. **Read project context first:**
|
|
- Check `docs/backend/architecture.md` for database conventions
|
|
- Check `RECOMMENDATIONS.md` for locked ORM (Prisma/Drizzle/other)
|
|
- Look at existing schemas/migrations in the project
|
|
|
|
2. **Gather requirements** — ask the user:
|
|
- What fields does this entity need?
|
|
- What are the relationships (belongs to, has many, many-to-many)?
|
|
- What are the primary query patterns (list, search, filter, aggregate)?
|
|
- Are there soft deletes, audit fields, or multi-tenancy requirements?
|
|
|
|
3. **Design the schema:**
|
|
- Table definition with column types, constraints, defaults
|
|
- Primary key strategy (UUID v7 / auto-increment / CUID)
|
|
- Foreign keys with ON DELETE behavior
|
|
- Indexes based on query patterns (composite where needed)
|
|
- Unique constraints where applicable
|
|
- Timestamps: `created_at`, `updated_at`
|
|
- Soft delete: `deleted_at` if required
|
|
|
|
4. **Provide deliverables:**
|
|
- Schema definition in the project's ORM format (Prisma/Drizzle/raw SQL)
|
|
- Migration file
|
|
- ER diagram (Mermaid) showing relationships
|
|
- Access pattern notes (which queries this schema optimizes for)
|
|
- Index justification (why each index exists)
|
|
|
|
5. **Verify via context7** — check current ORM syntax and best practices before writing.
|