74 lines
3.2 KiB
MySQL
74 lines
3.2 KiB
MySQL
|
-- Production-ready case solutions and the first, read-only knowledge base release.
|
||
|
|
|
||
|
|
ALTER TABLE sag_solutions
|
||
|
|
ADD COLUMN IF NOT EXISTS problem TEXT,
|
||
|
|
ADD COLUMN IF NOT EXISTS root_cause TEXT,
|
||
|
|
ADD COLUMN IF NOT EXISTS investigation TEXT,
|
||
|
|
ADD COLUMN IF NOT EXISTS workaround TEXT,
|
||
|
|
ADD COLUMN IF NOT EXISTS visibility VARCHAR(30) NOT NULL DEFAULT 'internal',
|
||
|
|
ADD COLUMN IF NOT EXISTS approval_status VARCHAR(30) NOT NULL DEFAULT 'draft',
|
||
|
|
ADD COLUMN IF NOT EXISTS is_final BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
|
ADD COLUMN IF NOT EXISTS tags JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
|
|
ADD COLUMN IF NOT EXISTS products JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
|
|
ADD COLUMN IF NOT EXISTS updated_by_user_id INTEGER,
|
||
|
|
ADD COLUMN IF NOT EXISTS approved_by_user_id INTEGER,
|
||
|
|
ADD COLUMN IF NOT EXISTS approved_at TIMESTAMP;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sag_solution_versions (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
solution_id INTEGER NOT NULL REFERENCES sag_solutions(id) ON DELETE CASCADE,
|
||
|
|
version_number INTEGER NOT NULL,
|
||
|
|
snapshot JSONB NOT NULL,
|
||
|
|
changed_by_user_id INTEGER,
|
||
|
|
change_note TEXT,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE (solution_id, version_number)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sag_solution_versions_solution
|
||
|
|
ON sag_solution_versions(solution_id, version_number DESC);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS knowledge_articles (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
solution_id INTEGER NOT NULL UNIQUE REFERENCES sag_solutions(id) ON DELETE RESTRICT,
|
||
|
|
sag_id INTEGER NOT NULL REFERENCES sag_sager(id) ON DELETE RESTRICT,
|
||
|
|
customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
|
||
|
|
title VARCHAR(255) NOT NULL,
|
||
|
|
summary TEXT,
|
||
|
|
problem TEXT,
|
||
|
|
root_cause TEXT,
|
||
|
|
investigation TEXT,
|
||
|
|
solution TEXT NOT NULL,
|
||
|
|
workaround TEXT,
|
||
|
|
visibility VARCHAR(30) NOT NULL DEFAULT 'internal',
|
||
|
|
status VARCHAR(30) NOT NULL DEFAULT 'published',
|
||
|
|
tags JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
|
|
products JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
|
|
version_number INTEGER NOT NULL DEFAULT 1,
|
||
|
|
published_by_user_id INTEGER,
|
||
|
|
published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
reviewed_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
search_document TSVECTOR GENERATED ALWAYS AS (
|
||
|
|
to_tsvector('simple',
|
||
|
|
coalesce(title, '') || ' ' || coalesce(summary, '') || ' ' ||
|
||
|
|
coalesce(problem, '') || ' ' || coalesce(root_cause, '') || ' ' ||
|
||
|
|
coalesce(investigation, '') || ' ' || coalesce(solution, '') || ' ' ||
|
||
|
|
coalesce(workaround, '') || ' ' || coalesce(tags::text, '') || ' ' ||
|
||
|
|
coalesce(products::text, '')
|
||
|
|
)
|
||
|
|
) STORED
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_articles_search
|
||
|
|
ON knowledge_articles USING GIN(search_document);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_articles_scope
|
||
|
|
ON knowledge_articles(status, visibility, customer_id, updated_at DESC);
|
||
|
|
|
||
|
|
-- Preserve the two existing solutions as drafts; publication always requires an explicit approval.
|
||
|
|
UPDATE sag_solutions
|
||
|
|
SET approval_status = COALESCE(NULLIF(approval_status, ''), 'draft'),
|
||
|
|
visibility = COALESCE(NULLIF(visibility, ''), 'internal')
|
||
|
|
WHERE approval_status IS NULL OR approval_status = '' OR visibility IS NULL OR visibility = '';
|
||
|
|
|