database-best-practices

# Database Best Practices Skill

## Purpose
Provides best practices and patterns for database operations in Shopify apps using Prisma ORM, focusing on data isolation, query optimization, and safe migrations.

## When This Skill Activates
- Working with Prisma schema or queries
- Creating database migrations
- Optimizing database performance
- Implementing multi-tenant data isolation
- Handling transactions

## Critical: Multi-Tenant Data Isolation

**ALWAYS filter by shopId** - This prevents data leaks between shops.

```typescript
// ✅ CORRECT - Always include shopId
const products = await db.product.findMany({
  where: {
    shopId: shop.id,
    status: "active",
  },
});

// ❌ WRONG - Missing shopId (data leak!)
const products = await db.product.findMany({
  where: { status: "active" },
});
```

## Core Patterns

### 1. Safe Query Pattern

```typescript
// Always filter by shopId for shop-specific data
async function getShopProducts(shopId: string) {
  return db.product.findMany({
    where: { shopId },
    select: {
      id: true,
      title: true,
      vendor: true,
      // Only select needed fields
    },
    take: 50,
    orderBy: { createdAt: "desc" },
  });
}
```

### 2. Pagination Pattern

```typescript
async function getPaginatedProducts(shopId: string, page: number = 1) {
  const pageSize = 50;
  const skip = (page - 1) * pageSize;

  const [products, totalCount] = await Promise.all([
    db.product.findMany({
      where: { shopId },
      skip,
      take: pageSize,
      orderBy: { createdAt: "desc" },
    }),
    db.product.count({
      where: { shopId },
    }),
  ]);

  return {
    products,
    totalCount,
    totalPages: Math.ceil(totalCount / pageSize),
    currentPage: page,
  };
}
```

### 3. Transaction Pattern

```typescript
// Use transactions for operations that must succeed/fail together
await db.$transaction(async (tx) => {
  // Update product
  await tx.product.update({
    where: { id: productId },
    data: { status: "synced" },