Giao diện
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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
product_type | text | NOT NULL | ||
sku | text | NOT NULL | ||
sku_key | text | |||
name | text | |||
category_id | uuid | FK → categories | ||
brand_id | uuid | FK → brands | ||
tag | text | |||
barcode | text | |||
barcode_key | text | |||
unit | text | |||
length | numeric | |||
width | numeric | |||
height | numeric | |||
dimension_unit | text | |||
weight | numeric | |||
weight_unit | text | |||
requires_installation | boolean | NOT NULL | ||
description | text | |||
status | text | NOT NULL | ||
created_by | uuid | NOT NULL | FK → accounts | |
updated_by | uuid | NOT NULL | FK → accounts | |
deleted_at | timestamptz | |||
deleted_by | uuid | FK → accounts | ||
is_deleted | boolean | |||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL | ||
unit_id | uuid | FK → product_units | U12-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_name | text | PSW-P1-03 (Word SP-US-02). Tên rút gọn, optional, trimmed 1..30 characters; null = none. | ||
short_description | text | PSW-P1-03 (Word SP-US-02). Mô tả ngắn, optional, trimmed 1..100 characters; null = none. | ||
is_genuine | boolean | NOT NULL | PSW-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 RESTRICTcategory_id→categories·ON DELETE RESTRICTcreated_by→accounts·ON DELETE RESTRICTdeleted_by→accounts·ON DELETE RESTRICTunit_id→product_units·ON DELETE RESTRICTupdated_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
name | text | NOT NULL | ||
name_key | text | |||
description | text | |||
status | text | NOT NULL | ||
created_by | uuid | NOT NULL | FK → accounts | |
updated_by | uuid | NOT NULL | FK → accounts | |
deleted_at | timestamptz | |||
deleted_by | uuid | FK → accounts | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL | ||
base_unit_id | uuid | FK → product_units | PSW-P1-01 (O2a). Base unit of the conversion group; null = independent or base. A base has no base (one level). | |
factor | numeric | PSW-P1-01 (O2a). 1 unit = factor x base unit (> 0, finite). Set exactly when base_unit_id is. | ||
for_sale | boolean | NOT NULL | PSW-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 RESTRICTcreated_by→accounts·ON DELETE RESTRICTdeleted_by→accounts·ON DELETE RESTRICTupdated_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
category_id | uuid | FK → categories | ||
name | text | NOT NULL | ||
name_key | text | |||
description | text | |||
data_type | text | NOT NULL | ||
default_unit_id | uuid | FK → product_units | ||
decimals | smallint | |||
min_value | numeric | |||
max_value | numeric | |||
is_required | boolean | NOT NULL | ||
position | integer | NOT NULL | ||
status | text | NOT NULL | ||
merged_into_id | uuid | FK → product_attributes | ||
created_by | uuid | NOT NULL | FK → accounts | |
updated_by | uuid | NOT NULL | FK → accounts | |
deleted_at | timestamptz | |||
deleted_by | uuid | FK → accounts | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (6):
category_id→categories·ON DELETE RESTRICTcreated_by→accounts·ON DELETE RESTRICTdefault_unit_id→product_units·ON DELETE RESTRICTdeleted_by→accounts·ON DELETE RESTRICTmerged_into_id→product_attributes·ON DELETE RESTRICTupdated_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
attribute_id | uuid | NOT NULL | FK → product_attributes | |
value | text | NOT NULL | ||
value_key | text | |||
position | integer | NOT NULL | ||
status | text | NOT NULL | ||
created_by | uuid | NOT NULL | FK → accounts | |
updated_by | uuid | NOT NULL | FK → accounts | |
deleted_at | timestamptz | |||
deleted_by | uuid | FK → accounts | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (4):
attribute_id→product_attributes·ON DELETE RESTRICTcreated_by→accounts·ON DELETE RESTRICTdeleted_by→accounts·ON DELETE RESTRICTupdated_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
product_id | uuid | NOT NULL | FK → products | |
attribute_id | uuid | NOT NULL | FK → product_attribute_options | |
value_text | text | |||
value_bool | boolean | |||
option_id | uuid | FK → product_attribute_options | ||
value_number | numeric | |||
value_unit_id | uuid | FK → product_units | ||
value_base | numeric | PSW-P1-05 column, written by PSW-P1-06: value_number x coalesce(unit factor, 1); no pi_api grant. | ||
is_highlighted | boolean | NOT NULL | ||
created_by | uuid | NOT NULL | FK → accounts | |
updated_by | uuid | NOT NULL | FK → accounts | |
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
removed_at | timestamptz | |||
removed_by | uuid | FK → accounts | ||
version | integer | NOT NULL | ||
position | integer | NOT NULL | PSO-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 RESTRICTcreated_by→accounts·ON DELETE RESTRICToption_id, attribute_id→product_attribute_options·ON DELETE RESTRICTproduct_id→products·ON DELETE RESTRICTremoved_by→accounts·ON DELETE RESTRICTupdated_by→accounts·ON DELETE RESTRICTvalue_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
actor_id | uuid | NOT NULL | FK → accounts | |
file_media_id | uuid | NOT NULL | FK → media | |
file_sha256 | text | NOT NULL | ||
mode | text | NOT NULL | ||
outcome | text | NOT NULL | ||
file_error | text | |||
row_count | integer | NOT NULL | ||
valid_count | integer | NOT NULL | ||
imported_count | integer | NOT NULL | ||
errors | jsonb | NOT NULL | ||
request_id | text | NOT NULL | ||
correlation_id | text | NOT NULL | ||
created_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra (2):
actor_id→accounts·ON DELETE RESTRICTfile_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
product_id | uuid | NOT NULL | FK → products | |
position | integer | NOT NULL | ||
name | text | NOT NULL | ||
short_description | text | |||
quantity | integer | NOT NULL | ||
unit_id | uuid | FK → product_units | PSW-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_by | uuid | NOT NULL | FK → accounts | |
updated_by | uuid | NOT NULL | FK → accounts | |
removed_at | timestamptz | |||
removed_by | uuid | FK → accounts | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (5):
created_by→accounts·ON DELETE RESTRICTproduct_id→products·ON DELETE RESTRICTremoved_by→accounts·ON DELETE RESTRICTunit_id→product_units·ON DELETE RESTRICTupdated_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
attribute_id | uuid | NOT NULL | FK → product_attributes | |
unit_id | uuid | NOT NULL | FK → product_units | |
created_by | uuid | NOT NULL | FK → accounts | |
created_at | timestamptz | NOT NULL | ||
removed_at | timestamptz | |||
removed_by | uuid | FK → accounts |
Khoá chính: id
Trỏ ra (4):
attribute_id→product_attributes·ON DELETE RESTRICTcreated_by→accounts·ON DELETE RESTRICTremoved_by→accounts·ON DELETE RESTRICTunit_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
value_id | uuid | NOT NULL | FK → product_attribute_values | |
product_id | uuid | NOT NULL | FK → product_attribute_values | |
attribute_id | uuid | NOT NULL | FK → product_attribute_values | |
option_id | uuid | NOT NULL | FK → product_attribute_options | |
created_by | uuid | NOT NULL | FK → accounts | |
created_at | timestamptz | NOT NULL | ||
removed_at | timestamptz | |||
removed_by | uuid | FK → accounts |
Khoá chính: id
Trỏ ra (4):
created_by→accounts·ON DELETE RESTRICToption_id, attribute_id→product_attribute_options·ON DELETE RESTRICTremoved_by→accounts·ON DELETE RESTRICTvalue_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
seq | bigint | NOT NULL | ||
product_id | uuid | NOT NULL | FK → products | |
sku_at_event | text | NOT NULL | ||
event_type | text | NOT NULL | ||
source | text | NOT NULL | ||
occurred_at | timestamptz | NOT NULL | ||
recorded_at | timestamptz | NOT NULL | ||
actor_account_id | uuid | NOT NULL | FK → accounts | |
actor_app | app_code | NOT NULL | ||
operation | text | NOT NULL | ||
request_id | text | NOT NULL | ||
correlation_id | correlation_ref | NOT NULL | ||
version_before | integer | |||
version_after | integer | NOT NULL | ||
source_ref | jsonb | NOT NULL | ||
summary | jsonb | NOT NULL | ||
changes | jsonb | NOT NULL | ||
changed_fields | text[] | NOT NULL | ||
payload_version | smallint | NOT NULL |
Khoá chính: id
Trỏ ra (2):
actor_account_id→accounts·ON DELETE RESTRICTproduct_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
account_id | uuid | NOT NULL | PK FK → app_memberships | |
app | app_code | NOT NULL | FK → app_memberships | |
columns | text[] | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT 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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
run_id | uuid | NOT NULL | FK → product_import_runs | |
actor_id | uuid | NOT NULL | FK → accounts | |
row_number | integer | NOT NULL | ||
position | integer | NOT NULL | ||
media_id | uuid | NOT NULL | FK → media | |
created_at | timestamptz | NOT NULL | ||
expires_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra (3):
actor_id→accounts·ON DELETE RESTRICTmedia_id→media·ON DELETE RESTRICTrun_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
product_id | uuid | NOT NULL | FK → products | |
media_id | uuid | NOT NULL | FK → media | |
media_use_id | uuid | NOT NULL | FK → media_uses | |
position | smallint | NOT NULL | ||
is_primary | boolean | NOT NULL | ||
created_by | uuid | NOT NULL | FK → accounts | |
created_at | timestamptz | NOT NULL | ||
removed_at | timestamptz | |||
removed_by | uuid | FK → accounts |
Khoá chính: id
Trỏ ra (5):
created_by→accounts·ON DELETE RESTRICTmedia_id→media·ON DELETE RESTRICTmedia_use_id→media_uses·ON DELETE RESTRICTproduct_id→products·ON DELETE RESTRICTremoved_by→accounts·ON DELETE RESTRICT
Được trỏ tới: không có