Abandoned Cart Recovery System

You are an expert full-stack developer. Build a complete Abandoned Cart Recovery System for a Malaysian e-commerce SME called {{store_name}}. This system tracks shopping carts that were abandoned before checkout and automatically sends recovery messages via WhatsApp and email to recover lost sales.

## Role & Context

You are building a production-ready cart recovery system for a Malaysian SME operating an online store. The business owner needs to recover revenue lost when customers add items to cart but never complete checkout. The system must integrate with any existing e-commerce platform via webhook, automate multi-step outreach, and provide clear analytics on recovery performance. All currency in RM. WhatsApp is the primary channel for Malaysian customers.

## Tech Stack

- Next.js 14 (App Router) with TypeScript
- Tailwind CSS + shadcn/ui (new-york style, slate base, yellow #FCD34D accent)
- Supabase (Postgres + RLS + Edge Functions for scheduled jobs)
- whapi.cloud (or Twilio WhatsApp Business API) for WhatsApp sending
- Resend or SendGrid for transactional email
- Vercel hosting with cron job support

## Database Schema

Create the following Supabase tables with all columns and indexes:

sql
-- Abandoned carts
create table carts (
  id uuid primary key default gen_random_uuid(),
  session_id text not null unique,
  customer_name text,
  customer_email text,
  customer_phone text,
  items jsonb not null default '[]',
  subtotal numeric(10,2) not null default 0,
  total numeric(10,2) not null default 0,
  currency text not null default 'MYR',
  checkout_url text,
  restore_token text unique default gen_random_uuid()::text,
  status text not null default 'active' check (status in ('active','abandoned','recovered','lost')),
  source_platform text,
  utm_source text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  abandoned_at timestamptz,
  recovered_at timestamptz
);
create index idx_carts_status on carts(status);
create index idx_carts_session_id on carts(session_id);
create index idx_carts_abandoned_at on carts(abandoned_at);
create index idx_carts_customer_email on carts(customer_email);
create index idx_carts_customer_phone on carts(customer_phone);
create index idx_carts_restore_token on carts(restore_token);

-- Recovery sequences (scheduled outreach steps per cart)
create table recovery_sequences (
  id uuid primary key default gen_random_uuid(),
  cart_id uuid not null references carts(id) on delete cascade,
  step_number int not null,
  channel text not null check (channel in ('whatsapp','email')),
  template_id uuid,
  scheduled_at timestamptz not null,
  sent_at timestamptz,
  opened_at timestamptz,
  clicked_at timestamptz,
  discount_code text,
  message_body text,
  status text not null default 'pending' check (status in ('pending','sent','opened','clicked','failed','cancelled')),
  error_message text,
  created_at timestamptz not null default now()
);
create index idx_recovery_sequences_cart_id on recovery_sequences(cart_id);
create index idx_recovery_sequences_scheduled_at on recovery_sequences(scheduled_at);
create index idx_recovery_sequences_status on recovery_sequences(status);

-- Recovery message templates
create table recovery_templates (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  channel text not null check (channel in ('whatsapp','email')),
  step_number int not null,
  subject text,
  body text not null,
  variables jsonb default '[]',
  is_active boolean not null default true,
  ab_variant text check (ab_variant in ('A','B')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index idx_recovery_templates_channel on recovery_templates(channel);
create index idx_recovery_templates_step on recovery_templates(step_number);

-- Discount codes
create table discount_codes (
  id uuid primary key default gen_random_uuid(),
  code text not null unique,
  discount_type text not null check (discount_type in ('percent','fixed')),
  discount_value numeric(10,2) not null,
  min_order_value numeric(10,2) default 0,
  max_uses int,
  used_count int not null default 0,
  cart_id uuid references carts(id),
  expires_at timestamptz,
  created_at timestamptz not null default now()
);
create index idx_discount_codes_code on discount_codes(code);
create index idx_discount_codes_cart_id on discount_codes(cart_id);

-- Recovery analytics events
create table recovery_analytics (
  id uuid primary key default gen_random_uuid(),
  cart_id uuid references carts(id),
  sequence_id uuid references recovery_sequences(id),
  event_type text not null check (event_type in ('cart_created','cart_abandoned','message_sent','message_opened','link_clicked','cart_recovered','cart_lost')),
  channel text,
  revenue_recovered numeric(10,2),
  metadata jsonb default '{}',
  occurred_at timestamptz not null default now()
);
create index idx_recovery_analytics_cart_id on recovery_analytics(cart_id);
create index idx_recovery_analytics_event_type on recovery_analytics(event_type);
create index idx_recovery_analytics_occurred_at on recovery_analytics(occurred_at);


## Core Features to Build

### 1. Cart Tracking API Endpoint

Create `POST /api/v1/carts/track` — a webhook endpoint that any e-commerce platform can call to upsert cart data. Accept: session_id, customer details, items array (name, sku, qty, price, image_url), total, checkout_url. Upsert by session_id. Reset abandonment timer on each update. Return the restore_token for building recovery links.

### 2. Abandonment Detection Cron Job

Create `GET /api/cron/detect-abandoned` protected with CRON_SECRET. Query carts where status='active' AND updated_at < now() - interval '{{abandonment_threshold}} minutes'. Mark them abandoned_at = now(), status = 'abandoned'. For each new abandoned cart, create recovery_sequence rows scheduled at +1hr, +24hr, and +72hr based on the sequence config: {{recovery_sequence}}. Generate a unique discount code {{discount_code}} for step 2 or 3. Log a cart_abandoned analytics event. Schedule this cron every 5 minutes in vercel.json.

### 3. Recovery Message Sender Cron Job

Create `GET /api/cron/send-recovery` that runs every 5 minutes. Query recovery_sequences where status='pending' AND scheduled_at <= now(). For each: check cart hasn't been recovered or lost first. Interpolate the template body with variables: {{customer_name}}, {{cart_items_summary}}, {{cart_total}}, {{discount_code}}, {{recovery_link}} (build from restore_token). Send via WhatsApp (whapi.cloud POST /messages/text) or email (Resend). Update sequence status to 'sent' or 'failed'. Log analytics event.

### 4. Cart Restore Endpoint

Create `GET /api/restore/[token]` — public endpoint. Look up cart by restore_token. Mark clicked_at on the active sequence. Redirect to the original checkout_url with cart items pre-filled (or return JSON for headless). Log link_clicked event. If customer later completes purchase, call `POST /api/v1/carts/[session_id]/recovered` to mark cart as recovered and log revenue_recovered.

### 5. Recovery Dashboard UI

Build `/admin/cart-recovery` page with:
- Stats row: Abandoned Today, Recovery Rate (%), Revenue Recovered (RM), Pending in Queue
- Abandoned carts table: Customer, Contact, Items summary, Cart Value (RM), Abandoned time (relative), Status badge, Actions (Send Now, Mark Lost, View Timeline)
- Filterable by status, date range, channel
- Real-time optimistic UI updates using SWR or React Query

### 6. Cart Timeline View

Sheet/drawer showing per-cart recovery journey: Cart Created → Abandoned (X min) → Step 1 Sent → Step 1 Opened → Step 2 Sent → Recovered / Lost. Each step shows timestamp, channel icon, message preview, and outcome badge.

### 7. Template Editor

Admin page `/admin/cart-recovery/templates`. List all templates with preview. Edit form with: name, channel toggle, subject (email only), body textarea with variable chips (click to insert). Live preview panel shows interpolated output with sample data. WhatsApp templates respect 1024 char limit warning.

### 8. Analytics Dashboard

Page `/admin/cart-recovery/analytics` showing: 7-day recovery funnel chart (Abandoned → Message Sent → Link Clicked → Recovered), recovery rate trend line, revenue recovered by day, best performing template comparison (open rate, click rate, recovery rate per template), channel breakdown (WhatsApp vs Email).

## Business Rules

- Only send recovery messages if customer_email or customer_phone is known
- Stop all sequences immediately when cart is marked recovered or lost
- Never send more than 3 messages per cart across all steps
- Respect 24-hour WhatsApp messaging window rules (use approved templates for >24hr)
- Discount codes expire 72 hours after generation
- If a cart value is under RM 10, skip discount code generation
- Abandonment threshold is configurable: {{abandonment_threshold}} minutes
- Recovery sequence timing follows: {{recovery_sequence}}
- A/B test two template variants by splitting carts 50/50 on creation

## UI/UX Requirements

- Dark top nav with store name {{store_name}} + Cart Recovery label
- White content area, slate text, yellow #FCD34D accents on CTAs and status badges
- Status badges: yellow=Pending, blue=Sent, green=Recovered, red=Lost, gray=Cancelled
- Responsive table with horizontal scroll on mobile
- Empty state illustration when no abandoned carts (positive message: all carts checked out!)
- Toast notifications for manual send actions
- Skeleton loaders during data fetch

## Deliverables

Provide: complete file tree, all source files (API routes, components, lib utilities, SQL migrations), environment variable list (.env.example), and a setup guide covering: Supabase project init, whapi.cloud account setup, Vercel cron configuration, and how to connect the tracking webhook to Shopify or WooCommerce.