darkweb-forensic-tool/CreateForensicToolTable.sql at main · mmujtaba0085/darkweb-forensic-tool · GitHub

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
CREATE DATABASE forensic_tool_db;
USE forensic_tool_db;

CREATE TABLE case_metadata (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id VARCHAR(100),
    investigator_name VARCHAR(100),
    notes TEXT,
    score INT,
    severity VARCHAR(50),
    file_hash VARCHAR(255),
    llm_summary TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE extracted_emails (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    email VARCHAR(255),
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);

CREATE TABLE extracted_payment_addresses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    address VARCHAR(255),
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);

CREATE TABLE risk_keywords (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    keyword VARCHAR(255),
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);

CREATE TABLE pgp_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    pgp_type VARCHAR(100),
    pgp_content TEXT,
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);

CREATE TABLE financial_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    data_type VARCHAR(100),
    content TEXT,
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);

CREATE TABLE shipping_addresses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    address_type VARCHAR(100),
    content TEXT,
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);

CREATE TABLE usernames (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    username_type VARCHAR(100),
    content TEXT,
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);

CREATE TABLE document_advertisements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    ad_type VARCHAR(255),
    content TEXT,
    suspicious_level VARCHAR(100),
    method VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);

CREATE TABLE suspicious_urls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    url TEXT,
    domain VARCHAR(255),
    suspicious_reason VARCHAR(255),
    risk_level VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);

CREATE TABLE actual_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    link_type VARCHAR(100),
    url TEXT,
    link_text TEXT,
    extraction_method VARCHAR(50),
    suspicious_level VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (case_id) REFERENCES case_metadata(id)
);