show tag list while typing tag in subscribers list

[DinoBellini]

Show tag list when adding tag to a subscribers list

[Bowrna]

Could I pick up this issue and solve it?

Edit: Tagging you for bringing this up in your notification cc: @knadh

[knadh]

Sure @Bowrna. N number of arbitrary tags can be recorded across millions of subscribers. Cataloguing (distinct) them, keeping it up-to-date, and making them searchable in realtime is going to be a bit tricky. Please propose potential approaches before you start working on them.

[Bowrna]

The tag list should contain the tags associated across all the lists without duplicates. It's currently stored in the "tags" column in the table "lists" as an array. When the user types the tag in the list feature, this should show a drop-down with matches from the existing set of tags.

1. Pull out the tags from the "lists" table. It may be a big set, and we can make this API send the result after compressing it to the frontend.
2. In frontend, we can use a fuzzy search JS library like fuse.js(basic) and do a fuzzy search on the pulled out tags and show in a dropdown the closest match, and during save update the backend.

Please do let me know if I am thinking in the right direction.

Edit: @knadh updating this message to bring it up in your notification to know your view.

[Bowrna]

@knadh if you find time, could you check the above way of handling this usecase and let me know your views.

[Bowrna]

https://www.postgresql.org/docs/current/arrays.html#ARRAYS-SEARCHING

@knadh The idea I suggested above is not a good one, and I was reflecting on how, for each API call, this has to be sent as a request to pick the latest tags.

How about maintaining a separate tags table for the subscriber list, where each distinct array element becomes a row, and doing a fuzzy match on it becomes easier?

My bad, I didn't take into account how much data gets transferred for each list call, making the above solution I suggested not a very useful one.

[knadh]

This must ideally be a materialized view, but that comes with the complexities of keeping the view refreshed periodically, performance issues of large tables etc.

The other simple approach is to have a tags table and when subscribers are created and updated, the tags are collected in parallel and maintained in the tags table. Instead of an automated materialized view, app-side logic to maintain at tags catalogue. Downside is that it can easily go out of sync with what's in the subscribers table, but maybe that's acceptable UX.

Not an easy tradeoff, either.

[rallisf1]

IMHO the lists table has the least activity of all, adding a trigger to update a tags materialized view should not affect performance.

Create materialized view

CREATE MATERIALIZED VIEW mat_tags AS SELECT DISTINCT unnest(tags) AS tag FROM public.lists WHERE tags IS NOT NULL;

Create function

CREATE OR REPLACE FUNCTION refresh_mat_tags()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW mat_tags;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Create trigger

CREATE TRIGGER trigger_refresh_mat_tags
AFTER INSERT OR UPDATE OR DELETE ON lists
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_mat_tags();

My request is to expose it in the API. It makes a huge difference to know the existing tags when applying segmentation remotely.

P.S. The above queries work fine.

[knadh]

Yep, tag catalogue on lists is a no-brainer. Even if there are tens of thousands of lists (very very unlikely), it will still be fine.

[Bowrna]

Thanks @rallisf1 for your suggestion. Let me implement this and raise a PR in 2 days. @knadh

[knadh]

Just realised that @vividvilla has been planning a unified approach for tags. A single materialized view approach that tracks tags across media, lists, campaigns (subscribers may require a separate approach), unifying all tag logic into one place. This will also enable to create a reusable tagsearch UI component that can be used across these screens. @vividvilla could you share your plan here?

[vividvilla]

To set context, I am currently working on the feature to add tags to media table and came across this discussion.

Currently in Listmonk, tags are available for campaigns and lists tables, but there are no APIs to retrieve all available tags, and frontend tag search is not implemented yet.

Here is the proposed plan:

• Create a single materialized view that aggregates all tags from different tables (media, campaigns, and lists), with a type field to indicate the source table and tags field for the actual tags.
• Introduce a new API endpoint: /api/tags?type=xxx or /api/campaigns/tags. @knadh can make the final decision on the structure.
• Create a Vue component using Buefy tag input that accepts a type parameter and can be used across media, campaigns, and lists. This component will call the API to return all tags as auto-suggestions.
• Replace the current search functionality in media, campaigns, and lists with the above component.

PS: Maybe we should move this to a new issue since it has deviated from the original request?

The materialized view would look something like this: Each type + tag combination creates a new row, and we can also record the related id and usage_count, though these are not mandatory.

CREATE MATERIALIZED VIEW mat_tag_inventory AS
  WITH all_tags AS (
    SELECT 'list' as type, UNNEST(tags) as tag, id as entity_id
    FROM lists
    WHERE tags IS NOT NULL AND array_length(tags, 1) > 0

    UNION ALL

    SELECT 'campaign' as type, UNNEST(tags) as tag, id as entity_id
    FROM campaigns
    WHERE tags IS NOT NULL AND array_length(tags, 1) > 0
  )
  SELECT
    NOW() as updated_at,
    type,
    tag,
    COUNT(*) as usage_count,
    ARRAY_AGG(DISTINCT entity_id ORDER BY entity_id) as entity_ids
  FROM all_tags
  WHERE tag IS NOT NULL AND trim(tag) != ''
  GROUP BY type, tag
  ORDER BY type, tag;

-- Indexes
CREATE UNIQUE INDEX mat_tag_inventory_idx ON mat_tag_inventory (type, tag);
CREATE INDEX mat_tag_inventory_usage_idx ON mat_tag_inventory (usage_count DESC);

-- Function to manually refresh the materialized view
CREATE OR REPLACE FUNCTION refresh_tag_inventory()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY mat_tag_inventory;
END;
$$ LANGUAGE plpgsql;

-- Function to be called by triggers
CREATE OR REPLACE FUNCTION trigger_refresh_tag_inventory()
RETURNS trigger AS $$
BEGIN
  PERFORM refresh_tag_inventory();
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Triggers on lists table
DROP TRIGGER IF EXISTS trigger_lists_tags_refresh ON lists;
CREATE TRIGGER trigger_lists_tags_refresh
  AFTER INSERT OR UPDATE OF tags OR DELETE ON lists
  FOR EACH STATEMENT
  EXECUTE FUNCTION trigger_refresh_tag_inventory();

-- Triggers on campaigns table
DROP TRIGGER IF EXISTS trigger_campaigns_tags_refresh ON campaigns;
CREATE TRIGGER trigger_campaigns_tags_refresh
  AFTER INSERT OR UPDATE OF tags OR DELETE ON campaigns
  FOR EACH STATEMENT
  EXECUTE FUNCTION trigger_refresh_tag_inventory();

[knadh]

This sounds good!

• It makes sense to move this to a new issue.
• Aggregating subscriber tags will require a much more careful approach. Maybe the same table, but different trigger conditions?
• /api/campaigns/tags and not /api/tags?type=xxx certainly to be consistent with the resource-based semantics.

[Bowrna]

• Introduce a new API endpoint: /api/tags?type=xxx or /api/campaigns/tags. @knadh can make the final decision on the structure.
• Create a Vue component using Buefy tag input that accepts a type parameter and can be used across media, campaigns, and lists. This component will call the API to return all tags as auto-suggestions.

@vividvilla Could I pick up this part alone and solve it in a different PR if its ok? Let me know what do you think.

[vividvilla]

@Bowrna sure, actually it's better if you take up the other tasks as well. If you need help with any part let me know, I will be happy to help.

[Bowrna]

@Bowrna sure, actually it's better if you take up the other tasks as well. If you need help with any part let me know, I will be happy to help.

Cool, let me try it and ask for help if i am struck

[Bowrna]

@knadh why do you aggregating subscriber tags will require a much more careful approach? From the lists available api calls, I see only delete operation is alone supporting bulk ids to delete at one go. Can you tell me if i am missing out any valid case here?

[knadh]

Aggregating unique tags (string array) across millions of subscriber rows is going to be extremely costly.

[github-actions[bot]]

This issue has been marked 'stale' after 90 days of inactivity. If there is no further activity, it will be closed in 7 days.