Customer Database¶

// Customer database
// Layer 1
// Onboarding
// Customer

// Separate transaction database
// Transaction: use customer_code from customer_info and asset_id per trade

// Layer 2
// Fullname
// Customer info: link to onboarding and expose customer_code for transaction database
// 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 [pk, increment]
  onboarding_id string
  register_id uuid
  customer_info_id string [ref: - customer_info.customer_info_id]
  idcard_id string [ref: - idcard.idcard_id]
  occupation_id string [ref: - occupation.occupation_id]
  register_address_id string [ref : - address.address_id]
  current_address_id string [ref : - address.address_id]
  work_address_id string [ref : - address.address_id]
  customer_suite_id string [ref: - customer_suite_submission.customer_suite_submission_id]
  cdd_id string [ref: - cdd.cdd_id]
  bank_id string [ref: - bank.bank_id]
  secondary_bank_id string [ref: - bank.bank_id]
  source_of_fund_id string [ref: - source_of_fund.source_of_fund_id]
  thaid_id string [ref: - thaid.thaid_id]
  ndid_id string [ref: - ndid.ndid_id]
  approval_id string [ref: - approval.approval_id]
  appman_id string [ref: - appman.appman_id]
  risk_score_id string [ref: - risk_score.risk_score_id]
  has_done_knowledge_test bool
  steps int
}

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

}

// Layer 2
// ----------------------
Table customer_info {
  id int [pk, increment]
  customer_info_id string
  customer_code int
  mobile string
  email string
  title_id string [ref: - title.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 string [ref: - ip_detail.ip_detail_id]
}

Table address {
  id int [pk, increment]
  address_id string
  house_number string
  floor string
  village_building string
  sub_street strign
  street strign
  country_code string
  location_id  int
}

Table idcard {
  id int [pk, increment]
  idcard_id string
  citizen_id  string
  laser_code string
  date_of_birth timestamp
  expire_date timestamp
  issue_date timestamp
}

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

Table customer_suite_answer {
  id int [pk, increment]
  customer_suite_answer_id string
  submission_id string [ref: > customer_suite_submission.customer_suite_submission_id]
  question_id string [ref: > suitability_question.suitability_question_id]
  choice_id string [ref: > suitability_choice.suitability_choice_id]
}

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


Table cdd {
  id int [pk, increment]
  cdd_id string
  mule string
  freeze_04 string
  freeze_05 string
  pep bool
  pep_message string
  dopa bool
}

Table appman {
  id int [pk, increment]
  appman_id string
  verification_id string
  is_verified bool
  status string [enum]
  verified_at timestamp
  expire_at timestamp
  idcard_id string [ref: - appman_id_card.appman_id_card_id]
  liveness_id string [ref: - appman_liveness.appman_liveness_id]
  liveness_attribute_id string [ref: - appman_liveness_attributes.appman_liveness_attributes_id]
  recognition string [ref: - appman_recognition.appman_recognition_id]
}

Table bank { //
  id int [pk, increment]
  bank_id string
  name string
  branch string
  account_no string
}

Table source_of_fund { //
  id int [pk, increment]
  source_of_fund_id string
  source_of_fund string
  country_of_income string
  purpose_of_investment string
}

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

Table ndid {
  id int [pk, increment]
  ndid_id string
  ndid_detail_id string [ref: - ndid_detail.ndid_detail_id]
}

Table approval {
  id int [pk, increment]
  approval_id string
  by string
  name string
  level int
  message string
  is_approve bool
}

Table risk_score {
  id int [pk, increment]
  risk_score_id string
  score int
  risk_level int
}

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

Table suitability_question {
  id int [pk, increment]
  suitability_question_id string
  question string
}

Table suitability_choice {
  id int [pk, increment]
  suitability_choice_id string
  question_id string [ref: > suitability_question.suitability_question_id]
  answer string
  score int
}

Table appman_id_card {
  id int [pk, increment]
  appman_id_card_id string
  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]
  appman_liveness_id string
  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 [pk, increment]
  appman_liveness_attributes_id string
  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 [pk, increment]
  appman_recognition_id string
  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 [pk, increment]
  education_id string
  code string
  name string
}

Table business_type {
  id int [pk, increment]
  business_type_id string
  name string
  risk_score int
}

Table income_range {
  id int [pk, increment]
  income_range_id string
  range string
}

Table thaid_detail {
  id int [pk, increment]
  thaid_detail_id string
}

Table ndid_detail {
  id int [pk, increment]
  ndid_detail_id string
}

Table ip_detail {
  id int [pk, increment]
  ip_detail_id string
  ip_address string
  country string
}

Table title {
  id int [pk, increment]
  title_id string
  name_en string
  name_th string
}

// ----------------------

// Transaction database
// ----------------------
// Current asset holding = sum(buy unit) - sum(sell unit)
// grouped by customer_code and asset_id from completed transactions
// If old rows are moved to transaction_history, calculate from
// UNION ALL(transaction, transaction_history) or maintain a rebuildable balance projection
Table transaction {
  id int [pk, increment]
  transaction_id string
  asset_id string [ref: - digital_asset.asset_id]
  seller_customer_code int
  buyer_customer_code int
  status enum
  price float
  amount_per_unit float
  unit float
}

// Transaction Layer 2
// ----------------------
Table digital_asset {
  id int [pk, increment]
  asset_id string
  name string
  by string
  total_unit float
}

Tree layer version:

Customer
│ --- layer-1 ---
│ ├── onboarding
│ │   ├── id: int [pk, increment]
│ │   ├── onboarding_id: string
│ │   ├── register_id: uuid
│ │   ├── customer_info_id: string [ref: - customer_info.customer_info_id]
│ │   ├── idcard_id: string [ref: - idcard.idcard_id]
│ │   ├── occupation_id: string [ref: - occupation.occupation_id]
│ │   ├── register_address_id: string [ref: - address.address_id]
│ │   ├── current_address_id: string [ref: - address.address_id]
│ │   ├── work_address_id: string [ref: - address.address_id]
│ │   ├── customer_suite_id: string [ref: - customer_suite_submission.customer_suite_submission_id]
│ │   ├── cdd_id: string [ref: - cdd.cdd_id]
│ │   ├── bank_id: string [ref: - bank.bank_id]
│ │   ├── secondary_bank_id: string [ref: - bank.bank_id]
│ │   ├── source_of_fund_id: string [ref: - source_of_fund.source_of_fund_id]
│ │   ├── thaid_id: string [ref: - thaid.thaid_id]
│ │   ├── ndid_id: string [ref: - ndid.ndid_id]
│ │   ├── approval_id: string [ref: - approval.approval_id]
│ │   ├── appman_id: string [ref: - appman.appman_id]
│ │   ├── risk_score_id: string [ref: - risk_score.risk_score_id]
│ │   ├── has_done_knowledge_test: bool
│ │   └── steps: int
│ └── customer
│     ├── id: int [pk, increment]
│     ├── customer_id: string
│     ├── customer_info_id: string [ref: - customer_info.customer_info_id]
│     ├── risk_score_id: string [ref: - risk_score.risk_score_id]
│     ├── idcard_id: string [ref: - idcard.idcard_id]
│     ├── occupation_id: string [ref: - occupation.occupation_id]
│     ├── register_address_id: string [ref: - address.address_id]
│     ├── current_address_id: string [ref: - address.address_id]
│     ├── work_address_id: string [ref: - address.address_id]
│     ├── customer_suite_id: string [ref: - customer_suite_submission.customer_suite_submission_id]
│     ├── bank_id: string [ref: - bank.bank_id]
│     ├── secondary_bank_id: string [ref: - bank.bank_id]
│     └── source_of_fund_id: string [ref: - source_of_fund.source_of_fund_id]
│ --- layer-2 ---
│ ├── customer_info
│ │   ├── id: int [pk, increment]
│ │   ├── customer_info_id: string
│ │   ├── customer_code: int
│ │   ├── mobile: string
│ │   ├── email: string
│ │   ├── title_id: string [ref: - title.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: string [ref: - ip_detail.ip_detail_id]
│ ├── address
│ │   ├── id: int [pk, increment]
│ │   ├── address_id: string
│ │   ├── house_number: string
│ │   ├── floor: string
│ │   ├── village_building: string
│ │   ├── sub_street: strign
│ │   ├── street: strign
│ │   ├── country_code: string
│ │   └── location_id: int
│ ├── idcard
│ │   ├── id: int [pk, increment]
│ │   ├── idcard_id: string
│ │   ├── citizen_id: string
│ │   ├── laser_code: string
│ │   ├── date_of_birth: timestamp
│ │   ├── expire_date: timestamp
│ │   └── issue_date: timestamp
│ ├── occupation
│ │   ├── id: int [pk, increment]
│ │   ├── occupation_id: string
│ │   ├── code: string
│ │   ├── position_name: string
│ │   ├── source_of_investment: string
│ │   ├── workplace_name: string
│ │   ├── education_id: string [ref: - education.education_id]
│ │   ├── parent_occupation_id: string [ref: - occupation.occupation_id]
│ │   ├── business_type_id: string [ref: - business_type.business_type_id]
│ │   └── income_range_id: string [ref: - income_range.income_range_id]
│ ├── customer_suite_answer
│ │   ├── id: int [pk, increment]
│ │   ├── customer_suite_answer_id: string
│ │   ├── submission_id: string [ref: > customer_suite_submission.customer_suite_submission_id]
│ │   ├── question_id: string [ref: > suitability_question.suitability_question_id]
│ │   └── choice_id: string [ref: > suitability_choice.suitability_choice_id]
│ ├── customer_suite_submission
│ │   ├── id: int [pk, increment]
│ │   ├── customer_suite_submission_id: string
│ │   ├── customer_info_id: string [ref: > customer_info.customer_info_id]
│ │   ├── total_score: int
│ │   └── risk_level: string
│ ├── cdd
│ │   ├── id: int [pk, increment]
│ │   ├── cdd_id: string
│ │   ├── mule: string
│ │   ├── freeze_04: string
│ │   ├── freeze_05: string
│ │   ├── pep: bool
│ │   ├── pep_message: string
│ │   └── dopa: bool
│ ├── appman
│ │   ├── id: int [pk, increment]
│ │   ├── appman_id: string
│ │   ├── verification_id: string
│ │   ├── is_verified: bool
│ │   ├── status: string [enum]
│ │   ├── verified_at: timestamp
│ │   ├── expire_at: timestamp
│ │   ├── idcard_id: string [ref: - appman_id_card.appman_id_card_id]
│ │   ├── liveness_id: string [ref: - appman_liveness.appman_liveness_id]
│ │   ├── liveness_attribute_id: string [ref: - appman_liveness_attributes.appman_liveness_attributes_id]
│ │   └── recognition: string [ref: - appman_recognition.appman_recognition_id]
│ ├── bank
│ │   ├── id: int [pk, increment]
│ │   ├── bank_id: string
│ │   ├── name: string
│ │   ├── branch: string
│ │   └── account_no: string
│ ├── source_of_fund
│ │   ├── id: int [pk, increment]
│ │   ├── source_of_fund_id: string
│ │   ├── source_of_fund: string
│ │   ├── country_of_income: string
│ │   └── purpose_of_investment: string
│ ├── thaid
│ │   ├── id: int [pk, increment]
│ │   ├── thaid_id: string
│ │   ├── url: string
│ │   ├── state: string
│ │   └── thaid_detail_id: string [ref: - thaid_detail.thaid_detail_id]
│ ├── ndid
│ │   ├── id: int [pk, increment]
│ │   ├── ndid_id: string
│ │   └── ndid_detail_id: string [ref: - ndid_detail.ndid_detail_id]
│ ├── approval
│ │   ├── id: int [pk, increment]
│ │   ├── approval_id: string
│ │   ├── by: string
│ │   ├── name: string
│ │   ├── level: int
│ │   ├── message: string
│ │   └── is_approve: bool
│ └── risk_score
│     ├── id: int [pk, increment]
│     ├── risk_score_id: string
│     ├── score: int
│     └── risk_level: int
│ --- layer-3 ---
│ ├── suitability_question
│ │   ├── id: int [pk, increment]
│ │   ├── suitability_question_id: string
│ │   └── question: string
│ ├── suitability_choice
│ │   ├── id: int [pk, increment]
│ │   ├── suitability_choice_id: string
│ │   ├── question_id: string [ref: > suitability_question.suitability_question_id]
│ │   ├── answer: string
│ │   └── score: int
│ ├── appman_id_card
│ │   ├── id: int [pk, increment]
│ │   ├── appman_id_card_id: string
│ │   ├── 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]
│ │   ├── appman_liveness_id: string
│ │   ├── 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 [pk, increment]
│ │   ├── appman_liveness_attributes_id: string
│ │   ├── 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 [pk, increment]
│ │   ├── appman_recognition_id: string
│ │   ├── 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 [pk, increment]
│ │   ├── education_id: string
│ │   ├── code: string
│ │   └── name: string
│ ├── business_type
│ │   ├── id: int [pk, increment]
│ │   ├── business_type_id: string
│ │   ├── name: string
│ │   └── risk_score: int
│ ├── income_range
│ │   ├── id: int [pk, increment]
│ │   ├── income_range_id: string
│ │   └── range: string
│ ├── thaid_detail
│ │   ├── id: int [pk, increment]
│ │   └── thaid_detail_id: string
│ ├── ndid_detail
│ │   ├── id: int [pk, increment]
│ │   └── ndid_detail_id: string
│ ├── ip_detail
│ │   ├── id: int [pk, increment]
│ │   ├── ip_detail_id: string
│ │   ├── ip_address: string
│ │   └── country: string
└─└── title
    ├── id: int [pk, increment]
    ├── title_id: string
    ├── name_en: string
    └── name_th: string

Transaction
│ --- layer-1 ---
│ └── transaction
│     ├── id: int [pk, increment]
│     ├── transaction_id: string
│     ├── asset_id: string [ref: - digital_asset.asset_id]
│     ├── seller_customer_code: int
│     ├── buyer_customer_code: int
│     ├── status: enum
│     ├── price: float
│     ├── amount_per_unit: float
│     └── unit: float
│ --- layer-2 ---
├── digital_asset
│   ├── id: int [pk, increment]
│   ├── asset_id: string
│   ├── name: string
│   ├── by: string
│   └── total_unit: float