Skip to content

Data model overview

The template table group lives in the dedicated cms_db schema and is defined entirely in hq/sql_models/template.py. This page is the map: the live tables, their history mirrors, the columns every table shares, and how they relate to each other. The per-table detail pages drill into each one.

The four live tables

Table Model class What it holds
templates Template A named, versioned template a site can adopt, including its full CSS.
template_blocks TemplateBlock A block of markup + settings within a template, keyed by element_type.
site_block_overrides SiteBlockOverride A site-specific override of a template block, keyed by element_type.
page_types PageType A data-driven page descriptor (route + layout + sources) a template exposes.

These are detailed on:

The columns every table shares (CMSBase)

Every live table in this group is declared as class X(CMSBase, Base). CMSBase (in hq/sql_models/base.py) composes three mixins so the standard columns are declared once:

Column Type Behaviour
id INT, auto-increment PK From IntPKMixin.
created_at DATETIME NOT NULL From TimestampMixin; server-defaults to now on insert.
updated_at DATETIME NOT NULL From TimestampMixin; now on insert, refreshes to now on every update (ON UPDATE CURRENT_TIMESTAMP).
is_active TINYINT(1) NOT NULL, default 1 From ActiveMixin; the soft-active flag.

When a per-table page lists only the table's own columns, assume these four are present as well.

Edit-tracking columns (AISD-126)

Each live table also carries two edit-tracking columns:

  • edited_by -- INT, nullable, indexed, a foreign key to users.id. The user who produced the row's current version.
  • creator_type -- VARCHAR(64), nullable, a string reference to creator_types.name (e.g. user, ai_generated). It is not a foreign key, following the schema's "string values, lookup tables for admin validation" decision.

Both are additive and nullable.

Foreign-key relationships

                    templates  (one)
                   /    |     \
        (many)    /     |      \   (many)
     page_types       sites    template_blocks
                        |              :
                        | (many)       : (override by element_type, NOT a FK)
              site_block_overrides ....:
  • templates ← page_types (page_types.template_id → templates.id): a template owns its page descriptors. Template.page_types is the ORM relationship.
  • templates ← template_blocks (template_blocks.template_id → templates.id): a template owns its blocks. Template.blocks is the relationship.
  • templates ← sites (sites.template_id → templates.id): a site adopts a template. Template.sites / Site.template. (This FK was deferred in AISD-84 and completed once templates joined the metadata, because create_all cannot emit a FK to a table it has not yet registered.)
  • sites ← site_block_overrides (site_block_overrides.site_id → sites.id): a site owns its block overrides. Site.block_overrides.

The element_type string key (not a foreign key)

element_type on both template_blocks and site_block_overrides is a string reference to template_elements.name, deliberately not a foreign key. It is indexed on both tables for the render-time lookup.

Why a string and not a FK? Keying an override on the element name (rather than on a template_blocks.id FK) lets a site's overrides survive a template switch: if a site moves from template A to template B, its override for the header element still matches, because both templates name that element header. A FK to a specific block id would dangle the moment the template changed. See Template blocks & overrides for the full resolution order.

History mirrors

Each live table has a flat history-mirror table that records a row's prior state:

Live table History mirror Model class
templates template_history TemplateHistory
template_blocks template_block_history TemplateBlockHistory
site_block_overrides site_block_override_history SiteBlockOverrideHistory
page_types page_type_history PageTypeHistory

The mirror tables share a deliberate shape (the AISD-126 ContentHistory pattern):

  • Their own surrogate id primary key (from IntPKMixin).
  • A plain indexed INT pointing back at the live row (e.g. template_id, page_type_id) with no foreign key -- so the history survives even if the live row is deleted.
  • A changed_at timestamp.
  • Every mirrored business column is nullable, with no unique constraint and no FK -- a mirror records whatever the row was, not a valid current state.
  • No relationship() -- mirrors are decoupled, read for audit only.

Rows are inserted only by an AFTER UPDATE trigger (e.g. trg_page_types_history), one per live table. The trigger fires on update, copies the OLD.* values into the mirror, and stamps changed_at = NOW(). The mirror's column list is kept in lock-step with that trigger's INSERT.

css_version is intentionally not mirrored

templates.css_version is a cache pointer, not editorial content, so template_history does not carry it and the trg_templates_history trigger is left untouched when css_version is added. See Templates & CSS in the DB.

How the tables are built and checked

  • Built from Base.metadata.create_all() -- the models are authoritative.
  • Post-create steps (guarded ALTERs for existing DBs, the history triggers, the seed rows) live in hq/sql_models/ddl/*.sql and are idempotent / upsert-safe.
  • Drift-checked by hq.cms.db check, which compares the live database back against this metadata.

Importing hq.sql_models.template opens no database connection.