postgres/init/01_schema.sql - master - tenisaname/Test_example_docker

postgres/init/01_schema.sql
49 строк2 KB
etenkin
first_commit
10 июн 2026, 10:41
10 июн 2026, 10:4185dbc50
-- ============================================================
-- SPP Management Module — PostgreSQL Schema (3NF)
-- ============================================================

CREATE TABLE IF NOT EXISTS departments (
    id          SERIAL PRIMARY KEY,
    code        VARCHAR(50)  NOT NULL UNIQUE,
    name        VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT NOW()
);

-- Hierarchical SPP reference with SCD Type 2 history (valid_from / valid_to)
CREATE TABLE IF NOT EXISTS spp_nodes (
    id          SERIAL PRIMARY KEY,
    code        VARCHAR(50)  NOT NULL,
    name        VARCHAR(255) NOT NULL,
    parent_id   INTEGER      REFERENCES spp_nodes(id) ON DELETE SET NULL,
    level       INTEGER      NOT NULL CHECK (level BETWEEN 1 AND 10),
    status      VARCHAR(20)  NOT NULL DEFAULT 'active' CHECK (status IN ('active','inactive')),
    valid_from  DATE         NOT NULL,
    valid_to    DATE,
    created_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_valid_range CHECK (valid_to IS NULL OR valid_to > valid_from)
);

CREATE INDEX IF NOT EXISTS idx_spp_valid ON spp_nodes (valid_from, valid_to);
CREATE INDEX IF NOT EXISTS idx_spp_parent ON spp_nodes (parent_id);

-- SPP ↔ Department many-to-many
CREATE TABLE IF NOT EXISTS spp_department_links (
    id            SERIAL  PRIMARY KEY,
    spp_node_id   INTEGER NOT NULL REFERENCES spp_nodes(id) ON DELETE CASCADE,
    department_id INTEGER NOT NULL REFERENCES departments(id) ON DELETE CASCADE,
    UNIQUE (spp_node_id, department_id)
);

-- Saved distribution results (JSONB snapshot)
CREATE TABLE IF NOT EXISTS calculation_results (
    id                SERIAL       PRIMARY KEY,
    session_id        VARCHAR(255) NOT NULL,
    created_at        TIMESTAMP    NOT NULL DEFAULT NOW(),
    status            VARCHAR(50)  NOT NULL DEFAULT 'active',
    spp_version_date  DATE         NOT NULL,
    total_amount      NUMERIC(18,2) NOT NULL,
    distribution_data JSONB        NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_calc_session ON calculation_results (session_id);
CREATE INDEX IF NOT EXISTS idx_calc_created ON calculation_results (created_at DESC);