Simple Mail Merge

A lightweight hobby project built to help students efficiently send multiple personalized application emails to companies listed in the OJT Companies List provided by our school.

This tool removes the repetitive work of manually editing and sending emails—while still keeping each message personalized.

✅ 1. Project Overview

Simple Mail Merge is a web-based application designed to:

• Send personalized application letters to multiple companies
• Use your own email account for sending messages
• Track delivery results through a summary report

This project was built mainly to help students applying for OJT / internships streamline their application process.

---

✅ 2. Features

Application Features

1. Light Mode / Dark Mode
2. Fully mobile responsive
3. OTP-based authentication
4. Send multiple personalized emails using your own email (via Google App Password)
5. Recipient file parsing and validation
6. Email summary report (success rate and list of successful and failed email sends)

---

Database Tables

tbl_user

Name	Data Type	Description
user_id	UUID	Primary key, auto-generated user ID
email	TEXT	User email address (unique)
otp_hash	TEXT	Hashed OTP for verification
expires_at	TIMESTAMPTZ	OTP expiration timestamp
attempts	INTEGER	Number of OTP attempts
last_login	TIMESTAMPTZ	Last successful login time
last_login_attempt	TIMESTAMPTZ	Last login attempt timestamp
created_at	TIMESTAMPTZ	Record creation timestamp

---

✅ 3. Business Logic & Validation

Sender email ownership

• Users must have access to the sender email to complete OTP verification

Google OAuth

• Required to allow the app to send emails using the user's account

Recipient import validation

• Invalid email formats are detected before sending

Security

• OTP expiration handling
• Limited OTP attempts to prevent abuse

---

✅ 4. Tech Stack

Frontend

• React (TypeScript)
• Bootstrap

Backend

• Express (TypeScript)
• Resend (Email service)
• Supabase Postgres

Deployment

• AWS Lambda + API Gateway
• Vercel
• Supabase

---

✅ 5. Installation / Setup

Requirements

• Node.js (v18+)
• npm (v9+)
• Docker + Docker Compose (optional, for containerized local setup)
• Supabase account
• Google account (for OAuth)
• Resend account

1. Clone the repository

git clone https://github.com/MarcLawrenceKing/mail-merge.git
cd mail-merge

2. Create environment files

Backend

cd server
cp .env.example .env

Frontend

cd ../client
cp .env.example .env

3. Configure environment variables

server/.env

SUPABASE_URL=your_supabase_url
SUPABASE_ANON_KEY=your_supabase_anon_key
DATABASE_URL=postgresql://postgres.your-project-ref:your_password@aws-0-region.pooler.supabase.com:6543/postgres?sslmode=require

RESEND_API_KEY=your_resend_api_key
RESEND_FROM_EMAIL=your_verified_sender@yourdomain.com

PORT=3005
JWT_SECRET=secret_to_encrypt_session_token

GOOGLE_CLIENT_ID=your_google_client_id
GOOGLE_CLIENT_SECRET=your_google_client_secret
GOOGLE_REDIRECT_URI=http://localhost:3000/api/auth/google/callback
CLIENT_URL=http://localhost:5173

# AWS Lambda & API Gateway Deployment
LAMBDA_FUNCTION_NAME=your_lambda_function_name
APIGW_API_ID=your_api_gateway_id
APIGW_STAGE=your_api_gateway_stage

ALLOWED_ORIGINS=http://localhost:5173

ALLOWED_ORIGINS accepts a comma-separated list and supports * wildcards (example: https://mail-merge-*.vercel.app).

client/.env

VITE_API_URL=http://localhost:3005

4. Run database migrations (Drizzle ORM)

cd server
npm run db:migrate

When you change the schema in server/src/db/schema.ts, generate a new migration:

cd server
npm run db:generate

db:migrate is written to be idempotent for tbl_user, so existing data is not deleted.

5A. Start locally with Node.js

Install dependencies:

cd client
npm install
cd ../server
npm install

Run apps:

cd client
npm run dev
cd ../server
npm run dev

5B. Start with Docker (separate FE/BE compose files)

Backend terminal:

cd server
docker compose up --build

Frontend terminal:

cd client
docker compose up --build

Open http://localhost:5173.