Twilio SMS Bot

Category: Customer Support

Get this pack →

This page is generated from the Air Pipe marketplace. Browse it live to install into your organization.

A complete Twilio SMS bot served straight from your database — no application server. Point a Twilio number's webhook at this pack and it will reply to incoming texts instantly, route keyword commands (HOURS, BOOK, PRICING, …) to canned replies, handle STOP/START opt-out, and log every message into Postgres.

Replies are returned as TwiML in the webhook response, so the bot needs no Twilio API credentials to answer — just the inbound webhook.

Endpoints

Method	Route	Description
POST	/sms/inbound	Twilio "A message comes in" webhook. Logs the SMS, applies keyword routing + opt-out, and returns a TwiML reply.
GET	/sms/messages	Conversation log, newest first. Filter with ?contact=+15551234567.
GET	/sms/contacts	Every number that has texted in, with message count and opt-out status.
GET	/sms/keywords	The configured keyword → reply menu.
POST	/sms/keywords/set	Add or update a keyword reply (use __DEFAULT__ for the fallback).
POST	/sms/seed	Create tables and load a sample keyword menu + conversation.

Setup

1. Managed variables

   • DATABASE_URL — your Postgres connection string.
   • WEBHOOK_TOKEN — a shared secret you choose; it authenticates Twilio's calls (see "Securing the webhook" below).

2. Create the schema and sample data

   curl -X POST https://<your-host>/sms/seed

   This loads a sample menu: HELP, HOURS, LOCATION, BOOK, PRICING, plus a __DEFAULT__ fallback. (schema.sql documents the tables if you prefer to create them yourself.)

3. Point Twilio at the webhook

   In the Twilio Console → your number → Messaging → A message comes in, set:

   https://<your-host>/sms/inbound?token=<WEBHOOK_TOKEN>     (HTTP POST)

   That's it — text the number and the bot replies.

How a reply is chosen

For each inbound message the bot, in order:

1. Records the sender in sms_contacts (incrementing their message count).
2. Flips opt-out state on STOP-family keywords (STOP, UNSUBSCRIBE, CANCEL, END, QUIT) and clears it on START/YES/UNSTOP.
3. Logs the inbound message.
4. Picks the reply: a STOP/START compliance message, else the row in sms_keywords matching the message (case-insensitive, trimmed), else the __DEFAULT__ reply.
5. Logs the outbound reply and returns it as TwiML.

Try it without Twilio

Twilio POSTs application/x-www-form-urlencoded, so you can simulate it with curl:

# A known keyword
curl -X POST "https://<your-host>/sms/inbound?token=<WEBHOOK_TOKEN>" \
  -H "content-type: application/x-www-form-urlencoded" \
  --data "From=%2B15551234567&To=%2B15559876543&Body=HOURS&MessageSid=SMxxxx"
# -> <?xml version="1.0" ...><Response><Message>We're open Mon-Fri 9am-5pm...</Message></Response>

# Opt out
curl -X POST "https://<your-host>/sms/inbound?token=<WEBHOOK_TOKEN>" \
  -H "content-type: application/x-www-form-urlencoded" \
  --data "From=%2B15551234567&Body=STOP&MessageSid=SMyyyy"

# See the conversation
curl "https://<your-host>/sms/messages?contact=%2B15551234567"

Managing keywords

# Add or update a keyword
curl -X POST https://<your-host>/sms/keywords/set \
  -H "content-type: application/json" \
  -d '{"keyword":"PROMO","reply":"Use code SAVE20 for 20% off!"}'

# Change the fallback reply for unmatched messages
curl -X POST https://<your-host>/sms/keywords/set \
  -H "content-type: application/json" \
  -d '{"keyword":"__DEFAULT__","reply":"Thanks! A team member will reply shortly."}'

Securing the webhook

Twilio's own request signature (X-Twilio-Signature) is computed over the URL plus sorted POST parameters, which is awkward to validate declaratively. This starter uses a simpler, effective approach: a shared-secret token in the webhook URL (?token=<WEBHOOK_TOKEN>). Requests without the correct token get 403. For defence in depth you can additionally restrict inbound traffic to Twilio's IP ranges at your edge/proxy.

Opt-out & compliance

The bot tracks opted_out per contact and sends the standard unsubscribe confirmation on STOP.

Important — Twilio handles STOP/START/HELP itself on most US numbers. For US long codes (and any number with Advanced Opt-Out), Twilio intercepts the standard keywords (STOP, UNSUBSCRIBE, START, HELP, …) at the carrier level, before your webhook is ever called — it sends its own compliance reply and adds the sender to an opt-out list. While a number is opted out, any outbound message to it is rejected with error 21610 ("Attempt to send to unsubscribed recipient") until the user texts START. So on those numbers the bot's own STOP/START branch is mostly a redundant internal record; it becomes the actual handler on numbers where Twilio's keyword handling is off (many non-US numbers, or Messaging Services with Advanced Opt-Out disabled). Either way, sms_contacts.opted_out is a good gate for any outbound campaigns you build on top.

Extending it

• Smarter replies: swap the DetermineReply step for an LLM call (e.g. the approach in the RAG Chatbot and LLM Gateway packs) to answer free-form questions.
• Outbound / proactive SMS: add a route that calls the Twilio REST API (/2010-04-01/Accounts/<AccountSid>/Messages.json) with HTTP Basic auth to send notifications or broadcasts to opted-in contacts.

Files

• config.yml — the bot (inbound webhook + read/admin routes).
• seed.yml — POST /sms/seed: tables + sample menu and conversation.
• schema.sql — the table definitions, for reference or manual setup.

Configuration

config.yml

name: TwilioSmsBot
description: >-
  A Twilio SMS bot served straight from your database. Receives inbound SMS via a
  Twilio webhook, replies instantly with TwiML, routes keyword commands to canned
  replies, handles STOP/START opt-out, and logs every message. No app server.

docs: true

# Required managed variables:
#   DATABASE_URL  — Postgres connection string
#   WEBHOOK_TOKEN — shared secret appended to the Twilio webhook URL as ?token=...
#                   (see README — Twilio's signature scheme is validated via a
#                   shared-secret URL token in this starter).

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # ---------------------------------------------------------------------------
  # POST /sms/inbound  — the Twilio webhook
  # ---------------------------------------------------------------------------
  # Configure this as the "A MESSAGE COMES IN" webhook on your Twilio number:
  #   https://<your-host>/sms/inbound?token=<WEBHOOK_TOKEN>   (HTTP POST)
  #
  # Twilio POSTs application/x-www-form-urlencoded fields (From, To, Body,
  # MessageSid, ...). The bot logs the message, decides a reply, and returns
  # TwiML — Twilio delivers the <Message> text back to the sender. Because the
  # reply is TwiML, no Twilio API credentials are needed for the bot to respond.
  sms/inbound:
    output: http
    method: POST
    summary: Twilio inbound SMS webhook
    description: >-
      Receives an inbound SMS from Twilio (form-urlencoded), records it, applies
      keyword routing and STOP/START opt-out, and replies with TwiML. Returns an
      XML (TwiML) body, not JSON.
    tags: [twilio, sms, webhook]
    request_example:
      From: "+15551234567"
      To: "+15559876543"
      Body: "HOURS"
      MessageSid: "SM0123456789abcdef0123456789abcdef"
    response_example: "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Response><Message>We're open Mon-Fri, 9am-5pm.</Message></Response>"

    actions:
      # Shared-secret check — the webhook URL must carry ?token=<WEBHOOK_TOKEN>.
      - name: Authorize
        input: a|params|
        hide_data_on_success: true
        assert:
          http_code_on_error: 403
          error_message: "Forbidden — invalid or missing token"
          tests:
            - value: token
              is_not_null: true
              is_not_empty: true
              is_equal_to: a|ap_var::WEBHOOK_TOKEN|
              description: "Must be present and match the WEBHOOK_TOKEN managed variable"

      # Twilio always sends From + Body for an SMS.
      - name: ValidateInbound
        run_when_succeeded:
          actions: [Authorize]
          http_code_on_error: 403
        input: a|body|
        assert:
          http_code_on_error: 422
          error_message: "Missing From or Body"
          tests:
            - value: From
              is_not_null: true
              description: "Sender's phone number"
            - value: Body
              is_not_null: true
              description: "Message text"

      # Track the sender; flip opt-out state on STOP/START keywords.
      - name: UpsertContact
        run_when_succeeded:
          actions: [ValidateInbound]
          http_code_on_error: 422
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO sms_contacts (contact_number, message_count, opted_out)
          VALUES (
            $1,
            1,
            CASE WHEN UPPER(TRIM($2)) IN ('STOP','STOPALL','UNSUBSCRIBE','CANCEL','END','QUIT')
                 THEN true ELSE false END
          )
          ON CONFLICT (contact_number) DO UPDATE SET
            last_seen     = now(),
            message_count = sms_contacts.message_count + 1,
            opted_out     = CASE
              WHEN UPPER(TRIM($2)) IN ('STOP','STOPALL','UNSUBSCRIBE','CANCEL','END','QUIT') THEN true
              WHEN UPPER(TRIM($2)) IN ('START','YES','UNSTOP')                               THEN false
              ELSE sms_contacts.opted_out
            END
          RETURNING contact_number, opted_out, message_count;
        params:
          - a|ValidateInbound::From|
          - a|ValidateInbound::Body|
        post_transforms:
          - extract_value: "[0]"

      # Log the inbound message.
      - name: LogInbound
        run_when_succeeded:
          actions: [UpsertContact]
          http_code_on_error: 500
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO sms_messages (message_sid, contact_number, direction, body)
          VALUES ($1, $2, 'inbound', $3);
        params:
          - a|ValidateInbound::MessageSid->default(null)|
          - a|ValidateInbound::From|
          - a|ValidateInbound::Body|

      # Decide the reply: STOP/START get compliance replies; otherwise look up the
      # keyword table, falling back to the '__DEFAULT__' row.
      - name: DetermineReply
        run_when_succeeded:
          actions: [LogInbound]
          http_code_on_error: 500
        database: main
        query: |
          SELECT
            CASE
              WHEN UPPER(TRIM($1)) IN ('STOP','STOPALL','UNSUBSCRIBE','CANCEL','END','QUIT')
                   THEN 'You have been unsubscribed and will receive no further messages. Reply START to resubscribe.'
              WHEN UPPER(TRIM($1)) IN ('START','YES','UNSTOP')
                   THEN 'You are resubscribed. Welcome back! Reply HELP for the menu.'
              ELSE COALESCE(
                (SELECT reply FROM sms_keywords WHERE keyword = UPPER(TRIM($1)) LIMIT 1),
                (SELECT reply FROM sms_keywords WHERE keyword = '__DEFAULT__'   LIMIT 1),
                'Thanks for your message! Reply HELP to see what I can do.'
              )
            END AS reply,
            (SELECT keyword FROM sms_keywords WHERE keyword = UPPER(TRIM($1)) LIMIT 1) AS matched_keyword;
        params:
          - a|ValidateInbound::Body|
        post_transforms:
          - extract_value: "[0]"

      # Log the outbound reply, then return it as TwiML. The CDATA section keeps the
      # reply text safe inside the XML regardless of punctuation it contains.
      - name: LogOutbound
        run_when_succeeded:
          actions: [DetermineReply]
          http_code_on_error: 500
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO sms_messages (contact_number, direction, body, matched_keyword)
          VALUES ($1, 'outbound', $2, $3);
        params:
          - a|ValidateInbound::From|
          - a|DetermineReply::reply|
          - a|DetermineReply::matched_keyword->default(null)|
        response_on_success:
          http_code: 200
          headers:
            content-type: "application/xml; charset=utf-8"
          body: |
            <?xml version="1.0" encoding="UTF-8"?><Response><Message><![CDATA[a|DetermineReply::reply|]]></Message></Response>

  # ---------------------------------------------------------------------------
  # GET /sms/messages?contact=+15551234567  — conversation log
  # ---------------------------------------------------------------------------
  sms/messages:
    output: http
    method: GET
    summary: List logged messages
    description: >-
      Returns the most recent messages (newest first). Pass ?contact=<number> to
      filter to a single conversation.
    tags: [sms, messages]
    response_example:
      - id: 2
        contact_number: "+15551234567"
        direction: outbound
        body: "We're open Mon-Fri, 9am-5pm."
        matched_keyword: HOURS
        created_at: "2026-06-21T12:00:01Z"

    actions:
      - name: ListMessages
        database: main
        query: |
          SELECT id, message_sid, contact_number, direction, body, matched_keyword, created_at
          FROM sms_messages
          WHERE ($1::text IS NULL OR contact_number = $1)
          ORDER BY created_at DESC
          LIMIT 100;
        params:
          - a|params::contact->default(null)|

  # ---------------------------------------------------------------------------
  # GET /sms/contacts  — senders + opt-out status
  # ---------------------------------------------------------------------------
  sms/contacts:
    output: http
    method: GET
    summary: List contacts
    description: Returns every number that has texted in, with message count and opt-out status.
    tags: [sms, contacts]
    response_example:
      - contact_number: "+15551234567"
        opted_out: false
        message_count: 3
        last_seen: "2026-06-21T12:00:00Z"

    actions:
      - name: ListContacts
        database: main
        query: |
          SELECT contact_number, opted_out, message_count, first_seen, last_seen
          FROM sms_contacts
          ORDER BY last_seen DESC
          LIMIT 100;

  # ---------------------------------------------------------------------------
  # GET /sms/keywords  — the bot's keyword menu
  # ---------------------------------------------------------------------------
  sms/keywords:
    output: http
    method: GET
    summary: List keywords
    description: Returns the configured keyword replies (excludes the internal __DEFAULT__ row).
    tags: [sms, keywords]
    response_example:
      - keyword: HOURS
        reply: "We're open Mon-Fri, 9am-5pm."
        updated_at: "2026-06-21T12:00:00Z"

    actions:
      - name: ListKeywords
        database: main
        query: |
          SELECT keyword, reply, updated_at
          FROM sms_keywords
          WHERE keyword <> '__DEFAULT__'
          ORDER BY keyword;

  # ---------------------------------------------------------------------------
  # POST /sms/keywords  — add or update a keyword reply
  # ---------------------------------------------------------------------------
  # Body: { "keyword": "HOURS", "reply": "We're open Mon-Fri, 9am-5pm." }
  # Use keyword "__DEFAULT__" to set the fallback reply for unmatched messages.
  sms/keywords/set:
    output: http
    method: POST
    summary: Add or update a keyword
    description: Upserts a keyword -> reply mapping. The keyword is normalised to UPPER/trimmed.
    tags: [sms, keywords]
    request_example:
      keyword: HOURS
      reply: "We're open Mon-Fri, 9am-5pm."
    response_example:
      keyword: HOURS
      reply: "We're open Mon-Fri, 9am-5pm."
      updated_at: "2026-06-21T12:00:00Z"

    actions:
      - name: ValidateKeyword
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: keyword
              is_not_null: true
              is_not_empty: true
              description: "Keyword to match (case-insensitive)"
            - value: reply
              is_not_null: true
              is_not_empty: true
              description: "Reply text to send"

      - name: UpsertKeyword
        run_when_succeeded:
          actions: [ValidateKeyword]
          http_code_on_error: 400
        database: main
        query: |
          INSERT INTO sms_keywords (keyword, reply)
          VALUES (UPPER(TRIM($1)), $2)
          ON CONFLICT (keyword) DO UPDATE SET
            reply      = EXCLUDED.reply,
            updated_at = now()
          RETURNING keyword, reply, updated_at;
        params:
          - a|ValidateKeyword::keyword|
          - a|ValidateKeyword::reply|
        post_transforms:
          - extract_value: "[0]"

seed.yml

name: TwilioSmsBotSeed
description: >-
  Creates the SMS bot tables (idempotent) and loads a sample keyword menu plus a
  short sample conversation. Safe to re-run — truncates before inserting.

docs: true

# Required managed variables:
#   DATABASE_URL — Postgres connection string

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # POST /sms/seed
  # Creates all tables/indexes, then loads sample keywords and a sample thread.
  sms/seed:
    output: http
    method: POST
    summary: Seed the SMS bot
    description: Creates tables and loads a sample keyword menu + conversation. Idempotent.
    tags: [seed]

    actions:
      - name: CreateKeywordsTable
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS sms_keywords (
            keyword    TEXT        PRIMARY KEY,
            reply      TEXT        NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
          );

      - name: CreateContactsTable
        run_when_succeeded: [CreateKeywordsTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS sms_contacts (
            contact_number TEXT        PRIMARY KEY,
            opted_out      BOOLEAN     NOT NULL DEFAULT false,
            message_count  INTEGER     NOT NULL DEFAULT 0,
            first_seen     TIMESTAMPTZ NOT NULL DEFAULT now(),
            last_seen      TIMESTAMPTZ NOT NULL DEFAULT now()
          );

      - name: CreateMessagesTable
        run_when_succeeded: [CreateContactsTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS sms_messages (
            id              BIGSERIAL   PRIMARY KEY,
            message_sid     TEXT,
            contact_number  TEXT        NOT NULL,
            direction       TEXT        NOT NULL CHECK (direction IN ('inbound', 'outbound')),
            body            TEXT        NOT NULL,
            matched_keyword TEXT,
            created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
          );

      - name: CreateIndex
        run_when_succeeded: [CreateMessagesTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE INDEX IF NOT EXISTS idx_sms_messages_contact
            ON sms_messages (contact_number, created_at DESC);

      - name: TruncateTables
        run_when_succeeded: [CreateIndex]
        database: main
        hide_data_on_success: true
        query: |
          TRUNCATE sms_messages, sms_contacts, sms_keywords RESTART IDENTITY;

      - name: InsertKeywords
        run_when_succeeded: [TruncateTables]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO sms_keywords (keyword, reply) VALUES
            ('HELP',       'Reply with: HOURS, LOCATION, BOOK, or PRICING. Reply STOP to unsubscribe.'),
            ('HOURS',      'We''re open Mon-Fri 9am-5pm, Sat 10am-2pm. Closed Sundays.'),
            ('LOCATION',   'Find us at 123 Main St, Springfield. Map: https://example.com/map'),
            ('BOOK',       'Book an appointment here: https://example.com/book'),
            ('PRICING',    'Plans start at $49/mo. Details: https://example.com/pricing'),
            ('__DEFAULT__','Thanks for your message! Reply HELP to see what I can do.');

      - name: InsertSampleContact
        run_when_succeeded: [InsertKeywords]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO sms_contacts (contact_number, message_count) VALUES ('+15551234567', 2);

      - name: InsertSampleMessages
        run_when_succeeded: [InsertSampleContact]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO sms_messages (contact_number, direction, body, matched_keyword) VALUES
            ('+15551234567', 'inbound',  'HOURS', NULL),
            ('+15551234567', 'outbound', 'We''re open Mon-Fri 9am-5pm, Sat 10am-2pm. Closed Sundays.', 'HOURS');

      - name: SeedSummary
        run_when_succeeded: [InsertSampleMessages]
        database: main
        query: |
          SELECT
            (SELECT count(*) FROM sms_keywords WHERE keyword <> '__DEFAULT__') AS keywords,
            (SELECT count(*) FROM sms_contacts)  AS contacts,
            (SELECT count(*) FROM sms_messages)  AS messages;
        post_transforms:
          - extract_value: "[0]"

schema.sql

-- Twilio SMS Bot — Postgres schema
--
-- Three tables:
--   sms_keywords  — keyword -> canned reply (the bot's "brain"). Keywords are
--                   stored normalised (UPPER, trimmed). A row with keyword
--                   '__DEFAULT__' is the fallback reply for unmatched messages.
--   sms_contacts  — one row per phone number that has texted in, with a message
--                   count and an opt-out flag (STOP/START compliance).
--   sms_messages  — full conversation log (both inbound and outbound), keyed by
--                   the contact's phone number so a thread groups cleanly.

CREATE TABLE IF NOT EXISTS sms_keywords (
    keyword    TEXT        PRIMARY KEY,
    reply      TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS sms_contacts (
    contact_number TEXT        PRIMARY KEY,
    opted_out      BOOLEAN     NOT NULL DEFAULT false,
    message_count  INTEGER     NOT NULL DEFAULT 0,
    first_seen     TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS sms_messages (
    id              BIGSERIAL   PRIMARY KEY,
    message_sid     TEXT,
    contact_number  TEXT        NOT NULL,
    direction       TEXT        NOT NULL CHECK (direction IN ('inbound', 'outbound')),
    body            TEXT        NOT NULL,
    matched_keyword TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sms_messages_contact ON sms_messages (contact_number, created_at DESC);