Blog CMS database schema
A schema for a content management system with posts and drafts, tagging, authorship, categories, and SEO fields. Supports scheduled publishing and revisions.
Database schema previewBlog CMS database schema
Recreate this in Raltey
Copy this prompt
Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.
Prompt
Design a PostgreSQL database schema for: Blog CMS database schema. A schema for a content management system with posts and drafts, tagging, authorship, categories, and SEO fields. Supports scheduled publishing and revisions. Include these tables: author, category, tag, post, post_tag, post_revision. Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.
Context
- - Single publication, many authors.
- - Post revisions kept for audit + rollback.
- - SEO fields (slug, meta title, meta description, canonical) live on the post.
Patterns
- - Scheduled publishing via a cron that promotes status from scheduled → published when published_at <= now().
- - Revisions written on every save; reads always come from post.body.
- - Index post(status, published_at DESC) for the public feed.
Tables
The schema, table by table
author
DBML - author
Table author {
id uuid pk
name text not null
bio text
avatar_url text
}category
DBML - category
Table category {
id uuid pk
slug text unique not null
name text not null
}tag
DBML - tag
Table tag {
id uuid pk
slug text unique not null
name text not null
}post
DBML - post
Table post {
id uuid pk
slug text unique not null
title text not null
excerpt text
body text
author_id uuid [ref: > author.id, not null]
category_id uuid [ref: > category.id]
status text default `'draft'` // draft, scheduled, published
published_at timestamptz
meta_title text
meta_description text
canonical_url text
created_at timestamptz default `now()`
updated_at timestamptz default `now()`
}post_tag
DBML - post_tag
Table post_tag {
post_id uuid [ref: > post.id]
tag_id uuid [ref: > tag.id]
pk (post_id, tag_id)
}post_revision
Append-only revisions for rollback + audit.
DBML - post_revision
Table post_revision {
id uuid pk
post_id uuid [ref: > post.id]
body text not null
author_id uuid [ref: > author.id]
created_at timestamptz default `now()`
}Relationships
| From | To | Type |
|---|---|---|
| author | post | 1:N |
| category | post | 1:N |
| post | post_tag | 1:N |
| tag | post_tag | 1:N |
| post | post_revision | 1:N |
Notes from the field
- Keep canonical_url nullable - default is the post's own URL.
- Add a unique index on slug - a duplicate slug is the most common silent bug in CMSes.
Frequently asked
Where should SEO fields live - on post or in a separate table?
On post, unless you support multiple locales per post. SEO fields are 1:1 with the post; a join table just adds latency.
How do I do scheduled publishing?
Store published_at on post and have a cron job (or Postgres pg_cron) promote scheduled posts to published when the timestamp passes. The status column avoids ambiguity.
More templates
FlowchartSaaS onboarding email flowFlowchartCustomer support runbook flowFlowchartPayment webhook retry and error flowFlowchartGitHub PR review and merge flowFlowchartLogin with magic link flowArchitectureThree-tier SaaS architecture on AWSArchitectureMicroservices architecture on KubernetesArchitectureReal-time data pipeline on GCP
Open this template in Raltey
Edit it on the canvas, ask the AI agent to extend it, then share or export.
Start for free