Laravel Spatie Activity Log for PostGRES

migration.sql

-- use case: find activities of a resource and/or within its related resources

-- causes sequential scans and is a tad slower because of the explicit json casting:

SELECT *
FROM activity_log
WHERE (log_name = 'resources' AND subject_id = 351030)
      OR (log_name = 'related_resources' AND (properties :: JSON -> 'attributes' ->> 'resource_id') :: INT = 351030)
ORDER BY created_at DESC;

-- switch to JSONB
-- no existing data gets lost
-- could be done initially by rewriting the CreateActivityLogTable migration as well: $table->jsonb('properties')->nullable();

ALTER TABLE public.activity_log
  ALTER COLUMN properties TYPE JSONB USING properties :: JSONB;

-- add index on logname and json attribute
-- creating a jsonb index with laravel's schema builder feels a bit messy but should be possible as well. not database driver independent!

CREATE INDEX "activity_log_log_name_properties-attributes-resource_id"
  ON activity_log (log_name, ((properties -> 'attributes' ->> 'resource_id') :: INT));

-- add index on logname and subject id
-- easily doable within the initial laravel migration

CREATE INDEX "activity_log_log_name_subject_id"
  ON activity_log (log_name, subject_id);

-- bliss. no more sequential scans, index scans only, average speed noticeably better
-- no more explicit json casting for properties column needed

SELECT *
FROM activity_log
WHERE (log_name = 'related_resources' AND (properties -> 'attributes' ->> 'resource_id') :: INT = 351030)
      OR (log_name = 'resources' AND subject_id = 351030)
ORDER BY created_at DESC;