Templates & CSS in the DB

This page covers the templates table -- the row a site adopts to get its look and its set of pages -- with a focus on the design decision that often surprises people: the full stylesheet is stored in the database, in templates.css, with a small templates.css_version pointer that busts the edge cache.

The templates table

A templates row (model Template) is a named, versioned template a site can adopt. In addition to the shared CMSBase columns (id, created_at, updated_at, is_active) it has:

Column	Type	Notes
name	VARCHAR(255) NOT NULL	Human-readable template name.
slug	VARCHAR(128) NOT NULL, unique	Stable identifier used in URLs and serve paths (e.g. celebrity-v1).
description	TEXT, nullable	Free-text description.
version	VARCHAR(32), nullable	Template version label.
preview_image	VARCHAR(255), nullable	URL/path to a preview thumbnail.
settings_schema	JSON, nullable	JSON schema describing the template's configurable settings.
default_settings	JSON, nullable	JSON of default setting values.
css	LONGTEXT, nullable	The template's full stylesheet, stored in the DB.
css_version	VARCHAR(32), nullable	Cache-bust pointer for the CSS above.
edited_by	INT FK → users.id, nullable, indexed	Edit tracking (AISD-126).
creator_type	VARCHAR(64), nullable	String reference to creator_types.name (not a FK).

Relationships: blocks (1:N TemplateBlock), sites (1:N Site via Site.template_id), and page_types (1:N PageType).

Why the CSS lives in the database

templates.css is a MySQL LONGTEXT holding the template's entire stylesheet. LONGTEXT (not TEXT) because a real stylesheet easily exceeds the TEXT 64 KB limit.

Storing CSS in the DB rather than as an object (an R2/S3 file) is a deliberate choice:

• It is admin-editable content. A template's CSS is something an admin edits, the same way they edit a block's markup. Keeping it in the DB means it flows through the same admin tooling, the same edit-tracking (edited_by / creator_type), and the same review path as everything else.
• It is still edge-cached. Storing it in the DB does not mean serving it from the DB on every request. cms-api serves the raw CSS once; the worker links it with a versioned URL and the CDN edge caches it. The browser fetches the stylesheet from the edge, not from the origin database, on the hot path.

How css_version busts the cache

templates.css_version is a short string -- it can carry a content hash -- and it is the cache-bust pointer for the stylesheet. The worker embeds it in the stylesheet URL it writes into every rendered page:

/_assets/styles/<slug>/<css_version>.css

for example:

/_assets/styles/celebrity-v1/1.css

Because the version is part of the URL path, the CDN treats each version as a distinct, independently cacheable asset:

1. An admin edits templates.css and bumps templates.css_version (e.g. from 1 to 2).
2. The next render emits /_assets/styles/celebrity-v1/2.css -- a URL the edge has never seen, so it misses the cache and fetches the new CSS once.
3. Every browser now loads the new stylesheet. The old .../1.css stays cached and harmless for anyone mid-session.

The headline benefit: bumping css_version publishes new CSS with no worker redeploy. The styling is data, and changing it is a data edit plus a pointer bump, never a code deploy.

The seed in 0015_page_types_and_css_version.sql sets the initial version for the shipped template:

SET @css := '1';
UPDATE templates SET css_version = @css
 WHERE slug = 'celebrity-v1' AND (css_version IS NULL OR css_version = '');

Where the raw CSS is served

cms-api exposes the raw stylesheet at:

/public/v2/templates/<slug>/css

The worker fetches it from there (once, then served from the edge under the versioned /_assets/styles/<slug>/<css_version>.css path described above).

Why css_version is not in the history mirror

template_history mirrors templates' editorial columns, but not css_version. The reason is that css_version is a cache pointer, not editorial content -- it records "which cached copy is current", which is operational state, not an edit worth auditing. So:

• When 0015_page_types_and_css_version.sql adds the css_version column to existing databases, it leaves the trg_templates_history trigger untouched.
• TemplateHistory (the model) has no css_version column.

css itself (the stylesheet body) is part of the template's editorial content; only the version pointer is excluded from history.

Adding the columns to existing databases

On a brand-new database create_all() adds css and css_version straight from the model. On an existing database the DDL file adds them with guarded, idempotent ALTERs -- it checks information_schema.columns first and only runs the ALTER if the column is absent, so re-running the file is a no-op:

SET @c := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'templates'
      AND column_name = 'css_version'
);
SET @ddl := IF(@c = 0,
    'ALTER TABLE templates ADD COLUMN css_version VARCHAR(32) NULL',
    'SELECT 1');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

The same guarded pattern adds css LONGTEXT NULL.