Store Schema (SQLite)
Store Router menggunakan SQLite (WAL mode) sebagai database lokal. File database tunggal (store.db) berisi semua data operasional toko.
Migration Files
Store schema dibangun dari 26 migration files (migrations/store/001 - 026). Berikut tabel-tabel kunci.
Event Log (001_eventlog.sql)
event_log -- Event Sourcing
Tabel paling penting di Store Router. Setiap perubahan data dicatat di sini dan di-sync ke Cloud Hub.
| Column | Type | Deskripsi |
|---|---|---|
id | INTEGER PK | Auto-increment local ID |
event_id | TEXT UNIQUE | UUIDv7 global unique |
origin | TEXT | Source identifier (e.g. "pos-01") |
origin_type | TEXT | pos, store-admin, mobile |
store_id | TEXT | Store ID |
type | TEXT | Event type (sale.completed, stock.adjusted) |
sequence | INTEGER | Per-origin sequence number |
payload | TEXT | JSON event data |
crc32 | INTEGER | Integrity checksum |
synced | INTEGER | 0=pending, 1=synced |
sync_batch_id | TEXT | Batch ID saat claimed for sync |
sequence_tracker -- Sequence per Origin
Tracking nomor sequence terakhir per origin untuk mendeteksi gaps.
POS Tables (004_pos.sql)
sessions -- Shift Kasir
| Column | Type | Deskripsi |
|---|---|---|
session_id | TEXT UNIQUE | Shift ID |
store_id | TEXT | Store ID |
pos_id | TEXT | POS terminal ID |
cashier_id | TEXT | Cashier user ID |
status | TEXT | open, closed |
starting_cash | REAL | Kas awal |
counted_cash | REAL | Kas akhir (blind count) |
system_cash | REAL | Kas akhir (kalkulasi sistem) |
cash_difference | REAL | Selisih kas |
transactions -- Header Transaksi
| Column | Type | Deskripsi |
|---|---|---|
transaction_id | TEXT UNIQUE | Format: TXN-{STORE}-{DATE}- |
session_id | TEXT FK | Shift yang aktif |
member_id | TEXT | Member ID (nullable) |
status | TEXT | open, completed, cancelled |
subtotal | REAL | Total sebelum diskon |
discount_amount | REAL | Total diskon |
discount_type | TEXT | auto_promo, voucher, points, tier |
total_amount | REAL | subtotal - discount |
total_paid | REAL | Total yang dibayar |
change_amount | REAL | Kembalian |
rounding_amount | REAL | Pembulatan |
transaction_items -- Line Items
Detail item per transaksi termasuk scan_method (barcode, rfid, manual) dan rfid_epc untuk tracking.
payments -- Multi-Payment
| Column | Type | Deskripsi |
|---|---|---|
payment_id | TEXT UNIQUE | Payment ID |
transaction_id | TEXT FK | Header transaksi |
method | TEXT | cash, debit, credit, qris, ewallet |
amount | REAL | Jumlah bayar |
bank_name | TEXT | BCA, Mandiri, BNI, etc |
edc_photo_id | TEXT | FK ke foto bukti EDC |
status | TEXT | recorded, verified, settled, reconciled, acknowledged |
edc_photos -- Bukti Pembayaran Non-Tunai
Foto struk EDC/QRIS yang diambil saat transaksi.
transaction_flags -- Operational Flags
Flag seperti stock_minus, wrong_location, rfid_fallback, manual_entry untuk audit trail.
Inventory (003_inventory.sql)
reservations -- Stock Reservations
Reservation stok saat transaksi sedang berjalan (belum dibayar) untuk mencegah overselling.
SKU & Member Cache (004_pos.sql)
sku_cache -- Cache Produk Lokal
| Column | Type | Deskripsi |
|---|---|---|
sku | TEXT PK | SKU produk |
name | TEXT | Nama produk |
price | REAL | Harga jual |
category | TEXT | Kategori |
barcode | TEXT | Barcode utama |
stock | INTEGER | Stok saat ini |
location | TEXT | floor, warehouse |
Cache ini di-update oleh Sync Agent saat menerima command product.sync dari Cloud Hub.
member_cache -- Cache Member Lokal
Data member yang di-cache untuk lookup cepat saat transaksi offline.
Cash Operations (007_cashops.sql)
| Tabel | Fungsi |
|---|---|
petty_cash | Transaksi kas kecil |
bank_deposits | Setoran ke bank |
settlement | Settlement akhir hari |
cash_denominations | Pecahan uang saat hitung kas |
Reconciliation (005_reconciliation.sql)
reconciliation
3-way reconciliation: POS system cash vs physical count vs EDC/bank statements.
Transfer (009_transfer.sql)
| Tabel | Fungsi |
|---|---|
transfer_orders | Header transfer stok |
transfer_items | Detail item transfer |
Receiving (011_receiving.sql)
| Tabel | Fungsi |
|---|---|
receiving_orders | Header penerimaan barang |
receiving_items | Detail item yang diterima |
Store Session (012_store_session.sql)
Buka/tutup toko per hari. Harus ada store session aktif sebelum kasir bisa buka shift.
Opname (013_opname.sql)
| Tabel | Fungsi |
|---|---|
opname_sessions | Header stock opname |
opname_items | Detail count per SKU |
opname_discrepancies | Selisih stok yang ditemukan |
Idempotency (015_idempotency.sql)
idempotency_keys
Menyimpan idempotency keys untuk mencegah duplikasi request dari POS Electron.
Audit (017_audit.sql)
audit_log
Local audit log untuk semua operasi penting di toko.