Database Schema

10 bảng chính, mọi bảng có RLS, audit log insert-only. Source: openspec/changes/wfp-mvp/supabase-schema.md.

1. ERD tổng quan

erDiagram
    warehouses ||--o{ customer_warehouse : "operates at"
    customers  ||--o{ customer_warehouse : "delivered to"
    customer_warehouse ||--o{ historical_orders : "history"
    customer_warehouse ||--o{ forecast_adjustments : "request"
    customer_warehouse ||--o{ forecast_results : "computed"
    forecast_adjustments ||--o{ audit_log : "logs"
    forecast_results     ||--o{ audit_log : "logs"
    config_definitions ||--o{ config_values : "values"
    customers ||--o{ product_mix : "mix"
    product_groups ||--o{ product_mix : "in"
    warehouses ||--o{ uph_config : "per wh"
    product_groups ||--o{ uph_config : "per group"

Text mô tả: Trục chính là customer_warehouse (cặp KH × Kho) — mọi historical_orders, forecast_adjustments, forecast_results đều thuộc 1 cặp này. Config tham số tách bảng config_definitions + config_values. WLU phụ thuộc uph_config × product_mix.

2. Bảng chính (10)

#	Bảng	Mô tả	Insert-only?
1	warehouses	Danh mục kho	–
2	customers	Danh mục khách	–
3	customer_warehouse	Cặp KH × Kho (tier, active)	–
4	product_groups	Nhóm sản phẩm (NORMAL/HEAVY/...)	–
5	product_mix	Mix theo khách × group × pk_type (%)	–
6	uph_config	UPH theo (warehouse, group, pk_type)	–
7	historical_orders	Đơn lịch sử 90 ngày (raw)	–
8	forecast_adjustments	Yêu cầu KAM (state machine)	–
9	forecast_results	Output nightly job + audited_qty	–
10	audit_log	Trail mọi action	✅ Yes
+	config_definitions / config_values	Tham số mùa, event multipliers	–

3. Conventions

• Mọi bảng có: id uuid pk default uuid_generate_v4(), created_at, updated_at, created_by uuid references auth.users
• snake_case cho table + column
• Index chuẩn trên (warehouse_id, customer_id, date) ở mọi bảng có 3 cột này
• Migration: M{phase}_{purpose}.sql (e.g. M1_core.sql)

4. RLS pattern

-- ví dụ cho forecast_adjustments
alter table forecast_adjustments enable row level security;

create policy "planner reads all"
on forecast_adjustments for select
using ( is_planner() );

create policy "kam reads own"
on forecast_adjustments for select
using ( created_by = auth.uid() );

create policy "kam inserts own"
on forecast_adjustments for insert
with check ( created_by = auth.uid() );

create policy "planner updates state"
on forecast_adjustments for update
using ( is_planner() );

5. Audit log — insert only

alter table audit_log enable row level security;

revoke update, delete on audit_log from anon, authenticated;

create policy "all authenticated can read audit"
on audit_log for select using ( auth.role() = 'authenticated' );

create policy "all authenticated can insert audit"
on audit_log for insert with check ( actor_id = auth.uid() );

audit_log schema (rút gọn):

Cột	Kiểu	Ghi chú
id	uuid	PK
entity_type	text	adjustment, forecast_result, config, import
entity_id	uuid	–
action	text	STATE_CHANGE, QTY_EDIT, CONFIG_CHANGE, IMPORT
before / after	jsonb	snapshot
reason	text	bắt buộc khi Δ > 30% (UI enforce)
actor_id	uuid	= auth.uid()
created_at	timestamptz	–

6. State machine ràng buộc

Adjustment state machine enforce cả ở DB (CHECK constraint) lẫn ở engine:

alter table forecast_adjustments
add constraint chk_state
check (state in ('NEW','SEEN','ACCEPTED','REJECTED'));

Transitions hợp lệ:

Từ	→	Tới
NEW	→	SEEN, ACCEPTED, REJECTED
SEEN	→	ACCEPTED, REJECTED
ACCEPTED, REJECTED	→	terminal (KHÔNG sửa)

7. Index chính

create index idx_hist_wh_cust_date
  on historical_orders (warehouse_id, customer_id, date desc);

create index idx_fc_wh_cust_date
  on forecast_results (warehouse_id, customer_id, date);

create index idx_adj_state_date
  on forecast_adjustments (state, target_date);

8. Seed data (demo)

Run npm run seed → load dist/seed-snapshot.json. Reset demo: npm run demo:reset.

Schema đầy đủ + tất cả migration: openspec/changes/wfp-mvp/supabase-schema.md

Tiếp: Data Flow · Deployment