SMS Feedback System

Automated SMS feedback system that sends review requests to customers 30 minutes after completing a transaction.

Features

• 🚀 Sends SMS via Twilio 30 minutes after transaction
• 🔄 Rotates between Google Reviews and Trustpilot links
• 📊 Tracks all sent messages in Neon PostgreSQL
• 🔁 Automatic retry on failure with exponential backoff
• 🎯 RESTful webhook API for integration
• 📈 Queue monitoring and management

Tech Stack

• Node.js/TypeScript - Backend runtime
• BullMQ - Job queue with 30-minute delay
• Redis - Queue backend
• Neon PostgreSQL - Free cloud database
• Twilio - SMS delivery
• Express - API framework

Setup

1. Prerequisites

• Node.js 18+
• Redis (local or cloud)
• Twilio account
• Neon database (free tier)

2. Environment Variables

Copy .env.example to .env and configure:

# Twilio
TWILIO_ACCOUNT_SID=your_account_sid
TWILIO_AUTH_TOKEN=your_auth_token
TWILIO_PHONE_NUMBER=+1234567890

# Neon Database
# Example: Neon pooled connection string with SSL enforced
DATABASE_URL=postgresql://neondb_owner:npg_ifV3syN4oeZj@ep-winter-wildflower-adai8149-pooler.c-2.us-east-1.aws.neon.tech/neondb?sslmode=require

# Optional: Stack Auth / Neon REST helpers
NEXT_PUBLIC_STACK_PROJECT_ID=66d04b70-1db5-4eb4-bede-7d8fcd01e107
NEXT_PUBLIC_STACK_PUBLISHABLE_CLIENT_KEY=pck_tpc9rsx22tpwr04p3tdrc6zmnxw4gyvzbtfyyn3tn2rt0
STACK_SECRET_SERVER_KEY=ssk_pv9qfwx4dxef4w8gm44hgx7bv6th39j38hf4hg1ypqqcg
NEON_REST_API=https://ep-winter-wildflower-adai8149.apirest.c-2.us-east-1.aws.neon.tech/neondb/rest/v1

# Redis
REDIS_HOST=localhost
REDIS_PORT=6379

# Security
API_KEY=your_secure_api_key

# Review Links
GOOGLE_REVIEWS_URL=https://g.page/r/your-link/review
TRUSTPILOT_URL=https://www.trustpilot.com/evaluate/your-business.com

3. Installation

npm install
npm run dev  # Development
npm run build && npm start  # Production

4. Docker Deployment

docker-compose up -d

API Endpoints

Schedule Feedback SMS

POST /webhook/transaction-complete
Headers: x-api-key: your_api_key
Body:
{
  "transactionId": "TXN123",
  "customerId": "CUST456",
  "customerFirstName": "John",
  "customerPhone": "+1234567890",
  "salesRepName": "Jane Smith",
  "transactionCompletedAt": "2024-01-09T12:00:00Z"
}

Cancel Scheduled SMS

DELETE /webhook/cancel/:transactionId
Headers: x-api-key: your_api_key

Queue Status

GET /admin/queue-status
Headers: x-api-key: your_api_key

Health Check

GET /health

Database Schema

The system automatically creates this table in your Neon database:

CREATE TABLE sms_feedback_jobs (
  id SERIAL PRIMARY KEY,
  transaction_id VARCHAR(255) UNIQUE NOT NULL,
  customer_id VARCHAR(255) NOT NULL,
  customer_first_name VARCHAR(100) NOT NULL,
  customer_phone VARCHAR(20) NOT NULL,
  sales_rep_name VARCHAR(100) NOT NULL,
  review_platform VARCHAR(50),
  review_link TEXT,
  status VARCHAR(50) DEFAULT 'pending',
  scheduled_at TIMESTAMP NOT NULL,
  sent_at TIMESTAMP,
  error_message TEXT,
  retry_count INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Message Format

Hello [first name], this is America First, your new vehicle protection partner. 
Please give us your feedback on [sales rep] by clicking the link below:

[Review Link]

Link Rotation

The system automatically rotates between:

1. Google Reviews
2. Trustpilot

Distribution is even (50/50) by default. Can be customized with weighted rotation.

Monitoring

• All SMS attempts are logged with Winston
• Failed messages are retried 3 times with exponential backoff
• Database tracks all sent/failed messages for audit

Production Considerations

1. Redis Persistence: Use Redis AOF or RDB for queue persistence
2. Twilio Messaging Service: Consider using for better deliverability
3. Rate Limiting: Twilio has rate limits, BullMQ handles this
4. Monitoring: Add Datadog/New Relic for production monitoring
5. Secrets: Use AWS Secrets Manager or similar for production

Costs

• Twilio: ~$0.0079 per SMS
• Neon: Free tier (3GB storage, 1 compute hour/day)
• Redis: Free tier available on Upstash/Redis Cloud
• Hosting: ~$5-20/month on Railway/Render/Fly.io

Support

For issues or questions about integration, contact the development team.