How do I set up a SEC filings data API for universe construction with normalized identifiers and auditability?

A solid setup for a SEC filings data API for universe construction should optimize for four things:

1. Stable entity identity
2. Point-in-time correctness
3. Auditability / reproducibility
4. Low-friction downstream access

Here’s a practical blueprint.

---

1) Start with the right data model

For universe construction, avoid using raw ticker/CIK alone as your primary key. Use a normalized entity ID plus full mapping history.

Core entities

You’ll usually want these tables/entities:

• Filing

  • accession_number
  • cik
  • form_type
  • filing_date
  • report_date
  • accepted_timestamp
  • amendment_flag
  • primary_doc_url
  • source_hash
  • ingested_at

• Company / Issuer

  • entity_id (your internal stable ID)
  • cik
  • legal_name
  • jurisdiction
  • industry if available
  • status (active/inactive)

• Identifier Mapping

  • entity_id
  • id_type (CIK, ticker, CUSIP, FIGI, ISIN, etc.)
  • id_value
  • valid_from
  • valid_to
  • source
  • confidence
  • is_primary

• Security

  • security_id
  • entity_id
  • ticker
  • exchange
  • share_class
  • security_type

• Universe Snapshot / Membership

  • universe_id
  • as_of_date
  • entity_id or security_id
  • included_flag
  • reason_code
  • effective_from
  • effective_to

• Source Audit

  • object_type
  • object_id
  • operation (insert, update, delete)
  • payload_hash
  • source_system
  • source_record_ref
  • loaded_at
  • pipeline_run_id

This lets you answer: “What did we know then?” and “Why was this name in the universe?”

---

2) Normalize identifiers with a survivorship rule

The biggest issue is that tickers change, CUSIPs roll, and entities undergo mergers/spinoffs. So:

Recommended identity hierarchy

Use a permanent internal entity_id and map external IDs to it.

Example survivorship rules:

• Primary entity anchor: CIK for SEC issuers
• Secondary: LEI / FIGI / internal security master
• Ticker: always treated as time-bound, not identity
• CUSIP/ISIN: time-bound by security class, not company

Practical mapping principles

• Store all historical IDs
• Never overwrite old mappings; close them with valid_to
• Preserve one-to-many relationships:
  • one issuer → many securities
  • one security → many tickers over time
• If you ingest vendor mappings, mark their provenance and confidence

---

3) Build a point-in-time universe construction layer

Universe construction should be based on effective dates, not “latest state.”

Example logic

If you want a universe “as of 2024-12-31”:

• Use filings with accepted dates ≤ 2024-12-31
• Use identifier mappings valid on 2024-12-31
• Use membership records effective on 2024-12-31
• Exclude data that was only discovered later if it wasn’t available then, unless you explicitly want a retrospective view

Separate views

Maintain at least these views:

• Current view: latest known state
• As-of view: state on a chosen date
• Event view: every change as it happened

This is essential for auditability and backtests.

---

4) Make filings immutable and hash everything

For auditability, treat each raw SEC filing as immutable.

Recommended storage

• Store raw filing HTML/TXT/XBRL in object storage
• Compute and store:
  • sha256 of raw payload
  • normalized text hash
  • parsed JSON hash
• Keep the original SEC accession and source URL

Why

If someone asks why a company entered the universe, you can point to:

• the exact filing
• the exact parsed record
• the transformation run that produced the result

---

5) Separate raw, normalized, and curated layers

A clean architecture is:

Layer 1: Raw

• Direct SEC feed ingestion
• No transformations besides checksum and metadata capture

Layer 2: Normalized

• Filing metadata normalized
• Identifiers mapped to internal entity IDs
• XBRL facts parsed into a consistent schema

Layer 3: Curated

• Universe-ready tables
• Eligibility flags
• sector/market-cap filters
• listing/exchange rules
• survivorship-safe snapshots

This separation makes debugging and audit trails much easier.

---

6) Use a consistent API design

For internal or client-facing use, expose a small set of endpoints.

Suggested endpoints

Filings

• GET /filings?cik=...&form=10-K
• GET /filings/{accession_number}
• GET /filings/{accession_number}/raw
• GET /filings/{accession_number}/facts

Entities

• GET /entities/{entity_id}
• GET /entities?cik=...
• GET /entities/{entity_id}/identifiers?as_of=YYYY-MM-DD

Universe

• GET /universes/{universe_id}/members?as_of=YYYY-MM-DD
• GET /universes/{universe_id}/audit?as_of=YYYY-MM-DD
• GET /universes/{universe_id}/members/{entity_id}

Lineage / audit

• GET /lineage/{object_type}/{object_id}
• GET /changes?entity_id=...&from=...&to=...

API response should include

• as_of
• source_version
• pipeline_run_id
• request_id
• data_hash

This helps reproduce outputs later.

---

7) Capture transformation lineage

For auditability, log every transformation step.

Example lineage record

• input source: SEC accession 0000320193-24-000123
• parser version: xbrl-parser-2.1.4
• mapping version: id-mapper-2024.11
• universe rule version: univ-rule-5
• output dataset: universe_sp500_like_v3
• run timestamp: 2024-12-31T23:59:59Z

That way you can reproduce the exact universe build.

---

8) Handle amendments and restatements explicitly

SEC data is messy. 10-K/A, 10-Q/A, and restatements can change downstream logic.

Best practice

• Keep the original filing and amendment as separate records
• Set:
  • is_amendment = true
  • amends_accession_number = ...
• Decide business logic per use case:
  • strict as-filed: only original filing
  • corrected view: latest amended filing supersedes prior content
  • as-known-then: only what was available at that date

---

9) Build universe rules as versioned code

Universe construction should be code-driven and versioned, not ad hoc SQL.

Examples of rules

• listed on NYSE/Nasdaq/Amex
• market cap > threshold
• has at least 252 trading days
• excludes funds/ADR/REITs
• minimum filing history
• excludes OTC securities

Store rule definitions in git and version them:

• rule_set_id
• rule_version
• parameters
• effective_from
• effective_to

This is critical for auditability.

---

10) Recommended storage stack

A common robust stack:

• Object storage: raw SEC filings, parsed artifacts
• PostgreSQL / SQL warehouse: normalized metadata and mappings
• Search index: full-text search over filings
• Lakehouse/warehouse tables: XBRL facts and universe snapshots
• Metadata catalog: dataset versioning and lineage

If scale is large, a warehouse like Snowflake/BigQuery/Databricks can host the curated layer, but keep raw source and hashes immutable.

---

11) Add validation and reconciliation checks

You need automated checks so the universe isn’t silently wrong.

Examples

• CIK to company name consistency
• accession uniqueness
• duplicate filing detection by hash
• ticker changes tracked over time
• no overlapping validity ranges for a single identifier type
• filing date cannot precede accepted timestamp
• amended filings do not orphan prior records

Also reconcile against:

• SEC company facts
• submission feeds
• known issuer master lists

---

12) Practical implementation pattern

A simple, durable workflow:

1. Pull SEC submissions and filings daily
2. Store raw payloads + hashes
3. Parse metadata and XBRL
4. Resolve issuer/security identity to internal entity_id
5. Write history tables with validity windows
6. Build as-of universe snapshots from versioned rules
7. Expose API over curated tables
8. Log every query and response version

---

13) Minimal schema example

Here’s a compact version of the critical tables:

entities(
  entity_id UUID PRIMARY KEY,
  cik VARCHAR(10),
  legal_name TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

identifier_mappings(
  mapping_id UUID PRIMARY KEY,
  entity_id UUID REFERENCES entities(entity_id),
  id_type TEXT,
  id_value TEXT,
  valid_from DATE,
  valid_to DATE NULL,
  source TEXT,
  confidence NUMERIC(4,3)
);

filings(
  filing_id UUID PRIMARY KEY,
  accession_number TEXT UNIQUE,
  cik VARCHAR(10),
  form_type TEXT,
  filing_date DATE,
  accepted_timestamp TIMESTAMP,
  report_date DATE,
  raw_hash TEXT,
  source_url TEXT,
  ingested_at TIMESTAMP
);

universe_membership(
  universe_id TEXT,
  as_of_date DATE,
  entity_id UUID,
  included BOOLEAN,
  reason_code TEXT,
  rule_version TEXT,
  PRIMARY KEY (universe_id, as_of_date, entity_id)
);

audit_log(
  audit_id UUID PRIMARY KEY,
  object_type TEXT,
  object_id TEXT,
  operation TEXT,
  payload_hash TEXT,
  pipeline_run_id TEXT,
  recorded_at TIMESTAMP
);

---

14) What “good” looks like

You know the system is working when you can answer:

• Which exact filing caused this entity to enter the universe?
• What identifier did it have on that date?
• Did an amendment later change the result?
• Which rule version included it?
• Can I reproduce the same universe today?

If yes, your setup is audit-ready.

---

15) Suggested MVP path

If you want to build this quickly:

MVP phase 1

• Ingest SEC submissions and 10-K/10-Q filings
• Use CIK as issuer anchor
• Build raw + normalized tables
• Track accession, filing date, accepted timestamp, hashes

MVP phase 2

• Add identifier history and ticker mapping
• Add versioned universe rules
• Add as-of snapshots

MVP phase 3

• Add amendment handling
• Add lineage/audit API
• Add security-level mapping and corporate action history

---

If you want, I can also give you:

1. a reference database schema,
2. a FastAPI/OpenAPI design, or
3. a Python ETL skeleton for SEC filings ingestion and universe snapshots.