# Database Structure ## users Purpose: login accounts. Fields: - id - name - email - password - role: owner, manager, employee - employee_id nullable - phone nullable - is_active - created_at - updated_at ## employees Purpose: staff records. Fields: - id - first_name - last_name - phone - email nullable - job_role - hourly_rate nullable - hire_date nullable - status: active, inactive - notes nullable - created_at - updated_at ## inventory_categories Fields: - id - name - description nullable - created_at - updated_at ## inventory_items Purpose: main stock item table. Fields: - id - inventory_category_id nullable - name - sku nullable - base_unit - current_quantity - minimum_quantity - cost_per_unit nullable - supplier_name nullable - expiry_tracking_enabled boolean default false - is_active boolean default true - notes nullable - created_at - updated_at Important notes: - `cost_per_unit` is nullable. - `base_unit` should be simple in early versions: piece, packet, kg, liter, bottle, box, and similar units. ## inventory_item_aliases Purpose: help command parser match real staff language. Fields: - id - inventory_item_id - alias - created_at - updated_at Examples: - Tomato: `tomato`, `tomatoes` - Cheese Packet: `cheese`, `cheez`, `mozzarella packet` ## inventory_movements Purpose: every stock change. Fields: - id - inventory_item_id - movement_type: purchase, stock_in, stock_out, waste, correction, count_adjustment - quantity - unit_cost nullable - total_cost nullable - reason nullable - source: manual, app_command, voice_command, whatsapp_command, stock_count, invoice_import - movement_date - created_by_user_id nullable - created_at - updated_at Behavior: - purchase and stock_in increase stock. - stock_out and waste decrease stock. - count_adjustment reconciles expected vs counted stock. - unit cost is optional. ## inventory_purchases Purpose: header record for bought inventory. Fields: - id - supplier_name nullable - invoice_number nullable - purchase_date - total_amount nullable - notes nullable - created_by_user_id - created_at - updated_at ## inventory_purchase_items Purpose: line items from a purchase. Fields: - id - inventory_purchase_id - inventory_item_id - quantity - unit_cost nullable - total_cost nullable - created_at - updated_at ## inventory_commands Purpose: store text, voice, and external inventory commands. Fields: - id - raw_command - parsed_action nullable - parsed_item_text nullable - parsed_quantity nullable - parsed_unit nullable - matched_inventory_item_id nullable - confidence_status: matched, uncertain, failed - status: applied, pending_review, rejected - source: app_text, app_voice, whatsapp - created_by_user_id nullable - employee_id nullable - reviewed_by_user_id nullable - reviewed_at nullable - created_at - updated_at Behavior: - Clear command creates an inventory movement immediately. - Unclear command waits for manager review. - Failed command is logged for improving aliases and parser rules. ## stock_counts Purpose: closing or scheduled inventory count. Fields: - id - count_date - count_type: daily_key_items, weekly_full, manual - status: draft, submitted, approved - counted_by_user_id - approved_by_user_id nullable - approved_at nullable - notes nullable - created_at - updated_at ## stock_count_items Fields: - id - stock_count_id - inventory_item_id - expected_quantity - counted_quantity - variance_quantity - created_at - updated_at ## shifts Fields: - id - employee_id - shift_date - start_time - end_time - role - status: scheduled, completed, missed, cancelled - notes nullable - created_by_user_id - created_at - updated_at ## timesheets Fields: - id - employee_id - shift_id nullable - work_date - scheduled_start nullable - scheduled_end nullable - actual_start nullable - actual_end nullable - break_minutes default 0 - worked_hours nullable - hourly_rate_snapshot nullable - estimated_pay nullable - status: pending, approved, rejected - approved_by_user_id nullable - approved_at nullable - notes nullable - created_at - updated_at ## daily_sales Purpose: sales totals by business date. Fields: - id - business_date - cash_amount default 0 - card_amount default 0 - online_amount default 0 - delivery_amount default 0 - other_amount default 0 - total_amount - notes nullable - created_by_user_id - created_at - updated_at Dashboard and report filters: - Today - Yesterday - This week - This month - Custom date range ## expense_categories Fields: - id - name - description nullable - created_at - updated_at ## expenses Fields: - id - expense_category_id - expense_date - amount - payment_method: cash, card, bank, online, other - vendor nullable - description - receipt_path nullable - created_by_user_id - created_at - updated_at