Enhanced Soft Delete Implementation

// lib/db.ts
import { PrismaClient } from '@prisma/client';

// 1. Define soft deletable models (ensure these match your schema)
const SOFT_DELETE_MODELS = [
  'User',
  'Product',
  'ProductVariant',
  'Sale',
  'Purchase',
  'Client',
  'Supplier',
  'Debt',
  'Notification'
];

const prisma = new PrismaClient().$extends({
  query: {
    $allModels: {
      // 2. Override delete operation
      async delete({ model, args, query }) {
        if (SOFT_DELETE_MODELS.includes(model)) {
          // Soft delete: update deletedAt field
          return (prisma as any)[model].update({
            ...args,
            data: { 
              deletedAt: new Date(),
              // Optional: clear sensitive data
              ...(model === 'User' && { password: null, email: `deleted-${Date.now()}@example.com` })
            }
          });
        }
        // Default behavior for non-soft-deletable models
        return query(args);
      },

      // 3. Override deleteMany operation
      async deleteMany({ model, args, query }) {
        if (SOFT_DELETE_MODELS.includes(model)) {
          return (prisma as any)[model].updateMany({
            ...args,
            data: { deletedAt: new Date() }
          });
        }
        return query(args);
      },

      // 4. Automatically filter out deleted records
      async findFirst({ model, args, query }) {
        if (SOFT_DELETE_MODELS.includes(model)) {
          args.where = { ...args.where, deletedAt: null };
        }
        return query(args);
      },

      async findUnique({ model, args, query }) {
        if (SOFT_DELETE_MODELS.includes(model)) {
          args.where = { ...args.where, deletedAt: null };
        }
        return query(args);
      },

      async findMany({ model, args, query }) {
        if (SOFT_DELETE_MODELS.includes(model)) {
          args.where = { ...args.where, deletedAt: null };
        }
        return query(args);
      },
      
      // 5. Count operations should exclude deleted items
      async count({ model, args, query }) {
        if (SOFT_DELETE_MODELS.includes(model)) {
          args.where = { ...args.where, deletedAt: null };
        }
        return query(args);
      }
    }
  }
});

// 6. Add custom methods for recovery/hard delete
prisma.$extends({
  model: {
    $allModels: {
      async restore<T>(this: T, id: string): Promise<any> {
        const context = Prisma.getExtensionContext(this);
        return (context as any).update({
          where: { id },
          data: { deletedAt: null }
        });
      },
      
      async hardDelete<T>(this: T, id: string): Promise<any> {
        const context = Prisma.getExtensionContext(this);
        return (context as any).delete({ where: { id }});
      }
    }
  }
});

export default prisma;

Key Improvements and Features:

1. Explicit Model Control

   • Only specified models get soft delete behavior
   • Prevents accidental deletion protection

2. Comprehensive Query Filtering

   • Automatically excludes deleted records from:
     • findFirst
     • findUnique
     • findMany
     • count

3. Data Protection

   • Optional sensitive data clearing (e.g., user emails/passwords)
   • Prevents PII retention in deleted records

4. Extended Functionality

   // Restore soft-deleted record
   await prisma.product.restore('prod_123');
   
   // Permanent hard delete
   await prisma.product.hardDelete('prod_123');

5. Include Deleted Records (When Needed)

   // Access deleted records explicitly
   const deletedProducts = await prisma.product.findMany({
     where: { deletedAt: { not: null } }
   });

6. Relationships Handling

   • Use Prisma middleware to cascade soft deletes:

   prisma.$use(async (params, next) => {
     if (params.model === 'Product' && params.action === 'update') {
       if (params.args.data.deletedAt !== null) {
         // Cascade to variants
         await prisma.productVariant.updateMany({
           where: { productId: params.args.where.id },
           data: { deletedAt: new Date() }
         });
       }
     }
     return next(params);
   });

Important Considerations:

1. Database Schema Requirements

   • All soft-deletable models need:

     model Product {
       id        String   @id @default(uuid())
       deletedAt DateTime? @db.Timestamp
       // ... other fields
     }

2. Indexing for Performance

   @@index([deletedAt])

3. Transaction Support

   • Works seamlessly with Prisma transactions:

     await prisma.$transaction([
       prisma.product.delete({ where: { id: 'prod_123' }}),
       prisma.inventory.update({ /* ... */ })
     ]);

4. Aggregate Queries

   • Modify aggregates to exclude deleted records:

     const activeProductCount = await prisma.product.aggregate({
       _count: { id: true },
       where: { deletedAt: null }
     });

Usage Examples:

Soft delete a product:

await prisma.product.delete({ where: { id: 'prod_123' }});
// Sets deletedAt to current timestamp

Find active products:

const products = await prisma.product.findMany();
// Automatically filters out deleted items

Find including deleted:

const allProducts = await prisma.product.findMany({
  where: { deletedAt: { not: null } }
});

Restore deleted item:

await prisma.product.restore('prod_123');

This implementation provides a robust soft-delete solution with:

1. Automatic filtering of deleted records
2. Secure data handling
3. Recovery capabilities
4. Performance optimizations
5. Clear separation from normal operations