Giao diện
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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
assignment_id | uuid | NOT NULL | FK → mission_contributions | |
mission_version_id | uuid | NOT NULL | FK → mission_criteria | |
criterion_id | uuid | NOT NULL | FK → mission_contributions | |
metric | mission_metric | NOT NULL | FK → mission_criteria | |
kind | text | NOT NULL | ||
amount | bigint | NOT NULL | ||
reverses_contribution_id | uuid | FK → mission_contributions | ||
source_type | text | NOT NULL | ||
source_id | text | NOT NULL | ||
source_event_id | uuid | NOT NULL | ||
source_event_type | text | NOT NULL | ||
occurred_at | timestamptz | NOT NULL | ||
recorded_at | timestamptz | NOT NULL | ||
correlation_id | correlation_ref | NOT NULL |
Khoá chính: id
Trỏ ra (3):
assignment_id, mission_version_id→mission_assignments·ON DELETE RESTRICTcriterion_id, mission_version_id, metric→mission_criteria·ON DELETE RESTRICTreverses_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
account_id | uuid | NOT NULL | FK → app_memberships | |
app | app_code | NOT NULL | FK → app_memberships | |
mission_version_id | uuid | NOT NULL | FK → mission_versions | |
assigned_at | timestamptz | NOT NULL | ||
window_starts_at | timestamptz | NOT NULL | ||
assigned_by | uuid | FK → accounts | ||
correlation_id | correlation_ref | NOT NULL | ||
completed_at | timestamptz | |||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (3):
assigned_by→accounts·ON DELETE RESTRICTaccount_id, app→app_memberships·ON DELETE RESTRICTmission_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
mission_version_id | uuid | NOT NULL | FK → mission_versions | |
metric | mission_metric | NOT NULL | ||
operator | text | NOT NULL | ||
threshold | bigint | NOT NULL | ||
created_at | timestamptz | NOT 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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
code | text | NOT NULL | ||
purpose | text | NOT NULL | ||
name | text | NOT NULL | ||
description | text | |||
created_by | uuid | NOT NULL | 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 (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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
definition_id | uuid | NOT NULL | FK → mission_definitions | |
version_no | integer | NOT NULL | ||
status | text | NOT NULL | ||
criteria_logic | text | NOT NULL | ||
effective_from | timestamptz | NOT NULL | ||
effective_to | timestamptz | |||
published_at | timestamptz | |||
published_by | uuid | FK → accounts | ||
paused_at | timestamptz | |||
paused_by | uuid | FK → accounts | ||
pause_reason | text | |||
created_by | uuid | NOT NULL | FK → accounts | |
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (4):
created_by→accounts·ON DELETE RESTRICTdefinition_id→mission_definitions·ON DELETE RESTRICTpaused_by→accounts·ON DELETE RESTRICTpublished_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
assignment_id | uuid | NOT NULL | FK → mission_contributions | |
mission_version_id | uuid | NOT NULL | FK → mission_criteria | |
criterion_id | uuid | NOT NULL | FK → mission_contributions | |
metric | mission_metric | NOT NULL | FK → mission_criteria | |
contributed_total | bigint | NOT NULL | ||
reversed_total | bigint | NOT NULL | ||
achieved | bigint | |||
contribution_count | integer | NOT NULL | ||
reversal_count | integer | NOT NULL | ||
last_contribution_id | uuid | FK → mission_contributions | ||
as_of | timestamptz | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (3):
assignment_id, mission_version_id→mission_assignments·ON DELETE RESTRICTcriterion_id, mission_version_id, metric→mission_criteria·ON DELETE RESTRICTlast_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ột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
order_id | uuid | NOT NULL | FK → orders | |
original_contribution_id | uuid | NOT NULL | FK → mission_contributions | |
assignment_id | uuid | NOT NULL | FK → mission_contributions | |
criterion_id | uuid | NOT NULL | FK → mission_contributions | |
metric | mission_metric | NOT NULL | ||
reason | text | NOT NULL | ||
outcome | text | NOT NULL | ||
original_amount | bigint | NOT NULL | ||
requested_amount | bigint | NOT NULL | ||
applied_amount | bigint | NOT NULL | ||
prior_reversed_amount | bigint | NOT NULL | ||
reversal_contribution_id | uuid | FK → mission_contributions | ||
refund_case_id | uuid | FK → refund_cases | ||
decision_id | uuid | FK → cancellation_decisions | ||
awarded_flags | text[] | NOT NULL | ||
source_event_id | uuid | NOT NULL | ||
source_event_type | text | NOT NULL | ||
correlation_id | correlation_ref | NOT NULL | ||
recorded_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra (5):
decision_id→cancellation_decisions·ON DELETE RESTRICTorder_id→orders·ON DELETE RESTRICToriginal_contribution_id, assignment_id, criterion_id→mission_contributions·ON DELETE RESTRICTrefund_case_id→refund_cases·ON DELETE RESTRICTreversal_contribution_id, assignment_id, criterion_id→mission_contributions·ON DELETE RESTRICT
Được trỏ tới: không có