46 lines
1.8 KiB
SQL
46 lines
1.8 KiB
SQL
-- IP WhiteList schema
|
|
|
|
CREATE TABLE IF NOT EXISTS companies (
|
|
id SERIAL PRIMARY KEY,
|
|
client_id VARCHAR(64) UNIQUE NOT NULL,
|
|
name VARCHAR(255),
|
|
custom_limit INTEGER DEFAULT NULL CHECK (custom_limit IS NULL OR custom_limit >= 0),
|
|
owner_email TEXT DEFAULT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS whitelist_entries (
|
|
id SERIAL PRIMARY KEY,
|
|
company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE RESTRICT,
|
|
value_cidr VARCHAR(18) NOT NULL CHECK (value_cidr ~ '^(\d{1,3}\.){3}\d{1,3}/\d{1,2}$'),
|
|
comment VARCHAR(255),
|
|
created_by VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_by VARCHAR(255),
|
|
updated_at TIMESTAMPTZ,
|
|
deleted_by VARCHAR(255),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Уникальность активного CIDR в рамках компании (защита от гонок дублей)
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_entries_active_cidr
|
|
ON whitelist_entries(company_id, value_cidr) WHERE deleted_at IS NULL;
|
|
|
|
-- Составной для списка активных записей компании
|
|
CREATE INDEX IF NOT EXISTS idx_entries_active
|
|
ON whitelist_entries(company_id, created_at DESC) WHERE deleted_at IS NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id SERIAL PRIMARY KEY,
|
|
user_email VARCHAR(255) NOT NULL,
|
|
company_id INTEGER NOT NULL,
|
|
action VARCHAR(32) NOT NULL CHECK (action IN ('CREATE','UPDATE','DELETE')),
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
entry_id INTEGER,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_audit_company_time ON audit_log(company_id, created_at DESC);
|