Save contact enquiries and queue email notifications

A contact form should not lose a message because the mail server is temporarily unavailable. Save the enquiry first, then send a notification from a durable queue. The success response means “your enquiry was saved”, not “an email reached someone's inbox”. That distinction makes support and recovery clearer.

This is a MySQL 8.4/InnoDB and Node.js integration pattern. It assumes a mysql2/promise-compatible pool and a separate worker. The SQL and adapter excerpt are not a complete deployed form, and no real email is sent. Use a local test database and a mock mail adapter while integrating it.

Validate a small request contract

Bound the HTTP request body before parsing JSON. Accept only name, reply address and message, with limits appropriate to your form. Trim outer whitespace, reject empty messages and control characters in email-header fields, and validate addresses with a maintained mail-address validator. A regular expression cannot prove a mailbox exists.

Keep the sender address fixed to your verified domain. Put the visitor's validated address in Reply-To, never From; otherwise notifications can fail sender authentication. Encode untrusted text when rendering HTML, or use a plain-text notification linking to a protected dashboard.

Commit the record and job together

CREATE TABLE enquiries (
  id CHAR(36) CHARACTER SET ascii PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  reply_address VARCHAR(254) NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB;
 
CREATE TABLE enquiry_notifications (
  enquiry_id CHAR(36) CHARACTER SET ascii PRIMARY KEY,
  state ENUM('queued','processing','accepted','unconfirmed') NOT NULL DEFAULT 'queued',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  next_attempt_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  lease_until TIMESTAMP(6) NULL,
  FOREIGN KEY (enquiry_id) REFERENCES enquiries(id)
) ENGINE=InnoDB;

import { randomUUID } from "node:crypto";
 
// value has already passed length, type and address validation.
export async function saveEnquiry(pool, value) {
  const connection = await pool.getConnection();
  const id = randomUUID();
  try {
    await connection.beginTransaction();
    await connection.execute(
      "INSERT INTO enquiries (id, name, reply_address, message) VALUES (?, ?, ?, ?)",
      [id, value.name, value.replyAddress, value.message]
    );
    await connection.execute(
      "INSERT INTO enquiry_notifications (enquiry_id) VALUES (?)", [id]
    );
    await connection.commit();
    return { id, status: "saved" };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

The outbox row cannot exist without its enquiry, and a committed enquiry always has a notification job. Use one connection for the entire transaction; independent pool calls may run on different connections. MySQL documents these transaction semantics.

Design worker ownership explicitly

For several workers, claim a queued job in a short transaction using row locking, set a processing lease and commit. Send outside the transaction so slow SMTP does not hold database locks. Record the result, then release the lease. A crashed worker's lease must eventually become recoverable.

Retry temporary failures with bounded exponential backoff and a maximum attempt count. Permanent address rejection needs attention, not endless retries. A timeout after sending message data can be ambiguous: the server may have accepted it. Keep an unconfirmed state rather than pretending nothing happened. See honest sent-email history.

Test storage and notification separately

Insert a synthetic enquiry with the worker disabled. The route should return saved and the queue should contain one job. Force the outbox insert to fail: the enquiry insert must roll back. Simulate SMTP rejection and verify the enquiry remains visible.

Kill the worker after claiming a job and confirm its lease expires. Kill it after mocked SMTP acceptance but before recording success: verify an uncertain result is exposed. A database transaction alone cannot eliminate this external side-effect gap.

Repeated form submissions need a separate idempotency policy. A random, short-lived submission key can deduplicate a browser retry, but should not collapse intentionally different messages. Apply rate limits and spam controls without logging entire messages or addresses unnecessarily.

Troubleshooting and privacy

If enquiries disappear during mail outages, storage and delivery are still coupled. If jobs stay processing forever, inspect lease recovery. If notifications arrive twice, inspect timeouts and retries before blaming the form button.

Set access controls and retention rules for the database and backups. A notification containing only an enquiry ID reduces personal content spread through email. For a managed queue, inbox and follow-up workflow, explore CodeKits branded email systems.