Skip to content

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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
request_idtextNOT NULL
operationtextNOT NULL
source_event_idtextNOT NULL
account_iduuidNOT NULLFK → point_account_links
link_iduuidFK → point_account_links
roletext
pointsinteger
target_buckettext
base_pointsinteger
tier_multipliernumeric
reward_rule_reftext
reason_codetext
original_request_iduuidFK → point_requests
related_order_iduuid
related_transaction_iduuid
adjust_directiontext
requested_byuuidFK → accounts
approved_byuuidFK → accounts
approved_attimestamptz
rate_snapshot_vndnumeric
rate_policy_versiontext
payload_hashbyteaNOT NULL
business_datedateNOT NULL
statustextNOT NULL
provider_reftext
provider_recorded_attimestamptz
result_codetext
requested_attimestamptz
reconciliation_statustextNOT NULL
check_countintegerNOT NULL
next_check_attimestamptz
escalated_attimestamptz
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL
base_unittextFU-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_detailsjsonbPPI-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_bodyjsonbPPI-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 RESTRICT
  • approved_by → accounts · ON DELETE RESTRICT
  • link_id, account_id → point_account_links · ON DELETE RESTRICT
  • original_request_id → point_requests · ON DELETE RESTRICT
  • requested_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

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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
account_iduuidNOT NULLFK → accounts
verified_identity_reftextNOT NULL
external_account_reftextNOT NULL
linked_attimestamptzNOT NULL
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL
ended_attimestamptzPPI-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_reasontextPPI-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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
kindtextNOT NULL
report_monthdateNOT NULL
timezonetextNOT NULL
rate_basistextNOT NULL
report_versioninteger
statustext
cutoff_attimestamptz
selection_hashbytea
request_countinteger
net_issued_pointsbigint
net_issued_vndnumeric
net_redeemed_pointsbigint
net_redeemed_vndnumeric
adjust_credit_pointsbigint
adjust_credit_vndnumeric
adjust_debit_pointsbigint
adjust_debit_vndnumeric
open_discrepancy_countinteger
settled_byuuidFK → accounts
settled_attimestamptz
settlement_notetext
corrects_report_iduuidFK → point_report_versions
point_request_iduuidFK → point_requests
metrictext
signed_pointsbigint
signed_vndnumeric
rate_snapshot_vndnumeric
original_recorded_attimestamptz
reasontext
correlation_idcorrelation_refNOT NULL
created_byuuidFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (4):

  • corrects_report_id → point_report_versions · ON DELETE RESTRICT
  • created_by → accounts · ON DELETE RESTRICT
  • point_request_id → point_requests · ON DELETE RESTRICT
  • settled_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
account_iduuidNOT NULLFK → point_account_links
link_iduuidNOT NULLFK → point_account_links
roletextNOT NULL
directiontextNOT NULL
pointsintegerNOT NULL
reason_codetextNOT NULL
reason_texttextNOT NULL
evidence_media_idsuuid[]NOT NULL
recon_item_iduuidFK → point_reconciliation_items
corrects_request_iduuidFK → point_requests
related_order_iduuid
rate_snapshot_vndnumericNOT NULL
rate_policy_versiontextNOT NULL
payload_hashbyteaNOT NULL
statustextNOT NULL
requested_byuuidFK → accountsP3-21 maker (operations, F-06). NULL = a RETURN_RECALL slip the system wrote at a completed return (PPI-P7-05).
requested_attimestamptzNOT NULL
decided_byuuidFK → accounts
decided_attimestamptz
decision_notetext
business_datedate
request_idtext
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL
origintextNOT NULLPPI-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).
recalljsonbPPI-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_keytextPPI-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 RESTRICT
  • corrects_request_id → point_requests · ON DELETE RESTRICT
  • decided_by → accounts · ON DELETE RESTRICT
  • link_id, account_id → point_account_links · ON DELETE RESTRICT
  • recon_item_id → point_reconciliation_items · ON DELETE RESTRICT
  • requested_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
run_iduuidNOT NULLFK → point_reconciliation_runs
discrepancy_typetextNOT NULL
item_keytextNOT NULL
point_request_iduuidFK → point_requests
external_request_reftext
provider_txn_reftext
provider_recorded_attimestamptz
expected_pointsinteger
actual_pointsinteger
expected_vndnumeric
actual_vndnumeric
evidencejsonbNOT NULL
remote_payload_hashbytea
statustextNOT NULL
owner_account_iduuidFK → accounts
notetext
retry_countintegerNOT NULL
last_retry_attimestamptz
resolved_byuuidFK → accounts
resolved_attimestamptz
resolution_notetext
adjustment_request_iduuidFK → point_requests
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (5):

  • adjustment_request_id → point_requests · ON DELETE RESTRICT
  • owner_account_id → accounts · ON DELETE RESTRICT
  • point_request_id → point_requests · ON DELETE RESTRICT
  • resolved_by → accounts · ON DELETE RESTRICT
  • run_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
run_date_vndateNOT NULL
run_versionintegerNOT NULL
timezonetextNOT NULL
window_starttimestamptzNOT NULL
window_endtimestamptzNOT NULL
trigger_kindtextNOT NULL
requested_byuuidFK → accounts
statustextNOT NULL
started_attimestamptzNOT NULL
finished_attimestamptz
provider_snapshot_reftext
remote_dataset_hashbytea
local_totalsjsonb
remote_totalsjsonb
error_codetext
lease_ownertext
lease_expires_attimestamptz
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT 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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
report_iduuidNOT NULLFK → point_report_versions
operationtextNOT NULL
adjust_directiontext
rate_snapshot_vndnumeric
request_countintegerNOT NULL
pointsbigintNOT NULL
vndnumeric
created_attimestamptzNOT NULL
target_buckettextPPI-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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
point_request_iduuidNOT NULLFK → point_requests
source_typetextNOT NULL
source_event_idtextNOT NULL
observedtextNOT NULL
status_aftertextNOT NULL
request_versionintegerNOT NULL
provider_reftext
result_codetext
provider_payload_hashbytea
occurred_attimestamptzNOT NULL
recorded_attimestamptzNOT NULL
correlation_idcorrelation_refNOT 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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
order_iduuidNOT NULLFK → orders
componenttextNOT NULLReward 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.
actiontextNOT NULL
recipient_account_iduuidNOT NULLFK → accounts
roletextNOT NULL
link_iduuidFK → point_account_links
target_buckettext
campaign_typetextNOT NULL
trigger_event_iduuidNOT NULL
trigger_event_typetextNOT NULL
eligible_attimestamptzNOT NULL
cod_collected_event_iduuid
cod_collected_attimestamptz
cod_reftext
fulfilled_event_iduuid
fulfilled_attimestamptz
online_paid_event_iduuidU19-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_attimestamptz
end_statetext
statustextNOT NULL
reason_codetext
undecided_reftext
request_idtext
pointsinteger
base_pointsinteger
tier_multipliernumeric
reward_rule_reftext
rate_snapshot_vndnumeric
rate_policy_versiontext
original_request_idtext
business_datedate
bound_attimestamptz
provider_reftext
provider_recorded_attimestamptz
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL
attribution_assignment_iduuidFK → attribution_assignments
base_vndbigintP4-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_basistext
base_unittextFU-715. Unit of base_points: NULL/POINTS = whole base points, VND = eligible paid cash VND (SPEC-01/SPEC-08).
reward_evidencejsonbFU-715. Inputs of the amount evaluation (base kind and VND, rate version, Tier multiplier ref, points); set once.
cycle_nosmallintF2 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 RESTRICT
  • link_id → point_account_links · ON DELETE RESTRICT
  • order_id → orders · ON DELETE RESTRICT
  • recipient_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
order_iduuidNOT NULLFK → orders
shortage_case_iduuidFK → stock_shortage_cases
componenttextNOT NULLORDER_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_iduuidNOT NULLFK → accounts
cancelled_value_vndbigintNOT NULL
order_value_vndbigintNOT NULL
statustextNOT NULL
reason_codetext
pointsinteger
request_idtext
original_request_idtext
rate_snapshot_vndnumeric
rate_policy_versiontext
business_datedate
provider_reftext
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL
close_outcome_iduuidFK → campaign_outcomes
failed_delivery_iduuidFK → 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_iduuidFK → 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 RESTRICT
  • failed_delivery_id → order_cycle_failed_deliveries · ON DELETE RESTRICT
  • order_id → orders · ON DELETE RESTRICT
  • recipient_account_id → accounts · ON DELETE RESTRICT
  • shortage_case_id → stock_shortage_cases · ON DELETE RESTRICT
  • stop_order_id → orders · ON DELETE RESTRICT

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

Sinh từ ad83b0aea · 820 migration · PostgreSQL 17