Designing a Soft-Delete and Data Lifecycle System: Tombstones, Retention Windows, and Undo Workflows for Multi-Tenant SaaS

Every SaaS application eventually gets the same support ticket: “I accidentally deleted something important. Can you get it back?” If your answer is “no,” you lose the customer. If your answer is “yes, but it takes an engineer and a database query,” you lose time and trust. The correct answer is a restore button that works in under two seconds.

Building that button is harder than it sounds. A soft-delete system is not just adding a deleted_at column. It touches your schema design, your query layer, your unique constraints, your background job infrastructure, your GDPR compliance posture, and your multi-tenant isolation guarantees. Get one layer wrong and you end up with ghost rows polluting unique indexes, unbounded table growth, restored records pointing at already-deleted parents, or GDPR erasure requests you cannot fulfill without also destroying the audit trail.

This article walks through every layer of a production soft-delete system, from the tombstone schema through the hard-delete scheduler to the GDPR conflict resolution.

---

Tombstone Patterns: Three Approaches

There are three ways to mark a record as deleted. Each has different tradeoffs for query ergonomics, schema expressiveness, and operational complexity.

Boolean Flag

ALTER TABLE documents ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE;

This is the starting point most teams reach for. Simple to add, easy to filter. The problem: booleans are opaque. You lose when the deletion happened, who triggered it, and whether the deletion was manual, cascaded from a parent, or scheduled for expiry.

Status Enum

CREATE TYPE record_status AS ENUM ('active', 'deleted', 'archived', 'pending_deletion');

ALTER TABLE documents ADD COLUMN status record_status NOT NULL DEFAULT 'active';

Better. You can distinguish between user-initiated deletion, system-scheduled expiry, and archival. You can add new states without a schema migration to add a column. The filter is WHERE status = 'active' everywhere, which is still a full-table concern at scale.

Deleted-At Timestamp (Recommended)

ALTER TABLE documents ADD COLUMN deleted_at TIMESTAMPTZ;
ALTER TABLE documents ADD COLUMN deleted_by UUID REFERENCES users(id);
ALTER TABLE documents ADD COLUMN deletion_reason TEXT;

deleted_at IS NULL is the live-record filter. deleted_at IS NOT NULL gives you the deletion timestamp for retention window calculations, audit logs, and undo UI. deleted_by lets you surface “Deleted by Alice 3 hours ago” in the restore dialog. This is the pattern that actually supports the workflows downstream.

The downside versus a status enum is that you cannot represent states like pending_deletion in a single nullable timestamp. In practice, combine both: use deleted_at for the primary soft-delete signal and a separate lifecycle_status for complex state machines.

---

Schema Foundation

Here is the base table shape that supports all the workflows this article covers:

// Schema using Postgres via node-postgres (pg) or Drizzle
const CREATE_DOCUMENTS_TABLE = `
  CREATE TABLE documents (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     UUID NOT NULL REFERENCES tenants(id),
    title         TEXT NOT NULL,
    content       TEXT,
    owner_id      UUID NOT NULL REFERENCES users(id),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at    TIMESTAMPTZ,
    deleted_by    UUID REFERENCES users(id),
    hard_delete_after TIMESTAMPTZ,
    deletion_reason TEXT
  );

  -- Partial index: only index live records
  CREATE INDEX idx_documents_tenant_live
    ON documents(tenant_id, created_at DESC)
    WHERE deleted_at IS NULL;

  -- Index for the hard-delete scheduler
  CREATE INDEX idx_documents_hard_delete_due
    ON documents(hard_delete_after)
    WHERE deleted_at IS NOT NULL AND hard_delete_after IS NOT NULL;
`;

The hard_delete_after column is set at soft-delete time based on the tenant’s retention policy. The partial index on hard_delete_after keeps the scheduler query fast without scanning the entire table.

---

Cascading Soft Deletes

When a user deletes a workspace, every project inside it should be soft-deleted too. When a project is deleted, every document inside it follows. Doing this at the application layer (rather than relying on database cascades) gives you control over setting deleted_by, hard_delete_after, and deletion_reason correctly on each child row.

interface SoftDeleteOptions {
  deletedBy: string;
  reason?: string;
  retentionDays?: number;
}

async function softDeleteWorkspace(
  db: Pool,
  tenantId: string,
  workspaceId: string,
  opts: SoftDeleteOptions
): Promise<void> {
  const client = await db.connect();
  try {
    await client.query('BEGIN');

    const deletedAt = new Date();
    const hardDeleteAfter = opts.retentionDays
      ? new Date(deletedAt.getTime() + opts.retentionDays * 86_400_000)
      : null;

    // Soft-delete the workspace itself
    await client.query(
      `UPDATE workspaces
       SET deleted_at = $1, deleted_by = $2, hard_delete_after = $3, deletion_reason = $4
       WHERE id = $5 AND tenant_id = $6 AND deleted_at IS NULL`,
      [deletedAt, opts.deletedBy, hardDeleteAfter, opts.reason ?? null, workspaceId, tenantId]
    );

    // Cascade to projects
    const projectResult = await client.query<{ id: string }>(
      `UPDATE projects
       SET deleted_at = $1, deleted_by = $2, hard_delete_after = $3, deletion_reason = 'cascaded from workspace'
       WHERE workspace_id = $4 AND tenant_id = $5 AND deleted_at IS NULL
       RETURNING id`,
      [deletedAt, opts.deletedBy, hardDeleteAfter, workspaceId, tenantId]
    );

    const projectIds = projectResult.rows.map(r => r.id);

    if (projectIds.length > 0) {
      // Cascade to documents
      await client.query(
        `UPDATE documents
         SET deleted_at = $1, deleted_by = $2, hard_delete_after = $3, deletion_reason = 'cascaded from project'
         WHERE project_id = ANY($4) AND tenant_id = $5 AND deleted_at IS NULL`,
        [deletedAt, opts.deletedBy, hardDeleteAfter, projectIds, tenantId]
      );
    }

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Key decisions here: all three levels are soft-deleted in a single transaction, so there is no window where the workspace is deleted but its children are still visible. The tenant_id filter on every UPDATE prevents cross-tenant cascade bugs. The deleted_at IS NULL guard on each UPDATE makes the operation idempotent: running it twice does not overwrite the original deleted_at timestamp.

---

Undo and Restore Workflows

Restore is the inverse operation, but it is not simply setting deleted_at = NULL. You have to handle referential integrity: a document cannot be restored into a project that is itself deleted.

interface RestoreResult {
  restored: boolean;
  blockedBy?: 'parent_deleted' | 'hard_deleted' | 'not_found';
  parentType?: string;
  parentId?: string;
}

async function restoreDocument(
  db: Pool,
  tenantId: string,
  documentId: string,
  restoredBy: string
): Promise<RestoreResult> {
  const client = await db.connect();
  try {
    await client.query('BEGIN');

    // Fetch the document and its parent project
    const result = await client.query<{
      deleted_at: Date | null;
      hard_delete_after: Date | null;
      project_id: string;
      project_deleted_at: Date | null;
    }>(
      `SELECT d.deleted_at, d.hard_delete_after, d.project_id,
              p.deleted_at AS project_deleted_at
       FROM documents d
       JOIN projects p ON p.id = d.project_id
       WHERE d.id = $1 AND d.tenant_id = $2
       FOR UPDATE`,
      [documentId, tenantId]
    );

    if (result.rows.length === 0) {
      await client.query('ROLLBACK');
      return { restored: false, blockedBy: 'not_found' };
    }

    const row = result.rows[0];

    if (row.deleted_at === null) {
      // Already live
      await client.query('COMMIT');
      return { restored: true };
    }

    // Check if past the hard-delete window
    if (row.hard_delete_after && new Date() > row.hard_delete_after) {
      await client.query('ROLLBACK');
      return { restored: false, blockedBy: 'hard_deleted' };
    }

    // Check if parent is deleted
    if (row.project_deleted_at !== null) {
      await client.query('ROLLBACK');
      return {
        restored: false,
        blockedBy: 'parent_deleted',
        parentType: 'project',
        parentId: row.project_id,
      };
    }

    // Safe to restore
    await client.query(
      `UPDATE documents
       SET deleted_at = NULL, deleted_by = NULL, hard_delete_after = NULL,
           deletion_reason = NULL, updated_at = NOW()
       WHERE id = $1 AND tenant_id = $2`,
      [documentId, tenantId]
    );

    await client.query('COMMIT');
    return { restored: true };
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

When the parent is deleted, the UI should offer to restore the parent first, or restore the entire hierarchy together. The blockedBy: 'parent_deleted' signal gives the frontend enough information to display the right prompt.

---

Unique Constraints and Soft Deletes

This is where most implementations break. If documents.title has a UNIQUE constraint per tenant, a user who deletes a document named “Q4 Report” and then creates a new one with the same name will hit a constraint violation because the deleted row still occupies the index.

The standard fix is a partial unique index that only covers live records:

-- Drop the naive unique constraint
ALTER TABLE documents DROP CONSTRAINT IF EXISTS documents_tenant_title_unique;

-- Replace with a partial index
CREATE UNIQUE INDEX idx_documents_unique_title_per_tenant
  ON documents(tenant_id, title)
  WHERE deleted_at IS NULL;

Now the constraint only applies to live rows. Deleted rows are invisible to the uniqueness check. If the user restores the old “Q4 Report,” the restore will fail at the index level because the new document already holds the name. You need to detect this at restore time and surface a conflict to the user (rename one of them, or hard-delete the deleted version first).

---

Retention Windows and Hard-Delete Scheduling

Soft-deleted records consume storage and slow down table scans if left indefinitely. The retention window defines how long a record stays in the tombstone state before being permanently purged.

interface RetentionPolicy {
  defaultRetentionDays: number;
  byEntityType?: Record<string, number>;
  complianceOverrideDays?: number; // for GDPR-sensitive tenants
}

async function applyRetentionPolicy(
  db: Pool,
  tenantId: string,
  policy: RetentionPolicy
): Promise<void> {
  const defaultCutoff = new Date();
  defaultCutoff.setDate(defaultCutoff.getDate() + policy.defaultRetentionDays);

  // Mark newly soft-deleted records with their hard-delete deadline
  await db.query(
    `UPDATE documents
     SET hard_delete_after = $1
     WHERE tenant_id = $2
       AND deleted_at IS NOT NULL
       AND hard_delete_after IS NULL`,
    [defaultCutoff, tenantId]
  );
}

// Hard-delete scheduler — runs as a background job every hour
async function runHardDeletePass(db: Pool, batchSize = 500): Promise<number> {
  const now = new Date();

  const result = await db.query<{ id: string; tenant_id: string }>(
    `DELETE FROM documents
     WHERE id IN (
       SELECT id FROM documents
       WHERE deleted_at IS NOT NULL
         AND hard_delete_after IS NOT NULL
         AND hard_delete_after <= $1
       ORDER BY hard_delete_after
       LIMIT $2
       FOR UPDATE SKIP LOCKED
     )
     RETURNING id, tenant_id`,
    [now, batchSize]
  );

  return result.rowCount ?? 0;
}

FOR UPDATE SKIP LOCKED prevents multiple scheduler instances from stepping on each other. The ORDER BY hard_delete_after ensures oldest records are purged first, which matters if you are trying to hit a storage budget. Run this as a recurring job (cron, pg_cron, or a queue-based worker) rather than in the request path.

---

Query Performance at Scale

Soft-deleted tables accumulate dead weight. Without careful indexing, every WHERE deleted_at IS NULL scan touches all rows including the deleted ones.

The partial index introduced in the schema section is the core fix. But there are additional considerations at scale:

Row visibility in Postgres. Even with a partial index, a table with 90% deleted rows will have bloated heap pages. The partial index avoids scanning deleted rows in index-only scans, but sequential scans (table statistics-driven) will still touch them. Run VACUUM ANALYZE regularly, and consider autovacuum_vacuum_scale_factor = 0.01 on high-churn tables to keep dead tuples from accumulating.

Partition by deletion status. For very large tables (100M+ rows), consider a deleted_at range partition or a separate deleted_documents table that you archive rows into. This is operationally heavier but keeps the live table lean.

Application-layer default scopes. Every query that touches soft-deletable tables needs the deleted_at IS NULL filter. In an ORM, you enforce this with a default scope. In raw SQL, you enforce it by code review and integration tests. The failure mode is a new engineer writing a query without the filter and accidentally exposing deleted records.

---

Multi-Tenant Isolation

In a pooled multi-tenant schema, soft-delete interacts with tenant isolation at two points.

First, the tenant_id filter must appear on every soft-delete and restore operation. A missing tenant_id in the WHERE clause of a restore operation lets a tenant restore another tenant’s deleted record if they know the UUID. This is a BOLA (Broken Object Level Authorization) class bug.

Second, retention policies should be per-tenant, not global. An enterprise customer paying for a 365-day retention window should not be affected by a change to the global default. Store the policy on the tenants table:

ALTER TABLE tenants ADD COLUMN retention_policy JSONB NOT NULL DEFAULT '{"defaultRetentionDays": 30}';

And read it at soft-delete time to set hard_delete_after correctly per tenant. This also lets you honor enterprise contracts that require longer retention without changing application code.

---

GDPR Right to Erasure Conflicts

Here is the hard part. Your soft-delete retention window is designed to keep data around for 30-90 days so users can undo mistakes. GDPR Article 17 requires you to erase personal data on request, typically within 30 days. These goals are in direct tension.

The resolution is not to disable soft deletes for GDPR-covered data. It is to treat a GDPR erasure request as an immediate hard delete, bypassing the retention window entirely.

async function processGdprErasureRequest(
  db: Pool,
  tenantId: string,
  subjectUserId: string,
  requestId: string
): Promise<void> {
  const client = await db.connect();
  try {
    await client.query('BEGIN');

    // Hard-delete all records owned by this user, including soft-deleted ones
    await client.query(
      `DELETE FROM documents
       WHERE tenant_id = $1 AND owner_id = $2`,
      [tenantId, subjectUserId]
    );

    // Anonymize records where user is referenced but not owner
    // (e.g., deleted_by, updated_by) — preserve row for referential integrity
    await client.query(
      `UPDATE documents
       SET deleted_by = NULL
       WHERE tenant_id = $1 AND deleted_by = $2`,
      [tenantId, subjectUserId]
    );

    // Record the erasure as an append-only audit receipt
    await client.query(
      `INSERT INTO gdpr_erasure_log (id, tenant_id, subject_user_id, request_id, completed_at)
       VALUES (gen_random_uuid(), $1, $2, $3, NOW())`,
      [tenantId, subjectUserId, requestId]
    );

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

The audit log row is exempt from erasure under GDPR Recital 65: you can retain proof that the erasure happened without retaining the personal data itself. Use a pseudonymized or hashed representation of the subject identifier in the log if you need to minimize PII even there.

Important: GDPR erasure also applies to records in the soft-delete tombstone state. A common mistake is an erasure pipeline that only touches deleted_at IS NULL rows. The tombstones are still personal data. The DELETE FROM documents WHERE owner_id = $1 query above covers both live and soft-deleted rows because there is no deleted_at IS NULL filter.

---

Tradeoffs Table

Approach	Undo Support	GDPR Compliance	Storage Cost	Query Complexity	Unique Constraint Handling
Boolean is_deleted	Possible but no timestamps	Hard (no erasure pipeline hooks)	Low overhead	Simple filter	Requires partial index
Status enum	Good with pending_deletion state	Medium (needs explicit GDPR state)	Low overhead	Moderate	Requires partial index
deleted_at timestamp	Full (timestamp enables retention windows)	Good (clear hard-delete trigger)	Low overhead	Simple filter	Requires partial index
Separate tombstone table	Excellent (live tables stay clean)	Excellent (truncate tombstone table per user)	Schema complexity	Two-table joins	Native unique on live table
Hard delete only	None	Trivial	Minimal	Simplest	Native unique constraint

The separate tombstone table deserves mention: moving soft-deleted rows to a deleted_documents table keeps the live table compact and makes the live table’s unique constraints work without partial indexes. The tradeoff is that restore requires a cross-table move (DELETE + INSERT in a transaction), and your application needs to query two tables when you do want to show deleted records (the trash UI).

For most SaaS products at under 50M rows, deleted_at with partial indexes is the right default. Switch to a separate tombstone table when table bloat becomes a measurable problem.

---

Production Considerations

Test restore under referential integrity pressure. Write integration tests that delete a parent, delete a child, then attempt to restore the child. Confirm the parent_deleted error is returned correctly. Then restore the parent first and confirm the child restore succeeds.

Cap the restore window in the UI. If hard_delete_after is 30 days, show users a countdown in the trash view (“This item will be permanently deleted in 18 days”). This sets expectations and reduces support volume.

Emit events on soft-delete and hard-delete. Other services (search indexes, caches, analytics pipelines) need to know when a record is deleted and when it is permanently gone. Soft-delete should trigger “remove from search index.” Hard-delete should trigger “purge from cold storage.” Use the outbox pattern to make these reliable.

Never delete without tenant_id in the WHERE clause. In a multi-tenant system, every soft-delete, restore, and hard-delete operation must filter by tenant_id. Make this a code review checklist item and an integration test fixture.

Monitor tombstone row counts. Add a metric tracking the ratio of soft-deleted rows to live rows per table. A ratio above 50% is a signal that your hard-delete scheduler is lagging or your retention policy is too long for your write volume.

---

Closing

Soft delete is one of those systems that looks trivial until you have to build the full lifecycle: creation, deletion, cascading, restore with integrity checks, unique constraint conflicts, retention expiry, hard-delete scheduling, and GDPR erasure that bypasses the retention window. Each piece is straightforward in isolation. The complexity is in the interactions.

The pattern that survives production is deleted_at timestamps with partial indexes, per-tenant retention policies stored alongside tenant configuration, a hard-delete scheduler that uses SKIP LOCKED for safe concurrent processing, and an erasure pipeline that treats GDPR requests as immediate hard deletes with no retention window exception. Build these as first-class primitives from the start and the “accidentally deleted something important” ticket becomes a two-second restore, not an engineer’s evening.