v_alerts

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

entity_id

int4

√

null

entity_qualified_id

text

2147483647

√

null

created_at

timestamptz

35,6

√

null

updated_at

timestamptz

35,6

√

null

creator_id

int4

√

null

card_id

int4

√

null

card_qualified_id

text

2147483647

√

null

alert_condition

text

2147483647

√

null

schedule_type

text

2147483647

√

null

schedule_day

text

2147483647

√

null

schedule_hour

int4

√

null

archived

bool

√

null

recipient_type

varchar

√

null

recipients

text

2147483647

√

null

recipient_external

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH parsed_cron AS (
         SELECT n_1.id,
            ns.cron_schedule,
            ns.ui_display_type,
            split_part((ns.cron_schedule)::text, ' '::text, 2) AS minutes,
            split_part((ns.cron_schedule)::text, ' '::text, 3) AS hours,
            split_part((ns.cron_schedule)::text, ' '::text, 4) AS day_of_month,
            split_part((ns.cron_schedule)::text, ' '::text, 6) AS day_of_week
           FROM (notification n_1
             JOIN notification_subscription ns ON ((n_1.id = ns.notification_id)))
          WHERE (((n_1.payload_type)::text = 'notification/card'::text) AND ((ns.type)::text = 'notification-subscription/cron'::text))
        ), schedule_info AS (
         SELECT parsed_cron.id,
                CASE
                    WHEN ((parsed_cron.ui_display_type)::text = 'cron/raw'::text) THEN 'custom'::text
                    WHEN ((parsed_cron.minutes ~ '^\*$'::text) OR (parsed_cron.minutes ~ '^\d+/\d+$'::text)) THEN 'by the minute'::text
                    WHEN ((parsed_cron.day_of_month <> '*'::text) AND ((parsed_cron.day_of_week = '?'::text) OR (parsed_cron.day_of_week ~ '^\d#1$'::text) OR (parsed_cron.day_of_week ~ '^\dL$'::text))) THEN 'monthly'::text
                    WHEN ((parsed_cron.day_of_week <> '?'::text) AND (parsed_cron.day_of_week <> '*'::text)) THEN 'weekly'::text
                    WHEN (parsed_cron.hours <> '*'::text) THEN 'daily'::text
                    ELSE 'hourly'::text
                END AS schedule_type,
                CASE
                    WHEN (parsed_cron.day_of_week ~ '^1'::text) THEN 'sun'::text
                    WHEN (parsed_cron.day_of_week ~ '^2'::text) THEN 'mon'::text
                    WHEN (parsed_cron.day_of_week ~ '^3'::text) THEN 'tue'::text
                    WHEN (parsed_cron.day_of_week ~ '^4'::text) THEN 'wed'::text
                    WHEN (parsed_cron.day_of_week ~ '^5'::text) THEN 'thu'::text
                    WHEN (parsed_cron.day_of_week ~ '^6'::text) THEN 'fri'::text
                    WHEN (parsed_cron.day_of_week ~ '^7'::text) THEN 'sat'::text
                    ELSE NULL::text
                END AS schedule_day,
                CASE
                    WHEN (parsed_cron.hours = '*'::text) THEN NULL::integer
                    WHEN (parsed_cron.hours ~ '^\d+$'::text) THEN (parsed_cron.hours)::integer
                    WHEN (parsed_cron.hours ~ '^(\d+)/\d+$'::text) THEN ("substring"(parsed_cron.hours, '^(\d+)/\d+$'::text))::integer
                    ELSE NULL::integer
                END AS schedule_hour
           FROM parsed_cron
        ), agg_recipients AS (
         SELECT nr.notification_handler_id,
            string_agg((cu.email)::text, ','::text) AS recipients,
            ( SELECT string_agg(nr2.details, ','::text) AS string_agg
                   FROM notification_recipient nr2
                  WHERE ((nr2.notification_handler_id = nr.notification_handler_id) AND ((nr2.type)::text = 'notification-recipient/raw-value'::text))) AS recipient_external
           FROM (notification_recipient nr
             LEFT JOIN core_user cu ON (((nr.user_id = cu.id) AND ((nr.type)::text = 'notification-recipient/user'::text))))
          GROUP BY nr.notification_handler_id
        )
 SELECT n.id AS entity_id,
    ('notification_'::text || n.id) AS entity_qualified_id,
    n.created_at,
    n.updated_at,
    n.creator_id,
    nc.card_id,
    ('card_'::text || nc.card_id) AS card_qualified_id,
        CASE
            WHEN ((nc.send_condition)::text = 'has_result'::text) THEN 'rows'::text
            WHEN ((nc.send_condition)::text = ANY ((ARRAY['goal_above'::character varying, 'goal_below'::character varying])::text[])) THEN 'goal'::text
            ELSE NULL::text
        END AS alert_condition,
    si.schedule_type,
    si.schedule_day,
    si.schedule_hour,
    (NOT n.active) AS archived,
    nh.channel_type AS recipient_type,
    ar.recipients,
    ar.recipient_external
   FROM ((((notification n
     JOIN notification_card nc ON ((n.payload_id = nc.id)))
     JOIN schedule_info si ON ((n.id = si.id)))
     LEFT JOIN notification_handler nh ON ((n.id = nh.notification_id)))
     LEFT JOIN agg_recipients ar ON ((nh.id = ar.notification_handler_id)))
  WHERE ((n.payload_type)::text = 'notification/card'::text);

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views