Skip to content

campaign / recurring ​

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

8 bảng. Quay lại campaign.

recurring_baskets ​

P2-09. A buyer's draft selection (UA-11 cart + UA-12 term) from one Recurring campaign; not an Order, not a reservation. OWN_ROW for the APP_USER buyer.

public.recurring_baskets · 19 cột · 9 check · 2 unique · 6 index · 3 policy · 7 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
campaign_iduuidNOT NULLFK → campaign_delivery_windows
campaign_typetextNOT NULLFK → campaigns
owner_account_iduuidNOT NULLFK → accounts
term_cyclesintegerFK → campaign_term_packagesSố kỳ of the chosen package (FK with package_no). G2S-P0-03: null while no package is chosen (a draft saved from G2-02 / G2-03, BR-MD-05); RecurringBasket.termCycles reads 0 then.
policy_version_iduuidNOT NULLFK → campaign_policy_versions
statustextNOT NULL
discarded_attimestamptz
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL
order_iduuidFK → ordersP2-13. The Recurring order that committed this basket (status COMMITTED); written by the order insert only.
committed_attimestamptz
package_nointegerFK → campaign_term_packages
delivery_window_nointegerFK → campaign_delivery_windows
skip_maxinteger
package_reward_valueinteger
closed_notice_attimestamptzG2S-P0-03 (BR-MD-05, S3). When the campaign close discarded this draft; RecurringBasket.closedNotice is read once from it (G2S-P1-03). DISCARDED rows only.
closed_notice_read_attimestamptzG2S-P1-03 (BR-MD-05, S3). When myRecurringBasket returned the close notice of this basket (closedNotice true once).

Khoá chính: id

Trỏ ra (6):

  • campaign_id, campaign_type → campaigns · ON DELETE RESTRICT
  • order_id → orders · ON DELETE RESTRICT
  • owner_account_id → accounts · ON DELETE RESTRICT
  • campaign_id, package_no, term_cycles → campaign_term_packages · ON DELETE RESTRICT
  • policy_version_id, campaign_id → campaign_policy_versions · ON DELETE RESTRICT
  • campaign_id, delivery_window_no → campaign_delivery_windows · ON DELETE RESTRICT

Được trỏ tới (4): order_quotes, recurring_basket_cycle_lines, recurring_basket_lines, recurring_skip_requests

recurring_cycles ​

P2-06 shape only (P2-09 extends): Recurring delivery schedule, one row per period 1..12; no skip/close state.

public.recurring_cycles · 14 cột · 4 check · 2 unique · 3 index · 4 policy · 4 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
campaign_iduuidNOT NULLFK → campaigns
campaign_typetextNOT NULLFK → campaigns
cycle_nointegerNOT NULL
delivers_fromtimestamptzNOT NULL
delivers_totimestamptzNOT NULL
created_byuuidNOT NULLFK → accounts
updated_byuuidNOT NULLFK → accounts
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
skip_cutoff_attimestamptzP2-09, retired by FU-RECCLOSE: always NULL. The cutoff of a cycle is delivers_from minus the approved version (campaign.skip_cutoff_version()).
delivers_from_vndateP2-09. Vietnam calendar date (UTC+07:00) of delivers_from; generated.
delivers_to_vndateP2-09. Vietnam calendar date (UTC+07:00) of delivers_to; generated.
delivery_monthdateU19-P1-05. Month of the period (day 1) = campaigns.delivery_start_month + (cycle_no - 1) months; set by rebuild_cycles.

Khoá chính: id

Trỏ ra (3):

  • campaign_id, campaign_type → campaigns · ON DELETE CASCADE
  • created_by → accounts · ON DELETE RESTRICT
  • updated_by → accounts · ON DELETE RESTRICT

Được trỏ tới (4): order_cycles, order_periods, recurring_skip_requests, shipments

recurring_forfeit_reviews ​

FU-RECCLOSE. Maker/checker review of a Recurring cycle forfeit (DEC-CP-SKIP: an Operations approver who is not the maker). Records the decision only: no order, payment or point effect (undecided, GAP-132).

public.recurring_forfeit_reviews · 15 cột · 6 check · 1 unique · 4 index · 0 policy · 3 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
order_iduuidNOT NULLFK → order_periods
cycle_nointegerNOT NULLFK → order_periods
campaign_iduuidNOT NULLFK → campaigns
statustextNOT NULL
versionintegerNOT NULL
reasontextNOT NULL
payload_hashbyteaNOT NULL
proposed_byuuidNOT NULLFK → accounts
proposed_attimestamptzNOT NULL
decided_byuuidFK → accounts
decided_attimestamptz
decision_notetext
source_idtextNOT NULL
correlation_idcorrelation_ref

Khoá chính: id

Trỏ ra (5):

  • campaign_id → campaigns · ON DELETE RESTRICT
  • decided_by → accounts · ON DELETE RESTRICT
  • order_id → orders · ON DELETE RESTRICT
  • order_id, cycle_no → order_periods · ON DELETE RESTRICT
  • proposed_by → accounts · ON DELETE RESTRICT

Được trỏ tới (2): recurring_forfeit_effects, refund_cases

recurring_basket_lines ​

P2-09. Product lines (quantity per cycle) of a DRAFT recurring basket; same campaign, one line per product.

public.recurring_basket_lines · 7 cột · 1 check · 1 unique · 3 index · 4 policy · 3 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
basket_iduuidNOT NULLFK → recurring_baskets
campaign_iduuidNOT NULLFK → campaign_products
campaign_product_iduuidNOT NULLFK → campaign_products
quantity_per_cycleintegerNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (2):

  • basket_id, campaign_id → recurring_baskets · ON DELETE RESTRICT
  • campaign_product_id, campaign_id → campaign_products · ON DELETE RESTRICT

Được trỏ tới (1): recurring_basket_cycle_lines

recurring_skip_cutoff_versions ​

FU-RECCLOSE. Approved versions of the Recurring self-skip cutoff (hours before the cycle starts), maker != checker, append-only. Empty until Operations records a value (DEC-CP-SKIP-CUTOFF is open): skip then answers policy_undecided.

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

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
version_nointegerNOT NULL
hours_before_cycleintegerNOT NULL
effective_fromtimestamptzNOT NULL
source_reftextNOT NULL
recorded_byuuidNOT NULLFK → accounts
approved_byuuidNOT NULLFK → accounts
approved_attimestamptzNOT NULL
created_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (2):

  • approved_by → accounts · ON DELETE RESTRICT
  • recorded_by → accounts · ON DELETE RESTRICT

Được trỏ tới (1): recurring_skip_requests

recurring_basket_cycle_lines ​

G2I-P2-01 (G2-06, DEC-OWN22-G2-06). Sparse per-cycle quantity of a DRAFT basket line where it differs from quantity_per_cycle; 0 = not delivered that cycle. Replaced by the save command, never updated.

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

CộtKiểuKhoáMô tả
basket_iduuidNOT NULLPK FK → recurring_basket_lines
campaign_iduuidNOT NULLFK → recurring_baskets
cycle_nointegerNOT NULLPK
campaign_product_iduuidNOT NULLPK FK → recurring_basket_lines
quantityintegerNOT NULL

Khoá chính: basket_id, cycle_no, campaign_product_id

Trỏ ra (2):

  • basket_id, campaign_id → recurring_baskets · ON DELETE RESTRICT
  • basket_id, campaign_product_id → recurring_basket_lines · ON DELETE CASCADE

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

recurring_forfeit_effects ​

FU-FORFEIT. One immutable row per APPROVED cycle forfeit review: the cycle that left the delivery plan and the stored cash and points share refunded for it (DEC-CP-FORFEIT-EFFECT, interim). No penalty or fee.

public.recurring_forfeit_effects · 10 cột · 2 check · 2 unique · 3 index · 0 policy · 3 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
review_iduuidNOT NULLFK → recurring_forfeit_reviews
order_iduuidNOT NULLFK → orders
cycle_nointegerNOT NULL
order_cycle_iduuidNOT NULLFK → order_cycles
cash_vndbigintNOT NULL
pointsintegerNOT NULL
refund_outcometextNOT NULL
rule_versiontextNOT NULL
created_attimestamptzNOT NULL

Khoá chính: id

Trỏ ra (3):

  • order_cycle_id → order_cycles · ON DELETE RESTRICT
  • order_id → orders · ON DELETE RESTRICT
  • review_id → recurring_forfeit_reviews · ON DELETE RESTRICT

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

recurring_skip_requests ​

P2-18 (shape of P2-09). One skip request of one committed period of a Recurring order with the immutable policy/n/cutoff snapshot; REQUESTED -> CANCELLED only. Rows exist only under an APPROVED skip rule. A skip is never a delivery and never cancels the commitment.

public.recurring_skip_requests · 29 cột · 10 check · 1 unique · 4 index · 1 policy · 7 trigger · RLS bật

CộtKiểuKhoáMô tả
iduuidNOT NULLPK
basket_iduuidNOT NULLFK → recurring_baskets
campaign_iduuidNOT NULLFK → campaign_policy_versions
cycle_iduuidNOT NULLFK → recurring_cycles
owner_account_iduuidNOT NULLFK → accounts
request_idtextNOT NULL
policy_version_iduuidNOT NULLFK → campaign_policy_versions
skip_rule_statustextNOT NULL
skip_rule_reftextNOT NULL
skip_max_countintegerNOT NULL
skip_cutoff_attimestamptzNOT NULL
prior_skip_countintegerNOT NULL
requested_attimestamptzNOT NULL
order_iduuidNOT NULLFK → order_periods
cycle_nointegerNOT NULLFK → order_periods
order_cycle_iduuidFK → order_cycles
statustextNOT NULL
versionintegerNOT NULL
decision_rule_codetextNOT NULL
decision_rule_versiontextNOT NULL
decided_attimestamptzNOT NULL
count_afterintegerNOT NULL
source_idtextNOT NULL
status_changed_attimestamptzNOT NULL
cancelled_attimestamptz
cancel_source_idtext
skip_cutoff_version_iduuidFK → recurring_skip_cutoff_versionsFU-RECCLOSE. The cutoff version the request was decided under (skip_cutoff_at is derived from it).
reason_codetextG2I-P1-01 D12. Why the buyer skips (design O-37B); optional at the API, null on older rows. Insert only.
reason_texttextG2I-P1-01 D12. The buyer's own words (1..200, trimmed); optional. Insert only; staff read it (cycle schedule).

Khoá chính: id

Trỏ ra (8):

  • basket_id, campaign_id → recurring_baskets · ON DELETE RESTRICT
  • cycle_id, campaign_id → recurring_cycles · ON DELETE RESTRICT
  • order_cycle_id → order_cycles · ON DELETE RESTRICT
  • order_id → orders · ON DELETE RESTRICT
  • owner_account_id → accounts · ON DELETE RESTRICT
  • order_id, cycle_no → order_periods · ON DELETE RESTRICT
  • policy_version_id, campaign_id → campaign_policy_versions · ON DELETE RESTRICT
  • skip_cutoff_version_id → recurring_skip_cutoff_versions · ON DELETE RESTRICT

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

Sinh từ ad83b0aea · 820 migration · PostgreSQL 17