Notification Delivery Service — Queue Infrastructure

Problem Description

A SaaS platform sends transactional emails and SMS messages to users: welcome emails, password resets, billing receipts, and security alerts. Currently these are fired synchronously during web requests, causing latency and lost messages when the email provider is slow. The team wants to move to an asynchronous notification queue backed by SQL Server — keeping the notifications as first-class relational records (so they can be audited, retried, and reported on) while also treating them as work items to be processed by background workers.

The platform has two types of notifications that currently matter: email notifications (which need recipient address, subject, and body) and SMS notifications (which need a phone number and message text). Both types share the same lifecycle tracking: they start pending, get picked up by a worker, succeed or fail, and can be retried up to a configurable number of times before being marked as permanently failed.

Multiple worker processes run in parallel and compete for the same queue. A configuration value for the maximum number of retry attempts should be stored centrally and readable by the worker without hardcoding the value into the procedure.

The team needs:

1. The queue table schema (base notification table plus the two subtype tables for email and SMS content)
2. The shared queue state reference table
3. A procedure that a background worker calls to atomically claim the next available notification for processing
4. State classification functions to make the state machine logic reusable

Output Specification

Produce a SQL script file named queue.sql containing:

• The QueueStatus reference table with appropriate state values seeded
• An AppSettings table with at least the max attempts configuration seeded
• The Notification base table with full queue lifecycle columns
• Subtype tables for email and SMS notification content
• At minimum two state classification functions (one to check if an item is processable, one to check if it is in a terminal state)
• A stored procedure that a background worker calls to atomically claim the next available notification item for processing

All objects should use named custom types where applicable. Assume the custom types (_Timestamp, _Bool, _Int, _Type, etc.) are already defined and available.