SMS Templates and Twilio Transport

Purpose

Admins CRUD a small library of SMS templates and the platform renders one against a dict of variables to send it through Twilio, logging the outcome; the scheduler also sends a handful of hardcoded (non-template) booking-reminder SMS through the same transport (services/scheduled_jobs.py 1403, 1467). The invariant the recipe scope names, "the inline SMSLog row written on both success and failure", does not hold at this commit: both write sites pass a template_name keyword to SMSLog(...), a column that does not exist on the model, so the write raises TypeError and is silently swallowed by an enclosing except Exception that only logs a warning (sms_service.py 161-174, 184-197; confirmed live against this commit's model, see Gotchas). What is actually trustworthy is narrower: a send either reaches Twilio and returns its message SID, or fails and returns an error string, both without ever raising into the caller.

When to use

Trigger phrases: "SMS template", "SMS_ENABLED", "mock SMS", "Twilio SMS", "{{variable}} SMS", "SMSLog", "booking reminder SMS", "test SMS". Read it before touching SMSService.send_sms, send_from_template, send_test_sms, send_booking_reminder, or the five CRUD routes under /templates/sms.

Not this recipe: platform email templates and Resend/SMTP transport (services/email_service.py, api/routers/mail_router.py, a neighbouring messaging-email-sms recipe); Telegram delivery (domain description names it, no Telegram code was found under services/ or api/routers/ at this commit, see Gotchas); the calendar booking/confirmation-token logic that builds the URL passed into send_booking_reminder (services/scheduled_jobs.py, calendar-booking domain); the cancellation-on-no-confirm flow that calls send_sms directly with an inline hardcoded string (cancel_unconfirmed_bookings, scheduled_jobs.py, cited here for context, not this recipe's subject).

Stack assumptions

At 62ba4fc: fastapi==0.118.2, synchronous handlers on sqlalchemy.orm.Session (SQLAlchemy==2.0.43), twilio==9.5.1 (twilio.rest.Client, lazy-constructed), pydantic==2.12.0, psycopg2-binary==2.9.10. SMSService.create_template/update_template call the Pydantic v1-style .dict() method (sms_service.py 60, 75) rather than model_dump(); it still works under pydantic 2.12 (deprecated alias) but a rebuild on a pydantic version that drops the alias breaks both methods. Foundations assumed: S4 (fastapi-project-layout) for mount_router and the app; S5 (auth-dependency-pattern) for AdminUser/DatabaseSession (api/dependencies.py 364-375, 568, 572); S8 (alembic-migrations) for the baseline-reconciliation pattern the two tables ship under (Data model).

Environment, core/config.py 570-573: TWILIO_ACCOUNT_SID (str, optional), TWILIO_AUTH_TOKEN (SecretStr, optional), TWILIO_PHONE_NUMBER (str, optional, E.164), SMS_ENABLED (bool, default=False). Fail behaviour is entirely soft: get_twilio_client() returns None if either credential is unset or the SDK import/construction raises (sms_service.py 23-36, exception logged, never raised); send_sms never raises for any of these three env gaps, it returns {"status": "failed", "error": "..."} for a missing client or missing phone number (138-148), or {"status": "mock", ...} when SMS_ENABLED is false (129-135, and prints the mock payload to stdout, not just the log).

Data model

Two tables, no workspace/tenant column on either (models/client_onboarding_model.py 284-309; baseline SQL, migrations/versions/baseline_prod_schema_001.py 1798-1829):

sms_templates (
  id               uuid PRIMARY KEY,
  name             varchar NOT NULL,          -- UNIQUE (uq_sms_templates_name)
  message_body     text NOT NULL,
  category         smscategory,               -- onboarding | reminder | payment | project_update (core/enums.py 154-159)
  character_count  integer,                   -- set by the service, len(message_body), never enforced by a CHECK
  is_active        boolean,
  created_at       timestamptz DEFAULT now(),
  updated_at       timestamptz
);
CREATE INDEX ix_sms_templates_category ON sms_templates (category);

sms_logs (
  id                  uuid PRIMARY KEY,
  sms_template_id     uuid REFERENCES sms_templates(id),   -- FK exists; sms_service.py never sets it (see Gotchas)
  recipient_phone     varchar NOT NULL,
  recipient_name      varchar,
  message_body        text,
  status              smsstatus,              -- queued | sent | delivered | failed (core/enums.py 162-167), DEFAULT queued
  twilio_message_sid  varchar,                -- UNIQUE (uq_sms_logs_twilio_message_sid)
  metadata            json,                   -- Python attr meta_data; never populated by sms_service.py
  sent_at             timestamptz,
  delivered_at        timestamptz,            -- never written by any code found at this commit (no delivery webhook)
  failed_at           timestamptz,            -- never written; the failure path sets no timestamp column at all
  failure_reason      varchar,                -- never written; sms_service.py writes a non-existent `error_message` instead
  created_at          timestamptz DEFAULT now()
);
CREATE INDEX ix_sms_logs_recipient_phone ON sms_logs (recipient_phone);
CREATE INDEX ix_sms_logs_status ON sms_logs (status);

No CHECK constraint on either table at this commit (only the two UNIQUEs and the two secondary indexes above; the FK from sms_logs.sms_template_id is added separately by deferred_fk_phantom_tables_001.py 733-736, in line with the baseline's "phantom table" reconciliation pattern, S8). What is NOT stored: no per-template variables: List[str] declaration the way EmailTemplateBase has one (schemas/communication_schemas.py 14-21 vs 54-58) - an SMS template's {{placeholders}} are discoverable only by reading message_body; no booking_id column on sms_logs even though send_sms/send_from_template/send_booking_reminder all accept a booking_id keyword (sms_service.py 102, 210, 269) - it is accepted and silently dropped, never referenced again in any of the three functions; no delivery-status webhook wiring exists to ever move a row from queued/sent to delivered or set delivered_at.

API surface

Router sms_templates_router.py 16, declared with its own prefix="/templates/sms", then mounted a second time with prefix="/api/templates/sms" (app_fastapi.py 876) - FastAPI concatenates both prefixes, so every route below actually lives at /api/templates/sms/templates/sms..., not /api/templates/sms/... (confirmed by constructing the same APIRouter+include_router call against this router's exact prefix in a throwaway FastAPI app at this commit; see Gotchas and Acceptance checks).

Route (as mounted)	Auth	Tenancy	Codes	Notes
GET /api/templates/sms/templates/sms (19-22)	AdminUser	none (global)	200 List[SMSTemplateResponse]	ordered by SMSTemplate.name (sms_service.py 49)
POST /api/templates/sms/templates/sms (24-27)	AdminUser	none	201 SMSTemplateResponse; 409 if name already exists (create_template, 55-57)	character_count = len(message_body) computed server-side
GET /api/templates/sms/templates/sms/{template_id} (29-35)	AdminUser	none	200; 404
PUT /api/templates/sms/templates/sms/{template_id} (37-40)	AdminUser	none	200; 404 (raised inside update_template, 72-73)	exclude_unset=True partial update; character_count recomputed only if message_body is one of the changed keys (79-81)
DELETE /api/templates/sms/templates/sms/{template_id} (42-46)	AdminUser	none	204; 404	hard delete, no sms_logs.sms_template_id is ever populated so nothing references it anyway
POST /api/templates/sms/templates/sms/test (48-55)	AdminUser	none	200 {"status":"success","message":...}; 400 on any exception	except Exception as e: raise HTTPException(400, ...) wraps send_test_sms, which itself raises HTTPException(404, ...) when the template id doesn't exist (sms_service.py 251) - that 404 is an Exception too, so it is re-caught and re-raised as 400, not 404 (measured by reading both call sites; no test exercises this)

AdminUser = Annotated[User, Depends(get_current_admin_user)] (api/dependencies.py 568, 364-375): 403 if current_user.is_admin is false. The mount also adds a SurfaceGate(LABS_SURFACES) dependency ahead of the route (mount_router, api/surface_gate.py 265-274) - a second, surface-level gate, not detailed further here (S5's scope).

Application logic

send_sms (sms_service.py 97-202), in order:

1. Reject if recipient_phone is falsy or does not start with "+" (121-126) - this is the entirety of the "E.164 validation"; no length or digit check, +abc passes.
2. If settings.SMS_ENABLED is false: print a bannered mock payload to stdout, log it, return {"status": "mock", "message": "SMS sending disabled"} (129-135). Nothing is written to sms_logs on this path.
3. Resolve the Twilio client (get_twilio_client, 23-36, module-level singleton, lazily constructed from TWILIO_ACCOUNT_SID/TWILIO_AUTH_TOKEN); missing client or missing TWILIO_PHONE_NUMBER returns a "failed" dict, no exception (138-148).
4. client.messages.create(body=message, from_=TWILIO_PHONE_NUMBER, to=recipient_phone) (152-156).
5. On success: attempt to write an SMSLog(recipient_phone=..., message_body=..., template_name=..., twilio_message_sid=..., status="sent", sent_at=utcnow()) (163-170) - raises TypeError: 'template_name' is an invalid keyword argument for SMSLog, caught by except Exception as log_error: logger.warning(...) (173-174); the row is never inserted, only a warning is logged. Returns {"status": "sent", "message_sid": ...} regardless.
6. On any exception from step 4 (Twilio raising): attempt to write SMSLog(recipient_phone=..., message_body=..., template_name=..., status="failed", error_message=str(e)) (186-192) - same template_name TypeError (the model also has no error_message column, failure_reason is the real one, but the constructor never reaches that kwarg because template_name raises first), caught the same way (196-197). Returns {"status": "failed", "error": str(e)} regardless.

send_from_template (204-243): looks up SMSTemplate by name (227); not found returns a "failed" dict, no exception. Rendering (232-235) is a plain Python loop, body.replace(f"{{{{{key}}}}}", str(value)) per (key, value) in the caller's dict - literal {{key}} substring replace, not a template engine: no escaping, a {{var}} whose key is absent from dynamic_data is left verbatim in the outgoing SMS, and nothing validates that the dict covers every placeholder in message_body.

send_test_sms (245-258): fetches the template by id via get_template (raises HTTPException(404) itself if missing, 251), then calls send_from_template with a second, redundant lookup by name and a hardcoded payload {"client_name": "Test User", "time_remaining": "3 hours"} (256) - a template using any other placeholder name renders with that literal {{placeholder}} left in the test message.

send_booking_reminder (260-308): bypasses SMSTemplate/send_from_template entirely - the 24h and 1h reminder bodies are hardcoded German f-strings built inline (287-300); template_name="booking_reminder_24h"/"booking_reminder_1h" is passed only as the (broken) SMSLog label, never used to look anything up.

Invariants

• A send never raises into its caller: send_sms's Twilio call is the only network I/O and it is wrapped in try/except Exception that always returns a status dict (150-202). No automated test asserts this; verified by reading.
• SMS_ENABLED=False never reaches Twilio: the check at 129 is unconditional and precedes client construction. No automated test asserts this either.
• Not actually held at this commit: "an SMSLog row is written on both success and failure" - both write sites raise and are swallowed (see Application logic steps 5-6, Gotchas).
• character_count on SMSTemplate is a snapshot taken at write time (create_template 61, update_template 80-81), never recomputed on read; editing message_body through any path other than these two service methods (e.g. a direct SQL update) leaves it stale.

Frontend

messaging-email-sms/ui/email-template-editor   the module id this recipe is required to name (task
                                                convention for this domain); at 62ba4fc it renders the
                                                EMAIL side of AdminCommunication.tsx for real (see
                                                below) - no SMS-specific editor module exists in this
                                                repo's frontend to point to instead

Measured, not inferred: frontend/src/pages/AdminCommunication.tsx has an activeTab: 'email' | 'sms' toggle. The email tab calls the real API (api.get('/api/templates/emails') line 63, api.delete('/api/templates/emails/${id}') 117, api.post('.../preview', ...) 127). The SMS tab's fetchTemplates branch never calls the network at all - setSmsTemplates([...]) is a four-item hardcoded array with a comment reading "SMS templates - still using mock data for now" (66-99); there is no create/edit/delete/test call anywhere in the file for SMS. The mock objects even carry a usage_count field (e.g. 42) that does not exist anywhere in SMSTemplateResponse or the sms_templates table. Minimum admin/debug surface for a rebuild: the five CRUD routes above are complete and admin-gated, but at this commit there is no real consumer of them; a rebuild wiring the SMS tab to the real endpoints must also fix the doubled mount prefix first (API surface) or every call 404s.

Security & tenancy

Auth dependency: AdminUser on every route (api/dependencies.py 364-375, 568), 403 if not is_admin; plus the mount-level SurfaceGate(LABS_SURFACES) (api/surface_gate.py 265-274, S5). Tenancy: none - sms_templates/sms_logs carry no workspace/tenant column (Data model), this is a single global template library shared by the whole admin surface, not a per-workspace object; a multi-tenant rebuild must add that scoping, it does not exist to copy. Fail direction: admin gate is closed (403/401 on failure); the transport itself fails open in the sense that every credential or validation gap returns a "failed"/"mock" status rather than throwing, so a misconfigured deploy degrades to "SMS silently not sent" rather than a loud error (Stack assumptions).

Secrets at rest: TWILIO_AUTH_TOKEN is a SecretStr (core/config.py 571), unwrapped only at client construction (get_secret_value(), sms_service.py 31); no token or webhook signature verification exists on this router (it has no inbound webhook, unlike the billing engine's HMAC seam). Rate limits: none dedicated to /templates/sms. Attacker-controlled input: recipient_phone and message_body on every route are admin-only (not public), so the main risk is an admin-authored template with an unescaped {{var}} reaching an SMS recipient verbatim if the caller's dynamic_data omits a key (Application logic).

Tests

None. grep -rl "sms_service\|SMSTemplate\|SMSLog\|sms_templates_router" backend/tests/ (excluding .claude/worktrees/) returns zero files at this commit - no hermetic test, no known-answer vector, no Playwright gate, nothing exercises send_sms, send_from_template, send_test_sms, send_booking_reminder, or any of the five router endpoints. This is a genuine gap, not an omission from this recipe: a rebuild has no existing suite to port, and the TypeError in SMSLog construction (Application logic) would have been caught immediately by even one test that asserted a row was inserted.

Deploy & ops

Config keys per app: TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN, TWILIO_PHONE_NUMBER, SMS_ENABLED (all core/config.py 570-573, all optional/default-false, no boot-time validation the way email's SMTP path has one - contrast messaging-email-sms/email-transport's validate_email_smtp_credentials). Migrations: both tables ship in the single baseline-reconciliation migration baseline_prod_schema_001.py (revision baseline_prod_schema_001, down_revision=None) as pg_dump-captured SQL (1798-1829), with the sms_logs → sms_templates FK added later by deferred_fk_phantom_tables_001.py (733-736) per that chain's own documented reason (FKs deferred until every phantom table exists). No scheduler job specific to this recipe; send_booking_reminder/send_sms are called synchronously from scheduled_jobs.py's existing booking-reminder and no-confirm-cancellation jobs (1403, 1467), which are out of this recipe's scope. No Argus-style check was found for this seam.

Rollback: stateless beyond the two tables; neither is ever deleted from by this recipe's code, so rolling back the application code needs no data unwind.

Gotchas

• 2026-09-12 (measured at 62ba4fc, app_fastapi.py 876 + sms_templates_router.py 16): the router declares prefix="/templates/sms" on its own APIRouter AND is mounted with prefix="/api/templates/sms". FastAPI's include_router concatenates both, so the live path is /api/templates/sms/templates/sms (and .../templates/sms/{id}, .../templates/sms/test), not the /api/templates/sms/... the naming clearly intends. Reproduced directly: building the identical APIRouter(prefix="/templates/sms") + app.include_router(router, prefix="/api/templates/sms") pair in a throwaway FastAPI app at this commit's dependency versions prints the route /api/templates/sms/templates/sms. Nothing in the frontend calls either path (Frontend), which is likely why this has never been noticed.
• 2026-09-12 (measured at 62ba4fc, sms_service.py 163-170 and 186-192): both SMSLog(...) construction sites pass template_name=..., a keyword that is not a column on the model (models/client_onboarding_model.py 295-309 has sms_template_id, not template_name). Confirmed live: SMSLog(recipient_phone=..., message_body=..., template_name="x", status="sent") raises TypeError: 'template_name' is an invalid keyword argument for SMSLog against this exact model. Both call sites wrap the construction in try/except Exception: logger.warning(...), so every single send, success or failure, fails to log and only emits a warning line. The failure-path call additionally passes error_message=..., also not a column (the real one is failure_reason) - moot, since template_name raises first in kwarg-application order.
• 2026-09-12 (measured at 62ba4fc, frontend/src/pages/AdminCommunication.tsx 66-99): the SMS tab of the only admin surface for this feature is entirely mock data with an explicit "still using mock data for now" comment; the email tab beside it is fully wired. No frontend code in this repo calls any /templates/sms route.
• 2026-09-12 (measured at 62ba4fc): the messaging-email-sms domain description (domains.json) names Telegram via the Telegram Bot API as an in-scope transport; no Telegram-related code was found under backend/services/ or backend/api/routers/ at this commit. If it exists it is not part of this reference implementation.
• 2026-09-12 (measured at 62ba4fc, sms_templates_router.py 48-55): the /test endpoint's blanket except Exception as e: raise HTTPException(400, ...) re-catches the HTTPException(404) that send_test_sms raises for a missing template id and re-raises it as 400, so a client cannot distinguish "template not found" from any other test-send failure by status code alone.

Reference implementation

Paths relative to the repo root; all at main 62ba4fc41009b454e1c033e5036c7e8577db2061 unless noted.

• alchemy_labs/backend/services/sms_service.py (311 lines, whole file in scope): get_twilio_client (23-36); SMSService.get_template/list_templates (41-49); create_template (51-66); update_template (68-85); delete_template (87-95); send_sms (97-202); send_from_template (204-243); send_test_sms (245-258); send_booking_reminder (260-308); module singleton sms_service (311).
• alchemy_labs/backend/api/routers/sms_templates_router.py (54 lines, whole file in scope): router declaration (16); list_sms_templates (19-22); create_sms_template (24-27); get_sms_template (29-35); update_sms_template (37-40); delete_sms_template (42-46); send_test_sms_template (48-55).
• alchemy_labs/backend/models/client_onboarding_model.py: SMSTemplate (284-293); SMSLog (295-309).
• alchemy_labs/backend/schemas/communication_schemas.py: SMSTemplateBase (54-58); SMSTemplateCreate/Update/Response (62-76); SendTestSMSRequest (78-80).
• alchemy_labs/backend/core/enums.py: SMSCategory (154-159); SMSStatus (162-167).
• alchemy_labs/backend/core/config.py: TWILIO_ACCOUNT_SID/TWILIO_AUTH_TOKEN/TWILIO_PHONE_NUMBER/SMS_ENABLED (570-573).
• alchemy_labs/backend/app_fastapi.py: import (76); mount (876).
• alchemy_labs/backend/api/dependencies.py: get_current_admin_user (364-375); AdminUser (568); DatabaseSession (572).
• alchemy_labs/backend/services/scheduled_jobs.py: send_booking_reminder caller (1403); direct send_sms caller in cancel_unconfirmed_bookings (1467), out of this recipe's scope, cited for context.
• alchemy_labs/backend/migrations/versions/baseline_prod_schema_001.py: sms_logs table (1798-1815); sms_templates table (1817-1829); indexes (3906-3918); unique constraints (3018-3027); primary keys (2748-2757).
• alchemy_labs/backend/migrations/versions/deferred_fk_phantom_tables_001.py: sms_logs.sms_template_id FK add (733-736).
• alchemy_labs/frontend/src/pages/AdminCommunication.tsx: SMSTemplate interface (35-42); mock fetchTemplates branch (66-99); real email calls for contrast (63, 117, 127).

No test files exist for this recipe at this commit (Tests). Deviations from an ideal implementation, all dated 2026-09-12: the double-mounted route prefix; the SMSLog write's invalid template_name/error_message keywords on both paths; the /test endpoint masking 404 as 400; zero frontend wiring for SMS despite email being fully wired in the same file; booking_id accepted by three functions and dropped everywhere.

Acceptance checks

1. grep -n 'prefix="/templates/sms"' backend/api/routers/sms_templates_router.py prints line 16; grep -n 'sms_templates_router.router' backend/app_fastapi.py prints line 876 showing prefix="/api/templates/sms" on the same call - both prefixes present confirms the doubling described in Gotchas.
2. In a Python shell with backend/.venv active from backend/: python -c "from models.client_onboarding_model import SMSLog; SMSLog(recipient_phone='+41791234567', message_body='hi', template_name='x', status='sent')" raises TypeError: 'template_name' is an invalid keyword argument for SMSLog.
3. grep -n "still using mock data" frontend/src/pages/AdminCommunication.tsx prints one match (line 66), and grep -c "api\.\(get\|post\|put\|delete\)" frontend/src/pages/AdminCommunication.tsx prints 3, all three referencing /api/templates/emails, none referencing /templates/sms.
4. grep -rl "sms_service\|SMSTemplate\|SMSLog\|sms_templates_router" backend/tests/ (run from alchemy_labs, excluding .claude/worktrees/) prints nothing.
5. grep -n "^ TWILIO_ACCOUNT_SID\|^ TWILIO_AUTH_TOKEN\|^ TWILIO_PHONE_NUMBER\|^ SMS_ENABLED" backend/core/config.py prints the four lines 570-573, SMS_ENABLED showing default=False.