CMS Blog Schema — StealThis.dev

Code

create table authors (
  id bigint primary key generated always as identity,
  email text not null unique,
  full_name text not null,
  bio text,
  created_at timestamptz not null default now()
);

create table posts (
  id bigint primary key generated always as identity,
  author_id bigint not null references authors(id),
  slug text not null unique,
  title text not null,
  excerpt text,
  body_md text not null,
  status text not null check (status in ('draft','review','published','archived')),
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table tags (
  id bigint primary key generated always as identity,
  slug text not null unique,
  label text not null
);

create table post_tags (
  post_id bigint not null references posts(id) on delete cascade,
  tag_id bigint not null references tags(id) on delete cascade,
  primary key (post_id, tag_id)
);

create table media_assets (
  id bigint primary key generated always as identity,
  author_id bigint not null references authors(id),
  url text not null,
  kind text not null check (kind in ('image','video','file')),
  alt_text text,
  created_at timestamptz not null default now()
);

create table post_media (
  post_id bigint not null references posts(id) on delete cascade,
  media_id bigint not null references media_assets(id) on delete cascade,
  role text not null check (role in ('cover','inline','attachment')),
  primary key (post_id, media_id)
);

create index idx_posts_status_published_at on posts(status, published_at desc);
create index idx_posts_author_created_at on posts(author_id, created_at desc);

insert into authors (email, full_name, bio)
values
  ('writer@cms.dev', 'Writer One', 'Writes about engineering systems'),
  ('editor@cms.dev', 'Editor Two', 'Focus on content quality');

insert into posts (author_id, slug, title, excerpt, body_md, status, published_at)
values
  (1, 'intro-to-schema-design', 'Intro to Schema Design', 'How to model entities and constraints', '# Intro\nModel entities first.', 'published', now() - interval '2 days'),
  (2, 'draft-performance-notes', 'Draft: Performance Notes', 'Index planning basics', '# Draft\nTBD.', 'draft', null);

insert into tags (slug, label)
values
  ('database', 'Database'),
  ('sql', 'SQL'),
  ('architecture', 'Architecture');

insert into post_tags (post_id, tag_id)
values
  (1, 1),
  (1, 2),
  (2, 3);

insert into media_assets (author_id, url, kind, alt_text)
values
  (1, 'https://cdn.example.com/cover-schema.png', 'image', 'Schema cover image');

insert into post_media (post_id, media_id, role)
values
  (1, 1, 'cover');

-- 1) Latest published posts with author
select
  p.slug,
  p.title,
  a.full_name as author,
  p.published_at
from posts p
join authors a on a.id = p.author_id
where p.status = 'published'
order by p.published_at desc
limit 20;

-- 2) Post count by tag
select
  t.slug,
  t.label,
  count(pt.post_id) as post_count
from tags t
left join post_tags pt on pt.tag_id = t.id
group by t.id
order by post_count desc, t.slug;

-- 3) Assets attached to published posts
select
  p.slug,
  m.url,
  pm.role
from post_media pm
join posts p on p.id = pm.post_id
join media_assets m on m.id = pm.media_id
where p.status = 'published';

erDiagram
  authors ||--o{ posts : writes
  posts ||--o{ post_tags : tagged_with
  tags ||--o{ post_tags : references
  authors ||--o{ media_assets : uploads
  posts ||--o{ post_media : uses
  media_assets ||--o{ post_media : attached_to

  authors {
    bigint id PK
    text email
  }

  posts {
    bigint id PK
    bigint author_id FK
    text slug
    text status
    timestamptz published_at
  }

  tags {
    bigint id PK
    text slug
    text label
  }

  post_tags {
    bigint post_id PK,FK
    bigint tag_id PK,FK
  }

  media_assets {
    bigint id PK
    bigint author_id FK
    text url
    text kind
  }

  post_media {
    bigint post_id PK,FK
    bigint media_id PK,FK
    text role
  }

Content publishing model for blog and editorial workflows.