💡 patents

USPTO patent grant and trademark application data from PatentsView bulk downloads and USPTO trademark bulk CSV. Patent tables are year-partitioned by grant_year (1976+). Trademark tables are year-partitioned by application_year. Claims and summaries include vector embedding columns (FLOAT[]) populated at ingestion time via batched embedding calls. Full-dump files are cached locally under GOVDATA_CACHE_DIR/patents/ with a quarterly TTL to avoid re-downloading on every year invocation.

17 datasets · 174 columns

patent_grants · table

One row per granted patent (patent_type utility or plant by default; design patents excluded unless PATENTS_INCLUDE_DESIGN=true), with patent_date, patent_title, num_claims, wipo_kind, and a withdrawn flag. Faithful recreation of g_patent.tsv, no joins, year-partitioned by grant_year starting from GOVDATA_START_YEAR (nominally 2010, but the configured/actual floor can drift — check observedCoverage below for the real minYear rather than assuming it matches the default) — narrower than the six full-dump snapshot tables (patent_assignees, patent_inventors, patent_cpc_classes, patent_abstracts, patent_applications, patent_figures), which cover all patents back to 1976 with no year cutoff. Abstract, filing date, and figure counts live in those tables, joined on patent_id at query time.

Column	Type	Null	Description
patent_id	string	yes	USPTO patent number (e.g., 10123456)
grant_year	integer	no	Calendar year of patent grant; primary partition key
patent_date	string	yes	Date of patent grant (YYYY-MM-DD)
patent_type	string	yes	utility / design / plant
patent_title	string	yes	Patent title
num_claims	integer	yes	Number of claims
wipo_kind	string	yes	WIPO kind code (B1, B2, etc.)
withdrawn	boolean	yes	Whether patent was withdrawn; filter to false for active grants

patent_assignees · table

To reach a patent assignee FROM a company rather than by name, join ref.canonical_org_entity on patents_assignee_id = assignee_id — that table carries one foreign key per source, so a firm's SEC CIK and its patent assignee id sit in the same row and the join is exact. Matching assignee_organization as text finds the wrong entities and misses subsidiaries filing under unrelated names. Faithful recreation of g_assignee_disambiguated.tsv — one row per patent-assignee combination (assignee_organization or first/last name, assignee_type, assignee_sequence), keyed by patent_id, no joins and no year. Loaded once per quarter as a full snapshot covering all patents back to 1976 (broader than patent_grants, which only materializes GOVDATA_START_YEAR+/2010+, so older patent_ids here have no patent_grants row). location_id is the foreign key into patent_locations for geography.

Column	Type	Null	Description
patent_id	string	yes	USPTO patent number (e.g., 10123456)
assignee_sequence	integer	yes	Order when patent has multiple assignees (0-based)
assignee_id	string	yes	PatentsView disambiguated assignee ID
assignee_organization	string	yes	Organization name; null for individual assignees
assignee_name_first	string	yes	First name; null for org assignees
assignee_name_last	string	yes	Last name; null for org assignees
assignee_type	string	yes	US company / foreign company / US government / individual
location_id	string	yes	FK into patent_locations for geographic fields

patent_inventors · table

Faithful recreation of g_inventor_disambiguated.tsv (~8 GB uncompressed) — one row per patent-inventor combination, keyed by patent_id, with an inferred gender_code useful for diversity analysis. Loaded once per quarter as a full snapshot covering all patents back to 1976 (broader than patent_grants, which only materializes GOVDATA_START_YEAR+/2010+, so older patent_ids here have no patent_grants row). location_id is the foreign key into patent_locations for geography.

Column	Type	Null	Description
patent_id	string	yes	USPTO patent number (e.g., 10123456)
inventor_id	string	yes	PatentsView disambiguated inventor ID
inventor_sequence	integer	yes	Inventor order on patent (0 = first/primary)
name_first	string	yes	First name
name_last	string	yes	Last name
gender_code	string	yes	M / F (inferred by PatentsView disambiguation); useful for diversity analysis
location_id	string	yes	FK into patent_locations for geographic fields

patent_cpc_classes · table

Faithful recreation of g_cpc_current.tsv — one row per patent-CPC code combination (cpc_section, cpc_class, cpc_subclass, cpc_group, cpc_type), keyed by patent_id; CPC is the primary technology taxonomy for patent analysis. Loaded once per quarter as a full snapshot covering all patents back to 1976 (broader than patent_grants, which only materializes GOVDATA_START_YEAR+/2010+, so older patent_ids here have no patent_grants row).

Column	Type	Null	Description
patent_id	string	yes	USPTO patent number (e.g., 10123456)
cpc_sequence	integer	yes	CPC code sequence on patent
cpc_section	string	yes	CPC section (A-H, Y)
cpc_class	string	yes	CPC class (2-char, e.g., H04)
cpc_subclass	string	yes	CPC subclass (1-char, e.g., L)
cpc_group	string	yes	Full CPC classification string (e.g., H04L0009000)
cpc_type	string	yes	i (inventive) or a (additional)

patent_abstracts · table

Faithful recreation of g_patent_abstract.tsv — one row per patent abstract, keyed by patent_id. Loaded once per quarter as a full snapshot covering all patents back to 1976 (broader than patent_grants, which only materializes GOVDATA_START_YEAR+/2010+, so older patent_ids here have no patent_grants row).

Column	Type	Null	Description
patent_id	string	yes	USPTO patent number (e.g., 10123456)
patent_abstract	string	yes	Patent abstract text

patent_applications · table

Faithful recreation of g_application.tsv — one row per application record (filing_date, patent_application_type), keyed by patent_id. Loaded once per quarter as a full snapshot covering all patents back to 1976 (broader than patent_grants, which only materializes GOVDATA_START_YEAR+/2010+, so older patent_ids here have no patent_grants row).

Column	Type	Null	Description
patent_id	string	yes	USPTO patent number (e.g., 10123456)
filing_date	string	yes	Original application filing date (YYYY-MM-DD)
patent_application_type	string	yes	Application type (e.g., utility, continuation)

patent_figures · table

Faithful recreation of g_figures.tsv — one row per patent figure/sheet count record (num_figures, num_sheets), keyed by patent_id. Loaded once per quarter as a full snapshot covering all patents back to 1976 (broader than patent_grants, which only materializes GOVDATA_START_YEAR+/2010+, so older patent_ids here have no patent_grants row).

Column	Type	Null	Description
patent_id	string	yes	USPTO patent number (e.g., 10123456)
num_figures	integer	yes	Number of figures
num_sheets	integer	yes	Number of drawing sheets

patent_locations · table

Faithful recreation of g_location_disambiguated.tsv — one row per disambiguated location (state_fips, county_fips, country_code, latitude, longitude), keyed by location_id. FK target for patent_inventors.location_id and patent_assignees.location_id. Loaded once per quarter as a full snapshot.

Column	Type	Null	Description
location_id	string	no	PatentsView disambiguated location ID (PK)
state_fips	string	yes	State FIPS code — US locations only. NULL on 71% of rows (D-276), but confirmed live this tracks country_code almost perfectly: US rows are populated (99.98%), every non-US country's rows are 100% NULL by definition (no US state to assign). A state-level analysis should filter to country_code='US' first rather than treating a NULL state_fips as a geocoding gap.
county_fips	string	yes	County FIPS code
country_code	string	yes	Disambiguated country code
latitude	double	yes	Latitude
longitude	double	yes	Longitude

patent_claims · table

One row per patent claim. Claims define the legal scope of the patent — the most analytically useful text component. Per-year source files: g_claims_{year}.tsv.zip from the PatentsView claims/ subdirectory. The embedding column is populated at ingestion time via batched embedding calls. For dependent claims, the transformer prepends the parent independent claim text before embedding to capture full legal scope.

Column	Type	Null	Description
patent_id	string	yes	USPTO patent number (e.g., 10123456)
grant_year	integer	no	Calendar year of patent grant; primary partition key
claim_sequence	integer	yes	Processing order of claim (1-based)
claim_number	integer	yes	Claim number as printed in the patent
claim_text	string	yes	Full claim text
dependent	string	yes	NULL = independent claim; otherwise a "claim N" reference to the parent's claim_number (verbatim from PatentsView)
exemplary	boolean	yes	Claim designated as exemplary; useful for ML classification

patent_summaries · table

One row per patent. Brief summary section text from the patent specification. More concise than the full description; covers key aspects and distinguishing features over prior art. Per-year source files: g_brf_sum_text_{year}.tsv.zip from the PatentsView brief-summary-text/ subdirectory. The embedding column is populated at ingestion time.

Column	Type	Null	Description
patent_id	string	yes	USPTO patent number (e.g., 10123456)
grant_year	integer	no	Calendar year of patent grant; primary partition key
summary_text	string	yes	Full brief summary section text

trademark_case_file · table

Faithful landing of USPTO TRCFECO2 case_file.csv — one row per trademark application (serial_no), all 79 source columns verbatim. Year-partitioned by application filing year.

Column	Type	Null	Description
serial_no	string	no	USPTO trademark serial number (primary key)
abandon_dt	string	yes	Date the mark/application was abandoned (YYYYMMDD)
amend_reg_dt	string	yes	Date of the file amendment to register (YYYYMMDD)
amend_lb_for_app_in	string	yes	Flag: filing basis amended to foreign application priority (44(d))
amend_lb_for_reg_in	string	yes	Flag: filing basis amended to foreign registration (44(e))
amend_lb_itu_in	string	yes	Flag: filing basis amended to intent-to-use
amend_lb_use_in	string	yes	Flag: filing basis amended to use in commerce
reg_cancel_cd	string	yes	Code identifying the section under which the registration was cancelled
reg_cancel_dt	string	yes	Date cancellation of the entire registration was recorded (YYYYMMDD)
cancel_pend_in	string	yes	Flag: a cancellation proceeding is pending
cert_mark_in	string	yes	Flag: mark is a certification mark
chg_reg_in	string	yes	Flag: registration changed (amendment, correction, fewer goods/classes)
coll_memb_mark_in	string	yes	Flag: mark is a collective membership mark
coll_serv_mark_in	string	yes	Flag: mark is a collective service mark
coll_trade_mark_in	string	yes	Flag: mark is a collective trademark
draw_color_cur_in	string	yes	Flag: drawing is currently in color
draw_color_file_in	string	yes	Flag: drawing was in color at filing
concur_use_in	string	yes	Flag: registration is subject to concurrent use
concur_use_pend_in	string	yes	Flag: a concurrent use proceeding is pending
file_location	string	yes	Current location of the application or registration file
draw_3d_cur_in	string	yes	Flag: drawing is currently three-dimensional
draw_3d_file_in	string	yes	Flag: drawing was three-dimensional at filing
exm_attorney_name	string	yes	Name of the USPTO examining attorney assigned to the file
lb_use_file_in	string	yes	Filing basis flag: filed as a use-in-commerce application
lb_for_app_cur_in	string	yes	Filing basis flag: foreign application priority (44(d)) currently
lb_for_reg_cur_in	string	yes	Filing basis flag: foreign registration (44(e)) currently
lb_intl_reg_cur_in	string	yes	Filing basis flag: international registration (66(a)) currently
lb_for_app_file_in	string	yes	Filing basis flag: foreign application priority (44(d)) at filing
lb_for_reg_file_in	string	yes	Filing basis flag: foreign registration (44(e)) at filing
lb_intl_reg_file_in	string	yes	Filing basis flag: international registration (66(a)) at filing
lb_none_cur_in	string	yes	Filing basis flag: no basis currently claimed
filing_dt	string	yes	Application filing date (YYYYMMDD); source of the year partition
for_priority_in	string	yes	Flag: record contains foreign application info and a priority claim
lb_itu_cur_in	string	yes	Filing basis flag: intent-to-use application currently
lb_itu_file_in	string	yes	Filing basis flag: intent-to-use application at filing
interfer_pend_in	string	yes	Flag: an interference proceeding is pending
exm_office_cd	string	yes	Code of the USPTO examining law office assigned
file_location_dt	string	yes	Date the file arrived at its current location (YYYYMMDD)
mark_draw_cd	string	yes	Drawing code 1=typed 2=design 3=stylized 4=standard char 5=color
mark_id_char	string	yes	Trademark text; null for design marks
opposit_pend_in	string	yes	Flag: an opposition proceeding is pending
amend_principal_in	string	yes	Flag: application amended to the Principal Register
concur_use_pub_in	string	yes	Flag: mark published subject to concurrent use
publication_dt	string	yes	Date the mark was published for opposition (YYYYMMDD)
registration_dt	string	yes	Date the mark was registered (YYYYMMDD)
renewal_dt	string	yes	Date of registration renewal (YYYYMMDD)
renewal_file_in	string	yes	Flag: a renewal application was filed
repub_12c_dt	string	yes	Date of republication under Section 12(c) (YYYYMMDD)
repub_12c_in	string	yes	Flag: registered mark republished under Section 12(c)
incontest_ack_in	string	yes	Flag: Section 15 affidavit of incontestability acknowledged
incontest_file_in	string	yes	Flag: Section 15 affidavit of incontestability filed
acq_dist_in	string	yes	Flag: acquired distinctiveness claimed for the whole mark (Section 2(f))
acq_dist_part_in	string	yes	Flag: acquired distinctiveness claimed for part of the mark (Section 2(f))
use_afdv_acc_in	string	yes	Flag: Section 8 continued-use affidavit accepted
use_afdv_file_in	string	yes	Flag: Section 8 continued-use affidavit filed
use_afdv_par_acc_in	string	yes	Flag: Section 8 continued-use affidavit partially accepted
serv_mark_in	string	yes	Flag: mark is a service mark
std_char_claim_in	string	yes	Standard character mark flag (T/F)
cfh_status_cd	string	yes	Status code (live/dead/registered/abandoned/cancelled)
cfh_status_dt	string	yes	Date the case file's current status was recorded (YYYYMMDD)
amend_supp_reg_in	string	yes	Flag: application amended to the Supplemental Register
supp_reg_in	string	yes	Flag: filed for or registered on the Supplemental Register
trade_mark_in	string	yes	Flag: mark is a trademark
lb_use_cur_in	string	yes	Filing basis flag: use-in-commerce application currently
lb_none_file_in	string	yes	Filing basis flag: no basis claimed at filing
ir_auto_reg_dt	string	yes	Date US registration auto-issued absent a first refusal (Madrid) (YYYYMMDD)
ir_first_refus_in	string	yes	Flag: USPTO issued a notice of first refusal to the IB (Madrid)
ir_death_dt	string	yes	Date the international registration expired (YYYYMMDD)
ir_publication_dt	string	yes	Date the international registration was published (YYYYMMDD)
ir_registration_dt	string	yes	Date of the international registration (Madrid) (YYYYMMDD)
ir_registration_no	string	yes	International registration (Madrid Protocol) number
ir_renewal_dt	string	yes	Date by which the international registration must be renewed (YYYYMMDD)
ir_status_cd	string	yes	International registration status code
ir_status_dt	string	yes	Effective date of the international registration status (YYYYMMDD)
ir_priority_dt	string	yes	Date international registration priority was claimed (YYYYMMDD)
ir_priority_in	string	yes	Flag: international registration priority claimed
related_other_in	string	yes	Flag: 'and others' appears in the list of prior registrations
registration_no	string	yes	Registration number; null (or all-zeros) if pending
tad_file_id	string	yes	Identifier of the source XML file the record was extracted from
year	integer	no	Application filing year (Iceberg partition)

trademark_owner · table

Faithful landing of USPTO TRCFECO2 owner.csv — applicant/owner identity, one row per (serial_no, own_seq). own_addr_state_cd is a 2-letter code, not FIPS — join geo.states. To reach a trademark owner's cross-schema identity FROM another schema rather than by name, join ref.canonical_org_entity on patents_trademark_owner_id = own_id — that table carries one foreign key per source, so the owner's LEI/SEC CIK and its own_id sit in the same row and the join is exact. Matching own_name as text finds the wrong entities and misses owners filing under a differently-worded name.

Column	Type	Null	Description
serial_no	string	no	Corresponding case file serial number
own_seq	string	yes	Sequence of owner records within the same party type
own_id	string	yes	Unique identifier for the owner record
own_name	string	yes	Name of the owning party
own_type_cd	string	yes	Owning party type code (e.g. original applicant, new owner)
own_entity_cd	string	yes	Owner legal entity type code
own_entity_desc	string	yes	Owner entity description or statement
own_composed_of	string	yes	Owner 'composed of' statement
own_altn_name	string	yes	Owner alternative names (DBA/AKA)
own_addr_1	string	yes	Owner address line 1
own_addr_2	string	yes	Owner address line 2
own_addr_city	string	yes	City or foreign designation of the owner address
own_addr_state_cd	string	yes	State code of the owner address
own_addr_postal	string	yes	US or foreign postal code of the owner address
own_addr_country_cd	string	yes	Country code of the owner address
own_addr_other_cd	string	yes	Other region (non-country) code of the owner address
own_nalty_state_cd	string	yes	State code of the owner's place of origin
own_nalty_country_cd	string	yes	Country code of the owner's place of origin
own_nalty_other_cd	string	yes	Other region (non-country) code of the owner's place of origin
year	integer	no	Application filing year (Iceberg partition)

trademark_classification · table

Faithful landing of USPTO TRCFECO2 classification.csv — US class + first-use dates + status, one row per (serial_no, class_id).

Column	Type	Null	Description
serial_no	string	no	Corresponding case file serial number
class_id	string	yes	Unique identifier for the classification record
class_primary_cd	string	yes	Classification primary code
class_status_cd	string	yes	Classification status code
class_status_dt	string	yes	Date the classification status was recorded (YYYYMMDD)
class_intl_count	string	yes	Number of international classes
class_us_count	string	yes	Number of US classes
first_use_any_dt	string	yes	First-use-anywhere date, converted to a date value (YYYYMMDD)
first_use_any_dt_raw	string	yes	First-use-anywhere date, as entered in the source XML
first_use_com_dt	string	yes	First-use-in-commerce date, converted to a date value (YYYYMMDD)
first_use_com_dt_raw	string	yes	First-use-in-commerce date, as entered in the source XML
year	integer	no	Application filing year (Iceberg partition)

trademark_intl_class · table

Faithful landing of USPTO TRCFECO2 intl_class.csv — international (Nice) class codes, one row per (serial_no, intl_class_cd).

Column	Type	Null	Description
serial_no	string	no	Corresponding case file serial number
intl_class_cd	string	yes	International (Nice) class code
class_id	string	yes	Corresponding classification record ID
year	integer	no	Application filing year (Iceberg partition)

trademark_statement · table

Faithful landing of USPTO TRCFECO2 statement.csv — goods/services and other statement text, one row per (serial_no, statement_type_cd).

Column	Type	Null	Description
serial_no	string	no	Corresponding case file serial number
statement_type_cd	string	yes	Statement type category code
statement_text	string	yes	Statement text (goods/services description is one statement type)
year	integer	no	Application filing year (Iceberg partition)

trademark_applications · view

One row per trademark application (serial_no), joined from trademark_case_file + owner + intl_class + statement. Replaces the old ETL-joined table. applicant_state is a 2-letter code (not FIPS) — join geo.states.

View — columns are resolved by the query engine at runtime.

patent_activity_by_county · view

County-year active-patent-grant counts (withdrawn excluded) attributed to the primary assignee's location — arg_min by assignee_sequence, the same primary-owner pattern trademark_applications uses — geocoded via patent_locations (state_fips/county_fips already disambiguated by PatentsView; no text-matching or geocoding step needed, unlike the trademark side). Enriched with a cross-schema join to census.acs_education (bachelor's-degree-or-higher rate) and econ.county_wages (average weekly wage, industry_code='10' all-industries total, own_code='0' all ownership) for the same county+year. Assignee location, not inventor location — a patent with foreign inventors but a US assignee counts toward that assignee's county. Each of the three joined sources has its own coverage floor (patent_grants, census.acs_education, econ.county_wages each start in a different year, and none of the three defaults are reliable indicators of what's actually loaded) — check each source's own observedCoverage block for its real minYear rather than assuming a specific year here; years outside a source's actual coverage window return null enrichment columns for that source, not zero. The enrichment joins compose pl.state_fips || pl.county_fips (2-digit + 3-digit, PatentsView's native decomposed format) into the standard 5-digit FIPS both census.acs_education and econ.county_wages key on.

View — columns are resolved by the query engine at runtime.