Master Data

Read Conventions first.

items

The item master. Very wide - pricing, planning, production, delivery attributes, and its own 24-account G/L set.

• id, business_id, code - UNIQUE (business_id, code)
• name NOT NULL; description, foreign_description, model, remarks DEFAULT ‘’
• item_type DEFAULT ‘inventory’
• item_group_id → item_groups; item_category_id → item_categories; item_subcategory_id → item_subcategories
• brand_id → brands
• country_of_origin, hs_code, commodity_code
• default_uom_id → uoms
• lifecycle_status DEFAULT ‘active’; is_active boolean DEFAULT true
• image_url
• parent_item_id → items CASCADE - variant parent
• product_type DEFAULT ‘simple’ - CHECK IN (‘simple’,‘variable’)
• managed_by DEFAULT ‘none’ - CHECK IN (‘none’,‘batch’,‘serial’)
• track_expiry boolean DEFAULT false
• default_purchase_price, default_selling_price numeric(12,2) DEFAULT 0
• tax_liable boolean, tax_rate numeric(5,2), tax_component_id → tax_components - item’s default tax code; tax_rate auto-synced from it
• min_inventory, max_inventory, required_inventory_level numeric(14,3) DEFAULT 0 - general (default) levels
• weight numeric(12,3), weight_unit CHECK IN (‘kg’,‘g’,‘lb’,‘oz’); length/width/height numeric(12,3), dimension_unit CHECK IN (‘cm’,‘m’,‘in’,‘ft’)
• packing_material_charge_percent - CHECK 0..100
• warranty_period_id → warranty_periods
• is_gift_voucher boolean DEFAULT false
• loyalty_points_excluded boolean DEFAULT false
• is_pos_quick_pick boolean DEFAULT false - max 5 per business, enforced in application code not SQL; partial index (business_id) WHERE is_pos_quick_pick
• allow_oversell boolean NULL - item-level override, wins outright over branch/business
• Planning Data: planning_method CHECK IN (‘none’,‘mrp’); procurement_method CHECK IN (‘buy’,‘make’) - this is the “is a production item” switch; order_interval, order_multiple/minimum_order_qty numeric(14,3), checking_rule, lead_time_days int, tolerance_days int
• Production Data: is_phantom_item boolean; default_issue_method CHECK IN (‘manual’,‘backflush’)
• gl_accounts_by DEFAULT ‘item_group’ - CHECK IN (‘item_group’,‘warehouse’,‘item_level’) - three mutually exclusive sources, not a cascade
• 24 nullable uuid → gl_accounts columns (used only when gl_accounts_by = 'item_level') - same names as gl_account_determination_defaults
• created_at, updated_at

item_groups

Top level of the item hierarchy, and the primary G/L Account Determination source.

• id, business_id, name, created_at
• inventory_account_id → gl_accounts
• 23 more nullable → gl_accounts: expense_account_id, revenue_account_id, cogs_account_id, allocation_account_id, variance_account_id, price_difference_account_id, negative_inventory_adjustment_account_id, inventory_offset_decrease_account_id, inventory_offset_increase_account_id, sales_returns_account_id, exchange_rate_difference_account_id, goods_clearing_account_id, gl_decrease_account_id, gl_increase_account_id, wip_inventory_account_id, wip_inventory_variance_account_id, wip_offset_account_id, inventory_offset_pl_account_id, expense_clearing_account_id, stock_in_transit_account_id, shipped_goods_account_id, sales_credit_account_id, purchase_credit_account_id

item_categories / item_subcategories

2nd and 3rd levels of the item hierarchy.

• item_categories - id, business_id, item_group_id → item_groups NOT NULL, name, created_at
• item_subcategories - id, business_id, item_category_id → item_categories NOT NULL, name, created_at

brands

id, business_id, name, created_at - UNIQUE (business_id, name)

uoms / uom_groups

Units of measure, with conversion to a base UoM.

• uoms - id, business_id, code, name - UNIQUE (business_id, code); allow_decimal boolean DEFAULT true; base_uom_id → uoms NULL (self-reference); conversion_factor numeric(18,6) DEFAULT 1; uom_group_id → uom_groups NOT NULL; created_at
• uom_groups - id, business_id, name - UNIQUE (business_id, name); allow_weight_loss_calc boolean DEFAULT false; created_at - seeded “Weight” (calc-enabled) and “Each / Count” per business

price_lists / item_prices

• price_lists - id, business_id, name - UNIQUE (business_id, name); currency_id → currencies NOT NULL; created_at
• item_prices - id, item_id CASCADE, price_list_id → price_lists; selling_price numeric(12,2) DEFAULT 0; cost_price numeric DEFAULT 0; created_at - UNIQUE (item_id, price_list_id)

item_barcodes / item_suppliers

• item_barcodes - id, item_id CASCADE, barcode text UNIQUE globally, is_active, created_at
• item_suppliers - supplier catalogue per item, also reused as the targeting table for supplier-based promotions. id, item_id CASCADE, supplier_id → business_partners, supplier_item_code, supplier_barcode, lead_time_days int, moq numeric(12,2), is_preferred boolean, created_at - UNIQUE (item_id, supplier_id)

item_default_bins

Per-item-per-warehouse settings row. NULL on a level column means “inherit the item’s general level”, not zero.

• item_id, warehouse_id - composite PK
• bin_id → bin_locations ON DELETE SET NULL
• locked, enforce_default_bin boolean DEFAULT false
• min_inventory, max_inventory, required_inventory_level numeric(14,3) NULL

branch_items

Which items are enabled at which branch.

• branch_id CASCADE, item_id CASCADE - composite PK; created_at

Variances (item variants)

• variances - variant dimension (e.g. “Colour”). id, business_id, name, created_at - UNIQUE (business_id, name)
• variance_values - id, variance_id CASCADE, value, image_url, created_at - UNIQUE (variance_id, value)
• item_variance_values - which variance values a (parent) item offers. item_id CASCADE, variance_id CASCADE, value_id → variance_values CASCADE - PK (item_id, value_id)
• item_variant_selections - the specific value a child/variant item resolves to per dimension. item_id CASCADE, variance_id CASCADE, value_id CASCADE - PK (item_id, variance_id)

warranty_periods / warranty_records

• warranty_periods - id, business_id, name - UNIQUE (business_id, name); duration_days int NOT NULL CHECK > 0; is_active, created_at
• warranty_records - one row per warranted A/R invoice line. id, business_id, ar_invoice_id, ar_invoice_line_id, item_id, customer_id → business_partners, branch_id, warranty_period_id (all NOT NULL), quantity NOT NULL, start_date/expiry_date NOT NULL, created_at; indexes on customer_id, item_id, ar_invoice_id

business_partners

Customers, suppliers and vendors in one table.

• id, business_id, name NOT NULL
• code - text NOT NULL DEFAULT ‘’ - partial UNIQUE index (business_id, code) WHERE code <> ''
• is_customer, is_supplier, is_vendor boolean - CHECK (is_customer OR is_supplier OR is_vendor)
• contact_person, phone, mobile, email, address, notes, tax_id, partner_group DEFAULT ‘’
• credit_limit numeric(12,2), payment_terms_days int, opening_balance numeric(12,2)
• payment_term_id → payment_terms; default_tax_group_id → tax_groups; default_price_list_id → price_lists
• sscl_liable boolean DEFAULT false
• date_of_birth date - birthday promotions
• is_loyalty_member boolean, loyalty_member_no text NULL, loyalty_enrolled_at, loyalty_points_balance numeric(14,2) CHECK >= 0 - UNIQUE (business_id, loyalty_member_no); NULLs never collide
• change_money_balance numeric(12,2) CHECK >= 0 - decrementing balance
• subject_to_withholding_tax boolean DEFAULT false - must be true before any WHT code applies
• default_withholding_tax_code_id → withholding_tax_codes
• is_active, created_at
• Every business has a standing ‘Walk-in Customer’ partner

partner_ledger_entries

• id, partner_id → business_partners CASCADE
• entry_date, description
• debit, credit numeric(12,2) - CHECK (debit >= 0 AND credit >= 0 AND (debit > 0 OR credit > 0))
• created_by → users nullable (auto-posted POS entries have no human creator), created_at

payment_terms

id, business_id, name - UNIQUE (business_id, name); term_type CHECK IN (‘credit’,‘down_payment’); credit_days int NULL; down_payment_percent numeric(5,2) NULL; is_active, created_at

Tax tables

• tax_components - an individual tax (VAT, SSCL, …). id, business_id, code - UNIQUE (business_id, code); name; rate numeric(6,3) NOT NULL; gl_account_id → gl_accounts; is_sscl boolean DEFAULT false (designates the SSCL surcharge; excluded from per-line tax pickers, consumed specially by the totals engine); is_active, created_at
• tax_groups - id, business_id, name UNIQUE (business_id, name); code - UNIQUE (business_id, code); is_active, created_at
• tax_group_components - ordered components within a group. id, tax_group_id CASCADE, tax_component_id, sequence int NOT NULL - UNIQUE (tax_group_id, sequence); calculation_basis CHECK IN (‘item_price’,‘running_total’); is_mandatory boolean DEFAULT false; purchase_calc_type/sale_calc_type DEFAULT ‘percentage’ CHECK IN (‘percentage’,‘fixed’); purchase_value/sale_value numeric DEFAULT 100 - “X% of the Total Value the component’s own rate applies to” (e.g. SSCL = 2.5% of 85% of total); 100 means the full value, no reduction

convenience_fees

Reusable business-level fee, applied per document.

• id, business_id, name - UNIQUE (business_id, name)
• fee_type CHECK IN (‘percentage’,‘fixed’); value numeric(14,2)
• applies_to DEFAULT ‘purchase’ - CHECK IN (‘purchase’,‘sale’)
• is_taxable boolean DEFAULT false; tax_group_id/tax_component_id - CHECK: not both set
• is_active, created_at

warehouses / bin_locations

• warehouses - id, business_id, branch_id → branches NULL, code - UNIQUE (business_id, code); name, address, is_active, created_at; 24 nullable uuid → gl_accounts columns (the “By Warehouse” G/L determination source, symmetric to item_groups)
• bin_locations - hierarchical bins within a warehouse; informational for now, stock quantity is still tracked at warehouse level. id, warehouse_id CASCADE, parent_id → bin_locations NULL, code - UNIQUE (warehouse_id, code); name, bin_type DEFAULT ‘standard’; capacity_qty/capacity_weight/capacity_volume numeric(14,3) NULL; is_active, created_at

Delivery partner tables

• delivery_partners - id, business_id, name - UNIQUE (business_id, name); code, contact_person, phone, email, address; is_active, created_at
• delivery_partner_rate_cards - id, delivery_partner_id CASCADE, city - UNIQUE (delivery_partner_id, city); district, shipping_zone; first_kg_rate, additional_kg_rate numeric CHECK >= 0; created_at
• delivery_partner_waybills - pre-issued waybill stock, allocated to an invoice when used. id, delivery_partner_id CASCADE, waybill_no - UNIQUE (delivery_partner_id, waybill_no); status DEFAULT ‘available’ CHECK IN (‘available’,‘allocated’); allocated_to_ar_invoice_id → ar_invoices NULL, allocated_at, created_at