Database Schema & Models

Relevant source files

The following files were used as context for generating this wiki page:

• prisma/schema.prisma
• src/schemas/user.ts
• src/schemas/warehouse.ts
• src/schemas/product.ts
• src/schemas/customer.ts
• src/schemas/armada.ts
• prisma/seed.ts
• CLAUDE.md

The Outmanage database is built on a MySQL foundation managed through the Prisma ORM. It facilitates a comprehensive outbound logistics system, covering inventory management across multiple warehouses, customer delivery orders, fleet operations, and a complex weighing system for shipments. The schema is designed to support Role-Based Access Control (RBAC) and detailed audit logging for nearly every entity in the system.

Sources: prisma/schema.prisma:1-10, CLAUDE.md:23-35

Core ER Diagram

The following diagram illustrates the primary relationships between the central entities of the system, including Users, Logistics (Delivery Orders/Shipments), and Inventory (Warehouses/Products).

User & Access Control Models

The system implements a granular RBAC model where users are assigned roles, and roles are linked to specific permissions across resources and actions.

User

The User model represents system actors. It includes standard authentication fields and optional associations with a Warehouse.

• Key Fields: id (UUID), email (Unique), password, roleId, warehouseId.
• Timezone Note: The system is configured to operate in Asia/Jakarta (GMT+7), and the seed process reflects this for timestamps.

Sources: prisma/schema.prisma:14-36, src/schemas/user.ts:13-33, prisma/seed.ts:180-195

RBAC (Role & Permission)

Access is controlled via three primary models:

1. Role: Defines a group of permissions (e.g., "Pemilik").
2. Permission: Defines an action (CREATE, READ, UPDATE, DELETE, etc.) on a resource (user, product, shipment, etc.).
3. RolePermission: A join table linking roles to their allowed permissions.

Sources: prisma/schema.prisma:50-84, prisma/seed.ts:47-150

Inventory & Fleet Models

Warehouse & Product

Inventory is partitioned by Warehouse. Each Product is owned by exactly one warehouse.

• Warehouse: Identified by a unique code and name.
• Product: Includes name, satuan (unit of measurement), and an optional legacy ID id_sl.

Sources: prisma/schema.prisma:86-125, src/schemas/product.ts:13-34, src/schemas/warehouse.ts:9-25

Customer & Armada

• Customer: Represents the destination for shipments.
• Armada: Represents vehicles in the fleet. It includes plateNumber used for AI-powered verification during shipment.

Sources: prisma/schema.prisma:141-168, src/schemas/armada.ts:14-30, CLAUDE.md:70-75

Logistics & Shipment Models

The logistics flow involves the transition of requirements from DeliveryOrder to Shipment.

Delivery Order (DO)

Represents a customer's request for products.

• Status Lifecycle: PENDING → PROSES → SELESAI.
• Items: Track quantity, pendingQuantity, and completedQuantity.

Sources: prisma/schema.prisma:185-214, comprehensive-swagger-api.json:440-470

Shipment & Shipment Item

A Shipment is a physical movement of goods.

• Verification: Shipments can be verified via platePhoto and isVerified flags.
• ShipmentItem: Links a specific Shipment to a DeliveryOrder and Product. It tracks requestedQuantity vs weightedQuantity.

Sources: prisma/schema.prisma:229-281, CLAUDE.md:37-43

Weighing System

The system supports bulk weighing where multiple items of the same product can be weighed together.

• ShipmentChosenProduct: Represents a product selected for weighing in a shipment.
• ShipmentChosenProductWeighing: Records weights (gross, net, tare) and timing.
• NotaTimbangan: A document generated for a specific weighing record.

Sources: prisma/schema.prisma:304-338, CLAUDE.md:38-40

Audit Logging Models

The schema includes a comprehensive audit trail. Every major entity has a corresponding log model to track changes (oldData vs newData).

Log Model	Entity Tracked	Triggered Actions
UserLog	User Profile/Auth	CREATE, UPDATE, DELETE
WarehouseLog	Warehouse Config	CREATE, UPDATE, DELETE
ProductLog	Inventory Items	CREATE, UPDATE, DELETE
CustomerLog	Client Profiles	CREATE, UPDATE, DELETE
ArmadaLog	Vehicle Details	CREATE, UPDATE, DELETE
DeliveryOrderLog	Orders	Status changes, Item updates
ShipmentLog	Shipments	Weighing, Verification, Completion

Sources: prisma/schema.prisma:38-48, prisma/schema.prisma:98-111, prisma/schema.prisma:127-139, src/schemas/armadaLog.ts:24-40, src/schemas/customerLog.ts:24-40

Enumerated Types

The schema enforces data integrity through several core enums:

Enum Name	Values	Purpose
STATUS	PENDING, PROSES, SELESAI, CANCEL	Global lifecycle for DOs and Shipments
ACTION	CREATE, UPDATE, DELETE, RESTORE	Logged system operations
PERMISSION_ACTION	CREATE, READ, WEIGH, VERIFY_PLATE, etc.	Granular permissions for RBAC
SHIPMENT_TYPE	ANTAR, JEMPUT	Direction of logistics flow
WEIGHING_METHOD	MANUAL, VENDOR	Origin of weight data

Sources: prisma/schema.prisma:340-410, prisma/seed.ts:60-100

Summary

The Outmanage database schema provides a robust structure for complex logistics. By decoupling DeliveryOrders from physical Shipments while maintaining item-level links, the system allows for flexible fulfillment strategies (e.g., partial shipments or bulk weighing). The pervasive logging and RBAC ensures security and accountability across all warehouse and fleet operations.

Sources: prisma/schema.prisma:1-410, CLAUDE.md:23-55