Codex Task: Material Stock, Movements, and Inventory¶

Objective¶

Extend the existing Materials module with company-level stock management.

Current stock must be calculated from:

1. Approved inventory counts, which establish an absolute stock baseline.
2. Material movements recorded after that inventory baseline.

Stock is tracked per company. There are no warehouses, locations, vans, or user-specific stock balances in this task.

---

Core Rules¶

Stock scope¶

• Stock belongs to a company.
• Every material has a separate stock balance for each company.
• All queries and mutations must respect the active company context.
• Super administrators must only see and modify stock for the currently selected company.
• Normal users must only access stock belonging to their company.

Movement types¶

Support exactly two movement types:

• in
• out

Examples:

• Received material: in
• Manual correction that increases stock: in
• Material used on a job: out
• Damaged or discarded material: out
• Manual correction that decreases stock: out

Do not create additional movement categories in this task.

Current stock calculation¶

Current stock must not be maintained only as an independently editable value.

Calculate current stock using the latest approved inventory count as the baseline:

current stock =
latest approved inventory quantity
+ all IN movements after that inventory
- all OUT movements after that inventory

When no approved inventory exists for a material:

current stock =
all IN movements
- all OUT movements

An approved inventory must not delete or rewrite earlier movements.

Older movements and inventories remain available as historical records.

Inventory behaviour¶

An inventory is a company-level process that includes every material that was active when the inventory was created.

An inventory must use at least these statuses:

• draft
• pending_approval
• approved

Optional:

• cancelled

Inventory workflow:

1. An authorised user creates an inventory.
2. The application creates inventory lines for all currently active company materials.
3. Users enter the physically counted absolute quantity for every line.
4. The inventory can be submitted for approval only after every material has a valid count.
5. Counts do not affect current stock while the inventory is draft or pending.
6. Approval makes every entered quantity the new stock baseline for that material.
7. Movements recorded after the inventory’s effective approval point are added to or subtracted from that baseline.
8. An approved inventory is immutable.

Materials created after an inventory was started must not be automatically inserted into that existing inventory.

Materials deactivated after inventory creation must remain in the inventory because they were part of the original count.

---

Permissions¶

Stock movements¶

All authenticated users may create stock movements within their current company:

• administrators
• dispatchers
• workers

Workers may create outgoing material movements:

• through job material usage
• manually through the stock movement interface

Administrators and dispatchers may create both incoming and outgoing movements manually.

Workers may also create both incoming and outgoing manual movements unless the existing application has a stricter central permission rule that must be preserved.

Users must never create movements for another company.

Inventory¶

Use the following permissions unless an existing application convention requires stricter access:

• Administrators and dispatchers can create inventories.
• Administrators and dispatchers can enter or edit inventory counts.
• Administrators and dispatchers can submit inventories for approval.
• Administrators and dispatchers can approve inventories.
• Workers can view current stock and movement history but cannot create, submit, approve, or cancel inventories.

Do not allow the same inventory to be approved twice.

---

Database Design¶

Implement the required schema through:

• database/schema.sql
• bin/upgrade-database.php

The production upgrade must be idempotent.

Suggested tables are below. Adjust names to match existing project conventions.

material_movements¶

Required fields:

• id
• company_id
• material_id
• movement_type
• quantity
• job_id, nullable
• created_by_user_id
• note, nullable
• occurred_at
• created_at

Rules:

• movement_type must allow only in or out.
• quantity must always be greater than zero.
• Do not store negative quantities.
• Direction comes from movement_type.
• job_id is populated for job-related material usage.
• Manual movements have a null job_id.
• Material, job, creator, and company relationships must be validated.
• A movement linked to a job must belong to the same company as that job and material.

Add indexes suitable for:

• company and material stock calculations
• material movement history
• job movement lookup
• chronological ordering

At minimum, consider indexes beginning with:

• (company_id, material_id, occurred_at)
• (company_id, job_id)
• (company_id, occurred_at)

material_inventories¶

Required fields:

• id
• company_id
• status
• started_by_user_id
• submitted_by_user_id, nullable
• approved_by_user_id, nullable
• started_at
• submitted_at, nullable
• approved_at, nullable
• note, nullable
• created_at
• updated_at

material_inventory_lines¶

Required fields:

• id
• inventory_id
• company_id
• material_id
• counted_quantity, nullable until entered
• system_quantity_at_start
• created_at
• updated_at

Requirements:

• One material may appear only once in an inventory.
• Store the calculated system quantity when the inventory is created for comparison.
• counted_quantity is the absolute physical quantity entered by the user.
• Quantities must allow decimals according to the material unit.
• Quantities cannot be negative.

Use a suitable precision such as:

DECIMAL(14, 3)

Use the same precision consistently for movements, inventory counts, and calculated stock.

---

Inventory Approval and Movement Timing¶

Inventory approval introduces an important timing boundary.

The implementation must ensure that movements are not lost when they occur while an inventory is being counted.

Example:

1. Inventory starts at 09:00.
2. System stock is 50.
3. Physical count entered is 48.
4. A worker uses 3 units at 10:00.
5. Inventory is approved at 11:00.

The application must use one clearly implemented and documented rule.

Use this rule:

• The approved counted quantity becomes the baseline at the inventory approval timestamp.
• Movements with an occurred_at value later than the approval timestamp affect the new baseline.
• Movements at or before the approval timestamp are considered reflected in the approved physical count.

To avoid accidentally excluding movements entered late:

• Default occurred_at to the current time.
• Store both occurred_at and created_at.
• Do not allow ordinary users to freely backdate movements.
• Administrators and dispatchers may set a movement date and time if the UI provides this option.
• Validate and document how backdated movements affect previously approved inventories.

Prefer preventing a movement from being backdated to a time before the latest approved inventory unless the application recalculates stock correctly from the historical timeline.

---

Stock Service or Repository¶

Create a central repository or service for all stock calculations.

Do not duplicate stock calculation SQL independently across views.

It should provide functions equivalent to:

material_current_stock(int $companyId, int $materialId): string
company_material_stock_list(int $companyId): array
material_movement_history(int $companyId, int $materialId, ...): array
latest_approved_inventory_line(int $companyId, int $materialId): ?array
create_material_movement(...): int
create_material_inventory(...): int
submit_material_inventory(...): void
approve_material_inventory(...): void

Use project naming and coding conventions rather than these exact function names when appropriate.

Stock calculations must be performed safely using decimal-compatible database values. Avoid unnecessary floating-point arithmetic.

---

Integration With Existing Job Material Usage¶

The existing job material usage feature must become part of the stock movement ledger.

When a worker records material used on a job:

• Create an out movement.

• Link it to the relevant job.

• Store the worker as the movement creator.

• Store the quantity used.

• Show the movement in both:

  • the job’s material usage section
  • the material’s movement history

Do not maintain two unrelated records for the same usage event.

Use either:

• the stock movement itself as the canonical job usage record, or
• an explicit one-to-one link between the existing usage record and its movement

Avoid double-subtracting job usage from stock.

Editing job material usage¶

If existing job material usage can be edited:

• Update the corresponding movement safely.
• Preserve an audit-friendly record where practical.

If existing job material usage can be deleted:

• Do not silently erase approved historical information.
• Follow existing job rules.
• Prefer reversing the original movement with an opposite movement if the job or movement is already treated as historical.

For the initial implementation, movement deletion may be limited to administrators and dispatchers and only when the movement is not protected by an approved inventory or closed job.

---

Negative Stock¶

Allow stock to become negative.

A material movement must not fail solely because the outgoing quantity exceeds calculated stock.

However:

• Show negative stock clearly in the UI.
• Highlight it as requiring attention.
• Display the resulting stock before movement submission when practical.
• Do not silently clamp stock to zero.

---

Materials List UI¶

Update the Materials list to include:

• material name
• code or SKU, when available
• unit
• status
• current stock

Current stock must be calculated for the selected company.

Provide clear visual treatment for:

• positive stock
• zero stock
• negative stock

Add actions for authorised users:

• Add material movement
• Start inventory
• View inventories

Workers should see only actions they are permitted to use.

---

Material Detail UI¶

The material detail page must show the current stock prominently.

Maintain the previously requested layout:

1. Material status panel
2. Recent movements table
3. Material details

The movement table must support page-size selection:

• 10
• 50
• 100

Show at least:

• date and time
• movement type
• quantity
• resulting direction
• linked job, when applicable
• note
• created by

Use clear labels such as:

• Material In
• Material Out

Add a movement creation action visible according to permissions.

---

Manual Movement Form¶

Create a form for adding a movement.

Required fields:

• material
• movement type
• quantity

Optional fields:

• note
• movement date and time, only where permitted

When opened from a material page, preselect the material.

Validation:

• Material must belong to the active company.
• Material must be active unless an administrator is correcting history.
• Quantity must be numeric and greater than zero.
• Movement type must be in or out.
• Linked job, when supplied, must belong to the same company.
• All writes require CSRF protection.

After creation:

• Redirect to a useful material or stock page.
• Show a success message.
• Display the new calculated stock.

---

Inventory List UI¶

Add an inventory list page under Materials.

Show:

• inventory ID or reference
• status
• started by
• started date
• submitted date
• approved date
• approved by
• counted material count

Provide filters for status where practical.

Actions depend on status and permissions:

• View
• Continue counting
• Submit for approval
• Approve
• Cancel

---

Inventory Count UI¶

The inventory page must list every material captured when the inventory was created.

Columns:

• material
• code or SKU
• unit
• system quantity at inventory start
• counted quantity
• difference

Difference:

counted quantity - system quantity at start

Requirements:

• Counted quantity must be easy to enter.
• Save draft values without affecting stock.
• Validate every quantity.
• Clearly show uncounted lines.
• Prevent submission until all lines have counts.
• Show a confirmation before submission.
• Show a stronger confirmation before approval because approval changes stock baselines.
• Approved inventories must be read-only.

For an approved inventory, show:

• previous calculated quantity
• counted quantity
• difference
• approval timestamp
• approving user

---

Navigation¶

Add suitable navigation under the Materials section:

• Materials
• Movements
• Inventory

A dedicated all-material movement page should show company-wide movements with filters for:

• material
• movement type
• job-linked or manual
• date range
• user

Use existing responsive navigation patterns.

---

Audit and Historical Integrity¶

Stock history must remain understandable.

At minimum, retain:

• who created each movement
• when the movement occurred
• when it was entered
• which job generated it
• who created, submitted, and approved each inventory
• inventory values before and after counting

Approved inventories must not be edited or deleted.

Do not delete movements automatically when an inventory is approved.

If movement deletion is implemented:

• restrict it appropriately
• prevent deletion where it would compromise approved history
• log enough context for troubleshooting

---

Transaction Safety¶

Use database transactions for:

• creating an inventory and all inventory lines
• submitting an inventory
• approving an inventory
• creating or updating job usage and its linked movement

Approval must lock or otherwise protect the inventory from concurrent double approval.

The complete inventory must either be approved successfully or remain unchanged.

---

Multi-Company Security¶

Every repository query and controller action must explicitly apply company scope.

Never trust IDs from URLs or forms without verifying ownership.

Test attempts to:

• open another company’s material
• create a movement for another company’s material
• link a movement to another company’s job
• access another company’s inventory
• approve another company’s inventory
• submit inventory line IDs from another inventory

All such requests must be rejected without exposing data.

---

Upgrade and Compatibility¶

Update:

• database/schema.sql
• bin/upgrade-database.php

The upgrade script must:

• be safe to run repeatedly
• detect existing tables and columns
• add missing indexes and constraints
• preserve existing materials and job material usage
• migrate existing material usage into stock movements when necessary
• avoid creating duplicate movements on repeated upgrade runs

If existing job material rows are migrated, use a deterministic link or migration marker so the process remains idempotent.

---

Testing¶

Add or update tests covering at least:

Stock without inventory¶

• IN 10 results in stock 10.
• OUT 3 results in stock 7.
• Another OUT 10 results in stock -3.

Stock with inventory¶

• Existing movements result in stock 20.
• Approved inventory count of 15 resets stock to 15.
• Later IN 5 results in stock 20.
• Later OUT 2 results in stock 18.
• Earlier movements remain visible but are not added again after the baseline.

Inventory lifecycle¶

• Draft inventory does not affect stock.
• Pending inventory does not affect stock.
• Incomplete inventory cannot be submitted.
• Approved inventory affects every captured material.
• Approved inventory cannot be edited.
• Inventory cannot be approved twice.

Job integration¶

• Job material usage creates one OUT movement.
• Editing usage does not double-create movements.
• Company stock decreases correctly.
• The movement links back to the job.

Permissions¶

• Workers can record job usage.
• Workers can create permitted manual movements.
• Workers cannot approve inventories.
• Dispatchers and administrators can manage inventories.
• Cross-company access is rejected.

Upgrade¶

• Upgrade works on the current production schema.
• Running the upgrade twice produces no duplicates or errors.

---

Acceptance Criteria¶

The feature is complete when:

1. Each company has an independently calculated stock balance for every material.
2. Both manual and job-generated IN/OUT movements affect stock.
3. All movements remain visible as historical records.
4. An inventory includes all active materials captured at creation.
5. Inventory counts are absolute quantities.
6. Draft and pending inventories do not affect stock.
7. Approval establishes new stock baselines.
8. Movements after approval adjust the approved quantities.
9. Job material usage does not subtract stock twice.
10. Negative stock is supported and visibly highlighted.
11. Approved inventories are immutable.
12. All routes and queries enforce company isolation.
13. Production database upgrades are idempotent.
14. The implementation works with the existing Materials module and UI style.
15. Existing material data and job usage are preserved.