Soft-deleting Postgres rows without losing the URL slug

Six lines of code that let you release a unique-constrained slug back into the namespace without dropping the constraint.

I want soft-delete on my blog posts. I also want slug to be UNIQUE NOT NULL. These two requirements quietly fight each other, and the standard fixes — partial unique indexes, deleted_at IS NULL predicates, separate archive tables — are all heavier than what I needed.

Here's the six-line trick I shipped instead.

The conflict

A blog post has a URL slug like my-cool-post. I want UNIQUE on slug so I can serve /blog/my-cool-post without ambiguity. I also want DELETE to be soft — set status = 'archived' so I can recover the post if I change my mind, and so historical analytics don't get orphaned.

Now the user re-uses the title "My Cool Post" two months later. They want a fresh post at /blog/my-cool-post. The INSERT fails because the archived row still owns the slug.

Get 5 practical emails covering the exact TypeScript concepts that trip up C# developers.

drippery.app

Standard answers:

• Partial unique index: CREATE UNIQUE INDEX ON posts(slug) WHERE status != 'archived'. Works, but it's a separate index to maintain and it doesn't handle the case where you want to look up the archived post by its old slug at all (your /blog/<slug> lookup needs to know the old slug is gone).
• deleted_at IS NULL everywhere: Now every query needs a predicate. One missed WHERE and you leak archived data.
• Separate archived_posts table: Schema duplication, two places to query, FK headaches with analytics tables that point at posts.id.

The trick

When you archive, rename the slug. Append -archived-${Date.now()}:

const archivedSlug = `${post.slug}-archived-${Date.now()}`;

await db
  .update(blogPosts)
  .set({
    status: "archived",
    slug: archivedSlug,
    updatedAt: new Date(),
  })
  .where(eq(blogPosts.id, post.id));

await revalidatePath(`/blog/${post.slug}`);
await revalidatePath("/blog");

That's it. Six lines of business logic.

What this gives you

• The original slug is now free. The next INSERT of a post titled "My Cool Post" succeeds with no contention.
• The unique constraint stays a plain UNIQUE NOT NULL — no partial index, no predicate, no rewrites of every query.
• The archived row is still queryable by ID, by status, by content. You haven't lost anything.
• The timestamp suffix makes collisions impossible across multiple archive/restore cycles. Date.now() is millisecond-precise; you won't archive two posts within the same millisecond unless you're shipping at scale I'm not at.
• The old URL stops resolving, which is exactly what you want for a deleted post. revalidatePath flushes the ISR cache so the public-facing site reflects the deletion immediately.

What this doesn't give you

• Restore doesn't auto-rename back. If you want a "restore" action, you need to handle the case where the original slug is now occupied by a newer post — typically by appending -2 or by failing with a 409 and asking the user to pick a new slug. I haven't built restore yet because nobody's asked for it.
• Search engines that already indexed /blog/<old-slug>-archived-1234 will get a 404 when they re-crawl. This is correct behavior for deleted content. If you instead want a 410 Gone, add a route handler that detects the -archived- suffix and returns 410.
• Audit trails get noisy slugs. Your activity log will show my-cool-post → my-cool-post-archived-1739812340000. If that bothers you, store the original slug in a separate original_slug column at archive time, and use it for display.

When this is the wrong tool

If you need to keep the archived URL working for legal or SEO reasons (e.g. blog posts that shouldn't 404 because they're cited from elsewhere), this pattern is wrong. You want the partial unique index approach instead, and a "tombstone" version of the page that explains the post was archived and links to a successor.

But if archive means "this is gone, the URL should die, and I want to be able to reuse the title later" — and that's the common case for blog post archives — six lines of slug-renaming is all the complexity you need.

The schema

For completeness, the relevant column on drippery_blog_posts:

slug: varchar("slug", { length: 255 }).notNull().unique(),
status: varchar("status", { length: 20 }).notNull().default("draft"),

No partial index. No deleted_at. No second table. Just UNIQUE on the slug, a status column, and a six-line archive handler that renames the slug on the way out.

Sometimes the best soft-delete is the one that briefly hard-deletes the namespace.

---

The blog engine behind this post is part of Drippery — a drip email tool I'm building for creators who want automated email sequences without the $39/month price tag.

Every database decision, every slug collision, every "why is this harder than it should be" moment — I write about it on my Substack.