Custom Schemas Tutorial

Transform your database models into custom response shapes (DTOs) using DataBridge custom schemas - hide fields, add computed properties, and control API responses

Custom Schemas Tutorial

Learn how to transform your database models into custom response shapes (DTOs) using DataBridge custom schemas.

Introduction
Schema Transformation Concepts
Field Types
Async Computed Fields
Performance Considerations
Real-World Examples
Best Practices
Troubleshooting

Introduction

Custom schemas allow you to transform your Prisma models into custom response shapes without modifying your database schema. They enable you to:

Hide sensitive fields (passwords, tokens, internal IDs)
Add computed fields (full names, formatted dates, calculations)
Rename fields for better API design (snake_case to camelCase)
Transform nested relations with custom shapes
Add business logic to response data

Schema Transformation Concepts

What are Schemas?

A schema defines how to transform a Prisma model into a custom response shape:

// Database Model (Prisma)
model User {
  id           Int      @id
  email        String
  password     String   // Sensitive!
  firstName    String
  lastName     String
  createdAt    DateTime
}

// Custom Schema (Your API)
UserPublic: {
  from: 'User',
  fields: {
    id: true,
    email: true,
    password: false,        // ❌ Hidden
    firstName: true,
    lastName: true,
    fullName: {             // ✨ Computed
      compute: (user) => `${user.firstName} ${user.lastName}`,
      type: 'string',
    },
    memberSince: {          // ✨ Renamed
      from: 'createdAt',
      type: 'date',
    },
  },
}

// API Response
{
  id: 1,
  email: "user@example.com",
  firstName: "John",
  lastName: "Doe",
  fullName: "John Doe",       // ✅ Computed
  memberSince: "2024-01-15"   // ✅ Renamed from createdAt
  // password: undefined      // ✅ Hidden
}

Schema Definition Syntax

import { defineSchemas } from './databridge.types';

export default defineSchemas({
  SchemaName: {
    // Required: Source Prisma model
    from: 'ModelName',
    
    // Required: Field definitions
    fields: {
      // Include field
      fieldName: true,
      
      // Exclude field
      sensitiveField: false,
      
      // Computed field
      computedField: {
        compute: (data, prisma?) => { /* ... */ },
        type: 'string' | 'number' | 'boolean' | 'date' | 'object',
      },
      
      // Renamed field
      newName: {
        from: 'oldFieldName',
        type: 'string',
        transform: (value) => { /* optional transformation */ },
      },
      
      // Relation field
      relationName: {
        include: true,
        shape: 'RelatedSchemaName',
        select: { /* Prisma select */ },
        where: { /* Prisma where */ },
      },
    },
    
    // Optional: Description for docs
    description: 'Public user profile',
  },
});

Field Types

1. Include/Exclude Fields

The simplest form - just include or exclude fields:

UserBasic: {
  from: 'User',
  fields: {
    id: true,           // ✅ Include
    email: true,
    firstName: true,
    lastName: true,
    password: false,    // ❌ Exclude
    apiKey: false,      // ❌ Exclude
  },
}

2. Computed Fields

Add new fields calculated from existing data:

UserProfile: {
  from: 'User',
  fields: {
    id: true,
    firstName: true,
    lastName: true,
    
    // Simple string concatenation
    fullName: {
      compute: (user) => `${user.firstName} ${user.lastName}`,
      type: 'string',
    },
    
    // Boolean logic
    isActive: {
      compute: (user) => user.lastLoginAt > new Date(Date.now() - 30 * 24 * 60 * 60 * 1000),
      type: 'boolean',
    },
    
    // Number calculation
    accountAge: {
      compute: (user) => {
        const days = Math.floor((Date.now() - user.createdAt.getTime()) / (24 * 60 * 60 * 1000));
        return days;
      },
      type: 'number',
    },
  },
}

3. Renamed Fields

Rename fields for better API design:

UserDto: {
  from: 'User',
  fields: {
    id: true,
    
    // Simple rename
    emailAddress: {
      from: 'email',
      type: 'string',
    },
    
    // Rename with transformation
    registrationDate: {
      from: 'createdAt',
      transform: (date) => date.toISOString().split('T')[0],
      type: 'string',
    },
    
    // Case conversion
    first_name: {
      from: 'firstName',
      type: 'string',
    },
    last_name: {
      from: 'lastName',
      type: 'string',
    },
  },
}

4. Relation Fields

Transform nested relations with custom shapes:

OrderDetailed: {
  from: 'Order',
  fields: {
    id: true,
    total: true,
    status: true,
    
    // Use another schema for nested relation
    customer: {
      include: true,
      shape: 'UserPublic',
    },
    
    // Array of nested objects
    items: {
      include: true,
      shape: 'OrderItemSummary',
    },
    
    // Relation with filtering
    recentComments: {
      include: true,
      shape: 'CommentPublic',
      where: {
        createdAt: {
          gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000),
        },
      },
    },
    
    // Relation with selection
    deliveryAddress: {
      include: true,
      select: {
        street: true,
        city: true,
        zipCode: true,
        // Don't include internal fields
      },
    },
  },
}

Async Computed Fields

Computed fields can be async and access Prisma client for additional queries:

ProductEnhanced: {
  from: 'Product',
  fields: {
    id: true,
    name: true,
    price: true,
    
    // Async computed field with database query
    averageRating: {
      compute: async (product, prisma) => {
        const result = await prisma.review.aggregate({
          where: { productId: product.id },
          _avg: { rating: true },
        });
        return result._avg.rating || 0;
      },
      type: 'number',
    },
    
    // Count related records
    reviewCount: {
      compute: async (product, prisma) => {
        return prisma.review.count({
          where: { productId: product.id },
        });
      },
      type: 'number',
    },
    
    // Complex aggregation
    salesStats: {
      compute: async (product, prisma) => {
        const [totalSold, revenue] = await Promise.all([
          prisma.orderItem.aggregate({
            where: { productId: product.id },
            _sum: { quantity: true },
          }),
          prisma.orderItem.aggregate({
            where: { productId: product.id },
            _sum: { price: true },
          }),
        ]);
        
        return {
          totalSold: totalSold._sum.quantity || 0,
          totalRevenue: revenue._sum.price || 0,
        };
      },
      type: 'object',
    },
  },
}

Performance Considerations

1. N+1 Query Problem

❌ Bad: Each computed field makes a separate query

ProductWithStats: {
  from: 'Product',
  fields: {
    id: true,
    name: true,
    
    // ❌ Separate query for each product
    reviewCount: {
      compute: async (product, prisma) => {
        return prisma.review.count({ where: { productId: product.id } });
      },
      type: 'number',
    },
    
    // ❌ Another separate query
    averageRating: {
      compute: async (product, prisma) => {
        const result = await prisma.review.aggregate({
          where: { productId: product.id },
          _avg: { rating: true },
        });
        return result._avg.rating || 0;
      },
      type: 'number',
    },
  },
}

✅ Good: Batch queries or include data upfront

// Option 1: Include data in initial query
const products = await prisma.product.findMany({
  include: {
    reviews: {
      select: { rating: true },
    },
  },
});

// Option 2: Compute from included data
ProductWithStats: {
  from: 'Product',
  fields: {
    id: true,
    name: true,
    
    // ✅ Compute from included data
    reviewCount: {
      compute: (product) => product.reviews?.length || 0,
      type: 'number',
    },
    
    averageRating: {
      compute: (product) => {
        if (!product.reviews || product.reviews.length === 0) return 0;
        const sum = product.reviews.reduce((acc, r) => acc + r.rating, 0);
        return sum / product.reviews.length;
      },
      type: 'number',
    },
  },
}

2. Caching Computed Values

For expensive computations, consider caching:

// Create a cache map outside the schema
const ratingCache = new Map<number, number>();

ProductCached: {
  from: 'Product',
  fields: {
    id: true,
    name: true,
    
    averageRating: {
      compute: async (product, prisma) => {
        // Check cache first
        if (ratingCache.has(product.id)) {
          return ratingCache.get(product.id)!;
        }
        
        // Compute and cache
        const result = await prisma.review.aggregate({
          where: { productId: product.id },
          _avg: { rating: true },
        });
        const rating = result._avg.rating || 0;
        ratingCache.set(product.id, rating);
        
        return rating;
      },
      type: 'number',
    },
  },
}

3. Avoid Heavy Computations

Keep computed fields fast:

// ✅ Good - simple computation
fullName: {
  compute: (user) => `${user.firstName} ${user.lastName}`,
  type: 'string',
}

// ⚠️  Okay - single query
orderCount: {
  compute: async (user, prisma) => {
    return prisma.order.count({ where: { userId: user.id } });
  },
  type: 'number',
}

// ❌ Bad - complex aggregation for each record
lifetimeStats: {
  compute: async (user, prisma) => {
    // This runs for EVERY user!
    const orders = await prisma.order.findMany({
      where: { userId: user.id },
      include: { items: true },
    });
    
    // Complex computation
    return orders.reduce((acc, order) => {
      return {
        totalOrders: acc.totalOrders + 1,
        totalSpent: acc.totalSpent + order.total,
        avgOrderValue: (acc.totalSpent + order.total) / (acc.totalOrders + 1),
        itemsPurchased: acc.itemsPurchased + order.items.length,
      };
    }, { totalOrders: 0, totalSpent: 0, avgOrderValue: 0, itemsPurchased: 0 });
  },
  type: 'object',
}

Real-World Examples

1. User Profile with Privacy

UserPublic: {
  from: 'User',
  description: 'Public user profile - safe for public display',
  fields: {
    // Basic info
    id: true,
    username: true,
    
    // Hide sensitive fields
    email: false,
    password: false,
    passwordResetToken: false,
    emailVerificationToken: false,
    apiKey: false,
    
    // Computed fields
    displayName: {
      compute: (user) => user.username || `User${user.id}`,
      type: 'string',
    },
    
    profileUrl: {
      compute: (user) => `/users/${user.username || user.id}`,
      type: 'string',
    },
    
    isVerified: {
      compute: (user) => user.emailVerifiedAt !== null,
      type: 'boolean',
    },
    
    memberSince: {
      from: 'createdAt',
      transform: (date) => date.toISOString().split('T')[0],
      type: 'string',
    },
  },
}

2. Product with Pricing & Inventory

ProductListing: {
  from: 'Product',
  description: 'Product listing for catalog',
  fields: {
    id: true,
    name: true,
    description: true,
    
    // Price formatting
    price: {
      from: 'priceInCents',
      transform: (cents) => cents / 100,
      type: 'number',
    },
    
    priceFormatted: {
      compute: (product) => `$${(product.priceInCents / 100).toFixed(2)}`,
      type: 'string',
    },
    
    // Inventory status
    inStock: {
      compute: (product) => product.stock > 0,
      type: 'boolean',
    },
    
    stockStatus: {
      compute: (product) => {
        if (product.stock === 0) return 'Out of Stock';
        if (product.stock < 5) return 'Low Stock';
        return 'In Stock';
      },
      type: 'string',
    },
    
    // Hide internal fields
    costPrice: false,
    supplierId: false,
    
    // Image handling
    imageUrl: {
      compute: (product) => product.imagePath
        ? `https://cdn.example.com/${product.imagePath}`
        : '/images/placeholder.jpg',
      type: 'string',
    },
    
    // Async computed fields
    averageRating: {
      compute: async (product, prisma) => {
        const result = await prisma.review.aggregate({
          where: { productId: product.id },
          _avg: { rating: true },
        });
        return result._avg.rating || 0;
      },
      type: 'number',
    },
    
    reviewCount: {
      compute: async (product, prisma) => {
        return prisma.review.count({
          where: { productId: product.id },
        });
      },
      type: 'number',
    },
  },
}

3. Order Summary

OrderSummary: {
  from: 'Order',
  description: 'Order summary for user order list',
  fields: {
    id: true,
    orderNumber: true,
    
    // Status badge
    status: true,
    statusDisplay: {
      compute: (order) => {
        const statusMap = {
          pending: 'Pending Payment',
          processing: 'Processing',
          shipped: 'Shipped',
          delivered: 'Delivered',
          cancelled: 'Cancelled',
        };
        return statusMap[order.status] || order.status;
      },
      type: 'string',
    },
    
    // Dates
    orderDate: {
      from: 'createdAt',
      transform: (date) => date.toLocaleDateString('en-US', {
        year: 'numeric',
        month: 'long',
        day: 'numeric',
      }),
      type: 'string',
    },
    
    estimatedDelivery: {
      compute: (order) => {
        if (order.status === 'delivered') return 'Delivered';
        if (order.status === 'cancelled') return 'N/A';
        
        const deliveryDate = new Date(order.createdAt);
        deliveryDate.setDate(deliveryDate.getDate() + 5);
        return deliveryDate.toLocaleDateString('en-US', {
          month: 'short',
          day: 'numeric',
        });
      },
      type: 'string',
    },
    
    // Pricing
    subtotal: true,
    tax: true,
    shipping: true,
    total: true,
    
    totalFormatted: {
      compute: (order) => `$${order.total.toFixed(2)}`,
      type: 'string',
    },
    
    // Item count
    itemCount: {
      compute: async (order, prisma) => {
        return prisma.orderItem.count({
          where: { orderId: order.id },
        });
      },
      type: 'number',
    },
    
    // Hide internal fields
    paymentIntentId: false,
    stripeCustomerId: false,
  },
}

4. Blog Post with Author

PostPublic: {
  from: 'Post',
  description: 'Blog post with author information',
  fields: {
    id: true,
    title: true,
    slug: true,
    
    // Content
    excerpt: {
      compute: (post) => {
        if (post.excerpt) return post.excerpt;
        // Auto-generate excerpt from content
        return post.content.substring(0, 150) + '...';
      },
      type: 'string',
    },
    
    content: true,
    
    // Read time estimation
    readingTime: {
      compute: (post) => {
        const wordsPerMinute = 200;
        const wordCount = post.content.split(/\s+/).length;
        const minutes = Math.ceil(wordCount / wordsPerMinute);
        return `${minutes} min read`;
      },
      type: 'string',
    },
    
    // Dates
    publishedAt: true,
    
    publishedDate: {
      from: 'publishedAt',
      transform: (date) => date.toLocaleDateString('en-US', {
        year: 'numeric',
        month: 'long',
        day: 'numeric',
      }),
      type: 'string',
    },
    
    // Status
    isDraft: {
      compute: (post) => post.publishedAt === null,
      type: 'boolean',
    },
    
    // Author with custom schema
    author: {
      include: true,
      shape: 'UserPublic',
    },
    
    // Hide internal fields
    authorId: false,
    
    // Featured image
    featuredImage: {
      compute: (post) => post.featuredImagePath
        ? {
            url: `https://cdn.example.com/${post.featuredImagePath}`,
            alt: post.featuredImageAlt || post.title,
          }
        : null,
      type: 'object',
    },
    
    // Engagement metrics
    commentCount: {
      compute: async (post, prisma) => {
        return prisma.comment.count({
          where: {
            postId: post.id,
            approved: true,
          },
        });
      },
      type: 'number',
    },
    
    likeCount: {
      compute: async (post, prisma) => {
        return prisma.like.count({
          where: { postId: post.id },
        });
      },
      type: 'number',
    },
  },
}

5. Shopping Cart

CartSummary: {
  from: 'Cart',
  description: 'Shopping cart with items and totals',
  fields: {
    id: true,
    
    // Items with product details
    items: {
      include: true,
      shape: 'CartItemDetailed',
    },
    
    // Calculations
    itemCount: {
      compute: (cart) => cart.items?.length || 0,
      type: 'number',
    },
    
    subtotal: {
      compute: (cart) => {
        return cart.items?.reduce((sum, item) => {
          return sum + (item.product.price * item.quantity);
        }, 0) || 0;
      },
      type: 'number',
    },
    
    tax: {
      compute: (cart) => {
        const subtotal = cart.items?.reduce((sum, item) => {
          return sum + (item.product.price * item.quantity);
        }, 0) || 0;
        return subtotal * 0.08; // 8% tax
      },
      type: 'number',
    },
    
    shipping: {
      compute: (cart) => {
        const subtotal = cart.items?.reduce((sum, item) => {
          return sum + (item.product.price * item.quantity);
        }, 0) || 0;
        return subtotal > 50 ? 0 : 5.99; // Free shipping over $50
      },
      type: 'number',
    },
    
    total: {
      compute: (cart) => {
        const subtotal = cart.items?.reduce((sum, item) => {
          return sum + (item.product.price * item.quantity);
        }, 0) || 0;
        const tax = subtotal * 0.08;
        const shipping = subtotal > 50 ? 0 : 5.99;
        return subtotal + tax + shipping;
      },
      type: 'number',
    },
    
    // Promotions
    hasPromotion: {
      compute: (cart) => cart.promoCode !== null,
      type: 'boolean',
    },
    
    discount: {
      compute: async (cart, prisma) => {
        if (!cart.promoCode) return 0;
        
        const promo = await prisma.promotion.findUnique({
          where: { code: cart.promoCode },
        });
        
        if (!promo || !promo.isActive) return 0;
        
        const subtotal = cart.items?.reduce((sum, item) => {
          return sum + (item.product.price * item.quantity);
        }, 0) || 0;
        
        if (promo.type === 'percentage') {
          return subtotal * (promo.value / 100);
        } else {
          return Math.min(promo.value, subtotal);
        }
      },
      type: 'number',
    },
    
    // Hide internal fields
    userId: false,
    sessionId: false,
  },
}

// Supporting schema
CartItemDetailed: {
  from: 'CartItem',
  fields: {
    id: true,
    quantity: true,
    
    // Product details
    product: {
      include: true,
      shape: 'ProductListing',
    },
    
    // Calculations
    lineTotal: {
      compute: (item) => item.product.price * item.quantity,
      type: 'number',
    },
    
    lineTotalFormatted: {
      compute: (item) => `$${(item.product.price * item.quantity).toFixed(2)}`,
      type: 'string',
    },
    
    // Availability
    isAvailable: {
      compute: (item) => {
        return item.product.stock >= item.quantity;
      },
      type: 'boolean',
    },
    
    availabilityMessage: {
      compute: (item) => {
        if (item.product.stock === 0) {
          return 'Out of stock';
        }
        if (item.product.stock < item.quantity) {
          return `Only ${item.product.stock} available`;
        }
        return 'In stock';
      },
      type: 'string',
    },
  },
}

6. Analytics Dashboard

UserAnalytics: {
  from: 'User',
  description: 'User analytics for admin dashboard',
  fields: {
    id: true,
    email: true,
    
    displayName: {
      compute: (user) => `${user.firstName} ${user.lastName}`,
      type: 'string',
    },
    
    // Account info
    joinDate: {
      from: 'createdAt',
      transform: (date) => date.toISOString().split('T')[0],
      type: 'string',
    },
    
    accountAge: {
      compute: (user) => {
        const days = Math.floor((Date.now() - user.createdAt.getTime()) / (1000 * 60 * 60 * 24));
        if (days < 30) return `${days} days`;
        if (days < 365) return `${Math.floor(days / 30)} months`;
        return `${Math.floor(days / 365)} years`;
      },
      type: 'string',
    },
    
    // Activity
    lastActive: {
      from: 'lastLoginAt',
      transform: (date) => {
        if (!date) return 'Never';
        const hours = Math.floor((Date.now() - date.getTime()) / (1000 * 60 * 60));
        if (hours < 1) return 'Just now';
        if (hours < 24) return `${hours} hours ago`;
        const days = Math.floor(hours / 24);
        if (days === 1) return 'Yesterday';
        if (days < 7) return `${days} days ago`;
        return date.toLocaleDateString();
      },
      type: 'string',
    },
    
    // Order metrics
    orderStats: {
      compute: async (user, prisma) => {
        const [orderCount, orderTotal, avgOrderValue] = await Promise.all([
          prisma.order.count({ where: { userId: user.id } }),
          prisma.order.aggregate({
            where: { userId: user.id },
            _sum: { total: true },
          }),
          prisma.order.aggregate({
            where: { userId: user.id },
            _avg: { total: true },
          }),
        ]);
        
        return {
          totalOrders: orderCount,
          totalSpent: orderTotal._sum.total || 0,
          averageOrderValue: avgOrderValue._avg.total || 0,
        };
      },
      type: 'object',
    },
    
    // Customer segment
    segment: {
      compute: async (user, prisma) => {
        const orderTotal = await prisma.order.aggregate({
          where: { userId: user.id },
          _sum: { total: true },
        });
        
        const totalSpent = orderTotal._sum.total || 0;
        
        if (totalSpent === 0) return 'New';
        if (totalSpent < 100) return 'Bronze';
        if (totalSpent < 500) return 'Silver';
        if (totalSpent < 1000) return 'Gold';
        return 'Platinum';
      },
      type: 'string',
    },
    
    // Engagement
    engagementScore: {
      compute: async (user, prisma) => {
        const [orders, reviews, comments] = await Promise.all([
          prisma.order.count({ where: { userId: user.id } }),
          prisma.review.count({ where: { userId: user.id } }),
          prisma.comment.count({ where: { userId: user.id } }),
        ]);
        
        // Simple engagement score
        return orders * 10 + reviews * 5 + comments * 2;
      },
      type: 'number',
    },
  },
}

7. API Response with Metadata

SearchResult: {
  from: 'Product',
  description: 'Search result with relevance and metadata',
  fields: {
    id: true,
    name: true,
    description: true,
    price: true,
    
    // Highlight search terms
    nameHighlighted: {
      compute: (product, _, context) => {
        if (!context?.searchTerm) return product.name;
        const regex = new RegExp(`(${context.searchTerm})`, 'gi');
        return product.name.replace(regex, '<mark>$1</mark>');
      },
      type: 'string',
    },
    
    // Relevance score (if using full-text search)
    relevance: {
      compute: (product, _, context) => {
        // Calculate relevance score
        if (!context?.searchTerm) return 1;
        
        const term = context.searchTerm.toLowerCase();
        const name = product.name.toLowerCase();
        const description = product.description?.toLowerCase() || '';
        
        let score = 0;
        if (name === term) score += 100;
        else if (name.startsWith(term)) score += 50;
        else if (name.includes(term)) score += 25;
        
        if (description.includes(term)) score += 10;
        
        return score;
      },
      type: 'number',
    },
    
    // Snippet
    snippet: {
      compute: (product, _, context) => {
        if (!context?.searchTerm || !product.description) {
          return product.description?.substring(0, 100) + '...';
        }
        
        const term = context.searchTerm.toLowerCase();
        const description = product.description;
        const index = description.toLowerCase().indexOf(term);
        
        if (index === -1) {
          return description.substring(0, 100) + '...';
        }
        
        const start = Math.max(0, index - 50);
        const end = Math.min(description.length, index + term.length + 50);
        const snippet = description.substring(start, end);
        
        return (start > 0 ? '...' : '') + snippet + (end < description.length ? '...' : '');
      },
      type: 'string',
    },
    
    // Category breadcrumb
    categoryPath: {
      compute: async (product, prisma) => {
        if (!product.categoryId) return [];
        
        const category = await prisma.category.findUnique({
          where: { id: product.categoryId },
          include: { parent: { include: { parent: true } } },
        });
        
        const path: string[] = [];
        let current = category;
        while (current) {
          path.unshift(current.name);
          current = current.parent;
        }
        return path;
      },
      type: 'object',
    },
  },
}

8. Audit Trail

AuditLogEntry: {
  from: 'AuditLog',
  description: 'Audit log entry with formatted details',
  fields: {
    id: true,
    action: true,
    entityType: true,
    entityId: true,
    
    // Formatted action
    actionDisplay: {
      compute: (log) => {
        const actionMap = {
          create: 'Created',
          update: 'Updated',
          delete: 'Deleted',
          view: 'Viewed',
        };
        return actionMap[log.action] || log.action;
      },
      type: 'string',
    },
    
    // Entity display name
    entityName: {
      compute: async (log, prisma) => {
        switch (log.entityType) {
          case 'User':
            const user = await prisma.user.findUnique({
              where: { id: log.entityId },
              select: { email: true },
            });
            return user?.email || `User #${log.entityId}`;
            
          case 'Product':
            const product = await prisma.product.findUnique({
              where: { id: log.entityId },
              select: { name: true },
            });
            return product?.name || `Product #${log.entityId}`;
            
          case 'Order':
            return `Order #${log.entityId}`;
            
          default:
            return `${log.entityType} #${log.entityId}`;
        }
      },
      type: 'string',
    },
    
    // Actor information
    actor: {
      include: true,
      shape: 'UserPublic',
    },
    
    // Timestamp formatting
    timestamp: {
      from: 'createdAt',
      transform: (date) => date.toLocaleString('en-US', {
        year: 'numeric',
        month: 'short',
        day: 'numeric',
        hour: '2-digit',
        minute: '2-digit',
      }),
      type: 'string',
    },
    
    relativeTime: {
      compute: (log) => {
        const seconds = Math.floor((Date.now() - log.createdAt.getTime()) / 1000);
        
        if (seconds < 60) return 'Just now';
        if (seconds < 3600) return `${Math.floor(seconds / 60)} minutes ago`;
        if (seconds < 86400) return `${Math.floor(seconds / 3600)} hours ago`;
        if (seconds < 604800) return `${Math.floor(seconds / 86400)} days ago`;
        return log.createdAt.toLocaleDateString();
      },
      type: 'string',
    },
    
    // Changes summary
    changesSummary: {
      compute: (log) => {
        if (!log.changes) return 'No changes recorded';
        
        const changes = JSON.parse(log.changes);
        const fields = Object.keys(changes);
        
        if (fields.length === 1) {
          return `Changed ${fields[0]}`;
        }
        if (fields.length === 2) {
          return `Changed ${fields[0]} and ${fields[1]}`;
        }
        return `Changed ${fields.length} fields`;
      },
      type: 'string',
    },
    
    // IP address (masked for privacy)
    ipAddressMasked: {
      from: 'ipAddress',
      transform: (ip) => {
        if (!ip) return 'Unknown';
        const parts = ip.split('.');
        if (parts.length === 4) {
          return `${parts[0]}.${parts[1]}.xxx.xxx`;
        }
        return ip;
      },
      type: 'string',
    },
  },
}

9. Notification System

NotificationItem: {
  from: 'Notification',
  description: 'User notification with rich formatting',
  fields: {
    id: true,
    type: true,
    
    // Icon based on type
    icon: {
      compute: (notification) => {
        const iconMap = {
          order_shipped: '📦',
          order_delivered: '✅',
          payment_success: '💳',
          payment_failed: '❌',
          new_message: '💬',
          promotion: '🎉',
          system: 'ℹ️',
        };
        return iconMap[notification.type] || '🔔';
      },
      type: 'string',
    },
    
    // Formatted title
    title: {
      compute: (notification) => {
        const titleMap = {
          order_shipped: 'Your order has shipped!',
          order_delivered: 'Order delivered',
          payment_success: 'Payment successful',
          payment_failed: 'Payment failed',
          new_message: 'New message',
          promotion: 'Special offer',
          system: 'System notification',
        };
        return titleMap[notification.type] || 'Notification';
      },
      type: 'string',
    },
    
    // Message with dynamic content
    message: {
      compute: async (notification, prisma) => {
        const data = notification.data ? JSON.parse(notification.data) : {};
        
        switch (notification.type) {
          case 'order_shipped':
            return `Order #${data.orderId} is on its way! Track your package.`;
            
          case 'order_delivered':
            return `Order #${data.orderId} has been delivered.`;
            
          case 'payment_success':
            return `Payment of $${data.amount} was processed successfully.`;
            
          case 'payment_failed':
            return `Payment of $${data.amount} failed. Please update your payment method.`;
            
          case 'new_message':
            const sender = await prisma.user.findUnique({
              where: { id: data.senderId },
              select: { firstName: true },
            });
            return `${sender?.firstName || 'Someone'} sent you a message.`;
            
          case 'promotion':
            return data.message || 'Check out our latest deals!';
            
          default:
            return data.message || '';
        }
      },
      type: 'string',
    },
    
    // Action button
    actionUrl: {
      compute: (notification) => {
        const data = notification.data ? JSON.parse(notification.data) : {};
        
        switch (notification.type) {
          case 'order_shipped':
          case 'order_delivered':
            return `/orders/${data.orderId}`;
            
          case 'payment_failed':
            return '/settings/payment';
            
          case 'new_message':
            return `/messages/${data.conversationId}`;
            
          case 'promotion':
            return data.url || '/promotions';
            
          default:
            return null;
        }
      },
      type: 'string',
    },
    
    actionLabel: {
      compute: (notification) => {
        switch (notification.type) {
          case 'order_shipped':
            return 'Track Order';
          case 'order_delivered':
            return 'View Order';
          case 'payment_failed':
            return 'Update Payment';
          case 'new_message':
            return 'Read Message';
          case 'promotion':
            return 'View Offer';
          default:
            return null;
        }
      },
      type: 'string',
    },
    
    // Status
    isRead: {
      compute: (notification) => notification.readAt !== null,
      type: 'boolean',
    },
    
    // Timestamp
    timeAgo: {
      compute: (notification) => {
        const seconds = Math.floor((Date.now() - notification.createdAt.getTime()) / 1000);
        
        if (seconds < 60) return 'Just now';
        if (seconds < 3600) return `${Math.floor(seconds / 60)}m ago`;
        if (seconds < 86400) return `${Math.floor(seconds / 3600)}h ago`;
        if (seconds < 604800) return `${Math.floor(seconds / 86400)}d ago`;
        return notification.createdAt.toLocaleDateString();
      },
      type: 'string',
    },
  },
}

10. Multi-Currency Support

ProductInternational: {
  from: 'Product',
  description: 'Product with multi-currency pricing',
  fields: {
    id: true,
    name: true,
    description: true,
    
    // Base price (USD)
    priceUSD: {
      from: 'price',
      type: 'number',
    },
    
    // Currency conversion
    pricing: {
      compute: (product, _, context) => {
        const basePrice = product.price;
        const currency = context?.currency || 'USD';
        
        // Exchange rates (in real app, fetch from API)
        const rates = {
          USD: 1,
          EUR: 0.85,
          GBP: 0.73,
          CAD: 1.25,
          AUD: 1.35,
          JPY: 110,
        };
        
        const symbols = {
          USD: '$',
          EUR: '€',
          GBP: '£',
          CAD: 'C$',
          AUD: 'A$',
          JPY: '¥',
        };
        
        const rate = rates[currency] || 1;
        const convertedPrice = basePrice * rate;
        const symbol = symbols[currency] || currency;
        
        return {
          amount: convertedPrice,
          currency,
          formatted: `${symbol}${convertedPrice.toFixed(currency === 'JPY' ? 0 : 2)}`,
        };
      },
      type: 'object',
    },
    
    // VAT/Tax based on region
    taxInfo: {
      compute: (product, _, context) => {
        const region = context?.region || 'US';
        
        const taxRates = {
          US: { rate: 0.08, name: 'Sales Tax' },
          CA: { rate: 0.13, name: 'HST' },
          UK: { rate: 0.20, name: 'VAT' },
          EU: { rate: 0.19, name: 'VAT' },
          AU: { rate: 0.10, name: 'GST' },
        };
        
        const tax = taxRates[region] || { rate: 0, name: 'Tax' };
        const basePrice = product.price;
        const taxAmount = basePrice * tax.rate;
        
        return {
          rate: tax.rate,
          name: tax.name,
          amount: taxAmount,
          priceWithTax: basePrice + taxAmount,
        };
      },
      type: 'object',
    },
    
    // Localized availability
    availability: {
      compute: (product, _, context) => {
        const region = context?.region || 'US';
        
        // Check regional availability
        const regionalStock = product.regionalStock
          ? JSON.parse(product.regionalStock)
          : {};
        
        const stock = regionalStock[region] || product.stock;
        
        if (stock === 0) return 'Out of stock';
        if (stock < 5) return `Only ${stock} left`;
        return 'In stock';
      },
      type: 'string',
    },
  },
}

Best Practices

1. Keep Schemas Focused

Each schema should have a single purpose:

// ✅ Good - focused schemas
UserPublic: {       // For public display
  from: 'User',
  fields: {
    id: true,
    username: true,
    avatar: true,
  },
}

UserPrivate: {      // For user's own profile
  from: 'User',
  fields: {
    id: true,
    username: true,
    email: true,
    phone: true,
  },
}

UserAdmin: {        // For admin dashboard
  from: 'User',
  fields: {
    id: true,
    email: true,
    role: true,
    createdAt: true,
    lastLoginAt: true,
  },
}

// ❌ Bad - one schema for everything
UserAllPurpose: {
  from: 'User',
  fields: {
    // Too many fields, hard to maintain
  },
}

2. Hide Sensitive Data

Always exclude sensitive fields:

// ✅ Good - explicit exclusions
UserSafe: {
  from: 'User',
  fields: {
    id: true,
    email: true,
    // Explicitly hide sensitive fields
    password: false,
    passwordResetToken: false,
    apiKey: false,
    stripeCustomerId: false,
  },
}

// ❌ Bad - forgot to exclude
UserUnsafe: {
  from: 'User',
  fields: {
    id: true,
    email: true,
    // Oops, password is included by default!
  },
}

3. Document Your Schemas

Add descriptions for maintainability:

// ✅ Good - well documented
UserPublic: {
  from: 'User',
  description: 'Public user profile - safe for public display. Excludes all sensitive data.',
  fields: {
    id: true,
    username: true,
    avatar: true,
    
    // Computed fields with comments
    profileUrl: {
      // Generate URL for user profile page
      compute: (user) => `/users/${user.username}`,
      type: 'string',
    },
  },
}

4. Reuse Schemas

Compose schemas from other schemas:

// ✅ Good - reusable schemas
UserPublic: {
  from: 'User',
  fields: {
    id: true,
    username: true,
    avatar: true,
  },
}

PostWithAuthor: {
  from: 'Post',
  fields: {
    id: true,
    title: true,
    content: true,
    
    // Reuse UserPublic schema
    author: {
      include: true,
      shape: 'UserPublic',  // ✅ Reusing existing schema
    },
  },
}

CommentWithAuthor: {
  from: 'Comment',
  fields: {
    id: true,
    text: true,
    
    // Reuse the same UserPublic schema
    author: {
      include: true,
      shape: 'UserPublic',  // ✅ Consistent author format
    },
  },
}

5. Performance First

Avoid expensive computations:

// ✅ Good - compute from included data
const posts = await prisma.post.findMany({
  include: { comments: true },  // Include once
});

PostWithStats: {
  from: 'Post',
  fields: {
    id: true,
    title: true,
    
    // ✅ Compute from included data
    commentCount: {
      compute: (post) => post.comments?.length || 0,
      type: 'number',
    },
  },
}

// ❌ Bad - separate query for each post
PostWithStats: {
  from: 'Post',
  fields: {
    id: true,
    title: true,
    
    // ❌ N+1 query problem!
    commentCount: {
      compute: async (post, prisma) => {
        return prisma.comment.count({
          where: { postId: post.id },
        });
      },
      type: 'number',
    },
  },
}

Troubleshooting

Error: Schema not found

⚠️  Warning: referenced shape "UserPublic" not found

Solution: Make sure the schema exists in the same file:

export default defineSchemas({
  // ✅ Define UserPublic first
  UserPublic: {
    from: 'User',
    fields: { /* ... */ },
  },
  
  // ✅ Now you can reference it
  PostWithAuthor: {
    from: 'Post',
    fields: {
      author: {
        include: true,
        shape: 'UserPublic',  // ✅ References defined schema
      },
    },
  },
});

Error: Missing base model

❌ Missing Base Model
Schema 'MySchema' is missing required field: from (source model)

Solution: Add the from field with a valid Prisma model:

MySchema: {
  from: 'User',  // ✅ Must match Prisma model name
  fields: { /* ... */ },
}

Error: Invalid field shape

❌ Invalid Field Definition
Schema 'MySchema' has invalid field definition for 'myField'

Solution: Use valid field definition syntax:

fields: {
  // ✅ Valid formats:
  field1: true,                    // Include
  field2: false,                   // Exclude
  field3: {                        // Computed
    compute: (data) => { /* ... */ },
    type: 'string',
  },
  field4: {                        // Renamed
    from: 'oldField',
    type: 'string',
  },
  field5: {                        // Relation
    include: true,
    shape: 'RelatedSchema',
  },
  
  // ❌ Invalid:
  field6: 'invalid',               // Must be boolean or object
  field7: { invalid: true },       // Missing required properties
}

Next Steps

Learn about Custom Queries to create custom endpoints
Check out the API Reference for complete type definitions
See Getting Started Guide for project setup

Need help? Open an issue on GitHub

Was this page helpful?

Edit this page on GitHub

Custom Schemas Tutorial

Custom Schemas Tutorial

Table of Contents

Introduction

Schema Transformation Concepts

What are Schemas?

Schema Definition Syntax

Field Types

1. Include/Exclude Fields

2. Computed Fields

3. Renamed Fields

4. Relation Fields

Async Computed Fields

Performance Considerations

1. N+1 Query Problem

2. Caching Computed Values

3. Avoid Heavy Computations

Real-World Examples

1. User Profile with Privacy

2. Product with Pricing & Inventory

3. Order Summary

4. Blog Post with Author

5. Shopping Cart

6. Analytics Dashboard

7. API Response with Metadata

8. Audit Trail

9. Notification System

10. Multi-Currency Support

Best Practices

1. Keep Schemas Focused

2. Hide Sensitive Data

3. Document Your Schemas

4. Reuse Schemas

5. Performance First

Troubleshooting

Error: Schema not found

Error: Missing base model

Error: Invalid field shape

Next Steps

Was this page helpful?