1CREATE TABLE "notification_kinds" (
 2    "id" SERIAL PRIMARY KEY,
 3    "name" VARCHAR NOT NULL
 4);
 5
 6CREATE UNIQUE INDEX "index_notification_kinds_on_name" ON "notification_kinds" ("name");
 7
 8CREATE TABLE notifications (
 9    "id" SERIAL PRIMARY KEY,
10    "created_at" TIMESTAMP NOT NULL DEFAULT now(),
11    "recipient_id" INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
12    "kind" INTEGER NOT NULL REFERENCES notification_kinds (id),
13    "entity_id" INTEGER,
14    "content" TEXT,
15    "is_read" BOOLEAN NOT NULL DEFAULT FALSE,
16    "response" BOOLEAN
17);
18
19CREATE INDEX
20    "index_notifications_on_recipient_id_is_read_kind_entity_id"
21    ON "notifications"
22    ("recipient_id", "is_read", "kind", "entity_id");