Thai Token Exchange

# 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

```