Soft-Delete and Restore in TypeScript with a Prisma Repository and Migration

-- migrations/20240517_add_soft_delete/migration.sql

ALTER TABLE "User" ADD COLUMN "deletedAt" TIMESTAMP(3);
ALTER TABLE "Post" ADD COLUMN "deletedAt" TIMESTAMP(3);

-- Partial indexes cover only live rows, which is the common query path.
CREATE INDEX "User_live_idx"
  ON "User" ("id")
  WHERE "deletedAt" IS NULL;

CREATE INDEX "Post_live_idx"
  ON "Post" ("authorId")
  WHERE "deletedAt" IS NULL;

-- Full index on the timestamp for retention/cleanup jobs.
CREATE INDEX "User_deletedAt_idx" ON "User" ("deletedAt");
CREATE INDEX "Post_deletedAt_idx" ON "Post" ("deletedAt");

type Delegate = {
  findMany: (args: any) => Promise<any[]>;
  findFirst: (args: any) => Promise<any | null>;
  update: (args: any) => Promise<any>;
  delete: (args: any) => Promise<any>;
};

export abstract class SoftDeleteRepository<T, D extends Delegate> {
  protected constructor(protected readonly model: D) {}

  findMany(where: Record<string, unknown> = {}): Promise<T[]> {
    return this.model.findMany({ where: { ...where, deletedAt: null } });
  }

  findById(id: string): Promise<T | null> {
    return this.model.findFirst({ where: { id, deletedAt: null } });
  }

  findWithDeleted(id: string): Promise<T | null> {
    return this.model.findFirst({ where: { id } });
  }

  softDelete(id: string): Promise<T> {
    return this.model.update({
      where: { id },
      data: { deletedAt: new Date() },
    });
  }

  restore(id: string): Promise<T> {
    return this.model.update({
      where: { id },
      data: { deletedAt: null },
    });
  }

  hardDelete(id: string): Promise<T> {
    return this.model.delete({ where: { id } });
  }
}

import { Injectable } from '@nestjs/common';
import { PrismaClient, User } from '@prisma/client';
import { SoftDeleteRepository } from './soft-delete.repository';

type UserDelegate = PrismaClient['user'];

@Injectable()
export class UsersService extends SoftDeleteRepository<User, UserDelegate> {
  constructor(private readonly prisma: PrismaClient) {
    super(prisma.user);
  }

  listActive(): Promise<User[]> {
    return this.findMany();
  }

  async deactivate(id: string): Promise<User> {
    const existing = await this.findById(id);
    if (!existing) {
      throw new Error(`User ${id} not found or already deleted`);
    }
    return this.softDelete(id);
  }

  async reactivate(id: string): Promise<User> {
    const archived = await this.findWithDeleted(id);
    if (!archived) {
      throw new Error(`User ${id} does not exist`);
    }
    return this.restore(id);
  }

  purge(id: string): Promise<User> {
    return this.hardDelete(id);
  }
}