Soft Delete Database Design: Unique Keys and Safe Restores

A team deletes a project called launch-plan, creates another project with the same name, and later restores the original. Which project gets the URL?

Adding deleted_at to a table does not answer that question. Soft delete database design must define identity, uniqueness, visibility, and recovery together. Otherwise, a convenient trash button becomes a source of broken links and confusing records.

This guide builds a small PostgreSQL model for a SaaS workspace with projects and tasks. The goal is practical: allow recovery, keep active project URLs unique, and make deletion behavior explicit.

1. Define what deletion means in your product

For this example, deleting a project removes it from ordinary project lists and prevents new task activity. Its tasks remain stored. An authorized administrator can view the trash and request a restore.

That is one policy, not a universal definition. An archived project might remain visible and searchable. A suspended project might be temporarily unavailable because of an account restriction. Give those states separate meanings if your product needs them.

Start with three questions: who can still see the record, which operations remain possible, and what must happen to dependent records? Write the answers before choosing columns.

A timestamp records the current deletion state and when the latest deletion occurred. It does not capture every previous delete and restore cycle. Use a separate lifecycle event history when that sequence matters.

2. Keep stable IDs separate from reusable names

A project ID identifies one project for its lifetime. A slug gives people a readable URL. Those responsibilities should remain separate even when both values happen to be unique today.

Suppose project 41 owns launch-plan. After it is deleted, project 57 may claim that slug. Project 41 is still project 41. Restoring it must never attach its tasks to project 57 simply because the names match.

Store relationships using primary keys. Treat slugs as names with a documented reuse policy. The same distinction appears in our guide to modeling identifiers within their namespaces.

Slug reuse also affects links. If old slug-only URLs must always identify the original project, retain permanent reservations or include a stable ID in the route. Allowing reuse means an old slug-only link may eventually resolve to a different project.

3. Enforce uniqueness among active projects

Here is a compact schema. It deliberately omits users, permissions, lifecycle events, and retention jobs so the relationships stay visible.

CREATE TABLE workspaces (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE projects (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES workspaces(id),
  slug text NOT NULL,
  deleted_at timestamptz,
  CONSTRAINT projects_slug_format
    CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$'),
  UNIQUE (workspace_id, id)
);

CREATE UNIQUE INDEX projects_active_slug_uidx
  ON projects (workspace_id, slug)
  WHERE deleted_at IS NULL;

CREATE TABLE tasks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL,
  project_id bigint NOT NULL,
  title text NOT NULL,
  FOREIGN KEY (workspace_id, project_id)
    REFERENCES projects (workspace_id, id)
);

CREATE INDEX tasks_project_idx
  ON tasks (workspace_id, project_id);

The slug check accepts lowercase ASCII words separated by single hyphens. Normalize user input before storing it, and keep that rule consistent across forms, imports, and APIs.

The partial unique index allows a slug to appear in multiple deleted rows while permitting only one active owner per workspace. PostgreSQL documents this use of partial unique indexes.

A plain UNIQUE (workspace_id, slug) would reserve the name even after deletion. Adding a nullable deletion timestamp to an ordinary unique constraint is also unsuitable: PostgreSQL treats nulls as distinct by default, allowing duplicate active combinations. See its constraint documentation for the precise rules.

The composite task foreign key prevents a task from claiming one workspace while referencing a project in another. The corresponding full unique key on projects supplies its reference target. A partial unique slug index cannot serve that role.

4. Make restore conflicts a normal workflow

Soft deletion is a state change:

UPDATE projects
SET deleted_at = CURRENT_TIMESTAMP
WHERE workspace_id = $1
  AND id = $2
  AND deleted_at IS NULL
RETURNING id;

These are parameterized SQL templates; bind the placeholders through your database driver. Repeating this update leaves an already deleted project unchanged. The service must distinguish an already completed request from a missing or unauthorized record.

Restoring means setting deleted_at = NULL. If another active project has claimed the slug, the unique index rejects the update. Keep the original project deleted and ask the administrator to choose an available slug. Apply the replacement slug and restore in one transaction.

A friendly availability check can improve the interface, but it cannot prevent a concurrent request from claiming the same name. Handle the database conflict as an expected outcome. Never silently rename the newer project or merge their tasks.

Restore authorization should be evaluated at request time. Permission to view the trash does not necessarily grant permission to reactivate a project.

5. Decide how deleted parents affect children

In this model, tasks have no independent deletion timestamp. Their visibility follows the parent project. This avoids updating thousands of task rows just to hide one project.

SELECT t.id, t.title
FROM tasks AS t
JOIN projects AS p
  ON p.workspace_id = t.workspace_id
 AND p.id = t.project_id
WHERE p.workspace_id = $1
  AND p.id = $2
  AND p.deleted_at IS NULL;

The foreign key confirms that a project exists. It does not require that project to be active. Likewise, ON DELETE CASCADE concerns physical row deletion; setting a timestamp does not invoke it.

If new tasks must be prohibited after deletion, coordinate task creation and project deletion. One approach is to lock the project row with SELECT ... FOR UPDATE, check its state, and insert the task in the same transaction. Every participating write path must follow the same protocol. Review PostgreSQL’s row locking behavior before implementing it.

If tasks later gain their own trash workflow, restoring a project should preserve tasks that were already independently deleted. Record the reason or deletion batch when implementing explicit cascaded soft deletion.

6. Review every read path and background job

Filtering the main project list is only the beginning. Direct detail routes, exports, search indexes, counts, notifications, and scheduled jobs also need a lifecycle policy.

A queued reminder may execute after its project is deleted. Recheck project state when the job runs. A search index may retain stale documents, so its synchronization process needs deletion and restore handling too.

Centralize ordinary active-record queries in a clearly named repository or application scope. Keep administrative trash access explicit. Query filtering supports the lifecycle rule; authorization still decides which tenant and records the caller may access.

Historical reports may intentionally include deleted projects. When a report requires values as they were at an earlier moment, soft deletion alone is insufficient. Our guide to snapshot data modeling explains how to preserve those facts.

7. Plan retention and permanent removal separately

Soft deletion keeps the row and its stored attributes. Plan permanent removal as a separate workflow with a defined retention period and explicit dependency checks.

The example foreign key blocks purging a project while tasks still reference it. A purge service must remove or otherwise resolve those tasks according to the product’s rules. Avoid adding cascading physical deletes simply to make the error disappear.

Search systems, object storage, caches, and backups have their own lifecycles. Decide how each copy is handled rather than assuming one database update completes the process. Record purge outcomes so interrupted jobs can be investigated and safely retried.

8. Test the lifecycle before shipping

• Create the same active slug twice in one workspace; the second insert must fail.
• Create the same slug in two workspaces; both should succeed.
• Delete a project and reuse its slug; existing tasks must retain their original project ID.
• Restore the original with the reused slug; report a conflict without changing either identity.
• Restore with a new available slug; retained tasks should become accessible again.
• Try to attach a task across workspace boundaries; the foreign key must reject it.
• Race task creation against deletion, and check delayed jobs against deleted parents.
• Attempt permanent removal while dependent tasks exist; verify the chosen purge policy.

Model the workspaces, projects, and tasks in DB Designer, then annotate the slug reuse and restoration rules beside their relationships. Keep PostgreSQL-specific index definitions in your reviewed migrations. A useful diagram makes the lifecycle decisions visible before they spread across application code.