Spam System Design

I. Essentials

• Data model
• Requirements document

II. Introduction

The spam package uses the acs-mail package as the store for message content, so all spam content also ends up in the acs_contents; the acs-mail package also handles the generation of multipart/alternative e-mail for us, and the actual transport of outgoing message via ns_sendmail.

The spam package is intended to be used as a singleton service, so other applications can link to a single instance of the spam package.

III. Historical Considerations

IV. Competitive Analysis

V. Design Tradeoffs

Picking groups of recipients

Spamming groups of users chosen with different sets of criteria is a feature that can be incorporated into a variety of different applications; for example:

• bboard: a forum admin sends mail to all users who have posted more than N messages in D days
• subsite: send mail to all users who are members of group G
• file-storage: send mail to all users who have downloaded a file F

This is part of a larger data-mining or personalization problem, and is out of the scope of the current spam service. So, for now, we require that each application provide a SQL query returning a list of party_id's as spam recipients as a parameter to the pages that create a new spam message. We prevent SQL tampering by passing it as a client property (using ad_{get|set}_client_property) instead of passing it as a URL variable.

Use of acs-mail

We use acs-mail for basic message storage and transport services, since there is already an e-mail queue processing procedure, and storing messages in the acs_mail table means that outgoing spams are content items in the acs_contents table.

Since acs-mail is still under development and its specifications are subject to change, we will suggest some enhancements for acs-mail as follows:

• The mail-queue processor procedure (acs_mail_process_queue) should use the user's prefer_text_only_p setting in user_preferences, and, in the case of multipart/alternative mail, just send the user the plain-text portion in case their mail reader is broken.
• We'd like to supply the name of a PL/SQL function that filters the mail body content before it is sent to the user, given a party_id as a parameter. This would allow spam to implement a mail merge function, without creating a trivially-different acs_mail_body for each recipient.

VI. API

Tcl

Also, we will include a "sweeper" procedure that periodically polls for spam that has been approved by an administrator and whose send-time has passed; these messages will be pushed into the acs-mail service's outgoing mail queue (acs_mail_queue_outgoing) for transport.

PL/SQL

The spam system will extend the ACS Mail service, and will include a PL/SQL package with a new function to create a new spam message object. There will also be an edit method for editing a spam message, or updating its status fields.

VI. Data Model Discussion

Spam only needs to keep track of four things beyond what acs-mail does:

• a SQL query for selecting users as recipients
• a flag to indicate whether an outgoing spam has been approved by an administrator
• the date for the mail to be sent out
• a flag to indicate whether an outgoing spam has been moved to the outgoing queue, which should occur only after spam is approved and the above send date has passed. (Is there a way to make this a check constraint, e.g.,

  constraint spam_messages_sent_p_ck
  check (sent_p = 'f' or (send_date >= sysdate and approved_p = 't')
  

  ? The question is, when adding such a constraint to the datamodel, does Oracle treat sysdate as the time at which the constraint is created, or at the time when the data is inserted?)

VIII. User Interface

End users

• spam-add: creates a new row in spam_messages, and displays the main spam-entry form; includes textarea for text, content-type radio buttons (plain text/HTML), date-to-send widget, the count of users to be spammed, and a confirmation button. Form posts to spam-confirm.
• spam-confirm: displays text as it will go out and gives "are you sure" button. Posts to spam-send
• spam-send: updates all columns in spam_messages table. Displays confirmation method "you may edit with spam-edit or view status with ..."
• spam-edit: displays same form as spam-add, but posts to spam-update
• spam-update: does update instead of insert
• spam-status: shows the status of a single spam message; also gives admins a widget to approve pending spam
• We will also modify the "group/one" page in acs-subsite to link to spam-add.

  Subsite administrators

• spam-queue: display the entire spam_messages table with links to spam-status
• spam-remove: delete/disable a spam message

  ACS Administrators

• ACS (site-wide) admins will have a page for entering arbitrary SQL queries for picking groups of users.