Approach for new SMS messaging project

🤝 Discussion
1

I'm trying to brainstorm how to approach a new app build.
I'd have a database of users (approx. 3,000) with name and phone number and need a way to send SMS messages to them all, as well as target them by group (using tags in the database) and the toughest part, sometimes by a geofence.

I know i could set up something with Retool and Twilio but am having a hard time figuring how to integrate a geofence feature. Maybe using something like Radar or PlotProject?

The user db are all opted in for this, so it's not random targeting. I'm just not sure how to bring this all together, and if it's even possible?

2

Geofence how? By user defined polygon?

Using PostGIS (similar in SQL server but I am very rusty there) you can do a query like this:

SELECT 
  c.*
FROM 
  customer AS c
JOIN
  geofence AS z
ON 
  ST_Within(ST_SetSRID(ST_MakePoint(c.lng, c.lat), 4326), z.geom)

If you are supplying the geofence polygon, make sure it is in WKT format and do something like this:

SELECT 
  c.*
FROM 
  customer AS c
WHERE 
  ST_Within(ST_SetSRID(ST_MakePoint(c.lng, c.lat), 4326), ST_GeomFromText('YourPolygonWKT', 4326));

Note I had ChatGPT do these and I tweaked them as it was was faster than loading up and searching my existing code bases!

1 Like
3

By trying to leverage 3rd party service to make it easy as possible, maybe by using Radar?https://radar.com/

Same way i'd use something like Twilio to send text messages through Retool.

It's well out of the realm of anything I've done so just trying to find ideas and different approaches.

5

I went a similar route and ended up handling number provisioning and delivery status stuff through the sip trunk provider’s API instead of building it all out myself. Made integration a lot simpler, especially since I could reuse existing workflows from another project. If you're already using a sip trunk provider, check what webhooks or APIs they expose—it saved me a ton of time.

1 Like