SQLAlchemy models for the CMS template table group (epic AISD-36, task AISD-85).
The three template tables -- templates, template_blocks,
site_block_overrides -- live in the dedicated cms_db schema. Each model
composes :class:hq.sql_models.base.CMSBase (INT auto-increment id,
created_at / updated_at audit columns, an is_active flag) so the
common columns are declared once, mirroring :mod:hq.sql_models.lookup and
:mod:hq.sql_models.core.
These models are the single source of truth for the schema: the bootstrap
creates the tables from Base.metadata.create_all(), so every foreign key,
index, and unique constraint is declared here (column unique=True /
index=True) and emitted by create_all -- there is no hand-written
CREATE TABLE DDL. relationship() links are declared up front so the ORM
mapper configures cleanly. The hq.cms.db check drift-check compares the live
DB back against this metadata. Importing this module opens no database
connection.
This module brings templates into the metadata, which lets
:mod:hq.sql_models.core complete the previously deferred
sites.template_id -> templates.id foreign key (AISD-84 deferred it because
create_all cannot emit a FK to a table not yet registered).
element_type on both template_blocks and site_block_overrides is a
string reference to template_elements.name -- intentionally not a
foreign key, following the db-design "string values, lookups for admin
validation" decision. Keying overrides on element_type (rather than a
template_blocks.id FK) lets a site's overrides survive a template switch. The
column is indexed on both tables for the render-time lookup.
Template resolution order (the precedence the renderer applies):
- HTML:
template_blocks.html_content -> overridden by
site_block_overrides.html_content (most specific wins).
- settings:
template_elements.default_settings ->
template_blocks.settings -> site_block_overrides.settings (each layer
overrides the previous, site override most specific).
html_content is a MySQL LONGTEXT (sqlalchemy.dialects.mysql.LONGTEXT)
because rendered block markup can exceed the TEXT 64 KB limit.
Template
Bases: CMSBase, Base
A named, versioned CMS template a site can adopt.
slug is unique; settings_schema / default_settings are JSON.
Relationships: blocks (1:N :class:TemplateBlock) and sites (1:N
:class:hq.sql_models.core.Site via Site.template_id).
Source code in template.py
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97 | class Template(CMSBase, Base):
"""A named, versioned CMS template a site can adopt.
``slug`` is unique; ``settings_schema`` / ``default_settings`` are JSON.
Relationships: ``blocks`` (1:N :class:`TemplateBlock`) and ``sites`` (1:N
:class:`hq.sql_models.core.Site` via ``Site.template_id``).
"""
__tablename__ = "templates"
name: Mapped[str] = mapped_column(String(255), nullable=False)
slug: Mapped[str] = mapped_column(String(128), nullable=False, unique=True)
description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
version: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
preview_image: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
settings_schema: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
default_settings: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
# The template's full stylesheet, stored in the DB (AISD P1.a). cms-api serves
# it at ``/public/v2/templates/<slug>/css`` and the worker links + edge-caches
# it at ``/_assets/styles/<slug>/<css_version>.css``. Keeping CSS in the DB
# (rather than an R2 object) makes it admin-editable content cached at the CF
# edge. LONGTEXT because a full stylesheet exceeds TEXT's 64 KB limit.
css: Mapped[Optional[str]] = mapped_column(LONGTEXT, nullable=True)
# Cache-bust pointer for the CSS above. The worker's stylesheet URL embeds it
# (``.../<css_version>.css``); bumping it publishes new CSS with no worker
# redeploy. A string so it can carry a content hash. Not mirrored to
# template_history (a cache pointer, not editorial content).
css_version: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
# Edit-tracking columns (AISD-126): edited_by (FK -> users.id, nullable,
# indexed) and creator_type (string reference to creator_types.name, NOT a
# FK). Both nullable (additive).
edited_by: Mapped[Optional[int]] = mapped_column(
Integer, ForeignKey("users.id", name="fk_templates_edited_by"), nullable=True, index=True
)
creator_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
blocks: Mapped[List["TemplateBlock"]] = relationship(
"TemplateBlock", back_populates="template"
)
sites: Mapped[List["Site"]] = relationship("Site", back_populates="template")
page_types: Mapped[List["PageType"]] = relationship(
"PageType", back_populates="template"
)
|
TemplateBlock
Bases: CMSBase, Base
A block of markup + settings within a template, keyed by element_type.
element_type is a string reference to template_elements.name (not a
FK), indexed for render-time lookup; html_content is a LONGTEXT.
Source code in template.py
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122 | class TemplateBlock(CMSBase, Base):
"""A block of markup + settings within a template, keyed by ``element_type``.
``element_type`` is a string reference to ``template_elements.name`` (not a
FK), indexed for render-time lookup; ``html_content`` is a LONGTEXT.
"""
__tablename__ = "template_blocks"
template_id: Mapped[int] = mapped_column(
Integer, ForeignKey("templates.id"), nullable=False, index=True
)
element_type: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
html_content: Mapped[Optional[str]] = mapped_column(LONGTEXT, nullable=True)
settings: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
# Edit-tracking columns (AISD-126); see Template for the rationale.
edited_by: Mapped[Optional[int]] = mapped_column(
Integer, ForeignKey("users.id", name="fk_template_blocks_edited_by"), nullable=True, index=True
)
creator_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
template: Mapped["Template"] = relationship("Template", back_populates="blocks")
|
SiteBlockOverride
Bases: CMSBase, Base
A site-specific override of a template block, keyed by element_type.
Keying on element_type (the string element name, indexed) rather than a
template_blocks.id FK lets a site's overrides survive a template switch;
html_content is a LONGTEXT.
Source code in template.py
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147 | class SiteBlockOverride(CMSBase, Base):
"""A site-specific override of a template block, keyed by ``element_type``.
Keying on ``element_type`` (the string element name, indexed) rather than a
``template_blocks.id`` FK lets a site's overrides survive a template switch;
``html_content`` is a LONGTEXT.
"""
__tablename__ = "site_block_overrides"
site_id: Mapped[int] = mapped_column(
Integer, ForeignKey("sites.id"), nullable=False, index=True
)
element_type: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
html_content: Mapped[Optional[str]] = mapped_column(LONGTEXT, nullable=True)
settings: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
# Edit-tracking columns (AISD-126); see Template for the rationale.
edited_by: Mapped[Optional[int]] = mapped_column(
Integer, ForeignKey("users.id", name="fk_site_block_overrides_edited_by"), nullable=True, index=True
)
creator_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
site: Mapped["Site"] = relationship("Site", back_populates="block_overrides")
|
PageType
Bases: CMSBase, Base
A data-driven page descriptor a template exposes (AISD P1.b).
One row per (page type, variant) a template renders. It replaces the
worker's hardcoded router.ts rules and pages/*.ts orchestrators: the
worker reads these rows (served inside the public /settings payload,
resolved for the site's active template) and runs one generic match route
-> fetch sources -> build -> wrap layout loop.
Scoped to a template_id (a template owns its page types); a future task
can add per-site overrides keyed by (type, variant) the same way
:class:SiteBlockOverride overrides blocks. Unique on
(template_id, type, variant).
variant lets one template carry multiple layouts for the same page
type (e.g. a "listicle" article alongside the "default" article).
It is NOT NULL with a "default" server default so the unique key stays
clean (MySQL treats NULLs as distinct). The worker selects the variant from a
content field (e.g. content.settings.layout / content_type), falling
back to "default" -- so a route resolves to (type) first, then the
builder/content picks the variant.
JSON column contract (all interpreted by the worker engine):
routes: list of path patterns, e.g. ["/{category}/{slug}"]. {name}
segments capture params; a trailing * segment is a catch-all.layout: ordered list of template element_type content slots to
render into the page body. NB: header / footer are added by the
worker's renderLayout wrapper and must NOT be listed here.sources: list of {bind, call, params, required} declared API calls
(call in lists/pages/authors/sitemap/seo). required: false sources
degrade to null on failure instead of failing the page.bindings: map of template-context key -> dotted path into the fetched
sources, e.g. {"related": "related.items"}.grids / sidebar / partial / seo: declarative fill + SEO hints
for pure-data page types (no code).transforms: ordered list of named editorial transforms the worker
applies (e.g. article_content_blocks, editors_picks_sidebar).builder: optional named builder transform for page types whose shaping
is too bespoke for the declarative fields (WK's existing pages use this for
exact parity). When unset, the engine uses the declarative path.position: ascending match-priority hint; literal-segment specificity
still wins within equal-length routes.
Source code in template.py
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227 | class PageType(CMSBase, Base):
"""A data-driven page descriptor a template exposes (AISD P1.b).
One row per (page type, variant) a template renders. It replaces the
worker's hardcoded ``router.ts`` rules and ``pages/*.ts`` orchestrators: the
worker reads these rows (served inside the public ``/settings`` payload,
resolved for the site's active template) and runs one generic ``match route
-> fetch sources -> build -> wrap layout`` loop.
Scoped to a ``template_id`` (a template owns its page types); a future task
can add per-site overrides keyed by ``(type, variant)`` the same way
:class:`SiteBlockOverride` overrides blocks. Unique on
``(template_id, type, variant)``.
``variant`` lets one template carry **multiple layouts for the same page
type** (e.g. a ``"listicle"`` article alongside the ``"default"`` article).
It is NOT NULL with a ``"default"`` server default so the unique key stays
clean (MySQL treats NULLs as distinct). The worker selects the variant from a
content field (e.g. ``content.settings.layout`` / ``content_type``), falling
back to ``"default"`` -- so a route resolves to ``(type)`` first, then the
builder/content picks the variant.
JSON column contract (all interpreted by the worker engine):
- ``routes``: list of path patterns, e.g. ``["/{category}/{slug}"]``. ``{name}``
segments capture params; a trailing ``*`` segment is a catch-all.
- ``layout``: ordered list of template ``element_type`` **content** slots to
render into the page body. NB: ``header`` / ``footer`` are added by the
worker's ``renderLayout`` wrapper and must NOT be listed here.
- ``sources``: list of ``{bind, call, params, required}`` declared API calls
(``call`` in lists/pages/authors/sitemap/seo). ``required: false`` sources
degrade to ``null`` on failure instead of failing the page.
- ``bindings``: map of template-context key -> dotted path into the fetched
sources, e.g. ``{"related": "related.items"}``.
- ``grids`` / ``sidebar`` / ``partial`` / ``seo``: declarative fill + SEO hints
for pure-data page types (no code).
- ``transforms``: ordered list of named editorial transforms the worker
applies (e.g. ``article_content_blocks``, ``editors_picks_sidebar``).
- ``builder``: optional named builder transform for page types whose shaping
is too bespoke for the declarative fields (WK's existing pages use this for
exact parity). When unset, the engine uses the declarative path.
- ``position``: ascending match-priority hint; literal-segment specificity
still wins within equal-length routes.
"""
__tablename__ = "page_types"
__table_args__ = (
UniqueConstraint(
"template_id", "type", "variant", name="uq_page_types_template_type_variant"
),
)
template_id: Mapped[int] = mapped_column(
Integer, ForeignKey("templates.id"), nullable=False, index=True
)
type: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
variant: Mapped[str] = mapped_column(
String(64), nullable=False, server_default="default"
)
routes: Mapped[Any] = mapped_column(JSON, nullable=False)
layout: Mapped[Any] = mapped_column(JSON, nullable=False)
ttl: Mapped[int] = mapped_column(Integer, nullable=False, server_default="300")
sources: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
bindings: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
grids: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
sidebar: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
partial: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
seo: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
transforms: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
builder: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
position: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
# Edit-tracking columns, mirroring the sibling template tables (AISD-126).
edited_by: Mapped[Optional[int]] = mapped_column(
Integer, ForeignKey("users.id", name="fk_page_types_edited_by"), nullable=True, index=True
)
creator_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
template: Mapped["Template"] = relationship("Template", back_populates="page_types")
|
TemplateHistory
Bases: IntPKMixin, Base
A flat audit mirror of a templates row's prior state (AISD-126).
Decoupled mirror per the ContentHistory pattern: own surrogate id PK;
a plain indexed template_id INT (the OLD templates.id) with no
foreign key; changed_at; every mirrored business column nullable, no
unique, no FK; plus the edited_by / creator_type editor reference.
No relationship(). Rows are inserted only by the trg_templates_history
AFTER UPDATE trigger; the column list stays in lock-step with that trigger's
INSERT.
Source code in template.py
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254 | class TemplateHistory(IntPKMixin, Base):
"""A flat audit mirror of a ``templates`` row's prior state (AISD-126).
Decoupled mirror per the ``ContentHistory`` pattern: own surrogate ``id`` PK;
a plain indexed ``template_id`` INT (the OLD ``templates.id``) with **no
foreign key**; ``changed_at``; every mirrored business column **nullable, no
unique, no FK**; plus the ``edited_by`` / ``creator_type`` editor reference.
No ``relationship()``. Rows are inserted only by the ``trg_templates_history``
AFTER UPDATE trigger; the column list stays in lock-step with that trigger's
INSERT.
"""
__tablename__ = "template_history"
template_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
changed_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
name: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
slug: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
version: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
preview_image: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
settings_schema: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
default_settings: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
edited_by: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
creator_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
|
TemplateBlockHistory
Bases: IntPKMixin, Base
A flat audit mirror of a template_blocks row's prior state (AISD-126).
Decoupled mirror per the ContentHistory pattern: own surrogate id PK;
a plain indexed template_block_id INT (the OLD template_blocks.id)
with no foreign key; changed_at; every mirrored business column
nullable, no unique, no FK; plus the edited_by / creator_type
editor reference. No relationship(). Rows are inserted only by the
trg_template_blocks_history AFTER UPDATE trigger; the column list stays in
lock-step with that trigger's INSERT.
Source code in template.py
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279 | class TemplateBlockHistory(IntPKMixin, Base):
"""A flat audit mirror of a ``template_blocks`` row's prior state (AISD-126).
Decoupled mirror per the ``ContentHistory`` pattern: own surrogate ``id`` PK;
a plain indexed ``template_block_id`` INT (the OLD ``template_blocks.id``)
with **no foreign key**; ``changed_at``; every mirrored business column
**nullable, no unique, no FK**; plus the ``edited_by`` / ``creator_type``
editor reference. No ``relationship()``. Rows are inserted only by the
``trg_template_blocks_history`` AFTER UPDATE trigger; the column list stays in
lock-step with that trigger's INSERT.
"""
__tablename__ = "template_block_history"
template_block_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
changed_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
template_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
element_type: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
html_content: Mapped[Optional[str]] = mapped_column(LONGTEXT, nullable=True)
settings: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
edited_by: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
creator_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
|
SiteBlockOverrideHistory
Bases: IntPKMixin, Base
A flat audit mirror of a site_block_overrides row's prior state (AISD-126).
Decoupled mirror per the ContentHistory pattern: own surrogate id PK;
a plain indexed site_block_override_id INT (the OLD
site_block_overrides.id) with no foreign key; changed_at; every
mirrored business column nullable, no unique, no FK; plus the
edited_by / creator_type editor reference. No relationship(). Rows
are inserted only by the trg_site_block_overrides_history AFTER UPDATE
trigger; the column list stays in lock-step with that trigger's INSERT.
Source code in template.py
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305 | class SiteBlockOverrideHistory(IntPKMixin, Base):
"""A flat audit mirror of a ``site_block_overrides`` row's prior state (AISD-126).
Decoupled mirror per the ``ContentHistory`` pattern: own surrogate ``id`` PK;
a plain indexed ``site_block_override_id`` INT (the OLD
``site_block_overrides.id``) with **no foreign key**; ``changed_at``; every
mirrored business column **nullable, no unique, no FK**; plus the
``edited_by`` / ``creator_type`` editor reference. No ``relationship()``. Rows
are inserted only by the ``trg_site_block_overrides_history`` AFTER UPDATE
trigger; the column list stays in lock-step with that trigger's INSERT.
"""
__tablename__ = "site_block_override_history"
site_block_override_id: Mapped[int] = mapped_column(
Integer, nullable=False, index=True
)
changed_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
site_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
element_type: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
html_content: Mapped[Optional[str]] = mapped_column(LONGTEXT, nullable=True)
settings: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
edited_by: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
creator_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
|
PageTypeHistory
Bases: IntPKMixin, Base
A flat audit mirror of a page_types row's prior state (AISD P1.b).
Decoupled mirror per the TemplateBlockHistory pattern: own surrogate
id PK; a plain indexed page_type_id INT (the OLD page_types.id)
with no foreign key; changed_at; every mirrored column nullable, no
unique, no FK; plus the edited_by / creator_type editor reference.
No relationship(). Rows are inserted only by the trg_page_types_history
AFTER UPDATE trigger; the column list stays in lock-step with that trigger's
INSERT.
Source code in template.py
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340 | class PageTypeHistory(IntPKMixin, Base):
"""A flat audit mirror of a ``page_types`` row's prior state (AISD P1.b).
Decoupled mirror per the ``TemplateBlockHistory`` pattern: own surrogate
``id`` PK; a plain indexed ``page_type_id`` INT (the OLD ``page_types.id``)
with **no foreign key**; ``changed_at``; every mirrored column **nullable, no
unique, no FK**; plus the ``edited_by`` / ``creator_type`` editor reference.
No ``relationship()``. Rows are inserted only by the ``trg_page_types_history``
AFTER UPDATE trigger; the column list stays in lock-step with that trigger's
INSERT.
"""
__tablename__ = "page_type_history"
page_type_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
changed_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
template_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
variant: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
routes: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
layout: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
ttl: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
sources: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
bindings: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
grids: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
sidebar: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
partial: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
seo: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
transforms: Mapped[Optional[Any]] = mapped_column(JSON, nullable=True)
builder: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
position: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
edited_by: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
creator_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
|