Affiliate System Development for an E-commerce shop

To build an affiliate tracking system for my existing Svelte E-commerce Website, connected and verified with the Database Budget: $170 (Fixed Price) There are two Database sheets involved in this project: • Database 1: Affiliate Applications - master affiliate profile sheet storing all affiliate profiles, settings and running totals. • Database 2: Affiliate Sales Orders - records all individual sales orders made via affiliate links Step 1 - Affiliate Link & Verification When a user visits the website via an affiliate link: Example: https://shop.mydomainname.com?refid=affiliateID (Domain link will be sent once the job is accepted.) The affiliateID will be verified against the Database Affiliate Applications sheet to check if it is a valid and active affiliate. 1. ✅ Valid affiliate → fire an affiliate cookie (minimum 1 day, maximum 30 days) A valid affiliate will carry the affiliateID and pass it through when a sales order is successfully purchased, which will be used in the next step for setting up the Affiliate Sales Orders record. 2. ❌ Invalid affiliate → redirect back to shop.mydomainname.com If the verification returns any empty, invalid or error data from the sheet, the user will be redirected back to the homepage at shop.mydomainname.com. Any errors within the affiliate function must not affect the live website in any way. Affiliate Applications Sheet (Database 1) will be sent once the job is accepted. Step 2 - Affiliate Cookie: The affiliate function runs entirely in the backend, no changes to the frontend UI for shop page. Cookie tracks the affiliate visit throughout the session (minimum 1 day, maximum 30 days) Step 3 - Order Record Duplication (Affiliate Sales Orders): When a purchase order is made via an affiliate link and the payment status is SUCCESS, the order record should be duplicated from the existing Online Order database with filtered data/columns only to Database 2 - Affiliate Sales Orders, tagged with the affiliateID and relevant data and commission rate from that affiliate link. If the order is not made via an affiliate link, the Affiliate Sales Orders sheet should not duplicate any order records. Affiliate Sales Orders Sheet (Database 2) will be sent once the job is accepted. Step 4 - Email Notification: After the order record is successfully duplicated into the Affiliate Sales Orders sheet, an email notification should be sent to the affiliate's registered email (retrieved from the Affiliate Applications sheet) via Resend. The email should be sent as a Congratulations - Sale Notification with the following details: 1. Order Summary — A customer purchased: [order summary] 2. Total Sales Amount — the order total for that purchase 3. Coupon Used — the coupon discount amount applied. If none, display "None" 4.. Delivery Fee — the delivery fee amount applied. If none, display "None" 5. Commission Earned — the calculated commission amount earned by the affiliate 6. Billplz Payment ID — the payment reference ID from Billplz 7. View affiliate payout summary at: https://affiliatehub.mydomainname.com/[affiliateID] Step 5 - Retrieve & Display Affiliate Payout Information: Retrieve data from Database 2 - Affiliate Sales Orders and display it on a unique affiliate page for each individual affiliate: https://affiliatehub.mydomainname.com/[affiliateID] Each affiliate can access their own unique page to view: 1. Total Sales 2. Order summary 3. Payout amount 4. Payout status 5. Payment date 6. And other relevant information At the bottom of the table, the Total Commission Earned will be displayed, calculated from all records in the Affiliate Sales Orders sheet. The Payout Status from the Database will be used to determine the outstanding balance, if the status is "Paid", the paid amount will be moved from the pending to approved column in Database 1 to show the status, payout and remaining balance to affiliate on the individual affiliate dashboard page: https://affiliatehub.mydomainname.com/[affiliateID] Auto-sync between Database 1 & Database 2: The auto-sync between Database 1 (Affiliate Applications) and Database 2 (Affiliate Sales Orders) will be triggered on every page load of the affiliate dashboard page: https://affiliatehub.mydomainname.com/[affiliateID] When the page loads, the system will: 1. Retrieve all records from Database 2 for the matching affiliateID 2. Recalculate and update the Total Payout (RM) Approved, Pending column in Database 1 3. Reflect the latest payout status, commission totals and remaining balance on the affiliate dashboard page This ensures the affiliate always sees the most up-to-date data every time they visit their dashboard. Page visibility: 1. Add (meta name="robots", content="noindex, nofollow") to hide the affiliate dashboard page from Google search results 2. Add Disallow: affiliatehub /affiliateID/ to robots.txt to block the entire /affiliateID/ page directory from search engine crawling Click Tracking - displayed on the Affiliate Dashboard page: Every time a unique user clicks an affiliate link, the click should be recorded server-side using a 24-hour cookie to track each user as only 1 unique click. How it works technically: 1. When a user clicks an affiliate link, the system checks if a 24-hour click tracking cookie exists for that user 2. If no cookie exists → the click is counted as a new unique click → increment totalClicks by 1 in Database 1 (Affiliate Applications) and record the unique click in Database 2 (Affiliate Sales Orders) 3. If cookie already exists → the click is not counted again — the user has already been tracked within the last 24 hours 4. The 24-hour cookie is set upon the first click and expires after 24 hours, after which the user will be counted as a new unique click again 5. Implemente API Rate Limit Handling. Step 6 - Security & Fraud Prevention: To protect against cookie stuffing and bot traffic, the following security checks must be implemented on the server side: 1. Referrer header check, block affiliate cookie from firing if there is no valid referrer (prevents hidden image cookie stuffing) 2. sec-fetch-mode check, block requests where sec-fetch-mode is no-cors (prevents iframe cookie stuffing) 3. User agent check, block requests from bots or suspicious user agents 4. Payout status default as "Pending", all commissions require manual review and approval before payout (Pending → Approved → Paid) 5. Any security errors or blocked requests must redirect back to shop.mydomainname.com and must not affect the live website in any way Tech Stack: 1. SvelteKit & TypeScript 2. Database API 3. Resend API 4. Billplz Payment Gateway 5. Tailwind CSS Requirements for Applicant: 1. Experience with SvelteKit and TypeScript 2. Experience with REST API integrations 3. Familiar with cookie handling and server-side functions 4. Experience with database integration and data manipulation 5. Knowledge of security best practices for affiliate systems 6. Familiar with Tailwind CSS 7. Clean, readable and well-documented code Please review the requirements and feel free to ask any questions. Looking forward to working with you!