S2 — Proactive Justice Operation System (PJOS) — Data Dictionary (Target Design)¶
TARGET DESIGN
พจนานุกรมข้อมูลของ schema PJOS ตาม schema.sql — ยังไม่ลงฐานข้อมูลจริง · ดู er_diagram.md · index.md
Convention ร่วมทุกตาราง business (ละไว้ในตารางย่อยด้านล่างเพื่อความกระชับ):
CreatedAt datetime2 · CreatedBy int FK→AppUser · UpdatedAt datetime2 · UpdatedBy int FK→AppUser · ตารางที่ soft-delete ได้มี IsDeleted bit
Append-only logs (AuditLog/PermissionLog/CaseStatusHistory/ApprovalAction) ไม่มี Updated*/IsDeleted
PII (🔒): CitizenId (เลขบัตร 13 หลัก), ชื่อ-เบอร์ของผู้เสียหาย/ผู้ใช้, ไฟล์แนบ → เข้ารหัส at-rest AES-256 [NFR-S02] ; การเข้าถึง log ใน AuditLog (Action='VIEW_PII') [NFR-S04]
1. Lookups¶
Province — จังหวัด (77) [REQ-PJO-009]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| ProvinceId 🔑 | int IDENTITY | PK |
| Code | varchar(10) UNIQUE | รหัสย่อ ('BKK','CBI') |
| NameTh / NameEn | nvarchar | ชื่อจังหวัด |
| Region | nvarchar(50) | ภูมิภาค |
| Sequence / IsActive | int / bit | ลำดับ / ใช้งาน |
Channel — ช่องทางรับเรื่อง (10) [REQ-PJO-001/003]¶
ChannelId 🔑, Code varchar(30) UNIQUE (SERVICE_CENTER/MSC/LINE_ORDER/WEB_MONITOR/PROACTIVE/FIELD/HOTLINE/MOBILE/AGENCY/LETTER), NameTh, IsInbound bit (4 ช่องทางหลักที่ต้อง dedup), Sequence, IsActive
HelpType — ประเภทความช่วยเหลือ (self-ref, เรื่องหลัก/รอง) [REQ-PJO-003]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| HelpTypeId 🔑 | int | PK |
| Code | varchar(40) | รหัส (optional) |
| NameTh | nvarchar(300) | ชื่อประเภท |
| ParentHelpTypeId | int FK→HelpType | NULL=เรื่องหลัก, มีค่า=เรื่องรอง |
| SourceSystem | varchar(20) | SERVICE_CENTER / OCIPA (ที่มา Master) |
OffenseBase — ฐานความผิด (มิติรายงาน) · VulnerableGroup — กลุ่มเปราะบาง · PoliceStation — สถานีตำรวจ¶
โครงสร้างคล้ายกัน: …Id 🔑 int, Code varchar, NameTh nvarchar, Sequence, IsActive
PoliceStation มี ProvinceId FK เพิ่ม (Master ร่วม Service Center/OCIPA) [REQ-PJO-003/010]
Unit — หน่วยงานภายในกรมฯ [REQ-PJO-008/012]¶
UnitId 🔑, Code, NameTh nvarchar(300), OwnerName nvarchar(300) (ผู้รับผิดชอบ), IsReferralTarget bit (ใช้เป็นปลายทางส่งต่อภายในได้), Sequence, IsActive
RightsArea — มาตรฐาน 4 ด้าน (Trigger Flow) [REQ-PJO-005]¶
RightsAreaId 🔑, Code varchar(30) UNIQUE (JUSTICE_ACCESS/OFFENDER_COMP/STATE_COMP/OTHER_HELP), NameTh, Sequence, IsActive
RightsAreaItem — รายการย่อยใต้ 4 ด้าน (admin CRUD, CR-4) [REQ-PJO-005]¶
RightsAreaItemId 🔑, RightsAreaId FK→RightsArea, NameTh nvarchar(500), Sequence, IsActive (+ audit cols)
RightsChecklistItem — รายการสิทธิที่ต้องแจ้ง (6) [REQ-PJO-001]¶
RightsChecklistItemId 🔑, Code, NameTh nvarchar(500), Sequence, IsActive
Role — บทบาท S2 (5) [REQ-PJO-012]¶
RoleId 🔑, Code varchar(30) UNIQUE (SYS_ADMIN/EXECUTIVE/SUPERVISOR/OFFICER_PROACTIVE/OFFICER_PROVINCE), NameTh, Description
2. Identity / Auth (thin SSO mirror)¶
AppUser — ผู้ใช้งาน (mirror SSO ของ S9 Portal, ไม่มี password) [REQ-PJO-012]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| AppUserId 🔑 | int | PK |
| Sub | varchar(128) UNIQUE | subject claim จาก JWT |
| Username | varchar(150) | อ้างอิงจาก SSO (ไม่เก็บรหัสผ่าน) |
| CitizenId 🔒 | varchar(13) | เลขบัตร (ThaiD) |
| FullName / Email / Phone | nvarchar/varchar | ข้อมูลติดต่อ |
| ProvinceId | int FK→Province | จังหวัดสังกัด (NULL=ส่วนกลาง เห็นทุกจังหวัด) |
| AuthSource | varchar(20) CHECK | SSO/THAID/AD/LOCAL |
| LastLoginAt / IsActive | datetime2 / bit |
[OPEN-AUTH]
ไม่มี PasswordHash/lockout — auth + lockout 5 ครั้ง (NFR-S07) ทำที่ S9 Portal/SSO · ถ้าลูกค้ายืนยันให้เก็บใน PJOS → เพิ่ม PasswordHash/FailedLoginCount/LockedUntil
RoleAssignment — การมอบบทบาท (mirror) [REQ-PJO-012]¶
RoleAssignmentId 🔑, AppUserId FK, RoleId FK, ProvinceId FK (scope สยจ.), GrantedBy FK→AppUser, GrantedAt, RevokedAt, IsActive · UNIQUE(AppUserId, RoleId, ProvinceId)
OfficerProfile — ข้อมูลเจ้าหน้าที่ผู้รับเคส [REQ-PJO-002/004]¶
OfficerProfileId 🔑, AppUserId FK UNIQUE, UnitId FK→Unit, PositionTitle, Capacity int (เพดานเคส), IsActive
workload (active/due) คำนวณ จาก
CaseAssignmentไม่เก็บซ้ำ — แยกจากAppUser(thin mirror) [D-PJO-3]
3. Intake¶
IntakeRecord — รับเข้า 4 ช่องทาง + Dedup [REQ-PJO-001, O-6]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| IntakeRecordId 🔑 | int | PK |
| IntakeNo | varchar(40) UNIQUE | เลขรายการรับเข้า (SC-IMPORT-001) |
| ChannelId | int FK→Channel | ช่องทาง |
| ExternalRef | varchar(100) | รหัสอ้างอิงต้นทาง (serviceCenterRef/MSC) |
| Summary / RequesterName 🔒 / ProvinceId | ข้อมูลเบื้องต้น sync จากต้นทาง | |
| DuplicateScore | tinyint | คะแนนเสี่ยงซ้ำ 0–100 (demo duplicateRisk) |
| SourceConfidence | tinyint | ความน่าเชื่อถือแหล่ง 0–100 |
| MatchedCaseId | int FK→Case | เคสที่คาดว่าซ้ำ |
| Status | varchar(20) CHECK | PENDING/CONFIRMED/MERGED/REJECTED |
| ConfirmedCaseId | int FK→Case | เคสที่สร้างเมื่อยืนยัน |
| ReceivedAt | datetime2 | เวลารับเข้า |
4. Case core¶
Case — เคสจัดการรายกรณี [REQ-PJO-003/009]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| CaseId 🔑 | int | PK |
| CaseNo | varchar(30) UNIQUE | เลขเคส (S2-2569-00120) |
| ServiceCenterRef | varchar(100) | อ้างอิง Service Center |
| ChannelId | int FK→Channel | ช่องทางที่มา |
| SourceIntakeRecordId | int FK→IntakeRecord | รายการรับเข้าที่ยืนยันเป็นเคสนี้ |
| ProvinceId / District | int FK / nvarchar | จังหวัด / อำเภอ-เขต |
| HelpTypeId / OffenseBaseId / PoliceStationId | int FK | ประเภทช่วยเหลือ / ฐานความผิด / สน. |
| Summary | nvarchar(2000) | เรื่อง/สรุป |
| RightsTopic / BeneficiaryInput | nvarchar | หัวข้อสิทธิ / ความต้องการผู้รับบริการ |
| ConsentStatus | varchar(15) CHECK | PENDING/CONSENTED/DECLINED |
| Priority | varchar(10) CHECK | NORMAL/HIGH/URGENT |
| BudgetEstimate | decimal(12,2) | ประมาณการงบ |
| Status | varchar(30) CHECK | 15 สถานะ: NEW→PENDING_CONTACT→RIGHTS_NOTIFIED→…→PLAN_APPROVED→IN_PROGRESS→REFERRED→PENDING_TERMINATION→EXITED→CLOSED (+DECLINED/REPORTING/PENDING_ASSIGN/PLANNING/PENDING_PLAN_REVIEW/PLAN_REJECTED) |
| ReceivedAt | datetime2 | วันรับเรื่อง (วันแรก SLA) |
| ContactDueAt / ReportDueAt | datetime2 | กำหนด 24 ชม. / 15 วัน |
| ContactSlaBreached / ReportSlaBreached | bit | Flag เมื่อพ้นกรอบ SLA [NFR-P06] |
| NoPlanFlag | bit | ไม่ต้องการทั้ง 4 ด้าน → "ไม่มีแผน" |
| IsDeleted | bit |
CaseVictim — ผู้เสียหายในเคส (1:N, multi-victim + demographics) [REQ-PJO-003, CR-5]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| CaseVictimId 🔑 | int | PK |
| CaseId | int FK→Case | |
| FullName 🔒 / CitizenId 🔒 / Phone 🔒 | nvarchar/varchar | ข้อมูลผู้เสียหาย |
| Gender | varchar(10) CHECK M/F/UNKNOWN | มิติรายงาน "ราย" [REQ-PJO-010] |
| Age | int | มิติรายงาน "ราย" [REQ-PJO-010] |
| VulnerableGroupId | int FK→VulnerableGroup | กลุ่มเปราะบาง |
| IsPrimary | bit | ผู้เสียหายหลัก |
| MediaConsent / MediaConsentAt | bit / datetime2 | ยินยอมออกสื่อ (CR-5) + timestamp |
| IsDeleted | bit |
มิติประชากร (เพศ/อายุ/กลุ่มเปราะบาง) อยู่ที่ "ราย" (CaseVictim) ไม่ใช่ "เรื่อง" (Case) — ตอบ SRS multi-victim + รายงาน "จำนวนราย แยกเพศ/อายุ" (REQ-010) ; ส่วน
OffenseBase/PoliceStation/Channel/Provinceอยู่ที่Case(ขับ "จำนวนเรื่อง")
CaseAssignment — มอบหมาย Multi-assignee + ประวัติ [REQ-PJO-004, CR-3]¶
CaseAssignmentId 🔑, CaseId FK, AssigneeUserId FK→AppUser, IsLead bit (Case Manager หลัก), AssignedBy FK→AppUser (หัวหน้า/ผู้บริหาร), AssignedAt, UnassignedAt, ChangeReason nvarchar(1000), IsCurrent bit
เปลี่ยนผู้รับมอบหมายเก็บประวัติ + เขียน
PermissionLog(REASSIGN) บังคับเหตุผล
CaseRightsCheck — การแจ้งสิทธิรายข้อต่อเคส [REQ-PJO-001]¶
CaseRightsCheckId 🔑, CaseId FK, RightsChecklistItemId FK, IsInformed bit, InformedAt · UNIQUE(CaseId, RightsChecklistItemId)
5. Outreach Reports¶
OutreachReport — รายงานผล 2 ช่วง SLA [REQ-PJO-002, CR-6]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| OutreachReportId 🔑 | int | PK |
| CaseId | int FK→Case | |
| RoundNo | tinyint CHECK 1/2 | 1=วันแรก (24 ชม.) ; 2=ภายใน 15 วัน |
| DueAt | datetime2 | กำหนดส่งรอบนี้ |
| Content | nvarchar(max) | เนื้อหารายงาน |
| Status | varchar(20) CHECK | DRAFT/PENDING_APPROVAL/APPROVED/REJECTED |
| SubmittedAt / CompletedAt | datetime2 | ส่ง / อนุมัติครบ |
UNIQUE(CaseId, RoundNo) · ขั้นอนุมัติแต่ละรอบเก็บใน ApprovalAction (รอบ2 = 2 ขั้น: หัวหน้างาน→ผู้บริหาร)
Source of truth — กำหนดเวลา SLA
Case.ContactDueAt (24 ชม.) / Case.ReportDueAt (15 วัน) เป็นค่าหลัก — คำนวณจาก ReceivedAt ใช้ขับ SLA flag + Dashboard (REQ-PJO-010, NFR-P06) · OutreachReport.DueAt เป็นกำหนดของ "รอบรายงาน" นั้น ๆ (derive: รอบ1 = ContactDueAt, รอบ2 = ReportDueAt) เก็บเพื่อ workflow/แจ้งเตือนของรายงานแต่ละรอบ
6. Trigger Gate + Plan + Approval¶
CaseTrigger — gate มาตรฐาน 4 ด้าน [REQ-PJO-005]¶
CaseTriggerId 🔑, CaseId FK, RightsAreaId FK→RightsArea, IsWanted bit (ต้องการ/ไม่ต้องการ), Note, DecidedAt · UNIQUE(CaseId, RightsAreaId)
ไม่ต้องการครบทั้ง 4 →
Case.NoPlanFlag = 1
Source of truth — \"ไม่มีแผน\"
AssistancePlan.NoPlan เป็นค่าหลัก (authoritative) ของแผนแต่ละ Version · Case.NoPlanFlag เป็น denorm ระดับเคสเพื่อ filter/Dashboard เร็ว — ตั้งจาก CaseTrigger (ไม่ต้องการครบ 4) ; app layer ต้อง sync 2 ค่านี้เมื่อ trigger/แผนเปลี่ยน
AssistancePlan — แผนช่วยเหลือ (versioned) [REQ-PJO-005/006]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| AssistancePlanId 🔑 | int | PK (1 แถว/Version) |
| CaseId | int FK→Case | |
| Version | int | เพิ่มทุกครั้งที่แก้หลังตีกลับ |
| Title / Summary | nvarchar | demo planTitle |
| NoPlan | bit | "ไม่มีแผน" |
| Status | varchar(20) CHECK | DRAFT/PENDING_REVIEW/APPROVED/REJECTED/SUPERSEDED |
| IsCurrent | bit | Version ล่าสุด |
| SubmittedAt / ApprovedAt | datetime2 |
UNIQUE(CaseId, Version) · ApprovalAction.OwnerEntityId ชี้ AssistancePlanId ของ Version เฉพาะ [D-PJO-2]
PlanItem — รายการในแผน (per ด้าน) [REQ-PJO-005]¶
PlanItemId 🔑, AssistancePlanId FK, RightsAreaId FK (NULL=ขั้นตอนกระบวนการ), RightsAreaItemId FK, Title, OwnerText, DueText, Budget decimal(12,2), Detail nvarchar(max), Sequence
ApprovalAction — การพิจารณาแบบลำดับชั้น (polymorphic, append-only) [REQ-PJO-002/006]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| ApprovalActionId 🔑 | int | PK |
| OwnerEntityType | varchar(40) CHECK | OutreachReport / AssistancePlan |
| OwnerEntityId | int | ชี้ Version เฉพาะ (AssistancePlanId/OutreachReportId) |
| StepNo | int | 1=หัวหน้างาน, 2=ผู้บริหาร/ผอ. |
| ApproverRole | varchar(30) | SUPERVISOR / EXECUTIVE |
| ApproverUserId | int FK→AppUser | |
| Action | varchar(10) CHECK | APPROVE / REJECT |
| Decision | varchar(15) CHECK | (เมื่อ REJECT) REWORK / TERMINATE |
| Comment | nvarchar(2000) | บังคับเมื่อ REJECT |
| ActedAt | datetime2 |
ทดแทน per-entity approval ของ S1 — ใช้ร่วม 3 flow (รอบ1/รอบ2/แผน) ; ลำดับขั้น config ที่
ApprovalStep[D-PJO-1]
7. Implementation / Referral / Termination¶
ImplementationResult — บันทึกผลตามแผน + ผลภาคสนาม [REQ-PJO-007]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| ImplementationResultId 🔑 | int | PK |
| CaseId / PlanItemId | int FK | เคส / รายการแผน (ถ้าระบุ) |
| ResultHtml | nvarchar(max) | Rich Text Editor (Bold/Italic/List) |
| AreaLocation | nvarchar(500) | พื้นที่ลงปฏิบัติ (demo FIELD_RESULTS.area) |
| PerformedByUserId / PerformedAt | int FK / datetime2 | ผู้ดำเนินการ |
| CompletionStatus | varchar(15) CHECK | DONE/NOT_DONE/UNABLE |
| CompletionReason | nvarchar(1000) | บังคับเมื่อไม่ DONE [TBD-5 Master] |
| IsDeleted | bit |
แนบหลักฐานบังคับทุกครั้ง →
Attachment(OwnerEntityType='ImplementationResult') ; กฎ "ต้องมีไฟล์" ตรวจที่ app layer (REQ-007)
Referral — ส่งต่อความช่วยเหลือ [REQ-PJO-008]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| ReferralId 🔑 | int | PK |
| CaseId | int FK→Case | |
| TargetType | varchar(15) CHECK | INTERNAL_SYSTEM / INTERNAL_UNIT / EXTERNAL |
| TargetSystem | varchar(30) | S1/S4/S6/… (เมื่อ INTERNAL_SYSTEM) [O-5] |
| TargetUnitId | int FK→Unit | หน่วยงานกรมฯ (เมื่อ INTERNAL_UNIT) |
| TargetOrgName | nvarchar(300) | ภายนอก (รพ./มูลนิธิ) |
| Method | varchar(15) CHECK | WEBSERVICE / MANUAL |
| Reason / Status | nvarchar / varchar CHECK | PENDING/ACCEPTED/REJECTED/FAILED |
| ExternalRefId | varchar(100) | Reference ID ปลายทาง |
| RequestPayload / ResponsePayload | nvarchar(max) | audit รับ-ส่ง |
| SentBy / SentAt / RespondedAt |
ProgramTermination — ขอ/พิจารณายุติโปรแกรม [REQ-PJO-007]¶
ProgramTerminationId 🔑, CaseId FK, Reason varchar(40) (SUCCESS/CLIENT_REQUEST/UNREACHABLE/REFERRED_OUT/DIRECTOR_DECISION — Master [TBD-5]), ReasonNote, Status CHECK REQUESTED/APPROVED/REJECTED, RequestedBy FK, RequestedAt, DecidedBy FK→AppUser (ผอ.), DecidedAt
8. Cross-cutting¶
Attachment — ไฟล์แนบ polymorphic [REQ-PJO-007/008/012]¶
AttachmentId 🔑, OwnerEntityType varchar(40), OwnerEntityId int, FileName 🔒, MimeType, SizeBytes bigint (≥50MB), StoragePath (Object Storage, เข้ารหัส at-rest), UploadedBy FK, UploadedAt, IsDeleted
Notification — แจ้งเตือน [REQ-PJO-002/004/006]¶
NotificationId 🔑 bigint, RecipientUserId FK→AppUser, Type varchar(40) (ASSIGNMENT/APPROVAL_REQUEST/APPROVAL_RESULT/SLA_WARNING), Title, Message, RelatedEntityType/Id, IsRead bit, SentAt
CaseStatusHistory — ประวัติสถานะเคส (append-only) [REQ-PJO-009/012]¶
StatusHistoryId 🔑 bigint, CaseId FK, FromStatus, ToStatus, Note, ChangedBy FK→AppUser, ChangedAt
ApprovalStep — สายอนุมัติ (config) [REQ-PJO-002/006]¶
ApprovalStepId 🔑, Process varchar(40) (OUTREACH_ROUND1/OUTREACH_ROUND2/PLAN_REVIEW), StepNo int, StepName nvarchar, RoleCode (SUPERVISOR/EXECUTIVE), SlaHours int, IsActive · UNIQUE(Process, StepNo)
SystemConfig — ค่าตั้งระบบ (key/value) [REQ-PJO-009/012]¶
ConfigKey 🔑 varchar(100), ConfigValue nvarchar(max), Description, UpdatedAt, UpdatedBy · เก็บ SLA, นโยบาย Masking, limit ผู้เสียหาย, field metadata ที่เปิดเผย
PermissionLog — log สิทธิ + เปลี่ยนผู้รับมอบหมาย (append-only) [REQ-PJO-004/012]¶
PermissionLogId 🔑 bigint, ActorUserId FK, TargetUserId FK→AppUser (NULL=มอบหมายเคส), RoleCode, Action CHECK GRANT/CHANGE/REVOKE/REASSIGN, Reason nvarchar NOT NULL (บังคับ), CreatedAt
AuditLog — audit trail ทั่วระบบ (append-only) [REQ-PJO-012, NFR-S04]¶
| คอลัมน์ | ชนิด | คำอธิบาย |
|---|---|---|
| AuditLogId 🔑 | bigint | PK |
| ActorUserId | int FK | |
| ActorName / ActorRole | nvarchar / varchar | denormalized (คงไว้แม้ user เปลี่ยน) |
| Action | varchar(40) | CREATE/UPDATE/DELETE/VIEW_PII/ASSIGN/APPROVE/REFER/EXPORT/LOGIN |
| EntityType / EntityId | varchar | |
| BeforeData / AfterData | nvarchar(max) | JSON ก่อน/หลัง |
| Reason | nvarchar(1000) | เหตุผล (บังคับสำหรับ reassign/plan reject) |
| IpAddress | varchar(45) | |
| CreatedAt | datetime2 |
Retention
AuditLog/PermissionLog/Notification/CaseStatusHistory เป็น log ปริมาณสูง — วางแผน archive/purge ตามนโยบาย retention ของกรมฯ (align เหมือน S1/S3, รอยืนยัน)