Arsitektur Teknis Kasir POS PWA Offline-First & Ritel (UNT-RETAIL)

1. Arsitektur Offline-First Kasir PWA#

Untuk mengatasi ketidakstabilan jaringan internet pedesaan, modul Kasir POS BUMDes dirancang menggunakan arsitektur Offline-First Progressive Web App (PWA) dengan pemisahan lapisan data lokal dan server pusat:

[ PERANGKAT KASIR / TABLET BROWSER ]
+-------------------------------------------------------------+
| UI Kasir (React / Next.js Client Component) |
| Barcode Scanner Event Listener + Virtual Pinpad |
+-------------------------------------------------------------+
 |
 +-------------------+-------------------+
 | |
 v v
[ Service Worker Cache ] [ Basis Data IndexedDB Lokal ]
(App Shell, JS, CSS, Audio Beep) - pos_products_cache (Katalog Offline)
 - pos_active_shift (Modal & Shift)
 - pos_sync_queue (Antrean Transaksi)
 |
 Background Sync (Online Event)
 |
 v
 +----------------------------------+
 | Server Supabase / PostgreSQL |
 | - RPC sync_pos_offline_batch |
 | - Update Stok & Jurnal SAK |
 +----------------------------------+

2. Skema Basis Data Relasional (DDL SQL)#

sql
-- 1. Sesi Kerja Kasir (Cashier Shifts)
CREATE TABLE pos_cashier_shifts (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-RETAIL',
 cashier_user_id UUID NOT NULL,
 shift_number INT NOT NULL DEFAULT 1,
 opened_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 closed_at TIMESTAMPTZ,
 opening_cash_float NUMERIC(15,2) NOT NULL DEFAULT 300000.00,
 expected_closing_cash NUMERIC(15,2) DEFAULT 0.00,
 actual_closing_cash NUMERIC(15,2) DEFAULT 0.00,
 cash_shortage_or_overage NUMERIC(15,2) DEFAULT 0.00,
 shift_status VARCHAR(20) NOT NULL DEFAULT 'open' -- 'open', 'closed'
);

-- 2. Master Produk Barcode Ritel
CREATE TABLE pos_products (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-RETAIL',
 barcode VARCHAR(50) UNIQUE NOT NULL,
 sku_code VARCHAR(30) UNIQUE NOT NULL,
 name VARCHAR(150) NOT NULL,
 category VARCHAR(50) NOT NULL,
 uom VARCHAR(20) NOT NULL DEFAULT 'pcs',
 retail_price NUMERIC(15,2) NOT NULL,
 wholesale_partner_price NUMERIC(15,2) NOT NULL,
 current_avg_cost NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 stock_quantity INT NOT NULL DEFAULT 0,
 min_stock_alert INT NOT NULL DEFAULT 5,
 is_active BOOLEAN DEFAULT TRUE,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. Transaksi Penjualan Kasir (POS Orders)
CREATE TABLE pos_orders (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 unit_id VARCHAR(50) NOT NULL DEFAULT 'UNT-RETAIL',
 shift_id UUID REFERENCES pos_cashier_shifts(id) NOT NULL,
 invoice_number VARCHAR(50) UNIQUE NOT NULL,
 customer_type VARCHAR(20) NOT NULL DEFAULT 'retail', -- 'retail', 'mitra_warung'
 customer_name VARCHAR(100) DEFAULT 'Warga Umum',
 total_gross_amount NUMERIC(15,2) NOT NULL,
 discount_amount NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 tax_amount NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 net_total_amount NUMERIC(15,2) NOT NULL,
 payment_method VARCHAR(30) NOT NULL, -- 'cash', 'qris', 'transfer', 'tempo_warung'
 amount_paid NUMERIC(15,2) NOT NULL,
 change_amount NUMERIC(15,2) NOT NULL DEFAULT 0.00,
 is_synced_from_offline BOOLEAN DEFAULT FALSE,
 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 4. Item Detail Penjualan (POS Order Items)
CREATE TABLE pos_order_items (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 order_id UUID REFERENCES pos_orders(id) ON DELETE CASCADE NOT NULL,
 product_id UUID REFERENCES pos_products(id) NOT NULL,
 quantity INT NOT NULL,
 unit_price NUMERIC(15,2) NOT NULL,
 unit_cost NUMERIC(15,2) NOT NULL,
 subtotal_amount NUMERIC(15,2) NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

3. Stored Procedure Batch Sinkronisasi Transaksi Offline#

Fungsi sync_pos_offline_orders_batch memproses antrean transaksi yang terkumpul selama kasir dalam mode offline:

sql
CREATE OR REPLACE FUNCTION sync_pos_offline_orders_batch(
 p_shift_id UUID,
 p_orders_json JSONB
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS func
DECLARE
 v_order RECORD;
 v_item RECORD;
 v_inserted_order_id UUID;
 v_total_processed INT := 0;
BEGIN
 FOR v_order IN SELECT * FROM jsonb_to_recordset(p_orders_json) AS x(
 invoice_number VARCHAR,
 customer_type VARCHAR,
 customer_name VARCHAR,
 total_gross_amount NUMERIC,
 discount_amount NUMERIC,
 net_total_amount NUMERIC,
 payment_method VARCHAR,
 amount_paid NUMERIC,
 change_amount NUMERIC,
 created_at TIMESTAMPTZ,
 items JSONB
 )
 LOOP
 -- Hindari duplikasi invoice jika sudah pernah tersinkron
 IF NOT EXISTS (SELECT 1 FROM pos_orders WHERE invoice_number = v_order.invoice_number) THEN
 -- 1. Insert Header Order
 INSERT INTO pos_orders (
 unit_id, shift_id, invoice_number, customer_type, customer_name,
 total_gross_amount, discount_amount, net_total_amount, payment_method,
 amount_paid, change_amount, is_synced_from_offline, created_at
 ) VALUES (
 'UNT-RETAIL', p_shift_id, v_order.invoice_number, v_order.customer_type, v_order.customer_name,
 v_order.total_gross_amount, v_order.discount_amount, v_order.net_total_amount, v_order.payment_method,
 v_order.amount_paid, v_order.change_amount, TRUE, v_order.created_at
 ) RETURNING id INTO v_inserted_order_id;

 -- 2. Insert Detail Items & Potong Stok Produk
 FOR v_item IN SELECT * FROM jsonb_to_recordset(v_order.items) AS y(
 product_id UUID,
 quantity INT,
 unit_price NUMERIC,
 unit_cost NUMERIC,
 subtotal_amount NUMERIC
 )
 LOOP
 INSERT INTO pos_order_items (
 order_id, product_id, quantity, unit_price, unit_cost, subtotal_amount
 ) VALUES (
 v_inserted_order_id, v_item.product_id, v_item.quantity,
 v_item.unit_price, v_item.unit_cost, v_item.subtotal_amount
 );

 -- Potong stok fisik produk
 UPDATE pos_products
 SET stock_quantity = stock_quantity - v_item.quantity,
 updated_at = NOW()
 WHERE id = v_item.product_id;
 END LOOP;

 v_total_processed := v_total_processed + 1;
 END IF;
 END LOOP;

 RETURN jsonb_build_object(
 'success', true,
 'processed_orders', v_total_processed,
 'message', 'Sinkronisasi transaksi kasir offline berhasil diproses.'
 );
END;
func;

4. Keamanan & Row Level Security (RLS)#

Seluruh tabel dilindungi oleh RLS untuk menjaga kerahasiaan data omzet antar unit bisnis BUMDes:

sql
ALTER TABLE pos_cashier_shifts ENABLE ROW LEVEL SECURITY;
ALTER TABLE pos_products ENABLE ROW LEVEL SECURITY;
ALTER TABLE pos_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE pos_order_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY rls_pos_isolation ON pos_orders
 FOR ALL
 USING (
 auth.jwt() ->> 'role' = 'super_admin' OR
 (auth.jwt() ->> 'unit_id' = 'UNT-RETAIL' AND unit_id = 'UNT-RETAIL')
 );