S1 — Legal Consultation System (LCS) — Target Schema (Phase 2)¶

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

ออกแบบใหม่ (greenfield) เพื่อ implement requirement จาก SRS S1 v3.0.0 (TOR 7.11.1–7.11.2) · ระบบนี้ยังไม่มี DB จริง — de-facto contract คือ mockup repos/lcs/fe/src/data.jsx + screens-*.jsx (การแปลง mockup → schema สรุปใน Mockup → Target mapping) · DDL เต็ม: schema.sql · ERD · Data Dictionary · 🛠️ Backend Implementation

ทำไมต้องออกแบบใหม่ (greenfield)¶

S1 ไม่มีระบบ legacy ให้ migrate — mockup lcs/fe (React/Vite) เก็บข้อมูลเป็น mock array ใน data.jsx (LAWYERS, INBOX_CASES, MY_CASES, QA_QUEUE ...) + ค่าฝังใน screens-*.jsx เท่านั้น lcs/be (NestJS) ยังเป็น placeholder ว่าง งานนี้คือแปลง mock contract นั้นเป็น clean target schema ระดับ production:

normalize array → junction — LAWYERS.spec[] → ConsultantSpecialty · 1 ที่ปรึกษาหลายจังหวัด → ConsultantProvince
เพิ่ม FK + audit + workflow ที่ mockup ไม่มี — FK referential integrity ครบทุก relation, audit cols (CreatedAt/CreatedBy/UpdatedAt/UpdatedBy), workflow state (application → review → approve, QA pass/fail/reback, escalation)
แยกแผนเวร vs ปฏิบัติจริง — DutyRoster/DutySlot (แผน) แยกจาก DutySession (ชั่วโมงจริง) เพราะค่าตอบแทนคิดจากชั่วโมงจริง และผู้มาแทนได้สถิติ ผู้ยกเลิกไม่ได้
persistent import round — ImportBatch ไม่ใช่แค่ validation ชั่วคราว: upload รอบใหม่ = เริ่มวาระ 3 ปีใหม่ และ "ไม่มีชื่อในรอบใหม่ → เพิกถอน"
polymorphic Attachment — 1 ตารางแนบไฟล์ใช้ร่วมทุก entity
thin-mirror auth — AppUser/RoleAssignment/Role เป็นเงา SSO กลาง ไม่เก็บรหัสผ่าน (ดู [OPEN-AUTH] ด้านล่าง)

S1 PK = int IDENTITY (เหมือน S3, ต่างจาก design_p2 D7)

S8/S9 ใช้ uniqueidentifier เพื่อ carry over GUID จาก legacy · S1 เป็น greenfield ไม่มี GUID เดิม จึงใช้ int IDENTITY(1,1) เป็น PK ทุกตาราง (log ปริมาณสูง = bigint IDENTITY) · CreatedBy/UpdatedBy = int FK → LCS.AppUser

Stack & Architecture (จาก SRS §2.1, §5.3)¶

ส่วน	เทคโนโลยี (ตาม SRS)
Backend	JavaScript / NestJS / RESTful API (`lcs-api.rlpd.go.th`)
Frontend	Responsive Web (Desktop/Tablet/Mobile) — mockup `lcs/fe`
Database	MS SQL Server — schema ใหม่ของ LCS
Auth	ThaiD (Digital ID) หลัก + Username/Password fallback (OAuth 2.0)
Integration	Web Service REST/SOAP, OpenAPI 3.0 → OCIPA (S6) + ระบบงานอื่น
Observability	NestJS app ใช้ไลบรารีกลาง `@rlpdjs/observability` (logger/trace/metrics/DB query log) → ELK [NFR-M02]

ความสัมพันธ์กับ repo rlpdjs

rlpdjs = แพ็กเกจ @rlpdjs/observability (ไลบรารี NestJS ที่ใช้ร่วมกัน) — ไม่ใช่ backend ของ S1 Prisma stub ของมันเป็น postgresql เพื่อ type-gen ของไลบรารีเท่านั้น ("consumer apps define their own schema") → backend ของ S1 สร้างใหม่ใน lcs/be (NestJS) แล้ว install @rlpdjs/observability เป็น dependency ; Prisma ของ S1 ตั้ง provider = sqlserver ชี้ schema LCS · ดู implementation.md

Role Hierarchy (SRS §3.1, 6 roles) — บริหารรวมศูนย์¶

Role Code	บทบาท	สิทธิ์โดยสรุป
ADMIN	ผู้ดูแลระบบ	จัดการผู้ใช้/สิทธิ์, Master Data, ตั้งค่าระบบ, Audit Log
DIRECTOR	ผอ.กพส.	อนุมัติลงทะเบียนที่ปรึกษา, เพิกถอนสิทธิ, รายงานเชิงบริหาร
OFFICER_LEGAL	พนง./จนท.คุ้มครองสิทธิฯ/นิติกร	บันทึกคำขอ, มอบหมายงาน, QA, ส่งต่อ Escalation, จัดการเอกสาร
CONSULTANT	ที่ปรึกษากฎหมาย	บันทึกผลคำปรึกษา, ตรวจสอบสถานะตน, แจ้งวันปฏิบัติงาน, ดูตารางเวร
PUBLIC	ประชาชน	ยื่นคำขอ + ติดตามผลด้วยเลขอ้างอิง ผ่าน Service Center / Web Portal
AUDITOR	ผู้ตรวจสอบ	เข้าถึง Log + Audit Trail (read-only)

กำหนดสิทธิ์ที่ส่วนกลาง (SSO via JWT) — สอดคล้อง design_p2 D8

ยืนยันตัวตน + มอบสิทธิ์อยู่ที่ SSO กลาง (ThaiD / Keycloak realm rlpd federate กับ RLPD AD) LCS อ่านบทบาทจริงจาก JWT ขณะ runtime · AppUser/RoleAssignment/Role เป็น local mirror (auto-provision เมื่อ login ครั้งแรก) — ไม่มีคอลัมน์ password/lockout

🔑 Requirement → Table Traceability (spine)¶

ทุก REQ-LCS-001…013 มีแถว — map ไปอย่างน้อย 1 ตาราง หรือ ระบุชัดว่าไม่มีผลต่อ schema

REQ	TOR	สาระสำคัญ	ตารางที่รองรับ
REQ-LCS-001	7.11.1	ลงทะเบียน + ตรวจคุณสมบัติ + อนุมัติ + วาระ 3 ปี + 1 คนหลายจังหวัด	`ConsultantApplication`, `Consultant`, `ConsultantSpecialty`, `ConsultantProvince`, `ConsultantTerm`, `Attachment`, `ApprovalStep`
REQ-LCS-002	7.11.1	ที่ปรึกษาตรวจสอบสถานะตนเอง (Self-Service) ผ่าน ThaiD	`Consultant` (Status), `ConsultantTerm`, `AppUser`, `DutySession` (ประวัติ), `DutySlot` (ตารางเวร)
REQ-LCS-003	7.11.1	นำเข้าไฟล์ .xls/.xlsx/.csv + Batch Validation + นำเข้าตารางเวร	`ImportBatch`, `ImportError`, `DutyRoster`, `DutySlot`
REQ-LCS-004	7.11.1	เพิกถอนสิทธิ (หมดวาระ/อายุ 75/ลาออก/ร้องเรียน/ไม่อยู่ในรอบนำเข้า) + auto + แจ้งผล	`ConsultantRevocation`, `Consultant` (Status), `Attachment`, `Notification`
REQ-LCS-005	7.11.1	จัดการสิทธิ + Log ทุกครั้ง + บังคับเหตุผลเมื่อยกเลิก	`RoleAssignment`, `PermissionLog`, `Role`, `AppUser`
REQ-LCS-006	7.11.2	บันทึกคำขอจาก 8 ช่องทาง + มอบหมาย (เฉพาะที่ปรึกษา Active+ว่าง)	`ConsultationRequest`, `Channel`, `RequestAssignment`, `Attachment`, `DutySession`/`DutySlot` (เช็คว่าง)
REQ-LCS-007	7.11.2	บันทึกผล (แก้ไข/ย้อนหลังได้) + QA pass/fail/ตีกลับ + checklist รายเกณฑ์ + Log	`ConsultationResult`, `QAReview`, `QAReviewItem`, `RequestStatusHistory`, `Notification`
REQ-LCS-008	7.11.2	Escalation ผ่าน Web Service (เริ่ม OCIPA) + เก็บ Reference ID	`Escalation`, `ConsultationRequest` (Status)
REQ-LCS-009	7.11.2	แจ้งวันว่าง + จัดเวร (กลาง 1–3/วัน ญ+ช, ภูมิภาค 1/วัน) + ยกเลิก + สถิติ	`ConsultantAvailability`, `DutyRoster`, `DutySlot`, `DutySession`, `DutyCancellation`
REQ-LCS-010	7.11.2	ค่าตอบแทน 8 ชม.=1,000 (ไม่ครบไม่จ่าย) แยกจังหวัด + สืบค้นการปฏิบัติ	`CompensationCalculation`, `CompensationItem`, `DutySession`, `Province`
REQ-LCS-011	7.11.2	Dashboard ≥5 รายงาน (Top 5 หัวข้อ drill-down, ช่องทาง, ฯลฯ) + Export	`ConsultationTopic` (drill-down), `ConsultationRequest`, `Channel`, `Consultant` — aggregate queries ; export → `AuditLog`
REQ-LCS-012	7.11.2	ประชาชนติดตามผลด้วยเลขอ้างอิง — เห็นเฉพาะสถานะที่อนุญาต	`ConsultationRequest` (ReferenceNo + PublicStatus), `RequestStatusHistory`, `SystemConfig` (สถานะที่เปิดเผย)
REQ-LCS-013	7.11.2	Log + Audit Trail ทุกการกระทำสำคัญ + before/after	`AuditLog`, `PermissionLog` (+ทุกตารางมี audit cols)
Auth	§3.1	SSO via JWT + RBAC 6 บทบาท	`AppUser` (mirror), `RoleAssignment` (mirror), `Role` (catalog)

REQ ที่ไม่มีผลต่อ schema (จัดการที่ app/infra layer)

REQ-LCS-011 — chart rendering + export .xlsx/.pdf/.csv = report engine ที่ app layer ; schema เก็บได้แค่ export action ใน AuditLog
NFR ทั้งหมด — TLS 1.2+ (NFR-S01), AES-256 at-rest (NFR-S02), lockout (NFR-S06), CSP (NFR-S07), 99.5% availability = infra/app layer ; ที่กระทบ schema คือ "PII ต้องเข้ารหัส at-rest" → คอลัมน์ CitizenId/ไฟล์แนบวางแผนเข้ารหัส (ดู data_dict.md)
Auth/lockout — อยู่ที่ central SSO ; LCS อ่าน JWT เท่านั้น

Table Inventory (36 ตาราง, schema `LCS`)¶

กลุ่ม	ตาราง
Lookup (6)	`Province`, `CaseType`, `Channel`, `Specialty`, `ConsultationTopic` (self-ref), `Role`
Identity/Auth — mirror (2)	`AppUser` (SSO, no password), `RoleAssignment`
Consultant Registry (8)	`ImportBatch`, `ImportError`, `ConsultantApplication`, `Consultant`, `ConsultantSpecialty`, `ConsultantProvince`, `ConsultantTerm`, `ConsultantRevocation`
Duty Roster & Attendance (5)	`DutyRoster`, `DutySlot`, `ConsultantAvailability`, `DutySession` (actual), `DutyCancellation`
Consultation Requests (7)	`ConsultationRequest`, `RequestAssignment`, `ConsultationResult`, `QAReview`, `QAReviewItem`, `Escalation`, `RequestStatusHistory`
Compensation (2)	`CompensationCalculation`, `CompensationItem`
Cross-cutting (6)	`Attachment` (polymorphic), `Notification`, `ApprovalStep`, `SystemConfig`, `PermissionLog`, `AuditLog`

Mockup → Target mapping (รองรับ frontend)¶

mockup data.jsx มี 5 entity หลัก + ค่าฝังใน screens-*.jsx — ตารางที่เหลือเป็น support/workflow/audit ที่เพิ่มเพื่อ integrity

Mockup (data.jsx / screens)	Target (LCS)	หมายเหตุ
`LAWYERS` (id, name, spec[], prov, status, since, cases, rating, edu, cert, exp)	`Consultant` + `ConsultantSpecialty` + `ConsultantProvince` + `ConsultantTerm`	spec[]→junction ; 1 คนหลายจังหวัด→junction ; since→Term.StartDate ; cases/rating = denorm
`INBOX_CASES`/`MY_CASES`/`mkCase` (id, channel, citizen, province, caseType, summary, receivedAt, status, files)	`ConsultationRequest` (+ `Attachment` สำหรับ files)	id→ReferenceNo UNIQUE ; channel/caseType→lookup FK ; status→CHECK
`QA_QUEUE` (qaScore, submittedAt) + checklist 5 เกณฑ์ Pass/Fail/N/A (`qa-pending`)	`QAReview` + `QAReviewItem` + `ConsultationResult`	qaScore→QAReview.Score ; 5 เกณฑ์→`QAReviewItem` ; ผลคำปรึกษาแยกเป็น `ConsultationResult` versioned
Compensation rows (hours, formula, paid) — `screens-7-9`	`CompensationCalculation` + `CompensationItem`	paid `จ่ายแล้ว/รอจ่าย/ส่ง S5 แล้ว`→Status `PAID/CONFIRMED/SENT_S5` ; ส่งต่อ S5 = `S5Reference`
Escalation rows (target, reason, priority, status) — `screens-extra`	`Escalation`	target→TargetOrgName ; status `accepted`→`ACCEPTED`
Roster grid (`lawyer-roster`)	`DutyRoster` + `DutySlot` + `DutySession` + `DutyCancellation`	แผน vs จริง แยกตาราง
Approval workflow + SLA (`admin-workflow`/`admin-sla`)	`ApprovalStep` + `SystemConfig`	step/sla configurable
Audit logs (`admin-audit`, `requiresReason`, คอลัมน์ เหตุผล)	`AuditLog` (+ `Reason`) + `PermissionLog`	`role.update`/`lawyer.revoke`/`qa.reject` บังคับเหตุผล → เก็บใน `AuditLog.Reason`
Channels/Statuses/Provinces/CaseTypes constants	`Channel`/`Province`/`CaseType` + CHECK	enum ป้ายไทย → lookup/CHECK

Support tables ที่เพิ่มใหม่ (ไม่มีใน mockup): ConsultantApplication (workflow ลงทะเบียน), ImportBatch/ImportError (REQ-003), ConsultantTerm/ConsultantRevocation (วาระ/เพิกถอน), ConsultantAvailability, RequestAssignment/RequestStatusHistory, Notification, Attachment, AppUser/RoleAssignment/Role, lookups.

หลักการแปลง mockup → relational (ทำไม mock ขึ้น production ตรง ๆ ไม่ได้)¶

data.jsx เป็น in-memory mock — บอกแค่รูปร่างข้อมูลที่ UI ต้องใช้ ไม่ได้บอกวิธีจัดเก็บ การแปลงหลัก:

id string → surrogate PK + business code — S1-2569-00432/LAW-2569-0001 (ปนความหมายธุรกิจกับ key) → PK int IDENTITY + คอลัมน์ business code UNIQUE (ReferenceNo/ConsultantCode) ; ReferenceNo ต้อง stable เพราะประชาชนใช้ติดตามผล (REQ-012)
array / single value → junction — LAWYERS.spec[] → ConsultantSpecialty ; LAWYERS.prov (เดียว) → ConsultantProvince (N:M) เพราะ SRS ว่า "1 คนปฏิบัติได้หลายจังหวัด"
enum ป้ายไทย → CHECK/lookup — 'รอมอบหมาย'/'ผ่าน QA'/'Active' (เปราะ, join บนสตริงไทย) → CHECK constraint + lookup table
string ชื่อ → FK — assignedTo:'นายอนันต์ แก้วใส' → RequestAssignment.ConsultantId (FK จริง)
field snapshot → derived/denorm — cases:128/rating:4.8 → Consultant.ClosedCaseCount/Rating (คำนวณจากแถวจริง)

สิ่งที่ "ตัดออก" ไม่ยกมาเป็นตาราง: STATUSES palette (สี = presentation), NAV/ROLE_NAV (routing config — ได้จาก role ใน JWT), fmtThaiDate/fmtMoney (utility ฝั่ง UI)

mock formula ค่าตอบแทนขัดกับ SRS — ยึด SRS

screens-7-9.jsx โชว์สูตรผสม (รายเคส ฿500 + รายชั่วโมง ฿200 / เหมาเวร) ซึ่ง ไม่ตรง SRS REQ-010 (8 ชม. = 1,000 บาท ไม่ครบไม่จ่าย) → schema ยึดตาม SRS (CompensationCalculation.RatePerDay/EligibleDays) ไม่ใช่ตาม mock · ถ้ากรมฯ ยืนยันสูตรผสมจริง → เพิ่มคอลัมน์ rate/หน่วย ใน CompensationItem ภายหลัง

ประเด็นที่ต้อง align กับลูกค้า (OPEN)¶

Tag	ประเด็น	สถานะ / แนวทางในเอกสารนี้
[OPEN-AUTH]	SRS S1 ระบุ Username/Password fallback + lockout 5 ครั้ง (NFR-S06/S08, REQ-005) แต่ design_p2 D8 = auth รวมศูนย์ที่ Keycloak (ไม่เก็บ credential ในแต่ละระบบ)	เอกสารนี้เลือก thin-mirror ไม่มี password/lockout เพื่อให้สอดคล้องทั้งชุด (เหมือน S3) — fallback ทำที่ Keycloak (รองรับ user/password native) · ถ้าลูกค้ายืนยันต้องเก็บ credential ใน LCS เอง → เพิ่มคอลัมน์ `PasswordHash`/`FailedLoginCount`/`LockedUntil` ใน `AppUser` + ตาราง `LoginAttempt`
[OPEN-DB]	SRS §5.3 ระบุ "ฐานข้อมูล LCS" แยก (`lcs-api.rlpd.go.th`) แต่ design_p2 D1 = Single DB `PSDBPRDDB` (ตรงกับ TOR risk "Single DB vs Micro Service")	เอกสารนี้ตามแนว design_p2 — schema namespace `LCS` ใน `PSDBPRDDB` · ถ้าลูกค้ายืนยัน DB แยกจริง → DDL เดิมใช้ได้ เพียงเปลี่ยน target DB (schema เดียวกัน)
[OPEN-S5]	mockup มีสถานะ "ส่ง S5 แล้ว" — แปลว่าค่าตอบแทนส่งต่อระบบ S5 (Lawyer Compensation)	รองรับด้วย `CompensationCalculation.Status='SENT_S5'` + `S5Reference` · interface กับ S5 = [TBD] (REQ-008 ครอบคลุมเฉพาะ OCIPA)
[TBD-003]	โครงสร้าง Template ไฟล์นำเข้าตารางเวร (.xlsx) — คอลัมน์ + validation rule	`ImportBatch`/`ImportError` รองรับแล้ว ; คอลัมน์มาตรฐานรอยืนยัน (SRS §5.2)
[TBD-008]	รายชื่อระบบปลายทาง Escalation (นอกจาก OCIPA)	`Escalation.TargetSystem` = varchar รองรับหลายปลายทาง
[TBD-012]	สถานะที่อนุญาตให้ประชาชนเห็นบน Service Center	`ConsultationRequest.PublicStatus` + `SystemConfig` (map สถานะภายใน→สาธารณะ)