Add "Object" and "Context/Container" columns

[dfidler]

Problem Statement

This is more of a question (for now) than a feature request as I know that what i need can already be "massaged" into activitylog, but I'm concerned about the overall performance of queries

Background

Logs are ultimately "sentences" and in a multi-tenant app, you always have the following componets of a sentence:

1. (A)ctor = The object making the change (aka - causer)
2. (C)ontext = a Tenant, or Container, for the activity (and, possibly, the actor - possibly, the log_name or maybe properties)
3. (S)ubject = The object being operated on (subject_id, subject_type)
4. (V)erb = the.. um... verb (event)
5. (O)bject = The object that the subject applies to (properties)
6. (D)omain = The classificiation that generated the log (event - l ike feature, authentication, etc)
7. Batch = Batch operations

In my app, a Tenant must be able to review any log item in which they are the Context, Subject or Object.

Now, all of this is possible with the current activitylog implementation but using "properties" for the object and by de-normalizing the log_name (to include my "context" inside of it).

For instance, I can use:

• log_name = "Tenant-{$this->id}" to encode the "Tentant".
• properties = $Object

My concern is searching performance as a json column is arguable the slowest datatype there is for querying, across millions of records.

So my idea is to extend activitylog to add the following columns:

1. context_type - class type (performedIn, in())
2. context_id - bigint (performedIn, in())
3. object_type - class type (performedOn(), on())
4. object_id - bigint (performedOn(), on())
   And then I could use the log_name for the "domain" (aka - feature of the app: security, permissions, etc, etc, etc, etc)

This would make querying/filtering the audit log fast with the ORM equivalent query being:

select * from activitylog where $tenant->id in (context_id, subject_id, object_id) and 'App\Models\Tenant' in (context_type, subject_type, object_type).  Obviously all of the morphable id and type columns would be indexed.

I guess my question is, am I over-architecting this or would others find this valuable too if I were to implement it? And is there a better way to do this rather than extending the code (I have looked and I don't see any, but activitylog will be very easy to extend to manage this).

Example Logs:

• A:UserX grants S:RoleX to O:UserY in C:TenantX
• A:UserX updates S:TenantX
• A:UserX deletes S:TenantX
• A:UserX V:ProprietryAction S:ProprietaryObjectX on O:ProprietaryObjectX in c:TenantX with properties ([])

[Gummibeer]

To all your questions one after another 😅

JSON columns slow:
Possibly true on MySQL, not a general true statement as PostgreSQL allows to index specific JSON keys.

Needed in the package:
No, what you describe is a pretty unique feature/setup. Like you already mentioned, it's super easy to add these things.

Over engineering:
I can't tell you. If that's what you need it's what you need. 😉
And yes, sometimes data redundancy is easier/better than normalizing your tables to death but having slow queries.

[dfidler]

re: JSON indexed keys, you're still looking at query times that are 2-3x that of an hstore and 10x that of a bigint index.

re: contribution - No worries. I'm very likely going to do this anyway (unless I can think of a better way) so thought I'd share if people thought it would be useful.