<mxfile host="app.diagrams.net" modified="2026-08-18T10:00:00.000Z" agent="Antigravity" version="21.0.0" type="device">
  <diagram id="sales-margin-erd" name="Sales &amp; Margin Commission ERD with Security &amp; Audit">
    <mxGraphModel dx="2200" dy="1400" grid="1" gridSize="10" guides="1" tooltips="1" connect="1" arrows="1" fold="1" page="1" pageScale="1" pageWidth="2600" pageHeight="1800" background="#090d16" math="0" shadow="0">
      <root>
        <mxCell id="0" />
        <mxCell id="1" parent="0" />
        
        <!-- Domain Category Headers -->
        <mxCell id="header-auth" value="&lt;b&gt;1. AUTH, USERS &amp;amp; ROLE-BASED ACCESS CONTROL (RBAC)&lt;/b&gt;" style="rounded=1;fillColor=#831843;strokeColor=#ec4899;fontColor=#f472b6;fontSize=14;align=center;" vertex="1" parent="1">
          <mxGeometry x="40" y="30" width="800" height="35" as="geometry" />
        </mxCell>

        <mxCell id="header-audit" value="&lt;b&gt;2. SESSIONS, IP TRACKING &amp;amp; AUDIT LOGS&lt;/b&gt;" style="rounded=1;fillColor=#4c0519;strokeColor=#f43f5e;fontColor=#fb7185;fontSize=14;align=center;" vertex="1" parent="1">
          <mxGeometry x="870" y="30" width="860" height="35" as="geometry" />
        </mxCell>

        <mxCell id="header-master" value="&lt;b&gt;3. MASTER DATA &amp;amp; SALES TRANSACTIONS&lt;/b&gt;" style="rounded=1;fillColor=#1e1b4b;strokeColor=#6366f1;fontColor=#818cf8;fontSize=14;align=center;" vertex="1" parent="1">
          <mxGeometry x="40" y="460" width="1070" height="35" as="geometry" />
        </mxCell>

        <mxCell id="header-comm" value="&lt;b&gt;4. COMMISSION &amp;amp; APPROVAL WORKFLOW&lt;/b&gt;" style="rounded=1;fillColor=#451a03;strokeColor=#f59e0b;fontColor=#fbbf24;fontSize=14;align=center;" vertex="1" parent="1">
          <mxGeometry x="1140" y="460" width="780" height="35" as="geometry" />
        </mxCell>

        <!-- USER_ACCOUNT Table -->
        <mxCell id="t_user" value="&lt;b&gt;USER_ACCOUNT (บัญชีผู้ใช้)&lt;/b&gt;&lt;hr&gt;+ user_id : INT [PK]&lt;br&gt;# employee_id : INT [FK]&lt;br&gt;+ username : VARCHAR(50) [UK]&lt;br&gt;+ email : VARCHAR(100) [UK]&lt;br&gt;+ password_hash : VARCHAR(255)&lt;br&gt;+ password_salt : VARCHAR(100)&lt;br&gt;+ is_active : BOOLEAN&lt;br&gt;+ is_locked : BOOLEAN&lt;br&gt;+ failed_attempts : INT&lt;br&gt;+ last_login_at : DATETIME&lt;br&gt;+ created_at : DATETIME" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#ec4899;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="40" y="90" width="240" height="230" as="geometry" />
        </mxCell>

        <!-- ROLE Table -->
        <mxCell id="t_role" value="&lt;b&gt;ROLE (บทบาท)&lt;/b&gt;&lt;hr&gt;+ role_id : INT [PK]&lt;br&gt;+ role_code : VARCHAR(30) [UK]&lt;br&gt;+ role_name : VARCHAR(100)&lt;br&gt;+ description : VARCHAR(255)&lt;br&gt;+ is_active : BOOLEAN" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#ec4899;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="320" y="90" width="230" height="130" as="geometry" />
        </mxCell>

        <!-- USER_ROLE Table -->
        <mxCell id="t_user_role" value="&lt;b&gt;USER_ROLE (ผู้ใช้-บทบาท)&lt;/b&gt;&lt;hr&gt;# user_id : INT [PK,FK]&lt;br&gt;# role_id : INT [PK,FK]&lt;br&gt;+ assigned_at : DATETIME&lt;br&gt;# assigned_by : INT [FK]" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#ec4899;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="320" y="270" width="230" height="110" as="geometry" />
        </mxCell>

        <!-- PERMISSION Table -->
        <mxCell id="t_permission" value="&lt;b&gt;PERMISSION (สิทธิ์)&lt;/b&gt;&lt;hr&gt;+ permission_id : INT [PK]&lt;br&gt;+ permission_code : VARCHAR(50) [UK]&lt;br&gt;+ module : VARCHAR(50)&lt;br&gt;+ description : VARCHAR(255)" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#ec4899;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="590" y="90" width="250" height="110" as="geometry" />
        </mxCell>

        <!-- ROLE_PERMISSION Table -->
        <mxCell id="t_role_perm" value="&lt;b&gt;ROLE_PERMISSION (บทบาท-สิทธิ์)&lt;/b&gt;&lt;hr&gt;# role_id : INT [PK,FK]&lt;br&gt;# permission_id : INT [PK,FK]" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#ec4899;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="590" y="270" width="250" height="80" as="geometry" />
        </mxCell>

        <!-- USER_SESSION Table -->
        <mxCell id="t_session" value="&lt;b&gt;USER_SESSION (Session &amp;amp; IP)&lt;/b&gt;&lt;hr&gt;+ session_id : VARCHAR(64) [PK]&lt;br&gt;# user_id : INT [FK]&lt;br&gt;+ session_token : VARCHAR(255) [UK]&lt;br&gt;+ ip_address : VARCHAR(45)&lt;br&gt;+ user_agent : VARCHAR(500)&lt;br&gt;+ device_type : VARCHAR(50)&lt;br&gt;+ is_revoked : BOOLEAN&lt;br&gt;+ created_at : DATETIME&lt;br&gt;+ expires_at : DATETIME&lt;br&gt;+ last_activity_at : DATETIME" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#f43f5e;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="870" y="90" width="260" height="230" as="geometry" />
        </mxCell>

        <!-- LOGIN_HISTORY Table -->
        <mxCell id="t_login_hist" value="&lt;b&gt;LOGIN_HISTORY (IP Connection Log)&lt;/b&gt;&lt;hr&gt;+ login_id : BIGINT [PK]&lt;br&gt;# user_id : INT [FK]&lt;br&gt;+ attempted_username : VARCHAR(50)&lt;br&gt;+ ip_address : VARCHAR(45)&lt;br&gt;+ geo_location : VARCHAR(150)&lt;br&gt;+ user_agent : VARCHAR(500)&lt;br&gt;+ login_status : ENUM&lt;br&gt;+ failure_reason : VARCHAR(255)&lt;br&gt;+ attempted_at : DATETIME" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#f43f5e;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="1160" y="90" width="270" height="210" as="geometry" />
        </mxCell>

        <!-- SYSTEM_AUDIT_LOG Table -->
        <mxCell id="t_audit" value="&lt;b&gt;SYSTEM_AUDIT_LOG (Audit Trail)&lt;/b&gt;&lt;hr&gt;+ audit_id : BIGINT [PK]&lt;br&gt;# user_id : INT [FK]&lt;br&gt;# session_id : VARCHAR(64) [FK]&lt;br&gt;+ action_type : VARCHAR(50)&lt;br&gt;+ entity_name : VARCHAR(100)&lt;br&gt;+ record_id : VARCHAR(100)&lt;br&gt;+ old_values : JSON&lt;br&gt;+ new_values : JSON&lt;br&gt;+ ip_address : VARCHAR(45)&lt;br&gt;+ request_path : VARCHAR(255)&lt;br&gt;+ status_code : INT&lt;br&gt;+ created_at : DATETIME" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#f43f5e;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="1460" y="90" width="270" height="260" as="geometry" />
        </mxCell>

        <!-- BRANCH Table -->
        <mxCell id="t_branch" value="&lt;b&gt;BRANCH (10 สาขา)&lt;/b&gt;&lt;hr&gt;+ branch_id : INT [PK]&lt;br&gt;+ branch_code : VARCHAR(20) [UK]&lt;br&gt;+ branch_name : VARCHAR(100)&lt;br&gt;+ location : VARCHAR(255)&lt;br&gt;+ is_active : BOOLEAN" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#6366f1;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="40" y="520" width="220" height="130" as="geometry" />
        </mxCell>

        <!-- EMPLOYEE Table -->
        <mxCell id="t_employee" value="&lt;b&gt;EMPLOYEE (พนักงาน/ผู้จัดการ)&lt;/b&gt;&lt;hr&gt;+ employee_id : INT [PK]&lt;br&gt;# branch_id : INT [FK]&lt;br&gt;+ employee_code : VARCHAR(20) [UK]&lt;br&gt;+ full_name : VARCHAR(150)&lt;br&gt;+ email : VARCHAR(100)&lt;br&gt;+ role : ENUM&lt;br&gt;+ is_active : BOOLEAN" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#6366f1;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="40" y="700" width="220" height="160" as="geometry" />
        </mxCell>

        <!-- PRODUCT Table -->
        <mxCell id="t_product" value="&lt;b&gt;PRODUCT (สินค้า)&lt;/b&gt;&lt;hr&gt;+ product_id : INT [PK]&lt;br&gt;# category_id : INT [FK]&lt;br&gt;+ product_code : VARCHAR(50) [UK]&lt;br&gt;+ product_name : VARCHAR(200)&lt;br&gt;+ base_price : DECIMAL(12,2)&lt;br&gt;+ is_active : BOOLEAN" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#6366f1;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="290" y="520" width="230" height="150" as="geometry" />
        </mxCell>

        <!-- PRODUCT_STANDARD_COST Table -->
        <mxCell id="t_cost" value="&lt;b&gt;PRODUCT_STANDARD_COST&lt;/b&gt;&lt;hr&gt;+ cost_id : INT [PK]&lt;br&gt;# product_id : INT [FK]&lt;br&gt;+ standard_cost : DECIMAL(12,2)&lt;br&gt;+ effective_start_date : DATE&lt;br&gt;+ effective_end_date : DATE" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#6366f1;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="290" y="700" width="230" height="130" as="geometry" />
        </mxCell>

        <!-- SALES_IMPORT_BATCH -->
        <mxCell id="t_batch" value="&lt;b&gt;SALES_IMPORT_BATCH (Excel)&lt;/b&gt;&lt;hr&gt;+ batch_id : INT [PK]&lt;br&gt;# branch_id : INT [FK]&lt;br&gt;+ period_year : INT&lt;br&gt;+ period_month : INT&lt;br&gt;+ file_name : VARCHAR(255)&lt;br&gt;+ import_status : ENUM&lt;br&gt;+ uploaded_at : DATETIME" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#10b981;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="550" y="520" width="250" height="150" as="geometry" />
        </mxCell>

        <!-- SALES_TRANSACTION -->
        <mxCell id="t_transaction" value="&lt;b&gt;SALES_TRANSACTION (บิลขาย)&lt;/b&gt;&lt;hr&gt;+ transaction_id : INT [PK]&lt;br&gt;# batch_id : INT [FK]&lt;br&gt;# branch_id : INT [FK]&lt;br&gt;# employee_id : INT [FK]&lt;br&gt;+ invoice_no : VARCHAR(50)&lt;br&gt;+ transaction_date : DATE&lt;br&gt;+ total_amount : DECIMAL(14,2)&lt;br&gt;+ total_margin : DECIMAL(14,2)" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#10b981;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="550" y="700" width="250" height="170" as="geometry" />
        </mxCell>

        <!-- SALES_TRANSACTION_ITEM -->
        <mxCell id="t_item" value="&lt;b&gt;SALES_TRANSACTION_ITEM&lt;/b&gt;&lt;hr&gt;+ item_id : INT [PK]&lt;br&gt;# transaction_id : INT [FK]&lt;br&gt;# product_id : INT [FK]&lt;br&gt;+ quantity : INT&lt;br&gt;+ unit_price : DECIMAL(12,2)&lt;br&gt;+ standard_cost_per_unit : DECIMAL(12,2)&lt;br&gt;+ subtotal_margin : DECIMAL(14,2)" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#10b981;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="830" y="700" width="270" height="170" as="geometry" />
        </mxCell>

        <!-- MONTHLY_COMMISSION -->
        <mxCell id="t_commission" value="&lt;b&gt;MONTHLY_COMMISSION (อนุมัติ)&lt;/b&gt;&lt;hr&gt;+ commission_id : INT [PK]&lt;br&gt;# employee_id : INT [FK]&lt;br&gt;+ period_year : INT&lt;br&gt;+ period_month : INT&lt;br&gt;+ total_sales_amount : DECIMAL(14,2)&lt;br&gt;+ total_margin_amount : DECIMAL(14,2)&lt;br&gt;+ commission_rate_pct : DECIMAL(5,2)&lt;br&gt;+ calculated_commission : DECIMAL(14,2)&lt;br&gt;+ approval_status : ENUM&lt;br&gt;# approved_by : INT [FK]" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#f59e0b;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="1140" y="520" width="260" height="230" as="geometry" />
        </mxCell>

        <!-- COMMISSION_APPROVAL_LOG -->
        <mxCell id="t_comm_log" value="&lt;b&gt;COMMISSION_APPROVAL_LOG&lt;/b&gt;&lt;hr&gt;+ log_id : INT [PK]&lt;br&gt;# commission_id : INT [FK]&lt;br&gt;# action_by : INT [FK]&lt;br&gt;+ action : VARCHAR(50)&lt;br&gt;+ comments : TEXT&lt;br&gt;+ created_at : DATETIME" style="shape=table;startSize=26;container=1;collapsible=0;childLayout=tableLayout;fixedRows=1;rowLines=0;fontStyle=1;align=center;resizeLast=1;fillColor=#1e293b;strokeColor=#f59e0b;strokeWidth=2;fontColor=#ffffff;" vertex="1" parent="1">
          <mxGeometry x="1440" y="520" width="250" height="150" as="geometry" />
        </mxCell>

        <!-- Relationships -->
        <mxCell id="rel_user_emp" style="edgeStyle=orthogonalEdgeStyle;rounded=0;strokeColor=#ec4899;strokeWidth=2;" edge="1" parent="1" source="t_user" target="t_employee">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>

        <mxCell id="rel_user_session" style="edgeStyle=orthogonalEdgeStyle;rounded=0;strokeColor=#f43f5e;strokeWidth=2;" edge="1" parent="1" source="t_user" target="t_session">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>

        <mxCell id="rel_user_login" style="edgeStyle=orthogonalEdgeStyle;rounded=0;strokeColor=#f43f5e;strokeWidth=2;" edge="1" parent="1" source="t_user" target="t_login_hist">
          <mxGeometry relative="1" as="geometry">
            <Array as="points">
              <mxPoint x="160" y="60" />
              <mxPoint x="1290" y="60" />
            </Array>
          </mxGeometry>
        </mxCell>

        <mxCell id="rel_session_audit" style="edgeStyle=orthogonalEdgeStyle;rounded=0;strokeColor=#f43f5e;strokeWidth=2;" edge="1" parent="1" source="t_session" target="t_audit">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>

        <mxCell id="rel_user_userrole" style="edgeStyle=orthogonalEdgeStyle;rounded=0;strokeColor=#ec4899;strokeWidth=2;" edge="1" parent="1" source="t_user" target="t_user_role">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>

        <mxCell id="rel_role_userrole" style="edgeStyle=orthogonalEdgeStyle;rounded=0;strokeColor=#ec4899;strokeWidth=2;" edge="1" parent="1" source="t_role" target="t_user_role">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>

        <mxCell id="rel_role_roleperm" style="edgeStyle=orthogonalEdgeStyle;rounded=0;strokeColor=#ec4899;strokeWidth=2;" edge="1" parent="1" source="t_role" target="t_role_perm">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>

        <mxCell id="rel_perm_roleperm" style="edgeStyle=orthogonalEdgeStyle;rounded=0;strokeColor=#ec4899;strokeWidth=2;" edge="1" parent="1" source="t_permission" target="t_role_perm">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>

      </root>
    </mxGraphModel>
  </diagram>
</mxfile>
