Dev Spec: Kho vật tư

Feature slug: material-warehouseVersion: 3.0 Ngày: 2026-03-27

---

C1) Scope

Loại	Nội dung
In scope	Bảng material_warehouse (1 kho per branch), material_price_config (versioned pricing), material_usage_unit (đơn vị quy đổi), material_stock_movement (immutable log nhập/xuất/kiểm kê), Bảng material_batch (FIFO deduct/reverse, cron HSD), view material_stock_balance, ALTER project_task_material thêm cột giá/quy đổi, Hasura metadata + permissions per role (Staff/Manager/Admin), event triggers materialAutoDeduct + materialAutoReverse, low stock notification, SCR-01~07 FE pages, sidebar tài chính kết nối FORMULA-005, cảnh báo tồn kho trên form subtask, ★ v3.0: Chuyển kho chi nhánh → kho vật tư via inventory_document (behavior: export_material_warehouse / import_material_warehouse), SCR-08 form chuyển kho, Hasura Action transferToMaterialWarehouse, event trigger inventory_document_material_warehouse
Out of scope	Kết nối kho tổng — HQ warehouse (F-01), FIFO/Weighted Average costing (F-02), override giá per branch (F-03), material recipe/BOM (F-04), chuyển kho giữa chi nhánh — branch↔branch (F-05), báo cáo chi phí vật tư (F-06), export báo cáo Excel (F-07), partition stock_movement (F-08), mobile UI

---

C2) Impact

Layer	File	Thay đổi
DB	Migration mới	CREATE TABLE material_warehouse + unique index
DB	Migration mới	CREATE TABLE material_price_config + partial unique indexes + versioning
DB	Migration mới	CREATE TABLE material_usage_unit + unique constraint
DB	Migration mới	CREATE TABLE material_stock_movement + composite indexes
DB	Migration mới	CREATE VIEW material_stock_balance (DISTINCT ON)
DB	Migration mới	ALTER TABLE project_task_material — 10 cột mới (giá, quy đổi, wastage)
Hasura	public_material_warehouse.yaml	Metadata mới: permissions, relationships
Hasura	public_material_price_config.yaml	Metadata mới: permissions, relationships, remote relationship → product
Hasura	public_material_usage_unit.yaml	Metadata mới: permissions, relationships
Hasura	public_material_stock_movement.yaml	Metadata mới: permissions (insert only for user), immutable
Hasura	public_material_stock_balance.yaml	View metadata: select permissions
Hasura	public_project_task_material.yaml	Sửa: thêm cột mới vào insert/select/update permissions
Hasura	Event triggers config	Thêm trigger material_auto_deduct on project_task status change
GraphQL	material-warehouse.graphql	Fragments + mutations + queries mới
GraphQL	project.graphql	Sửa fragment ProjectTaskMaterial thêm cột giá/quy đổi
FE	MaterialWarehouse/List.vue	New page — SCR-01 danh sách kho vật tư
FE	MaterialWarehouse/ConfigForm.vue	New component — SCR-02 form cấu hình vật tư
FE	MaterialWarehouse/Detail.vue	New component — SCR-03 chi tiết + lịch sử
FE	MaterialWarehouse/StockImport.vue	New component — SCR-06 nhập kho
FE	MaterialWarehouse/Inventory.vue	New component — SCR-07 kiểm kê
FE	TaskForm/MaterialForm.vue	Modify — SCR-04 thêm ĐVT selector, cột giá/thành tiền
FE	TaskDetail/General.vue	Modify — SCR-05 aggregate cột giá
FE	OrderDetail/FinancialSidebar.vue	Modify — thêm dòng chi phí vật tư (FORMULA-005)
FE	Router config	Thêm route /warehouse/material-warehouse
FE	Sidebar navigation	Thêm menu item "Kho vật tư" dưới nhóm "Kho"
FE	Pinia store	New store useMaterialWarehouseStore
BE	ecommerce-api hoặc restful-api	New handler materialAutoDeduct (Hasura event trigger)
BE	ecommerce-api hoặc restful-api	New handler materialAutoReverse (Hasura event trigger)
DB	Migration mới	CREATE TABLE material_batch + indexes (FIFO, expiry)
Hasura	public_material_batch.yaml	Metadata mới: permissions, relationships (warehouse, movements)
BE	material_batch_expiry_check	Cron job: auto-lock lô hết HSD + cảnh báo 90 ngày
BE	notification-api	New logic: low stock alert (dedupe 1/ngày)
DB	inventory_document (existing, ecommerce DB)	★ v3.0: +2 new behaviors: export_material_warehouse, import_material_warehouse
DB	product_supplying (existing, ecommerce DB)	★ v3.0: Dùng cho lot tracking khi chuyển kho (type: export, lot_number)
DB	master_data (existing)	★ v3.0: +2 new status entries cho material warehouse behaviors
Hasura	Event trigger config	★ v3.0: Thêm trigger inventory_document_material_warehouse on inventory_document status change
Hasura	Action config	★ v3.0: Thêm action transferToMaterialWarehouse → ecommerce-api
BE	ecommerce-api	★ v3.0: New handler transferToMaterialWarehouse (Hasura action — chuyển kho chi nhánh → kho vật tư)
BE	ecommerce-api	★ v3.0: New handler inventory_document_material_warehouse (event trigger — notification + cancel logic)
FE	MaterialWarehouse/Transfer.vue	★ v3.0: New component — SCR-08 form chuyển kho chi nhánh → kho vật tư
FE	MaterialWarehouse/List.vue	★ v3.0: Modify — thêm button [★ Chuyển kho] trên SCR-01
FE	ProductLotNumberSelect (existing component)	★ v3.0: Reuse cho SCR-08 — chọn lô nguồn (lot_number, expiry, price) (Ref: DEC-D36)

---

C3) Rules & Formulas

FORMULA-001: Giá đơn vị kho (có hao hụt)

• Mô tả: Tính giá per đơn vị kho từ giá nhập, có tính hao hụt hệ thống
• Công thức: stock_unit_price = source_price / (source_quantity × (1 - wastage_rate))
• Biến số:
  • source_price: giá nhập gốc (VND) — nguồn: material_price_config.source_price
  • source_quantity: SL trong 1 đơn vị mua — nguồn: material_price_config.source_quantity
  • wastage_rate: tỷ lệ hao hụt hệ thống (0~0.9999) — nguồn: material_price_config.wastage_rate
• Đơn vị: VND (NUMERIC(15,4) — 4 decimal nội bộ)
• Ví dụ: Serum 500ml, giá 2,000,000đ, hao hụt 2% → 2,000,000 / (500 × 0.98) = 4,081.6327đ/ml
• Edge cases:
  • wastage_rate = 0 → source_price / source_quantity (bình thường, không hao hụt)
  • source_quantity = 0 → CHECK constraint source_quantity > 0 block ở DB
  • source_price = 0 → hợp lệ (vật tư miễn phí / nội bộ), stock_unit_price = 0
  • wastage_rate = 1 → CHECK constraint wastage_rate < 1 block (chia cho 0)

FORMULA-001b: Giá đơn vị lô (batch unit price)

• Mô tả: Tính giá per stock unit từ giá nhập lô (batch-based, dùng cho FIFO deduct)
• Công thức: batch_unit_price = purchase_price / purchase_quantity
• Biến số:
  • purchase_price: giá nhập lô (VND) — nguồn: material_batch.purchase_price
  • purchase_quantity: SL nhập lô (stock unit) — nguồn: material_batch.purchase_quantity
• Đơn vị: VND (NUMERIC(15,4) — 4 decimal nội bộ)
• Ví dụ: Nhập lô Serum 500ml, giá 2,000,000đ → 2,000,000 / 500 = 4,000.0000đ/ml
• Edge cases:
  • purchase_quantity = 0 → CHECK constraint purchase_quantity > 0 block ở DB
  • purchase_price = 0 → hợp lệ (vật tư miễn phí), batch_unit_price = 0

FORMULA-002: Giá đơn vị sử dụng (FIFO)

• Mô tả: Tính giá per đơn vị sử dụng từ giá lô FIFO × hệ số quy đổi
• Công thức: usage_unit_price = batch_unit_price × to_stock_factor
• Biến số:
  • batch_unit_price: giá per stock unit của lô FIFO — nguồn: FORMULA-001b output / material_batch.unit_price
  • to_stock_factor: 1 usage unit = X stock units — nguồn: material_usage_unit.to_stock_factor
• Đơn vị: VND (NUMERIC(15,4) — 4 decimal nội bộ)
• Ví dụ:
  • giọt: 4,000.0000 × 0.05 = 200.0000đ/giọt
  • muỗng: 4,000.0000 × 5 = 20,000.0000đ/muỗng
  • ml: 4,000.0000 × 1 = 4,000.0000đ/ml
• Edge cases:
  • to_stock_factor = 0 → CHECK constraint to_stock_factor > 0 block ở DB
  • batch_unit_price = 0 → usage_unit_price = 0 (hợp lệ)
• Lưu ý: v1.x dùng stock_unit_price từ material_price_config (Latest Price — DEC-D04 superseded). v2.0 lấy giá từ lô FIFO.

FORMULA-003: Chi phí vật tư per subtask (FIFO split)

• Mô tả: Chi phí thực tế 1 vật tư trên 1 subtask — tính theo FIFO, có thể split qua nhiều lô với giá khác nhau
• Công thức: amount = SUM(batch_unit_price_i × qty_from_batch_i) cho mỗi lô FIFO
• Biến số:
  • batch_unit_price_i: giá per stock unit của lô thứ i — nguồn: material_batch.unit_price (FIFO order)
  • qty_from_batch_i: SL lấy từ lô thứ i (stock unit) — tính từ FIFO deduct logic
  • Tổng SUM(qty_from_batch_i) = total_stock_equivalent của subtask material
• Đơn vị: VND (BIGINT, rounded — không decimal)
• Ví dụ: 3 giọt Serum (= 0.15ml stock), lô A giá 4,000đ/ml còn 0.10ml, lô B giá 4,200đ/ml:
  • Lô A: 4,000 × 0.10 = 400đ
  • Lô B: 4,200 × 0.05 = 210đ
  • amount = ROUND(400 + 210) = 610đ
• Edge cases:
  • quantity = 0 → FE block (validation: quantity > 0)
  • unit_price = NULL (product chưa config giá) → amount = NULL, hiện "(chưa có giá)" (Ref: DEC-D11)
  • is_discrete = true AND quantity có phần thập phân → FE block (Ref: BR-04)
  • Không đủ tồn lô active → BLOCK toàn bộ + rollback, gửi notification (Ref: DEC-D23)
• Timing: Giá amount có 2 phase:
  • Phase 1 (save subtask): amount = ước tính từ lô FIFO cũ nhất (display only, có thể thay đổi)
  • Phase 2 (done subtask): amount = thực tế từ FIFO deduct → UPDATE project_task_material (final, locked)
  • Ref: DEC-D28

FORMULA-004: Quy đổi về stock unit

• Mô tả: Tính SL quy đổi về đơn vị kho — dùng cho trừ tồn kho khi subtask done
• Công thức:
  • stock_equivalent = quantity × to_stock_factor
  • total_stock_equivalent = (quantity + wastage_quantity) × to_stock_factor
• Biến số:
  • quantity: SL thực tế — nguồn: project_task_material.quantity
  • wastage_quantity: SL hao hụt kỹ thuật — nguồn: project_task_material.wastage_quantity
  • to_stock_factor: hệ số quy đổi snapshot — nguồn: project_task_material.to_stock_factor
• Đơn vị: stock unit (ml, g, miếng — tùy config)
• Ví dụ: 3 giọt Serum, hao hụt 1 giọt, factor 0.05
  • stock_equivalent = 3 × 0.05 = 0.15ml
  • total_stock_equivalent = (3 + 1) × 0.05 = 0.20ml ← trừ kho dùng giá trị này
• Edge cases:
  • wastage_quantity = 0 → total_stock_equivalent = stock_equivalent
  • wastage_quantity = NULL → default 0 (DB DEFAULT)
  • to_stock_factor = NULL (chưa config) → stock_equivalent = NULL, không trừ kho (Ref: EC-06)

FORMULA-005: Tổng chi phí vật tư per đơn hàng

• Mô tả: Tổng chi phí vật tư cho tổng hợp tài chính đơn hàng dịch vụ
• Công thức:

  material_cost = SUM(ptm.amount)
    WHERE ptm.task_id IN (
      SELECT id FROM project_task
      WHERE parent_id IN (
        SELECT id FROM project_task WHERE order_id = $order_id
      )
    )
    AND ptm.amount IS NOT NULL

• Biến số:
  • ptm.amount: chi phí vật tư per subtask — nguồn: project_task_material.amount (FORMULA-003)
  • order_id: ID đơn hàng — nguồn: route params
• Đơn vị: VND (BIGINT)
• Ví dụ: Đơn "Laser Q-switch": Serum 612đ + Gel 15,000đ + Mask 30,000đ = 45,612đ
• Edge cases:
  • Không có subtask nào có materials → material_cost = 0
  • Subtask có vật tư nhưng amount = NULL (chưa config giá) → exclude khỏi SUM
  • Backward compat: Đơn cũ (có inventory_document.capture): dùng SUM(capture.Price × capture.Quantity). Không có gì: material_cost = 0

FORMULA-006: Chi phí hao hụt (tracking riêng)

• Mô tả: Chi phí hao hụt kỹ thuật (đổ bỏ, dùng thừa) — dùng cho báo cáo
• Công thức: wastage_cost = ROUND(unit_price × wastage_quantity)
• Biến số:
  • unit_price: snapshot giá (Phase 1 — ước tính. Phase 2 khi done = final. Ref: DEC-D28) — nguồn: project_task_material.unit_price
  • wastage_quantity: SL hao hụt kỹ thuật — nguồn: project_task_material.wastage_quantity
• Đơn vị: VND (BIGINT, rounded)
• Ví dụ: 1 giọt Serum hao hụt = ROUND(204.0816 × 1) = 204đ
• Edge cases:
  • wastage_quantity = 0 → wastage_cost = 0
  • unit_price = NULL → wastage_cost = NULL

---

C4) Data Model

Database: project

Bảng 1: material_warehouse — Kho vật tư per branch

CREATE TABLE material_warehouse (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  branch_id   UUID NOT NULL,
  name        TEXT NOT NULL,                -- "Kho vật tư Quận 1"
  is_active   BOOLEAN NOT NULL DEFAULT true,
  created_by  TEXT,
  updated_by  TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at  TIMESTAMPTZ,                  -- soft delete

  -- UNIQUE(branch_id) -- thay bằng partial index bên dưới (soft delete conflict)
);

-- Partial unique index: 1 chi nhánh = 1 kho vật tư active (soft-delete safe)
CREATE UNIQUE INDEX idx_mw_branch_active
  ON material_warehouse(branch_id) WHERE deleted_at IS NULL;

-- Index: soft delete filter
CREATE INDEX idx_mw_active ON material_warehouse(branch_id) WHERE deleted_at IS NULL;

-- Trigger: auto-update updated_at
CREATE OR REPLACE FUNCTION update_material_warehouse_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_material_warehouse_updated_at
  BEFORE UPDATE ON material_warehouse
  FOR EACH ROW
  EXECUTE FUNCTION update_material_warehouse_updated_at();

Bảng 2: material_price_config — Cấu hình giá per product (versioned)

CREATE TABLE material_price_config (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id        UUID NOT NULL,           -- FK → ecommerce.product (remote, cross-DB)
  branch_id         UUID DEFAULT NULL,       -- NULL = toàn hệ thống, có giá trị = override per branch

  -- Đơn vị kho
  stock_unit        TEXT NOT NULL,            -- 'ml', 'g', 'miếng'

  -- Giá nguồn → giá stock
  source_price      NUMERIC(15,2) NOT NULL,   -- giá nhập gốc (VND)
  source_quantity   NUMERIC(12,4) NOT NULL    -- SL trong 1 đơn vị mua
                    CHECK (source_quantity > 0),
  stock_unit_price  NUMERIC(15,4) NOT NULL,   -- = source_price / (source_quantity × (1 - wastage_rate))

  -- Hao hụt hệ thống
  wastage_rate      NUMERIC(5,4) NOT NULL DEFAULT 0
                    CHECK (wastage_rate >= 0 AND wastage_rate < 1),

  -- Costing method (Ref: DEC-D04 — SUPERSEDED v2.0: chuyển sang batch-based FIFO)
  costing_method    TEXT NOT NULL DEFAULT 'fifo'
                    CHECK (costing_method IN ('latest_price', 'weighted_average', 'fifo')),

  -- Ngưỡng cảnh báo tồn kho (stock unit)
  min_stock         NUMERIC(12,4),            -- cảnh báo khi tồn ≤ giá trị này
  max_stock         NUMERIC(12,4),            -- (Phase sau)

  -- Versioning (Ref: DEC-D02)
  effective_from    TIMESTAMPTZ NOT NULL DEFAULT now(),
  effective_to      TIMESTAMPTZ,              -- NULL = active

  -- Audit
  created_by        TEXT,
  updated_by        TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at        TIMESTAMPTZ,              -- soft delete

  CONSTRAINT unique_active_config UNIQUE (product_id, branch_id, effective_from)
);

-- Chỉ 1 config active per product — global (Ref: BR-01)
CREATE UNIQUE INDEX idx_mpc_product_active_global
  ON material_price_config (product_id)
  WHERE effective_to IS NULL AND branch_id IS NULL AND deleted_at IS NULL;

-- Chỉ 1 config active per product per branch
CREATE UNIQUE INDEX idx_mpc_product_active_branch
  ON material_price_config (product_id, branch_id)
  WHERE effective_to IS NULL AND branch_id IS NOT NULL AND deleted_at IS NULL;

-- Lịch sử giá per product
CREATE INDEX idx_mpc_product_id ON material_price_config(product_id);
CREATE INDEX idx_mpc_branch_id ON material_price_config(branch_id) WHERE branch_id IS NOT NULL;

-- Trigger: auto-update updated_at
CREATE OR REPLACE FUNCTION update_material_price_config_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_material_price_config_updated_at
  BEFORE UPDATE ON material_price_config
  FOR EACH ROW
  EXECUTE FUNCTION update_material_price_config_updated_at();

Bảng 3: material_usage_unit — Đơn vị sử dụng (1:N per config)

CREATE TABLE material_usage_unit (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  config_id         UUID NOT NULL REFERENCES material_price_config(id)
                    ON DELETE RESTRICT,    -- không cascade — soft delete only

  usage_unit_name   TEXT NOT NULL,             -- 'giọt', 'muỗng', 'ml'
  to_stock_factor   NUMERIC(12,6) NOT NULL     -- 1 usage × factor = stock units
                    CHECK (to_stock_factor > 0),
  usage_unit_price  NUMERIC(15,4) NOT NULL,    -- = stock_unit_price × to_stock_factor

  -- Metadata
  uom_category      TEXT NOT NULL DEFAULT 'custom'
                    CHECK (uom_category IN ('volume', 'weight', 'count', 'length', 'custom')),
  is_discrete       BOOLEAN NOT NULL DEFAULT false,  -- true = SL phải nguyên (Ref: DEC-D07)
  is_default        BOOLEAN NOT NULL DEFAULT false,  -- default unit cho product này
  sort_order        INT NOT NULL DEFAULT 0,

  -- Audit
  created_by        TEXT,
  updated_by        TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at        TIMESTAMPTZ,               -- soft delete

  UNIQUE(config_id, usage_unit_name)           -- ngăn trùng tên đơn vị per config
);

-- Index: load usage units by config
CREATE INDEX idx_muu_config_id ON material_usage_unit(config_id);

-- Trigger: auto-update updated_at
CREATE OR REPLACE FUNCTION update_material_usage_unit_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_material_usage_unit_updated_at
  BEFORE UPDATE ON material_usage_unit
  FOR EACH ROW
  EXECUTE FUNCTION update_material_usage_unit_updated_at();

Bảng 4: material_batch — Lô vật tư (FIFO tracking)

CREATE TABLE material_batch (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  warehouse_id      UUID NOT NULL REFERENCES material_warehouse(id),
  product_id        UUID NOT NULL,
  batch_code        TEXT,
  expiry_date       DATE,
  purchase_price    NUMERIC(15,2) NOT NULL,
  purchase_quantity NUMERIC(12,4) NOT NULL CHECK (purchase_quantity > 0),
  unit_price        NUMERIC(15,4) NOT NULL,
  initial_qty       NUMERIC(12,4) NOT NULL CHECK (initial_qty > 0),
  remaining_qty     NUMERIC(12,4) NOT NULL CHECK (remaining_qty >= 0),
  status            TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'depleted', 'locked', 'disposed')),
  note              TEXT,
  created_by        TEXT,
  updated_by        TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at        TIMESTAMPTZ,
  CONSTRAINT unique_batch UNIQUE (warehouse_id, product_id, batch_code, created_at)
);

-- Index: FIFO query — lấy lô cũ nhất còn hàng
CREATE INDEX idx_mb_fifo ON material_batch (warehouse_id, product_id, created_at ASC)
  WHERE status = 'active' AND remaining_qty > 0 AND deleted_at IS NULL;

-- Index: Cron check HSD
CREATE INDEX idx_mb_expiry ON material_batch (expiry_date)
  WHERE expiry_date IS NOT NULL AND status = 'active' AND deleted_at IS NULL;

-- Trigger: auto-update updated_at
CREATE OR REPLACE FUNCTION update_material_batch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_material_batch_updated_at
  BEFORE UPDATE ON material_batch
  FOR EACH ROW
  EXECUTE FUNCTION update_material_batch_updated_at();

Bảng 5: material_stock_movement — Nhập/xuất/điều chỉnh (immutable)

CREATE TABLE material_stock_movement (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  warehouse_id          UUID NOT NULL REFERENCES material_warehouse(id),
  product_id            UUID NOT NULL,

  -- Batch tracking (v2.0)
  batch_id              UUID REFERENCES material_batch(id),  -- lô liên quan (NULL cho movement trước v2)
  batch_remaining_after NUMERIC(12,4),                        -- tồn lô sau giao dịch
  unit_price            NUMERIC(15,4),                        -- giá/stock_unit của lô tại thời điểm giao dịch

  -- Số lượng (stock unit)
  quantity_change       NUMERIC(12,4) NOT NULL,   -- dương = nhập, âm = xuất
  running_balance       NUMERIC(12,4) NOT NULL,   -- tồn sau giao dịch (Ref: DEC-D15)
  stock_unit            TEXT NOT NULL,

  -- Nguồn (Ref: DEC-D16, DEC-D29 — 'transfer' đã in-scope v3.0)
  source_type           TEXT NOT NULL
    CHECK (source_type IN (
      'stock_in',       -- nhập lô (thay 'manual' cho nhập lô mới)
      'manual',         -- nhập tay (legacy, backward compat)
      'auto_deduct',    -- trừ khi subtask done (FIFO split)
      'auto_reverse',   -- hoàn lại khi undo done (Ref: DEC-D18)
      'adjustment',     -- sau kiểm kê
      'wastage',        -- hao hụt / đổ bỏ / hết HSD
      'disposal',       -- hủy lô hết HSD (cron auto-lock)
      'transfer',       -- chuyển từ kho chi nhánh → kho vật tư (v3.0, DEC-D29, FR-022)
      'import',         -- import Excel
      'batch_reverse'   -- hủy phiếu nhập Excel (reverse batch)
    )),
  source_reference_id   UUID,             -- link đến subtask material / phiếu
  source_reference_type TEXT,             -- 'project_task_material', 'inventory_document'

  -- Metadata
  note                  TEXT,
  created_by            TEXT,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now()
  -- Không có updated_at, updated_by, deleted_at → immutable (Ref: BR-13)
);

-- Index: tồn kho hiện tại per product per warehouse (query DISTINCT ON)
CREATE INDEX idx_msm_warehouse_product
  ON material_stock_movement (warehouse_id, product_id, created_at DESC);

-- Index: truy vấn theo source (reverse lookup)
CREATE INDEX idx_msm_source
  ON material_stock_movement (source_reference_id, source_type);

-- Index: filter by source_type (báo cáo)
CREATE INDEX idx_msm_source_type
  ON material_stock_movement (source_type, created_at DESC);

-- Index: truy vấn movement theo batch (v2.0)
CREATE INDEX idx_msm_batch
  ON material_stock_movement (batch_id, created_at DESC)
  WHERE batch_id IS NOT NULL;

Bảng 6: ALTER project_task_material — Thêm cột giá + quy đổi

ALTER TABLE project_task_material
  ADD COLUMN usage_unit             TEXT,                       -- ĐVT sử dụng (snapshot)
  ADD COLUMN unit_price             NUMERIC(15,4),              -- snapshot giá (Phase 1 — ước tính. Phase 2 khi done = final. Ref: DEC-D05, DEC-D28)
  ADD COLUMN amount                 BIGINT,                     -- ROUND(unit_price × quantity)
  ADD COLUMN to_stock_factor        NUMERIC(12,6),              -- snapshot hệ số quy đổi
  ADD COLUMN stock_equivalent       NUMERIC(12,4),              -- quantity × to_stock_factor
  ADD COLUMN wastage_quantity       NUMERIC(12,4) DEFAULT 0,    -- hao hụt kỹ thuật
  ADD COLUMN wastage_reason         TEXT,                       -- lý do hao hụt
  ADD COLUMN total_stock_equivalent NUMERIC(12,4),              -- (quantity + wastage) × factor
  ADD COLUMN price_snapshot_at      TIMESTAMPTZ,                -- thời điểm snapshot giá (no DEFAULT — tránh table rewrite PG14)
  ADD COLUMN config_version_id      UUID REFERENCES material_price_config(id); -- FK → material_price_config

-- Index: lookup by config version (audit trail)
CREATE INDEX idx_ptm_config_version ON project_task_material(config_version_id)
  WHERE config_version_id IS NOT NULL;

View: material_stock_balance — Tồn kho hiện tại (v2.0 — batch-based)

-- v2.0: Tính tồn kho từ material_batch thay vì DISTINCT ON material_stock_movement
CREATE VIEW material_stock_balance AS
SELECT
  warehouse_id, product_id,
  SUM(remaining_qty) AS total_remaining,
  COUNT(*) FILTER (WHERE status = 'active' AND remaining_qty > 0) AS active_batch_count,
  MIN(expiry_date) FILTER (WHERE status = 'active' AND remaining_qty > 0) AS nearest_expiry,
  MAX(updated_at) AS last_updated
FROM material_batch
WHERE deleted_at IS NULL AND status IN ('active', 'depleted')
GROUP BY warehouse_id, product_id;

Breaking change v2.0: View không còn dùng DISTINCT ON trên material_stock_movement. Các query phụ thuộc running_balance, stock_unit, branch_id cần update. branch_id lấy qua JOIN material_warehouse. Thêm columns mới: active_batch_count, nearest_expiry.

Relationship Diagram

product (existing, ecommerce DB)
    │
    │ remote relationship (cross-DB)
    ▼
material_price_config (NEW, project DB)
    │ 1:N
    ├── material_usage_unit (NEW)
    │     └── config_id → material_price_config.id (FK, ON DELETE RESTRICT)
    │
    └── project_task_material (MODIFIED)
          ├── config_version_id → material_price_config.id (logical FK)
          ├── task_id → project_task.id (FK, ON DELETE CASCADE) [existing]
          └── product_id → ecommerce.product.id (remote) [existing]

material_warehouse (NEW)
    │ 1:N
    ├── material_batch (NEW — v2.0, FIFO tracking)
    │     ├── warehouse_id → material_warehouse.id (FK)
    │     ├── product_id → ecommerce.product.id (remote)
    │     └── 1:N → material_stock_movement (via batch_id)
    │
    └── material_stock_movement (NEW, immutable)
          ├── warehouse_id → material_warehouse.id (FK)
          ├── batch_id → material_batch.id (FK, nullable — v2.0)
          ├── product_id → ecommerce.product.id (remote)
          └── source_reference_id → project_task_material.id (logical)

material_stock_balance (VIEW on material_batch — v2.0)
    └── SUM(remaining_qty) GROUP BY (warehouse_id, product_id)

---

C5) API & Hasura

Hasura Metadata: public_material_warehouse.yaml

table:
  name: material_warehouse
  schema: public

select_permissions:
  - role: manager
    permission:
      columns: '*'
      filter:
        branch_id:
          _eq: X-Hasura-Branch-Id
  - role: admin
    permission:
      columns: '*'
      filter: {}

insert_permissions:
  - role: admin
    permission:
      columns: [branch_id, name, is_active]
      set:
        created_by: x-hasura-user-id

update_permissions:
  - role: admin
    permission:
      columns: [name, is_active, updated_by, deleted_at]

delete_permissions: []
# Soft delete only — no hard delete

Hasura Metadata: public_material_price_config.yaml

table:
  name: material_price_config
  schema: public

object_relationships: []

array_relationships:
  - name: usage_units
    using:
      foreign_key_constraint_on:
        column: config_id
        table:
          name: material_usage_unit
          schema: public

remote_relationships:
  - name: product
    definition:
      to_source:
        field_mapping:
          product_id: id
        relationship_type: object
        source: ecommerce
        table:
          name: product
          schema: public

select_permissions:
  - role: manager
    permission:
      columns: '*'
      filter:
        _or:
          - branch_id:
              _eq: X-Hasura-Branch-Id
          - branch_id:
              _is_null: true
      allow_aggregations: true
  - role: admin
    permission:
      columns: '*'
      filter: {}
      allow_aggregations: true

insert_permissions:
  - role: admin
    permission:
      columns:
        - product_id
        - branch_id
        - stock_unit
        - source_price
        - source_quantity
        - stock_unit_price
        - wastage_rate
        - costing_method
        - min_stock
        - max_stock
        - effective_from
        - effective_to
      set:
        created_by: x-hasura-user-id

update_permissions:
  - role: admin
    permission:
      columns:
        - effective_to
        - updated_by
        - deleted_at
      # Chỉ cho close version (set effective_to) hoặc soft delete
      # Không sửa giá — tạo version mới (Ref: DEC-D02)

delete_permissions: []
# Soft delete only — no hard delete

Hasura Metadata: public_material_usage_unit.yaml

table:
  name: material_usage_unit
  schema: public

object_relationships:
  - name: config
    using:
      foreign_key_constraint_on: config_id

select_permissions:
  - role: user
    permission:
      # Explicit columns — KHÔNG bao gồm usage_unit_price (Ref: DEC-D10, Staff không xem giá vốn)
      columns:
        - id
        - config_id
        - usage_unit_name
        - to_stock_factor
        - uom_category
        - is_discrete
        - is_default
        - sort_order
        - created_by
        - updated_by
        - created_at
        - updated_at
        - deleted_at
      filter: {}
  - role: manager
    permission:
      columns: '*'
      filter: {}
  - role: admin
    permission:
      columns: '*'
      filter: {}

insert_permissions:
  - role: admin
    permission:
      columns:
        - config_id
        - usage_unit_name
        - to_stock_factor
        - usage_unit_price
        - uom_category
        - is_discrete
        - is_default
        - sort_order
      set:
        created_by: x-hasura-user-id

update_permissions:
  - role: admin
    permission:
      columns:
        - to_stock_factor
        - usage_unit_price
        - is_discrete
        - is_default
        - sort_order
        - updated_by
        - deleted_at

delete_permissions: []
# Soft delete only — không cho hard delete (dùng update deleted_at)

Hasura Metadata: public_material_stock_movement.yaml

table:
  name: material_stock_movement
  schema: public

object_relationships:
  - name: warehouse
    using:
      foreign_key_constraint_on: warehouse_id

remote_relationships:
  - name: product
    definition:
      to_source:
        field_mapping:
          product_id: id
        relationship_type: object
        source: ecommerce
        table:
          name: product
          schema: public

select_permissions:
  - role: manager
    permission:
      columns: '*'
      filter:
        warehouse:
          branch_id:
            _eq: X-Hasura-Branch-Id
      allow_aggregations: true
  - role: admin
    permission:
      columns: '*'
      filter: {}
      allow_aggregations: true

insert_permissions:
  # running_balance tính server-side qua Hasura Action, KHÔNG cho client gửi trực tiếp
  - role: manager
    permission:
      columns:
        - warehouse_id
        - product_id
        - quantity_change
        - stock_unit
        - source_type
        - source_reference_id
        - source_reference_type
        - note
      set:
        created_by: x-hasura-user-id
  - role: admin
    permission:
      columns:
        - warehouse_id
        - product_id
        - quantity_change
        - stock_unit
        - source_type
        - source_reference_id
        - source_reference_type
        - note
      set:
        created_by: x-hasura-user-id

update_permissions: []
# Immutable — KHÔNG cho update (Ref: BR-13)

delete_permissions: []
# Immutable — KHÔNG cho delete (Ref: BR-13)

Hasura Metadata: public_material_stock_balance.yaml (View)

table:
  name: material_stock_balance
  schema: public
  is_view: true

select_permissions:
  - role: manager
    permission:
      columns: '*'
      filter:
        branch_id:
          _eq: X-Hasura-Branch-Id    # dùng branch_id thay vì X-Hasura-Allowed-Warehouse-Ids
  - role: admin
    permission:
      columns: '*'
      filter: {}

Hasura Metadata: public_material_batch.yaml (v2.0)

table:
  name: material_batch
  schema: public

object_relationships:
  - name: warehouse
    using:
      foreign_key_constraint_on: warehouse_id

array_relationships:
  - name: movements
    using:
      foreign_key_constraint_on:
        column: batch_id
        table:
          name: material_stock_movement
          schema: public

insert_permissions:
  - role: user
    permission:
      columns: [warehouse_id, product_id, batch_code, expiry_date, purchase_price, purchase_quantity, unit_price, initial_qty, remaining_qty, status, note]
      set:
        created_by: x-hasura-user-id

select_permissions:
  - role: user
    permission:
      columns: '*'
      filter: {}

update_permissions:
  - role: user
    permission:
      columns:
        - note
        - updated_by
        - updated_at
      filter: {}
      set:
        updated_by: x-hasura-user-id

delete_permissions:
  - role: user
    permission:
      filter: {}

material_batch — Hasura Permission Restriction (v3.0 P1 fix)

Vấn đề: Nếu role user có UPDATE permission trên remaining_qty, status → bypass immutable movement log.

Fix: Restrict Hasura UPDATE permission cho material_batch:

• Role user: UPDATE chỉ cho phép columns: note, updated_by, updated_at
• KHÔNG cho phép UPDATE trực tiếp: remaining_qty, status, purchase_price, purchase_quantity
• Các mutation trên remaining_qty/status chỉ qua backend handlers:
  • materialAutoDeduct event → UPDATE remaining_qty, status
  • materialAutoReverse event → UPDATE remaining_qty, status
  • transferToMaterialWarehouse action → INSERT only
  • material_batch_expiry_check cron → UPDATE status='locked'
  • disposeBatch action (nếu cần) → UPDATE status='disposed', remaining_qty=0

Hasura Metadata: Sửa public_project_task_material.yaml

Thêm cột mới vào permissions (bổ sung trên metadata hiện có):

# Thêm vào insert_permissions.columns (role: user)
- usage_unit
- unit_price
- amount
- to_stock_factor
- stock_equivalent
- wastage_quantity
- wastage_reason
- total_stock_equivalent
- price_snapshot_at
- config_version_id

# Thêm vào select_permissions.columns (role: user) — đã là '*'
# Không cần thay đổi

# Thêm vào update_permissions.columns (role: user)
- usage_unit
- unit_price
- amount
- to_stock_factor
- stock_equivalent
- wastage_quantity
- wastage_reason
- total_stock_equivalent
- price_snapshot_at
- config_version_id

Hasura Event Trigger: material_auto_deduct

event_triggers:
  - name: material_auto_deduct
    definition:
      enable_manual: true
      update:
        columns:
          - status_id
    table:
      name: project_task
      schema: public
    retry_conf:
      interval_sec: 10
      num_retries: 3
      timeout_sec: 60
    webhook: '{{RESTFUL_API_URL}}/events/material-auto-deduct'

Action: transferToMaterialWarehouse (★ NEW v3.0)

Type: Hasura Action (POST /actions) Handler: ecommerce-api Ref: DEC-D29, DEC-D31, DEC-D32, DEC-D33, DEC-D34, DEC-D36, FR-022

Input:

{
  "warehouse_id": "UUID — kho vật tư đích",
  "items": [
    {
      "product_id": "UUID",
      "lot_number": "string — mã lô nguồn (bắt buộc, DEC-D36)",
      "quantity": "number — SL đơn vị mua (chai/hộp)"
    }
  ]
}

Security: Client chỉ gửi định danh lô (product_id + lot_number) và quantity. Backend PHẢI lookup product_supplying để lấy purchase_price, supplier_id, expiry_date — KHÔNG trust giá trị do client gửi (tránh spoof giá/HSD).

Logic:

Transaction strategy (DEC-D37): ecommerce schema + material schema cùng 1 PostgreSQL instance. Go handler dùng 1 sql.Tx connection cho cả 2 schema → single ACID transaction. Không cần saga/outbox ở Phase 1.

1. Validate: warehouse exists, user has Manager/Admin role for branch (DEC-D31: không cần approval cho same-branch transfer)
2. Validate per item:
   • Product has material_price_config (source_quantity — DEC-D34: cấu hình quy đổi "1 chai = 500ml")
   • Lot exists: lot_number not empty
   • Source lot eligibility (DEC-D36 + P1 fix): lot phải có status = 'active' AND remaining_qty > 0. Lot locked (hết hạn) hoặc disposed → block 400 "Lô {lot_number} đã bị khóa/hủy, không thể chuyển". Near-expiry (≤90 ngày) → cho phép nhưng log warning.
   • Lot has sufficient quantity: remaining_qty >= requested_quantity
3. BEGIN TRANSACTION
4. Create inventory_document (behavior: export_material_warehouse, status: released) — DEC-D33
5. For each item:
   • a. Create product_supplying (type: export, lot_number, quantity) → deduct branch stock
   • b. Calculate stock_quantity = quantity × source_quantity (e.g., 2 chai × 500ml = 1000ml — DEC-D32)
   • c. Get wastage_rate from material_price_config for this product
   • d. Calculate unit_price = purchase_price / (stock_quantity × (1 - wastage_rate)) (DEC-D37b — khớp PRD) VD: 500,000đ / (1000ml × 0.98) = 510.20đ/ml (có tính hao hụt 2%)
   • e. Create material_batch (purchase_price, purchase_quantity=stock_quantity, unit_price, initial_qty=stock_quantity, remaining_qty=stock_quantity, expiry_date, status=active)
   • f. Create material_stock_movement (source_type=transfer, quantity_change=+stock_quantity, batch_id, source_reference_id=inventory_document.id)
6. COMMIT
7. Return success with batch IDs

Error responses:

HTTP	Message	Điều kiện
400	"Sản phẩm chưa cấu hình quy đổi đơn vị"	Product thiếu material_price_config.source_quantity
400	"Tồn lô không đủ: cần X, lô chỉ còn Y"	Lot remaining_qty < requested quantity
400	"Chọn mã lô hàng cho vật tư"	lot_number trống hoặc NULL
400	"Lô {lot_number} đã bị khóa/hủy, không thể chuyển"	Lot status != 'active' (locked/disposed)
403	"Không có quyền chuyển kho chi nhánh này"	User không có role Manager/Admin cho branch

# Hasura Action definition
actions:
  - name: transferToMaterialWarehouse
    definition:
      kind: synchronous
      handler: '{{ECOMMERCE_API_URL}}/actions/transfer-to-material-warehouse'
      forward_client_headers: true
    permissions:
      - role: manager
      - role: admin

---

Event: inventory_document_material_warehouse (★ NEW v3.0)

Trigger: inventory_document.status_id changed to inventory_released WHERE behavior IN ('export_material_warehouse')Handler: ecommerce-api/event Ref: DEC-D29, DEC-D33

Idempotency guard:

• Thêm column material_transfer_processed_at TIMESTAMPTZ vào inventory_document
• Trước khi xử lý: CHECK IF material_transfer_processed_at IS NOT NULL → SKIP (đã xử lý)
• Sau khi xử lý thành công: SET material_transfer_processed_at = now()
• Retry/replay sẽ bị skip nhờ guard này
• Cancel path tương tự: CHECK IF đã cancel → SKIP

event_triggers:
  - name: inventory_document_material_warehouse
    definition:
      enable_manual: true
      update:
        columns:
          - status_id
    table:
      name: inventory_document
      schema: public
    retry_conf:
      interval_sec: 10
      num_retries: 3
      timeout_sec: 60
    webhook: '{{ECOMMERCE_API_URL}}/events/inventory-document-material-warehouse'

Logic:

1. On Released: Gửi notification cho Manager + Admin của branch

   • Template: "Phiếu chuyển kho #{doc_code} đã hoàn thành — {item_count} vật tư đã nhập kho vật tư"
   • Dedupe: 1 notification / document

2. On Canceled (nếu batch chưa sử dụng):

   • Block cancel nếu batch đã có auto_deduct movements (đã sử dụng → không thể hoàn)
   • Branch-side reverse: tạo product_supplying type: release (canonical — khớp với inventory_document_update_status.go hiện tại: canceled → release records). KHÔNG dùng import — import là cho nhận hàng mới, release là hoàn lại hàng đã hold/export.
   • Material-side dispose: nếu remaining_qty = initial_qty VÀ không có auto_deduct movements → set batch status = disposed, remaining_qty = 0
   • Tạo material_stock_movement (source_type=disposal, quantity_change=-remaining_qty)
   • Set inventory_document.status_id = 'inventory_canceled'

---

GraphQL Fragments

# Fragment: MaterialWarehouse
fragment MaterialWarehouseFields on material_warehouse {
  id
  branch_id
  name
  is_active
  created_by
  updated_by
  created_at
  updated_at
  deleted_at
}

# Fragment: MaterialPriceConfig
fragment MaterialPriceConfigFields on material_price_config {
  id
  product_id
  branch_id
  stock_unit
  source_price
  source_quantity
  stock_unit_price
  wastage_rate
  costing_method
  min_stock
  max_stock
  effective_from
  effective_to
  created_by
  updated_by
  created_at
  updated_at
  deleted_at
}

# Fragment: MaterialUsageUnit
fragment MaterialUsageUnitFields on material_usage_unit {
  id
  config_id
  usage_unit_name
  to_stock_factor
  usage_unit_price
  uom_category
  is_discrete
  is_default
  sort_order
  created_by
  updated_by
  created_at
  updated_at
  deleted_at
}

# Fragment: MaterialBatch (v2.0)
fragment MaterialBatchFields on material_batch {
  id
  warehouse_id
  product_id
  batch_code
  expiry_date
  purchase_price
  purchase_quantity
  unit_price
  initial_qty
  remaining_qty
  status
  note
  created_by
  updated_by
  created_at
  updated_at
  deleted_at
}

# Fragment: MaterialStockMovement
fragment MaterialStockMovementFields on material_stock_movement {
  id
  warehouse_id
  product_id
  batch_id              # v2.0
  batch_remaining_after # v2.0
  unit_price            # v2.0
  quantity_change
  running_balance
  stock_unit
  source_type
  source_reference_id
  source_reference_type
  note
  created_by
  created_at
}

# Fragment: MaterialStockBalance (view — v2.0 batch-based)
fragment MaterialStockBalanceFields on material_stock_balance {
  warehouse_id
  product_id
  total_remaining       # v2.0 (thay running_balance)
  active_batch_count    # v2.0
  nearest_expiry        # v2.0
  last_updated
}

# Fragment: ProjectTaskMaterial (sửa — thêm cột mới)
fragment ProjectTaskMaterialFields on project_task_material {
  id
  task_id
  product_id
  product_name
  product_sku
  product_unit
  quantity
  note
  # --- Cột mới ---
  usage_unit
  unit_price
  amount
  to_stock_factor
  stock_equivalent
  wastage_quantity
  wastage_reason
  total_stock_equivalent
  price_snapshot_at
  config_version_id
  # ----------------
  created_by
  updated_by
  created_at
  updated_at
}

Mutations

# Mutation: Tạo/sửa price config (Admin only)
mutation InsertPriceConfig(
  $object: material_price_config_insert_input!
) {
  insert_material_price_config_one(object: $object) {
    ...MaterialPriceConfigFields
  }
}

# Mutation: Close version (set effective_to) — dùng khi Admin "Cập nhật giá"
mutation ClosePriceConfigVersion($id: uuid!, $effective_to: timestamptz!) {
  update_material_price_config_by_pk(
    pk_columns: { id: $id }
    _set: { effective_to: $effective_to, updated_by: "current-user" }
  ) {
    id
    effective_to
  }
}

# Mutation: Bulk insert usage units
mutation InsertUsageUnits(
  $objects: [material_usage_unit_insert_input!]!
) {
  insert_material_usage_unit(
    objects: $objects
    on_conflict: {
      constraint: material_usage_unit_config_id_usage_unit_name_key
      update_columns: [to_stock_factor, usage_unit_price, is_discrete, is_default, sort_order, updated_by]
    }
  ) {
    returning {
      ...MaterialUsageUnitFields
    }
  }
}

# Mutation: Delete usage unit (Admin only)
mutation DeleteUsageUnit($id: uuid!) {
  delete_material_usage_unit_by_pk(id: $id) {
    id
  }
}

# Mutation: Insert stock movement (manual / import / adjustment)
mutation InsertStockMovement(
  $objects: [material_stock_movement_insert_input!]!
) {
  insert_material_stock_movement(objects: $objects) {
    returning {
      ...MaterialStockMovementFields
    }
  }
}

# Mutation: Create batch when importing stock (Ref: FR-007, FR-018)
mutation InsertMaterialBatch($object: material_batch_insert_input!) {
  insert_material_batch_one(object: $object) {
    id
    warehouse_id
    product_id
    batch_code
    expiry_date
    purchase_price
    purchase_quantity
    unit_price
    initial_qty
    remaining_qty
    status
    created_at
  }
}

# Mutation: Batch stock-in (tạo lô + movement trong 1 action)
mutation BatchStockIn(
  $batch: material_batch_insert_input!
  $movement: material_stock_movement_insert_input!
) {
  insert_material_batch_one(object: $batch) {
    id
  }
  insert_material_stock_movement_one(object: $movement) {
    id
  }
}

# Mutation: Create warehouse (Admin only)
mutation InsertWarehouse($object: material_warehouse_insert_input!) {
  insert_material_warehouse_one(object: $object) {
    ...MaterialWarehouseFields
  }
}

# Mutation: Update warehouse (Admin only)
mutation UpdateWarehouse($id: uuid!, $set: material_warehouse_set_input!) {
  update_material_warehouse_by_pk(
    pk_columns: { id: $id }
    _set: $set
  ) {
    ...MaterialWarehouseFields
  }
}

# Mutation: Upsert task materials (with price snapshot)
mutation InsertTaskMaterialsWithPrice(
  $objects: [project_task_material_insert_input!]!
) {
  insert_project_task_material(
    objects: $objects
    on_conflict: {
      constraint: project_task_material_task_id_product_id_key
      update_columns: [
        quantity, note, usage_unit, unit_price, amount,
        to_stock_factor, stock_equivalent,
        wastage_quantity, wastage_reason, total_stock_equivalent,
        price_snapshot_at, config_version_id, updated_by
      ]
    }
  ) {
    returning {
      ...ProjectTaskMaterialFields
    }
  }
}

Queries

# Query: Lấy warehouse theo branch
query GetWarehouseByBranch($branchId: uuid!) {
  material_warehouse(
    where: {
      branch_id: { _eq: $branchId }
      deleted_at: { _is_null: true }
    }
    limit: 1
  ) {
    ...MaterialWarehouseFields
  }
}

# Query: Tồn kho hiện tại per warehouse (SCR-01)
query GetMaterialStockBalance($warehouseId: uuid!) {
  material_stock_balance(
    where: { warehouse_id: { _eq: $warehouseId } }
  ) {
    ...MaterialStockBalanceFields
  }
}

# Query: Config active per product (branch → global fallback) (Ref: BR-12)
query GetPriceConfig($productId: uuid!, $branchId: uuid) {
  material_price_config(
    where: {
      product_id: { _eq: $productId }
      effective_to: { _is_null: true }
      deleted_at: { _is_null: true }
      _or: [
        { branch_id: { _eq: $branchId } }
        { branch_id: { _is_null: true } }
      ]
    }
    order_by: { branch_id: desc_nulls_last }
    limit: 1
  ) {
    ...MaterialPriceConfigFields
    usage_units(
      where: { deleted_at: { _is_null: true } }
      order_by: { sort_order: asc }
    ) {
      ...MaterialUsageUnitFields
    }
  }
}

# Query: Tất cả config active (cho danh sách SCR-01)
query GetAllActiveConfigs($branchId: uuid) {
  material_price_config(
    where: {
      effective_to: { _is_null: true }
      deleted_at: { _is_null: true }
      _or: [
        { branch_id: { _eq: $branchId } }
        { branch_id: { _is_null: true } }
      ]
    }
    order_by: { product_id: asc, branch_id: desc_nulls_last }
  ) {
    ...MaterialPriceConfigFields
    usage_units(
      where: { deleted_at: { _is_null: true } }
      order_by: { sort_order: asc }
    ) {
      ...MaterialUsageUnitFields
    }
    product {
      id
      name
      sku
      disabled
      featured_image_url
    }
  }
}

# Query: Usage units per config
query GetUsageUnits($configId: uuid!) {
  material_usage_unit(
    where: {
      config_id: { _eq: $configId }
      deleted_at: { _is_null: true }
    }
    order_by: { sort_order: asc }
  ) {
    ...MaterialUsageUnitFields
  }
}

# Query: Lịch sử movement per product per warehouse (SCR-03)
query GetMovementHistory(
  $warehouseId: uuid!
  $productId: uuid!
  $limit: Int = 50
  $offset: Int = 0
) {
  material_stock_movement(
    where: {
      warehouse_id: { _eq: $warehouseId }
      product_id: { _eq: $productId }
    }
    order_by: { created_at: desc }
    limit: $limit
    offset: $offset
  ) {
    ...MaterialStockMovementFields
  }
  material_stock_movement_aggregate(
    where: {
      warehouse_id: { _eq: $warehouseId }
      product_id: { _eq: $productId }
    }
  ) {
    aggregate {
      count
    }
  }
}

# Query: Lịch sử giá per product (SCR-03)
query GetPriceHistory($productId: uuid!, $branchId: uuid) {
  material_price_config(
    where: {
      product_id: { _eq: $productId }
      deleted_at: { _is_null: true }
      _or: [
        { branch_id: { _eq: $branchId } }
        { branch_id: { _is_null: true } }
      ]
    }
    order_by: { effective_from: desc }
  ) {
    ...MaterialPriceConfigFields
  }
}

# Query: Tổng chi phí vật tư per đơn hàng (FORMULA-005)
query GetOrderMaterialCost($orderId: uuid!) {
  project_task_material_aggregate(
    where: {
      task: {
        parent_task: {
          order_id: { _eq: $orderId }
        }
        # DEC-D19: exclude canceled subtasks
        status_id: { _nlike: "%canceled%" }
      }
      amount: { _is_null: false }
    }
  ) {
    aggregate {
      sum {
        amount
      }
    }
  }
}

# ──────────────────────────────────────────────────────────────
# Expandable row queries (v3.0)
# ──────────────────────────────────────────────────────────────

# Query: GetMaterialListWithBatches (SCR-01 expandable — v3.0)
#
# SCR-01 expandable row cần thêm data:
# - material_usage_unit[] per product (tên, to_stock_factor, usage_unit_price tính từ batch FIFO)
# - material_batch[] WHERE status='active' AND remaining_qty > 0, ORDER BY created_at ASC
#   Fields: batch_code, remaining_qty, unit_price, expiry_date, created_at
#   Lô đầu tiên (FIFO) đánh dấu is_fifo_current = true
#
# Extend query GetMaterialWarehouseList thêm nested:
query GetMaterialListWithBatches($warehouseId: uuid!, $branchId: uuid) {
  material_price_config(
    where: {
      effective_to: { _is_null: true }
      deleted_at: { _is_null: true }
      _or: [
        { branch_id: { _eq: $branchId } }
        { branch_id: { _is_null: true } }
      ]
    }
    order_by: { product_id: asc, branch_id: desc_nulls_last }
  ) {
    ...MaterialPriceConfigFields
    material_usage_units(
      where: { deleted_at: { _is_null: true } }
      order_by: { sort_order: asc }
    ) {
      usage_unit_name
      to_stock_factor
      # usage_unit_price tính FE-side: batch FIFO unit_price × to_stock_factor
    }
    product {
      id
      name
      sku
      material_batches(
        where: {
          warehouse_id: { _eq: $warehouseId }
          status: { _eq: "active" }
          remaining_qty: { _gt: 0 }
        }
        order_by: { created_at: asc }
      ) {
        batch_code
        remaining_qty
        unit_price
        expiry_date
        created_at
        # FE đánh dấu is_fifo_current = true cho phần tử đầu tiên (index 0)
      }
    }
  }
}

# Query: GetSubtaskMaterialFIFODetail (SCR-04 done expandable — v3.0)
#
# Sau subtask done, [chi tiết] cần load movement data per vật tư
# để hiển thị lô nào bị trừ, số lượng, đơn giá, tồn lô sau trừ.
query GetSubtaskMaterialFIFODetail($ptmId: uuid!) {
  material_stock_movement(
    where: {
      source_reference_id: { _eq: $ptmId }
      source_type: { _eq: "auto_deduct" }
    }
    order_by: { created_at: asc }
  ) {
    batch_id
    quantity_change
    unit_price
    batch_remaining_after
    material_batch {
      batch_code
      status
      expiry_date
    }
  }
}

Error Contract

Scenario	Error	HTTP/GraphQL	FE handling
branch_id không tồn tại khi tạo warehouse	FK violation (nếu có FK) hoặc orphan data	200 (GraphQL error)	Toast "Chi nhánh không tồn tại"
Duplicate branch_id trên material_warehouse	UNIQUE violation	200 (GraphQL error)	Toast "Chi nhánh đã có kho vật tư"
Duplicate active config (product_id, branch_id)	Partial unique index violation	200 (GraphQL error)	Toast "Sản phẩm đã có cấu hình giá đang hoạt động"
Duplicate usage_unit_name per config	UNIQUE violation → upsert	200 (success via on_conflict)	Transparent — upsert handles
source_quantity <= 0	CHECK constraint violation	200 (GraphQL error)	Toast "SL đơn vị mua phải > 0"
to_stock_factor <= 0	CHECK constraint violation	200 (GraphQL error)	Toast "Hệ số quy đổi phải > 0"
wastage_rate >= 1 hoặc < 0	CHECK constraint violation	200 (GraphQL error)	Toast "Hao hụt phải từ 0% đến <100%"
costing_method invalid value	CHECK constraint violation	200 (GraphQL error)	Toast "Phương pháp tính giá không hợp lệ"
source_type invalid value (valid: stock_in/manual/auto_deduct/auto_reverse/adjustment/wastage/disposal/transfer/import/batch_reverse)	CHECK constraint violation	200 (GraphQL error)	Toast "Loại giao dịch không hợp lệ"
quantity <= 0 trên subtask material	FE validation block	N/A	Inline error "SL phải > 0"
is_discrete = true AND quantity có decimal	FE validation block	N/A	Inline error "Đơn vị rời — SL phải là số nguyên"
Unauthorized — Staff truy cập SCR-01	Hasura permission deny	200 (GraphQL error)	Menu ẩn, redirect nếu truy cập trực tiếp URL
Unauthorized — Manager sửa config	Hasura permission deny	200 (GraphQL error)	Toast "Không có quyền. Liên hệ Admin."
Update/Delete material_stock_movement	Hasura permission deny (no update/delete)	200 (GraphQL error)	Không có UI cho action này — chỉ log lỗi
Insert movement khi warehouse không tồn tại	FK violation	200 (GraphQL error)	Toast "Kho vật tư không tồn tại hoặc đã bị xóa"
Concurrent stock deduction race condition	pg_advisory_xact_lock serialize	N/A	Transparent — lock đảm bảo sequential
Event trigger handler timeout	Retry 3 lần, interval 10s	N/A	Log error → manual reconciliation

---

C6) Scheduler

Cron: material_batch_expiry_check (v2.0)

Thuộc tính	Giá trị
Schedule	0 0 * * * (hàng ngày 00:00 UTC+7)
Endpoint	POST /schedulers/material-batch-expiry-check
Timeout	120s
Retry	3 lần, interval 30s

Logic:

1. Auto-lock lô hết HSD:

   UPDATE material_batch
   SET status = 'locked', updated_by = 'system-cron', updated_at = now()
   WHERE expiry_date <= CURRENT_DATE
     AND status = 'active'
     AND deleted_at IS NULL;

   • Với mỗi lô bị lock: tạo material_stock_movement với source_type = 'disposal', quantity_change = -remaining_qty, batch_id = lô.id
   • Cập nhật remaining_qty = 0 trên lô

2. Cảnh báo lô sắp hết hạn (90 ngày):

   SELECT * FROM material_batch
   WHERE expiry_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '90 days'
     AND status = 'active'
     AND remaining_qty > 0
     AND deleted_at IS NULL;

   • Gửi notification cho Manager + Admin của branch liên quan
   • Dedupe: 1 notification / batch / tuần (check notification table: source_type = 'batch_expiry_warning', source_id = batch.id, created_at > now() - 7 days)

Lưu ý: Low stock notification vẫn trigger realtime khi auto_deduct hoặc adjustment xảy ra — không cần cron. Dedupe bằng check notification table (1/ngày/product/warehouse).

---

C7) Migration

Migration 1: Create material_warehouse

Path: diva-backend/services/controller/migrations/project/{timestamp}_create_material_warehouse/up.sql

Content: SQL từ C4 Bảng 1 (CREATE TABLE + INDEX + TRIGGER).

down.sql:

DROP TRIGGER IF EXISTS trg_material_warehouse_updated_at ON material_warehouse;
DROP FUNCTION IF EXISTS update_material_warehouse_updated_at();
DROP TABLE IF EXISTS material_warehouse;

Migration 2: Create material_price_config

Path: diva-backend/services/controller/migrations/project/{timestamp}_create_material_price_config/up.sql

Content: SQL từ C4 Bảng 2 (CREATE TABLE + INDEXES + TRIGGER).

down.sql:

DROP TRIGGER IF EXISTS trg_material_price_config_updated_at ON material_price_config;
DROP FUNCTION IF EXISTS update_material_price_config_updated_at();
DROP TABLE IF EXISTS material_price_config;

Migration 3: Create material_usage_unit

Path: diva-backend/services/controller/migrations/project/{timestamp}_create_material_usage_unit/up.sql

Content: SQL từ C4 Bảng 3 (CREATE TABLE + INDEX + TRIGGER).

down.sql:

DROP TRIGGER IF EXISTS trg_material_usage_unit_updated_at ON material_usage_unit;
DROP FUNCTION IF EXISTS update_material_usage_unit_updated_at();
DROP TABLE IF EXISTS material_usage_unit;

Migration 4: Create material_batch (v2.0)

Path: diva-backend/services/controller/migrations/project/{timestamp}_create_material_batch/up.sql

Content: SQL từ C4 Bảng 4 (CREATE TABLE + INDEXES + TRIGGER).

down.sql:

DROP TRIGGER IF EXISTS trg_material_batch_updated_at ON material_batch;
DROP FUNCTION IF EXISTS update_material_batch_updated_at();
DROP TABLE IF EXISTS material_batch;

Migration 5: Create material_stock_movement + View

Path: diva-backend/services/controller/migrations/project/{timestamp}_create_material_stock_movement/up.sql

Content: SQL từ C4 Bảng 5 + View material_stock_balance.

down.sql:

DROP VIEW IF EXISTS material_stock_balance;
DROP TABLE IF EXISTS material_stock_movement;

Migration 6: ALTER project_task_material

Path: diva-backend/services/controller/migrations/project/{timestamp}_alter_project_task_material_add_price_columns/up.sql

Content: SQL từ C4 Bảng 6 (ALTER TABLE + INDEX).

down.sql:

DROP INDEX IF EXISTS idx_ptm_config_version;
ALTER TABLE project_task_material
  DROP COLUMN IF EXISTS usage_unit,
  DROP COLUMN IF EXISTS unit_price,
  DROP COLUMN IF EXISTS amount,
  DROP COLUMN IF EXISTS to_stock_factor,
  DROP COLUMN IF EXISTS stock_equivalent,
  DROP COLUMN IF EXISTS wastage_quantity,
  DROP COLUMN IF EXISTS wastage_reason,
  DROP COLUMN IF EXISTS total_stock_equivalent,
  DROP COLUMN IF EXISTS price_snapshot_at,
  DROP COLUMN IF EXISTS config_version_id;

Hasura Metadata Files

1. New: metadata/databases/project/tables/public_material_warehouse.yaml
2. New: metadata/databases/project/tables/public_material_price_config.yaml
3. New: metadata/databases/project/tables/public_material_usage_unit.yaml
4. New: metadata/databases/project/tables/public_material_batch.yaml (v2.0)
5. New: metadata/databases/project/tables/public_material_stock_movement.yaml
6. New: metadata/databases/project/tables/public_material_stock_balance.yaml
7. Edit: metadata/databases/project/tables/public_project_task_material.yaml — thêm cột mới vào permissions
8. Edit: metadata/databases/project/tables/tables.yaml — thêm 6 entries mới
9. New: Event trigger config cho material_auto_deduct
10. New: Cron trigger config cho material_batch_expiry_check (v2.0)

Validation Checklist

• [ ] Migration timestamp > latest existing migration trong project DB
• [ ] Tên bảng snake_case, prefix material_ cho 5 bảng mới (incl. material_batch v2.0)
• [ ] FK warehouse_id references material_warehouse(id) — tạo migration 1 trước migration 4, 5
• [ ] FK config_id references material_price_config(id) — tạo migration 2 trước migration 3
• [ ] FK batch_id references material_batch(id) — tạo migration 4 trước migration 5
• [ ] Audit fields: created_at, updated_at, created_by, updated_by trên warehouse/config/usage_unit/batch
• [ ] material_stock_movement KHÔNG có updated_at, deleted_at — immutable by design (Ref: BR-13)
• [ ] Soft delete (deleted_at) trên warehouse, config, usage_unit, batch — KHÔNG trên stock_movement
• [ ] CHECK constraints: source_quantity > 0, to_stock_factor > 0, wastage_rate >= 0 AND < 1, purchase_quantity > 0, initial_qty > 0, remaining_qty >= 0
• [ ] Partial unique indexes: idx_mpc_product_active_global, idx_mpc_product_active_branch
• [ ] Partial index idx_mb_fifo — FIFO query performance
• [ ] Partial index idx_mb_expiry — cron HSD performance
• [ ] View material_stock_balance v2.0 dùng SUM(remaining_qty) từ material_batch — thay thế DISTINCT ON
• [ ] Hasura metadata YAML follows same structure as existing public_project_task_*.yaml files
• [ ] Event trigger webhook URL matches service endpoint pattern
• [ ] Cron trigger material_batch_expiry_check — endpoint + schedule đúng

---

C8) Security

Concern	Mitigation
SQL injection	Hasura parameterized queries — không có raw SQL từ client
Unauthorized access — Staff xem kho vật tư	Hasura role-based permissions: Staff (role: user) không có select permission trên material_warehouse, material_price_config. Menu ẩn.
Unauthorized access — Manager sửa giá	Hasura permissions: Manager chỉ có select, không có insert/update trên material_price_config
Cross-branch data leak	material_warehouse filter branch_id = X-Hasura-Branch-Id cho Manager. Admin xem tất cả.
Staff xem giá vốn vật tư	FE ẩn cột giá/thành tiền khi role = Staff (Ref: DEC-D10). Hasura permission trên project_task_material: cột unit_price, amount vẫn trả về nhưng FE không render.
Immutable stock movement bị sửa	material_stock_movement không có update/delete permissions trong Hasura (Ref: BR-13)
Race condition trừ kho đồng thời	pg_advisory_xact_lock(hashtext(warehouse_id), hashtext(product_id)) serialize per (warehouse, product) — 2 params (Ref: DEC-D18)
Mass insert stock movements	Rate limit event trigger handler: max 3 retries, 60s timeout
Giả mạo running_balance	BE handler tính running_balance server-side, không trust client value. FE gửi quantity_change, BE query latest balance + tính.

---

C9) NFR

ID	Requirement	Metric	Target
NFR-001	Insert stock movement (manual/import) không chậm	Response time	< 500ms cho 1 movement (single product)
NFR-002	Bulk insert stock movements (import Excel)	Response time	< 5s cho 50 products
NFR-003	Query tồn kho hiện tại (view)	Response time	< 200ms cho 500 products per warehouse
NFR-004	Query lịch sử movement (paginated)	Response time	< 300ms cho 50 rows per page
NFR-005	Event trigger auto-deduct latency	End-to-end time	< 2s từ status change → movement created
NFR-006	Aggregate chi phí vật tư per đơn hàng	Response time	< 200ms cho 20 subtask × 5 materials
NFR-007	Price config insert (with usage units)	Response time	< 500ms cho 1 config + 5 usage units
NFR-008	Concurrent auto-deduct (10 KTV cùng lúc)	Throughput	Tất cả serialize đúng, không race condition, < 5s total

---

C10) Observability

Metric	Cách đo	Alert threshold
material_stock_movement table size	pg_total_relation_size('material_stock_movement')	> 5GB → investigate partition (Ref: F-08)
material_stock_movement row count	SELECT count(*) FROM material_stock_movement (scheduled weekly)	> 5M rows → plan partition
Insert movement latency	Hasura request duration InsertStockMovement	p99 > 1s → alert
Stock balance view query latency	Hasura request duration GetMaterialStockBalance	p99 > 500ms → alert
Event trigger material_auto_deduct success rate	Hasura event trigger metrics	failure rate > 5% → alert
Event trigger material_auto_deduct latency	Time from trigger → handler complete	p99 > 5s → alert
Event trigger retry count	Hasura event trigger retry metrics	> 10 retries/hour → alert
pg_advisory_xact_lock wait time	pg_stat_activity WHERE wait_event = 'advisory'	> 3s average → alert
Low stock notification count	Notification API metrics	> 50/day per branch → review min_stock configs
Dead letter queue (failed events)	Hasura event trigger failed events	> 0 unprocessed after 1 hour → alert

---

C11) Tasks (Dev breakdown)

#	Task	Estimate	Dependency	Owner
T1	Tạo migration create_material_warehouse (table + partial unique index + trigger)	0.25d	—	BE
T2	Tạo migration create_material_price_config (table + partial unique indexes + trigger)	0.5d	—	BE
T3	Tạo migration create_material_usage_unit (table + unique + trigger, ON DELETE RESTRICT)	0.5d	T2	BE
T4	Tạo migration create_material_batch (table + FIFO index + expiry index + trigger) — v2.0	0.5d	T1	BE
T5	Tạo migration create_material_stock_movement + view material_stock_balance (v2.0 batch-based SUM)	0.5d	T1, T4	BE
T6	Tạo migration ALTER project_task_material (10 cột mới + index + FK config_version_id)	0.25d	T2	BE
T7	Tạo Hasura metadata 5 bảng mới + view (permissions, relationships, remote rel) + event trigger config + material_batch metadata — v2.0	1d	T1-T5	BE
T7b	Tạo Hasura metadata material_batch — v2.0	0.5d	T4, T7	BE
T8	Sửa Hasura metadata project_task_material (thêm cột vào permissions) + Event trigger handler materialAutoDeduct (Go, FIFO split logic, pg_advisory_xact_lock 2-param, idempotent check, running_balance server-side) + handler materialAutoReverse (Go, reverse movement + restore batch remaining_qty, sort + idempotent) + Low stock notification logic (dedupe 1/ngày, in-app notification) — v2.0: FIFO split tăng complexity	5d	T5-T7	BE
T9	Cron material_batch_expiry_check (auto-lock HSD + cảnh báo 90 ngày + dedupe 1/tuần) — v2.0	1d	T4, T7	BE
T10	FE: SCR-01 Trang danh sách kho vật tư (table + filter + badge tồn kho + QTable expand slot: ĐVT list + batch list v3.0)	2.5d	T7	FE
T11	FE: SCR-02 Form cấu hình vật tư (price + usage units + auto-calc)	2d	T7	FE
T12	FE: SCR-03 Chi tiết vật tư + lịch sử giá + lịch sử movement + danh sách lô (v2.0)	2d	T7	FE
T13	FE: SCR-06 Nhập kho manual + import Excel + mở rộng: giá lô, HSD, mã lô (v2.0)	2.5d	T5, T7	FE
T14	FE: SCR-07 Kiểm kê + điều chỉnh	1.5d	T5, T7	FE
T15	FE: SCR-04 Sửa MaterialForm — ĐVT selector, cột giá/thành tiền, snapshot + done detail expand: movement query + FIFO display v3.0	2.5d	T8	FE
T16	FE: SCR-05 Sửa AggregateView — cột giá, backward compat	0.5d	T15	FE
T17	FE: Sidebar tài chính — nguồn chi phí vật tư mới (FORMULA-005)	1d	T8	FE
T18	FE: Cảnh báo tồn kho trên form subtask (badge đỏ khi tồn thấp)	0.5d	T8, T15	FE
T19	FE: Router config + Sidebar navigation + Pinia store	0.5d	T7	FE
T20	FE: Permission check (ẩn cột giá Staff, menu ẩn)	0.5d	T7	FE
T21	FE: Batch reverse UI (hủy phiếu nhập Excel)	0.5d	T8, T13	FE
T22	QA: Test cases (~40-50 TC, bao gồm FIFO deduct, reverse, batch expiry, cron, idempotent, race condition, edge cases) — v2.0: thêm batch + FIFO TC	3.5d	All	QA
T23	★ v3.0: Thêm master_data entries cho export_material_warehouse, import_material_warehouse behaviors	0.25d	T7	BE
T24	★ v3.0: Tạo transferToMaterialWarehouse action handler trong ecommerce-api (validate + transaction + batch create + movement)	2d	T4, T5, T7, T23	BE
T25	★ v3.0: Tạo inventory_document_material_warehouse event trigger (notification + cancel logic + block cancel if used)	1d	T24	BE
T26	★ v3.0: FE — SCR-08 Transfer form page (reuse ProductLotNumberSelect, lot selection DEC-D36, unit conversion display DEC-D32)	2d	T24	FE
T27	★ v3.0: FE — Update SCR-01 thêm button [★ Chuyển kho] (permission: Manager/Admin)	0.5d	T26	FE
T28	★ v3.0: QA — Test cases transfer (~10-15 TC: happy path, insufficient lot, missing config, cancel batch, permission, unit conversion edge cases)	1.5d	T24-T27	QA
	Tổng	~37.25d

Phân chia team

Role	Tasks	Effort
Backend Dev	T1-T9, T23-T25 (DB + Hasura + batch + FIFO + event triggers + cron + notification + ★ transfer action/event)	~13.25d
Frontend Dev	T10-T21, T26-T27 (UI pages + components + batch list + stock-in mở rộng + ★ transfer form + expandable rows)	~19d
QA	T22, T28 (test cases incl. FIFO + batch + ★ transfer)	~5d

Gantt (simplified)

Week 1: T1-T7, T7b (DB + Hasura + batch metadata) — BE
         T10-T11 (SCR-01, SCR-02) — FE (parallel, sau T7 done)
Week 2: T8 (Event triggers + FIFO split + notification) — BE
         T9 (Cron batch expiry) — BE
         T12-T14 (SCR-03 + batch list, SCR-06 + stock-in, SCR-07) — FE
Week 3: T15-T21 (MaterialForm, Aggregate, Sidebar, Cảnh báo, Router, Permission, Batch reverse) — FE
         T23 (master_data entries) — BE
         T24 (transferToMaterialWarehouse action handler) — BE
         T22 (QA bắt đầu, test DB + BE + FIFO trước) — QA
Week 4: T25 (event trigger inventory_document_material_warehouse) — BE
         T26-T27 (SCR-08 Transfer form + SCR-01 button) — FE
         T22 (QA tiếp tục, regression + batch/FIFO TC) — QA
Week 5: T28 (QA transfer TC) + Bug fix — All

---

C12) Traceability

FR → Artifact → Test Case

FR/NFR	Mô tả (khớp prd.md)	FE Artifact	BE Artifact	TC-ID	Status
FR-001	Tạo kho vật tư per branch (auto-create)	SCR-01 (implicit)	InsertWarehouse mutation	TC-MW-001, TC-MW-002	Planned
FR-002	Xem danh sách kho vật tư + tồn kho + cảnh báo	SCR-01 List page	GetAllActiveConfigs + GetMaterialStockBalance	TC-MW-003	Planned
FR-003	Quản lý vật tư — thêm/ẩn	SCR-01 "+ Thêm vật tư"	InsertPriceConfig + soft delete	TC-MW-004, TC-MW-005	Planned
FR-004	Cấu hình giá vật tư per product (source_price, wastage, stock_unit)	SCR-02 ConfigForm	InsertPriceConfig mutation	TC-PC-001, TC-PC-002	Planned
FR-005	Cập nhật giá (versioning) — close version cũ, tạo version mới	SCR-03 "Cập nhật giá"	ClosePriceConfigVersion + InsertPriceConfig	TC-PC-003, TC-PC-004	Planned
FR-006	Cấu hình đơn vị quy đổi (usage_unit, factor)	SCR-02 UsageUnit table	InsertUsageUnits mutation	TC-UU-001, TC-UU-002, TC-UU-003	Planned
FR-007	Nhập tồn kho manual (tạo lô + movement)	SCR-06 StockImport	BatchStockIn (InsertMaterialBatch + InsertStockMovement, source_type='stock_in')	TC-SI-001, TC-SI-002	Planned
FR-008	Nhập tồn kho bằng Excel (tạo lô per dòng)	SCR-06 ImportExcel	BatchStockIn per row (source_type='stock_in')	TC-SI-003	Planned
FR-009	Chọn đơn vị sử dụng khi thêm vật tư vào subtask	SCR-04 MaterialForm ĐVT dropdown	material_usage_unit query	TC-SM-001, TC-SM-002	Planned
FR-010	Two-Phase Pricing: ước tính lúc save + finalize lúc done (DEC-D28)	SCR-04 badge "(ước tính)"	InsertTaskMaterialsWithPrice + auto-deduct UPDATE	TC-PRICE-001~004	Planned
FR-011	Ghi nhận hao hụt kỹ thuật (wastage_quantity)	SCR-04 wastage fields	total_stock_equivalent calculation	TC-SM-003	Planned
FR-012	Auto-deduct khi subtask done (FIFO, block nếu thiếu)	—	materialAutoDeduct event trigger handler	TC-AD-001, TC-FIFO-001~003	Planned
FR-013	Auto-reverse khi undo done / canceled sau done	—	materialAutoReverse event trigger handler	TC-AD-002	Planned
FR-014	Kiểm kê + điều chỉnh chênh lệch	SCR-07 Inventory	InsertStockMovement (source_type='adjustment')	TC-SK-001	Planned
FR-015	Cảnh báo tồn kho thấp (notification)	Badge đỏ SCR-01, SCR-04	sendLowStockNotification logic	TC-AL-001	Planned
FR-016	Chi phí vật tư trong sidebar tài chính đơn hàng	FinancialSidebar	GetOrderMaterialCost query (FORMULA-005)	TC-FI-001	Planned
FR-017	Import cấu hình Excel (danh mục vật tư + giá)	SCR-02 Import	BE parse + batch insert configs	TC-CFG-001, TC-CFG-002	Planned
FR-018	Quản lý lô hàng (batch lifecycle)	SCR-03 BatchList	InsertMaterialBatch + batch status updates	TC-BATCH-001, TC-BATCH-002	Planned
FR-019	HSD — cảnh báo 90 ngày + auto-lock hết hạn	SCR-03 expiry badge	Cron material_batch_expiry_check	TC-HSD-001, TC-HSD-002, TC-HSD-003	Planned
FR-020	FIFO xuất kho (auto-deduct FIFO split)	SCR-04 (price display)	materialAutoDeduct FIFO handler	TC-FIFO-001, TC-FIFO-002, TC-FIFO-003	Planned
FR-021	Hủy lô (disposal)	SCR-03 "Hủy lô" button	UpdateBatchStatus + disposal movement	TC-DISPOSE-001	Planned
FR-022	★ v3.0: Chuyển kho chi nhánh → kho vật tư (transfer via inventory_document)	SCR-08 Transfer form, SCR-01 [★ Chuyển kho] button	transferToMaterialWarehouse action, inventory_document_material_warehouse event trigger	TC-TRF-001, TC-TRF-002, TC-TRF-003	Planned
NFR-001	Insert movement < 500ms	—	DB + Hasura perf	TC-NFR-001	Planned
NFR-003	Stock balance query < 200ms	—	View batch-based SUM perf (v2.0)	TC-NFR-003	Planned
NFR-005	Auto-deduct < 2s end-to-end	—	Event trigger latency	TC-NFR-005	Planned
NFR-008	Concurrent deduct — no race condition	—	pg_advisory_xact_lock test	TC-NFR-008	Planned

Event Trigger Pseudocode (BE reference)

materialAutoDeduct — Go handler

// POST /events/material-auto-deduct
// Hasura event trigger payload: project_task row (old + new)
// v2.0: FIFO batch-based deduction

func MaterialAutoDeductHandler(c *gin.Context) {
  payload := parseEventPayload(c)
  newTask := payload.Event.Data.New
  oldTask := payload.Event.Data.Old

  // Guard: chỉ xử lý khi status chuyển sang done
  if !isDoneStatus(newTask.StatusID) || isDoneStatus(oldTask.StatusID) {
    // Nếu old đã done VÀ new không done → đây là undo → gọi reverse
    if isDoneStatus(oldTask.StatusID) && !isDoneStatus(newTask.StatusID) {
      materialAutoReverse(newTask.ID)
      return
    }
    c.JSON(200, gin.H{"message": "skipped"})
    return
  }

  // Chỉ xử lý subtask (có parent_id)
  if newTask.ParentID == nil {
    c.JSON(200, gin.H{"message": "not a subtask"})
    return
  }

  // Lấy danh sách vật tư trên subtask
  materials := queryTaskMaterials(newTask.ID)

  warehouse := getWarehouseByBranch(getBranchFromTask(newTask))
  if warehouse == nil {
    log.Warn("No warehouse for branch", getBranchFromTask(newTask))
    c.JSON(200, gin.H{"message": "no warehouse"})
    return
  }

  // Sort product_ids ascending trước khi lock — prevent deadlock
  sort.Slice(materials, func(i, j int) bool {
    return materials[i].ProductID.String() < materials[j].ProductID.String()
  })

  for _, mat := range materials {
    if mat.TotalStockEquivalent == nil {
      continue // chưa config → skip
    }

    // === IDEMPOTENT CHECK: skip nếu đã deduct cho material này ===
    exists := db.Exists("SELECT 1 FROM material_stock_movement WHERE source_reference_id = ? AND source_type = 'auto_deduct'", mat.ID)
    if exists {
      log.Info("Already deducted for material", mat.ID)
      continue
    }

    // === CRITICAL: Serialize per (warehouse, product) ===
    tx := db.Begin()
    tx.Exec("SELECT pg_advisory_xact_lock(hashtext($1), hashtext($2))",
      warehouse.ID.String(), mat.ProductID.String())

    // === v2.0: FIFO BATCH DEDUCTION ===
    // Lấy các lô active còn hàng, sắp xếp theo created_at ASC (FIFO)
    batches := tx.Query(`
      SELECT id, remaining_qty, unit_price
      FROM material_batch
      WHERE warehouse_id = $1 AND product_id = $2
        AND status = 'active' AND remaining_qty > 0
        AND deleted_at IS NULL
      ORDER BY created_at ASC
      FOR UPDATE`, warehouse.ID, mat.ProductID)

    totalToDeduct := *mat.TotalStockEquivalent
    remainingToDeduct := totalToDeduct
    currentBalance := getLatestRunningBalance(tx, warehouse.ID, mat.ProductID)
    var totalAmount float64

    for _, batch := range batches {
      if remainingToDeduct <= 0 {
        break
      }

      // Tính SL trừ từ lô này
      deductFromBatch := min(batch.RemainingQty, remainingToDeduct)
      newBatchRemaining := batch.RemainingQty - deductFromBatch
      currentBalance -= deductFromBatch
      totalAmount += batch.UnitPrice * deductFromBatch

      // Cập nhật remaining_qty của lô
      tx.Exec(`UPDATE material_batch SET remaining_qty = $1, status = CASE WHEN $1 = 0 THEN 'depleted' ELSE status END, updated_at = now() WHERE id = $2`,
        newBatchRemaining, batch.ID)

      // Insert movement per batch
      tx.Create(&MaterialStockMovement{
        WarehouseID:         warehouse.ID,
        ProductID:           mat.ProductID,
        BatchID:             &batch.ID,
        BatchRemainingAfter: &newBatchRemaining,
        UnitPrice:           &batch.UnitPrice,
        QuantityChange:      -deductFromBatch,
        RunningBalance:      currentBalance,
        StockUnit:           getStockUnit(mat.ProductID),
        SourceType:          "auto_deduct",
        SourceReferenceID:   &mat.ID,
        SourceReferenceType: strPtr("project_task_material"),
      })

      remainingToDeduct -= deductFromBatch
    }

    // Nếu không đủ tồn kho → BLOCK toàn bộ, rollback (DEC-D23: không cho phép tồn kho âm, không partial deduct)
    if remainingToDeduct > 0 {
      tx.Rollback()
      log.Error("Insufficient stock for deduct",
        "warehouse", warehouse.ID,
        "product", mat.ProductID,
        "needed", totalToDeduct,
        "shortage", remainingToDeduct,
      )
      // Gửi notification cho Manager + Admin
      sendInsufficientStockNotification(warehouse, mat.ProductID, totalToDeduct, remainingToDeduct)
      // Return error → subtask KHÔNG chuyển done
      c.JSON(400, gin.H{"error": fmt.Sprintf("Không đủ tồn kho %s: cần %v, thiếu %v", mat.ProductName, totalToDeduct, remainingToDeduct)})
      return
    }

    if err := tx.Commit(); err != nil {
      tx.Rollback()
      log.Error("Failed to deduct stock", err)
      continue
    }

    // Cảnh báo tồn kho thấp
    minStock := getMinStock(mat.ProductID)
    if minStock != nil && currentBalance <= *minStock {
      sendLowStockNotification(warehouse, mat.ProductID, currentBalance, *minStock)
    }
  }

  c.JSON(200, gin.H{"message": "ok"})
}

materialAutoReverse — Go handler

// Được gọi từ materialAutoDeduct khi detect undo done
// v2.0: Restore batch remaining_qty khi reverse

func materialAutoReverse(taskID uuid.UUID) {
  // Step 1: Query materials by taskID TRƯỚC
  materials := queryTaskMaterials(taskID)

  // Step 2: Từ material.id → query movements auto_deduct (có batch_id)
  var movements []MaterialStockMovement
  for _, mat := range materials {
    movs := queryMovementsBySourceRef(mat.ID, "auto_deduct")
    movements = append(movements, movs...)
  }

  if len(movements) == 0 {
    log.Info("No auto_deduct movements found for task", taskID)
    return
  }

  // Sort product_ids ascending trước khi lock — prevent deadlock
  sort.Slice(movements, func(i, j int) bool {
    if movements[i].WarehouseID.String() == movements[j].WarehouseID.String() {
      return movements[i].ProductID.String() < movements[j].ProductID.String()
    }
    return movements[i].WarehouseID.String() < movements[j].WarehouseID.String()
  })

  for _, mov := range movements {
    // === IDEMPOTENT CHECK: skip nếu đã reverse cho movement này ===
    exists := db.Exists("SELECT 1 FROM material_stock_movement WHERE source_reference_id = ? AND source_type = 'auto_reverse' AND batch_id = ?", mov.SourceReferenceID, mov.BatchID)
    if exists {
      log.Info("Already reversed for material+batch", mov.SourceReferenceID, mov.BatchID)
      continue
    }

    tx := db.Begin()

    // === CRITICAL: Serialize per (warehouse, product) — 2 params ===
    tx.Exec("SELECT pg_advisory_xact_lock(hashtext($1), hashtext($2))",
      mov.WarehouseID.String(), mov.ProductID.String())

    currentBalance := getLatestRunningBalance(tx, mov.WarehouseID, mov.ProductID)
    reverseQty := abs(mov.QuantityChange)
    newBalance := currentBalance + reverseQty

    // === v2.0: Restore batch remaining_qty ===
    if mov.BatchID != nil {
      tx.Exec(`UPDATE material_batch
        SET remaining_qty = remaining_qty + $1,
            status = 'active',
            updated_at = now()
        WHERE id = $2`, reverseQty, *mov.BatchID)
    }

    newBatchRemaining := float64(0)
    if mov.BatchID != nil {
      newBatchRemaining = getBatchRemainingQty(tx, *mov.BatchID) // after restore
    }

    tx.Create(&MaterialStockMovement{
      WarehouseID:         mov.WarehouseID,
      ProductID:           mov.ProductID,
      BatchID:             mov.BatchID,
      BatchRemainingAfter: &newBatchRemaining,
      UnitPrice:           mov.UnitPrice,
      QuantityChange:      reverseQty,
      RunningBalance:      newBalance,
      StockUnit:           mov.StockUnit,
      SourceType:          "auto_reverse",
      SourceReferenceID:   mov.SourceReferenceID,
      SourceReferenceType: strPtr("project_task_material"),
      Note:                strPtr("Reverse: subtask undo/canceled — batch restored"),
    })

    if err := tx.Commit(); err != nil {
      tx.Rollback()
      log.Error("Failed to reverse stock", err)
    }
  }
}

---

Changelog

Version	Ngày	Thay đổi	Tác giả
1.0	2026-03-26	Khởi tạo dev spec — C1~C12 đầy đủ từ design doc v1.1. 6 formulas, 5 DB tables, 4 Hasura metadata, 2 event triggers, 20 tasks, 20 FR traceability.	PO/BA + AI
1.1	2026-03-27	Review fixes: idempotent check, advisory lock 2-param, partial unique index, Hasura permission restrict, fix reverse logic, sync task numbering	PO/BA + AI
2.0	2026-03-27	Design v2 — Batch-based FIFO: Thêm bảng material_batch (FIFO tracking, HSD, mã lô), sửa material_stock_movement (+batch_id, +batch_remaining_after, +unit_price, +source_type: stock_in/disposal), sửa view material_stock_balance (batch-based SUM thay DISTINCT ON), thêm FORMULA-001b (batch_unit_price), sửa FORMULA-002/003 (FIFO), thêm Hasura metadata material_batch, thêm cron material_batch_expiry_check (auto-lock HSD + cảnh báo 90d), sửa pseudocode auto-deduct/reverse cho FIFO split + batch restore, DEC-D04 superseded (latest_price → FIFO), task breakdown tăng ~23d → ~29d (+6d).	PO/BA + AI
2.1	2026-03-27	Post-review fixes: (1) Pseudocode materialAutoDeduct — partial deduct → block entirely + rollback + notification (DEC-D23); (2) FORMULA-003 thêm Two-Phase Pricing timing (DEC-D28); (3) Thêm mutation InsertMaterialBatch + BatchStockIn; (4) C12 traceability thêm FR-018~021 (batch lifecycle, HSD, FIFO, disposal), sửa FR-008/009 map đúng BatchStockIn; (5) Thêm ghi chú DEC-D28 vào snapshot giá sections.	PO/BA + AI
3.0	2026-03-27	Chuyển kho chi nhánh → kho vật tư (v3.0): (1) C1 Scope: thêm transfer via inventory_document vào in-scope, cập nhật out-of-scope (kho tổng thay kho chính); (2) C2 Impact: +11 dòng mới (inventory_document +2 behaviors, product_supplying lot tracking, master_data +2 entries, ProductLotNumberSelect reuse, action + event trigger + handler, SCR-08 + SCR-01 modify); (3) C5 API: thêm Hasura Action transferToMaterialWarehouse (validate → transaction → batch create → movement, DEC-D29/D31/D32/D33/D34/D36); (4) C5 Event: thêm inventory_document_material_warehouse trigger (notification + cancel logic + block cancel if used); (5) C11 Tasks: +6 tasks (T23-T28), tổng ~29d → ~36.25d; (6) C12 Traceability: thêm FR-022; (7) Gantt mở rộng sang Week 5.	PO/BA + AI