# AffiliateHub — Database ER Design (Step 1)

32 tables. Grouped by domain below, with relationships and the reasoning behind
a few non-obvious design choices.

## 1. Access control
```
roles ─┬─< permission_role >─┬─ permissions
       │
       └─< users
```
- `roles`: guest isn't stored (unauthenticated), so rows are typically
  `affiliate`, `admin`, `super_admin`. Extra roles (e.g. `moderator`) can be
  added without a migration.
- `permissions` + `permission_role`: granular capabilities (`manage-products`,
  `approve-withdrawals`, ...) assigned per role, so admin can later add a
  "support staff" role with a narrower permission set.

## 2. Users & referral tree
```
users ─┬─ role_id → roles
       └─ referred_by → users.id (self-referential, nullable)
```
- `referred_by` gives you the referral tree directly: `User::referrer()` /
  `User::referrals()` (a hasMany back on the same FK) walk it either
  direction without a separate closure table. Fine at this scale; if you
  need deep multi-level tree queries later, a nested-set or closure table
  can be layered on top.
- `pending_earnings` / `available_balance` / `lifetime_earnings` are **cached
  columns**, not the source of truth. They're recomputed from `commissions`
  and `withdrawals` on every state change (approve, pay, reject) via a
  model observer — this keeps dashboard reads O(1) instead of re-summing
  thousands of commission rows per page load.

## 3. Catalog
```
categories ─(self parent_id, nullable)
brands
tags ─< product_tag >─ products ─┬─ category_id → categories
                                  ├─ brand_id → brands (nullable)
                                  ├─< product_images
                                  └─< product_reviews ─ user_id → users
```
- `categories.parent_id` self-references itself for subcategories, one level
  is enough for most catalogs but the design allows arbitrary depth.
- `products.specifications` is a JSON column rather than a separate
  `product_specs` table — spec sets vary wildly per product/category and a
  rigid EAV table adds joins for little benefit at this scale.

## 4. Affiliate tracking (the core of the platform)
```
users ─< affiliate_links >─ products
                │
                ├─< clicks
                │      │
                │      └─(1:1 optional)→ conversions
                │
                └─< conversions ─┬─ user_id → users (who gets credit)
                                  └─ order_id → orders (nullable)

orders ─┬─ product_id → products
        ├─ affiliate_link_id → affiliate_links (nullable)
        └─ coupon_id → coupons (nullable)

conversions ─< commissions ─ user_id → users
```
This is the part worth explaining carefully:

- **`affiliate_links`**: one row per (user, product) pair — a user gets a
  stable short `code` per product rather than a random code per click. The
  public route is `/go/{code}`.
- **`clicks`**: every hit on `/go/{code}` logs a row and drops a
  `tracking_token` cookie. `converted` flips to true if a conversion later
  cites this click.
- **`orders`**: represents a sale event as reported by the merchant (webhook,
  postback URL, or manual admin entry for offline/manual programs). Kept
  separate from `conversions` because not every order is affiliate-driven,
  and not every conversion needs an `orders` row (e.g. a merchant that only
  sends a final commission amount, not a full order object).
- **`conversions`**: the attribution event — ties a `click` (nullable, in
  case attribution comes from a raw postback without click data) to the
  `affiliate_link` and credited `user`, at a point-in-time `sale_amount` and
  computed `commission_amount`. Status starts `pending` and becomes
  `approved`/`rejected` after admin (or auto-approval rules) review it.
- **`commissions`**: deliberately a separate table from `conversions`, one
  row per *payable* event. Reasons: (1) a single conversion could be split
  into an adjustment/reversal later — refunds create a second, negative
  commission row rather than mutating history; (2) manual bonuses/adjustments
  from admin need a commission row with no conversion behind them
  (`conversion_id` is nullable for exactly this).
- **`coupons`**: optionally tied to a `product_id` or `category_id` (not
  both required) and referenced by `orders`. Its FK is added in migration
  `000021` rather than `000015` because `orders` is created before
  `coupons` in the timeline and a forward reference isn't possible in a
  single `Schema::create`.

> **Multi-item orders**: this schema assumes one product per order, matching
> "click → single affiliate link → single product" attribution. If you later
> sell bundles/carts, add an `order_items` table and move `product_id` /
> `amount` down onto it — flagged here so it isn't a surprise later.

## 5. Payouts
```
users ─< payment_methods
users ─< withdrawals ─ payment_method_id → payment_methods
```
- `payment_methods.details` is a single encrypted JSON text column (cast with
  Laravel's `encrypted:array` cast in the model) rather than separate columns
  per payment type — bank details, a PayPal email, and a crypto wallet
  address don't share a column shape, and encrypting the whole blob is
  simpler than field-level encryption for sensitive payout info.
- `withdrawals.status`: `pending → processing → completed`, or `rejected`.
  Approving a withdrawal doesn't touch `commissions` rows directly; it's the
  event the cached `users.available_balance` observer listens for.

## 6. Content & marketing
```
banners, sliders            — homepage/section creative, no relations
pages                        — CMS (About, Terms, Privacy, FAQ, ...)
blog_categories ─< blog_posts ─ user_id → users (author)
```

## 7. Support & engagement
```
support_tickets ─ user_id → users
       └─< support_ticket_replies ─ user_id → users, is_admin_reply flag
newsletter_subscribers       — no FK, public opt-in by email
wishlists ─ user_id → users, product_id → products (unique pair)
```

## 8. Platform plumbing
```
settings          — key/value site config, grouped (general/seo/payment/email)
activity_logs     — audit trail, user_id nullable (system events)
notifications     — Laravel's standard polymorphic notifications table
personal_access_tokens — Sanctum API tokens
password_reset_tokens, sessions, cache, cache_locks, jobs, failed_jobs — framework defaults
```

---

## Why this ordering
Migrations run in filename order, so every foreign key must point at a table
created earlier. The one exception (`orders.coupon_id` → `coupons`) is solved
by deferring that single FK to its own migration after both tables exist,
rather than reordering the whole catalog/affiliate block around it.

## What's next (Step 2)
Eloquent models for all 32 tables with relationships, casts, and scopes
(e.g. `Product::featured()`, `User::affiliate()`), plus factories and seeders
so the schema above is immediately testable with realistic fake data.
