20 lines
1.2 KiB
MySQL
20 lines
1.2 KiB
MySQL
|
-- Новости компании: блок на сводке и реестр в офисе
|
|||
|
|
CREATE TABLE IF NOT EXISTS company_news (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
title TEXT NOT NULL,
|
|||
|
|
body TEXT,
|
|||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'pending', 'published')),
|
|||
|
|
created_by BIGINT REFERENCES portal_users(id) ON DELETE SET NULL,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
published_at TIMESTAMPTZ,
|
|||
|
|
notify_departments JSONB DEFAULT '[]',
|
|||
|
|
notify_employee_ids JSONB DEFAULT '[]'
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_company_news_status ON company_news(status);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_company_news_created_at ON company_news(created_at DESC);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_company_news_published_at ON company_news(published_at DESC NULLS LAST) WHERE status = 'published';
|
|||
|
|
|
|||
|
|
COMMENT ON TABLE company_news IS 'Новости компании: черновики и публикации с уведомлением по отделам/сотрудникам';
|