Attorney & Firm Tables

The attorney and firm tables form the enrichment data layer. Attorneys are enriched via the Blitz API and firm website scraping. Firms are seeded with 200 AmLaw firms.

Created via Dashboard

The attorneys table was created via the Supabase dashboard, not in migration files. It will not appear in auto-generated types. The TypeScript interface is manually defined in src/lib/attorney-utils.ts.

attorneys

The attorney enrichment table with 76 columns. Each row represents an attorney at a law firm, enriched from LinkedIn data via the Blitz API and (optionally) from firm website scraping.

Identity Fields

Column	Type	Nullable	Description
id	uuid	NO	Primary key
firm_id	uuid	YES	FK to firms.id
firm_name	text	YES	Firm name (denormalized)
full_name	text	YES	Full name
first_name	text	YES	First name
last_name	text	YES	Last name

Position Fields

Column	Type	Nullable	Description
job_title	text	YES	Current job title
seniority	text	YES	Derived seniority level (see values below)
job_description	text	YES	Job description from LinkedIn
job_start_date	text	YES	Start date of current position

Seniority values: partner, counsel, senior_associate, associate, other

Column Name

The column is seniority, NOT seniority_level. This is a common source of bugs.

LinkedIn Fields

Column	Type	Nullable	Description
linkedin_url	text	YES	LinkedIn profile URL (unique constraint)
linkedin_headline	text	YES	LinkedIn headline
linkedin_about	text	YES	LinkedIn about section
linkedin_connections	integer	YES	Number of LinkedIn connections
linkedin_work_history	jsonb	YES	Full work history from LinkedIn

Location Fields

Column	Type	Nullable	Description
city	text	YES	City
state	text	YES	State
country	text	YES	Country
country_code	text	YES	Country code (e.g., US)
office_name	text	YES	Office location name

Contact Fields

Column	Type	Nullable	Description
email_primary	text	YES	Primary email address
email_status	text	YES	Email validation status
email_work	text	YES	Work email
email_all	jsonb	YES	All known email addresses
phone_mobile	text	YES	Mobile phone number
phone_mobile_found	boolean	YES	Whether mobile was found during enrichment

email_status values: valid, invalid, catch_all, unknown

No "unverified" Status

The email_status column only accepts valid, invalid, catch_all, or unknown. The value "unverified" is not valid and will cause insert errors.

Education Fields

Column	Type	Nullable	Description
law_school	text	YES	Law school name
law_school_year	text	YES	Law school graduation year
law_school_degree	text	YES	Law degree (e.g., JD)
law_school_honors	text	YES	Honors (requires website scraping)
undergrad_school	text	YES	Undergraduate institution
undergrad_year	text	YES	Undergrad graduation year
undergrad_degree	text	YES	Undergrad degree
undergrad_major	text	YES	Undergrad major (requires website scraping)
other_education	jsonb	YES	Other education entries

Career Fields

Column	Type	Nullable	Description
years_experience	integer	YES	Calculated years of experience
prior_firms	jsonb	YES	List of prior firms from work history

Enrichment Tracking

Column	Type	Nullable	Description
enrichment_status	text	YES	Current enrichment state
enrichment_level	text	YES	How complete the enrichment is
credits_used	integer	YES	Total Blitz API credits consumed
enriched_at	timestamptz	YES	Last enrichment timestamp
source_waterfall	text	YES	Enrichment source chain
source_person	text	YES	Source for person data
source_email	text	YES	Source for email data
source_phone	text	YES	Source for phone data
source_website	text	YES	Source for website-scraped data
is_active	boolean	YES	Whether attorney is currently active

Website Scraping Fields (34 columns)

These fields are populated by firm website scraping, not the Blitz API:

Column	Type	Nullable	Description
practice	text	YES	Primary practice area
practice_secondary	text	YES	Secondary practice area
practice_specialties	jsonb	YES	Specific specialties
client_types	jsonb	YES	Types of clients served
representative_matters	jsonb	YES	Notable matters/cases
bar_admissions	jsonb	YES	Bar admissions list
bar_court_admissions	jsonb	YES	Court admissions list
rankings	jsonb	YES	Industry rankings (Chambers, etc.)
awards	jsonb	YES	Awards and recognitions
is_ranked	boolean	YES	Whether attorney has any ranking
has_publications	boolean	YES	Whether attorney has publications
publications_count	integer	YES	Number of publications
phone_office	text	YES	Office phone number
firm_website_profile	text	YES	URL to attorney's firm bio page
languages	jsonb	YES	Languages spoken
icp_score	numeric	YES	ICP match score
icp_ranking	text	YES	ICP tier ranking
icp_what_matched	jsonb	YES	Which ICP criteria matched
is_verified	boolean	YES	Manual verification flag
is_priority	boolean	YES	Priority flag for outreach
notes	text	YES	Free-text notes
tags	jsonb	YES	Custom tags
website_scraped_at	timestamptz	YES	Last website scrape timestamp
email_validated_at	timestamptz	YES	Last email validation timestamp
firm_domain	text	YES	Firm's domain (e.g., kirkland.com)
firm_linkedin_url	text	YES	Firm's LinkedIn company page URL
created_at	timestamptz	YES
updated_at	timestamptz	YES

Enrichment Data Flow

---

firms

Law firm database. Seeded with 200 AmLaw firms via migration 20260206_seed_amlaw_firms.sql.

Column	Type	Nullable	Description
id	uuid	NO	Primary key
name	text	NO	Full firm name
short_name	text	YES	Abbreviated name
linkedin_url	text	YES	LinkedIn company page URL
domain	text	YES	Primary domain (e.g., kirkland.com)
website	text	YES	Full website URL
amlaw_rank	integer	YES	AmLaw ranking number
tier	text	YES	Tier classification
attorney_count	integer	YES	Total attorney count
partner_count	integer	YES	Number of partners
associate_count	integer	YES	Number of associates
office_count	integer	YES	Number of offices
hq_city	text	YES	Headquarters city
hq_state	text	YES	Headquarters state
hq_country	text	YES	Headquarters country
office_cities	jsonb	YES	List of office cities
profile_url_pattern	text	YES	URL pattern for attorney bio pages
enrichment_status	text	YES	Enrichment state
last_enriched_at	timestamptz	YES	Last enrichment timestamp
rank	integer	YES	General rank field
description	text	YES	Firm description
logo_url	text	YES	Logo image URL
created_at	timestamptz	YES
updated_at	timestamptz	YES

Tier Values

Tier	Description	Count
amlaw_10	Top 10 AmLaw firms	10
amlaw_25	AmLaw 11-25	15
amlaw_50	AmLaw 26-50	25
amlaw_100	AmLaw 51-100	50
amlaw_200	AmLaw 101-200	100

Profile URL Patterns

The profile_url_pattern column stores the detected URL template for finding attorney bio pages on the firm's website. These are detected by scripts/detect-profile-patterns.ts.

Common patterns:

Firm	Pattern
Most firms	/people/{first}-{last}
Kirkland & Ellis	/lawyers/{last_initial}/{last}-{first}
Gibson Dunn	/lawyers/{last}
Latham & Watkins	/people/{last}
Cleary Gottlieb	/professionals/{first}-{last}
WilmerHale	/bio/{first}-{last}

Pattern variables: {first}, {last}, {first_initial}, {last_initial}, {full} (first-last).

Entity Relationships