spb/uqo-research-db
Public
1# ============================================================2# Project: EDGAR Research Database3# Description: SEC EDGAR Research Infrastructure4#5# Authors:6# - Manel Kammoun — manel.kammoun@uqo.ca7# - Djerrycharli Tandjambianda — djerrycharli.tandjambianda@uqo.ca8# - Simon-Pierre Boucher — simon-pierre.boucher@uqo.ca9#10# Institution:11# Université du Québec en Outaouais (UQO)12#13# License:14# Research / Academic Use15# ============================================================1617from __future__ import annotations181920class DatabaseSchema:2122 @staticmethod23 def _companies_ddl() -> str:24 return """25 CREATE TABLE IF NOT EXISTS companies (26 cik VARCHAR PRIMARY KEY,27 ticker VARCHAR,28 company_name VARCHAR,29 sector VARCHAR,30 sub_industry VARCHAR,31 sic_code VARCHAR,32 state VARCHAR,33 date_added DATE,34 created_at TIMESTAMP DEFAULT current_timestamp,35 updated_at TIMESTAMP DEFAULT current_timestamp36 );37 """3839 @staticmethod40 def _filings_ddl() -> str:41 return """42 CREATE TABLE IF NOT EXISTS filings (43 accession_number VARCHAR PRIMARY KEY,44 cik VARCHAR REFERENCES companies(cik),45 ticker VARCHAR,46 company_name VARCHAR,47 form_type VARCHAR NOT NULL,48 filing_date DATE NOT NULL,49 report_date DATE,50 fiscal_year INTEGER,51 fiscal_quarter INTEGER,52 sec_url VARCHAR,53 local_path VARCHAR,54 primary_document VARCHAR,55 file_size_bytes BIGINT,56 download_status VARCHAR DEFAULT 'pending',57 parse_status VARCHAR DEFAULT 'pending',58 created_at TIMESTAMP DEFAULT current_timestamp,59 updated_at TIMESTAMP DEFAULT current_timestamp60 );61 """6263 @staticmethod64 def _filing_sections_ddl() -> str:65 return """66 CREATE TABLE IF NOT EXISTS filing_sections (67 section_id VARCHAR PRIMARY KEY,68 accession_number VARCHAR REFERENCES filings(accession_number),69 section_name VARCHAR NOT NULL,70 item_number VARCHAR,71 section_order INTEGER,72 text TEXT,73 word_count INTEGER,74 char_count INTEGER,75 created_at TIMESTAMP DEFAULT current_timestamp76 );77 """7879 @staticmethod80 def _filing_chunks_ddl() -> str:81 return """82 CREATE TABLE IF NOT EXISTS filing_chunks (83 chunk_id VARCHAR PRIMARY KEY,84 section_id VARCHAR REFERENCES filing_sections(section_id),85 accession_number VARCHAR REFERENCES filings(accession_number),86 chunk_text TEXT,87 chunk_order INTEGER,88 word_count INTEGER,89 char_count INTEGER,90 start_char INTEGER,91 end_char INTEGER,92 created_at TIMESTAMP DEFAULT current_timestamp93 );94 """9596 @staticmethod97 def _embeddings_metadata_ddl() -> str:98 return """99 CREATE TABLE IF NOT EXISTS embeddings_metadata (100 embedding_id VARCHAR PRIMARY KEY,101 source_type VARCHAR NOT NULL,102 source_id VARCHAR NOT NULL,103 model_name VARCHAR NOT NULL,104 model_version VARCHAR,105 embedding_dimension INTEGER,106 created_at TIMESTAMP DEFAULT current_timestamp107 );108 """109110 @staticmethod111 def _processing_log_ddl() -> str:112 return """113 CREATE TABLE IF NOT EXISTS processing_log (114 log_id VARCHAR PRIMARY KEY,115 operation VARCHAR NOT NULL,116 entity_type VARCHAR,117 entity_id VARCHAR,118 status VARCHAR NOT NULL,119 message TEXT,120 started_at TIMESTAMP,121 completed_at TIMESTAMP,122 metadata_json TEXT123 );124 """125126 @staticmethod127 def get_upgrade_sql() -> list[str]:128 """Idempotent column additions for databases created before 2026-10-27."""129 return [130 "ALTER TABLE companies ADD COLUMN IF NOT EXISTS in_sp500 BOOLEAN DEFAULT FALSE",131 "ALTER TABLE companies ADD COLUMN IF NOT EXISTS in_russell3000 BOOLEAN DEFAULT FALSE",132 "ALTER TABLE companies ADD COLUMN IF NOT EXISTS sic_description VARCHAR",133 "ALTER TABLE companies ADD COLUMN IF NOT EXISTS state_of_incorporation VARCHAR",134 "ALTER TABLE companies ADD COLUMN IF NOT EXISTS exchange VARCHAR",135 "ALTER TABLE companies ADD COLUMN IF NOT EXISTS fiscal_year_end VARCHAR",136 "ALTER TABLE companies ADD COLUMN IF NOT EXISTS russell_weight_pct DOUBLE",137 "ALTER TABLE companies ADD COLUMN IF NOT EXISTS shard_node VARCHAR",138 ]139140 @classmethod141 def get_create_tables_sql(cls) -> list[str]:142 return [143 cls._companies_ddl(),144 cls._filings_ddl(),145 cls._filing_sections_ddl(),146 cls._filing_chunks_ddl(),147 cls._embeddings_metadata_ddl(),148 cls._processing_log_ddl(),149 ]150151 @staticmethod152 def get_create_indexes_sql() -> list[str]:153 return [154 "CREATE INDEX IF NOT EXISTS idx_filings_cik ON filings(cik);",155 "CREATE INDEX IF NOT EXISTS idx_filings_form_type ON filings(form_type);",156 "CREATE INDEX IF NOT EXISTS idx_filings_filing_date ON filings(filing_date);",157 "CREATE INDEX IF NOT EXISTS idx_filings_fiscal_year ON filings(fiscal_year);",158 "CREATE INDEX IF NOT EXISTS idx_filing_sections_accession ON filing_sections(accession_number);",159 "CREATE INDEX IF NOT EXISTS idx_filing_chunks_accession ON filing_chunks(accession_number);",160 "CREATE INDEX IF NOT EXISTS idx_filing_chunks_section ON filing_chunks(section_id);",161 "CREATE INDEX IF NOT EXISTS idx_embeddings_source_id ON embeddings_metadata(source_id);",162 "CREATE INDEX IF NOT EXISTS idx_embeddings_model_name ON embeddings_metadata(model_name);",163 "CREATE INDEX IF NOT EXISTS idx_processing_log_entity ON processing_log(entity_id);",164 ]165166 @staticmethod167 def get_create_views_sql() -> list[str]:168 filing_summary = """169 CREATE OR REPLACE VIEW filing_summary AS170 SELECT171 f.accession_number,172 f.cik,173 c.ticker,174 c.company_name,175 c.sector,176 c.sub_industry,177 c.in_sp500,178 c.in_russell3000,179 f.form_type,180 f.filing_date,181 f.report_date,182 f.fiscal_year,183 f.fiscal_quarter,184 f.sec_url,185 f.file_size_bytes,186 f.download_status,187 f.parse_status188 FROM filings f189 LEFT JOIN companies c ON f.cik = c.cik;190 """191192 section_detail = """193 CREATE OR REPLACE VIEW section_detail AS194 SELECT195 s.section_id,196 s.accession_number,197 f.cik,198 f.ticker,199 f.company_name,200 f.form_type,201 f.filing_date,202 f.fiscal_year,203 s.section_name,204 s.item_number,205 s.section_order,206 s.word_count,207 s.char_count208 FROM filing_sections s209 JOIN filings f ON s.accession_number = f.accession_number;210 """211212 chunk_detail = """213 CREATE OR REPLACE VIEW chunk_detail AS214 SELECT215 ch.chunk_id,216 ch.section_id,217 ch.accession_number,218 f.cik,219 f.ticker,220 f.company_name,221 f.form_type,222 f.filing_date,223 f.fiscal_year,224 s.section_name,225 s.item_number,226 ch.chunk_order,227 ch.word_count,228 ch.char_count,229 ch.start_char,230 ch.end_char231 FROM filing_chunks ch232 JOIN filing_sections s ON ch.section_id = s.section_id233 JOIN filings f ON ch.accession_number = f.accession_number;234 """235236 return [filing_summary, section_detail, chunk_detail]237