Database

PostgreSQL database is used as a storage.

List of tables

                List of relations
 Schema |         Name         | Type  |  Owner
--------+----------------------+-------+----------
 public | event_targets        | table | postgres
 public | migration_info       | table | postgres
 public | new_reports          | table | postgres
 public | notification_types   | table | postgres
 public | read_errors          | table | postgres
 public | reported             | table | postgres
 public | states               | table | postgres
(7 rows)

Schema description

Database schema is described in this document

Schema regeneration

DB schema description can be generated by generate_db_schema_doc.sh script. Output is written into directory docs/db-description/. Its content can be viewed at this address.

Structure of all tables in database

Table `migration_info`

This table contains information about the latest DB schema and migration status.

 Column  |  Type   | Modifiers
---------+---------+-----------
 version | integer | not null

Table `new_reports`

This table contains new reports consumed from Kafka topic and stored to database in shrunk format (some attributes are removed).

   Column     |            Type             | Modifiers
--------------+-----------------------------+-----------
 org_id       | integer                     | not null
 account_id   | integer                     | not null
 cluster      | character(36)               | not null
 report       | character varying           | not null
 updated_at   | timestamp without time zone | not null
 kafka_offset | bigint                      | not null default 0
Indexes:
    "new_reports_pkey" PRIMARY KEY, btree (org_id, cluster, updated_at)
    "new_reports_cluster_idx" btree (cluster)
    "new_reports_org_id_idx" btree (org_id)
    "new_reports_updated_at_asc_idx" btree (updated_at)
    "new_reports_updated_at_desc_idx" btree (updated_at DESC)
    "report_kafka_offset_btree_idx" btree (kafka_offset)

Table `reported`

Information of notifications reported to user or skipped due to some conditions.

      Column       |            Type             | Modifiers
-------------------+-----------------------------+-----------
 org_id            | integer                     | not null
 account_id        | integer                     | not null
 cluster           | character(36)               | not null
 notification_type | integer                     | not null
 state             | integer                     | not null
 report            | character varying           | not null
 updated_at        | timestamp without time zone | not null
 notified_at       | timestamp without time zone | not null
 error_log         | character varying           | 
 event_type_id     | integer                     | not null


Indexes:
    "reported_pkey" PRIMARY KEY, btree (org_id, cluster)
    "notified_at_desc_idx" btree (notified_at DESC)
    "updated_at_desc_idx" btree (updated_at)
Foreign-key constraints:
    "fk_notification_type" FOREIGN KEY (notification_type) REFERENCES notification_types(id)
    "fk_state" FOREIGN KEY (state) REFERENCES states(id)
    "reported_event_type_id_fkey" FOREIGN KEY (event_type_id) REFERENCES event_targets(id)

Table `notification_types`

This table contains list of all notification types used by Notification service. Frequency can be specified as in crontab - https://crontab.guru/

  Column   |       Type        | Modifiers
-----------+-------------------+-----------
 id        | integer           | not null
 value     | character varying | not null
 frequency | character varying | not null
 comment   | character varying |
Indexes:
    "notification_types_pkey" PRIMARY KEY, btree (id)
    "notification_types_id_idx" btree (id)
Referenced by:
    TABLE "reported" CONSTRAINT "fk_notification_type" FOREIGN KEY (notification_type) REFERENCES notification_types(id)

Currently the following values are stored in this read-only table:

 id |  value  |  frequency  |               comment                
----+---------+-------------+--------------------------------------
  1 | instant | * * * * * * | instant notifications performed ASAP
  2 | instant | @weekly     | weekly summary
(2 rows)

Table `states`

This table contains states for each row stored in reported table. User can be notified about the report, report can be skipped if the same as previous, skipped becuase of lower pripority, or can be in error state.

 Column  |       Type        | Modifiers
---------+-------------------+-----------
 id      | integer           | not null
 value   | character varying | not null
 comment | character varying |
Indexes:
    "states_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "reported" CONSTRAINT "fk_state" FOREIGN KEY (state) REFERENCES states(id)

Currently the following values are stored in this read-only table:

 id | value |                   comment                   
----+-------+---------------------------------------------
  1 | sent  | notification has been sent to user
  2 | same  | skipped, report is the same as previous one
  3 | lower | skipped, all issues has low priority
  4 | error | notification delivery error
(4 rows)

Table `event_targets`

This table contains specification of all event targets currently supported.

                 Table "public.event_targets"
  Column  |       Type        | Collation | Nullable | Default 
----------+-------------------+-----------+----------+---------
 id       | integer           |           | not null | 
 name     | character varying |           | not null | 
 metainfo | character varying |           | not null | 

Indexes:
    "event_targets_pkey" PRIMARY KEY, btree (id)
    "event_targets_metainfo_key" UNIQUE CONSTRAINT, btree (metainfo)
    "event_targets_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "reported" CONSTRAINT "reported_event_type_id_fkey" FOREIGN KEY (event_type_id) REFERENCES event_targets(id)

Currently the following values are stored in this read-only table:

 id |         name          |                             metainfo              
----+-----------------------+-------------------------------------------------------------------
  1 | notifications backend | the target of the report is the ccx notification s ervice back end
  2 | service log           | the target of the report is the ServiceLog
(2 rows)

Table `read_errors`

This table stores information about errors that are detected during new reports collection. There’s 1:N mapping between new_reports and read_errors tables which means that multiple errors can be stored for one new report.

                                        Table "public.read_errors"
   Column   |            Type             |                           Modifiers
------------+-----------------------------+----------------------------------------------------------------
 error_id   | integer                     | not null default nextval('read_errors_error_id_seq'::regclass)
 org_id     | integer                     | not null
 cluster    | character(36)               | not null
 updated_at | timestamp without time zone | not null
 error_text | character varying(1000)     | not null
 created_at | timestamp without time zone | not null
Indexes:
    "read_errors_pkey" PRIMARY KEY, btree (error_id)
    "read_errors_error_id_org_id_cluster_updated_at_key"
    UNIQUE CONSTRAINT, btree (error_id, org_id, cluster, updated_at)
Foreign-key constraints:
    "read_errors_org_id_fkey" FOREIGN KEY (org_id, cluster, updated_at)
    REFERENCES new_reports(org_id, cluster, updated_at)

Database

List of tables

Schema description

Schema regeneration

Structure of all tables in database

Table migration_info

Table new_reports

Table reported

Table notification_types

Table states

Table event_targets

Table read_errors

Table `migration_info`

Table `new_reports`

Table `reported`

Table `notification_types`

Table `states`

Table `event_targets`

Table `read_errors`