Problem
Content lived in multiple places that drifted apart: a portfolio, a blog, a news digest, and a quiz app all needed the same material. Every edit meant copying prose between repos, and "publish" was a manual, error-prone ritual.
Approach
I designed a build-time pipeline as a pnpm monorepo. A sync step clones the private content repo; dedicated ingest tools (mdx-ingest, json-ingest, news-ingest) parse MDX frontmatter and JSON into a SQLite database via Drizzle; the Astro sites query that database at build time and emit static HTML. The same database is exported to static JSON for the quiz PWA via quiz-export, so there is exactly one source of truth.
Key decisions:
- SQLite as a build artifact, not a server - zero runtime database, fully static output.
- Schema-first with Drizzle migrations, so content shape changes are reviewable diffs.
- Lock-aware upserts so future CMS-owned rows are never clobbered by the pipeline.
- Dedicated ingest/export tools per content type, so adding a surface is a mapping, not a rewrite.
Outcome
Publishing collapsed to a single command. Adding a new content type is a schema migration plus an ingest mapping - no bespoke glue per surface.
Part of prj--personal-portfolio--v3
This pipeline is the content backbone of prj--personal-portfolio--v3, a TypeScript pnpm monorepo that ships four frontends from one SQLite artifact. The rest of the platform includes:
- Astro portfolio (paulserban.eu) and blog (blog.paulserban.eu) sites, plus an Astro news-feed digest site
- A spaced-repetition quiz PWA with dual SM-2 / FSRS schedulers and offline support
- A shared newspaper design system UI kit used across every surface
- A local HTTPS Traefik + Docker Compose stack that mirrors production domains on one machine
- CI/CD that ingests content, builds all four surfaces in parallel, and deploys a merged GitHub Pages preview