Arsitektur Teknis: Unit Ekowisata & Ticketing (UNT-WISATA)

Dokumen ini mendokumentasikan spesifikasi arsitektur data relasional, fungsi prosedur atomik, dan protokol komunikasi perangkat keras untuk sistem loket tiket gelang QR dan validasi gerbang otomatis kawasan wisata.

1. Skema Basis Data Relasional (PostgreSQL DDL)#

1.1 Tabel Katalog Produk Tiket & Wahana (tourism_ticket_products)

sql
CREATE TABLE IF NOT EXISTS public.tourism_ticket_products (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 tenant_id UUID NOT NULL REFERENCES public.tenants(id),
 product_code VARCHAR(50) NOT NULL UNIQUE, -- 'TKT-GATE', 'TKT-ATV', 'TKT-FOX', 'RENT-TENT'
 name VARCHAR(150) NOT NULL,
 category VARCHAR(50) NOT NULL, -- 'gate_entry', 'ride', 'rental', 'guide_service'
 price NUMERIC(12,2) NOT NULL,
 insurance_fee NUMERIC(12,2) DEFAULT 0.00, -- Komponen premi titipan
 local_tax_rate NUMERIC(5,2) DEFAULT 10.00, -- Pajak PBJT Hiburan 10%
 is_active BOOLEAN DEFAULT TRUE,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

1.2 Tabel Pesanan & Transaksi Loket (tourism_orders)

sql
CREATE TABLE IF NOT EXISTS public.tourism_orders (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 tenant_id UUID NOT NULL REFERENCES public.tenants(id),
 order_number VARCHAR(100) NOT NULL UNIQUE, -- 'ORD-WIS/20260914/0045'
 cashier_id UUID REFERENCES auth.users(id),
 customer_name VARCHAR(150) DEFAULT 'Pengunjung Umum',
 total_amount NUMERIC(15,2) NOT NULL,
 payment_method VARCHAR(50) NOT NULL, -- 'cash', 'qris', 'bank_transfer'
 payment_status VARCHAR(30) DEFAULT 'paid', -- 'pending', 'paid', 'refunded'
 cash_tendered NUMERIC(15,2),
 cash_change NUMERIC(15,2),
 created_at TIMESTAMPTZ DEFAULT NOW()
);

1.3 Tabel Gelang Tiket Ber-QR Code (tourism_ticket_items)

sql
CREATE TABLE IF NOT EXISTS public.tourism_ticket_items (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 tenant_id UUID NOT NULL REFERENCES public.tenants(id),
 order_id UUID NOT NULL REFERENCES public.tourism_orders(id) ON DELETE CASCADE,
 product_id UUID NOT NULL REFERENCES public.tourism_ticket_products(id),
 qr_code VARCHAR(100) NOT NULL UNIQUE, -- 'QRW-987214981'
 status VARCHAR(30) DEFAULT 'unused', -- 'unused', 'checked_in', 'redeemed', 'expired'
 valid_date DATE NOT NULL,
 first_scanned_at TIMESTAMPTZ,
 scanned_device_id VARCHAR(100),
 created_at TIMESTAMPTZ DEFAULT NOW()
);

1.4 Tabel Kemitraan Pemandu Wisata (tourism_guide_assignments)

sql
CREATE TABLE IF NOT EXISTS public.tourism_guide_assignments (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 tenant_id UUID NOT NULL REFERENCES public.tenants(id),
 ticket_item_id UUID NOT NULL REFERENCES public.tourism_ticket_items(id),
 guide_name VARCHAR(150) NOT NULL,
 guide_nik VARCHAR(16) NOT NULL,
 fee_total NUMERIC(12,2) NOT NULL, -- Contoh Rp 100.000
 guide_share NUMERIC(12,2) NOT NULL, -- 90% (Rp 90.000)
 bumdes_share NUMERIC(12,2) NOT NULL, -- 10% (Rp 10.000)
 payout_status VARCHAR(30) DEFAULT 'pending', -- 'pending', 'settled'
 settled_at TIMESTAMPTZ,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

2. Prosedur Tersimpan Atomik (Stored Procedures)#

2.1 Eksekusi Penjualan Tiket Loket Terpadu (process_tourism_ticket_sale)

sql
CREATE OR REPLACE FUNCTION public.process_tourism_ticket_sale(
 p_tenant_id UUID,
 p_cashier_id UUID,
 p_items JSONB, -- Array of [{product_id, quantity, unit_price}]
 p_payment_method VARCHAR,
 p_cash_tendered NUMERIC
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS func
DECLARE
 v_order_id UUID;
 v_order_no VARCHAR(100);
 v_total_amount NUMERIC(15,2) := 0;
 v_item RECORD;
 v_product RECORD;
 v_qr_code VARCHAR(100);
 v_i INT;
 v_generated_tickets JSONB := '[]'::JSONB;
BEGIN
 v_order_no := 'ORD-WIS/' || TO_CHAR(NOW(), 'YYYYMMDD') || '/' || LPAD(FLOOR(RANDOM()*10000)::TEXT, 4, '0');

 -- 1. Hitung Total Pesanan
 FOR v_item IN SELECT * FROM jsonb_to_recordset(p_items) AS x(product_id UUID, quantity INT, unit_price NUMERIC)
 LOOP
 v_total_amount := v_total_amount + (v_item.quantity * v_item.unit_price);
 END LOOP;

 -- 2. Buat Header Pesanan
 INSERT INTO public.tourism_orders (
 tenant_id, order_number, cashier_id, total_amount, payment_method, 
 payment_status, cash_tendered, cash_change
 ) VALUES (
 p_tenant_id, v_order_no, p_cashier_id, v_total_amount, p_payment_method, 
 'paid', p_cash_tendered, (p_cash_tendered - v_total_amount)
 ) RETURNING id INTO v_order_id;

 -- 3. Terbitkan Tiket Gelang QR Individual
 FOR v_item IN SELECT * FROM jsonb_to_recordset(p_items) AS x(product_id UUID, quantity INT, unit_price NUMERIC)
 LOOP
 FOR v_i IN 1..v_item.quantity LOOP
 v_qr_code := 'QRW-' || SUBSTRING(MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT) FROM 1 FOR 12);
 
 INSERT INTO public.tourism_ticket_items (
 tenant_id, order_id, product_id, qr_code, status, valid_date
 ) VALUES (
 p_tenant_id, v_order_id, v_item.product_id, v_qr_code, 'unused', CURRENT_DATE
 );

 v_generated_tickets := v_generated_tickets || jsonb_build_object(
 'qr_code', v_qr_code,
 'product_id', v_item.product_id
 );
 END LOOP;
 END LOOP;

 RETURN jsonb_build_object(
 'success', true,
 'order_id', v_order_id,
 'order_number', v_order_no,
 'total_amount', v_total_amount,
 'tickets', v_generated_tickets
 );
END;
func;

3. Protokol Validasi Gerbang Otomatis (Turnstile Integration API)#

Ketika pengunjung memindai gelang pada modul tripod turnstile gerbang, pembaca optik mengirimkan HTTP POST request ke endpoint gateway internal BUMDes:

POST /api/tourism/gate/validate-scan
Payload: { "qr_code": "QRW-987214981", "device_id": "TURNSTILE-GATE-01" }

Logika Eksekusi Server (< 150 ms):

1. Periksa apakah qr_code terdaftar pada tanggal hari ini dan berstatus unused.
2. Jika VALID: Update status menjadi checked_in, catat timestamp, dan kirimkan sinyal relay OPEN_RELAY_PULSE_200MS ke solenoid palang gerbang turnstile.
3. Jika TIDAK VALID / SUDAH DIGUNAKAN: Nyalakan buzzer peringatan merah dan tolak pembukaan palang.