Repository navigation
Expand file tree
/
Copy pathschema.sql
More file actions
42 lines (39 loc) · 1.44 KB
/
Copy pathschema.sql
File metadata and controls
42 lines (39 loc) · 1.44 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE IF NOT EXISTS users (
id VARCHAR(36) PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255) UNIQUE NOT NULL,
image VARCHAR(512),
github_id VARCHAR(50) UNIQUE NOT NULL,
profile_public TINYINT(1) NOT NULL DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS incidents (
id VARCHAR(36) PRIMARY KEY,
title VARCHAR(255) NOT NULL,
description TEXT,
severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
status ENUM('investigating', 'identified', 'resolving', 'resolved') NOT NULL DEFAULT 'investigating',
created_by VARCHAR(36) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
resolved_at TIMESTAMP NULL,
FOREIGN KEY (created_by) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS entries (
id VARCHAR(36) PRIMARY KEY,
incident_id VARCHAR(36) NOT NULL,
user_id VARCHAR(36) NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS claims (
id VARCHAR(36) PRIMARY KEY,
incident_id VARCHAR(36) NOT NULL,
user_id VARCHAR(36) NOT NULL,
task VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_claim (incident_id, user_id),
FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id)
);