orion/app/modules/cms/docs/data-model.md

CMS Data Model

Entity relationships and database schema for the CMS module.

Entity Relationship Overview

Platform 1──* ContentPage
Store    1──* ContentPage
Store    1──* MediaFile
Store    1──1 StoreTheme

Models

ContentPage

Multi-language content pages with platform/store hierarchy. Pages can be platform marketing pages, store defaults, or store-specific overrides.

Field	Type	Constraints	Description
id	Integer	PK	Primary key
platform_id	Integer	FK, not null, indexed	Platform this page belongs to
store_id	Integer	FK, nullable, indexed	Store association (null = platform/default)
is_platform_page	Boolean	not null, default False	Platform marketing page vs store default
slug	String(100)	not null, indexed	Page identifier (about, faq, contact, etc.)
title	String(200)	not null	Page title
content	Text	not null	HTML or Markdown content
content_format	String(20)	default "html"	Format: html, markdown
template	String(50)	default "default"	Template: default, minimal, modern, full
sections	JSON	nullable	Structured homepage sections with i18n
title_translations	JSON	nullable	Language-keyed title dict {en, fr, de, lb}
content_translations	JSON	nullable	Language-keyed content dict {en, fr, de, lb}
meta_description	String(300)	nullable	SEO meta description
meta_keywords	String(300)	nullable	SEO keywords
is_published	Boolean	not null, default False	Publication status
published_at	DateTime	nullable, tz-aware	Publication timestamp
display_order	Integer	not null, default 0	Menu/footer ordering
show_in_footer	Boolean	not null, default True	Footer visibility
show_in_header	Boolean	not null, default False	Header navigation
show_in_legal	Boolean	not null, default False	Legal bar visibility
created_at	DateTime	tz-aware	Record creation time
updated_at	DateTime	tz-aware	Record update time
created_by	Integer	FK, nullable	Creator user ID
updated_by	Integer	FK, nullable	Updater user ID

Unique Constraint: (platform_id, store_id, slug) Composite Indexes: (platform_id, store_id, is_published), (platform_id, slug, is_published), (platform_id, is_platform_page)

Page tiers: platform → store_default (store_id null, not platform) → store_override (store_id set)

MediaFile

Media files (images, videos, documents) managed per-store.

Field	Type	Constraints	Description
id	Integer	PK	Primary key
store_id	Integer	FK, not null, indexed	Store owner
filename	String(255)	not null, indexed	UUID-based stored filename
original_filename	String(255)	nullable	Original upload name
file_path	String(500)	not null	Relative path from uploads/
media_type	String(20)	not null	image, video, document
mime_type	String(100)	nullable	MIME type
file_size	Integer	nullable	File size in bytes
width	Integer	nullable	Image/video width in pixels
height	Integer	nullable	Image/video height in pixels
thumbnail_path	String(500)	nullable	Path to thumbnail
alt_text	String(500)	nullable	Alt text for images
description	Text	nullable	File description
folder	String(100)	default "general"	Folder: products, general, etc.
tags	JSON	nullable	Tags for categorization
extra_metadata	JSON	nullable	Additional metadata (EXIF, etc.)
is_optimized	Boolean	default False	Optimization status
optimized_size	Integer	nullable	Size after optimization
usage_count	Integer	default 0	Usage tracking
created_at	DateTime	tz-aware	Record creation time
updated_at	DateTime	tz-aware	Record update time

Composite Indexes: (store_id, folder), (store_id, media_type)

StoreTheme

Per-store theme configuration including colors, fonts, layout, and branding. One-to-one with Store.

Field	Type	Constraints	Description
id	Integer	PK	Primary key
store_id	Integer	FK, unique, not null	One-to-one with store
theme_name	String(100)	default "default"	Preset: default, modern, classic, minimal, vibrant
is_active	Boolean	default True	Theme active status
colors	JSON	default {...}	Color scheme: primary, secondary, accent, background, text, border
font_family_heading	String(100)	default "Inter, sans-serif"	Heading font
font_family_body	String(100)	default "Inter, sans-serif"	Body font
logo_url	String(500)	nullable	Store logo path
logo_dark_url	String(500)	nullable	Dark mode logo
favicon_url	String(500)	nullable	Favicon path
banner_url	String(500)	nullable	Homepage banner
layout_style	String(50)	default "grid"	Layout: grid, list, masonry
header_style	String(50)	default "fixed"	Header: fixed, static, transparent
product_card_style	String(50)	default "modern"	Card: modern, classic, minimal
custom_css	Text	nullable	Custom CSS overrides
social_links	JSON	default {}	Social media URLs
meta_title_template	String(200)	nullable	SEO title template
meta_description	Text	nullable	SEO meta description
created_at	DateTime	tz-aware	Record creation time
updated_at	DateTime	tz-aware	Record update time

Design Patterns

• Three-tier content hierarchy: Platform pages → store defaults → store overrides
• JSON translations: Title and content translations stored as JSON dicts with language keys
• Media organization: Files organized by store and folder with type classification
• Theme presets: Named presets with full customization via JSON color scheme and CSS overrides
• SEO support: Meta description, keywords, and title templates on pages and themes