Blog System

Full-featured blog CMS with multi-language support, organization blogs, authors, categories, and comments

Blog System

Concordia’s blog system provides a complete CMS for publishing articles with multi-language translations, author management, categorization, media galleries, and community engagement through comments.

Overview

The blog system is built from multiple schema files:

• blog_posts.schema.ts - Core article structure
• blog_authors.schema.ts - Author profiles
• blog_categories.schema.ts - Content categorization
• blog_translations.schema.ts - Multi-language content
• blog_comments.schema.ts - Community discussions
• blog_organization.schema.ts - Organization-level blogs

Blog Posts

From src/database/schemas/blog_posts.schema.ts:

Core Fields

id · text · required

Primary key identifier

slug · text · required

Unique URL-friendly identifier for the post

status · text · required

Publication status: draft, pending_review, published, archived, rejected

organizationId · text

Optional reference to organization that owns this post

publishedAt · timestamp

Publication date/time, set when status changes to published

Display Options

displayInHome · boolean · default: false

Whether to feature this post on the homepage

displayInBlog · boolean · default: true

Whether to show this post in the main blog listing

isFeatured · boolean · default: false

Mark post as featured for special prominence

Content Metadata

readingTime · text

Estimated reading time (e.g., “5 min”)

wordCount · text

Total word count of the article

timeRequired · text

ISO 8601 duration format for Schema.org compatibility

inLanguage · text · required

Primary language code (ISO 639-1)

Community Features

allowComments · boolean · default: true

Enable/disable comments on this post

discussionUrl · text

Link to external discussion (e.g., forum thread)

Schema.org Integration

license · text

Content license (e.g., “CC BY-SA 4.0”)

url · text

Canonical URL for this article

identifier · text

Unique identifier for Schema.org structured data

Multi-Language Translations

From src/database/schemas/blog_translations.schema.ts:

id · text · required

Translation entry ID

postId · text · required

Reference to parent blog post

inLanguage · text · required

Language code for this translation (ISO 639-1)

headline · jsonb · required

Article title/headline in this language

alternativeHeadline · jsonb

Alternative headline/subtitle

articleBody · jsonb · required

Full article content as structured JSON

excerpt · jsonb · required

Short excerpt or summary for listings

seoTitle · jsonb

SEO-optimized page title

seoDescription · jsonb

Meta description for search engines

seoKeywords · jsonb

SEO keywords array

canonicalUrl · jsonb

Canonical URL for this translation

Translations are indexed by post_id and in_language for efficient multilingual queries.

Authors

From src/database/schemas/blog_authors.schema.ts:

Identity Fields

id · text · required

Author unique identifier

slug · text · required

Unique URL slug for author profile page

givenName · jsonb

First name in multiple languages

familyName · jsonb

Last name in multiple languages

displayName · jsonb · required

Display name shown on articles (multilingual)

bio · jsonb

Author biography in multiple languages

jobTitle · jsonb

Professional title in multiple languages

Contact & Profile

email · text

Unique email address for the author

avatarId · text

Reference to blog_media table for avatar image

avatarUrl · text

Direct URL to avatar image

website · text

Author’s personal or professional website

sameAs · jsonb

Array of URLs for social media profiles (Schema.org)

Example: ["https://twitter.com/author", "https://linkedin.com/in/author"]

Organization Affiliation

worksForId · text

Reference to blog_organizations table

Authors can be affiliated with an organization, enabling organization blogs where multiple authors contribute under a shared brand.

Display Flags

displayInHome · boolean · default: false

Show author on homepage

displayInBlog · boolean · default: true

Show author in blog listings

isFeatured · boolean · default: false

Feature this author prominently

SEO Fields

seoTitle · jsonb

SEO title for author page

seoDescription · jsonb

Meta description for author page

seoKeywords · jsonb

Keywords for author page

canonicalUrl · jsonb

Canonical URL for author profile

Categories

From src/database/schemas/blog_categories.schema.ts:

id · text · required

Category identifier

slug · text · required

Unique URL slug

name · jsonb · required

Category name in multiple languages

description · jsonb

Category description in multiple languages

featuredImageId · text

Reference to featured image in blog_media

parentId · text

Parent category for hierarchical structure

displayInHome · boolean · default: false

Show category on homepage

displayInMenu · boolean · default: true

Show category in navigation menu

displayInBlog · boolean · default: true

Show category in blog section

isFeatured · boolean · default: false

Mark as featured category

Categories support hierarchy via the parentId field, allowing nested category structures.

Comments

From src/database/schemas/blog_comments.schema.ts:

Universal Comment System

id · text · required

Comment identifier

postId · text · required

ID of the entity being commented on

postType · text · required

Type of entity: blog, place, event, hike, classified

parentId · text

Parent comment ID for threaded discussions

Author Information

authorName · text · required

Commenter’s display name

authorEmail · text · required

Commenter’s email (for notifications, not displayed)

Content

content · jsonb · required

Comment text as structured JSON (supports rich formatting)

rating · integer · default: 0

Optional rating (0-5 stars) when comment is a review

inLanguage · text · required

Language code of the comment

Moderation

status · text · required

Moderation status: pending, approved, rejected

Comments default to pending status and require moderation approval before being visible.

Organization Blogs

From src/database/schemas/blog_organization.schema.ts, organizations can have their own blog channels:

Identity

id · text · required

Organization identifier

name · text · required

Organization name

slug · text · required

Unique URL slug

alternateName · jsonb

Alternative names array

description · jsonb

Description in multiple languages

url · text

Organization website

logo · text

Logo URL

image · text

Featured image URL

slogan · jsonb

Organization slogan in multiple languages

Contact Information

email · text

Primary email

telephone · text

Primary phone number

address · jsonb

Structured address object:

{
  "streetAddress": "123 Main St",
  "addressLocality": "Paris",
  "addressRegion": "Île-de-France",
  "postalCode": "75001",
  "addressCountry": "FR"
}

News Media Organization Fields

Concordia supports Schema.org NewsMediaOrganization properties for editorial transparency:

publishingPrinciples · text

Editorial standards and principles

correctionsPolicy · text

How corrections are handled

ethicsPolicy · text

Ethical guidelines

diversityPolicy · text

Diversity and inclusion policy

ownershipFundingInfo · text

Ownership and funding transparency

verificationFactCheckingPolicy · text

Fact-checking procedures

Relationships

From blog_posts.schema.ts, posts connect to:

Authors (Many-to-Many)

export const blogPostAuthors = pgTable("blog_post_authors", {
  postId: text("post_id").notNull(),
  authorId: text("author_id").notNull(),
});

Multiple authors can collaborate on a single post.

Categories (Many-to-Many)

export const blogPostCategories = pgTable("blog_post_categories", {
  postId: text("post_id").notNull(),
  categoryId: text("category_id").notNull(),
});

Media (Many-to-Many)

export const blogPostMedia = pgTable("blog_post_media", {
  postId: text("post_id").notNull(),
  mediaId: text("media_id").notNull(),
  type: text("type").notNull(),
  position: text("position"),
});

type · text · required

Media usage type: cover, inline, gallery

position · text

Position indicator for inline or gallery media

Database Indexes

Performance optimizations from blog_posts.schema.ts:

CREATE UNIQUE INDEX idx_blog_posts_slug ON blog_posts(slug);
CREATE INDEX idx_blog_posts_status ON blog_posts(status);
CREATE INDEX idx_blog_posts_published_at ON blog_posts(published_at);
CREATE INDEX idx_blog_posts_home ON blog_posts(display_in_home);
CREATE INDEX idx_blog_posts_featured ON blog_posts(is_featured);

CREATE INDEX idx_blog_post_authors_post ON blog_post_authors(post_id);
CREATE INDEX idx_blog_post_authors_author ON blog_post_authors(author_id);

Article Status Workflow

From concordia-specs.md (lines 748-757):

Best Practices

Multi-Language Content

1. Always create translations for all supported languages
2. Use JSONB fields for language-specific content
3. Set inLanguage to match the primary content language

SEO Optimization

1. Populate seoTitle, seoDescription, and seoKeywords for each translation
2. Use canonical URLs to avoid duplicate content issues
3. Set appropriate timeRequired and wordCount for Schema.org

Organization Blogs

1. Link authors to organizations via worksForId
2. Set organizationId on posts for organization attribution
3. Use organization branding (logo, slogan) consistently