Content Pipeline SSG Platform

Problem

A personal engineering platform is not one site. Portfolio case studies, long-form blog posts, a curated news digest, and a spaced-repetition quiz all need to share the same intellectual material - and stay consistent when that material changes.

Without a deliberate content architecture, every surface becomes its own copy of the truth. Edits fork. Tags diverge. Quiz questions drift from the posts they belong to. "Publish" turns into a manual, error-prone ritual of copying prose between places and hoping the builds still agree.

I needed a platform contract: author once, deliver many times, with zero runtime database and a clear path to a future CMS without rewriting the pipeline.

Solution

The content pipeline is the backbone of prj--personal-portfolio--v3: a TypeScript pnpm monorepo that treats content as a first-class system, not as files dropped into each app.

Authors work in a private content repo (content--paulserban.eu). A sync step pulls published (and staged) trees into the app monorepo. Dedicated ingest CLIs parse MDX and JSON into a single SQLite database via Drizzle. Three Astro sites query that database at build time and emit static HTML; quiz-export turns the same rows into versioned static JSON for a Vite/React PWA. There is exactly one source of truth at build time - content.db - and no content API in production.

Architecture

content--paulserban.eu (private Git)
        │  content-sync
        ▼
 content/live/…/publish/
        │
   ┌────┼──────────────┐
   ▼    ▼              ▼
mdx-ingest  json-ingest  news-ingest
   └────┬──────────────┘
        ▼
 database/output/content.db   <- Drizzle schema (shared/db-schema)
        │
   ┌────┼──────────┬────────────┐
   ▼    ▼          ▼            ▼
portfolio  blog  news-feed   quiz-export -> public/data/*.json
 (Astro)  (Astro) (Astro)         │
                                  ▼
                            quiz-web-app (CSR PWA)

CI ingests once, publishes content-db (and quiz data) as artifacts, then builds four surfaces in parallel before merge/deploy.

Approach

As architect and lead implementer, I owned the seams between authoring, storage, and delivery - so each surface could evolve without inventing its own content story.

Platform boundaries first

• Two-repo model - content releases independently of app releases; CI pulls with a token instead of coupling writers to frontend deploys.
• SQLite as a build artifact, not a server - zero runtime database, CDN-friendly static output, one file shared by three Astro builds.
• Schema package split - shared--db-schema holds tables/types without bundling the native driver; shared--db owns better-sqlite3, pragmas, migrations, and upserts.
• Ingest ≠ render - MDX bodies are stored raw; Astro and shared--markdown compile later so one authoring format can feed multiple renderers.
• Dedicated tools per format - MDX, JSON, RSS, and quiz JSON delivery are separate CLIs. Adding a surface is a mapping, not a rewrite of a monolith.

Schema that can grow

The schema evolved in reviewable migrations as the product did - normalised tags, question types and options, project case-study fields, companion cheat sheets / learning plans, then news items. Content shape changes are diffs, not tribal knowledge.

Questions stay co-located with posts via a slug convention ({post-slug}--{uid}) and a foreign key to posts.slug, so study material cannot silently detach from the article it teaches.

CMS-ready writes before there is a CMS

Every writable content row carries sync_source (mdx | json | rss) and locked. Upserts go through upsertWithLockCheck: if a row is locked (future CMS-owned), the pipeline skips it instead of clobbering editorial edits. That is architectural foresight - a state machine for conflict resolution without shipping a CMS UI in v0.1.

Operational design

• pnpm start runs the DAG of sync -> ingest -> quiz-export via workspace dependencies and a shared task manager.
• News is decoupled - a daily workflow refreshes an RSS cache in the app repo, then ingest maps cache -> news_items with retention pruning, so the content repo is not on the hot path for headlines.
• Local domain parity - the same content.db mounts into a Traefik HTTPS stack that mirrors production subdomains, so cross-site navigation and build consumers are validated before CI.

Stack

Design decisions that mattered

• JAMstack over SSR for v0.1. Personal publishing traffic does not justify a runtime content API. Build-time queries keep hosting cheap and failure modes simple.
• Drizzle over heavier ORMs. Schema stays SQL-shaped and migration-first; enough control for lock semantics and ingest tooling without fighting an abstraction.
• One artifact, four consumers. Portfolio, blog, and news-feed speak Drizzle at generate time; quiz speaks JSON at runtime. The export step is an explicit delivery boundary, not an accidental second database.
• Fan-out CI. Ingest is the expensive consistency point. Building four apps against one artifact avoids four divergent content pulls and makes "what shipped" auditable.
• Trade-off named up front. One-way sync until a CMS exists; no DB->MDX round-trip yet; pipeline authority yields only when locked says so.

What shipped

• End-to-end pipeline: content-sync, mdx-ingest, json-ingest, news-sync/ingest, quiz-export
• Shared schema with nine migrations tracking real product growth
• Four live surfaces from one database: paulserban.eu, blog, news-feed, quiz
• DEV path that ingests live content and deploys a merged GitHub Pages preview; TEST path with fixture content to per-domain S3/CloudFront
• Lock-aware upsert contract ready for CMS-owned rows
• Adjacent platform pieces - shared newspaper UI kit, Traefik local mesh, Changesets release flow - designed as one monorepo, not four brochure sites

Outcome

Publishing collapsed to a single pipeline invocation. Adding a content type is a schema migration plus an ingest mapping - not bespoke glue per surface. The architecture treats a personal site like a small product platform: clear package boundaries, an evolvable data contract, CI that fans out from one artifact, and write semantics that leave room for a CMS without a rewrite.

Part of prj--personal-portfolio--v3

This pipeline is the content backbone of the monorepo. Related pieces:

• Spaced-repetition quiz PWA - primary consumer of quiz-export
• Shared newspaper design system used across every surface
• Local HTTPS Traefik + Docker Compose stack that mirrors production domains on one machine
• AWS multi-subdomain hosting - Route 53 -> CloudFront -> private S3 for the four live surfaces
• Multi-env CI/CD & Terraform delivery - ingest once, parallel builds, per-domain S3/CloudFront deploys across DEV -> TEST -> STAGE -> PROD