Building an SEC data layer where the wrong sum is hard to write

The SEC publishes every number from every 10-K and 10-Q as a set of Parquet-friendly tables, and the obvious query against them is wrong. Summing Apple’s fiscal 2025 revenue tag inside its 10-K returns 9,523,848,000,000. The real figure is 416,161,000,000. Nothing errors. The table named num holds the consolidated total, the geographic breakdown, the product breakdown, prior-year comparatives, and several precision variants of the same fact, all in one pool. I wanted to ask questions about filings in English and get numbers back, so the first job was to make that sum hard to write at all. This post walks through how sec-data-intelligence does it.

The problem: one table, many grains

Each SEC Financial Statement and Notes archive has eight tables: sub, num, txt, pre, dim, tag, ren, and cal. sub is one row per filing. pre says where a tag appears on a rendered statement. dim decodes the segment hash on a fact. num is every numeric fact.

The traps in num are all things a language model would reasonably not know. dimn = 0 means a consolidated fact; anything else is a segment. iprx ranks precision variants of one value. qtrs is the duration, 0 for an instant, 4 for a year. ddate is normalized to month end, so Apple’s year ending 27 September is stored as 30 September. pre.negating flags values stored positive but shown negative. sub.fy is not reliable either; NVIDIA’s 10-K for the year ending January 2025 reports fy = 2024.

I wrote these down as they turned up in context/pitfalls.md, which ended with sixteen entries. Every one of them became either a rule in the data model or a constraint in a tool.

Architecture

The system is a single DuckDB file built from local Parquet, a Google ADK agent, and a FastAPI server. There is no warehouse and no cluster.

The build is a sequence of numbered SQL files in src/sec_intel/pipeline/sql/: 010_bronze.sql, 020_silver.sql, 025_reference.sql, 030_gold.sql, and 040_fts.sql.

• Bronze is views over the raw Parquet. Every row carries src_period, the archive it came from.
• Silver does the mechanical work. It parses YYYYMMDD integers into dates, keeps iprx = 0, and drops duplicates that appear in overlapping archives.
• Gold is the interface. gold.company, gold.filing, gold.fact, gold.fact_segment, gold.statement_line, and gold.note_text each have one grain.

The agent sits on top of eight functions in src/sec_intel/query/tools.py: find_company, list_filings, get_financial_statement, get_fact, get_segment_breakdown, search_notes, get_context, and run_sql. The first seven write their own SQL from checked-in files. Only run_sql accepts SQL from the model.

Decision: separate consolidated facts from segments

Most of the 23x error comes from mixing consolidated and segment rows. So gold.fact only contains consolidated rows, and gold.fact_segment only contains single-axis segment rows. There is no object that holds both, so there is nothing to sum across.

gold.fact also handles facts reported in more than one unit. It only picks a unit when the same fact genuinely appears twice; ordinary share counts and per-share values pass through untouched:

-- src/sec_intel/pipeline/sql/030_gold.sql
), resolved AS (
    SELECT *, row_number() OVER (
        PARTITION BY adsh, tag, version, ddate_date, qtrs, dimh, coreg
        ORDER BY (uom = preferred_uom) DESC, (uom = 'USD') DESC, uom
    ) AS unit_rank
    FROM consolidated
)
SELECT adsh, tag, version, ddate_date AS ddate, qtrs, uom, value, ...
FROM resolved
WHERE unit_count = 1 OR unit_rank = 1;

uom travels with every value. A golden test asserts that Apple’s diluted EPS comes back as (7.46, "USD"), because a column that mixes dollars, shares, and dollars-per-share cannot be scaled without looking at the unit first.

The tradeoff is coverage. fact_segment filters to dimn = 1. A fact broken down by two axes at once, such as region by product, is left out rather than split into two misleading single-axis rows. An empty result for a two-axis question is a coverage limit, not proof that the filer disclosed nothing.

Decision: rank filings by whether they have financials

Amendments break the rule “take the latest filing”. Many 10-K/A filings only add Part III executive compensation and carry no statements. The project’s measurements found that in 66.4% of amended 10-K groups, the latest /A had zero income-statement rows.

gold.filing checks whether a filing has any statement rows in pre, then ranks on that before date:

-- src/sec_intel/pipeline/sql/030_gold.sql
row_number() OVER (
    PARTITION BY cik, period_date, base_form
    ORDER BY has_financials DESC, filed_date DESC, adsh DESC
) AS financial_rank

The view exposes is_latest_with_financials and also is_orphan_amendment, for an /A whose original is not in the corpus. The flag is there so the agent can say so instead of silently trusting the amendment.

The same view drops fiscal year as a key. Every tool after list_filings takes an accession number (adsh), not a year. The model resolves a filing once and every later call is pinned to it.

Decision: typed tools first, SQL as an audited exception

Letting a model write SQL against these tables is where the wrong numbers came from, so the common path gives it no SQL to write. The tool signatures are narrow:

def list_filings(cik: int, form: str, limit: int) -> dict[str, Any]:
def get_fact(adsh: str, tag: str, ddate: str, qtrs: str) -> dict[str, Any]:
def search_notes(query: str, cik: int, adsh: str) -> dict[str, Any]:

Every parameter is required. The module docstring records why. ADK function declarations mishandle default values, so "" and 0 mean “unset” and the docstrings the model reads say so. Every response has the same shape, rows, row_count, sql, and params, and failures come back as an error field with empty rows. Tools report failures instead of raising them.

Some questions still need arbitrary SQL. Fourth-quarter revenue, for example, is the annual fact minus the nine-month fact from a different accession. That goes through run_sql, and that path is where most of the defensive code lives.

Containing run_sql

There are two layers, and the order matters.

The engine layer is in src/sec_intel/query/connection.py:

connection = duckdb.connect(str(path), read_only=True)
try:
    connection.execute("LOAD fts")
    connection.execute("SET enable_external_access=false")
    yield connection
finally:
    connection.close()

A read-only connection still lets a query call read_parquet or read_text on any path the process can see. enable_external_access=false blocks that, and DuckDB refuses to turn it back on for the rest of the session. The full-text extension has to be loaded first, because loading extensions is also blocked once the latch is set.

DuckDB 1.5.5 has no statement timeout setting, so the deadline is a threading.Timer that calls connection.interrupt after 30 seconds.

The second layer is src/sec_intel/query/guard.py, which parses the statement with sqlglot and allows exactly one SELECT against gold. Its docstring calls itself defense in depth, not the primary control. It exists to refuse things the engine would permit, like reading silver or returning a million rows. It adds LIMIT 500 if the query has none.

The parser had a surprise. In sqlglot’s DuckDB dialect, FROM '/tmp/x.parquet' parses as an ordinary table with an identifier name and no schema, the same shape as a reference to a CTE. So the guard collects CTE names explicitly and rejects every other unqualified table:

for table in statement.find_all(exp.Table):
    if not isinstance(table.this, exp.Identifier):
        raise SqlNotAllowedError(
            "Table functions are not permitted; query gold objects by name"
        )
    if table.db == ALLOWED_SCHEMA:
        continue
    if not table.db and table.name in cte_names:
        continue

BM25 search produces no table node to allowlist, so fts_gold_note_text.match_bm25 is allowed by name as a qualified function. Every other unknown function is refused.

Segment rollups need arithmetic

Keeping segments in their own view stops them leaking into totals. It does not make them add up. For Apple’s fiscal 2025 revenue, the golden tests pin the geographic axis at 416,161,000,000, which matches the total, and the product axis at 723,164,000,000, which does not. The product axis contains a Product member that is itself the sum of iPhone, Mac, iPad, and Wearables.

The SEC gives no marker for this. cal relates tags to tags, not segment members to each other. So get_segment_breakdown requires an explicit axis, returns the consolidated total beside the member sum, and looks for rollups by brute force:

for size in range(2, len(siblings) + 1):
    if any(
        abs(sum(member["value"] for member in subset) - target) <= tolerance
        for subset in combinations(siblings, size)
    ):
        rollups.append(candidate["member"])
        break

This is exponential in the number of members, so it stops at 20 members and reports that it skipped the check. The tolerance is 0.5% of the candidate’s value. The result is reported as likely_rollup_members, not as a hierarchy, because arithmetic can suggest a rollup but cannot prove one.

What broke

The model answered from memory. The unit tests pass with 212 tests at 100% branch coverage against a real DuckDB file. A live evaluation still found the agent answering Apple’s FY2019 revenue with a plausible number and an accession number. The corpus only covers 2025 archives. The tools had returned zero rows, and the model filled the gap from training.

The fix was not in the data layer. It was a grounding block at the top of src/sec_intel/prompt.py. Every figure and every accession number must appear verbatim in a tool result from this conversation, and zero rows means the corpus does not have the answer. The same commit changed get_segment_breakdown to return the valid axes and periods when nothing matches, instead of a sums_to_total verdict computed over an empty set.

The FTS index accepted a bad key. adsh looks like the natural key for note text, but it is not unique there. create_fts_index accepted the non-unique key without complaint, then every match_bm25 call failed at query time with “More than one row returned by a subquery used as an expression”. gold.note_text now gets a row_number() as doc_id, and a comment in 040_fts.sql records why. The full history also did not fit in local temporary storage, so the index covers 10-K and 10-Q filings only, and search_notes says so when it finds nothing rather than inviting the model to rephrase.

The container had no extension. LOAD fts never fetches over the network; only INSTALL does. A fresh container had never run INSTALL, and with external access latched off there was no way to fetch it later. Every query failed with “Extension fts not found”. The Dockerfile now runs INSTALL fts at build time as the same user the app runs as. The commit notes that docker run --network none still loads it.

What’s next

The limits are written into the tools and the prompt rather than hidden:

• The corpus is eleven monthly archives, 2025_01 to 2025_11, not all of EDGAR.
• Two-axis segment facts are excluded from gold.fact_segment.
• Fourth quarter is derived through run_sql, not stored.
• public_float_usd is the filer’s cover-page float, not a market cap.
• Cross-company screening and embeddings are out of scope for this version.

The two I would pick up first are multi-axis segments and cross-company screening. Both need new gold objects with their own grain, and both would get the same treatment. Decide what can be summed before a model is allowed to ask.