Building a Complete Blog Platform: Search, Filtering, Code Highlighting & RSS Feed

// schema.ts
export const blogPosts = sqliteTable('blog_posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  slug: text('slug').notNull().unique(),
  titleEn: text('title_en').notNull(),
  titleFa: text('title_fa').notNull(),
  contentEn: text('content_en'),
  contentFa: text('content_fa'),
  categoryId: integer('category_id').references(() => blogCategories.id),
  status: text('status').default('draft'),
  featured: integer('featured').default(0),
  publishedAtEn: text('published_at_en'),
})

-- Create virtual FTS table
CREATE VIRTUAL TABLE blog_fts USING fts5(
  slug UNINDEXED,
  title_en,
  title_fa,
  excerpt_en,
  excerpt_fa,
  content_en,
  content_fa,
  content='blog_posts',
  content_rowid='id'
);

-- Trigger to keep FTS in sync
CREATE TRIGGER blog_posts_ai AFTER INSERT ON blog_posts BEGIN
  INSERT INTO blog_fts(rowid, slug, title_en, title_fa, excerpt_en, excerpt_fa, content_en, content_fa)
  VALUES (new.id, new.slug, new.title_en, new.title_fa, new.excerpt_en, new.excerpt_fa, new.content_en, new.content_fa);
END;

// Search API
export async function searchPosts(query: string, locale: string) {
  const db = getDb()
  const results = db.prepare(`
    SELECT bp.slug, bp.title_en, bp.title_fa, bp.excerpt_en, bp.excerpt_fa,
           rank
    FROM blog_fts 
    JOIN blog_posts bp ON bp.id = blog_fts.rowid
    WHERE blog_fts MATCH ?
      AND bp.status = 'published'
    ORDER BY rank
    LIMIT 20
  `).all(`${query}*`)
  return results
}

import { codeToHtml } from 'shiki'
import { visit } from 'unist-util-visit'

export async function highlightCode(tree: Root) {
  const nodes: [Code, Parent, number][] = []
  
  visit(tree, 'code', (node: Code, index, parent: Parent) => {
    nodes.push([node, parent, index as number])
  })

  await Promise.all(nodes.map(async ([node, parent, index]) => {
    const html = await codeToHtml(node.value, {
      lang: node.lang || 'text',
      theme: 'github-dark',
    })
    parent.children.splice(index, 1, {
      type: 'html',
      value: html,
    })
  }))
}

// app/[locale]/blog/page.tsx
export default async function BlogPage({ searchParams }: { searchParams: { category?: string } }) {
  const db = getDb()
  const posts = db.prepare(`
    SELECT bp.*, bc.name_en as cat_en, bc.name_fa as cat_fa
    FROM blog_posts bp
    LEFT JOIN blog_categories bc ON bc.id = bp.category_id
    WHERE bp.status = 'published'
      ${searchParams.category ? 'AND bc.slug = ?' : ''}
    ORDER BY bp.id DESC
  `).all(...(searchParams.category ? [searchParams.category] : []))

  return <BlogList posts={posts} />
}

// app/rss.xml/route.ts
export async function GET() {
  const db = getDb()
  const posts = db.prepare(`
    SELECT * FROM blog_posts WHERE status = 'published' ORDER BY id DESC LIMIT 50
  `).all() as BlogPost[]

  const rss = `<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
  <channel>
    <title>HBZ Blog — Infrastructure Engineering</title>
    <link>https://habibazar.ir/en/blog</link>
    <description>Network, Security, and Infrastructure insights</description>
    <atom:link href="https://habibazar.ir/rss.xml" rel="self" type="application/rss+xml"/>
    ${posts.map(p => `
    <item>
      <title>${p.title_en}</title>
      <link>https://habibazar.ir/en/blog/${p.slug}</link>
      <description>${p.excerpt_en}</description>
      <pubDate>${new Date(p.created_at).toUTCString()}</pubDate>
      <guid>https://habibazar.ir/en/blog/${p.slug}</guid>
    </item>
    `).join('')}
  </channel>
</rss>`

  return new Response(rss, {
    headers: { 'Content-Type': 'application/rss+xml; charset=utf-8' }
  })
}

export function calculateReadTime(content: string, locale: 'en' | 'fa'): string {
  const wpm = locale === 'fa' ? 200 : 238  // Persian readers slightly slower
  const words = content.trim().split(/s+/).length
  const minutes = Math.ceil(words / wpm)
  return locale === 'fa' ? `${minutes} دقیقه مطالعه` : `${minutes} min read`
}