Data Deletion Standards

From the CTO — Required reading for all product and engineering teams. Deleting a row is easy. Deciding whether you are allowed to — and whether you will regret it — is not. This guide tells you which records must never be physically deleted, which ones must be, and how to implement both correctly.

---

Table of Contents

1. Why This Matters
2. The Standard at a Glance
3. Soft Delete: Marking Records as Inactive
4. Hard Delete: Physically Removing Records
5. GDPR and the Right to Erasure
6. Database Setup
7. Quick Reference

---

Why This Matters

Most engineers default to DELETE FROM table WHERE id = $1. That is the wrong default for most records in a production system.

Deleted data cannot be recovered

A hard delete is permanent. There is no undo. If a customer deletes their account by mistake, if a bug triggers a mass delete, or if a bad migration runs in production, the data is gone. In many cases this means losing financial records, audit history, or evidence needed for a legal dispute. A soft delete gives you a recovery window and a safety net.

Audit trails are a legal requirement

Financial and transactional records must be preserved. Payment records, invoices, and order history are required by tax law in most countries for a minimum of 5 to 7 years. Deleting them — even at a user's request — can put the company in violation of accounting regulations. The user's name can be removed (anonymized), but the transaction itself must remain.

Missing records break referential integrity

A hard delete on a parent row can corrupt related data. If you delete a users row and orders, invoices, or audit logs reference that user, those records now point to nothing. The data looks inconsistent or becomes unreadable. A soft delete keeps the row in place and keeps all references valid.

Architectural warning: Never use DELETE as the default action for user-initiated removal requests. Evaluate each entity against the rules in this guide. When in doubt, soft delete and consult the team.

---

The Standard at a Glance

Entity type	Delete type	Reason
Users, accounts	Soft delete + anonymize on erasure request	Needed for referential integrity; PII removed on GDPR request
Orders, transactions	Soft delete only — never hard delete	Legal and tax retention requirements
Invoices, receipts	Soft delete only — never hard delete	Legal and tax retention requirements
Audit logs, activity history	No delete — append-only	Immutable by design
Sessions, refresh tokens	Hard delete	No business value after expiry
Temporary files, drafts	Hard delete	No compliance requirement
User-generated content (posts, comments)	Soft delete	May be referenced by other records
Notification records	Hard delete after retention window	Low compliance risk, high volume
PII fields on retained records	Nullify / anonymize in place	Compliance with right to erasure

---

Soft Delete: Marking Records as Inactive

A soft delete does not remove the row. It marks the row as deleted by setting a deleted_at timestamp. The row stays in the database. All foreign key references remain valid.

Required columns

Add these two columns to every table that uses soft delete.

deleted_at  TIMESTAMPTZ DEFAULT NULL,
deleted_by  UUID        DEFAULT NULL REFERENCES users(id)

Column	Type	Description
deleted_at	TIMESTAMPTZ	Null when the record is active. Set to the current timestamp when deleted.
deleted_by	UUID	The ID of the user or service that triggered the deletion. Useful for audit.

A record is considered active when deleted_at IS NULL. A record is considered deleted when deleted_at IS NOT NULL.

Implementation

// ✅ Soft delete — set deleted_at, do not remove the row
async function softDeleteOrder(orderId: string, deletedBy: string) {
  await db.query(
    `UPDATE orders SET deleted_at = now(), deleted_by = $1 WHERE id = $2 AND deleted_at IS NULL`,
    [deletedBy, orderId]
  );
}

// ❌ Wrong — physically removes the record
async function deleteOrder(orderId: string) {
  await db.query('DELETE FROM orders WHERE id = $1', [orderId]);
}

Filtering active records

Every query on a soft-deleted table must filter out deleted rows unless there is a specific reason to include them (e.g. an admin audit view).

// ✅ Active records only
const orders = await db.query(
  'SELECT * FROM orders WHERE user_id = $1 AND deleted_at IS NULL',
  [userId]
);

// ✅ Include deleted — only in admin or audit context
const allOrders = await db.query(
  'SELECT * FROM orders WHERE user_id = $1',
  [userId]
);

// ❌ Wrong — forgets to filter deleted records
const orders = await db.query('SELECT * FROM orders WHERE user_id = $1', [userId]);

Prisma: soft delete with middleware

// ponytail: global middleware handles the filter once instead of repeating it in every query
prisma.$use(async (params, next) => {
  if (params.model === 'Order') {
    if (params.action === 'findMany' || params.action === 'findFirst') {
      params.args.where = { ...params.args.where, deletedAt: null };
    }
    if (params.action === 'delete') {
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
  }
  return next(params);
});

Unique constraints and soft delete

A unique constraint (e.g. email UNIQUE) will block a new user from registering with the same email as a soft-deleted account. Use a partial unique index that only applies to active rows.

-- ✅ Unique only among active records
CREATE UNIQUE INDEX idx_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

---

Hard Delete: Physically Removing Records

A hard delete permanently removes the row with DELETE FROM. Use it only for the record types listed in the table above.

When hard delete is correct

Hard delete is appropriate when all of the following are true:

1. The record has no compliance or legal retention requirement.
2. No other record holds a foreign key reference to this row.
3. Losing the record permanently has no audit or business consequence.

// ✅ Hard delete — sessions have no retention requirement
async function deleteExpiredSessions() {
  await db.query(
    'DELETE FROM sessions WHERE expires_at < now()',
  );
}

// ✅ Hard delete — user explicitly deletes a draft
async function deleteDraft(draftId: string, userId: string) {
  await db.query(
    'DELETE FROM drafts WHERE id = $1 AND author_id = $2',
    [draftId, userId]
  );
}

Foreign key safety

Before running a hard delete, confirm the row is not referenced by any table. If it is, either soft delete it or remove the child references first.

-- Check for foreign key references before deleting
SELECT conrelid::regclass AS referencing_table
FROM pg_constraint
WHERE confrelid = 'sessions'::regclass AND contype = 'f';

---

GDPR and the Right to Erasure

When a user requests deletion of their personal data under GDPR (the "right to erasure"), the correct response is not to hard delete their account row. It is to anonymize the PII in place while keeping the non-personal records intact.

What to remove versus what to keep

Data	Action
Name, email, phone, address	Nullify or replace with anonymized placeholder
Password hash, tokens	Delete immediately
Profile photo, personal files	Hard delete from storage
Order records, payment amounts	Keep — financial retention requirement
References to the user's ID	Keep — UUID v7 is not PII on its own
Audit log entries	Keep — remove PII fields within them if present

Implementation

// ✅ GDPR erasure — anonymize PII, keep transactional records
async function eraseUserData(userId: string) {
  await db.transaction(async (trx) => {
    // 1. Anonymize PII on the user row
    await trx.query(
      `UPDATE users
       SET
         email      = $1,
         name       = 'Deleted User',
         phone      = NULL,
         address    = NULL,
         deleted_at = now()
       WHERE id = $2`,
      [`deleted+${userId}@erased.invalid`, userId]
    );

    // 2. Remove credentials and active sessions
    await trx.query('DELETE FROM sessions WHERE user_id = $1', [userId]);
    await trx.query('DELETE FROM refresh_tokens WHERE user_id = $1', [userId]);

    // 3. Orders and invoices: kept, user_id reference remains valid
    // No action required — UUID is not PII
  });
}

Rule: A GDPR erasure request removes the person's identity from your system. It does not remove their financial or transactional history. Tax law takes precedence over the right to erasure for records that fall under a legal retention obligation.

---

Database Setup

Soft delete table template

CREATE TABLE example_entity (
  id          UUID        PRIMARY KEY DEFAULT uuid_generate_v7(),
  -- ... entity fields ...
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at  TIMESTAMPTZ DEFAULT NULL,
  deleted_by  UUID        DEFAULT NULL REFERENCES users(id)
);

-- Index for fast filtering of active records
CREATE INDEX idx_example_entity_active ON example_entity (id) WHERE deleted_at IS NULL;

Retention-based hard delete (cleanup job)

For records approved for hard delete (sessions, expired tokens, old notifications), run a scheduled cleanup job. Never delete in the application request path.

// ✅ Scheduled job — runs daily, not in a request handler
async function purgeExpiredSessions() {
  const result = await db.query(
    'DELETE FROM sessions WHERE expires_at < now() - INTERVAL \'7 days\''
  );
  log.info('SESSIONS_PURGED', { count: result.rowCount });
}

Prisma schema

model Order {
  id        String    @id @db.Uuid
  userId    String    @db.Uuid
  amount    Decimal   @db.Decimal(12, 2)
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime? // null = active
  deletedBy String?   @db.Uuid

  user      User      @relation(fields: [userId], references: [id])

  @@index([userId], where: "deleted_at IS NULL", name: "idx_orders_user_active")
}

---

Quick Reference

// ✅ Soft delete
await db.query(
  'UPDATE orders SET deleted_at = now(), deleted_by = $1 WHERE id = $2',
  [actorId, orderId]
);

// ✅ Filter active records in every query
WHERE deleted_at IS NULL

// ✅ Hard delete — only for approved entity types
await db.query('DELETE FROM sessions WHERE expires_at < now()');

// ✅ GDPR erasure — anonymize PII, keep records
UPDATE users SET email = 'deleted+{id}@erased.invalid', name = 'Deleted User', deleted_at = now()

// ❌ DELETE FROM orders — never
// ❌ DELETE FROM invoices — never
// ❌ DELETE FROM users without anonymizing references — never
// ❌ Forgetting WHERE deleted_at IS NULL in a query on a soft-delete table

Task	How
Delete an order or invoice	Soft delete — set deleted_at, never DELETE
Delete a user account	Soft delete + anonymize PII fields
Handle a GDPR erasure request	Anonymize PII in place, delete credentials, keep transactions
Delete an expired session	Hard delete — DELETE FROM sessions WHERE expires_at < now()
Delete a draft	Hard delete — no retention requirement
Filter active records	Always add WHERE deleted_at IS NULL
Allow re-registration after soft delete	Partial unique index WHERE deleted_at IS NULL
Purge old data automatically	Scheduled cleanup job — not in the request path

---

Questions or edge cases not covered here? Open a discussion in the #engineering-standards channel.