# Two-way appointment reminders with VoxiSMS

Version 1 · October 2026. This inactive workflow contains no credentials or customer data. Configure and test it with your own PostgreSQL database and VoxiSMS line before activation.

Import [workflow.json](workflow.json) into n8n. The template has 20 functional nodes and five explanatory notes. It accepts authenticated booking updates, reserves reminders, tracks incoming confirmations and maintains a staff queue for exceptions.

## Requirements

- PostgreSQL 15 or later, reachable from n8n using TLS, with permission to install the dedicated schema once. No existing business tables are modified. This is a first installation script, not an upgrade migration.
- The verified VoxiSMS community node, a VoxiSMS credential, and an Android phone/line that can send and receive SMS. [VoxiSMS node setup](https://voxisms.com/integrations/n8n).
- A booking system that can send authenticated JSON webhooks, including updated/cancelled/completed bookings. This template does not create or modify bookings in the source system.
- An owner for the staff queue and n8n execution failures. There is no external staff notification service configured in this portable export.

## 1. Prepare a dedicated database

Run [schema.sql](schema.sql) once as a schema owner. It creates only `voxisms_appts`. If that schema already exists, the script fails instead of overwriting it. Use a separate database for this template when practical.

Create a dedicated login through your database provider. After replacing `voxisms_runtime` with that role name, grant:

```sql
GRANT USAGE ON SCHEMA voxisms_appts TO voxisms_runtime;
GRANT SELECT ON ALL TABLES IN SCHEMA voxisms_appts TO voxisms_runtime;
GRANT INSERT, UPDATE ON voxisms_appts.appointments,
  voxisms_appts.outbox, voxisms_appts.inbound_events,
  voxisms_appts.suppressions, voxisms_appts.staff_queue TO voxisms_runtime;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA voxisms_appts TO voxisms_runtime;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA voxisms_appts TO voxisms_runtime;
```

Functions use the caller's permissions; they do not run as an administrator. The runtime role cannot change the business live switch with these grants. Treat it as a trusted backend role, never an anonymous/public API role. The schema is not a tenant-isolation boundary; use a dedicated role/database for unrelated businesses. Do not expose this schema through a public SQL/REST API.

As owner, insert one business row. The following values are fictional; replace the customer ID and line with those on your VoxiSMS account before runtime testing. No API token belongs in SQL.

```sql
INSERT INTO voxisms_appts.businesses
  (id, display_name, customer_id, line_number, live_enabled)
VALUES ('default', 'Example Salon', '15555550100', '+15555550100', false);
```

Defaults: remind within 24 hours, exclude appointments less than/equal to one hour away, send 08:00–20:00 in the appointment's IANA timezone, flag unconfirmed appointments within three hours. Edit these fields in `businesses` if needed. Overnight sending windows are not supported. Each sweep handles up to 100 appointments, one at a time.

## 2. Import and configure n8n

1. Import `workflow.json` using **Import from File**. Keep it inactive/unpublished.
2. Select the dedicated PostgreSQL credential on all six Postgres nodes. Use TLS with certificate verification when connecting over a network.
3. Select your existing VoxiSMS credential on **Send VoxiSMS reminder** and **Verified incoming SMS**. Set customer_id to the credential's account ID and line_number to the actual Android/SIM receiving number; those can differ. Verify the sending number on a handset. Signed events may omit the leading + on toNumber; SQL normalizes only complete international digits before comparing the exact receiving line. Outbound sends cannot independently verify this mapping.
4. Create/select a **Header Auth** credential on **Booking intake**, for example a random secret in `X-Booking-Token`. Configure the source to send that header over HTTPS. Keep authentication enabled. Give each imported copy a unique webhook path if several copies share an n8n instance.
5. Set the same `businessId` in **Booking configuration**, **Reminder configuration** and **Reply configuration**. Leave all three `dryRun` fields true.
6. Review the five canvas notes. Keep retries disabled on the SMS node. Configure n8n failure monitoring and your execution-data retention policy before launch.

The database's `live_enabled=false` is a second lock. Both it and each branch's `dryRun=false` must be intentionally enabled for writes. The manual trigger uses the reminder configuration too; it is a preview only while that configuration remains in dry run. Successful execution data is not retained by default; failure data may include phone numbers and reply content.

## 3. Booking payload

[booking.example.json](booking.example.json) contains a fictional example. Replace dates with a future appointment and the actual source update timestamp. Every request, including cancel/complete, supplies all fields:

| Field | Meaning |
| --- | --- |
| externalId | Stable unique booking ID within the business, 1–100 characters. |
| customerName | Contact display name, 1–80 characters; not included in reminder text. |
| phone | E.164 with + and country code. No inferred country. |
| startsAt | ISO timestamp with Z or an explicit offset. |
| timezone | IANA zone for appointment display and sending hours, e.g. Europe/Brussels. |
| smsConsent | Actual JSON boolean. False blocks reminders. |
| sourceUpdatedAt | Authoritative source update timestamp with offset; later than the previous update. |
| action | upsert (default), cancel, or complete. Closed bookings need a new externalId to reopen. |

Send JSON to the node's test URL while listening, or its production URL only after activation. Check the response `outcome`: `preview_only` means nothing was stored. Invalid payloads stop the execution and return an error; monitor these failures. Replayed/older updates return `ignored_old_or_replayed_booking`. Upstream systems must preserve ordering timestamps; receipt time is not an adequate substitute.

Changing phone, appointment time or timezone rotates the reference and allows one new reminder for the new revision. Nonmaterial updates preserve confirmation. STOP remains effective even if later booking updates say consent is true. Do not remove a suppression without an explicit, recorded re-consent process.

## 4. Preview and controlled testing

The workflow can be inspected/imported with no database. Execution requires a configured database; there is no hidden in-memory fallback.

For a local or staging preview, run [demo-preview.sql](demo-preview.sql) against the installed schema. It temporarily inserts a fictional business/appointment, calls the dry-run functions and rolls everything back. It does not call VoxiSMS. Dates and the test clock are fixed so results remain reproducible.

Before live use, perform these checks in a dedicated staging copy:

1. Verify Header Auth rejects a request without its secret; send a valid booking and confirm the dry-run response writes nothing.
2. Seed a fictional future appointment directly in the staging DB and run **Run reminder check** with dry run true. Confirm the preview text, recipient, timezone and the false branch; confirm no outbox reservation or SMS.
3. When ready for a controlled handset test, use only an explicitly chosen test number. Enable the staging business row and booking/reply processing, create a future test booking, then enable reminder processing. These changes can cause a real SMS when a schedule/manual check runs.
4. Confirm one reminder, reply YES with its reference, and verify `appointments.status=confirmed`. With separate test bookings, verify CANCEL, RESCHEDULE, ambiguity and STOP. Replay one stored event; confirm it does not repeat a state change.
5. Verify the SMS node's error output reaches **Flag uncertain send**, successful output preserves item linking to the reservation, and multiple loop items use their own claim tokens. Simulate errors without sending repeated real SMS.
6. Ensure staff can see and act on the queue; verify failed inbound executions can be replayed into **Match reply and update tracking** using the original eventId after the cause is fixed. Do not resend directly from the outgoing SMS node.
7. Turn off the staging business and deactivate the staging workflow after testing. Remove test records using an owner-managed staging reset.

Activation registers the incoming SMS webhook and starts the schedule. Deactivation removes that webhook. An active workflow left in dry run receives events without storing them; do not leave a live customer line in that state. Provider webhook retries do not guarantee replay of a later PostgreSQL failure after n8n accepts the webhook. Monitor and recover failed executions.

## 5. Staff queue and recovery

Run this in a trusted database client, or adapt it into your chosen staff dashboard/notification integration:

```sql
SELECT q.id, q.reason, q.external_id, q.customer_name,
       coalesce(q.phone, e.from_number) AS phone,
       q.starts_at, q.timezone, q.appointment_status,
       e.content AS reply, q.created_at
FROM voxisms_appts.open_staff_queue q
LEFT JOIN voxisms_appts.inbound_events e
  ON e.business_id=q.business_id AND e.event_id=q.event_id
WHERE q.business_id='default'
ORDER BY q.created_at;
```

- **cancellation_requested/reschedule_requested:** handle the request in the booking system, then send its updated booking webhook. The template does not silently cancel or rebook anything. If declining a request, resolve the source/customer conversation and explicitly restore the appropriate tracking state with an audited operator action; a plain YES does not override the pending request.
- **ambiguous/unmatched/unrecognized replies:** inspect the sender and original message; contact the customer through your normal process. Extra prose or punctuation (for example `YES please` or `STOP!`) goes to staff. Only complete documented commands are automated.
- **appointment_still_unconfirmed:** follow up before the appointment; no repeat SMS is automatically sent. This can include consented or nonconsented appointments that never received a reminder.
- **send_outcome_unknown:** inspect the outbox and provider logs using `outbox.id` (the custom message ID). Do not delete the reservation or retry the SMS node: a timeout can still mean the original SMS was accepted. A known acceptance can be recorded with `record_send(outbox_id, claim_token, true, provider_message_id)` by a trusted operator. Otherwise document the resolution before considering a separate manual send.
- **conflicting_event_id_staff_review:** the original event remains authoritative. A later event reusing its ID cannot change a booking; inspect execution/provider logs for the conflicting payload.

After genuinely resolving a queue item, a trusted operator can mark it resolved by its business and numeric ID:

```sql
UPDATE voxisms_appts.staff_queue
SET status='resolved', resolved_at=now()
WHERE business_id='default' AND id=123; -- Replace 123 with the reviewed issue.
```

No messages are sent to staff or customers by these queries. Choose retention periods for booking/contact data, inbound text, outbox history and n8n failures. Retain enough deduplication history for your replay window. SQL plan sweeps recover interrupted attempts after 15 minutes; recovery depends on the schedule continuing to run. A STOP or cancellation after reservation may race with an API call already in flight.

## Database connection requirements

Use PostgreSQL 15 or later with TLS and certificate verification. Supabase is optional. If n8n reports a certificate-chain error, resolve trust with your database provider or use a compatible connection before activation. Do not use “Ignore SSL Issues” as the production setup. This requirement concerns the database connection, not importing the workflow JSON.

## References

- [VoxiSMS node documentation](https://github.com/aboven/n8n-nodes-voxisms)
- [VoxiSMS node installation and credentials](https://voxisms.com/integrations/n8n)
- [n8n Postgres node](https://docs.n8n.io/integrations/builtin/app-nodes/n8n-nodes-base.postgres/)