# FDA Exclusivity Export — Agent/LLM Reference (Evaluation Sample)

**Export:** 2026-04 | **Generated:** 2026-05-19

> **Evaluation sample.** This bundle contains ~200 NDAs and ~44 BLAs. The schema is identical to the full dataset. `first_generic_entry` returns 0 rows in this sample — ANDA data is not included. Use this document to understand the full schema; row counts will differ from production.

This document describes the complete schema of `fda_exclusivity.db` for AI and programmatic consumers. Read this before querying.

---

## Bundle Overview

```
fda_exclusivity.db    -- primary deliverable (SQLite 3)
csv/                  -- one CSV per table/view
EXAMPLES.sql          -- ready-to-run queries
```

The database contains two namespaced sources (ob_ = Orange Book NDA/ANDA, pb_ = Purple Book BLA) plus shared tables and cross-source derived views.

---

## Orange Book Tables (ob_*)

### ob_applications

FDA application registry for small-molecule drugs (NDA, ANDA) and some biologics (BLA via OB).

| Column | Type | Notes |
|--------|------|-------|
| appl_no | TEXT PK | FDA application number, e.g. "NDA022406" |
| appl_type | TEXT | NDA / ANDA / BLA (normalized from raw FDA single-char codes) |
| sponsor_name | TEXT | FDA-reported applicant name |
| rxdl_company | TEXT | RxDataLab canonical company name ('' if unmatched) |
| rxdl_ticker | TEXT | Stock ticker ('' if unavailable) |
| rxdl_cik | TEXT | SEC CIK ('' if unavailable) |
| rxdl_company_id | INTEGER | RxDataLab internal ID (NULL if unmatched) |

**Key gotcha:** `appl_type` values have been normalized from FDA single-char codes (N→NDA, A→ANDA, B→BLA). Raw FDA files use single chars; this export uses full strings.

### ob_products

One row per product formulation within an application.

| Column | Type | Notes |
|--------|------|-------|
| appl_no | TEXT | FK to ob_applications |
| product_no | TEXT | 3-digit product number within the NDA/ANDA |
| ingredient | TEXT | Active ingredient(s), semicolon-delimited for combos |
| dosage_form_route | TEXT | Combined dosage form and route, e.g. "TABLET;ORAL" |
| trade_name | TEXT | Brand name |
| applicant | TEXT | FDA applicant name on this specific product |
| strength | TEXT | Dose strength(s) |
| appl_type | TEXT | NDA / ANDA / BLA |
| te_code | TEXT | Therapeutic equivalence code (AB, AA, etc.); '' if not rated |
| approval_date | TEXT | ISO date or '' if not yet approved |
| reference_listed_drug | TEXT | 'Yes' if this product is the RLD for generic substitution |
| reference_standard | TEXT | 'Yes' if designated reference standard |
| product_type | TEXT | 'RX' (prescription) or 'OTC' |

### ob_patents

Listed OB patents. One row per (appl_no, product_no, patent_no, use_code, pediatric_excl).

| Column | Type | Notes |
|--------|------|-------|
| appl_no | TEXT | FK to ob_applications |
| product_no | TEXT | FK within application |
| patent_no | TEXT | Patent number, e.g. "9876543" |
| use_code | TEXT | Method-of-use code (e.g. "U-2145"); '' for compound patents |
| pediatric_excl | INTEGER | 1 if pediatric exclusivity extends this patent by 6 months |
| patent_expire_date | TEXT | ISO date of stated expiry |
| drug_substance_flag | TEXT | 'Y' if covers drug substance |
| drug_product_flag | TEXT | 'Y' if covers drug product |
| delist_flag | TEXT | 'Y' if delisted |
| ob_submission_date | TEXT | Date patent was submitted to FDA |

**PED adjustment:** For `ob_loe_estimates`, patents with `pediatric_excl = 1` have +6 months added to `patent_expire_date`. The raw `patent_expire_date` in this table is the unadjusted statutory expiry.

**Use codes:** Join to `ob_patent_use_codes` on `use_code` for the method-of-use description.

### ob_exclusivity

Exclusivity grants. One row per (appl_no, product_no, exclusivity_code, exclusivity_expiration_date).

| Column | Type | Notes |
|--------|------|-------|
| appl_no | TEXT | FK to ob_applications |
| product_no | TEXT | FK within application |
| exclusivity_code | TEXT | See ob_exclusivity_codes for definitions |
| exclusivity_expiration_date | TEXT | ISO date |

**Common codes:** NCE (5-yr new chemical entity), ODE (7-yr orphan), PED (6-mo pediatric), NP (3-yr new product), I-YYY (biologics). Join to `ob_exclusivity_codes` for full definitions.

### ob_*_changes (Change Log Tables)

SCD-derived change history for each OB table. All follow the same pattern:

| Column | Notes |
|--------|-------|
| event | 'beginning_of_history' / 'added' / 'updated' / 'removed' |
| ob_release_date | ISO date of the OB monthly release that captured this change |

- **beginning_of_history**: rows present in the first OB import (not necessarily new on that date)
- **added**: first appearance after baseline
- **updated**: content change within the same PK (new values shown)
- **removed**: row retired from the active snapshot

Tables: `ob_product_changes`, `ob_patent_changes`, `ob_exclusivity_changes`, `ob_application_changes`

OB snapshot: **EOBZIP_2026_04** | Products/exclusivity history: 30 months (2023-10-01 to 2026-04-01) | Patent history: 30 months

---

## Purple Book Tables (pb_*)

### pb_applications

BLA registry for biologics.

| Column | Type | Notes |
|--------|------|-------|
| bla_number | TEXT PK | 6-digit BLA number, e.g. "103580" |
| sponsor_name | TEXT | FDA-reported applicant |
| rxdl_company | TEXT | RxDataLab canonical company name |
| rxdl_ticker | TEXT | Stock ticker |
| rxdl_cik | TEXT | SEC CIK |
| rxdl_company_id | INTEGER | RxDataLab internal ID (NULL if unmatched) |

### pb_products

Biologic product entries within a BLA. One row per (bla_number, product_number).

| Column | Type | Notes |
|--------|------|-------|
| bla_number | TEXT | FK to pb_applications |
| product_number | TEXT | 4-digit product number |
| proprietary_name | TEXT | Brand name |
| proper_name | TEXT | INN/nonproprietary name; includes biosimilar suffix for biosimilars (e.g. "-aaaa") |
| license_type | TEXT | 'BLA' (reference product) or 'BLA Biosimilar' |
| marketing_status | TEXT | 'Approved', 'Withdrawn', etc. |
| licensure | TEXT | 'Reference Product' or 'Biosimilar' or 'Interchangeable Biosimilar' |

**Key gotcha:** `proper_name` includes the FDA 4-letter suffix for biosimilars. For the reference molecule name, strip everything from the first '-' onward, or filter on `licensure = 'Reference Product'`.

### pb_exclusivity

Exclusivity grants for biologics. One row per (bla_number, product_number, exclusivity_type).

| Column | Type | Notes |
|--------|------|-------|
| bla_number | TEXT | FK to pb_applications |
| product_number | TEXT | FK within BLA |
| exclusivity_type | TEXT | See enum below |
| exclusivity_date | TEXT | ISO expiration date |

**exclusivity_type enum:**
- `standard` — FDA "Exclusivity Expiration Date" column; historically the 12-year BPCIA reference product exclusivity, but currently unpopulated (0 rows in this dataset — FDA has not populated this column in recent Purple Book releases)
- `ref_product` — FDA "Ref. Product Exclusivity Exp. Date" column; the 12-year BPCIA reference product exclusivity for newer biologics (FDA populates this selectively — not all reference products have it listed)
- `orphan` — 7-year orphan drug exclusivity
- `first_interchangeable` — 1-year exclusivity for first interchangeable biosimilar (protects the biosimilar sponsor, not the reference product)

**For LOE analysis:** Use `standard`, `ref_product`, and `orphan` types. `first_interchangeable` protects biosimilar sponsors (not reference product). Note: not all reference products have `ref_product` exclusivity listed (FDA populates it selectively); `orphan` is the more complete coverage signal for those products.

**History note:** Unlike OB exclusivity, PB exclusivity dates can be updated in-place (date changes without changing the type). The `pb_exclusivity_changes` table includes `updated` events for this.

### pb_patents

Listed PB patents.

| Column | Type | Notes |
|--------|------|-------|
| bla_number | TEXT | FK to pb_applications |
| patent_number | TEXT | Patent number |
| patent_expire_date | TEXT | ISO expiration date |
| patent_type | TEXT | e.g. 'Drug Product', 'Drug Substance', 'Method of Treatment' |

**PB patent caveats (read before use):**
- **Sparse coverage:** FDA patent listing under BPCIA is optional for most reference products. Only a small fraction of BLAs list patents in the Purple Book. Most BLAs have zero rows in `pb_patents`.
- **Shorter history than PB products:** PB patent tracking began later than PB product/exclusivity tracking. `pb_patent_history_months` (1 months) is much shorter than `pb_history_months` (72 months). Changes before the patent tracking start date are not captured in `pb_patent_changes`.
- **OB vs PB patent depth asymmetry:** OB patent history (30 months) is deeper than PB patent history, even though PB product/application history (72 months, back to 2020-05-01) is longer than OB. This is expected — OB patent listing has been mandatory since Hatch-Waxman; PB patent listing is newer and sparsely adopted.
- **For LOE analysis:** Use `pb_loe_estimates` (exclusivity-based) rather than PB patents. BPCIA exclusivity (`ref_product` 12yr, `orphan` 7yr) is the primary competitive barrier for biologics, not patent expiry.

### pb_*_changes (Change Log Tables)

Same pattern as OB change tables. All follow the same event enum.

Tables: `pb_product_changes`, `pb_exclusivity_changes`, `pb_application_changes`, `pb_patent_changes`

**Key difference from OB:** `pb_exclusivity_changes` can emit `updated` events (date changed in-place). OB exclusivity changes are add/remove only (all fields are in the PK).

PB snapshot: **PB_2026_01** | Products/exclusivity history: 72 months (2020-05-01 to 2026-04-01) | Patent history: 1 months

---

## Shared Tables

### drug_labels

SPL drug label data. Covers both NDA (via ob_applications) and BLA (via pb_applications) application numbers. The NDA and BLA number spaces are non-overlapping.

| Column | Type | Notes |
|--------|------|-------|
| appl_no | TEXT PK | Application number — join key for both OB and PB |
| label_effective_date | TEXT | ISO date of the label version |
| brand_name | TEXT | Brand/proprietary name from label |
| generic_name | TEXT | Generic/nonproprietary name |
| manufacturer_name | TEXT | Manufacturer |
| route | TEXT | Route(s) of administration |
| indications_and_usage | TEXT | Full indication text (truncated at 5000 chars) |
| pharm_class_epc | TEXT | Established Pharmacologic Class (EPC) |
| pharm_class_moa | TEXT | Mechanism of action |
| rxcui | TEXT | RxNorm CUI |
| unii | TEXT | FDA Unique Ingredient Identifier |
| boxed_warning | TEXT | Boxed warning text (NULL if none) |
| ndc | TEXT | Representative NDC |

**Coverage:** 3,019 application labels. Coverage is higher for NDAs approved after 2010.

### patent_details

PatentsView enrichment for OB-listed patents.

| Column | Type | Notes |
|--------|------|-------|
| patent_no | TEXT PK | Matches ob_patents.patent_no |
| title | TEXT | Patent title |
| grant_date | TEXT | ISO grant date |
| patent_application_number | TEXT | USPTO application number |

### ob_patent_use_codes

Lookup table for OB method-of-use patent codes.

| Column | Notes |
|--------|-------|
| use_code | e.g. "U-2145" |
| definition | Description of the patented method of use |

### ob_exclusivity_codes

Lookup table for OB exclusivity type codes.

| Column | Notes |
|--------|-------|
| exclusivity_code | e.g. "NCE", "ODE", "PED" |
| definition | Full description |

### export_metadata

Single-row table with export generation stats. Query this first to understand the provenance and depth of the data.

| Column | Notes |
|--------|-------|
| export_date | UTC timestamp when this bundle was generated |
| export_label | Bundle label, e.g. "2026_05" |
| ob_snapshot_label | Exact OB FDA release ingested as the current snapshot (e.g. "EOBZIP_2026_04") |
| pb_snapshot_label | Exact PB FDA release ingested as the current snapshot (e.g. "PB_2026_04") |
| ob_applications_count | Active OB application rows |
| ob_products_count | Active OB product rows |
| ob_patents_count | Active OB patent rows |
| ob_exclusivity_count | Active OB exclusivity rows |
| pb_applications_count | Active PB application rows |
| pb_products_count | Active PB product rows |
| pb_exclusivity_count | Active PB exclusivity rows |
| drug_labels_count | SPL label rows |
| ob_history_months | Distinct monthly OB releases in product/exclusivity change tables |
| ob_history_start | Earliest ob_release_date in ob_product_changes |
| ob_history_end | Most recent ob_release_date in ob_product_changes |
| ob_patent_history_months | Distinct monthly releases in ob_patent_changes (may be shorter than ob_history_months) |
| pb_history_months | Distinct monthly PB releases in product/exclusivity change tables |
| pb_history_start | Earliest pb_release_date in pb_product_changes |
| pb_history_end | Most recent pb_release_date in pb_product_changes |
| pb_patent_history_months | Distinct monthly releases in pb_patent_changes (significantly shorter than pb_history_months) |
| indication_coverage_pct | % of NDAs with SPL indication text |

---

## Derived Views

### ob_loe_estimates

Reported loss-of-exclusivity cliff per NDA application. One row per NDA with active patent or exclusivity protection.

`reported_loe_date` = MAX(PED-adjusted patent expiry, exclusivity expiration)
`years_to_loe` = computed at export time; recalculate from `reported_loe_date` for current values.

Columns: `appl_no`, `sponsor_name`, `rxdl_company`, `trade_name`, `ingredient`, `max_patent_exp`, `any_ped_extension`, `max_excl_exp`, `excl_codes`, `reported_loe_date`, `years_to_loe`

### pb_loe_estimates

Reported LOE per BLA reference product. Uses `standard`, `ref_product`, and `orphan` exclusivity types (`first_interchangeable` is excluded — it protects the biosimilar sponsor, not the reference product). One row per BLA; `standard_excl_date` captures whichever of `standard` or `ref_product` is present.

Columns: `bla_number`, `sponsor_name`, `rxdl_company`, `trade_name`, `proper_name`, `standard_excl_date`, `orphan_excl_date`, `reported_loe_date`, `years_to_loe`

### all_loe_estimates

UNION of `ob_loe_estimates` and `pb_loe_estimates`. Primary cross-source use case: company LOE calendar spanning both NDA and BLA portfolio.

Columns: `app_id`, `app_type` (NDA/BLA), `source` (ob/pb), `sponsor_name`, `rxdl_company`, `trade_name`, `drug_name`, `reported_loe_date`, `years_to_loe`

### all_applications

Unified innovator portfolio view. Excludes ANDAs. Use `rxdl_company` as the join key across sources.

Columns: `app_id`, `appl_type`, `sponsor_name`, `rxdl_company`, `rxdl_ticker`, `rxdl_cik`, `rxdl_company_id`, `source` (ob/pb)

### first_generic_entry

**Empty in this evaluation sample.** ANDA (generic application) data is not included in the sample, so this view returns 0 rows. In the full dataset, this view covers ~2,400 NDA formulations with at least one approved generic entry.

Schema for reference: `nda_appl_no`, `nda_sponsor`, `trade_name`, `ingredient`, `dosage_form_route`, `nda_approval_date`, `first_generic_approval_date`, `first_anda_appl_no`, `generic_sponsor`, `generic_count`

No PB equivalent in this release (biosimilar matching requires INN normalization).

---

## LOE Disclaimers

`reported_loe_date` is derived solely from FDA Orange Book and Purple Book records. It does not account for:
- Patent maintenance fee lapse (patent abandoned before stated expiry)
- Patent term adjustments (PTA) granted by USPTO after original expiry
- Paragraph IV challenges (ANDA/biosimilar applicant may succeed before reported LOE)
- Regulatory decisions after the export date
- Agreements (settlements, authorized generics) that may alter effective exclusivity

Use as a screening tool, not a legal opinion.
