Skip to content

Core tables

Company, occupation, badge, wage, hiring, and location fields.

companies

PK company_name. REST field company_URL maps to column company_url.

Field Type Key Description Interface notes
company_uid VARCHAR(50) INDEX Stable unique identifier. Generated on INSERT; preserved on UPDATE. Resolves aoi_parents / aoi_subsidiaries / aoi_siblings. Returned on company payloads. Filter: event_type reads company_event_type.
company_name VARCHAR(255) PRIMARY Canonical company name. Join key to aliases, company_occupation_summary, title_conversion, hiring_flag. Path param {name}. DSL: GET COMPANY, WHERE COMPANY IS.
company_description TEXT Company description. GET /api/companies/{name}, GET COMPANY.
company_url TEXT Homepage URL, fully formed. REST JSON key: company_URL.
primary_industry VARCHAR(100) INDEX Primary industry. Must match industries.industry_name. REST: ?industry= DSL: WHERE INDUSTRY IS.
secondary_industry VARCHAR(100) Secondary industry, or NULL. Company detail payloads.
company_city VARCHAR(255) Headquarters city. REST: ?company_city= DSL: WHERE COMPANY_CITY IS.
company_state VARCHAR(255) Headquarters state. REST: ?company_state= DSL: WHERE COMPANY_STATE IS.
company_country VARCHAR(255) Headquarters country. Company list/detail payloads.
company_event_type VARCHAR(50) INDEX Corporate event (rebrand, merger, bankruptcy, etc.), or blank. REST: ?event_type= DSL: WHERE EVENT_TYPE IS.
company_event_note VARCHAR(500) How to interpret remaining badge data when an event is set. Company detail payloads.
aoi_parents VARCHAR(255) Semicolon-separated parent company_uid values, if any. EXPAND RELATIONSHIPS / ?expand=relationships.
aoi_subsidiaries VARCHAR(500) Semicolon-separated subsidiary company_uid values. EXPAND RELATIONSHIPS / ?expand=relationships.
aoi_siblings VARCHAR(500) Semicolon-separated sibling company_uid values. EXPAND RELATIONSHIPS / ?expand=relationships.
overall_badge VARCHAR(50) INDEX Overall rating: Platinum, Gold, or NULL (unbadged / insufficient data). REST: ?overall_badge= DSL: WHERE OVERALL_BADGE IS. Ranked = any non-null.
badge_early_career VARCHAR(50) Entry-level opportunities: Platinum, Gold, or NULL. REST: ?badge_early_career= DSL: WHERE BADGE_EARLY_CAREER IS.
badge_growth VARCHAR(50) Career growth: Platinum, Gold, or NULL. REST: ?badge_growth= DSL: WHERE BADGE_GROWTH IS.
badge_stability VARCHAR(50) Job stability: Platinum, Gold, or NULL. REST: ?badge_stability= DSL: WHERE BADGE_STABILITY IS.
created_at TIMESTAMP Record creation time. ?expand=timestamps / EXPAND TIMESTAMPS.
updated_at TIMESTAMP Last update time. ?expand=timestamps / EXPAND TIMESTAMPS.

company_uid_name

PK company_uid. Not a query surface; resolves relationship UIDs to names.

Field Type Key Description Interface notes
company_uid VARCHAR(20) PRIMARY Company UID. Used by EXPAND RELATIONSHIPS.
company_name VARCHAR(255) INDEX Canonical company name. Used by EXPAND RELATIONSHIPS.

industries

PK industry_id. Valid names for companies.primary_industry.

Field Type Key Description Interface notes
industry_id SERIAL PRIMARY Surrogate key. Not exposed as a path param.
industry_name VARCHAR(100) UNIQUE Exact industry name. GET /api/industries, LIST INDUSTRIES. Filter value for ?industry=.
company_count INT Cached company count in this industry. GET /api/industries, LIST INDUSTRIES.
created_at TIMESTAMP Record creation time. Not served unless timestamps expanded where supported.
updated_at TIMESTAMP Last update time. Not served unless timestamps expanded where supported.

company_aliases

Unique (company_name, alias). company_name must match companies.company_name.

Field Type Key Description Interface notes
alias_id SERIAL PRIMARY Surrogate key. Not a client field.
company_uid VARCHAR(50) Denormalized company UID. COMPANYCONVERT result.
company_name VARCHAR(255) INDEX Canonical name returned after an alias match. GET /api/companyconvert, COMPANYCONVERT.
alias VARCHAR(255) INDEX Alternative name (ticker, brand, abbreviation, former name, etc.). Match input to COMPANYCONVERT.
alias_type VARCHAR(50) INDEX Kind of alias. Not required on convert requests.
notes VARCHAR(500) Curator notes. Not served on convert responses.
created_at TIMESTAMP Record creation time. Not served.
updated_at TIMESTAMP Last update time. Not served.

company_occupation_summary

PK co_occ_uid. Grain (company_name, cluster_id). Occupation-level badges and hiring volume at a company.

Field Type Key Description Interface notes
co_occ_uid VARCHAR(50) PRIMARY Concatenation of company_uid and cluster_id. Not a client field.
company_uid VARCHAR(50) INDEX Company UID. Occupation-at-company and badge-analytics payloads.
company_name VARCHAR(255) INDEX Canonical company name. LIST OCCUPATIONS FOR COMPANY, LIST COMPANIES FOR CLUSTER.
cluster_id INT INDEX Occupation cluster. Join to occupation_info / wages / AI impact. Path {id} on cluster company lists.
cluster_name VARCHAR(255) INDEX Cluster display name. Occupation-at-company payloads.
badge_early_career VARCHAR(50) Early-career badge on this pair: Platinum, Gold, or NULL. WYWM sort archetype early_career. Filter badge_early_career.
badge_growth VARCHAR(50) Growth badge on this pair: Platinum, Gold, or NULL. WYWM sort archetype growth. Filter badge_growth.
badge_stability VARCHAR(50) Stability badge on this pair: Platinum, Gold, or NULL. WYWM sort archetype stability. Filter badge_stability.
entry_posting_count INT Count of entry-level-accessible postings for this occupation at this company. Sort: entry_postings / ORDER BY ENTRY_POSTINGS.
entry_posting_tier VARCHAR(20) INDEX Band of entry_posting_count: high | above-average | average | low | none. REST: ?entry_posting_tier= DSL: WHERE ENTRY_POSTING_TIER IS.
total_posting_count INT Count of all postings for this occupation at this company. Sort: total_postings / ORDER BY TOTAL_POSTINGS.
total_posting_tier VARCHAR(20) INDEX Band of total_posting_count: high | above-average | average | low | none. REST: ?total_posting_tier= DSL: WHERE TOTAL_POSTING_TIER IS.
early_career_access SMALLINT 1 = occupation is available as an entry-level job at this company; 0 = not. Response-only. Not a filter or sort.
top10_occupation VARCHAR(10) Whether this occupation is in the firm's top 10. Stored '1' / '0'. REST: ?top10= DSL: WHERE TOP10 IS. Accepts 1/0/Yes/Y/true.
created_at TIMESTAMP Record creation time. ?expand=timestamps / EXPAND TIMESTAMPS.
updated_at TIMESTAMP Last update time. ?expand=timestamps / EXPAND TIMESTAMPS.

occupation_cluster

Composite PK (cluster_id, onet_code). Many O*NET codes share one cluster.

Field Type Key Description Interface notes
cluster_id INT PRIMARY (composite) Cluster ID. GET /api/clusters/{id}. TITLECONVERT ?expand=cluster.
onet_code VARCHAR(20) PRIMARY (composite) O*NET-SOC code, format XX-XXXX.XX. GET /api/occupations/{onet_code}. TITLECONVERT data[].onet_code.
consolidated_title VARCHAR(255) INDEX O*NET occupation title for this code. TITLECONVERT onet_title; occupation detail.
cluster_name VARCHAR(255) INDEX Cluster display name. Canonical spelling is occupation_info.cluster_name. GET /api/clusters, CLUSTERCONVERT, TITLECONVERT expand=cluster.
cluster_occs TEXT Pipe-separated member titles in the cluster. Cluster detail payloads.
created_at TIMESTAMP Record creation time. Not served unless timestamps expanded.
updated_at TIMESTAMP Last update time. Not served unless timestamps expanded.

occupation_info

PK cluster_id. Canonical cluster_name, education, pathways, skills, common titles, company-frequency caches. ai_flag is a deprecated cache of ai_expertise_impact.ai_flag. Numbered column families (destinations, premium skills, skill adjacency, common titles) vary in width between deliveries and are resolved from the schema at request time — do not assume a fixed count.

Field Type Key Description Interface notes
cluster_id INT PRIMARY Cluster ID. GET /api/clusters/{id}, LIST CLUSTERS, LIST OCCUPATIONS.
cluster_name VARCHAR(255) INDEX Canonical display name. All cluster/occupation list and detail payloads.
ba_plus_share DECIMAL(10,4) Share of workers with a bachelor's or higher (0.0–1.0). GET …/education, GET EDUCATION.
ai_flag VARCHAR(50) INDEX DEPRECATED 0.17.0-beta. Cached AI-impact category; NULL when unclassified. Still served and still filterable — read ai_expertise_impact.ai_flag instead. REST: ?ai_flag= DSL: WHERE AI_FLAG IS. Source of truth: ai_expertise_impact.
job_level VARCHAR(50) Education/experience band. See Values sheet. REST: ?job_level= DSL: WHERE JOB_LEVEL IS.
company_count_all INT Cached count of companies where this cluster appears. LIST OCCUPATIONS / frequency sorts.
company_count_badged INT Cached count of badged companies for this cluster. LIST OCCUPATIONS / frequency sorts (lens=badged).
company_count_top10 INT Cached count of companies where this cluster is a top-10 occupation. LIST OCCUPATIONS / frequency sorts (lens=top10).
cluster_id_destination_1 INT Top career-pathway destination cluster. GET …/pathways, GET /api/clusters/{id}/cluster-destinations.
cluster_id_destination_2 INT 2nd destination cluster. Same as destination_1.
cluster_id_destination_3 INT 3rd destination cluster. Same as destination_1.
cluster_id_destination_4 INT 4th destination cluster (0.17.0-beta). Same as destination_1.
cluster_id_destination_5 INT 5th destination cluster (0.17.0-beta). The family width is read from the schema at request time, so a 6th needs no client change. Same as destination_1.
cluster_name_destination_1 VARCHAR(255) Name of destination 1. GET …/pathways.
cluster_name_destination_2 VARCHAR(255) Name of destination 2. GET …/pathways.
cluster_name_destination_3 VARCHAR(255) Name of destination 3. GET …/pathways.
cluster_name_destination_4 VARCHAR(255) Name of destination 4. GET …/pathways.
cluster_name_destination_5 VARCHAR(255) Name of destination 5. GET …/pathways.
skill_adj_cluster_1 INT Most skill-adjacent cluster ID. GET /api/clusters/{id}/skill-adj-clusters.
skill_adj_cluster_2 INT 2nd skill-adjacent cluster ID. GET /api/clusters/{id}/skill-adj-clusters.
skill_adj_cluster_3 INT 3rd skill-adjacent cluster ID. GET /api/clusters/{id}/skill-adj-clusters.
premium_skill_1 VARCHAR(255) Skill with the greatest wage premium in this cluster. GET CLUSTER, LIST CLUSTERS, ?has_premium_skill=.
premium_skill_2 VARCHAR(255) 2nd wage-premium skill. Same as premium_skill_1.
premium_skill_3 VARCHAR(255) 3rd wage-premium skill. Same as premium_skill_1.
premium_skill_description_1 TEXT What premium_skill_1 means, in plain language (0.17.0-beta). premium_skill_details[] on GET …/pathways and GET …/ai-impact.
premium_skill_description_2 TEXT Description of premium_skill_2. Same as description_1.
premium_skill_description_3 TEXT Description of premium_skill_3. Same as description_1.
premium_skill_vintage VARCHAR(10) '2026-09' = skills came from that delivery. 'carried' = the delivery blanked them and the prior values were retained. Not published by default.
p50_wage_growth_0_10 NUMERIC(10,6) Median wage growth year 0 to year 10, employment-weighted across msa_size by the provider — NOT reproducible from the wage_data percentiles, which are unweighted per msa_size. Do not reconcile the two. wage_growth.p50_growth_0_10 on GET …/wages and GET /api/clusters/{id} (0.17.5-beta).
wage_growth_tier VARCHAR(10) low | medium | high. Tertiles of p50_wage_growth_0_10 cut by the provider within the delivery, so relative to that delivery and not recomputable from served values. wage_growth.tier on the same endpoints (0.17.5-beta).
common_clean_job_title_1 VARCHAR(255) Most common job title for this cluster. Cluster/occupation detail; occupation search.
common_clean_job_title_2 VARCHAR(255) 2nd common job title. Same as title_1.
common_clean_job_title_3 VARCHAR(255) 3rd common job title. Same as title_1.
created_at TIMESTAMP Record creation time. Not served unless timestamps expanded.
updated_at TIMESTAMP Last update time. Not served unless timestamps expanded.

ai_expertise_impact

PK/FK cluster_id → occupation_info. Source of truth for AI-impact classification and narratives. Unclassified clusters return coverage.classified=false and null narratives.

Field Type Key Description Interface notes
cluster_id INT PRIMARY, FK Cluster this assessment applies to. GET /api/occupations/{onet}/ai-impact, GET /api/clusters/{id}/ai-impact, GET AI_IMPACT FOR CLUSTER.
cluster_name VARCHAR(255) Cluster name. AI-impact payloads.
ai_flag VARCHAR(50) INDEX Raising the Bar | Shrinking Fields | Lower Potential | Winners Pull Away. Filter ?ai_flag= / WHERE AI_FLAG IS. Mirrors into occupation_info.ai_flag.
entry_barrier_trend VARCHAR(20) INDEX Rising | Falling. REST: ?entry_barrier_trend= DSL: WHERE ENTRY_BARRIER_TREND IS.
expertise_premium_trend VARCHAR(20) INDEX Rising | Falling. REST: ?expertise_premium_trend= DSL: WHERE EXPERTISE_PREMIUM_TREND IS.
low_expertise_narrative TEXT Narrative for entry-level / low-expertise practitioners. AI-impact endpoints; EXPAND AI_IMPACT.
high_expertise_narrative TEXT Narrative for experienced / high-expertise practitioners. AI-impact endpoints; EXPAND AI_IMPACT.
methodology_version VARCHAR(20) INDEX Methodology stamp on the classification. AI-impact coverage block.
created_at TIMESTAMP Record creation time. Not served.
updated_at TIMESTAMP Last update time. Not served.

wage_data

Grain (cluster_id, msa_size, years_in). years_in is 0–10 — eleven tenure points since 0.17.0-beta, three before it. Not the promotion_retention bands.

Field Type Key Description Interface notes
wage_id SERIAL PRIMARY Surrogate key. Not a client field.
cluster_id INT INDEX Cluster ID. GET /api/clusters/{id}/wages, GET /api/occupations/{onet}/wages, GET WAGES.
cluster_name VARCHAR(255) Cluster name. Wage payloads.
msa_size VARCHAR(20) Large | Small/Medium. REST: ?msa_size= DSL: WHERE MSA_SIZE IS.
years_in INT INDEX Tenure the row describes, 0–10. The grain wage rows are keyed and ordered by (0.17.0-beta). REST: ?years_in= DSL: WHERE YEARS_IN IS. Returned on every wage row.
experience_level VARCHAR(10) 0 through 10 (years, as strings). The text mirror of years_in; kept so pre-0.17.0 filters on 0 | 5 | 10 resolve to the same rows. Sort on years_in, not this — lexically, 10 falls between 1 and 2. REST: ?experience_level= DSL: WHERE EXPERIENCE_LEVEL IS.
percentile_25 DECIMAL(12,2) 25th percentile wage. Wage payloads.
median DECIMAL(12,2) Median wage. Wage payloads.
percentile_75 DECIMAL(12,2) 75th percentile wage. Wage payloads; skill-adj metrics block.
created_at TIMESTAMP Record creation time. Not served.
updated_at TIMESTAMP Last update time. Not served.

promotion_retention

Grain (cluster_id, experience_level, msa_size). experience_level is Entry-Level | Experienced — not the wage_data year keys. Passing experience_level=10 fills wages and leaves these fields null.

Field Type Key Description Interface notes
id SERIAL PRIMARY Surrogate key. Not a client field.
cluster_id INT INDEX Cluster ID. GET /api/clusters/{id}/retention, GET RETENTION, skill-adj metrics.
cluster_name VARCHAR(255) INDEX Cluster name. Retention payloads.
experience_level VARCHAR(20) Entry-Level | Experienced. REST: ?experience_level= DSL: WHERE EXPERIENCE_LEVEL IS.
msa_size VARCHAR(20) Large | Small/Medium. REST: ?msa_size= DSL: WHERE MSA_SIZE IS.
internal_promotion_rate DECIMAL(10,6) Share in a better role at the same company 5 years after start (0.0–1.0). Retention payloads; skill-adj min/max filters.
external_promotion_rate DECIMAL(10,6) Share in a better role at a different company 5 years after start (0.0–1.0). Retention payloads; skill-adj min/max filters.
retention_rate_3yr DECIMAL(10,6) Share remaining at the start company for 3+ years (0.0–1.0). Retention payloads; skill-adj min/max filters.
created_at TIMESTAMP Record creation time. Not served.
updated_at TIMESTAMP Last update time. Not served.

location_mapping

ZIP → CBSA / msa_size. hiring_flag.cbsa is INT; this cbsa is VARCHAR(20) — join on the numeric value.

Field Type Key Description Interface notes
id SERIAL PRIMARY Surrogate key. Not a client field.
zip INT INDEX ZIP code (integer, no leading zeros). GET /api/location/zip/{zip}.
usps_zip_pref_city TEXT USPS preferred city. Location payloads.
usps_zip_pref_state TEXT USPS preferred state. Location payloads.
cbsa VARCHAR(20) INDEX OMB 5-digit CBSA code as text (e.g. 41860). GET /api/location/cbsa/{cbsa}. Hiring filters.
cbsa_name TEXT CBSA display name. Location and hiring payloads.
census_cbsa_pop_est_2024 VARCHAR(20) 2024 Census population estimate. Location payloads.
msa_size VARCHAR(20) INDEX Large | Small/Medium. Wage and promotion filter key. Returned on ZIP lookup; used as msa_size on wage/retention queries.
created_at TIMESTAMP Record creation time. Not served.
updated_at TIMESTAMP Last update time. Not served.

hiring_flag

Grain (company, cluster, CBSA). Geographic hiring. Occupation-level volume is company_occupation_summary, not this table.

Field Type Key Description Interface notes
hiring_id SERIAL PRIMARY Surrogate key. Not a client field.
company_name VARCHAR(255) INDEX Company name. GET /api/companies/{name}/hiring, GET HIRING FOR COMPANY.
company_uid VARCHAR(50) INDEX Company UID. Hiring payloads.
cluster_name VARCHAR(255) INDEX Cluster name. Hiring payloads.
cluster_id INT INDEX Cluster ID. GET HIRING … AND CLUSTER_ID IS. LIST COMPANIES FOR CLUSTER WHERE CBSA IS.
cbsa_name TEXT Metro area name. Hiring payloads.
cbsa INT OMB CBSA code stored as integer. REST/DSL CBSA filters. Join numerically to location_mapping.cbsa.
postings_count_qtile VARCHAR(10) Geographic hiring intensity: 1 | 2 | 3 (3 = strongest). API field/filter: hiring_postings_qtile. DSL: WHERE HIRING_POSTINGS_QTILE IS.
created_at TIMESTAMP Record creation time. Not served.
updated_at TIMESTAMP Last update time. Not served.