# Prisma Schema for Soft Delete in prompts.chat: Implementation and Migration Strategies

> Implement soft delete in your Prisma schema with a deletedAt field. Learn migration strategies and query filtering to preserve prompts.chat data integrity and exclude deleted records.

- Repository: [Fatih Kadir Akın/prompts.chat](https://github.com/f/prompts.chat)
- Tags: migration-guide
- Published: 2026-04-02

---

**To implement soft delete in the prompts.chat Prisma schema, add a nullable `deletedAt` DateTime field to the model, create a migration to add the column in PostgreSQL, and filter all queries with `where: { deletedAt: null }` to exclude deleted records while preserving data integrity.**

The prompts.chat repository demonstrates a production-ready approach to soft delete using Prisma and PostgreSQL. By adding a nullable timestamp column rather than physically removing rows, the platform maintains audit trails and referential integrity while allowing content restoration. This guide examines the exact Prisma schema definitions, migration SQL, and application logic used to implement this pattern across the Prompt and Comment models.

## Designing the Prisma Schema for Soft Delete

The foundation of soft delete in prompts.chat rests on a single nullable field added to the `Prompt` model in `prisma/schema.prisma`. At line 117, the schema defines:

```prisma
deletedAt           DateTime?          // <‑‑ soft‑delete marker

```

When this field contains a timestamp, the record is considered deleted. When it remains `null`, the record is active and visible.

To maintain query performance while filtering deleted records, the schema includes a composite index at line 143:

```prisma
@@index([isPrivate, isUnlisted, deletedAt, createdAt(sort: Desc)])

```

This index optimizes list queries that exclude soft-deleted prompts while sorting by creation date, ensuring the database efficiently filters out rows where `deletedAt` is not null.

## Migration Strategy for Adding Soft Delete

Adding soft delete to an existing table requires a non-destructive migration that preserves existing data. The prompts.chat implementation follows a four-step migration strategy.

### 1. Add the Column via SQL Migration

The migration file [`prisma/migrations/20251211114705_add_soft_delete_to_prompts/migration.sql`](https://github.com/f/prompts.chat/blob/main/prisma/migrations/20251211114705_add_soft_delete_to_prompts/migration.sql) executes the following SQL at line 2:

```sql
ALTER TABLE "prompts" ADD COLUMN "deletedAt" TIMESTAMP(3);

```

Using a nullable `TIMESTAMP(3)` column allows existing rows to retain `NULL` values automatically, preventing data loss during deployment.

### 2. Back-Fill Existing Data

Because the column is nullable, existing prompts automatically receive `NULL` values without requiring an explicit back-fill operation. This ensures all legacy content remains visible immediately after migration.

### 3. Create Supporting Indexes

The composite index defined in the Prisma schema is created automatically when running the migration. This index supports efficient filtering of soft-deleted records in production queries.

### 4. Apply the Migration

Generate and apply the migration using the Prisma CLI:

```bash
npx prisma migrate dev --name add_soft_delete_to_prompts

```

This command generates the SQL file and synchronizes the database schema with the Prisma model definition.

## Query Filtering at the Application Level

With the schema in place, the application layer must filter out soft-deleted records in every read operation. In `src/app/api/prompts/[id]/route.ts` at line 73, queries include `deletedAt: null` in their `where` clauses:

```typescript
const prompt = await prisma.prompt.findUnique({
  where: { 
    id: params.id,
    deletedAt: null,  // Excludes soft-deleted records
  },
});

```

This pattern ensures that soft-deleted prompts are invisible to standard API consumers while remaining accessible for administrative auditing or restoration workflows.

## Implementing Soft Delete and Restore Endpoints

Rather than using `prisma.prompt.delete()`, the prompts.chat codebase implements soft delete as an update operation that sets the timestamp.

### Soft Delete Operation

The delete handler in `src/app/api/prompts/[id]/route.ts` at line 414 updates the record:

```typescript
await prisma.prompt.update({
  where: { id },
  data: { deletedAt: new Date() },
});

```

This approach preserves the row and all foreign key relationships while marking it as deleted.

### Restore Operation

To restore a previously deleted prompt, the restore endpoint in `src/app/api/prompts/[id]/restore/route.ts` at line 36 sets the field back to `null`:

```typescript
await prisma.prompt.update({
  where: { id },
  data: { deletedAt: null },
});

```

This immediate restoration pattern allows content moderators to reverse accidental deletions without data migration.

## Extending Soft Delete to Additional Models

The same pattern applies to other domain models requiring soft delete functionality. The `Comment` model demonstrates this reuse in migration `20251218145900_add_comments_system`, which adds a `deletedAt` column to the comments table at line 17 of [`prisma/migrations/20251218145900_add_comments_system/migration.sql`](https://github.com/f/prompts.chat/blob/main/prisma/migrations/20251218145900_add_comments_system/migration.sql):

```sql
ALTER TABLE "comments" ADD COLUMN "deletedAt" TIMESTAMP(3);

```

By consistently applying the nullable `DateTime?` field pattern across models, the codebase maintains uniform soft delete semantics and query patterns throughout the application.

## Summary

- **Use a nullable `deletedAt` field** in your Prisma schema to mark records as deleted without removing them from the database.
- **Create composite indexes** that include `deletedAt` to maintain query performance when filtering deleted content.
- **Execute non-destructive migrations** using `ALTER TABLE` with nullable columns to preserve existing data during deployment.
- **Filter all read queries** with `where: { deletedAt: null }` to ensure soft-deleted records remain hidden from standard users.
- **Implement delete as update** by setting `deletedAt: new Date()`, and restore by setting `deletedAt: null`, preserving referential integrity.

## Frequently Asked Questions

### How does prompts.chat handle soft-deleted records in database queries?

All database queries in the API routes explicitly filter for `deletedAt: null` in their `where` clauses. As implemented in `src/app/api/prompts/[id]/route.ts`, this ensures that soft-deleted prompts and comments are excluded from standard read operations while remaining available for administrative restoration.

### What are the benefits of using a nullable DateTime field for soft delete?

Using a nullable `DateTime` field provides a clear binary state: `null` indicates active records, while a timestamp indicates deletion time and audit trail. This approach, used in the prompts.chat Prisma schema, avoids the need for separate boolean flags and automatically records when each deletion occurred.

### Can soft-deleted prompts be restored in prompts.chat?

Yes, the repository includes a dedicated restore endpoint at `src/app/api/prompts/[id]/restore/route.ts` that updates the `deletedAt` field back to `null`. This immediately makes the prompt visible again without requiring data recreation or breaking any existing relationships.

### How do you migrate an existing Prisma model to support soft delete without downtime?

Add the `deletedAt` column as a nullable field using `ALTER TABLE "table_name" ADD COLUMN "deletedAt" TIMESTAMP(3)`. Because the column is nullable, existing rows automatically receive `NULL` values, ensuring zero downtime and no data loss during the migration deployment.