0

I have a web application build in Django + Python that interact with web services (written in JAVA).

Now all the database management part is done by web-services i.e. all CRUD operations to actual database is done by web-services.

Now i have to track all User Activities done on my website in some log table.

Like If User posted a new article, then a new row is created into Articles table by web-services and side by side, i need to add a new row into log table , something like "User : Raman has posted a new article (with ID, title etc)"

I have to do this for all Objects in my database like "Article", "Media", "Comments" etc

Note : I am using PostgreSQL

So what is the best way to achieve this..?? (Should I do it in PostgreSQL OR JAVA ..??..And How..??)

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
Ramandeep Singh
  • 5,063
  • 3
  • 28
  • 34

5 Answers5

1

So, you have UI <-> Web Services <-> DB

Since the web services talk to the DB, and the web services contain the business logic (i.e. I guess you validate stuff there, create your queries and execute them), then the best place to 'log' activities is in the services themselves.

IMO, logging PostgreSQL transactions is a different thing. It's not the same as logging 'user activities' anymore.

EDIT: This still means you create DB schema for 'logs' and write them to DB.

Second EDIT: Catching log worthy events in the UI and then logging them from there might not be the best idea either. You will have to rewrite logging if you ever decide to replace the UI, or for example, write an alternate UI for, say mobile devices, or something else.

ArjunShankar
  • 23,020
  • 5
  • 61
  • 83
  • I was wondering we cud write `Triggers` in `PostgreSQL` that would fill the Log table , when a new row is inserted into Article, Media, Comments table etc..is it a good idea ?? – Ramandeep Singh Mar 30 '12 at 15:07
  • Depends. What if in the future, you have a situation where a user can upload an article which contains media (possible, right?) as well and this is handled in a single web service call? – ArjunShankar Mar 30 '12 at 15:13
  • True..!!..i'll see into it..!!..actually services are all complete..!!..i thot may be i dont have to put an additional code..neways thanx..!! – Ramandeep Singh Mar 30 '12 at 15:40
1

For an audit table within the DB itself, have a look at the PL/pgSQL Trigger Audit Example

This logs every INSERT, UPDATE, DELETE into another table.

Silas Parker
  • 8,017
  • 1
  • 28
  • 43
0

In your log table you can have various columns, including:

  • user_id (the user that did the action)
  • activity_type (the type of activity, such as view or commented_on)
  • object_id (the actual object that it concerns, such as the Article or Media)
  • object_type (the type of object; this can be used later, in combination with object_id to lookup the object in the database)

This way, you can keep track of all actions the users do. You'd need to update this table whenever something happens that you wish to track.

Simeon Visser
  • 118,920
  • 18
  • 185
  • 180
  • that what I was asking.. best possible solution to update this table..??...should i do it in the `PostGreSQL` OR `JAVA` ..??..and How..?? – Ramandeep Singh Mar 30 '12 at 12:08
  • 1
    Such table is quite good but it may grow very fast for even small website have 50K members. If only this table grows 10x faster than whol database how we maintain it porperly? – brsbilgic Aug 07 '14 at 15:11
0

Whenever we had to do this, we overrode signals for every model and possible action.

https://docs.djangoproject.com/en/dev/topics/signals/

You can have the signal do whatever you want, from injecting some HTML into the page, to making an entry in the database. They're an excellent tool to learn to use.

kcunning
  • 297
  • 1
  • 4
0

I used django-audit-log and I am very satisfied.

Django-audit-log can track multiple models each in it's own additional table. All of these tables are pretty unified, so it should be fairly straightforward to create a SQL view that shows data for all models.

Here is what I've done to track a single model ("Pauza"):

class Pauza(models.Model):
    started      = models.TimeField(null=True, blank=False)
    ended        = models.TimeField(null=True, blank=True)
    #... more fields ...

    audit_log = AuditLog()

If you want changes to show in Django Admin, you can create an unmanaged model (but this is by no means required):

class PauzaAction(models.Model):

    started      = models.TimeField(null=True, blank=True)
    ended        = models.TimeField(null=True, blank=True)
    #... more fields ...

    # fields added by Audit Trail:
    action_id    = models.PositiveIntegerField(primary_key=True, default=1, blank=True)
    action_user  = models.ForeignKey(User, null=True, blank=True)
    action_date  = models.DateTimeField(null=True, blank=True)
    action_type  = models.CharField(max_length=31, choices=(('I', 'create'), ('U', 'update'), ('D', 'delete'),), null=True, blank=True)
    pauza        = models.ForeignKey(Pauza, db_column='id', on_delete=models.DO_NOTHING, default=0, null=True, blank=True)

    class Meta:
        db_table = 'testapp_pauzaauditlogentry'
        managed = False
        app_label = 'testapp'

Table testapp_pauzaauditlogentry is automatically created by django-audit-log, this merely creates a model for displaying data from it. It may be a good idea to throw in some rude tamper protection:

class PauzaAction(models.Model):

    # ... all like above, plus:

    def save(self, *args, **kwargs):
        raise Exception('Permission Denied')
    def delete(self, *args, **kwargs):
        raise Exception('Permission Denied')

As I said, I imagine you could create a SQL view with the four action_ fields and an additional 'action_model' field that could contain varchar references to model itself (maybe just the original table name).

frnhr
  • 12,354
  • 9
  • 63
  • 90