Skip to content

Instantly share code, notes, and snippets.

@wsydney76

wsydney76/craft-db-model-long.md

Last active April 13, 2026 08:36
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save wsydney76/2741d23073161bbf6ce842ccd3c70994 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save wsydney76/2741d23073161bbf6ce842ccd3c70994 to your computer and use it in GitHub Desktop.
Download ZIP
Craft CMS 5 Content Storage Database Model (draft)
Raw
craft-db-model-long.md

Craft CMS 5 Content Storage Database Model

The central elements table

In Craft CMS 5, the elements table serves as the foundational registry for every piece of content and content-like object in the system. Rather than storing the actual content itself, it provides a unified identity layer that all element types—such as entries, assets, categories, users, and nested entries—inherit from.

Each row in the elements table represents a single element instance and contains core, globally relevant metadata that is independent of site, field layout, or element-specific attributes. This includes the element’s unique ID, its fully qualified class type (which determines behavior and capabilities), and system-level state flags such as whether the element is enabled, archived, or soft-deleted. It also tracks lifecycle timestamps like creation and last update dates.

The key architectural role of this table is to decouple identity and lifecycle management from both localization and content storage. Site-specific data—such as slugs, URIs, and custom field values—is handled separately in elements_sites, while element-type-specific data lives in dedicated tables like entries or assets. This separation allows Craft to treat all content uniformly at the API level while still supporting highly flexible schemas and multi-site configurations.

In practice, the elements table acts as the anchor point for nearly all relational operations within Craft. Other tables reference elements via their IDs, including relations between elements, structure hierarchies, revisions, and drafts. This makes it possible to implement a consistent querying model across heterogeneous content types, since everything ultimately resolves back to a shared element identity.

Because all major content types share the same base element identity, Craft can apply consistent querying, drafts, revisions, propagation, status handling, and relations across many different kinds of content. In practice, the elements table is the root record, and other tables add context or type-specific data on top of it.

Element type specific data in dedicated tables

Each element type usually has its own table that stores attributes unique to that type. For example, entries store their section, type, author, post date, and expiry date in the entries table. Assets store volume, folder, filename, kind, size, dimensions, and other file-specific information in asset-related tables. Categories, tags, and users likewise have their own dedicated tables for properties that do not belong in the shared base element record.

This means Craft’s storage pattern is layered. The elements table stores the common identity. The elements_sites table stores per-site state. Then an element-type-specific table stores attributes that only make sense for that kind of element. Queries join these layers together as needed.

Plugins can create their own element types and store their specific data in tables created and managed by the plugin, e.g. Craft Commerce creates products, variants, orders (and more) and corresponding commerce_products, commerce_variants, commerce_orders table.

Site-specific content and localized values

Craft separates global element identity from site-specific content. Localized and per-site values are stored in elements_sites. This table links an element ID to a site ID and stores values that can vary by site, such as the URI, enabled state for that site, and other content metadata tied to a specific site context.

This separation is important in multi-site installations. The same element can exist once in elements, while having one or more corresponding rows in elements_sites, one for each site where the element is propagated. That allows Craft to keep one shared element identity while still supporting translated or site-specific content behavior.

How custom fields are stored

Custom fields in Craft CMS 5 are not stored in separate content tables. Instead, custom field values are stored in the JSON content column on elements_sites, alongside the site-specific element record. This lets Craft keep custom field data flexible without adding new columns to native tables like entries or assets whenever a field is added.

The exact storage pattern still depends on the field type. Simple scalar fields are stored as values within that JSON content structure. More complex fields may serialize structured data there, while some field types also rely on additional tables for their own internal storage mechanics. Relational fields are a separate case, because their connections are persisted in the relations table rather than as direct values in the JSON content column.

How generated fields are stored

Generated fields are generally stored like other custom fields. Their values live in the content JSON column on elements_sites, keyed by the field instance UID and scoped to the site-specific version of the element.

What makes them different is how the value is created, not where it is stored. The content is generated automatically on saving an element by rendering a twig object template defined in the field layout provider (e.g. an entry type) rather than entered manually, but once saved it follows the same storage model as other custom field values.

The type of the generated value is preserved, so numbers are stored as int/float, which allows expected behavior when querying or sorting.

Field layouts, field instances, and custom field storage

A field layout defines which fields are attached to an element type and how those fields are presented in the authoring UI. In Craft, a custom field is not only a reusable field definition; it is attached to a layout through a field layout element that acts like a field instance in that specific content context. This distinction matters because the same underlying field can be used in different places while still being represented as a specific layout assignment.

For storage, Craft does not key custom field values in the JSON content column by the field handle alone. Instead, the stored keys are based on the field instance identifier from the layout assignment, using the instance UID. That means the persisted JSON reflects the field layout’s configured field instances rather than just the global field definition. This approach lets Craft distinguish between layout-specific instances, supports more flexible configuration over time, and avoids treating the handle as the primary storage key.

How relations are stored

Relational fields in Craft CMS 5 involve two related but different storage concepts: the field instance’s value in the elements_sites.content JSON column, and the actual relationship records in the relations table.

At the elements_sites level, Craft stores custom field data using keys based on the field layout instance UID, not simply the base field handle. This means the JSON content represents the value for that specific field instance as it appears in a particular layout and site context. For relational fields, that value is best understood as the field instance’s localized content state: it tells Craft that this layout instance has a relational value and preserves the site-aware field context in which the value exists.

However, Craft does not rely on the JSON content column to store the actual full set of element-to-element links for relational fields. The concrete source-to-target connections are normalized into the relations table. There, Craft stores records that link the owning element to each related target element, together with metadata such as the target, the site context when applicable, the sort order, and the field reference responsible for the relation.

This split exists because the two layers serve different purposes. The elements_sites.content JSON column is about field-instance-based content storage in a site-specific layout context. The relations table is about efficiently modeling and querying normalized links between elements. In other words, the content JSON reflects the field instance value, while the relations table stores the actual relational graph.

A key reason for this design is that custom field values in elements_sites are keyed by field instance UID, because the same base field can appear in different layouts or contexts as distinct instances. By contrast, the relations table only needs to know which underlying field definition created the relation, not which layout instance key was used in JSON storage. That is why the JSON content is tied to the field instance, while relation rows are tied to the base field: one layer models authoring context, and the other models reusable relational structure.

This design gives Craft several advantages. It keeps the content JSON flexible and layout-aware, while allowing the relations table to support ordered, many-to-many, and site-aware element links without embedding large relational payloads directly into a JSON blob. It also lets Craft query inbound and outbound relationships consistently across entries, assets, categories, tags, users, and other element types.

How content in Matrix fields is stored

Nested entries are stored as entries in their own right, not inline inside the parent entry’s main row. Each nested entry gets its own record in the shared elements table, its own site-specific row in elements_sites, and its own entry-specific row in the entries table, just like any other entry. The parent entry does not contain the full nested entry data directly; instead, Craft stores the connection between the parent element and the nested entry in the elements_owners table.

Content block elements

The content block field semantically groups existing fields into a reusable bundle, and stores content as a nested element.

This makes complex content models easier to organize, improves reuse across layouts, and gives developers a clearer structural boundary for related content parts.

The contentblocks table links a content block to its field and owner, but does not store any additional content.

How entries in a structure section store their hierarchy

When entries belong to a Structure section, Craft stores their hierarchical position separately from the main entry content tables. Instead of putting parent-child relationships directly onto the entries table, Craft uses the structureelements table to record where each element sits inside a structure.

This table models the hierarchy using a nested set pattern. In a nested set model, each node is assigned boundary values that represent its place within the overall tree. Rather than only storing a direct parent reference, the structure can describe ancestry, descendants, sibling ordering, and subtree boundaries through these positional values.

The main advantage of this approach is efficient querying of hierarchical content. Because the hierarchy is encoded as ranges, Craft can fetch an entry’s descendants, ancestors, or an entire subtree with set-based queries, without recursively walking parent pointers row by row. That makes Structure sections well suited for ordered trees such as documentation, navigation, or nested page content, where both hierarchy and sort order need to be queried consistently.

It also fits Craft’s broader storage model: the entry’s identity still lives in elements, entry-specific attributes live in entries, site-specific content lives in elements_sites, and hierarchical placement for Structure sections lives in structureelements.

How drafts and revisions are stored

Drafts and revisions in Craft CMS 5 are derivative forms of a canonical element, not completely separate content models. They still participate in the shared element architecture, which means they get their own rows in the elements table and their own site-specific rows in elements_sites, but they remain linked to the canonical element they were created from. This lets Craft track alternate versions of the same content while preserving a clear relationship to the primary element record.

The number of revisions created can be limited by settings, so older revisions exceding that number will be deleted.

When drafts and revisions involve nested entries, Craft does not automatically duplicate every nested element as soon as the parent element gets a draft or revision. Instead, unchanged nested elements can continue to be reused through their existing relationships. The parent draft or revision records its own versioned state, but it can still point to the same nested entry records via the elements_owners table as long as those nested entries have not themselves changed.

A new nested element is typically only needed when content inside that nested element is actually modified within the draft or revision context. In that case, Craft creates a derivative version of the nested entry as well, so the draft can preserve its edited state without overwriting the canonical nested entry that other versions may still reference. This allows versioning to be more granular: the system reuses existing nested elements where possible, and only creates new derivative elements when a nested element’s own fields diverge.

Tracking changes

Craft CMS uses changedattributes and changedfields tables to track what a draft has changed compared with its canonical element.

The changedattributes table records changes to native element attributes, such as the title, slug, or other built-in properties. The changedfields table records changes to custom field values.

Together, they let Craft know which built-in attributes and custom fields have explicitly diverged in a draft, so that they can be marked as changed on editing screens, or used for merging content.

How soft-deleting elements works

Soft-deleting means an element is marked as deleted without being immediately removed from the database. Instead of permanently erasing the record at once, Craft keeps the element in its underlying tables and changes its state so it is treated as deleted by normal queries and the control panel.

At the shared storage level, this state is represented by setting a value ot the dateDeleted column of the element record in the elements table. That fits Craft’s overall architecture, because deletion status is a lifecycle concern tied to the element’s core identity, not just to one site or one custom field value. As a result, entries, assets, categories, and other element types can all participate in the same deletion workflow through their shared element model.

This design allows Craft to support behaviors like a trash system, restoration, and safer content management workflows. Since the element is not immediately destroyed, the system can still retain enough information to recover it, preserve references temporarily, and avoid irreversible loss from accidental deletion.

Only when an element is permanently deleted does Craft fully remove the relevant records and related data from storage. In that sense, soft-deletion is an intermediate lifecycle state: the element still exists in the database, but it is no longer considered active content.

How full text search is provided

The searchindex table supports Craft CMS’s internal search system by storing normalized search data for element content. Rather than querying raw entry content, custom field JSON, or related tables directly every time a user performs a search, Craft prepares searchable text in advance and saves it in this table in a format optimized for lookup.

Each row in searchindex represents indexed search content for a specific attribute or field of an element in a given site context. In practice, this means Craft can break searchable content into smaller indexable units, tied to the owning element and the field or attribute that produced the text. This structure allows the system to search across titles, native attributes, and custom field values using a unified mechanism.

The main purpose of the table is performance and consistency. When content is saved, Craft updates the search index so later queries can run against preprocessed search data instead of reconstructing searchable text from many different storage layers at query time. That is especially useful because Craft content may be spread across native element tables, elements_sites, nested elements, and custom field storage formats.

The searchindex table is not the canonical source of content. It is a derived index used specifically for search operations. If the table were rebuilt, the actual content would still live in its normal storage locations, such as elements, elements_sites, entries, and other element-specific or field-specific tables. The index exists to make search practical, not to serve as the authoritative record.

Because search behavior can be site-specific, the indexed rows are scoped in a way that supports localized content. This allows searches to reflect the correct language or site version of an element rather than mixing all content variants together.

In short, the searchindex table acts as Craft’s search-optimized projection of content: it takes text from many parts of the content model, normalizes it, stores it in a query-friendly form, and allows search queries to run efficiently across entries and other searchable elements.

Putting it all together

When you save an entry in Craft CMS 5, Craft typically writes to multiple layers of storage. It updates the shared element record, updates or creates the site-specific row, writes entry-native attributes to the entries table, stores custom field values in the JSON content column on elements_sites, and records any relational field connections in the relations table. The result is a flexible schema that supports localization, drafts, revisions, and highly customizable content models without forcing every field into one wide table.

Conclusion

Craft CMS 5 stores built-in elements with a normalized, layered database design. Shared identity lives in elements, localized state lives in elements_sites, element-specific attributes live in dedicated tables like entries, relations live in the relations table, and custom field values are generally stored in the JSON content column on elements_sites. Understanding that split helps developers reason more clearly about querying, debugging, migrations, and custom module development.

Raw
craft-db-model.md

Craft CMS 5 Content Storage Database Model

The elements table (core identity layer)

The elements table is the canonical registry for all content entities in Craft CMS 5 (entries, assets, categories, users, nested entries, etc.). It does not store actual content—it defines identity and lifecycle.

Each row represents one element and includes:

  • primary ID
  • fully qualified class (element type)
  • global state flags (enabled, archived, soft-deleted)
  • lifecycle timestamps

Key role: separate identity from storage concerns.

  • Site-specific data → elements_sites
  • Type-specific data → dedicated tables (entries, assets, …)

All higher-level features — relations, drafts, revisions, structures — anchor to elements.id. This enables a uniform API and query model across all element types.

Element-type-specific tables

Each element type persists attributes that are not universally shared:

  • entries: section, type, author, publish/expiry dates
  • assets: volume, path, filename, size, dimensions
  • users: username, email, password hash, last login
  • addresses: country, state, city, postal code
  • others follow the same pattern

Storage is layered:

  1. elements → identity + lifecycle
  2. elements_sites → localization + field data
  3. type table → domain-specific attributes

This avoids sparse or overly wide tables and keeps concerns isolated.

Plugins extend this model by introducing their own element types and tables (e.g. Commerce for products, variants, orders etc.).

Site-specific data (elements_sites)

elements_sites maps elements to sites and stores localized state:

  • URI / slug
  • per-site enabled state
  • custom field values (JSON)

One element can have multiple rows—one per site.

This enables:

  • multi-site content
  • site-specific URLs and states
  • shared identity with localized behavior

Custom field storage

Custom fields are stored in the elements_sites.content JSON column.

  • keys = field instance UIDs (not handles)

  • values vary by field type:

    • simple → scalar values
    • complex → structured JSON
    • some fields → additional tables

Why JSON:

  • avoids schema migrations when fields change
  • supports flexible, user-defined content models

Propagation:

  • Field values are stored per site
  • Field translation settings determine if values are copied across sites or maintained separately
  • Translation methods can be
    • Not translatable (same value across sites)
    • Translate for each site (separate values per site)
    • Translate for each site group (shared values across a group of sites)
    • Translate for each language (shared values across sites with the same language)
    • Custom translation method (defined by the field type)

Generated fields

Generated fields:

  • are stored like normal fields (in JSON)
  • are computed on save (via Twig templates)
  • persist as typed values (e.g. numbers remain numeric)

They behave like cached computed values—stored for query performance, not recalculated on every read.

Field layouts and field instances

Fields are attached via layout instances, not just definitions.

Implications:

  • JSON keys use instance UID
  • the same field can exist multiple times across layouts

This allows:

  • context-specific configuration
  • reuse without collisions
  • stable storage even if handles change

Relations

Relational fields are split across two layers:

JSON (elements_sites.content)

Stores the field’s presence and state per field instance in a specific layout + site context.

relations table

Stores actual connections:

  • source element
  • target element
  • field (definition-level)
  • site context (if applicable)
  • sort order

Why split:

  • JSON → reflects authoring context and reusable field instances
  • relations → normalized graph for efficient bidirectional querying

This allows consistent queries like “find all entries related to X” without parsing JSON.

Nested content (Matrix / nested entries)

Nested entries are first-class elements, not embedded data:

  • own elements row
  • own elements_sites row
  • own entries row

Parent linkage is stored in elements_owners.

Benefit:

  • nested content supports relations, drafts, permissions, etc.
  • consistent behavior with top-level entries

Content blocks

Content blocks:

  • group fields into reusable units
  • are implemented as nested elements

contentblocks table links:

  • block ↔ field ↔ owner

It defines structure, not content storage.

Structures (structureelements)

Hierarchies are stored using a nested set model.

structureelements tracks:

  • position in tree
  • ordering
  • ancestry via boundary values

Why nested sets:

  • efficient subtree queries (no recursion)
  • predictable ordering

Fits the layered model: hierarchy is separate from content.

Drafts and revisions

Drafts and revisions are derivative elements:

  • separate rows in elements and elements_sites
  • linked to a canonical element

Nested entries are reused unless modified:

  • unchanged → shared
  • changed → new derivative element

This minimizes duplication while preserving version integrity.

Change tracking

Differences from canonical are tracked in:

  • changedattributes → native fields (title, slug, etc.)
  • changedfields → custom fields

Used for:

  • UI indicators (“modified”)
  • merge logic

Soft deletion

Soft deletion sets elements.dateDeleted.

  • element remains in database
  • excluded from normal queries
  • can be restored

This provides a safe intermediate state before permanent deletion.

Search (searchindex)

searchindex is a denormalized search projection:

  • stores preprocessed text per element + field + site
  • fields defined whether they are searchable
  • optimized for lookup via database fulltext search, not storage
  • values for nested elements can be stored on both the nested element and the parent for efficient querying

Purpose:

  • avoid reconstructing searchable text at query time
  • unify search across all content sources

It can be rebuilt from canonical data.

Write flow (example: saving an entry)

Saving an entry typically writes to:

  • elements → identity + lifecycle
  • elements_sites → localized state + JSON fields
  • entries → entry-specific attributes
  • relations → relational links
  • searchindex → searchable text

Each layer handles a distinct concern.

Summary

Craft CMS 5 uses a normalized, layered architecture:

  • elements → identity + lifecycle
  • elements_sites → localization + flexible field storage
  • type tables → structured domain data
  • relations → normalized graph
  • JSON → schema flexibility

This design balances:

  • flexibility (custom fields, layouts)
  • consistency (shared element model)
  • performance (normalized relations, search index)

and is the foundation for multi-site, drafts, and extensibility.

Raw
craft5-database-schema.html
<!DOCTYPE html>
<html lang="en" data-theme="dark">
<head>
<meta charset="UTF-8"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<title>Craft CMS 5 + Commerce 5 — Database Schema</title>
<link href="https://api.fontshare.com/v2/css?f[]=satoshi@400,500,700&display=swap" rel="stylesheet"/>
<style>
:root,[data-theme='light']{
--bg:#f4f3ef;--surface:#ffffff;--surface2:#f0efe9;--border:#dddbd5;
--text:#1e1c18;--text2:#6b6966;--text3:#a8a7a3;
--primary:#01696f;--shadow:0 2px 8px rgba(0,0,0,.07);
--c-common:#01696f;--c-element:#2563eb;--c-site:#7c3aed;
--c-commerce:#c2410c;--c-workflow:#9d174d;--c-field:#0369a1;
--mono:'JetBrains Mono','Fira Code',monospace;--card-r:10px
}
[data-theme='dark']{
--bg:#0f0e0d;--surface:#1a1918;--surface2:#211f1d;--border:#2c2a27;
--text:#cfcdc9;--text2:#797670;--text3:#4e4b48;
--primary:#4f98a3;--shadow:0 2px 14px rgba(0,0,0,.4);
--c-common:#4f98a3;--c-element:#60a5fa;--c-site:#a78bfa;
--c-commerce:#fb923c;--c-workflow:#f472b6;--c-field:#38bdf8
}
*,*::before,*::after{box-sizing:border-box;margin:0;padding:0}
body{font-family:'Satoshi',sans-serif;background:var(--bg);color:var(--text);min-height:100vh;overflow-x:hidden}
/* HEADER */
header{position:sticky;top:0;z-index:50;background:var(--surface);border-bottom:1px solid var(--border);padding:10px 18px;display:flex;align-items:center;gap:14px;flex-wrap:wrap;box-shadow:var(--shadow)}
.logo{font-weight:700;font-size:15px;display:flex;align-items:center;gap:9px;flex-shrink:0}
.logo-sub{font-size:11px;color:var(--text2);font-weight:400}
.logo-icon{color:var(--primary)}
.hctrl{display:flex;gap:7px;align-items:center;margin-left:auto;flex-wrap:wrap}
.srchwrap{position:relative}
.srchwrap svg{position:absolute;left:9px;top:50%;transform:translateY(-50%);color:var(--text3);pointer-events:none}
.srchwrap input{background:var(--surface2);border:1px solid var(--border);border-radius:7px;padding:5px 10px 5px 30px;font-size:13px;color:var(--text);font-family:inherit;width:200px;outline:none;transition:border-color .15s}
.srchwrap input:focus{border-color:var(--primary)}
.btn{background:var(--surface2);border:1px solid var(--border);border-radius:6px;padding:5px 11px;font-size:12px;font-family:inherit;color:var(--text2);cursor:pointer;transition:all .15s;white-space:nowrap}
.btn:hover{border-color:var(--primary);color:var(--primary)}
.tbtn{width:30px;height:30px;display:flex;align-items:center;justify-content:center;padding:0}
/* LEGEND */
.legend{background:var(--surface2);border-bottom:1px solid var(--border);padding:8px 18px;display:flex;gap:10px;align-items:center;flex-wrap:wrap}
.leg-lbl{font-size:11px;font-weight:700;color:var(--text3);text-transform:uppercase;letter-spacing:.06em}
.leg-item{display:flex;align-items:center;gap:5px;font-size:11.5px;color:var(--text2);cursor:pointer;padding:3px 8px;border-radius:5px;border:1px solid transparent;transition:all .15s;user-select:none}
.leg-item:hover{border-color:var(--border)}
.leg-item.off{opacity:.35}
.leg-dot{width:9px;height:9px;border-radius:2px;flex-shrink:0}
/* LAYOUT */
.layout{display:flex;height:calc(100vh - 95px)}
.sidebar{width:240px;flex-shrink:0;border-right:1px solid var(--border);background:var(--surface);overflow-y:auto;padding:8px 0}
.sb-group{font-size:10.5px;font-weight:700;color:var(--text3);text-transform:uppercase;letter-spacing:.07em;padding:10px 14px 4px;display:flex;align-items:center;gap:6px}
.sb-item{display:block;width:100%;background:none;border:none;padding:4px 14px 4px 28px;font-family:inherit;font-size:12px;color:var(--text2);cursor:pointer;text-align:left;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;transition:color .1s,background .1s}
.sb-item:hover{color:var(--text);background:var(--surface2)}
.sb-item.active{color:var(--primary);font-weight:600}
.sb-item.faded{opacity:.3}
/* CANVAS */
.canvas-wrap{flex:1;overflow-y:auto;padding:18px}
.canvas{display:grid;gap:14px;grid-template-columns:repeat(auto-fill,minmax(290px,1fr));align-content:start}
/* CARDS */
.tcard{background:var(--surface);border:1.5px solid var(--border);border-radius:var(--card-r);overflow:hidden;transition:box-shadow .18s,border-color .18s;scroll-margin:20px}
.tcard:hover{box-shadow:var(--shadow),0 0 0 2px var(--primary);border-color:var(--primary)}
.tcard.hl{animation:pulse .4s ease}
.tcard.gone{display:none}
.tcard.dim{opacity:.2;pointer-events:none}
@keyframes pulse{0%,100%{box-shadow:0 0 0 2px var(--primary)}50%{box-shadow:0 0 0 5px var(--primary)}}
.card-hdr{padding:9px 12px 7px;border-bottom:1px solid var(--border);display:flex;align-items:flex-start;justify-content:space-between;cursor:pointer}
.card-hdr:hover .tname{color:var(--primary)}
.tname{font-family:var(--mono);font-size:12.5px;font-weight:600;color:var(--text)}
.gbadge{display:inline-flex;font-size:9.5px;font-weight:700;padding:2px 6px;border-radius:3px;margin-top:3px;text-transform:uppercase;letter-spacing:.04em}
.chev{background:none;border:none;cursor:pointer;color:var(--text3);padding:2px;flex-shrink:0;line-height:0;transition:color .12s}
.chev:hover{color:var(--primary)}
.cols-list{list-style:none}
.col-r{display:flex;align-items:center;border-bottom:1px solid var(--border);font-size:11px;font-family:var(--mono)}
.col-r:last-child{border-bottom:none}
.col-r:hover{background:var(--surface2)}
.cico{width:22px;text-align:center;font-size:10px;flex-shrink:0;padding:3px 0}
.cico.pk{color:#f59e0b}
.cico.fk{color:var(--c-element)}
.cn{flex:1;padding:3px 4px;color:var(--text);overflow:hidden;text-overflow:ellipsis;white-space:nowrap}
.ct{padding:3px 8px 3px 2px;color:var(--text3);font-size:10px;white-space:nowrap}
.cnote{padding:3px 8px 3px 0;color:var(--c-workflow);font-size:9.5px;font-family:'Satoshi',sans-serif;white-space:nowrap}
.card-foot{padding:5px 12px;background:var(--surface2);border-top:1px solid var(--border);font-size:10px;color:var(--text3);cursor:pointer;white-space:nowrap;overflow:hidden;text-overflow:ellipsis}
.card-foot:hover{color:var(--primary)}
/* GROUP COLOUR BADGES */
.g-common{background:color-mix(in oklch,var(--c-common) 12%,transparent);color:var(--c-common)}
.g-element{background:color-mix(in oklch,var(--c-element) 12%,transparent);color:var(--c-element)}
.g-site{background:color-mix(in oklch,var(--c-site) 12%,transparent);color:var(--c-site)}
.g-commerce{background:color-mix(in oklch,var(--c-commerce) 12%,transparent);color:var(--c-commerce)}
.g-workflow{background:color-mix(in oklch,var(--c-workflow) 12%,transparent);color:var(--c-workflow)}
.g-field{background:color-mix(in oklch,var(--c-field) 12%,transparent);color:var(--c-field)}
/* MODAL */
.modal-bg{display:none;position:fixed;inset:0;z-index:200;background:rgba(0,0,0,.55);backdrop-filter:blur(3px);align-items:center;justify-content:center}
.modal-bg.open{display:flex}
.modal{background:var(--surface);border:1px solid var(--border);border-radius:12px;max-width:700px;width:92vw;max-height:82vh;overflow:hidden;display:flex;flex-direction:column;box-shadow:0 20px 60px rgba(0,0,0,.5)}
.mhdr{padding:12px 16px;border-bottom:1px solid var(--border);display:flex;align-items:center;gap:10px}
.mtitle{font-family:var(--mono);font-weight:600;font-size:13px;flex:1;color:var(--text)}
.mclose{background:none;border:none;color:var(--text2);cursor:pointer;font-size:17px;padding:2px 6px;border-radius:4px}
.mclose:hover{color:var(--text);background:var(--surface2)}
.mbody{overflow-y:auto;padding:14px 16px}
pre{font-family:var(--mono);font-size:11.5px;line-height:1.7;color:var(--text);white-space:pre-wrap;word-break:break-word}
.kw{color:#7dd3fc}.tn{color:#86efac}.ty{color:#fca5a5}.cm{color:var(--text3);font-style:italic}
.copybtn{margin-top:10px;background:var(--surface2);border:1px solid var(--border);border-radius:6px;padding:5px 13px;font-size:12px;font-family:inherit;color:var(--text2);cursor:pointer;transition:all .15s}
.copybtn:hover{border-color:var(--primary);color:var(--primary)}
/* RELATIONS SIDE PANEL */
.rpanel{position:fixed;right:0;top:0;bottom:0;width:280px;background:var(--surface);border-left:1px solid var(--border);z-index:100;transform:translateX(100%);transition:transform .2s ease;overflow-y:auto;padding:14px}
.rpanel.open{transform:translateX(0);box-shadow:-4px 0 20px rgba(0,0,0,.2)}
.rph{font-size:12px;font-weight:700;color:var(--text);margin-bottom:10px;display:flex;align-items:center;justify-content:space-between}
.rclose{background:none;border:none;cursor:pointer;color:var(--text3);font-size:15px;line-height:1}
.rclose:hover{color:var(--text)}
.ritem{padding:7px 9px;border-radius:6px;background:var(--surface2);margin-bottom:5px;border:1px solid var(--border);font-size:11px}
.rdir{font-size:9.5px;font-weight:700;text-transform:uppercase;letter-spacing:.06em;color:var(--text3);margin-bottom:2px}
.rtable{font-family:var(--mono);font-size:11.5px;color:var(--primary);cursor:pointer;font-weight:600}
.rtable:hover{text-decoration:underline}
.rcol{color:var(--text3);font-family:var(--mono);font-size:10px;margin-top:1px}
@media(max-width:650px){.sidebar{display:none}.canvas{grid-template-columns:1fr}}
/* Migration badges */
.migbadge{display:inline-flex;font-size:9px;font-weight:700;padding:1px 5px;border-radius:3px;margin-left:4px;background:color-mix(in oklch,var(--c-workflow) 15%,transparent);color:var(--c-workflow);font-family:'Satoshi',sans-serif;white-space:nowrap;flex-shrink:0}
.col-r.migrated{background:color-mix(in oklch,var(--c-workflow) 5%,transparent)}
.tcard.has-dropped{opacity:.5;border-style:dashed}
.dropped-label{font-size:9px;font-weight:700;padding:2px 7px;border-radius:3px;background:color-mix(in oklch,#ef4444 15%,transparent);color:#ef4444;text-transform:uppercase;letter-spacing:.05em;display:inline-flex;margin-top:3px}
.migfilter-wrap{display:flex;align-items:center;gap:6px;font-size:11px;color:var(--text2)}
.migfilter-wrap input[type=checkbox]{accent-color:var(--c-workflow)}
</style>
</head>
<body>
<header>
<div class="logo">
<svg class="logo-icon" width="26" height="26" viewBox="0 0 26 26" fill="none" stroke="currentColor" stroke-width="1.8" stroke-linecap="round" stroke-linejoin="round">
<rect x="2" y="2" width="8" height="5" rx="1.2"/><rect x="16" y="2" width="8" height="5" rx="1.2"/>
<rect x="2" y="19" width="8" height="5" rx="1.2"/><rect x="16" y="19" width="8" height="5" rx="1.2"/>
<path d="M6 7v5.5M20 7v5.5M6 19v-6.5M20 19v-6.5M6 12.5h14"/>
</svg>
<div>Craft CMS 5 + Commerce 5<div class="logo-sub">Database Schema Reference — Install.php + all migrations (CMS 5.x · Commerce 5.x)</div></div>
</div>
<div class="hctrl">
<div class="srchwrap">
<svg width="13" height="13" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2.2"><circle cx="11" cy="11" r="8"/><path d="m21 21-4.35-4.35"/></svg>
<input id="srch" type="text" placeholder="Search tables or columns…" autocomplete="off"/>
</div>
<button class="btn" onclick="setAll(true)">Expand All</button>
<button class="btn" onclick="setAll(false)">Collapse All</button>
<label class="migfilter-wrap" title="Highlight columns added by post-Install migrations">
<input type="checkbox" id="migOnly" onchange="toggleMigHighlight(this.checked)"/> Migration columns
</label>
<button class="btn" id="sqlAllBtn">SQL View</button>
<button class="btn tbtn" id="thbtn" title="Toggle theme"></button>
</div>
</header>
<div class="legend">
<span class="leg-lbl">Groups</span>
<div class="leg-item" data-g="common" onclick="togGrp('common',this)"><div class="leg-dot" style="background:var(--c-common)"></div>Core / Common</div>
<div class="leg-item" data-g="element" onclick="togGrp('element',this)"><div class="leg-dot" style="background:var(--c-element)"></div>Element Types</div>
<div class="leg-item" data-g="site" onclick="togGrp('site',this)"><div class="leg-dot" style="background:var(--c-site)"></div>Site / Multi-site</div>
<div class="leg-item" data-g="workflow" onclick="togGrp('workflow',this)"><div class="leg-dot" style="background:var(--c-workflow)"></div>Editing Workflow</div>
<div class="leg-item" data-g="field" onclick="togGrp('field',this)"><div class="leg-dot" style="background:var(--c-field)"></div>Fields &amp; Layout</div>
<div class="leg-item" data-g="commerce" onclick="togGrp('commerce',this)"><div class="leg-dot" style="background:var(--c-commerce)"></div>Commerce</div>
</div>
<div class="layout">
<div class="sidebar" id="sb"></div>
<div class="canvas-wrap"><div class="canvas" id="cv"></div></div>
</div>
<div class="modal-bg" id="sqlModal">
<div class="modal">
<div class="mhdr">
<div class="mtitle" id="sqlTitle"></div>
<button class="mclose" onclick="closeModal()">✕</button>
</div>
<div class="mbody"><pre id="sqlBody"></pre><button class="copybtn" onclick="copySQL()">Copy SQL</button></div>
</div>
</div>
<div class="rpanel" id="rpanel">
<div class="rph"><span id="rpTitle">Relations</span><button class="rclose" onclick="closeRP()">✕</button></div>
<div id="rpBody"></div>
</div>
<script>
// ── helpers ──────────────────────────────────────────────────────────────────
const c = (n,t,note,pk,fk) => ({n,t:t||'',note:note||'',pk:!!pk,fk:!!fk});
const pk = (n,t) => c(n||'id', t||'int AUTO_INCREMENT', 'PRIMARY KEY', true, false);
const fk = (n,t) => c(n, t||'int', 'FK', false, true);
const ts = () => [c('dateCreated','datetime','NOT NULL'),c('dateUpdated','datetime','NOT NULL')];
const tsu = () => [...ts(), c('uid','char(36)')];
// ── table data ───────────────────────────────────────────────────────────────
const GRP = {
common: {lbl:'Core / Common', cls:'g-common'},
element: {lbl:'Element Types', cls:'g-element'},
site: {lbl:'Site / Multi-site',cls:'g-site'},
workflow: {lbl:'Editing Workflow', cls:'g-workflow'},
field: {lbl:'Fields & Layout', cls:'g-field'},
commerce: {lbl:'Commerce', cls:'g-commerce'},
};
const TABLES = [
// ── CORE COMMON ──────────────────────────────────────────────────────────────
{name:'elements',group:'common',desc:'Root record for every element (entries, assets, users, addresses…)',cols:[
pk(), fk('canonicalId','int'), fk('draftId','int'), fk('revisionId','int'), fk('fieldLayoutId','int'),
c('type','varchar','PHP element class'), c('enabled','tinyint(1)'), c('archived','tinyint(1)'),
...ts(), c('dateLastMerged','datetime'), c('dateDeleted','datetime'), c('deletedWithOwner','tinyint(1)'), c('uid','char(36)'),
]},
{name:'elements_owners',group:'common',desc:'Nested element ownership for Matrix / Content Block fields',cols:[
fk('elementId','int'), fk('ownerId','int'), c('sortOrder','smallint unsigned'),
]},
{name:'elements_bulkops',group:'common',desc:'Tracks elements participating in bulk operations',cols:[
fk('elementId','int'), c('key','char(10)'), c('timestamp','datetime'),
]},
{name:'structures',group:'common',desc:'Structure container (hierarchy tree config)',cols:[
pk(), c('maxLevels','smallint unsigned'), ...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'structureelements',group:'common',desc:'Nested set tree (lft/rgt) for structure navigation',cols:[
pk(), fk('structureId'), fk('elementId'),
c('root','int unsigned'), c('lft','int unsigned','nested sets left'), c('rgt','int unsigned','nested sets right'), c('level','smallint unsigned'),
...tsu(),
]},
{name:'info',group:'common',desc:'Single-row system info (version, schema version)',cols:[
pk(), c('version','varchar(50)'), c('schemaVersion','varchar(15)'), c('maintenance','tinyint(1)'), c('configVersion','char(12)'), c('fieldVersion','char(12)'), ...tsu(),
]},
{name:'plugins',group:'common',desc:'Installed plugins registry',cols:[
pk(), c('handle','varchar'), c('version','varchar'), c('schemaVersion','varchar'), c('installDate','datetime'), ...tsu(),
]},
{name:'migrations',group:'common',desc:'Applied migration history per track',cols:[
pk(), c('track','varchar','app / plugin / content'), c('name','varchar'), c('applyTime','datetime'), ...tsu(),
]},
{name:'projectconfig',group:'common',desc:'Project config DB snapshot (dot-notation key/value)',cols:[
c('path','varchar','PRIMARY KEY',true,false), c('value','text'),
]},
{name:'queue',group:'common',desc:'Background job queue (Yii2 Queue)',cols:[
pk(), c('channel','varchar'), c('job','blob'), c('description','text'), c('timePushed','int'), c('ttr','int'), c('delay','int'),
c('priority','int unsigned'), c('dateReserved','datetime'), c('timeUpdated','int'), c('progress','smallint'),
c('progressLabel','varchar'), c('attempt','int'), c('fail','tinyint(1)'), c('dateFailed','datetime'), c('error','text'),
]},
{name:'gqltokens',group:'common',desc:'GraphQL authentication tokens',cols:[
pk(), c('name','varchar'), c('accessToken','varchar'), c('enabled','tinyint(1)'), c('expiryDate','datetime'), c('lastUsed','datetime'), fk('schemaId'), ...tsu(),
]},
{name:'gqlschemas',group:'common',desc:'GraphQL schema scope definitions',cols:[
pk(), c('name','varchar'), c('scope','json'), c('isPublic','tinyint(1)'), ...tsu(),
]},
{name:'tokens',group:'common',desc:'One-time URL tokens (password reset, preview)',cols:[
pk(), c('token','char(32)'), c('route','text'), c('usageLimit','tinyint unsigned'), c('usageCount','tinyint unsigned'), c('expiryDate','datetime'), ...tsu(),
]},
{name:'sequences',group:'common',desc:'Auto-increment sequence generator',cols:[
c('name','varchar','PRIMARY KEY',true,false), c('next','int unsigned','default 1'),
]},
{name:'systemmessages',group:'common',desc:'Customizable system email/notification message templates',cols:[
pk(), c('language','varchar'), c('key','varchar'), c('subject','text'), c('body','text'), ...tsu(),
]},
{name:'announcements',group:'common',desc:'In-CP plugin / Craft announcement notifications',cols:[
pk(), fk('userId'), fk('pluginId'), c('heading','varchar'), c('body','text'), c('unread','tinyint(1)'), c('dateRead','datetime'), c('dateCreated','datetime'),
]},
{name:'shunnedmessages',group:'common',desc:'Dismissed CP announcements per user',cols:[
pk(), fk('userId'), c('message','varchar'), c('expiryDate','datetime'), ...tsu(),
]},
{name:'widgets',group:'common',desc:'Control panel dashboard widget configuration',cols:[
pk(), fk('userId'), c('type','varchar'), c('sortOrder','smallint unsigned'), c('colspan','tinyint'), c('settings','json'), c('enabled','tinyint(1)'), ...tsu(),
]},
{name:'queue',group:'common',desc:'Background job queue',cols:[]},
{name:'deprecationerrors',group:'common',desc:'Deprecation warning log (dev mode only)',cols:[
pk(), c('key','varchar'), c('fingerprint','varchar'), c('lastOccurrence','datetime'), c('file','varchar'), c('line','smallint unsigned'), c('message','text'), c('traces','json'), ...tsu(),
]},
{name:'craftidtokens',group:'common',desc:'Craft ID OAuth tokens for plugin licensing',cols:[
pk(), fk('userId'), c('accessToken','text'), c('expiryDate','datetime'), ...tsu(),
]},
{name:'resourcepaths',group:'common',desc:'Published resource URL → path cache',cols:[
c('hash','varchar','PRIMARY KEY',true,false), c('path','varchar'),
]},
{name:'bulkopevents',group:'common',desc:'Bulk operation event coordination between workers',cols:[
c('key','char(10)','PK part',true,false), c('senderClass','varchar','PK part'), c('eventName','varchar','PK part'), c('timestamp','datetime'),
]},
// ── ELEMENT TYPES ────────────────────────────────────────────────────────────
{name:'entries',group:'element',desc:'Entry-specific data — extends elements via id FK',cols:[
fk('id','int'), fk('sectionId'), fk('parentId'), fk('primaryOwnerId'), fk('fieldId'), fk('typeId'),
c('postDate','datetime'), c('expiryDate','datetime'),
c('status','enum','live / pending / expired'),
c('deletedWithEntryType','tinyint(1)'), c('deletedWithSection','tinyint(1)'), ...ts(),
]},
{name:'entries_authors',group:'element',desc:'Many-to-many: entries ↔ users (multiple authors) UNIQUE(entryId, authorId)',cols:[
fk('entryId'), fk('authorId'), c('sortOrder','smallint unsigned'),
]},
{name:'entrytypes',group:'element',desc:'Entry type definitions — shared across sections in Craft 5',cols:[
pk(), fk('fieldLayoutId'), c('name','varchar'), c('handle','varchar'), c('description','text'),
c('icon','varchar'), c('color','varchar'), c('hasTitleField','tinyint(1)'),
c('titleTranslationMethod','varchar'), c('titleTranslationKeyFormat','text'), c('titleFormat','varchar'),
c('showSlugField','tinyint(1)'), c('slugTranslationMethod','varchar'), c('showStatusField','tinyint(1)'),
...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'sections',group:'element',desc:'Section configuration (single / channel / structure)',cols:[
pk(), fk('structureId'), c('name','varchar'), c('handle','varchar'),
c('type','enum','single / channel / structure'), c('enableVersioning','tinyint(1)'), c('maxAuthors','smallint unsigned'),
c('propagationMethod','varchar'), c('defaultPlacement','enum','beginning/end'), c('previewTargets','json'),
...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'sections_entrytypes',group:'element',desc:'Section ↔ EntryType pivot UNIQUE(sectionId, typeId)',cols:[
fk('sectionId'), fk('typeId'), c('sortOrder','smallint unsigned'), c('name','varchar','section override'), c('handle','varchar','section override'), c('description','text'),
]},
{name:'assets',group:'element',desc:'Asset-specific data (images, files) — extends elements',cols:[
fk('id','int'), fk('volumeId'), fk('folderId'), fk('uploaderId'),
c('filename','varchar'), c('mimeType','varchar'), c('kind','varchar(50)','image/video/pdf…'),
c('alt','text'), c('width','int unsigned'), c('height','int unsigned'), c('size','bigint unsigned'),
c('focalPoint','varchar(13)'), c('deletedWithVolume','tinyint(1)'), c('keptFile','tinyint(1)'), c('dateModified','datetime'), ...ts(),
]},
{name:'volumes',group:'element',desc:'Volume (filesystem) configuration',cols:[
pk(), fk('fieldLayoutId'), c('name','varchar'), c('handle','varchar'),
c('fs','varchar','filesystem adapter class'), c('subpath','varchar'), c('transformFs','varchar'), c('transformSubpath','varchar'),
c('titleTranslationMethod','varchar'), c('altTranslationMethod','varchar'), c('sortOrder','smallint unsigned'),
...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'volumefolders',group:'element',desc:'Folder tree within asset volumes',cols:[
pk(), fk('parentId'), fk('volumeId'), c('name','varchar'), c('path','varchar'), ...tsu(),
]},
{name:'imagetransforms',group:'element',desc:'Named image transform definitions',cols:[
pk(), c('name','varchar'), c('handle','varchar'),
c('mode','enum','stretch/fit/crop/letterbox'), c('position','enum','9 positions'),
c('width','int unsigned'), c('height','int unsigned'), c('format','varchar'), c('quality','int'),
c('interlace','enum','none/line/plane/partition'), c('fill','varchar(11)'), c('upscale','tinyint(1)'),
c('parameterChangeTime','datetime'), ...tsu(),
]},
{name:'imagetransformindex',group:'element',desc:'Transform file existence cache per asset',cols:[
pk(), fk('assetId'), c('transformer','varchar'), c('filename','varchar'), c('format','varchar'),
c('transformString','varchar'), c('fileExists','tinyint(1)'), c('inProgress','tinyint(1)'), c('error','tinyint(1)'), c('dateIndexed','datetime'), ...tsu(),
]},
{name:'assetindexdata',group:'element',desc:'In-progress asset indexing data per file/folder',cols:[
pk(), fk('sessionId'), fk('volumeId'), c('uri','text'), c('size','bigint unsigned'),
c('timestamp','datetime'), c('isDir','tinyint(1)'), fk('recordId'),
c('isSkipped','tinyint(1)'), c('inProgress','tinyint(1)'), c('completed','tinyint(1)'), ...tsu(),
]},
{name:'assetindexingsessions',group:'element',desc:'Asset indexing session state',cols:[
pk(), c('indexedVolumes','text'), c('totalEntries','int'), c('processedEntries','int'),
c('cacheRemoteImages','tinyint(1)'), c('listEmptyFolders','tinyint(1)'), c('isCli','tinyint(1)'), c('actionRequired','tinyint(1)'), ...tsu(),
]},
{name:'users',group:'element',desc:'User-specific data — extends elements via id FK',cols:[
fk('id','int'), fk('photoId'), fk('affiliatedSiteId'),
c('active','tinyint(1)'), c('pending','tinyint(1)'), c('locked','tinyint(1)'), c('suspended','tinyint(1)'), c('admin','tinyint(1)'),
c('username','varchar'), c('fullName','varchar'), c('firstName','varchar'), c('lastName','varchar'),
c('email','varchar'), c('password','varchar'),
c('lastLoginDate','datetime'), c('lastLoginAttemptIp','varchar(45)'),
c('invalidLoginWindowStart','datetime'), c('invalidLoginCount','tinyint unsigned'),
c('lastInvalidLoginDate','datetime'), c('lockoutDate','datetime'), c('hasDashboard','tinyint(1)'),
c('verificationCode','varchar'), c('verificationCodeIssuedDate','datetime'),
c('unverifiedEmail','varchar'), c('passwordResetRequired','tinyint(1)'), c('lastPasswordChangeDate','datetime'), ...ts(),
]},
{name:'usergroups',group:'element',desc:'User group definitions',cols:[
pk(), c('name','varchar'), c('handle','varchar'), c('description','text'), ...tsu(),
]},
{name:'usergroups_users',group:'element',desc:'User ↔ Group membership pivot UNIQUE(groupId, userId)',cols:[
pk(), fk('groupId'), fk('userId'), ...tsu(),
]},
{name:'userpermissions',group:'element',desc:'Permission string registry',cols:[
pk(), c('name','varchar','permission key'), ...tsu(),
]},
{name:'userpermissions_usergroups',group:'element',desc:'Group permission assignments',cols:[
pk(), fk('permissionId'), fk('groupId'), ...tsu(),
]},
{name:'userpermissions_users',group:'element',desc:'Direct user permission assignments',cols:[
pk(), fk('permissionId'), fk('userId'), ...tsu(),
]},
{name:'userpreferences',group:'element',desc:'Per-user CP preferences JSON blob',cols:[
c('userId','int','PRIMARY KEY + FK → users.id',true,true), c('preferences','json'),
]},
{name:'sessions',group:'element',desc:'User session tokens',cols:[
pk(), fk('userId'), c('token','char(100)'), ...tsu(),
]},
{name:'authenticator',group:'element',desc:'TOTP 2-factor auth secrets per user',cols:[
pk(), fk('userId'), c('auth2faSecret','varchar'), c('oldTimestamp','int unsigned'), ...ts(),
]},
{name:'recoverycodes',group:'element',desc:'2FA backup recovery codes (hashed)',cols:[
pk(), fk('userId'), c('recoveryCodes','text'), ...ts(),
]},
{name:'webauthn',group:'element',desc:'WebAuthn passkey credentials',cols:[
pk(), fk('userId'), c('credentialId','varchar'), c('credential','text'), c('credentialName','varchar'), c('dateLastUsed','datetime'), ...tsu(),
]},
{name:'sso_identities',group:'element',desc:'SSO provider identity links UNIQUE(provider, identityId, userId)',cols:[
c('provider','varchar','PK part',true,false), c('identityId','varchar','PK part'), fk('userId'), ...ts(),
]},
{name:'addresses',group:'element',desc:'Address element — linked to user, order, or custom field',cols:[
fk('id','int'), fk('primaryOwnerId'), fk('fieldId'),
c('countryCode','varchar'), c('administrativeArea','varchar'), c('locality','varchar'),
c('dependentLocality','varchar'), c('postalCode','varchar'), c('sortingCode','varchar'),
c('addressLine1','varchar'), c('addressLine2','varchar'), c('addressLine3','varchar'),
c('organization','varchar'), c('organizationTaxId','varchar'),
c('fullName','varchar'), c('firstName','varchar'), c('lastName','varchar'),
c('latitude','varchar'), c('longitude','varchar'), ...ts(),
]},
{name:'contentblocks',group:'element',desc:'Content Block nested element — new in Craft 5',cols:[
fk('id','int'), fk('primaryOwnerId'), fk('fieldId'),
]},
{name:'categories',group:'element',desc:'Category element — extends elements',cols:[
fk('id','int'), fk('groupId'), fk('parentId'), c('deletedWithGroup','tinyint(1)'), ...ts(),
]},
{name:'categorygroups',group:'element',desc:'Category group configuration',cols:[
pk(), fk('structureId'), fk('fieldLayoutId'), c('name','varchar'), c('handle','varchar'),
c('defaultPlacement','enum','beginning/end'), ...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'tags',group:'element',desc:'Tag element — extends elements',cols:[
fk('id','int'), fk('groupId'), c('deletedWithGroup','tinyint(1)'), ...ts(),
]},
{name:'taggroups',group:'element',desc:'Tag group configuration',cols:[
pk(), c('name','varchar'), c('handle','varchar'), fk('fieldLayoutId'), ...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'globalsets',group:'element',desc:'Global set element (singleton content)',cols:[
pk(), c('name','varchar'), c('handle','varchar'), fk('fieldLayoutId'), c('sortOrder','smallint unsigned'), ...tsu(),
]},
// ── SITE / MULTI-SITE ────────────────────────────────────────────────────────
{name:'elements_sites',group:'site',desc:'Per-site content, slug, URI, and enable state — UNIQUE(elementId, siteId)',cols:[
pk(), fk('elementId'), fk('siteId'),
c('title','varchar','site-specific title'), c('slug','varchar'), c('uri','varchar'),
c('content','json','all custom field values for this site'), c('enabled','tinyint(1)','per-site enabled flag'),
...ts(), c('uid','char(36)'),
]},
{name:'assets_sites',group:'site',desc:'Per-site alt text override for assets',cols:[
fk('assetId'), fk('siteId'), c('alt','text','site-specific alt text'),
]},
{name:'sites',group:'site',desc:'Site (locale) definitions',cols:[
pk(), fk('groupId'), c('primary','tinyint(1)'), c('enabled','varchar'),
c('name','varchar'), c('handle','varchar'), c('language','varchar'),
c('hasUrls','tinyint(1)'), c('baseUrl','varchar'), c('sortOrder','smallint unsigned'),
...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'sitegroups',group:'site',desc:'Site group containers',cols:[
pk(), c('name','varchar'), ...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'sections_sites',group:'site',desc:'Per-site section routing — UNIQUE(sectionId, siteId)',cols:[
pk(), fk('sectionId'), fk('siteId'),
c('hasUrls','tinyint(1)'), c('uriFormat','text'), c('template','varchar(500)'), c('enabledByDefault','tinyint(1)'), ...tsu(),
]},
{name:'categorygroups_sites',group:'site',desc:'Per-site category group URL/template settings',cols:[
pk(), fk('groupId'), fk('siteId'), c('hasUrls','tinyint(1)'), c('uriFormat','text'), c('template','varchar(500)'), ...tsu(),
]},
// ── WORKFLOW ─────────────────────────────────────────────────────────────────
{name:'drafts',group:'workflow',desc:'Draft metadata — element row in elements has draftId FK',cols:[
pk(), fk('canonicalId'), fk('creatorId'),
c('provisional','tinyint(1)','auto-save draft'), c('name','varchar'), c('notes','text'),
c('trackChanges','tinyint(1)'), c('dateLastMerged','datetime'), c('saved','tinyint(1)'),
]},
{name:'revisions',group:'workflow',desc:'Revision metadata — element row has revisionId FK',cols:[
pk(), fk('canonicalId'), fk('creatorId'), c('num','int','revision number'), c('notes','text'),
]},
{name:'changedattributes',group:'workflow',desc:'Tracks which native attributes changed for draft merging',cols:[
fk('elementId'), fk('siteId'), c('attribute','varchar','changed attribute name'),
c('dateUpdated','datetime'), c('propagated','tinyint(1)'), fk('userId'),
]},
{name:'changedfields',group:'workflow',desc:'Tracks which fields changed for draft merging',cols:[
fk('elementId'), fk('siteId'), fk('fieldId'), c('layoutElementUid','char(36)'),
c('dateUpdated','datetime'), c('propagated','tinyint(1)'), fk('userId'),
]},
{name:'elementactivity',group:'workflow',desc:'Live CP "who is editing?" presence tracking',cols:[
fk('elementId'), fk('userId'), fk('siteId'), fk('draftId'),
c('type','varchar','viewing / saving …'), c('timestamp','datetime'),
]},
// ── FIELDS & LAYOUT ──────────────────────────────────────────────────────────
{name:'fields',group:'field',desc:'Field definitions (CKEditor, PlainText, Assets, Matrix…)',cols:[
pk(), c('name','text'), c('handle','varchar(64)'),
c('context','varchar','global / layout:uid'), c('columnSuffix','char(8)'), c('instructions','text'),
c('searchable','tinyint(1)'), c('translationMethod','varchar'), c('translationKeyFormat','text'),
c('type','varchar','field class'), c('settings','text','JSON-serialized config'),
...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'fieldlayouts',group:'field',desc:'Field layout definitions (tabs, element positions)',cols:[
pk(), c('type','varchar','owner element class'), c('config','json','full layout tab/element config'),
...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'relations',group:'field',desc:'Element relation field values — UNIQUE(fieldId, sourceId, sourceSiteId, targetId)',cols:[
pk(), fk('fieldId'), fk('sourceId'), fk('sourceSiteId'), fk('targetId'), c('sortOrder','smallint unsigned'), ...tsu(),
]},
{name:'searchindex',group:'field',desc:'Full-text keyword search index',cols:[
fk('elementId'), fk('siteId'), c('attribute','varchar'), c('fieldId','int'), c('keywords','mediumtext'),
]},
{name:'searchindexqueue',group:'field',desc:'Queue of elements pending search re-index',cols:[
pk(), fk('elementId'), fk('siteId'), c('reserved','tinyint(1)'),
]},
{name:'searchindexqueue_fields',group:'field',desc:'Fields within a search index queue job',cols:[
fk('jobId'), c('fieldHandle','varchar'),
]},
// ── COMMERCE ─────────────────────────────────────────────────────────────────
{name:'commerce_stores',group:'commerce',desc:'Store instances — multi-store support in Commerce 5',cols:[
pk(), c('name','varchar'), c('handle','varchar'), c('primary','tinyint(1)'), c('sortOrder','int'), c('uid','char(36)'), ...ts(),
]},
{name:'commerce_orders',group:'commerce',desc:'Order element — extends elements — core Commerce table',cols:[
fk('id','int'), fk('storeId'), fk('gatewayId'), fk('customerId'), fk('orderStatusId'),
fk('billingAddressId'), fk('shippingAddressId'), fk('estimatedBillingAddressId'), fk('estimatedShippingAddressId'),
fk('paymentSourceId'),
c('number','varchar(32)','unique order number'), c('reference','varchar'), c('couponCode','varchar'),
c('itemTotal','decimal(14,4)'), c('itemSubtotal','decimal(14,4)'), c('totalQty','int'), c('totalWeight','decimal'),
c('total','decimal(14,4)'), c('totalPrice','decimal(14,4)'), c('totalPaid','decimal(14,4)'),
c('totalDiscount','decimal(14,4)'), c('totalTax','decimal(14,4)'), c('totalShippingCost','decimal(14,4)'),
c('paidStatus','enum','paid / partial / unpaid / overPaid'),
c('email','varchar'), c('isCompleted','tinyint(1)'), c('dateOrdered','datetime'), c('datePaid','datetime'),
c('currency','varchar'), c('paymentCurrency','varchar'), c('lastIp','varchar'),
c('orderLanguage','varchar(12)'), c('origin','enum','web / cp / remote'),
c('shippingMethodHandle','varchar'), c('shippingMethodName','varchar'),
c('recalculationMode','enum','all / none / adjustmentsOnly'),
c('returnUrl','text'), c('cancelUrl','text'), c('orderSiteId','int'), ...tsu(),
]},
{name:'commerce_lineitems',group:'commerce',desc:'Line items within an order',cols:[
pk(), fk('orderId'), fk('purchasableId'), fk('taxCategoryId'), fk('shippingCategoryId'),
c('type','enum','purchasable / custom'), c('description','text'), c('options','json'), c('optionsSignature','varchar'),
c('price','decimal(14,4)'), c('promotionalPrice','decimal(14,4)'), c('salePrice','decimal(14,4)'),
c('sku','varchar'), c('weight','decimal'), c('height','decimal'), c('length','decimal'), c('width','decimal'),
c('subtotal','decimal(14,4)'), c('total','decimal(14,4)'), c('qty','int'),
c('note','text'), c('privateNote','text'), c('hasFreeShipping','tinyint(1)'), c('isPromotable','tinyint(1)'),
c('snapshot','longtext','purchasable data snapshot at order time'), ...tsu(),
]},
{name:'commerce_orderadjustments',group:'commerce',desc:'Tax, shipping, discount adjustments on an order',cols:[
pk(), fk('orderId'), fk('lineItemId'), c('type','varchar'), c('name','varchar'), c('description','text'),
c('amount','decimal(14,4)'), c('included','tinyint(1)'), c('isEstimated','tinyint(1)'), c('sourceSnapshot','longtext'), ...tsu(),
]},
{name:'commerce_orderstatuses',group:'commerce',desc:'Order status state machine per store',cols:[
pk(), fk('storeId'), c('name','varchar'), c('handle','varchar'), c('color','enum'), c('description','text'),
c('default','tinyint(1)'), c('sortOrder','int'), c('dateDeleted','datetime'), ...tsu(),
]},
{name:'commerce_orderhistories',group:'commerce',desc:'Audit log of order status changes',cols:[
pk(), fk('orderId'), fk('userId'), c('userName','varchar'), fk('prevStatusId'), fk('newStatusId'), c('message','text'), ...tsu(),
]},
{name:'commerce_ordernotices',group:'commerce',desc:'Notices / warnings attached to an order',cols:[
pk(), fk('orderId'), c('type','varchar'), c('attribute','varchar'), c('message','text'), ...tsu(),
]},
{name:'commerce_transactions',group:'commerce',desc:'Payment gateway transaction records',cols:[
pk(), fk('orderId'), fk('gatewayId'), fk('userId'), fk('parentId'),
c('hash','varchar'), c('type','enum','authorize / capture / purchase / refund'),
c('amount','decimal(14,4)'), c('paymentAmount','decimal'), c('currency','varchar'), c('paymentCurrency','varchar'),
c('paymentRate','decimal'), c('status','enum','pending / redirect / success / failed / processing'),
c('reference','varchar'), c('code','varchar'), c('message','text'), c('note','text'), c('response','text'), ...tsu(),
]},
{name:'commerce_products',group:'commerce',desc:'Product element — extends elements',cols:[
fk('id','int'), fk('typeId'), fk('defaultVariantId'),
c('postDate','datetime'), c('expiryDate','datetime'),
c('defaultSku','varchar'), c('defaultPrice','decimal(14,4)'),
c('defaultHeight','decimal'), c('defaultLength','decimal'), c('defaultWidth','decimal'), c('defaultWeight','decimal'), ...tsu(),
]},
{name:'commerce_variants',group:'commerce',desc:'Variant element — purchasable — extends elements',cols:[
fk('id','int'), fk('productId'),
c('sku','varchar'), c('price','decimal(14,4)'),
c('width','decimal'), c('height','decimal'), c('length','decimal'), c('weight','decimal'),
c('stock','int'), c('hasUnlimitedStock','tinyint(1)'), c('minQty','int'), c('maxQty','int'),
c('isDefault','tinyint(1)'), c('deletedWithProduct','tinyint(1)'), c('sortOrder','int'), ...tsu(),
]},
{name:'commerce_purchasables',group:'commerce',desc:'Purchasable registry — any buyable element',cols:[
pk(), fk('elementId'), c('sku','varchar','UNIQUE purchasable SKU'), c('price','decimal(14,4)'), c('description','text'), ...tsu(),
]},
{name:'commerce_purchasables_stores',group:'commerce',desc:'Per-store purchasable price/availability settings',cols:[
pk(), fk('purchasableId'), fk('storeId'),
c('basePrice','decimal(14,4)'), c('basePromotionalPrice','decimal(14,4)'),
c('promotable','tinyint(1)'), c('availableForPurchase','tinyint(1)'), c('freeShipping','tinyint(1)'),
c('minQty','int'), c('maxQty','int'), ...tsu(),
]},
{name:'commerce_producttypes',group:'commerce',desc:'Product type configuration',cols:[
pk(), fk('fieldLayoutId'), fk('variantFieldLayoutId'),
c('name','varchar'), c('handle','varchar'), c('hasDimensions','tinyint(1)'),
c('hasVariants','tinyint(1)'), c('hasVariantTitleField','tinyint(1)'),
c('variantTitleFormat','varchar'), c('skuFormat','varchar'), c('descriptionFormat','text'), ...tsu(),
]},
{name:'commerce_producttypes_sites',group:'site',desc:'Per-site product type URL/template settings',cols:[
pk(), fk('productTypeId'), fk('siteId'), c('hasUrls','tinyint(1)'), c('uriFormat','text'), c('template','varchar'), c('enabledByDefault','tinyint(1)'), ...tsu(),
]},
{name:'commerce_catalog_pricing',group:'commerce',desc:'Computed catalog price per purchasable/store/rule/user',cols:[
pk(), fk('purchasableId'), fk('storeId'), fk('catalogPricingRuleId'), fk('userId'),
c('price','decimal(14,4)'), c('dateFrom','datetime'), c('dateTo','datetime'),
c('isPromotionalPrice','tinyint(1)'), c('hasUpdatePending','tinyint(1)'), ...tsu(),
]},
{name:'commerce_catalog_pricing_rules',group:'commerce',desc:'Rules that drive automated catalog price computation',cols:[
pk(), fk('storeId'), c('name','varchar'), c('description','text'),
c('apply','enum','toPercent / toFlat / byPercent / byFlat'), c('applyAmount','decimal(14,4)'), c('applyPriceType','enum'),
c('productCondition','text'), c('variantCondition','text'), c('purchasableCondition','text'), c('customerCondition','text'),
c('enabled','tinyint(1)'), c('isPromotionalPrice','tinyint(1)'), ...tsu(),
]},
{name:'commerce_discounts',group:'commerce',desc:'Discount rules (coupon-based promotions)',cols:[
pk(), fk('storeId'), c('name','varchar'), c('description','text'), c('couponFormat','varchar(20)'),
c('requireCouponCode','tinyint(1)'), c('perUserLimit','int unsigned'), c('perEmailLimit','int unsigned'),
c('totalDiscountUseLimit','int unsigned'), c('dateFrom','datetime'), c('dateTo','datetime'),
c('baseDiscount','decimal(14,4)'), c('perItemDiscount','decimal(14,4)'), c('percentDiscount','decimal(14,4)'),
c('percentageOffSubject','enum','original / discounted'), c('hasFreeShippingForOrder','tinyint(1)'),
c('enabled','tinyint(1)'), c('stopProcessing','tinyint(1)'), c('sortOrder','int'), ...tsu(),
]},
{name:'commerce_coupons',group:'commerce',desc:'Coupon codes linked to discounts',cols:[
pk(), fk('discountId'), c('code','varchar'), c('uses','int'), c('maxUses','int'), ...tsu(),
]},
{name:'commerce_customers',group:'commerce',desc:'Commerce customer profile linked to Craft User element',cols:[
pk(), fk('customerId'), fk('primaryBillingAddressId'), fk('primaryShippingAddressId'), fk('primaryPaymentSourceId'), ...tsu(),
]},
{name:'commerce_gateways',group:'commerce',desc:'Payment gateway configurations',cols:[
pk(), c('type','varchar'), c('name','varchar'), c('handle','varchar'), c('settings','text'),
c('paymentType','enum','authorize / purchase'), c('isFrontendEnabled','varchar(500)'),
c('isArchived','tinyint(1)'), c('dateArchived','datetime'), c('sortOrder','int'), ...tsu(),
]},
{name:'commerce_paymentsources',group:'commerce',desc:'Saved payment methods (cards) per customer',cols:[
pk(), fk('customerId'), fk('gatewayId'), c('token','text'), c('description','text'), c('response','text'), ...tsu(),
]},
{name:'commerce_subscriptions',group:'commerce',desc:'Subscription element — extends elements',cols:[
fk('id','int'), fk('userId'), fk('planId'), fk('gatewayId'), fk('orderId'), fk('paymentSourceId'),
c('reference','varchar'), c('trialDays','int'), c('nextPaymentDate','datetime'),
c('hasStarted','tinyint(1)'), c('isSuspended','tinyint(1)'), c('dateSuspended','datetime'),
c('isCanceled','tinyint(1)'), c('dateCanceled','datetime'), c('isExpired','tinyint(1)'), c('dateExpired','datetime'),
c('subscriptionData','longtext'), ...tsu(),
]},
{name:'commerce_plans',group:'commerce',desc:'Subscription plan definitions per gateway',cols:[
pk(), fk('gatewayId'), fk('planInformationId'), c('name','varchar'), c('handle','varchar'),
c('reference','varchar'), c('enabled','tinyint(1)'), c('planData','text'),
c('isArchived','tinyint(1)'), c('dateArchived','datetime'), c('sortOrder','int'), ...tsu(),
]},
{name:'commerce_inventoryitems',group:'commerce',desc:'Inventory item metadata (origin country, HS code)',cols:[
pk(), fk('purchasableId'), c('countryCodeOfOrigin','varchar'), c('administrativeAreaCodeOfOrigin','varchar'), c('harmonizedSystemCode','varchar'), ...tsu(),
]},
{name:'commerce_inventorylocations',group:'commerce',desc:'Physical stock locations (warehouses)',cols:[
pk(), c('handle','varchar'), c('name','varchar'), fk('addressId'), ...ts(), c('dateDeleted','datetime'), c('uid','char(36)'),
]},
{name:'commerce_inventorylocations_stores',group:'commerce',desc:'Location ↔ Store availability pivot',cols:[
pk(), fk('inventoryLocationId'), fk('storeId'), c('sortOrder','int'), ...tsu(),
]},
{name:'commerce_inventorytransactions',group:'commerce',desc:'Append-only inventory movement ledger',cols:[
pk(), fk('inventoryLocationId'), fk('inventoryItemId'),
c('movementHash','varchar'), c('quantity','int'),
c('type','enum','incoming / available / committed / reserved / damaged / safety / fulfilled / qualityControl'),
c('note','varchar'), fk('transferId'), fk('lineItemId'), fk('userId'),
c('dateCreated','datetime'), c('uid','char(36)'),
]},
{name:'commerce_transfers',group:'commerce',desc:'Stock transfer orders between inventory locations',cols:[
pk(), fk('originLocationId'), fk('destinationLocationId'), c('status','enum'), c('dateCreated','datetime'), c('uid','char(36)'),
]},
{name:'commerce_taxcategories',group:'commerce',desc:'Tax category definitions',cols:[
pk(), c('name','varchar'), c('handle','varchar'), c('description','varchar'), c('default','tinyint(1)'), ...tsu(),
]},
{name:'commerce_taxzones',group:'commerce',desc:'Geographic tax zones (country/state conditions)',cols:[
pk(), c('name','varchar'), c('description','text'), c('condition','text'), c('isCountryBased','tinyint(1)'), c('default','tinyint(1)'), ...tsu(),
]},
{name:'commerce_taxrates',group:'commerce',desc:'Tax rate rules per zone/category/store',cols:[
pk(), fk('taxZoneId'), fk('taxCategoryId'), fk('storeId'),
c('name','varchar'), c('rate','decimal(14,4)'), c('include','tinyint(1)'), c('isVat','tinyint(1)'),
c('taxable','enum'), c('removeVatIncluded','tinyint(1)'), ...tsu(),
]},
{name:'commerce_shippingmethods',group:'commerce',desc:'Shipping method definitions per store',cols:[
pk(), fk('storeId'), c('name','varchar'), c('handle','varchar'), c('enabled','tinyint(1)'), c('orderCondition','text'), c('sortOrder','int'), ...tsu(),
]},
{name:'commerce_shippingzones',group:'commerce',desc:'Geographic shipping zones',cols:[
pk(), c('name','varchar'), c('description','text'), c('condition','text'), ...tsu(),
]},
{name:'commerce_shippingrules',group:'commerce',desc:'Shipping rules within a method (rate conditions)',cols:[
pk(), fk('methodId'), fk('shippingZoneId'), fk('storeId'),
c('name','varchar'), c('description','text'), c('enabled','tinyint(1)'), c('priority','int'),
c('baseRate','decimal'), c('perItemRate','decimal'), c('weightRate','decimal'), c('percentageRate','decimal'),
c('minRate','decimal'), c('maxRate','decimal'),
c('minQty','int'), c('maxQty','int'), c('minTotal','decimal'), c('maxTotal','decimal'),
c('minWeight','decimal'), c('maxWeight','decimal'), ...tsu(),
]},
{name:'commerce_shippingcategories',group:'commerce',desc:'Shipping category definitions per store',cols:[
pk(), fk('storeId'), c('name','varchar'), c('handle','varchar'), c('description','varchar'), c('default','tinyint(1)'), ...tsu(),
]},
{name:'commerce_emails',group:'commerce',desc:'Order notification email templates per store',cols:[
pk(), fk('storeId'), c('name','varchar'), c('subject','varchar'),
c('recipientType','enum','customer / custom'), c('to','varchar'), c('bcc','varchar'), c('cc','varchar'), c('replyTo','varchar'),
c('enabled','tinyint(1)'), c('templatePath','varchar'), c('pdfId','int'), c('language','varchar'), ...tsu(),
]},
{name:'commerce_pdfs',group:'commerce',desc:'PDF template definitions for order documents',cols:[
pk(), fk('storeId'), c('name','varchar'), c('handle','varchar'), c('description','varchar'),
c('templatePath','varchar'), c('fileNameFormat','varchar'), c('paperOrientation','varchar'), c('paperSize','varchar'),
c('enabled','tinyint(1)'), c('isDefault','tinyint(1)'), c('sortOrder','int'), c('language','varchar'), c('linkExpiry','int'), ...tsu(),
]},
{name:'commerce_paymentcurrencies',group:'commerce',desc:'Accepted payment currencies per store with exchange rates',cols:[
pk(), fk('storeId'), c('iso','varchar(3)'), c('primary','tinyint(1)'), c('rate','decimal(14,4)'), ...tsu(),
]},
{name:'commerce_sitestores',group:'site',desc:'Site ↔ Store mapping pivot',cols:[
pk(), fk('siteId'), fk('storeId'), ...tsu(),
]},
].filter((t,i,arr) => arr.findIndex(x=>x.name===t.name)===i); // dedup
// ── Relations ────────────────────────────────────────────────────────────────
const RELS = {};
function addRel(from, to, col, toCol='id') {
(RELS[from]||(RELS[from]=[])).push({dir:'→', table:to, col, toCol});
(RELS[to]||(RELS[to]=[])).push({dir:'←', table:from, col, toCol});
}
[
['elements','elements','canonicalId'],['elements','drafts','draftId'],['elements','revisions','revisionId'],['elements','fieldlayouts','fieldLayoutId'],
['elements_sites','elements','elementId'],['elements_sites','sites','siteId'],
['elements_owners','elements','elementId'],['elements_owners','elements','ownerId'],
['drafts','elements','canonicalId'],['drafts','users','creatorId'],
['revisions','elements','canonicalId'],['revisions','users','creatorId'],
['changedattributes','elements','elementId'],['changedattributes','sites','siteId'],
['changedfields','elements','elementId'],['changedfields','sites','siteId'],['changedfields','fields','fieldId'],
['elementactivity','elements','elementId'],['elementactivity','sites','siteId'],
['entries','elements','id'],['entries','sections','sectionId'],['entries','entrytypes','typeId'],
['entries_authors','entries','entryId'],['entries_authors','users','authorId'],
['sections','structures','structureId'],
['sections_entrytypes','sections','sectionId'],['sections_entrytypes','entrytypes','typeId'],
['sections_sites','sections','sectionId'],['sections_sites','sites','siteId'],
['assets','elements','id'],['assets','volumes','volumeId'],['assets','volumefolders','folderId'],['assets','users','uploaderId'],
['assets_sites','assets','assetId'],['assets_sites','sites','siteId'],
['volumefolders','volumes','volumeId'],['volumefolders','volumefolders','parentId'],
['users','elements','id'],['users','assets','photoId'],
['usergroups_users','usergroups','groupId'],['usergroups_users','users','userId'],
['userpermissions_usergroups','userpermissions','permissionId'],['userpermissions_usergroups','usergroups','groupId'],
['userpermissions_users','userpermissions','permissionId'],['userpermissions_users','users','userId'],
['sessions','users','userId'],['authenticator','users','userId'],['webauthn','users','userId'],
['addresses','elements','id'],['contentblocks','elements','id'],
['categories','elements','id'],['categories','categorygroups','groupId'],
['categorygroups_sites','categorygroups','groupId'],['categorygroups_sites','sites','siteId'],
['tags','elements','id'],['tags','taggroups','groupId'],
['structureelements','structures','structureId'],['structureelements','elements','elementId'],
['relations','fields','fieldId'],['relations','elements','sourceId'],['relations','elements','targetId'],
['sites','sitegroups','groupId'],['gqltokens','gqlschemas','schemaId'],
['commerce_orders','elements','id'],['commerce_orders','commerce_stores','storeId'],
['commerce_orders','commerce_gateways','gatewayId'],['commerce_orders','commerce_customers','customerId'],
['commerce_orders','commerce_orderstatuses','orderStatusId'],
['commerce_lineitems','commerce_orders','orderId'],['commerce_lineitems','commerce_purchasables','purchasableId'],
['commerce_orderadjustments','commerce_orders','orderId'],
['commerce_orderhistories','commerce_orders','orderId'],
['commerce_transactions','commerce_orders','orderId'],['commerce_transactions','commerce_gateways','gatewayId'],
['commerce_products','elements','id'],['commerce_products','commerce_producttypes','typeId'],
['commerce_variants','elements','id'],['commerce_variants','commerce_products','productId'],
['commerce_purchasables','elements','elementId'],
['commerce_purchasables_stores','commerce_purchasables','purchasableId'],['commerce_purchasables_stores','commerce_stores','storeId'],
['commerce_catalog_pricing','commerce_purchasables','purchasableId'],['commerce_catalog_pricing','commerce_stores','storeId'],
['commerce_catalog_pricing_rules','commerce_stores','storeId'],
['commerce_discounts','commerce_stores','storeId'],['commerce_coupons','commerce_discounts','discountId'],
['commerce_customers','users','customerId'],
['commerce_inventoryitems','commerce_purchasables','purchasableId'],
['commerce_inventorytransactions','commerce_inventorylocations','inventoryLocationId'],
['commerce_inventorytransactions','commerce_inventoryitems','inventoryItemId'],
['commerce_subscriptions','elements','id'],
].forEach(([f,t,col,tc])=>addRel(f,t,col,tc||'id'));
// ── State ─────────────────────────────────────────────────────────────────────
const hiddenGroups = new Set();
let activeSql = null;
// ── Build sidebar ─────────────────────────────────────────────────────────────
function buildSidebar() {
const sb = document.getElementById('sb');
const byGrp = {};
TABLES.forEach(t => (byGrp[t.group]||(byGrp[t.group]=[])).push(t));
Object.entries(GRP).forEach(([gk,gv]) => {
const tabs = byGrp[gk]||[];
if(!tabs.length) return;
const grpDiv = document.createElement('div');
const lbl = document.createElement('div');
lbl.className = 'sb-group';
lbl.innerHTML = `<span style="width:8px;height:8px;border-radius:2px;background:var(--c-${gk});flex-shrink:0;display:inline-block"></span>${gv.lbl}`;
grpDiv.appendChild(lbl);
tabs.forEach(t => {
const btn = document.createElement('button');
btn.className = 'sb-item';
btn.dataset.sbt = t.name;
btn.textContent = t.name;
btn.onclick = () => jumpTo(t.name);
grpDiv.appendChild(btn);
});
sb.appendChild(grpDiv);
});
}
// ── Build canvas ──────────────────────────────────────────────────────────────
function buildCanvas() {
const cv = document.getElementById('cv');
TABLES.forEach(t => {
const card = document.createElement('div');
card.className = 'tcard';
card.id = 'card-'+t.name;
card.dataset.group = t.group;
if(t._dropped) card.classList.add('has-dropped');
const gv = GRP[t.group];
// Header
const hdr = document.createElement('div');
hdr.className = 'card-hdr';
const droppedLabel = t._dropped ? '<div class="dropped-label">⚠ Dropped</div>' : '';
const migLabel = t._fromMigration ? '<div style="font-size:9px;color:var(--c-workflow);margin-top:2px">via migration</div>' : '';
hdr.innerHTML = `<div><div class="tname">${t.name}</div><div class="gbadge ${gv.cls}">${gv.lbl}</div>${droppedLabel}${migLabel}</div>
<button class="chev" title="Toggle columns" onclick="event.stopPropagation();togCols('${t.name}')">
<svg width="13" height="13" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2.5"><path d="m18 15-6-6-6 6"/></svg>
</button>`;
hdr.addEventListener('click', (e) => { if(e.target.closest('.chev')) return; showRel(t.name); });
// Columns
const ul = document.createElement('ul');
ul.className = 'cols-list';
ul.dataset.cl = t.name;
t.cols.forEach(col => {
if(!col||!col.n) return;
const li = document.createElement('li');
li.className = 'col-r';
const iconHtml = col.pk ? '<span class="cico pk">🔑</span>' : col.fk ? '<span class="cico fk">🔗</span>' : '<span class="cico"></span>';
const migBadge = col._mig ? `<span class="migbadge">${col._mig.replace('added ','')}</span>` : '';
if(col._mig) li.classList.add('migrated');
li.innerHTML = `${iconHtml}<span class="cn" title="${col.n}">${col.n}${migBadge}</span><span class="ct">${col.t}</span>${col.note&&col.note!==col._mig?`<span class="cnote">${col.note}</span>`:''}`;
ul.appendChild(li);
});
// Footer
const foot = document.createElement('div');
foot.className = 'card-foot';
foot.title = 'Show CREATE TABLE SQL';
foot.textContent = t.desc ? (t.desc.length>58 ? t.desc.slice(0,55)+'…' : t.desc) : `${t.cols.filter(x=>x&&x.n).length} columns`;
foot.onclick = () => showSQL(t.name);
card.appendChild(hdr);
card.appendChild(ul);
card.appendChild(foot);
cv.appendChild(card);
});
}
// ── Toggle columns ────────────────────────────────────────────────────────────
function togCols(name) {
const ul = document.querySelector(`[data-cl="${name}"]`);
if(!ul) return;
const open = ul.style.display !== 'none';
ul.style.display = open ? 'none' : '';
const card = document.getElementById('card-'+name);
if(card) {
const path = card.querySelector('.chev path');
if(path) path.setAttribute('d', open ? 'm6 9 6 6 6-6' : 'm18 15-6-6-6 6');
}
}
function setAll(expand) {
TABLES.forEach(t => {
const ul = document.querySelector(`[data-cl="${t.name}"]`);
if(!ul) return;
ul.style.display = expand ? '' : 'none';
const card = document.getElementById('card-'+t.name);
if(card) {
const path = card.querySelector('.chev path');
if(path) path.setAttribute('d', expand ? 'm18 15-6-6-6 6' : 'm6 9 6 6 6-6');
}
});
}
// ── Jump to table ─────────────────────────────────────────────────────────────
function jumpTo(name) {
const card = document.getElementById('card-'+name);
if(!card || card.classList.contains('gone')) return;
const ul = document.querySelector(`[data-cl="${name}"]`);
if(ul) ul.style.display = '';
card.scrollIntoView({behavior:'smooth', block:'start'});
card.classList.add('hl');
setTimeout(() => card.classList.remove('hl'), 1200);
document.querySelectorAll('.sb-item').forEach(el => el.classList.toggle('active', el.dataset.sbt===name));
}
// ── Group toggle ──────────────────────────────────────────────────────────────
function togGrp(g, el) {
const hiding = !hiddenGroups.has(g);
hiding ? hiddenGroups.add(g) : hiddenGroups.delete(g);
el.classList.toggle('off', hiding);
TABLES.filter(t=>t.group===g).forEach(t => {
const card = document.getElementById('card-'+t.name);
const sbi = document.querySelector(`.sb-item[data-sbt="${t.name}"]`);
if(card) card.classList.toggle('gone', hiding);
if(sbi) sbi.classList.toggle('faded', hiding);
});
applySearch(document.getElementById('srch').value);
}
// ── Search ────────────────────────────────────────────────────────────────────
function applySearch(q) {
q = (q||'').toLowerCase().trim();
TABLES.forEach(t => {
if(hiddenGroups.has(t.group)) return;
const card = document.getElementById('card-'+t.name);
const sbi = document.querySelector(`.sb-item[data-sbt="${t.name}"]`);
if(!q) {
card.classList.remove('gone','dim');
if(sbi) sbi.classList.remove('faded');
return;
}
const hit = t.name.toLowerCase().includes(q) ||
(t.desc||'').toLowerCase().includes(q) ||
t.cols.some(c => c && c.n && (c.n.toLowerCase().includes(q) || (c.note||'').toLowerCase().includes(q)));
card.classList.toggle('dim', !hit);
card.classList.remove('gone');
if(sbi) sbi.classList.toggle('faded', !hit);
});
}
document.getElementById('srch').addEventListener('input', e => applySearch(e.target.value));
// ── SQL modal ─────────────────────────────────────────────────────────────────
function genSQL(t) {
const defs = t.cols.filter(x=>x&&x.n).map(col => {
let line = ` \`${col.n}\` ${col.t.toUpperCase()}`;
if(col.pk) line += ' NOT NULL AUTO_INCREMENT';
if(col.note && col.note!=='FK' && col.note!=='PRIMARY KEY') line += ` -- ${col.note}`;
return line;
});
const pks = t.cols.filter(x=>x&&x.pk).map(x=>`\`${x.n}\``);
if(pks.length) defs.push(` PRIMARY KEY (${pks.join(', ')})`);
const fkeys = t.cols.filter(x=>x&&x.fk&&!x.pk).map(x=>` -- FK: \`${x.n}\``);
return `-- Source: craftcms/cms & craftcms/commerce 5.x\n-- https://github.com/craftcms/cms/blob/5.x/src/migrations/Install.php\n${t.desc?'-- '+t.desc+'\n':''}\nCREATE TABLE \`${t.name}\` (\n${defs.join(',\n')}${fkeys.length?'\n'+fkeys.join('\n'):''}\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`;
}
function showSQL(name) {
const t = TABLES.find(x=>x.name===name);
if(!t) return;
activeSql = t;
document.getElementById('sqlTitle').textContent = name;
const raw = genSQL(t);
document.getElementById('sqlBody').innerHTML = raw
.replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;')
.replace(/(CREATE TABLE|ENGINE|DEFAULT|CHARSET|PRIMARY KEY|NOT NULL|AUTO_INCREMENT)/g,'<span class="kw">$1</span>')
.replace(/(--[^\n]*)/g,'<span class="cm">$1</span>');
document.getElementById('sqlModal').classList.add('open');
}
document.getElementById('sqlAllBtn').onclick = () => {
activeSql = null;
document.getElementById('sqlTitle').textContent = 'All Tables — SQL Definitions';
document.getElementById('sqlBody').textContent = TABLES.map(t=>genSQL(t)).join('\n\n');
document.getElementById('sqlModal').classList.add('open');
};
function closeModal() { document.getElementById('sqlModal').classList.remove('open'); }
document.getElementById('sqlModal').addEventListener('click', e => { if(e.target===e.currentTarget) closeModal(); });
function copySQL() {
const txt = activeSql ? genSQL(activeSql) : TABLES.map(t=>genSQL(t)).join('\n\n');
navigator.clipboard.writeText(txt).then(() => {
const btn = document.querySelector('.copybtn');
btn.textContent='Copied!'; setTimeout(()=>btn.textContent='Copy SQL',1800);
});
}
// ── Relations panel ───────────────────────────────────────────────────────────
function showRel(name) {
const rels = RELS[name]||[];
document.getElementById('rpTitle').textContent = name;
const body = document.getElementById('rpBody');
body.innerHTML = '';
if(!rels.length) { body.innerHTML='<p style="color:var(--text3);font-size:11px">No recorded relations</p>'; }
rels.forEach(r => {
const div = document.createElement('div');
div.className = 'ritem';
div.innerHTML = `<div class="rdir">${r.dir==='→'?'References':'Referenced by'}</div>
<div class="rtable" onclick="jumpTo('${r.table}')">${r.table}</div>
<div class="rcol">via <code>${r.col}</code> → <code>${r.toCol}</code></div>`;
body.appendChild(div);
});
document.getElementById('rpanel').classList.add('open');
}
function closeRP() { document.getElementById('rpanel').classList.remove('open'); }
// ── Theme ──────────────────────────────────────────────────────────────────────
const root = document.documentElement;
function setThemeIcon() {
const dark = root.dataset.theme==='dark';
document.getElementById('thbtn').innerHTML = dark
? '<svg width="15" height="15" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2"><path d="M21 12.79A9 9 0 1 1 11.21 3 7 7 0 0 0 21 12.79z"/></svg>'
: '<svg width="15" height="15" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2"><circle cx="12" cy="12" r="5"/><path d="M12 1v2M12 21v2M4.22 4.22l1.42 1.42M18.36 18.36l1.42 1.42M1 12h2M21 12h2M4.22 19.78l1.42-1.42M18.36 5.64l1.42-1.42"/></svg>';
}
document.getElementById('thbtn').onclick = () => {
root.dataset.theme = root.dataset.theme==='dark' ? 'light' : 'dark';
setThemeIcon();
};
setThemeIcon();
// ── Migration highlight toggle ────────────────────────────────────────────────
function toggleMigHighlight(on) {
document.querySelectorAll('.col-r.migrated').forEach(el => {
el.style.background = on ? 'color-mix(in oklch,var(--c-workflow) 12%,transparent)' : '';
});
document.querySelectorAll('.tcard[data-migrated]').forEach(el => {
el.style.boxShadow = on ? '0 0 0 2px var(--c-workflow)' : '';
});
}
// ── Init ──────────────────────────────────────────────────────────────────────
buildSidebar();
buildCanvas();
</script>
<script data-pplx-inline-edit>
(function(){
if(window===window.top)return;
function inlineAll(orig,clone){
if(orig.nodeType!==1)return;
try{
var cs=getComputedStyle(orig);
var t='';
for(var i=0;i<cs.length;i++){t+=cs[i]+':'+cs.getPropertyValue(cs[i])+';';}
clone.style.cssText=t;
}catch(e){}
var oc=orig.children,cc=clone.children;
for(var j=0;j<oc.length&&j<cc.length;j++){inlineAll(oc[j],cc[j]);}
}
function stripExternal(clone){
var imgs=clone.querySelectorAll('img');
for(var i=0;i<imgs.length;i++){
var s=imgs[i].getAttribute('src');
if(s&&!s.startsWith('data:'))imgs[i].removeAttribute('src');
}
var all=clone.querySelectorAll('*');
for(var i=0;i<all.length;i++){
var st=all[i].style.cssText;
if(st&&st.indexOf('url(')>=0){
all[i].style.cssText=st.replace(/url\(["']?(?!data:)[^)"']*["']?\)/gi,'none');
}
}
}
window.addEventListener('message',function(e){
if(!e.data||e.data.type!=='INLINE_EDIT_CAPTURE_REQUEST')return;
var scrollX=window.scrollX||window.pageXOffset||0;
var scrollY=window.scrollY||window.pageYOffset||0;
var w=window.innerWidth,h=window.innerHeight;
try{
var clone=document.documentElement.cloneNode(true);
var rm=clone.querySelectorAll('script,link[rel="stylesheet"],style');
for(var i=0;i<rm.length;i++){rm[i].remove();}
inlineAll(document.documentElement,clone);
stripExternal(clone);
var html=new XMLSerializer().serializeToString(clone);
var svg='<svg xmlns="http://www.w3.org/2000/svg" width="'+w+'" height="'+h+'">'
+'<foreignObject width="100%" height="100%">'
+'<div xmlns="http://www.w3.org/1999/xhtml" style="width:'+w+'px;height:'+h+'px;overflow:hidden">'
+'<div style="transform:translate(-'+scrollX+'px,-'+scrollY+'px);transform-origin:top left">'
+html+'</div></div></foreignObject></svg>';
var svgUrl='data:image/svg+xml;charset=utf-8,'+encodeURIComponent(svg);
var img=new Image();
img.onload=function(){
var c=document.createElement('canvas');c.width=w;c.height=h;
c.getContext('2d').drawImage(img,0,0);
window.parent.postMessage({type:'INLINE_EDIT_SCREENSHOT_RESULT',dataUrl:c.toDataURL('image/png'),scrollX:scrollX,scrollY:scrollY},'*');
};
img.onerror=function(){
window.parent.postMessage({type:'INLINE_EDIT_SCREENSHOT_RESULT',dataUrl:null,scrollX:scrollX,scrollY:scrollY},'*');
};
img.src=svgUrl;
}catch(err){
window.parent.postMessage({type:'INLINE_EDIT_SCREENSHOT_RESULT',dataUrl:null,scrollX:scrollX,scrollY:scrollY},'*');
}
});
})();
</script></body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment