Core Tables

Content Tables

posts

The primary content table.

Column	Type	Notes
`id`	INT UNSIGNED PK
`user_id`	INT UNSIGNED	FK to Shield `users.id`
`title`	VARCHAR(255)
`slug`	VARCHAR(255) UNIQUE	URL segment
`body`	LONGTEXT	Raw content (markdown or HTML)
`format`	ENUM('html','markdown')	Determines render pipeline
`excerpt`	TEXT NULL	Manual excerpt; fallback to auto-generated
`featured_image`	VARCHAR(512) NULL	Path or URL
`status`	ENUM('draft','published','scheduled')
`is_premium`	TINYINT(1)	Paywall flag
`meta_title`	VARCHAR(255) NULL	SEO override
`meta_description`	TEXT NULL	SEO override
`published_at`	DATETIME NULL	Null = draft
`scheduled_at`	DATETIME NULL	Future publish time
`share_on_publish`	TINYINT(1)	Auto-share to social on publish
`created_at`	DATETIME NULL
`updated_at`	DATETIME NULL
`deleted_at`	DATETIME NULL	Soft delete

pages

Static CMS pages. Same structure as posts minus category/tag joins. Adds:

Column	Type	Notes
`parent_id`	INT UNSIGNED NULL	For hierarchical pages
`template`	VARCHAR(100) NULL	Theme template override

posts_to_categories / tags_to_posts

Pivot tables. Both follow the same pattern:

posts_to_categories: post_id (FK), category_id (FK), PRIMARY KEY(post_id, category_id)
tags_to_posts:       tag_id (FK),  post_id (FK),     PRIMARY KEY(tag_id, post_id)

comments

Column	Type	Notes
`id`	INT UNSIGNED PK
`post_id`	INT UNSIGNED	FK to posts
`user_id`	INT UNSIGNED NULL	FK if authenticated commenter
`author_name`	VARCHAR(100)	Guest name
`author_email`	VARCHAR(255)
`body`	TEXT
`status`	ENUM('approved','pending','spam','trash')
`parent_id`	INT UNSIGNED NULL	Threaded replies
`created_at`	DATETIME NULL

post_revisions

Stores previous versions of post body on each save.

Column	Type
`id`	INT UNSIGNED PK
`post_id`	INT UNSIGNED
`user_id`	INT UNSIGNED
`body`	LONGTEXT
`created_at`	DATETIME NULL

Appearance Tables

themes

Column	Type
`id`	INT UNSIGNED PK
`name`	VARCHAR(100)
`folder`	VARCHAR(100) UNIQUE
`version`	VARCHAR(20)
`author`	VARCHAR(100)
`status`	ENUM('active','inactive','unavailable')
`pv_safe`	TINYINT(1)
`created_at`	DATETIME NULL
`updated_at`	DATETIME NULL

theme_options

Key-value store for theme configuration. One row per option key per theme.

Column	Type
`id`	INT UNSIGNED PK
`theme_id`	INT UNSIGNED
`option_key`	VARCHAR(100)
`option_value`	TEXT

widgets

Column	Type
`id`	INT UNSIGNED PK
`name`	VARCHAR(100)
`slug`	VARCHAR(100) UNIQUE
`folder`	VARCHAR(100)
`version`	VARCHAR(20)
`description`	TEXT NULL
`status`	ENUM('active','inactive','unavailable')
`pv_safe`	TINYINT(1)
`created_at`	DATETIME NULL
`updated_at`	DATETIME NULL

widget_areas

Named slots defined by themes.

Column	Type
`id`	INT UNSIGNED PK
`name`	VARCHAR(100)
`slug`	VARCHAR(100) UNIQUE
`description`	TEXT NULL

widget_instances

A placed, configured widget assigned to an area.

Column	Type
`id`	INT UNSIGNED PK
`widget_id`	INT UNSIGNED
`area_id`	INT UNSIGNED
`options_json`	TEXT
`sort_order`	INT
`is_active`	TINYINT(1)
`created_at`	DATETIME NULL
`updated_at`	DATETIME NULL

System Tables

plugins

Column	Type
`id`	INT UNSIGNED PK
`name`	VARCHAR(100)
`folder`	VARCHAR(100) UNIQUE
`namespace`	VARCHAR(100)
`version`	VARCHAR(20)
`status`	ENUM('active','inactive','unavailable')
`pv_safe`	TINYINT(1)
`created_at`	DATETIME NULL
`updated_at`	DATETIME NULL

settings

Generic key-value store for CMS configuration.

Column	Type
`id`	INT UNSIGNED PK
`class`	VARCHAR(100)
`key`	VARCHAR(100)
`value`	TEXT NULL
`type`	VARCHAR(20)
`context`	VARCHAR(255) NULL

marketplace_items

Column	Type
`id`	INT UNSIGNED PK
`slug`	VARCHAR(100) UNIQUE
`type`	ENUM('theme','plugin','widget')
`installed_version`	VARCHAR(20)
`license_key`	VARCHAR(64) NULL
`created_at`	DATETIME NULL
`updated_at`	DATETIME NULL

marketplace_licenses

Column	Type
`id`	INT UNSIGNED PK
`marketplace_item_id`	INT UNSIGNED
`license_key`	VARCHAR(64)
`is_valid`	TINYINT(1)
`last_checked`	DATETIME NULL
`created_at`	DATETIME NULL
`updated_at`	DATETIME NULL

Analytics Tables

activity_logs

Column	Type
`id`	INT UNSIGNED PK
`user_id`	INT UNSIGNED
`username`	VARCHAR(100)
`action`	VARCHAR(100)
`subject_type`	VARCHAR(50)
`subject_id`	INT UNSIGNED
`description`	TEXT NULL
`ip_address`	VARCHAR(45)
`created_at`	DATETIME NULL

page_views

Column	Type
`id`	INT UNSIGNED PK
`post_id`	INT UNSIGNED NULL
`url`	VARCHAR(512)
`ip_hash`	VARCHAR(64)
`created_at`	DATETIME NULL

User Tables

author_profiles

Extension of Shield users with public-facing profile data.

Column	Type
`id`	INT UNSIGNED PK
`user_id`	INT UNSIGNED UNIQUE
`display_name`	VARCHAR(100)
`bio`	TEXT NULL
`avatar_url`	VARCHAR(512) NULL
`website`	VARCHAR(512) NULL
`twitter`	VARCHAR(100) NULL
`created_at`	DATETIME NULL
`updated_at`	DATETIME NULL

← Database Schema

Conventions →