# TiDB Partner Portal — Full Specification

## Overview

The TiDB Partner Portal is a web application for TiDB/PingCap cloud partners and resellers. It provides deal registration, agreement management, support ticketing, and program administration — modeled after AWS Partner Central, Microsoft Partner Center, and Google Cloud Partner Advantage.

**Project:** [github.com/henriqueleandro-arch/partner-portal](https://github.com/henriqueleandro-arch/partner-portal)

---

## Tech Stack

| Layer | Technology |
|-------|-----------|
| Frontend | React 18 + Vite + Tailwind CSS |
| Backend | Node.js + Express |
| Database | TiDB Cloud (MySQL-compatible) |
| Auth (MVP) | JWT (bcrypt + jsonwebtoken) |
| Auth (Production) | AWS Cognito (email/SMS verification, MFA) |
| Email | Amazon SES |
| SMS/OTP | Amazon SNS |
| File Storage | Amazon S3 |
| Hosting | AWS EC2 (MVP), ECS/Fargate (production) |
| CI/CD | GitHub Actions |
| DNS/SSL | Route 53 + ACM |
| CDN | CloudFront |
| Monitoring | CloudWatch + Grafana |
| CAPTCHA | Google reCAPTCHA v3 or AWS WAF CAPTCHA |

---

## Architecture

```
┌─────────────────────────────────────────────────────────┐
│                     CloudFront CDN                       │
│                    (SSL termination)                     │
└──────────────┬──────────────────────┬────────────────────┘
               │                      │
        Static Assets            API Requests
               │                      │
    ┌──────────▼──────────┐  ┌────────▼────────────┐
    │    S3 Bucket         │  │   ALB / EC2          │
    │  (React Frontend)    │  │  (Express Backend)   │
    └─────────────────────┘  └────────┬────────────┘
                                      │
                              ┌───────▼────────┐
                              │  TiDB Cloud     │
                              │  (Essential)    │
                              └────────────────┘
```

---

## Feature Specification

### Phase 1 — Core Foundation (MVP) ✅ Built

#### 1. Partner Registration & Login

**Registration Flow:**
- Partner fills out form: first name, last name, work email, password, company name, company domain, phone
- Password hashed with bcrypt (cost factor 10)
- JWT token returned on successful registration
- Welcome notification created automatically
- Email verification flag set (mock in MVP, real via SES/Cognito in production)

**Login Flow:**
- Email + password authentication
- bcrypt comparison against stored hash
- JWT token with payload: `{ userId, email, role }`
- Token expiry: 24 hours (configurable via `JWT_EXPIRES_IN`)

**Endpoints:**
| Method | Path | Auth | Description |
|--------|------|------|-------------|
| POST | /api/auth/register | Public | Register new partner |
| POST | /api/auth/login | Public | Login, returns JWT |
| GET | /api/auth/me | JWT | Current user profile |

#### 2. Agreement Management

Partners must review and digitally accept program agreements before fully participating in the partner program.

**Agreement Types (seeded):**
- TiDB Partner Program Agreement (v1.0)
- TiDB Cloud Reseller Agreement (v1.0)
- Mutual Non-Disclosure Agreement (v1.0)

**Acceptance Flow:**
- Partner views list of active agreements with acceptance status
- Clicks to open full agreement text in a modal
- Clicks "I Accept This Agreement" to record acceptance
- Acceptance logged with timestamp and IP address
- Cannot accept same agreement twice (unique constraint)

**Endpoints:**
| Method | Path | Auth | Description |
|--------|------|------|-------------|
| GET | /api/agreements | JWT | List all active agreements with acceptance status |
| GET | /api/agreements/:id | JWT | Get single agreement with full content |
| POST | /api/agreements/:id/accept | JWT | Record acceptance |

#### 3. Opportunity / Deal Registration

Partners register customer opportunities for deal protection and co-sell support.

**Deal Lifecycle:**
```
draft → submitted → approved → won
                  ↘ lost    ↗ lost
```

**Deal Fields:**
- Deal title (required)
- Customer name (required)
- Customer email
- Description
- Product interest: TiDB Cloud, TiDB Dedicated, TiDB Serverless, TiShift
- Estimated value (decimal)
- Currency: USD, EUR, GBP, MXN, BRL
- Status (managed by lifecycle)
- Admin notes (admin only)

**Access Control:**
- Partners see only their own deals
- Partners can edit only draft deals
- Partners can submit drafts for approval
- Admins see all deals across all partners
- Admins can approve, reject, or mark deals as won
- Status transitions trigger notifications

**Endpoints:**
| Method | Path | Auth | Description |
|--------|------|------|-------------|
| GET | /api/deals | JWT | List deals (scoped by role) |
| GET | /api/deals/:id | JWT | Single deal detail |
| POST | /api/deals | JWT | Create deal (status: draft) |
| PUT | /api/deals/:id | JWT | Update deal |
| PUT | /api/deals/:id/submit | JWT | Submit for approval |
| PUT | /api/deals/:id/approve | Admin | Approve deal |
| PUT | /api/deals/:id/reject | Admin | Reject deal |
| PUT | /api/deals/:id/won | Admin | Mark as won |

#### 4. Partner Dashboard

Role-aware dashboard with summary statistics, quick actions, and notifications.

**Partner View:**
- Total deals count
- Deals by status (draft, submitted, approved, won, lost)
- Agreement acceptance progress (accepted/total)
- Open support tickets count
- Recent notifications

**Admin View:**
- Total deals count
- Pending approvals count
- Total registered partners
- Open support tickets count
- Recent notifications

**Endpoints:**
| Method | Path | Auth | Description |
|--------|------|------|-------------|
| GET | /api/dashboard/summary | JWT | Aggregated stats |
| GET | /api/dashboard/notifications | JWT | Recent notifications |
| PUT | /api/dashboard/notifications/:id/read | JWT | Mark as read |

#### 5. User & Role Management

**Roles:**
- `admin` — PingCap internal staff, full access
- `partner` — External partner organization members

**Features:**
- Admin: view all users, change roles, invite users
- Partner: view team members (same company), invite team members
- Invited users get a temporary password (in MVP; production uses email invite flow)

**Endpoints:**
| Method | Path | Auth | Description |
|--------|------|------|-------------|
| GET | /api/users | Admin | List all users |
| GET | /api/users/team | JWT | List same-company users |
| POST | /api/users/invite | JWT | Invite team member |
| PUT | /api/users/:id/role | Admin | Change user role |

#### 6. Support Ticketing

Partners can create and track support requests. Admins manage ticket lifecycle and respond.

**Ticket Fields:**
- Subject (required)
- Description (required)
- Priority: low, medium, high, critical
- Status: open, in_progress, resolved, closed
- Admin response

**Ticket Lifecycle:**
```
open → in_progress → resolved → closed
     ↘ closed
```

**Endpoints:**
| Method | Path | Auth | Description |
|--------|------|------|-------------|
| GET | /api/tickets | JWT | List tickets (scoped by role) |
| GET | /api/tickets/:id | JWT | Single ticket |
| POST | /api/tickets | JWT | Create ticket |
| PUT | /api/tickets/:id/status | Admin | Update status + respond |

---

### Phase 2 — Growth & Enablement (Planned)

#### 7. Partner Tiers
- Tier progression: Registered → Silver → Gold → Premier
- Based on: certifications earned, revenue generated, customer success stories
- Automated tier calculation based on partner activity
- Tier-specific benefits unlocked at each level
- Annual review cycle

#### 8. Lead Distribution
- PingCap sales team pushes qualified leads to eligible partners
- Accept/reject workflow with 14-day SLA timer
- Lead-to-deal conversion tracking
- Geographic and product-based lead routing
- Partner capacity and specialization matching

#### 9. Training & Certification Tracking
- Integration with TiDB Academy / training platform
- Track certifications per partner employee
- Link employee learning profiles to partner organization
- Certification requirements for tier progression
- Training completion dashboards

#### 10. Reporting & Analytics
- Revenue dashboards (pipeline value, closed revenue)
- Deal pipeline visualization (funnel charts)
- Win rate analytics by partner, product, region
- Customer usage metrics (TiDB Cloud consumption)
- Exportable reports (CSV, PDF)
- Scheduled report delivery via email

#### 11. Co-Sell Pipeline
- Joint opportunity management between PingCap sales and partner
- Shared pipeline visibility
- Co-sell status tracking
- Internal notes and collaboration tools
- Integration with PingCap CRM (Salesforce)

---

### Phase 3 — Advanced (Planned)

#### 12. MDF / Incentives Management
- Marketing Development Fund (MDF) request workflow
- Budget allocation per partner per quarter
- Proof-of-execution submission
- Claims management and reimbursement tracking
- Activity categories: demand gen, events, digital marketing
- 50% cost-sharing model (configurable)

#### 13. Marketplace Integration
- Partners list joint solutions on TiDB marketplace
- Solution publishing workflow with review/approval
- Customer-facing partner solution directory
- Usage tracking and revenue attribution

#### 14. API & CRM Integration
- REST API for partner systems integration
- Pre-built CRM connectors (Salesforce, HubSpot)
- Webhook notifications for deal status changes
- OAuth 2.0 authentication for API access
- API sandbox environment for testing
- Rate limiting and usage quotas

#### 15. Partner Directory
- Public-facing searchable directory of certified partners
- Filterable by: region, specialization, tier, product expertise
- Partner profile pages with capabilities and case studies
- Customer referral request flow

#### 16. Co-branded Marketing Assets
- Campaign templates and collateral library
- Logo usage guidelines and brand assets
- Co-branded content generation tools
- Marketing campaign tracking and ROI measurement
- Digital asset management with version control

---

## Database Schema

### Tables

#### users
| Column | Type | Description |
|--------|------|-------------|
| user_id | INT AUTO_INCREMENT PK | Unique identifier |
| email | VARCHAR(255) UNIQUE | Login email |
| password_hash | VARCHAR(255) | bcrypt hash |
| first_name | VARCHAR(100) | First name |
| last_name | VARCHAR(100) | Last name |
| role | ENUM('admin','partner') | User role |
| company_name | VARCHAR(255) | Organization name |
| company_domain | VARCHAR(255) | Company website |
| phone | VARCHAR(50) | Phone number |
| email_verified | TINYINT(1) | Verification status |
| invited_by | INT | Inviter user_id |
| created_at | TIMESTAMP | Registration date |
| updated_at | TIMESTAMP | Last update |

#### agreements
| Column | Type | Description |
|--------|------|-------------|
| agreement_id | INT AUTO_INCREMENT PK | Unique identifier |
| title | VARCHAR(255) | Agreement name |
| slug | VARCHAR(100) UNIQUE | URL-friendly identifier |
| content | TEXT | Full legal text |
| version | VARCHAR(20) | Version number |
| is_active | TINYINT(1) | Active flag |
| created_at | TIMESTAMP | Creation date |

#### agreement_acceptances
| Column | Type | Description |
|--------|------|-------------|
| acceptance_id | INT AUTO_INCREMENT PK | Unique identifier |
| agreement_id | INT FK | Agreement reference |
| user_id | INT FK | User who accepted |
| accepted_at | TIMESTAMP | Acceptance date |
| ip_address | VARCHAR(45) | Client IP |

#### deals
| Column | Type | Description |
|--------|------|-------------|
| deal_id | INT AUTO_INCREMENT PK | Unique identifier |
| user_id | INT FK | Registering partner |
| company_name | VARCHAR(255) | Partner company |
| customer_name | VARCHAR(255) | Customer name |
| customer_email | VARCHAR(255) | Customer contact |
| deal_title | VARCHAR(255) | Deal name |
| description | TEXT | Deal details |
| product_interest | VARCHAR(100) | TiDB product |
| estimated_value | DECIMAL(15,2) | Deal value |
| currency | VARCHAR(3) | Currency code |
| status | ENUM('draft','submitted','approved','won','lost') | Lifecycle status |
| submitted_at | TIMESTAMP | Submission date |
| decided_at | TIMESTAMP | Approval/rejection date |
| admin_notes | TEXT | Admin feedback |
| created_at | TIMESTAMP | Creation date |
| updated_at | TIMESTAMP | Last update |

#### tickets
| Column | Type | Description |
|--------|------|-------------|
| ticket_id | INT AUTO_INCREMENT PK | Unique identifier |
| user_id | INT FK | Ticket creator |
| subject | VARCHAR(255) | Ticket subject |
| description | TEXT | Issue details |
| priority | ENUM('low','medium','high','critical') | Priority level |
| status | ENUM('open','in_progress','resolved','closed') | Ticket status |
| admin_response | TEXT | Admin reply |
| created_at | TIMESTAMP | Creation date |
| updated_at | TIMESTAMP | Last update |

#### notifications
| Column | Type | Description |
|--------|------|-------------|
| notification_id | INT AUTO_INCREMENT PK | Unique identifier |
| user_id | INT FK | Recipient |
| title | VARCHAR(255) | Notification title |
| message | TEXT | Notification body |
| is_read | TINYINT(1) | Read status |
| link | VARCHAR(255) | Action URL |
| created_at | TIMESTAMP | Creation date |

---

## Authentication & Security

### MVP (Current)
- JWT tokens stored in localStorage
- bcrypt password hashing (cost factor 10)
- Role-based middleware (`requireRole('admin')`)
- CORS restricted to frontend origin
- SQL parameterized queries (injection prevention)
- Input validation on required fields

### Production (Planned)
- AWS Cognito for full auth lifecycle
- Email verification via SES
- SMS OTP via SNS
- Multi-factor authentication (MFA)
- OAuth 2.0 / OIDC for SSO
- CAPTCHA on registration and login (reCAPTCHA v3)
- Rate limiting on auth endpoints
- CSRF protection
- Helmet.js security headers
- Audit logging for all state changes

---

## Deployment

### Local Development
```bash
# Clone and install
git clone https://github.com/henriqueleandro-arch/partner-portal.git
cd partner-portal
npm run setup

# Configure
cp .env.example .env
# Edit .env with your TiDB Cloud credentials

# Run migrations
npm run db:migrate

# Start dev servers (backend :3001 + frontend :5173)
npm run dev
```

### AWS EC2 Production (Planned)
1. **EC2 Instance:** t3.medium, Amazon Linux 2023
2. **ALB:** Application Load Balancer with SSL termination
3. **PM2:** Node.js process manager
4. **Nginx:** Reverse proxy for static assets + API
5. **Route 53:** DNS management
6. **ACM:** SSL certificate provisioning
7. **S3 + CloudFront:** Frontend static hosting + CDN

### CI/CD Pipeline (Planned)
```
PR opened → lint + test + build
PR merged to main → deploy to staging
Manual approval → deploy to production
```
- GitHub Actions workflows
- Docker containerized builds
- Environment-specific configuration
- Rollback capability

---

## Implementation Questions & Decisions

| Question | Decision |
|----------|----------|
| AWS EC2 deployment | t3.medium with ALB, PM2, Nginx |
| Email/mobile verification | AWS Cognito (handles full flow), SES for transactional email |
| Which AWS account? | Dedicated account under AWS Organization (dev/staging/prod) |
| GitHub repo | github.com/henriqueleandro-arch/partner-portal (private) |
| CI/CD | GitHub Actions (lint → test → build → deploy) |
| CAPTCHA | Google reCAPTCHA v3 (invisible, score-based) |
| TiDB Cloud tier | Essential for production, Starter for dev/staging |
| Domain | Subdomain of existing domain (e.g., partners.pingcap.com), Route 53 for DNS |

---

## Research: Industry Partner Portal Benchmarks

### AWS Partner Central
- ACE Pipeline Manager for co-sell opportunities
- AI-powered deal sizing with MRR forecasts
- Channel Partner Private Offers (CPPO) for marketplace
- Three service tiers: Select, Advanced, Premier ($2,500/year)
- Partner Funding Portal for MDF management
- Full REST API suite with EventBridge integration
- Automated Specialization validation agents

### Microsoft Partner Center
- Solutions Partner designations across 6 solution areas
- Partner Capability Score (Performance + Skilling + Customer Success)
- Co-sell with Microsoft via Referrals workspace
- GDAP for granular delegated admin privileges
- Commercial Marketplace publishing (SaaS, VMs, containers)
- CRM connectors for Salesforce and Dynamics 365
- Microsoft Graph API for async billing

### Google Cloud Partner Advantage
- Three engagement models: Sell, Service, Build
- Deal Registration with 50%+ win rate improvement
- Partner Sales Console with billing and reporting
- Cloud Channel API for resale automation
- Partner Certification Kickstart (PCK) program
- Marketplace Channel Private Offers
- Transitioning to Google Cloud Partner Network (Q1 2026)

---

## Demo Accounts

| Role | Email | Password |
|------|-------|----------|
| Admin | admin@pingcap.com | admin123 |
| Partner | partner@acme.com | partner123 |

---

## Project Structure

```
partner-portal/
├── package.json                # Monorepo root (concurrently)
├── .gitignore
├── .env                        # Environment config (not committed)
├── SPEC.md                     # This file
├── database/
│   ├── 001_schema.sql          # Table definitions
│   └── 002_seed.sql            # Demo data
├── backend/
│   ├── package.json
│   ├── server.js               # Express app entry point
│   ├── db.js                   # TiDB connection pool
│   ├── migrate.js              # SQL migration runner
│   ├── middleware/
│   │   ├── auth.js             # JWT authentication
│   │   └── rbac.js             # Role-based access control
│   └── routes/
│       ├── auth.routes.js      # Register, login, profile
│       ├── agreements.routes.js # Agreement CRUD + acceptance
│       ├── deals.routes.js     # Deal registration lifecycle
│       ├── dashboard.routes.js # Stats + notifications
│       ├── tickets.routes.js   # Support ticket management
│       └── users.routes.js     # User + team management
└── frontend/
    ├── package.json
    ├── vite.config.js           # Vite + API proxy
    ├── tailwind.config.js       # PingCap brand colors
    ├── postcss.config.js
    ├── index.html
    └── src/
        ├── main.jsx
        ├── App.jsx              # Router + protected routes
        ├── index.css            # Tailwind + Inter font
        ├── api/
        │   └── client.js        # Axios instance + JWT interceptor
        ├── context/
        │   └── AuthContext.jsx   # Auth state management
        ├── components/
        │   └── Layout.jsx       # Sidebar + main content shell
        └── pages/
            ├── Login.jsx
            ├── Register.jsx
            ├── Dashboard.jsx
            ├── Deals.jsx
            ├── DealForm.jsx
            ├── Agreements.jsx
            ├── Tickets.jsx
            ├── TicketForm.jsx
            └── Users.jsx
```