Database Schema

The documentation site describes the schema for the AllThingsPG database in SQL Server. AllThingsDB is a relational database that contains both data and business logic and will be used by the Phase 2 version of www.allthingspg.org. Refer to a link for a discusson on Databases.

The schema for the database is created, maintained and deployed by a SSDT project in VS 2022 called AllThingsPG.Database contained in GitHub, and is considered the master source of all schema The database will be developed and tested on a local SQL Server, and when ready, will be deployed to a staging environment in Azure to a Azure SQL database. Refer to Builds and Deployments for more information.

The VS2022 database project defines all implemented schema including tables, views, constraints, stored procedures, functions and other objects. The database is built and deployed from that source, and can either deploy to local development or Azure SQLh in staging.

Database project inventory

Object type	Count	What is listed here
Tables	36	34 of the 36 project tables are documented here; two legacy or supporting tables are omitted. The remaining table sections include columns, declared constraints, foreign keys, and sample data.
Views	12	Purpose only.
Functions	15	Purpose only.
Stored procedures	75	Purpose only.

Sample data shows project seed values or rows available in the supplied extract. Where no sample rows are available, the page says NO DATA AVAILABLE. A section with fewer than five available records shows all available records rather than invented rows. Two legacy or supporting tables are omitted from this reference.

Table List with Classifications

Tablename	TableType	Purpose
SystemTables	System	Lists database tables with their catalog category and order.
SystemSettings	System	Stores application and database settings.
Numbers	System	Stores integers from 1 through 1,000 for utility use.
ErrorLog	System	Records database messages and errors.
RouteType	Lookup	Names the kinds of menu destinations.
VisitorType	Lookup	Defines visitor audiences.
ContentType	Lookup	Classifies content documents.
FormatType	Lookup	Defines content block formats and file extensions.
MimeType	Lookup	Defines media types and rendering characteristics.
WorkflowStatus	Lookup	Defines content workflow states.
MasterMenu	Menu	Holds the imported source menu.
MasterMenuStage	Menu	Stages imported menu rows for processing.
MenuItemTestHarness	Menu	Stores menu test cases and their validation results.
TestHarness	Menu	Stores menu test cases and results.
MenuItem	Menu	Holds the website's navigation items and route targets.
PortalMenu	Menu	Holds visitor-specific portal tiles.
BoardMember	Menu	Holds board member names, roles, biographies, and images.
ContentDocument	Content	Holds a page's content description and publication state.
ContentElement	Content	Holds text, media, or file content blocks.
StagingDocument	Staging	Holds a content document being prepared.
StagingElement	Staging	Holds content blocks being prepared.
Registration	Registration	Holds a person's email, visitor type, and participation choices.
Contact	Registration	Holds contact and address details for a registration.
Newsletter	Registration	Connects a newsletter to its content and dates.
Email	Registration	Holds an email subject, body, and sent date.
EmailRecipient	Registration	Connects an email to a registered recipient.
EmailAttachment	Registration	Holds a file attached to an email.
EmailRequest	Registration	Holds an outbound email request for processing.
MigrationDocument	Migration	Stores legacy page metadata captured for migration.
MigrationElement	Migration	Stores legacy page content blocks captured for migration.
MigrationMapping	Migration	Maps legacy page titles to current menu items.

Tables

SystemTables

Complete list of the 31 tables classified by the project seed routine.

Implemented columns

Column	SQL definition
TableID	INT IDENTITY (1, 1) NOT NULL
TableName	VARCHAR (50) NOT NULL
TableType	VARCHAR (15) NOT NULL
SortOrder	INT NOT NULL

Constraints and foreign keys

Name	Definition
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([TableID] ASC)
CK_SystemTables_TableType	CONSTRAINT [CK_SystemTables_TableType] CHECK ([TableType]='Migration' OR [TableType]='Registration' OR [TableType]='Menu' OR [TableType]='Staging' OR [TableType]='Content' OR [TableType]='Lookup' OR [TableType]='System')
UQ_SystemTables_TableName	CONSTRAINT [UQ_SystemTables_TableName] UNIQUE NONCLUSTERED ([TableName] ASC)

Sample Data

TableName	TableType	SortOrder
SystemTables	System	11
SystemSettings	System	12
Numbers	System	13
ErrorLog	System	14
RouteType	Lookup	21
VisitorType	Lookup	22
ContentType	Lookup	23
FormatType	Lookup	24
MimeType	Lookup	25
WorkflowStatus	Lookup	26
MasterMenu	Menu	30
MasterMenuStage	Menu	31
MenuItemTestHarness	Menu	32
TestHarness	Menu	33
MenuItem	Menu	34
PortalMenu	Menu	35
BoardMember	Menu	36
ContentDocument	Content	41
ContentElement	Content	42
StagingDocument	Staging	51
StagingElement	Staging	52
Registration	Registration	61
Contact	Registration	62
Newsletter	Registration	63
Email	Registration	64
EmailRecipient	Registration	65
EmailAttachment	Registration	66
EmailRequest	Registration	67
MigrationDocument	Migration	70
MigrationElement	Migration	71
MigrationMapping	Migration	72

SystemSettings

Stores typed settings such as site configuration, menu source, and feature choices. These rows are from the database project’s baseline settings.

Implemented columns

Column	SQL definition
SettingID	INT IDENTITY (1, 1) NOT NULL
SettingKey	VARCHAR (50) NOT NULL
SettingType	VARCHAR (50) NOT NULL
SettingGroup	VARCHAR (50) NOT NULL
SettingValue	SQL_VARIANT NULL
LastUpdated	DATETIME2 (7) CONSTRAINT [DF_SystemSettings_LastUpdated] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_SystemSettings_LastUpdated	CONSTRAINT [DF_SystemSettings_LastUpdated] DEFAULT (sysdatetime()) NOT NULL
PK_SystemSettings	CONSTRAINT [PK_SystemSettings] PRIMARY KEY CLUSTERED ([SettingID] ASC)
CK_SystemSettings_GroupUpper	CONSTRAINT [CK_SystemSettings_GroupUpper] CHECK (([SettingGroup]) collate Latin1_General_CS_AS=(upper([SettingGroup])) collate Latin1_General_CS_AS)
CK_SystemSettings_GUID_Valid	CONSTRAINT [CK_SystemSettings_GUID_Valid] CHECK ([SettingType]<>'GUID' OR TRY_CAST([SettingValue] AS [uniqueidentifier]) IS NOT NULL)
CK_SystemSettings_JSON_Valid	CONSTRAINT [CK_SystemSettings_JSON_Valid] CHECK ([SettingType]<>'JSON' OR isjson(CONVERT([nvarchar](max),[SettingValue]))=(1))
CK_SystemSettings_KeyUpper	CONSTRAINT [CK_SystemSettings_KeyUpper] CHECK (([SettingKey]) collate Latin1_General_CS_AS=(upper([SettingKey])) collate Latin1_General_CS_AS)
CK_SystemSettings_SettingGroup	CONSTRAINT [CK_SystemSettings_SettingGroup] CHECK ([SettingGroup]='GLOBALVAR' OR [SettingGroup]='APPLICATION' OR [SettingGroup]='DATABASE' OR [SettingGroup]='SYSTEM')
CK_SystemSettings_SettingType	CONSTRAINT [CK_SystemSettings_SettingType] CHECK ([SettingType]='LIST' OR [SettingType]='JSON' OR [SettingType]='GUID' OR [SettingType]='DATETIME' OR [SettingType]='DATE' OR [SettingType]='MONEY' OR [SettingType]='DECIMAL' OR [SettingType]='INTEGER' OR [SettingType]='STRING' OR [SettingType]='BOOLEAN' OR [SettingType]='BINARY')
CK_SystemSettings_TypeUpper	CONSTRAINT [CK_SystemSettings_TypeUpper] CHECK (([SettingType]) collate Latin1_General_CS_AS=(upper([SettingType])) collate Latin1_General_CS_AS)
CK_SystemSettings_Value_NotNull	CONSTRAINT [CK_SystemSettings_Value_NotNull] CHECK (([SettingType]='JSON' OR [SettingType]='GUID' OR [SettingType]='DATETIME' OR [SettingType]='DATE' OR [SettingType]='MONEY' OR [SettingType]='DECIMAL' OR [SettingType]='INTEGER' OR [SettingType]='BOOLEAN' OR [SettingType]='BINARY') AND [SettingValue] IS NOT NULL OR ([SettingType]='LIST' OR [SettingType]='STRING'))
UQ_SystemSettings_Key	CONSTRAINT [UQ_SystemSettings_Key] UNIQUE NONCLUSTERED ([SettingKey] ASC)

Sample Data

SettingKey	SettingType	SettingGroup	SettingValue
SITE_NAME	STRING	SYSTEM	AllThingsPG
MASTER_MENU	STRING	SYSTEM	C:\ATPG\Master_Menu.csv
ENABLE_REGISTRATION	BOOLEAN	APPLICATION	TRUE
DEFAULT_PAGE_SIZE	INTEGER	GLOBALVAR	25
SUPPORT_EMAIL	STRING	GLOBALVAR	support@allthingspg.org

Numbers

Contains the integers 1 through 1,000.

Implemented columns

Column	SQL definition
Number	INT NOT NULL

Constraints and foreign keys

Name	Definition
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([Number] ASC)

Sample Data

Number
1
2
3
4
5

ErrorLog

Records routine name, message, severity, parameters, and execution context. The following ten rows illustrate possible log entries; they are not captured incidents.

Implemented columns

Column	SQL definition
ErrorLogID	INT IDENTITY (1, 1) NOT NULL
ProcedureName	[sysname] NOT NULL
ErrorMessage	NVARCHAR (4000) NULL
ErrorNumber	INT NULL
ErrorSeverity	INT NULL
ErrorState	INT NULL
Parameters	NVARCHAR (MAX) NULL
UserName	NVARCHAR (128) DEFAULT (suser_sname()) NOT NULL
HostName	NVARCHAR (128) DEFAULT (host_name()) NOT NULL
LogLevel	VARCHAR (10) DEFAULT ('ERROR') NOT NULL
LogDate	DATETIME2 (7) DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([ErrorLogID] ASC)
CK_ErrorLog_LogLevel	CONSTRAINT [CK_ErrorLog_LogLevel] CHECK ([LogLevel]='DEBUG' OR [LogLevel]='INFO' OR [LogLevel]='WARNING' OR [LogLevel]='ERROR')

Sample Data

ProcedureName	ErrorMessage	LogLevel	LogDate
Populate_MenuItems	Menu validation failed: route target is missing.	ERROR	Example timestamp
Insert_MenuItem	Route type is not recognized.	ERROR	Example timestamp
Insert_ContentDocument	Content type is not recognized.	ERROR	Example timestamp
Insert_ContentElement	Content document was not found.	ERROR	Example timestamp
Perform_MenuItem_Validation	Visitor mask contains an unsupported code.	WARNING	Example timestamp
Populate_MasterMenu	Required source file could not be opened.	ERROR	Example timestamp
Populate_TestHarness	Test case has no expected result.	WARNING	Example timestamp
Publish_Staging	Staged menu item could not be published.	ERROR	Example timestamp
Get_SystemSetting	Requested setting key was not found.	WARNING	Example timestamp
Initialize_Database	Initialization completed with a logged diagnostic.	INFO	Example timestamp

Lookup tables

RouteType

Identifies how a menu item is opened. All current seed values:

Implemented columns

Column	SQL definition
Code	CHAR (1) NOT NULL
Description	NVARCHAR (200) NOT NULL
Folder	NVARCHAR (200) NOT NULL
SortOrder	INT NOT NULL

Constraints and foreign keys

Name	Definition
PK_RouteType	CONSTRAINT [PK_RouteType] PRIMARY KEY CLUSTERED ([Code] ASC)

Sample Data

Code	Description	Folder	SortOrder
V	Visitor	Visitor	1
C	Content	Content	2
P	Page	Page	3
R	Redirect	Redirect	4

VisitorType

Defines the visitor types available to the site. All current seed values:

Implemented columns

Column	SQL definition
Code	CHAR (1) NOT NULL
Description	NVARCHAR (200) NOT NULL
SortOrder	INT NOT NULL
IsDefault	BIT NOT NULL

Constraints and foreign keys

Name	Definition
PK_VisitorType	CONSTRAINT [PK_VisitorType] PRIMARY KEY CLUSTERED ([Code] ASC)

Sample Data

Code	Description	SortOrder	IsDefault
P	Patient	1	0
C	Caregiver	2	0
M	Provider	3	0
D	Pharmaceutical	4	0
V	Guest	5	1

ContentType

Classifies a content document. All current seed values:

Implemented columns

Column	SQL definition
Code	CHAR (1) NOT NULL
Name	NVARCHAR (50) NOT NULL
Description	NVARCHAR (200) NOT NULL
SortOrder	INT NOT NULL

Constraints and foreign keys

Name	Definition
PK_ContentType	CONSTRAINT [PK_ContentType] PRIMARY KEY CLUSTERED ([Code] ASC)

Sample Data

Code	Name	Description	SortOrder
A	Article	Long-form educational content	1
N	NewsItem	News updates and announcements	2
F	FAQ	Question and answer pairs	3
T	Testimonial	Patient and caregiver stories	4
P	Profile	Board members, staff, medical experts	5
E	Event	Events, webinars, fundraisers	6
L	Link	External resource or reference	8
R	Research	Clinical trial or research study	9
X	External	External file	10
G	Gallery	Image gallery metadata	11
D	Document	PDF or downloadable file reference	12
U	Unknown	Content has not been populated	99

FormatType

Defines a content block’s format and associated file extensions. All current seed values:

Implemented columns

Column	SQL definition
Code	CHAR (1) NOT NULL
Name	NVARCHAR (50) NOT NULL
Description	NVARCHAR (200) NOT NULL
FileTypes	NVARCHAR (200) NOT NULL
SortOrder	INT NOT NULL

Constraints and foreign keys

Name	Definition
PK_FormatType	CONSTRAINT [PK_FormatType] PRIMARY KEY CLUSTERED ([Code] ASC)

Sample Data

Code	Name	Description	FileTypes
T	Text	Plain Text	.txt
R	RTF	Rich Text	.rtf
H	HTML	Web Page Markup	.html, .htm
E	EPUB	Electronic Pub	.epub
P	PDF	Portable Doc	.pdf
M	MD	Markdown	.md
J	JSON	JSON	.json
I	IMAGE	Binary Image	.gif, .jpg, .svg, .webp, .png
A	AUDIO	Binary Audio	.mp3, .wav, .ogg
W	WORD	MS Word Document	.docx, .docm, .dotx, .dotm
S	EXCEL	MS Spreadsheet	.xls, .xlsx
N	PPT	Powerpoint	.ppt, .pptx, .pps, .ppsx, .pot, .potx, .pptm, .ppsm, .potm
G	GDOC	Google Document	.gdoc
D	OPEN	Open Document	.odt
?	UNKNOWN	Unknown	.bin

MimeType

Identifies the media type and whether content is text, binary, or renderable. All current seed values:

Implemented columns

Column	SQL definition
Code	CHAR (1) NOT NULL
FormatType	CHAR (1) NOT NULL
Name	NVARCHAR (50) NOT NULL
Description	NVARCHAR (200) NOT NULL
IsText	BIT NOT NULL
IsBinary	BIT NOT NULL
IsRenderable	BIT NOT NULL
SortOrder	INT NOT NULL

Constraints and foreign keys

Name	Definition
PK_MimeType	CONSTRAINT [PK_MimeType] PRIMARY KEY CLUSTERED ([Code] ASC)
FK_MimeType_FormatType	CONSTRAINT [FK_MimeType_FormatType] FOREIGN KEY ([FormatType]) REFERENCES [dbo].[FormatType] ([Code])

Sample Data

Code	FormatType	Name	Description	Text	Binary	Renderable
T	T	TXT	text/plain	1	0	1
M	M	MD	text/markdown	1	0	1
H	H	HTML	text/html	1	0	1
C	H	HTM	text/htm	1	0	1
R	R	RTF	application/rtf	1	0	0
J	J	JSON	application/json	1	0	0
E	E	EPUB	application/epub+zip	0	1	0
P	P	PDF	application/pdf	0	1	1
I	I	JPG	image/jpeg	0	1	1
N	I	PNG	image/png	0	1	1
F	I	GIF	image/gif	0	1	1
S	I	SVG	image/svg+xml	1	0	1
W	I	WEBP	image/webp	0	1	1
A	A	MP3	audio/mpeg	0	1	1
U	A	WAV	audio/wav	0	1	1
Q	A	OGG	audio/ogg	0	1	1
D	W	DOCX	application/vnd.openxmlformats-officedocument.wordprocessingml.document	0	1	0
X	W	DOCM	application/vnd.ms-word.document.macroEnabled.12	0	1	0
Y	W	DOTX	application/vnd.openxmlformats-officedocument.wordprocessingml.template	0	1	0
Z	W	DOTM	application/vnd.ms-word.template.macroEnabled.12	0	1	0
L	S	XLS	application/vnd.ms-excel	0	1	0
K	S	XLSX	application/vnd.openxmlformats-officedocument.spreadsheetml.sheet	0	1	0
!	N	PPT	application/vnd.ms-powerpoint	0	1	0
@	N	PPTX	application/vnd.openxmlformats-officedocument.presentationml.presentation	0	1	0
#	N	PPS	application/vnd.ms-powerpoint	0	1	0
$	N	PPSX	application/vnd.openxmlformats-officedocument.presentationml.slideshow	0	1	0
%	N	POT	application/vnd.ms-powerpoint	0	1	0
^	N	POTX	application/vnd.openxmlformats-officedocument.presentationml.template	0	1	0
&	N	PPTM	application/vnd.ms-powerpoint.presentation.macroEnabled.12	0	1	0
*	N	PPSM	application/vnd.ms-powerpoint.slideshow.macroEnabled.12	0	1	0
+	N	POTM	application/vnd.ms-powerpoint.template.macroEnabled.12	0	1	0
G	G	GDOC	application/vnd.google-apps.document	0	1	0
O	D	ODT	application/vnd.oasis.opendocument.text	0	1	0
B	?	BIN	application/octet-stream	0	1	0

WorkflowStatus

Defines the content workflow stage. All current seed values:

Implemented columns

Column	SQL definition
Code	CHAR (1) NOT NULL
Title	NVARCHAR (50) NOT NULL
Description	NVARCHAR (200) NULL
SortOrder	INT NOT NULL

Constraints and foreign keys

Name	Definition
PK_WorkflowStatus	CONSTRAINT [PK_WorkflowStatus] PRIMARY KEY CLUSTERED ([Code] ASC)

Sample Data

Code	Title	Description	SortOrder
D	Draft	Content is newly curated or created	1
R	Review	Content needs review	2
A	Approved	Content has been approved but not published	3
X	Rejected	Content has been rejected	4
P	Published	Content has been published	5
S	Staging	Content is linked to a staged menu item	6

Menu tables

MasterMenu

Imported source menu entries, including hierarchy labels, route, audience, description, and notes.

Implemented columns

Column	SQL definition
MenuID	INT IDENTITY (1, 1) NOT NULL
ParentMenuID	INT NULL
MenuItemID	INT NULL
ContentDocumentID	INT NULL
Parent	NVARCHAR (100) NOT NULL
Child	NVARCHAR (100) NOT NULL
SubMenu	NVARCHAR (100) NOT NULL
MenuType	CHAR (1) NOT NULL
Title	NVARCHAR (200) NULL
VisitorMask	NVARCHAR (20) NULL
RouteType	NVARCHAR (10) NULL
RouteTarget	NVARCHAR (200) NULL
Description	NVARCHAR (200) NOT NULL
Notes	NVARCHAR (200) NULL

Constraints and foreign keys

Name	Definition
PK_MasterMenu	CONSTRAINT [PK_MasterMenu] PRIMARY KEY CLUSTERED ([MenuID] ASC)
UQ_MasterMenu	CONSTRAINT [UQ_MasterMenu] UNIQUE NONCLUSTERED ([Title] ASC, [RouteType] ASC, [VisitorMask] ASC)

Sample Data

Sample status
NO DATA AVAILABLE

TestHarness

Stores menu validation cases and their expected/actual results.

Implemented columns

Column	SQL definition
TestHarnessID	INT IDENTITY (1, 1) NOT NULL
TestID	INT NOT NULL
ParentTestID	INT NULL
Category	VARCHAR (50) NULL
MenuType	CHAR (1) NULL
Description	NVARCHAR (300) NULL
Notes	NVARCHAR (300) NULL
Title	NVARCHAR (200) NULL
RouteType	CHAR (1) NULL
RouteTarget	NVARCHAR (200) NULL
VisitorMask	VARCHAR (10) NULL
SortOrder	INT NULL
IsActive	BIT NULL
IsProcessed	BIT NULL
Expected	VARCHAR (10) NULL
Actual	VARCHAR (10) NULL
IsError	BIT NULL
ErrorNumber	INT NULL
ErrorMessage	NVARCHAR (500) NULL
MenuItemID	INT NULL
ContentDocumentID	INT NULL

No constraints are declared in this table definition.

Sample Data

Sample status
NO DATA AVAILABLE

MenuItem

The navigation records used by the site. For content routes, RouteTarget contains the associated ContentDocumentID. The examples below are grouped by route type and come from the supplied menu extract.

Implemented columns

Column	SQL definition
MenuItemID	INT IDENTITY (1, 1) NOT NULL
ParentMenuItemID	INT NULL
Title	NVARCHAR (200) NOT NULL
RouteType	CHAR (1) NOT NULL
RouteTarget	NVARCHAR (200) NOT NULL
VisitorMask	VARCHAR (10) NOT NULL
SortOrder	INT CONSTRAINT [DF_MenuItem_SortOrder] DEFAULT ((0)) NOT NULL
IsActive	BIT CONSTRAINT [DF_MenuItem_IsActive] DEFAULT ((1)) NOT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_MenuItem_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_MenuItem_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_MenuItem_SortOrder	CONSTRAINT [DF_MenuItem_SortOrder] DEFAULT ((0)) NOT NULL
DF_MenuItem_IsActive	CONSTRAINT [DF_MenuItem_IsActive] DEFAULT ((1)) NOT NULL
DF_MenuItem_CreatedDate	CONSTRAINT [DF_MenuItem_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_MenuItem_ModifiedDate	CONSTRAINT [DF_MenuItem_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_MenuItem	CONSTRAINT [PK_MenuItem] PRIMARY KEY CLUSTERED ([MenuItemID] ASC)
UQ_MenuItem_Title_RouteType_VisitorMask	CONSTRAINT [UQ_MenuItem_Title_RouteType_VisitorMask] UNIQUE NONCLUSTERED ([Title] ASC, [RouteType] ASC, [VisitorMask] ASC)
CK_MenuItem_SortOrder	CONSTRAINT [CK_MenuItem_SortOrder] CHECK ([SortOrder]>=(0))
CK_MenuItem_Title_Normalized	CONSTRAINT [CK_MenuItem_Title_Normalized] CHECK (NOT [Title] like '%[%?=&#:/\<>]%')
CK_MenuItem_VisitorMask	CONSTRAINT [CK_MenuItem_VisitorMask] CHECK (NOT [VisitorMask] like '%[^PCMDVR]%' AND len([VisitorMask])>(0))
FK_MenuItem_Parent	CONSTRAINT [FK_MenuItem_Parent] FOREIGN KEY ([ParentMenuItemID]) REFERENCES [dbo].[MenuItem] ([MenuItemID])
FK_MenuItem_RouteType	CONSTRAINT [FK_MenuItem_RouteType] FOREIGN KEY ([RouteType]) REFERENCES [dbo].[RouteType] ([Code])

Visitor routes (V)

Sample Data

MenuItemID	ParentMenuItemID	Title	RouteTarget	VisitorMask
1	NULL	Welcome	/visitor?target=Guest	PCMDV
2	1	Patient	/visitor?target=Patient	PCMDV

Content routes (C)

Sample Data

MenuItemID	Title	RouteTarget	VisitorMask	ContentDocumentID	IsPublished
8	About PG	/content?target=1	PCMDV	1	0
9	What is PG	/content?target=2	PCMDV	2	0
18	Emotional Journey	/content?target=11	PCMDV	11	0
25	Emotional Journey	/content?target=18	P	18	0
32	Emotional Journey	/content?target=25	C	25	0
38	Being a Provider	/content?target=31	M	31	0
39	Early Diagnosis	/content?target=32	M	32	0

Page routes (P)

Sample Data

MenuItemID	Title	RouteTarget	VisitorMask
72	Clinic Finder	/page?target=clinicfinder.cshtml	PCMDV

Redirect routes (R)

Sample Data

MenuItemID	Title	RouteTarget	VisitorMask
74	All Things Pyoderma 4.2k members	/redirect?target=https://www.facebook.com/groups/368163159232	PCMDV

The repeated Emotional Journey title represents general, patient, and caregiver pages with separate menu and content IDs. The content rows shown are unpublished placeholders, not authored articles.

PortalMenu

Stores tiles shown for individual visitor types: title, short description, icon path, and linked menu item.

Implemented columns

Column	SQL definition
PortalMenuID	INT IDENTITY (1, 1) NOT NULL
MenuItemID	INT NOT NULL
Title	NVARCHAR (200) NOT NULL
Description	NVARCHAR (200) NOT NULL
IconPath	NVARCHAR (200) NOT NULL
VisitorType	CHAR (1) NOT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_PortalMenu_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_PortalMenu_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_PortalMenu_CreatedDate	CONSTRAINT [DF_PortalMenu_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_PortalMenu_ModifiedDate	CONSTRAINT [DF_PortalMenu_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_PortalMenu	CONSTRAINT [PK_PortalMenu] PRIMARY KEY CLUSTERED ([PortalMenuID] ASC)
FK_PortalMenu_MenuItem	CONSTRAINT [FK_PortalMenu_MenuItem] FOREIGN KEY ([MenuItemID]) REFERENCES [dbo].[MenuItem] ([MenuItemID])
FK_PortalMenu_VisitorType	CONSTRAINT [FK_PortalMenu_VisitorType] FOREIGN KEY ([VisitorType]) REFERENCES [dbo].[VisitorType] ([Code])

Sample Data

Sample status
NO DATA AVAILABLE

BoardMember

Stores board member names, roles, positions, descriptions, photos, and display order. These names and roles come from the database seed procedure.

Implemented columns

Column	SQL definition
BoardMemberID	INT IDENTITY (1, 1) NOT NULL
Name	NVARCHAR (200) NOT NULL
Role	NVARCHAR (200) NULL
Position	NVARCHAR (200) NULL
Description	NVARCHAR (MAX) NULL
PictureFileName	VARCHAR (255) NULL
PictureFileType	VARCHAR (50) NULL
PictureData	VARBINARY (MAX) NULL
SortOrder	INT CONSTRAINT [DF_BoardMember_SortOrder] DEFAULT ((0)) NOT NULL
IsActive	BIT CONSTRAINT [DF_BoardMember_IsActive] DEFAULT ((1)) NOT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_BoardMember_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_BoardMember_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_BoardMember_SortOrder	CONSTRAINT [DF_BoardMember_SortOrder] DEFAULT ((0)) NOT NULL
DF_BoardMember_IsActive	CONSTRAINT [DF_BoardMember_IsActive] DEFAULT ((1)) NOT NULL
DF_BoardMember_CreatedDate	CONSTRAINT [DF_BoardMember_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_BoardMember_ModifiedDate	CONSTRAINT [DF_BoardMember_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_BoardMember	CONSTRAINT [PK_BoardMember] PRIMARY KEY CLUSTERED ([BoardMemberID] ASC)
CK_BoardMember_SortOrder	CONSTRAINT [CK_BoardMember_SortOrder] CHECK ([SortOrder]>=(0))

Sample Data

Name	Role	Position	PictureFileName	SortOrder
Dr. Alex Ortega Loayza	Founder	Medical Director	Founder.jpg	1
Corlis Watkins-Nass	President	Marketing	President.jpg	2
Dave DePottie	Chairman	Information Technology	Chairman.jpg	3
Lasaunia Thompson	Treasurer	Fundraising	Treasurer.jpg	4
LaGail Odoms	Secretary	Documentation	Secretary.jpg	5

Content tables

ContentDocument

One document-level record is associated with a menu item. Current extract examples are placeholders with ContentType U (Unknown), WorkflowStatus D (Draft), and IsPublished 0.

Implemented columns

Column	SQL definition
ContentDocumentID	INT IDENTITY (1, 1) NOT NULL
MenuItemID	INT NOT NULL
ContentType	CHAR (1) NOT NULL
Description	NVARCHAR (500) NOT NULL
MetaData	NVARCHAR (MAX) NOT NULL
WorkflowStatus	CHAR (1) DEFAULT ('D') NOT NULL
IsPublished	BIT NOT NULL
PublishedDate	DATETIME2 (0) NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_ContentDocument_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_ContentDocument_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_ContentDocument_CreatedDate	CONSTRAINT [DF_ContentDocument_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_ContentDocument_ModifiedDate	CONSTRAINT [DF_ContentDocument_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_ContentDocument	CONSTRAINT [PK_ContentDocument] PRIMARY KEY CLUSTERED ([ContentDocumentID] ASC)
CK_ContentDocument_MetaData_IsJSON	CONSTRAINT [CK_ContentDocument_MetaData_IsJSON] CHECK (isjson([MetaData])=(1))
FK_ContentDocument_ContentType	CONSTRAINT [FK_ContentDocument_ContentType] FOREIGN KEY ([ContentType]) REFERENCES [dbo].[ContentType] ([Code])
FK_ContentDocument_MenuItem	CONSTRAINT [FK_ContentDocument_MenuItem] FOREIGN KEY ([MenuItemID]) REFERENCES [dbo].[MenuItem] ([MenuItemID]) ON DELETE CASCADE
FK_ContentDocument_WorkflowStatus	CONSTRAINT [FK_ContentDocument_WorkflowStatus] FOREIGN KEY ([WorkflowStatus]) REFERENCES [dbo].[WorkflowStatus] ([Code])

Sample Data

ContentDocumentID	MenuItemID	ContentType	Description	WorkflowStatus	IsPublished
1	8	U	About PG	D	0
2	9	U	What is PG	D	0
11	18	U	Emotional Journey	D	0
18	25	U	Emotional Journey	D	0
25	32	U	Emotional Journey	D	0
31	38	U	Being a Provider	D	0
32	39	U	Early Diagnosis	D	0

ContentElement

Stores a content block’s format, media type, block information, and text, binary, or file content. It has no persisted SortOrder, so the schema does not define editorial order for multiple blocks. No authored content elements appear in the supplied extract.

Implemented columns

Column	SQL definition
ContentElementID	INT IDENTITY (1, 1) NOT NULL
ContentDocumentID	INT NOT NULL
FormatType	CHAR (1) NOT NULL
MimeType	CHAR (1) NOT NULL
BlockInfo	NVARCHAR (MAX) NOT NULL
TextContent	NVARCHAR (MAX) NULL
BinaryContent	VARBINARY (MAX) NULL
LocalFilePath	NVARCHAR (255) NULL
ExternalFilePath	NVARCHAR (255) NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_ContentElement_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_ContentElement_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_ContentElement_CreatedDate	CONSTRAINT [DF_ContentElement_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_ContentElement_ModifiedDate	CONSTRAINT [DF_ContentElement_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_ContentElement	CONSTRAINT [PK_ContentElement] PRIMARY KEY CLUSTERED ([ContentElementID] ASC)
CK_ContentElement_BlockInfo_IsJSON	CONSTRAINT [CK_ContentElement_BlockInfo_IsJSON] CHECK (isjson([BlockInfo])=(1))
FK_ContentElement_ContentDocument	CONSTRAINT [FK_ContentElement_ContentDocument] FOREIGN KEY ([ContentDocumentID]) REFERENCES [dbo].[ContentDocument] ([ContentDocumentID]) ON DELETE CASCADE
FK_ContentElement_FormatType	CONSTRAINT [FK_ContentElement_FormatType] FOREIGN KEY ([FormatType]) REFERENCES [dbo].[FormatType] ([Code])
FK_ContentElement_MimeType	CONSTRAINT [FK_ContentElement_MimeType] FOREIGN KEY ([MimeType]) REFERENCES [dbo].[MimeType] ([Code])

Sample Data

Sample status
NO DATA AVAILABLE

Staging tables

StagingDocument

Holds a content document while it is being prepared. No staging rows are included in the supplied extract.

Implemented columns

Column	SQL definition
StagingDocumentID	INT IDENTITY (1, 1) NOT NULL
StagingMenuItemID	INT NULL
ContentType	CHAR (1) NOT NULL
Description	NVARCHAR (500) NOT NULL
Metadata	NVARCHAR (MAX) NOT NULL
WorkflowStatus	CHAR (1) NOT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_StagingDocument_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_StagingDocument_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_StagingDocument_CreatedDate	CONSTRAINT [DF_StagingDocument_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_StagingDocument_ModifiedDate	CONSTRAINT [DF_StagingDocument_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_StagingDocument	CONSTRAINT [PK_StagingDocument] PRIMARY KEY CLUSTERED ([StagingDocumentID] ASC)
CK_StagingDocument_Metadata_IsJSON	CONSTRAINT [CK_StagingDocument_Metadata_IsJSON] CHECK (isjson([Metadata])=(1))
FK_StagingDocument_ContentType	CONSTRAINT [FK_StagingDocument_ContentType] FOREIGN KEY ([ContentType]) REFERENCES [dbo].[ContentType] ([Code])
FK_StagingDocument_StagingMenuItem	CONSTRAINT [FK_StagingDocument_StagingMenuItem] FOREIGN KEY ([StagingMenuItemID]) REFERENCES [dbo].[StagingMenuItem] ([StagingMenuItemID])

Sample Data

Sample status
NO DATA AVAILABLE

StagingElement

Holds a text, media, or file block associated with a staged document.

Implemented columns

Column	SQL definition
StagingElementID	INT IDENTITY (1, 1) NOT NULL
StagingDocumentID	INT NOT NULL
WorkflowStatus	CHAR (1) NOT NULL
FormatType	CHAR (1) NOT NULL
MimeType	CHAR (1) NOT NULL
BlockInfo	NVARCHAR (MAX) NOT NULL
TextContent	NVARCHAR (MAX) NULL
BinaryContent	VARBINARY (MAX) NULL
LocalFilePath	NVARCHAR (255) NULL
ExternalFilePath	NVARCHAR (255) NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_StagingElement_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_StagingElement_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_StagingElement_CreatedDate	CONSTRAINT [DF_StagingElement_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_StagingElement_ModifiedDate	CONSTRAINT [DF_StagingElement_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_StagingElement	CONSTRAINT [PK_StagingElement] PRIMARY KEY CLUSTERED ([StagingElementID] ASC)
CK_StagingElement_BlockInfo_IsJSON	CONSTRAINT [CK_StagingElement_BlockInfo_IsJSON] CHECK (isjson([BlockInfo])=(1))
FK_StagingElement_FormatType	CONSTRAINT [FK_StagingElement_FormatType] FOREIGN KEY ([FormatType]) REFERENCES [dbo].[FormatType] ([Code])
FK_StagingElement_MimeType	CONSTRAINT [FK_StagingElement_MimeType] FOREIGN KEY ([MimeType]) REFERENCES [dbo].[MimeType] ([Code])
FK_StagingElement_StagingDocument	CONSTRAINT [FK_StagingElement_StagingDocument] FOREIGN KEY ([StagingDocumentID]) REFERENCES [dbo].[StagingDocument] ([StagingDocumentID])
FK_StagingElement_WorkflowStatus	CONSTRAINT [FK_StagingElement_WorkflowStatus] FOREIGN KEY ([WorkflowStatus]) REFERENCES [dbo].[WorkflowStatus] ([Code])

Sample Data

Sample status
NO DATA AVAILABLE

Registration tables

Registration

Stores the email address, visitor type, optional account credentials, and participation preferences.

Implemented columns

Column	SQL definition
RegistrationID	INT IDENTITY (1, 1) NOT NULL
Email	NVARCHAR (500) NOT NULL
PasswordHash	VARBINARY (256) NULL
PasswordSalt	VARBINARY (256) NULL
VisitorType	CHAR (1) NOT NULL
IsMember	BIT NOT NULL
IsVolunteer	BIT NOT NULL
IsNewsLetter	BIT NOT NULL
IsFundraising	BIT NOT NULL
IsActive	BIT NOT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_Registration_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_Registration_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_Registration_CreatedDate	CONSTRAINT [DF_Registration_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_Registration_ModifiedDate	CONSTRAINT [DF_Registration_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([RegistrationID] ASC)
FK_Registration_VisitorType	CONSTRAINT [FK_Registration_VisitorType] FOREIGN KEY ([VisitorType]) REFERENCES [dbo].[VisitorType] ([Code])
UQ_Registration_Email	CONSTRAINT [UQ_Registration_Email] UNIQUE NONCLUSTERED ([Email] ASC)

Sample Data

Sample status
NO DATA AVAILABLE

Contact

Stores a registrant’s name and optional contact and address details.

Implemented columns

Column	SQL definition
ContactID	INT IDENTITY (1, 1) NOT NULL
RegistrationID	INT NOT NULL
FirstName	VARCHAR (100) NOT NULL
LastName	VARCHAR (100) NOT NULL
MiddleName	VARCHAR (100) NULL
MobilePhone	VARCHAR (20) NULL
HomePhone	VARCHAR (20) NULL
WorkPhone	VARCHAR (20) NULL
WorkEmail	VARCHAR (500) NULL
AddressLine1	VARCHAR (200) NULL
AddressLine2	VARCHAR (200) NULL
ForeignAddress	VARCHAR (500) NULL
City	VARCHAR (100) NULL
StateProvince	VARCHAR (100) NULL
PostalCode	VARCHAR (20) NULL
Country	VARCHAR (100) NULL
DateOfBirth	DATE NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_Contact_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_Contact_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_Contact_CreatedDate	CONSTRAINT [DF_Contact_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_Contact_ModifiedDate	CONSTRAINT [DF_Contact_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([ContactID] ASC)
FK_Contact_Registration	CONSTRAINT [FK_Contact_Registration] FOREIGN KEY ([RegistrationID]) REFERENCES [dbo].[Registration] ([RegistrationID])

Sample Data

Sample status
NO DATA AVAILABLE

Newsletter

Connects a content document to a newsletter description and its distribution and publication dates.

Implemented columns

Column	SQL definition
NewsletterID	INT IDENTITY (1, 1) NOT NULL
ContentDocumentID	INT NOT NULL
Description	NVARCHAR (MAX) NOT NULL
NewsletterType	CHAR (1) NOT NULL
DistributionDate	DATETIME2 (7) DEFAULT (sysutcdatetime()) NOT NULL
PublishDate	DATETIME2 (7) DEFAULT (sysutcdatetime()) NOT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_Newsletter_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_Newsletter_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_Newsletter_CreatedDate	CONSTRAINT [DF_Newsletter_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_Newsletter_ModifiedDate	CONSTRAINT [DF_Newsletter_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([NewsletterID] ASC)

Sample Data

Sample status
NO DATA AVAILABLE

Email

Stores the subject and body of a message and when it was sent.

Implemented columns

Column	SQL definition
EmailID	INT IDENTITY (1, 1) NOT NULL
Subject	VARCHAR (200) NOT NULL
BodyText	NVARCHAR (MAX) NULL
SentDate	DATETIME2 (7) NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_Email_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_Email_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_Email_CreatedDate	CONSTRAINT [DF_Email_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_Email_ModifiedDate	CONSTRAINT [DF_Email_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([EmailID] ASC)

Sample Data

Sample status
NO DATA AVAILABLE

EmailRecipient

Connects a message to a registration and can record when it was sent.

Implemented columns

Column	SQL definition
EmailRecipientID	INT IDENTITY (1, 1) NOT NULL
EmailID	INT NOT NULL
RegistrationID	INT NOT NULL
DateSent	DATETIME2 (7) NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_EmailRecipient_CreatedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_EmailRecipient_CreatedDate	CONSTRAINT [DF_EmailRecipient_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([EmailRecipientID] ASC)
FK_EmailRecipient_Email	CONSTRAINT [FK_EmailRecipient_Email] FOREIGN KEY ([EmailID]) REFERENCES [dbo].[Email] ([EmailID]) ON DELETE CASCADE
FK_EmailRecipient_Registration	CONSTRAINT [FK_EmailRecipient_Registration] FOREIGN KEY ([RegistrationID]) REFERENCES [dbo].[Registration] ([RegistrationID]) ON DELETE CASCADE

Sample Data

Sample status
NO DATA AVAILABLE

EmailAttachment

Stores an attached file’s name, type, and binary contents; it may also refer to a newsletter.

Implemented columns

Column	SQL definition
EmailAttachmentID	INT IDENTITY (1, 1) NOT NULL
EmailID	INT NOT NULL
NewsLetterID	INT NULL
FileName	VARCHAR (255) NOT NULL
FileType	VARCHAR (50) NOT NULL
FileData	VARBINARY (MAX) NOT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_EmailAttachment_CreatedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_EmailAttachment_CreatedDate	CONSTRAINT [DF_EmailAttachment_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([EmailAttachmentID] ASC)
FK_EmailAttachment_Email	CONSTRAINT [FK_EmailAttachment_Email] FOREIGN KEY ([EmailID]) REFERENCES [dbo].[Email] ([EmailID])
FK_EmailAttachment_Newsletter	CONSTRAINT [FK_EmailAttachment_Newsletter] FOREIGN KEY ([NewsLetterID]) REFERENCES [dbo].[Newsletter] ([NewsletterID])

Sample Data

Sample status
NO DATA AVAILABLE

Other SQL project tables

These supporting, migration, audit, and legacy tables are not assigned a TableType in SystemTables. No sample records are available for them in the supplied project data.

EmailRequest

Holds a requested message, its processing state, and the resulting EmailID when processed.

Implemented columns

Column	SQL definition
EmailRequestID	INT IDENTITY (1, 1) NOT NULL
RegistrationID	INT NULL
RequestType	CHAR (1) NOT NULL
Subject	NVARCHAR (200) NOT NULL
RequestBody	NVARCHAR (MAX) NOT NULL
IsProcessed	BIT NOT NULL CONSTRAINT [DF_EmailRequest_IsProcessed] DEFAULT ((0))
ProcessedDate	DATETIME2 (7) NULL
ProcessedEmailID	INT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_EmailRequest_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_EmailRequest_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_EmailRequest_IsProcessed	CONSTRAINT [DF_EmailRequest_IsProcessed] DEFAULT ((0))
DF_EmailRequest_CreatedDate	CONSTRAINT [DF_EmailRequest_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_EmailRequest_ModifiedDate	CONSTRAINT [DF_EmailRequest_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([EmailRequestID] ASC)
FK_EmailRequest_Registration	CONSTRAINT [FK_EmailRequest_Registration] FOREIGN KEY ([RegistrationID]) REFERENCES [dbo].[Registration] ([RegistrationID])
FK_EmailRequest_Email	CONSTRAINT [FK_EmailRequest_Email] FOREIGN KEY ([ProcessedEmailID]) REFERENCES [dbo].[Email] ([EmailID])

Sample Data

Sample status
NO DATA AVAILABLE

MasterMenu_Stage

Menu-import staging rows with a load order and source menu fields.

Implemented columns

Column	SQL definition
LoadOrder	INT IDENTITY (1, 1) NOT NULL
Parent	NVARCHAR (100) NOT NULL
Child	NVARCHAR (100) NOT NULL
SubMenu	NVARCHAR (100) NOT NULL
Type	NVARCHAR (10) NOT NULL
VisitorMask	NVARCHAR (20) NULL
RouteType	NVARCHAR (10) NULL
RouteTarget	NVARCHAR (200) NULL
Description	NVARCHAR (200) NOT NULL
Notes	NVARCHAR (200) NULL

No constraints are declared in this table definition.

Sample Data

Sample status
NO DATA AVAILABLE

MasterMenuStage

Holds imported menu fields in the second menu-import staging table.

Implemented columns

Column	SQL definition
MenuID	INT IDENTITY (1, 1) NOT NULL
Parent	NVARCHAR (100) NOT NULL
Child	NVARCHAR (100) NOT NULL
SubMenu	NVARCHAR (100) NOT NULL
VisitorMask	NVARCHAR (20) NULL
RouteType	NVARCHAR (10) NULL
RouteTarget	NVARCHAR (200) NULL
Description	NVARCHAR (200) NOT NULL
Notes	NVARCHAR (200) NULL

No constraints are declared in this table definition.

Sample Data

Sample status
NO DATA AVAILABLE

MenuItemTestHarness

Stores menu-specific test cases, expected outcomes, and processing results.

Implemented columns

Column	SQL definition
TestID	INT NOT NULL
ParentTestID	INT NULL
Category	VARCHAR (50) NOT NULL
Type	CHAR (1) NOT NULL
Description	NVARCHAR (300) NOT NULL
Expected	VARCHAR (10) NOT NULL
Result	VARCHAR (10) NULL
IsError	BIT NULL
Notes	NVARCHAR (500) NULL
Title	NVARCHAR (200) NULL
RouteType	CHAR (1) NULL
RouteTarget	NVARCHAR (200) NULL
VisitorMask	VARCHAR (10) NULL
SortOrder	INT NULL
IsActive	BIT NULL
IsProcessed	BIT NULL

No constraints are declared in this table definition.

Sample Data

Sample status
NO DATA AVAILABLE

MigrationDocument

Holds captured legacy page titles, content classifications, descriptions, and metadata.

Implemented columns

Column	SQL definition
MigrationDocumentID	INT IDENTITY (1, 1) NOT NULL
MenuTitle	NVARCHAR (200) NOT NULL
ContentType	CHAR (1) NOT NULL
Description	NVARCHAR (500) NOT NULL
MetaData	NVARCHAR (MAX) NOT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_Migration_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_Migration_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_Migration_CreatedDate	CONSTRAINT [DF_Migration_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_Migration_ModifiedDate	CONSTRAINT [DF_Migration_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_Migration	CONSTRAINT [PK_Migration] PRIMARY KEY CLUSTERED ([MigrationDocumentID] ASC)
CK_MigrationDocument_MetaData_IsJSON	CONSTRAINT [CK_MigrationDocument_MetaData_IsJSON] CHECK (isjson([MetaData])=(1))

Sample Data

Sample status
NO DATA AVAILABLE

MigrationElement

Holds a captured text or media block associated with a legacy document.

Implemented columns

Column	SQL definition
MigrationElementID	INT IDENTITY (1, 1) NOT NULL
MigrationDocumentID	INT NOT NULL
FormatType	CHAR (1) NOT NULL
MimeType	CHAR (1) NOT NULL
BlockInfo	NVARCHAR (MAX) NOT NULL
TextMigration	NVARCHAR (MAX) NULL
BinaryMigration	VARBINARY (MAX) NULL
LocalFilePath	NVARCHAR (255) NULL
ExternalFilePath	NVARCHAR (255) NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_MigrationElement_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_MigrationElement_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_MigrationElement_CreatedDate	CONSTRAINT [DF_MigrationElement_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_MigrationElement_ModifiedDate	CONSTRAINT [DF_MigrationElement_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_MigrationElement	CONSTRAINT [PK_MigrationElement] PRIMARY KEY CLUSTERED ([MigrationElementID] ASC)
CK_MigrationElement_BlockInfo_IsJSON	CONSTRAINT [CK_MigrationElement_BlockInfo_IsJSON] CHECK (isjson([BlockInfo])=(1))
FK_MigrationElement_FormatType	CONSTRAINT [FK_MigrationElement_FormatType] FOREIGN KEY ([FormatType]) REFERENCES [dbo].[FormatType] ([Code])
FK_MigrationElement_MigrationDocument	CONSTRAINT [FK_MigrationElement_MigrationDocument] FOREIGN KEY ([MigrationDocumentID]) REFERENCES [dbo].[MigrationDocument] ([MigrationDocumentID]) ON DELETE CASCADE
FK_MigrationElement_MimeType	CONSTRAINT [FK_MigrationElement_MimeType] FOREIGN KEY ([MimeType]) REFERENCES [dbo].[MimeType] ([Code])

Sample Data

Sample status
NO DATA AVAILABLE

MigrationMapping

Connects an old menu title to its destination MenuItemID and records whether the match is correct.

Implemented columns

Column	SQL definition
MigrationMappingID	INT IDENTITY (1, 1) NOT NULL
OldMenuTitle	NVARCHAR (200) NOT NULL
MenuItemID	INT NULL
IsCorrect	BIT NULL

No constraints are declared in this table definition.

Sample Data

Sample status
NO DATA AVAILABLE

StagingMenuItem

Holds a menu hierarchy and routes before they become active MenuItem records.

Implemented columns

Column	SQL definition
StagingMenuItemID	INT IDENTITY (1, 1) NOT NULL
ParentMenuItemID	INT NULL
Title	NVARCHAR (200) NOT NULL
RouteType	CHAR (1) NOT NULL
RouteTarget	NVARCHAR (200) NOT NULL
VisitorMask	VARCHAR (10) NOT NULL
SortOrder	INT CONSTRAINT [DF_StagingMenuItem_SortOrder] DEFAULT ((0)) NOT NULL
IsActive	BIT CONSTRAINT [DF_StagingMenuItem_IsActive] DEFAULT ((1)) NOT NULL
CreatedDate	DATETIME2 (0) CONSTRAINT [DF_StagingMenuItem_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
ModifiedDate	DATETIME2 (0) CONSTRAINT [DF_StagingMenuItem_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
DF_StagingMenuItem_SortOrder	CONSTRAINT [DF_StagingMenuItem_SortOrder] DEFAULT ((0)) NOT NULL
DF_StagingMenuItem_IsActive	CONSTRAINT [DF_StagingMenuItem_IsActive] DEFAULT ((1)) NOT NULL
DF_StagingMenuItem_CreatedDate	CONSTRAINT [DF_StagingMenuItem_CreatedDate] DEFAULT (sysdatetime()) NOT NULL
DF_StagingMenuItem_ModifiedDate	CONSTRAINT [DF_StagingMenuItem_ModifiedDate] DEFAULT (sysdatetime()) NOT NULL
PK_StagingMenuItem	CONSTRAINT [PK_StagingMenuItem] PRIMARY KEY CLUSTERED ([StagingMenuItemID] ASC)
CK_StagingMenuItem_SortOrder	CONSTRAINT [CK_StagingMenuItem_SortOrder] CHECK ([SortOrder]>=(0))
CK_StagingMenuItem_Title_Normalized	CONSTRAINT [CK_StagingMenuItem_Title_Normalized] CHECK (NOT [Title] like '%[%?=&#:/\<>]%')
CK_StagingMenuItem_VisitorMask	CONSTRAINT [CK_StagingMenuItem_VisitorMask] CHECK (NOT [VisitorMask] like '%[^PCMDVR]%' AND len([VisitorMask])>(0))
FK_StagingMenuItem_Parent	CONSTRAINT [FK_StagingMenuItem_Parent] FOREIGN KEY ([ParentMenuItemID]) REFERENCES [dbo].[StagingMenuItem] ([StagingMenuItemID])
FK_StagingMenuItem_RouteType	CONSTRAINT [FK_StagingMenuItem_RouteType] FOREIGN KEY ([RouteType]) REFERENCES [dbo].[RouteType] ([Code])
UQ_StagingMenuItem_Title_RouteType_VisitorMask	CONSTRAINT [UQ_StagingMenuItem_Title_RouteType_VisitorMask] UNIQUE NONCLUSTERED ([Title] ASC, [RouteType] ASC, [VisitorMask] ASC)

Sample Data

Sample status
NO DATA AVAILABLE

SystemSettings_Audit

Keeps prior setting values and the dates they were last updated and audited.

Implemented columns

Column	SQL definition
AuditID	INT IDENTITY (1, 1) NOT NULL
SettingID	INT NOT NULL
SettingKey	VARCHAR (50) NOT NULL
SettingType	VARCHAR (50) NOT NULL
SettingGroup	VARCHAR (50) NOT NULL
SettingValue	SQL_VARIANT NULL
LastUpdated	DATETIME2 (7) NOT NULL
AuditDate	DATETIME2 (7) DEFAULT (sysdatetime()) NOT NULL

Constraints and foreign keys

Name	Definition
Unnamed PRIMARY KEY	PRIMARY KEY CLUSTERED ([AuditID] ASC)

Sample Data

Sample status
NO DATA AVAILABLE

Database objects

The following lists cover every view, function, and stored procedure in the database project. They summarize each object by its role.

Views

Name	Purpose
ContentDocumentList	Provides a unified view of ContentDocument records joined with MenuItem, RouteType, ContentType, and WorkflowStatus metadata for validation and reporting.
ContentElementList	Provides a unified view of ContentElement records joined with ContentDocument, MenuItem, RouteType, ContentType, FormatType, MimeType, and WorkflowStatus metadata for validation and reporting.
MasterMenuList	Provides the imported MasterMenu rows with a resolved Title based on the populated hierarchy column and resolved menu and content mappings.
MenuItemList	Provides a unified view of MenuItem records joined with ContentDocument and parent MenuItem metadata for validation and reporting.
MissingPlaceholders	Lists content-route menu items that do not yet have a ContentDocument placeholder.
MissingTableList	Returns all tables from TableList that do not currently exist in the database. Used to determine which lookup tables must be created.
PatientRegistry	Returns all Registration records that are Patients
RegistrationList	Returns all Registration records joined to Contact
StagingDocumentList	Provides a unified view of StagingDocument joined to StagingMenuItem with RouteType, ContentType and WorkflowStatus descriptions.
StagingElementList	Shows staged content elements with their staged document, menu, and lookup details.
StagingMenuItemList	Shows staged menu items with parent, route, and staged content details.
TableList	Returns metadata for all system tables including existence, column count, index count, constraint count, record count, and creation/modification timestamps.

Functions

Name	Purpose
Create_MetaData	Generates a standardized JSON metadata object for documenting content. Accepts title, author, description, category, and comma-separated tags, returning a structured JSON object with creation/modification timestamps.
Get_SystemSetting	Returns the SettingValue for a given SettingKey from the SystemSettings table. Returns NULL if the key is invalid or not found.
Get_TableList	Returns a comma‑separated list of table names from SystemTables ordered by SortOrder. Accepts a table type filter such as 'Lookup', 'System', 'Content', etc. Use ALL, null, or empty string for all tables.
Normalize_ErrorMessage	Normalizes an error or debug message by removing trailing punctuation unless the message ends with an allowed emphasis sequence.
Normalize_RouteTarget	Builds a route target from the route type and supplied value, validating visitor, page, redirect, or content targets.
Normalize_RouteType	Uppercases a route type and returns its code only when it exists in RouteType.
Normalize_SettingKey	Normalizes a slug with optional uppercasing and a configurable separator. Despite its name, it processes a slug input rather than a setting key.
Normalize_Slug	Normalizes a slug string by converting to lowercase, replacing spaces with hyphens, removing special characters, and cleaning up multiple/leading/trailing hyphens. Returns a URL-friendly slug string.
Normalize_Title	Cleans a title string for safe use in URLs and query strings. Trims whitespace, removes URL-breaking characters, and collapses multiple spaces into a single space.
Normalize_Token	Normalizes a single token by trimming whitespace, removing all non-alphanumeric characters, and converting the result to uppercase.
Normalize_VisitorMask	Uppercases and validates visitor codes, removes duplicates, orders them by VisitorType, and expands ALL or A to the full mask.
SplitCSVRow	Splits one CSV row into its field values while respecting quoted fields.
TableExists	Returns 1 if a table exists in the dbo schema, otherwise 0.
TablesExists	Checks whether all requested tables exist in the database.
Validate_Url	Validates an external URL string without using regular expressions. This function is intentionally strict and only accepts true external URLs. Accepts: - absolute URLs starting with http:// or https:// - hostnames such as example.com or www.example.com - hostnames with paths or queries - IPv4 addresses with optional paths Rejects: - internal paths starting with / - email addresses - non-http schemes (mailto:, ftp:, file:, tel:, sms:, etc.) - malformed URLs - protocol-relative URLs (//example.com) - file paths - query-only or fragment-only strings Returns 1 if valid, 0 otherwise.

Stored procedures

Name	Purpose
Clear_ErrorLog	Clears the ErrorLog table, reseeds the identity, inserts an initialization record, and logs opening and closing messages tied to @LogDate.
Create_BoardMember_Table	Drops and recreates the BoardMember table. Not part of the content system (no ContentType) -- the Board of Directors page is a static Page route (RouteType='P') that queries this table directly. Bio is deferred (not yet a column).
Create_Content_MetaData	Generates a standardized JSON metadata object for ContentDocument metadata. Accepts title, author, description, a category token, and a comma-separated list of tags, returning a structured JSON object with standard timestamps and version information.
Create_Content_Tables	Creates ContentDocument and ContentElement tables using refactored naming conventions and JSON validation rules.
Create_Element_MetaData	Generates a standardized JSON metadata object for content elements. Controls placement and tagging of the element within the content body.
Create_ErrorLog_Table	Drops and recreates the ErrorLog table using the latest schema standards. Inserts an initialization record and logs opening and closing messages tied to @LogDate.
Create_Lookup_Tables	Drops and recreates all lookup tables, removes dependent foreign keys, repopulates lookup tables, and restores foreign key constraints.
Create_MasterMenu_Table	Drops and recreates the MasterMenu table used to store the raw menu definition imported from the MASTER_MENU CSV. The location of the file is a system setting
Create_Menu_Tables	Creates the master-menu and menu-related tables.
Create_MenuItem_Table	Drops and recreates the MenuItem table. Removes foreign key references from StagingDocument and ContentDocument, deletes all MenuItem records, recreates the table with full constraints, and optionally populates initial menu items.
Create_Migration_Tables	Creates the legacy migration document, element, and mapping tables.
Create_Numbers_Table	Creates and populates the Numbers tally table used by normalization functions. Recreates the table each time.
Create_PortalMenu_Table	Creates the PortalMenu table for visitor-specific portal tiles.
Create_Registration_Tables	Create all the tables needed for Registration and various contacts with users
Create_Staging_Tables	Creates staging tables used for temporary content processing. Drops existing staging tables and recreates them with full constraints. Requires lookup tables to exist first.
Create_System_Tables	Recreates all core system tables if missing. Each table creation procedure is responsible for its own existence check. This routine simply orchestrates the creation of ErrorLog, SystemTables, SystemSettings, and Numbers tables.
Create_SystemSettings_Table	Drops and recreates the SystemSettings and SystemSettings_Audit tables. Applies all validation constraints and initializes settings using Populate_SystemSettings.
Create_SystemTables_Table	Drops and recreates the SystemTables lookup table, then inserts canonical system, lookup, content, staging, and menu table definitions.
Create_TableList_Table	Legacy routine that creates and seeds a physical TableList table. The current project defines TableList as a view, so this routine appears outdated.
Create_Tables	Creates all tables needed for the AllThingsPG database including system, lookup, menu, content, and staging tables. Executes individual table procedures in correct dependency order.
Create_TempTable_FromCSV	Reads the header row of a CSV file and dynamically creates a global temp table (##MenuCSV) with one NVARCHAR(MAX) column per header field.
Create_TestHarness_Table	Drops and recreates the TestHarness table used for testing menus
Delete_Content	Deletes ContentDocument and related ContentElement records based on ContentDocumentID or MenuItemID Supports full purge with identity reseed
Delete_MenuItem	Deletes a single MenuItem or all MenuItem records depending on the DeleteAll flag. Includes validation, reseeding, and full error handling.
Delete_Staging	Deletes StagingDocument and StagingElement records based on StagingDocumentID or StagingMenuItemID Supports full purge with identity reseed
Execute_TestHarness	Executes the TestHarness table to insert parent and child MenuItems into StagingMenuItem table, capturing MenuItemID and ContentDocumentID results and marking tests as PASS/FAIL based on Expected vs Actual.
Get_RouteType	Normalizes a RouteType value by trimming whitespace, uppercasing the input, validating length, and returning the matching RouteType.Code value.
Get_SystemInfo	Retrieves a system setting by key. Returns the raw sql_variant value via output parameter. Throws if the key is not found.
Initialize_Database	Initializes the database by 1) creating missing 2) clearing logs 3) deleting core data 4) reseeding identities 5) repopulating system tables
Insert_BoardMember	Inserts a new BoardMember record.
Insert_Content_Placeholder	Inserts a content record for a MenuItem that does not have one. Validates MenuItemID and Title before creating the ContentDocument record.
Insert_ContentDocument	Inserts a new ContentDocument record after validating content type, workflow status, JSON metadata, and ensuring only one ContentDocument exists per MenuItemID.
Insert_ContentElement	Inserts a new ContentElement record linked to a ContentDocument. Validates ContentDocumentID, FormatType, MimeType and JSON format for BlockInfo. Enforces mutual exclusivity between TextContent and BinaryContent.
Insert_EmailRequest	Inserts a new outbound email request tied to a Registration when available.
Insert_MenuItem	Inserts a StagingMenuItem record with full validation, normalization, auto-sort ordering, placeholder creation, and full logging/error handling
Insert_MenuItem_Child	Inserts a child MenuItem under a specified parent. Inherits RouteType, VisitorMask, and IsActive when not supplied. Auto-generates RouteTarget and SortOrder when needed.
Insert_MenuItem_Parent	Adds a parent menu item to the staging menu.
Insert_MissingContentPlaceholders	Iterates through all MenuItems with RouteType = 'C' and ensures each has a ContentDocument placeholder. Calls Insert_Content_Placeholder for any missing ContentDocument record.
Insert_Registration	Inserts a new Registration + Contact, or if a Registration already exists for @Email, updates that Registration's VisitorType/flags and its existing Contact in place (never creates a second Registration or a second Contact for the same email).
Log_Debug	Inserts a debug entry into the ErrorLog table. If the table does not exist, it is created automatically and a warning entry is logged. Used by all procedures to record debug messages tied to a specific LogDate.
Log_Error	Inserts an error entry into the ErrorLog table. If the table does not exist, it is created automatically and a warning entry is logged. This procedure is used by all error handling blocks to ensure consistent logging.
Opening_Message	Logs a standard opening debug message for a procedure, ensuring consistent logging behavior tied to a specific LogDate.
Perform_MenuItem_Validation	Puts a wrapper around Validate_MenuItem and raises an error if the validation fails. Can validate StagingMenuItem or Production Outputs ErrorLog entries if debug enabled.
Perform_Migration	Runs the legacy content migration and publication process.
Perform_Table_Check	Validates required system, content, lookup, staging, and menu tables. Optionally creates missing tables based on @IsCreateMissing. Throws an exception listing missing tables when creation is disabled.
Populate_BoardMembers	Inserts a list of current Board Members
Populate_ContentDocument	Master procedure to populate all content types. Currently populates articles via Populate_Staging. Validates that the Content table is empty before proceeding.
Populate_ContentType	Populates the ContentType table with the standard content type definitions used throughout the system. Clears existing rows and inserts deterministic values.
Populate_FormatType	Populates the FormatType table with the standard format type definitions used throughout the system. Clears existing rows and inserts deterministic values.
Populate_Lookup_Tables	Populates all lookup tables in correct dependency order. Ensures lookup tables exist, clears MenuItem, and reloads all lookup categories with deterministic values.
Populate_MasterMenu	Loads the MasterMenu table from the MASTER_MENU CSV file with the location defined in SystemSettings. Performs a direct data dump matching the CSV column order exactly. Uses a staging table to load the data first, then copies to the MasterMenu table to resequence the MenuID. 1) Establish the parent/child relationship from the heirarchy in the spreadsheet by setting ParentMenuID for each child (type = 'C') 2) Set RouteTarget when RouteType = 'V' based on the VisitorType table
Populate_MenuItems	Rebuilds menu data from the configured source, validates and stages the menu, publishes MenuItem records, then refreshes portal tiles.
Populate_MimeType	Populates the MimeType table with the standard MIME type definitions used throughout the system. Clears existing rows and inserts deterministic values.
Populate_Numbers_Table	Populates the Numbers table with values from 1 to 1000 using a recursive CTE.
Populate_PortalMenu	Builds portal tile records for menu items assigned to a single visitor type.
Populate_Registry	Populates repeatable local demonstration registrations. The patient registry is exposed as a view over Registration records with VisitorType = 'P'. Seed records cover the available visitor types and demonstrate membership, volunteer, and newsletter preferences. Each registration receives a corresponding bogus Contact record. Fundraising is left disabled for all seed records.
Populate_RouteType	Clears and repopulates the RouteType table with the supported routing types. Validates table existence, deletes dependent MenuItem rows, and inserts the canonical routing definitions.
Populate_Staging	Placeholder procedure for staging population. Currently implements standard logging, error handling, and closing patterns without performing any data changes.
Populate_SystemSettings	Populates the SystemSettings table with recommended baseline settings using Set_SystemSetting. Demonstrates valid examples for each supported SettingType and SettingGroup.
Populate_SystemTables	Populates the SystemTables lookup with all system, lookup, content, staging, and menu tables in the correct sort order.
Populate_TestHarness	Loads menu items into the test harness for validation.
Populate_VisitorType	Populates the VisitorType table with the standard visitor type codes used throughout the system. Clears existing rows and inserts deterministic values.
Populate_WorkflowStatus	Populates the WorkflowStatus table with the standard workflow status codes used throughout the system. Clears existing rows and inserts deterministic values.
Print_Messages	Displays debug messages for a specific LogDate and optionally logs an additional message. Intended to be used inside the ClosingPattern to show debug output when @IsDebug = 1.
Publish_Migration	Transfers mapped legacy documents and elements into the content tables.
Publish_Staging	Publishes StagingMenuItem into production MenuItem.
Set_SystemSetting	Inserts or updates a system setting value in the SystemSettings table. Performs full validation and normalization for all supported SettingTypes.
Show_Foreign_Keys	Returns all foreign keys in the database, or only those referencing a specific parent table when @TableName is provided.
Show_Schema	Displays unified schema information for one or more tables. Supports filtering by table list or table type. Validates table existence, resolves table lists, and outputs columns, indexes, and constraints.
Show_Tables	Returns a filtered list of tables from TableList based on either a comma-separated list of table names or a table type such as Lookup, System, Content, Staging, or Menu.
Success_Message	Writes a standardized success message for procedures and optionally logs additional closing messages or parameters.
Validate_Content	Validates content documents and elements to ensure proper MenuItem relationships, ContentType, WorkflowStatus, RouteType, JSON metadata, and element format/mime types. Writes validation errors to ErrorLog and sets @IsValid accordingly.
Validate_Database	Runs all validation routines (MenuItem, Content, Staging) and aggregates their results into a single @IsValid flag. Writes validation errors to ErrorLog and supports debug output when enabled.
Validate_MenuItem	Validates Staging or Production MenuItem configuration and routing rules. Checks RouteType, RouteTarget, VisitorMask, folder alignment, querystring structure, title normalization, parent relationships, and duplicate SortOrder and Title within the same parent.
Validate_Staging	Validates staging content to ensure all StagingElements reference an existing StagingDocument. Writes validation errors to ErrorLog and sets @IsValid accordingly.

Back to top