Goals

Ref integ of email addresses
More customizable scoping

Use cases

Trigger a notification

Notify anytime any Document changes.
Notify anytime a Document in a certain locale changes.
Notify when a particular Document changes.
Delete an object and make its watches go away.
New articles or changes to Documents that are untranslated into de and that are localizable and that are approved.
Notify when an approval happens in German.
Major edits to Documents in English
Major edits to Documents
Any activity tagged with "blue"
Anything Fred does
Tell what the user is subscribed to
Notify any new thread in a given discussion forum
Notify any post (new thread or reply) in a given discussion forum
Notify any post in a discussion thread
Notify any new thread in any KB forum in a given locale
Notify any post in any KB forum in a given locale
Notify any new thread in a given KB forum
Notify any post in a given KB forum
Notify any post in a given KB forum thread
Notify on new question (confirmed)
Notify on new answer to a given question
Notify on answer marked as solution to a given question
Notify on new flagged item for review

Deliver notification

Logged in user

(non-exclusive)

To email
To private message
(Future, other options?)

Anonymous user

To email

If a user registers, we should look for any anonymous watches with that email address and add them to the user, when the user is confirmed.

Evolution of table structure

Iteration 1: Event type determines the format of the path column(s).

path         path2*     type**                      email/whatever
15                      DocumentEvent               whatever@whatever.com
15.en-US                DocumentEvent
15.de.2837              DocumentEvent
15.en-US.30             DocumentSignificanceEvent
                        TagEvent

* optional. Choose the split per event type based on where orthogonality happens.
** An event type, which can span, frex, use cases 1-3 below. Determines the use of the spec fields.

Iteration 2: Get ref-integ on content type.

content_type    object_id    path      event_type                 email/?  (usecase)
15                           en-US     DocumentLocaleEvent                 2
15                                     DefaultEvent                        1
15              2837                   DefaultEvent                        3
15                           en-US.30  DocumentLocaleSignifEvent           7
15                           de.L.A    UntranslatedEvent                   5

Don't make path col unicode: save space.

Iteration 3: More normalization, yielding better queryability: orthogonality of path elements (not just hierarchy) is possible without ridiculously inefficient substring queries. I don't think the joins would be too bad, but benches would be nice.

watches id content_type object_id event_type user/email/whatever 1 15 2345 UntranslatedEvent

watch_elements: watch_id name value (int for size/speed/ad hoc joins) 1 lang CRC32(de) # Postgres doesn't have a CRC32 function, so ad hoc joins on string keys would be a pain. 1 localizable 1 1 approved 1

on UntranslatedEvent for object_id=2345 and content_type=15:
    select distinct email from watches
    left join watch_elements lang on watches.id=lang.watch_id and name='LANG' and (value IS NULL or value='de')
    left join watch_elements localizable on watches.id=localizable.watch_id and name='LOCA' and (value IS NULL or value='1')
    left join watch_elements approved on watches.id=approved.watch_id and name='APPR' and (value IS NULL or value='1')
    where content_type=15 and (object_id=2345 or object_id IS NULL) and event_type=UntranslatedEvent 
    # It's an (n+1)-table join where n is the max length of the path (excluding content type and object ID). n of the joins, however, would be selecting among the same n rows: not a big deal.

Iteration 4: Move content_type and object_id into watch_elements? See what the benches say.

watches:
id event_type        user/email/whatever
1  UntranslatedEvent

watch_elements:
watch_id     name         value (int for size/speed/ad hoc joins)
1            lang         CRC32(de)  # Postgres doesn't support CRC32 in the DB, so ad hoc joins on string keys would be a drag.
1            localizable  1
1            approved     1
1            content_type 15
1            object_id    2345

This will bring it up to an (n+1)-table join, n being the max length of a certain event type's path *including* content type and object ID.

Don't hamper work now because of Postgres. Assume we're on MySQL until Oracle shows up with a bill.

Features

Maybe blocking an addy someday till a mail is confirmed (to prevent spamming)?
Daily digests, supported by 2 tables:

digests:
user     event_type

digest_elements (values plugged into the template specified by event_type):
name      value
id        8634
hat_color red

Support/Notifications

Contents