Support/Notifications

Goals

• Ref integ of email addresses
• More customizable scoping

Use cases

1. Notify anytime any Document changes.
2. Notify anytime a Document in a certain locale changes.
3. Notify when a particular Document changes.
4. Delete an object and make its watches go away.
5. New articles or changes to Documents that are untranslated into de and that are localizable and that are approved.
6. Notify when an approval happens in German.
7. Major edits to Documents in English
8. Major edits to Documents
9. Any activity tagged with "blue"
10. Anything Fred does
11. Tell what the user is subscribed to

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. Maintains referential integrity of object and content type.

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.

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