Twilio SMS Delivery Status Tracking with Next.js, NextAuth, and MongoDB

+-----------------+     +--------------------+     +-----------------+     +----------------------+     +-----------------+
|  User Browser   | --> | Next.js Frontend | --> | Next.js API Route | --> |    Twilio SMS API    | --> | Recipient Phone |
| (Authenticated) |     |  (Send SMS UI)   |     |   (/api/send-sms) |     |                      |     +-----------------+
+-----------------+     +--------------------+     +--------+--------+     +----------+-----------+
        |                                                 |                      |
        |                                                 | Stores Initial       | Sends Status Update
        |                                                 | Message Log          | (Webhook POST)
        |                                                 v                      v
        |                                       +---------+---------+     +--------+-------------+
        |                                       |     MongoDB       | <-- | Next.js API Route    |
        |                                       | (Message Status)  |     | (/api/sms-status)    |
        |                                       +-------------------+     +----------------------+
        |                                                                       ^
        |                                                                       | Verifies Request
        |                                                                       |
        +-----------------------------------------------------------------------+
                                         (Session Check)

npx create-next-app@latest sms-status-tracker

cd sms-status-tracker
npm install next-auth @auth/mongodb-adapter mongodb mongoose twilio

# .env.local

# Twilio Credentials (https://console.twilio.com/)
TWILIO_ACCOUNT_SID=ACxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
TWILIO_AUTH_TOKEN=your_auth_token_here # KEEP THIS SECRET
TWILIO_PHONE_NUMBER=+15551234567 # Your active Twilio phone number

# MongoDB Connection String (e.g., from MongoDB Atlas)
DATABASE_URL=mongodb+srv://<username>:<password>@<cluster-url>/<database-name>?retryWrites=true&w=majority

# NextAuth.js Configuration
# Generate strong secrets: `openssl rand -base64 32`
NEXTAUTH_SECRET=your_super_secret_nextauth_value_here # Required for production
NEXTAUTH_URL=http://localhost:3000 # Development URL, change for production

# Twilio Webhook Security (Use TWILIO_AUTH_TOKEN by default, or generate a specific secret)
# Using AUTH_TOKEN is simpler but less isolated. For higher security, set up specific webhook signing keys in Twilio.
TWILIO_WEBHOOK_SECRET=your_auth_token_here # Or a dedicated webhook signing secret

sms-status-tracker/
├── app/
│   ├── api/
│   │   ├── auth/
│   │   │   └── [...nextauth]/
│   │   │       └── route.js      # NextAuth.js handler
│   │   ├── send-sms/
│   │   │   └── route.js      # API to send SMS
│   │   └── sms-status/
│   │       └── route.js      # API to receive Twilio callbacks
│   ├── components/
│   │   └── Provider.js     # Session Provider wrapper
│   ├── lib/
│   │   ├── mongodb.js      # MongoDB client promise setup
│   │   └── mongoose.js     # Mongoose connection setup (optional)
│   ├── models/
│   │   └── Message.js      # Mongoose schema for messages
│   ├── dashboard/
│   │   └── page.js         # Simple UI to send SMS
│   ├── auth/               # Optional: Custom auth pages dir
│   │   ├── signin/
│   │   │   └── page.js
│   │   └── verify-request/
│   │       └── page.js
│   ├── layout.js           # Root layout
│   └── page.js             # Home page
├── .env.local              # Environment variables (DO NOT COMMIT)
├── next.config.js
├── package.json
└── tailwind.config.js

// app/lib/mongodb.js
import { MongoClient } from 'mongodb';

if (!process.env.DATABASE_URL) {
  throw new Error('Invalid/Missing environment variable: `DATABASE_URL`');
}

const uri = process.env.DATABASE_URL;
const options = {};

let client;
let clientPromise;

if (process.env.NODE_ENV === 'development') {
  // In development mode, use a global variable so that the value
  // is preserved across module reloads caused by HMR (Hot Module Replacement).
  if (!global._mongoClientPromise) {
    client = new MongoClient(uri, options);
    global._mongoClientPromise = client.connect();
  }
  clientPromise = global._mongoClientPromise;
} else {
  // In production mode, it's best to not use a global variable.
  client = new MongoClient(uri, options);
  clientPromise = client.connect();
}

// Export a module-scoped MongoClient promise. By doing this in a
// separate module, the client can be shared across functions.
export default clientPromise;

// app/api/auth/[...nextauth]/route.js
import NextAuth from 'next-auth';
import EmailProvider from 'next-auth/providers/email';
import { MongoDBAdapter } from '@auth/mongodb-adapter';
import clientPromise from '@/app/lib/mongodb'; // Use alias if configured, else adjust path

export const authOptions = {
  adapter: MongoDBAdapter(clientPromise),
  providers: [
    EmailProvider({
      // Configure your email sending service here (e.g., SendGrid, Resend)
      // This setup requires an email provider like SendGrid, Nodemailer, etc.
      // For simplicity in this guide, we assume setup, but ensure this works.
      // Example using environment variables for a generic SMTP server:
      server: {
        host: process.env.EMAIL_SERVER_HOST,
        port: process.env.EMAIL_SERVER_PORT,
        auth: {
          user: process.env.EMAIL_SERVER_USER,
          pass: process.env.EMAIL_SERVER_PASSWORD,
        },
      },
      from: process.env.EMAIL_FROM,
    }),
    // Add other providers like Google, GitHub, etc. if needed
  ],
  pages: {
    signIn: '/auth/signin', // Custom sign-in page (optional)
    verifyRequest: '/auth/verify-request', // Page shown after email sent (optional)
    // error: '/auth/error', // Error code passed in query string as ?error=
  },
  session: {
    strategy: 'jwt', // Use JWT for session strategy
  },
  secret: process.env.NEXTAUTH_SECRET, // Use the secret from .env.local
  // Add callbacks if needed for customizing session/token data
  callbacks: {
     async session({ session, token, user }) {
       // Send properties to the client, like an access_token and user id from a provider.
       if (token) {
         session.userId = token.sub // Add user ID to session
       }
       return session
     }
   }
};

const handler = NextAuth(authOptions);

export { handler as GET, handler as POST };

// app/components/Provider.js
'use client';

import { SessionProvider } from 'next-auth/react';

const Provider = ({ children, session }) => {
  return <SessionProvider session={session}>{children}</SessionProvider>;
};

export default Provider;

// app/layout.js
import './globals.css';
import { Inter } from 'next/font/google';
import Provider from './components/Provider'; // Adjust path if needed

const inter = Inter({ subsets: ['latin'] });

export const metadata = {
  title: 'SMS Status Tracker',
  description: 'Track Twilio SMS delivery status with Next.js',
};

export default function RootLayout({ children }) {
  return (
    <html lang=""en"">
      <body className={inter.className}>
        <Provider> {/* Wrap children with the Session Provider */}
          {children}
        </Provider>
      </body>
    </html>
  );
}

// app/page.js
'use client';

import { useSession, signIn, signOut } from 'next-auth/react';
import Link from 'next/link';

export default function Home() {
  const { data: session, status } = useSession();

  return (
    <main className=""flex min-h-screen flex-col items-center justify-center p-24"">
      <h1 className=""text-4xl font-bold mb-8"">SMS Status Tracker</h1>

      {status === 'loading' && <p>Loading...</p>}

      {status === 'authenticated' && session && (
        <div className=""text-center"">
          <p className=""mb-4"">Signed in as {session.user?.email}</p>
          <Link
            href=""/dashboard""
            className=""bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded mr-2""
          >
            Go to Dashboard
          </Link>
          <button
            onClick={() => signOut()}
            className=""bg-red-500 hover:bg-red-700 text-white font-bold py-2 px-4 rounded""
          >
            Sign Out
          </button>
        </div>
      )}

      {status === 'unauthenticated' && (
        <button
          // Point to the specific provider or the default sign-in page
          onClick={() => signIn('email')} // Or just signIn() for default page
          className=""bg-green-500 hover:bg-green-700 text-white font-bold py-2 px-4 rounded""
        >
          Sign In
        </button>
        // If using Email provider, you might need an input field here or redirect to pages.signIn
      )}
    </main>
  );
}

// app/auth/signin/page.js
'use client';
import { useState } from 'react';
import { signIn } from 'next-auth/react';

export default function SignIn() {
  const [email, setEmail] = useState('');

  const handleSignIn = (e) => {
    e.preventDefault();
    // Redirect to the verify request page after email is sent
    signIn('email', { email, callbackUrl: '/auth/verify-request' });
  };

  return (
    <div className=""flex justify-center items-center min-h-screen"">
      <form
        onSubmit={handleSignIn}
        className=""bg-white p-8 rounded shadow-md w-full max-w-sm""
      >
        <h2 className=""text-2xl font-bold mb-6 text-center text-gray-800"">Sign In / Sign Up</h2>
        <p className=""text-center text-gray-600 mb-6"">
          Enter your email to receive a magic link.
        </p>
        <div className=""mb-4"">
          <label htmlFor=""email"" className=""block text-gray-700 text-sm font-bold mb-2"">
            Email
          </label>
          <input
            type=""email""
            id=""email""
            name=""email""
            value={email}
            onChange={(e) => setEmail(e.target.value)}
            required
            className=""shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline""
            placeholder=""you@example.com""
          />
        </div>
        <button
          type=""submit""
          className=""bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline w-full""
        >
          Send Magic Link
        </button>
      </form>
    </div>
  );
}

// app/auth/verify-request/page.js
export default function VerifyRequest() {
  return (
    <div className=""flex justify-center items-center min-h-screen"">
      <div className=""bg-white p-8 rounded shadow-md text-center"">
        <h2 className=""text-2xl font-bold mb-4 text-gray-800"">Check your email</h2>
        <p className=""text-gray-600"">
          A sign in link has been sent to your email address. Click the link to sign in.
        </p>
      </div>
    </div>
  );
}

// app/lib/mongoose.js
import mongoose from 'mongoose';

const MONGODB_URI = process.env.DATABASE_URL;

if (!MONGODB_URI) {
  throw new Error(
    'Please define the DATABASE_URL environment variable inside .env.local'
  );
}

/**
 * Global is used here to maintain a cached connection across hot reloads
 * in development. This prevents connections growing exponentially
 * during API Route usage.
 */
let cached = global.mongoose;

if (!cached) {
  cached = global.mongoose = { conn: null, promise: null };
}

async function dbConnect() {
  if (cached.conn) {
    return cached.conn;
  }

  if (!cached.promise) {
    const opts = {
      bufferCommands: false,
    };

    cached.promise = mongoose.connect(MONGODB_URI, opts).then((mongoose) => {
      return mongoose;
    });
  }
  cached.conn = await cached.promise;
  console.log(""Mongoose Connected!""); // Log successful connection
  return cached.conn;
}

export default dbConnect;

// app/models/Message.js
import mongoose from 'mongoose';

const MessageSchema = new mongoose.Schema(
  {
    userId: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'User', // Reference to the User model created by NextAuth.js adapter
      required: true,
    },
    to: {
      type: String,
      required: [true, 'Recipient phone number is required.'],
      trim: true,
    },
    body: {
      type: String,
      required: [true, 'Message body is required.'],
      trim: true,
    },
    twilioSid: {
      // The unique ID returned by Twilio upon sending
      type: String,
      required: true,
      unique: true,
      index: true, // Index for faster lookups by SID
    },
    status: {
      // Status from Twilio (e.g., 'queued', 'sent', 'delivered', 'failed', 'undelivered')
      type: String,
      required: true,
      default: 'queued', // Initial status
      enum: ['queued', 'sending', 'sent', 'delivered', 'undelivered', 'failed', 'received'], // Add more as needed
    },
    errorCode: {
      // Twilio error code if status is 'failed' or 'undelivered'
      type: Number,
      required: false,
    },
    errorMessage: {
       // Twilio error message
       type: String,
       required: false,
    }
  },
  {
    timestamps: true, // Automatically add createdAt and updatedAt
  }
);

// Avoid recompiling the model if it already exists
export default mongoose.models.Message || mongoose.model('Message', MessageSchema);

// app/api/send-sms/route.js
import { NextResponse } from 'next/server';
import { getServerSession } from 'next-auth/next';
import { authOptions } from '@/app/api/auth/[...nextauth]/route'; // Adjust path
import dbConnect from '@/app/lib/mongoose'; // Adjust path
import Message from '@/app/models/Message'; // Adjust path
import twilio from 'twilio';

// Initialize Twilio client
const accountSid = process.env.TWILIO_ACCOUNT_SID;
const authToken = process.env.TWILIO_AUTH_TOKEN;
const twilioPhoneNumber = process.env.TWILIO_PHONE_NUMBER;
const client = twilio(accountSid, authToken);

export async function POST(request) {
  // 1. Check Authentication
  const session = await getServerSession(authOptions);
  if (!session || !session.userId) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  // 2. Parse Request Body
  let recipient;
  let messageBody;
  try {
    const body = await request.json();
    recipient = body.to;
    messageBody = body.body;

    // Basic Input Validation
    if (!recipient || typeof recipient !== 'string' || !recipient.trim()) {
       return NextResponse.json({ error: 'Recipient phone number (`to`) is required.' }, { status: 400 });
    }
     if (!messageBody || typeof messageBody !== 'string' || !messageBody.trim()) {
       return NextResponse.json({ error: 'Message body (`body`) is required.' }, { status: 400 });
    }

    // Consider more robust phone number validation (e.g., using a library)
    // Format recipient number to E.164 if necessary (Twilio prefers this)
    // Example basic check:
    if (!/^\+?[1-9]\d{1,14}$/.test(recipient)) {
        return NextResponse.json({ error: 'Invalid recipient phone number format.' }, { status: 400 });
    }

  } catch (error) {
    return NextResponse.json({ error: 'Invalid request body.' }, { status: 400 });
  }

  // 3. Construct Status Callback URL
  // IMPORTANT: Use your production URL or ngrok URL here during development
  const statusCallbackUrl = `${process.env.NEXTAUTH_URL}/api/sms-status`;

  // 4. Send SMS via Twilio & Log to DB
  try {
    await dbConnect(); // Ensure DB connection

    const message = await client.messages.create({
      body: messageBody,
      from: twilioPhoneNumber,
      to: recipient,
      statusCallback: statusCallbackUrl, // Tell Twilio where to POST status updates
      statusCallbackMethod: 'POST', // Use POST method
    });

    console.log(`Twilio message sent: SID ${message.sid}, Status: ${message.status}`);

    // 5. Store Message Log in MongoDB
    const newMessageLog = new Message({
      userId: session.userId,
      to: recipient,
      body: messageBody,
      twilioSid: message.sid, // Store the SID from Twilio's response
      status: message.status, // Initial status from Twilio (e.g., 'queued', 'sending')
    });
    await newMessageLog.save();

    console.log(`Message log created in DB for SID: ${message.sid}`);

    return NextResponse.json(
        { success: true, messageSid: message.sid, status: message.status },
        { status: 201 }
    );

  } catch (error) {
    console.error('Error sending SMS or saving log:', error);

    // Handle specific Twilio errors if needed
    const errorMessage = error.message || 'Failed to send SMS.';
    let statusCode = 500;
    if (error.status) {
        statusCode = error.status; // Use status code from Twilio error if available
    }

    // Attempt to log the failure if possible (optional)
     try {
       await dbConnect(); // Ensure connection for logging failure
       const failedLog = new Message({
         userId: session.userId,
         to: recipient,
         body: messageBody,
         twilioSid: `failed_${Date.now()}`, // Generate a temporary SID for failed attempts
         status: 'failed',
         errorMessage: errorMessage,
         errorCode: error.code // Store Twilio error code if available
       });
       await failedLog.save();
     } catch (dbError) {
       console.error(""Failed to save error log to DB:"", dbError);
     }

    return NextResponse.json({ error: errorMessage, code: error.code }, { status: statusCode });
  }
}

// app/dashboard/page.js
'use client';

import { useState, useEffect } from 'react';
import { useSession } from 'next-auth/react';
import { useRouter } from 'next/navigation'; // Use next/navigation for App Router

export default function Dashboard() {
  const { data: session, status } = useSession();
  const router = useRouter();
  const [recipient, setRecipient] = useState('');
  const [messageBody, setMessageBody] = useState('');
  const [isSending, setIsSending] = useState(false);
  const [feedback, setFeedback] = useState({ type: '', message: '' });
  const [messages, setMessages] = useState([]); // State to hold sent messages

  // Redirect if not authenticated
  useEffect(() => {
    if (status === 'unauthenticated') {
      router.push('/'); // Redirect to home/login page
    }
  }, [status, router]);

  // Function to fetch messages (using dummy data for this guide)
  const fetchMessages = () => {
      // In a real application, you would fetch this from an API endpoint
      // that retrieves messages for the logged-in user from the database.
      console.log(""Fetching messages - API endpoint needed"");
      // Using dummy data for demonstration purposes:
       setMessages([
         {_id: ""1"", to: ""+1555000111"", body: ""Test message 1"", status: ""delivered"", twilioSid: ""SMxxx1"", createdAt: new Date(Date.now() - 60000 * 5).toISOString()}, // 5 mins ago
         {_id: ""2"", to: ""+1555000222"", body: ""Test message 2"", status: ""sent"", twilioSid: ""SMxxx2"", createdAt: new Date(Date.now() - 60000 * 2).toISOString()}, // 2 mins ago
         {_id: ""3"", to: ""+1555000333"", body: ""Test message 3"", status: ""failed"", twilioSid: ""SMxxx3"", createdAt: new Date(Date.now() - 60000 * 1).toISOString(), errorMessage: ""Invalid number""}, // 1 min ago
       ].sort((a, b) => new Date(b.createdAt) - new Date(a.createdAt))); // Sort newest first
  };

  // Call fetchMessages on component mount when authenticated
   useEffect(() => {
     if (status === 'authenticated') {
       fetchMessages();
     }
   }, [status]);

  const handleSubmit = async (e) => {
    e.preventDefault();
    if (!recipient || !messageBody) {
      setFeedback({ type: 'error', message: 'Please enter recipient and message.' });
      return;
    }
    setIsSending(true);
    setFeedback({ type: '', message: '' }); // Clear previous feedback

    try {
      const response = await fetch('/api/send-sms', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify({ to: recipient, body: messageBody }),
      });

      const data = await response.json();

      if (response.ok) {
        setFeedback({ type: 'success', message: `Message sent! SID: ${data.messageSid}, Status: ${data.status}` });
        setRecipient(''); // Clear form
        setMessageBody('');
        // Refresh the message list (using dummy data refresh for now)
        fetchMessages();
      } else {
        setFeedback({ type: 'error', message: `Error: ${data.error || 'Failed to send message.'}` });
      }
    } catch (error) {
      console.error('Send SMS error:', error);
      setFeedback({ type: 'error', message: 'An unexpected error occurred.' });
    } finally {
      setIsSending(false);
    }
  };

  if (status === 'loading') {
    return <p className=""text-center mt-10"">Loading session...</p>;
  }

  if (status === 'unauthenticated') {
     // Should be redirected by useEffect, but good practice to handle render
     return <p className=""text-center mt-10"">Redirecting...</p>;
  }

  return (
    <div className=""container mx-auto p-4 md:p-8"">
      <h1 className=""text-3xl font-bold mb-6"">Send SMS</h1>

      {feedback.message && (
        <div
          className={`p-4 mb-4 rounded ${
            feedback.type === 'success' ? 'bg-green-100 text-green-800' : 'bg-red-100 text-red-800'
          }`}
        >
          {feedback.message}
        </div>
      )}

      <form onSubmit={handleSubmit} className=""bg-white p-6 rounded shadow-md mb-8"">
        <div className=""mb-4"">
          <label htmlFor=""recipient"" className=""block text-gray-700 text-sm font-bold mb-2"">
            Recipient Phone (E.164 format, e.g., +15551234567)
          </label>
          <input
            type=""tel""
            id=""recipient""
            value={recipient}
            onChange={(e) => setRecipient(e.target.value)}
            className=""shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline""
            placeholder=""+15551234567""
            required
          />
        </div>
        <div className=""mb-6"">
          <label htmlFor=""messageBody"" className=""block text-gray-700 text-sm font-bold mb-2"">
            Message
          </label>
          <textarea
            id=""messageBody""
            value={messageBody}
            onChange={(e) => setMessageBody(e.target.value)}
            className=""shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline h-24""
            placeholder=""Enter your message here""
            required
          ></textarea>
        </div>
        <div className=""flex items-center justify-between"">
          <button
            type=""submit""
            className=""bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline disabled:opacity-50""
            disabled={isSending}
          >
            {isSending ? 'Sending...' : 'Send Message'}
          </button>
        </div>
      </form>

       {/* Display Sent Messages */}
       <h2 className=""text-2xl font-bold mb-4"">Message Log (Demo Data)</h2>
       <div className=""bg-white p-6 rounded shadow-md"">
         {messages.length > 0 ? (
           <ul className=""divide-y divide-gray-200"">
             {messages.map((msg) => (
               <li key={msg._id || msg.twilioSid} className=""py-4"">
                 <p><strong>To:</strong> {msg.to}</p>
                 <p><strong>Body:</strong> {msg.body}</p>
                 <p><strong>Status:</strong> <span className={`font-semibold ${
                   msg.status === 'delivered' ? 'text-green-600' :
                   msg.status === 'failed' || msg.status === 'undelivered' ? 'text-red-600' :
                   'text-yellow-600' // queued, sending, sent
                 }`}>{msg.status}</span></p>
                 {msg.errorMessage && <p className=""text-red-500 text-sm""><strong>Error:</strong> {msg.errorMessage} (Code: {msg.errorCode || 'N/A'})</p>}
                 <p className=""text-xs text-gray-500"">SID: {msg.twilioSid}</p>
                 <p className=""text-xs text-gray-500"">Sent: {new Date(msg.createdAt).toLocaleString()}</p>
               </li>
             ))}
           </ul>
         ) : (
           <p>No message history yet.</p> // Added fallback text
         )}
       </div>
    </div>
  );
}

// app/api/sms-status/route.js
import { NextResponse } from 'next/server';
import twilio from 'twilio';
import dbConnect from '@/app/lib/mongoose'; // Adjust path
import Message from '@/app/models/Message'; // Adjust path

// Twilio credentials for signature validation
const authToken = process.env.TWILIO_AUTH_TOKEN;

export async function POST(request) {
  // 1. Extract Request Data
  const url = new URL(request.url);
  const twilioSignature = request.headers.get('x-twilio-signature') || '';
  
  // Parse form data (Twilio sends application/x-www-form-urlencoded)
  const formData = await request.formData();
  const params = {};
  
  for (const [key, value] of formData.entries()) {
    params[key] = value;
  }

  console.log('Received Twilio status callback:', params);

  // 2. Validate Twilio Signature for Security
  // CRITICAL: Always validate to prevent unauthorized status updates
  const fullUrl = url.toString(); // Use the complete URL including query params
  
  // Twilio's webhook validation
  const isValid = twilio.validateRequest(
    authToken,
    twilioSignature,
    fullUrl,
    params
  );

  if (!isValid) {
    console.error('Invalid Twilio signature. Potential security breach.');
    return NextResponse.json(
      { error: 'Invalid signature' },
      { status: 403 }
    );
  }

  console.log('Twilio signature validated successfully.');

  // 3. Extract Message Details from Twilio Callback
  const messageSid = params.MessageSid || params.SmsSid; // Twilio uses both
  const messageStatus = params.MessageStatus || params.SmsStatus;
  const errorCode = params.ErrorCode ? parseInt(params.ErrorCode, 10) : null;
  const errorMessage = params.ErrorMessage || null;

  if (!messageSid || !messageStatus) {
    console.error('Missing required parameters in callback.');
    return NextResponse.json(
      { error: 'Missing required parameters' },
      { status: 400 }
    );
  }

  // 4. Update Message Status in Database
  try {
    await dbConnect(); // Ensure DB connection

    const updatedMessage = await Message.findOneAndUpdate(
      { twilioSid: messageSid }, // Find by Twilio SID
      {
        status: messageStatus,
        ...(errorCode && { errorCode }),
        ...(errorMessage && { errorMessage }),
      },
      { new: true, runValidators: true } // Return updated document
    );

    if (!updatedMessage) {
      console.warn(`Message with SID ${messageSid} not found in database.`);
      // Return 200 anyway to prevent Twilio from retrying
      return NextResponse.json(
        { message: 'Message not found, but acknowledged' },
        { status: 200 }
      );
    }

    console.log(
      `Message ${messageSid} updated to status: ${messageStatus}`
    );

    // 5. Respond to Twilio
    // Always return 200 OK to prevent Twilio from retrying
    return NextResponse.json(
      { success: true, messageSid, newStatus: messageStatus },
      { status: 200 }
    );

  } catch (error) {
    console.error('Error updating message status in database:', error);
    
    // Return 200 to prevent Twilio retries even on DB errors
    // Log the error for investigation but acknowledge receipt
    return NextResponse.json(
      { message: 'Error logged, but acknowledged' },
      { status: 200 }
    );
  }
}

const statusCallbackUrl = `${process.env.NEXTAUTH_URL}/api/sms-status`;

// In app/models/Message.js, the index on twilioSid is already defined:
twilioSid: {
  type: String,
  required: true,
  unique: true,
  index: true, // Creates an index for faster lookups
}

MessageSchema.index({ userId: 1, createdAt: -1 });