Laravel 11 · MySQL 8 Specification Built 2026-10-10

03 — Database schema (MySQL 8)#

This document is normative. Migrations must match these names, types and indexes exactly.

Global conventions:

Engine InnoDB, charset utf8mb4, collation utf8mb4_0900_ai_ci.
Primary keys are BIGINT UNSIGNED AUTO_INCREMENT named id.
Timestamps are TIMESTAMP NULL (created_at, updated_at); soft deletes add deleted_at.
Money is not stored anywhere in KodePress. Durations are integers of seconds.
Every content table has site_id with a foreign key to sites, from the very first migration.
Foreign keys use ON DELETE CASCADE for owned children and ON DELETE SET NULL for references that should survive the loss of the target. Each table below states which.
JSON columns are never indexed directly; where a JSON value must be searched, it is also stored in a real column.
All user-entered HTML is sanitised before it reaches the database.

3.1 Table map#

Group	Tables
Tenancy	`sites`
Identity	`users`, `roles`, `permissions`, `model_has_roles`, `model_has_permissions`, `role_has_permissions`, `sessions`, `password_reset_tokens`
Content	`pages`, `page_versions`, `global_blocks`, `templates`
Taxonomy	`categories`, `category_page`, `tags`, `page_tag`
Navigation	`menus`, `menu_items`
Layout parts	`template_parts`, `template_part_versions`
Media	`media_folders`, `media`
Forms	`forms`, `form_submissions`
Design	`theme_settings`
SEO	`redirects`
Audit	`audit_logs`
Framework	`failed_jobs`, `cache_locks` (file cache needs no `cache` table)

Migration order matters. Three foreign-key pairs are circular and MySQL cannot create them inline:

Pair	Resolution
`pages.current_version_id` <-> `page_versions.page_id`	create both tables, then add `pages.current_version_id` FK in a later migration
`users.avatar_media_id` <-> `media.uploaded_by`	create `users` and `media` without them, then add both FKs
`pages.featured_media_id`, `pages.primary_category_id`, `pages.header_part_id`, `pages.footer_part_id`	add after `media`, `categories` and `template_parts` exist

So the migration sequence is: sites -> users (no media FK) -> spatie tables -> media_folders -> media -> categories -> tags -> template_parts -> pages (no version/media/part FKs) -> page_versions -> pivots -> menus -> menu_items -> template_part_versions -> global_blocks -> templates -> forms -> form_submissions -> theme_settings -> redirects -> audit_logs -> one final add_deferred_foreign_keys migration.

ER summary:

sites 1──n pages 1──n page_versions
            │  └── pages.current_version_id ──► page_versions.id   (nullable)
            ├──n category_page n──┐
            ├──n page_tag     n── tags
            └── parent_id (self)  categories ── parent_id (self)

sites 1──n menus 1──n menu_items ── parent_id (self)
sites 1──n template_parts 1──n template_part_versions
sites 1──n media_folders 1──n media
sites 1──n forms 1──n form_submissions
sites 1──1 theme_settings
sites 1──n redirects, templates, global_blocks, audit_logs

3.2 sites#

One row per website. A single-site install has exactly one row with is_default = 1.

CREATE TABLE sites (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name            VARCHAR(190)    NOT NULL,
  domain          VARCHAR(190)    NULL,              -- NULL = matches any host (single-site)
  path_prefix     VARCHAR(60)     NULL,              -- reserved for sub-folder sites
  default_locale  CHAR(2)         NOT NULL DEFAULT 'bn',
  locales         JSON            NOT NULL,          -- ["bn","en"]
  timezone        VARCHAR(64)     NOT NULL DEFAULT 'Asia/Dhaka',
  date_format     VARCHAR(32)     NOT NULL DEFAULT 'd M Y',
  is_default      TINYINT(1)      NOT NULL DEFAULT 0,
  is_active       TINYINT(1)      NOT NULL DEFAULT 1,
  options         JSON            NULL,              -- site info: logo ids, contact, social, analytics
  created_at      TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY sites_domain_unique (domain),
  KEY sites_is_default_index (is_default)
) ENGINE=InnoDB;

options keys (all optional): tagline, logo_media_id, logo_dark_media_id, favicon_media_id, contact_email, contact_phone, address, social ({facebook,youtube,...}), analytics_head, analytics_body, maintenance_mode.

3.3 users#

Breeze columns plus role-independent profile and 2FA fields.

CREATE TABLE users (
  id                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id                BIGINT UNSIGNED NULL,       -- NULL = can access every site (super admin)
  name                   VARCHAR(190)  NOT NULL,
  email                  VARCHAR(190)  NOT NULL,
  email_verified_at      TIMESTAMP     NULL,
  password               VARCHAR(255)  NOT NULL,
  avatar_media_id        BIGINT UNSIGNED NULL,
  bio                    TEXT          NULL,         -- shown on the author page
  locale                 CHAR(2)       NOT NULL DEFAULT 'bn',  -- admin UI language
  two_factor_secret      TEXT          NULL,
  two_factor_recovery    TEXT          NULL,
  two_factor_confirmed_at TIMESTAMP    NULL,
  last_login_at          TIMESTAMP     NULL,
  last_login_ip          VARCHAR(45)   NULL,
  is_active              TINYINT(1)    NOT NULL DEFAULT 1,
  remember_token         VARCHAR(100)  NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY users_email_unique (email),
  KEY users_site_id_foreign (site_id),
  CONSTRAINT users_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE SET NULL,
  CONSTRAINT users_avatar_media_id_foreign FOREIGN KEY (avatar_media_id) REFERENCES media (id) ON DELETE SET NULL
) ENGINE=InnoDB;

roles, permissions, model_has_roles, model_has_permissions, role_has_permissions are the stock spatie/laravel-permission tables, published unchanged. The role and permission names are listed in 11-roles-and-security.md.

sessions and password_reset_tokens are the stock Laravel tables. SESSION_DRIVER=database makes sessions load-bearing, so it is never truncated by a deploy step.

3.4 pages#

One table for pages, posts and landing pages. type discriminates. The published tree lives in page_versions; the unpublished tree lives in draft_content.

CREATE TABLE pages (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id             BIGINT UNSIGNED NOT NULL,
  type                ENUM('page','post','landing') NOT NULL DEFAULT 'page',
  locale              CHAR(2)       NOT NULL DEFAULT 'bn',
  translation_of      BIGINT UNSIGNED NULL,          -- groups translations of the same content
  parent_id           BIGINT UNSIGNED NULL,          -- page hierarchy (nested paths)
  title               VARCHAR(255)  NOT NULL,
  slug               VARCHAR(255)  NOT NULL,         -- last path segment only
  path                VARCHAR(255)  NOT NULL,        -- full resolved path, e.g. about/team
  excerpt             TEXT          NULL,
  featured_media_id   BIGINT UNSIGNED NULL,
  primary_category_id BIGINT UNSIGNED NULL,          -- for breadcrumbs and permalinks
  author_id           BIGINT UNSIGNED NULL,
  status              ENUM('draft','pending','scheduled','published') NOT NULL DEFAULT 'draft',
  layout              VARCHAR(60)   NOT NULL DEFAULT 'default',  -- resources/views/public/layouts
  header_part_id      BIGINT UNSIGNED NULL,          -- per-page override; see doc 08
  footer_part_id      BIGINT UNSIGNED NULL,
  header_mode         ENUM('inherit','none','custom') NOT NULL DEFAULT 'inherit',
  footer_mode         ENUM('inherit','none','custom') NOT NULL DEFAULT 'inherit',
  seo                 JSON          NULL,
  draft_content       JSON          NULL,            -- autosaved, unpublished tree
  current_version_id  BIGINT UNSIGNED NULL,          -- the published tree
  is_homepage         TINYINT(1)    NOT NULL DEFAULT 0,
  comment_count       INT UNSIGNED  NOT NULL DEFAULT 0,  -- reserved; comments are not in scope
  published_at        TIMESTAMP     NULL,            -- future value + status=scheduled = cron publishes
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY pages_site_locale_path_unique (site_id, locale, path),
  KEY pages_site_type_status_index (site_id, type, status, published_at),
  KEY pages_parent_id_index (parent_id),
  KEY pages_author_id_index (author_id),
  KEY pages_translation_of_index (translation_of),
  KEY pages_is_homepage_index (site_id, is_homepage),
  KEY pages_slug_index (site_id, slug),
  CONSTRAINT pages_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE,
  CONSTRAINT pages_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES pages (id) ON DELETE SET NULL,
  CONSTRAINT pages_author_id_foreign FOREIGN KEY (author_id) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT pages_featured_media_id_foreign FOREIGN KEY (featured_media_id) REFERENCES media (id) ON DELETE SET NULL,
  CONSTRAINT pages_primary_category_id_foreign FOREIGN KEY (primary_category_id) REFERENCES categories (id) ON DELETE SET NULL,
  CONSTRAINT pages_current_version_id_foreign FOREIGN KEY (current_version_id) REFERENCES page_versions (id) ON DELETE SET NULL,
  CONSTRAINT pages_header_part_id_foreign FOREIGN KEY (header_part_id) REFERENCES template_parts (id) ON DELETE SET NULL,
  CONSTRAINT pages_footer_part_id_foreign FOREIGN KEY (footer_part_id) REFERENCES template_parts (id) ON DELETE SET NULL
) ENGINE=InnoDB;

Rules:

path is derived: parent.path + "/" + slug, or just slug at the root. Posts use the permalink pattern from site options (default blog/{slug}) and store the result in path too, so the catch-all route is a single indexed lookup.
Changing slug or parent_id rewrites path for the page and all descendants, and inserts a 301 in redirects for every changed path.
is_homepage is unique per site in application logic (setting it clears the previous one).
Trash is deleted_at (soft delete). Restoring a page re-checks its path for a collision.
seo keys: title, description, og_media_id, canonical, noindex, nofollow.
comment_count exists so a comments plugin can land without a migration; core never writes it.

3.5 page_versions#

Append-only snapshots. Never updated after creation (except label).

CREATE TABLE page_versions (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id     BIGINT UNSIGNED NOT NULL,
  page_id     BIGINT UNSIGNED NOT NULL,
  version     INT UNSIGNED    NOT NULL,          -- 1,2,3 ... per page
  content     JSON            NOT NULL,          -- the block tree (doc 04)
  title       VARCHAR(255)    NOT NULL,          -- title at publish time
  seo         JSON            NULL,
  label       VARCHAR(190)    NULL,              -- optional user note
  created_by  BIGINT UNSIGNED NULL,
  created_at  TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY page_versions_page_version_unique (page_id, version),
  KEY page_versions_site_id_index (site_id),
  KEY page_versions_created_at_index (page_id, created_at),
  CONSTRAINT page_versions_page_id_foreign FOREIGN KEY (page_id) REFERENCES pages (id) ON DELETE CASCADE,
  CONSTRAINT page_versions_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB;

Retention: keep the latest kodepress.versions.keep (default 30) per page plus every labelled version; a nightly scheduled command prunes the rest.

3.6 global_blocks#

Reusable sections. Editing one updates every page that references it. A page references a global block with a block of type global whose data.global_block_id points here; the renderer inlines the stored tree.

CREATE TABLE global_blocks (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id     BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(190)    NOT NULL,
  slug        VARCHAR(190)    NOT NULL,
  content     JSON            NOT NULL,       -- a sections[] tree, usually one section
  status      ENUM('draft','published') NOT NULL DEFAULT 'published',
  usage_count INT UNSIGNED    NOT NULL DEFAULT 0,   -- maintained on publish, for the UI warning
  created_by  BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY global_blocks_site_slug_unique (site_id, slug),
  CONSTRAINT global_blocks_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
) ENGINE=InnoDB;

3.7 templates#

Saved designs: whole pages, single sections, headers or footers. Used by the template picker and the Add Section gallery. Seeded templates have is_builtin = 1 and cannot be deleted.

CREATE TABLE templates (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id         BIGINT UNSIGNED NULL,          -- NULL = shipped with KodePress, shared by all sites
  kind            ENUM('page','section','header','footer') NOT NULL,
  name            VARCHAR(190)    NOT NULL,
  category        VARCHAR(60)     NULL,          -- hero, features, pricing, faq, testimonial, cta, gallery, contact
  content         JSON            NOT NULL,
  thumbnail_path  VARCHAR(255)    NULL,
  is_builtin      TINYINT(1)      NOT NULL DEFAULT 0,
  position        INT             NOT NULL DEFAULT 0,
  created_by      BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY templates_site_kind_index (site_id, kind, category, position),
  CONSTRAINT templates_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
) ENGINE=InnoDB;

3.8 categories, tags and pivots#

Categories nest (for auto sub-menu from category children in doc 07). Tags are flat.

CREATE TABLE categories (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id       BIGINT UNSIGNED NOT NULL,
  parent_id     BIGINT UNSIGNED NULL,
  name          VARCHAR(190)    NOT NULL,
  slug          VARCHAR(190)    NOT NULL,
  description   TEXT            NULL,
  image_media_id BIGINT UNSIGNED NULL,
  seo           JSON            NULL,
  position      INT             NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY categories_site_slug_unique (site_id, slug),
  KEY categories_parent_id_index (parent_id),
  CONSTRAINT categories_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE,
  CONSTRAINT categories_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE tags (
  id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id  BIGINT UNSIGNED NOT NULL,
  name     VARCHAR(190)    NOT NULL,
  slug     VARCHAR(190)    NOT NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY tags_site_slug_unique (site_id, slug),
  CONSTRAINT tags_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE category_page (
  page_id     BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (page_id, category_id),
  KEY category_page_category_id_index (category_id),
  CONSTRAINT category_page_page_id_foreign FOREIGN KEY (page_id) REFERENCES pages (id) ON DELETE CASCADE,
  CONSTRAINT category_page_category_id_foreign FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE page_tag (
  page_id BIGINT UNSIGNED NOT NULL,
  tag_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (page_id, tag_id),
  KEY page_tag_tag_id_index (tag_id),
  CONSTRAINT page_tag_page_id_foreign FOREIGN KEY (page_id) REFERENCES pages (id) ON DELETE CASCADE,
  CONSTRAINT page_tag_tag_id_foreign FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
) ENGINE=InnoDB;

3.9 menus and menu_items#

CREATE TABLE menus (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id   BIGINT UNSIGNED NOT NULL,
  name      VARCHAR(190)    NOT NULL,
  slug      VARCHAR(190)    NOT NULL,
  location  ENUM('header','topbar','footer-1','footer-2','footer-3','footer-4','mobile','none')
            NOT NULL DEFAULT 'none',
  settings  JSON            NULL,     -- alignment, submenu animation, mobile behaviour
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY menus_site_slug_unique (site_id, slug),
  KEY menus_site_location_index (site_id, location),
  CONSTRAINT menus_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
) ENGINE=InnoDB;

Several menus may share a location; the Menu Slot block picks a menu explicitly, and location is only the default used when a header preset asks for "the header menu".

CREATE TABLE menu_items (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id        BIGINT UNSIGNED NOT NULL,
  menu_id        BIGINT UNSIGNED NOT NULL,
  parent_id      BIGINT UNSIGNED NULL,
  position       INT             NOT NULL DEFAULT 0,
  depth          TINYINT UNSIGNED NOT NULL DEFAULT 0,   -- 0,1,2 — max 3 levels
  type           ENUM('page','post','category','tag','url','anchor','phone','email',
                      'button','dropdown','mega','divider','heading') NOT NULL DEFAULT 'url',
  label          VARCHAR(190)    NOT NULL,
  target_id      BIGINT UNSIGNED NULL,   -- page / post / category / tag id, by type
  url            VARCHAR(500)    NULL,   -- for url, anchor, phone, email
  icon           VARCHAR(60)     NULL,
  badge_text     VARCHAR(60)     NULL,
  badge_color    VARCHAR(20)     NULL,
  new_tab        TINYINT(1)      NOT NULL DEFAULT 0,
  css_class      VARCHAR(190)    NULL,
  highlight_color VARCHAR(20)    NULL,
  visibility     JSON            NULL,   -- {roles:[], auth:"any|guest|user", devices:[], locales:[]}
  settings       JSON            NULL,   -- {panel_width, background, columns, open_on, animation, ...}
  mega           JSON            NULL,   -- the mega-panel block tree, NULL = plain dropdown
  is_active      TINYINT(1)      NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY menu_items_menu_parent_position_index (menu_id, parent_id, position),
  KEY menu_items_site_id_index (site_id),
  KEY menu_items_type_target_index (type, target_id),
  CONSTRAINT menu_items_menu_id_foreign FOREIGN KEY (menu_id) REFERENCES menus (id) ON DELETE CASCADE,
  CONSTRAINT menu_items_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES menu_items (id) ON DELETE CASCADE
) ENGINE=InnoDB;

target_id is intentionally not a foreign key: it points at different tables depending on type. Integrity is enforced in the application — a MenuIntegrityService flags broken targets in the admin UI instead of blocking deletes. menu_items.type = 'mega' is a convenience flag; the real test for a mega panel is mega IS NOT NULL AND JSON_LENGTH(mega, '$.sections') > 0.

3.10 template_parts and template_part_versions#

Headers, footers, top bars and announcement bars. Same tree, same editor, own versions.

CREATE TABLE template_parts (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id            BIGINT UNSIGNED NOT NULL,
  kind               ENUM('header','footer','topbar','announcement') NOT NULL,
  name               VARCHAR(190)   NOT NULL,
  preset             VARCHAR(60)    NULL,   -- logo-left, centered-logo, two-row, transparent, hamburger, sidebar
  content            JSON           NOT NULL,
  draft_content      JSON           NULL,
  settings           JSON           NULL,   -- sticky, shrink_on_scroll, hide_on_scroll_down, height, shadow, border, mobile:{...}
  conditions         JSON           NULL,   -- assignment rules; see doc 08
  is_default         TINYINT(1)     NOT NULL DEFAULT 0,
  status             ENUM('draft','published') NOT NULL DEFAULT 'draft',
  current_version_id BIGINT UNSIGNED NULL,
  position           INT            NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY template_parts_site_kind_index (site_id, kind, status),
  KEY template_parts_default_index (site_id, kind, is_default),
  CONSTRAINT template_parts_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE template_part_versions (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id          BIGINT UNSIGNED NOT NULL,
  template_part_id BIGINT UNSIGNED NOT NULL,
  version          INT UNSIGNED    NOT NULL,
  content          JSON            NOT NULL,
  settings         JSON            NULL,
  label            VARCHAR(190)    NULL,
  created_by       BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY tpv_part_version_unique (template_part_id, version),
  CONSTRAINT tpv_part_foreign FOREIGN KEY (template_part_id) REFERENCES template_parts (id) ON DELETE CASCADE,
  CONSTRAINT tpv_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB;

Exactly one is_default = 1 row per (site_id, kind) is enforced in the application; the installer seeds one default header and one default footer.

3.11 media_folders and media#

CREATE TABLE media_folders (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id   BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  name      VARCHAR(190)    NOT NULL,
  path      VARCHAR(500)    NOT NULL,   -- materialised, e.g. 2026/products
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY media_folders_site_path_unique (site_id, path(191)),
  CONSTRAINT media_folders_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE,
  CONSTRAINT media_folders_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES media_folders (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE media (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id       BIGINT UNSIGNED NOT NULL,
  folder_id     BIGINT UNSIGNED NULL,
  disk          VARCHAR(30)     NOT NULL DEFAULT 'public',
  path          VARCHAR(500)    NOT NULL,     -- media/2026/10/photo.webp
  original_name VARCHAR(255)    NOT NULL,
  mime_type     VARCHAR(100)    NOT NULL,
  extension     VARCHAR(16)     NOT NULL,
  size          BIGINT UNSIGNED NOT NULL,     -- bytes, of the stored file
  width         INT UNSIGNED    NULL,
  height        INT UNSIGNED    NULL,
  alt_text      VARCHAR(255)    NULL,
  caption       VARCHAR(500)    NULL,
  title         VARCHAR(255)    NULL,
  conversions   JSON            NULL,         -- {"thumb":"...","medium":"...","large":"...","original":"..."}
  checksum      CHAR(40)        NULL,         -- sha1 of the upload, for duplicate detection
  uploaded_by   BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY media_site_folder_index (site_id, folder_id, created_at),
  KEY media_checksum_index (site_id, checksum),
  KEY media_mime_index (site_id, mime_type),
  CONSTRAINT media_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE,
  CONSTRAINT media_folder_id_foreign FOREIGN KEY (folder_id) REFERENCES media_folders (id) ON DELETE SET NULL,
  CONSTRAINT media_uploaded_by_foreign FOREIGN KEY (uploaded_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB;

Blocks store media_id, never a URL, so moving or re-optimising a file never breaks a page.

3.12 forms and form_submissions#

CREATE TABLE forms (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id       BIGINT UNSIGNED NOT NULL,
  name          VARCHAR(190)    NOT NULL,
  slug          VARCHAR(190)    NOT NULL,
  fields        JSON            NOT NULL,   -- ordered field definitions; see doc 10
  settings      JSON            NULL,       -- submit label, success message or redirect, honeypot, rate limit
  notify_emails VARCHAR(500)    NULL,       -- comma separated
  store_submissions TINYINT(1)  NOT NULL DEFAULT 1,
  is_active     TINYINT(1)      NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY forms_site_slug_unique (site_id, slug),
  CONSTRAINT forms_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE form_submissions (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id    BIGINT UNSIGNED NOT NULL,
  form_id    BIGINT UNSIGNED NOT NULL,
  page_id    BIGINT UNSIGNED NULL,          -- where it was submitted from
  data       JSON            NOT NULL,      -- {field_key: value}
  files      JSON            NULL,          -- [{media_id, original_name}]
  ip_address VARCHAR(45)     NULL,
  user_agent VARCHAR(500)    NULL,
  is_read    TINYINT(1)      NOT NULL DEFAULT 0,
  is_spam    TINYINT(1)      NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY form_submissions_form_created_index (form_id, created_at),
  KEY form_submissions_site_unread_index (site_id, is_read),
  CONSTRAINT form_submissions_form_id_foreign FOREIGN KEY (form_id) REFERENCES forms (id) ON DELETE CASCADE,
  CONSTRAINT form_submissions_page_id_foreign FOREIGN KEY (page_id) REFERENCES pages (id) ON DELETE SET NULL
) ENGINE=InnoDB;

3.13 theme_settings#

One row per site. tokens is the whole design system; see 09-theme-tokens.md.

CREATE TABLE theme_settings (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id     BIGINT UNSIGNED NOT NULL,
  preset      VARCHAR(60)     NULL,        -- which of the 4 presets it started from
  tokens      JSON            NOT NULL,
  custom_css  TEXT            NULL,        -- Admin only
  custom_head TEXT            NULL,        -- Admin only
  css_hash    CHAR(32)        NULL,        -- names the generated stylesheet
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY theme_settings_site_unique (site_id),
  CONSTRAINT theme_settings_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
) ENGINE=InnoDB;

3.14 redirects#

CREATE TABLE redirects (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id     BIGINT UNSIGNED NOT NULL,
  from_path   VARCHAR(500)    NOT NULL,       -- stored without a leading slash, lower-cased
  to_path     VARCHAR(500)    NOT NULL,       -- path or absolute URL
  status_code SMALLINT UNSIGNED NOT NULL DEFAULT 301,
  is_regex    TINYINT(1)      NOT NULL DEFAULT 0,
  hits        INT UNSIGNED    NOT NULL DEFAULT 0,
  last_hit_at TIMESTAMP       NULL,
  source      ENUM('auto','manual') NOT NULL DEFAULT 'manual',
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY redirects_site_from_unique (site_id, from_path(191)),
  KEY redirects_site_regex_index (site_id, is_regex),
  CONSTRAINT redirects_site_id_foreign FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
) ENGINE=InnoDB;

hits and last_hit_at are updated at most once per path per hour (a cache flag guards the write), so a crawler cannot turn the 404 path into a write storm.

3.15 audit_logs#

CREATE TABLE audit_logs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id     BIGINT UNSIGNED NULL,
  user_id     BIGINT UNSIGNED NULL,
  user_name   VARCHAR(190)    NULL,       -- denormalised: survives user deletion
  action      VARCHAR(60)     NOT NULL,   -- created, updated, deleted, published, restored, login, failed_login, settings_changed
  subject_type VARCHAR(120)   NULL,       -- model class
  subject_id  BIGINT UNSIGNED NULL,
  subject_label VARCHAR(255)  NULL,       -- page title etc., for a readable log
  changes     JSON            NULL,       -- {before:{}, after:{}} for scalar fields only, never whole trees
  ip_address  VARCHAR(45)     NULL,
  user_agent  VARCHAR(500)    NULL,
  created_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY audit_logs_subject_index (subject_type, subject_id, created_at),
  KEY audit_logs_user_index (user_id, created_at),
  KEY audit_logs_site_action_index (site_id, action, created_at)
) ENGINE=InnoDB;

changes never stores a full block tree — version history already does that. A nightly command prunes rows older than kodepress.audit.keep_days (default 365).

3.16 Reserved for later phases#

These are not created in Phase 0. They are listed so names are not taken by accident.

Table	Phase	Purpose
`plugins`	5	installed plugin slug, version, enabled flag, settings JSON
`plugin_migrations`	5	which plugin migrations have run
`site_user`	5	multi-site access per user, when one user spans sites
`comments`	out of scope	a plugin would add it; `pages.comment_count` is the only hook core keeps

3.17 Seed data created by `kodepress:install`#

Table	Seeded
`sites`	one row, `is_default = 1`, locales `["bn","en"]`, timezone `Asia/Dhaka`
`roles` / `permissions`	Admin, Editor, Writer, Designer and the full permission list (doc 11)
`users`	the first Admin, from the interactive prompt
`theme_settings`	the `default` preset tokens
`templates`	5 page templates + 8 section templates + 2 header + 2 footer, all `is_builtin = 1`
`template_parts`	one default header, one default footer, both `published`
`menus` / `menu_items`	a `main` header menu with Home, About, Blog, Contact
`pages`	Home (`is_homepage`), About, Contact (with a form), Blog index, 3 demo posts
`categories` / `tags`	2 categories, 4 tags, attached to the demo posts
`forms`	one Contact form wired to the Contact page
`media`	6 placeholder images, royalty-free, already converted to WebP

Demo content is removable in one click from Settings -> Site info -> Remove demo content, which deletes exactly the seeded rows (tracked by a seed_batch flag in options) and nothing else.

Previous02 · Architecture Next04 · Block system

KodePress documentation · generated from the Markdown sources by tools/build-docs-site.py · internal preview, not indexed.