Admin Dashboard and CRM Notification System.(How to trigger operations on ecto update, insert ,etc))

Questions / Help
1

Admin Dashboard and CRM Notification System

Database Relationships

The relationships between tables in the database are structured as follows:

  • Case: A case can have many accounts.
  • Account:
    • An account can have many messages.
    • An account is associated with a single service provider.
  • Service Provider (SP): A service provider can have many accounts.
  • Events:
    • An account can have multiple events, such as incoming and outgoing emails.
  • messages An account can have a conversation or several messages

Objective

Implement a status/notification system for accounts in the admin dashboard. The system should support the following statuses:

  • Unread messages.
  • Messages read but not acknowledged within N days.
  • Email notifications sent.
  • Custom statuses (e.g., Status X, Status Y).

But there are some status which need to be calculated based on case which has multiple accounts

Additional Requirements

  • Ability to remove a status from the queue, with an option to override it. For this reason, a database field is necessary.
  • Currently, statuses are determined using queries, but we aim to move this logic into database fields for better performance and maintainability.

Key Questions

1. Triggering Changes

How should we trigger changes (e.g., recalculating notification/status) for an account in scenarios like:

  • A new message for that account?
  • A new email or event?

2. Implementation Approach

Is this the correct approach to implement the notification/status system? Specifically:

  • Should we use multiple database columns or a single jsonb field in PostgreSQL?
  • Which approach is faster and more efficient?

anything you find wrong to improve here let me

2

I don’t see why would you use a JSONB column for that. What’s wrong with normal table columns?

Difficult to say without seeing some code. From what you wrote here I am not seeing the difficulty in general; it seems all of this can be done by simply storing a timestamp on when was the last time notifications / events / messages were acted on and then query for all records that are newer than it.

But in case I misunderstood you and you are really interested in how can you capture and act on database changes since some previous time then here’s one piece of PostgreSQL CDC software that can help you with that: GitHub - sequinstream/sequin: Postgres change data capture to streams and queues like Kafka, SQS, HTTP endpoints, and more

1 Like