S9 — Web Portal — Data Dictionary (Target Design)

TARGET DESIGN — เฉพาะตารางใหม่ (extension ของ PORTAL)

ทุกตาราง/ทุกคอลัมน์ ของ ตารางใหม่ (สร้างจาก schema.sql) · ตาราง reuse เดิมดูที่ data dict ปัจจุบัน · ภาพรวม + Traceability · ERD

convention: audit CreatedAt/CreatedBy/UpdatedAt/UpdatedBy ('system') · FK ผู้ใช้ = nvarchar(100)→UserProfile.Id · ระบบ = nvarchar(50)→RLPDSystems.SystemCode · REQ mapping ดูที่ index

✏️ Modified (reuse table)

ตาราง	การแก้ไข
PermissionRequest	ALTER TABLE PORTAL.PermissionRequest ADD AdOnlyAccess bit NOT NULL DEFAULT 0 — ช่อง "ขอสิทธิ์ AD เท่านั้น" (REQ-WPT-005, O-8) · คอลัมน์เดิมทั้งหมดดูที่ data dict ปัจจุบัน

---

AuditLog

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
UserId	nvarchar(100)
SystemCode	nvarchar(50)
Action	nvarchar(150)	NOT NULL
EntityType	nvarchar(150)
EntityId	nvarchar(100)
Detail	nvarchar(max)		JSON
IpAddress	nvarchar(100)
UserAgent	nvarchar(500)
CreatedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: SystemCode → RLPDSystems.SystemCode

AuthProvider

Column	Data Type	Constraints	Description
Id	int	PK, IDENTITY, NOT NULL
Code	nvarchar(30)	NOT NULL	THAID|DIGITAL_ID|AD|LOCAL
Name	nvarchar(100)	NOT NULL
Protocol	nvarchar(20)	NOT NULL	OIDC|SAML|LDAP|PASSWORD
IsActive	bit	DEFAULT 1, NOT NULL

EndpointRegistry

Column	Data Type	Constraints	Description
Id	int	PK, IDENTITY, NOT NULL
SystemCode	nvarchar(50)	NOT NULL
EndpointPath	nvarchar(500)	NOT NULL
HttpMethod	nvarchar(10)	NOT NULL
SpecVersion	nvarchar(20)		OpenAPI version
OpenApiUrl	nvarchar(1000)		link to spec/swagger
IsActive	bit	DEFAULT 1, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL

Foreign Keys: SystemCode → RLPDSystems.SystemCode

LoginAttempt

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
UserId	nvarchar(100)
Username	nvarchar(100)
CitizenId	nvarchar(13)
Success	bit	NOT NULL
FailureReason	nvarchar(100)		BAD_PASSWORD|LOCKED|INACTIVE|EXPIRED
ProviderCode	nvarchar(30)
IpAddress	nvarchar(100)
UserAgent	nvarchar(500)
AttemptedAt	datetime	DEFAULT getdate(), NOT NULL

Notification

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
RecipientUserId	nvarchar(100)	NOT NULL
Type	nvarchar(50)	NOT NULL	PERMISSION_REQUEST|CASE_ROUTED|...
RefId	nvarchar(255)
Title	nvarchar(255)	NOT NULL
Body	nvarchar(max)
IsRead	bit	DEFAULT 0, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: RecipientUserId → UserProfile.Id

PasswordHistory

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
UserId	nvarchar(100)	NOT NULL
PasswordHash	nvarchar(255)	NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: UserId → UserProfile.Id

PermissionApproval

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
RequestId	nvarchar(255)	NOT NULL	-> PermissionRequest.RequestId
SystemCode	nvarchar(50)	NOT NULL
ApproverUserId	nvarchar(100)
Decision	nvarchar(20)	DEFAULT 'PENDING', NOT NULL	PENDING|APPROVED|REJECTED
DecidedAt	datetime
Note	nvarchar(max)
CreatedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: RequestId → PermissionRequest.RequestId; SystemCode → RLPDSystems.SystemCode; ApproverUserId → UserProfile.Id

PublishedQuery

Column	Data Type	Constraints	Description
Id	int	PK, IDENTITY, NOT NULL
Name	nvarchar(255)	NOT NULL
QueryDefinition	nvarchar(max)	NOT NULL	builder definition (SQL/JSON)
PublishedEndpoint	nvarchar(500)		exposed path
OwnerUserId	nvarchar(100)
ApiTokenId	int		-> ApiTokens.Id (OAuth/token binding)
IsActive	bit	DEFAULT 1, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL

Foreign Keys: OwnerUserId → UserProfile.Id; ApiTokenId → ApiTokens.Id

SecurityPolicy

Column	Data Type	Constraints	Description
Id	int	PK, IDENTITY, NOT NULL
PolicyKey	nvarchar(50)	DEFAULT 'DEFAULT', NOT NULL
MaxFailedAttempts	int	DEFAULT 4, NOT NULL
LockoutMinutes	int	DEFAULT 5, NOT NULL
PasswordMaxAgeDays	int	DEFAULT 60, NOT NULL
InactiveDays	int	DEFAULT 45, NOT NULL
PreventPasswordReuse	bit	DEFAULT 1, NOT NULL
ReuseHistoryCount	int	DEFAULT 1, NOT NULL	"ห้ามซ้ำกับครั้งล่าสุด"
BypassPasswordReuse	bit	DEFAULT 0, NOT NULL	CR-4: admin toggle to bypass
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL

ServiceCenterRequest

Column	Data Type	Constraints	Description
Id	nvarchar(100)	PK, NOT NULL	running id
Origin	nvarchar(30)	DEFAULT 'EXTERNAL', NOT NULL	EXTERNAL (สป.ยธ./MSC) | PORTAL (multi-case)
SourceAgency	nvarchar(255)		'สป.ยธ.','MSC',...
ExternalRef	nvarchar(255)
CitizenId	nvarchar(13)
Channel	nvarchar(50)
Payload	nvarchar(max)		JSON
Status	nvarchar(30)	DEFAULT 'RECEIVED', NOT NULL	RECEIVED|ROUTED|CLOSED
ReceivedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL

ServiceCenterRouting

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
ServiceCenterRequestId	nvarchar(100)	NOT NULL
TargetSystemCode	nvarchar(50)	NOT NULL
RoutingStatus	nvarchar(30)	DEFAULT 'PENDING', NOT NULL	PENDING|ACCEPTED|REJECTED|FORWARDED
Accepted	bit		officer discretion
DecidedBy	nvarchar(100)
DecidedAt	datetime
TargetCaseRef	nvarchar(255)		id created in the target subsystem
Note	nvarchar(max)
CreatedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: ServiceCenterRequestId → ServiceCenterRequest.Id; TargetSystemCode → RLPDSystems.SystemCode

SsoDownstreamGrant

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
SsoSessionId	nvarchar(100)	NOT NULL
SystemCode	nvarchar(50)	NOT NULL
AssertionRef	nvarchar(255)		SAML assertion / OIDC code ref
IssuedAt	datetime	DEFAULT getdate(), NOT NULL
ExpiresAt	datetime

Foreign Keys: SsoSessionId → SsoSession.Id; SystemCode → RLPDSystems.SystemCode

SsoSession

Column	Data Type	Constraints	Description
Id	nvarchar(100)	PK, NOT NULL	session id
UserId	nvarchar(100)	NOT NULL
ProviderId	int
SessionTokenHash	nvarchar(255)
IssuedAt	datetime	DEFAULT getdate(), NOT NULL
ExpiresAt	datetime
LastActivityAt	datetime
IpAddress	nvarchar(100)
UserAgent	nvarchar(500)
IsRevoked	bit	DEFAULT 0, NOT NULL

Foreign Keys: UserId → UserProfile.Id; ProviderId → AuthProvider.Id

SubsystemCard

Column	Data Type	Constraints	Description
Id	int	PK, IDENTITY, NOT NULL
SystemCode	nvarchar(50)	NOT NULL
TitleTh	nvarchar(255)	NOT NULL
TitleEn	nvarchar(255)
Description	nvarchar(500)
IconUrl	nvarchar(500)
LaunchUrl	nvarchar(500)
BreadcrumbRoot	nvarchar(255)		e.g. "OCIPA" -> "OCIPA › คำขอเยียวยา"
Visibility	nvarchar(20)	DEFAULT 'MEMBER_ONLY', NOT NULL	MEMBER_ONLY|PUBLIC|ROLE
DisplayOrder	int	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL

Foreign Keys: SystemCode → RLPDSystems.SystemCode

SubsystemOwner

Column	Data Type	Constraints	Description
Id	int	PK, IDENTITY, NOT NULL
SystemCode	nvarchar(50)	NOT NULL
OwnerUserId	nvarchar(100)	NOT NULL
IsPrimary	bit	DEFAULT 0, NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL

Foreign Keys: SystemCode → RLPDSystems.SystemCode; OwnerUserId → UserProfile.Id

Survey

Column	Data Type	Constraints	Description
Id	int	PK, IDENTITY, NOT NULL
Title	nvarchar(255)	NOT NULL
Description	nvarchar(max)
SystemCode	nvarchar(50)		NULL = global
IsActive	bit	DEFAULT 1, NOT NULL
StartDate	datetime
EndDate	datetime
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL

Foreign Keys: SystemCode → RLPDSystems.SystemCode

SurveyAnswer

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
ResponseId	bigint	NOT NULL
QuestionId	int	NOT NULL
RatingValue	tinyint		1..5 (มากที่สุด..น้อยที่สุด)
ChoiceValue	nvarchar(255)
TextValue	nvarchar(max)

Foreign Keys: ResponseId → SurveyResponse.Id; QuestionId → SurveyQuestion.Id

SurveyQuestion

Column	Data Type	Constraints	Description
Id	int	PK, IDENTITY, NOT NULL
SurveyId	int	NOT NULL
Section	nvarchar(20)	NOT NULL	RESPONDENT|SYSTEM|FORMAT|CONTENT|OVERALL
QuestionText	nvarchar(500)	NOT NULL
AnswerType	nvarchar(20)	NOT NULL	RATING5|CHOICE|TEXT
Sequence	int	DEFAULT 0, NOT NULL
IsRequired	bit	DEFAULT 1, NOT NULL

Foreign Keys: SurveyId → Survey.Id

SurveyResponse

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
SurveyId	int	NOT NULL
SystemCode	nvarchar(50)		which system was evaluated
RespondentGender	nvarchar(50)
RespondentAge	nvarchar(20)
RespondentEducation	nvarchar(100)
RespondentMarital	nvarchar(50)
CitizenId	nvarchar(13)		optional / anonymised
IpAddress	nvarchar(100)
SubmittedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: SurveyId → Survey.Id; SystemCode → RLPDSystems.SystemCode

UsageStatDaily

Column	Data Type	Constraints	Description
StatDate	date	PK, NOT NULL
SystemCode	nvarchar(50)	PK, NOT NULL
LoginCount	int	DEFAULT 0, NOT NULL
RequestCount	int	DEFAULT 0, NOT NULL

Foreign Keys: SystemCode → RLPDSystems.SystemCode

UserIdentity

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
UserId	nvarchar(100)	NOT NULL	-> UserProfile.Id
ProviderId	int	NOT NULL
ExternalSubject	nvarchar(255)	NOT NULL	OIDC 'sub' / AD sAMAccountName
IsPrimary	bit	DEFAULT 0, NOT NULL
LinkedAt	datetime	DEFAULT getdate(), NOT NULL
IsActive	bit	DEFAULT 1, NOT NULL

Foreign Keys: UserId → UserProfile.Id; ProviderId → AuthProvider.Id

UserSecurityStatus

Column	Data Type	Constraints	Description
UserId	nvarchar(100)	PK, NOT NULL
FailedCount	int	DEFAULT 0, NOT NULL
LockedUntil	datetime
PasswordChangedAt	datetime
LastLoginAt	datetime
MustChangePassword	bit	DEFAULT 0, NOT NULL
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL

Foreign Keys: UserId → UserProfile.Id

UserSubsystemMembership

Column	Data Type	Constraints	Description
Id	bigint	PK, IDENTITY, NOT NULL
UserId	nvarchar(100)		-> UserProfile.Id (may be null before first login)
CitizenId	nvarchar(13)	NOT NULL	ThaiD national id (the cross-system key)
SystemCode	nvarchar(50)	NOT NULL	-> RLPDSystems.SystemCode
MembershipStatus	nvarchar(20)	DEFAULT 'MEMBER', NOT NULL	MEMBER|PENDING|REVOKED
Source	nvarchar(20)	DEFAULT 'SUBSYSTEM', NOT NULL	SELF|SUBSYSTEM (how it was granted)
GrantedAt	datetime
RevokedAt	datetime
CreatedAt	datetime	DEFAULT getdate(), NOT NULL
CreatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL
UpdatedAt	datetime	DEFAULT getdate(), NOT NULL
UpdatedBy	nvarchar(100)	DEFAULT 'system', NOT NULL

Foreign Keys: UserId → UserProfile.Id; SystemCode → RLPDSystems.SystemCode