Developer Menu 1/4

1. Database Structure (SQL Verified)

This page is generated from database/sql/database.sql (Database: nex_stock) and reflects the real stock/inventory schema used by stoqio.

Schema Overview

  • Total tables in SQL dump: 75.
  • Mixed key strategy: many business entities use string IDs (for example ...-prod-..., ...-comp-...), while ledger and line tables often use bigint IDs.
  • Most operational tables are company-scoped via company_id.
  • Soft-delete pattern exists in core flow tables using is_deleted and deleted_at.

Core Domain Map

1) Tenant and Access Control

  • company: tenant profile, branding, currency/language, plan linkage.
  • user: staff/user identity, role link, company and warehouse scope.
  • role, right, role_right: role-permission matrix.
  • company_rights: rights assigned at tenant level.
  • company_feature_preference: feature flags (for example product variants, barcode, invoice-delete policy).

2) Product Master

  • categories, brand, units: product setup dictionaries.
  • product: central product table (brand/category/unit linkage, bundle flag, variants JSON, VAT, status).
  • product_variants, variant_attributes: variant type and values.
  • product_bundles, product_bundle_items: bundle pricing and component mapping.
  • product_barcode_store, inventory_item_barcode: barcode persistence.

3) Stock and Warehouse

  • warehouses: multi-warehouse master with is_default.
  • inventory: product-level stock summary per warehouse.
  • inventory_items: lot/variant-level stock batches with cost/price metadata.
  • inventory_histories: movement/audit timeline for stock operations.
  • stock_transfers, stock_transfer_items: warehouse-to-warehouse movement logs.

4) Invoice and Financial Flow

  • invoices: header table for sale, purchase, return, draft, quotation, expense.
  • invoice_items: product/bundle line items with qty, rate, discount, VAT, return flags.
  • transactions: payment transaction records per invoice.
  • discount_history, coupon: discount artifacts.

5) Return and Waitlist Flow

  • sales_returns, sales_return_items, sales_return_payments, sales_return_approvals.
  • customer_waitlists, customer_waitlist_reservations: out-of-stock reservation and notification lifecycle.

6) Supplier and Cash Management

  • suppliers: supplier profile and status per company.
  • timesheet, timesheet_cash_handovers: cashier shift and handover cash reconciliation.

Critical Table Specifications (From SQL)

product

  • Primary key: id varchar(255).
  • Business columns: name, code, brand_id, category_id, unit_id, status.
  • Advanced flags: is_bundle, has_variant, has_segment.
  • Metadata: variants (longtext), segmentation (longtext), vat.
  • Multi-tenant and lifecycle: company_id, deleted_at.

inventory

  • Primary key: id bigint unsigned.
  • Fields: warehouse_id, product_id, quantity, stock_out_mood (LIFO|FIFO|RANDOM), alert_quantity.
  • Soft delete columns: is_deleted, deleted_at.

inventory_items

  • Batch-level stock rows with lineage to invoice item.
  • Key linkage: inventory_id, invoice_item_id, invoice_id, product_id, variant_id.
  • Pricing columns: price, purchase_price, price_json.
  • Control columns: lot_number, expiry_date, alert_quantity, stock_out_mood.

invoices

  • Primary key: id varchar(40); business number: invoice_no.
  • Type enum: sale|purchase|expense|return|draft|quotation.
  • Parties: customer_id, supplier_id, user_id, warehouse_id.
  • Monetary fields: qty, discount, vat_rate, vat, sub_total, total, paid_amount, due_amount, change_due.

invoice_items

  • Line granularity with item_type (product / bundle reference via bundle_id).
  • Quantity and price fields: qty, unit_price, discount, discount_amount, vat, total.
  • Return linkage: is_returned, return_reason, original_sale_item_id.

sales_returns

  • ID: char(36), reference: return_no.
  • Status enum: pending_approval|approved|completed|rejected.
  • Flow linkage: original_invoice_id and return_invoice_id.
  • Settlement fields: refund_amount, exchange_total, extra_payable, approval_required.

Developer Integrity Rules

  1. Keep company_id scoping in every query/mutation for multi-tenant safety.
  2. When changing invoice logic, update both invoices and invoice_items, then reconcile inventory tables.
  3. Never update stock only in inventory; batch truth must stay consistent in inventory_items and history tables.
  4. For returns, preserve traceability between original sale item/invoice and return rows.
  5. Prefer additive migrations; avoid direct SQL edits in production.

Source of Truth

All structure in this page is aligned to: database/sql/database.sql. If migration and SQL dump differ, treat SQL dump and runtime database as authoritative for deployment debugging.