TL;DR

論理削除（deleted_atカラム）は一見安全だが、運用で多くの問題が発生する
代替として「物理削除 + Archiveテーブル」パターンを採用
Prisma + PostgreSQLでの具体的な実装方法を紹介

この記事の対象読者

論理削除で苦労した経験がある方
新規プロジェクトの削除戦略を検討中の方
Prisma/NestJSを使っている方

論理削除で発生した問題（実体験）

問題1: WHERE句の追加漏れ

// 本来書くべきコード
const users = await prisma.user.findMany({
  where: { deletedAt: null }
});

// 実際に書いてしまったコード
const users = await prisma.user.findMany();
// → 削除済みユーザーも取得してしまいバグ発生

「ミドルウェアで自動付与すればいい」と思いますよね。でもそうすると...

// ミドルウェア設定
prisma.$use(async (params, next) => {
  if (params.action === 'findMany') {
    params.args.where = { ...params.args.where, deletedAt: null };
  }
  return next(params);
});

// 管理画面で削除済みユーザー一覧を見たい
// → 取得できない！

// 回避策として別のPrismaインスタンスを作る？
// → 管理コストが増大

問題2: UNIQUE制約との戦い

-- メールアドレスをUNIQUEにしたい
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- ユーザーA: test@example.com で登録
-- ユーザーAを論理削除
-- ユーザーB: 同じtest@example.comで登録したい
-- → エラー！UNIQUEに引っかかる

部分インデックスで解決できるが...

-- PostgreSQLの場合
CREATE UNIQUE INDEX users_email_unique ON users(email) WHERE deleted_at IS NULL;

-- MySQLの場合
-- 部分インデックスがない！Generated Columnで代替が必要
ALTER TABLE users ADD COLUMN email_unique VARCHAR(255)
  GENERATED ALWAYS AS (CASE WHEN deleted_at IS NULL THEN email ELSE NULL END);
CREATE UNIQUE INDEX users_email_unique ON users(email_unique);

DBによって対応が異なり、ポータビリティが下がります。

問題3: テーブル・インデックスの肥大化

-- 100万ユーザーのうち80万が退会済み
-- deleted_at IS NULLのチェックが毎回必要
SELECT * FROM users WHERE email = ? AND deleted_at IS NULL;

-- インデックスも80万件の「ゴミ」を含んでいる

解決策: 物理削除 + Archiveテーブル

方針

メインテーブルからは物理削除する（DELETE）
削除前にArchiveテーブルにJSON形式で保存
監査ログは別テーブルで管理

Step 1: Archiveテーブルの作成

// prisma/schema.prisma

model Archive {
  id         String   @id @default(uuid()) @db.Uuid
  entityName String   @map("table_name")    // "User", "Post" など
  recordId   String   @map("record_id")     // 削除されたレコードのID
  data       Json                           // レコード内容をJSONで保存
  createdAt  DateTime @default(now()) @map("created_at")

  @@index([entityName, recordId])
  @@map("archives")
}

マイグレーション実行:

npx prisma migrate dev --name add_archive_table

Step 2: ArchiveServiceの実装

// src/archive/archive.service.ts

import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';

interface ArchiveRecord {
  entityName: string;
  recordId: string;
  data: Record<string, unknown>;
}

@Injectable()
export class ArchiveService {
  constructor(private readonly prisma: PrismaService) {}

  /**
   * レコードをアーカイブに保存
   */
  async archive(record: ArchiveRecord): Promise<void> {
    await this.prisma.archive.create({
      data: {
        entityName: record.entityName,
        recordId: record.recordId,
        data: record.data,
      },
    });
  }

  /**
   * アーカイブからレコードを検索
   */
  async findArchived(
    entityName: string,
    recordId: string,
  ): Promise<Record<string, unknown> | null> {
    const archive = await this.prisma.archive.findFirst({
      where: { entityName, recordId },
      orderBy: { createdAt: 'desc' },
    });
    return archive?.data as Record<string, unknown> | null;
  }

  /**
   * 特定エンティティの削除履歴一覧
   */
  async listArchived(entityName: string): Promise<Record<string, unknown>[]> {
    const archives = await this.prisma.archive.findMany({
      where: { entityName },
      orderBy: { createdAt: 'desc' },
    });
    return archives.map((a) => a.data as Record<string, unknown>);
  }
}

Step 3: Userリポジトリでの使用

// src/user/user.repository.ts

import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';
import { ArchiveService } from '../archive/archive.service';

@Injectable()
export class UserRepository {
  constructor(
    private readonly prisma: PrismaService,
    private readonly archiveService: ArchiveService,
  ) {}

  async delete(userId: string): Promise<void> {
    // 1. 削除対象のユーザーを取得
    const user = await this.prisma.user.findUnique({
      where: { id: userId },
    });

    if (!user) {
      throw new Error('User not found');
    }

    // 2. トランザクションでアーカイブ保存 → 物理削除
    await this.prisma.$transaction(async (tx) => {
      // アーカイブに保存
      await tx.archive.create({
        data: {
          entityName: 'User',
          recordId: userId,
          data: user,
        },
      });

      // 物理削除
      await tx.user.delete({
        where: { id: userId },
      });
    });
  }

  // 通常のfindはシンプルに
  async findById(userId: string): Promise<User | null> {
    return this.prisma.user.findUnique({
      where: { id: userId },
      // deleted_at条件が不要！
    });
  }

  async findAll(): Promise<User[]> {
    return this.prisma.user.findMany();
    // シンプル！
  }
}

Step 4: 削除済みユーザーの確認（管理画面向け）

// src/admin/admin.controller.ts

@Controller('admin')
@UseGuards(AdminGuard)
export class AdminController {
  constructor(private readonly archiveService: ArchiveService) {}

  @Get('deleted-users')
  async getDeletedUsers() {
    return this.archiveService.listArchived('User');
  }

  @Get('deleted-users/:id')
  async getDeletedUser(@Param('id') id: string) {
    return this.archiveService.findArchived('User', id);
  }
}

Step 5: ユーザー復元機能（オプション）

// src/user/user.service.ts

@Injectable()
export class UserService {
  async restoreUser(userId: string): Promise<User> {
    // アーカイブからデータ取得
    const archivedData = await this.archiveService.findArchived('User', userId);

    if (!archivedData) {
      throw new Error('Archived user not found');
    }

    // トランザクションで復元 → アーカイブ削除
    return this.prisma.$transaction(async (tx) => {
      // ユーザー再作成
      const user = await tx.user.create({
        data: {
          id: archivedData.id,
          email: archivedData.email,
          displayName: archivedData.displayName,
          // ... その他のフィールド
        },
      });

      // アーカイブから削除
      await tx.archive.deleteMany({
        where: { entityName: 'User', recordId: userId },
      });

      return user;
    });
  }
}

GDPR対応（完全削除）

GDPRの「忘れられる権利」に対応する場合:

// 完全削除（アーカイブからも削除）
async purgeUser(userId: string): Promise<void> {
  await this.prisma.$transaction(async (tx) => {
    // 本体がまだあれば削除
    await tx.user.deleteMany({
      where: { id: userId },
    });

    // アーカイブからも削除
    await tx.archive.deleteMany({
      where: { entityName: 'User', recordId: userId },
    });

    // 監査ログは残す（誰がいつ削除要求したかの記録）
    await tx.auditLog.create({
      data: {
        auditableType: 'User',
        auditableId: userId,
        action: 'GDPR_PURGE',
        auditedChanges: { reason: 'User requested data erasure' },
        version: 1,
      },
    });
  });
}

Before / After 比較

クエリのシンプルさ

// Before（論理削除）
const users = await prisma.user.findMany({
  where: {
    deletedAt: null,
    posts: {
      some: {
        deletedAt: null,
        comments: {
          some: { deletedAt: null }
        }
      }
    }
  }
});

// After（物理削除）
const users = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        comments: { some: {} }
      }
    }
  }
});

UNIQUE制約

// Before（論理削除）
// 部分インデックスが必要、DBによっては対応していない

// After（物理削除）
// 普通のUNIQUE制約でOK
model User {
  email String @unique  // これだけ！
}

注意点とトレードオフ

外部キー制約

関連レコードがある場合、削除順序に注意:

// 子レコードを先に削除
await tx.post.deleteMany({ where: { authorId: userId } });
await tx.user.delete({ where: { id: userId } });

// または ON DELETE CASCADE を設定
model Post {
  author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
}

Archiveテーブルの定期パージ

// 1年以上前のアーカイブを削除するバッチ
@Cron('0 0 * * 0') // 毎週日曜0時
async purgeOldArchives() {
  const oneYearAgo = new Date();
  oneYearAgo.setFullYear(oneYearAgo.getFullYear() - 1);

  await this.prisma.archive.deleteMany({
    where: { createdAt: { lt: oneYearAgo } },
  });
}

まとめ

観点	論理削除	物理削除+Archive
クエリ	毎回WHERE条件必要	シンプル
UNIQUE制約	部分インデックス必要	標準で対応
復元	容易	可能（要実装）
GDPR対応	追加対応必要	容易
複雑さ	暗黙的	明示的

論理削除は「簡単そう」に見えて、運用で複雑さが増大します。
物理削除 + Archiveは初期実装は少し手間ですが、長期的にはメンテナンスしやすいシステムになります。

プロジェクトの要件に合わせて検討してみてください。