Skip to content

catalog / product ​

Trang sinh tự động

Sinh từ ad83b0aea — chạy 820 migration lên PostgreSQL 17 rồi đọc pg_catalog. Mô tả bảng và cột lấy từ COMMENT ON. Không sửa tay: lần chạy docgen sau sẽ ghi đè.

13 bảng. Quay lại catalog.

products ​

P2-02. Standard products and combos. Immutable type, editable unique SKU, Draft/On sale/Discontinued, soft delete (is_deleted, final), leaf category. PERM_GATED catalog.read/write (matrix CAT-01/02).

public.products · 32 cột · 13 check · 1 unique · 8 index · 3 policy · 6 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
product_typetextNOT NULL
skutextNOT NULL
sku_keytext
nametext
category_iduuidFK → categories
brand_iduuidFK → brands
tagtext
barcodetext
barcode_keytext
unittext
lengthnumeric
widthnumeric
heightnumeric
dimension_unittext
weightnumeric
weight_unittext
requires_installationbooleanNOT NULL
descriptiontext
statustextNOT NULL
created_byuuidNOT NULLFK → accounts
updated_byuuidNOT NULLFK → accounts
deleted_attimestamptz
deleted_byuuidFK → accounts
is_deletedboolean
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL
unit_iduuidFK → product_unitsU12-P2-16. Canonical unit (ADD-SP-01). NULL = no unit or an unmapped legacy text. Display label = coalesce(product_units.name, products.unit); products.unit is kept for compatibility, never dropped here.
short_nametextPSW-P1-03 (Word SP-US-02). Tên rút gọn, optional, trimmed 1..30 characters; null = none.
short_descriptiontextPSW-P1-03 (Word SP-US-02). Mô tả ngắn, optional, trimmed 1..100 characters; null = none.
is_genuinebooleanNOT NULLPSW-P1-03 (Word SP-US-02). Hàng chính hãng toggle, default false (Tắt).

Khoá chính: id

Trỏ ra (6):

  • brand_id → brands · ON DELETE RESTRICT
  • category_id → categories · ON DELETE RESTRICT
  • created_by → accounts · ON DELETE RESTRICT
  • deleted_by → accounts · ON DELETE RESTRICT
  • unit_id → product_units · ON DELETE RESTRICT
  • updated_by → accounts · ON DELETE RESTRICT

Được trỏ tới (9): campaign_products, combo_components, combo_components, content_versions, order_lines, product_accessories, product_attribute_values, product_change_events, product_media

product_units ​

U12-P2-16. Canonical product units (ADD-SP-01). Soft delete, version, name_key unique among live rows. PERM_GATED catalog.read/write (matrix CAT-01/02, G-UNIT).

public.product_units · 15 cột · 8 check · 0 unique · 3 index · 3 policy · 6 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
nametextNOT NULL
name_keytext
descriptiontext
statustextNOT NULL
created_byuuidNOT NULLFK → accounts
updated_byuuidNOT NULLFK → accounts
deleted_attimestamptz
deleted_byuuidFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL
base_unit_iduuidFK → product_unitsPSW-P1-01 (O2a). Base unit of the conversion group; null = independent or base. A base has no base (one level).
factornumericPSW-P1-01 (O2a). 1 unit = factor x base unit (> 0, finite). Set exactly when base_unit_id is.
for_salebooleanNOT NULLPSW-P1-01 (O2b). Offered as a product / accessory unit ("Dùng làm đơn vị tính sản phẩm"); independent of status.

Khoá chính: id

Trỏ ra (4):

  • base_unit_id → product_units · ON DELETE RESTRICT
  • created_by → accounts · ON DELETE RESTRICT
  • deleted_by → accounts · ON DELETE RESTRICT
  • updated_by → accounts · ON DELETE RESTRICT

Được trỏ tới (6): product_accessories, product_attribute_units, product_attribute_values, product_attributes, product_units, products

product_attributes ​

PSW-P1-05. Product attributes ("Thông số"): category_id null = COMMON; phase computed by catalog.attribute_phase. Soft delete, version, name_key unique per category / COMMON among live rows. PERM_GATED catalog.read/write.

public.product_attributes · 21 cột · 13 check · 0 unique · 2 index · 3 policy · 5 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
category_iduuidFK → categories
nametextNOT NULL
name_keytext
descriptiontext
data_typetextNOT NULL
default_unit_iduuidFK → product_units
decimalssmallint
min_valuenumeric
max_valuenumeric
is_requiredbooleanNOT NULL
positionintegerNOT NULL
statustextNOT NULL
merged_into_iduuidFK → product_attributes
created_byuuidNOT NULLFK → accounts
updated_byuuidNOT NULLFK → accounts
deleted_attimestamptz
deleted_byuuidFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (6):

  • category_id → categories · ON DELETE RESTRICT
  • created_by → accounts · ON DELETE RESTRICT
  • default_unit_id → product_units · ON DELETE RESTRICT
  • deleted_by → accounts · ON DELETE RESTRICT
  • merged_into_id → product_attributes · ON DELETE RESTRICT
  • updated_by → accounts · ON DELETE RESTRICT

Được trỏ tới (4): product_attribute_options, product_attribute_units, product_attribute_values, product_attributes

product_attribute_options ​

PSW-P1-05. Options of a SELECT / MULTI_SELECT attribute; value_key unique per attribute among live rows. Soft delete.

public.product_attribute_options · 13 cột · 5 check · 1 unique · 3 index · 3 policy · 4 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
attribute_iduuidNOT NULLFK → product_attributes
valuetextNOT NULL
value_keytext
positionintegerNOT NULL
statustextNOT NULL
created_byuuidNOT NULLFK → accounts
updated_byuuidNOT NULLFK → accounts
deleted_attimestamptz
deleted_byuuidFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (4):

  • attribute_id → product_attributes · ON DELETE RESTRICT
  • created_by → accounts · ON DELETE RESTRICT
  • deleted_by → accounts · ON DELETE RESTRICT
  • updated_by → accounts · ON DELETE RESTRICT

Được trỏ tới (2): product_attribute_value_options, product_attribute_values

product_attribute_values ​

PSW-P1-05. Attribute values on products, one live row per (product, attribute); value as entered (unit kept), value_base for filtering only (P1-06). Soft removal (removed_at, final). PERM_GATED catalog.read/write.

public.product_attribute_values · 18 cột · 8 check · 1 unique · 7 index · 3 policy · 8 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
product_iduuidNOT NULLFK → products
attribute_iduuidNOT NULLFK → product_attribute_options
value_texttext
value_boolboolean
option_iduuidFK → product_attribute_options
value_numbernumeric
value_unit_iduuidFK → product_units
value_basenumericPSW-P1-05 column, written by PSW-P1-06: value_number x coalesce(unit factor, 1); no pi_api grant.
is_highlightedbooleanNOT NULL
created_byuuidNOT NULLFK → accounts
updated_byuuidNOT NULLFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
removed_attimestamptz
removed_byuuidFK → accounts
versionintegerNOT NULL
positionintegerNOT NULLPSO-P1-01 (DEC-OWN28-SPECS-ORDER). Display order of the value on its product (1..n, written by the API from the order of SaveProductInput.specs / the import cell); not unique by design (in-place reorders), readers order by (position, created_at, id). Insert without it = after the live values (trigger product_attribute_values_position).

Khoá chính: id

Trỏ ra (7):

  • attribute_id → product_attributes · ON DELETE RESTRICT
  • created_by → accounts · ON DELETE RESTRICT
  • option_id, attribute_id → product_attribute_options · ON DELETE RESTRICT
  • product_id → products · ON DELETE RESTRICT
  • removed_by → accounts · ON DELETE RESTRICT
  • updated_by → accounts · ON DELETE RESTRICT
  • value_unit_id → product_units · ON DELETE RESTRICT

Được trỏ tới (1): product_attribute_value_options

product_import_runs ​

P2-03 SP-US-07: one append-only row per product import validate/commit; all-or-nothing by CHECK.

public.product_import_runs · 14 cột · 8 check · 1 unique · 4 index · 2 policy · 1 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
actor_iduuidNOT NULLFK → accounts
file_media_iduuidNOT NULLFK → media
file_sha256textNOT NULL
modetextNOT NULL
outcometextNOT NULL
file_errortext
row_countintegerNOT NULL
valid_countintegerNOT NULL
imported_countintegerNOT NULL
errorsjsonbNOT NULL
request_idtextNOT NULL
correlation_idtextNOT NULL
created_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (2):

  • actor_id → accounts · ON DELETE RESTRICT
  • file_media_id → media · ON DELETE RESTRICT

Được trỏ tới (1): product_import_staged_images

product_accessories ​

PSW-P1-04 (SP-US-02 AC21, S6). Accessories of a product: <= 10 live rows ordered by position, soft removal, version. PERM_GATED catalog.read / catalog.write (matrix CAT-01/02).

public.product_accessories · 14 cột · 6 check · 0 unique · 3 index · 3 policy · 5 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
product_iduuidNOT NULLFK → products
positionintegerNOT NULL
nametextNOT NULL
short_descriptiontext
quantityintegerNOT NULL
unit_iduuidFK → product_unitsPSW-P1-04 (S6). Optional unit from the product-unit list: a new assignment must be live, ACTIVE and for_sale; a unit the product's accessories already have is kept (AC-A3).
created_byuuidNOT NULLFK → accounts
updated_byuuidNOT NULLFK → accounts
removed_attimestamptz
removed_byuuidFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (5):

  • created_by → accounts · ON DELETE RESTRICT
  • product_id → products · ON DELETE RESTRICT
  • removed_by → accounts · ON DELETE RESTRICT
  • unit_id → product_units · ON DELETE RESTRICT
  • updated_by → accounts · ON DELETE RESTRICT

Được trỏ tới: không có

product_attribute_units ​

PSW-P1-05. Allowed units of a NUMBER attribute. Soft removal (removed_at, final). PERM_GATED catalog.read/write.

public.product_attribute_units · 7 cột · 1 check · 0 unique · 3 index · 3 policy · 4 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
attribute_iduuidNOT NULLFK → product_attributes
unit_iduuidNOT NULLFK → product_units
created_byuuidNOT NULLFK → accounts
created_attimestamptzNOT NULL
removed_attimestamptz
removed_byuuidFK → accounts

Khoá chính: id

Trỏ ra (4):

  • attribute_id → product_attributes · ON DELETE RESTRICT
  • created_by → accounts · ON DELETE RESTRICT
  • removed_by → accounts · ON DELETE RESTRICT
  • unit_id → product_units · ON DELETE RESTRICT

Được trỏ tới: không có

product_attribute_value_options ​

PSW-P1-05. Chosen options of a MULTI_SELECT value row. Soft removal (removed_at, final).

public.product_attribute_value_options · 9 cột · 1 check · 0 unique · 3 index · 3 policy · 4 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
value_iduuidNOT NULLFK → product_attribute_values
product_iduuidNOT NULLFK → product_attribute_values
attribute_iduuidNOT NULLFK → product_attribute_values
option_iduuidNOT NULLFK → product_attribute_options
created_byuuidNOT NULLFK → accounts
created_attimestamptzNOT NULL
removed_attimestamptz
removed_byuuidFK → accounts

Khoá chính: id

Trỏ ra (4):

  • created_by → accounts · ON DELETE RESTRICT
  • option_id, attribute_id → product_attribute_options · ON DELETE RESTRICT
  • removed_by → accounts · ON DELETE RESTRICT
  • value_id, product_id, attribute_id → product_attribute_values · ON DELETE RESTRICT

Được trỏ tới: không có

product_change_events ​

U12-P2-18 (ADD-SP-04, G-HISTORY). Immutable product change history, one row per (command, product); real old/new values (masked at read time, U12-P3-09). Written by catalog.record_product_history, purged after 24 months only.

public.product_change_events · 20 cột · 10 check · 2 unique · 5 index · 0 policy · 2 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
seqbigintNOT NULL
product_iduuidNOT NULLFK → products
sku_at_eventtextNOT NULL
event_typetextNOT NULL
sourcetextNOT NULL
occurred_attimestamptzNOT NULL
recorded_attimestamptzNOT NULL
actor_account_iduuidNOT NULLFK → accounts
actor_appapp_codeNOT NULL
operationtextNOT NULL
request_idtextNOT NULL
correlation_idcorrelation_refNOT NULL
version_beforeinteger
version_afterintegerNOT NULL
source_refjsonbNOT NULL
summaryjsonbNOT NULL
changesjsonbNOT NULL
changed_fieldstext[]NOT NULL
payload_versionsmallintNOT NULL

Khoá chính: id

Trỏ ra (2):

  • actor_account_id → accounts · ON DELETE RESTRICT
  • product_id → products · ON DELETE RESTRICT

Được trỏ tới: không có

product_column_preferences ​

U12-P2-19 ADD-SP-07. Product-list columns of one Admin (canonical ProductColumn codes, fixed order, SKU/NAME always present); own row only, last write wins. No row = catalog.product_default_columns().

public.product_column_preferences · 6 cột · 3 check · 0 unique · 1 index · 3 policy · 2 trigger · RLS bật

CộtKiểuKhoáMô tả
account_iduuidNOT NULLPK FK → app_memberships
appapp_codeNOT NULLFK → app_memberships
columnstext[]NOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: account_id

Trỏ ra (1):

  • account_id, app → app_memberships · ON DELETE RESTRICT

Được trỏ tới: không có

product_import_staged_images ​

U6-P7-49 SP-US-07 / NEW-SP-15: images downloaded at VALIDATE, kept (staged media, TTL) until the COMMIT of the same file attaches them or the sweep removes them.

public.product_import_staged_images · 8 cột · 3 check · 2 unique · 4 index · 3 policy · 0 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
run_iduuidNOT NULLFK → product_import_runs
actor_iduuidNOT NULLFK → accounts
row_numberintegerNOT NULL
positionintegerNOT NULL
media_iduuidNOT NULLFK → media
created_attimestamptzNOT NULL
expires_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (3):

  • actor_id → accounts · ON DELETE RESTRICT
  • media_id → media · ON DELETE RESTRICT
  • run_id → product_import_runs · ON DELETE RESTRICT

Được trỏ tới: không có

product_media ​

P2-02. Ordered product gallery (<= 8, one primary). One media_uses row per attachment; removal is soft.

public.product_media · 10 cột · 2 check · 1 unique · 4 index · 3 policy · 7 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
product_iduuidNOT NULLFK → products
media_iduuidNOT NULLFK → media
media_use_iduuidNOT NULLFK → media_uses
positionsmallintNOT NULL
is_primarybooleanNOT NULL
created_byuuidNOT NULLFK → accounts
created_attimestamptzNOT NULL
removed_attimestamptz
removed_byuuidFK → accounts

Khoá chính: id

Trỏ ra (5):

  • created_by → accounts · ON DELETE RESTRICT
  • media_id → media · ON DELETE RESTRICT
  • media_use_id → media_uses · ON DELETE RESTRICT
  • product_id → products · ON DELETE RESTRICT
  • removed_by → accounts · ON DELETE RESTRICT

Được trỏ tới: không có

Sinh từ ad83b0aea · 820 migration · PostgreSQL 17