RelationalDesign: schema_creation.sql

DROP SCHEMA IF EXISTS project CASCADE;
CREATE SCHEMA project;
SET search_path TO project;

-- ---------- Основни ентитети ----------

CREATE TABLE tenants (
    id           INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name         TEXT NOT NULL,
    owner_email  TEXT NOT NULL,
    created_at   TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE users (
    id           INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email        TEXT NOT NULL UNIQUE,
    name         TEXT,
    picture      TEXT,
    created_at   TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE memberships (
    user_id      INTEGER NOT NULL,
    tenant_id    INTEGER NOT NULL,
    role         TEXT NOT NULL DEFAULT 'admin',
    created_at   TIMESTAMP NOT NULL DEFAULT now(),
    CONSTRAINT pk_memberships PRIMARY KEY (user_id, tenant_id),
    CONSTRAINT fk_memberships_user   FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE CASCADE,
    CONSTRAINT fk_memberships_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE TABLE environments (
    id           INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tenant_id    INTEGER NOT NULL,
    name         TEXT NOT NULL,
    created_at   TIMESTAMP NOT NULL DEFAULT now(),
    CONSTRAINT uq_environments_tenant_name UNIQUE (tenant_id, name),
    CONSTRAINT fk_environments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

-- env_tokens -> environments преку составен клуч (tenant_id, env_name)  [релација "issues"]
CREATE TABLE env_tokens (
    id           INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tenant_id    INTEGER NOT NULL,
    env_name     TEXT NOT NULL,
    token        TEXT NOT NULL UNIQUE,
    created_at   TIMESTAMP NOT NULL DEFAULT now(),
    expires_at   TIMESTAMP,
    CONSTRAINT fk_env_tokens_env FOREIGN KEY (tenant_id, env_name)
        REFERENCES environments(tenant_id, name) ON DELETE CASCADE
);

-- env_settings -> environments (составен клуч)  [релација "configured_by", 1:1]
CREATE TABLE env_settings (
    tenant_id             INTEGER NOT NULL,
    env_name              TEXT NOT NULL,
    save_process_history  BOOLEAN NOT NULL DEFAULT false,
    save_metrics_history  BOOLEAN NOT NULL DEFAULT false,
    save_network_history  BOOLEAN NOT NULL DEFAULT false,
    created_at            TIMESTAMP DEFAULT now(),
    updated_at            TIMESTAMP DEFAULT now(),
    CONSTRAINT pk_env_settings PRIMARY KEY (tenant_id, env_name),
    CONSTRAINT fk_env_settings_env FOREIGN KEY (tenant_id, env_name)
        REFERENCES environments(tenant_id, name) ON DELETE CASCADE
);

-- ---------- Мрежна инфраструктура и организациска структура ----------

-- networks -> environments (составен клуч)  [релација "has_networks"]
CREATE TABLE networks (
    id           INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tenant_id    INTEGER NOT NULL,
    env_name     TEXT NOT NULL,
    name         TEXT NOT NULL,
    cidr         TEXT NOT NULL,
    gateway_ip   TEXT,
    vlan         INTEGER,
    CONSTRAINT uq_networks_tenant_name UNIQUE (tenant_id, name),
    CONSTRAINT fk_networks_env FOREIGN KEY (tenant_id, env_name)
        REFERENCES environments(tenant_id, name) ON DELETE CASCADE
);

-- computer_groups со хиерархија (self-reference)  [релација "parent_of"]
CREATE TABLE computer_groups (
    id           INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tenant_id    INTEGER NOT NULL,
    name         TEXT NOT NULL,
    description  TEXT,
    parent_id    INTEGER,
    CONSTRAINT uq_computer_groups_tenant_name UNIQUE (tenant_id, name),
    CONSTRAINT fk_computer_groups_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_computer_groups_parent FOREIGN KEY (parent_id) REFERENCES computer_groups(id) ON DELETE SET NULL
);