Konsep Utama: E-commerce Lokal dengan Fitur Khusus
Website ini merupakan platform e-commerce yang difokuskan untuk melayani satu wilayah tertentu (dalam kasus ini Desa Bukit Lembah Subur, Kerumutan, Pelalawan, Riau) dengan fitur-fitur khusus seperti:
1. Multi-Role User System
- Customer: Pembeli biasa
- Vendor: Penjual/pedagang lokal
- Vendor Moderator: Staf/karyawan vendor
- Courier: Kurir lokal (bisa dari desa setempat)
- Admin: Pengelola sistem
2. Fitur Inti
- Pasar Digital Lokal:
- Vendor bisa mendaftar dan menjual produk (dengan sistem approval)
- Produk bisa memiliki varian (warna/ukuran)
- Kategori produk bertingkat (parent-child)
- Logistik Terintegrasi:
- Sistem kurir lokal dengan tarif pengiriman fleksibel (diset via JSON)
- Tracking pengiriman lengkap dengan riwayat status
- Pembayaran Hybrid:
- Transfer bank
- Saldo digital (top-up & pembayaran)
- Komisi otomatis untuk platform
- Manajemen Vendor:
- Vendor bisa menambahkan staf/moderator dengan hak akses spesifik
- Approval sistem untuk vendor baru
3. Karakteristik Unik
- Desa Digital:
- Khusus melayani 1 desa (Bukit Lembah Subur)
- Alamat pengiriman detail sampai level RT/RW dan dusun
- Kurir Warga:
- Warga setempat bisa mendaftar sebagai kurir
- Bisa buat voucher pengiriman (jika diizinkan admin)
- Sosial Ekonomi:
- Transaksi menggunakan saldo digital
- Sistem komisi untuk platform
4. Flow Bisnis
- Vendor menjual produk → Customer memesan
- Courier mengambil pesanan → Antar ke alamat spesifik (termasuk dusun)
- Pembayaran via transfer/saldo digital
- Platform dapat komisi dari setiap transaksi
5. Teknologi Pendukung
- File Manager terpusat untuk gambar produk/logo
- Voucher untuk diskon produk/pengiriman
- Review System untuk produk dan kurir
Contoh Use Case
- Ibu Rumah Tangga memesan sayur via website → Petani Lokal (vendor) menerima order → Pemuda Desa (courier) mengantar ke alamat spesifik di Dusun 3.
- Warung Kelontong daftar sebagai vendor → Staf warung (vendor moderator) bisa upload produk dan kelola pesanan.
- Admin Desa bisa monitor seluruh transaksi dan aliran dana digital warga.
Value Proposition
- Untuk Warga: Belanja lebih praktis, dukung ekonomi lokal
- Untuk Pedagang: Jangkauan pasar lebih luas
- Untuk Desa: Digitalisasi transaksi ekonomi
Website ini menggabungkan konsep e-commerce, logistik hyperlocal, dan ekosistem digital desa dalam satu platform terpadu.
-- users table (Tabel utama untuk semua jenis pengguna, termasuk saldo dan status aktif)
CREATE TABLE `users` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) UNIQUE NOT NULL,
`email_verified_at` TIMESTAMP NULL,
`password` VARCHAR(255) NULL, -- Nullable for social login
`phone` VARCHAR(20) NULL,
`role` ENUM('customer', 'vendor', 'admin', 'courier', 'vendor_moderator') NOT NULL,
`avatar_file_id` BIGINT UNSIGNED NULL, -- Foreign key ke tabel files jika avatar dikelola di File Manager
`is_active` BOOLEAN NOT NULL DEFAULT TRUE, -- Untuk fitur aktif/nonaktif
`balance` DECIMAL(15, 2) NOT NULL DEFAULT 0.00, -- Saldo digital, semua user bisa punya
`remember_token` VARCHAR(100) NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`avatar_file_id`) REFERENCES `files`(`id`) ON DELETE SET NULL
);
-- social_accounts table (Untuk integrasi login media sosial)
CREATE TABLE `social_accounts` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`provider_name` VARCHAR(255) NOT NULL,
`provider_id` VARCHAR(255) NOT NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE (`provider_name`, `provider_id`),
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);
-- vendors table (Detail profil vendor)
CREATE TABLE `vendors` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED UNIQUE NOT NULL,
`store_name` VARCHAR(255) NOT NULL,
`store_description` TEXT NULL,
`store_logo_file_id` BIGINT UNSIGNED NULL, -- Foreign key ke tabel files untuk logo toko
`bank_details` JSON NULL, -- Nomor rekening, nama bank, dll.
`approval_status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`store_logo_file_id`) REFERENCES `files`(`id`) ON DELETE SET NULL
);
-- vendor_moderators table (Untuk staf/moderator vendor)
CREATE TABLE `vendor_moderators` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`vendor_id` BIGINT UNSIGNED NOT NULL, -- ID vendor pemilik (merujuk ke vendors.id)
`permissions` JSON NULL, -- Menyimpan hak akses spesifik
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`vendor_id`) REFERENCES `vendors`(`id`) ON DELETE CASCADE
);
-- couriers table (Detail profil kurir)
CREATE TABLE `couriers` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED UNIQUE NOT NULL,
`vehicle_type` VARCHAR(255) NULL,
`license_number` VARCHAR(255) NULL,
`shipping_rates` JSON NULL, -- Menyimpan struktur tarif kurir
`can_create_shipping_voucher` BOOLEAN NOT NULL DEFAULT FALSE,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);
-- files table (untuk menyimpan metadata file yang diunggah oleh user)
CREATE TABLE `files` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL,
`path` VARCHAR(255) NOT NULL,
`url` VARCHAR(255) NULL,
`mime_type` VARCHAR(100) NOT NULL,
`size` BIGINT UNSIGNED NOT NULL,
`disk` VARCHAR(50) NOT NULL DEFAULT 'public',
`caption` TEXT NULL,
`is_public` BOOLEAN NOT NULL DEFAULT FALSE,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);
-- villages table (Hanya akan ada 1 entri: Bukit Lembah Subur)
CREATE TABLE `villages` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL UNIQUE,
`district` VARCHAR(255) DEFAULT 'Kerumutan',
`regency` VARCHAR(255) DEFAULT 'Pelalawan',
`province` VARCHAR(255) DEFAULT 'Riau',
`country` VARCHAR(255) DEFAULT 'Indonesia',
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- dusuns table (Dusun yang diizinkan di Desa Bukit Lembah Subur)
CREATE TABLE `dusuns` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`village_id` BIGINT UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`village_id`) REFERENCES `villages`(`id`) ON DELETE CASCADE,
UNIQUE (`village_id`, `name`)
);
-- addresses table (Alamat pengiriman milik pengguna)
CREATE TABLE `addresses` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`label` VARCHAR(255) NULL,
`recipient_name` VARCHAR(255) NOT NULL,
`phone` VARCHAR(20) NOT NULL,
`blok_ed` VARCHAR(50) NULL,
`jalan` VARCHAR(255) NOT NULL,
`rt` VARCHAR(10) NOT NULL,
`rw` VARCHAR(10) NOT NULL,
`dusun_id` BIGINT UNSIGNED NOT NULL,
`postal_code` VARCHAR(10) NULL,
`is_default` BOOLEAN NOT NULL DEFAULT FALSE,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`dusun_id`) REFERENCES `dusuns`(`id`) ON DELETE RESTRICT
);
-- categories table
CREATE TABLE `categories` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`slug` VARCHAR(255) UNIQUE NOT NULL,
`parent_id` BIGINT UNSIGNED NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`parent_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL
);
-- products table
CREATE TABLE `products` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`vendor_id` BIGINT UNSIGNED NOT NULL,
`category_id` BIGINT UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL,
`slug` VARCHAR(255) UNIQUE NOT NULL,
`description` TEXT NULL,
`base_price` DECIMAL(15, 2) NOT NULL,
`status` ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft',
`type` ENUM('single', 'variable') NOT NULL DEFAULT 'single',
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`vendor_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE RESTRICT
);
-- product_images table
CREATE TABLE `product_images` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`product_id` BIGINT UNSIGNED NOT NULL,
`file_id` BIGINT UNSIGNED NOT NULL,
`is_thumbnail` BOOLEAN NOT NULL DEFAULT FALSE,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`file_id`) REFERENCES `files`(`id`) ON DELETE CASCADE
);
-- attributes table (Misal: Warna, Ukuran, Material)
CREATE TABLE `attributes` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL UNIQUE,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- attribute_values table (Misal: Merah, Biru, S, M, L)
CREATE TABLE `attribute_values` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`attribute_id` BIGINT UNSIGNED NOT NULL,
`value` VARCHAR(255) NOT NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`attribute_id`) REFERENCES `attributes`(`id`) ON DELETE CASCADE,
UNIQUE (`attribute_id`, `value`)
);
-- product_variants table (Jika product.type = 'variable')
CREATE TABLE `product_variants` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`product_id` BIGINT UNSIGNED NOT NULL,
`sku` VARCHAR(255) UNIQUE NULL,
`name` VARCHAR(255) NULL, -- Nama varian (e.g., "Merah, XL")
`price` DECIMAL(15, 2) NOT NULL,
`stock` INT UNSIGNED NOT NULL DEFAULT 0,
`image_file_id` BIGINT UNSIGNED NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`image_file_id`) REFERENCES `files`(`id`) ON DELETE SET NULL
);
-- product_variant_attribute_value (Pivot table untuk menghubungkan varian dengan nilai atributnya)
CREATE TABLE `product_variant_attribute_value` (
`product_variant_id` BIGINT UNSIGNED NOT NULL,
`attribute_value_id` BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (`product_variant_id`, `attribute_value_id`),
FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`attribute_value_id`) REFERENCES `attribute_values`(`id`) ON DELETE CASCADE
);
-- orders table (Pesanan yang dibuat oleh pengguna mana pun)
CREATE TABLE `orders` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`address_id` BIGINT UNSIGNED NOT NULL,
`order_number` VARCHAR(255) UNIQUE NOT NULL,
`total_amount` DECIMAL(15, 2) NOT NULL,
`shipping_cost` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
`discount_amount` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
`payment_status` ENUM('pending', 'paid', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
`order_status` ENUM('pending', 'processing', 'ready_to_ship', 'shipped', 'delivered', 'cancelled', 'returned') NOT NULL DEFAULT 'pending',
`shipping_method` VARCHAR(255) NULL,
`courier_id` BIGINT UNSIGNED NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
FOREIGN KEY (`address_id`) REFERENCES `addresses`(`id`) ON DELETE RESTRICT,
FOREIGN KEY (`courier_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
);
-- order_items table (Detail produk dalam pesanan)
CREATE TABLE `order_items` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`order_id` BIGINT UNSIGNED NOT NULL,
`product_id` BIGINT UNSIGNED NOT NULL,
`product_variant_id` BIGINT UNSIGNED NULL,
`vendor_id` BIGINT UNSIGNED NOT NULL,
`quantity` INT UNSIGNED NOT NULL,
`price_per_unit` DECIMAL(15, 2) NOT NULL,
`subtotal` DECIMAL(15, 2) NOT NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE RESTRICT,
FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants`(`id`) ON DELETE RESTRICT,
FOREIGN KEY (`vendor_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT
);
-- payments table (Log transaksi pembayaran)
CREATE TABLE `payments` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`order_id` BIGINT UNSIGNED UNIQUE NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`transaction_id` VARCHAR(255) UNIQUE NOT NULL,
`method` ENUM('bank_transfer', 'credit_card', 'digital_balance', 'other') NOT NULL,
`amount` DECIMAL(15, 2) NOT NULL,
`status` ENUM('pending', 'completed', 'failed', 'refunded') NOT NULL,
`paid_at` TIMESTAMP NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT
);
-- transactions table (Log semua transaksi saldo digital)
CREATE TABLE `transactions` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`type` ENUM('topup', 'payment', 'refund', 'withdrawal', 'commission_payout') NOT NULL,
`amount` DECIMAL(15, 2) NOT NULL,
`current_balance` DECIMAL(15, 2) NOT NULL,
`reference_id` BIGINT UNSIGNED NULL,
`reference_type` VARCHAR(50) NULL,
`description` TEXT NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT
);
-- shipments table (Detail pengiriman per pesanan)
CREATE TABLE `shipments` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`order_id` BIGINT UNSIGNED UNIQUE NOT NULL,
`courier_id` BIGINT UNSIGNED NULL,
`tracking_number` VARCHAR(255) NULL,
`status` ENUM('pending', 'picked_up', 'in_transit', 'delivered', 'failed_delivery', 'returned') NOT NULL DEFAULT 'pending',
`pickup_address` TEXT NULL,
`delivery_address` TEXT NULL,
`estimated_delivery_at` TIMESTAMP NULL,
`actual_delivery_at` TIMESTAMP NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`courier_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
);
-- shipment_history table (Log status perubahan pengiriman)
CREATE TABLE `shipment_history` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`shipment_id` BIGINT UNSIGNED NOT NULL,
`status` VARCHAR(255) NOT NULL,
`location` VARCHAR(255) NULL,
`description` TEXT NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`) ON DELETE CASCADE
);
-- reviews table
CREATE TABLE `reviews` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`product_id` BIGINT UNSIGNED NULL,
`courier_id` BIGINT UNSIGNED NULL,
`order_id` BIGINT UNSIGNED NOT NULL,
`rating` TINYINT UNSIGNED NOT NULL,
`comment` TEXT NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`courier_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
CHECK (
(`product_id` IS NOT NULL AND `courier_id` IS NULL) OR
(`product_id` IS NULL AND `courier_id` IS NOT NULL)
),
UNIQUE (`user_id`, `order_id`, `product_id`),
UNIQUE (`user_id`, `order_id`, `courier_id`)
);
-- commissions table (Komisi platform dari penjualan vendor)
CREATE TABLE `commissions` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`vendor_id` BIGINT UNSIGNED NOT NULL,
`order_item_id` BIGINT UNSIGNED NOT NULL,
`commission_percentage` DECIMAL(5, 2) NOT NULL,
`commission_amount` DECIMAL(15, 2) NOT NULL,
`status` ENUM('pending', 'paid', 'cancelled') NOT NULL DEFAULT 'pending',
`payout_transaction_id` BIGINT UNSIGNED NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`vendor_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
FOREIGN KEY (`order_item_id`) REFERENCES `order_items`(`id`) ON DELETE RESTRICT,
FOREIGN KEY (`payout_transaction_id`) REFERENCES `transactions`(`id`) ON DELETE SET NULL
);
-- vouchers table
CREATE TABLE `vouchers` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`code` VARCHAR(255) UNIQUE NOT NULL,
`name` VARCHAR(255) NULL,
`description` TEXT NULL,
`type` ENUM('product_discount', 'shipping_discount') NOT NULL,
`value` DECIMAL(15, 2) NOT NULL,
`value_type` ENUM('fixed', 'percentage') NOT NULL,
`start_date` TIMESTAMP NOT NULL,
`end_date` TIMESTAMP NOT NULL,
`usage_limit` INT UNSIGNED NULL,
`used_count` INT UNSIGNED NOT NULL DEFAULT 0,
`min_order_amount` DECIMAL(15, 2) NULL,
`applies_to` JSON NULL,
`created_by_user_id` BIGINT UNSIGNED NOT NULL,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`created_by_user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT
);
-- order_vouchers table (Pivot table untuk melacak voucher yang digunakan pada pesanan)
CREATE TABLE `order_vouchers` (
`order_id` BIGINT UNSIGNED NOT NULL,
`voucher_id` BIGINT UNSIGNED NOT NULL,
`discount_amount` DECIMAL(15, 2) NOT NULL,
PRIMARY KEY (`order_id`, `voucher_id`),
FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`voucher_id`) REFERENCES `vouchers`(`id`) ON DELETE RESTRICT
);
Enum "users_role_enum" {
"customer"
"vendor"
"admin"
"courier"
"vendor_moderator"
}
Enum "vendors_approval_status_enum" {
"pending"
"approved"
"rejected"
}
Enum "products_status_enum" {
"draft"
"published"
"archived"
}
Enum "products_type_enum" {
"single"
"variable"
}
Enum "orders_payment_status_enum" {
"pending"
"paid"
"failed"
"refunded"
}
Enum "orders_order_status_enum" {
"pending"
"processing"
"ready_to_ship"
"shipped"
"delivered"
"cancelled"
"returned"
}
Enum "payments_method_enum" {
"bank_transfer"
"credit_card"
"digital_balance"
"other"
}
Enum "payments_status_enum" {
"pending"
"completed"
"failed"
"refunded"
}
Enum "transactions_type_enum" {
"topup"
"payment"
"refund"
"withdrawal"
"commission_payout"
}
Enum "shipments_status_enum" {
"pending"
"picked_up"
"in_transit"
"delivered"
"failed_delivery"
"returned"
}
Enum "commissions_status_enum" {
"pending"
"paid"
"cancelled"
}
Enum "vouchers_type_enum" {
"product_discount"
"shipping_discount"
}
Enum "vouchers_value_type_enum" {
"fixed"
"percentage"
}
Table "users" {
"id" BIGINT [pk, increment]
"name" VARCHAR(255) [not null]
"email" VARCHAR(255) [unique, not null]
"email_verified_at" TIMESTAMP
"password" VARCHAR(255)
"phone" VARCHAR(20)
"role" users_role_enum [not null]
"avatar_file_id" BIGINT
"is_active" BOOLEAN [not null, default: TRUE]
"balance" DECIMAL(15,2) [not null, default: 0.00]
"remember_token" VARCHAR(100)
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "social_accounts" {
"id" BIGINT [pk, increment]
"user_id" BIGINT [not null]
"provider_name" VARCHAR(255) [not null]
"provider_id" VARCHAR(255) [not null]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
Indexes {
(provider_name, provider_id) [unique]
}
}
Table "vendors" {
"id" BIGINT [pk, increment]
"user_id" BIGINT [unique, not null]
"store_name" VARCHAR(255) [not null]
"store_description" TEXT
"store_logo_file_id" BIGINT
"bank_details" JSON
"approval_status" vendors_approval_status_enum [not null, default: 'pending']
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "vendor_moderators" {
"id" BIGINT [pk, increment]
"user_id" BIGINT [not null]
"vendor_id" BIGINT [not null]
"permissions" JSON
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "couriers" {
"id" BIGINT [pk, increment]
"user_id" BIGINT [unique, not null]
"vehicle_type" VARCHAR(255)
"license_number" VARCHAR(255)
"shipping_rates" JSON
"can_create_shipping_voucher" BOOLEAN [not null, default: FALSE]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "files" {
"id" BIGINT [pk, increment]
"user_id" BIGINT [not null]
"name" VARCHAR(255) [not null]
"path" VARCHAR(255) [not null]
"url" VARCHAR(255)
"mime_type" VARCHAR(100) [not null]
"size" BIGINT [not null]
"disk" VARCHAR(50) [not null, default: 'public']
"caption" TEXT
"is_public" BOOLEAN [not null, default: FALSE]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "villages" {
"id" BIGINT [pk, increment]
"name" VARCHAR(255) [unique, not null]
"district" VARCHAR(255) [default: 'Kerumutan']
"regency" VARCHAR(255) [default: 'Pelalawan']
"province" VARCHAR(255) [default: 'Riau']
"country" VARCHAR(255) [default: 'Indonesia']
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "dusuns" {
"id" BIGINT [pk, increment]
"village_id" BIGINT [not null]
"name" VARCHAR(255) [not null]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
Indexes {
(village_id, name) [unique]
}
}
Table "addresses" {
"id" BIGINT [pk, increment]
"user_id" BIGINT [not null]
"label" VARCHAR(255)
"recipient_name" VARCHAR(255) [not null]
"phone" VARCHAR(20) [not null]
"blok_ed" VARCHAR(50)
"jalan" VARCHAR(255) [not null]
"rt" VARCHAR(10) [not null]
"rw" VARCHAR(10) [not null]
"dusun_id" BIGINT [not null]
"postal_code" VARCHAR(10)
"is_default" BOOLEAN [not null, default: FALSE]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "categories" {
"id" BIGINT [pk, increment]
"name" VARCHAR(255) [not null]
"slug" VARCHAR(255) [unique, not null]
"parent_id" BIGINT
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "products" {
"id" BIGINT [pk, increment]
"vendor_id" BIGINT [not null]
"category_id" BIGINT [not null]
"name" VARCHAR(255) [not null]
"slug" VARCHAR(255) [unique, not null]
"description" TEXT
"base_price" DECIMAL(15,2) [not null]
"status" products_status_enum [not null, default: 'draft']
"type" products_type_enum [not null, default: 'single']
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "product_images" {
"id" BIGINT [pk, increment]
"product_id" BIGINT [not null]
"file_id" BIGINT [not null]
"is_thumbnail" BOOLEAN [not null, default: FALSE]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "attributes" {
"id" BIGINT [pk, increment]
"name" VARCHAR(255) [unique, not null]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "attribute_values" {
"id" BIGINT [pk, increment]
"attribute_id" BIGINT [not null]
"value" VARCHAR(255) [not null]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
Indexes {
(attribute_id, value) [unique]
}
}
Table "product_variants" {
"id" BIGINT [pk, increment]
"product_id" BIGINT [not null]
"sku" VARCHAR(255) [unique]
"name" VARCHAR(255)
"price" DECIMAL(15,2) [not null]
"stock" INT [not null, default: 0]
"image_file_id" BIGINT
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "product_variant_attribute_value" {
"product_variant_id" BIGINT [not null]
"attribute_value_id" BIGINT [not null]
Indexes {
(product_variant_id, attribute_value_id) [pk]
}
}
Table "orders" {
"id" BIGINT [pk, increment]
"user_id" BIGINT [not null]
"address_id" BIGINT [not null]
"order_number" VARCHAR(255) [unique, not null]
"total_amount" DECIMAL(15,2) [not null]
"shipping_cost" DECIMAL(15,2) [not null, default: 0.00]
"discount_amount" DECIMAL(15,2) [not null, default: 0.00]
"payment_status" orders_payment_status_enum [not null, default: 'pending']
"order_status" orders_order_status_enum [not null, default: 'pending']
"shipping_method" VARCHAR(255)
"courier_id" BIGINT
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "order_items" {
"id" BIGINT [pk, increment]
"order_id" BIGINT [not null]
"product_id" BIGINT [not null]
"product_variant_id" BIGINT
"vendor_id" BIGINT [not null]
"quantity" INT [not null]
"price_per_unit" DECIMAL(15,2) [not null]
"subtotal" DECIMAL(15,2) [not null]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "payments" {
"id" BIGINT [pk, increment]
"order_id" BIGINT [unique]
"user_id" BIGINT [not null]
"transaction_id" VARCHAR(255) [unique, not null]
"method" payments_method_enum [not null]
"amount" DECIMAL(15,2) [not null]
"status" payments_status_enum [not null]
"paid_at" TIMESTAMP
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "transactions" {
"id" BIGINT [pk, increment]
"user_id" BIGINT [not null]
"type" transactions_type_enum [not null]
"amount" DECIMAL(15,2) [not null]
"current_balance" DECIMAL(15,2) [not null]
"reference_id" BIGINT
"reference_type" VARCHAR(50)
"description" TEXT
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "shipments" {
"id" BIGINT [pk, increment]
"order_id" BIGINT [unique, not null]
"courier_id" BIGINT
"tracking_number" VARCHAR(255)
"status" shipments_status_enum [not null, default: 'pending']
"pickup_address" TEXT
"delivery_address" TEXT
"estimated_delivery_at" TIMESTAMP
"actual_delivery_at" TIMESTAMP
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "shipment_history" {
"id" BIGINT [pk, increment]
"shipment_id" BIGINT [not null]
"status" VARCHAR(255) [not null]
"location" VARCHAR(255)
"description" TEXT
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "reviews" {
"id" BIGINT [pk, increment]
"user_id" BIGINT [not null]
"product_id" BIGINT
"courier_id" BIGINT
"order_id" BIGINT [not null]
"rating" TINYINT [not null]
"comment" TEXT
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
Indexes {
(user_id, order_id, product_id) [unique]
(user_id, order_id, courier_id) [unique]
}
}
Table "commissions" {
"id" BIGINT [pk, increment]
"vendor_id" BIGINT [not null]
"order_item_id" BIGINT [not null]
"commission_percentage" DECIMAL(5,2) [not null]
"commission_amount" DECIMAL(15,2) [not null]
"status" commissions_status_enum [not null, default: 'pending']
"payout_transaction_id" BIGINT
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "vouchers" {
"id" BIGINT [pk, increment]
"code" VARCHAR(255) [unique, not null]
"name" VARCHAR(255)
"description" TEXT
"type" vouchers_type_enum [not null]
"value" DECIMAL(15,2) [not null]
"value_type" vouchers_value_type_enum [not null]
"start_date" TIMESTAMP [not null]
"end_date" TIMESTAMP [not null]
"usage_limit" INT
"used_count" INT [not null, default: 0]
"min_order_amount" DECIMAL(15,2)
"applies_to" JSON
"created_by_user_id" BIGINT [not null]
"created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
"updated_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
}
Table "order_vouchers" {
"order_id" BIGINT [not null]
"voucher_id" BIGINT [not null]
"discount_amount" DECIMAL(15,2) [not null]
Indexes {
(order_id, voucher_id) [pk]
}
}
Ref:"files"."id" < "users"."avatar_file_id" [delete: set null]
Ref:"users"."id" < "social_accounts"."user_id" [delete: cascade]
Ref:"users"."id" < "vendors"."user_id" [delete: cascade]
Ref:"files"."id" < "vendors"."store_logo_file_id" [delete: set null]
Ref:"users"."id" < "vendor_moderators"."user_id" [delete: cascade]
Ref:"vendors"."id" < "vendor_moderators"."vendor_id" [delete: cascade]
Ref:"users"."id" < "couriers"."user_id" [delete: cascade]
Ref:"users"."id" < "files"."user_id" [delete: cascade]
Ref:"villages"."id" < "dusuns"."village_id" [delete: cascade]
Ref:"users"."id" < "addresses"."user_id" [delete: cascade]
Ref:"dusuns"."id" < "addresses"."dusun_id" [delete: restrict]
Ref:"categories"."id" < "categories"."parent_id" [delete: set null]
Ref:"users"."id" < "products"."vendor_id" [delete: cascade]
Ref:"categories"."id" < "products"."category_id" [delete: restrict]
Ref:"products"."id" < "product_images"."product_id" [delete: cascade]
Ref:"files"."id" < "product_images"."file_id" [delete: cascade]
Ref:"attributes"."id" < "attribute_values"."attribute_id" [delete: cascade]
Ref:"products"."id" < "product_variants"."product_id" [delete: cascade]
Ref:"files"."id" < "product_variants"."image_file_id" [delete: set null]
Ref:"product_variants"."id" < "product_variant_attribute_value"."product_variant_id" [delete: cascade]
Ref:"attribute_values"."id" < "product_variant_attribute_value"."attribute_value_id" [delete: cascade]
Ref:"users"."id" < "orders"."user_id" [delete: restrict]
Ref:"addresses"."id" < "orders"."address_id" [delete: restrict]
Ref:"users"."id" < "orders"."courier_id" [delete: set null]
Ref:"orders"."id" < "order_items"."order_id" [delete: cascade]
Ref:"products"."id" < "order_items"."product_id" [delete: restrict]
Ref:"product_variants"."id" < "order_items"."product_variant_id" [delete: restrict]
Ref:"users"."id" < "order_items"."vendor_id" [delete: restrict]
Ref:"orders"."id" < "payments"."order_id" [delete: set null]
Ref:"users"."id" < "payments"."user_id" [delete: restrict]
Ref:"users"."id" < "transactions"."user_id" [delete: restrict]
Ref:"orders"."id" < "shipments"."order_id" [delete: cascade]
Ref:"users"."id" < "shipments"."courier_id" [delete: set null]
Ref:"shipments"."id" < "shipment_history"."shipment_id" [delete: cascade]
Ref:"users"."id" < "reviews"."user_id" [delete: cascade]
Ref:"products"."id" < "reviews"."product_id" [delete: set null]
Ref:"users"."id" < "reviews"."courier_id" [delete: set null]
Ref:"orders"."id" < "reviews"."order_id" [delete: cascade]
Ref:"users"."id" < "commissions"."vendor_id" [delete: restrict]
Ref:"order_items"."id" < "commissions"."order_item_id" [delete: restrict]
Ref:"transactions"."id" < "commissions"."payout_transaction_id" [delete: set null]
Ref:"users"."id" < "vouchers"."created_by_user_id" [delete: restrict]
Ref:"orders"."id" < "order_vouchers"."order_id" [delete: cascade]
Ref:"vouchers"."id" < "order_vouchers"."voucher_id" [delete: restrict]
