Structured Note Reference Data Dictionary

This document describes the structured note reference data schema extracted from SEC 424B2 filings. Column names, datatypes, nullability, and key flags reflect the current production schema.

Overview

Each structured note is identified by its instrument_id and has reference data spread across several tables, each capturing a different aspect of the note's terms:

• r_reference — Core identity, provenance, currency, and summary fields.
• r_deal_parties — Issuer, underwriter, selling agent, calculation agent, and guarantor.
• r_product_classification — Legal wrapper, payoff archetype, tax treatment, and asset linkage.
• r_product_mechanics — Settlement, FX handling, return type, dividend treatment, and basket method.
• r_call_leg — Call window, exercise style, payment type, premium, and notice period.
• r_observation_leg — Observation frequency, type, scope, window, and averaging method.
• r_observation_schedule — Per-date observation, payment, coupon barrier, and autocall trigger values.
• r_coupon_leg — Coupon structure, conditionality, memory, frequency, and terms.
• r_coupon_barrier — Barrier type plus optional reference-rate bounds.
• r_coupon_payout — Coupon rate, reference rate, spread, floor, and cap.
• r_note_underlying_link — Underlier identity, weight, credit-reference flag, and initial fixing fields.
• r_piecewise_payoff — Payoff-curve breakpoints mapping underlier performance to investor payout.
• r_fee_economics — Public offering price, estimated value, fees, and deal-size fields.

Data Pipeline

Reference data is populated via an automated pipeline that:

1. Retrieves un-analyzed 424B2 prospectus filings from the SEC EDGAR database.
2. Converts HTML filings to plain text and strips boilerplate sections.
3. Extracts structured fields from the prospectus for each reference table.
4. Loads extracted data into staging tables.
5. Runs post-load normalization to canonicalize enum values, fix variants, and enforce domain constraints.

Relationships

The r_reference table anchors the structured-note schema. Other r_* tables relate back to it through instrument_id, and r_reference itself links to security_master in the central security_master database.

The following tables are one-to-many relative to instrument_id:

• r_note_underlying_link — Underlier identity, weight, credit-reference flag, and initial fixing fields.
• r_observation_schedule — Per-date observation, payment, coupon barrier, and autocall trigger values.
• r_piecewise_payoff — Payoff-curve breakpoints mapping underlier performance to investor payout.

Table Definitions

r_reference

Core identity and provenance for each structured note. One row per instrument_id. This is the parent table; all other r_* tables reference it via foreign key.