Skip to content

SPEC.md

Latest commit

 

History

History
288 lines (256 loc) · 9.39 KB

SPEC.md

File metadata and controls

288 lines (256 loc) · 9.39 KB
Raw

Everflow Clone — MVP Spec

Build a fully functional affiliate/partner marketing platform inspired by Everflow. Single deployable Node.js app with Express backend + HTML/CSS/JS frontend.

Tech Stack

  • Backend: Node.js + Express + better-sqlite3
  • Frontend: Server-rendered HTML with vanilla JS, Tailwind CSS (CDN)
  • Auth: Session-based (express-session + SQLite store)
  • Database: SQLite (single file, zero config)

Database Schema

users

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  name TEXT NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('admin', 'affiliate', 'advertiser')),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'pending', 'blocked')),
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

offers

CREATE TABLE offers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  description TEXT,
  advertiser_id INTEGER REFERENCES users(id),
  url TEXT NOT NULL,            -- destination URL
  payout_type TEXT NOT NULL CHECK (payout_type IN ('cpa', 'cpc', 'cpl', 'revshare')),
  payout_amount REAL NOT NULL DEFAULT 0,
  revenue_amount REAL NOT NULL DEFAULT 0,
  category TEXT,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'expired')),
  cap_daily INTEGER,            -- daily conversion cap
  cap_total INTEGER,            -- total conversion cap
  start_date TEXT,
  end_date TEXT,
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

affiliates (extends users with affiliate-specific data)

CREATE TABLE affiliate_profiles (
  user_id INTEGER PRIMARY KEY REFERENCES users(id),
  company TEXT,
  website TEXT,
  traffic_sources TEXT,         -- JSON array
  tier TEXT DEFAULT 'standard',
  approved_at TEXT,
  notes TEXT
);

offer_affiliates (which affiliates can run which offers)

CREATE TABLE offer_affiliates (
  offer_id INTEGER NOT NULL REFERENCES offers(id),
  affiliate_id INTEGER NOT NULL REFERENCES users(id),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
  custom_payout REAL,
  created_at TEXT DEFAULT (datetime('now')),
  PRIMARY KEY (offer_id, affiliate_id)
);

clicks

CREATE TABLE clicks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  offer_id INTEGER NOT NULL REFERENCES offers(id),
  affiliate_id INTEGER NOT NULL REFERENCES users(id),
  click_id TEXT UNIQUE NOT NULL,    -- unique tracking ID
  ip_address TEXT,
  user_agent TEXT,
  referer TEXT,
  sub1 TEXT, sub2 TEXT, sub3 TEXT, sub4 TEXT, sub5 TEXT,  -- sub-ID tracking
  country TEXT,
  device_type TEXT,
  converted INTEGER DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX idx_clicks_offer ON clicks(offer_id);
CREATE INDEX idx_clicks_affiliate ON clicks(affiliate_id);
CREATE INDEX idx_clicks_click_id ON clicks(click_id);
CREATE INDEX idx_clicks_created ON clicks(created_at);

conversions

CREATE TABLE conversions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  click_id TEXT NOT NULL REFERENCES clicks(click_id),
  offer_id INTEGER NOT NULL REFERENCES offers(id),
  affiliate_id INTEGER NOT NULL REFERENCES users(id),
  payout REAL NOT NULL,
  revenue REAL NOT NULL,
  status TEXT NOT NULL DEFAULT 'approved' CHECK (status IN ('approved', 'pending', 'rejected')),
  transaction_id TEXT,           -- advertiser's order/txn ID
  created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX idx_conv_offer ON conversions(offer_id);
CREATE INDEX idx_conv_affiliate ON conversions(affiliate_id);
CREATE INDEX idx_conv_created ON conversions(created_at);

tracking_links

CREATE TABLE tracking_links (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  offer_id INTEGER NOT NULL REFERENCES offers(id),
  affiliate_id INTEGER NOT NULL REFERENCES users(id),
  tracking_code TEXT UNIQUE NOT NULL,
  custom_url TEXT,
  created_at TEXT DEFAULT (datetime('now'))
);

API Endpoints

Auth

  • POST /api/auth/login — { email, password } → session
  • POST /api/auth/register — { email, password, name, role }
  • POST /api/auth/logout
  • GET /api/auth/me — current user

Offers (admin/advertiser)

  • GET /api/offers — list (with filters: status, category, search)
  • POST /api/offers — create
  • GET /api/offers/:id — detail
  • PUT /api/offers/:id — update
  • DELETE /api/offers/:id — soft delete (set status=expired)

Affiliates (admin)

  • GET /api/affiliates — list
  • GET /api/affiliates/:id — detail + stats
  • PUT /api/affiliates/:id/approve — approve affiliate
  • PUT /api/affiliates/:id/block — block affiliate

Offer Applications (affiliate)

  • POST /api/offers/:id/apply — affiliate applies to run offer
  • GET /api/my/offers — affiliate's approved offers

Tracking

  • GET /track/:tracking_code — click tracking endpoint (redirects to offer URL)
    • Records click with IP, UA, referer, subs, country (GeoIP lookup via header)
    • Generates unique click_id
    • 302 redirects to offer URL with click_id appended

Conversions

  • GET /api/postback — conversion postback endpoint
    • Params: click_id, txn_id (optional), payout (optional override)
    • Validates click exists, records conversion, updates click.converted=1
  • POST /api/conversions — manual conversion entry (admin)
  • GET /api/conversions — list with filters

Tracking Links

  • POST /api/tracking-links — generate link for offer+affiliate
  • GET /api/tracking-links — list for current user

Reporting

  • GET /api/reports/overview — dashboard totals (clicks, conversions, revenue, payout, profit, CR%)
  • GET /api/reports/by-offer — grouped by offer (with date range)
  • GET /api/reports/by-affiliate — grouped by affiliate (with date range)
  • GET /api/reports/by-date — daily breakdown (with date range)
  • GET /api/reports/by-country — grouped by country

Frontend Pages

Shared Layout

  • Sidebar navigation (dark theme)
  • Top bar with user name, role badge, logout
  • Responsive (mobile-friendly)

Dashboard (/)

  • KPI cards: Total Clicks, Conversions, Revenue, Payout, Profit, CR%
  • Line chart: clicks + conversions over last 30 days (Chart.js)
  • Top 5 offers table
  • Top 5 affiliates table
  • Recent conversions list

Offers (/offers)

  • Filterable/searchable table
  • Create/edit modal or page
  • Status toggle (active/paused)
  • Per-offer stats (clicks, conversions, CR%, revenue)

Offer Detail (/offers/:id)

  • Stats cards
  • Affiliate list (who's running this offer)
  • Tracking link generator
  • Conversion log

Affiliates (/affiliates) — admin only

  • Table with name, company, status, stats
  • Approve/block actions
  • Detail view with per-affiliate breakdown

Affiliate Portal (/portal) — affiliate role

  • Available offers to apply for
  • My approved offers with tracking links
  • My stats (clicks, conversions, earnings)

Conversions (/conversions)

  • Filterable table (by offer, affiliate, status, date range)
  • Approve/reject actions (admin)

Reports (/reports)

  • Date range picker
  • Tabs: By Offer, By Affiliate, By Date, By Country
  • Charts + tables
  • CSV export button

Settings (/settings) — admin

  • Postback URL configuration
  • Default payout settings
  • Company name/branding

Seed Data

Include seed data with:

  • 1 admin user (admin@everflow.local / admin123)
  • 3 sample advertisers
  • 5 sample affiliates (2 approved, 2 pending, 1 blocked)
  • 10 sample offers across categories
  • 500+ sample clicks + 50+ conversions (spread across last 30 days)
  • Pre-generated tracking links

File Structure

/
├── package.json
├── server.js                 # Express app entry point
├── src/
│   ├── db.js                 # SQLite setup + schema + seed
│   ├── auth.js               # Auth middleware + routes
│   ├── routes/
│   │   ├── offers.js
│   │   ├── affiliates.js
│   │   ├── tracking.js
│   │   ├── conversions.js
│   │   ├── reports.js
│   │   └── settings.js
│   └── middleware/
│       ├── auth.js           # requireAuth, requireRole
│       └── cors.js
├── public/
│   ├── css/
│   │   └── app.css           # Custom styles (Tailwind via CDN)
│   ├── js/
│   │   ├── app.js            # Shared JS (fetch helpers, sidebar)
│   │   ├── dashboard.js      # Dashboard charts
│   │   ├── offers.js         # Offer CRUD
│   │   ├── affiliates.js
│   │   ├── conversions.js
│   │   └── reports.js        # Report charts + export
│   └── index.html            # SPA shell (or multi-page)
├── views/                    # HTML templates
│   ├── layout.html
│   ├── login.html
│   ├── dashboard.html
│   ├── offers.html
│   ├── offer-detail.html
│   ├── affiliates.html
│   ├── conversions.html
│   ├── reports.html
│   └── settings.html
└── README.md

Key Implementation Notes

  1. Use better-sqlite3 (sync API, fast, no async overhead)
  2. Passwords hashed with bcrypt
  3. Session stored in SQLite (connect-sqlite3)
  4. Click tracking endpoint must be FAST — minimal processing, async logging
  5. Charts via Chart.js (CDN)
  6. Tailwind CSS via CDN (no build step)
  7. Express serves both API and static files
  8. Single npm install && node server.js to run
  9. Seed data auto-generated on first run if DB is empty
  10. All dates in ISO 8601 format