Схема базы данных¶

START TRANSACTION;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'bell') THEN
        CREATE SCHEMA bell;
    END IF;
END $EF$;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE bell.notifications (
    id uuid NOT NULL,
    orpon bigint NOT NULL,
    app_title character varying(256),
    room_number character varying(4) NOT NULL,
    header character varying(128) NOT NULL,
    text character varying(256) NOT NULL,
    status_type character varying(32) NOT NULL,
    created_at timestamp without time zone NOT NULL,
    status_changed_at timestamp without time zone NOT NULL,
    payload jsonb NOT NULL DEFAULT '{}',
    CONSTRAINT pk_notifications PRIMARY KEY (id)
);
COMMENT ON TABLE bell.notifications IS 'Уведомление. # Описание модели';
COMMENT ON COLUMN bell.notifications.id IS 'Идентификатор. Создаётся сервером. # Тип: Guid';
COMMENT ON COLUMN bell.notifications.orpon IS 'ОРПОН. # Диапазон 1..9223372036854775807';
COMMENT ON COLUMN bell.notifications.app_title IS 'Наименование приложения-отправителя. # Диапазон: 3..256';
COMMENT ON COLUMN bell.notifications.room_number IS 'Номер квартиры. # Диапазон: 1..4';
COMMENT ON COLUMN bell.notifications.header IS 'Заголовок. # Диапазон: 3..128';
COMMENT ON COLUMN bell.notifications.text IS 'Тело. # Диапазон: 3..256';
COMMENT ON COLUMN bell.notifications.status_type IS 'Статус';
COMMENT ON COLUMN bell.notifications.created_at IS 'Дата создания. # Тип: DateTime';
COMMENT ON COLUMN bell.notifications.status_changed_at IS 'Дата последнего изменения статуса. Заполняется и обновляется сервером. # Тип: DateTime';
COMMENT ON COLUMN bell.notifications.payload IS 'Полезная нагрузка';

CREATE INDEX ix_notifications_created_at ON bell.notifications USING btree (created_at DESC);

CREATE INDEX ix_notifications_orpon ON bell.notifications (orpon DESC);

CREATE INDEX ix_notifications_payload ON bell.notifications USING gin (payload jsonb_ops);

CREATE INDEX ix_notifications_room_number ON bell.notifications (room_number DESC);

CREATE INDEX ix_notifications_status_changed_at ON bell.notifications USING btree (status_changed_at DESC);

CREATE INDEX ix_notifications_status_type ON bell.notifications (status_type DESC);


COMMIT;