32 lines
1.7 KiB
MySQL
32 lines
1.7 KiB
MySQL
|
-- Support-Tickets: je Organisation, mit Verlauf (Kunde/Personal), interne Notizen nur für Personal sichtbar
|
||
|
|
CREATE TABLE tickets (
|
||
|
|
id CHAR(36) PRIMARY KEY,
|
||
|
|
number VARCHAR(20) NOT NULL UNIQUE,
|
||
|
|
org_id CHAR(36) NOT NULL REFERENCES organizations(id),
|
||
|
|
resource_id CHAR(36) NULL REFERENCES resources(id),
|
||
|
|
subject VARCHAR(200) NOT NULL,
|
||
|
|
status ENUM('open','pending_customer','pending_staff','resolved','closed') NOT NULL DEFAULT 'pending_staff',
|
||
|
|
priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
|
||
|
|
created_by CHAR(36) NOT NULL REFERENCES users(id),
|
||
|
|
assigned_to CHAR(36) NULL REFERENCES users(id),
|
||
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
|
|
last_message_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
resolved_at DATETIME(3) NULL,
|
||
|
|
closed_at DATETIME(3) NULL,
|
||
|
|
KEY idx_tickets_org (org_id), KEY idx_tickets_status (status), KEY idx_tickets_assigned (assigned_to)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
CREATE TABLE ticket_messages (
|
||
|
|
id CHAR(36) PRIMARY KEY,
|
||
|
|
ticket_id CHAR(36) NOT NULL REFERENCES tickets(id),
|
||
|
|
author_id CHAR(36) NOT NULL REFERENCES users(id),
|
||
|
|
author_kind ENUM('customer','staff') NOT NULL,
|
||
|
|
body TEXT NOT NULL,
|
||
|
|
internal_note TINYINT(1) NOT NULL DEFAULT 0, -- nur für Personal sichtbar, nie für den Kunden
|
||
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
KEY idx_tm_ticket (ticket_id)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
INSERT INTO number_sequences (name, next_value) VALUES ('ticket', 1000) ON DUPLICATE KEY UPDATE name = name;
|