For the past few days instead of finishing the thing that I should be finishing, I have been building the Rat Mail system. Just like everywhere else in the world, the Pareto Principle is in effect here, and a lot of our problems are caused by a small number of users. I’ve stitched together a sequence of scripts that will send me an email every Monday morning ratting on the troublesome users.

A lot of the trouble is remarkably consistent. Registration email addresses that satisfy a fairly simple regular expression are likely to be from users creating sock-puppet accounts to cause trouble. An account that is created at a particular school and then is primarily used at a specific non-school location that has been cut off from creating new accounts is likely to be a problem. There are a lot of things going on that are fairly predictable. Instead of having me comb through the data on the site, I’ve put machines in charge of doing it.

Here are the pieces.

First I wrote an R script that I called rat-facts.R that queries the database, figures out which users need to be ratted on, and then writes the body of the email into a text file called rats.txt. As I find more ways to identify trouble-makers, their behavior will get encoded in this script, and I will learn about what they have been doing.

Next, I wrote an AppleScript that I called send-mail.scpt that tells Mail.app (which is logged in to Gmail as me) to send an email whose body is drawn from rats.txt

set theFile to "/Users/amy/ratfacts/rats.txt"
open for access theFile
set fileContents to (read theFile)
close access theFile

set fileContents to fileContents as string


set recipientName to "Amy"
set recipientAddress to "email.address@example.com"
set theSubject to "Rat facts!"
set theContent to fileContents

tell application "Mail"
	
	##Create the message
	set theMessage to make new outgoing message with properties {subject:theSubject, content:theContent, visible:true}
	
	##Set a recipient
	tell theMessage
		make new to recipient with properties {name:recipientName, address:recipientAddress}
		
		##Send the Message
		send
		
	end tell
end tell

This isn’t how I wanted to send the email. I really, really, really wanted to figure out how to get postfix to authenticate to Gmail’s SMTP server as me and do all of this through a shell script instead of needing to have an AppleScript talk to Mail.app. But I was not willing to put in the amount of time that it would have taken to make that work. Compromises.

Finally, I told my office computer (recall, access to this database is restricted to users that have been GRANTed acces and that are connecting from the office IP address) to run rat-facts.R to create the text file then then run send-mail.scpt to send the email. This was also harder than it needed to be because for reasons that entirely escape me, the crons are not running on my office computer. They fail silently, and I have absolutely no idea why, so I am stuck with launchd. Thus, I had to set up two plist files in ~/Library/LaunchAgents/: one to run rat-facts.R and one to run the AppleScript. Monday is Weekday 1, so this will have the AppleScript run every Monday morning at quarter after 8 and send the email.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>Label</key>
  <string>com.amyszczepanski.sendmail</string>

  <key>ProgramArguments</key>
  <array>
    <string>/usr/bin/osascript</string>
    <string>/Users/amy/ratfacts/community/send-mail.scpt</string>
  </array>

  <key>Nice</key>
  <integer>1</integer>

    <key>StartCalendarInterval</key>
    <dict>
        <key>Hour</key>
        <integer>8</integer>
        <key>Minute</key>
        <integer>15</integer>
        <key>Weekday</key>
        <integer>1</integer>
    </dict>
</dict>
</plist>

To make sure that everything would actually run, I had to tell launchd about these plists by issuing the command launchctl load com.amyszczepanski.sendmail.plist from within ~/Library/LaunchAgents/.

Ideally when I get to my desk on Monday morning, my inbox will have a message waiting for me with a list all of the things that I need to delete and turn off. If it works, next week it will just have a list of the links that I’ll need to click in order to clean everything up.