● Technical Documentation

Arinex F&B

Dokumentasi database PostgreSQL lengkap untuk analisa, pembelajaran, maintenance, development backend, frontend/PWA, analytics, forecasting dan reusable client template.

PostgreSQL POS ERP F&B Flask Python Analytics Forecasting Reusable Core

1. Overview Project

Project ini merupakan fondasi Arinex Core untuk membangun software ERP yang dapat digunakan kembali untuk berbagai klien.

Tujuannya bukan membuat aplikasi sekali pakai untuk satu kedai, melainkan membuat core system yang dapat dijual dan dikustomisasi berulang.

PostgreSQL Source of truth database

3 Roles OWNER • STAFF • CREW

POS Sales • Payment • Shift

ERP Inventory • Purchasing • Finance

Forecast Demand • Recommendation

Reusable Template untuk client

Database aktual:


bdbfbmmf_arinex_prototype_kedai_fnb

Nama konseptual:


arinex_Template_fnb

2. Setup Database

2.1 Membuat database

CREATE DATABASE arinex_prototype_kedai_fnb;

Pada shared hosting/cPanel, nama database biasanya mendapatkan prefix.

bdbfbmmf_arinex_prototype_kedai_fnb

2.2 Masalah phpPgAdmin yang ditemukan

Pada salah satu menu SQL phpPgAdmin, multi-statement dibungkus menjadi:

SELECT COUNT(*) AS total FROM (
    CREATE TABLE ...
) AS sub;

Akibatnya:

ERROR: syntax error at or near "CREATE"

Solusi project: gunakan menu SQL di pojok kanan atas yang menjalankan SQL PostgreSQL secara normal.

2.3 Test SQL editor

CREATE TABLE IF NOT EXISTS arinex_test (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO arinex_test (name)
VALUES ('Arinex Prototype');

SELECT * FROM arinex_test;

2.4 Standar migration

CREATE TABLE IF NOT EXISTS ...
CREATE INDEX IF NOT EXISTS ...
INSERT INTO ...
ON CONFLICT DO NOTHING;

3. Architecture

ARINEX CORE │ ┌────────────────────┼────────────────────┐ │ │ │ FRONTEND FLASK PYTHON /PWA BACKEND ANALYTICS │ │ │ └────────────────────┼────────────────────┘ │ POSTGRESQL │ ┌────────────────┼────────────────┐ │ │ │ POS INVENTORY PURCHASING │ │ │ └────────────────┼────────────────┘ │ ACCOUNTING │ REPORTING

Source of truth

PostgreSQL = data utama.
Flask = business logic.
Frontend = presentation.
Python = analytics/forecast.

4. Modules Database 01–15

No.	Module	Fungsi
01	Organization	Company, branch, warehouse, storage location.
02	User & Access	Users, roles, permissions, employee, branch access.
03	Product Master	Produk, kategori, brand, unit, barcode.
04	Supplier	Supplier, contact, supplier products.
05	Inventory	Balance, lots, movement ledger.
06	Purchasing	PR → Approval → PO → GR.
07	Recipe / HPP	Recipe, production, ingredient cost.
08	Repack	ORI → RAW, conversion.
09	POS	Sales, payment, shift, return.
10	Control	Stock opname, expense, approval, audit.
11	Analytics	Summary, demand, forecast.
12	Pricing	Price list, promotion, voucher.
13	Finance	Accounting, journal, cash/bank.
14	ERP Control	Document number, reference, events, locks.
15	Core Template	Tenant, branding, feature modules, subscription.

5. Table Catalog

Organization

Table	Fungsi	Parent / Relasi
`companies`	Root client/company.	Root.
`branches`	Outlet.	companies.
`warehouses`	Gudang.	branches.
`storage_locations`	Rak/lokasi.	warehouses.

User & Access

Table	Fungsi
`users`	Akun login.
`roles`	Role.
`permissions`	Hak akses.
`user_roles`	User → role.
`role_permissions`	Role → permission.
`user_branches`	User → branch.
`departments`	Departemen.
`positions`	Jabatan.
`employees`	Data employee.
`login_logs`	Riwayat login.

Product Master

Table	Fungsi
`categories`	Kategori produk.
`brands`	Brand.
`units`	Satuan.
`products`	SKU, barcode, nama, harga, stock rule.
`product_units`	Konversi unit.
`product_barcodes`	Barcode.

Supplier

Table	Fungsi
`suppliers`	Master supplier.
`supplier_contacts`	Kontak supplier.
`supplier_products`	Harga, MOQ, lead time.

Inventory

Table	Fungsi
`inventory_balances`	Saldo stok terkini.
`inventory_lots`	Batch, expiry, cost.
`inventory_movements`	Ledger stok.

Purchasing

Table	Fungsi
`purchase_requisitions`	Permintaan pembelian.
`purchase_requisition_items`	Detail PR.
`purchase_orders`	PO supplier.
`purchase_order_items`	Detail PO.
`goods_receipts`	Penerimaan barang.
`goods_receipt_items`	Detail penerimaan.

F&B

Table	Fungsi
`recipes`	Recipe version.
`recipe_items`	Komposisi bahan.
`production_orders`	Production batch.
`production_items`	Detail konsumsi bahan.
`repack_orders`	Header repack.
`repack_components`	Input repack.
`repack_outputs`	Output repack.
`repack_conversion_rules`	Aturan konversi.

POS

Table	Fungsi
`payment_methods`	Cash, QRIS, Transfer, Card, Voucher.
`customers`	Customer/member.
`cashier_shifts`	Shift kasir.
`cash_movements`	Cash in/out.
`sales`	Header transaksi.
`sale_items`	Detail transaksi.
`sale_payments`	Pembayaran.
`sale_returns`	Retur.
`sale_return_items`	Detail retur.
`customer_point_transactions`	Point ledger.

Control & Audit

Table	Fungsi
`stock_opnames`	Stock opname.
`stock_opname_items`	Physical count.
`expense_categories`	Kategori expense.
`expenses`	Pengeluaran.
`approval_requests`	Approval header.
`approval_steps`	Approval level.
`audit_logs`	Audit.
`system_notifications`	Notifikasi.

Analytics

Table	Fungsi
`sales_daily_summary`	Summary penjualan harian.
`product_demand_history`	Demand history.
`forecast_models`	Metadata model.
`forecast_results`	Forecast.
`purchase_recommendations`	Smart purchasing.
`store_daily_summary`	KPI store.

Pricing

Table	Fungsi
`price_lists`	Master price list.
`price_list_items`	Harga produk.
`product_price_history`	Histori harga.
`promotions`	Promo.
`promotion_products`	Produk promo.
`promotion_rules`	Rule promo.
`vouchers`	Voucher.
`voucher_transactions`	Pemakaian voucher.
`promotion_transactions`	Pemakaian promo.

Finance

Table	Fungsi
`accounting_periods`	Periode accounting.
`chart_of_accounts`	COA.
`journal_entries`	Header jurnal.
`journal_entry_lines`	Debit/kredit.
`cash_accounts`	Kas/bank/e-wallet.
`cash_account_transactions`	Cash/bank ledger.

ERP Control

Table	Fungsi
`document_number_configs`	Nomor dokumen.
`document_number_logs`	History nomor.
`system_settings`	System settings.
`transaction_references`	Relasi dokumen.
`erp_transaction_locks`	Anti duplicate process.
`transaction_events`	Event system.

Core Template

Table	Fungsi
`tenant_profiles`	Profil client.
`tenant_branding`	Branding client.
`feature_modules`	Daftar fitur.
`company_modules`	Feature flags.
`business_settings`	Setting bisnis.
`tax_configs`	Pajak.
`service_charge_configs`	Service charge.
`receipt_configs`	Printer/receipt.
`pos_configs`	POS settings.
`service_plans`	Plan layanan.
`company_subscriptions`	Subscription client.

6. RBAC

USER │ ├── USER_ROLES │ ↓ │ ROLES │ ↓ │ ROLE_PERMISSIONS │ ↓ │ PERMISSIONS │ └── USER_BRANCHES ↓ BRANCHES

OWNER

Owner mempunyai akses penuh sesuai seluruh permission.

STAFF

Staff mengakses POS, Store Information, inventory operasional, purchasing tertentu, report dan analytics sesuai permission.

KASIR

Kasir difokuskan ke transaksi POS, shift dan cash movement.

Permission wajib diverifikasi di backend. Frontend hanya mengontrol tampilan, bukan keamanan sebenarnya.

7. Inventory Engine

inventory_balances

Menyimpan posisi stok saat ini berdasarkan product, warehouse dan location.

inventory_movements

Menyimpan histori setiap pergerakan. Inilah ledger stok.

Aturan utama

Jangan menjadikan products.current_stock sebagai source of truth.

Contoh histori

08:00 PURCHASE_RECEIPT +10 KG 10:00 SALE -1 KG 12:00 SALE -2 KG 15:00 WASTE -0.5 KG ------------------------------- SALDO AKHIR 6.5 KG

8. Purchasing

SALES HISTORY + CURRENT STOCK + FORECAST + SAFETY STOCK + LEAD TIME + MOQ ↓ PURCHASE RECOMMENDATION ↓ REVIEW ↓ PURCHASE REQUISITION ↓ APPROVAL ↓ PURCHASE ORDER ↓ SUPPLIER ↓ GOODS RECEIPT ↓ INVENTORY

PR

Purchase Requisition adalah permintaan internal.

PO

Purchase Order adalah order ke supplier. PO tidak otomatis menambah stok.

Goods Receipt

Goods Receipt menjadi titik saat barang benar-benar diterima dan inventory dapat diposting.

Partial Receipt

PO Kopi 10 KG Susu 20 L Diterima Kopi 10 KG Susu 15 L STATUS: PARTIAL_RECEIVED

9. F&B Recipe, HPP, Production & Repack

Contoh Recipe

LATTE ├── Coffee 18 gram ├── Milk 150 ml ├── Sugar 10 gram └── Cup 1 pcs

100 Latte terjual

Coffee 18g × 100 = 1.800g Milk 150ml × 100 = 15.000ml Sugar 10g × 100 = 1.000g Cup 1 × 100 = 100 pcs

Repack

ORI 1 KG ↓ CONVERSION RULE ↓ RAW ↓ INVENTORY ↓ RECIPE ↓ POS

HPP transaksi disimpan pada saat transaksi agar perubahan cost bahan di masa depan tidak mengubah histori transaksi lama.

10. POS, Payment & Shift

Alur POS

LOGIN ↓ OPEN SHIFT ↓ SCAN / SEARCH PRODUCT ↓ PRICE ↓ PROMOTION / VOUCHER ↓ SALE ↓ PAYMENT ↓ INVENTORY OUT ↓ CASH / BANK ↓ JOURNAL ↓ AUDIT ↓ RECEIPT

Split payment

TOTAL Rp100.000 CASH Rp40.000 QRIS Rp60.000

Return

SALE ↓ SALE RETURN ↓ SALE RETURN ITEMS ↓ INVENTORY RETURN ↓ REFUND ↓ AUDIT

11. Finance & Accounting

Kode	Akun	Type	Normal
1101	Kas	ASSET	DEBIT
1102	Bank	ASSET	DEBIT
1103	QRIS / E-Wallet	ASSET	DEBIT
1201	Persediaan	ASSET	DEBIT
2101	Hutang Supplier	LIABILITY	CREDIT
3101	Modal	EQUITY	CREDIT
4101	Penjualan	REVENUE	CREDIT
5101	HPP	COGS	DEBIT
6101+	Expense	EXPENSE	DEBIT

Contoh jurnal

DEBIT   Kas             100.000
CREDIT  Penjualan       100.000

DEBIT   HPP              40.000
CREDIT  Persediaan       40.000

12. Analytics & Forecasting

SALES ↓ SALES DAILY SUMMARY ↓ PRODUCT DEMAND HISTORY ↓ PYTHON FORECAST ENGINE ↓ FORECAST RESULTS ↓ PURCHASE RECOMMENDATION ↓ REVIEW ↓ PURCHASE REQUISITION

Input engine

Sales history.
Inventory balance.
Inventory movement.
Supplier lead time.
MOQ.
Promotion.
Recipe.
Operational calendar.

Baseline forecast

Forecast =
SUM(sales 7 hari terakhir) / 7

Purchase recommendation

Forecast Demand
+ Safety Stock
- Available Stock
= Suggested Purchase

13. Pricing, Promotion & Voucher

Price List

NORMAL MEMBER WHOLESALE ONLINE

Promotion

PERCENT_DISCOUNT FIXED_DISCOUNT BUY_X_GET_Y BUNDLE SPECIAL_PRICE FREE_ITEM

Voucher

Voucher harus menyimpan:

Code.
Discount.
Minimum transaction.
Maximum discount.
Usage limit.
Validity period.

14. Approval, Audit & ERP Control

Approval

REQUEST ↓ APPROVAL_REQUEST ↓ APPROVAL_STEP ↓ APPROVED

Audit

Audit log menyimpan:

User.
Action.
Module.
Reference.
Old Data.
New Data.
IP.
Timestamp.

Transaction reference

PR ↓ PO ↓ GR ↓ INVENTORY MOVEMENT

Transaction event

SALE_COMPLETED GOODS_RECEIVED STOCK_ADJUSTED SHIFT_CLOSED FORECAST_COMPLETED PURCHASE_RECOMMENDED

15. Arinex Core — Reusable Client Template

ARINEX CORE │ ┌───────────────┼───────────────┐ │ │ │ CLIENT A CLIENT B CLIENT C │ │ │ Branding Branding Branding Config Config Config Master Data Master Data Master Data │ │ │ └───────────────┼───────────────┘ │ SAME CORE ENGINE

Yang dibuat sekali

Authentication.
RBAC.
POS.
Inventory.
Purchasing.
Recipe.
Finance.
Approval.
Audit.
Analytics.
Forecasting.

Yang berubah per client

Logo.
Nama usaha.
Warna.
Theme.
Menu.
Feature module.
Tax.
Service charge.
Receipt.
Master data.

16. SQL Standard

CREATE TABLE

CREATE TABLE IF NOT EXISTS example_table (
    id BIGSERIAL PRIMARY KEY,
    company_id BIGINT NOT NULL,
    name VARCHAR(150) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Foreign Key

CONSTRAINT fk_example_company
    FOREIGN KEY (company_id)
    REFERENCES companies(id)
    ON DELETE RESTRICT

Unique

CONSTRAINT uq_example_code
    UNIQUE (company_id, code)

Check Constraint

CONSTRAINT chk_status
    CHECK (
        status IN (
            'ACTIVE',
            'INACTIVE',
            'DELETED'
        )
    )

Index

CREATE INDEX IF NOT EXISTS idx_example_company
ON example_table(company_id);

Seed aman diulang

INSERT INTO roles (code, name)
VALUES (
    'OWNER',
    'Owner'
)
ON CONFLICT (code)
DO NOTHING;

17. Error Handling & Troubleshooting

Error: syntax error at or near "CREATE"

SELECT COUNT(*) AS total FROM (
    CREATE TABLE ...
) AS sub;

Penyebab: editor phpPgAdmin membungkus multiple statement.

Solusi: gunakan SQL editor pojok kanan atas yang berhasil digunakan selama project.

Error: No rows found

Belum tentu error.

Contoh view:

SELECT * FROM v_recipe_cost;

Jika belum ada recipe, hasilnya memang dapat kosong.

Error: relation does not exist

SELECT current_database();

SELECT current_schema();

Error: Foreign Key

Pastikan parent row sudah ada.

COMPANIES ↓ BRANCHES ↓ WAREHOUSES ↓ PRODUCT / INVENTORY

Error: Duplicate Key

SELECT *
FROM companies
WHERE code = 'ARINEX';

Error: Permission denied

SELECT current_user;

Error: Flask 500

500 ERROR ↓ Flask traceback ↓ DB connection ↓ SQL ↓ Environment ↓ Python dependency ↓ WSGI / Passenger

18. Maintenance

Backup

pg_dump \
    -U DATABASE_USER \
    -d DATABASE_NAME \
    -F c \
    -f arinex_backup.dump

Restore

pg_restore \
    -U DATABASE_USER \
    -d DATABASE_NAME \
    arinex_backup.dump

VACUUM

VACUUM ANALYZE;

Database size

SELECT
    pg_size_pretty(
        pg_database_size(
            current_database()
        )
    );

19. Security

Password menggunakan hashing.
Database credential menggunakan environment variables.
Backend melakukan permission checking.
SQL menggunakan parameterized query.
Tenant isolation wajib diuji.
Audit log tidak boleh mudah dimanipulasi.
Production database user menggunakan privilege minimum yang diperlukan.

SQL Injection

sql = f"""
SELECT *
FROM users
WHERE username = '{username}'
"""

cursor.execute(
    """
    SELECT *
    FROM users
    WHERE username = %s
    """,
    (username,)
)

20. Transaction Engine

BEGIN │ ├── CREATE SALE │ ├── CREATE SALE ITEMS │ ├── VALIDATE PAYMENT │ ├── UPDATE INVENTORY │ ├── CREATE INVENTORY MOVEMENT │ ├── CREATE CASH/BANK │ ├── CREATE JOURNAL │ └── CREATE AUDIT │ ▼ COMMIT Jika gagal: ROLLBACK

Jangan sampai pembayaran sukses tetapi stok gagal berkurang, atau stok berkurang tetapi penjualan tidak tercatat.

21. Document Numbering

SALE PR PO GR SO SHIFT REPACK PROD EXP ADJ

Contoh:

SALE-20260825-000001
PR-20260825-000001
PO-20260825-000001
GR-20260825-000001
SHIFT-20260825-000001

Histori nomor disimpan pada:


document_number_logs

22. Backend Flask Blueprint

HTTP REQUEST ↓ MIDDLEWARE ↓ AUTHENTICATION ↓ TENANT CONTEXT ↓ PERMISSION ↓ ROUTE ↓ SERVICE ↓ REPOSITORY / SQL ↓ POSTGRESQL

Target folder

backend/
│
├── app.py
├── config.py
├── db.py
│
├── middleware/
│   ├── auth.py
│   ├── tenant.py
│   └── permission.py
│
├── routes/
│   ├── auth.py
│   ├── products.py
│   ├── sales.py
│   ├── inventory.py
│   ├── purchasing.py
│   ├── suppliers.py
│   └── reports.py
│
├── services/
│   ├── sale_service.py
│   ├── inventory_service.py
│   ├── purchasing_service.py
│   ├── recipe_service.py
│   ├── accounting_service.py
│   └── approval_service.py
│
├── repositories/
├── analytics/
├── migrations/
└── tests/

Prinsip service

Route tipis.
Business logic di service.
Database access melalui repository/helper.
Transaction dikontrol service.
Permission selalu di backend.

23. Frontend / PWA

PWA START ↓ AUTH ↓ TENANT CONFIG ↓ BRANDING ↓ FEATURE MODULES ↓ PERMISSION ↓ RENDER UI

Client configuration

{
    "business_name": "Kedai ABC",

    "logo": "/client/logo.png",

    "theme": {
        "primary": "#8B4513",
        "secondary": "#222222"
    },

    "features": {
        "pos": true,
        "inventory": true,
        "purchasing": true,
        "recipe": true,
        "hotel": false
    }
}

24. Development Flow A-Z

STEP 01 Database Setup ↓ STEP 02 Organization ↓ STEP 03 Users / Roles ↓ STEP 04 Product ↓ STEP 05 Supplier ↓ STEP 06 Inventory ↓ STEP 07 Purchasing ↓ STEP 08 Recipe / HPP ↓ STEP 09 Repack ↓ STEP 10 POS ↓ STEP 11 Stock Opname ↓ STEP 12 Pricing / Promotion ↓ STEP 13 Finance ↓ STEP 14 ERP Control ↓ STEP 15 Client Template ↓ STEP 16 Flask Backend ↓ STEP 17 Transaction Engine ↓ STEP 18 Frontend / PWA ↓ STEP 19 Analytics ↓ STEP 20 Python Forecasting ↓ STEP 21 Client Deployment

25. Testing Strategy

Database

Duplicate SKU ditolak.
Duplicate barcode ditolak.
Invalid foreign key ditolak.
Constraint diuji.
Index diuji melalui query.

POS

TEST 01 Normal Sale TEST 02 Split Payment TEST 03 Void TEST 04 Return TEST 05 Voucher TEST 06 Discount TEST 07 Out of Stock TEST 08 HPP TEST 09 Tax TEST 10 Rollback

Purchasing

PR ↓ APPROVAL ↓ PO ↓ PARTIAL GR ↓ COMPLETE GR ↓ INVENTORY

26. Backup & Recovery

Backup

pg_dump \
    -U DATABASE_USER \
    -d DATABASE_NAME \
    -F c \
    -f arinex_backup.dump

Restore

pg_restore \
    -U DATABASE_USER \
    -d DATABASE_NAME \
    arinex_backup.dump

Backup harus pernah berhasil di-restore sebelum dianggap sebagai recovery plan yang valid.

27. Production Checklist

Area	Checklist
Database	Backup, restore, FK, unique, indexes.
Security	Secret aman, password hash, RBAC backend.
POS	Atomic transaction, rollback.
Inventory	Movement dan balance konsisten.
Purchasing	PR → Approval → PO → GR.
Accounting	Debit = Credit.
Tenant	Client isolation.
PWA	Offline & sync testing.
Forecast	Recommendation tidak langsung posting tanpa approval.

28. SQL Maintenance Commands

Database

SELECT current_database();

User

SELECT current_user;

Schema

SELECT current_schema();

All Tables

SELECT
    table_name
FROM
    information_schema.tables
WHERE
    table_schema = 'public'
ORDER BY
    table_name;

Views

SELECT
    table_name
FROM
    information_schema.views
WHERE
    table_schema = 'public';

Columns

SELECT
    table_name,
    column_name,
    data_type,
    is_nullable
FROM
    information_schema.columns
WHERE
    table_schema = 'public'
ORDER BY
    table_name,
    ordinal_position;

Foreign Keys

SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints tc
JOIN
    information_schema.key_column_usage kcu
    ON
        tc.constraint_name = kcu.constraint_name
JOIN
    information_schema.constraint_column_usage ccu
    ON
        ccu.constraint_name = tc.constraint_name
WHERE
    tc.constraint_type = 'FOREIGN KEY'
ORDER BY
    tc.table_name;

29. Final Blueprint

ARINEX CORE │ ┌─────────────────┼─────────────────┐ │ │ │ POSTGRESQL FLASK PYTHON │ BACKEND ANALYTICS │ │ │ │ ┌───┼───┐ │ │ │ │ │ │ │ AUTH RBAC TENANT │ │ │ │ │ TRANSACTION │ │ ENGINE │ │ │ │ └─────────────────┼─────────────────┘ │ FRONTEND / PWA │ ┌──────────────┼──────────────┐ │ │ │ POS STORE REPORT │ │ │ KASIR STAFF / OWNER OWNER │ │ CLIENT CONFIG │ ┌────────────────┼────────────────┐ │ │ │ BRANDING FEATURE FLAG SETTINGS │ │ │ └────────────────┼────────────────┘ │ CLIENT BARU │ TANPA MEMBUAT CORE DARI NOL

30. Roadmap

Tahap	Pekerjaan	Tujuan
1	Schema Verification	Pastikan database aktual sesuai blueprint.
2	Flask Core	Auth, RBAC, tenant.
3	Master API	Product, supplier, customer.
4	Inventory Service	Movement/balance.
5	Purchasing Service	PR/PO/GR.
6	POS Service	Sales/payment/shift.
7	Accounting	Auto journal.
8	Frontend/PWA	Reusable UI.
9	Analytics	Dashboard KPI.
10	Forecasting	Demand prediction.
11	Purchase Recommendation	Smart procurement.
12	Deployment Template	Instalasi client baru.

Target bisnis: satu Arinex Core dapat digunakan berulang untuk berbagai klien. Perbedaan utama berada pada branding, konfigurasi, feature modules dan master data.

31. Status Project

Area	Status
Organization	Selesai
User / Role / Permission	Selesai
Product	Selesai
Supplier	Selesai
Inventory	Selesai
Purchasing	Selesai
Recipe / HPP	Selesai
Repack	Selesai
POS	Selesai
Analytics	Selesai
Finance	Selesai
ERP Control	Selesai
Core Template	Selesai
Flask Backend	Berikutnya
Frontend / PWA	Berikutnya
Python Forecasting	Berikutnya