Blog System — Database Reference

Last Updated: 2026-06-28 Status: Active

Source of truth: apps/blog-service/src/db/schema.ts ORM: Drizzle ORM (type-safe, SQL-first) DB: Neon PostgreSQL — two connections: BLOG_DATABASE_URL (blog tables) + CORE_DATABASE_URL (billing, shared core)

1. Tables at a Glance

2. blog_posts

The main table. Every post belongs to exactly one tenant; categories and tags are optional.

Indexes

Business Rules

• Slug is auto-generated from title: lowercase, non-alphanumeric → -, leading/trailing - stripped
• Slug uniqueness is checked within tenant_id — two tenants can have the same slug
• On collision: appends -1, -2, ... up to 100 attempts; falls back to 8-char UUID fragment
• Slug is re-generated when title changes — old URLs break; there is no redirect handling
• status transitions: draft ↔ published, draft ↔ scheduled, scheduled → published
• published_at is set to NOW() on publish, cleared to null on unpublish
• Public API only returns posts where published_at IS NOT NULL AND deleted_at IS NULL
• Soft-delete: DELETE /admin/posts/:id sets deleted_at; admin can view deleted posts via ?deleted=true; POST /admin/posts/:id/restore clears the tombstone. Slug collision on restore → auto-append suffix.
• Content HTML cache: content_html is written on every create/update. On public reads, if content_html IS NOT NULL AND content_html_version = RENDERER_VERSION, the cached HTML is served directly; otherwise it is live-rendered and backfilled asynchronously.

3. blog_categories

One category per post (a post has at most one primary category). Categories are tenant-scoped.

Indexes

Notes

• Deleting a category sets category_id = NULL on all affected posts (FK onDelete: SET NULL)
• No description or cover_image_url in the current schema (those are in the vision doc)
• No parent_id — categories are flat, not hierarchical (hierarchical is future)

4. blog_tags

Flat tag system. A post can have many tags via the junction table.

Indexes

Notes

• blog_tags has no created_at or updated_at columns — unlike categories. Known gap.
• Deleting a tag cascade-deletes all rows in blog_post_tags where tag_id matches

5. blog_post_tags

M:N junction table between posts and tags.

Primary key: composite (post_id, tag_id) — enforces uniqueness of the pair

Indexes

Notes

• Both cascades are onDelete: CASCADE — deleting a post removes all its tag junctions; deleting a tag removes all post associations
• Tag updates on a post are done as delete-all-then-insert, not diff. This is safe because the whole operation is in a transaction.

6. blog_overview_snapshots

Stale-while-revalidate (SWR) cache for the GET /admin/posts/overview endpoint. One row per tenant; recomputed in the background when stale.

SWR behavior:

• Fresh (now() - computed_at < 60s) → return immediately
• Stale → return stale payload + kick off background recompute via waitUntil()
• Missing → compute synchronously, persist, return

Invalidation: Any mutation (create, update, delete, restore, publish, unpublish, schedule, unschedule) resets computed_at = epoch to force a recompute on the next overview request.

7. Relation Map

blog_categories ──┐
                  │ one category (nullable FK)
blog_posts ───────┤
                  │ many tags (via junction)
blog_post_tags ───┤
                  │
blog_tags ────────┘
 
blog_overview_snapshots  (one row per tenant — independent cache table)

• One post → one category (or null)
• One post → many tags (via blog_post_tags)
• One category → many posts
• One tag → many posts (via blog_post_tags)

8. Migrations

Migration files live in apps/blog-service/drizzle/. They are applied with drizzle-kit.

Running Migrations

# From apps/blog-service/
npm run migrate        # Apply pending migrations
npm run generate       # Generate new migration from schema changes
npm run db:push        # Push schema directly (dev only — bypasses migration files)
npm run studio         # Open Drizzle Studio (DB browser)

Adding a New Column

1. Edit apps/blog-service/src/db/schema.ts
2. Run npm run generate — creates a new .sql file in drizzle/
3. Review the generated SQL
4. Run npm run migrate to apply

Never edit existing migration files. Always generate new ones.

9. Env Variables

The two databases can be in the same Neon project (separate databases) or different projects. They share no FK constraints — the relationship is resolved at runtime.