Towing Alerts

A No Tow subscription service where city residents can register a license plate and then receive alerts (email/text/voice) if a vehicle with that plate is towed.

See maintenance tasks.

Summary

Residents can use the page at https://www.cityofboston.gov/towing/alerts.

The resident is able to register a license plate and receive one or any of:

• an email alert (managed by SQL Server)

• a text alert (SMS via email)

• a voice alert (managed by Twilio)

Every 15 minutes the city gets an update from the police on towed vehicles. For each new vehicle towed the license plate is checked against plates registered by residents and alerts are sent when matches are found.

Statistics

Statistics as at 10/27/2021	Value
Email subscribers List	765,168
Voice Subscribers	3.292
SMS Subscribers	1 (Satyen)

Code

This is an ASP application hosted on ZPCOBWEB01.web.cob (a DMZ IIS Server).

The code is is found in the following folder:

D:\wwwcob\towing\alerts

: default.asp

This page generates forms to collect information for email, text and voice alert subscriptions. The forms all submit to subscribe.asp which does the actual subscription for alerts that are sent when a registered vehicle is towed.

: subscribe.asp

Email:

Subscription - First the script checks if the email & plate is already subscribed. If it is not, then the script checks how many license plates are already registered against the email address, if its more than 10 it wont register this new one (unless the email is on a whitelist - see box out below). If the plate is not registered to this address, and the address has less than 10 plates registered to it (or is whitelisted) then the email/plate combo is registered in the table towed_emails in the database Towing on vSQL01 (aka ZPDMZSQL01).

Unsubscription - First the script checks if the email & plate is already subscribed. If it is, then the entry is removed from towed_emails and is added to towed_emails_optout in the database Towing on vSQL01 (aka ZPDMZSQL01).

Fleet Owners Whitelist.

Line 125 of subscribe.asp contains a white list of subscribers who can register more than 10 vehicles.

Text:

Text subscription is completed by adding a record to the towing_emails table, but using an email address that routes through the subscribers telephone provider. In this way sms messages can be sent without needing to use an sms gateway.

There is a "No Provider' option, which should ideally be removed.

Provider email to text gateways can be found here: https://avtech.com/articles/138/list-of-email-to-sms-addresses/

Note: We do have the option to send SMS via Twilio using a stored procedure in the twiSQL database, however this incurs an additional cost. As at 2021-11-17, this process does work, and is used by the SMS block in the sp_process_towing_messages stored procedure. To be certain that SMS messages are delivered, it could be that users who select 'No Provider' are added to the towing_sms table.

Voice:

Subscription - First the script checks if the email & plate is already subscribed. If it is not, then the script checks how many license plates are already registered against the email address, if its more than 10 it wont register this new one (there is no whitelist for voice registrations). If the plate is not registered to this address, and the address has less than 10 plates registered to it then the email/plate combo is registered in the table towed_phonenumbers in the database Towing on vSQL01 (aka ZPDMZSQL01).

Unsubscription - First the script checks if the email & plate is already subscribed. If it is, then the entry is removed from towed_phonenumbers and is added to towed_phonenumbers_optout in the database Towing on vSQL01 (aka ZPDMZSQL01).

: remindme.asp

This page allows the user to provide an email address and the system will email a list of plates registered to that email address.

The email handler used is installed as part of the IIS service at ZPCOBWEB01.web.cob (10.241.250.22), and the SMTP server the mail is routed through is at smtp.web.cob (10.241.250.209)

Database

The utility code which manages connections to the Database Server and posts queries etc to the server is contained within:

D:\wwwcob\includes\stdf.inc

Note: the same include file has a mail handler which connects to (public) mail.cityofboston.gov (140.241.251.209) - but this seems to be deprecated.

The database server used by this sub-service is vSQL01 (aka ZPDMZSQL01). The server is hosted in the DMZ in the web.cob (aka lincdom) domain. Developers need a separate and specific account to be set up on the web.cob domain to view/edit databases and tables on this server.

Towing

The primary database used by this sub-service is Towing. This database holds information on registered plates and vehicles towed.

The tables used by this sub-service are:

TableName	Key Fields	Description
towed_emails towed_phonenumbers towed_sms	subscriber_email, or subscriber_phone subscriber_plate subscriber_state	Contains list of mail, sms and voice subscribers and their plates to monitor.
Towline_bpd	License Plate Tow Date-Time Infringement Location	Contains a list of all vehicles towed by services authorized by the City.
towed_emails_log towed_phonenumbers_log towed_sms_log		Contains log of alerts raised
towed_import_log		Contains a summary of activity (used in daily report)
towed_phone_alerts_queue		Contains a queue for voice messages which are then processed by an external service.
Towline_complete		Archive for the complete list of towed vehicles since time began.

The email/voice is handled by the SMTP service on the MSSQL Server.

twiSQL

Manages SMS message dispatch via the Twilio SMS gateway.

This database is used by the sp_process_towing_messages. There are no tables inside the database, and 3 stored procedures. There is also an Assembly (twilioSQL) loaded., plus (possibly auto-created) a Service Broker.

The stored procedure SendBrokerMessage is called in order to route an SMS message via Twilio.

Connected Services

Police Updates

The police update information on newly towed vehicles every 15 minutes. The police have a job/process that pushes the data to vSQL01.

The police data is inserted directly into the Towline_bpdtable by the Police IT department (contacts below). The actual SSIS-ODBC process involves truncating the towline_bpd table and then bulk inserting a complete set of new records.

Note: The MSSQL bulk insert does not fire triggers by default.

Permissions

The police account (youvebeentowed (?)) needs permission to truncate the Towline_bpd table and to (bulk) insert new records. It does not need permissions to stored procedures, triggers or the twiSQL database.

Contacts

Staff	Position	Email
Rich Petruccelli	Data Services Manager	[email protected]
Thomas Hutchings (Hutch)		t[email protected]
Frank Alexopoulos	Apps development	[email protected]

Outbound Communication (email, sms and voice)

Reminder emails

Reminder emails created and sent by the remindme.asp page (one-time & on-demand by the resident) are routed through an SMTP server the mail at smtp.web.cob.

Alert Initiation

As the police update the towline_bpd table (i.e. as vehicles are towed) alerts are originated and handled by the Towing database and TwiSQL database on the MSSQL Server at vSQL01.

Alerting is fully decoupled from the cityofboston.gov hosted asp pages, and initiated by the MSSQL server vSQL01.

There is a stored procedure, sp_process_towing_messages , in the Towing database which is executed every 5 minutes by the SQL Job; TowingSendMail.

sp_process_towing_messages reads the towline_bpd table and discovers new tow events. It then determines which tows have subscriptions. Finally, it dispatches the outbound communications to subscribers.

• The sp evaluates the inserted rows, looks to see if the license plate is registered (towed_emails, towed_phonenumbers and towed_sms), and if so ends out an alert.

• The sp uses the system stored procedure sp_send_dbmail, to send emails directly to the subscriber from the MS SQL server. Some SMS subscribers are also in the towed_emails table because the subscriber has registered for SMS/text by providing a phone number and provider. (see box below)

• The sp interfaces directly with Twilio (for unspecified-provider SMS's)

• The sp drops records into a queue for voice processing (which runs on a scheduled task).

• The sp records which recipients have been communicated with, and which have been processed

• The sp maintains statistics on what has been sent out.

Alert emails:

Emails are sent directly from Towing.dbo.sp_process_towing_messages . The sp constructs and send them out using the built-in MSSQL email service (from msdb.dbo.sp_send_dbmail).

The email process handles both emails and (the majority of) sms messages. SMS strategy is to send an email to a mobile provider specific email address which then routes the email as an SMS to the recipient/subscriber.

For example the email record in towed_emails 781###@att.net was registered as phone number 781### and AT&T as the provider. Using that email format prompts an email-to-sms service at att.net.

SMS/Text messages:

The majority of text messages are sent via email-to-sms (see box-out above).

Note: Some subscriptions are saved in the table towed_smsand initiated by process_towing_messages .

Voice calls:

• The process is managed within sp_process_towing_messages which is executed every 5 minutes by the SQLAgent Job TowingSendMail.

• Voice calls identified in the stored procedure are queued into the table Towing_twilio_Queue in the Towing database.

• Every 10 minutes, a scheduled task Towing_Twilio_Queue runs on zpcobweb10 (10.241.250.22). The task runs a script c:\installs\scripts\curl_towing_queue_process.bat .

• The script simply calls twilio-alert-place.aspx. curl.exe -connect-timeout 300 http://zpcobweb01.web.cob/towing/alerts/twilio/twilio-alert-place.aspx

• Twilio-alert-place.aspx app/script calls a stored procedure Towing.dbo.spTowingTwilioQueueRead which fetches queued voice messages from the towed_phone_alerts_queued table. For each queued message the endpoint payload and creates a Twilio object and initiates the outbound call (via the Twilio REST API). The app/script also removes the record from the queue (towed_phone_alerts_queued) using the stored procedure towing.dbo.spTowingTwilioQueueDelete.

The message which is sent to theTwilio REST service (to make the voice call to the subscriber) includes a call to an endpoint twiml-alert-content.aspx This app/script receives the towed vehicle details from the querystring/payload and creates and returns the correct message for the Twilio voice service to read out.

The message also defines an interaction where the call recipient can hear the message again, obtain tow company information or unsubscribe (using spTowingTwilioUnsubscribe) from future voice alerts.

There is an additional endpoint at Twilio-incoming.aspx . This is provided to handle incoming calls to the No-Tow telephone number. This app/script determines the caller phonenumber and looks up recent towing records to see if a vehicle with a plate registered to this number has been towed. If so the details of the tow event are read back to the caller

Permissions

The job which initiates the sp which processes new alerts does need extensive permissions.

Stored Procedures (execute permission):

• msdb.dbo.sp_send_dbmail: to actually send out emails using MSSQL Mail services.

• twiSQl.dbo.SendBrokerMessage: This interacts with a service on the twiSQL DB on the same server (????) There is a twilioSQL assembly loaded on the twiSQL database, but not on the Towing DB.

Functions (Call/Execute permission):

• Towing.dbo.functionVerboseReasonCode

• Towing.dbo.functionUnabbreviateTowCompany

• Towing.dbo.functionUnabbreviateYear

• Towing.dbo.functionUnabbreviateCarMake

• Towing.dbo.functionUnabbreviateCarColor

• Towing.dbo.URLEncode

Tables (Insert and read permission):

• Towing.dbo.Towline_bpd (also requires delete permission)

• Towing.dbo.towed_import_log

• Towing.dbo.towed_emails

• Towing.dbo.towed_emails_optout

• Towing.dbo.towed_alerts_log

• Towing.dbo.towed_phonenumber

• Towing.dbo.towed_phone_alerts_queued

• Towing.dbo.towed_phone_alerts_log

• Towing.dbo.towed_sms

• Towing.dbo.towed_sms_alerts_log

Twilio - Voice Alerts

Voice alerts are somehow processed by Twilio using a complicated call-back process to cityofboston.gov.

This needs further investigation and access to the twilio UI.