Skip to content

loyalty / mission ​

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 đè.

7 bảng. Quay lại loyalty.

mission_contributions ​

P4-01. APPEND_ONLY evidence. CONTRIBUTE rows must fall inside [window_starts_at, effective_to) and before paused_at; REVERSE rows reference their original, never exceed it and never rewrite history.

public.mission_contributions · 15 cột · 7 check · 1 unique · 6 index · 0 policy · 5 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
assignment_iduuidNOT NULLFK → mission_contributions
mission_version_iduuidNOT NULLFK → mission_criteria
criterion_iduuidNOT NULLFK → mission_contributions
metricmission_metricNOT NULLFK → mission_criteria
kindtextNOT NULL
amountbigintNOT NULL
reverses_contribution_iduuidFK → mission_contributions
source_typetextNOT NULL
source_idtextNOT NULL
source_event_iduuidNOT NULL
source_event_typetextNOT NULL
occurred_attimestamptzNOT NULL
recorded_attimestamptzNOT NULL
correlation_idcorrelation_refNOT NULL

Khoá chính: id

Trỏ ra (3):

  • assignment_id, mission_version_id → mission_assignments · ON DELETE RESTRICT
  • criterion_id, mission_version_id, metric → mission_criteria · ON DELETE RESTRICT
  • reverses_contribution_id, assignment_id, criterion_id → mission_contributions · ON DELETE RESTRICT

Được trỏ tới (4): mission_contributions, mission_progress_snapshots, mission_reversal_evidence, mission_reversal_evidence

mission_assignments ​

P4-01. One row per (account, published version). window_starts_at = greatest(assigned_at, effective_from), set by trigger. completed_at is set once by the progress projection (P4-06).

public.mission_assignments · 12 cột · 3 check · 2 unique · 4 index · 2 policy · 5 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
account_iduuidNOT NULLFK → app_memberships
appapp_codeNOT NULLFK → app_memberships
mission_version_iduuidNOT NULLFK → mission_versions
assigned_attimestamptzNOT NULL
window_starts_attimestamptzNOT NULL
assigned_byuuidFK → accounts
correlation_idcorrelation_refNOT NULL
completed_attimestamptz
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (3):

  • assigned_by → accounts · ON DELETE RESTRICT
  • account_id, app → app_memberships · ON DELETE RESTRICT
  • mission_version_id → mission_versions · ON DELETE RESTRICT

Được trỏ tới (2): mission_contributions, mission_progress_snapshots

mission_criteria ​

P4-01. SNAPSHOT: editable only while the parent version is DRAFT; frozen once published. No expression/JSON column: metric is the MT-01 catalog domain, operator is always >=.

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

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
mission_version_iduuidNOT NULLFK → mission_versions
metricmission_metricNOT NULL
operatortextNOT NULL
thresholdbigintNOT NULL
created_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (1):

  • mission_version_id → mission_versions · ON DELETE CASCADE

Được trỏ tới (2): mission_contributions, mission_progress_snapshots

mission_definitions ​

P4-01. Stable Mission identity (code, purpose). All measurable policy lives in mission_versions + mission_criteria; changing it means a new version, never an edit of a published one (A-MIS-01 AC06).

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

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
codetextNOT NULL
purposetextNOT NULL
nametextNOT NULL
descriptiontext
created_byuuidNOT NULLFK → 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 (2): mission_versions, tier_version_missions

mission_versions ​

P4-01. VERSIONED_ROWS. Created DRAFT; publish needs >= 1 criterion; published policy is immutable. Pause and window end are set-once, never retroactive. Resume is not defined (GAP-MISSION-RESUME).

public.mission_versions · 16 cột · 8 check · 1 unique · 4 index · 3 policy · 4 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
definition_iduuidNOT NULLFK → mission_definitions
version_nointegerNOT NULL
statustextNOT NULL
criteria_logictextNOT NULL
effective_fromtimestamptzNOT NULL
effective_totimestamptz
published_attimestamptz
published_byuuidFK → accounts
paused_attimestamptz
paused_byuuidFK → accounts
pause_reasontext
created_byuuidNOT NULLFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (4):

  • created_by → accounts · ON DELETE RESTRICT
  • definition_id → mission_definitions · ON DELETE RESTRICT
  • paused_by → accounts · ON DELETE RESTRICT
  • published_by → accounts · ON DELETE RESTRICT

Được trỏ tới (2): mission_assignments, mission_criteria

mission_progress_snapshots ​

P4-01 table, P4-06 behaviour. Projection only: rebuildable from mission_contributions; no manual progress edit path (A-MIS-04). Met = achieved >= criterion threshold, evaluated by the service.

public.mission_progress_snapshots · 15 cột · 5 check · 1 unique · 2 index · 0 policy · 2 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
assignment_iduuidNOT NULLFK → mission_contributions
mission_version_iduuidNOT NULLFK → mission_criteria
criterion_iduuidNOT NULLFK → mission_contributions
metricmission_metricNOT NULLFK → mission_criteria
contributed_totalbigintNOT NULL
reversed_totalbigintNOT NULL
achievedbigint
contribution_countintegerNOT NULL
reversal_countintegerNOT NULL
last_contribution_iduuidFK → mission_contributions
as_oftimestamptzNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (3):

  • assignment_id, mission_version_id → mission_assignments · ON DELETE RESTRICT
  • criterion_id, mission_version_id, metric → mission_criteria · ON DELETE RESTRICT
  • last_contribution_id, assignment_id, criterion_id → mission_contributions · ON DELETE RESTRICT

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

mission_reversal_evidence ​

P4-05. APPEND_ONLY_RETAIN evidence of a refund-driven Mission inverse: one row per (original contribution, reason). Written only by mission.reverse_order_contributions (pi_worker consumer). Awarded Tier, reward and rank lots are never touched; awarded_flags only marks what a BO-approved ADJUST may want to review.

public.mission_reversal_evidence · 20 cột · 7 check · 1 unique · 4 index · 0 policy · 2 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
order_iduuidNOT NULLFK → orders
original_contribution_iduuidNOT NULLFK → mission_contributions
assignment_iduuidNOT NULLFK → mission_contributions
criterion_iduuidNOT NULLFK → mission_contributions
metricmission_metricNOT NULL
reasontextNOT NULL
outcometextNOT NULL
original_amountbigintNOT NULL
requested_amountbigintNOT NULL
applied_amountbigintNOT NULL
prior_reversed_amountbigintNOT NULL
reversal_contribution_iduuidFK → mission_contributions
refund_case_iduuidFK → refund_cases
decision_iduuidFK → cancellation_decisions
awarded_flagstext[]NOT NULL
source_event_iduuidNOT NULL
source_event_typetextNOT NULL
correlation_idcorrelation_refNOT NULL
recorded_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (5):

  • decision_id → cancellation_decisions · ON DELETE RESTRICT
  • order_id → orders · ON DELETE RESTRICT
  • original_contribution_id, assignment_id, criterion_id → mission_contributions · ON DELETE RESTRICT
  • refund_case_id → refund_cases · ON DELETE RESTRICT
  • reversal_contribution_id, assignment_id, criterion_id → mission_contributions · ON DELETE RESTRICT

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

Sinh từ ad83b0aea · 820 migration · PostgreSQL 17