Multisite/network of sites support

Ok. I am looking at schema and you have actividy ID (aid) as primary key. Now in a multisite network a.domain.com and b.domain.com may only share users and sessions table but not necessarily the sequences table. So if they share the activity table, you will get collision of activity ids. This can be easily avoided by adding a new column "siteid" or "sitename" and this column along with aid becomes the primary key. This way you can have records like "siteA,23456" and "siteB, 23456" in the activity table.
The above scenario is not hypothetical. It very much exists in d.o and groups.d.o which do not have same sequences table. (and some of my network of sites). So if we want to aggregate activities of a user at one place for both d.o and groups.d.o currently not possible.
Or Instead of one huge drupal commons installation, for better scalability I may partition with groups.domain.com (where only groups are enabled) , blogs.domain.com (where only blogs are enabled) with seperate drupal commons installations but share same user, profile tables.

Although this module was originally designed for aggregating activity on a single site, its utlity several magitude increases if it can support multiple sites aggregation. End user won't need to go to each site to see what is happening on all those network sites.

If you know some tricks how to achive this, please let us know.

I am not using domain access module. Rather trying to keep it as generic as possible to avoid future conflicts and dependencies. These are in actual production and not speculative need.

Main Site
  www.domain.com : Main database (M1.main_)

A.domain.com
$db_prefix = array(
      'default'   => 'A_',
      'users'     => 'M1.main_',
      'sessions'  => 'M1.main_',
      'profile_fields'  => 'M1.main_',
      'profile_values'  => 'M1.main_',
      activity_log => 'M1.main_'   (planned)
    );

B.domain
$db_prefix = array(
      'default'   => 'B_',
      'users'     => 'M1.main_',
      'sessions'  => 'M1.main_',
      'profile_fields'  => 'M1.main_',
      'profile_values'  => 'M1.main_',
      activity_log => 'M1.main_'   (planned)
    );

One beauty of above solution is you get single sign on out of box as long as all are subdomains.
As you notice A.domain/node/1234 will be different than B.domain/node/1234 So if A.domain/node/1234 creates Activity record in M1, its sequence will be from sequence table in A.domain say 7890. Now the sequence table in B.domain is completely independent so it can still create record in M1 as 7890 (but it will fail).

A simple future proof generic solution would be add the extra column for site id when you create Activity record.

Another solution would keep activity log only on M1 (not shared) but A and B call service on M1 to create a record. Since only the sequence from M1 used, there is no chance of collision.

Does this provide enough info?

I think you summed it nicely and I can understand your reasons.

Some activity is shared across subdomains, and some is subdomain-specific.
I think this use case will not be needed as this goes against the reason activity log exists (aggregation of activity in one place).
All activity is shared across all subdomains
This would the most required case.

Problem is that stored messages are dynamic, not static

I can see why you made this important architecture choice but it may limit the success of this module. I see this module has far bigger possibilities.

Would you just consider allowing 1) static messages as additional type? and 2) Creating feed of activities (this should be free if you are using views) These could be just html link from other network sites. Then we can write another separate bridge module which can take say certain feed from another subdomain and add to the activity log. So user gets consolidated activity view from multiple sites. or in other words allow activity type messages which are shown as it is and not loaded with actual content. For example this bridge module can import activity feed from facebook and show user everything in one place.

(I am not suggesting replace dynamic messages as you have currently. This is another type of message added to activity log when displayed shows as it is. )

You need unique IDs for everything involved

Exactly. That is why I originally proposed a "siteid" column which combined with activity id or node id will be always unique. So primary key will be composite (siteid, aId) instead of only (aid)

if you post a blog post in an Organic Group the module records the node ID, but on a different subsite that ID would represent a different node.

And combining node id with site id will open the path to query the right subsite.

I completely understand if you think this usecase is not frequent. And I don't expect you to provide all the coding required. But atleast a structural change of siteid support, opens up possibilities for others to build on that. For normal case the siteid will always have just one value of site where it runs.

You can have services module on those multiple subdomains. So here is what is possible
Each subdomain has own database and services module. And the subdomain which aggregates activity will act as services client to pull content.

I know it is not trivial and automatic. But atleast identifying and recording the source site id is starting point. Other processing can be build on the top.