Blog System — Database Reference

Last Updated: 2026-05-06 Status: Active

Source of truth: apps/blog-service/src/db/schema.ts ORM: Drizzle ORM (type-safe, SQL-first) DB: Neon PostgreSQL (serverless)

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

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. Relation Map

blog_categories ──┐
                  │ one category (nullable FK)
blog_posts ───────┤
                  │ many tags (via junction)
blog_post_tags ───┤
                  │
blog_tags ────────┘

• 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)

7. 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.

8. Env Variables

Both must point to the same Neon project (different databases or branches are fine; the project-level connection pooler is shared).