Soft delete and name reuse: make restore conflicts explicit

Decision memo: a deleted project may free its name

A workspace administrator deletes a project called reports, creates a replacement with that name and later opens Trash to restore the original. The product now has two retained records and one available name. A restore button cannot resolve that conflict by simply clearing deleted_at.

For a product that allows immediate name reuse, define restoration as a request to reactivate the original immutable record ID. If another active record owns its old name within the same tenant, restore must stop with a conflict or accept a new name. It must preserve the newer record and its relationships. This is a proposed product contract, illustrated with an executed local database fixture. No user interviews or production outcomes are claimed.

The alternative is to reserve the name throughout the recovery window. That makes an unchanged-name restore easier to promise, but deletion no longer frees the name for a replacement. Permanent reservation is another option for identifiers whose reuse would confuse external clients. Choose the rule for the particular field before deciding what the delete dialog says.

The rest of this memo develops immediate reuse for project names. It does not apply that choice automatically to account identifiers, public hostnames or security principals.

Separate the record from the name people remember

An immutable project ID identifies the record. A display name helps a person recognize it. A normalized name key defines which names compete within a workspace. These fields have different jobs even when the first prototype uses one string for all of them.

The proposed uniqueness rule is: at most one active project has a given name_key within a given tenant_id. Deleted projects do not participate in that active-name claim. Another tenant can use the same key. The old project's tasks and audit entries continue to refer to its ID, not whichever active project currently answers to its former name.

That distinction changes the restore preview. Show the deleted record's creation or deletion details and its current ID-bound relationships. If reports is occupied, offer a new name for the old record. Do not attach the old tasks to the replacement because the names match.

Decide how names are normalized. Case folding, whitespace and Unicode equivalence are product rules that must agree between validation and the database key. The fixture supplies exact lowercase keys and tests none of those transformations. Its results therefore establish a contract for those exact keys, not a complete international naming policy.

Nine executed checks make the proposed contract concrete

The standard-library Python fixture uses an in-memory SQLite database with an active-only unique index. It creates a task referencing the original project, reuses its name and attempts both conflicted and renamed restores. The results file contains nine cases and the database version.

The last two checks are serialized orderings, not overlapping database transactions. They show the intended winner in each order. They do not establish production transaction isolation, lock behavior or retry timing.

The conflicted restore leaves the original in Trash and does not modify the replacement. Restoring under reports-old preserves the original ID and its task reference. The mismatched-tenant check assumes a trusted tenant context and tests the write predicate; it does not test authentication or an authorization service.

Put active uniqueness in the database contract

PostgreSQL's partial-index documentation describes enforcing uniqueness only among rows satisfying an index predicate. For the proposed active-name rule, the conceptual definition is:

CREATE UNIQUE INDEX active_project_name
ON projects (tenant_id, name_key)
WHERE deleted_at IS NULL;

Both indexed keys should be non-null under this contract. PostgreSQL's constraint documentation explains that nulls are distinct by default in ordinary unique constraints. Adding nullable deleted_at to an ordinary unique key does not express this active-only rule: multiple active rows with a null deletion timestamp can evade that intended comparison.

A unique key containing a Boolean deleted flag creates another limitation. It allows one active and one deleted record for a name, but cannot retain multiple deleted records with that same key. The fixture's two retained trash rows make that limitation relevant to the chosen product behavior.

The executed engine is SQLite, whose unique partial indexes enforce uniqueness across the selected subset. The example SQL expresses the same predicate, but the fixture is not a PostgreSQL integration test. Run transaction and error-mapping checks against the production engine before shipping.

An application availability check can improve the preview. It cannot reserve a name until the later restore commits. Let the database enforce the final claim, map the conflict to a recoverable product response and avoid reporting a successful restore before that write completes.

Restoration needs a conflict flow, not an overwrite shortcut

The restore command should identify the deleted project by ID and derive the tenant from trusted request context. It should also check that the caller may restore this resource, that it is still recoverable and that its expected version remains current. Those are implementation requirements beyond the fixture.

When the old name is occupied, the interface can explain that the original project is still available but needs another name. The server must validate the replacement key during the same operation that reactivates the row. If that new name becomes occupied after the preview, return another conflict and preserve the original state.

Renaming the active replacement is a separate user decision. It may affect its links, integrations or collaborators. Deleting it to make room is a separate destructive action. Neither belongs as a silent side effect of restoring an older record.

Restore previews also need to describe dependencies. A retained task row can still reference the same ID, as the fixture demonstrates, while an external file, secret or scheduled job may already be gone. Decide which dependent resources are restored automatically, which are unavailable and which need a new setup step. Do not let a successful parent-row update imply that every resource returned.

For account closure and retained exports, use the separate data handoff guide. Project trash is a recovery feature; it does not itself establish a complete data-removal process.

Define what reused names mean for links and history

A saved ID-based link to the original project should resolve according to the original project's state. Reusing its name must not silently retarget that link to a new project. A name-based route may be defined to resolve the current active owner, but then the product must acknowledge that it does not identify the historical record.

Audit entries need enough identity to distinguish a deleted original from a replacement created under the same name. Displaying only reports can make actions appear to belong to one project when they belong to two. Store the resource ID and tenant alongside the recorded decision, following the audit-log data-minimization guide.

Search results and caches need the same separation. A name becoming available in the database does not prove that old derived entries stopped appearing. The stale search deletion model covers event ordering for that boundary. Make name availability and derived-data completion separate acceptance items.

There is a delivery cost to the chosen policy: conflict previews, rename-on-restore, stable IDs in downstream consumers and tests for competing claims. That cost follows from allowing reuse. Reserving names changes the workload, but it still requires the interface to explain why a deleted name cannot be selected.

A release-ready requirement for delete, recreate and restore

Use this acceptance statement: deleting a project releases its name for active use within its tenant while preserving its immutable ID during the supported recovery period. Restoring targets that original ID. If an active record has claimed the requested name, the restore fails without changing either record and offers a separately validated new name.

Run the fixture with Python 3 first. Then test the complete interface and production database with two sessions racing to create or restore the same key. Verify the surviving owner, conflict response and unchanged relationships after each order. Include a stale restore preview, an unauthorized caller, an expired recovery period and a dependency that cannot be restored.

The current fixture proves nine local checks, including one active owner under each serialized claim order. The production acceptance record should add the exact transaction outcome and the screen the administrator sees when reports is taken.

Sources

Documentation checked October 11, 2026.

1. PostgreSQL 18: partial unique indexes
2. PostgreSQL 18: uniqueness, nulls and foreign keys
3. SQLite: unique partial indexes