Should trashed content continue to reserve its slug?

[masonjames]

PR #1814 fixes a seed re-application bug, but it raises a broader question about how trash should affect slugs.

Today, content tables enforce uniqueness on (slug, locale), including trashed rows, while normal lookups exclude those rows. This creates a mismatch:

1. A seeded entry is created and later trashed.
2. The seed is applied again.
3. The lookup finds no live entry, but insertion fails with a raw unique-constraint error because the trashed row still owns the slug.

The PR preserves the current behavior while handling it cleanly:

• skip and update leave the trashed entry untouched.
• error returns a clear conflict.
• Skipped trashed entries are not used as translation or reference targets.
• Tests cover all three conflict modes and the translation-reference case.

That fixes the crash, but should something in the trash prevent new live content from using the same slug?
I see three options:

1. Keep reserving the slug. Restoration remains simple and preserves the original URL, but users cannot reuse a slug until the old item is permanently deleted. This would need clearer UI and API errors.
2. Rename the slug when content is trashed. This keeps the existing schema while freeing the original slug. WordPress adopted this approach after discussing the same problem in ticket #11863: it stores the desired slug, appends a trash suffix, and tries to reclaim the original during restoration.
3. Apply uniqueness only to live rows. A partial unique index on (slug, locale) WHERE deleted_at IS NULL expresses the rule cleanly and permits duplicate slugs in the trash. It would, however, require replacing the existing constraint across every dynamic content table and supporting both SQLite/D1 and PostgreSQL.

Restoration still needs an explicit policy whichever option we choose:

• Fail with a clear conflict and let the user resolve it?
• Restore under a generated slug and warn the user?
• Reclaim the original slug and rename the newer live item?
• Allow multiple trashed entries to retain the same original slug?
• If trash releases the slug, should seeds create a new live entry while never implicitly restoring the old one?

My leaning is that trash should free the slug, and a conflicting restore should fail clearly rather than silently renaming unrelated live content. Rename-on-trash is probably the least disruptive implementation today, while live-only uniqueness is the cleaner long-term model.

I also think PR #1814 remains useful as a compatibility guard: existing databases already contain unsuffixed trashed rows, and seed application should not crash while the broader behavior is being decided.

[MA2153]

Leaning towards 3. If restoration couldn't proceed because the slug has been reclaimed, we should just nullify it. I don't consider slug as a major data loss issue.

[MA2153]

There is a different perspective too about enforcing slug uniqueness only for published entries. I am assuming trashed entries would be restored as Draft and not instantly land as Published.