Database Expert | Bootspring Docs

The Database Expert agent specializes in database design, optimization, and data management.

Expertise Areas#

Schema Design - Normalization, relationships, constraints
Query Optimization - Indexes, execution plans, performance
ORMs - Prisma, Drizzle, TypeORM, Sequelize
Migrations - Schema versioning, rollbacks, data migrations
SQL - Complex queries, CTEs, window functions
NoSQL - MongoDB, Redis, DynamoDB
Data Modeling - ERD design, domain modeling

Usage Examples#

Schema Design#

Use the database-expert agent to design a schema for an e-commerce platform with:
- Users and authentication
- Products with categories
- Orders and order items
- Reviews and ratings

Response includes:

Complete schema design
Relationship definitions
Index recommendations
Constraint definitions

Query Optimization#

Use the database-expert agent to optimize this slow query:
SELECT * FROM orders
JOIN order_items ON orders.id = order_items.order_id
WHERE orders.created_at > '2024-01-01'

Response includes:

Query analysis
Index suggestions
Optimized query
Execution plan explanation

Prisma Schema#

Use the database-expert agent to create a Prisma schema for a social media app.

Response includes:

Model definitions
Relations setup
Enum types
Index decorators

Best Practices Applied#

1. Schema Design#

Proper normalization (usually 3NF)
Appropriate denormalization for performance
Consistent naming conventions
Proper use of constraints

2. Performance#

Strategic index placement
Covering indexes
Query analysis
Connection pooling

3. Data Integrity#

Foreign key constraints
Check constraints
Unique constraints
Default values

4. Scalability#

Partitioning strategies
Sharding considerations
Read replicas
Caching layers

Common Patterns#

Prisma Schema Example#

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  tags      Tag[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
  @@index([published, createdAt])
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
}

Migration Strategy#

// 1. Create migration
npx prisma migrate dev --name add_user_roles

// 2. Data migration (if needed)
async function migrateData() {
  await prisma.$transaction(async (tx) => {
    // Update existing users
    await tx.user.updateMany({
      where: { role: null },
      data: { role: 'USER' },
    });
  });
}

// 3. Deploy migration
npx prisma migrate deploy

Index Strategy#

-- Primary lookup index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Covering index for common query
CREATE INDEX idx_orders_status_created
ON orders(status, created_at)
INCLUDE (total, user_id);

-- Partial index for active records
CREATE INDEX idx_users_active
ON users(email)
WHERE deleted_at IS NULL;

Database-Specific Guidance#

PostgreSQL#

JSON/JSONB usage
Full-text search
Extensions (PostGIS, pgvector)
Connection pooling (PgBouncer)

MySQL#

InnoDB vs MyISAM
Character sets
Replication setup
Query cache

MongoDB#

Document design
Aggregation pipelines
Indexing strategies
Sharding

Redis#

Data structures
Caching patterns
Pub/Sub
Persistence options

Sample Prompts#

Task	Prompt
Schema design	"Design a schema for a multi-tenant SaaS application"
Optimization	"Optimize queries for a reporting dashboard"
Migration	"Create a migration to add soft deletes to all tables"
Indexing	"Recommend indexes for these common query patterns"
Relationships	"Model a many-to-many relationship with metadata"

Configuration#

// bootspring.config.js
module.exports = {
  agents: {
    customInstructions: {
      'database-expert': `
        - Use Prisma as the ORM
        - Follow PostgreSQL best practices
        - Include index recommendations
        - Consider query performance
        - Use soft deletes where appropriate
      `,
    },
  },
  stack: {
    database: ['postgresql', 'prisma'],
  },
};

Backend Expert - API and server logic
Performance Expert - Query optimization
Software Architect - Data architecture