​

🧮 MySQL Schema

📝 Status: Struktur awal – fokus pada tabel utama per feature. Dokumen ini mendeskripsikan tabel utama yang dipakai oleh fitur-fitur inti (Inventory, POS Transaction, Payments, Promotions/Bundles, Stock Opname & Approval) beserta field kunci dan flow antar tabel. Untuk desain schema ERP generik V2 yang sudah disederhanakan ala universal journal, lihat juga:

• ERP Schema V2 Overview
• Halaman modul di grup “ERP Schema V2” seperti Core, Finance, Inventory, dan lainnya.

​

0. 🧭 Mapping 12 Modules → Feature → Tabel

Catatan: daftar tabel di bawah fokus ke tabel yang sudah ada di mstore-monolith saat ini. Tabel future/planned bisa ditambahkan kemudian.

​

0.1 01_core – System, Auth, RBAC, Config

• Feature utama
  • Auth & session (login, refresh token, device registration)
  • RBAC (role, permission, role-permission, user-role)
  • Tenant/merchant & branch registration
  • Config & feature flag
  • Notification & user activity log
• Tabel kunci (contoh)
  • users, user_sessions, user_refresh_tokens, user_devices, user_mapping
  • merchants, branches, branch_regions, branch_contacts
  • roles, permissions, role_permissions
  • config_apps
  • notifications, user_activity_logs, audit_logs

​

0.2 02_finance – Accounting, AR/AP, Kas & Bank

• Feature utama
  • Chart of Accounts & jurnal umum
  • AR/AP (piutang & hutang)
  • Kas & bank (mutasi, rekonsiliasi)
  • Pajak & laporan keuangan
• Tabel kunci (contoh)
  • chart_of_accounts, journal_entries, journal_entry_lines, journal_code_sequences
  • transaction_accounts, transaction_account_mappings
  • accounts_receivable, accounts_payable
  • ar_invoices, ar_invoice_lines, ar_receipts, ar_payments, ar_allocations, ar_invoice_installments
  • ap_bills, ap_bill_lines, ap_payments, ap_allocations, ap_bill_installments
  • bank_mutations, bank_reconciliations, bank_reconciliation_items
  • tax_rates, exchange_rates, exchange_rate_overrides

​

0.3 03_inventory – Barang, Gudang, Stok

• Feature utama
  • Master inventory & kategori
  • UOM & attribute inventory
  • Gudang & mapping branch–warehouse
  • Stok per gudang–batch
  • Mutasi (movement) dan transfer
• Tabel kunci (contoh)
  • inventory, inventory_category, uom
  • warehouses, branch_warehouses, warehouse_stocks
  • inventory_movements, inventory_transfers, inventory_transfer_lines
  • inventory_batches, inventory_prices, inventory_suppliers
  • cogs

​

0.4 04_procurement – Supplier & Pengadaan

• Feature utama
  • Supplier/vendor master
  • Purchase Order (PO)
  • Receiving (goods receipt)
  • Integrasi ke AP & inventory
• Tabel kunci (contoh)
  • partners, supplier_details, partner_bank_accounts
  • purchase_orders, purchase_order_items
  • receivings, receiving_items
  • Integrasi ke: ap_bills, ap_payments, inventory_movements

​

0.5 05_sales – Penjualan & POS

• Feature utama
  • POS transaksi offline-first
  • Sales header & item
  • Addons per item
  • Integrasi ke payment, inventory, accounting
• Tabel kunci (contoh)
  • transactions, transaction_items, transaction_item_addons
  • (opsional) sales, sales_items jika dipakai sebagai header lain
  • Integrasi ke: payments, inventory_movements, journal_entries, accounts_receivable

​

0.6 06_hr – HR & Payroll (Planned)

• Feature utama (konseptual)
  • Employee master
  • Absensi & cuti
  • Payroll (komponen gaji, slip gaji)
• Tabel kunci
  • Belum terdefinisi penuh di schema saat ini – bisa ditambahkan kemudian (mis. employees, attendance, payroll_runs, dll.)

​

0.7 07_crm – Customer Relationship

• Feature utama
  • Customer master & contact detail
  • Segmentation, loyalty, campaign
• Tabel kunci (contoh)
  • partners, customer_details
  • Kedepan: tabel tambahan untuk loyalty/campaign bisa ditambahkan sebagai extension modul ini.

​

0.8 08_cs – Customer Service (Planned)

• Feature utama (konseptual)
  • Ticketing (issue, status, assignment)
  • Feedback & rating
  • SLA monitoring
• Tabel kunci
  • Belum spesifik di schema saat ini. Dapat ditambahkan kemudian (mis. tickets, ticket_comments, feedback, dll.).

​

0.9 09_audit – Audit & Compliance

• Feature utama
  • Audit trail aktivitas user
  • SoD & security audit
  • Compliance reporting
• Tabel kunci (contoh)
  • audit_logs, user_activity_logs
  • (RBAC & role change) menggunakan data dari roles, permissions, role_permissions, user_mapping

​

0.10 10_analytics – Dashboard & BI

• Feature utama
  • Dashboard KPI per modul (sales, finance, inventory)
  • Report builder & export
  • Forecasting & trend
• Tabel kunci
  • Mengambil data dari modul lain (sales, finance, inventory, hr, dsb.).
  • Tidak selalu membutuhkan tabel khusus di OLTP; bisa mengandalkan view/materialized view atau data mart terpisah (belum dicakup di schema ini).

​

0.11 11_holding – Multi-Entity / Group

• Feature utama (konseptual)
  • Entity management (holding, anak perusahaan)
  • Intercompany transactions
  • Group consolidation
• Tabel kunci
  • Saat ini, sebagian konsep diwakili merchants + kode/struktur bisnis.
  • Tabel khusus untuk entity group & intercompany bisa ditambahkan kemudian (mis. entities, entity_groups, intercompany_transactions).

​

0.12 12_global – Multi-Region, IAM, Compliance Global

• Feature utama
  • SSO/IAM, MFA
  • Regional config (country, currency, timezone)
  • Global compliance (GDPR, SOX, dsb.)
• Tabel kunci (contoh)
  • exchange_rates, exchange_rate_overrides (multi-currency)
  • Field currency di banyak tabel (finance, payments, price_lists)
  • Tabel/konfigurasi tambahan untuk IAM & compliance global dapat dibuat saat implementasi modul ini.

​

1. ⚙️ Konvensi Global

• Multi-tenant
  • Hampir semua tabel bisnis memiliki merchant_id (FK ke merchants.id).
  • Banyak tabel juga menyimpan merchant_code sebagai denormalized lookup.
• Branch & Warehouse
  • branches.id dan branches.branch_code dipakai untuk scope per cabang.
  • warehouses.id dan warehouses.warehouse_code untuk scope per gudang.
• Soft delete & audit
  • Pola umum kolom audit:
    • created_at, updated_at, deleted_at.
• Enum dan status
  • Banyak tabel menggunakan enum atau varchar dengan check constraint untuk status.
  • Contoh: inventory_movements.movement_type, payments.status, stock_opnames.status.

​

2. 🔐 Core System, Auth & RBAC Feature (Module 01_core)

​

2.1 Tabel Utama

• users – master user global.
• user_sessions – sesi login + refresh hash per device.
• user_refresh_tokens – keluarga refresh token (rotating refresh) per user/device.
• user_devices – mapping user → device (FCM token, push notification).
• merchants – tenant utama (perusahaan/bisnis) + business_level L0–L4.
• branches – cabang/gerai per merchant.
• branch_regions, branch_contacts – informasi region & kontak cabang.
• roles, permissions, role_permissions – RBAC role/permission registry.
• user_mapping – mapping user ↔ merchant ↔ branch ↔ role (multi-tenant & multi-branch).
• config_apps – konfigurasi aplikasi per merchant (feature flag, setting khusus).
• notifications – notifikasi user (push/email/sms).
• user_activity_logs, audit_logs – activity log & audit trail perubahan data.

​

2.2 users

• Unique: email, user_code.

​

2.3 user_sessions

• id – PK
• user_id – FK ke users.id
• device_id – ID device (mobile/web)
• session_id – ID sesi unik (unik secara global)
• ip, user_agent, client_id, origin_app – metadata koneksi
• refresh_hash – hash dari refresh token aktif
• refresh_expires_at, last_used_at, revoked_at, refresh_rotated_at
• status – enum: active, revoked, expired
• last_seen_at, risk_score, login_time, logout_time

​

2.4 user_refresh_tokens

• id – PK
• user_id
• device_id
• token_hash – hash unik refresh token (unik di tabel)
• status – enum: active, rotated, revoked, expired
• rt_family_id – ID keluarga token
• session_id – link ke user_sessions (opsional)
• abs_expires_at, expires_at, revoked_at

​

2.5 user_devices

• id – PK
• user_id – FK ke users.id
• device_id – ID unik device
• fcm_token – token FCM

​

2.6 merchants

• id – PK
• merchant_code – kode unik merchant
• name, owner_name, email, phone, address
• business_level – enum: L0–L4 (mapping ke level bisnis di RBAC docs)

​

2.7 branches, branch_regions, branch_contacts

• branches – cabang per merchant.
  • Field utama: id, branch_code, merchant_id, name, address, type, currency_code, timezone, phone, email, default_warehouse_id.
  • Unique per merchant: (merchant_id, branch_code), (merchant_id, email), (merchant_id, phone).
• branch_regions – mapping cabang → region.
  • Field: branch_id, region_id, kode denormalized.
• branch_contacts – kontak tambahan per cabang.
  • Field: branch_id, type (phone, email, whatsapp, other), value.

​

2.8 RBAC: roles, permissions, role_permissions, user_mapping

• roles
  • id, roles_code, name, description, available_levels
  • available_levels menyimpan business level yang boleh memakai role (mis. L0,L1).
• permissions
  • id, permission_code, name, description.
• role_permissions
  • Mapping role ↔ permission.
  • Field: role_id, permission_id (+ kode denormalized).
  • Unique: (role_id, permission_id).
• user_mapping
  • Mapping user ↔ merchant ↔ branch ↔ role.
  • Field: user_id, merchant_id, branch_id, role_id (+ kode denormalized).
  • Unique: (user_id, merchant_id, branch_id, role_id).

​

2.9 config_apps

• id
• merchant_id, merchant_code
• group_name – grup config (mis. tax, pos, payment)
• config_name – nama config
• config_value – nilai (string/JSON)
• method_auth – metode auth jika config terkait integrasi eksternal

​

2.10 notifications, user_activity_logs, audit_logs

• notifications – notifikasi ke user.
  • Field: user_id, branch_id, type (approval, reminder, info, dll), entity_type, entity_id, message, is_read, channel, priority.
• user_activity_logs – log aktivitas high-level user.
  • Field: user_id, activity_type (FK ke lookup_code), activity_time, ip_address, user_agent.
• audit_logs – audit trail perubahan data (before/after).
  • Field: user_id, action, table_name, record_id, old_data, new_data.

​

2.11 Flow Core System & RBAC

• users + user_sessions + user_refresh_tokens mengelola login & sesi.
• merchants + branches membentuk tenant & cabang.
• roles, permissions, role_permissions, user_mapping membentuk RBAC multi-tenant.
• config_apps menyimpan setting per merchant.
• notifications, user_activity_logs, audit_logs memberi observability & compliance untuk semua modul.

​

3. 📦 Inventory & Warehouse Feature (Module 03_inventory)

​

3.1 Tabel Utama

• inventory – master barang/inventory per merchant + cabang.
• warehouses – daftar gudang per merchant.
• branch_warehouses – mapping cabang ↔ gudang (default/backup/return/display).
• warehouse_stocks – stok per gudang + inventory (+ batch optional).
• inventory_movements – riwayat movement granular (IN/OUT per gudang–barang–batch).
• inventory_transfers – header mutasi antar gudang (workflow + approval).

​

3.2 inventory

• Unique: unique_merchant_inventory_sku (merchant_id, inventory_sku).

​

3.3 warehouses

​

3.4 branch_warehouses

​

3.5 warehouse_stocks

​

3.6 inventory_movements

​

3.7 inventory_transfers

• id, transfer_code
• merchant_id
• from_warehouse_id, to_warehouse_id
• status enum: draft, requested, approved, in_transit, received, cancelled
• requested_by, approved_by, shipped_by, received_by
• requested_at, approved_at, shipped_at, received_at
• approval_request_id (FK ke approval_requests.id)

​

3.8 Flow Inventory & Warehouse

• Inventory master disimpan di inventory (merchant-scoped).
• Stock real per gudang di warehouse_stocks.
• Semua perubahan stok harus lewat inventory_movements (bukan update langsung ke stok total).
• Mutasi antar gudang dicatat di inventory_transfers + menghasilkan movement transfer_out / transfer_in.

​

4. 🧾 Sales & POS Feature (Module 05_sales)

​

4.1 Tabel Utama

• transactions – header transaksi POS.
• transaction_items – detail item.
• transaction_item_addons – detail addons per item.

​

4.2 transactions

​

4.3 transaction_items

• id – PK
• transaction_id – FK ke transactions.id
• product_id – FK ke product.id
• quantity – qty
• price – harga per unit
• subtotal – total line

​

4.4 transaction_item_addons

• id – PK
• transaction_item_id – FK ke transaction_items.id
• addon_id
• quantity, price, subtotal

​

4.5 Flow POS Transactions

1. Header transaksi dibuat di transactions.
2. Item-item disimpan di transaction_items.
3. Addons per item di transaction_item_addons.
4. Pembayaran dicatat di tabel payments (lihat section berikutnya) dan di-link ke transactions.id.

​

5. 💳 Payments Feature

​

5.1 Tabel Utama

• payments – payment record per transaksi/payment attempt.
• payment_methods – master metode pembayaran (tidak dirinci di sini).

​

5.2 payments

• (merchant_id, payment_code)
• (merchant_id, idempotency_key)
• (merchant_id, reference_id)
• (merchant_id, external_payment_id)
• (merchant_id, external_invoice_id)

​

5.3 Flow Payments

1. POS membuat transactions.
2. Frontend/payment service membuat payments (pending) ke provider.
3. Provider kirim callback → update payments.status dan transactions.payment_status.
4. Offline-first: is_offline, sync_status, dan payload JSON dipakai untuk rekonsiliasi ketika online.

​

6. 🎯 Promotions & Bundles Feature

​

6.1 Tabel Utama

• promotions – definisi promo per merchant.
• promotion_conditions – syarat promo (produk, kategori, minimal belanja, dsb.).
• promotion_rewards – benefit promo (discount, free item, cashback, dsb.).
• promotion_branches, promotion_customers, promotion_countries – scoping promo.
• price_lists – daftar harga per merchant/branch.
• bundles – definisi paket bundle.
• bundle_components – komponen produk di dalam bundle.

​

6.2 promotions

• id, merchant_id, merchant_code
• code – kode promo (unik per merchant)
• name – nama promo
• type – enum: catalog, cart, payment, shipping
• priority – urutan prioritas pemrosesan
• start_date, end_date
• is_active

​

6.3 price_lists

• id, merchant_id, merchant_code
• branch_id, branch_code
• code, name, currency
• valid_from, valid_to
• is_active

​

6.4 bundles

• id, merchant_id, merchant_code
• code, name
• price_type – fixed atau computed
• fixed_price – jika price_type=fixed
• is_active

​

6.5 bundle_components

• id
• bundle_id – FK ke bundles.id
• product_id – FK ke product.id
• product_code
• qty

​

6.6 Flow Promotions & Bundles

• Promo didefinisikan di promotions dan dihubungkan dengan conditions & rewards.
• price_lists digunakan sebagai basis tier harga per merchant/branch.
• Bundles didefinisikan di bundles dan isinya di bundle_components.

​

7. 📊 Stock Opname & Approval Feature

​

7.1 Tabel Utama

• stock_opnames – header sesi stock opname per cabang.
• stock_opname_items – detail item dan selisih qty.
• approval_requests, approval_flows, approval_logs, approvers – engine approval generik.

​

7.2 stock_opnames

• id
• branch_id – cabang lokasi opname
• opname_date – tanggal opname
• status – enum: scheduled, in_progress, waiting_approval, approved, rejected, adjusted
• approval_request_id – FK ke approval_requests.id
• created_by – user pembuat

​

7.3 stock_opname_items

• id
• stock_opname_id – FK ke stock_opnames.id
• product_id – FK ke product.id
• system_qty – qty menurut sistem
• physical_qty – qty hasil hitung fisik
• discrepancy – kolom generated (physical_qty - system_qty)
• status – pending, approved, rejected, adjusted

​

7.4 Flow Stock Opname & Approval

1. Sesi stock opname dibuat di stock_opnames (per branch + tanggal).
2. Item opname disimpan di stock_opname_items dengan qty sistem vs fisik.
3. Jika butuh persetujuan, dibuat approval_requests dan log di approval_logs.
4. Setelah disetujui, service akan mem-post adjustment ke inventory_movements dan update warehouse_stocks.

​

11. 🔍 Use Case Queries

​

11.1 AR Aging (Accounts Receivable)

SELECT
  c.name               AS customer_name,
  ar.invoice_number,
  ar.due_date,
  DATEDIFF(CURDATE(), ar.due_date) AS days_overdue,
  CASE
    WHEN DATEDIFF(CURDATE(), ar.due_date) <= 30 THEN '0-30'
    WHEN DATEDIFF(CURDATE(), ar.due_date) BETWEEN 31 AND 60 THEN '31-60'
    WHEN DATEDIFF(CURDATE(), ar.due_date) BETWEEN 61 AND 90 THEN '61-90'
    ELSE '>90'
  END AS aging_bucket,
  ar.amount           AS amount
FROM accounts_receivable ar
JOIN partners c ON c.id = ar.customer_id
WHERE ar.status IN ('open', 'partial')
  AND ar.deleted_at IS NULL
ORDER BY c.name, ar.due_date;

​

11.2 AP Aging (Accounts Payable)

SELECT
  s.name               AS supplier_name,
  ap.invoice_number,
  ap.due_date,
  DATEDIFF(CURDATE(), ap.due_date) AS days_overdue,
  CASE
    WHEN DATEDIFF(CURDATE(), ap.due_date) <= 30 THEN '0-30'
    WHEN DATEDIFF(CURDATE(), ap.due_date) BETWEEN 31 AND 60 THEN '31-60'
    WHEN DATEDIFF(CURDATE(), ap.due_date) BETWEEN 61 AND 90 THEN '61-90'
    ELSE '>90'
  END AS aging_bucket,
  ap.amount           AS amount
FROM accounts_payable ap
JOIN partners s ON s.id = ap.supplier_id
WHERE ap.status IN ('open', 'partial')
  AND ap.deleted_at IS NULL
ORDER BY s.name, ap.due_date;

​

11.3 Kartu Stok per Warehouse & Inventory

SELECT
  im.moved_at,
  im.movement_type,
  im.reference_type,
  im.reference_code,
  im.qty,
  im.unit,
  @running := @running + im.qty AS running_balance
FROM (
  SELECT
    m.moved_at,
    m.movement_type,
    m.reference_type,
    m.reference_code,
    /* Convention: IN positive, OUT negative di level service */
    CASE
      WHEN m.movement_type IN ('purchase_receipt','transfer_in','return_in','production_receipt')
        THEN m.qty
      ELSE -m.qty
    END AS qty,
    m.unit
  FROM inventory_movements m
  WHERE m.warehouse_id = :warehouse_id
    AND m.inventory_id = :inventory_id
    AND m.deleted_at IS NULL
  ORDER BY m.moved_at, m.id
) im
CROSS JOIN (SELECT @running := 0) vars;

​

11.4 Ringkasan Stok per Warehouse (Qty On Hand & Reserved)

SELECT
  w.warehouse_code,
  i.inventory_sku,
  i.name          AS inventory_name,
  ws.unit,
  ws.qty_on_hand,
  ws.qty_reserved,
  (ws.qty_on_hand - ws.qty_reserved) AS qty_available,
  i.min_stock,
  CASE
    WHEN (ws.qty_on_hand - ws.qty_reserved) < i.min_stock THEN 1
    ELSE 0
  END AS is_below_min
FROM warehouse_stocks ws
JOIN warehouses w ON w.id = ws.warehouse_id
JOIN inventory  i ON i.id = ws.inventory_id
WHERE ws.deleted_at IS NULL
ORDER BY w.warehouse_code, i.inventory_sku;

​

11.5 Rekonsiliasi Bank (Mutasi vs Transaksi)

SELECT
  br.id                         AS reconciliation_id,
  br.period_start,
  br.period_end,
  bm.id                         AS bank_mutation_id,
  bm.description,
  bm.amount,
  bri.match_status,
  t.id                          AS transaction_id,
  t.transaction_code
FROM bank_reconciliation_items bri
JOIN bank_reconciliations br ON br.id = bri.bank_reconciliation_id
JOIN bank_mutations bm       ON bm.id = bri.bank_mutation_id
LEFT JOIN transactions t     ON t.id = bri.transaction_id
WHERE br.id = :reconciliation_id
  AND bri.deleted_at IS NULL
ORDER BY bm.transaction_date, bm.id;

​

11.6 Penjualan Harian per Cabang (Sales/POS)

SELECT
  DATE(t.transaction_date) AS trx_date,
  b.branch_code,
  SUM(t.total_amount)      AS total_sales,
  COUNT(*)                 AS trx_count
FROM transactions t
JOIN branches b ON b.id = t.branch_id
WHERE t.status IN ('paid','synced','journaled')
  AND t.deleted_at IS NULL
  AND t.transaction_date BETWEEN :start_date AND :end_date
GROUP BY DATE(t.transaction_date), b.branch_code
ORDER BY trx_date, b.branch_code;

​

11.7 Penjualan per Produk (Top N Produk)

SELECT
  p.id            AS product_id,
  p.name          AS product_name,
  SUM(ti.quantity) AS qty_sold,
  SUM(ti.subtotal) AS revenue
FROM transaction_items ti
JOIN product p       ON p.id = ti.product_id
JOIN transactions t  ON t.id = ti.transaction_id
WHERE t.status IN ('paid','synced','journaled')
  AND t.deleted_at IS NULL
  AND t.transaction_date BETWEEN :start_date AND :end_date
GROUP BY p.id, p.name
ORDER BY qty_sold DESC
LIMIT :top_n;

​

11.8 Laporan Status Payments (Pending/Success/Expired)

SELECT
  provider,
  channel,
  status,
  COUNT(*)                 AS count_payments,
  SUM(amount)              AS total_amount
FROM payments
WHERE deleted_at IS NULL
  AND created_at BETWEEN :start_date AND :end_date
GROUP BY provider, channel, status
ORDER BY provider, channel, status;

​

11.9 Daftar PO Terbuka & Overdue

SELECT
  po.po_number,
  s.name          AS supplier_name,
  b.branch_code,
  po.order_date,
  po.status,
  po.total
FROM purchase_orders po
JOIN partners s ON s.id = po.supplier_id
JOIN branches b ON b.id = po.branch_id
WHERE po.status IN ('draft','waiting_approval','approved')
  AND po.deleted_at IS NULL
  AND po.order_date <= :cutoff_date
ORDER BY po.order_date, po.po_number;

​

11.10 Receiving vs PO (Variance Quantity)

SELECT
  po.po_number,
  p.id                AS product_id,
  p.name              AS product_name,
  poi.qty             AS qty_ordered,
  COALESCE(SUM(ri.qty_received), 0) AS qty_received,
  (poi.qty - COALESCE(SUM(ri.qty_received), 0)) AS qty_remaining
FROM purchase_order_items poi
JOIN purchase_orders po ON po.id = poi.purchase_order_id
JOIN product p         ON p.id = poi.product_id
LEFT JOIN receiving_items ri ON ri.purchase_order_item_id = poi.id
WHERE po.deleted_at IS NULL
GROUP BY po.po_number, p.id, p.name, poi.qty
ORDER BY po.po_number, p.name;

​

11.11 Laporan Stock Opname (Variance & Status)

SELECT
  so.id                AS stock_opname_id,
  b.branch_code,
  so.opname_date,
  soi.product_id,
  p.name              AS product_name,
  soi.system_qty,
  soi.physical_qty,
  soi.discrepancy,
  soi.status
FROM stock_opnames so
JOIN branches b        ON b.id = so.branch_id
JOIN stock_opname_items soi ON soi.stock_opname_id = so.id
JOIN product p         ON p.id = soi.product_id
WHERE so.deleted_at IS NULL
  AND soi.deleted_at IS NULL
  AND so.opname_date BETWEEN :start_date AND :end_date
ORDER BY so.opname_date, b.branch_code, p.name;

​

11.12 Aktivitas User & Audit Log

-- Aktivitas user (login, transaksi, dsb.)
SELECT
  u.username,
  u.email,
  ua.activity_time,
  lc.code       AS activity_type_code,
  ua.ip_address,
  ua.user_agent
FROM user_activity_logs ua
JOIN users u       ON u.id = ua.user_id
JOIN lookup_code lc ON lc.id = ua.activity_type
WHERE ua.activity_time BETWEEN :start_time AND :end_time
  AND u.id = :user_id
ORDER BY ua.activity_time DESC;

-- Audit log perubahan data
SELECT
  u.username,
  al.action,
  al.table_name,
  al.record_id,
  al.created_at,
  al.old_data,
  al.new_data
FROM audit_logs al
LEFT JOIN users u ON u.id = al.user_id
WHERE al.created_at BETWEEN :start_time AND :end_time
  AND (al.user_id = :user_id OR :user_id IS NULL)
ORDER BY al.created_at DESC;

​

Catatan & Next Step

• Dokumen ini baru mencakup tabel utama, flow antar tabel, dan contoh query use-case yang paling umum.
• Query bisa disesuaikan dengan kebutuhan (filter tambahan, index, dsb.) dan dijadikan view/materialized view jika dibutuhkan performa lebih baik.
• Use-case tambahan bisa ditambahkan di section ini dengan pola yang sama.

​

8. 📒 Finance & Accounting Feature (Module 02_finance)

​

8.1 Tabel Utama (Ringkasan)

• COA & Jurnal
  • chart_of_accounts – master akun.
  • journal_entries – header jurnal (per dokumen bisnis).
  • journal_entry_lines – detail debit/credit per akun.
  • journal_code_sequences – generator kode jurnal per modul/periode.
• Accounts Receivable (AR)
  • accounts_receivable – saldo piutang per invoice/customer.
  • ar_invoices, ar_invoice_lines – invoice AR dan komponennya.
  • ar_payments – pembayaran yang dialokasikan ke AR.
  • ar_receipts – penerimaan kas AR (header official receipt).
• Accounts Payable (AP)
  • accounts_payable – saldo hutang per invoice/supplier.
  • ap_bills, ap_bill_lines – bill AP dan komponennya.
  • ap_payments – pembayaran hutang ke supplier.
• Kas & Bank
  • bank_mutations – mutasi bank (import statement).
  • bank_reconciliations – header rekonsiliasi bank (periode, status, approval).
  • bank_reconciliation_items – mapping mutasi bank ↔ transaksi internal.
• Tax & FX
  • tax_rates – master tax rate per region.
  • exchange_rates – kurs global harian.
  • exchange_rate_overrides – override kurs per merchant/branch.

​

8.2 Flow Finance Tingkat Tinggi

• Semua transaksi bisnis penting (sales, purchase, payment) pada akhirnya mem-post jurnal ke journal_entries + journal_entry_lines.
• Piutang dikelola via accounts_receivable + ar_*, hutang via accounts_payable + ap_*.
• Kas & bank memakai bank_mutations (import) dan bank_reconciliations untuk rekonsiliasi ke transaksi internal.
• Kurs & pajak diambil dari exchange_rates, exchange_rate_overrides, dan tax_rates sebagai referensi global/regional.

​

9. 📥 Procurement Feature (Module 04_procurement)

​

9.1 Tabel Utama (Ringkasan)

• Master Supplier
  • partners (type=supplier) – master supplier.
  • supplier_details – detail tambahan supplier.
  • partner_bank_accounts – rekening bank supplier.
• Purchase Order & Receiving
  • purchase_orders – header PO.
  • purchase_order_items – detail item PO.
  • receivings – header penerimaan barang (GRN).
  • receiving_items – detail item yang diterima.
• Integrasi ke Finance & Inventory
  • AP:
    • accounts_payable, ap_bills, ap_bill_lines, ap_payments.
  • Inventory:
    • inventory_movements (movement purchase_receipt) dan update ke warehouse_stocks.

​

9.2 Flow Procurement → AP → Inventory (Konseptual)

​

10. 🌐 Modules 06–12 (HR, CRM, CS, Audit, Analytics, Holding, Global)

​

10.1 HR & Payroll (Module 06_hr)

• Scope: employee master, attendance, leave, payroll.
• Status schema: belum ada tabel khusus di mstore-monolith.
• Catatan: ketika diimplementasikan, tabel seperti employees, attendance, payroll_runs, payroll_lines bisa dibuat dan di-link ke modul finance (journal) & core (users).

​

10.2 CRM (Module 07_crm)

• Scope: customer master, segmentation, loyalty, campaign.
• Tabel yang dipakai saat ini:
  • partners (type=customer) – master customer.
  • customer_details – config customer (group, payment_term, credit_limit, price_list).
• Catatan: loyalty/campaign dapat menambah tabel baru (mis. loyalty_points, campaigns) sebagai extension modul ini.

​

10.3 Customer Service (Module 08_cs)

• Scope: ticketing, reminder, SLA.
• Tabel relevan saat ini:
  • notifications – notifikasi ke user terkait approvals, reminder, info.
  • reminders – jadwal reminder (stock_opname, PO expiry, shift closing, custom).
  • reminder_logs – log pengiriman reminder (due/overdue/escalation) ke user.
• Catatan: ticketing penuh (ticket, comment, SLA) bisa ditambahkan nanti dengan tabel baru.

​

10.4 Audit & Compliance (Module 09_audit)

• Scope: audit trail, SoD, compliance reporting.
• Tabel utama:
  • user_activity_logs – log aktivitas user (tipe aktivitas via lookup_code).
  • audit_logs – before/after change per record (table_name, record_id, old_data, new_data).
• Integrasi: memakai data dari modul Core (users, roles, permissions, user_mapping) untuk analisis SoD.

​

10.5 Analytics & BI (Module 10_analytics)

• Scope: dashboard KPI, laporan, forecasting.
• Karakteristik:
  • Mengkonsumsi data dari modul lain (finance, sales, inventory, hr).
  • Biasanya diimplementasikan via view/materialized view atau data mart terpisah, bukan tabel OLTP tambahan.
• Catatan: schema OLTP saat ini belum memiliki tabel khusus untuk analytics; desain data warehouse bisa didokumentasikan terpisah.

​

10.6 Holding / Multi-Entity (Module 11_holding)

• Scope: multi-entity, intercompany, group consolidation.
• Tabel relevan saat ini:
  • merchants – dapat merepresentasikan entity berbeda (perusahaan/grup).
• Catatan:
  • Tabel khusus seperti entities, entity_groups, intercompany_transactions bisa ditambahkan nanti.
  • Consolidation biasanya dilakukan di layer analytics/BI.

​

10.7 Global / Multi-Region & Compliance (Module 12_global)

• Scope: multi-currency, multi-region, SSO/IAM, global compliance.
• Tabel relevan saat ini:
  • exchange_rates – kurs global per tanggal.
  • exchange_rate_overrides – override kurs per merchant/branch.
  • Field currency di berbagai tabel (finance, payments, price_lists) – fondasi multi-currency.
• Catatan:
  • IAM/SSO global dapat menambah tabel/config baru (provider, identity mapping, dsb.).
  • Compliance (SOX/GDPR) lebih banyak memanfaatkan audit & logs yang sudah ada di modul Core/Audit.