🧾 ERP Schema V2 – Full DDL (Documentation Only)

DDL di halaman ini adalah dokumentasi, bukan migration resmi. Selalu gunakan sebagai referensi saat menyusun migration Atlas HCL / SQL, dan review sebelum eksekusi di environment manapun.

0. Konvensi Umum

Dialek: MySQL (InnoDB, utf8mb4).
Semua tabel menggunakan PK BIGINT UNSIGNED AUTO_INCREMENT kecuali dinyatakan lain.
Index & FK bisa di-adjust sesuai kebutuhan performa produksi.
Penamaan tabel mengikuti prefix modul: core_, fi_, gl_, inv_, po_, so_, hr_, crm_, cs_, au_, an_, ic_, rg_.

1. Core Module (`core_*`)

CREATE TABLE core_tenant (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  is_active   TINYINT(1)      NOT NULL DEFAULT 1,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_core_tenant_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE core_company (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_core_company_tenant_code (tenant_id, code),
  KEY idx_core_company_tenant (tenant_id),
  CONSTRAINT fk_core_company_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenant (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE core_branch (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  branch_type VARCHAR(32)     NOT NULL DEFAULT 'STORE',
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_core_branch_company_code (company_id, code),
  KEY idx_core_branch_company (company_id),
  CONSTRAINT fk_core_branch_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE core_user (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  email       VARCHAR(128)    NOT NULL,
  password    VARCHAR(255)    NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  is_active   TINYINT(1)      NOT NULL DEFAULT 1,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_core_user_email (email),
  KEY idx_core_user_tenant (tenant_id),
  CONSTRAINT fk_core_user_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenant (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE core_role (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_core_role_tenant_code (tenant_id, code),
  KEY idx_core_role_tenant (tenant_id),
  CONSTRAINT fk_core_role_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenant (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE core_permission (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  description TEXT            NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_core_perm_tenant_code (tenant_id, code),
  KEY idx_core_perm_tenant (tenant_id),
  CONSTRAINT fk_core_perm_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenant (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE core_role_perm (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_id   BIGINT UNSIGNED NOT NULL,
  perm_id   BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_core_role_perm (role_id, perm_id),
  KEY idx_core_role_perm_perm (perm_id),
  CONSTRAINT fk_core_role_perm_role
    FOREIGN KEY (role_id) REFERENCES core_role (id),
  CONSTRAINT fk_core_role_perm_perm
    FOREIGN KEY (perm_id) REFERENCES core_permission (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE core_user_role (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id   BIGINT UNSIGNED NOT NULL,
  role_id   BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_core_user_role (user_id, role_id),
  KEY idx_core_user_role_role (role_id),
  CONSTRAINT fk_core_user_role_user
    FOREIGN KEY (user_id) REFERENCES core_user (id),
  CONSTRAINT fk_core_user_role_role
    FOREIGN KEY (role_id) REFERENCES core_role (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE core_config (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  company_id  BIGINT UNSIGNED NULL,
  k           VARCHAR(128)    NOT NULL,
  v           TEXT            NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_core_config_scope (tenant_id, company_id, k),
  KEY idx_core_config_tenant (tenant_id),
  KEY idx_core_config_company (company_id),
  CONSTRAINT fk_core_config_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenant (id),
  CONSTRAINT fk_core_config_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE core_notif (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NOT NULL,
  title       VARCHAR(255)    NOT NULL,
  body        TEXT            NOT NULL,
  is_read     TINYINT(1)      NOT NULL DEFAULT 0,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_core_notif_user (user_id),
  CONSTRAINT fk_core_notif_user
    FOREIGN KEY (user_id) REFERENCES core_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Finance Module (`fi_*`)

CREATE TABLE fi_tax (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(32)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  rate        DECIMAL(9,4)    NOT NULL,
  is_active   TINYINT(1)      NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_fi_tax_company_code (company_id, code),
  KEY idx_fi_tax_company (company_id),
  CONSTRAINT fk_fi_tax_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fi_ar_inv (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  cust_id     BIGINT UNSIGNED NOT NULL,
  inv_no      VARCHAR(64)     NOT NULL,
  inv_date    DATE            NOT NULL,
  due_date    DATE            NOT NULL,
  currency    VARCHAR(8)      NOT NULL,
  total       DECIMAL(18,2)   NOT NULL,
  status      VARCHAR(32)     NOT NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_fi_ar_inv_company_inv (company_id, inv_no),
  KEY idx_fi_ar_inv_company (company_id),
  KEY idx_fi_ar_inv_cust (cust_id),
  CONSTRAINT fk_fi_ar_inv_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
  -- cust_id → so_customer.id (domain FK, bisa ditegakkan di implementasi)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fi_ar_inv_line (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ar_id       BIGINT UNSIGNED NOT NULL,
  mat_id      BIGINT UNSIGNED NULL,
  description VARCHAR(255)    NOT NULL,
  qty         DECIMAL(18,4)   NOT NULL DEFAULT 1,
  unit_price  DECIMAL(18,4)   NOT NULL,
  amount      DECIMAL(18,2)   NOT NULL,
  tax_id      BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  KEY idx_fi_ar_inv_line_ar (ar_id),
  KEY idx_fi_ar_inv_line_mat (mat_id),
  CONSTRAINT fk_fi_ar_inv_line_ar
    FOREIGN KEY (ar_id) REFERENCES fi_ar_inv (id) ON DELETE CASCADE
  -- mat_id → inv_mat.id, tax_id → fi_tax.id
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fi_ar_pay (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ar_id       BIGINT UNSIGNED NOT NULL,
  pay_date    DATE            NOT NULL,
  amount      DECIMAL(18,2)   NOT NULL,
  method      VARCHAR(32)     NOT NULL,
  ref_no      VARCHAR(64)     NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_fi_ar_pay_ar (ar_id),
  CONSTRAINT fk_fi_ar_pay_ar
    FOREIGN KEY (ar_id) REFERENCES fi_ar_inv (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fi_ap_inv (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  vendor_id   BIGINT UNSIGNED NOT NULL,
  inv_no      VARCHAR(64)     NOT NULL,
  inv_date    DATE            NOT NULL,
  due_date    DATE            NOT NULL,
  currency    VARCHAR(8)      NOT NULL,
  total       DECIMAL(18,2)   NOT NULL,
  status      VARCHAR(32)     NOT NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_fi_ap_inv_company_inv (company_id, inv_no),
  KEY idx_fi_ap_inv_company (company_id),
  KEY idx_fi_ap_inv_vendor (vendor_id),
  CONSTRAINT fk_fi_ap_inv_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
  -- vendor_id → po_vendor.id
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fi_ap_inv_line (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ap_id       BIGINT UNSIGNED NOT NULL,
  mat_id      BIGINT UNSIGNED NULL,
  description VARCHAR(255)    NOT NULL,
  qty         DECIMAL(18,4)   NOT NULL DEFAULT 1,
  unit_price  DECIMAL(18,4)   NOT NULL,
  amount      DECIMAL(18,2)   NOT NULL,
  tax_id      BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  KEY idx_fi_ap_inv_line_ap (ap_id),
  KEY idx_fi_ap_inv_line_mat (mat_id),
  CONSTRAINT fk_fi_ap_inv_line_ap
    FOREIGN KEY (ap_id) REFERENCES fi_ap_inv (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fi_ap_pay (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ap_id       BIGINT UNSIGNED NOT NULL,
  pay_date    DATE            NOT NULL,
  amount      DECIMAL(18,2)   NOT NULL,
  method      VARCHAR(32)     NOT NULL,
  ref_no      VARCHAR(64)     NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_fi_ap_pay_ap (ap_id),
  CONSTRAINT fk_fi_ap_pay_ap
    FOREIGN KEY (ap_id) REFERENCES fi_ap_inv (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fi_cash_tx (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  tx_date     DATE            NOT NULL,
  type        VARCHAR(32)     NOT NULL,
  amount      DECIMAL(18,2)   NOT NULL,
  currency    VARCHAR(8)      NOT NULL,
  note        VARCHAR(255)    NULL,
  ref_type    VARCHAR(64)     NULL,
  ref_id      BIGINT UNSIGNED NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_fi_cash_tx_company (company_id, tx_date),
  CONSTRAINT fk_fi_cash_tx_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. General Ledger Module (`gl_*`)

CREATE TABLE gl_coa (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(32)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  type        VARCHAR(32)     NOT NULL, -- asset, liability, equity, revenue, expense
  is_postable TINYINT(1)      NOT NULL DEFAULT 1,
  parent_id   BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_gl_coa_company_code (company_id, code),
  KEY idx_gl_coa_company (company_id),
  KEY idx_gl_coa_parent (parent_id),
  CONSTRAINT fk_gl_coa_company
    FOREIGN KEY (company_id) REFERENCES core_company (id),
  CONSTRAINT fk_gl_coa_parent
    FOREIGN KEY (parent_id) REFERENCES gl_coa (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE gl_entry (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  entry_date  DATE            NOT NULL,
  ref_type    VARCHAR(64)     NOT NULL,
  ref_id      BIGINT UNSIGNED NULL,
  note        VARCHAR(255)    NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_gl_entry_company_date (company_id, entry_date),
  CONSTRAINT fk_gl_entry_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE gl_line (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entry_id    BIGINT UNSIGNED NOT NULL,
  coa_id      BIGINT UNSIGNED NOT NULL,
  branch_id   BIGINT UNSIGNED NULL,
  debit       DECIMAL(18,2)   NOT NULL DEFAULT 0,
  credit      DECIMAL(18,2)   NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_gl_line_entry (entry_id),
  KEY idx_gl_line_coa (coa_id),
  KEY idx_gl_line_branch (branch_id),
  CONSTRAINT fk_gl_line_entry
    FOREIGN KEY (entry_id) REFERENCES gl_entry (id) ON DELETE CASCADE,
  CONSTRAINT fk_gl_line_coa
    FOREIGN KEY (coa_id) REFERENCES gl_coa (id),
  CONSTRAINT fk_gl_line_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4. Inventory Module (`inv_*`)

CREATE TABLE inv_mat (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  uom         VARCHAR(16)     NOT NULL,
  category    VARCHAR(64)     NULL,
  is_active   TINYINT(1)      NOT NULL DEFAULT 1,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_mat_company_code (company_id, code),
  KEY idx_inv_mat_company (company_id),
  CONSTRAINT fk_inv_mat_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE inv_stock (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  branch_id       BIGINT UNSIGNED NOT NULL,
  mat_id          BIGINT UNSIGNED NOT NULL,
  qty             DECIMAL(18,4)   NOT NULL DEFAULT 0,
  last_updated_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_stock_branch_mat (branch_id, mat_id),
  KEY idx_inv_stock_mat (mat_id),
  CONSTRAINT fk_inv_stock_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id),
  CONSTRAINT fk_inv_stock_mat
    FOREIGN KEY (mat_id) REFERENCES inv_mat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE inv_move (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  branch_id   BIGINT UNSIGNED NOT NULL,
  mat_id      BIGINT UNSIGNED NOT NULL,
  move_date   DATETIME        NOT NULL,
  type        VARCHAR(32)     NOT NULL,
  qty         DECIMAL(18,4)   NOT NULL,
  ref_type    VARCHAR(64)     NULL,
  ref_id      BIGINT UNSIGNED NULL,
  note        VARCHAR(255)    NULL,
  PRIMARY KEY (id),
  KEY idx_inv_move_branch_mat_date (branch_id, mat_id, move_date),
  CONSTRAINT fk_inv_move_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id),
  CONSTRAINT fk_inv_move_mat
    FOREIGN KEY (mat_id) REFERENCES inv_mat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE inv_adj (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  branch_id   BIGINT UNSIGNED NOT NULL,
  adj_date    DATE            NOT NULL,
  reason      VARCHAR(255)    NULL,
  status      VARCHAR(32)     NOT NULL,
  created_by  BIGINT UNSIGNED NOT NULL,
  approved_by BIGINT UNSIGNED NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_inv_adj_branch_date (branch_id, adj_date),
  CONSTRAINT fk_inv_adj_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id),
  CONSTRAINT fk_inv_adj_created_by
    FOREIGN KEY (created_by) REFERENCES core_user (id),
  CONSTRAINT fk_inv_adj_approved_by
    FOREIGN KEY (approved_by) REFERENCES core_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE inv_adj_line (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  adj_id        BIGINT UNSIGNED NOT NULL,
  mat_id        BIGINT UNSIGNED NOT NULL,
  system_qty    DECIMAL(18,4)   NOT NULL,
  physical_qty  DECIMAL(18,4)   NOT NULL,
  diff_qty      DECIMAL(18,4)   NOT NULL,
  PRIMARY KEY (id),
  KEY idx_inv_adj_line_adj (adj_id),
  KEY idx_inv_adj_line_mat (mat_id),
  CONSTRAINT fk_inv_adj_line_adj
    FOREIGN KEY (adj_id) REFERENCES inv_adj (id) ON DELETE CASCADE,
  CONSTRAINT fk_inv_adj_line_mat
    FOREIGN KEY (mat_id) REFERENCES inv_mat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5. Procurement Module (`po_*`)

CREATE TABLE po_vendor (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  address     TEXT            NULL,
  tax_id      VARCHAR(64)     NULL,
  is_active   TINYINT(1)      NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_po_vendor_company_code (company_id, code),
  KEY idx_po_vendor_company (company_id),
  CONSTRAINT fk_po_vendor_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE po_req (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id   BIGINT UNSIGNED NOT NULL,
  branch_id    BIGINT UNSIGNED NOT NULL,
  req_no       VARCHAR(64)     NOT NULL,
  req_date     DATE            NOT NULL,
  status       VARCHAR(32)     NOT NULL,
  requester_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_po_req_company_req (company_id, req_no),
  KEY idx_po_req_branch (branch_id),
  CONSTRAINT fk_po_req_company
    FOREIGN KEY (company_id) REFERENCES core_company (id),
  CONSTRAINT fk_po_req_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id),
  CONSTRAINT fk_po_req_requester
    FOREIGN KEY (requester_id) REFERENCES core_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE po_req_line (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  req_id      BIGINT UNSIGNED NOT NULL,
  mat_id      BIGINT UNSIGNED NOT NULL,
  qty         DECIMAL(18,4)   NOT NULL,
  uom         VARCHAR(16)     NOT NULL,
  note        VARCHAR(255)    NULL,
  PRIMARY KEY (id),
  KEY idx_po_req_line_req (req_id),
  KEY idx_po_req_line_mat (mat_id),
  CONSTRAINT fk_po_req_line_req
    FOREIGN KEY (req_id) REFERENCES po_req (id) ON DELETE CASCADE,
  CONSTRAINT fk_po_req_line_mat
    FOREIGN KEY (mat_id) REFERENCES inv_mat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE po_order (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  branch_id   BIGINT UNSIGNED NOT NULL,
  vendor_id   BIGINT UNSIGNED NOT NULL,
  po_no       VARCHAR(64)     NOT NULL,
  po_date     DATE            NOT NULL,
  currency    VARCHAR(8)      NOT NULL,
  status      VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_po_order_company_po (company_id, po_no),
  KEY idx_po_order_vendor (vendor_id),
  CONSTRAINT fk_po_order_company
    FOREIGN KEY (company_id) REFERENCES core_company (id),
  CONSTRAINT fk_po_order_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id),
  CONSTRAINT fk_po_order_vendor
    FOREIGN KEY (vendor_id) REFERENCES po_vendor (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE po_order_line (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  po_id       BIGINT UNSIGNED NOT NULL,
  mat_id      BIGINT UNSIGNED NOT NULL,
  qty         DECIMAL(18,4)   NOT NULL,
  unit_price  DECIMAL(18,4)   NOT NULL,
  amount      DECIMAL(18,2)   NOT NULL,
  tax_id      BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  KEY idx_po_order_line_po (po_id),
  KEY idx_po_order_line_mat (mat_id),
  CONSTRAINT fk_po_order_line_po
    FOREIGN KEY (po_id) REFERENCES po_order (id) ON DELETE CASCADE,
  CONSTRAINT fk_po_order_line_mat
    FOREIGN KEY (mat_id) REFERENCES inv_mat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE po_gr (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  branch_id   BIGINT UNSIGNED NOT NULL,
  vendor_id   BIGINT UNSIGNED NOT NULL,
  po_id       BIGINT UNSIGNED NOT NULL,
  gr_no       VARCHAR(64)     NOT NULL,
  gr_date     DATE            NOT NULL,
  status      VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_po_gr_company_gr (company_id, gr_no),
  KEY idx_po_gr_po (po_id),
  CONSTRAINT fk_po_gr_company
    FOREIGN KEY (company_id) REFERENCES core_company (id),
  CONSTRAINT fk_po_gr_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id),
  CONSTRAINT fk_po_gr_vendor
    FOREIGN KEY (vendor_id) REFERENCES po_vendor (id),
  CONSTRAINT fk_po_gr_po
    FOREIGN KEY (po_id) REFERENCES po_order (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE po_gr_line (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gr_id         BIGINT UNSIGNED NOT NULL,
  po_line_id    BIGINT UNSIGNED NOT NULL,
  mat_id        BIGINT UNSIGNED NOT NULL,
  qty_received  DECIMAL(18,4)   NOT NULL,
  qty_rejected  DECIMAL(18,4)   NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_po_gr_line_gr (gr_id),
  KEY idx_po_gr_line_mat (mat_id),
  CONSTRAINT fk_po_gr_line_gr
    FOREIGN KEY (gr_id) REFERENCES po_gr (id) ON DELETE CASCADE,
  CONSTRAINT fk_po_gr_line_mat
    FOREIGN KEY (mat_id) REFERENCES inv_mat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

6. Sales & POS Module (`so_*`)

CREATE TABLE so_customer (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  phone       VARCHAR(64)     NULL,
  email       VARCHAR(128)    NULL,
  address     TEXT            NULL,
  is_active   TINYINT(1)      NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_so_customer_company_code (company_id, code),
  KEY idx_so_customer_company (company_id),
  CONSTRAINT fk_so_customer_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE so_order (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  branch_id   BIGINT UNSIGNED NOT NULL,
  cust_id     BIGINT UNSIGNED NOT NULL,
  so_no       VARCHAR(64)     NOT NULL,
  so_date     DATE            NOT NULL,
  status      VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_so_order_company_so (company_id, so_no),
  KEY idx_so_order_branch (branch_id),
  KEY idx_so_order_cust (cust_id),
  CONSTRAINT fk_so_order_company
    FOREIGN KEY (company_id) REFERENCES core_company (id),
  CONSTRAINT fk_so_order_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id),
  CONSTRAINT fk_so_order_cust
    FOREIGN KEY (cust_id) REFERENCES so_customer (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE so_order_line (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  so_id       BIGINT UNSIGNED NOT NULL,
  mat_id      BIGINT UNSIGNED NOT NULL,
  qty         DECIMAL(18,4)   NOT NULL,
  unit_price  DECIMAL(18,4)   NOT NULL,
  discount    DECIMAL(18,4)   NOT NULL DEFAULT 0,
  amount      DECIMAL(18,2)   NOT NULL,
  PRIMARY KEY (id),
  KEY idx_so_order_line_so (so_id),
  KEY idx_so_order_line_mat (mat_id),
  CONSTRAINT fk_so_order_line_so
    FOREIGN KEY (so_id) REFERENCES so_order (id) ON DELETE CASCADE,
  CONSTRAINT fk_so_order_line_mat
    FOREIGN KEY (mat_id) REFERENCES inv_mat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE so_invoice (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  branch_id   BIGINT UNSIGNED NOT NULL,
  cust_id     BIGINT UNSIGNED NOT NULL,
  inv_no      VARCHAR(64)     NOT NULL,
  inv_date    DATE            NOT NULL,
  status      VARCHAR(32)     NOT NULL,
  total       DECIMAL(18,2)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_so_invoice_company_inv (company_id, inv_no),
  KEY idx_so_invoice_branch (branch_id),
  KEY idx_so_invoice_cust (cust_id),
  CONSTRAINT fk_so_invoice_company
    FOREIGN KEY (company_id) REFERENCES core_company (id),
  CONSTRAINT fk_so_invoice_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id),
  CONSTRAINT fk_so_invoice_cust
    FOREIGN KEY (cust_id) REFERENCES so_customer (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE so_invoice_line (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  so_inv_id   BIGINT UNSIGNED NOT NULL,
  mat_id      BIGINT UNSIGNED NOT NULL,
  qty         DECIMAL(18,4)   NOT NULL,
  unit_price  DECIMAL(18,4)   NOT NULL,
  discount    DECIMAL(18,4)   NOT NULL DEFAULT 0,
  amount      DECIMAL(18,2)   NOT NULL,
  PRIMARY KEY (id),
  KEY idx_so_invoice_line_inv (so_inv_id),
  KEY idx_so_invoice_line_mat (mat_id),
  CONSTRAINT fk_so_invoice_line_inv
    FOREIGN KEY (so_inv_id) REFERENCES so_invoice (id) ON DELETE CASCADE,
  CONSTRAINT fk_so_invoice_line_mat
    FOREIGN KEY (mat_id) REFERENCES inv_mat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE so_pos (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id     BIGINT UNSIGNED NOT NULL,
  branch_id      BIGINT UNSIGNED NOT NULL,
  pos_no         VARCHAR(64)     NOT NULL,
  tx_date        DATETIME        NOT NULL,
  cashier_id     BIGINT UNSIGNED NOT NULL,
  total          DECIMAL(18,2)   NOT NULL,
  payment_method VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_so_pos_company_pos (company_id, pos_no),
  KEY idx_so_pos_branch (branch_id),
  KEY idx_so_pos_cashier (cashier_id),
  CONSTRAINT fk_so_pos_company
    FOREIGN KEY (company_id) REFERENCES core_company (id),
  CONSTRAINT fk_so_pos_branch
    FOREIGN KEY (branch_id) REFERENCES core_branch (id),
  CONSTRAINT fk_so_pos_cashier
    FOREIGN KEY (cashier_id) REFERENCES core_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE so_pos_line (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  pos_id      BIGINT UNSIGNED NOT NULL,
  mat_id      BIGINT UNSIGNED NOT NULL,
  qty         DECIMAL(18,4)   NOT NULL,
  unit_price  DECIMAL(18,4)   NOT NULL,
  discount    DECIMAL(18,4)   NOT NULL DEFAULT 0,
  amount      DECIMAL(18,2)   NOT NULL,
  PRIMARY KEY (id),
  KEY idx_so_pos_line_pos (pos_id),
  KEY idx_so_pos_line_mat (mat_id),
  CONSTRAINT fk_so_pos_line_pos
    FOREIGN KEY (pos_id) REFERENCES so_pos (id) ON DELETE CASCADE,
  CONSTRAINT fk_so_pos_line_mat
    FOREIGN KEY (mat_id) REFERENCES inv_mat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

7. HR & Payroll Module (`hr_*`)

CREATE TABLE hr_emp (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  emp_no      VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  department  VARCHAR(128)    NULL,
  position    VARCHAR(128)    NULL,
  hire_date   DATE            NOT NULL,
  status      VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_emp_company_empno (company_id, emp_no),
  KEY idx_hr_emp_company (company_id),
  CONSTRAINT fk_hr_emp_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE hr_att (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  emp_id      BIGINT UNSIGNED NOT NULL,
  att_date    DATE            NOT NULL,
  check_in    DATETIME        NULL,
  check_out   DATETIME        NULL,
  status      VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_att_emp_date (emp_id, att_date),
  KEY idx_hr_att_status (status),
  CONSTRAINT fk_hr_att_emp
    FOREIGN KEY (emp_id) REFERENCES hr_emp (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE hr_leave (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  emp_id      BIGINT UNSIGNED NOT NULL,
  start_date  DATE            NOT NULL,
  end_date    DATE            NOT NULL,
  type        VARCHAR(32)     NOT NULL,
  status      VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  KEY idx_hr_leave_emp (emp_id),
  CONSTRAINT fk_hr_leave_emp
    FOREIGN KEY (emp_id) REFERENCES hr_emp (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE hr_payroll (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  emp_id       BIGINT UNSIGNED NOT NULL,
  period_start DATE            NOT NULL,
  period_end   DATE            NOT NULL,
  gross_salary DECIMAL(18,2)   NOT NULL,
  deduction    DECIMAL(18,2)   NOT NULL DEFAULT 0,
  net_salary   DECIMAL(18,2)   NOT NULL,
  status       VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  KEY idx_hr_payroll_emp_period (emp_id, period_start, period_end),
  CONSTRAINT fk_hr_payroll_emp
    FOREIGN KEY (emp_id) REFERENCES hr_emp (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

8. CRM Module (`crm_*`)

CREATE TABLE crm_contact (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  email       VARCHAR(128)    NULL,
  phone       VARCHAR(64)     NULL,
  type        VARCHAR(32)     NOT NULL,
  source      VARCHAR(64)     NULL,
  PRIMARY KEY (id),
  KEY idx_crm_contact_company (company_id),
  CONSTRAINT fk_crm_contact_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE crm_lead (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  contact_id       BIGINT UNSIGNED NOT NULL,
  title            VARCHAR(255)    NOT NULL,
  stage            VARCHAR(32)     NOT NULL,
  expected_value   DECIMAL(18,2)   NULL,
  expected_close_date DATE         NULL,
  owner_user_id    BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_crm_lead_company (company_id),
  KEY idx_crm_lead_contact (contact_id),
  CONSTRAINT fk_crm_lead_company
    FOREIGN KEY (company_id) REFERENCES core_company (id),
  CONSTRAINT fk_crm_lead_contact
    FOREIGN KEY (contact_id) REFERENCES crm_contact (id),
  CONSTRAINT fk_crm_lead_owner
    FOREIGN KEY (owner_user_id) REFERENCES core_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE crm_campaign (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  start_date  DATE            NOT NULL,
  end_date    DATE            NULL,
  budget      DECIMAL(18,2)   NULL,
  status      VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_crm_campaign_company_code (company_id, code),
  KEY idx_crm_campaign_company (company_id),
  CONSTRAINT fk_crm_campaign_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

9. Customer Service Module (`cs_*`)

CREATE TABLE cs_ticket (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id        BIGINT UNSIGNED NOT NULL,
  customer_ref_type VARCHAR(64)     NULL,
  customer_ref_id   BIGINT UNSIGNED NULL,
  subject           VARCHAR(255)    NOT NULL,
  status            VARCHAR(32)     NOT NULL,
  priority          VARCHAR(32)     NOT NULL,
  created_by        BIGINT UNSIGNED NOT NULL,
  assigned_to       BIGINT UNSIGNED NULL,
  created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cs_ticket_company (company_id),
  KEY idx_cs_ticket_assigned (assigned_to),
  CONSTRAINT fk_cs_ticket_company
    FOREIGN KEY (company_id) REFERENCES core_company (id),
  CONSTRAINT fk_cs_ticket_created_by
    FOREIGN KEY (created_by) REFERENCES core_user (id),
  CONSTRAINT fk_cs_ticket_assigned_to
    FOREIGN KEY (assigned_to) REFERENCES core_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cs_ticket_msg (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ticket_id      BIGINT UNSIGNED NOT NULL,
  sender_user_id BIGINT UNSIGNED NOT NULL,
  message        TEXT            NOT NULL,
  created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cs_ticket_msg_ticket (ticket_id),
  CONSTRAINT fk_cs_ticket_msg_ticket
    FOREIGN KEY (ticket_id) REFERENCES cs_ticket (id) ON DELETE CASCADE,
  CONSTRAINT fk_cs_ticket_msg_sender
    FOREIGN KEY (sender_user_id) REFERENCES core_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

10. Audit & Compliance Module (`core_audit`, `au_sod`)

CREATE TABLE core_audit (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id     BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  entity_type   VARCHAR(128)    NOT NULL,
  entity_id     BIGINT UNSIGNED NULL,
  action        VARCHAR(64)     NOT NULL,
  payload_before JSON           NULL,
  payload_after JSON            NULL,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_core_audit_tenant (tenant_id),
  KEY idx_core_audit_entity (entity_type, entity_id),
  CONSTRAINT fk_core_audit_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenant (id),
  CONSTRAINT fk_core_audit_user
    FOREIGN KEY (user_id) REFERENCES core_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE au_sod (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  role_a_id   BIGINT UNSIGNED NOT NULL,
  role_b_id   BIGINT UNSIGNED NOT NULL,
  rule_code   VARCHAR(64)     NOT NULL,
  description TEXT            NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_au_sod_pair (tenant_id, role_a_id, role_b_id),
  KEY idx_au_sod_tenant (tenant_id),
  CONSTRAINT fk_au_sod_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenant (id),
  CONSTRAINT fk_au_sod_role_a
    FOREIGN KEY (role_a_id) REFERENCES core_role (id),
  CONSTRAINT fk_au_sod_role_b
    FOREIGN KEY (role_b_id) REFERENCES core_role (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

11. Analytics Module (`an_*`)

CREATE TABLE an_kpi (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  description TEXT            NULL,
  formula     TEXT            NULL,
  unit        VARCHAR(32)     NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_an_kpi_company_code (company_id, code),
  KEY idx_an_kpi_company (company_id),
  CONSTRAINT fk_an_kpi_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE an_kpi_val (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  kpi_id       BIGINT UNSIGNED NOT NULL,
  company_id   BIGINT UNSIGNED NOT NULL,
  period_start DATE            NOT NULL,
  period_end   DATE            NOT NULL,
  value        DECIMAL(18,4)   NOT NULL,
  dimension1   VARCHAR(64)     NULL,
  dimension2   VARCHAR(64)     NULL,
  PRIMARY KEY (id),
  KEY idx_an_kpi_val_kpi (kpi_id),
  KEY idx_an_kpi_val_company_period (company_id, period_start, period_end),
  CONSTRAINT fk_an_kpi_val_kpi
    FOREIGN KEY (kpi_id) REFERENCES an_kpi (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

12. Intercompany Module (`ic_*`)

CREATE TABLE ic_entity (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  company_id  BIGINT UNSIGNED NOT NULL,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  type        VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ic_entity_tenant_code (tenant_id, code),
  KEY idx_ic_entity_company (company_id),
  CONSTRAINT fk_ic_entity_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenant (id),
  CONSTRAINT fk_ic_entity_company
    FOREIGN KEY (company_id) REFERENCES core_company (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ic_tx (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id      BIGINT UNSIGNED NOT NULL,
  from_entity_id BIGINT UNSIGNED NOT NULL,
  to_entity_id   BIGINT UNSIGNED NOT NULL,
  tx_date        DATE            NOT NULL,
  amount         DECIMAL(18,2)   NOT NULL,
  currency       VARCHAR(8)      NOT NULL,
  description    VARCHAR(255)    NULL,
  status         VARCHAR(32)     NOT NULL,
  PRIMARY KEY (id),
  KEY idx_ic_tx_tenant_date (tenant_id, tx_date),
  CONSTRAINT fk_ic_tx_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenant (id),
  CONSTRAINT fk_ic_tx_from_entity
    FOREIGN KEY (from_entity_id) REFERENCES ic_entity (id),
  CONSTRAINT fk_ic_tx_to_entity
    FOREIGN KEY (to_entity_id) REFERENCES ic_entity (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

13. Global / Region & Policy Module (`rg_*`)

CREATE TABLE rg_region (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code             VARCHAR(64)     NOT NULL,
  name             VARCHAR(255)    NOT NULL,
  type             VARCHAR(32)     NOT NULL, -- country, state, city
  parent_region_id BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rg_region_code (code),
  KEY idx_rg_region_parent (parent_region_id),
  CONSTRAINT fk_rg_region_parent
    FOREIGN KEY (parent_region_id) REFERENCES rg_region (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE rg_currency (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code       VARCHAR(8)      NOT NULL,
  name       VARCHAR(64)     NOT NULL,
  symbol     VARCHAR(8)      NULL,
  is_default TINYINT(1)      NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rg_currency_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE rg_rate (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  from_currency VARCHAR(8)      NOT NULL,
  to_currency   VARCHAR(8)      NOT NULL,
  rate_date     DATE            NOT NULL,
  rate          DECIMAL(18,6)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rg_rate_pair_date (from_currency, to_currency, rate_date),
  KEY idx_rg_rate_date (rate_date)
  -- from_currency, to_currency → rg_currency.code (logical FK)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE rg_policy (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code        VARCHAR(64)     NOT NULL,
  name        VARCHAR(255)    NOT NULL,
  description TEXT            NULL,
  category    VARCHAR(64)     NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rg_policy_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE rg_policy_assign (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  policy_id       BIGINT UNSIGNED NOT NULL,
  target_type     VARCHAR(64)     NOT NULL,
  target_id       BIGINT UNSIGNED NOT NULL,
  effective_start DATE            NULL,
  effective_end   DATE            NULL,
  PRIMARY KEY (id),
  KEY idx_rg_policy_assign_policy (policy_id),
  KEY idx_rg_policy_assign_target (target_type, target_id),
  CONSTRAINT fk_rg_policy_assign_policy
    FOREIGN KEY (policy_id) REFERENCES rg_policy (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Schema & Design

ERP Schema V2

Operations

ERP Schema V2 - Full DDL (Docs Only)

🧾 ERP Schema V2 – Full DDL (Documentation Only)

0. Konvensi Umum

1. Core Module (`core_*`)

2. Finance Module (`fi_*`)

3. General Ledger Module (`gl_*`)

4. Inventory Module (`inv_*`)

5. Procurement Module (`po_*`)

6. Sales & POS Module (`so_*`)

7. HR & Payroll Module (`hr_*`)

8. CRM Module (`crm_*`)

9. Customer Service Module (`cs_*`)

10. Audit & Compliance Module (`core_audit`, `au_sod`)

11. Analytics Module (`an_*`)

12. Intercompany Module (`ic_*`)

13. Global / Region & Policy Module (`rg_*`)

​🧾 ERP Schema V2 – Full DDL (Documentation Only)

​0. Konvensi Umum

​1. Core Module (core_*)

​2. Finance Module (fi_*)

​3. General Ledger Module (gl_*)

​4. Inventory Module (inv_*)

​5. Procurement Module (po_*)

​6. Sales & POS Module (so_*)

​7. HR & Payroll Module (hr_*)

​8. CRM Module (crm_*)

​9. Customer Service Module (cs_*)

​10. Audit & Compliance Module (core_audit, au_sod)

​11. Analytics Module (an_*)

​12. Intercompany Module (ic_*)

​13. Global / Region & Policy Module (rg_*)

🧾 ERP Schema V2 – Full DDL (Documentation Only)

0. Konvensi Umum

1. Core Module (`core_*`)

2. Finance Module (`fi_*`)

3. General Ledger Module (`gl_*`)

4. Inventory Module (`inv_*`)

5. Procurement Module (`po_*`)

6. Sales & POS Module (`so_*`)

7. HR & Payroll Module (`hr_*`)

8. CRM Module (`crm_*`)

9. Customer Service Module (`cs_*`)

10. Audit & Compliance Module (`core_audit`, `au_sod`)

11. Analytics Module (`an_*`)

12. Intercompany Module (`ic_*`)

13. Global / Region & Policy Module (`rg_*`)