User:Bluedeck/haystack/db

CREATE TABLE permission_groups (

   name VARCHAR(256) NOT NULL UNIQUE PRIMARY KEY,
   t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
   t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (

    id BIGSERIAL PRIMARY KEY,
    display_name VARCHAR(1024),
    username_normalized VARCHAR(1024) UNIQUE,
    email_primary VARCHAR(1024) UNIQUE,
    settings JSONB,
    lang_code VARCHAR(64),
    blocked BOOLEAN NOT NULL DEFAULT false,
    blocked_till TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    blocked_indef BOOLEAN NOT NULL DEFAULT false,
    bot BOOLEAN NOT NULL DEFAULT false,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX users_by_username_normalized ON users (username_normalized);
CREATE UNIQUE INDEX users_by_email_primary ON users (email_primary);

CREATE TABLE sessions (

    id BIGSERIAL PRIMARY KEY,
    summary VARCHAR(1024),
    secret VARCHAR(64) NOT NULL,
    invalidated BOOLEAN NOT NULL DEFAULT false,
    expiry TIMESTAMP WITH TIME ZONE NOT NULL,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE        INDEX sessions_by_user_id ON sessions (user_id);
CREATE UNIQUE INDEX sessions_by_secret ON sessions (secret);

CREATE TABLE feeds (

    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    name VARCHAR(1024) UNIQUE,
    creator_read_revision BOOLEAN NOT NULL DEFAULT false,
    creator_delete_feed BOOLEAN NOT NULL DEFAULT false,
    read_permission_name VARCHAR(64),
    settings JSONB,
    deleted BOOLEAN NOT NULL DEFAULT false,
    protected BOOLEAN NOT NULL DEFAULT false,
    protected_till TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    protected_indef BOOLEAN NOT NULL DEFAULT false,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX feeds_by_name ON feeds (name);
CREATE        INDEX feeds_by_user_id ON feeds (user_id);

CREATE TABLE pages (

    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    name VARCHAR(1024) UNIQUE,
    creator_read BOOLEAN NOT NULL DEFAULT false,
    creator_delete BOOLEAN NOT NULL DEFAULT false,
    settings JSONB,
    deleted BOOLEAN NOT NULL DEFAULT false,
    protected BOOLEAN NOT NULL DEFAULT false,
    protected_till TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    protected_indef BOOLEAN NOT NULL DEFAULT false,
    mime_type VARCHAR(128) NOT NULL DEFAULT 'text/plain',
    feed_id BIGINT REFERENCES feeds (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX pages_by_name ON pages (name);
CREATE        INDEX pages_by_feed_id ON pages (feed_id);
CREATE        INDEX pages_by_user_id ON pages (user_id);

CREATE TABLE revisions (

    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    user_id BIGINT REFERENCES users (id) ON DELETE RESTRICT,
    page_id BIGINT REFERENCES pages (id) ON DELETE RESTRICT,
    feed_id BIGINT REFERENCES feeds (id) ON DELETE RESTRICT,
    creator_read BOOLEAN NOT NULL DEFAULT false,
    creator_delete BOOLEAN NOT NULL DEFAULT false,
    lang_code VARCHAR(64),
    settings JSONB,
    read_permission_name VARCHAR(64),
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX revisions_by_user_id ON revisions (user_id);
CREATE INDEX revisions_by_page_id ON revisions (page_id);
CREATE INDEX revisions_by_feed_id ON revisions (feed_id);

CREATE TABLE tags (

    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    name VARCHAR(1024) UNIQUE,
    description BIGINT REFERENCES pages (id) ON DELETE RESTRICT,
    color VARCHAR(64),
    hidden BOOLEAN NOT NULL DEFAULT false,
    deleted BOOLEAN NOT NULL DEFAULT false,
    settings JSONB,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX tags_by_name ON tags (name);
CREATE        INDEX tags_by_color ON tags (color);

CREATE TABLE notifications (

    id BIGSERIAL PRIMARY KEY,
    summary VARCHAR(1024),
    revision_id BIGINT NOT NULL REFERENCES revisions (id) ON DELETE RESTRICT,
    deleted BOOLEAN NOT NULL DEFAULT false,
    viewed BOOLEAN NOT NULL DEFAULT false,
    sender_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    receiver_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX notifications_by_receiver_id ON notifications (receiver_id);
CREATE INDEX notifications_by_receiver_id_deleted ON notifications (receiver_id, deleted);
CREATE INDEX notifications_by_receiver_id_sender_id ON notifications (receiver_id, sender_id);
CREATE INDEX notifications_by_receiver_id_sender_id_deleted ON notifications (receiver_id, sender_id, deleted);

CREATE TABLE logs (

    id BIGSERIAL PRIMARY KEY,
    settings JSONB,
    type VARCHAR(64),
    summary VARCHAR(1024),
    user_id BIGINT NOT NULL REFERENCES users (id),
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX logs_by_user_id ON logs (user_id);
CREATE INDEX logs_by_type ON logs (type);
CREATE INDEX logs_by_user_id_type ON logs (user_id, type);

CREATE TABLE users_permission_groups_pivot (

    user_id BIGINT NOT NULL REFERENCES users (id),
    group_name VARCHAR(256) NOT NULL REFERENCES permission_groups (name)
    PRIMARY KEY (user_id, permission_group_name)
);

CREATE INDEX users_permission_groups_pivot_by_user_id ON users_permission_groups_pivot (user_id);
CREATE INDEX users_permission_groups_pivot_by_group_name ON users_permission_groups_pivot (group_name);

CREATE TABLE pages_tags_pivot (

    tag_id BIGINT NOT NULL REFERENCES tags (id) ON DELETE RESTRICT,
    page_id BIGINT NOT NULL REFERENCES pages (id) ON DELETE RESTRICT,
    PRIMARY KEY (tag_id, page_id)
);

CREATE INDEX pages_tags_pivot_by_tag_id ON pages_tags_pivot (tag_id);
CREATE INDEX pages_tags_pivot_by_page_id ON pages_tags_pivot (page_id);

CREATE TABLE feeds_tags_pivot (

    tag_id BIGINT NOT NULL REFERENCES tags (id) ON DELETE RESTRICT,
    feed_id BIGINT NOT NULL REFERENCES feeds (id) ON DELETE RESTRICT,
    PRIMARY KEY (tag_id, feed_id)
);

CREATE INDEX feeds_tags_pivot_by_tag_id ON feeds_tags_pivot (tag_id);
CREATE INDEX feeds_tags_pivot_by_feed_id ON feeds_tags_pivot (feed_id);