Database Schema

┌─────────────────────────────────────────────────────────────┐
│                     PostgreSQL + pgvector                   │
│  • Embeddings (384-dim vectors)                             │
│  • Metadata (ticker, year, quarter, section)                │
│  • Chunk text (truncated for search context)                │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│                       Railway S3                            │
│  • Full transcript text                                     │
│  • Complete SEC filing documents                            │
└─────────────────────────────────────────────────────────────┘

CREATE TABLE transcript_chunks (
    id SERIAL PRIMARY KEY,
    chunk_text TEXT NOT NULL,              -- Transcript chunk (1000 chars, 200 overlap)
    embedding VECTOR(384),                  -- all-MiniLM-L6-v2 embedding
    ticker VARCHAR(10) NOT NULL,            -- Company ticker (e.g., "AAPL")
    year INTEGER NOT NULL,                  -- Year (e.g., 2024)
    quarter INTEGER NOT NULL,               -- Quarter (1-4)
    chunk_index INTEGER,                    -- Position in transcript
    speaker VARCHAR(255),                   -- Speaker name (if available)
    metadata JSONB,                         -- Additional metadata
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_transcript_ticker ON transcript_chunks(ticker);
CREATE INDEX idx_transcript_year_quarter ON transcript_chunks(year, quarter);
CREATE INDEX idx_transcript_embedding ON transcript_chunks USING ivfflat (embedding vector_cosine_ops);

# From search_engine.py
def search_similar_chunks(query, top_k, quarter):
    """
    Combines:
    - Vector search: 70% weight (semantic similarity via pgvector)
    - Keyword search: 30% weight (TF-IDF for exact terms)
    """

-- Vector similarity search with quarter filtering
SELECT chunk_text, ticker, year, quarter
FROM transcript_chunks
WHERE ticker = %s 
  AND year = %s 
  AND quarter = %s
ORDER BY embedding <=> %s  -- Cosine similarity
LIMIT 15;

CREATE TABLE ten_k_chunks (
    id SERIAL PRIMARY KEY,
    chunk_text TEXT NOT NULL,              -- Filing chunk (variable size)
    embedding VECTOR(384),                  -- all-MiniLM-L6-v2 embedding
    ticker VARCHAR(10) NOT NULL,            -- Company ticker
    fiscal_year INTEGER NOT NULL,           -- Fiscal year of filing
    sec_section VARCHAR(50),                -- Section identifier (item_1, item_7, etc.)
    sec_section_title VARCHAR(255),         -- Human-readable section name
    is_financial_statement BOOLEAN,         -- True if chunk is from financial statements
    chunk_index INTEGER,                    -- Position within section
    metadata JSONB,                         -- Additional context
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_10k_ticker_year ON ten_k_chunks(ticker, fiscal_year);
CREATE INDEX idx_10k_section ON ten_k_chunks(sec_section);
CREATE INDEX idx_10k_embedding ON ten_k_chunks USING ivfflat (embedding vector_cosine_ops);

CREATE TABLE ten_k_tables (
    id SERIAL PRIMARY KEY,
    ticker VARCHAR(10) NOT NULL,
    fiscal_year INTEGER NOT NULL,
    table_index INTEGER,                   -- Position in filing
    content JSONB NOT NULL,                -- Structured table data
    statement_type VARCHAR(100),           -- income_statement, balance_sheet, cash_flow
    is_financial_statement BOOLEAN,        -- True for core financial statements
    sec_section VARCHAR(50),               -- Section where table appears
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_tables_ticker_year ON ten_k_tables(ticker, fiscal_year);
CREATE INDEX idx_tables_statement_type ON ten_k_tables(statement_type);
CREATE INDEX idx_tables_content ON ten_k_tables USING gin (content);

# From sec_filings_service_smart_parallel.py
def select_relevant_tables(question, ticker, year):
    """
    1. Retrieve all tables for company/year
    2. LLM analyzes question and table metadata
    3. Returns only relevant tables (e.g., "cost of goods sold" → income_statement)
    4. Reduces context size and improves accuracy
    """

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create IVFFlat index for fast approximate nearest neighbor search
-- Lists parameter: sqrt(total_rows) is a good heuristic
CREATE INDEX idx_transcript_embedding 
ON transcript_chunks 
USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 100);

CREATE INDEX idx_10k_embedding 
ON ten_k_chunks 
USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 100);

# Download earnings transcripts
python agent/rag/data_ingestion/download_transcripts.py

# Ingest transcripts into database
python agent/rag/data_ingestion/ingest_with_structure.py \
  --ticker AAPL \
  --year-start 2020 \
  --year-end 2025

# Ingest SEC 10-K filings
python agent/rag/data_ingestion/ingest_10k.py \
  --ticker AAPL \
  --year 2024

# From search_engine.py
results = search_similar_chunks(
    query="iPhone revenue growth",
    top_k=15,
    ticker="AAPL",
    year=2024,
    quarter=4
)

# Returns:
# [
#   {
#     "chunk_text": "iPhone revenue grew 8% year-over-year...",
#     "ticker": "AAPL",
#     "year": 2024,
#     "quarter": 4,
#     "similarity_score": 0.89
#   },
#   ...
# ]

# From sec_filings_service_smart_parallel.py
results = search_10k_chunks(
    query="executive compensation stock awards",
    ticker="AAPL",
    fiscal_year=2023,
    sections=["item_11"]  # Executive Compensation section only
)

-- Get balance sheet tables for Apple 2023
SELECT content
FROM ten_k_tables
WHERE ticker = 'AAPL'
  AND fiscal_year = 2023
  AND statement_type = 'balance_sheet'
  AND is_financial_statement = true;