# PRD Extensions — Bundle SKUs + Reviews Moderation (DRAFT)

**Status:** Draft. Not yet merged into PRD (1).md. Awaiting Ekow's sign-off.
**Prepared:** 16 May 2026
**Origin:** Q-PDP-3 (bundle SKU model absent from PRD §9) and Q-PDP-4 (reviews require staff moderation at launch).
**Targets:** PRD §6.1 (storefront), PRD §6.10 (new, reviews), PRD §6.12 (admin), PRD §9 (data model).

---

## A. Bundle SKUs

### A.1 Why this is needed

Round 2A's PDP design introduces a "Bundle" chip row beneath the variant selector — *TV only*, *+ Wall mount*, *+ Soundbar*. The PRD's product schema (`products → product_variants → prices`) does not model bundles. Without an extension, that PDP affordance has no backing data.

Two distinct bundle patterns are common in commerce and both should be supported:

| Pattern | Description | Storefront placement |
|---|---|---|
| **Fixed bundle** | A curated SKU with predefined components, sold as a single unit at a bundle price. Example: "Home Theatre Starter Pack — 55" TV + Soundbar + Wall Mount — GHS 9,499". | Listed as its own product card on PLP; PDP shows component breakdown. |
| **Configurable bundle** | A base product with optional add-ons selected at PDP time. Example: TV with optional wall mount or soundbar. | Inline chips on the parent product's PDP. |

### A.2 Data model extension (additions to PRD §9)

```sql
-- Add to products
ALTER TABLE products
  ADD COLUMN is_bundle BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN bundle_type TEXT
    CHECK (bundle_type IN ('fixed', 'configurable')),
  ADD COLUMN inventory_mode TEXT NOT NULL DEFAULT 'virtual'
    CHECK (inventory_mode IN ('virtual', 'kitted'));

-- Bundle composition
CREATE TABLE bundle_items (
  id              BIGSERIAL PRIMARY KEY,
  bundle_id       BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  component_id    BIGINT NOT NULL REFERENCES products(id),
  component_variant_id BIGINT REFERENCES product_variants(id),
    -- if null, customer picks the variant at PDP time
  quantity        INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
  is_optional     BOOLEAN NOT NULL DEFAULT false,
    -- false = always included (fixed bundle, or required component)
    -- true  = customer-selectable add-on (configurable bundle)
  display_order   INT NOT NULL DEFAULT 0,
  price_strategy  TEXT NOT NULL DEFAULT 'inherit'
    CHECK (price_strategy IN ('inherit', 'fixed', 'discount_percent', 'discount_amount')),
  price_value_minor BIGINT,
    -- meaning depends on strategy:
    --   inherit:          ignored (use component's own price)
    --   fixed:            line price for this component within this bundle
    --   discount_percent: percent off in basis points (e.g., 1500 = 15.00%)
    --   discount_amount:  amount off in pesewas
  CHECK (bundle_id != component_id)
);

CREATE INDEX idx_bundle_items_bundle ON bundle_items(bundle_id, display_order);
CREATE INDEX idx_bundle_items_component ON bundle_items(component_id);
```

### A.3 Inventory semantics

Two modes via `products.inventory_mode`:

| Mode | Behaviour | Use case |
|---|---|---|
| **virtual** (default) | Bundle has no inventory of its own. Availability = min(component_inventory / required_qty). Sale of a bundle decrements each component's `inventory_levels`. | Most configurable bundles, and fixed bundles where ops doesn't pre-pack. |
| **kitted** | Bundle has its own row in `inventory_levels`. Ops pre-packs and tracks the kitted SKU separately. Sale decrements the bundle's inventory directly; components are not auto-decremented. | Pre-assembled gift boxes, vendor-pre-packed kits. |

Reservation semantics inherit the existing 15-minute TTL (PRD §6.4) — for virtual bundles, a single bundle reservation creates N child reservations on each component atomically (rolled back together on abandonment or failure).

### A.4 Pricing semantics

Bundle line price = sum over `bundle_items` of each component's effective price (per `price_strategy`), evaluated at cart-time against the customer's current price list (B2C list price or B2B customer-specific price per PRD §6.3).

For configurable bundles, only selected optional components are summed.

Examples:
- *Fixed bundle, all components inherit*: bundle price = sum(component list prices). No bundle-level discount.
- *Fixed bundle with discount_percent = 1000 (10%) on each component*: each component contributes `price × 0.90`; final bundle = sum of discounted components.
- *Fixed bundle with one component at `fixed` strategy*: that component contributes the fixed value verbatim; others use their strategy.
- *Configurable bundle (TV + optional mount + optional soundbar)*: chips selected at PDP → those bundle_items added to the bundle line; unselected optional items contribute 0.

### A.5 Cart and order semantics

- **Cart**: a bundle appears as one `cart_item` with a child relation `cart_item_components` storing the selected components and per-component prices. Quantity edits scale all components proportionally.
- **Order**: at checkout, the bundle `cart_item` **explodes** into N `order_items`, each linked back via `parent_order_item_id`. This keeps tax computation (PRD §6.4) per-component (different products have different taxability flags), keeps fulfilment grouping intact (UI groups by `parent_order_item_id`), and means the existing `order_items` table doesn't need a new shape for bundles.
- **Invoice and order timeline**: render the bundle as a parent line with components indented; total line price is the sum of components, shown on the parent row.
- **Refunds**: per-component refunds are allowed; bundle line is treated as a group for UX but each component refund is independent in the ledger.

### A.6 B2B quote semantics (PRD §6.3 extension)

- A `quote_items` row can reference a bundle product; on quote acceptance, the bundle explodes into `order_items` per the cart/order semantics above.
- Admin can set a quote-level bundle override discount (overrides the per-bundle_item `price_strategy` for this quote only) — stored in `quote_items.metadata` JSON to avoid schema churn for an admin-side adjustment.
- Quote PDFs (PRD §10 Phase 2) render bundles in the same parent-child structure as the order timeline.

### A.7 Storefront surfacing (PRD §6.1 extension)

- **PLP**: bundles can appear as their own product cards (filterable via `is_bundle = true`). Recommend a "Bundles" virtual category and an explicit badge so customers know they're seeing a multi-item SKU.
- **PDP**:
  - For *fixed* bundles: header shows "Includes:" with a component list (image + name + qty).
  - For *configurable* bundles: optional bundle_items render as the chip row beneath the variant selector (already in Round 2A design).
  - Stock indicator computes per `inventory_mode`.
  - "Customers also bought" can include bundles; bundles can also include other products and bundles as cross-sell.

### A.8 Admin (PRD §6.12 extension)

- Bundle creation UI in admin: pick a parent product, mark `is_bundle = true`, set `bundle_type`, then add `bundle_items` rows with the four `price_strategy` options surfaced as a small wizard.
- Validation: prevent circular references (bundle A includes bundle B which includes bundle A) at write time via a recursive CTE check.

### A.9 Out of scope for v1.0

- Bundles-of-bundles (nesting beyond one level). The schema permits it (a bundle item could reference another bundle product), but UI and pricing logic only support flat bundles in v1.0. Add a constraint flag `allow_nesting BOOLEAN DEFAULT false` and validate at write.
- Subscription bundles (recurring delivery of a bundle). Non-goal per PRD §3.3.
- Customer-built custom bundles ("pick any 3 from this list at 15% off"). v1.1+ — model as a `bundle_rules` table when needed.

---

## B. Reviews Moderation

### B.1 Why this is needed

Launch policy per Ekow: **all reviews require staff moderation before publishing.** The PRD does not currently document a reviews entity at all, so the entire section is an addition. Architecture must allow flipping the policy (e.g., auto-approve verified buyers) without a redesign.

### B.2 Data model (new tables, additions to PRD §9)

```sql
CREATE TABLE reviews (
  id                BIGSERIAL PRIMARY KEY,
  product_id        BIGINT NOT NULL REFERENCES products(id),
  customer_id       BIGINT NOT NULL REFERENCES customers(id),
  order_id          BIGINT REFERENCES orders(id),
    -- nullable; if present, this is a "verified purchase" review
  rating            SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  title             TEXT,
  body              TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'pending_moderation'
    CHECK (status IN ('pending_moderation', 'approved', 'rejected', 'flagged')),
  moderated_by      BIGINT REFERENCES staff_users(id),
  moderation_notes  TEXT,
  moderated_at      TIMESTAMPTZ,
  helpful_count     INT NOT NULL DEFAULT 0,
  reported_count    INT NOT NULL DEFAULT 0,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (customer_id, product_id, order_id)
    -- one review per (customer, product, order); enforces "review your purchase, not the product abstractly"
);

CREATE INDEX idx_reviews_product_status ON reviews(product_id, status, created_at DESC);
CREATE INDEX idx_reviews_pending ON reviews(status, created_at) WHERE status = 'pending_moderation';
CREATE INDEX idx_reviews_flagged ON reviews(status, created_at) WHERE status = 'flagged';

CREATE TABLE review_reports (
  id          BIGSERIAL PRIMARY KEY,
  review_id   BIGINT NOT NULL REFERENCES reviews(id) ON DELETE CASCADE,
  reporter_id BIGINT REFERENCES customers(id),
    -- nullable: anonymous "Report" affordance allowed
  reason      TEXT NOT NULL,
    -- 'spam' | 'offensive' | 'off_topic' | 'fake' | 'other'
  notes       TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE review_moderation_policy (
  id             BIGSERIAL PRIMARY KEY,
  scope          TEXT NOT NULL CHECK (scope IN ('global', 'category', 'product')),
  scope_id       BIGINT,
    -- null when scope='global'; references categories.id or products.id otherwise
  auto_approve_verified_purchases    BOOLEAN NOT NULL DEFAULT false,
  auto_approve_minimum_rating        SMALLINT,
    -- e.g., set to 4 to auto-approve verified purchases of rating 4+
  banned_keywords                    TEXT[],
    -- words that auto-flag (or auto-reject — config flag below)
  banned_keyword_action              TEXT NOT NULL DEFAULT 'flag'
    CHECK (banned_keyword_action IN ('flag', 'reject')),
  active                             BOOLEAN NOT NULL DEFAULT true,
  created_at                         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                         TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Seed default policy
INSERT INTO review_moderation_policy (scope, auto_approve_verified_purchases, banned_keyword_action)
  VALUES ('global', false, 'flag');
```

### B.3 Moderation state machine

```
                ┌─────────────────────┐
                │ pending_moderation  │ ← (customer submits)
                └──┬──────────────┬───┘
   (admin approve) │              │ (admin reject)
                   ▼              ▼
              ┌─────────┐    ┌──────────┐
              │ approved│    │ rejected │
              └──┬──────┘    └──────────┘
   (customer report
    OR keyword hit)
                   │
                   ▼
              ┌─────────┐
              │ flagged │
              └─┬──────┬┘
   (admin       │      │ (admin reject)
    dismiss)    ▼      ▼
              ┌─────────┐  ┌──────────┐
              │ approved│  │ rejected │
              └─────────┘  └──────────┘
```

All transitions log to `audit_log` (PRD §6.7) with actor, before/after, reason.

### B.4 Eligibility to write a review

- Authenticated customer only.
- Must have at least one delivered order containing the product (`orders.status = 'delivered'` + `order_items` join), OR the system permits unverified reviews per policy (default: not permitted in v1.0).
- One review per (customer, product, order); customer can edit their own pending or approved review (edit re-sets `status = pending_moderation` for staff re-review).

### B.5 Storefront surfacing (PRD §6.1 + PDP Round 2A revision)

- PDP "Reviews" tab shows only `status = approved` reviews.
- Aggregate rating computed only from `status = approved` reviews.
- "Write a review" CTA shown if customer is eligible. On submit:
  - Customer sees: *"Thanks — your review is in moderation and will appear shortly. We'll email you when it's published."*
  - Customer's own pending review is visible to them on the PDP under a "Your pending review" section (private, not shown to other users).
- Customer can flag any approved review via "Report" affordance → creates a `review_reports` row, transitions review to `flagged`.

### B.6 Admin moderation queue (sketches for Round 4)

- New surface at `/admin/reviews/moderation`.
- Default view: pending queue, oldest first.
- Filter chips: *Pending* (default) · *Flagged* · *Recently rejected* · *All*.
- Per-row: star rating, title, body (truncated), customer name + verified-purchase badge, product thumbnail + title, submitted timestamp, action buttons (Approve · Reject · Flag).
- Bulk select for batch approve / reject with a single reason field.
- Detail drawer: full review body, customer order history (does this customer have a pattern?), prior reviews on the same product, any reports against this review.
- Required notes on rejection (free-text, audit-logged).
- Pending-queue size alert via Sentry custom event when count > 50 (configurable).
- Pusher Channels surfaces a `new_pending_review` event so staff console shows a counter badge in real time (PRD §6.9).

### B.7 Email notifications (PRD §6.9 extension)

| Trigger | Recipient | Template |
|---|---|---|
| Review submitted | Customer | "Thanks, your review is in moderation" |
| Review approved | Customer | "Your review is now live on ProcureGlobal" + link |
| Review rejected | Customer | "About your recent review" + moderator note + appeal CTA |
| Review reported | Staff (digest, hourly) | "N reviews flagged for review" |

### B.8 Policy flexibility — flipping to auto-approve

To switch policy later (e.g., auto-approve verified purchases of rating 4+):

```sql
UPDATE review_moderation_policy
  SET auto_approve_verified_purchases = true,
      auto_approve_minimum_rating = 4
  WHERE scope = 'global';
```

No code change, no redesign. Existing reviews stay in their current state; only new submissions are affected. The submission API checks the active policy before insert and sets initial `status` accordingly.

Per-category and per-product overrides via additional rows in `review_moderation_policy` (e.g., "auto-approve for the Electronics category but moderate Health & Beauty"). Resolution order at write time: product → category → global.

### B.9 Anti-abuse

- Rate limit: 1 review submission per customer per minute (Upstash Ratelimit per PRD §7.3).
- Keyword pre-filter on submission (cheap, runs in Express); a hit triggers `banned_keyword_action` (flag or reject by policy).
- IP and device fingerprint stored on submission for cross-account abuse detection (anonymous to staff unless escalated; raw IP encrypted at rest).
- Auto-flag if customer has > 3 rejected reviews in the past 90 days.

### B.10 Out of scope for v1.0

- Photo/video attachments on reviews (v1.1).
- AI-assisted moderation pre-screen (v1.1+ — would consume Anthropic or OpenAI API for first-pass classification; gated by cost analysis).
- Seller responses to reviews (v1.2 — applicable when/if marketplace functionality lands per PRD §3.3 non-goal removal).
- Translation of reviews across English/Twi/other Ghanaian languages (v1.2+).

---

## C. Adjustments to Round 2A PDP design (already-shipped)

If both extensions are approved as drafted, two small touch-ups to the existing `design-round2a-pdp.html`:

1. **Reviews tab content**: when a customer who is eligible-but-has-no-pending-review hits the tab, show the "Write a review" CTA in a pinned card at the top. When the customer has a pending review, show the *"Your pending review"* private card above the public reviews list.
2. **Bundle chip row**: today the chips show static "+ Wall mount", "+ Soundbar" with hard-coded prices. Once `bundle_items` exists, the chips render from data. No visual change.

These are non-blocking; the design stands as-is. Engineering can adopt the wiring when the data model lands.