@prakhar-gitlab / ai-investment-platform
Code Commits Issues Pulls Sessions CI/CD
main
main
sql 124 lines 4.57 KB
Raw
1 CREATE TABLE research_sources (
2 source_id VARCHAR(120) PRIMARY KEY,
3 source_type VARCHAR(60) NOT NULL,
4 source_name VARCHAR(240) NOT NULL,
5 reliability_level VARCHAR(20) NOT NULL,
6 fetch_strategy VARCHAR(40) NOT NULL,
7 automatic_access VARCHAR(40) NOT NULL,
8 javascript_required BOOLEAN NOT NULL DEFAULT FALSE,
9 requests_per_minute INTEGER NOT NULL,
10 min_delay_seconds DECIMAL(12, 3) NOT NULL
11 );
12
13 CREATE TABLE company_research_profiles (
14 company_id UUID PRIMARY KEY,
15 instrument_id UUID NOT NULL UNIQUE,
16 company_name VARCHAR(240) NOT NULL,
17 isin VARCHAR(20),
18 ticker VARCHAR(40) NOT NULL,
19 exchange VARCHAR(40) NOT NULL,
20 mic VARCHAR(12),
21 country VARCHAR(2) NOT NULL,
22 currency VARCHAR(3) NOT NULL,
23 official_website VARCHAR(600),
24 investor_relations_url VARCHAR(600),
25 press_release_url VARCHAR(600),
26 annual_reports_url VARCHAR(600),
27 exchange_announcements_url VARCHAR(600),
28 regulatory_filings_url VARCHAR(600)
29 );
30
31 CREATE TABLE research_documents (
32 document_id UUID PRIMARY KEY,
33 canonical_url VARCHAR(1000) NOT NULL,
34 original_url VARCHAR(1000) NOT NULL,
35 title VARCHAR(500),
36 source_type VARCHAR(60) NOT NULL,
37 source_name VARCHAR(240) NOT NULL,
38 publisher VARCHAR(240),
39 published_at TIMESTAMP,
40 retrieved_at TIMESTAMP NOT NULL,
41 language VARCHAR(16),
42 content_type VARCHAR(120) NOT NULL,
43 document_type VARCHAR(40) NOT NULL,
44 content_hash CHAR(64) NOT NULL,
45 instrument_id UUID,
46 company_id UUID,
47 country VARCHAR(2),
48 exchange VARCHAR(40),
49 status VARCHAR(40) NOT NULL,
50 reliability_level VARCHAR(20) NOT NULL,
51 entity_resolution_confidence DECIMAL(5, 4) NOT NULL
52 );
53
54 CREATE TABLE research_document_sources (
55 document_source_id UUID PRIMARY KEY,
56 document_id UUID NOT NULL,
57 source_id VARCHAR(120),
58 source_url VARCHAR(1000) NOT NULL,
59 discovered_at TIMESTAMP NOT NULL,
60 CONSTRAINT fk_research_document_sources_document FOREIGN KEY (document_id) REFERENCES research_documents (document_id)
61 );
62
63 CREATE TABLE research_events (
64 event_id UUID PRIMARY KEY,
65 instrument_id UUID NOT NULL,
66 company_id UUID NOT NULL,
67 event_type VARCHAR(80) NOT NULL,
68 event_date TIMESTAMP,
69 detected_at TIMESTAMP NOT NULL,
70 title VARCHAR(300) NOT NULL,
71 summary VARCHAR(1200) NOT NULL,
72 source_document_id UUID NOT NULL,
73 source_url VARCHAR(1000) NOT NULL,
74 source_type VARCHAR(60) NOT NULL,
75 reliability VARCHAR(20) NOT NULL,
76 confidence DECIMAL(5, 4) NOT NULL,
77 impact VARCHAR(40) NOT NULL,
78 time_horizon VARCHAR(40) NOT NULL,
79 currency VARCHAR(3),
80 monetary_value DECIMAL(28, 4),
81 monetary_original VARCHAR(80),
82 percentage_value DECIMAL(12, 4),
83 percentage_original VARCHAR(80),
84 customer VARCHAR(240),
85 counterparty VARCHAR(240),
86 location VARCHAR(240),
87 capacity_value DECIMAL(28, 4),
88 capacity_unit VARCHAR(40),
89 status VARCHAR(40) NOT NULL,
90 CONSTRAINT fk_research_events_document FOREIGN KEY (source_document_id) REFERENCES research_documents (document_id)
91 );
92
93 CREATE TABLE research_event_evidence (
94 evidence_id UUID PRIMARY KEY,
95 event_id UUID NOT NULL,
96 source_document_id UUID NOT NULL,
97 evidence_reference VARCHAR(600) NOT NULL,
98 CONSTRAINT fk_research_event_evidence_event FOREIGN KEY (event_id) REFERENCES research_events (event_id)
99 );
100
101 CREATE TABLE research_fetch_history (
102 fetch_id UUID PRIMARY KEY,
103 source_id VARCHAR(120),
104 url VARCHAR(1000) NOT NULL,
105 attempted_at TIMESTAMP NOT NULL,
106 status VARCHAR(40) NOT NULL,
107 http_status INTEGER,
108 failure_code VARCHAR(120),
109 bytes_read BIGINT,
110 latency_ms BIGINT
111 );
112
113 CREATE INDEX idx_research_documents_instrument_id ON research_documents (instrument_id);
114 CREATE INDEX idx_research_documents_company_id ON research_documents (company_id);
115 CREATE INDEX idx_research_documents_published_at ON research_documents (published_at);
116 CREATE UNIQUE INDEX idx_research_documents_canonical_url ON research_documents (canonical_url);
117 CREATE INDEX idx_research_documents_content_hash ON research_documents (content_hash);
118 CREATE INDEX idx_research_documents_source_type ON research_documents (source_type);
119 CREATE INDEX idx_research_documents_status ON research_documents (status);
120 CREATE INDEX idx_research_events_instrument_id ON research_events (instrument_id);
121 CREATE INDEX idx_research_events_company_id ON research_events (company_id);
122 CREATE INDEX idx_research_events_event_type ON research_events (event_type);
123 CREATE INDEX idx_research_events_event_date ON research_events (event_date);
124 CREATE INDEX idx_research_events_impact ON research_events (impact);