59 lines
2.2 KiB
SQL
59 lines
2.2 KiB
SQL
-- PostgreSQL Audit Schema for Knowledge Base Tracking
|
|
-- Task 1.2: Create 3 tables with audit trail functionality
|
|
|
|
-- Table 1: knowledge_base_updates
|
|
-- Tracks all knowledge base updates with approval workflow
|
|
CREATE TABLE IF NOT EXISTS knowledge_base_updates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
ticket_id INTEGER NOT NULL,
|
|
problem_text TEXT NOT NULL,
|
|
kategorie VARCHAR(100),
|
|
lösung TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
approved_at TIMESTAMP,
|
|
executed_at TIMESTAMP,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
|
|
freescout_note TEXT,
|
|
CONSTRAINT status_check CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED', 'EXECUTED'))
|
|
);
|
|
|
|
-- Indexes for knowledge_base_updates
|
|
CREATE INDEX idx_kbu_ticket_id ON knowledge_base_updates(ticket_id);
|
|
CREATE INDEX idx_kbu_status ON knowledge_base_updates(status);
|
|
CREATE INDEX idx_kbu_kategorie ON knowledge_base_updates(kategorie);
|
|
|
|
-- Table 2: kb_feedback
|
|
-- Stores user feedback on knowledge base entries
|
|
CREATE TABLE IF NOT EXISTS kb_feedback (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
kb_entry_id VARCHAR(255) NOT NULL,
|
|
feedback VARCHAR(50) NOT NULL,
|
|
reason TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT feedback_check CHECK (feedback IN ('helpful', 'not_helpful'))
|
|
);
|
|
|
|
-- Index for kb_feedback
|
|
CREATE INDEX idx_kbf_entry_id ON kb_feedback(kb_entry_id);
|
|
|
|
-- Table 3: workflow_executions
|
|
-- Tracks workflow execution history
|
|
CREATE TABLE IF NOT EXISTS workflow_executions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workflow_name VARCHAR(255),
|
|
ticket_id INTEGER,
|
|
status VARCHAR(50),
|
|
error_message TEXT,
|
|
execution_time_ms INTEGER,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indexes for workflow_executions
|
|
CREATE INDEX idx_we_ticket_id ON workflow_executions(ticket_id);
|
|
CREATE INDEX idx_we_workflow_name ON workflow_executions(workflow_name);
|
|
|
|
-- Grant permissions to kb_user
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON knowledge_base_updates TO kb_user;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON kb_feedback TO kb_user;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON workflow_executions TO kb_user;
|