Blog (relational)
File: 01-blog.xdbml · Target: PostgreSQL relational
An entry-level relational schema covering users, posts, and comments. Demonstrates basic entities, foreign keys, indexes, and validation patterns.
Source
xdbml
xdbml: 0.3
Project blog {
targets: PostgreSQL
Note: '''
A minimal relational blog schema covering users, posts, and comments.
This is the simplest example in the xDBML examples corpus -- a single
container, three entities, two foreign-key relationships, and a
small handful of indexes. Intended as a first-look reference for
anyone learning xDBML.
'''
}
Container blog_app [type: schema] {
Note: '''
The default application schema. All user-facing entities live here.
In a production deployment this schema would also carry session
storage, password reset tokens, and audit log entities -- omitted
here for brevity.
'''
Entity users {
Note: 'Registered users. One row per account.'
id int [pk, increment, note: 'Surrogate primary key']
email varchar [unique, not null,
pattern: '^[^@]+@[^@]+\.[^@]+$',
maxLength: 255,
note: 'Login identifier; lowercased on insert by application code']
display_name varchar [not null,
minLength: 1,
maxLength: 80,
note: 'Public-facing name; may differ from real name']
bio varchar [note: 'Optional self-description, plain text, no HTML allowed']
created_at timestamp [not null,
default: `CURRENT_TIMESTAMP`,
granularity: second,
note: 'Account creation timestamp; UTC']
is_admin boolean [not null,
default: false,
note: 'Site administrators see moderation tools and can ban accounts']
indexes {
email [unique]
created_at
}
}
Entity posts {
Note: '''
Blog posts authored by users. Posts are either draft or published.
Draft posts are visible only to their author; published posts are
visible to everyone.
'''
id int [pk, increment]
author_id int [not null, note: 'References users.id; the author of the post']
title varchar [not null,
minLength: 1,
maxLength: 200,
note: 'Plain text title; markdown not allowed here']
slug varchar [unique,
not null,
pattern: '^[a-z0-9-]+$',
note: 'URL-safe identifier; auto-generated from title on creation']
body varchar [not null, note: 'Markdown content; rendered to HTML at read time']
status varchar [not null,
enum: ['draft', 'published', 'archived'],
default: 'draft',
note: 'Lifecycle stage of the post']
published_at timestamp [granularity: second,
note: 'When the post was first published; null for drafts']
created_at timestamp [not null,
default: `CURRENT_TIMESTAMP`,
granularity: second]
updated_at timestamp [not null,
default: `CURRENT_TIMESTAMP`,
granularity: second,
note: 'Updated on every modification by an application trigger']
indexes {
slug [unique]
author_id
(status, published_at)
published_at
}
}
Entity comments {
Note: '''
Reader comments on posts. Comments are flat (no threading) in v1.
A future version may add a parent_comment_id field for replies.
'''
id int [pk, increment]
post_id int [not null, note: 'References posts.id; the post being commented on']
author_id int [not null, note: 'References users.id; the comment author']
body varchar [not null,
minLength: 1,
maxLength: 5000,
note: 'Plain text; no markdown or HTML rendering']
created_at timestamp [not null,
default: `CURRENT_TIMESTAMP`,
granularity: second]
indexes {
(post_id, created_at)
author_id
}
}
}
// Relationships
Ref: blog_app.posts.author_id > blog_app.users.id [source: '0..*', target: '1..1']
Ref: blog_app.comments.post_id > blog_app.posts.id [source: '0..*', target: '1..1', delete: cascade]
Ref: blog_app.comments.author_id > blog_app.users.id [source: '0..*', target: '1..1']