The next feature I want to add to our application is SEO-friendly and self-healing URLs for the post show view.

Right now, our application uses auto-incrementing integer IDs as the identifier in our post URLs. This is problematic for two main reasons:

1. It exposes the total number of records in our database, which could be a security risk.
2. It’s not SEO-friendly—search engines prefer URLs that contain descriptive, keyword-rich slugs rather than just numeric IDs.

To improve search engine rankings and user experience, we want to embed the post title into the URL. However, post titles can change over time, so we need a way to maintain a canonical URL that always brings users back to the correct post.

Generating a Persistent, SEO-Friendly URL

To support SEO-friendly URLs, we’ll use virtual columns, a feature we previously discussed.

We define a public ID column, which consists of a hex-encoded combination of:

• The post ID (which remains fixed).
• The created_at timestamp (which never changes).
• This ensures the public ID is deterministic and uniquely identifies each post.
• Since we’re adding this column after the table was created, we use a dynamic virtual column (not stored).
• We index the public ID so that lookups remain fast and efficient, even as our database grows.

Overriding the find Method for Flexible Lookups

To make this work, we need to override the find method in our Post model:

• If the identifier is an integer, we fall back to the default lookup behavior.
• Otherwise, we extract the public ID by:
  • Splitting the URL parameter on dashes.
  • Taking the last segment (our public ID).
  • Using find_by(public_id) to locate the post.

This ensures that even if the title changes, the post remains accessible via its public ID.

Ensuring SEO-Friendly, Self-Healing URLs

Rails makes it easy to define how models are parameterized into URLs using the to_param method.

Here’s how we construct the SEO-friendly URL:

1. Slugify the title (parameterize to remove special characters).
2. Append the public ID.
3. Use "-" as a delimiter to separate them.

This results in readable and search-engine-friendly URLs like:

/posts/my-awesome-blog-post-3af2b8

Adding a Self-Healing Mechanism

A self-healing URL means:

• If someone modifies the title in the URL, it still redirects to the correct post.
• If they remove the title entirely, the public ID still ensures they reach the correct page.

Since our find method only looks at the last segment (the public ID), the URL remains resilient to minor changes.

Enforcing Canonical URLs in the Controller

To ensure that every post resolves to its correct canonical URL, we update our PostsController:

• Add a before_action called ensure_canonical_url.
• If the current URL doesn’t match the canonical URL, we redirect to the correct version.

before_action :ensure_canonical_url, only: %i[ show edit update destroy ]

def ensure_canonical_url 
  redirect_to @post if @post.to_param != params[:id]
end

Now, no matter how much users alter the URL, they always land on the right page with the correct format.

Verifying Index Usage for Fast Lookups

To confirm our index is working, we:

• Run an Active Record query using where/find_by(public_id).
• Check the SQL query plan with explain.
• Verify that SQLite uses our indexed virtual column to retrieve results efficiently.

This ensures fast lookups, even in a large database.

Final Results: Fully SEO-Optimized, Self-Healing URLs

• SEO-friendly URLs improve search visibility.
• Self-healing links ensure the correct post is always displayed.
• Indexed virtual columns keep database queries fast and efficient.

Up next, we’ll dive into full-text search, using SQLite’s built-in search engine to enable powerful search functionality in our application.