Skip to content

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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
definition_iduuidNOT NULLFK → tier_definitions
audiencetextNOT NULLFK → tier_definitions
version_nointegerNOT NULL
statustextNOT NULL
nametextNOT NULL
rank_orderintegerNOT NULL
rank_thresholdbigintNOT NULL
effective_fromtimestamptzNOT NULL
effective_totimestamptz
published_attimestamptz
published_byuuidFK → accounts
created_byuuidFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (3):

  • created_by → accounts · ON DELETE RESTRICT
  • definition_id, audience → tier_definitions · ON DELETE RESTRICT
  • published_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
audiencetextNOT NULL
codetextNOT NULL
is_standardbooleanNOT NULL
created_byuuidFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT 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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
version_nointegerNOT NULL
tier_countintegerNOT NULL
change_kindtextNOT NULL
changed_definition_iduuidFK → tier_definitions
effective_fromtimestamptzNOT NULL
effective_totimestamptz
created_byuuidFK → accounts
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (2):

  • changed_definition_id → tier_definitions · ON DELETE RESTRICT
  • created_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
account_iduuidNOT NULLFK → tier_assignments
audiencetextNOT NULLFK → tier_versions
tier_version_iduuidNOT NULLFK → tier_versions
previous_assignment_iduuidFK → tier_assignments
evaluation_iduuidFK → tier_evaluations
change_kindtextNOT NULL
reasontext
effective_attimestamptzNOT NULL
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (4):

  • account_id → accounts · ON DELETE RESTRICT
  • evaluation_id, account_id, audience → tier_evaluations · ON DELETE RESTRICT
  • previous_assignment_id, account_id, audience → tier_assignments · ON DELETE RESTRICT
  • tier_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
account_iduuidNOT NULLFK → accounts
audiencetextNOT NULLFK → tier_versions
evaluated_attimestamptzNOT NULL
tier_version_iduuidNOT NULLFK → tier_versions
current_tier_version_iduuidNOT NULLFK → tier_versions
trigger_kindtextNOT NULL
outcometextNOT NULL
decisiontextNOT NULL
active_rankbigintNOT NULL
rank_thresholdbigintNOT NULL
evidencejsonbNOT NULL
source_event_iduuid
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (3):

  • account_id → accounts · ON DELETE RESTRICT
  • current_tier_version_id, audience → tier_versions · ON DELETE RESTRICT
  • tier_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
kindtextNOT NULL
reasontext
set_version_iduuidFK → tier_set_versions
statustextNOT NULL
phasetext
checkpoint_keytext
processed_countintegerNOT NULL
changed_countintegerNOT NULL
total_countinteger
attemptsintegerNOT NULL
failure_codetext
requested_byuuidFK → accounts
correlation_idcorrelation_refNOT NULL
started_attimestamptzNOT NULL
finished_attimestamptz
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (2):

  • requested_by → accounts · ON DELETE RESTRICT
  • set_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
version_nointegerNOT NULL
decision_reftextNOT NULL
approval_reftextNOT NULL
effective_fromtimestamptzNOT NULL
created_attimestamptzNOT 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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
tier_version_iduuidNOT NULLFK → tier_versions
tier_labeltextNOT NULL
previousjsonbNOT NULL
multipliersjsonbNOT NULL
digesttextNOT NULL
reasontextNOT NULL
statustextNOT NULL
proposed_byuuidNOT NULLFK → accounts
decided_byuuidFK → accounts
decided_attimestamptz
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (3):

  • decided_by → accounts · ON DELETE RESTRICT
  • proposed_by → accounts · ON DELETE RESTRICT
  • tier_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ộtKiểuKhoáMô tả
tier_version_iduuidNOT NULLPK FK → tier_versions
activitytextNOT NULLPK
multipliernumeric(10,4)NOT NULL
created_attimestamptzNOT 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ộtKiểuKhoáMô tả
set_version_iduuidNOT NULLPK FK → tier_set_versions
definition_iduuidNOT NULLPK FK → tier_definitions
audiencetextNOT NULLFK → tier_definitions
nametextNOT NULL
rank_thresholdbigintNOT NULL
coefficientnumeric(10,4)
benefit_descriptiontext
updated_attimestamptzNOT NULL
created_attimestamptzNOT NULL

Khoá chính: set_version_id, definition_id

Trỏ ra (2):

  • definition_id, audience → tier_definitions · ON DELETE RESTRICT
  • set_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ộtKiểuKhoáMô tả
tier_version_iduuidNOT NULLPK FK → tier_versions
mission_definition_iduuidNOT NULLPK FK → mission_definitions
created_attimestamptzNOT NULL

Khoá chính: tier_version_id, mission_definition_id

Trỏ ra (2):

  • mission_definition_id → mission_definitions · ON DELETE RESTRICT
  • tier_version_id → tier_versions · ON DELETE RESTRICT

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

Sinh từ ad83b0aea · 820 migration · PostgreSQL 17