79 lines
2.9 KiB
SQL
79 lines
2.9 KiB
SQL
CREATE TABLE IF NOT EXISTS ai_jobs (
|
|
id CHAR(36) PRIMARY KEY,
|
|
project_id INT NULL,
|
|
feature_type VARCHAR(80) NOT NULL,
|
|
status VARCHAR(24) NOT NULL DEFAULT 'queued',
|
|
phase VARCHAR(160) NOT NULL DEFAULT 'Venter på Codex',
|
|
input_hash CHAR(64) NULL,
|
|
input_payload JSON NULL,
|
|
result_payload JSON NULL,
|
|
error_message TEXT NULL,
|
|
model VARCHAR(100) NULL,
|
|
reasoning_effort VARCHAR(20) NULL,
|
|
usage_payload JSON NULL,
|
|
requested_by VARCHAR(120) NULL,
|
|
attempt_count INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
started_at DATETIME NULL,
|
|
completed_at DATETIME NULL,
|
|
expires_at DATETIME NULL,
|
|
FOREIGN KEY (project_id) REFERENCES customer_projects(id) ON DELETE CASCADE,
|
|
INDEX idx_ai_jobs_project_feature (project_id, feature_type, created_at),
|
|
INDEX idx_ai_jobs_status (status, created_at),
|
|
INDEX idx_ai_jobs_input_hash (input_hash)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS ai_suggestions (
|
|
id CHAR(36) PRIMARY KEY,
|
|
job_id CHAR(36) NOT NULL,
|
|
project_id INT NOT NULL,
|
|
suggestion_type VARCHAR(80) NOT NULL,
|
|
target_type VARCHAR(80) NULL,
|
|
target_id VARCHAR(120) NULL,
|
|
operation_type VARCHAR(40) NOT NULL DEFAULT 'update',
|
|
before_payload JSON NULL,
|
|
proposed_payload JSON NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
input_hash CHAR(64) NOT NULL,
|
|
decision_by VARCHAR(120) NULL,
|
|
decision_at DATETIME NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (job_id) REFERENCES ai_jobs(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (project_id) REFERENCES customer_projects(id) ON DELETE CASCADE,
|
|
INDEX idx_ai_suggestions_job (job_id, status),
|
|
INDEX idx_ai_suggestions_project (project_id, status)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_documents (
|
|
id CHAR(36) PRIMARY KEY,
|
|
project_id INT NOT NULL,
|
|
filename VARCHAR(255) NOT NULL,
|
|
original_name VARCHAR(255) NOT NULL,
|
|
file_path VARCHAR(700) NOT NULL,
|
|
file_hash CHAR(64) NOT NULL,
|
|
file_size INT NOT NULL,
|
|
mime_type VARCHAR(100) NOT NULL,
|
|
caption TEXT NULL,
|
|
uploaded_by VARCHAR(120) NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at DATETIME NULL,
|
|
FOREIGN KEY (project_id) REFERENCES customer_projects(id) ON DELETE CASCADE,
|
|
UNIQUE KEY uniq_project_document_hash (project_id, file_hash),
|
|
INDEX idx_project_documents_project (project_id, deleted_at)
|
|
);
|
|
|
|
INSERT INTO system_settings (setting_key, setting_value)
|
|
VALUES
|
|
('ai_feature.excel_review.enabled', 'true'),
|
|
('ai_feature.final_review.enabled', 'true'),
|
|
('ai_feature.risk_review.enabled', 'false'),
|
|
('ai_feature.package_suggestions.enabled', 'false'),
|
|
('ai_feature.history_comparison.enabled', 'false'),
|
|
('ai_feature.price_review.enabled', 'false'),
|
|
('ai_feature.customer_text.enabled', 'false'),
|
|
('ai_feature.document_review.enabled', 'false'),
|
|
('ai_feature.change_review.enabled', 'false'),
|
|
('ai_feature.excel_mapping.enabled', 'false'),
|
|
('ai_feature.support_draft.enabled', 'false')
|
|
ON DUPLICATE KEY UPDATE setting_value = setting_value;
|