jekcms Database Schema: What's Actually in It

The central table is posts, which stores both blog posts and pages in a single table using a type column — ENUM('post', 'page'), not an open-ended set of custom content types. This keeps the schema simple: category and tag relationships, comments, votes, and SEO meta all point at one posts.id regardless of whether the row is a blog post or a static page. The trade-off is that most content queries carry a WHERE type = 'post' clause, which is why type gets its own index rather than relying on a scan.

Post Meta: Flexibility vs. Performance

Post meta lives in post_meta, a key-value table linked to posts.id. This design maximises flexibility — adding a new meta field requires no schema change — but it has a known performance cost: fetching a post and its meta always requires either a second query or a JOIN. jekcms mitigates this by batch-loading meta for a set of post IDs in a single query rather than one query per post, so listing pages don't pay the cost N times over.

Settings Table: Site-Wide Configuration

The settings table stores site-wide configuration as grouped key-value rows — each row has a group, a key, a typed value, and an autoload flag. Rows marked for autoload are pulled into memory in a single query at bootstrap, so frequently-read settings (site name, active theme, cache flags) cost nothing extra per request; settings you rarely read can skip autoload and get fetched on demand instead.

Tables That Cause Problems at Scale

The table most likely to cause performance problems on large installations is post_meta — the default schema indexes post_id and meta_key separately rather than as a single compound index, so a lookup for a specific meta key on a specific post still has to intersect two indexes instead of hitting one. On sites with heavy meta usage (SEO fields, custom fields, revision data), adding a compound index is worth doing yourself; it isn't there by default. sessions is the other one to watch — rows accumulate if old sessions aren't pruned periodically.

The Table List

jekcms's schema is close to two dozen tables, organised by purpose rather than by a WordPress-style content/taxonomy/meta split:

Content: posts, post_meta, categories, tags,
 post_categories, post_tags, comments, media
SEO & engagement: seo_meta, post_votes, post_revisions
Users & access: users, sessions, api_tokens, blocked_ips
Site structure: settings, themes, menus, menu_items, redirects
Automation: scheduled_tasks, automation_logs,
 social_queue, analytics_cache, newsletter_subscribers

A separate content_queue table is added by the content wizard's own setup step rather than the base schema — it's only present if you've used that feature.

Growth Patterns

• Content tables grow linearly — 30 posts/month adds ~30 rows to posts, and however many meta keys each post uses to post_meta
• The media table doesn't spawn a separate row per thumbnail size — each upload is one row, and its generated size variants (seven, from thumbnail to Open Graph) are stored as JSON in a thumbnails column on that same row
• sessions and file-based page cache entries grow unbounded without periodic cleanup

The posts Table, Simplified

CREATE TABLE posts (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 author_id INT UNSIGNED NOT NULL,
 title VARCHAR(255) NOT NULL,
 slug VARCHAR(255) NOT NULL,
 content LONGTEXT,
 type ENUM('post','page') NOT NULL DEFAULT 'post',
 status ENUM('draft','pending','published','scheduled','trash') DEFAULT 'draft',
 visibility ENUM('public','private','password') DEFAULT 'public',
 published_at DATETIME NULL,
 content_modified_at DATETIME NULL,
 UNIQUE KEY uk_slug (slug),
 KEY idx_status (status),
 KEY idx_type (type),
 KEY idx_published (published_at),
 FULLTEXT KEY ft_search (title, content)
);

Note that the slug is unique across the whole table, not scoped per type — a post and a page can't share a slug, which keeps URL resolution unambiguous without needing to know the content type up front. content_modified_at is worth calling out: it's tracked separately from the generic updated_at timestamp specifically so that routine system touches (view counters, background SEO passes) don't masquerade as a real content update in your sitemap or structured data.

The Most Impactful Optional Index

ALTER TABLE post_meta ADD INDEX idx_post_meta_lookup (post_id, meta_key);

This isn't part of the shipped schema, but on a site with tens of thousands of meta rows, adding it is usually the single cheapest query-performance win available — the built-in idx_post and idx_key indexes already narrow the search a lot, so the compound index is a refinement rather than a fix for a missing index entirely.

Why a Key-Value Table Instead of JSON Columns?

Shared hosting frequently runs MySQL 5.6/5.7 or MariaDB versions with limited or no JSON support. The key-value pattern in post_meta works identically across all of them, which matters for a product that has to run unmodified across whatever database version a customer's host happens to offer. Where jekcms controls both sides of the data — like the size variants on a media row — it does use a native JSON column, since that data isn't arbitrary third-party meta and doesn't need the same backward compatibility guarantee.

Backups and Schema Changes

• jekcms doesn't track schema changes in its own database table — there's no migrations table to manage. The installed version is recorded in a version.json file, and schema changes for existing sites ship as part of normal core updates, applied through the signed-manifest updater rather than a numbered-migration runner.
• Use --single-transaction for mysqldump exports to avoid locking InnoDB tables during a live backup
• content_queue, where present, can be safely truncated once a batch has finished processing

Avoiding N+1 Queries on Listing Pages

The naive way to render a list of 20 posts with their meta is one query for the posts plus one query per post for its meta — 21 queries minimum, more if each post needs several meta keys. jekcms avoids this by loading all the post IDs first, then pulling every relevant post_meta row for that whole batch in a single query:

SELECT post_id, meta_key, meta_value
FROM post_meta
WHERE post_id IN (?, ?, ?, ...)
ORDER BY post_id, meta_key;

The result is grouped into an in-memory array keyed by post_id, so a listing page's meta cost stays at two queries — one for the posts, one for all of their meta — regardless of how many posts are on the page.