# 05 — Lược đồ cơ sở dữ liệu (PostgreSQL 18) Phần 1
Tài liệu #3. Tham chiếu:
01-master-plan.md§8 ·02-domain-model.md— mã bất biến (⚑) trong tài liệu này trỏ về §23 của doc 02. Ngày lập: 13/08/2026 · Trạng thái: Bản nháp 1 — đã kiểm chứng cú pháp trên PostgreSQL 18 Phạm vi: lược đồ vật lý. Nghiệp vụ xem doc 02; hạ tầng vận hành xem doc 01 §8.9.
0. Cách đọc & cách kiểm chứng
Toàn bộ DDL trong tài liệu này được viết để chạy được nguyên văn trên PostgreSQL 18. Cách kiểm chứng:
docker run --rm -d --name pgcheck -e POSTGRES_PASSWORD=secret -p 55432:5432 pgvector/pgvector:pg18
# ghép toàn bộ code block SQL của tài liệu này thành schema.sql rồi:
docker exec -i pgcheck psql -U postgres -v ON_ERROR_STOP=1 < schema.sql
docker rm -f pgcheck
Ký hiệu:
⚑ Xn— cưỡng chế một bất biến trong doc 02 §23⚠— chỗ dễ sai, đã có người trả giá⏳ Pn— chỉ tạo ở pha n🔧— điều chỉnh so với doc 01 (giải thích ở §12)
1. Ba lỗi SQL trong doc 01 và cách sửa 🔧
Ba đoạn DDL minh hoạ trong doc 01 không chạy được trên PostgreSQL. Tài liệu này là bản đúng; doc 01 sẽ được vá theo.
1.1 unaccent() không IMMUTABLE → không dùng được trong generated column và index
Doc 01 §8.2 viết:
-- ✗ LỖI: ERROR: generation expression is not immutable
search_vec tsvector GENERATED ALWAYS AS (
to_tsvector('simple', unaccent(coalesce(name,'')))
) STORED
unaccent(text) một tham số là STABLE, không phải IMMUTABLE, vì nó tra cứu từ điển qua search_path. PostgreSQL từ chối dùng nó trong generated column và trong index expression.
Cách sửa chuẩn — bọc bằng dạng hai tham số (dạng này là IMMUTABLE):
CREATE OR REPLACE FUNCTION f_unaccent(text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT
AS $$ SELECT public.unaccent('public.unaccent'::regdictionary, $1) $$;
Từ đây mọi nơi trong dự án dùng f_unaccent(), không bao giờ gọi unaccent() trực tiếp trong DDL.
⚠ Nếu sau này ai đó DROP EXTENSION unaccent rồi tạo lại ở schema khác, các index dựa trên f_unaccent sẽ hỏng. Vì vậy hàm ghim rõ public.unaccent bằng tên đầy đủ.
1.2 Bảng phân mảnh: khoá chính phải chứa cột phân mảnh
Doc 01 §8.5 viết:
-- ✗ LỖI: unique constraint on partitioned table must include all partitioning columns
CREATE TABLE stock_movements (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
...
) PARTITION BY RANGE (occurred_at);
Đúng: PRIMARY KEY (id, occurred_at).
1.3 Không phân mảnh orders — quyết định đảo ngược so với doc 01 🔧
Doc 01 §8.9 đề xuất phân mảnh orders theo tháng. Hệ quả kỹ thuật của việc đó:
- PK của
ordersphải thành(id, placed_at). - Mọi khoá ngoại trỏ vào
orderscũng phải thành khoá kép —order_items,payments,shipments,invoices,returnsđều phải mang thêm cộtorder_placed_atvà giữ nó đồng bộ vĩnh viễn. - Truy vấn "tìm đơn theo
order_number" không cóplaced_atsẽ quét mọi phân mảnh.
Chi phí đó không xứng đáng cho tới khi orders thực sự rất lớn. Quyết định: không phân mảnh orders. Thay vào đó:
| Chiến lược | Áp dụng cho |
|---|---|
| Phân mảnh RANGE theo tháng | Chỉ bảng append-only không có khoá ngoại trỏ vào: stock_movements, outbox_messages, audit_logs, carrier_events, webhook_logs |
| Index bộ phận + BRIN | orders — đơn đang mở chỉ chiếm vài phần nghìn, index bộ phận giải quyết 95% truy vấn nóng |
| Lưu trữ (archive) | orders cũ hơn 3 năm chuyển sang orders_archive khi cần |
Ngưỡng xem xét lại: orders > 100 triệu dòng hoặc thời gian VACUUM bảng vượt cửa sổ bảo trì.
2. Quy ước chung
| Chủ đề | Quy ước | Lý do |
|---|---|---|
| Tên bảng | snake_case, số nhiều: products, order_items |
Khớp mặc định Eloquent |
| Tên cột | snake_case; khoá ngoại <đơn_số>_id |
|
| Khoá chính nội bộ | id bigint GENERATED ALWAYS AS IDENTITY |
Index nhỏ, JOIN nhanh, không phân mảnh B-tree |
| Định danh công khai | public_id uuid NOT NULL DEFAULT uuidv7() |
Chặn liệt kê tuần tự (IDOR, đối thủ đếm đơn/ngày) |
| Tiền | numeric(19,4) + cột currency_code char(3) |
⚑ Không bao giờ float/real/double |
| Thời gian | timestamptz, lưu UTC |
Hiển thị Asia/Ho_Chi_Minh ở tầng ứng dụng |
| Ngày thuần | date — hạn dùng, kỳ hiệu lực |
Không có múi giờ để nhầm |
| Trạng thái | text + CHECK (... IN (...)), không dùng CREATE TYPE ... AS ENUM |
Thêm giá trị mới chỉ là sửa CHECK; enum PostgreSQL khó bỏ giá trị và gây rắc rối khi migrate |
| Boolean | boolean NOT NULL DEFAULT false — không nullable |
Ba trạng thái true/false/null luôn là bug chờ nổ |
| JSON | Luôn jsonb, không bao giờ json |
json chỉ lưu văn bản, không index được |
NOT NULL |
Mặc định. Nullable phải có lý do viết ra | |
| Xoá mềm | deleted_at timestamptz — chỉ với dữ liệu tham chiếu |
⚑ Chứng từ tài chính không xoá mềm |
| Dấu thời gian | created_at, updated_at NOT NULL DEFAULT now() |
|
| Tên ràng buộc | <bảng>_<cột>_<loại>: products_sku_uq, stock_no_oversell_chk |
Thông báo lỗi đọc hiểu được |
2.1 Extension & hàm nền
-- ══════════════════════════════════════════════════════════════════
-- 00_foundation.sql — chạy đầu tiên, trước mọi migration
-- ══════════════════════════════════════════════════════════════════
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- tìm gần đúng, chịu lỗi chính tả
CREATE EXTENSION IF NOT EXISTS unaccent; -- bỏ dấu tiếng Việt
CREATE EXTENSION IF NOT EXISTS btree_gist; -- BẮT BUỘC cho EXCLUDE có cột scalar
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS vector; -- ⏳ P6 (pgvector)
-- Bọc unaccent thành IMMUTABLE — xem §1.1
CREATE OR REPLACE FUNCTION f_unaccent(text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT
AS $$ SELECT public.unaccent('public.unaccent'::regdictionary, $1) $$;
-- Chuẩn hoá chuỗi tìm kiếm tiếng Việt: bỏ dấu + thường + gọn khoảng trắng
CREATE OR REPLACE FUNCTION f_search_key(text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT
AS $$ SELECT lower(regexp_replace(f_unaccent($1), '\s+', ' ', 'g')) $$;
-- Tự cập nhật updated_at
CREATE OR REPLACE FUNCTION f_touch_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END $$;
⚠ btree_gist là bắt buộc, không phải tuỳ chọn. Không có nó thì ràng buộc chống chồng lấn thời gian ở price_rules và campaigns (⚑ P3) sẽ báo lỗi "data type bigint has no default operator class for access method gist".
3. Sơ đồ quan hệ tổng thể
┌──────────────┐
│ categories │◄──┐ closure
└──────┬───────┘ │
│ ┌────┴──────────────┐
┌──────▼───────┐ │ category_closure│
┌──────────────►│ products │ └─────────────────┘
│ └──────┬───────┘
┌────┴─────┐ │ 1:N ┌────────────────────┐
│ brands │ ┌──────▼───────────┐ │product_translations│
└──────────┘ │ product_variants │◄─────┤ (locale, slug) │
└──┬────┬────┬─────┘ └────────────────────┘
┌─────────────┘ │ └──────────────┐
▼ ▼ ▼
┌───────────────┐ ┌──────────────┐ ┌─────────────────┐
│ price_rules │ │ stock_levels │ │ stock_batches │
│ price_lists │ │ (variant,wh) │ │ (lô + HSD) │
└───────────────┘ └──────┬───────┘ └────────┬────────┘
│ │
┌───────────▼──────────┐ ┌──────▼──────────┐
│ stock_reservations │ │ stock_movements │ ⟂ phân mảnh
└───────────┬──────────┘ └─────────────────┘
│
┌──────────┐ ┌────────────▼──────────┐ ┌──────────────┐
│ carts │──►│ checkout_sessions │──►│ orders │
│cart_items│ │ checkout_lines │ │ order_items │
└──────────┘ └───────────────────────┘ │order_addresses│
│order_status_ │
│ history │
└───┬───┬───┬───┘
┌─────────────────────────────┘ │ └──────────────┐
▼ ▼ ▼
┌───────────────────────┐ ┌────────────────┐ ┌──────────────┐
│ payment_transactions │ │ shipments │ │ invoices │
│ payment_refunds │ │ shipment_items │ │invoice_lines │
└──────────┬────────────┘ │ carrier_events │⟂ └──────────────┘
│ └────────────────┘
┌──────────▼────────────┐ ▲
│ ledger_transactions │ ┌────────┴────────┐
│ ledger_entries ⚑Y1 │ │ return_requests │
│ ledger_accounts │ │ return_items │
└───────────────────────┘ └─────────────────┘
┌──────────────┐ ┌────────────────┐ ┌──────────────────┐
│ customers │ │ campaigns │ │ loyalty_accounts │
│customer_addr │ │ coupons │ │ loyalty_txns │
│ users │ │coupon_redempt. │ └──────────────────┘
└──────────────┘ └────────────────┘
── HẠ TẦNG ── outbox_messages ⟂ · idempotency_keys · audit_logs ⟂
administrative_units · carrier_address_mappings
⟂ = phân mảnh RANGE theo tháng
4. Dữ liệu tham chiếu
4.1 Đơn vị hành chính Việt Nam — có phiên bản ⚠
Đây là bảng mà thiết kế sai sẽ tốn hàng tuần migrate về sau (doc 01 §12.2, doc 02 §3.3).
CREATE TABLE administrative_units (
code text PRIMARY KEY, -- mã Tổng cục Thống kê
parent_code text REFERENCES administrative_units(code),
level smallint NOT NULL, -- 1=tỉnh/TP · 2=huyện (lịch sử) · 3=xã/phường
name text NOT NULL,
name_with_type text NOT NULL, -- "Phường Bến Nghé"
search_key text GENERATED ALWAYS AS (f_search_key(name)) STORED,
valid_from date NOT NULL,
valid_to date, -- NULL = còn hiệu lực
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT admin_units_level_chk CHECK (level IN (1, 2, 3)),
CONSTRAINT admin_units_validity_chk CHECK (valid_to IS NULL OR valid_to > valid_from)
);
-- Chỉ tra cứu đơn vị còn hiệu lực khi khách nhập địa chỉ mới
CREATE INDEX admin_units_active_idx ON administrative_units (parent_code, level)
WHERE valid_to IS NULL;
-- Gợi ý gõ không dấu: "ba dinh" → "Ba Đình"
CREATE INDEX admin_units_search_idx ON administrative_units USING gin (search_key gin_trgm_ops);
-- ⚑ Ánh xạ mã cũ → mã mới sau sáp nhập. KHÔNG ĐƯỢC THIẾU.
CREATE TABLE administrative_unit_mappings (
old_code text NOT NULL,
new_code text NOT NULL REFERENCES administrative_units(code),
effective_from date NOT NULL,
kind text NOT NULL,
note text,
PRIMARY KEY (old_code, new_code, effective_from),
CONSTRAINT admin_map_kind_chk
CHECK (kind IN ('merged', 'renamed', 'split', 'dissolved', 'reassigned'))
);
-- Mỗi hãng vận chuyển có bộ mã riêng và cập nhật lệch thời điểm — doc 02 §12.3
CREATE TABLE carrier_address_mappings (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
carrier text NOT NULL,
our_code text NOT NULL REFERENCES administrative_units(code),
carrier_code text NOT NULL,
carrier_name text,
valid_from date NOT NULL DEFAULT CURRENT_DATE,
valid_to date,
CONSTRAINT carrier_addr_carrier_chk
CHECK (carrier IN ('ghn', 'ghtk', 'viettelpost', 'ahamove', 'jt', 'best'))
);
CREATE UNIQUE INDEX carrier_addr_uq
ON carrier_address_mappings (carrier, our_code)
WHERE valid_to IS NULL;
⚠ Ba điều bắt buộc phải nhớ về bảng này:
- Địa chỉ trong
orderslà văn bản snapshot, không phải khoá ngoại vào đây (doc 02 §3.3). - Khi danh mục hành chính đổi, chỉ thêm dòng mới + đóng
valid_todòng cũ, không bao giờUPDATEtên tại chỗ. - Bảng ánh xạ hãng vận chuyển phải cập nhật riêng cho từng hãng, vì họ chuyển đổi ở thời điểm khác nhau.
4.2 Kho & tiền tệ
CREATE TABLE warehouses (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
code text NOT NULL,
name text NOT NULL,
address_line text NOT NULL,
ward_code text REFERENCES administrative_units(code),
province_code text REFERENCES administrative_units(code),
is_sellable boolean NOT NULL DEFAULT true, -- kho hỏng/cách ly = false
priority smallint NOT NULL DEFAULT 100, -- thứ tự ưu tiên phân bổ
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT warehouses_code_uq UNIQUE (code),
CONSTRAINT warehouses_public_id_uq UNIQUE (public_id)
);
CREATE TABLE currencies (
code char(3) PRIMARY KEY,
name text NOT NULL,
decimal_places smallint NOT NULL DEFAULT 0, -- VND = 0
symbol text NOT NULL
);
INSERT INTO currencies (code, name, decimal_places, symbol)
VALUES ('VND', 'Việt Nam Đồng', 0, '₫')
ON CONFLICT DO NOTHING;
5. Catalog
CREATE TABLE brands (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
name text NOT NULL,
slug text NOT NULL,
logo_path text,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz,
CONSTRAINT brands_slug_uq UNIQUE (slug),
CONSTRAINT brands_public_id_uq UNIQUE (public_id)
);
CREATE TABLE categories (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
parent_id bigint REFERENCES categories(id),
sort_order integer NOT NULL DEFAULT 0,
status text NOT NULL DEFAULT 'active',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT categories_status_chk CHECK (status IN ('active', 'hidden')),
CONSTRAINT categories_public_id_uq UNIQUE (public_id),
CONSTRAINT categories_no_self_chk CHECK (parent_id IS NULL OR parent_id <> id)
);
-- Closure table: "mọi sản phẩm trong nhánh này" = 1 JOIN phẳng (doc 01 §8.2)
CREATE TABLE category_closure (
ancestor_id bigint NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
descendant_id bigint NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
depth smallint NOT NULL,
PRIMARY KEY (ancestor_id, descendant_id)
);
CREATE INDEX category_closure_desc_idx ON category_closure (descendant_id, depth);
CREATE TABLE category_translations (
category_id bigint NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
locale text NOT NULL,
name text NOT NULL,
slug text NOT NULL,
description text,
PRIMARY KEY (category_id, locale),
CONSTRAINT category_tr_slug_uq UNIQUE (locale, slug)
);
-- ── Sản phẩm ──────────────────────────────────────────────────────
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
brand_id bigint REFERENCES brands(id),
type text NOT NULL DEFAULT 'simple',
status text NOT NULL DEFAULT 'draft',
attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
published_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz,
CONSTRAINT products_public_id_uq UNIQUE (public_id),
CONSTRAINT products_type_chk CHECK (type IN ('simple', 'configurable', 'bundle', 'giftcard')),
CONSTRAINT products_status_chk CHECK (status IN ('draft', 'active', 'archived')),
-- ⚑ C2 (một nửa): active thì bắt buộc có published_at
CONSTRAINT products_published_chk
CHECK (status <> 'active' OR published_at IS NOT NULL)
);
CREATE TRIGGER products_touch BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION f_touch_updated_at();
-- Index bộ phận: chỉ ~vài % số dòng đang 'active' → index nhỏ hơn cả chục lần
CREATE INDEX products_active_idx ON products (published_at DESC)
WHERE status = 'active' AND deleted_at IS NULL;
CREATE INDEX products_brand_idx ON products (brand_id)
WHERE status = 'active' AND deleted_at IS NULL;
-- jsonb_path_ops nhỏ và nhanh hơn jsonb_ops khi chỉ dùng toán tử @>
CREATE INDEX products_attrs_gin ON products USING gin (attributes jsonb_path_ops);
CREATE TABLE product_translations (
product_id bigint NOT NULL REFERENCES products(id) ON DELETE CASCADE,
locale text NOT NULL,
name text NOT NULL,
slug text NOT NULL,
short_desc text,
description text,
meta_title text,
meta_desc text,
-- 'simple' (KHÔNG phải 'english') vì tiếng Việt không có bộ stem trong core;
-- f_unaccent bảo đảm biểu thức IMMUTABLE — xem §1.1
search_vec tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('simple', f_unaccent(coalesce(name, ''))), 'A') ||
setweight(to_tsvector('simple', f_unaccent(coalesce(short_desc, ''))), 'B') ||
setweight(to_tsvector('simple', f_unaccent(coalesce(description, ''))), 'C')
) STORED,
search_key text GENERATED ALWAYS AS (f_search_key(name)) STORED,
PRIMARY KEY (product_id, locale),
CONSTRAINT product_tr_slug_uq UNIQUE (locale, slug) -- ⚑ C5
);
CREATE INDEX product_tr_fts_idx ON product_translations USING gin (search_vec);
CREATE INDEX product_tr_trgm_idx ON product_translations USING gin (search_key gin_trgm_ops);
CREATE TABLE product_categories (
product_id bigint NOT NULL REFERENCES products(id) ON DELETE CASCADE,
category_id bigint NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
is_primary boolean NOT NULL DEFAULT false,
PRIMARY KEY (product_id, category_id)
);
CREATE INDEX product_categories_cat_idx ON product_categories (category_id);
-- Mỗi sản phẩm chỉ có đúng 1 danh mục chính (dùng cho breadcrumb, canonical URL)
CREATE UNIQUE INDEX product_primary_category_uq
ON product_categories (product_id) WHERE is_primary;
-- ── Biến thể: THỨ THỰC SỰ BÁN ĐƯỢC (doc 02 §3 bảng thuật ngữ) ─────
CREATE TABLE product_variants (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
product_id bigint NOT NULL REFERENCES products(id) ON DELETE CASCADE,
sku text NOT NULL,
barcode text,
options jsonb NOT NULL DEFAULT '{}'::jsonb, -- {"shade":"01","size":"3g"}
weight_gram integer NOT NULL DEFAULT 0,
length_mm integer,
width_mm integer,
height_mm integer,
-- Thuộc tính "nâng lên cột" vì dùng để lọc/sắp xếp (doc 01 §8.2)
volume_ml numeric(10,2),
shade_code text,
-- Mỹ phẩm: bắt buộc theo lô (doc 02 §7.4)
requires_batch_tracking boolean NOT NULL DEFAULT true,
shelf_life_days integer,
min_shelf_life_days integer NOT NULL DEFAULT 120, -- ⚑ I8
status text NOT NULL DEFAULT 'active',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT variants_sku_uq UNIQUE (sku), -- ⚑ C1
CONSTRAINT variants_public_id_uq UNIQUE (public_id),
CONSTRAINT variants_status_chk CHECK (status IN ('active', 'archived')),
CONSTRAINT variants_weight_chk CHECK (weight_gram >= 0),
CONSTRAINT variants_shelf_chk CHECK (min_shelf_life_days >= 0)
);
CREATE INDEX variants_product_idx ON product_variants (product_id) WHERE status = 'active';
CREATE INDEX variants_options_gin ON product_variants USING gin (options jsonb_path_ops);
-- Registry điều khiển admin UI + validation phần jsonb
CREATE TABLE attribute_definitions (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code text NOT NULL,
label_vi text NOT NULL,
label_en text,
data_type text NOT NULL,
allowed_values jsonb,
unit text,
is_filterable boolean NOT NULL DEFAULT false,
is_required boolean NOT NULL DEFAULT false,
applies_to text NOT NULL DEFAULT 'product',
sort_order integer NOT NULL DEFAULT 0,
CONSTRAINT attr_def_code_uq UNIQUE (code),
CONSTRAINT attr_def_type_chk CHECK (data_type IN ('string','number','bool','enum','date')),
CONSTRAINT attr_def_scope_chk CHECK (applies_to IN ('product','variant'))
);
CREATE TABLE media (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
disk text NOT NULL DEFAULT 's3',
path text NOT NULL,
mime_type text NOT NULL,
size_bytes bigint NOT NULL,
width integer,
height integer,
blurhash text,
alt_vi text,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT media_public_id_uq UNIQUE (public_id)
);
CREATE TABLE product_media (
product_id bigint NOT NULL REFERENCES products(id) ON DELETE CASCADE,
media_id bigint NOT NULL REFERENCES media(id),
variant_id bigint REFERENCES product_variants(id) ON DELETE CASCADE,
sort_order smallint NOT NULL DEFAULT 0,
role text NOT NULL DEFAULT 'gallery',
PRIMARY KEY (product_id, media_id),
CONSTRAINT product_media_role_chk CHECK (role IN ('gallery','thumbnail','hero','swatch'))
);
6. Pricing
CREATE TABLE customer_groups (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code text NOT NULL,
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT customer_groups_code_uq UNIQUE (code)
);
CREATE TABLE price_lists (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
code text NOT NULL,
name text NOT NULL,
currency_code char(3) NOT NULL REFERENCES currencies(code),
customer_group_id bigint REFERENCES customer_groups(id), -- NULL = mọi khách
priority smallint NOT NULL DEFAULT 100,
valid_from timestamptz NOT NULL DEFAULT now(),
valid_to timestamptz,
status text NOT NULL DEFAULT 'active',
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT price_lists_code_uq UNIQUE (code),
CONSTRAINT price_lists_status_chk CHECK (status IN ('draft','active','expired')),
CONSTRAINT price_lists_range_chk CHECK (valid_to IS NULL OR valid_to > valid_from)
);
CREATE TABLE price_rules (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
price_list_id bigint NOT NULL REFERENCES price_lists(id) ON DELETE CASCADE,
variant_id bigint NOT NULL REFERENCES product_variants(id) ON DELETE CASCADE,
min_quantity integer NOT NULL DEFAULT 1,
list_price numeric(19,4) NOT NULL,
sale_price numeric(19,4) NOT NULL,
valid_from timestamptz NOT NULL DEFAULT now(),
valid_to timestamptz,
-- Cột dẫn xuất phục vụ ràng buộc chống chồng lấn bên dưới
validity tstzrange GENERATED ALWAYS AS (tstzrange(valid_from, valid_to, '[)')) STORED,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT price_rules_positive_chk CHECK (list_price >= 0 AND sale_price >= 0), -- ⚑ P2
CONSTRAINT price_rules_sale_chk CHECK (sale_price <= list_price), -- ⚑ P1
CONSTRAINT price_rules_minqty_chk CHECK (min_quantity >= 1), -- ⚑ P4
CONSTRAINT price_rules_range_chk CHECK (valid_to IS NULL OR valid_to > valid_from),
-- ⚑ P3: CSDL tự chặn hai quy tắc giá chồng lấn thời gian cho cùng
-- (bảng giá, biến thể, bậc số lượng). Cần extension btree_gist.
CONSTRAINT price_rules_no_overlap
EXCLUDE USING gist (
price_list_id WITH =,
variant_id WITH =,
min_quantity WITH =,
validity WITH &&
)
);
CREATE INDEX price_rules_lookup_idx
ON price_rules (variant_id, price_list_id, min_quantity DESC);
⚠ EXCLUDE ... WITH = trên bigint/integer chỉ hoạt động khi đã có btree_gist. Đây là lý do extension đó nằm trong 00_foundation.sql.
7. Promotion
CREATE TABLE campaigns (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
name text NOT NULL,
description text,
priority smallint NOT NULL DEFAULT 100,
stackable boolean NOT NULL DEFAULT false, -- ⚑ M4
conditions jsonb NOT NULL DEFAULT '[]'::jsonb, -- doc 02 §6.1
effects jsonb NOT NULL DEFAULT '[]'::jsonb,
valid_from timestamptz NOT NULL,
valid_to timestamptz NOT NULL,
status text NOT NULL DEFAULT 'draft',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT campaigns_public_id_uq UNIQUE (public_id),
CONSTRAINT campaigns_status_chk CHECK (status IN ('draft','active','paused','expired')),
CONSTRAINT campaigns_range_chk CHECK (valid_to > valid_from)
);
CREATE INDEX campaigns_active_idx ON campaigns (priority DESC, valid_from)
WHERE status = 'active';
-- Tách khỏi campaigns vì redemption_count là điểm nóng ghi (doc 02 §6.1)
CREATE TABLE coupons (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
campaign_id bigint NOT NULL REFERENCES campaigns(id),
code text NOT NULL,
max_redemptions integer, -- NULL = không giới hạn
max_redemptions_per_customer integer NOT NULL DEFAULT 1,
redemption_count integer NOT NULL DEFAULT 0,
reserved_count integer NOT NULL DEFAULT 0, -- giữ chỗ lúc checkout
valid_from timestamptz NOT NULL,
valid_to timestamptz NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT coupons_code_uq UNIQUE (code),
-- ⚑ M2: lưới an toàn cuối cùng ở tầng CSDL
CONSTRAINT coupons_limit_chk
CHECK (max_redemptions IS NULL
OR redemption_count + reserved_count <= max_redemptions),
CONSTRAINT coupons_count_chk CHECK (redemption_count >= 0 AND reserved_count >= 0)
);
CREATE TABLE coupon_redemptions (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
coupon_id bigint NOT NULL REFERENCES coupons(id),
customer_id bigint, -- NULL = khách vãng lai
order_id bigint,
reference text NOT NULL, -- checkout_session_id hoặc order_id
status text NOT NULL DEFAULT 'reserved',
discount_amount numeric(19,4) NOT NULL,
redeemed_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT coupon_redemptions_status_chk
CHECK (status IN ('reserved','consumed','released')),
-- ⚑ M5 (idempotency): một tham chiếu chỉ tạo một lần giữ chỗ
CONSTRAINT coupon_redemptions_ref_uq UNIQUE (coupon_id, reference)
);
-- ⚑ M3: giới hạn lượt/khách. Index này cưỡng chế ĐÚNG trường hợp
-- max_redemptions_per_customer = 1 (mặc định, và là 95% thực tế).
CREATE UNIQUE INDEX coupon_redemptions_per_customer_uq
ON coupon_redemptions (coupon_id, customer_id)
WHERE customer_id IS NOT NULL AND status = 'consumed';
⚠ Giới hạn của ⚑ M3 ở tầng CSDL — nói rõ để không ai tưởng nhầm là đã kín:
index bộ phận trên chỉ cưỡng chế được một lượt/khách. Với max_redemptions_per_customer > 1, PostgreSQL không có cách khai báo thuần nào để đếm ngưỡng N (CHECK không được chứa truy vấn con, và ngưỡng nằm ở bảng coupons khác). Trường hợp đó phải kiểm ở tầng ứng dụng bằng câu lệnh đếm nguyên tử trong cùng transaction:
-- Chèn lượt dùng, chỉ khi khách chưa đạt ngưỡng
INSERT INTO coupon_redemptions (coupon_id, customer_id, reference, status, discount_amount)
SELECT $1, $2, $3, 'reserved', $4
WHERE (
SELECT count(*) FROM coupon_redemptions
WHERE coupon_id = $1 AND customer_id = $2 AND status IN ('reserved','consumed')
) < (SELECT max_redemptions_per_customer FROM coupons WHERE id = $1)
ON CONFLICT (coupon_id, reference) DO NOTHING
RETURNING id;
-- 0 dòng ⇒ đã đạt ngưỡng HOẶC đã giữ chỗ trước đó (idempotent)
⚠ Câu này vẫn có cửa sổ race ở mức READ COMMITTED (hai transaction cùng đọc count(*) trước khi bên kia chèn). Với N > 1, hoặc chấp nhận rủi ro vượt ngưỡng đúng 1 lượt, hoặc dùng SELECT ... FOR UPDATE trên dòng coupons để tuần tự hoá. Đề xuất: giữ max_redemptions_per_customer = 1 cho mọi mã public — vừa đơn giản vừa được CSDL bảo đảm tuyệt đối.
⚠ Vì sao có cả redemption_count lẫn reserved_count: mã được giữ chỗ lúc checkout và chỉ tiêu thụ khi đơn confirmed (doc 02 §6.3). Không tách hai bộ đếm thì một mã 100 lượt sẽ bị đốt sạch bởi các phiên checkout không bao giờ thanh toán.
8. Inventory — nhóm bảng quan trọng nhất
CREATE TABLE stock_levels (
variant_id bigint NOT NULL REFERENCES product_variants(id) ON DELETE CASCADE,
warehouse_id bigint NOT NULL REFERENCES warehouses(id),
on_hand integer NOT NULL DEFAULT 0,
reserved integer NOT NULL DEFAULT 0,
available integer GENERATED ALWAYS AS (on_hand - reserved) STORED,
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (variant_id, warehouse_id),
-- ⚑ I1, I2, I3 — LƯỚI AN TOÀN CUỐI CÙNG.
-- Dù mọi tầng code phía trên sai, CSDL vẫn không cho phép oversell.
CONSTRAINT stock_no_oversell_chk
CHECK (reserved >= 0 AND on_hand >= 0 AND reserved <= on_hand)
);
-- Trang danh mục cần "còn hàng không" — index bộ phận trên tập nhỏ
CREATE INDEX stock_levels_available_idx ON stock_levels (variant_id)
WHERE on_hand > reserved;
CREATE TABLE stock_reservations (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
variant_id bigint NOT NULL,
warehouse_id bigint NOT NULL,
quantity integer NOT NULL,
reference_type text NOT NULL,
reference_id text NOT NULL,
status text NOT NULL DEFAULT 'held',
expires_at timestamptz NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
released_at timestamptz,
FOREIGN KEY (variant_id, warehouse_id)
REFERENCES stock_levels (variant_id, warehouse_id),
CONSTRAINT reservations_qty_chk CHECK (quantity > 0),
CONSTRAINT reservations_status_chk CHECK (status IN ('held','consumed','released','expired')),
CONSTRAINT reservations_ref_chk CHECK (reference_type IN ('checkout_session','order','manual')),
-- ⚑ I5: webhook/retry gọi lại không tạo giữ chỗ trùng
CONSTRAINT reservations_ref_uq UNIQUE (reference_type, reference_id, variant_id)
);
-- Job dọn chạy mỗi phút chỉ quét index bộ phận nhỏ này (⚑ I6)
CREATE INDEX reservations_sweep_idx ON stock_reservations (expires_at)
WHERE status = 'held';
CREATE INDEX reservations_ref_idx ON stock_reservations (reference_type, reference_id);
-- ── Lô hàng + hạn dùng (mỹ phẩm) ──────────────────────────────────
CREATE TABLE stock_batches (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
variant_id bigint NOT NULL REFERENCES product_variants(id),
warehouse_id bigint NOT NULL REFERENCES warehouses(id),
batch_code text NOT NULL,
manufactured_at date,
expires_at date NOT NULL,
quantity integer NOT NULL DEFAULT 0,
unit_cost numeric(19,4) NOT NULL, -- ⚑ giá vốn: nguồn duy nhất từ GoodsReceipt
currency_code char(3) NOT NULL DEFAULT 'VND' REFERENCES currencies(code),
received_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT batches_uq UNIQUE (variant_id, warehouse_id, batch_code),
CONSTRAINT batches_qty_chk CHECK (quantity >= 0),
CONSTRAINT batches_cost_chk CHECK (unit_cost >= 0),
CONSTRAINT batches_date_chk CHECK (manufactured_at IS NULL OR expires_at > manufactured_at)
);
-- FEFO: lô hết hạn SỚM NHẤT xuất trước. Index bộ phận bỏ qua lô đã hết.
CREATE INDEX batches_fefo_idx ON stock_batches (variant_id, warehouse_id, expires_at)
WHERE quantity > 0;
-- Cảnh báo hàng cận date
CREATE INDEX batches_expiring_idx ON stock_batches (expires_at)
WHERE quantity > 0;
-- ── Sổ chuyển động kho: APPEND ONLY, phân mảnh theo tháng ─────────
CREATE TABLE stock_movements (
id bigint GENERATED ALWAYS AS IDENTITY,
variant_id bigint NOT NULL,
warehouse_id bigint NOT NULL,
batch_id bigint,
quantity integer NOT NULL, -- dương = nhập, âm = xuất
reason text NOT NULL,
reference_type text,
reference_id text,
unit_cost numeric(19,4),
note text,
created_by bigint,
occurred_at timestamptz NOT NULL DEFAULT now(),
-- 🔧 §1.2: khoá chính BẮT BUỘC chứa cột phân mảnh
PRIMARY KEY (id, occurred_at),
CONSTRAINT movements_qty_chk CHECK (quantity <> 0),
CONSTRAINT movements_reason_chk CHECK (reason IN (
'purchase','sale','return','adjustment','transfer_in','transfer_out',
'damage','expiry','stocktake'
))
) PARTITION BY RANGE (occurred_at);
-- Phân mảnh: tạo trước bằng pg_partman ở P7; ở P0 tạo tay
CREATE TABLE stock_movements_2026_08 PARTITION OF stock_movements
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE stock_movements_2026_09 PARTITION OF stock_movements
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE stock_movements_default PARTITION OF stock_movements DEFAULT;
CREATE INDEX movements_variant_idx ON stock_movements (variant_id, occurred_at DESC);
CREATE INDEX movements_ref_idx ON stock_movements (reference_type, reference_id);
8.1 Câu lệnh giữ chỗ nguyên tử — trái tim của hệ thống
-- ⚑ I3. Toàn bộ tính đúng đắn chống oversell nằm trong MỘT câu lệnh này.
-- Mệnh đề WHERE được đánh giá LẠI sau khi lấy khoá hàng (EvalPlanQual),
-- nên ở mức READ COMMITTED vẫn không có cửa sổ race.
UPDATE stock_levels
SET reserved = reserved + $1,
updated_at = now()
WHERE variant_id = $2
AND warehouse_id = $3
AND on_hand - reserved >= $1
RETURNING on_hand - reserved AS available_after;
-- 0 dòng bị ảnh hưởng ⇒ hết hàng ⇒ PHP ném InsufficientStock
-- Job dọn giữ chỗ quá hạn (⚑ I6, chạy mỗi phút).
-- SKIP LOCKED cho phép nhiều worker chạy song song không giẫm chân nhau.
WITH expired AS (
SELECT id, variant_id, warehouse_id, quantity
FROM stock_reservations
WHERE status = 'held'
AND expires_at < now()
ORDER BY expires_at
FOR UPDATE SKIP LOCKED
LIMIT 500
), released AS (
UPDATE stock_reservations r
SET status = 'expired', released_at = now()
FROM expired e
WHERE r.id = e.id
RETURNING e.variant_id, e.warehouse_id, e.quantity
)
UPDATE stock_levels sl
SET reserved = sl.reserved - r.quantity,
updated_at = now()
FROM released r
WHERE sl.variant_id = r.variant_id
AND sl.warehouse_id = r.warehouse_id;
8.2 Truy vấn đối chiếu (chạy hằng đêm)
-- ⚑ I4: tổng chuyển động phải khớp on_hand
SELECT sl.variant_id, sl.warehouse_id, sl.on_hand,
COALESCE(SUM(sm.quantity), 0) AS movement_sum
FROM stock_levels sl
LEFT JOIN stock_movements sm
ON sm.variant_id = sl.variant_id
AND sm.warehouse_id = sl.warehouse_id
GROUP BY sl.variant_id, sl.warehouse_id, sl.on_hand
HAVING sl.on_hand <> COALESCE(SUM(sm.quantity), 0);
-- ⚑ I7: tổng lô phải khớp on_hand (chỉ variant theo lô)
SELECT sl.variant_id, sl.warehouse_id, sl.on_hand,
COALESCE(SUM(b.quantity), 0) AS batch_sum
FROM stock_levels sl
JOIN product_variants v ON v.id = sl.variant_id AND v.requires_batch_tracking
LEFT JOIN stock_batches b
ON b.variant_id = sl.variant_id AND b.warehouse_id = sl.warehouse_id
GROUP BY sl.variant_id, sl.warehouse_id, sl.on_hand
HAVING sl.on_hand <> COALESCE(SUM(b.quantity), 0);
Cả hai truy vấn trả về 0 dòng là điều kiện bình thường. Có dòng ⇒ cảnh báo P0.
9. Cart & Checkout
CREATE TABLE carts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
customer_id bigint,
session_key text, -- khách vãng lai
currency_code char(3) NOT NULL DEFAULT 'VND' REFERENCES currencies(code),
status text NOT NULL DEFAULT 'active',
expires_at timestamptz NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT carts_public_id_uq UNIQUE (public_id),
CONSTRAINT carts_status_chk CHECK (status IN ('active','converted','abandoned','merged')),
CONSTRAINT carts_owner_chk CHECK (customer_id IS NOT NULL OR session_key IS NOT NULL)
);
-- ⚑ K1: mỗi khách đăng nhập có tối đa 1 giỏ active
CREATE UNIQUE INDEX carts_one_active_per_customer_uq
ON carts (customer_id) WHERE status = 'active' AND customer_id IS NOT NULL;
CREATE INDEX carts_abandoned_idx ON carts (updated_at)
WHERE status = 'active';
CREATE TABLE cart_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
cart_id bigint NOT NULL REFERENCES carts(id) ON DELETE CASCADE,
variant_id bigint NOT NULL REFERENCES product_variants(id),
quantity integer NOT NULL,
added_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT cart_items_qty_chk CHECK (quantity >= 1), -- ⚑ K3
CONSTRAINT cart_items_uq UNIQUE (cart_id, variant_id) -- ⚑ K2
);
-- ── Checkout: nơi GIÁ ĐÓNG BĂNG (doc 02 §9) ───────────────────────
CREATE TABLE checkout_sessions (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
cart_id bigint NOT NULL REFERENCES carts(id),
customer_id bigint,
shipping_address jsonb, -- AddressSnapshot
billing_address jsonb,
shipping_method text,
shipping_fee numeric(19,4) NOT NULL DEFAULT 0,
payment_method text,
subtotal numeric(19,4) NOT NULL DEFAULT 0,
discount_total numeric(19,4) NOT NULL DEFAULT 0,
tax_total numeric(19,4) NOT NULL DEFAULT 0,
grand_total numeric(19,4) NOT NULL DEFAULT 0,
currency_code char(3) NOT NULL DEFAULT 'VND' REFERENCES currencies(code),
price_snapshot_hash text, -- ⚑ H3
coupon_codes text[] NOT NULL DEFAULT '{}',
status text NOT NULL DEFAULT 'draft',
expires_at timestamptz NOT NULL, -- ⚑ H2: +15 phút
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT checkout_public_id_uq UNIQUE (public_id),
CONSTRAINT checkout_status_chk CHECK (status IN
('draft','pricing_locked','reserved','completed','expired','failed')),
CONSTRAINT checkout_total_chk CHECK (grand_total >= 0) -- ⚑ H4
);
CREATE INDEX checkout_expiry_idx ON checkout_sessions (expires_at)
WHERE status IN ('pricing_locked','reserved');
CREATE TABLE checkout_lines (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
checkout_session_id bigint NOT NULL REFERENCES checkout_sessions(id) ON DELETE CASCADE,
variant_id bigint NOT NULL REFERENCES product_variants(id),
sku text NOT NULL,
name text NOT NULL,
quantity integer NOT NULL,
list_price numeric(19,4) NOT NULL,
unit_price numeric(19,4) NOT NULL,
discount_total numeric(19,4) NOT NULL DEFAULT 0,
tax_rate numeric(5,4) NOT NULL DEFAULT 0,
tax_total numeric(19,4) NOT NULL DEFAULT 0,
line_total numeric(19,4) NOT NULL,
applied_promotions jsonb NOT NULL DEFAULT '[]'::jsonb,
CONSTRAINT checkout_lines_qty_chk CHECK (quantity >= 1),
CONSTRAINT checkout_lines_uq UNIQUE (checkout_session_id, variant_id)
);
10. Order
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
order_number text NOT NULL, -- ⚑ O8: "HS26081300123"
customer_id bigint,
customer_snapshot jsonb NOT NULL, -- tên/email/phone lúc đặt
-- BA TRỤC TRẠNG THÁI ĐỘC LẬP (doc 02 §10.1)
status text NOT NULL DEFAULT 'draft',
payment_status text NOT NULL DEFAULT 'unpaid',
fulfilment_status text NOT NULL DEFAULT 'unfulfilled',
subtotal numeric(19,4) NOT NULL,
discount_total numeric(19,4) NOT NULL DEFAULT 0,
shipping_fee numeric(19,4) NOT NULL DEFAULT 0,
tax_total numeric(19,4) NOT NULL DEFAULT 0,
grand_total numeric(19,4) NOT NULL,
paid_amount numeric(19,4) NOT NULL DEFAULT 0,
refunded_amount numeric(19,4) NOT NULL DEFAULT 0,
currency_code char(3) NOT NULL DEFAULT 'VND' REFERENCES currencies(code),
payment_method text NOT NULL,
shipping_method text,
checkout_session_id bigint REFERENCES checkout_sessions(id),
parent_order_id bigint REFERENCES orders(id), -- tách đơn (doc 02 §10.6)
placed_at timestamptz NOT NULL DEFAULT now(),
confirmed_at timestamptz,
shipped_at timestamptz,
delivered_at timestamptz,
completed_at timestamptz,
cancelled_at timestamptz,
cancellation_reason text,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT orders_number_uq UNIQUE (order_number), -- ⚑ O8
CONSTRAINT orders_public_id_uq UNIQUE (public_id),
CONSTRAINT orders_status_chk CHECK (status IN (
'draft','pending_payment','confirmed','processing','shipped',
'delivered','completed','cancelled','returning','returned')),
CONSTRAINT orders_payment_status_chk CHECK (payment_status IN (
'unpaid','authorized','partially_paid','paid','partially_refunded','refunded')),
CONSTRAINT orders_fulfilment_status_chk CHECK (fulfilment_status IN (
'unfulfilled','partially_fulfilled','fulfilled','cancelled')),
-- ⚑ O1
CONSTRAINT orders_total_chk CHECK (grand_total >= 0),
CONSTRAINT orders_total_math_chk
CHECK (grand_total = subtotal - discount_total + shipping_fee + tax_total),
-- ⚑ O4
CONSTRAINT orders_refund_chk CHECK (refunded_amount <= paid_amount),
CONSTRAINT orders_paid_chk CHECK (paid_amount >= 0 AND refunded_amount >= 0)
);
-- Index bộ phận thay cho phân mảnh (§1.3): đơn đang mở chỉ chiếm vài phần nghìn
CREATE INDEX orders_open_idx ON orders (placed_at DESC)
WHERE status IN ('pending_payment','confirmed','processing','shipped');
CREATE INDEX orders_pending_payment_idx ON orders (placed_at)
WHERE status = 'pending_payment'; -- job tự huỷ sau 30′
CREATE INDEX orders_customer_idx ON orders (customer_id, placed_at DESC)
WHERE customer_id IS NOT NULL;
-- BRIN: bảng chỉ ghi thêm theo thời gian → index nhỏ hơn B-tree hàng trăm lần
CREATE INDEX orders_placed_brin ON orders USING brin (placed_at);
CREATE TABLE order_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id),
variant_id bigint NOT NULL REFERENCES product_variants(id),
-- ⚑ O2 SNAPSHOT — không bao giờ đọc lại từ Catalog/Pricing
sku text NOT NULL,
name text NOT NULL,
variant_options jsonb NOT NULL DEFAULT '{}'::jsonb,
list_price numeric(19,4) NOT NULL,
unit_price numeric(19,4) NOT NULL,
discount_total numeric(19,4) NOT NULL DEFAULT 0, -- đã phân bổ về dòng (doc 02 §6.4)
tax_rate numeric(5,4) NOT NULL DEFAULT 0,
tax_total numeric(19,4) NOT NULL DEFAULT 0,
line_total numeric(19,4) NOT NULL,
unit_cost numeric(19,4), -- giá vốn lô lúc xuất → lãi gộp
quantity integer NOT NULL,
quantity_fulfilled integer NOT NULL DEFAULT 0,
quantity_returned integer NOT NULL DEFAULT 0,
applied_promotions jsonb NOT NULL DEFAULT '[]'::jsonb,
CONSTRAINT order_items_qty_chk CHECK (quantity > 0),
CONSTRAINT order_items_fulfil_chk CHECK (quantity_fulfilled BETWEEN 0 AND quantity), -- ⚑ O5
CONSTRAINT order_items_return_chk CHECK (quantity_returned BETWEEN 0 AND quantity_fulfilled), -- ⚑ O6
CONSTRAINT order_items_price_chk CHECK (unit_price >= 0 AND line_total >= 0)
);
CREATE INDEX order_items_order_idx ON order_items (order_id);
CREATE INDEX order_items_variant_idx ON order_items (variant_id);
CREATE TABLE order_addresses (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
type text NOT NULL,
-- ⚑ SNAPSHOT VĂN BẢN, không FK sang administrative_units (doc 02 §3.3)
recipient_name text NOT NULL,
phone text NOT NULL,
line1 text NOT NULL,
ward text,
district text,
province text NOT NULL,
country_code char(2) NOT NULL DEFAULT 'VN',
ward_code text, -- mã tại thời điểm chụp, chỉ để tra cứu
province_code text,
note text,
CONSTRAINT order_addresses_type_chk CHECK (type IN ('shipping','billing')),
CONSTRAINT order_addresses_uq UNIQUE (order_id, type)
);
CREATE TABLE order_status_history (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
axis text NOT NULL, -- status | payment_status | fulfilment_status
from_value text,
to_value text NOT NULL,
reason text,
actor_type text NOT NULL,
actor_id bigint,
occurred_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT order_history_axis_chk CHECK (axis IN ('status','payment_status','fulfilment_status')),
CONSTRAINT order_history_actor_chk CHECK (actor_type IN ('customer','admin','system','webhook'))
);
CREATE INDEX order_history_order_idx ON order_status_history (order_id, occurred_at);
⚠ orders_total_math_chk là một ràng buộc rất mạnh. Nó biến ⚑ O1 thành bất khả xâm phạm, nhưng cũng có nghĩa mọi thao tác cập nhật tiền phải cập nhật toàn bộ các cột liên quan cùng lúc. Đó là điều đúng nên làm — nếu một migration nào đó thấy vướng ràng buộc này, gần như chắc chắn migration đó đang sai.
11. Payment — sổ kế toán kép
CREATE TABLE payment_transactions (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
order_id bigint NOT NULL REFERENCES orders(id),
gateway text NOT NULL,
gateway_transaction_id text,
amount numeric(19,4) NOT NULL,
currency_code char(3) NOT NULL DEFAULT 'VND' REFERENCES currencies(code),
status text NOT NULL DEFAULT 'pending',
idempotency_key text NOT NULL, -- ⚑ Y2
raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb, -- ⚑ Y7 lưu vĩnh viễn
failure_code text,
failure_message text,
authorized_at timestamptz,
captured_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT payment_txn_idem_uq UNIQUE (idempotency_key), -- ⚑ Y2
CONSTRAINT payment_txn_public_uq UNIQUE (public_id),
CONSTRAINT payment_txn_gateway_chk CHECK (gateway IN
('vnpay','momo','zalopay','cod','bank_transfer','stripe')),
CONSTRAINT payment_txn_status_chk CHECK (status IN
('pending','authorized','captured','failed','voided','refunded')),
CONSTRAINT payment_txn_amount_chk CHECK (amount > 0)
);
CREATE INDEX payment_txn_order_idx ON payment_transactions (order_id);
CREATE INDEX payment_txn_gateway_idx ON payment_transactions (gateway, gateway_transaction_id);
CREATE INDEX payment_txn_pending_idx ON payment_transactions (created_at)
WHERE status IN ('pending','authorized');
CREATE TABLE payment_refunds (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payment_transaction_id bigint NOT NULL REFERENCES payment_transactions(id),
order_id bigint NOT NULL REFERENCES orders(id),
amount numeric(19,4) NOT NULL,
reason text NOT NULL,
status text NOT NULL DEFAULT 'pending',
gateway_refund_id text,
idempotency_key text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT refunds_idem_uq UNIQUE (idempotency_key),
CONSTRAINT refunds_amount_chk CHECK (amount > 0),
CONSTRAINT refunds_status_chk CHECK (status IN ('pending','succeeded','failed'))
);
-- ── Sổ cái kép (doc 01 §8.7, doc 02 §11) ──────────────────────────
CREATE TABLE ledger_accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code text NOT NULL,
name text NOT NULL,
type text NOT NULL,
currency_code char(3) NOT NULL DEFAULT 'VND' REFERENCES currencies(code),
CONSTRAINT ledger_accounts_code_uq UNIQUE (code),
CONSTRAINT ledger_accounts_type_chk
CHECK (type IN ('asset','liability','equity','revenue','expense'))
);
INSERT INTO ledger_accounts (code, name, type) VALUES
('cash.vnpay', 'Tiền tại VNPay', 'asset'),
('cash.momo', 'Tiền tại MoMo', 'asset'),
('cash.zalopay', 'Tiền tại ZaloPay', 'asset'),
('cash.bank', 'Tiền gửi ngân hàng', 'asset'),
('ar.cod.ghn', 'Phải thu COD - GHN', 'asset'),
('ar.cod.ghtk', 'Phải thu COD - GHTK', 'asset'),
('asset.inventory', 'Hàng tồn kho', 'asset'),
('revenue.sales', 'Doanh thu bán hàng', 'revenue'),
('revenue.shipping', 'Doanh thu phí vận chuyển', 'revenue'),
('contra.refunds', 'Giảm trừ doanh thu - hoàn tiền','revenue'),
('liability.tax_payable', 'Thuế GTGT phải nộp', 'liability'),
('liability.giftcard', 'Thẻ quà tặng chưa sử dụng', 'liability'),
('liability.loyalty_points','Điểm thưởng chưa quy đổi', 'liability'),
('expense.payment_fee', 'Phí cổng thanh toán', 'expense'),
('expense.shipping_cost', 'Chi phí vận chuyển', 'expense'),
('expense.cogs', 'Giá vốn hàng bán', 'expense')
ON CONFLICT (code) DO NOTHING;
CREATE TABLE ledger_transactions (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT uuidv7(),
description text NOT NULL,
reference_type text,
reference_id text,
occurred_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT ledger_txn_public_uq UNIQUE (public_id)
);
CREATE INDEX ledger_txn_ref_idx ON ledger_transactions (reference_type, reference_id);
CREATE INDEX ledger_txn_time_brin ON ledger_transactions USING brin (occurred_at);
CREATE TABLE ledger_entries (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
transaction_id bigint NOT NULL REFERENCES ledger_transactions(id),
account_id bigint NOT NULL REFERENCES ledger_accounts(id),
amount numeric(19,4) NOT NULL, -- dương = Nợ, âm = Có
memo text,
CONSTRAINT ledger_entries_nonzero_chk CHECK (amount <> 0)
);
CREATE INDEX ledger_entries_txn_idx ON ledger_entries (transaction_id);
CREATE INDEX ledger_entries_acct_idx ON ledger_entries (account_id, id);
-- ⚑ Y1: mỗi giao dịch PHẢI cân bằng về 0. Kiểm ở thời điểm COMMIT
-- nên vẫn chèn được từng dòng một trong transaction.
CREATE OR REPLACE FUNCTION f_assert_ledger_balanced()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE s numeric;
BEGIN
SELECT COALESCE(SUM(amount), 0) INTO s
FROM ledger_entries
WHERE transaction_id = NEW.transaction_id;
IF s <> 0 THEN
RAISE EXCEPTION 'Bút toán không cân bằng: transaction_id=% tổng=%',
NEW.transaction_id, s;
END IF;
RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER ledger_balanced_trg
AFTER INSERT ON ledger_entries
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION f_assert_ledger_balanced();
-- ⚑ Y5: sổ cái APPEND-ONLY — chặn ở tầng CSDL, không dựa vào kỷ luật lập trình
CREATE OR REPLACE FUNCTION f_block_ledger_mutation()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE EXCEPTION 'ledger_entries là append-only. Sửa sai bằng bút toán đảo.';
END $$;
CREATE TRIGGER ledger_no_update_trg
BEFORE UPDATE OR DELETE ON ledger_entries
FOR EACH ROW EXECUTE FUNCTION f_block_ledger_mutation();
-- Số dư = SUM, KHÔNG lưu rời (doc 02 §16 nguyên lý L1)
CREATE VIEW v_ledger_balances AS
SELECT a.code, a.name, a.type,
COALESCE(SUM(e.amount), 0) AS balance
FROM ledger_accounts a
LEFT JOIN ledger_entries e ON e.account_id = a.id
GROUP BY a.id, a.code, a.name, a.type;
All Rights Reserved
