Giao diện
pipoint / point
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 đè.
10 bảng. Quay lại pipoint.
point_requests
P3-15. Write-ahead PiPoint command records (idempotency key, snapshot, outcome, recovery). Not a balance or ledger: PiPoint alone owns Pending/Available/history. No delete; UNKNOWN is not terminal.
public.point_requests · 40 cột · 27 check · 1 unique · 10 index · 13 policy · 13 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
request_id | text | NOT NULL | ||
operation | text | NOT NULL | ||
source_event_id | text | NOT NULL | ||
account_id | uuid | NOT NULL | FK → point_account_links | |
link_id | uuid | FK → point_account_links | ||
role | text | |||
points | integer | |||
target_bucket | text | |||
base_points | integer | |||
tier_multiplier | numeric | |||
reward_rule_ref | text | |||
reason_code | text | |||
original_request_id | uuid | FK → point_requests | ||
related_order_id | uuid | |||
related_transaction_id | uuid | |||
adjust_direction | text | |||
requested_by | uuid | FK → accounts | ||
approved_by | uuid | FK → accounts | ||
approved_at | timestamptz | |||
rate_snapshot_vnd | numeric | |||
rate_policy_version | text | |||
payload_hash | bytea | NOT NULL | ||
business_date | date | NOT NULL | ||
status | text | NOT NULL | ||
provider_ref | text | |||
provider_recorded_at | timestamptz | |||
result_code | text | |||
requested_at | timestamptz | |||
reconciliation_status | text | NOT NULL | ||
check_count | integer | NOT NULL | ||
next_check_at | timestamptz | |||
escalated_at | timestamptz | |||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL | ||
base_unit | text | FU-715. Unit of base_points (see point_reward_actions.base_unit); with VND the points are checked against rate_snapshot_vnd in exact arithmetic. | ||
result_details | jsonb | PPI-P5-04 (D-02). PiPoint insufficient-points details: {"available": n}, the balance PiPoint reported when it refused the debit. Set once with result_code INSUFFICIENT_POINTS; null otherwise. | ||
provider_body | jsonb | PPI-P5-11. The walletPlatform-ledger body {action, payload} sent the first time; every resend of the request sends it unchanged (same requestKey = same content). Fixed at insert; null when nothing is sent or for rows before 20261230053300. |
Khoá chính: id
Trỏ ra (5):
account_id→accounts·ON DELETE RESTRICTapproved_by→accounts·ON DELETE RESTRICTlink_id, account_id→point_account_links·ON DELETE RESTRICToriginal_request_id→point_requests·ON DELETE RESTRICTrequested_by→accounts·ON DELETE RESTRICT
Được trỏ tới (12): order_points_excess_refunds, payment_obligations, point_adjustments, point_reconciliation_items, point_reconciliation_items, point_report_versions, point_request_events, point_requests, rank_debits, rank_lots, refund_branches, refund_branches
point_account_links
P3-15. Link only: one PIDEAL identity <-> one external PiPoint account. No balance/ledger here or anywhere in PIDEAL. Identity columns are immutable; PPI-P3-12: a link ends (ended_at / end_reason) when its uid is released, and the uniques hold over live links only.
public.point_account_links · 11 cột · 4 check · 1 unique · 5 index · 3 policy · 5 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
account_id | uuid | NOT NULL | FK → accounts | |
verified_identity_ref | text | NOT NULL | ||
external_account_ref | text | NOT NULL | ||
linked_at | timestamptz | NOT NULL | ||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL | ||
ended_at | timestamptz | PPI-P3-12 (DEC-PPI-OWN02-UNLINK-WALLET). When the wallet link ended (its PiPoint uid was released); null = live. Set once; the row stays as history. | ||
end_reason | text | PPI-P3-12. Why the link ended: SUPPORT_UNLINK (PPI-P3-08) | ACCOUNT_DELETED (PPI-P3-09); null while live. Set once. |
Khoá chính: id
Trỏ ra (1):
account_id→accounts·ON DELETE RESTRICT
Được trỏ tới (3): point_adjustments, point_requests, point_reward_actions
point_report_versions
P3-15 table, P3-23 process. Monthly PiPoint report SNAPSHOT versions (DRAFT/SUPERSEDED/SETTLED, SETTLED immutable) and CORRECTION rows for late data after SETTLED. Not a balance or ledger; offline marker only.
public.point_report_versions · 35 cột · 8 check · 0 unique · 5 index · 1 policy · 4 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
kind | text | NOT NULL | ||
report_month | date | NOT NULL | ||
timezone | text | NOT NULL | ||
rate_basis | text | NOT NULL | ||
report_version | integer | |||
status | text | |||
cutoff_at | timestamptz | |||
selection_hash | bytea | |||
request_count | integer | |||
net_issued_points | bigint | |||
net_issued_vnd | numeric | |||
net_redeemed_points | bigint | |||
net_redeemed_vnd | numeric | |||
adjust_credit_points | bigint | |||
adjust_credit_vnd | numeric | |||
adjust_debit_points | bigint | |||
adjust_debit_vnd | numeric | |||
open_discrepancy_count | integer | |||
settled_by | uuid | FK → accounts | ||
settled_at | timestamptz | |||
settlement_note | text | |||
corrects_report_id | uuid | FK → point_report_versions | ||
point_request_id | uuid | FK → point_requests | ||
metric | text | |||
signed_points | bigint | |||
signed_vnd | numeric | |||
rate_snapshot_vnd | numeric | |||
original_recorded_at | timestamptz | |||
reason | text | |||
correlation_id | correlation_ref | 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 (4):
corrects_report_id→point_report_versions·ON DELETE RESTRICTcreated_by→accounts·ON DELETE RESTRICTpoint_request_id→point_requests·ON DELETE RESTRICTsettled_by→accounts·ON DELETE RESTRICT
Được trỏ tới (2): point_report_version_lines, point_report_versions
point_adjustments
P3-21. Maker/checker ADJUST proposals (immutable payload + decision). Not a balance or ledger; the ADJUST itself is the point_requests row with request_id = adj.<id>. No delete; approved rows never change.
public.point_adjustments · 30 cột · 15 check · 2 unique · 8 index · 1 policy · 9 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
account_id | uuid | NOT NULL | FK → point_account_links | |
link_id | uuid | NOT NULL | FK → point_account_links | |
role | text | NOT NULL | ||
direction | text | NOT NULL | ||
points | integer | NOT NULL | ||
reason_code | text | NOT NULL | ||
reason_text | text | NOT NULL | ||
evidence_media_ids | uuid[] | NOT NULL | ||
recon_item_id | uuid | FK → point_reconciliation_items | ||
corrects_request_id | uuid | FK → point_requests | ||
related_order_id | uuid | |||
rate_snapshot_vnd | numeric | NOT NULL | ||
rate_policy_version | text | NOT NULL | ||
payload_hash | bytea | NOT NULL | ||
status | text | NOT NULL | ||
requested_by | uuid | FK → accounts | P3-21 maker (operations, F-06). NULL = a RETURN_RECALL slip the system wrote at a completed return (PPI-P7-05). | |
requested_at | timestamptz | NOT NULL | ||
decided_by | uuid | FK → accounts | ||
decided_at | timestamptz | |||
decision_note | text | |||
business_date | date | |||
request_id | text | |||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL | ||
origin | text | NOT NULL | PPI-P5-08. MANUAL (requestPointAdjust), RETURN_RECALL (the PENDING slip a completed return writes, E-09a, PPI-P7-05) or FAILED_DELIVERY_RECALL (#479 A: the Available reward share of a Mua định kỳ cycle not delivered). | |
recall | jsonb | PPI-P5-08 (E-09b/c). Recall basis of a RETURN_RECALL slip: cycleNo, returnedValueVnd, orderValueVnd and the lines [{component ORDER_REWARD | ONLINE_BONUS | SPIN | AFFILIATE, availablePoints, points}]; immutable. | ||
recall_key | text | PPI-P5-08. return:<order>:<cycle|all>:<account>:<role> (one per return and recipient); #479 A failed:<order>:<cycle|stop>:<component> (one per failed cycle, or the stop, and reward). |
Khoá chính: id
Trỏ ra (6):
account_id→accounts·ON DELETE RESTRICTcorrects_request_id→point_requests·ON DELETE RESTRICTdecided_by→accounts·ON DELETE RESTRICTlink_id, account_id→point_account_links·ON DELETE RESTRICTrecon_item_id→point_reconciliation_items·ON DELETE RESTRICTrequested_by→accounts·ON DELETE RESTRICT
Được trỏ tới (1): rank_adjustments
point_reconciliation_items
P3-15 table, P3-22 workflow. One discrepancy of a run; evidence immutable, handling OPEN -> IN_PROGRESS -> RESOLVED. Never edits point_requests or PiPoint data; fixes go through same-request replay or ADJUST.
public.point_reconciliation_items · 27 cột · 12 check · 1 unique · 5 index · 3 policy · 5 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
run_id | uuid | NOT NULL | FK → point_reconciliation_runs | |
discrepancy_type | text | NOT NULL | ||
item_key | text | NOT NULL | ||
point_request_id | uuid | FK → point_requests | ||
external_request_ref | text | |||
provider_txn_ref | text | |||
provider_recorded_at | timestamptz | |||
expected_points | integer | |||
actual_points | integer | |||
expected_vnd | numeric | |||
actual_vnd | numeric | |||
evidence | jsonb | NOT NULL | ||
remote_payload_hash | bytea | |||
status | text | NOT NULL | ||
owner_account_id | uuid | FK → accounts | ||
note | text | |||
retry_count | integer | NOT NULL | ||
last_retry_at | timestamptz | |||
resolved_by | uuid | FK → accounts | ||
resolved_at | timestamptz | |||
resolution_note | text | |||
adjustment_request_id | uuid | FK → point_requests | ||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (5):
adjustment_request_id→point_requests·ON DELETE RESTRICTowner_account_id→accounts·ON DELETE RESTRICTpoint_request_id→point_requests·ON DELETE RESTRICTresolved_by→accounts·ON DELETE RESTRICTrun_id→point_reconciliation_runs·ON DELETE RESTRICT
Được trỏ tới (1): point_adjustments
point_reconciliation_runs
P3-15 table, P3-22 job. One row per VN business date and rerun; immutable after COMPLETE/INCOMPLETE. Evidence and totals only: no balance, no tolerance, no ledger write.
public.point_reconciliation_runs · 22 cột · 10 check · 1 unique · 3 index · 4 policy · 4 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
run_date_vn | date | NOT NULL | ||
run_version | integer | NOT NULL | ||
timezone | text | NOT NULL | ||
window_start | timestamptz | NOT NULL | ||
window_end | timestamptz | NOT NULL | ||
trigger_kind | text | NOT NULL | ||
requested_by | uuid | FK → accounts | ||
status | text | NOT NULL | ||
started_at | timestamptz | NOT NULL | ||
finished_at | timestamptz | |||
provider_snapshot_ref | text | |||
remote_dataset_hash | bytea | |||
local_totals | jsonb | |||
remote_totals | jsonb | |||
error_code | text | |||
lease_owner | text | |||
lease_expires_at | timestamptz | |||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (1):
requested_by→accounts·ON DELETE RESTRICT
Được trỏ tới (1): point_reconciliation_items
point_report_version_lines
P3-23. Immutable per-type, per-rate totals of one point_report_versions SNAPSHOT. Not a balance or ledger.
public.point_report_version_lines · 10 cột · 5 check · 0 unique · 2 index · 1 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
report_id | uuid | NOT NULL | FK → point_report_versions | |
operation | text | NOT NULL | ||
adjust_direction | text | |||
rate_snapshot_vnd | numeric | |||
request_count | integer | NOT NULL | ||
points | bigint | NOT NULL | ||
vnd | numeric | |||
created_at | timestamptz | NOT NULL | ||
target_bucket | text | PPI-P5-03 E-08. ISSUE only: PENDING (PIDEAL-only, D-01 = A) or AVAILABLE (credit at PiPoint); NULL on lines built before. |
Khoá chính: id
Trỏ ra (1):
report_id→point_report_versions·ON DELETE RESTRICT
Được trỏ tới: không có
point_request_events
P3-15. APPEND_ONLY observations of a point request (write-ahead steps, provider answers, lookups). Not a point ledger: no balance, no running total. Update/delete/truncate rejected.
public.point_request_events · 13 cột · 7 check · 1 unique · 3 index · 10 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
point_request_id | uuid | NOT NULL | FK → point_requests | |
source_type | text | NOT NULL | ||
source_event_id | text | NOT NULL | ||
observed | text | NOT NULL | ||
status_after | text | NOT NULL | ||
request_version | integer | NOT NULL | ||
provider_ref | text | |||
result_code | text | |||
provider_payload_hash | bytea | |||
occurred_at | timestamptz | NOT NULL | ||
recorded_at | timestamptz | NOT NULL | ||
correlation_id | correlation_ref | NOT NULL |
Khoá chính: id
Trỏ ra (1):
point_request_id→point_requests·ON DELETE RESTRICT
Được trỏ tới: không có
point_reward_actions
P3-20. One owed PiPoint command per (order, reward component, operation): intent, frozen request snapshot, outcome, or a visible BLOCKED / NO_PIPOINT_ACCOUNT / SKIPPED reason. Not a balance or ledger.
public.point_reward_actions · 45 cột · 21 check · 1 unique · 7 index · 3 policy · 10 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
order_id | uuid | NOT NULL | FK → orders | |
component | text | NOT NULL | Reward component. 'ORDER_PENDING' is the stored value of the buyer's ORDER REWARD (code: ORDER_REWARD, PPI-P4-04 / W-08): Pending at the confirmed deposit then RELEASE, or Available at its event (Mua chung delivery); target_bucket says which. Unrelated to PiPoint held / hold. ONLINE_REMAINING: pay-now / prepay-all bonus; AFFILIATE_ORDER: Affiliate reward of the link owner; SPIN: Lucky Wheel reward. | |
action | text | NOT NULL | ||
recipient_account_id | uuid | NOT NULL | FK → accounts | |
role | text | NOT NULL | ||
link_id | uuid | FK → point_account_links | ||
target_bucket | text | |||
campaign_type | text | NOT NULL | ||
trigger_event_id | uuid | NOT NULL | ||
trigger_event_type | text | NOT NULL | ||
eligible_at | timestamptz | NOT NULL | ||
cod_collected_event_id | uuid | |||
cod_collected_at | timestamptz | |||
cod_ref | text | |||
fulfilled_event_id | uuid | |||
fulfilled_at | timestamptz | |||
online_paid_event_id | uuid | U19-P2-20. The paid milestone of a RELEASE: the order.paid milestone event (or the online remaining / zero remaining fact, the same milestone). | ||
online_paid_at | timestamptz | |||
end_state | text | |||
status | text | NOT NULL | ||
reason_code | text | |||
undecided_ref | text | |||
request_id | text | |||
points | integer | |||
base_points | integer | |||
tier_multiplier | numeric | |||
reward_rule_ref | text | |||
rate_snapshot_vnd | numeric | |||
rate_policy_version | text | |||
original_request_id | text | |||
business_date | date | |||
bound_at | timestamptz | |||
provider_ref | text | |||
provider_recorded_at | timestamptz | |||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL | ||
attribution_assignment_id | uuid | FK → attribution_assignments | ||
base_vnd | bigint | P4-13. AFFILIATE_ORDER only: reward base at the deposit (V0 / committed cycles; Recurring = one cycle). The rate and amount are undecided (GAP-125); this is not a balance or ledger. | ||
base_basis | text | |||
base_unit | text | FU-715. Unit of base_points: NULL/POINTS = whole base points, VND = eligible paid cash VND (SPEC-01/SPEC-08). | ||
reward_evidence | jsonb | FU-715. Inputs of the amount evaluation (base kind and VND, rate version, Tier multiplier ref, points); set once. | ||
cycle_no | smallint | F2 DEC-AFFILIATE-ROUND-TIMING. AFFILIATE_ORDER only: the delivered cycle (1 for a Group Buy order, 1..12 for a Recurring cycle). NULL = a row of the earlier deposit-time path, finished as it is (no new row is added to such an order). |
Khoá chính: id
Trỏ ra (4):
attribution_assignment_id→attribution_assignments·ON DELETE RESTRICTlink_id→point_account_links·ON DELETE RESTRICTorder_id→orders·ON DELETE RESTRICTrecipient_account_id→accounts·ON DELETE RESTRICT
Được trỏ tới: không có
point_reward_partial_cancels
FU-SHORTAGE2, U19-R31. One owed partial CANCEL_ISSUE of the Pending order reward per (partial shortage case, order) (proportional share, rounded down, cumulative) or per (Recurring close outcome, order) (the reward recalculated on the kept products); frozen request snapshot, outcome. Not a balance or ledger (PPT-BRULE-01).
public.point_reward_partial_cancels · 23 cột · 9 check · 5 unique · 7 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 | |
shortage_case_id | uuid | FK → stock_shortage_cases | ||
component | text | NOT NULL | ORDER_PENDING (the buyer's order reward) or, for a failed-delivery / stop row only (#479 A decision 4), AFFILIATE_ORDER (the link owner's Affiliate reward, recipient of its ISSUE). | |
recipient_account_id | uuid | NOT NULL | FK → accounts | |
cancelled_value_vnd | bigint | NOT NULL | ||
order_value_vnd | bigint | NOT NULL | ||
status | text | NOT NULL | ||
reason_code | text | |||
points | integer | |||
request_id | text | |||
original_request_id | text | |||
rate_snapshot_vnd | numeric | |||
rate_policy_version | text | |||
business_date | date | |||
provider_ref | text | |||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL | ||
close_outcome_id | uuid | FK → campaign_outcomes | ||
failed_delivery_id | uuid | FK → order_cycle_failed_deliveries | #479 A. The cycle settled as a failed delivery (CONTINUED) whose price-ratio share of the Pending order reward this row cancels; cancelled_value_vnd = Σ value of the failed cycles so far, order_value_vnd = Σ cycle values. | |
stop_order_id | uuid | FK → orders | #479 A (owner 11/11 decision 3). The Mua định kỳ order stopped for failed deliveries whose lost share (the cycles the stop cancelled + the failed ones, cumulative) this row cancels from the Pending reward; the delivered keep theirs. |
Khoá chính: id
Trỏ ra (6):
close_outcome_id→campaign_outcomes·ON DELETE RESTRICTfailed_delivery_id→order_cycle_failed_deliveries·ON DELETE RESTRICTorder_id→orders·ON DELETE RESTRICTrecipient_account_id→accounts·ON DELETE RESTRICTshortage_case_id→stock_shortage_cases·ON DELETE RESTRICTstop_order_id→orders·ON DELETE RESTRICT
Được trỏ tới: không có