200 markdown posts and the future-dated content puzzle

The blog has about 200 posts. They're stored in Postgres, edited through an admin UI, rendered as markdown, and cached with unstable_cache so the average page load doesn't touch the database. None of that is interesting on its own. What's interesting is the third state every post can be in.

A post is one of three things at any moment: a draft, published-and-visible, or scheduled ("published, but the publish date is in the future"). Drafts shouldn't appear anywhere public. Scheduled posts shouldn't appear until their datetime passes. The moment a scheduled post crosses that boundary, it should appear, without me logging in to nudge a cache.

This sounds like a one-line WHERE clause. It is not.

The naive approach has two bugs

The first cut at "get me a post by URL slug" looked roughly like this:

prisma.post.findUnique({
  where: { section_slug: { section, slug } },
})

(section, slug) is a compound-unique index on the table; findUnique is the right Prisma vocabulary for it, and the schema enforces that no two posts can collide on the pair. So far, so reasonable.

The first bug: this returns drafts. The canonical post URL — /blog/tech/some-slug — serves whatever row sits at that key regardless of published. There's no place to add AND published: true because findUnique only accepts the unique constraint's columns; that's the contract.

I migrated to findFirst:

prisma.post.findFirst({
  where: { section, slug, published: true },
  // …
})

findFirst accepts arbitrary filter clauses. The compound-unique still backs the query — Prisma is smart enough to use the index — but the WHERE can now include published: true, and the public route never serves a draft.

The second bug is the future-dated one, and it's where the design got interesting.

The cache stays correct as now moves

The obvious next move is to add datetime: { lte: now } to the WHERE: "only fetch posts whose publish date has passed."

prisma.post.findFirst({
  where: { section, slug, published: true, datetime: { lte: now } },
})

This works for one request. But the result gets cached with unstable_cache, and the moment you cache it, now is frozen into the cache entry. A post scheduled for 9:00am, cached at 8:55am as "not found," stays "not found" until something busts the cache. The admin manually revalidating their own scheduled post defeats the point of the feature.

The fix is to take now out of the cached query and apply it at read time:

const cachedFetcher = unstable_cache(
  () =>
    prisma.post.findFirst({
      where: { section, slug, published: true },               // ❶
      select: { /* … */ },
    }),
  [`post-${section}-${slug}`],
  { tags: [`post-${section}-${slug}`, `blog-${section}`] },
)
 
const post = await cachedFetcher()
if (!post) return null
 
const now = currentDatetimeString()
return post.datetime <= now ? post : null                       // ❷

The cached query stops at "published, including scheduled" ❶. The cache entry is the row itself, datetime included. Each request applies datetime <= now to the cached row freshly ❷. When now crosses the row's datetime, the same cached row that previously resolved to null now resolves to the post. No revalidation, no cron, no manual bust.

There's a tradeoff worth naming out loud: the post doesn't surface at the precise minute its datetime says. It surfaces on the first request after that minute. If nobody loads the site at 9:00am, the cached entry stays "not found" until 9:14am when someone visits. For a blog this is fine; the post is live the moment a reader arrives, and that's what the reader experiences. If "publishes exactly at 09:00:00 UTC" ever becomes a real product requirement, this design needs revisiting.

The implications cascade

Once "filter datetime <= now at read time" became the pattern, it had to be applied consistently. Five places in the codebase touched posts — the paginated feed, search, the single-post lookup, the archive, the sitemap — and each needed the same shape:

function publishedWhere(section: Section, now: string) {
  return { section, published: true, datetime: { lte: now } }
}

Pagination is where the pattern gets harder. The single-post case cached one row and filtered it at read time: clean, because there are no boundaries. With pagination, the page boundaries themselves depend on which posts are visible right now. A cache keyed by (section, page) falls apart on two counts: now freezes into each page's entry, and when a scheduled post crosses its datetime, every subsequent page boundary shifts by one, so every page-N cache entry has to invalidate.

The implementation takes two simplifications. First, only cache page 1: it's the hot path, and pages 2-and-onward are progressively cold. Second, for page 1, cache PAGE_SIZE + futureCount rows instead of PAGE_SIZE:

function makeBlogPage1Cache(section: Section) {
  return unstable_cache(
    async () => {
      const futureCount = await prisma.post.count({
        where: {
          section,
          published: true,
          datetime: { gt: currentDatetimeString() },
        },
      })
 
      return prisma.post.findMany({
        where: { section, published: true },          // no datetime in the cache
        select: postListItemSelect,
        orderBy: { datetime: "desc" },
        take: PAGE_SIZE + futureCount,                // padded by scheduled count
      })
    },
    [`blog-page1-${section}`],
    { tags: [`blog-${section}`] }
  )
}

The padding is what makes the read-time filter safe. take(PAGE_SIZE + futureCount) with orderBy: datetime desc returns the newest N rows. That set includes every scheduled post (future datetime sorts first) plus enough live ones to fill page 1. At read time, filter out the scheduled ones and slice:

const cached = await blogPage1Cache[section]()
const posts = cached
  .filter((post) => post.datetime <= now)
  .slice(0, PAGE_SIZE)

When a scheduled post crosses its datetime, the same cached payload passes one more row through the filter, the new post lands at the right index, and the page-1 slice picks up its newer member. No cache bust needed. Adding or editing a post calls revalidatePostSection(section); that's how new future posts get pulled into the padding window on the next rebuild.

totalPages doesn't come from the cache. A live count runs alongside the cached read so the last-page link stays accurate as scheduled posts cross over:

const [cached, total] = await Promise.all([
  blogPage1Cache[section](),
  prisma.post.count({ where: publishedWhere(section, now) }),
])
 
return {
  posts: cached.filter(p => p.datetime <= now).slice(0, PAGE_SIZE),
  totalPages: Math.ceil(total / PAGE_SIZE),
}

Pages 2 and onward skip the cache and run publishedWhere(section, now) against the DB directly. Deep pagination is rare enough that paying full DB cost doesn't move any metric.

The single-post lookup, the archive, and the sitemap use the simpler version of the same idea: cache without datetime, filter at read time, no padding needed because there's no slice boundary at risk. Search is direct-DB-read with no cache; a search query's specificity makes caching it pointless anyway.

The catch-all route

The site has about a decade of legacy URLs from earlier blog setups. Old posts that used to live at /some-slug (no section prefix) need to redirect to their canonical /blog/{section}/{slug} location, permanently.

The naive way is an [slug] API route that returns JSON, with the page-level rendering done elsewhere. That route existed in an earlier version of this site. It surfaced raw JSON to the browser on miss, which is a strictly worse 404 than the framework's own.

The cleaner version is a root-level catch-all page:

// src/app/[slug]/page.tsx
export default async function LegacySlugPage({ params }: Props) {
  const { slug } = await params
 
  let match: Awaited<ReturnType<typeof lookupLegacySlug>> = null
 
  try {
    match = await lookupLegacySlug(slug)
  } catch (error) {
    console.error("[page:[slug]] lookupLegacySlug failed for", slug, error)
  }
 
  if (match?.kind === "post") {
    permanentRedirect(`/blog/${match.section}/${match.slug}`)
  }
 
  if (match?.kind === "project") {
    permanentRedirect(`/projects/${match.slug}`)
  }
 
  notFound()
}

Next.js's App Router routes static segments first. /about, /admin, /blog, /projects all win against [slug] because they're declared as real folders. The catch-all only takes over for one-segment paths that nothing else handles: the exact set where a legacy redirect makes sense.

The DB-outage branch is the small detail I'm fond of. If lookupLegacySlug throws, the default behavior would render the framework's 500 page. A 404 with the full site chrome (and the navigation back to working pages) is strictly better UX for a visitor: they get somewhere, even though their specific URL is broken. The error is still logged for me.

lookupLegacySlug uses the same pattern as getPostBySlug:

const cachedLookup = unstable_cache(
  async (slug: string): Promise<CachedLookup> => {
    const [post, project] = await Promise.all([
      prisma.post.findFirst({
        where: { slug, published: true },
        select: { section: true, slug: true, datetime: true },
      }),
      prisma.project.findFirst({
        where: { slug },
        select: { slug: true },
      }),
    ])
 
    return { post, project }
  },
  ["legacy-redirect"],
  { revalidate: 300, tags: ["posts", "projects"] },
)
 
export async function lookupLegacySlug(slug: string): Promise<LegacyMatch> {
  const { post, project } = await cachedLookup(slug)
  const now = currentDatetimeString()
 
  if (post && post.datetime <= now) {
    return { kind: "post", section: post.section, slug: post.slug }
  }
  // …
}

Same shape: cache without datetime, filter at read time.

What's next

The next post is about three things server components got me on. Not big architectural complaints: small, surprising bugs that came out of how Next.js decides what to fetch and when. Worth it; if you're new to the App Router, knowing the shapes ahead of time saves a real amount of debugging.

Scheduled-content-on-unstable_cache is the kind of thing everyone solves slightly differently. If yours doesn't look like mine, @roland.leth.ro.