ConsoleBot/Scripts/Postgres/ToDoListDb.sql - main - d.alekseev/SmartMenuBot

ConsoleBot/Scripts/Postgres/ToDoListDb.sql
86 строк3 KB
d.alekseev
feat: нотификации и фоновые задачи доставки уведомлений
26 фев 2026, 10:55
26 фев 2026, 10:554338916
-- DDL for PostgreSQL database "ToDoList"
-- Tables are based on Core/Entities: ToDoUser, ToDoList, ToDoItem
-- Note: double quotes are used to preserve identifier case.

BEGIN;

CREATE TABLE IF NOT EXISTS "ToDoUser"
(
    "UserId"           uuid        NOT NULL,
    "TelegramUserId"   bigint      NOT NULL,
    "TelegramUserName" text        NULL,
    "RegisteredAt"     timestamptz NOT NULL,
    CONSTRAINT "PK_ToDoUser" PRIMARY KEY ("UserId")
);

CREATE TABLE IF NOT EXISTS "ToDoList"
(
    "Id"        uuid        NOT NULL,
    "Name"      varchar(10) NOT NULL,
    "UserId"    uuid        NOT NULL,
    "CreatedAt" timestamptz NOT NULL,
    CONSTRAINT "PK_ToDoList" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ToDoList_UserId_ToDoUser_UserId"
        FOREIGN KEY ("UserId")
            REFERENCES "ToDoUser" ("UserId")
            ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS "ToDoItem"
(
    "Id"             uuid        NOT NULL,
    "UserId"         uuid        NOT NULL,
    "ListId"         uuid        NULL,
    "Name"           text        NOT NULL,
    "CreatedAt"      timestamptz NOT NULL,
    "Deadline"       timestamptz NOT NULL,
    "State"          int         NOT NULL,
    "StateChangedAt" timestamptz NULL,
    CONSTRAINT "PK_ToDoItem" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ToDoItem_UserId_ToDoUser_UserId"
        FOREIGN KEY ("UserId")
            REFERENCES "ToDoUser" ("UserId")
            ON DELETE RESTRICT,
    CONSTRAINT "FK_ToDoItem_ListId_ToDoList_Id"
        FOREIGN KEY ("ListId")
            REFERENCES "ToDoList" ("Id")
            ON DELETE RESTRICT,
    CONSTRAINT "CK_ToDoItem_State" CHECK ("State" IN (0, 1))
);

CREATE TABLE IF NOT EXISTS "Notification"
(
    "Id"         uuid        NOT NULL,
    "UserId"     uuid        NOT NULL,
    "Type"       text        NOT NULL,
    "Text"       text        NOT NULL,
    "ScheduledAt" timestamptz NOT NULL,
    "IsNotified" boolean     NOT NULL,
    "NotifiedAt" timestamptz NULL,
    CONSTRAINT "PK_Notification" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Notification_UserId_ToDoUser_UserId"
        FOREIGN KEY ("UserId")
            REFERENCES "ToDoUser" ("UserId")
            ON DELETE RESTRICT
);

-- Indexes
CREATE UNIQUE INDEX IF NOT EXISTS "UX_ToDoUser_TelegramUserId"
    ON "ToDoUser" ("TelegramUserId");

CREATE INDEX IF NOT EXISTS "IX_ToDoList_UserId"
    ON "ToDoList" ("UserId");

CREATE INDEX IF NOT EXISTS "IX_ToDoItem_UserId"
    ON "ToDoItem" ("UserId");

CREATE INDEX IF NOT EXISTS "IX_ToDoItem_ListId"
    ON "ToDoItem" ("ListId");

CREATE INDEX IF NOT EXISTS "IX_Notification_UserId"
    ON "Notification" ("UserId");

CREATE UNIQUE INDEX IF NOT EXISTS "UX_Notification_UserId_Type"
    ON "Notification" ("UserId", "Type");

COMMIT;