Entity Relationship Diagram

Dokumentasi lengkap database schema dengan ERD (Entity Relationship Diagram) menggunakan Mermaid.

💡 Working with ERD:

Zoom: Cmd/Ctrl + Scroll atau klik kanan → “Open Image in New Tab”
Download: Copy code ERD → paste ke Mermaid Live Editor → Download PNG/SVG
Full Guide: Lihat Mermaid Guide untuk tutorial lengkap

🎯 Database Overview

MStore menggunakan MySQL 9.3.0 sebagai primary database dengan struktur multi-tenant merchant-scoped. Total Tables: 120 tables
Total Foreign Keys: 209 relationships
Migration Tool: Atlas HCL
ORM: GORM v1.30

📊 Domain Architecture

Database MStore diorganisir dalam 12 domain utama:

Domain	Tables	Description
Core Multi-Tenant	8	Merchants, Branches, Users, Roles
Transaction & POS	12	Transactions, Payments, Cashier Shifts
Inventory Management	15	Inventory, Warehouse, Stock, Movements
Product & Catalog	10	Products, Bundles, Prices, Addons
Procurement	8	Purchase Orders, Receivings, Suppliers
Production	7	Production Orders, Consumptions, Results
Accounting	12	Chart of Accounts, Journal Entries, AP/AR
Approval System	5	Approval Flows, Requests, Logs
Promotion & Pricing	7	Promotions, Conditions, Rewards
Banking & Finance	6	Bank Reconciliation, Mutations
Audit & Logging	8	Audit Logs, Activity Logs, Notifications
Supporting	22	Regions, Tax Rates, Config, Lookup

📊 Core Entities

1. Multi-Tenant Core

2. Transaction Flow

3. Inventory & Warehouse

4. Inventory Transfer

5. Product & Bundle

6. Approval Flow

7. Accounting & Journal

🔑 Key Relationships

Multi-Tenant Isolation

Every table has merchant_id untuk data isolation:

-- Example query with merchant isolation
SELECT * FROM transactions 
WHERE merchant_id = ? 
  AND branch_id = ?
  AND deleted_at IS NULL;

Foreign Key Constraints

Enforced at Database Level:

-- Example FK constraints
ALTER TABLE transactions
    ADD CONSTRAINT fk_transactions_merchant 
    FOREIGN KEY (merchant_id) REFERENCES merchants(id),
    ADD CONSTRAINT fk_transactions_branch 
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    ADD CONSTRAINT fk_transactions_cashier 
    FOREIGN KEY (cashier_id) REFERENCES users(id);

Unique Constraints

Merchant-Scoped Uniqueness:

-- Unique per merchant
CREATE UNIQUE INDEX uq_merchant_sku 
    ON inventory(merchant_id, inventory_sku);

CREATE UNIQUE INDEX uq_merchant_warehouse 
    ON warehouses(merchant_id, warehouse_code);

CREATE UNIQUE INDEX uq_merchant_transfer 
    ON inventory_transfers(merchant_id, transfer_code);

📈 Indexing Strategy

Performance Indexes

-- Merchant filtering (most queries)
CREATE INDEX idx_merchant ON transactions(merchant_id);
CREATE INDEX idx_merchant ON inventory(merchant_id);
CREATE INDEX idx_merchant ON warehouse_stocks(merchant_id);

-- Composite indexes for common queries
CREATE INDEX idx_merchant_branch 
    ON transactions(merchant_id, branch_id);

CREATE INDEX idx_merchant_warehouse_inventory 
    ON warehouse_stocks(merchant_id, warehouse_id, inventory_id);

-- Status filtering
CREATE INDEX idx_status ON transactions(status);
CREATE INDEX idx_merchant_status 
    ON inventory_transfers(merchant_id, status);

-- Date range queries
CREATE INDEX idx_created_at ON transactions(created_at);
CREATE INDEX idx_movement_date ON inventory_movements(movement_date);

-- SLA monitoring
CREATE INDEX idx_status_due 
    ON approval_requests(status, due_at);

🔍 Common Queries

1. Get Branch Stock Summary

SELECT 
    b.branch_code,
    b.branch_name,
    i.inventory_sku,
    i.inventory_name,
    SUM(ws.qty_on_hand) AS total_on_hand,
    SUM(ws.qty_reserved) AS total_reserved,
    SUM(ws.qty_on_hand - ws.qty_reserved) AS total_available
FROM warehouse_stocks ws
JOIN warehouses w ON ws.warehouse_id = w.id
JOIN branches b ON w.branch_id = b.id
JOIN inventory i ON ws.inventory_id = i.id
WHERE ws.merchant_id = ?
  AND b.branch_id = ?
  AND ws.deleted_at IS NULL
GROUP BY b.branch_code, b.branch_name, i.inventory_sku, i.inventory_name;

2. Transaction with Items

SELECT 
    t.id,
    t.transaction_code,
    t.grand_total,
    t.status,
    ti.product_id,
    p.product_name,
    ti.quantity,
    ti.unit_price,
    ti.subtotal
FROM transactions t
JOIN transaction_items ti ON t.id = ti.transaction_id
JOIN products p ON ti.product_id = p.id
WHERE t.merchant_id = ?
  AND t.id = ?;

3. Pending Approvals for User

SELECT 
    ar.id,
    ar.doc_code,
    ar.doc_type,
    ar.amount,
    ar.current_level,
    ar.total_levels,
    ar.requested_at,
    ar.due_at,
    af.required_role
FROM approval_requests ar
JOIN approval_flows af ON ar.flow_id = af.id
JOIN approvers a ON af.required_role = a.role
WHERE a.user_id = ?
  AND ar.status = 'PENDING'
  AND ar.current_level = af.level_order
  AND a.is_active = TRUE;

4. Inventory Movement History

SELECT 
    im.movement_date,
    im.movement_type,
    im.quantity,
    im.reference_type,
    im.reference_code,
    w.warehouse_name,
    i.inventory_name,
    ib.batch_code
FROM inventory_movements im
JOIN warehouses w ON im.warehouse_id = w.id
JOIN inventory i ON im.inventory_id = i.id
LEFT JOIN inventory_batches ib ON im.inventory_batch_id = ib.id
WHERE im.merchant_id = ?
  AND im.inventory_id = ?
ORDER BY im.movement_date DESC
LIMIT 100;

🛠️ Migration Management

Atlas HCL Structure

third_party/migrations/atlas/schema/
├── 01_core_tenant.my.hcl           # Merchants, Branches, Users
├── 02_rbac.my.hcl                  # Roles, Permissions
├── 03_master_warehouse.my.hcl      # Warehouses
├── 04_master_data_import.my.hcl    # Inventory, Products
├── 05_transactional.my.hcl         # Transactions, Payments
├── 06_approval_flow.my.hcl         # Approval System
├── 07_inventory_flow.my.hcl        # Warehouse Stocks, Movements
├── 08_promotions.my.hcl            # Bundles, Price Lists
└── 09_accounting.my.hcl            # Chart of Accounts, Journals

Running Migrations

# Generate migration diff
atlas migrate diff \
  --dir "file://third_party/migrations/atlas/schema" \
  --to "file://third_party/migrations/atlas/schema" \
  --dev-url "mysql://root:root@localhost:3306/mstore-dev"

# Apply migrations
atlas migrate apply \
  --dir "file://third_party/migrations/atlas/migrations" \
  --url "mysql://root:root@localhost:3306/mstore-monolith"

# Verify schema
atlas schema inspect \
  --url "mysql://root:root@localhost:3306/mstore-monolith"

📊 Database Statistics

Table Sizes (Estimated)

Table	Estimated Rows	Growth Rate
transactions	1M+	High (daily)
transaction_items	5M+	High (daily)
inventory_movements	10M+	High (daily)
warehouse_stocks	100K	Medium
inventory	50K	Low
products	100K	Low
approval_requests	500K	Medium
approval_logs	2M+	Medium
payments	1M+	High (daily)

Storage Requirements

Per Merchant (Average):

Transactions: ~500MB/year
Inventory Movements: ~1GB/year
Audit Logs: ~200MB/year
Total: ~2GB/year per merchant

Retention Policy:

Transactional data: 3 years
Audit logs: 7 years
Archived data: S3/cold storage

💡 Best Practices

DO ✅

Always filter by merchant_id first
Use composite indexes untuk common queries
Implement soft deletes (deleted_at)
Use GORM hooks untuk audit fields
Implement database connection pooling
Use prepared statements (GORM default)
Monitor slow query log
Regular ANALYZE TABLE untuk statistics

DON’T ❌

Jangan skip merchant_id filter
Jangan gunakan SELECT * di production
Jangan skip indexes untuk FK
Jangan hardcode IDs
Jangan skip transaction untuk atomic operations
Jangan allow NULL di FK columns
Jangan skip backup strategy

🔄 Backup & Recovery

Backup Strategy

# Daily full backup
mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  mstore-monolith > backup_$(date +%Y%m%d).sql

# Incremental backup (binary logs)
mysqlbinlog mysql-bin.000001 > incremental.sql

Point-in-Time Recovery

# Restore full backup
mysql mstore-monolith < backup_20251013.sql

# Apply binary logs up to specific time
mysqlbinlog --stop-datetime="2025-10-13 14:30:00" \
  mysql-bin.000001 | mysql -u root -p mstore-monolith

Schema & Design

ERP Schema V2

Operations

Entity Relationship Diagram

Entity Relationship Diagram

🎯 Database Overview

📊 Domain Architecture

📊 Core Entities

1. Multi-Tenant Core

2. Transaction Flow

3. Inventory & Warehouse

4. Inventory Transfer

5. Product & Bundle

6. Approval Flow

7. Accounting & Journal

🔑 Key Relationships

Multi-Tenant Isolation

Foreign Key Constraints

Unique Constraints

📈 Indexing Strategy

Performance Indexes

🔍 Common Queries

1. Get Branch Stock Summary

2. Transaction with Items

3. Pending Approvals for User

4. Inventory Movement History

🛠️ Migration Management

Atlas HCL Structure

Running Migrations

📊 Database Statistics

Table Sizes (Estimated)

Storage Requirements

💡 Best Practices

DO ✅

DON’T ❌

🔄 Backup & Recovery

Backup Strategy

Point-in-Time Recovery

Schema & Design

ERP Schema V2

Operations

Documentation Index

​Entity Relationship Diagram

​🎯 Database Overview

​📊 Domain Architecture

​📊 Core Entities

​1. Multi-Tenant Core

​2. Transaction Flow

​3. Inventory & Warehouse

​4. Inventory Transfer

​5. Product & Bundle

​6. Approval Flow

​7. Accounting & Journal

​🔑 Key Relationships

​Multi-Tenant Isolation

​Foreign Key Constraints

​Unique Constraints

​📈 Indexing Strategy

​Performance Indexes

​🔍 Common Queries

​1. Get Branch Stock Summary

​2. Transaction with Items

​3. Pending Approvals for User

​4. Inventory Movement History

​🛠️ Migration Management

​Atlas HCL Structure

​Running Migrations

​📊 Database Statistics

​Table Sizes (Estimated)

​Storage Requirements

​💡 Best Practices

​DO ✅

​DON’T ❌

​🔄 Backup & Recovery

​Backup Strategy

​Point-in-Time Recovery

Entity Relationship Diagram

🎯 Database Overview

📊 Domain Architecture

📊 Core Entities

1. Multi-Tenant Core

2. Transaction Flow

3. Inventory & Warehouse

4. Inventory Transfer

5. Product & Bundle

6. Approval Flow

7. Accounting & Journal

🔑 Key Relationships

Multi-Tenant Isolation

Foreign Key Constraints

Unique Constraints

📈 Indexing Strategy

Performance Indexes

🔍 Common Queries

1. Get Branch Stock Summary

2. Transaction with Items

3. Pending Approvals for User

4. Inventory Movement History

🛠️ Migration Management

Atlas HCL Structure

Running Migrations

📊 Database Statistics

Table Sizes (Estimated)

Storage Requirements

💡 Best Practices

DO ✅

DON’T ❌

🔄 Backup & Recovery

Backup Strategy

Point-in-Time Recovery