Django model design to analyse user activities on external websites
Is the logic clear in the models?
  


Somewhat. Your narrative didn't touch on the Nick table, which I think many people would name a User table. You didn't make it clear if user alice on the site example1.com is different from alice on example2.com - that's a namespace issue. Your table implies they would be distinct, that there is a compound primary key on (site, username). That would be good DB modeling, but weird from a UX perspective, if you put yourself in Alice's shoes. I would personally lean toward designing this with a unified username namespace across all sites, and similarly a unified thread_id namespace. That way you're always free to shard it out later (or partition it out) by site, but you're never forced to.

The Thread table appears to me to be broken. That is, no PK is defined, and I don't even see an obvious one. You have thread_type, but no thread_id. Maybe pub_date has enough entropy for uniqueness? I'm willing to believe that if we're looking at posts from a single user, but site wide? No, that is difficult to believe. I recommend adding some unique identifier. Remember that Primary Key dictates physical layout on disk, and the details of which rows are near which other rows will affect query performance.


  
  Regarding the first_seen and last_seen fields in Nick model: if I can look up the Activity model inexpensively in runtime, I would rather not to have them, since the two fields need to be computed each time I insert a new record. What are my better options here?
  


I'm not happy with the Activity model. Substantively, you haven't described a Primary Key. I propose compound PK (username, post_date), assuming the timestamps are high enough resolution, otherwise the more complex (username, post_date, thread_id). You can assign IDs how you like, in ranges or even as GUIDs, but the post_date will matter for query performance. Trivially, rename to singular "username" and singular "thread_id" (or "thread" if you really want). Probing Nick when computing str() seems silly, as you already have exactly the right username. I notice you chose to leave site out of this table.

Updating the last_seen and (hardly ever) the first_seen columns is not such a big burden. And suppose that last_seen is not part of the UX, not displayed on screen. Then you could choose not to update it in timely fashion. Or if it is displayed, you could let transient memcached track recent updates. However you do it, at some later time, like 5 minutes or hourly or daily, you could do a reporting run where you read max(post_date) from Activity and update Nick. Or keep it out of Nick and instead update some offline reporting table. I'm just saying you have lots of flexibility in how you handle this detail. Nothing wrong with denormalizing in the name of query performance. Just wait till you can benchmark two competing queries so you understand what you're trading off.


  
  There will be about 10 sites included, should I have another model just for sites?
  


Not based on what you've told us so far. A GROUP BY (or DISTINCT) would soon extract the current set of sites from one of your existing tables. You've not yet invented any requirements that require mutable site attributes be stored in the database.


  
  The url field in Activity model exists only for a forum type, meaning quite a few millions of records (Blogs and News) will not have a value there, is this a good practice?
  


Depends on your query details, but sure, looks fine the way it is. Many urls will be NULL or will be the empty string, depending on whether you choose to make it a nullable column. Either way, it's not like you're losing much.

For rows without an URL, I bet you could avoid writing the row in the first place. I bet a carefully structured thread_id could encode all of (is_original, low_timing_resolution_post_date, unique_thread_number) within 64 or even 32 bits.