# Partner Nook — Database & Architecture Design (v1)

**Modelled on GoAffPro's actual app structure & logic.** This is the design to
approve **before** building. Nothing here is coded yet.

Scope is anchored to the client PRD (which explicitly excludes MLM, party plans,
marketplace, membership plans, compensation-plan builder, multi-store) — but the
schema is structured the way GoAffPro structures it, so those are addable later
without redesign.

---

## 1. GoAffPro app structure we are matching

**Admin panel** (merchant side) — 5 top-level sections:

| Section | Sub-pages (GoAffPro) | In our MVP |
|---|---|---|
| **Dashboard** | Program KPIs | ✅ |
| **Setup** | Look & Feel, Commissions, Groups, Coupons, Connections, Targets, Compensation Plans, Automations, Membership Plans, Form referrals, Activities | ✅ Look&Feel, Commissions, Groups, Coupons, Targets · ⏳ rest later |
| **Manage** | Affiliates, Sales, Payouts, Social Media, PayPal Payouts, Digital Assets, Bulk Tools, Affiliate Pages/Files, Influencer Seeding, Party Plans | ✅ Affiliates, Sales, Payouts, Digital Assets, Bulk Tools |
| **Grow** | AI Tools, Recruit, Marketplace, Post-Checkout Popup, Marketing, Multi-Level, Analytics | ✅ Marketing, Analytics, Recruit · ❌ MLM/Marketplace (out of scope) |
| **Settings** | General, Notifications, Payments, Extras, Integrations, Developer, Advanced, Reports | ✅ General, Notifications, Payments, Advanced, Reports |

**Affiliate portal** (affiliate side) — tabs:

| Tab | Purpose | In our MVP |
|---|---|---|
| **Home** | Stats, referral link, coupon, quick links | ✅ |
| **Marketing Tools** | Product links, creative assets/banners, share tools | ✅ (links + assets) |
| **Payments** | Balance, request payout, history, payment details, invoices | ✅ |
| **Settings** | Profile, password, notification prefs | ✅ |
| **Analytics / Transactions / Network / Shop** | advanced tabs | ⏳ Analytics + Transactions later |

---

## 2. Database schema (PostgreSQL, Prisma)

ER overview:

```
Merchant 1─1 ProgramSettings
Merchant 1─* Affiliate           Affiliate *─1 CommissionGroup
Merchant 1─* CommissionGroup
Merchant 1─* Coupon              Coupon *─1 Affiliate
Merchant 1─* CommissionRule      (scope: PRODUCT | COLLECTION | AFFILIATE | GROUP)
Merchant 1─* Reward              (SIGNUP_BONUS | TARGET | BOOSTER | RECURRING)
Merchant 1─* Order               Order *─1 Affiliate,  Order 1─* OrderItem
Merchant 1─* Click               Click *─1 Affiliate
Merchant 1─* Customer            Customer *─1 Affiliate (connection)
Affiliate 1─* Transaction        (wallet ledger)
Merchant 1─* Payout              Payout *─1 PayoutSession
Merchant 1─* SignupField
Merchant 1─* Asset               (creative media / links)
Merchant 1─* EmailTemplate
```

### 2.1 Merchant  (the store / program)
| field | type | note |
|---|---|---|
| id | cuid PK | |
| shop | string unique | myshop.myshopify.com |
| programName | string | |
| currency | string | e.g. INR |
| timezone | string | |
| logoUrl, brandColor, brandFont | string? | Look & Feel |
| portalSubdomain, customDomain | string? | portal hosting |
| active | bool | program on/off |
| createdAt, updatedAt | datetime | |

### 2.2 ProgramSettings  (1–1 with Merchant — GoAffPro "Settings")
| group | fields |
|---|---|
| Registration | registrationOpen (bool), autoApprove (bool), autoApproveDelayHrs (int), geoBlockCountries (string[]) |
| Tracking | cookieDays (int), clickToConversionDays (int), conflictResolution (`FIRST_CLICK`\|`LAST_CLICK`), conversionPreference (string[]: link/coupon order), blockSelfReferral (bool), newCustomersOnly (bool), autoVerifySales (bool) |
| Commission | commissionBase (`SUBTOTAL`\|`TOTAL`), includeShipping (bool), includeTax (bool), commissionHoldDays (int), defaultCommissionType (`PERCENT`\|`FLAT`), defaultCommissionValue (decimal) |
| Payout | minPayout (decimal), paymentTermDays (int), defaultPaymentMethod (enum) |
| advanced | Json — overflow for rarely-used toggles |

### 2.3 Affiliate
| field | type | note |
|---|---|---|
| id | cuid PK | |
| merchantId | FK | |
| name, email | string | unique per merchant |
| passwordHash | string? | portal login |
| phone, socialHandle | string? | |
| status | `PENDING`\|`ACTIVE`\|`REJECTED`\|`BLOCKED` | |
| emailVerified | bool | |
| groupId | FK? | CommissionGroup |
| referralCode | string unique | token in referral link |
| referralLinkCustom | string? | vanity slug |
| commissionType, commissionValue | enum?, decimal? | per-affiliate override |
| minPayoutOverride, holdDaysOverride | decimal?, int? | |
| paymentMethod | enum? | PAYPAL/UPI/BANK/... |
| paymentDetails | Json? | |
| balanceAvailable, balancePending, balanceLifetime | decimal | wallet |
| tags | string[] | |
| privateNote | string? | admin-only |
| signupData | Json? | custom fields |
| affiliateManagerId | FK? | |
| createdAt, updatedAt | | |

### 2.4 CommissionGroup
id, merchantId, name, isDefault(bool), commissionType, commissionValue, badge, welcomeTemplateId?, createdAt

### 2.5 CommissionRule  (product / collection / affiliate / group specific rates)
id, merchantId, scope(`PRODUCT`\|`COLLECTION`\|`AFFILIATE`\|`GROUP`), targetId(string), commissionType, commissionValue, priority(int), active(bool)

### 2.6 Coupon
| field | type | note |
|---|---|---|
| id, merchantId, affiliateId | | |
| code | string unique | e.g. SARA10 |
| kind | `REFERRAL`\|`PERSONAL` | |
| discountType | `PERCENT`\|`FIXED`\|`FREE_SHIP` | |
| discountValue | decimal | |
| usageLimit, usedCount | int | |
| newCustomersOnly, minOrderValue | bool, decimal? | |
| shopifyDiscountId | string? | link to real Shopify discount |
| active | bool | |

### 2.7 Click  (referral link visit)
id, merchantId, affiliateId, referralCode, cookieId, ip, userAgent, landingUrl, createdAt

### 2.8 Customer  (for new-vs-returning + connection/recurring)
id, merchantId, shopifyCustomerId?, email, firstOrderAt?, connectedAffiliateId?(FK), connectionExpiresAt?

### 2.9 Order  (= a referred Sale)
| field | type | note |
|---|---|---|
| id, merchantId, affiliateId | | |
| shopifyOrderId, orderNumber | string | |
| customerEmail, isNewCustomer | string, bool | |
| subtotal, shipping, tax, discount, total | decimal | |
| conversionSource | `REFERRAL_LINK`\|`COUPON`\|`MANUAL` | |
| couponCode | string? | |
| commissionAmount | decimal | |
| saleStatus | `PENDING`\|`APPROVED`\|`REJECTED`\|`REFUNDED` | verification |
| commissionStatus | `HELD`\|`AVAILABLE`\|`PAID`\|`REVERSED` | hold-time lifecycle |
| holdUntil | datetime? | now + commissionHoldDays |
| createdAt, processedAt | | |

### 2.10 OrderItem  (enables product/collection-level commission)
id, orderId, productId, variantId, title, price, qty, commissionAmount

### 2.11 Reward
id, merchantId, type(`SIGNUP_BONUS`\|`TARGET`\|`BOOSTER`\|`RECURRING`), config(Json), active

### 2.12 Transaction  (affiliate wallet ledger — audit trail of money)
id, affiliateId, type(`COMMISSION`\|`BONUS`\|`ADJUSTMENT`\|`PAYOUT`\|`REVERSAL`), amount(±), balanceAfter, orderId?, payoutId?, note, createdAt

### 2.13 Payout  &  2.14 PayoutSession
- **PayoutSession**: id, merchantId, method, totalAmount, affiliateCount, status(`OPEN`\|`PROCESSING`\|`COMPLETED`), createdAt
- **Payout**: id, merchantId, affiliateId, sessionId?, amount, method(`PAYPAL`\|`MANUAL`\|`STORE_CREDIT`\|`UPI`\|`BANK`), status(`REQUESTED`\|`PROCESSING`\|`PAID`\|`FAILED`), reference, invoiceUrl, requestedByAffiliate(bool), paidAt, createdAt

### 2.15 Support tables
- **SignupField**: id, merchantId, label, key, type(text/number/select), required, options[], sortOrder
- **Asset** (creative media): id, merchantId, type(`IMAGE`\|`LINK`\|`ARTICLE`\|`FILE`), title, url, category, createdAt
- **EmailTemplate**: id, merchantId, type(`WELCOME`\|`APPROVAL`\|`REJECTION`\|`NEW_SALE`\|`PAYOUT`\|`ADMIN_NEW_AFFILIATE`\|`ADMIN_PAYOUT_REQUEST`), subject, body, enabled

---

## 3. Core business logic (the "engine")

### 3.1 Commission resolution — priority order (highest wins)
```
1. CommissionRule scope=PRODUCT      (per line item)
2. CommissionRule scope=COLLECTION   (per line item)
3. Affiliate.commissionOverride
4. CommissionRule scope=AFFILIATE
5. CommissionGroup rate (via affiliate.groupId)
6. Merchant default (ProgramSettings.defaultCommission*)
```
Base amount = subtotal or total (per settings), ± shipping ± tax per toggles.
Then apply modifiers/boosters (order value, first-time customer, target hit).

### 3.2 Attribution (when an order arrives)
```
1. Coupon match  → order.couponCode maps to a Coupon → affiliate  (source=COUPON)
2. Click cookie  → cookieId within cookieDays & clickToConversionDays → affiliate (source=REFERRAL_LINK)
3. Conflict      → conflictResolution (FIRST_CLICK|LAST_CLICK), conversionPreference order
4. Guards        → blockSelfReferral, newCustomersOnly
```

### 3.3 Commission lifecycle
```
order created ─► saleStatus=PENDING, commissionStatus=HELD, holdUntil=now+holdDays
   │  (autoVerifySales? → APPROVED immediately, else admin Accept/Reject)
   ▼
APPROVED + holdUntil passed ─► commissionStatus=AVAILABLE ─► balanceAvailable += amount
   │
refund/cancel ─► saleStatus=REFUNDED, commissionStatus=REVERSED, balance -= amount (Transaction REVERSAL)
   ▼
payout ─► Payout(PAID) ─► balanceAvailable -= amount, Transaction PAYOUT
```

---

## 4. Backend architecture — NestJS modules

```
apps/api/src/
├── prisma/                  # PrismaService (global)
├── auth/                    # admin auth + affiliate portal auth (JWT)
├── program/                 # merchant + ProgramSettings (Setup + Settings)
├── affiliates/              # CRUD, approve/reject/block, tags, notes, bulk import
├── groups/                  # commission groups
├── commissions/            # CommissionRule CRUD + calculation engine (§3.1)
├── coupons/                 # coupon CRUD + Shopify discount sync
├── tracking/                # clicks, referral redirect, attribution engine (§3.2)
├── sales/                   # orders, verification (accept/reject), refunds
├── rewards/                 # signup bonus, targets, boosters
├── payouts/                 # sessions, payouts, wallet transactions, invoices
├── notifications/           # email templates + sending (SendGrid/Mailgun)
├── analytics/               # program KPIs, leaderboard, reports
├── assets/                  # creative media / links
├── shopify/                 # webhooks (orders/refunds), discount API, (Trackier later)
└── common/                  # guards, dto, decimal utils, config
```

Each module = controller + service (+ dto). Calculation & attribution live in
`commissions` and `tracking` services and are unit-testable in isolation.

---

## 5. Frontend architecture — Next.js (mirrors GoAffPro nav)

```
apps/web/app/
├── (admin)/admin/
│   ├── page.tsx                     # Dashboard (KPIs)
│   ├── setup/
│   │   ├── look-and-feel/           # logo, colors, portal
│   │   ├── commissions/             # default + rules + product/collection
│   │   ├── groups/                  # commission groups
│   │   ├── coupons/                 # coupon config + list
│   │   └── targets/                 # rewards/bonuses/targets
│   ├── manage/
│   │   ├── affiliates/              # list, filters, detail, approve/reject, bulk
│   │   ├── affiliates/[id]/         # profile, commission, coupon, tags, wallet
│   │   ├── sales/                   # all sales, accept/reject, sources
│   │   ├── payouts/                 # pending, sessions, history, mark-paid
│   │   └── assets/                  # digital assets
│   ├── grow/
│   │   ├── marketing/               # bulk email, campaigns
│   │   ├── recruit/                 # invite, marketplace listing
│   │   └── analytics/               # charts, leaderboard, reports
│   └── settings/
│       ├── general/                 # registration, tracking, sales settings
│       ├── notifications/           # email templates on/off
│       ├── payments/                # methods, min payout, hold time, terms
│       └── advanced/                # attribution, self-referral, fraud
├── (portal)/portal/
│   ├── page.tsx                     # Home (stats, referral link, coupon)
│   ├── marketing/                   # product links, assets, share
│   ├── payments/                    # balance, request payout, history, details
│   ├── analytics/                   # own performance
│   └── settings/                    # profile, password, notifications
├── (public)/
│   ├── signup/                      # public branded signup (slim/classic/modern)
│   ├── login/                       # affiliate login
│   └── store/                       # demo store (until real Shopify)
└── lib/                             # api client, auth, formatting
```

Shared UI kit (buttons, cards, tables, stat tiles, badges) in `components/` so
every screen reads as one system — fixing the "inconsistent UI" problem.

---

## 6. What changes vs the current code

The current `apps/api` + `apps/web` proved the **loop works**. This design keeps
the working engine but restructures into the module/route layout above and
**expands the schema** (settings, rules, coupons table, wallet transactions,
payout sessions, rewards, assets, email templates, order items). We migrate the
DB with a new Prisma migration; existing demo data is reseeded.

---

## 7. Build order (after you approve this design)

1. **Schema + migration** (all tables above) + seed
2. **Settings + Commissions engine** (Setup → the "10-min setup" GoAffPro shows)
3. **Affiliates + Sales + attribution** (Manage)
4. **Coupons + Shopify discount sync**
5. **Payouts + wallet + invoices**
6. **Portal** (Home, Marketing, Payments, Settings)
7. **Rewards, Analytics, Notifications**
8. **Shopify webhooks / Trackier** (replace demo simulate)
9. **Shared UI kit + polish pass** (consistent, professional UI)

---

*Approve this (or tell me what to change — scope, tables, naming) and I'll build
to it, module by module.*