# ============================================================ # Project: EDGAR Research Database # Description: SEC EDGAR Research Infrastructure # # Authors: # - Manel Kammoun — manel.kammoun@uqo.ca # - Djerrycharli Tandjambianda — djerrycharli.tandjambianda@uqo.ca # - Simon-Pierre Boucher — simon-pierre.boucher@uqo.ca # # Institution: # Université du Québec en Outaouais (UQO) # # License: # Research / Academic Use # ============================================================ from __future__ import annotations class DatabaseSchema: @staticmethod def _companies_ddl() -> str: return """ CREATE TABLE IF NOT EXISTS companies ( cik VARCHAR PRIMARY KEY, ticker VARCHAR, company_name VARCHAR, sector VARCHAR, sub_industry VARCHAR, sic_code VARCHAR, state VARCHAR, date_added DATE, created_at TIMESTAMP DEFAULT current_timestamp, updated_at TIMESTAMP DEFAULT current_timestamp ); """ @staticmethod def _filings_ddl() -> str: return """ CREATE TABLE IF NOT EXISTS filings ( accession_number VARCHAR PRIMARY KEY, cik VARCHAR REFERENCES companies(cik), ticker VARCHAR, company_name VARCHAR, form_type VARCHAR NOT NULL, filing_date DATE NOT NULL, report_date DATE, fiscal_year INTEGER, fiscal_quarter INTEGER, sec_url VARCHAR, local_path VARCHAR, primary_document VARCHAR, file_size_bytes BIGINT, download_status VARCHAR DEFAULT 'pending', parse_status VARCHAR DEFAULT 'pending', created_at TIMESTAMP DEFAULT current_timestamp, updated_at TIMESTAMP DEFAULT current_timestamp ); """ @staticmethod def _filing_sections_ddl() -> str: return """ CREATE TABLE IF NOT EXISTS filing_sections ( section_id VARCHAR PRIMARY KEY, accession_number VARCHAR REFERENCES filings(accession_number), section_name VARCHAR NOT NULL, item_number VARCHAR, section_order INTEGER, text TEXT, word_count INTEGER, char_count INTEGER, created_at TIMESTAMP DEFAULT current_timestamp ); """ @staticmethod def _filing_chunks_ddl() -> str: return """ CREATE TABLE IF NOT EXISTS filing_chunks ( chunk_id VARCHAR PRIMARY KEY, section_id VARCHAR REFERENCES filing_sections(section_id), accession_number VARCHAR REFERENCES filings(accession_number), chunk_text TEXT, chunk_order INTEGER, word_count INTEGER, char_count INTEGER, start_char INTEGER, end_char INTEGER, created_at TIMESTAMP DEFAULT current_timestamp ); """ @staticmethod def _embeddings_metadata_ddl() -> str: return """ CREATE TABLE IF NOT EXISTS embeddings_metadata ( embedding_id VARCHAR PRIMARY KEY, source_type VARCHAR NOT NULL, source_id VARCHAR NOT NULL, model_name VARCHAR NOT NULL, model_version VARCHAR, embedding_dimension INTEGER, created_at TIMESTAMP DEFAULT current_timestamp ); """ @staticmethod def _processing_log_ddl() -> str: return """ CREATE TABLE IF NOT EXISTS processing_log ( log_id VARCHAR PRIMARY KEY, operation VARCHAR NOT NULL, entity_type VARCHAR, entity_id VARCHAR, status VARCHAR NOT NULL, message TEXT, started_at TIMESTAMP, completed_at TIMESTAMP, metadata_json TEXT ); """ @staticmethod def get_upgrade_sql() -> list[str]: """Idempotent column additions for databases created before 2026-10-27.""" return [ "ALTER TABLE companies ADD COLUMN IF NOT EXISTS in_sp500 BOOLEAN DEFAULT FALSE", "ALTER TABLE companies ADD COLUMN IF NOT EXISTS in_russell3000 BOOLEAN DEFAULT FALSE", "ALTER TABLE companies ADD COLUMN IF NOT EXISTS sic_description VARCHAR", "ALTER TABLE companies ADD COLUMN IF NOT EXISTS state_of_incorporation VARCHAR", "ALTER TABLE companies ADD COLUMN IF NOT EXISTS exchange VARCHAR", "ALTER TABLE companies ADD COLUMN IF NOT EXISTS fiscal_year_end VARCHAR", "ALTER TABLE companies ADD COLUMN IF NOT EXISTS russell_weight_pct DOUBLE", "ALTER TABLE companies ADD COLUMN IF NOT EXISTS shard_node VARCHAR", ] @classmethod def get_create_tables_sql(cls) -> list[str]: return [ cls._companies_ddl(), cls._filings_ddl(), cls._filing_sections_ddl(), cls._filing_chunks_ddl(), cls._embeddings_metadata_ddl(), cls._processing_log_ddl(), ] @staticmethod def get_create_indexes_sql() -> list[str]: return [ "CREATE INDEX IF NOT EXISTS idx_filings_cik ON filings(cik);", "CREATE INDEX IF NOT EXISTS idx_filings_form_type ON filings(form_type);", "CREATE INDEX IF NOT EXISTS idx_filings_filing_date ON filings(filing_date);", "CREATE INDEX IF NOT EXISTS idx_filings_fiscal_year ON filings(fiscal_year);", "CREATE INDEX IF NOT EXISTS idx_filing_sections_accession ON filing_sections(accession_number);", "CREATE INDEX IF NOT EXISTS idx_filing_chunks_accession ON filing_chunks(accession_number);", "CREATE INDEX IF NOT EXISTS idx_filing_chunks_section ON filing_chunks(section_id);", "CREATE INDEX IF NOT EXISTS idx_embeddings_source_id ON embeddings_metadata(source_id);", "CREATE INDEX IF NOT EXISTS idx_embeddings_model_name ON embeddings_metadata(model_name);", "CREATE INDEX IF NOT EXISTS idx_processing_log_entity ON processing_log(entity_id);", ] @staticmethod def get_create_views_sql() -> list[str]: filing_summary = """ CREATE OR REPLACE VIEW filing_summary AS SELECT f.accession_number, f.cik, c.ticker, c.company_name, c.sector, c.sub_industry, c.in_sp500, c.in_russell3000, f.form_type, f.filing_date, f.report_date, f.fiscal_year, f.fiscal_quarter, f.sec_url, f.file_size_bytes, f.download_status, f.parse_status FROM filings f LEFT JOIN companies c ON f.cik = c.cik; """ section_detail = """ CREATE OR REPLACE VIEW section_detail AS SELECT s.section_id, s.accession_number, f.cik, f.ticker, f.company_name, f.form_type, f.filing_date, f.fiscal_year, s.section_name, s.item_number, s.section_order, s.word_count, s.char_count FROM filing_sections s JOIN filings f ON s.accession_number = f.accession_number; """ chunk_detail = """ CREATE OR REPLACE VIEW chunk_detail AS SELECT ch.chunk_id, ch.section_id, ch.accession_number, f.cik, f.ticker, f.company_name, f.form_type, f.filing_date, f.fiscal_year, s.section_name, s.item_number, ch.chunk_order, ch.word_count, ch.char_count, ch.start_char, ch.end_char FROM filing_chunks ch JOIN filing_sections s ON ch.section_id = s.section_id JOIN filings f ON ch.accession_number = f.accession_number; """ return [filing_summary, section_detail, chunk_detail]