Blog Feature Plan - Trystpilot

Blog Feature Plan — Trystpilot

Branch: claude/plan-blog-features-kkj63 Date: 2026-03-01 Status: Planning

Goals

Replace the current static placeholder blog (German copy-paste from Trustpilot) with a fully dynamic, DB-backed blog system integrated into the existing admin permissions layer.

Content pillars:

Relationship Advice
Romance Ideas
Dating Tips
Couples Games & Activities
Platform News

Current State

What	Status
`app/blog/page.tsx`	400-line static `"use client"` component — hardcoded German articles
`app/blog/layout.tsx`	Layout wrapper only (Header/Footer)
No `app/blog/[slug]/`	Individual post pages don’t exist
No blog API routes	Zero backend
No admin authoring UI	No way to write/publish posts
Middleware auth	Covers `/moderation` + `/moderation/:path*` — blog admin falls under this automatically

Permissions Model (existing, unchanged)

The existing middleware already gates /moderation/* behind ADMIN_SECRET.

ADMIN_SECRET env var + cookie(admin_key) or header(x-admin-key)
  └─ /moderation/*        ← already protected
      └─ /moderation/blog  ← new blog management UI lives here
          └─ /moderation/blog/new
          └─ /moderation/blog/[slug]/edit

Public blog routes (/blog, /blog/[slug]) remain unauthenticated. Blog write API (POST /api/blog, PATCH /api/blog/[slug], DELETE /api/blog/[slug]) will verify ADMIN_SECRET at the API route level (same pattern as /api/reviews/[id]/moderate).

Database Migration

New file: db/migrations/003_blog_posts.sql

CREATE TABLE blog_posts (
  id              UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  slug            VARCHAR(255) UNIQUE NOT NULL,
  title           VARCHAR(500) NOT NULL,
  excerpt         TEXT,
  content         TEXT         NOT NULL,
  category        VARCHAR(100) NOT NULL CHECK (category IN (
                    'relationship-advice',
                    'romance-ideas',
                    'dating-tips',
                    'couples-games',
                    'platform-news'
                  )),
  cover_image_url VARCHAR(1000),
  author_name     VARCHAR(100) NOT NULL DEFAULT 'Trystpilot Team',
  status          VARCHAR(20)  NOT NULL DEFAULT 'draft'
                    CHECK (status IN ('draft', 'published', 'archived')),
  featured        BOOLEAN      NOT NULL DEFAULT false,
  published_at    TIMESTAMP,
  created_at      TIMESTAMP    NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP    NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_blog_posts_status_published ON blog_posts (status, published_at DESC);
CREATE INDEX idx_blog_posts_category         ON blog_posts (category);
CREATE INDEX idx_blog_posts_featured         ON blog_posts (featured) WHERE featured = true;

No existing tables are touched. This is purely additive.

Content Categories

Slug	Label	Description
`relationship-advice`	Relationship Advice	Communication, trust, emotional availability
`romance-ideas`	Romance Ideas	Date nights, gestures, surprises
`dating-tips`	Dating Tips	First dates, online dating, conversation
`couples-games`	Couples Games	Activities, quizzes, challenges
`platform-news`	Platform News	Trystpilot updates & announcements

File Changes

New Files

File	Purpose
`db/migrations/003_blog_posts.sql`	Schema migration
`app/api/blog/route.ts`	`GET` (public list) + `POST` (admin create)
`app/api/blog/[slug]/route.ts`	`GET` (public post) + `PATCH` (admin update) + `DELETE` (admin archive)
`app/blog/[slug]/page.tsx`	Individual post page — SSG with ISR
`app/moderation/blog/page.tsx`	Blog post management list (admin)
`app/moderation/blog/new/page.tsx`	Create new post form (admin)
`app/moderation/blog/[slug]/edit/page.tsx`	Edit existing post form (admin)

Modified Files

File	Change
`app/blog/page.tsx`	Convert from static `"use client"` to Server Component; query DB for posts
`app/blog/layout.tsx`	Minimal — keep as-is
`app/sitemap.ts`	Add published blog post URLs
`CLAUDE.md`	Update implementation state table
`ROADMAP.md`	Add blog milestone

Middleware

No changes needed — /moderation/blog/* is already covered by the existing /moderation/:path* matcher.

API Design

`GET /api/blog`

Public. Query params: category, featured, limit, offset. Returns array of posts (no content field — excerpt only for listing).

`POST /api/blog`

Admin only. Body: { title, slug, excerpt, content, category, cover_image_url, featured, status }. Verifies x-admin-key header === process.env.ADMIN_SECRET.

`GET /api/blog/[slug]`

Public. Returns full post including content.

`PATCH /api/blog/[slug]`

Admin only. Partial update. Setting status: "published" auto-sets published_at.

`DELETE /api/blog/[slug]`

Admin only. Sets status: "archived" (soft delete — no hard deletes).

Blog Page (`app/blog/page.tsx`) Rewrite

Convert to Server Component (remove "use client")
ISR with revalidate = 300
Fetch featured post + posts per category from DB
Replace German category names with English content pillars
Category nav links to #category-slug anchors on the same page
Search form wired to GET /api/blog?q=... (client-side useRouter)

Individual Post Page (`app/blog/[slug]/page.tsx`)

generateStaticParams fetches all published slugs
generateMetadata sets title + description from post
ISR revalidate = 600
Renders content as Markdown (use existing marked or add react-markdown)
Breadcrumb: Home → Blog → Category → Post
Related posts section (same category, limit 3)

Admin Blog UI (`app/moderation/blog/`)

Post List (`/moderation/blog`)

Table: title, category, status badge, published_at, actions
Actions: Edit | Publish/Unpublish | Archive
“New Post” button → /moderation/blog/new

Create/Edit Form

Fields: Title, Slug (auto-generated from title, editable), Category dropdown, Excerpt (textarea), Content (textarea — plain Markdown), Cover Image URL, Featured toggle, Status (draft/published)
Submit → POST /api/blog or PATCH /api/blog/[slug]
Auth: sends x-admin-key header (same pattern as moderation actions)

Sitemap Update

Add to app/sitemap.ts:

const blogPosts = await db.query(
  `SELECT slug, updated_at FROM blog_posts WHERE status = 'published'`
);
// Map to { url, lastModified } entries

Seed Data (Optional)

5 seed posts, one per category, with realistic relationship-focused content. Added to scripts/db-seed.mjs.

What Is NOT In Scope

Rich text / WYSIWYG editor (plain Markdown textarea is sufficient for v1)
Image uploads (cover_image_url is a URL field only — no file hosting)
Comments on blog posts
Newsletter / email subscription
Author accounts (all posts attributed to “Trystpilot Team” for now)
Tag system (categories are sufficient for v1)

Implementation Order

db/migrations/003_blog_posts.sql
app/api/blog/route.ts + app/api/blog/[slug]/route.ts
app/blog/page.tsx rewrite (Server Component, DB-backed)
app/blog/[slug]/page.tsx (individual post)
app/moderation/blog/page.tsx (post list)
app/moderation/blog/new/page.tsx + edit page
app/sitemap.ts update
CLAUDE.md + ROADMAP.md updates