Soft-Delete Pattern (deletedAt)

Status: shipped 2026-05-10 for Service + Resource (global deletedAt column), and on TenantCustomer.deletedAt for the tenant-scoped Customer-unlink flow shipped 2026-05-11. Audit/restore UI deferred until first real request. Global Customer GDPR delete (right-to-be-forgotten) deferred — pick up when customer self-service or platform admin needs it.

Why

Three problems pushed us off the "single isActive flag" approach:

1. Mixed semantics on one column. isActive=false was being used both for "tạm ngưng" (staff on parental leave; service paused for the season) and "đã rời" (staff đã nghỉ việc; dịch vụ không bao giờ chạy lại). UX nên tách.
2. SERVICE_IN_USE 409 was a dead end. Service.delete() fell back to throwing when bookings referenced the service. Admin's only recourse was to flip isActive=false, which is the wrong semantic — they really meant "remove this".
3. No GDPR delete path for Customer. Norway/EU customers have a right-to-be-forgotten request channel; we needed a way to scrub PII without losing booking history at the salon (which is part of the accounting trail under Bokføringsloven § 13).

Model

Two independent boolean-ish states per row:

deletedAt is terminal by convention. We don't ship a "restore" path until somebody actually asks — see "Audit view" deferred below.

Implementation

Schema

Service, Resource, Customer each get:

deletedAt DateTime? @map("deleted_at")
@@index([tenantId, deletedAt])  // or just @@index([deletedAt]) for global Customer

Migration: 20260510175118_add_deleted_at_soft_delete — pure column add, no backfill.

PrismaService extension

booking-api/src/prisma/soft-delete.extension.ts defines a Prisma client extension that auto-injects deletedAt: null into the where clause for the three soft-delete models:

const SOFT_DELETE_MODELS = ['service', 'resource', 'customer'] as const;
const FILTERED_OPS = new Set([
  'findFirst', 'findFirstOrThrow', 'findMany',
  'count', 'aggregate', 'groupBy',
  'update', 'updateMany', 'delete', 'deleteMany',
]);

findUnique / findUniqueOrThrow cannot accept non-unique filter fields (Prisma rejects the where-clause), so the extension post-filters instead: it runs the query as-is and drops the result when deletedAt != null.

The extension is wired in PrismaService by:

1. Building an extended client via this.$extends(softDeleteExtension).
2. Copying its service / resource / customer model proxies onto this so existing prisma.service.findMany(...) call sites get the auto-filter for free.
3. Overriding $transaction(callback) to use the extended client, so tx.service.findMany(...) inside transactions gets the same filter — without this step, soft-delete would silently bypass inside every $transaction.

Bypass for audit views

When you intentionally want to read deleted rows (audit table, restore endpoint, GDPR export), pass deletedAt explicitly:

// Show only deleted rows
prisma.service.findMany({ where: { deletedAt: { not: null } } });

// Show both
prisma.service.findMany({ where: { deletedAt: { not: undefined } } });

// Read a deleted row by id (must use findFirst, not findUnique)
prisma.service.findFirst({ where: { id, deletedAt: { not: null } } });

The extension only injects when the caller has not set deletedAt at all — any explicit value (null, not: null, { gt: someDate }) bypasses the inject.

Delete behaviour per model

Decision (2026-05-10): always soft-delete for all three models. The earlier draft tried hard-delete first and only soft-deleted on P2003, but the operator gave up that complexity in favour of "đỡ rủi ro" — a single, predictable code path that always preserves audit trail. DROP-then-regret is worse than the small DB cost of keeping an extra row around. There is no scenario where the operator benefits from the row physically disappearing.

Service

// service.service.ts
async delete(id, tenantId) {
  const existing = await this.findById(id, tenantId);
  await this.prisma.$transaction(async (tx) => {
    if (existing.imageKey) {
      await this.uploadService.syncReference(existing.imageKey, null, { type: 'Service', id }, tenantId, tx);
    }
    await tx.service.update({
      where: { id },
      data: { deletedAt: new Date() },
    });
  });
}

The image's UploadedFile reference is cleared so the orphan-cleanup worker can reclaim the blob (the snapshot already keeps imageKey on BookingItem.serviceSnapshot for audit; the live row gives up its claim). BookingItem snapshot pattern (shipped 2026-05-10) keeps receipts/invoices rendering correctly after soft-delete. The previous SERVICE_IN_USE (409) error code is removed; callers always get 204.

Resource

Resource.userId is @unique, so a soft-deleted row would block the underlying User from being re-linked to a new resource. We clear userId and flip isActive=false in the same UPDATE:

data: { deletedAt: new Date(), userId: null, isActive: false }

We do not cascade-clean child rows (ResourceSkill / ResourceSchedule / …Override / TimeOff / PortfolioItem) — they stay queryable for audit and the soft-deleted parent simply makes them invisible in admin lists.

DELETE /resources/:id is the new endpoint (was missing before) — OWNER / ADMIN only, returns 204.

Customer — tenant-scoped unlink (NOT global delete)

Customer is a global model (no tenantId). A tenant admin's "Delete customer" must not scrub data the customer still uses at another salon, and must not revoke their global login. The action is therefore tenant-scoped and lives on the bridge:

// customer.service.ts
async delete(customerId, tenantId) {
  const customer = await prisma.customer.findFirst({ where: { id: customerId } });
  if (!customer) throw new NotFoundException('CUSTOMER_NOT_FOUND');

  await prisma.tenantCustomer.upsert({
    where: { tenantId_customerId: { tenantId, customerId } },
    update: { deletedAt: new Date() },
    create: { tenantId, customerId, deletedAt: new Date() },
  });
}

findAllByTenant filters out customers with a deleted bridge:

where: {
  bookings: { some: { tenantId } },
  NOT: { tenantCustomers: { some: { tenantId, deletedAt: { not: null } } } },
}

The customer stays globally usable: login still works, they can still book at other salons, booking history at this tenant stays intact (each Booking carries customerName/customerPhone/customerEmail snapshots). Re-booking will trigger TenantCustomer auto-backfill again with a fresh row.

Global GDPR right-to-be-forgotten (scrub PII + revoke tokens) is a separate endpoint, not exposed to tenant admins. That belongs to customer self-service or platform admin and is deferred until needed.

What's NOT covered

• Tenant — rare admin action; isActive is enough for now.
• ServiceCategory — cascade-unassign already works.
• Lookup tables (Tax, AccountingAccount, …) — no privacy/GDPR concern, low churn.
• Audit / restore UI — deferred. Admins can psql against the DB if they need to inspect deleted rows. Add when first real request comes in.

Testing

• booking-api/src/prisma/soft-delete.extension.spec.ts — integration spec hitting local Postgres: verifies findMany filter, findUnique post-filter, audit bypass, and that $transaction callbacks inherit the filter.
• booking-api/src/core/service/service.service.spec.ts — delete block: 3 cases (always soft-deletes, releases image reference, NotFoundException).
• booking-api/src/core/resource/resource.service.spec.ts — delete block: 3 cases (always soft-deletes + clears userId/isActive, releases avatar, NotFoundException).
• booking-api/src/core/customer/customer.service.spec.ts — delete (tenant-scoped unlink) block: 2 cases (NotFoundException, upsert TenantCustomer with deletedAt and do not touch global Customer row).
• Controller specs assert wiring.

References

• Memory: project_deletedat_pattern_pending.md (origin), feedback_prisma_migration_order.md (lesson — manual prefix to ensure phase ordering).
• Booking snapshot pattern (shipped same week): docs/flows/booking-flow.md — Snapshot section.