sitefinity-database-structure

name

sitefinity-database-structure

description

Grounded reference for the Progress Sitefinity CMS database structure (classic MVC/Feather era). Use this skill whenever you need to understand or query Sitefinity tables directly - page composition, content lifecycle (Master/Temp/Live), drafts, versioning, templates, Module Builder tables, dynmc_ tables, content links, or URL storage. Generic across Sitefinity sites; every claim was verified against a production Sitefinity 15.4 database AND actual platform behavior - nothing is guessed.

You are an expert on how Progress Sitefinity persists its data in SQL Server. This reference was verified two ways: empirically against a long-lived production Sitefinity 15.4 database AND against actual Telerik 15.4 platform behavior (PageManager, LifecycleDecorator, the PropertyPersister pipeline, PagesFluentMapping column mappings). Anything quantitative (row counts, ratios) varies per site - always measure your own database with the discovery queries provided.

Version baseline: Sitefinity 15.4. Details may differ slightly on older or newer versions (column additions, lifecycle nuances, attribute availability). Before relying on version-sensitive specifics, check what the target project actually runs:

(Get-Item "<site>\bin\Telerik.Sitefinity.dll").VersionInfo.FileVersion   # e.g. 15.4.8630.0 -> Sitefinity 15.4

Scope: the classic ASP.NET page model - WebForms-era controls and MVC/Feather widgets (MvcControllerProxy, hybrid pages, ResourcePackages). The newer ASP.NET Core renderer / decoupled frontend ("Sitefinity Core") is OUT OF SCOPE: its pages and templates carry a value in the renderer column and use a different widget/persistence model - do not apply this reference's widget mechanics to those pages.

Ground rules

• Read-only by default. Point database tools at a copy (nightly/staging restore), never production. Schema or data changes are proposed as SQL for human review - an agent should not mutate a live CMS database.
• C# code that needs raw SQL must resolve the connection string at runtime via Config.Get<DataConfig>().ConnectionStrings["Sitefinity"].ConnectionString (namespaces Telerik.Sitefinity.Configuration + Telerik.Sitefinity.Data.Configuration; "Sitefinity" is the standard connection name). Never read App_Data\Sitefinity\Configuration\DataConfig.config off disk and hardcode the literal string - each environment has its own and credentials rotate.
• There are ZERO foreign key constraints in the schema. Every relationship below is enforced only by the OpenAccess ORM at runtime. Joins can and do dangle (verified examples below). Never assume referential integrity.
• Stock Sitefinity ships NO stored procedures - the ORM emits parameterized SQL. Any procs you find are custom/maintenance additions by the site's developers. Ignore them when reasoning about how Sitefinity itself works.

Naming conventions (OpenAccess quirks)

Quirk	Examples
Vowel-dropped column names	nme (name), val (value), vrsion, ownr, commnt, lbel, dta, ky, qery, prent_template_id
Trailing-underscore columns	title_, url_name_, description_, caption_, keywords_ - these back localizable Lstring properties
Vowel-stripped table names	sf_mdia_content_sf_permissions, sf_drft_pages_sf_language_data - long names get letters squeezed out
Sitefinity's own typos	sf_vesion_items [sic], include_script_manger [sic]
voa_class / voa_version	OpenAccess type discriminator (int hash) and optimistic-concurrency counter. voa_class matters when one table stores multiple CLR types (see sf_draft_pages, sf_dynamic_content). The hash values are deployment-specific - discover them with GROUP BY, don't hardcode
voa_keygen table	OpenAccess HIGH/LOW key generator infrastructure, not app data
Guid.Empty means "unset"	sibling_id, base_control_id, original_control_id, original_content_id, personalization_* use 00000000-0000-0000-0000-000000000000 for "none". Older rows sometimes use NULL instead. Always treat NULL and Guid.Empty as equivalent "unset"
Numbered duplicate columns	id2, id3, published2, theme2 - artifacts of multiple CLR classes flat-mapped onto one table; usually only one of the set is populated per row

Table categories

A mature Sitefinity DB has hundreds of tables in three buckets (census query below):

• sf_* tables - stock Sitefinity (pages, content, libraries/media, taxonomies, security/permissions, forms, versioning, search/publishing, ecommerce, multisite). Typically the large majority of all tables.
• dynmc_* tables - generated by the Publishing/Search-pipes subsystem, NOT Module Builder. Each maps to an sf_meta_types row with class_name = Dynamic_<32-hex> and base_class_name = Telerik.Sitefinity.DynamicTypes.Model.DynamicTypeBase. Table name = dynmc_ + the 32-hex with the letters a/e stripped, truncated to 24 chars. Common columns live in sf_dynamic_type_base; per-type fields in the dynmc_ table. Usually only the search-index sync-tracking type has rows (one per indexed item, pairing with sf_publishing_point); the rest sit at 0. Orphaned dynmc_ tables whose meta type was deleted are common debris.
• Unprefixed tables - Module Builder content types plus any custom app tables. Module Builder names derive from {module}_{type} lowercased (e.g. a "Knowledge Base" module's Solution type -> knowledgebase_solution), with vowel-stripping on long names; resolve the real names from sf_mb_dynamic_module_type (type_namespace + type_name), never by guessing.

-- Census: every table with row counts
SELECT t.name, p.rows
FROM sys.tables t
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)
ORDER BY t.name;

Page composition model (the part that matters for page/widget work)

sf_page_node                  the page in the sitemap tree
  | id (PK)  parent_id  root_id  node_type  url_name_  title_
  | content_id  ---------------------> AUTHORITATIVE pointer to the CURRENT sf_page_data row
  v
sf_page_data                  ONE row per page (per culture); the published composition
  | content_id (PK)  status (0|2)  visible  vrsion  last_control_id  culture
  | page_node_id  -> sf_page_node.id   (reverse nav; CAN match stale orphan rows - see Data rot)
  | template_id   -> sf_page_templates.id
  v
sf_draft_pages                editing drafts; SHARED table, discriminated by voa_class
  | id (PK)  is_temp_draft  vrsion  template_id (the draft's OWN template choice)
  | page_id  -> sf_page_data.content_id (PageDraft) or sf_page_templates.id (TemplateDraft)
  | hosts THREE entity families: PageDraft, TemplateDraft, and form-description drafts
  |   (discover the voa_class values per family: GROUP BY voa_class, is_temp_draft)
  v
sf_object_data                every control/widget instance AND every nested complex object
  | id (PK)  object_type  place_holder  sibling_id  is_layout_control  caption_
  | page_id -> ONE OF: sf_page_data.content_id (live page controls)
  |                    sf_draft_pages.id       (draft + template-draft controls)
  |                    sf_page_templates.id    (template controls)
  |   (internal mapping: PageControl.Page -> PageData, PageDraftControl.Page -> PageDraft,
  |    TemplateControl.Page -> PageTemplate, TemplateDraftControl.Page -> TemplateDraft -
  |    four flat-mapped subclasses ALL ToColumn("page_id"); a truly polymorphic non-FK column)
  | page_id NULL -> nested object: parent_prop_id -> sf_control_properties.id
  |                 (complex property values, e.g. ChoiceItem, ordered by collection_index)
  |                 or parent_id-linked, or pure orphans (garbage accumulates - see Data rot)
  | base_control_id -> the TEMPLATE control this page-level row overrides (is_overrided_control=1)
  | original_control_id -> on a DRAFT control: the LIVE control it mirrors. Publish is a
  |   mirror-sync keyed on this: matched live rows update IN PLACE (stable ids), unmatched
  |   draft controls create new live rows (draft gets back-stamped), unreferenced live rows die
  v
sf_control_properties         the property tree
  | id (PK)  nme varchar(50)  val nvarchar(MAX)
  | Level 1:  control_id = widget id, prnt_prop_id NULL
  | Level 2+: control_id = NULL,      prnt_prop_id = parent property row id

Scale intuition: on long-lived sites, draft control rows typically far outnumber live ones in sf_object_data - most of what's in these tables does not render. Always classify a row's page_id (live page / draft / template) before drawing conclusions from it.

Key mechanics:

1. The property tree recurses through objects: control (object_data) -> properties (control_properties) -> complex value (object_data with parent_prop_id) -> its properties -> ... collection_index orders list members; dictionary_key keys dictionary members.
2. Render order is a per-placeholder linked list on sibling_id: each control points to the control BEFORE it; Guid.Empty (or NULL) marks the head. No sort column exists. (Platform doc comment on ControlData.SiblingId: "the ID of the control that is immediately preceding this control in the current zone". Template inheritance can legally produce multiple Empty-heads in one placeholder.)

MVC widgets

• object_type for classic MVC widgets = Telerik.Sitefinity.Mvc.Proxy.MvcControllerProxy; Feather dynamic widgets use ...Frontend.Mvc.Infrastructure.Controllers.MvcWidgetProxy. object_type strings are written at creation time and never normalized - old rows can carry full assembly-qualified names with ancient version numbers. Match with LIKE '%TypeName%', never equality.
• Level-1 properties: ControllerName = full CLR type name (the toolbox registers ControllerType.FullName, and the property is [PropertyPersistence(IsKey = true)] so it is ALWAYS persisted), ID = a page-scoped control id like C005, and - only if configured - a Settings container row (val NULL) whose CHILDREN hold designer values.
• Persistence is sparse, and the mechanism is default-instance diffing: on save, PropertyPersister.DoPersist => !object.Equals(liveValue, defaultInstanceValue) where the default instance is a fresh new T() via reflection (ObjectDataUtility.CreateDefaultInstance; the [DefaultValue] attribute is only a fallback when a getter throws). Key properties bypass the diff. The Settings container (ComplexPropertyPersister) is deleted outright if zero children survive the diff - hence a freshly-dropped/unconfigured widget has exactly 2 rows (ControllerName, ID) and NO Settings row; unset properties fall back to controller defaults at runtime. Child property names match the controller's C# property names exactly.
• Scalars are written via TypeConverter.ConvertToInvariantString (invariant strings: "True", "False", "42"). [PropertyPersistence(PersistAsJson = true)] properties store the whole graph as ONE JSON row; without it each list item becomes an sf_object_data row (parent_prop_id + collection_index) - and a list item whose values all equal that type's defaults produces ZERO rows and silently vanishes on save (ListPropertyPersister deletes it). This is why complex IList<> widget properties should carry PersistAsJson = true.
• The Cnnn ID comes from ControlManager.SetControlId: num = Math.Max(LastControlId + 1, Controls.Count() + 1), value = "C" + num.ToString("#000") (templates use the template Key as the prefix instead of "C"). LastControlId columns: sf_page_data.last_control_id, sf_draft_pages.last_control_id2 (PageDraft), sf_draft_pages.last_control_id (TemplateDraft).

Layout controls and placeholders

• Feather grids are Telerik.Sitefinity.Frontend.GridSystem.GridControl rows (is_layout_control=1) whose Layout property holds the grid template path (e.g. ~/Frontend-Assembly/Telerik.Sitefinity.Frontend/GridSystem/Templates/grid-6+6.html). Pre-Feather sites also have legacy WebForms Telerik.Sitefinity.Web.UI.LayoutControl rows.
• Placeholder naming: top-level placeholder names come from the template's markup (site-specific - discover them with SELECT DISTINCT place_holder FROM sf_object_data WHERE ...). A layout control whose ID property is C308 creates child placeholders named C308_Col00, C308_Col01, ...; content widgets reference those strings in place_holder (regex: ^(.+)_Col(\d{2})$).

Templates

sf_page_templates: PK id, nme, title_, framework (enum PageTemplateFramework: Hybrid=0, Mvc=1, WebForms=2; NULL on ancient backend templates), prent_template_id (template-inheritance chain). Template-owned widgets live in sf_object_data with page_id = template.id. Pages do NOT copy template controls; they merge at render/edit time. A page-level override row carries base_control_id = the template control id.

nme is NOT unique - stock installs commonly carry several templates named Bootstrap4.default (one per layout variant), and nothing stops duplicates among custom templates either. Resolve templates by id in any automation, discovering them first:

SELECT id, nme, title_, framework, prent_template_id
FROM sf_page_templates ORDER BY nme;

Content lifecycle (the Master/Temp/Live story)

Enum Telerik.Sitefinity.GenericContent.Model.ContentLifecycleStatus (verified values):

Name	Value	Meaning
Master	0	The editable source-of-truth copy
Temp	1	Transient checkout copy while an editor has the item open
Live	2	The published copy visitors see
Deleted	4	Soft-deleted (Recycle Bin)
PartialTemp	8	Multilingual partial-edit state

Pages and generic content follow DIFFERENT lifecycle storage models:

• Generic + Module Builder content (news, sf_dynamic_content, ...): persistent Master(0) + Live(2) PAIRS. The live row's original_content_id points to the master's id; the master's original_content_id is Guid.Empty. (Verified on a module builder type: exact N master / N live pairing.) Temp(1) rows appear only mid-edit.
• Pages (sf_page_data): ONE row per page at rest - status 2 if published, status 0 (+ visible=0) if never published/unpublished. original_content_id is NULL. Editing happens through PageDraft rows in sf_draft_pages instead of a master copy; is_temp_draft=1 marks the per-user checkout copy (PageManager.EditPage returns it), is_temp_draft=0 is the master draft. Verified publish flow: CheckIn copies temp -> master (controls, template_id, last_control_id, version) and briefly flips sf_page_data.status to 0; Publish guards pageData.Version > draft.Version (throws "modified by someone else"), then sets vrsion+1, status=2, visible=1 (always), locked_by=Empty, publication_date on FIRST publish only, and mirror-syncs controls onto the live row keyed by the draft controls' original_control_id (stable live ids). Temp drafts persist by design: PageManager.DeleteTempAfterPublish => false in source - expect them to accumulate over the years on any long-lived site, each holding its own template_id and widget copies.

Version history (sf_version_*)

Version history is NOT relational - it is serialized snapshots:

• sf_version_chnges: one row per saved version. item_id = the item id (for pages: the sf_page_data.content_id), dta = the serialized snapshot blob, commnt = the History UI comment, is_published_version, change_type ('publish', ...).
• vrsion encoding (internal VersionDataProvider logic): major * 10000 + minor. A publish calls IncrementMajorVersion (10000, 20000, 30000...); a draft save increments the minor (10001, 10002...). Change.Version in C# returns the RAW multiplied value; the UI renders major + "." + minor. ORDER BY vrsion DESC finds the latest.
• Publishing through PageManager.PublishPageDraft writes NO version row. Version snapshots are created by the CALLING layer - the backend page editor (ZoneEditorService) calls VersionManager.CreateVersion(draft, pageDataId, isPublished: true) after publishing. Custom migration code that publishes via PageManager must do the same or no History entry appears (and "annotate the latest version" would stamp a years-old row).
• sf_vesion_items: tracker per versioned item - id = item id, type_name (pages are versioned as Telerik.Sitefinity.Pages.Model.PageDraft - the draft is what gets serialized), last_version, trunk_id.
• sf_version_dependency / sf_version_trunks / sf_version_serial_info: supporting structure.

Restoring a version deserializes the blob through VersionManager. You cannot meaningfully reconstruct an old page composition with SQL alone.

Built-in content modules vs Module Builder types (two storage generations)

Sitefinity has two generations of content storage. The compiled-in modules (News, Events, Blogs, Lists, Generic Content / shared content blocks, Libraries, Forms, Comments) predate Module Builder (introduced around Sitefinity 5, 2012-era); Module Builder types arrived later with a different physical layout. Both share the SAME lifecycle semantics (Master/Live pairs, original_content_id, the status enum) - what differs is where the columns live:

	Built-in modules	Module Builder (dynamic) types
Physical layout	One table per type, everything inline: sf_news_items, sf_events, sf_blog_posts, sf_list_items, sf_content_items each carry the FULL Content base column set (content_id PK, status, original_content_id, vrsion, visible, publication_date, ownr, url_name_, title_, content_, draft_culture, content_state, approval_workflow_state_, ...) PLUS type-specific columns (news: summary_, author_; list items: parent_id, ordinal)	Split storage: lifecycle columns for ALL types live in the shared sf_dynamic_content table (discriminated by voa_class); the per-type table holds only custom fields + parent_id, joined on base_id
Item id column	content_id	base_id
Query for live items	No join needed: WHERE status = 2 on the type's own table	Must join sf_dynamic_content for status
Custom fields added later	Real columns added to the stock table (verified: custom copyright_notes, sort_order, sort_index columns physically on sf_media_content), declared via sf_meta_fields rows whose meta type has is_dynamic = 0	Columns in the per-type table, declared the same way with is_dynamic = 1
Taxonomy fields	Junction tables {table}_{field}: sf_content_items_category, sf_list_items_tags, ...	Junction tables {type}_{field}: same pattern
URL rows (sf_url_data.app_name)	Per module: /News, /Events, /Blogs, /Lists, /Generic_Content, /Libraries	All under /DynamicModule

Additional built-in-module specifics (all verified):

• Libraries are flat-mapped single-table inheritance: sf_media_content holds Image, Video, AND Document rows discriminated by voa_class. This is where the "numbered duplicate column" quirk peaks: width/width2, height/height2 per mapped class, and even the taxonomy junctions come numbered per class (sf_media_content_category, _category2, _category3, _tags, _tags2, _tags3). Library containers live in sf_libraries; file metadata in sf_media_file_links / sf_media_file_urls / sf_media_thumbnails; binaries are on disk/blob storage unless sf_chunks has rows.
• Shared content blocks are ContentItems: a ContentBlock widget with "shared content" stores a SharedContentID Settings property pointing at sf_content_items.content_id; a non-shared ContentBlock keeps its HTML directly in the widget's own property rows. Same widget, two storage locations.
• Forms are neither model: the form STRUCTURE is sf_form_description (its drafts live in the sf_draft_pages multi-entity table, and its fields are sf_object_data controls - a form is a page-like composition); the RESPONSES go to a generated table per form (sf_forms_frm_* / vowel-stripped sf_frms_frm_*), one column per form field, plus the summary sf_form_entry table. Deleting/renaming form fields leaves orphan columns in those tables.
• Comments hang off content via {table}_sf_commnt junction tables.

When you meet an unfamiliar table, classify it before theorizing: check sf_meta_types/sf_meta_fields (is it a declared field store?), sf_mb_dynamic_module_type (Module Builder?), and the {table}_{field} junction pattern (custom taxonomy on a built-in?). A table that looks like mystery debris is often just a custom field someone added to a stock type years ago.

Module Builder anatomy

• Registries: sf_mb_dynamic_module (modules), sf_mb_dynamic_module_type (types; type_namespace = Telerik.Sitefinity.DynamicTypes.Model.<Module>), sf_mb_dynamic_module_field (designer-level fields). These mirror into the ORM meta model: sf_meta_types / sf_meta_fields / sf_meta_attribute (where column_name, db_sql_type, taxonomy bindings, and RelatedData fields are declared).
• Storage is split: the per-type table (PK base_id) holds ONLY the custom fields + parent_id hierarchy. All lifecycle columns live in the shared sf_dynamic_content table (base_id join, voa_class discriminates the type): status, original_content_id, url_name_, publication_date, ownr, vrsion, visible, ...
• Per-type tables have no indexes beyond the PK and no status column. To query live items of a type:

  SELECT t.*, d.url_name_
  FROM yourmodule_yourtype t
  JOIN sf_dynamic_content d ON d.base_id = t.base_id
  WHERE d.status = 2 AND d.visible = 1;
  

• Taxonomy fields and multi-value fields get junction tables named {type}_{field} (vowel-stripped when long). Fields declared as RelatedData/RelatedMedia have NO column at all - see sf_content_link.

Related data without FKs: sf_content_link

sf_content_link (singular name) is how RelatedData/RelatedMedia/"related items" fields connect things: parent_item_id + parent_item_type + component_property_name -> child_item_id + child_item_type, with ordinal for order and available_for_temp/master/live tinyints scoping each link to lifecycle states (a master edit creates master-scoped links that flip live on publish). Stock example: a user profile's Avatar -> Image. App code can also use it for arbitrary item-to-item relations, so expect site-specific component_property_name values and large row counts.

URLs: sf_url_data

sf_url_data holds every current AND historical URL. Pages use app_name = 'Sitefinity/' and carry the page node id in id2 (content_id/item_type NULL). Content items use app_name = '/DynamicModule', /Libraries, /UserProfiles, etc. with content_id + item_type populated. is_default=1 marks the canonical URL; old URLs remain as redirect rows. On monolingual sites culture = 127 (invariant LCID) everywhere.

Multilingual columns on monolingual sites

If the site is single-language: culture columns hold the language code or NULL (very old rows can predate a culture migration), the *_sf_language_data junction tables exist as infrastructure noise, and PartialTemp status / ChildrenLocalizationStrategy go unused. Do not build logic that branches on culture without first checking whether the site is actually multilingual (SELECT culture, COUNT(*) FROM sf_page_data GROUP BY culture).

Data rot catalog (a long-lived Sitefinity DB lies to you)

Because nothing is FK-enforced, debris accumulates over years of upgrades, deletions, and editor sessions. Check for each of these before trusting any single-row assumption:

1. Stale duplicate Live page rows: one sf_page_node can match multiple sf_page_data rows ALL with status=2 (upgrade-era leftovers; the oldest often have culture NULL). Only the row matched by sf_page_node.content_id is current. Always resolve pages via pn.content_id = pd.content_id, never only via pd.page_node_id = pn.id.
2. Orphaned sf_page_data rows with NULL page_node_id.
3. Years-old temp drafts (plus their control rows) - see lifecycle section; their accumulation is by design, not a bug.
4. Dangling base_control_id: page-level override controls pointing at template controls that no longer exist.
5. Orphan sf_object_data rows (no page_id, no parent_prop_id, no parent_id).
6. Orphan dynmc_* tables whose meta type was deleted; apparent mystery tables. Many "mysteries" turn out to be custom-field or taxonomy junction tables ({table}_{field}) someone added to a stock type years ago - classify via sf_meta_fields before declaring debris.
7. Workflow tables can be empty while sf_approval_tracking_record is huge - lifecycle audit happens without approval workflows defined.
8. The largest tables are typically permission junctions (*_sf_permissions) - security metadata dwarfs content.

Querying tips

• Counts without scanning: sys.tables joined to sys.partitions (index_id IN (0,1)).
• STRING_AGG (SQL Server 2017+) gives compact column lists: SELECT STRING_AGG(c.name, ', ') FROM sys.columns c WHERE c.object_id = OBJECT_ID('sf_page_data');
• If querying through a guarded MCP tool, expect auto-applied row caps (so OFFSET/FETCH may fail - paginate with WHERE key > last) and keyword blocklists that match anywhere in the text, including aliases (delete, truncate, ...).