Onboarding DB Schema

Database ของ User Service (PostgreSQL) — ครอบคลุม 15 table ที่เกี่ยวกับ Onboarding flow ตั้งแต่ config ไปถึง runtime, audit, session, invitation

Sections:

1. ERD — ความสัมพันธ์หลัก (อ่านก่อนเพื่อ mental model)
2. กลุ่มตาราง — จัดกลุ่มตามหน้าที่ (overview)
3. รายละเอียดแต่ละ Table — ทุก column พร้อมคำอธิบาย (property reference)
4. Status State Machine — สถานะ FlowInstance และ transition
5. Indexes Summary — index ที่มีอยู่
6. FK Cascade & Delete Order — ลำดับลบที่ปลอดภัย
7. Notable Behaviors — behavior พิเศษที่ต้องรู้
8. Flow Simulation (NEW_REQUESTOR) — ไล่ DB write ทีละ step (อ่านเพื่อเห็นภาพรวม)

ERD (Main Relationships)

erDiagram
    StepCatalog {
        uuid    Id              PK
        string  StepTypeCode    UK
        string  DisplayName
        string  ExecutionMode   "Interactive / Automatic"
        bool    LockedAfterFirstExecution
        bool    SensitiveInReview
        bool    ReworkAllowed   "DB default true"
        int     MaxRetries
        string  DependenciesJson "jsonb"
    }

    FlowDefinition {
        uuid    Id              PK
        string  Code            UK "e.g. STANDARD"
        string  Name
        uuid    ServiceId       "cross-context Codex, no FK"
        string  TemplateKey
        string  FrontendBaseUrl
        int     InvitationTtlDays
        int     SessionTtlSeconds
        bool    IsActive
        bool    AllowSelfStart
        string  RefNoIssueAt    "OnEnterDraft / OnEnterSubmitted"
        string  DocumentConfigCode
        int     StaleDaysBeforeAbandon
        string  SpawnsCustomerFlowCode
        string  RequestorRoleCode
        string  AuthRequirement "Any / RequiresLogin / RequiresAnonymous"
        string  LoggedInRedirectFlowCode
        string  OnExistingAccountPolicy "Allow / RequireLogin"
    }

    FlowStep {
        uuid    Id              PK
        uuid    FlowDefinitionId FK
        uuid    StepCatalogId   FK
        string  StepType        "denormalized"
        int     Order           "0-based"
        bool    CanGoBack
    }

    FlowInstance {
        uuid    Id              PK
        uuid    FlowDefinitionId "logical ref, no FK constraint"
        string  FlowSnapshotJson "jsonb snapshot at creation"
        string  Status          "Draft/Submitted/Resubmitted/InReview/Reviewed/InApprove/Approved/Rejected/Rework/Finalized/Abandoned/Cancelled"
        uuid    OwnerUserId     "nullable, cross-context User.Id"
        uuid    CompanyId       "nullable, cross-context"
        uuid    ServiceId       "nullable, cross-context"
        string  RefNo
        bool    RefNoPending
        int     CurrentStepIndex
        bool    Archived
        string  StuckAtStepType
        string  CancelledBy     "Owner / Admin"
        string  CancelReason
        string  CompletedLockedStepsJson "jsonb"
        string  StepAttemptsJson "jsonb"
    }

    FlowInstanceStepData {
        uuid    FlowInstanceId  PK "composite PK; FK via relationship"
        string  StepType        PK "composite PK"
        string  DataJson        "jsonb"
        bool    IsSkipped       "true = copy from UserStepRecord"
        datetime CompletedAt
    }

    FlowInstanceHistory {
        uuid    Id              PK
        uuid    FlowInstanceId  FK
        int     Sequence        UK "monotonic per instance"
        string  FromStatus
        string  ToStatus
        string  Action          "Submit / Resubmit / Approve / Reject / Rework / Finalize"
        string  ActorType       "Owner / Admin / System"
        uuid    ActorId
        string  ActorName
        string  Reason
        string  ReworkContextJson "jsonb"
        datetime OccurredAt
        int     SubmissionNo
        bool    IsSubmissionRow
        string  RefNo           "scalar copy"
        uuid    CompanyId       "scalar copy"
        uuid    OwnerUserId     "scalar copy"
        int     CurrentStepIndex "scalar copy"
    }

    FlowInstanceHistoryStep {
        uuid    Id              PK
        uuid    FlowInstanceHistoryId FK
        string  StepType        UK "composite with HistoryId"
        int     StepOrder
        string  DataJson        "jsonb frozen"
        bool    IsSensitive
        bool    IsSkipped
        datetime CompletedAt
    }

    FlowInvestigationEvent {
        uuid    Id              PK
        uuid    FlowInstanceId  FK
        string  EventType       "STEP_STUCK / STEP_RETRIED / STEP_FAILED"
        string  StepType
        string  ErrorCode
        string  Detail          "no PII"
        int     AttemptNo
        string  CorrelationId
        datetime CreatedAt
    }

    CorrectionRequest {
        uuid    Id              PK
        uuid    FlowInstanceId  FK
        uuid    ApprovalDecisionId UK
        datetime RequestedAt
        string  RequestedFromStatus
        uuid    RequestedByEmployeeId
        string  TargetStepsJson "jsonb"
        string  DecidedByContextJson "jsonb"
    }

    EditSession {
        uuid    Id              PK
        string  SessionId       UK "__Host-onboarding cookie"
        uuid    FlowInstanceId  FK
        uuid    UserId          "nullable"
        string  Email
        bool    IsActive
        datetime ExpiresAt
    }

    InvitationToken {
        string  Id              PK "opaque random token"
        string  Email
        string  FlowCode
        string  Status          "PENDING / CONSUMED / REVOKED / EXPIRED"
        uuid    CreatedByAdminId
        datetime ExpiresAt
        datetime ConsumedAt
        uuid    CompanyId
        uuid    OriginatingFlowInstanceId FK "nullable"
        uuid    ConsumedByFlowInstanceId  "nullable FK to FlowInstance"
    }

    CompanyRequestor {
        uuid    Id              PK
        uuid    CompanyId       UK "1 company = 1 requestor"
        uuid    RequestorUserId "cross-context User.Id"
        datetime CreatedAt
    }

    UserStepRecord {
        uuid    Id              PK
        string  Email           "linked by Email, no FK to FlowInstance"
        string  StepType
        datetime CompletedAt
        string  DataJson        "jsonb"
        uuid    CompanyId
        uuid    ServiceId
    }

    OnboardingSession {
        uuid    Id              PK
        string  Email           "linked by Email"
        string  EntraObjectId
        string  FlowSnapshotJson "legacy v1"
        int     CurrentStepIndex "legacy v1"
        bool    IsActive
        datetime ExpiresAt
    }

    ServiceEnrollment {
        uuid    Id              PK
        uuid    UserId          "cross-context User.Id"
        uuid    ServiceId       "cross-context, no FK (Service table dropped)"
        datetime EnrolledAt
    }

    StepCatalog          ||--o{ FlowStep                : "defines steps"
    FlowDefinition       ||--o{ FlowStep                : "contains"
    FlowInstance         ||--o{ FlowInstanceStepData    : "stores"
    FlowInstance         ||--o{ FlowInstanceHistory     : "records"
    FlowInstance         ||--o{ FlowInvestigationEvent  : "logs"
    FlowInstance         ||--o{ CorrectionRequest       : "receives"
    FlowInstance         ||--o{ EditSession             : "cursored by"
    FlowInstanceHistory  ||--o{ FlowInstanceHistoryStep : "freezes (CASCADE)"
    FlowInstance         |o--o{ InvitationToken         : "originates (nullable)"

กลุ่มตาราง

ส่วนนี้ = overview/mental model — จัดกลุ่มตามหน้าที่เพื่อเข้าใจภาพรวม ดูรายละเอียด column แต่ละ table ที่ section ถัดไป (รายละเอียดแต่ละ Table)

Group 1 — Config / Seed (อย่าลบ)

Key rules:

• FlowStep.StepType denormalized จาก StepCatalog.StepTypeCode — ใช้ join performance
• FlowDefinition.ServiceId เป็น cross-context Guid ไปหา Codex Service — ไม่มี FK constraint (dropped in 2026-05-28)
• StepCatalog.DependenciesJson กำหนด Required/Optional dependency ระหว่าง step

Group 2 — Runtime Execution

FlowInstance (Main Aggregate)

1 row = 1 การ execute ของ FlowDefinition จาก user

Indexes:

• OwnerUserId
• Status
• RefNo
• (CompanyId, ServiceId, Status) — composite สำหรับ admin list
• RefNoPending — background retry polling

Cross-context refs (plain Guid ไม่มี FK):

• OwnerUserId → User.Id
• CompanyId → Company.Id
• ServiceId → Codex Service

Active Slot (IsActiveSlot()) — status ที่กิน uniqueness slot:

Draft | Submitted | Resubmitted | InReview | Reviewed | InApprove | Approved | Rework | Finalized

Terminal = Rejected | Abandoned | Cancelled (ไม่กิน slot → start ใหม่ได้)

FlowInstanceStepData

Composite PK (FlowInstanceId, StepType) — 1 row ต่อ step ที่ execute แล้ว

• IsSkipped = true → data copy มาจาก UserStepRecord (D-V2-39 copy-on-skip)
• Handler อ่านผลของ step อื่นด้วย StepType ไม่ใช่ index

FlowInvestigationEvent

Append-only diagnostics — ตอบ “อะไรพัง ตรงไหน” สำหรับ dev/ops
ห้าม PII — ไม่มี id-card number / personal info

Group 3 — Audit History

FlowInstanceHistory

Append-only timeline — 1 row ต่อ 1 status change

• Sequence monotonic ต่อ instance (unique constraint)
• Scalar copy ของ RefNo, CompanyId, OwnerUserId ณ เวลานั้น → อ่าน card โดยไม่ต้อง join

FlowInstanceHistoryStep

Frozen snapshot ของ step data ณ ตอน submit — CASCADE ลบตาม History

• เฉพาะ submission row (Draft→Submitted, Rework→Submitted)
• IsSensitive = true → API ไม่เคย return DataJson ออก

Group 4 — Correction / Rework

CorrectionRequest

Append-only — 1 row ต่อครั้งที่ Admin ขอให้ Requestor แก้

• ApprovalDecisionId UK — idempotency (กัน duplicate จาก ASB retry)
• TargetStepsJson: [{stepType, note, autoIncluded}] — step ที่ต้องแก้ + per-step note

Group 5 — Sessions

EditSession:

• SessionId = __Host-onboarding cookie value (UK)
• ตาย TTL → FlowInstance ยังอยู่ → เปิด EditSession ใหม่ resume ได้
• anonymous phase: UserId = null, Email = ""; OTP complete → bind owner

UserStepRecord:

• key: Email + StepType (+ optional CompanyId, ServiceId สำหรับ per-company scope)
• FlowEngine อ่านจากนี้ตอน skip step ที่เคยทำในรอบก่อน

Group 6 — Invitations

InvitationToken

Token = opaque random string (ไม่ใช่ JWT) ส่งไปใน email link ?code=<token>

• OriginatingFlowInstanceId — FlowInstance ของ Requestor ที่สร้าง token
• ConsumedByFlowInstanceId — FlowInstance ของ Customer ที่ใช้ token
• ทั้งสอง FK เป็น nullable → ไม่บล็อกลบ FlowInstance

Group 7 — Identity Bindings

CompanyRequestor

1 Company = 1 Requestor วันนี้ (UK บน CompanyId)

• ถ้าต้องการ M
  ในอนาคต: drop UK → เพิ่ม UK(CompanyId, RequestorUserId)
• RequestorUserId → User.Id (cross-context, ไม่มี FK)

ServiceEnrollment

User enrolled ใน Service ใด

• ServiceId = cross-context Codex ServiceId — FK constraint ถูก drop (migration 2026-05-28 removed Services table)
• Table ยังอยู่แต่ไม่มี FK

รายละเอียดแต่ละ Table

ส่วนนี้ = property reference — ทุก column พร้อมคำอธิบายและ type สำหรับค้นหาขณะเขียนโค้ด ภาพรวมดูที่ section กลุ่มตาราง ด้านบน

Config / Seed Tables

StepCatalog — “เมนูกลาง” ของ step type ทั้งหมดที่ระบบรู้จัก — Admin เพิ่มที่นี่ก่อน ถึงจะใช้ใน FlowDefinition ได้

ข้อมูลส่วนใหญ่ใส่ตั้งแต่ตอน migration (Seed data) ไม่ค่อยเปลี่ยน ปัจจุบันมี 3 step type: ConsentStep, NdidStep, PersonalInfoStep

FlowDefinition — template ของ onboarding flow บอกว่ามี step อะไร เรียงยังไง และมีกฎอะไรบ้าง

เช่น flow STANDARD มี 3 step: Consent → NDID → PersonalInfo, AllowSelfStart = true (user เริ่มเองได้) Admin แก้ได้ผ่าน Admin UI แต่ instance ที่สร้างไปแล้วไม่กระทบ เพราะระบบ snapshot definition ไว้ใน FlowInstance ตั้งแต่ตอนสร้าง

FlowStep — รายการ step แต่ละตัวใน FlowDefinition เรียงตาม Order

เมื่อสร้าง FlowInstance ระบบดึง FlowStep เหล่านี้มา snapshot ไว้ใน FlowSnapshotJson เพื่อล็อกลำดับ ณ เวลานั้น

Runtime Execution Tables

FlowInstance — table หลัก 1 row = user 1 คน เริ่ม onboarding 1 ครั้ง

Status วิ่งตาม State Machine: Draft → Submitted → InReview → Reviewed → InApprove → Approved/Rejected/Rework → Finalized เก็บทุกอย่างที่ต้องรู้เกี่ยวกับ session การ onboard: อยู่ที่ step ไหน, status ปัจจุบัน, เลขใบสมัคร

FlowInstanceStepData — ข้อมูลที่ user กรอกในแต่ละ step บันทึกเป็น JSON ต่อ step

Composite PK (FlowInstanceId, StepType) — 1 step = 1 row ต่อ instance ถ้า step ถูก skip ระบบ copy ข้อมูลจาก UserStepRecord มาใส่ (IsSkipped = true) user ไม่ต้องกรอกใหม่

Audit History Tables

FlowInstanceHistory — timeline การเปลี่ยน status ทุกครั้ง append-only ไม่เคยลบหรือแก้

1 row = 1 status change เช่น Draft→Submitted, InApprove→Approved เก็บ scalar copy ของ RefNo, CompanyId, OwnerUserId, CurrentStepIndex ไว้ในแต่ละ row — ทำให้ render audit card ได้โดยไม่ต้อง join FlowInstance

FlowInstanceHistoryStep — snapshot ของ step data ณ ตอนที่ user กด Submit “freeze” ค่าไว้ตลอดกาล

มีเฉพาะใน submission rows (IsSubmissionRow = true) เช่น Draft→Submitted หรือ Rework→Submitted ใช้แสดงว่า submit รอบที่ N user กรอกอะไรไว้ — ถ้า IsSensitive = true (เช่น NdidStep) API ซ่อน DataJson

Diagnostics

FlowInvestigationEvent — log ทุกครั้งที่ step ติด, retry, หรือ failed — append-only, ไม่มี PII

Correction / Rework

CorrectionRequest — บันทึกทุกครั้งที่ Approver ขอให้ Requestor กลับไปแก้ข้อมูลในบาง step

1 row = 1 ครั้งที่ขอแก้ append-only ไม่ลบ ApprovalDecisionId (Unique) ป้องกัน duplicate กรณี message ถูกส่งซ้ำจาก message broker (idempotency)

Sessions

EditSession — ผูก browser session (cookie) เข้ากับ FlowInstance ที่กำลังทำอยู่

SessionId = ค่าใน __Host-onboarding cookie ที่ browser ถือ anonymous phase: UserId = null, Email = ""; หลัง OTP สำเร็จ = bind user เข้า session แม้ cookie หมดอายุ FlowInstance ยังอยู่ — user กลับมา browser ใหม่ สร้าง EditSession ใหม่ resume ต่อจากเดิมได้

OnboardingSession — session v1 เดิม (ก่อน refactor มาเป็น EditSession) ไม่ใช้แล้ว

table ยังอยู่ใน DB แต่ code ใหม่ไม่ write ลงมาที่นี่แล้ว ไม่ต้องสนใจในการ implement ใหม่

UserStepRecord — cache ว่า email นี้เคยทำ step อะไรเสร็จแล้วบ้าง (ข้ามรอบ)

พอ user ต้อง onboard อีกครั้ง (เช่น ถูก reject แล้ว resubmit หรือเปิด service ใหม่) ระบบดึง record นี้มา auto-fill ใน FlowInstanceStepData แทนที่จะต้องกรอกใหม่ key: Email + StepType บวก optional CompanyId / ServiceId สำหรับ scope ต่อบริษัท

Invitations

InvitationToken — token ที่ Admin สร้างและส่งให้ Customer ทาง email เป็น random string ธรรมดา (ไม่ใช่ JWT)

เมื่อ Customer กดลิงก์ในอีเมล (?code=<token>) ระบบ validate token นี้แล้วเริ่ม onboarding ให้อัตโนมัติ มี 2 nullable FK กลับ FlowInstance: ของ Requestor ที่สร้าง (OriginatingFlowInstanceId) และของ Customer ที่ใช้ (ConsumedByFlowInstanceId)

Identity Bindings

CompanyRequestor — บันทึกว่าบริษัทนี้มี Requestor (คนยื่นเรื่องแทนบริษัท) คือใคร

UK บน CompanyId enforce 1 บริษัท = 1 Requestor เขียนตอน Finalize ของ Requestor flow สำเร็จ

ServiceEnrollment — บันทึกว่า user enroll ใน service ใดแล้ว

ServiceId = cross-context Codex ServiceId — ไม่มี FK (Services table ถูก drop ใน migration 2026-05-28)

Status State Machine

                 ┌──────────────────────────────┐
                 │           DRAFT              │ ← สร้างใหม่
                 └──────────────┬───────────────┘
                                │ Submit
                                ▼
                 ┌──────────────────────────────┐
                 │         SUBMITTED            │
                 └──────────────┬───────────────┘
                                │ (Orchestrator picks up)
                    ┌───────────┴──────────────┐
                    ▼                          ▼
           ┌─────────────┐           ┌────────────────┐
           │  IN_REVIEW  │──Reviewed▶│   REVIEWED     │
           └─────────────┘           └───────┬────────┘
                                             │
                                     ┌───────▼────────┐
                                     │  IN_APPROVE    │
                                     └───────┬────────┘
                          ┌──────────────────┼──────────────────┐
                          ▼                  ▼                  ▼
                   ┌────────────┐    ┌─────────────┐   ┌──────────────┐
                   │  APPROVED  │    │  REJECTED   │   │    REWORK    │
                   └─────┬──────┘    └──────────── ┘   └──────┬───────┘
                         │ (Finalize saga)                     │ Resubmit
                         ▼                                     ▼
                  ┌─────────────┐                    ┌───────────────────┐
                  │  FINALIZED  │                    │   RESUBMITTED     │──▶ (back to IN_REVIEW)
                  └─────────────┘                    └───────────────────┘

Terminal states (ไม่กิน active slot):
  REJECTED | ABANDONED | CANCELLED

Abandon: ทุก non-terminal state → ABANDONED (OwnerUserId / Admin)
Cancel:  non-terminal state → CANCELLED (Owner self / Admin explicit)

Indexes Summary

FK Cascade & Delete Order

FlowInstanceHistoryStep เป็นตารางเดียวที่มี ON DELETE CASCADE ชัดเจน (ผ่าน EF HasMany…OnDelete(Cascade))

สำหรับ cleanup script ให้ลบตามลำดับนี้:

1. FlowInstanceHistoryStep   (grandchild, FK → FlowInstanceHistory)
2. FlowInstanceHistory       (FK → FlowInstance)
3. FlowInstanceStepData      (FK → FlowInstance)
4. CorrectionRequest         (FK → FlowInstance)
5. FlowInvestigationEvent    (FK → FlowInstance)
6. EditSession               (FK → FlowInstance)
7. InvitationToken           (nullable FKs → nullify หรือ delete)
8. FlowInstance              (parent)
── per-user cleanup ──
9. CompanyRequestor          (by RequestorUserId)
10. UserStepRecord           (by Email)
11. OnboardingSession        (by Email, legacy)

ไม่แตะ: FlowDefinition, FlowStep, StepCatalog (config/seed)

Notable Behaviors

Flow Simulation — NEW_REQUESTOR Onboarding (Happy Path)

ติดตามข้อมูลใน DB จากต้นจนจบ — ผู้สมัครใหม่ somchai@example.com (ไม่มี account เดิม, ไม่มี JWT) ทำ NEW_REQUESTOR flow (OTP → JuristicBinding → … → FxRequestorTcConsent) จน Finalized

NEW_REQUESTOR step sequence เป็น ops-seeded (ไม่อยู่ใน EF HasData) — Order ขึ้นอยู่กับ DB ที่ seed ไว้

Placeholders: <A> = FlowInstance Id, <UserId> = UserId ที่สร้างจาก OTP step, <CompanyId> = CompanyId หลัง Approve

1 — Anonymous entry (StartRequestorFlow)

ผู้ใช้ไม่มี JWT — ส่งแค่ email มาใน body. Handler สร้าง FlowInstance แบบ owner-less + EditSession cookie สำหรับ anonymous session

FlowInstanceHistory ยังไม่มี — Draft ไม่ fire status change event

2 — OtpVerificationStep: user กรอก email (ExecuteStepAction / SaveDraft)

OTP code ส่งออกผ่าน NS email (ไม่เขียน DB) — NS stamp pending hash ลง Redis

3 — OtpVerificationStep: user ยืนยัน OTP (ExecuteStepAction / Next)

Entra identity ถูก deactivate (accountEnabled=false) — จะ activate อีกครั้งที่ Finalize (ADR-0034)

4 — Business steps: JuristicBinding → SignatoryForm (ExecuteStepAction / Next ×6)

Steps ที่เหลือก่อน Submit ทำ pattern เดียวกัน — แสดงรวมเป็น 1 กลุ่ม

5 — FxRequestorTcConsentStep: กด Next = Submit (ExecuteStepAction / Submit)

Step สุดท้ายของ interactive flow — action Next บน step นี้ถูก engine map เป็น Submit path (FxRequestorTcConsentStep รวม T&C consent + dependency check ก่อน submit)

FlowInstanceHistory + FlowInstanceHistoryStep เขียนโดย domain event listener ณ SaveChanges (atomic กับ status flip)

6 — ApplyFlowProgress: InReview → Reviewed → InApprove (3 hops)

7 — Approve → Finalize อัตโนมัติ (TransitionFlowInstance[Approve])

Approve + Finalize fire domain event ใน SaveChanges เดียวกัน → 2 History rows พร้อมกัน

สรุปสุดท้าย — สิ่งที่อยู่ใน DB หลัง Happy Path เสร็จ

ServiceEnrollment ไม่มีใน rows ด้านบน — เขียนแยกผ่าน Orchestrator async provisioning

Rework Path — ต่างจาก Happy Path อย่างไร

เมื่อ Approver ขอแก้แทนที่จะ Approve (TransitionFlowInstance[Rework]):

เมื่อ User กด Submit อีกครั้ง (Resubmit) หลังแก้ข้อมูล: