# Database Analysis — ERP Gudang (MySQL → PostgreSQL)

Source: `database/migrations/` + `erp-gudang.sql` (mysqldump snapshot).

## 1. Overview

43 tables total:
- **20 core business** — products, categories, units, suppliers, customers, warehouses, stocks, stock_movements, stock_opnames, stock_opname_items, stock_transfers, stock_transfer_items, sales_orders, sales_order_items, purchase_orders, purchase_order_items, goods_receipts, goods_receipt_items, invoices, payments
- **6 finance/support** — cashflows, vouchers, activity_logs, roles, personal_access_tokens, sessions
- **5 HR** — employees, attendances, payrolls, leaves, performances
- **4 sales tracking** — sales_visits, sales_locations, sales_activities, sales_routes
- **8 framework** — users, migrations, cache, cache_locks, jobs, job_batches, failed_jobs, password_reset_tokens

## 2. Naming / typing conventions

| Laravel pattern | Meaning | Target (PostgreSQL) |
|---|---|---|
| `bigint(20) UNSIGNED` PK, `id` | Auto-increment PK | `BIGSERIAL` |
| `foreignId(...)->constrained()` | FK | `BIGINT REFERENCES ...` |
| `enum(...)` | DB-level enum | `TEXT` + `CHECK (col IN (...))` |
| `decimal(15,2)` | Money | `NUMERIC(15,2)` |
| `decimal(12,4)` | Stock qty | `NUMERIC(12,4)` |
| `decimal(10,8)`/`(11,8)` | GPS lat/lng | `NUMERIC` |
| `tinyint(1)` | Boolean | `BOOLEAN` |
| `timestamp` | Datetime | `TIMESTAMPTZ` (or `TIMESTAMP`) |
| `longtext` JSON | JSON field | `JSONB` |
| generated STORED column | `stock_opname_items.difference` | `GENERATED ALWAYS AS (physical_quantity - system_quantity) STORED` |
| softDeletes | `deleted_at` | `deleted_at TIMESTAMPTZ NULL` |

## 3. Core business tables — columns (authoritative from dump)

### products
`id, code(50,uniq), name(200), description(text), category_id(FK), unit_id(FK), purchase_price(15,2), selling_price(15,2), current_stock(int, NEVER WRITTEN), min_stock(12,4), is_active(bool), image, deleted_at, timestamps`

### categories / units / suppliers / customers / warehouses
- categories: `id, name(100,uniq), description, is_active`
- units: `id, name(50,uniq), symbol(20), description`
- suppliers: `id, code(30,uniq), name, contact_person, phone, email, address, city, tax_number, bank_name, bank_account, credit_limit(15,2), payment_terms(int,days=30), is_active, deleted_at`
- customers: `id, code(30,uniq), name, contact_person, phone, email, address, city, tax_number, credit_limit(15,2), payment_terms, customer_type ENUM(retail,wholesale,distributor), assigned_sales_id(FK employees, nullable), is_active, deleted_at`
- warehouses: `id, code(20,uniq), name, address, city, phone, manager_id(FK employees, nullable), is_active`

### stocks
`id, product_id(FK), warehouse_id(FK), quantity(12,4) default 0, reserved_quantity(12,4) default 0, last_updated_at, timestamps; UNIQUE(product_id, warehouse_id)`

### stock_movements
`id, movement_number(50,uniq), product_id, warehouse_id, movement_type ENUM(stock_in,stock_out,transfer_in,transfer_out,adjustment,opname), quantity(12,4), quantity_before(12,4), quantity_after(12,4), reference_type, reference_id, notes, created_by(FK users)`

### stock_opnames / stock_opname_items
- opnames: `id, opname_number(50,uniq), warehouse_id, opname_date, status ENUM(draft,in_progress,completed,cancelled), notes, created_by, approved_by`
- items: `id, stock_opname_id, product_id, system_quantity(12,4), physical_quantity(12,2) NULL, difference(12,4) GENERATED STORED = physical - system, notes`

### stock_transfers / stock_transfer_items
- transfers: existing fields plus `voucher_number(unique), dispatched_at, voucher_snapshot(jsonb), lifecycle_version`; status adds `partial,discrepancy` (`approved` is legacy-only).
- items: existing requested/sent/received plus `quantity_transit,quantity_damaged,quantity_lost NUMERIC(12,4)` nonnegative. Received is good stock only; damaged is separately tracked non-operational quarantine.
- `stock_transfer_events`: action/actor/time/reason/payload audit with unique `(stock_transfer_id,request_key)` for receipt/reconciliation idempotency. Dispatch/receipt movements reference same transfer ID. Voucher snapshot preserves company and all sent item identities/units/quantities.
- Migration 000135 retains old balances/history with lifecycle version 0 and zero new counters, never replays stock. Old non-draft shipments require evidence-backed reconciliation. Recovery restores application/database backup together; do not drop live transit/audit data.

### sales_orders / sales_order_items
- sales_orders: `id, so_number(50,uniq), customer_id, warehouse_id(NULLABLE), sales_id(FK employees NULL), order_date, delivery_date NULL, status ENUM(draft,confirmed,picking,shipped,delivered,invoiced,paid,cancelled), order_type ENUM(regular,pos,consignment), subtotal, tax_amount, discount_amount, total_amount, paid_amount, remaining_amount, shipping_cost (all 15,2), shipping_address, notes`
- sales_order_items: `id, sales_order_id, product_id, quantity(12,2!), unit_price, discount_percent(5,2), discount_amount, subtotal`

> NOTE: `sales_order_items.quantity` is `decimal(12,2)` in the dump, not `(12,4)`. Keep as-is.

### purchase_orders / purchase_order_items
- purchase_orders: `id, po_number(50,uniq), supplier_id, warehouse_id, created_by(FK users), order_date, expected_date NULL, due_date NULL, status ENUM(draft,approved,sent,partial,received,paid,cancelled) DEFAULT draft, subtotal, tax_amount, discount_amount, total_amount, paid_amount, remaining_amount (15,2), notes`
- purchase_order_items: `id, purchase_order_id, product_id, quantity(12,4), received_quantity(12,4) default 0, unit_price(15,2), discount(15,2), subtotal(15,2)`

### goods_receipts / goods_receipt_items
- receipts: `id, gr_number(50,uniq), purchase_order_id, warehouse_id, received_by(FK users), received_date, notes, status ENUM(draft,confirmed,cancelled) default draft`
- items: `id, goods_receipt_id, purchase_order_item_id, product_id, quantity_ordered(12,4), quantity_received(12,4), batch_number(50), expired_date(date)`

### invoices
`id, invoice_number(50,uniq), sales_order_id, customer_id, invoice_date, due_date, status ENUM(unpaid,partial,paid,overdue,cancelled), subtotal, tax_amount, total_amount, paid_amount, remaining_amount (15,2), notes`

### payments
`id, payment_number(50,uniq), payable_type, payable_id (morph), payment_date, amount(15,2), payment_method ENUM(cash,transfer,giro,cheque), bank_name, bank_account, reference_number, notes, created_by`

### cashflows
`id, transaction_number(50,uniq), type ENUM(income,expense), category ENUM(sales,purchase,salary,operational,other), transaction_date, amount(15,2), description, reference_type, reference_id, created_by`

### vouchers
`id, voucher_number(uniq), type default payment, description, voucher_date, amount(15,2), status default available, created_by`

### activity_logs
`id, user_id, module, action, description, model_type, model_id, old_values(JSON), new_values(JSON), ip_address, user_agent`

## 4. HR + sales tracking tables (columns abbreviated)
- **employees**: `id, employee_number(30,uniq), user_id(FK NULL), name, nik(20,uniq), department ENUM(management,sales,finance,warehouse,hr,it,operations), position, employment_type ENUM(permanent,contract,freelance,intern), join_date, end_date, basic_salary(15,2), phone, email, address, city, bank_name, bank_account, bank_account_name, photo, is_active, deleted_at`
- **attendances**: `id, employee_id, attendance_date, clock_in(ts), clock_out(ts), clock_in_lat/lng, clock_out_lat/lng, status ENUM(present,late,absent,leave,holiday,wfh), work_hours(10,2), overtime_hours(10,2), notes, photo_clock_in/out; UNIQUE(employee_id, attendance_date)`
- **payrolls**: `id, payroll_number, employee_id, period_month, period_year, basic_salary, allowances, overtime_pay, bonus, deductions, bpjs_ketenagakerjaan, bpjs_kesehatan, pph21, net_salary (all 15,2), payment_date, status ENUM(draft,approved,paid), notes; UNIQUE(employee_id, period_month, period_year)`
- **leaves**: `id, employee_id, leave_type ENUM(annual,sick,maternity,paternity,emergency,unpaid), start_date, end_date, total_days, reason, status ENUM(pending,approved,rejected,cancelled), approved_by, approved_at, notes`
- **performances**: `id, employee_id, evaluator_id, period_month, period_year, kpi_score, attitude_score, teamwork_score, achievement_score, total_score (5,2), grade ENUM(A,B,C,D), achievements, improvements, notes; UNIQUE(employee_id, period_month, period_year)`
- **sales_visits**: `id, sales_id(FK employees), customer_id, visit_date, check_in_time, check_out_time, check_in_lat/lng, check_out_lat/lng, purpose, result, notes, sales_order_id, status ENUM(planned,in_progress,completed,cancelled), photo_check_in/out`
- **sales_activities**: `id, sales_id, sales_visit_id NULL, activity_date, activity_type ENUM(call,meeting,demo,proposal,negotiation,closing,follow_up), description, result, follow_up_date, follow_up_notes`
- **sales_routes**: `id, sales_id, route_date, name, planned_customers(JSON), status ENUM(planned,in_progress,completed), notes`
- **sales_locations**: `id, sales_id, latitude(10,8), longitude(11,8), accuracy(8,2), speed(8,2), heading(8,2), recorded_at(ts, default CURRENT), battery_level(tinyint), timestamps`

## 5. Auth / system tables
- **users**: `id, name, email(uniq), email_verified_at, password, remember_token, role ENUM(admin,sales,finance,warehouse,hr_manager,hr_staff,employee,system) default employee, permissions(JSON), is_active(bool), last_login_at, avatar`
- **personal_access_tokens**: `id, tokenable_type, tokenable_id, name, token(64,uniq), abilities, last_used_at, expires_at, timestamps` (Sanctum)
- **roles**: decorative (unused by authz)
- **sessions / cache / cache_locks / jobs / job_batches / failed_jobs / password_reset_tokens**: Laravel framework tables

## 6. Actual data (from dump) — for parity checksums

| Table | Rows | Notes |
|---|---|---|
| users | 7 | admin@erp.com, sales@erp.com, finance@erp.com, gudang@erp.com, hr@erp.com, system@erp.com + agunghr@erp.com |
| employees | 6 | one per seed user (super admin/management, etc.) |
| categories | 7 | Minuman, Makanan, Sembako, Toiletries, Household, Elektronik, Pakaian |
| units | 9 | Karton/ktn, Pcs/pcs, Lusin/lsn, Kg/kg, Liter/ltr, Rim/rim, Box/box, Sak/sak, dam/dm |
| products | 7 | PRD-001..005 + PRD-012, MAK-20260619-001; current_stock all 0 |
| warehouses | 3 | GDG-01 Jakarta, GDG-02 Bandung, GDG-03 Surabaya |
| suppliers | 2 | SUPP-0001, SUP-002 |
| customers | 2 | CUS-001 (distributor, credit 100M), CUS-002 (retail) |
| stocks | 3 | **product 4 = -1.0000 (negative!)**; product 1 reserved 150 |
| stock_movements | 9 | stock_in x2, stock_out x7 |
| sales_orders | 6 | statuses: invoiced, confirmed x3, paid, invoiced |
| sales_order_items | 7 | |
| invoices | 3 | partial, paid, unpaid |
| payments | 2 | transfer method |
| cashflows | 3 | income x2 (correct reference), expense x1 (reference NULL — payDebt bug) |
| purchase_orders | 1 | PO-20260318-0001, paid, 1,387,500 total |
| purchase_order_items | 1 | product 6, qty 10 |
| goods_receipts | 1 | GR-20260318-0001 |
| goods_receipt_items | 1 | product 6 qty 10 |
| stock_opnames | 5 | 4 in_progress, 1 completed |
| stock_opname_items | 24 | |
| stock_transfers | 1 | TRF-20260620-0001, draft |
| stock_transfer_items | 1 | product 1 qty 110 |
| vouchers | 1 | PV-20260712-0001, used |
| roles | 0 | decorative |

## 7. Indexes / constraints (existing)

Standard PKs + unique numbers on all document tables. Notable composite indexes:
- `sales_orders (customer_id, status, order_date)`
- `sales_orders (warehouse_id)`, `sales_orders (sales_id)`
- `invoices (customer_id, status, due_date)`
- `purchase_orders (supplier_id, status)`
- `payments (payable_type, payable_id)`
- `cashflows (type, transaction_date)`
- `activity_logs (module, action, created_at)` and `(model_type, model_id)`
- `stock_movements (reference_type, reference_id)`
- `sales_locations (sales_id, recorded_at)`, `sales_visits (sales_id, visit_date)`, `sales_routes (sales_id, route_date)`
- `attendances UNIQUE(employee_id, attendance_date)`, `payrolls UNIQUE(employee_id, period_month, period_year)`
- `stocks UNIQUE(product_id, warehouse_id)`

## 8. PostgreSQL migration assessment

- **Schema mapping is mechanical** (Section 2) — no domain redesign needed. Column names/types map 1:1.
- **Enums → CHECK constraints.** Do NOT use PG `CREATE TYPE` unless the domain needs it; TEXT+CHECK is reversible and matches MySQL enum semantics closely enough.
- **Money:** `NUMERIC(15,2)` everywhere. No floats.
- **`sales_order_items.quantity`** stays `NUMERIC(12,2)` (matches dump).
- **Generated column** `stock_opname_items.difference` must be recreated as PG `GENERATED ALWAYS ... STORED`.
- **JSON** `planned_customers` (sales_routes) and `permissions` (users) → `JSONB`.
- **Sequences for document numbering** replace the `count()+1` hooks: per-document-type daily counters (see migration-plan.md §Numbering).
- **Data load** from `erp-gudang.sql` must preserve exact IDs (FK relationships), re-map enum strings as-is, and respect FK order (categories→units→products→suppliers→customers→warehouses→users→employees→stocks→…).
- `cache`, `cache_locks`, `sessions`, `jobs` tables are **framework tables** — migrate for parity but unused by the Go backend (Redis replaces them).
- Laravel's `migrations` table content is not needed by Go. Atlas tracks applied
  Go-backend migrations in `atlas_schema_revisions`; keep the Laravel list only
  as migration reference data.