Spaces:

XRyZ
/

GCW-SiTukang

Running

App Files Files Community

GCW-SiTukang / docs /database_schema.md

Ryu2804

Deploy files from GitHub repository

fa9d865 22 days ago

preview code

Raw

History Blame Contribute Delete

62.9 kB

1. Architecture Overview

HandyDirect menggunakan satu tabel users untuk semua aktor (User, Worker, Admin). Diferensiasi dilakukan melalui kolom role yang disimpan sebagai enum. JWT access token yang dihasilkan saat login akan mengandung informasi role sehingga middleware di backend dapat melakukan Role-Based Access Control (RBAC) tanpa perlu query tambahan ke database untuk setiap request.

Key Principles

Single users table — semua aktor (user, worker, admin) berada di satu tabel.
worker_profiles table — data spesifik worker (spesialisasi, bio, verifikasi) disimpan terpisah dan di-link via user_id.
UUID v4 — semua primary key menggunakan UUID untuk keamanan dan distribusi.
Soft delete — menggunakan kolom deleted_at (nullable TIMESTAMPTZ) pada tabel yang membutuhkan.
Audit timestamps — setiap tabel memiliki created_at dan updated_at.
PostgreSQL enums — digunakan untuk kolom dengan nilai terbatas dan tetap.

2. RBAC & Authentication Strategy

JWT Token Payload

{
  "sub": "user_id (UUID)",
  "role": "user | worker | admin",
  "email": "user@email.com",
  "iat": 1698230400,
  "exp": 1698234000,
  "jti": "unique-token-id"
}

Middleware Flow

Request → Extract JWT → Verify Signature → Decode Payload → Check role
  ├── role == "user"   → Allow access to user endpoints
  ├── role == "worker" → Allow access to worker endpoints
  ├── role == "admin"  → Allow access to admin endpoints
  └── role mismatch    → Return 403 Forbidden

Refresh Token Strategy

Access token: short-lived (1 hour)
Refresh token: long-lived (30 days), stored in refresh_tokens table
Refresh tokens are rotated on every refresh (old token invalidated)

3. Enum Types

Berikut adalah semua PostgreSQL enum types yang digunakan:

-- =============================================
-- ENUM TYPES
-- =============================================

CREATE TYPE user_role AS ENUM ('user', 'worker', 'admin');

CREATE TYPE order_status AS ENUM (
  'pending',
  'accepted',
  'on_the_way',
  'arrived',
  'in_progress',
  'work_paused',
  'completed',
  'cancelled',
  'rejected',
  'waiting_payment',
  'waiting_for_payment',
  'paid'
);

CREATE TYPE order_urgency AS ENUM ('normal', 'urgent');

CREATE TYPE purchase_status AS ENUM (
  'draft',
  'pending_approval',
  'approved',
  'rejected',
  'needs_clarification'
);

CREATE TYPE purchase_category AS ENUM (
  'material',
  'alat',
  'sparepart',
  'bahan_bangunan',
  'biaya_tambahan',
  'lainnya'
);

CREATE TYPE risk_flag_type AS ENUM (
  'harga_tidak_wajar',
  'item_tidak_relevan',
  'data_tidak_lengkap',
  'nota_tidak_jelas',
  'duplikat',
  'alasan_tidak_lengkap'
);

CREATE TYPE message_type AS ENUM ('text', 'image', 'system');

CREATE TYPE payment_method AS ENUM ('cash', 'bank_transfer', 'ewallet');

CREATE TYPE payment_status AS ENUM ('unpaid', 'pending', 'paid', 'refunded', 'waiting_payment', 'waiting_for_payment');

CREATE TYPE verification_status AS ENUM ('unverified', 'pending', 'verified', 'rejected');

CREATE TYPE notification_type AS ENUM ('order', 'purchase', 'chat', 'promo', 'system', 'payment');

CREATE TYPE article_category AS ENUM ('faq', 'guide', 'tips', 'safety', 'payment');

CREATE TYPE faq_category AS ENUM ('general', 'payment', 'tracking', 'security', 'cancellation');

CREATE TYPE wallet_tx_type AS ENUM ('earning', 'withdrawal', 'refund', 'bonus', 'fee');

CREATE TYPE wallet_tx_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');

CREATE TYPE audit_action AS ENUM (
  'created', 'ai_processed', 'submitted',
  'approved', 'rejected', 'clarification_requested',
  'clarification_responded', 'edited', 'deleted'
);

4. Entity Relationship Diagram (ERD)

┌──────────────┐       ┌──────────────────┐       ┌─────────────┐
│    users     │1─────1│  worker_profiles  │       │  categories │
│  (all roles) │       │  (worker only)    │       │             │
└──────┬───────┘       └────────┬─────────┘       └──────┬──────┘
       │                        │                         │
       │                        │ M:N                     │ 1:N
       │                 ┌──────┴──────┐           ┌──────┴──────┐
       │                 │worker_services│          │  services   │
       │                 └──────┬──────┘           └──────┬──────┘
       │                        │                         │
       │ 1:N (as user)          │                         │
       │                        │                         │
  ┌────┴────────────────────────┴─────────────────────────┴───┐
  │                          orders                            │
  │  (user_id, worker_id, service_id, category_id)            │
  └───┬───────┬──────────┬──────────┬──────────┬──────────┬───┘
      │       │          │          │          │          │
      │1:N    │1:N       │1:N       │1:N       │1:1       │1:1
      │       │          │          │          │          │
 ┌────┴──┐ ┌──┴────┐ ┌───┴────┐ ┌──┴─────┐ ┌─┴──────┐ ┌┴───────┐
 │order_  │ │order_ │ │purch-  │ │chat_   │ │invoices│ │reviews │
 │photos  │ │time-  │ │ases    │ │messages│ │        │ │        │
 │        │ │line   │ │        │ │        │ │        │ │        │
 └────────┘ └───────┘ └───┬────┘ └────────┘ └───┬────┘ └───┬────┘
                           │                      │          │
                     ┌─────┴──────┐          ┌────┴────┐ ┌───┴─────┐
                     │purchase_   │          │invoice_ │ │review_  │
                     │risk_flags  │          │line_    │ │tags     │
                     │            │          │items    │ │         │
                     ├────────────┤          └─────────┘ └─────────┘
                     │purchase_   │
                     │audit_logs  │
                     └────────────┘

  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐
  │notifications │    │  articles    │    │   faqs       │
  │ (per user)   │    │ (knowledge)  │    │              │
  └──────────────┘    └──────────────┘    └──────────────┘

  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐
  │ promotions   │    │worker_wallets│───1│wallet_trans- │
  │              │    │ (per worker) │ :N │actions       │
  └──────────────┘    └──────────────┘    └──────────────┘

  ┌──────────────┐
  │refresh_tokens│
  │ (per user)   │
  └──────────────┘

5. Table Definitions

5.1 `users`

Tabel utama untuk semua aktor. Kolom role digunakan sebagai dasar RBAC di JWT middleware.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`full_name`	VARCHAR(255)	NO	—	Nama lengkap
`email`	VARCHAR(255)	NO	—	Email (unique)
`phone`	VARCHAR(20)	NO	—	Nomor telepon (unique)
`password_hash`	VARCHAR(255)	NO	—	Hashed password (bcrypt)
`role`	user_role	NO	`'user'`	Role: user, worker, admin
`avatar_url`	TEXT	YES	NULL	URL foto profil
`address`	TEXT	YES	NULL	Alamat lengkap
`latitude`	DECIMAL(10,7)	YES	NULL	Latitude lokasi
`longitude`	DECIMAL(10,7)	YES	NULL	Longitude lokasi
`is_active`	BOOLEAN	NO	`TRUE`	Status aktif akun
`email_verified_at`	TIMESTAMPTZ	YES	NULL	Waktu verifikasi email
`phone_verified_at`	TIMESTAMPTZ	YES	NULL	Waktu verifikasi phone
`last_login_at`	TIMESTAMPTZ	YES	NULL	Login terakhir
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate
`deleted_at`	TIMESTAMPTZ	YES	NULL	Soft delete

Constraints & Indexes:

PK: id
UNIQUE: email, phone
INDEX: role, is_active, latitude + longitude (GIST), deleted_at

CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    full_name       VARCHAR(255) NOT NULL,
    email           VARCHAR(255) NOT NULL UNIQUE,
    phone           VARCHAR(20) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    role            user_role NOT NULL DEFAULT 'user',
    avatar_url      TEXT,
    address         TEXT,
    latitude        DECIMAL(10, 7),
    longitude       DECIMAL(10, 7),
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    email_verified_at TIMESTAMPTZ,
    phone_verified_at TIMESTAMPTZ,
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ
);

CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_active ON users(is_active);
CREATE INDEX idx_users_location ON users USING GIST (
    ST_MakePoint(longitude, latitude)
) WHERE latitude IS NOT NULL AND longitude IS NOT NULL;
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

5.2 `refresh_tokens`

Menyimpan refresh token untuk JWT rotation strategy.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`user_id`	UUID	NO	—	FK → users.id
`token_hash`	VARCHAR(255)	NO	—	Hashed refresh token
`device_info`	VARCHAR(500)	YES	NULL	Info device/browser
`ip_address`	INET	YES	NULL	IP address saat login
`expires_at`	TIMESTAMPTZ	NO	—	Waktu expired
`revoked_at`	TIMESTAMPTZ	YES	NULL	Waktu revoke (jika di-revoke)
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat

CREATE TABLE refresh_tokens (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash      VARCHAR(255) NOT NULL,
    device_info     VARCHAR(500),
    ip_address      INET,
    expires_at      TIMESTAMPTZ NOT NULL,
    revoked_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_refresh_tokens_user ON refresh_tokens(user_id);
CREATE INDEX idx_refresh_tokens_hash ON refresh_tokens(token_hash);
CREATE INDEX idx_refresh_tokens_expires ON refresh_tokens(expires_at);

5.3 `worker_profiles`

Data spesifik worker. Satu user dengan role='worker' memiliki tepat satu worker_profile.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`user_id`	UUID	NO	—	FK → users.id (UNIQUE)
`specialization`	VARCHAR(255)	YES	NULL	Spesialisasi utama
`bio`	TEXT	YES	NULL	Deskripsi pengalaman
`cover_photo_url`	TEXT	YES	NULL	Foto cover profil
`verification_status`	verification_status	NO	`'unverified'`	Status verifikasi
`id_card_url`	TEXT	YES	NULL	Foto KTP (untuk verifikasi)
`certificate_urls`	JSONB	YES	`'[]'`	Array URL sertifikat
`base_price`	INTEGER	YES	NULL	Harga dasar (Rupiah)
`price_unit`	VARCHAR(50)	YES	`'per kunjungan'`	Satuan harga
`booking_fee`	INTEGER	NO	`2000`	Booking fee (Rupiah)
`rating_avg`	DECIMAL(2,1)	NO	`0.0`	Rata-rata rating
`total_reviews`	INTEGER	NO	`0`	Total ulasan
`completed_jobs`	INTEGER	NO	`0`	Total pekerjaan selesai
`is_available`	BOOLEAN	NO	`TRUE`	Sedang tersedia/tidak
`verified_at`	TIMESTAMPTZ	YES	NULL	Waktu diverifikasi
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE worker_profiles (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    specialization      VARCHAR(255),
    bio                 TEXT,
    cover_photo_url     TEXT,
    verification_status verification_status NOT NULL DEFAULT 'unverified',
    id_card_url         TEXT,
    certificate_urls    JSONB DEFAULT '[]',
    base_price          INTEGER,
    price_unit          VARCHAR(50) DEFAULT 'per kunjungan',
    booking_fee         INTEGER NOT NULL DEFAULT 2000,
    rating_avg          DECIMAL(2, 1) NOT NULL DEFAULT 0.0,
    total_reviews       INTEGER NOT NULL DEFAULT 0,
    completed_jobs      INTEGER NOT NULL DEFAULT 0,
    is_available        BOOLEAN NOT NULL DEFAULT TRUE,
    verified_at         TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_worker_profiles_user ON worker_profiles(user_id);
CREATE INDEX idx_worker_profiles_available ON worker_profiles(is_available);
CREATE INDEX idx_worker_profiles_rating ON worker_profiles(rating_avg DESC);
CREATE INDEX idx_worker_profiles_verification ON worker_profiles(verification_status);

5.4 `categories`

Kategori jasa: AC, Pipa, Atap, Listrik, Kunci, Kayu, Cat, Kebun, dll.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`name`	VARCHAR(100)	NO	—	Nama kategori
`slug`	VARCHAR(100)	NO	—	Slug URL-friendly (unique)
`icon_url`	TEXT	YES	NULL	URL ikon kategori
`description`	TEXT	YES	NULL	Deskripsi kategori
`display_order`	INTEGER	NO	`0`	Urutan tampilan
`is_active`	BOOLEAN	NO	`TRUE`	Status aktif
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE categories (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(100) NOT NULL,
    slug            VARCHAR(100) NOT NULL UNIQUE,
    icon_url        TEXT,
    description     TEXT,
    display_order   INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_active ON categories(is_active, display_order);

5.5 `services`

Layanan spesifik dalam kategori. Contoh: kategori "Listrik" → "Instalasi Listrik", "Perbaikan Listrik".

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`category_id`	UUID	NO	—	FK → categories.id
`name`	VARCHAR(255)	NO	—	Nama layanan
`slug`	VARCHAR(255)	NO	—	Slug (unique)
`description`	TEXT	YES	NULL	Deskripsi layanan
`icon_url`	TEXT	YES	NULL	URL ikon
`base_price`	INTEGER	YES	NULL	Harga dasar (Rupiah)
`price_unit`	VARCHAR(50)	YES	`'per kunjungan'`	Satuan harga
`estimated_duration`	VARCHAR(50)	YES	NULL	Estimasi durasi (e.g. "1-3 jam")
`is_active`	BOOLEAN	NO	`TRUE`	Status aktif
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE services (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    category_id         UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    name                VARCHAR(255) NOT NULL,
    slug                VARCHAR(255) NOT NULL UNIQUE,
    description         TEXT,
    icon_url            TEXT,
    base_price          INTEGER,
    price_unit          VARCHAR(50) DEFAULT 'per kunjungan',
    estimated_duration  VARCHAR(50),
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_services_category ON services(category_id);
CREATE INDEX idx_services_slug ON services(slug);
CREATE INDEX idx_services_active ON services(is_active);

5.6 `worker_services`

Junction table: relasi many-to-many antara worker dan services yang mereka tawarkan.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`worker_id`	UUID	NO	—	FK → users.id (role=worker)
`service_id`	UUID	NO	—	FK → services.id
`custom_price`	INTEGER	YES	NULL	Harga kustom worker (override)
`is_active`	BOOLEAN	NO	`TRUE`	Masih ditawarkan?
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat

CREATE TABLE worker_services (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    worker_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    service_id      UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE,
    custom_price    INTEGER,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(worker_id, service_id)
);

CREATE INDEX idx_worker_services_worker ON worker_services(worker_id);
CREATE INDEX idx_worker_services_service ON worker_services(service_id);

5.7 `orders`

Tabel utama pesanan/laporan dari user ke worker.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`order_number`	VARCHAR(50)	NO	—	Nomor order (unique, e.g. HD-20231025-001)
`user_id`	UUID	NO	—	FK → users.id (pemesan)
`worker_id`	UUID	NO	—	FK → users.id (pekerja)
`service_id`	UUID	NO	—	FK → services.id
`category_id`	UUID	NO	—	FK → categories.id
`title`	VARCHAR(255)	NO	—	Judul laporan/pesanan
`description`	TEXT	NO	—	Deskripsi masalah
`status`	order_status	NO	`'pending'`	Status pesanan
`urgency`	order_urgency	NO	`'normal'`	Tingkat urgensi
`location_address`	TEXT	NO	—	Alamat lokasi pekerjaan
`location_detail`	VARCHAR(500)	YES	NULL	Detail tambahan lokasi
`location_lat`	DECIMAL(10,7)	NO	—	Latitude lokasi
`location_lng`	DECIMAL(10,7)	NO	—	Longitude lokasi
`preferred_date`	DATE	YES	NULL	Tanggal yang diinginkan
`preferred_time_start`	TIME	YES	NULL	Jam mulai yang diinginkan
`preferred_time_end`	TIME	YES	NULL	Jam selesai yang diinginkan
`notes`	TEXT	YES	NULL	Catatan tambahan
`booking_fee`	INTEGER	NO	`2000`	Booking fee (Rupiah)
`base_service_fee`	INTEGER	YES	NULL	Biaya jasa dasar
`total_material_cost`	INTEGER	NO	`0`	Total biaya material (approved)
`total_additional_cost`	INTEGER	NO	`0`	Total biaya tambahan (approved)
`grand_total`	INTEGER	YES	NULL	Grand total (dihitung saat selesai)
`cancellation_reason`	TEXT	YES	NULL	Alasan pembatalan
`cancellation_category`	VARCHAR(50)	YES	NULL	Kategori alasan batal
`cancelled_by`	UUID	YES	NULL	Siapa yang membatalkan
`accepted_at`	TIMESTAMPTZ	YES	NULL	Waktu order diterima
`started_at`	TIMESTAMPTZ	YES	NULL	Waktu pengerjaan dimulai
`completed_at`	TIMESTAMPTZ	YES	NULL	Waktu selesai
`cancelled_at`	TIMESTAMPTZ	YES	NULL	Waktu dibatalkan
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE orders (
    id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_number          VARCHAR(50) NOT NULL UNIQUE,
    user_id               UUID NOT NULL REFERENCES users(id),
    worker_id             UUID NOT NULL REFERENCES users(id),
    service_id            UUID NOT NULL REFERENCES services(id),
    category_id           UUID NOT NULL REFERENCES categories(id),
    title                 VARCHAR(255) NOT NULL,
    description           TEXT NOT NULL,
    status                order_status NOT NULL DEFAULT 'pending',
    urgency               order_urgency NOT NULL DEFAULT 'normal',
    location_address      TEXT NOT NULL,
    location_detail       VARCHAR(500),
    location_lat          DECIMAL(10, 7) NOT NULL,
    location_lng          DECIMAL(10, 7) NOT NULL,
    preferred_date        DATE,
    preferred_time_start  TIME,
    preferred_time_end    TIME,
    notes                 TEXT,
    booking_fee           INTEGER NOT NULL DEFAULT 2000,
    base_service_fee      INTEGER,
    total_material_cost   INTEGER NOT NULL DEFAULT 0,
    total_additional_cost INTEGER NOT NULL DEFAULT 0,
    grand_total           INTEGER,
    cancellation_reason   TEXT,
    cancellation_category VARCHAR(50),
    cancelled_by          UUID REFERENCES users(id),
    accepted_at           TIMESTAMPTZ,
    started_at            TIMESTAMPTZ,
    completed_at          TIMESTAMPTZ,
    cancelled_at          TIMESTAMPTZ,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_worker ON orders(worker_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_service ON orders(service_id);
CREATE INDEX idx_orders_category ON orders(category_id);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_orders_number ON orders(order_number);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_orders_worker_status ON orders(worker_id, status);

5.8 `order_photos`

Foto yang dilampirkan user saat membuat order.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`order_id`	UUID	NO	—	FK → orders.id
`photo_url`	TEXT	NO	—	URL foto
`caption`	VARCHAR(500)	YES	NULL	Keterangan foto
`display_order`	INTEGER	NO	`0`	Urutan tampilan
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat

CREATE TABLE order_photos (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id        UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    photo_url       TEXT NOT NULL,
    caption         VARCHAR(500),
    display_order   INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_order_photos_order ON order_photos(order_id);

5.9 `order_timeline`

Timeline/progress pekerjaan. Setiap perubahan status dicatat di sini untuk tracking halaman user.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`order_id`	UUID	NO	—	FK → orders.id
`event`	VARCHAR(50)	NO	—	Kode event (e.g. order_created, worker_arrived)
`label`	VARCHAR(255)	NO	—	Label tampilan (e.g. "Pesanan Dibuat")
`description`	TEXT	YES	NULL	Deskripsi tambahan
`actor_id`	UUID	YES	NULL	FK → users.id (siapa yang trigger)
`actor_type`	VARCHAR(20)	YES	NULL	'user', 'worker', 'system'
`metadata`	JSONB	YES	NULL	Data tambahan (latitude, longitude, notes, dll)
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu event terjadi

CREATE TABLE order_timeline (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id        UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    event           VARCHAR(50) NOT NULL,
    label           VARCHAR(255) NOT NULL,
    description     TEXT,
    actor_id        UUID REFERENCES users(id),
    actor_type      VARCHAR(20),
    metadata        JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_order_timeline_order ON order_timeline(order_id);
CREATE INDEX idx_order_timeline_event ON order_timeline(event);
CREATE INDEX idx_order_timeline_created ON order_timeline(order_id, created_at);

5.10 `purchases`

Item pembelian material/alat/biaya tambahan oleh worker. Inti dari fitur AI-assisted purchase tracking.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`order_id`	UUID	NO	—	FK → orders.id
`worker_id`	UUID	NO	—	FK → users.id (worker yang memasukkan)
`item_name`	VARCHAR(255)	NO	—	Nama item/barang
`category`	purchase_category	NO	`'material'`	Kategori pembelian
`quantity`	DECIMAL(10,2)	NO	`1`	Jumlah
`unit`	VARCHAR(50)	NO	`'pcs'`	Satuan (pcs, meter, sak, liter, dll)
`unit_price`	INTEGER	NO	`0`	Harga satuan (Rupiah)
`total_price`	INTEGER	NO	`0`	Harga total (Rupiah)
`reason`	TEXT	YES	NULL	Alasan pembelian
`receipt_photo_url`	TEXT	YES	NULL	URL foto nota/struk
`status`	purchase_status	NO	`'draft'`	Status approval
`confidence`	DECIMAL(3,2)	YES	NULL	AI confidence score (0.00 – 1.00)
`needs_clarification`	BOOLEAN	NO	`FALSE`	Perlu klarifikasi dari user?
`clarification_question`	TEXT	YES	NULL	Pertanyaan klarifikasi dari AI/user
`clarification_response`	TEXT	YES	NULL	Respons klarifikasi dari worker
`ai_explanation`	TEXT	YES	NULL	Penjelasan AI tentang pembelian ini
`raw_input`	TEXT	YES	NULL	Input mentah dari worker (sebelum AI proses)
`ai_processed_at`	TIMESTAMPTZ	YES	NULL	Waktu diproses AI
`approved_by`	UUID	YES	NULL	FK → users.id (user yang approve)
`approved_at`	TIMESTAMPTZ	YES	NULL	Waktu diapprove
`rejected_by`	UUID	YES	NULL	FK → users.id (user yang reject)
`rejected_at`	TIMESTAMPTZ	YES	NULL	Waktu ditolak
`rejection_reason`	TEXT	YES	NULL	Alasan penolakan
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE purchases (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id                UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    worker_id               UUID NOT NULL REFERENCES users(id),
    item_name               VARCHAR(255) NOT NULL,
    category                purchase_category NOT NULL DEFAULT 'material',
    quantity                DECIMAL(10, 2) NOT NULL DEFAULT 1,
    unit                    VARCHAR(50) NOT NULL DEFAULT 'pcs',
    unit_price              INTEGER NOT NULL DEFAULT 0,
    total_price             INTEGER NOT NULL DEFAULT 0,
    reason                  TEXT,
    receipt_photo_url       TEXT,
    status                  purchase_status NOT NULL DEFAULT 'draft',
    confidence              DECIMAL(3, 2),
    needs_clarification     BOOLEAN NOT NULL DEFAULT FALSE,
    clarification_question  TEXT,
    clarification_response  TEXT,
    ai_explanation          TEXT,
    raw_input               TEXT,
    ai_processed_at         TIMESTAMPTZ,
    approved_by             UUID REFERENCES users(id),
    approved_at             TIMESTAMPTZ,
    rejected_by             UUID REFERENCES users(id),
    rejected_at             TIMESTAMPTZ,
    rejection_reason        TEXT,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_purchases_order ON purchases(order_id);
CREATE INDEX idx_purchases_worker ON purchases(worker_id);
CREATE INDEX idx_purchases_status ON purchases(status);
CREATE INDEX idx_purchases_order_status ON purchases(order_id, status);
CREATE INDEX idx_purchases_created ON purchases(created_at DESC);

5.11 `purchase_risk_flags`

Risk flags yang dihasilkan AI untuk setiap item pembelian.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`purchase_id`	UUID	NO	—	FK → purchases.id
`type`	risk_flag_type	NO	—	Jenis risk flag
`message`	TEXT	NO	—	Pesan/penjelasan risk flag
`is_resolved`	BOOLEAN	NO	`FALSE`	Sudah ditindaklanjuti?
`resolved_by`	UUID	YES	NULL	FK → users.id
`resolved_at`	TIMESTAMPTZ	YES	NULL	Waktu resolved
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat

CREATE TABLE purchase_risk_flags (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_id     UUID NOT NULL REFERENCES purchases(id) ON DELETE CASCADE,
    type            risk_flag_type NOT NULL,
    message         TEXT NOT NULL,
    is_resolved     BOOLEAN NOT NULL DEFAULT FALSE,
    resolved_by     UUID REFERENCES users(id),
    resolved_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_purchase_risk_flags_purchase ON purchase_risk_flags(purchase_id);
CREATE INDEX idx_purchase_risk_flags_type ON purchase_risk_flags(type);
CREATE INDEX idx_purchase_risk_flags_unresolved ON purchase_risk_flags(is_resolved) WHERE is_resolved = FALSE;

5.12 `purchase_audit_logs`

Audit trail untuk setiap aksi pada item pembelian. Menjamin transparansi proses approval.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`purchase_id`	UUID	NO	—	FK → purchases.id
`action`	audit_action	NO	—	Jenis aksi
`actor_id`	UUID	YES	NULL	FK → users.id (pelaku aksi)
`actor_name`	VARCHAR(255)	YES	NULL	Nama pelaku (untuk display)
`actor_type`	VARCHAR(20)	NO	—	'user', 'worker', 'system', 'ai'
`note`	TEXT	YES	NULL	Catatan tambahan
`old_data`	JSONB	YES	NULL	Data sebelum perubahan
`new_data`	JSONB	YES	NULL	Data setelah perubahan
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu aksi

CREATE TABLE purchase_audit_logs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_id     UUID NOT NULL REFERENCES purchases(id) ON DELETE CASCADE,
    action          audit_action NOT NULL,
    actor_id        UUID REFERENCES users(id),
    actor_name      VARCHAR(255),
    actor_type      VARCHAR(20) NOT NULL,
    note            TEXT,
    old_data        JSONB,
    new_data        JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_purchase_audit_purchase ON purchase_audit_logs(purchase_id);
CREATE INDEX idx_purchase_audit_action ON purchase_audit_logs(action);
CREATE INDEX idx_purchase_audit_actor ON purchase_audit_logs(actor_id);
CREATE INDEX idx_purchase_audit_created ON purchase_audit_logs(created_at DESC);

5.13 `chat_messages`

Pesan chat antara user dan worker per order.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`order_id`	UUID	NO	—	FK → orders.id
`sender_id`	UUID	NO	—	FK → users.id (pengirim)
`sender_type`	VARCHAR(20)	NO	—	'user', 'worker', 'system'
`content`	TEXT	YES	NULL	Isi pesan (nullable jika image-only)
`message_type`	message_type	NO	`'text'`	Tipe pesan
`media_url`	TEXT	YES	NULL	URL media (foto, dll)
`is_read`	BOOLEAN	NO	`FALSE`	Sudah dibaca penerima?
`read_at`	TIMESTAMPTZ	YES	NULL	Waktu dibaca
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dikirim

CREATE TABLE chat_messages (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id        UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    sender_id       UUID NOT NULL REFERENCES users(id),
    sender_type     VARCHAR(20) NOT NULL,
    content         TEXT,
    message_type    message_type NOT NULL DEFAULT 'text',
    media_url       TEXT,
    is_read         BOOLEAN NOT NULL DEFAULT FALSE,
    read_at         TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_chat_messages_order ON chat_messages(order_id);
CREATE INDEX idx_chat_messages_sender ON chat_messages(sender_id);
CREATE INDEX idx_chat_messages_order_created ON chat_messages(order_id, created_at DESC);
CREATE INDEX idx_chat_messages_unread ON chat_messages(order_id, is_read) WHERE is_read = FALSE;

5.14 `reviews`

Rating dan review yang diberikan user setelah pekerjaan selesai.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`order_id`	UUID	NO	—	FK → orders.id (UNIQUE — 1 review per order)
`user_id`	UUID	NO	—	FK → users.id (reviewer)
`worker_id`	UUID	NO	—	FK → users.id (yang di-review)
`rating`	SMALLINT	NO	—	Rating 1–5
`comment`	TEXT	YES	NULL	Ulasan teks
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE reviews (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id        UUID NOT NULL UNIQUE REFERENCES orders(id) ON DELETE CASCADE,
    user_id         UUID NOT NULL REFERENCES users(id),
    worker_id       UUID NOT NULL REFERENCES users(id),
    rating          SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment         TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_reviews_order ON reviews(order_id);
CREATE INDEX idx_reviews_user ON reviews(user_id);
CREATE INDEX idx_reviews_worker ON reviews(worker_id);
CREATE INDEX idx_reviews_worker_rating ON reviews(worker_id, rating);
CREATE INDEX idx_reviews_created ON reviews(created_at DESC);

5.15 `review_tags`

Tag yang dipilih user pada review (e.g. "cepat", "rapi", "profesional").

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`review_id`	UUID	NO	—	FK → reviews.id
`tag`	VARCHAR(50)	NO	—	Nama tag
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat

CREATE TABLE review_tags (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    review_id       UUID NOT NULL REFERENCES reviews(id) ON DELETE CASCADE,
    tag             VARCHAR(50) NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_review_tags_review ON review_tags(review_id);
CREATE INDEX idx_review_tags_tag ON review_tags(tag);

5.16 `invoices`

Invoice/laporan akhir untuk setiap order yang selesai.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`order_id`	UUID	NO	—	FK → orders.id (UNIQUE)
`invoice_number`	VARCHAR(50)	NO	—	Nomor invoice (unique)
`base_service_fee`	INTEGER	NO	`0`	Biaya jasa dasar
`total_material_cost`	INTEGER	NO	`0`	Total biaya material (approved)
`total_additional_cost`	INTEGER	NO	`0`	Biaya tambahan
`booking_fee`	INTEGER	NO	`2000`	Booking fee
`platform_fee`	INTEGER	NO	`0`	Biaya layanan platform
`discount_amount`	INTEGER	NO	`0`	Jumlah diskon
`promo_code`	VARCHAR(50)	YES	NULL	Kode promo yang digunakan
`grand_total`	INTEGER	NO	`0`	Grand total pembayaran
`currency`	VARCHAR(3)	NO	`'IDR'`	Mata uang
`payment_instruction`	TEXT	YES	NULL	Instruksi pembayaran
`ai_work_summary`	TEXT	YES	NULL	Ringkasan pekerjaan oleh AI
`ai_materials_summary`	TEXT	YES	NULL	Ringkasan material oleh AI
`worker_notes`	TEXT	YES	NULL	Catatan dari worker
`all_purchases_approved`	BOOLEAN	NO	`TRUE`	Semua pembelian sudah diapprove?
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE invoices (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id                UUID NOT NULL UNIQUE REFERENCES orders(id) ON DELETE CASCADE,
    invoice_number          VARCHAR(50) NOT NULL UNIQUE,
    base_service_fee        INTEGER NOT NULL DEFAULT 0,
    total_material_cost     INTEGER NOT NULL DEFAULT 0,
    total_additional_cost   INTEGER NOT NULL DEFAULT 0,
    booking_fee             INTEGER NOT NULL DEFAULT 2000,
    platform_fee            INTEGER NOT NULL DEFAULT 0,
    discount_amount         INTEGER NOT NULL DEFAULT 0,
    promo_code              VARCHAR(50),
    grand_total             INTEGER NOT NULL DEFAULT 0,
    currency                VARCHAR(3) NOT NULL DEFAULT 'IDR',
    payment_instruction     TEXT,
    ai_work_summary         TEXT,
    ai_materials_summary    TEXT,
    worker_notes            TEXT,
    all_purchases_approved  BOOLEAN NOT NULL DEFAULT TRUE,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_invoices_order ON invoices(order_id);
CREATE INDEX idx_invoices_number ON invoices(invoice_number);

5.17 `invoice_line_items`

Rincian baris-baris pada invoice (breakdown biaya).

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`invoice_id`	UUID	NO	—	FK → invoices.id
`label`	VARCHAR(255)	NO	—	Label item (e.g. "Pipa PVC 2 meter")
`description`	TEXT	YES	NULL	Deskripsi tambahan
`category`	VARCHAR(50)	NO	—	Kategori: 'service', 'material', 'additional', 'fee', 'discount'
`quantity`	DECIMAL(10,2)	YES	`1`	Jumlah
`unit`	VARCHAR(50)	YES	NULL	Satuan
`unit_price`	INTEGER	YES	NULL	Harga satuan
`amount`	INTEGER	NO	`0`	Total amount untuk line item ini (Rupiah)
`purchase_id`	UUID	YES	NULL	FK → purchases.id (jika dari purchase)
`display_order`	INTEGER	NO	`0`	Urutan tampilan
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat

CREATE TABLE invoice_line_items (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id      UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    label           VARCHAR(255) NOT NULL,
    description     TEXT,
    category        VARCHAR(50) NOT NULL,
    quantity        DECIMAL(10, 2) DEFAULT 1,
    unit            VARCHAR(50),
    unit_price      INTEGER,
    amount          INTEGER NOT NULL DEFAULT 0,
    purchase_id     UUID REFERENCES purchases(id),
    display_order   INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_invoice_line_items_invoice ON invoice_line_items(invoice_id);
CREATE INDEX idx_invoice_line_items_purchase ON invoice_line_items(purchase_id);

5.18 `payments`

Catatan pembayaran untuk setiap order.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`order_id`	UUID	NO	—	FK → orders.id
`invoice_id`	UUID	NO	—	FK → invoices.id
`user_id`	UUID	NO	—	FK → users.id (pembayar)
`amount`	INTEGER	NO	—	Jumlah pembayaran (Rupiah)
`currency`	VARCHAR(3)	NO	`'IDR'`	Mata uang
`payment_method`	payment_method	NO	—	Metode pembayaran
`payment_status`	payment_status	NO	`'unpaid'`	Status pembayaran
`payment_proof_url`	TEXT	YES	NULL	URL bukti pembayaran
`transaction_ref`	VARCHAR(255)	YES	NULL	Referensi transaksi (external)
`paid_at`	TIMESTAMPTZ	YES	NULL	Waktu bayar
`refunded_at`	TIMESTAMPTZ	YES	NULL	Waktu refund
`refund_amount`	INTEGER	YES	NULL	Jumlah refund
`refund_reason`	TEXT	YES	NULL	Alasan refund
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE payments (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id            UUID NOT NULL REFERENCES orders(id),
    invoice_id          UUID NOT NULL REFERENCES invoices(id),
    user_id             UUID NOT NULL REFERENCES users(id),
    amount              INTEGER NOT NULL,
    currency            VARCHAR(3) NOT NULL DEFAULT 'IDR',
    payment_method      payment_method NOT NULL,
    payment_status      payment_status NOT NULL DEFAULT 'unpaid',
    payment_proof_url   TEXT,
    transaction_ref     VARCHAR(255),
    paid_at             TIMESTAMPTZ,
    refunded_at         TIMESTAMPTZ,
    refund_amount       INTEGER,
    refund_reason       TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_payments_invoice ON payments(invoice_id);
CREATE INDEX idx_payments_user ON payments(user_id);
CREATE INDEX idx_payments_status ON payments(payment_status);
CREATE INDEX idx_payments_method ON payments(payment_method);

5.19 `notifications`

Notifikasi push/in-app untuk user.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`user_id`	UUID	NO	—	FK → users.id (penerima)
`type`	notification_type	NO	—	Tipe notifikasi
`title`	VARCHAR(255)	NO	—	Judul notifikasi
`body`	TEXT	NO	—	Isi notifikasi
`deep_link`	VARCHAR(500)	YES	NULL	Deep link ke halaman terkait
`metadata`	JSONB	YES	NULL	Data tambahan (order_id, purchase_id, dll)
`is_read`	BOOLEAN	NO	`FALSE`	Sudah dibaca?
`read_at`	TIMESTAMPTZ	YES	NULL	Waktu dibaca
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat

CREATE TABLE notifications (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type            notification_type NOT NULL,
    title           VARCHAR(255) NOT NULL,
    body            TEXT NOT NULL,
    deep_link       VARCHAR(500),
    metadata        JSONB,
    is_read         BOOLEAN NOT NULL DEFAULT FALSE,
    read_at         TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_notifications_user ON notifications(user_id);
CREATE INDEX idx_notifications_user_unread ON notifications(user_id, is_read) WHERE is_read = FALSE;
CREATE INDEX idx_notifications_type ON notifications(type);
CREATE INDEX idx_notifications_created ON notifications(user_id, created_at DESC);

5.20 `articles`

Artikel knowledge base: tips, panduan, edukasi.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`title`	VARCHAR(500)	NO	—	Judul artikel
`slug`	VARCHAR(500)	NO	—	Slug URL-friendly (unique)
`category`	article_category	NO	—	Kategori artikel
`thumbnail_url`	TEXT	YES	NULL	URL thumbnail
`excerpt`	TEXT	YES	NULL	Ringkasan singkat
`content_html`	TEXT	NO	—	Konten HTML
`read_time_minutes`	INTEGER	YES	NULL	Estimasi waktu baca (menit)
`author`	VARCHAR(255)	YES	`'Tim HandyDirect'`	Penulis
`tags`	JSONB	YES	`'[]'`	Array tag
`is_published`	BOOLEAN	NO	`FALSE`	Sudah dipublikasi?
`published_at`	TIMESTAMPTZ	YES	NULL	Waktu publikasi
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE articles (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title               VARCHAR(500) NOT NULL,
    slug                VARCHAR(500) NOT NULL UNIQUE,
    category            article_category NOT NULL,
    thumbnail_url       TEXT,
    excerpt             TEXT,
    content_html        TEXT NOT NULL,
    read_time_minutes   INTEGER,
    author              VARCHAR(255) DEFAULT 'Tim HandyDirect',
    tags                JSONB DEFAULT '[]',
    is_published        BOOLEAN NOT NULL DEFAULT FALSE,
    published_at        TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_articles_slug ON articles(slug);
CREATE INDEX idx_articles_category ON articles(category);
CREATE INDEX idx_articles_published ON articles(is_published, published_at DESC);
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);

5.21 `faqs`

Frequently Asked Questions.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`question`	TEXT	NO	—	Pertanyaan
`answer`	TEXT	NO	—	Jawaban
`category`	faq_category	NO	—	Kategori FAQ
`display_order`	INTEGER	NO	`0`	Urutan tampilan
`is_active`	BOOLEAN	NO	`TRUE`	Status aktif
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE faqs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    question        TEXT NOT NULL,
    answer          TEXT NOT NULL,
    category        faq_category NOT NULL,
    display_order   INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_faqs_category ON faqs(category);
CREATE INDEX idx_faqs_active ON faqs(is_active, display_order);

5.22 `promotions`

Promo banner yang ditampilkan di Home user.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`title`	VARCHAR(255)	NO	—	Judul promo
`description`	TEXT	YES	NULL	Deskripsi promo
`image_url`	TEXT	NO	—	URL gambar promo
`cta_label`	VARCHAR(100)	YES	NULL	Label CTA button
`deep_link`	VARCHAR(500)	YES	NULL	Deep link ke halaman terkait
`promo_code`	VARCHAR(50)	YES	NULL	Kode promo (jika ada)
`discount_percent`	DECIMAL(5,2)	YES	NULL	Persentase diskon
`discount_amount`	INTEGER	YES	NULL	Jumlah diskon tetap (Rupiah)
`min_order_amount`	INTEGER	YES	NULL	Minimum order untuk promo
`display_order`	INTEGER	NO	`0`	Urutan tampilan
`is_active`	BOOLEAN	NO	`TRUE`	Status aktif
`valid_from`	TIMESTAMPTZ	YES	NULL	Mulai berlaku
`valid_until`	TIMESTAMPTZ	YES	NULL	Berakhir
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE promotions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title               VARCHAR(255) NOT NULL,
    description         TEXT,
    image_url           TEXT NOT NULL,
    cta_label           VARCHAR(100),
    deep_link           VARCHAR(500),
    promo_code          VARCHAR(50),
    discount_percent    DECIMAL(5, 2),
    discount_amount     INTEGER,
    min_order_amount    INTEGER,
    display_order       INTEGER NOT NULL DEFAULT 0,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    valid_from          TIMESTAMPTZ,
    valid_until         TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_promotions_active ON promotions(is_active, display_order);
CREATE INDEX idx_promotions_valid ON promotions(valid_from, valid_until);
CREATE INDEX idx_promotions_code ON promotions(promo_code) WHERE promo_code IS NOT NULL;

5.23 `worker_wallets`

Wallet/dompet digital worker. Satu worker memiliki satu wallet.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`worker_id`	UUID	NO	—	FK → users.id (UNIQUE)
`balance`	BIGINT	NO	`0`	Saldo saat ini (Rupiah)
`total_earnings`	BIGINT	NO	`0`	Total pendapatan keseluruhan
`total_withdrawn`	BIGINT	NO	`0`	Total yang sudah ditarik
`pending_earnings`	BIGINT	NO	`0`	Pendapatan yang belum masuk
`is_active`	BOOLEAN	NO	`TRUE`	Status aktif
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE worker_wallets (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    worker_id           UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    balance             BIGINT NOT NULL DEFAULT 0,
    total_earnings      BIGINT NOT NULL DEFAULT 0,
    total_withdrawn     BIGINT NOT NULL DEFAULT 0,
    pending_earnings    BIGINT NOT NULL DEFAULT 0,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_worker_wallets_worker ON worker_wallets(worker_id);

5.24 `wallet_transactions`

Riwayat transaksi wallet worker.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`wallet_id`	UUID	NO	—	FK → worker_wallets.id
`order_id`	UUID	YES	NULL	FK → orders.id (jika dari order)
`type`	wallet_tx_type	NO	—	Tipe transaksi
`amount`	INTEGER	NO	—	Jumlah (Rupiah, positif)
`balance_before`	BIGINT	NO	—	Saldo sebelum transaksi
`balance_after`	BIGINT	NO	—	Saldo setelah transaksi
`description`	TEXT	YES	NULL	Deskripsi transaksi
`reference_id`	VARCHAR(255)	YES	NULL	ID referensi eksternal
`status`	wallet_tx_status	NO	`'pending'`	Status transaksi
`completed_at`	TIMESTAMPTZ	YES	NULL	Waktu selesai
`created_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu dibuat
`updated_at`	TIMESTAMPTZ	NO	`NOW()`	Waktu diupdate

CREATE TABLE wallet_transactions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_id           UUID NOT NULL REFERENCES worker_wallets(id) ON DELETE CASCADE,
    order_id            UUID REFERENCES orders(id),
    type                wallet_tx_type NOT NULL,
    amount              INTEGER NOT NULL,
    balance_before      BIGINT NOT NULL,
    balance_after       BIGINT NOT NULL,
    description         TEXT,
    reference_id        VARCHAR(255),
    status              wallet_tx_status NOT NULL DEFAULT 'pending',
    completed_at        TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_wallet_tx_wallet ON wallet_transactions(wallet_id);
CREATE INDEX idx_wallet_tx_order ON wallet_transactions(order_id);
CREATE INDEX idx_wallet_tx_type ON wallet_transactions(type);
CREATE INDEX idx_wallet_tx_status ON wallet_transactions(status);
CREATE INDEX idx_wallet_tx_created ON wallet_transactions(wallet_id, created_at DESC);

6. Migration Order

Urutan migrasi database harus mengikuti dependency graph (tabel yang di-referensi harus dibuat lebih dulu):

Phase 1 — Enum Types (no dependencies)
  └── All CREATE TYPE statements

Phase 2 — Independent Tables (no FK dependencies)
  ├── users
  ├── categories
  ├── articles
  ├── faqs
  └── promotions

Phase 3 — First-level Dependencies
  ├── refresh_tokens        (depends on: users)
  ├── worker_profiles       (depends on: users)
  ├── services              (depends on: categories)
  ├── worker_wallets        (depends on: users)
  └── notifications         (depends on: users)

Phase 4 — Second-level Dependencies
  ├── worker_services       (depends on: users, services)
  └── orders                (depends on: users, services, categories)

Phase 5 — Order-dependent Tables
  ├── order_photos          (depends on: orders)
  ├── order_timeline        (depends on: orders, users)
  ├── purchases             (depends on: orders, users)
  ├── chat_messages         (depends on: orders, users)
  ├── reviews               (depends on: orders, users)
  ├── invoices              (depends on: orders)
  └── wallet_transactions   (depends on: worker_wallets, orders)

Phase 6 — Third-level Dependencies
  ├── purchase_risk_flags   (depends on: purchases, users)
  ├── purchase_audit_logs   (depends on: purchases, users)
  ├── review_tags           (depends on: reviews)
  ├── invoice_line_items    (depends on: invoices, purchases)
  └── payments              (depends on: orders, invoices, users)

7. Indexes Summary

Table	Total Indexes	Key Index Strategy
`users`	4	Role, active status, geospatial (GIST)
`refresh_tokens`	3	User lookup, token hash, expiry
`worker_profiles`	4	User, availability, rating, verification
`categories`	2	Slug, active + order
`services`	3	Category, slug, active
`worker_services`	2 + UNIQUE	Worker, service
`orders`	9	User, worker, status, service, composite
`order_photos`	1	Order
`order_timeline`	3	Order, event, composite time
`purchases`	5	Order, worker, status, composite, time
`purchase_risk_flags`	3	Purchase, type, partial (unresolved)
`purchase_audit_logs`	4	Purchase, action, actor, time
`chat_messages`	4	Order, sender, composite time, partial (unread)
`reviews`	5	Order, user, worker, composite rating, time
`review_tags`	2	Review, tag
`invoices`	2	Order, invoice number
`invoice_line_items`	2	Invoice, purchase
`payments`	5	Order, invoice, user, status, method
`notifications`	4	User, partial (unread), type, composite time
`articles`	4	Slug, category, published, GIN (tags)
`faqs`	2	Category, active + order
`promotions`	3	Active + order, validity, partial (promo code)
`worker_wallets`	1 + UNIQUE	Worker
`wallet_transactions`	5	Wallet, order, type, status, composite time
TOTAL	~81 indexes

8. Notes & Conventions

8.1 Naming Conventions

Convention	Rule	Example
Table names	snake_case, plural	`worker_profiles`, `chat_messages`
Column names	snake_case	`full_name`, `created_at`
Primary keys	Always `id` (UUID)	`id UUID PRIMARY KEY`
Foreign keys	`{referenced_table_singular}_id`	`user_id`, `order_id`
Timestamps	Always TIMESTAMPTZ	`created_at`, `updated_at`
Boolean columns	Prefix `is_` or `has_`	`is_active`, `is_read`, `needs_clarification`
Enum types	snake_case	`user_role`, `order_status`
Indexes	`idx_{table}_{column(s)}`	`idx_orders_user_status`

8.2 Monetary Values

Semua nilai uang disimpan dalam satuan terkecil (Rupiah, tanpa desimal) sebagai INTEGER atau BIGINT.
Contoh: Rp150.000 disimpan sebagai 150000.
Ini menghindari masalah floating-point arithmetic.

8.3 Soft Delete

Hanya tabel users yang menggunakan soft delete (deleted_at).
Tabel lain menggunakan ON DELETE CASCADE dari parent.

8.4 Timestamps

Seluruh timestamp menggunakan TIMESTAMPTZ (timestamp with time zone).
Semua waktu disimpan dalam UTC dan dikonversi di client/frontend.

8.5 JSONB Columns

metadata (order_timeline, notifications) — untuk data fleksibel yang tidak perlu kolom dedicated.
certificate_urls (worker_profiles) — array URL sertifikat.
tags (articles) — array tag artikel.
old_data / new_data (purchase_audit_logs) — snapshot data sebelum/sesudah perubahan.

8.6 Geospatial

Menggunakan PostGIS extension untuk geographic queries.
Index GIST pada users untuk pencarian nearby workers.
Formula jarak menggunakan ST_DistanceSphere() atau ST_DWithin().

-- Contoh query: mencari worker dalam radius 10km
SELECT u.*, wp.*, 
       ST_DistanceSphere(
           ST_MakePoint(u.longitude, u.latitude),
           ST_MakePoint(:user_lng, :user_lat)
       ) / 1000.0 AS distance_km
FROM users u
JOIN worker_profiles wp ON wp.user_id = u.id
WHERE u.role = 'worker'
  AND u.is_active = TRUE
  AND wp.is_available = TRUE
  AND ST_DWithin(
      ST_MakePoint(u.longitude, u.latitude)::geography,
      ST_MakePoint(:user_lng, :user_lat)::geography,
      10000  -- 10km in meters
  )
ORDER BY distance_km ASC;

8.7 Trigger: Auto-update `updated_at`

CREATE OR REPLACE FUNCTION trigger_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at column
CREATE TRIGGER set_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON worker_profiles
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON categories
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON services
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON purchases
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON reviews
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON invoices
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON payments
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON faqs
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON promotions
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON worker_wallets
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON wallet_transactions
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

8.8 Required PostgreSQL Extensions

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";      -- For gen_random_uuid() fallback
CREATE EXTENSION IF NOT EXISTS "pgcrypto";        -- For gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS "postgis";         -- For geospatial queries

📌 Document Version: 1.0.0 | Last Updated: 2026-05-30 | Total Tables: 24 | Total Indexes: ~81

1. Architecture Overview

Key Principles

2. RBAC & Authentication Strategy

JWT Token Payload

Middleware Flow

Refresh Token Strategy

3. Enum Types

4. Entity Relationship Diagram (ERD)

5. Table Definitions

5.1 users

5.2 refresh_tokens

5.3 worker_profiles

5.4 categories

5.5 services

5.6 worker_services

5.7 orders

5.8 order_photos

5.9 order_timeline

5.10 purchases

5.11 purchase_risk_flags

5.12 purchase_audit_logs

5.13 chat_messages

5.14 reviews

5.15 review_tags

5.16 invoices

5.17 invoice_line_items

5.18 payments

5.19 notifications

5.20 articles

5.21 faqs

5.22 promotions

5.23 worker_wallets

5.24 wallet_transactions