--- name: building-keyword-driven-blog description: Use when asked to add a blog to one or more Next.js sites from Google Ads keyword data ("make a blog like moneyos.cc/blog", "fetch keywords and write posts", a blog per sibling site), with posts stored in each site's own Neon Postgres database and category sizes that should look hand-made rather than generated. --- # Building a keyword-driven blog ## Overview Fetch real search data from the Google Ads API, plan categories and posts from it, build the blog engine once, copy it to every sibling site, and fan the writing out to subagents. Every post is a row in that site's own Neon database, reached through one validated write path, and a content check fails the build when a post breaks a rule. The reference files next to this SKILL.md are the working engine from six production sites (Next.js 16.4 with Cache Components, Tailwind 4, Drizzle on Neon). The full brief behind the rules is https://www.masao.site/ai/blog-builder.md. ## Steps 1. **Keyword data.** Run `keyword-research.ts` from a repo that holds a Google Ads OAuth connection (it reads the refresh token from that app's database). It expands hand-written seed sections per site through `GenerateKeywordIdeas`, feeds the relevant 3+ word ideas back in for long-tail variants, drops off-topic ideas with a relevance filter, fetches `GenerateKeywordHistoricalMetrics`, and writes `ai/seo/keywords/keywords.md` per site with high-traffic (≥1,000/mo) and long-tail (3+ words, 10–999/mo) tables. Tighten the filter until the high-traffic table has no brand-navigation noise (bare "quickbooks payroll", "excel to pdf"). 2. **Plan, one planner subagent per site.** Give it `planner-brief.md` filled in: category count and the post count per category, the site's lane, and the sibling lanes. Use odd, unequal counts (for example 9, 9, 7, 7, 5, 5) with the largest at most twice the smallest, so the index doesn't look machine-made and the balance rule still passes. It writes `docs/blog-keywords.md` (keyword map plus section plan), `docs/blog-plan/CATEGORIES.md` and `categories.json`. 3. **Check the plans across sites before writing.** List every primary keyword and slug across all sibling plans and re-key collisions by lane; two sites on the same query split the ranking. Check that the keyword map's Category column holds slugs, not names: planners slip on this, and slip again when re-run. 4. **Engine in the first site.** Copy the reference files (table below), `pnpm add drizzle-orm @neondatabase/serverless zod marked` and `pnpm add -D drizzle-kit tsx`, add the scripts below, `NEON_URL=x pnpm db:generate`, `pnpm db:migrate`, fill `blog-config.ts` from `categories.json`, add the `--color-blog-*` tokens to `globals.css`, add `/blog` to the header nav plus a phone-only link next to Sign in, and add the `.md` rewrite. 5. **Pattern post.** Write one post yourself to the writer brief and seed it. Writers on every site match its form. 6. **Prove the engine on a production build** before copying it: `pnpm build` then `next start`, and check an unknown slug returns 404 (see gotchas). 7. **Copy to the siblings.** Everything except `blog-config.ts`, the theme tokens and the header edit is identical. Migrate each site's own database. 8. **Write.** One writer subagent per 3–5 posts of one category, reading `writer-brief.md` from disk; the dispatch prompt names only the slugs. Writers run `pnpm check:content --files --slug ` until it prints PASS. Then one reviewer per one or two categories (`reviewer-brief.md`). 9. **Publish per site.** `pnpm blog:seed`, `pnpm check:content` against the database (full rules: orphans, balance, every mapped keyword has a post), `pnpm build`, browser check at desktop and phone width, commit, push. ## Files | Reference file | Goes to | Role | | -------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------- | | `db-schema.ts`, `db-client.ts` | `src/db/schema.ts`, `src/db/client.ts` | `posts` table (slug PK, unique primary keyword, `seeded_hash`), lazy Neon client | | `drizzle.config.ts`, `migrate.ts` | root, `scripts/` | Migrations from `NEON_URL` | | `blog-schema.ts` | `src/lib/blog/schema.ts` | zod post shape; Post, Category, Author, CallToAction types | | `blog-config.ts` | `src/lib/blog/config.ts` | The only per-site file: name, categories, author, CTA, preview-image colors | | `blog-routes.ts` | `src/lib/blog/routes.ts` | Paths, reserved slugs, site pages posts may link to | | `markdown.ts` | `src/lib/blog/markdown.ts` | One parser for headings, sections, links and prose words; renderer with heading anchors | | `rules.ts`, `rules.test.ts` | `src/lib/blog/` | Every content rule, and a failing fixture per rule (`tsx --test`) | | `files.ts`, `rows.ts`, `loader.ts`, `write.ts` | `src/lib/blog/` | Staged files, row mapping, the one cached read path, the one write path | | `feeds.ts` | `src/lib/blog/feeds.ts` | Atom, llms.txt, Markdown copy, BlogPosting and BreadcrumbList JSON-LD | | `seed-blog.ts`, `check-content.ts` | `scripts/` | Idempotent seed; the content check (`--files`, `--slug`, `--report`) | | `cta.tsx`, `toc.tsx`, `toc-spy.tsx`, `parts.tsx` | `src/components/blog/` | CTA box, table of contents with scroll spy, list/breadcrumb/JSON-LD | | `blog-layout.tsx`, `blog.css` | `src/app/blog/layout.tsx`, `src/app/blog/blog.css` | Header/footer, feed link, `ensureStatic`, post body styles | | `blog-index-page.tsx`, `recent-page.tsx`, `updated-page.tsx`, `category-page.tsx`, `post-page.tsx` | `src/app/blog/page.tsx`, `recent/page.tsx`, `recent/updated/page.tsx`, `category/[slug]/page.tsx`, `[slug]/page.tsx` | The pages | | `md-route.ts`, `og-route.tsx`, `feed-route.ts`, `llms-route.ts` | `src/app/blog/md/[slug]/route.ts`, `og/[slug]/route.tsx`, `feed.xml/route.ts`, `llms.txt/route.ts` | Markdown copy, preview image, feed, llms.txt | | `revalidate-route.ts`, `sitemap.ts`, `robots.ts` | `src/app/api/blog/revalidate/route.ts`, `src/app/` | Tag revalidation, sitemap with last-modified, robots | | `planner-brief.md`, `writer-brief.md`, `reviewer-brief.md` | `docs/blog-plan/` | Worker briefs with `__PLACEHOLDERS__`; `writer-brief-example.md` is one filled in | | `blog-doc.md`, `add-blog-post-skill.md` | `docs/blog.md`, `.claude/skills/add-blog-post/SKILL.md` | Living doc and the add-a-post skill for the next agent | | `keyword-research.ts` | the Google Ads app's `scripts/` | Keyword Planner expansion and metrics per site | Scripts: `"test": "tsx --test src/lib/blog/*.test.ts"`, `"db:generate": "drizzle-kit generate"`, `"db:migrate": "tsx --env-file=.env.local scripts/migrate.ts"`, `"blog:seed": "tsx --env-file=.env.local scripts/seed-blog.ts"`, `"check:content": "tsx --env-file-if-exists=.env.local scripts/check-content.ts"`, `"verify": "pnpm lint && pnpm typecheck && pnpm test && pnpm check:content && pnpm build"`. Rewrite in `next.config.ts`: `{ source: "/blog/:slug.md", destination: "/blog/md/:slug" }`. ## Decisions that matter - **Authored as files, stored as rows.** Writers and reviewers edit `content/blog/.md` in git; `blog:seed` saves each through `savePost`, which validates and runs the per-post rules first. The slug primary key and unique primary keyword make the database refuse duplicates whatever code writes. A row edited in the database since its last seed is skipped unless `--force`. - **One read path,** `"use cache"` + `cacheTag("blog")` + `cacheLife("max")`, returning ISO date strings so values serialize. Every page, the feed, the sitemap and llms.txt call it. - **Rules are code, not advice.** 40–60 word answer-first opening, section openers ≤60 words, 4–8 `##` headings with ≥2 questions and none shared across posts, 1,500 prose words (800 short), ≤20% shared 5-word runs between sections of different posts, keyword in title and slug and its words in description and opening, internal links and anchors resolve, no orphans, balanced categories. In `--slug` mode links resolve against the keyword map, so writers can check before the rest of the blog exists. - **Lanes between siblings.** Each site owns a niche; a generic keyword in another lane is only ever a secondary. Product claims come only from the site's own homepage files, and the brief tells writers the product may be in early access. - **Derived keywords are fine** when a site's data is thin in its lane; mark them, and don't take a sibling's keyword to fill a count. ## Next.js 16 gotchas - **Unknown slugs answered 200 in production.** With `partialPrefetching`, an unlisted param serves the prerendered fallback shell before `notFound()` runs. `export const ensureStatic = "navigation"` on the blog layout makes the request wait for the full render, so it 404s. A known category with zero posts still answers 200 with a `noindex` not-found body on its first hit; don't ship empty categories. - **`ensureStatic = "navigation"` needs at least one param** from every `generateStaticParams`, so a site's build fails until its first post is seeded. Seed before the first push. - `revalidateTag(tag, profile)` takes two arguments now; the route uses `{ expire: 0 }`. - pnpm 12 exits non-zero on `pnpm add` when it skips esbuild's build script. Add `esbuild: false` under `allowBuilds` in `pnpm-workspace.yaml`; tsx and drizzle-kit work without it. - Read `node_modules/next/dist/docs/` before trusting memory: `ensureStatic`, `PageProps`, `RouteContext` and `LayoutProps` are all in it. ## Verification Per site: `pnpm check:content --report` against the database (per-category counts, words vs floor, opening length, max overlap, inbound links), `pnpm build` (post and category pages listed as static), then against `next start`: `/blog`, `/blog/recent`, `/blog/recent/updated`, a category, a post, `.md`, `/blog/feed.xml`, `/blog/llms.txt`, `/sitemap.xml`, `/robots.txt`, `/blog/og/` all 200 with the right content type; `/blog/nope` and `/blog/category/nope` 404; `curl` a post and find a sentence from its last section in the HTML. In a browser at desktop and phone width: click a table-of-contents entry, see the CTA under the table and at the end, no horizontal overflow, and the Blog link in the header. ## Common mistakes | Mistake | Fix | | ---------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------ | | Equal category sizes from the brief's default | Assign odd, unequal counts up front; keep max ≤ 2× min | | Planner writes category names into the map | Rewrite the Category column to slugs from `categories.json`, and re-check after any re-plan | | Sibling sites target the same keyword | Cross-site duplicate check on keywords and slugs before writers start | | `pkill -f 'next start'` from the agent's own shell | It matches its own command line and kills the shell. Kill by PID from `ss -ltnp` | | Launching more subagents than the limit allows | The default cap is 20 concurrent; queue the rest and launch as each finishes | | Pushing a site with no posts | The build fails under `ensureStatic`; seed first | | Writers quietly change a primary keyword ("reconciliation in bank" became "...accounts") | The full check's "mapped keyword has no post" rule catches it; restore the mapped keyword in the post | | Skipping the reviewer pass because every post passed the check | The check counts words; reviewers found wrong-way worked examples, broken formulas, product overclaims and stale vendor facts in nearly every category |