The central table is posts, which stores content for all content types — blog posts, pages, and custom types — in a single table with a type discriminator column. This keeps joins simple and allows cross-type queries without UNION operations. The trade-off is that category-specific queries always include a WHERE type = 'post' clause, which requires indexing the column correctly.

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 pre-loads post meta into an in-memory array on the first access per request and caches subsequent reads from that array.

Options Table: Fast Site Configuration

The options table stores site-wide configuration as key-value pairs, similar to WordPress's wp_options. Frequently accessed options (site name, active theme, cache settings) are loaded into memory on every request via a single SELECT at bootstrap. This means changing a site option is reflected on the next request without any cache invalidation step.

Tables That Cause Problems at Scale

The tables most likely to cause performance problems on large installations are post_meta (the default schema lacks a compound index on (post_id, meta_key)) and sessions (rows accumulate if the cleanup cron job is not configured). Both have documented optimisation steps in the performance section of the administration guide.

The Complete Table List

Content (6): posts, post_meta, categories, post_categories, tags, post_tags
Users (3): users, user_meta, sessions
Media (2): media, media_meta
Taxonomy (3): taxonomies, terms, term_relationships
System (7): options, redirects, cache, migrations,
 api_tokens, audit_log, content_queue

Growth Patterns

  • Content tables grow linearly — 30 posts/month adds ~30 rows to posts, 150-300 to post_meta
  • Media tables grow faster: each image generates 4 thumbnail variants in media_meta
  • sessions and cache grow unbounded without the cleanup cron job

The posts Table Schema

CREATE TABLE posts (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 type VARCHAR(20) NOT NULL DEFAULT 'post',
 title VARCHAR(500) NOT NULL,
 slug VARCHAR(500) NOT NULL,
 content LONGTEXT,
 status ENUM('draft','published','scheduled','trashed') DEFAULT 'draft',
 author_id INT UNSIGNED NOT NULL,
 published_at DATETIME NULL,
 INDEX idx_type_status (type, status),
 INDEX idx_slug (slug),
 UNIQUE INDEX idx_unique_slug (slug, type)
);

The Most Impactful Missing Index

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

On a site with 50,000 meta rows, this single change reduced page generation from 120ms to 35ms.

Why Not JSON Columns?

Shared hosting frequently runs MySQL 5.6 or MariaDB versions lacking full JSON support. The key-value pattern works on MySQL 5.5-8.0 and all MariaDB versions. Sites on MySQL 8.0+ can enable JSON-based meta via META_STORAGE=json.

Backup and Migration

  • migrations tracks applied schema changes — never delete rows from it
  • audit_log can exceed 100,000 rows; archive entries older than 90 days
  • Use --single-transaction for dumps to avoid locking InnoDB tables
  • content_queue can be safely truncated after processing completes

Query Optimization Patterns

The most expensive query in a typical JekCMS installation is the homepage post listing with category names and author data.

Without optimization, this requires three separate queries or a multi-table JOIN that scans the entire posts table. JekCMS solves this with a single indexed query using a covering index on (status, type, published_at) combined with LEFT JOINs to categories and users. The result is cached for 5 minutes in production, reducing database load by approximately 85% on high-traffic homepages.

The N+1 Problem in Post Listings

Fetching post meta for each post in a listing creates the classic N+1 query problem. If a page shows 20 posts and each needs 3 meta values, that is 60 additional queries per page load. JekCMS prevents this by batch-loading all meta for a set of post IDs 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 results are grouped into an associative array keyed by post_id. Subsequent calls to get_post_meta($id, $key) read from this in-memory cache rather than hitting the database. This pattern reduces the homepage query count from 60+ to exactly 2 regardless of how many posts are displayed.

Schema Versioning and Migrations

Every schema change is tracked in the migrations table with a timestamp-based filename. When JekCMS boots, it compares the list of applied migrations against the files in database/migrations/ and runs any pending ones inside a transaction.

If a migration fails, the transaction rolls back and the error is logged to logs/migration.log with the full SQL statement that caused the failure. This approach makes deployments repeatable: you can deploy the same codebase to 50 sites and each one will independently apply only the migrations it has not yet run.

  • Migration filenames follow YYYY_MM_DD_HHMMSS_description.sql format
  • Each migration runs inside START TRANSACTION / COMMIT
  • Failed migrations are recorded with status failed and must be manually resolved
  • The db_version in version.json is incremented after successful batch runs