Customer Database¶

// 4 top layer 1
// Onboarding
// Customer
// Transaction: combine of customer and asset
// Digital Asset: asset_id

// Layer 2
// Fullname
// Customer info: link to transaction, onboarding
// customer suite test answer: answer
// suite test id: question and answer

// Layer 3
// suite test question and answer
// Knowledge test


// Layer 1
// ----------------------
// Onboarding
Table onboarding {
  id int
  register_id uuid
  customer_info_id int [ref: - customer_info.id]
  idcard_id int [ref: - idcard.id]
  occupation_id int [ref: - occupation.id]
  register_address_id int [ref : - address.id]
  current_address_id int [ref : - address.id]
  work_address_id int [ref : - address.id]
  customer_suite_id int [ref: - customer_suite_submission.id]
  cdd_id int [ref: - cdd.id]
  bank_id int [ref: - bank.id]
  secondary_bank_id int [ref: - bank.id]
  source_of_fund_id int [ref: - source_of_fund.id]
  thaid_id int [ref: - thaid.id]
  ndid_id int [ref: - ndid.id]
  approval_id int [ref: - approval.id]
  appman_id int [ref: - appman.id]
  risk_score_id int [ref: - risk_score.id]
  has_done_knowledge_test bool
  steps int
}

// Customer
Table customer {
  id int
  customer_info_id int [ref: - customer_info.id]
  risk_score_id int [ref: - risk_score.id]
  idcard_id int [ref: - idcard.id]
  occupation_id int [ref: - occupation.id]
  register_address_id int [ref : - address.id]
  current_address_id int [ref : - address.id]
  work_address_id int [ref : - address.id]
  customer_suite_id int [ref: - customer_suite_submission.id]
  bank_id int [ref: - bank.id]
  secondary_bank_id int [ref: - bank.id]
  source_of_fund_id int [ref: - source_of_fund.id]

}

// Transaction
Table transaction {
  id uuid
  seller_info_id int [ref: - customer_info.id]
  buyer_info_id int [ref: - customer_info.id]
  price float
  amount_per_unit float
  unit float
}

// Digital Asset
Table digital_asset {
  id uuid
  customer_info_id int [ref: - customer_info.id]
  asset_detail_id int [ref: - asset_detail.id]

}

// ----------------------
// Layer 2
// ----------------------
Table customer_info {
  id int
  mobile string
  email string
  title_id int [ref: - title.id]
  first_name_th string
  middle_name_th string
  last_name_th string
  first_name_en string
  middle_name_en string
  last_name_en string
  customer_type enum
  ip_id int [ref: - ip_detail.id]
}

Table address {
  id int
  house_number string
  floor string
  village_building string
  sub_street strign
  street strign
  country_code string
  location_id  int
}

Table idcard {
  id int
  citizen_id  string
  laser_code string
  date_of_birth timestamp
  expire_date timestamp
  issue_date timestamp
}

Table occupation {
  id int
  code string
  position_name string
  source_of_investment string
  workplace_name string
  education_id string [ref: - education.id]
  occupation_id int [ref: - occupation.id]
  business_type_id int [ref: - business_type.id]
  income_range_id int [ref: - income_range.id]
  
}

Table customer_suite_answer {
  id int
  submission_id int [ref: > customer_suite_submission.id]
  question_id int [ref: > suitability_question.id]
  choice_id int [ref: > suitability_choice.id]
}

Table customer_suite_submission {
  id int
  customer_info_id int [ref: > customer_info.id]
  total_score int
  risk_level string
}


Table cdd {
  id int
  mule string
  freeze_04 string
  freeze_05 string
  pep bool
  pep_message string
  dopa bool
}

Table appman {
  id int
  verification_id string
  idcard_id int [ref: - appman_id_card.id]
  liveness_id int [ref: - appman_liveness.id]
  liveness_attribute_id int [ref: - appman_liveness_attributes.id]
  recognition int [ref: - appman_recognition.id]
}

Table bank { //
  id int
  name string
  branch string
  account_no string
}

Table source_of_fund { //
  id int
  source_of_fund string
  country_of_income string
  purpose_of_investment string
}

Table thaid {
  id int
  url string
  state string
  thaid_detail_id int [ref: - thaid_detail.id]
}

Table ndid {
  id int
  ndid_detail_id int [ref: - ndid_detail.id]
}

Table approval {
  id int
  by string
  name string
  level int
  message string
  is_approve bool
}

Table risk_score {
  id int
  score int
  risk_level int
}

Table asset_detail {
  id int
  name string
  price float
}

// ----------------------
// Layer 3
// ----------------------

Table suitability_question {
  id int
  question string
}

Table suitability_choice {
  id int 
  question_id int [ref: > suitability_question.id]
  answer string
  score int
}

Table appman_id_card {
  id int
  register_id string
  verification_id string
  id_number string
  address_th string
  full_name_th string
  full_name_en string
  serial_number string
  date_birth_en string
  date_birth_th string
  date_issue_en string
  date_issue_th string
  date_expire_en string
  date_expire_th string
  laser_code string
  updated_at timestamp
}

Table appman_liveness {
  id int [pk, increment]
  register_id string
  verification_id string
  verified bool
  valid bool
  completed bool
  success bool
  was_processed bool
  scan_result_blob text
  updated_at timestamp
}

Table appman_liveness_attributes {
  id int
  register_id string
  verification_id string
  yaw float
  roll float
  pitch float
  is_beard bool
  beard_confidence float
  is_smile bool
  smile_confidence float
  gender string
  gender_confidence float
  sharpness float
  brightness float
  age_low int
  age_high int
  is_eyes_open bool
  eyes_open_confidence float
  is_mustache bool
  mustache_confidence float
  is_mouth_open bool
  mouth_open_confidence float
  confidence float
  is_eyeglasses bool
  eyeglasses_confidence float
  is_sunglasses bool
  sunglasses_confidence float
}

Table appman_recognition {
  id int
  register_id string
  verification_id string
  yaw float
  roll float
  pitch float
  sharpness float
  brightness float
  top float
  left float
  width float
  height float
  similarity float
  pass_similarity bool
  confidence float
  verified bool
  valid bool
  completed bool
  source_key string
  target_key string
  source_url string
  target_url string
  updated_at timestamp
}
Table education {
  id int
  code string
  name string
}

Table business_type {
  id int
  name string
  risk_score int
}

Table income_range {
  id int
  range string
}

Table thaid_detail {
  id int
}

Table ndid_detail {
  id int
}

Table ip_detail {
  id int
  ip_address string
  country string
}

Table title {
  id int
  name_en string
  name_th string
}

Tree layer version:

.
├── Layer 1
│   ├── onboarding
│   │   ├── id: int
│   │   ├── register_id: uuid
│   │   ├── customer_info_id: int [ref: customer_info.id]
│   │   ├── idcard_id: int [ref: idcard.id]
│   │   ├── occupation_id: int [ref: occupation.id]
│   │   ├── register_address_id: int [ref: address.id]
│   │   ├── current_address_id: int [ref: address.id]
│   │   ├── work_address_id: int [ref: address.id]
│   │   ├── customer_suite_id: int [ref: customer_suite_submission.id]
│   │   ├── cdd_id: int [ref: cdd.id]
│   │   ├── bank_id: int [ref: bank.id]
│   │   ├── secondary_bank_id: int [ref: bank.id]
│   │   ├── source_of_fund_id: int [ref: source_of_fund.id]
│   │   ├── thaid_id: int [ref: thaid.id]
│   │   ├── ndid_id: int [ref: ndid.id]
│   │   ├── approval_id: int [ref: approval.id]
│   │   ├── appman_id: int [ref: appman.id]
│   │   ├── risk_score_id: int [ref: risk_score.id]
│   │   ├── has_done_knowledge_test: bool
│   │   └── steps: int
│   ├── customer
│   │   ├── id: int
│   │   ├── customer_info_id: int [ref: customer_info.id]
│   │   ├── risk_score_id: int [ref: risk_score.id]
│   │   ├── idcard_id: int [ref: idcard.id]
│   │   ├── occupation_id: int [ref: occupation.id]
│   │   ├── register_address_id: int [ref: address.id]
│   │   ├── current_address_id: int [ref: address.id]
│   │   ├── work_address_id: int [ref: address.id]
│   │   ├── customer_suite_id: int [ref: customer_suite_submission.id]
│   │   ├── bank_id: int [ref: bank.id]
│   │   ├── secondary_bank_id: int [ref: bank.id]
│   │   └── source_of_fund_id: int [ref: source_of_fund.id]
│   ├── transaction
│   │   ├── id: uuid
│   │   └── seller_info_id: int [ref: customer_info.id]
│   │   └── buyer_info_id: int [ref: - customer_info.id]
│   │   └── price: float
│   │   └── amount_per_unit: float
│   │   └── unit: float
│   └── digital_asset
│       ├── id: uuid
│       ├── customer_info_id: int [ref: customer_info.id]
│       └── asset_detail_id: int [ref: asset_detail.id]
├── Layer 2
│   ├── customer_info
│   │   ├── id: int
│   │   ├── mobile: string
│   │   ├── email: string
│   │   ├── title_id: int [ref: title.id]
│   │   ├── first_name_th: string
│   │   ├── middle_name_th: string
│   │   ├── last_name_th: string
│   │   ├── first_name_en: string
│   │   ├── middle_name_en: string
│   │   ├── last_name_en: string
│   │   ├── customer_type: enum
│   │   └── ip_id: int [ref: ip_detail.id]
│   ├── address
│   │   ├── id: int
│   │   ├── house_number: string
│   │   ├── floor: string
│   │   ├── village_building: string
│   │   ├── sub_street: string
│   │   ├── street: string
│   │   ├── country_code: string
│   │   └── location_id: int
│   ├── idcard
│   │   ├── id: int
│   │   ├── citizen_id: string
│   │   ├── laser_code: string
│   │   ├── date_of_birth: timestamp
│   │   ├── expire_date: timestamp
│   │   └── issue_date: timestamp
│   ├── occupation
│   │   ├── id: int
│   │   ├── code: string
│   │   ├── position_name: string
│   │   ├── source_of_investment: string
│   │   ├── workplace_name: string
│   │   ├── education_id: string [ref: education.id]
│   │   ├── occupation_id: int [ref: occupation.id]
│   │   ├── business_type_id: int [ref: business_type.id]
│   │   └── income_range_id: int [ref: income_range.id]
│   ├── customer_suite_answer
│   │   ├── id: int
│   │   ├── submission_id: int [ref: customer_suite_submission.id]
│   │   ├── question_id: int [ref: suitability_question.id]
│   │   └── choice_id: int [ref: suitability_choice.id]
│   ├── customer_suite_submission
│   │   ├── id: int
│   │   ├── customer_info_id: int [ref: customer_info.id]
│   │   ├── total_score: int
│   │   └── risk_level: string
│   ├── cdd
│   │   ├── id: int
│   │   ├── mule: string
│   │   ├── freeze_04: string
│   │   ├── freeze_05: string
│   │   ├── pep: bool
│   │   ├── pep_message: string
│   │   └── dopa: bool
│   ├── appman
│   │   ├── id: int
│   │   ├── verification_id: string
│   │   ├── idcard_id: int [ref: appman_id_card.id]
│   │   ├── liveness_id: int [ref: appman_liveness.id]
│   │   ├── liveness_attribute_id: int [ref: appman_liveness_attributes.id]
│   │   └── recognition: int [ref: appman_recognition.id]
│   ├── bank
│   │   ├── id: int
│   │   ├── name: string
│   │   ├── branch: string
│   │   └── account_no: string
│   ├── source_of_fund
│   │   ├── id: int
│   │   ├── source_of_fund: string
│   │   ├── country_of_income: string
│   │   └── purpose_of_investment: string
│   ├── thaid
│   │   ├── id: int
│   │   ├── url: string
│   │   ├── state: string
│   │   └── thaid_detail_id: int [ref: thaid_detail.id]
│   ├── ndid
│   │   ├── id: int
│   │   └── ndid_detail_id: int [ref: ndid_detail.id]
│   ├── approval
│   │   ├── id: int
│   │   ├── by: string
│   │   ├── name: string
│   │   ├── level: int
│   │   ├── message: string
│   │   └── is_approve: bool
│   ├── risk_score
│   │   ├── id: int
│   │   ├── score: int
│   │   └── risk_level: int
│   └── asset_detail
│       ├── id: int
│       ├── name: string
│       └── price: float
└── Layer 3
    ├── suitability_question
    │   ├── id: int
    │   └── question: string
    ├── suitability_choice
    │   ├── id: int
    │   ├── question_id: int [ref: suitability_question.id]
    │   ├── answer: string
    │   └── score: int
    ├── appman_id_card
    │   ├── id: int
    │   ├── register_id: string
    │   ├── verification_id: string
    │   ├── id_number: string
    │   ├── address_th: string
    │   ├── full_name_th: string
    │   ├── full_name_en: string
    │   ├── serial_number: string
    │   ├── date_birth_en: string
    │   ├── date_birth_th: string
    │   ├── date_issue_en: string
    │   ├── date_issue_th: string
    │   ├── date_expire_en: string
    │   ├── date_expire_th: string
    │   ├── laser_code: string
    │   └── updated_at: timestamp
    ├── appman_liveness
    │   ├── id: int [pk, increment]
    │   ├── register_id: string
    │   ├── verification_id: string
    │   ├── verified: bool
    │   ├── valid: bool
    │   ├── completed: bool
    │   ├── success: bool
    │   ├── was_processed: bool
    │   ├── scan_result_blob: text
    │   └── updated_at: timestamp
    ├── appman_liveness_attributes
    │   ├── id: int
    │   ├── register_id: string
    │   ├── verification_id: string
    │   ├── yaw: float
    │   ├── roll: float
    │   ├── pitch: float
    │   ├── is_beard: bool
    │   ├── beard_confidence: float
    │   ├── is_smile: bool
    │   ├── smile_confidence: float
    │   ├── gender: string
    │   ├── gender_confidence: float
    │   ├── sharpness: float
    │   ├── brightness: float
    │   ├── age_low: int
    │   ├── age_high: int
    │   ├── is_eyes_open: bool
    │   ├── eyes_open_confidence: float
    │   ├── is_mustache: bool
    │   ├── mustache_confidence: float
    │   ├── is_mouth_open: bool
    │   ├── mouth_open_confidence: float
    │   ├── confidence: float
    │   ├── is_eyeglasses: bool
    │   ├── eyeglasses_confidence: float
    │   ├── is_sunglasses: bool
    │   └── sunglasses_confidence: float
    ├── appman_recognition
    │   ├── id: int
    │   ├── register_id: string
    │   ├── verification_id: string
    │   ├── yaw: float
    │   ├── roll: float
    │   ├── pitch: float
    │   ├── sharpness: float
    │   ├── brightness: float
    │   ├── top: float
    │   ├── left: float
    │   ├── width: float
    │   ├── height: float
    │   ├── similarity: float
    │   ├── pass_similarity: bool
    │   ├── confidence: float
    │   ├── verified: bool
    │   ├── valid: bool
    │   ├── completed: bool
    │   ├── source_key: string
    │   ├── target_key: string
    │   ├── source_url: string
    │   ├── target_url: string
    │   └── updated_at: timestamp
    ├── education
    │   ├── id: int
    │   ├── code: string
    │   └── name: string
    ├── business_type
    │   ├── id: int
    │   ├── name: string
    │   └── risk_score: int
    ├── income_range
    │   ├── id: int
    │   └── range: string
    ├── thaid_detail
    │   └── id: int
    ├── ndid_detail
    │   └── id: int
    ├── ip_detail
    │   ├── id: int
    │   ├── ip_address: string
    │   └── country: string
    └── title
        ├── id: int
        ├── name_en: string
        └── name_th: string