Giao diện
loyalty / tier
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 đè.
11 bảng. Quay lại loyalty.
tier_versions
P4-07. VERSIONED_ROWS. Created DRAFT; published policy (name, order, threshold, window) is immutable; effective_to is set-once and never retroactive. Old versions are retained for evaluations and history.
public.tier_versions · 16 cột · 8 check · 2 unique · 7 index · 4 policy · 4 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
definition_id | uuid | NOT NULL | FK → tier_definitions | |
audience | text | NOT NULL | FK → tier_definitions | |
version_no | integer | NOT NULL | ||
status | text | NOT NULL | ||
name | text | NOT NULL | ||
rank_order | integer | NOT NULL | ||
rank_threshold | bigint | NOT NULL | ||
effective_from | timestamptz | NOT NULL | ||
effective_to | timestamptz | |||
published_at | timestamptz | |||
published_by | uuid | FK → accounts | ||
created_by | uuid | FK → accounts | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (3):
created_by→accounts·ON DELETE RESTRICTdefinition_id, audience→tier_definitions·ON DELETE RESTRICTpublished_by→accounts·ON DELETE RESTRICT
Được trỏ tới (8): sale_role_transfers, sale_role_transfers, tier_assignments, tier_evaluations, tier_evaluations, tier_multiplier_changes, tier_multipliers, tier_version_missions
tier_definitions
P4-07. Stable Tier identity per audience (USER|SALE). Policy lives in tier_versions; a definition is never deleted (Standard cannot be, A-TIER-01 AC01).
public.tier_definitions · 8 cột · 4 check · 2 unique · 4 index · 4 policy · 4 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
audience | text | NOT NULL | ||
code | text | NOT NULL | ||
is_standard | boolean | NOT NULL | ||
created_by | uuid | FK → accounts | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (1):
created_by→accounts·ON DELETE RESTRICT
Được trỏ tới (5): member_tier_history, member_tier_history, tier_set_items, tier_set_versions, tier_versions
tier_set_versions
U28-P3-01. VERSIONED_ROWS. One row per save of the one customer Tier set; version_no is the CAS token; contiguous non-retroactive windows; only the set-once effective_to moves; never deleted.
public.tier_set_versions · 10 cột · 5 check · 1 unique · 5 index · 1 policy · 5 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
version_no | integer | NOT NULL | ||
tier_count | integer | NOT NULL | ||
change_kind | text | NOT NULL | ||
changed_definition_id | uuid | FK → tier_definitions | ||
effective_from | timestamptz | NOT NULL | ||
effective_to | timestamptz | |||
created_by | uuid | FK → accounts | ||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra (2):
changed_definition_id→tier_definitions·ON DELETE RESTRICTcreated_by→accounts·ON DELETE RESTRICT
Được trỏ tới (3): member_tier_history, tier_reevaluations, tier_set_items
tier_assignments
P4-07. APPEND_ONLY_RETAIN Tier history: a new row per change, never an update. Current = the row nobody follows. Absent any row the member is on the Standard Tier by default (A-TIER-02 AC02).
public.tier_assignments · 11 cột · 5 check · 1 unique · 5 index · 2 policy · 3 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
account_id | uuid | NOT NULL | FK → tier_assignments | |
audience | text | NOT NULL | FK → tier_versions | |
tier_version_id | uuid | NOT NULL | FK → tier_versions | |
previous_assignment_id | uuid | FK → tier_assignments | ||
evaluation_id | uuid | FK → tier_evaluations | ||
change_kind | text | NOT NULL | ||
reason | text | |||
effective_at | timestamptz | NOT NULL | ||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra (4):
account_id→accounts·ON DELETE RESTRICTevaluation_id, account_id, audience→tier_evaluations·ON DELETE RESTRICTprevious_assignment_id, account_id, audience→tier_assignments·ON DELETE RESTRICTtier_version_id, audience→tier_versions·ON DELETE RESTRICT
Được trỏ tới (2): sale_role_transfers, tier_assignments
tier_evaluations
P4-07. APPEND_ONLY_RETAIN evidence of one Tier evaluation (rank, threshold, outcome, evidence json). Written by the P4-09 worker under a lease; no update, no delete.
public.tier_evaluations · 15 cột · 9 check · 2 unique · 6 index · 2 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
account_id | uuid | NOT NULL | FK → accounts | |
audience | text | NOT NULL | FK → tier_versions | |
evaluated_at | timestamptz | NOT NULL | ||
tier_version_id | uuid | NOT NULL | FK → tier_versions | |
current_tier_version_id | uuid | NOT NULL | FK → tier_versions | |
trigger_kind | text | NOT NULL | ||
outcome | text | NOT NULL | ||
decision | text | NOT NULL | ||
active_rank | bigint | NOT NULL | ||
rank_threshold | bigint | NOT NULL | ||
evidence | jsonb | NOT NULL | ||
source_event_id | uuid | |||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra (3):
account_id→accounts·ON DELETE RESTRICTcurrent_tier_version_id, audience→tier_versions·ON DELETE RESTRICTtier_version_id, audience→tier_versions·ON DELETE RESTRICT
Được trỏ tới (2): sale_role_transfers, tier_assignments
tier_reevaluations
U28-P3-01. Versioned business row of a full customer re-evaluation (one per set version) or of the P3-09 backfill run: RUNNING -> COMPLETED | FAILED, FAILED -> RUNNING retry from checkpoint_key; never deleted.
public.tier_reevaluations · 19 cột · 13 check · 0 unique · 4 index · 1 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
kind | text | NOT NULL | ||
reason | text | |||
set_version_id | uuid | FK → tier_set_versions | ||
status | text | NOT NULL | ||
phase | text | |||
checkpoint_key | text | |||
processed_count | integer | NOT NULL | ||
changed_count | integer | NOT NULL | ||
total_count | integer | |||
attempts | integer | NOT NULL | ||
failure_code | text | |||
requested_by | uuid | FK → accounts | ||
correlation_id | correlation_ref | NOT NULL | ||
started_at | timestamptz | NOT NULL | ||
finished_at | timestamptz | |||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (2):
requested_by→accounts·ON DELETE RESTRICTset_version_id→tier_set_versions·ON DELETE RESTRICT
Được trỏ tới (1): member_tier_history
tier_benefit_policy_versions
P4-08. APPROVED Tier benefit (multiplier / monetary conversion) policy records, one per decision (SPEC-01). Not seeded: while empty every multiplier write is refused with policy_undecided. Immutable, no delete.
public.tier_benefit_policy_versions · 6 cột · 3 check · 1 unique · 2 index · 1 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
version_no | integer | NOT NULL | ||
decision_ref | text | NOT NULL | ||
approval_ref | text | NOT NULL | ||
effective_from | timestamptz | NOT NULL | ||
created_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra: không có
Được trỏ tới: không có
tier_multiplier_changes
FU-TIERAPPROVE. Maker/checker record of one Tier multiplier change (a DRAFT version's set vs the published one): a Sale Manager proposes, a different Business Owner approves the unchanged values; publishing needs the approval.
public.tier_multiplier_changes · 14 cột · 6 check · 0 unique · 3 index · 4 policy · 3 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
tier_version_id | uuid | NOT NULL | FK → tier_versions | |
tier_label | text | NOT NULL | ||
previous | jsonb | NOT NULL | ||
multipliers | jsonb | NOT NULL | ||
digest | text | NOT NULL | ||
reason | text | NOT NULL | ||
status | text | NOT NULL | ||
proposed_by | uuid | NOT NULL | FK → accounts | |
decided_by | uuid | FK → accounts | ||
decided_at | timestamptz | |||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (3):
decided_by→accounts·ON DELETE RESTRICTproposed_by→accounts·ON DELETE RESTRICTtier_version_id→tier_versions·ON DELETE RESTRICT
Được trỏ tới: không có
tier_multipliers
P4-07. Multiplier by business activity for one Tier version. SPEC-01 is open: nothing is seeded; an absent row is "no value" (never zero). Applies only to rewards generated after the version's effective time.
public.tier_multipliers · 4 cột · 2 check · 0 unique · 1 index · 4 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
tier_version_id | uuid | NOT NULL | PK FK → tier_versions | |
activity | text | NOT NULL | PK | |
multiplier | numeric(10,4) | NOT NULL | ||
created_at | timestamptz | NOT NULL |
Khoá chính: tier_version_id, activity
Trỏ ra (1):
tier_version_id→tier_versions·ON DELETE RESTRICT
Được trỏ tới: không có
tier_set_items
U28-P3-01. Immutable Tiers of one tier_set_versions row (full snapshot): stable definition id, name, threshold, common coefficient (NULL = not configured), benefit description. Standard = tier_definitions.is_standard.
public.tier_set_items · 9 cột · 5 check · 1 unique · 3 index · 1 policy · 4 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
set_version_id | uuid | NOT NULL | PK FK → tier_set_versions | |
definition_id | uuid | NOT NULL | PK FK → tier_definitions | |
audience | text | NOT NULL | FK → tier_definitions | |
name | text | NOT NULL | ||
rank_threshold | bigint | NOT NULL | ||
coefficient | numeric(10,4) | |||
benefit_description | text | |||
updated_at | timestamptz | NOT NULL | ||
created_at | timestamptz | NOT NULL |
Khoá chính: set_version_id, definition_id
Trỏ ra (2):
definition_id, audience→tier_definitions·ON DELETE RESTRICTset_version_id→tier_set_versions·ON DELETE RESTRICT
Được trỏ tới: không có
tier_version_missions
Chưa có mô tả — bảng này chưa có COMMENT ON TABLE.
public.tier_version_missions · 3 cột · 0 check · 0 unique · 1 index · 4 policy · 1 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
tier_version_id | uuid | NOT NULL | PK FK → tier_versions | |
mission_definition_id | uuid | NOT NULL | PK FK → mission_definitions | |
created_at | timestamptz | NOT NULL |
Khoá chính: tier_version_id, mission_definition_id
Trỏ ra (2):
mission_definition_id→mission_definitions·ON DELETE RESTRICTtier_version_id→tier_versions·ON DELETE RESTRICT
Được trỏ tới: không có