S8 — Official Website — Data Dictionary (Target Design)¶

TARGET DESIGN — ไม่ใช่ schema ปัจจุบัน

ทุกตาราง/ทุกคอลัมน์ (สร้างจาก schema.sql ซึ่งเป็น single source of truth) · ภาพรวม + Traceability · ERD

มาตรฐานทุกตาราง: PK = uniqueidentifier (DEFAULT NEWSEQUENTIALID()) เว้นแต่ตาราง log = bigint IDENTITY · audit CreatedAt/CreatedBy/UpdatedAt/UpdatedBy · soft delete IsDeleted · IsActive

Authorization รวมศูนย์

S8 ไม่มีตาราง Role/UserRole — role catalog อยู่ที่ PORTAL.ModuleRoles, การมอบสิทธิ์อยู่ที่ PORTAL.UserRole + Keycloak (อ่านจาก JWT) · WEB.RoleMenu เก็บแค่ map central role code → เมนูในแอป

`AccessibilityIssue`¶

Column	Data Type	Constraints	Description
AccessibilityIssueId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier
PageUrl	nvarchar(1000)
WcagCriterion	nvarchar(50)		e.g. '1.4.3 Contrast'
Severity	nvarchar(20)	DEFAULT 'CRITICAL', NOT NULL	CRITICAL\|MAJOR\|MINOR
Description	nvarchar(max)
Status	nvarchar(20)	DEFAULT 'OPEN', NOT NULL	OPEN\|IN_PROGRESS\|FIXED\|WONT_FIX
DetectedAt	datetime	DEFAULT getdate(), NOT NULL
ResolvedAt	datetime
CreatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId

`AdminMenu`¶

Column	Data Type	Constraints
AdminMenuId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
ParentMenuId	uniqueidentifier
Section	nvarchar(255)
Name	nvarchar(255)	NOT NULL
MenuLevel	int
Icon	nvarchar(255)
Controller	nvarchar(255)
Action	nvarchar(255)
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL

Foreign Keys: ParentMenuId → AdminMenu.AdminMenuId

`Department`¶

Column	Data Type	Constraints	Description
DepartmentId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
ParentDepartmentId	uniqueidentifier		NULL = top-level (กรมฯ)
Code	nvarchar(50)	NOT NULL	subdomain key: [department].rlpd.go.th
NameTh	nvarchar(500)	NOT NULL
NameEn	nvarchar(500)
Initials	nvarchar(200)
Address	nvarchar(max)
UrlFacebook	nvarchar(500)
UrlTwitter	nvarchar(500)
UrlYoutube	nvarchar(500)
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

Foreign Keys: ParentDepartmentId → Department.DepartmentId

`DepartmentTheme`¶

Column	Data Type	Constraints
DepartmentThemeId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier	NOT NULL
TemplateId	uniqueidentifier	NOT NULL
ThemeColorId	uniqueidentifier
IsActive	bit	DEFAULT 1, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; TemplateId → Template.TemplateId; ThemeColorId → ThemeColor.ThemeColorId

`DepartmentThemeHistory`¶

Column	Data Type	Constraints
Id	bigint	PK, IDENTITY, NOT NULL
DepartmentId	uniqueidentifier	NOT NULL
TemplateId	uniqueidentifier
ThemeColorId	uniqueidentifier
ChangedAt	datetime	DEFAULT getdate(), NOT NULL
ChangedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId

`ApiAccessLog`¶

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
ApiClientId	uniqueidentifier
ApiVersion	nvarchar(10)		'v1','v2' (API versioning)
Endpoint	nvarchar(500)	NOT NULL
HttpMethod	nvarchar(10)
StatusCode	int
RequestIp	nvarchar(100)
ResponseTimeMs	int
CalledAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: ApiClientId → ApiClient.ApiClientId

`ApiClient`¶

Column	Data Type	Constraints	Description
ApiClientId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
ClientName	nvarchar(255)	NOT NULL
ClientType	nvarchar(20)	DEFAULT 'EXTERNAL', NOT NULL	'INTERNAL' \| 'EXTERNAL'
AuthType	nvarchar(20)	DEFAULT 'API_KEY', NOT NULL	'API_KEY' \| 'OAUTH2'
ApiKeyHash	nvarchar(255)
OAuthClientId	nvarchar(255)
RateLimitPerMin	int	DEFAULT 60, NOT NULL	rate limit
AllowedIps	nvarchar(max)		comma-separated CIDR/IP
IsActive	bit	DEFAULT 1, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

`ApiClientScope`¶

Column	Data Type	Constraints	Description
ApiClientScopeId	bigint	PK, IDENTITY, NOT NULL
ApiClientId	uniqueidentifier	NOT NULL
Scope	nvarchar(100)	NOT NULL	'news:read','article:read'
DepartmentId	uniqueidentifier		limit to one department, NULL=all

Foreign Keys: ApiClientId → ApiClient.ApiClientId; DepartmentId → Department.DepartmentId

`AuditLog`¶

Column	Data Type	Constraints
Id	bigint	PK, IDENTITY, NOT NULL
UserId	uniqueidentifier
Username	nvarchar(250)
DepartmentId	uniqueidentifier
Action	nvarchar(150)
EntityType	nvarchar(150)
EntityId	nvarchar(100)
Method	nvarchar(10)
Path	nvarchar(4000)
Detail	nvarchar(max)
ClientIp	nvarchar(100)
CreatedAt	datetime	DEFAULT getdate(), NOT NULL

`Banner`¶

Column	Data Type	Constraints	Description
BannerId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier
BannerTypeId	uniqueidentifier	NOT NULL
BannerLocationId	uniqueidentifier	NOT NULL
TemplateId	uniqueidentifier
BannerGroupId	uniqueidentifier
MediaAssetId	uniqueidentifier		image (original + variants)
Name	nvarchar(255)
Sequence	int	DEFAULT 0, NOT NULL
PeriodActive	bit	DEFAULT 0, NOT NULL
PeriodStart	date
PeriodEnd	date
Target	nvarchar(255)
ShowOnMain	bit	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; BannerTypeId → BannerType.BannerTypeId; BannerLocationId → BannerLocation.BannerLocationId; TemplateId → Template.TemplateId; BannerGroupId → BannerGroup.BannerGroupId; MediaAssetId → MediaAsset.MediaAssetId

`BannerGroup`¶

Column	Data Type	Constraints
BannerGroupId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Name	nvarchar(255)	NOT NULL
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)

`BannerLocation`¶

Column	Data Type	Constraints
BannerLocationId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Name	nvarchar(50)	NOT NULL
LocationType	nvarchar(50)
Sequence	int	DEFAULT 0, NOT NULL

`BannerSetting`¶

Column	Data Type	Constraints
BannerSettingId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier
BannerTypeId	uniqueidentifier	NOT NULL
AutoSlide	bit	DEFAULT 1, NOT NULL
SpeedSlide	int	DEFAULT 3000, NOT NULL
DelaySlide	int	DEFAULT 0, NOT NULL
IsLoop	bit	DEFAULT 1, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; BannerTypeId → BannerType.BannerTypeId

`BannerTranslation`¶

Column	Data Type	Constraints	Description
BannerTranslationId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
BannerId	uniqueidentifier	NOT NULL
LanguageId	uniqueidentifier	NOT NULL
Url	nvarchar(3000)
Alt	nvarchar(3000)		WCAG alt text [REQ-WEB-001]
TitleFirst	nvarchar(100)
TitleSecond	nvarchar(100)
TitleDetail	nvarchar(1000)

Foreign Keys: BannerId → Banner.BannerId; LanguageId → Language.LanguageId

`BannerType`¶

Column	Data Type	Constraints
BannerTypeId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Name	nvarchar(255)	NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL

`Calendar`¶

Column	Data Type	Constraints
CalendarId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier
CalendarCategoryId	uniqueidentifier
Description	nvarchar(255)	NOT NULL
Location	nvarchar(500)
HtmlRemark	nvarchar(max)
StartDate	date	NOT NULL
EndDate	date	NOT NULL
IsTimeSpecified	bit	DEFAULT 0, NOT NULL
StartTime	time
EndTime	time
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; CalendarCategoryId → CalendarCategory.CalendarCategoryId

`CalendarCategory`¶

Column	Data Type	Constraints
CalendarCategoryId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Name	nvarchar(255)	NOT NULL
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL

`Category`¶

Column	Data Type	Constraints
CategoryId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier
NameTh	nvarchar(255)	NOT NULL
ShortUrl	char(6)
IsContentImages	bit	DEFAULT 0, NOT NULL
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId

`CategoryTranslation`¶

Column	Data Type	Constraints
CategoryTranslationId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
CategoryId	uniqueidentifier	NOT NULL
LanguageId	uniqueidentifier	NOT NULL
Name	nvarchar(255)
HtmlContent	nvarchar(max)

Foreign Keys: CategoryId → Category.CategoryId; LanguageId → Language.LanguageId

`Content`¶

Column	Data Type	Constraints	Description
ContentId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
ContentTypeId	uniqueidentifier
CategoryId	uniqueidentifier
DepartmentId	uniqueidentifier
ParentContentId	uniqueidentifier
Name	nvarchar(500)
Target	varchar(10)		'_self' \| '_blank'
ShortUrl	char(8)
Sequence	int	DEFAULT 0, NOT NULL
Status	nvarchar(20)	DEFAULT 'DRAFT', NOT NULL	DRAFT\|PENDING\|APPROVED\|PUBLISHED\|ARCHIVED [workflow]
PeriodActive	bit	DEFAULT 0, NOT NULL
PeriodStart	date
PeriodEnd	date
CountView	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

Foreign Keys: ContentTypeId → ContentType.ContentTypeId; CategoryId → Category.CategoryId; DepartmentId → Department.DepartmentId; ParentContentId → Content.ContentId

`ContentArticle`¶

Column	Data Type	Constraints	Description
ContentArticleId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
ContentId	uniqueidentifier	NOT NULL
LanguageId	uniqueidentifier	NOT NULL
Title	nvarchar(500)
HtmlContent	nvarchar(max)		WYSIWYG / HTML code mode, large content
EmbedCode	nvarchar(max)

Foreign Keys: ContentId → Content.ContentId; LanguageId → Language.LanguageId

`ContentAttachFile`¶

Column	Data Type	Constraints
ContentAttachFileId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
ContentId	uniqueidentifier	NOT NULL
MediaAssetId	uniqueidentifier	NOT NULL
LanguageId	uniqueidentifier
Title	nvarchar(255)
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)

Foreign Keys: ContentId → Content.ContentId; MediaAssetId → MediaAsset.MediaAssetId; LanguageId → Language.LanguageId

`ContentImage`¶

Column	Data Type	Constraints
ContentImageId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
ContentId	uniqueidentifier	NOT NULL
MediaAssetId	uniqueidentifier	NOT NULL
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)

Foreign Keys: ContentId → Content.ContentId; MediaAssetId → MediaAsset.MediaAssetId

`ContentRevision`¶

Column	Data Type	Constraints	Description
ContentRevisionId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
ContentId	uniqueidentifier	NOT NULL
LanguageId	uniqueidentifier	NOT NULL
VersionNo	int	NOT NULL
Title	nvarchar(500)
HtmlContent	nvarchar(max)
Status	nvarchar(20)	DEFAULT 'DRAFT', NOT NULL	DRAFT\|PENDING\|APPROVED\|REJECTED\|PUBLISHED
EditorBy	nvarchar(100)
SubmittedAt	datetime
ReviewerBy	nvarchar(100)
ReviewedAt	datetime
ReviewNote	nvarchar(max)
CreatedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: ContentId → Content.ContentId; LanguageId → Language.LanguageId

`ContentType`¶

Column	Data Type	Constraints
ContentTypeId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Name	nvarchar(255)	NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL

`EmbedCode`¶

Column	Data Type	Constraints	Description
EmbedCodeId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier		NULL = global / main site
Scope	nvarchar(20)	DEFAULT 'DEPARTMENT', NOT NULL	'SITE' \| 'DEPARTMENT'
Position	nvarchar(20)	NOT NULL	'HEADER' \| 'FOOTER' \| 'SECTION'
PlacementKey	nvarchar(100)		section identifier when Position='SECTION'
Name	nvarchar(255)	NOT NULL
Provider	nvarchar(50)		'FACEBOOK' \| 'JAVASCRIPT' \| 'CHATBOT' \| 'OTHER'
CodeSnippet	nvarchar(max)	NOT NULL
RequiresSuperAdmin	bit	DEFAULT 0, NOT NULL	HEADER-level => 1
IsEnabled	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId

`EmbedCodeAudit`¶

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
EmbedCodeId	uniqueidentifier	NOT NULL
Action	nvarchar(20)	NOT NULL	CREATE\|UPDATE\|DELETE\|ENABLE\|DISABLE
OldValue	nvarchar(max)
NewValue	nvarchar(max)
ChangedBy	nvarchar(100)
ChangedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: EmbedCodeId → EmbedCode.EmbedCodeId

`Faq`¶

Column	Data Type	Constraints
FaqId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier
ParentFaqId	uniqueidentifier
Name	nvarchar(max)
Sequence	int	DEFAULT 0, NOT NULL
ViewCount	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; ParentFaqId → Faq.FaqId

`FaqQuestion`¶

Column	Data Type	Constraints
FaqQuestionId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
FaqId	uniqueidentifier	NOT NULL
LanguageId	uniqueidentifier
Question	nvarchar(max)
Answer	nvarchar(max)
IsFeatured	bit	DEFAULT 0, NOT NULL
ViewCount	int	DEFAULT 0, NOT NULL
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)

Foreign Keys: FaqId → Faq.FaqId; LanguageId → Language.LanguageId

`Footer`¶

Column	Data Type	Constraints
FooterId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier
TemplateId	uniqueidentifier
ParentFooterId	uniqueidentifier
Name	nvarchar(255)
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; TemplateId → Template.TemplateId; ParentFooterId → Footer.FooterId

`FooterItem`¶

Column	Data Type	Constraints
FooterItemId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
FooterId	uniqueidentifier	NOT NULL
LanguageId	uniqueidentifier
Name	nvarchar(255)
Url	nvarchar(max)
Sequence	int	DEFAULT 0, NOT NULL

Foreign Keys: FooterId → Footer.FooterId; LanguageId → Language.LanguageId

`Language`¶

Column	Data Type	Constraints	Description
LanguageId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Code	nvarchar(5)	NOT NULL	'th','en'
Name	nvarchar(50)	NOT NULL
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL

`MediaAsset`¶

Column	Data Type	Constraints	Description
MediaAssetId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier
MediaTypeId	uniqueidentifier
FileName	nvarchar(255)	NOT NULL	stored name
OriginalName	nvarchar(255)	NOT NULL
StoragePath	nvarchar(1000)	NOT NULL
MimeType	nvarchar(100)
FileSize	bigint
Width	int		original dimensions (images)
Height	int
AltText	nvarchar(1000)		WCAG alt [REQ-WEB-001]
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; MediaTypeId → MediaType.MediaTypeId

`MediaType`¶

Column	Data Type	Constraints	Description
MediaTypeId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Extension	nvarchar(20)	NOT NULL	'jpg','png','webp','pdf','docx','xlsx'
MimeType	nvarchar(100)	NOT NULL
Category	nvarchar(20)	NOT NULL	'IMAGE' \| 'DOCUMENT' \| 'VIDEO'
IsActive	bit	DEFAULT 1, NOT NULL

`MediaVariant`¶

Column	Data Type	Constraints	Description
MediaVariantId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
MediaAssetId	uniqueidentifier	NOT NULL
VariantLabel	nvarchar(50)	NOT NULL	'banner-main','banner-service','article','thumb'
Width	int	NOT NULL
Height	int	NOT NULL
StoragePath	nvarchar(1000)	NOT NULL
FileSize	bigint
CreatedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: MediaAssetId → MediaAsset.MediaAssetId

`Menu`¶

Column	Data Type	Constraints	Description
MenuId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier		NULL = main site navbar
ParentMenuId	uniqueidentifier
MenuTypeId	uniqueidentifier
CategoryId	uniqueidentifier		when menu links to a content category
Url	nvarchar(max)
Icon	nvarchar(255)
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; ParentMenuId → Menu.MenuId; MenuTypeId → MenuType.MenuTypeId

`MenuTranslation`¶

Column	Data Type	Constraints
MenuTranslationId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
MenuId	uniqueidentifier	NOT NULL
LanguageId	uniqueidentifier	NOT NULL
Name	nvarchar(255)	NOT NULL

Foreign Keys: MenuId → Menu.MenuId; LanguageId → Language.LanguageId

`MenuType`¶

Column	Data Type	Constraints	Description
MenuTypeId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Name	nvarchar(255)	NOT NULL	'LINK','CATEGORY','DROPDOWN', ...

`Popup`¶

Column	Data Type	Constraints	Description
PopupId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
DepartmentId	uniqueidentifier
LanguageId	uniqueidentifier
Name	nvarchar(255)
ContentType	int		1=image, 2=html...
HtmlContent	nvarchar(max)
MediaAssetId	uniqueidentifier
Alt	nvarchar(max)		WCAG
SizeClass	nvarchar(20)		modal size (sm/md/lg)
PeriodActive	bit	DEFAULT 0, NOT NULL
PeriodStart	date
PeriodEnd	date
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; LanguageId → Language.LanguageId; MediaAssetId → MediaAsset.MediaAssetId

`Position`¶

Column	Data Type	Constraints
PositionId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Description	nvarchar(255)	NOT NULL
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL

`PublicAccessLog`¶

Column	Data Type	Constraints
Id	bigint	PK, IDENTITY, NOT NULL
ClientIp	nvarchar(100)
Path	nvarchar(4000)
Method	nvarchar(10)
CategoryId	uniqueidentifier
ContentId	uniqueidentifier
CreatedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: CategoryId → Category.CategoryId; ContentId → Content.ContentId

`RoleMenu`¶

Column	Data Type	Constraints	Description
RoleMenuId	bigint	PK, IDENTITY, NOT NULL
RoleCode	nvarchar(50)	NOT NULL	e.g. CONTENT_EDITOR — from JWT / PORTAL.ModuleRoles (SystemCode='S8')
AdminMenuId	uniqueidentifier	NOT NULL
PermissionLevel	int	DEFAULT 0, NOT NULL	0=none, 1=view, 2=manage

Foreign Keys: AdminMenuId → AdminMenu.AdminMenuId

`SearchLog`¶

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
SearchScope	nvarchar(20)	NOT NULL	'ARTICLE' \| 'INTERNAL'
Keyword	nvarchar(500)	NOT NULL
UserId	uniqueidentifier		NULL = public/anonymous (article search)
DepartmentId	uniqueidentifier
ResultCount	int
SearchedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: UserId → User.UserId; DepartmentId → Department.DepartmentId

`Template`¶

Column	Data Type	Constraints	Description
TemplateId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Code	nvarchar(50)	NOT NULL
Name	nvarchar(100)	NOT NULL
Kind	nvarchar(20)	NOT NULL	'MAIN_VERTICAL' \| 'SUBDEPARTMENT' \| 'STANDARD_VERTICAL'
IsStandard	bit	DEFAULT 0, NOT NULL	the Standard Vertical Template
PublicFolder	nvarchar(100)
BannerMainWidth	int		recommended banner sizes -> drives auto-resize variants
BannerMainHeight	int
BannerServiceWidth	int
BannerServiceHeight	int
MaxContentLeft	int
MaxContentRight	int
MaxFooter	int
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL

`ThemeColor`¶

Column	Data Type	Constraints	Description
ThemeColorId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
Name	nvarchar(255)	NOT NULL
StyleDom	nvarchar(max)		CSS variables / style block
Sequence	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL

`User`¶

Column	Data Type	Constraints	Description
UserId	uniqueidentifier	PK, DEFAULT NEWSEQUENTIALID(), NOT NULL
KeycloakUserId	nvarchar(100)		Keycloak UUID (SSO identity) — links to realm 'rlpd'
Username	nvarchar(255)	NOT NULL	mirrors Keycloak username
CitizenId	nchar(13)		from ThaiD
Title	nvarchar(50)
Name	nvarchar(100)
Email	nvarchar(100)
Tel	nvarchar(20)
DepartmentId	uniqueidentifier
SubDepartmentId	uniqueidentifier
PositionId	uniqueidentifier
LastLoginAt	datetime
IsActive	bit	DEFAULT 1, NOT NULL
IsDeleted	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)

Foreign Keys: DepartmentId → Department.DepartmentId; SubDepartmentId → Department.DepartmentId; PositionId → Position.PositionId

S8 — Official Website — Data Dictionary (Target Design)¶

AccessibilityIssue¶

AdminMenu¶

Department¶

DepartmentTheme¶

DepartmentThemeHistory¶

ApiAccessLog¶

ApiClient¶

ApiClientScope¶

AuditLog¶

Banner¶

BannerGroup¶

BannerLocation¶

BannerSetting¶

BannerTranslation¶

BannerType¶

Calendar¶

CalendarCategory¶

Category¶

CategoryTranslation¶

Content¶

ContentArticle¶

ContentAttachFile¶

ContentImage¶

ContentRevision¶

ContentType¶

EmbedCode¶

EmbedCodeAudit¶

Faq¶

FaqQuestion¶

Footer¶

FooterItem¶

Language¶

MediaAsset¶

MediaType¶

MediaVariant¶

Menu¶

MenuTranslation¶

MenuType¶

Popup¶

Position¶

PublicAccessLog¶

RoleMenu¶

SearchLog¶

Template¶

ThemeColor¶

User¶

`AccessibilityIssue`¶

`AdminMenu`¶

`Department`¶

`DepartmentTheme`¶

`DepartmentThemeHistory`¶

`ApiAccessLog`¶

`ApiClient`¶

`ApiClientScope`¶

`AuditLog`¶

`Banner`¶

`BannerGroup`¶

`BannerLocation`¶

`BannerSetting`¶

`BannerTranslation`¶

`BannerType`¶

`Calendar`¶

`CalendarCategory`¶

`Category`¶

`CategoryTranslation`¶

`Content`¶

`ContentArticle`¶

`ContentAttachFile`¶

`ContentImage`¶

`ContentRevision`¶

`ContentType`¶

`EmbedCode`¶

`EmbedCodeAudit`¶

`Faq`¶

`FaqQuestion`¶

`Footer`¶

`FooterItem`¶

`Language`¶

`MediaAsset`¶

`MediaType`¶

`MediaVariant`¶

`Menu`¶

`MenuTranslation`¶

`MenuType`¶

`Popup`¶

`Position`¶

`PublicAccessLog`¶

`RoleMenu`¶

`SearchLog`¶

`Template`¶

`ThemeColor`¶

`User`¶