Clay Data Model Proposal

Purpose

This document proposes a normalized database model for the fractional executives directory and jobs board.

The goal is to make it easy to answer two questions before implementation:

What is the canonical app data model we want to support?
Which of those fields can realistically come from Clay, and which must be derived or managed in-app?

This proposal assumes PostgreSQL or Supabase Postgres.

Design Principles

Keep Clay as a source system, not the canonical application schema.
Normalize shared taxonomies such as roles, industries, locations, and specialties.
Keep rich profile content in child tables instead of large denormalized blobs.
Store raw Clay payloads so we can remap or reprocess records without losing source context.
Derive search facets, market stats, related profiles, and landing-page aggregates from canonical tables.
Keep a clear separation between source ingestion state and publish state.

What Should Be Canonical vs Derived

Canonical

Roles
Industries
Locations
Specialties
Executive profiles
Executive experience
Executive education
Executive certifications
Executive reviews
Executive case studies
Executive verification state
Jobs
Job-to-industry relationships
Profile claim requests and ownership state
Clay ingestion metadata

Derived

Search results
Search facets
Market stats
Role, location, and industry landing-page payloads
Similar executives
Review count and average rating cache
Human-readable compensation summary
Human-readable location labels for some jobs

Suggested Enums

Executive Availability

AVAILABLE
LIMITED
UNAVAILABLE
BOOKED

Claim Status

UNCLAIMED
CLAIMED
VERIFIED

Travel Willingness

LOCAL
REGIONAL
NATIONAL
INTERNATIONAL

Verification Check Type

IDENTITY
CREDENTIALS
TRACK_RECORD

Verification Status

VERIFIED
PENDING
UNVERIFIED

Job Employment Type

CONTRACTOR
PART_TIME
FULL_TIME
TEMPORARY

Job Remote Type

REMOTE
HYBRID
ONSITE

Publish Status

DRAFT
REVIEW
PUBLISHED
ARCHIVED

Ingestion Status

PENDING
SUCCEEDED
FAILED
SKIPPED

Core Taxonomy Tables

`roles`

Canonical leadership role taxonomy used across directory and jobs.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`slug`	`text unique`	Example: `fractional-cfo`
`title`	`text`	Example: `CFO`
`full_title`	`text`	Example: `Chief Financial Officer`
`description`	`text`	SEO + directory copy
`is_active`	`boolean`	Soft disable
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`industries`

Canonical industry taxonomy.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`slug`	`text unique`	Example: `saas`
`name`	`text`	Example: `SaaS`
`description`	`text`	Optional taxonomy description
`is_active`	`boolean`	Soft disable
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`locations`

Canonical geographic taxonomy. Executives should reference this directly. Jobs can reference it when clean, while still keeping a display string.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`slug`	`text unique`	Example: `austin-tx`
`city`	`text`	Example: `Austin`
`state_region`	`text nullable`	Example: `TX`
`country_code`	`text`	Example: `US`
`country_name`	`text`	Example: `USA`
`metro`	`text nullable`	Example: `Greater Austin`
`display_name`	`text`	Example: `Austin, TX`
`lat`	`numeric nullable`	Optional
`lng`	`numeric nullable`	Optional
`is_active`	`boolean`	Soft disable
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`specialties`

Canonical specialty tags used for search and richer profile context.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`slug`	`text unique`	Example: `forecasting`
`name`	`text`	Example: `Forecasting`
`is_active`	`boolean`	Soft disable
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

Executive Directory Tables

`executives`

This is the canonical profile record. It should contain the core fields required for both cards and full profile pages.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`slug`	`text unique`	Public URL slug
`first_name`	`text`	Required
`last_name`	`text`	Required
`full_name`	`text`	Can be derived but storing is pragmatic
`headline`	`text`	Card + profile hero
`bio`	`text nullable`	Full profile
`photo_url`	`text nullable`	Optional
`role_id`	`uuid fk -> roles.id`	Required
`location_id`	`uuid fk -> locations.id`	Required
`years_experience`	`integer nullable`	Search + profile hero
`availability`	`enum`	`AVAILABLE`, etc.
`remote_available`	`boolean`	Profile detail
`travel_willingness`	`enum nullable`	Profile detail
`minimum_engagement`	`text nullable`	Example: `3 months`
`linkedin_url`	`text nullable`	Optional
`website_url`	`text nullable`	Optional
`hourly_min`	`integer nullable`	Numeric sort/filter field
`hourly_max`	`integer nullable`	Numeric sort/filter field
`monthly_min`	`integer nullable`	Numeric detail field
`monthly_max`	`integer nullable`	Numeric detail field
`claim_status`	`enum`	UI trust state
`featured`	`boolean`	Directory ranking flag
`highlight_outcome`	`text nullable`	Card summary line
`review_count_cached`	`integer default 0`	Denormalized cache
`avg_rating_cached`	`numeric nullable`	Denormalized cache
`last_confirmed_at`	`timestamptz nullable`	Trust surface
`published_at`	`timestamptz nullable`	Publish state
`status`	`enum`	`DRAFT`, `REVIEW`, `PUBLISHED`, `ARCHIVED`
`source_system`	`text`	Example: `clay`
`source_record_id`	`text nullable`	Stable Clay row id
`source_synced_at`	`timestamptz nullable`	Last sync timestamp
`raw_payload`	`jsonb nullable`	Raw source payload
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`executive_industries`

Many-to-many join between executives and industries.

Column	Type	Notes
`executive_id`	`uuid fk -> executives.id`	Required
`industry_id`	`uuid fk -> industries.id`	Required
`sort_order`	`integer default 0`	Preserve preferred ordering
`created_at`	`timestamptz`	Audit

Unique constraint:

(executive_id, industry_id)

`executive_specialties`

Many-to-many join between executives and specialties.

Column	Type	Notes
`executive_id`	`uuid fk -> executives.id`	Required
`specialty_id`	`uuid fk -> specialties.id`	Required
`sort_order`	`integer default 0`	Preserve preferred ordering
`created_at`	`timestamptz`	Audit

Unique constraint:

(executive_id, specialty_id)

`executive_experience`

Structured career timeline entries.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`executive_id`	`uuid fk -> executives.id`	Required
`company_name`	`text`	Denormalized for now
`role_title`	`text`	Example: `Interim CFO`
`description`	`text nullable`	Timeline copy
`start_date`	`date nullable`	Partial dates okay
`end_date`	`date nullable`	Null if current
`is_current`	`boolean`	UI convenience
`sort_order`	`integer default 0`	Preserve timeline order
`source_record_id`	`text nullable`	Optional source mapping
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`executive_education`

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`executive_id`	`uuid fk -> executives.id`	Required
`institution`	`text`	Required
`degree`	`text nullable`	Optional
`field`	`text nullable`	Optional
`year_end`	`integer nullable`	Optional
`sort_order`	`integer default 0`	Preserve display order
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`executive_certifications`

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`executive_id`	`uuid fk -> executives.id`	Required
`name`	`text`	Short name, example: `PMP`
`full_name`	`text nullable`	Full credential name
`year_obtained`	`integer nullable`	Optional
`sort_order`	`integer default 0`	Preserve display order
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`executive_reviews`

Optional but valuable. These power trust signals and rating metadata.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`executive_id`	`uuid fk -> executives.id`	Required
`author_name`	`text`	Required
`author_title`	`text nullable`	Optional
`author_company`	`text nullable`	Optional
`rating`	`numeric`	1-5 range
`content`	`text`	Required
`verified`	`boolean`	Source trust
`published_at`	`timestamptz nullable`	Optional moderation
`source_record_id`	`text nullable`	Optional source mapping
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`executive_case_studies`

Structured outcome stories.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`executive_id`	`uuid fk -> executives.id`	Required
`title`	`text`	Required
`situation`	`text nullable`	Optional
`action`	`text nullable`	Optional
`outcome`	`text nullable`	Optional
`metric`	`text nullable`	Example: `Forecast accuracy improved in 6 weeks`
`sort_order`	`integer default 0`	Preserve display order
`source_record_id`	`text nullable`	Optional source mapping
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`executive_verifications`

This stores the trust badges shown on profile pages. It is more flexible than hard-coding three badge fields on the executives table.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`executive_id`	`uuid fk -> executives.id`	Required
`check_type`	`enum`	`IDENTITY`, `CREDENTIALS`, `TRACK_RECORD`
`status`	`enum`	`VERIFIED`, `PENDING`, `UNVERIFIED`
`detail`	`text nullable`	UI detail text
`checked_at`	`timestamptz nullable`	Optional
`source`	`text nullable`	Example: `manual-review`, `clay`, `supabase-claim`
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

Unique constraint:

(executive_id, check_type)

Jobs Board Tables

`jobs`

Canonical job posting record. The current app treats location and compensation as free text in some places, but the database should store structured fields and derive summary strings from them.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`slug`	`text unique`	Public URL slug
`title`	`text`	Required
`company_name`	`text`	Required
`location_summary`	`text`	Display string, example: `Remote (US) with monthly Boston travel`
`primary_location_id`	`uuid fk -> locations.id nullable`	Use when cleanly mappable
`role_id`	`uuid fk -> roles.id`	Required
`summary`	`text`	Card + hero copy
`description`	`text`	Full detail page + JobPosting schema
`apply_url`	`text`	Required
`posted_at`	`timestamptz`	Required
`employment_type`	`enum`	Required
`remote_type`	`enum nullable`	Prefer explicit value over inference
`compensation_summary`	`text nullable`	Human-readable display
`comp_min_amount`	`integer nullable`	Structured sort/filter field
`comp_max_amount`	`integer nullable`	Structured sort/filter field
`comp_period`	`text nullable`	Example: `hour`, `month`, `year`
`comp_currency`	`text nullable`	Example: `USD`
`curated_at`	`timestamptz nullable`	Last reviewed or editorial approval date
`community_topic_hint`	`text nullable`	Optional community routing
`published_at`	`timestamptz nullable`	Publish state
`status`	`enum`	`DRAFT`, `REVIEW`, `PUBLISHED`, `ARCHIVED`
`source_system`	`text`	Example: `clay`
`source_record_id`	`text nullable`	Stable Clay row id
`source_synced_at`	`timestamptz nullable`	Last sync timestamp
`raw_payload`	`jsonb nullable`	Raw source payload
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`job_industries`

Many-to-many join between jobs and industries.

Column	Type	Notes
`job_id`	`uuid fk -> jobs.id`	Required
`industry_id`	`uuid fk -> industries.id`	Required
`sort_order`	`integer default 0`	Preserve preferred ordering
`created_at`	`timestamptz`	Audit

Unique constraint:

(job_id, industry_id)

`job_responsibilities`

Separate table if we want fully structured editability and source tracking.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`job_id`	`uuid fk -> jobs.id`	Required
`body`	`text`	Required
`sort_order`	`integer default 0`	Preserve display order
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

`job_requirements`

Separate table if we want fully structured editability and source tracking.

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`job_id`	`uuid fk -> jobs.id`	Required
`body`	`text`	Required
`sort_order`	`integer default 0`	Preserve display order
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

Ownership and Trust Workflow Tables

These align with the existing claim flow already present in the app.

`profile_claim_requests`

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`executive_id`	`uuid fk -> executives.id`	Required
`user_id`	`uuid nullable`	Supabase auth user id if available
`email`	`text nullable`	Useful for review
`status`	`text`	Example: `RECEIVED`, `IN_REVIEW`, `APPROVED`, `REJECTED`
`submitted_payload`	`jsonb`	Request body
`review_notes`	`text nullable`	Internal ops notes
`created_at`	`timestamptz`	Audit
`reviewed_at`	`timestamptz nullable`	Audit

`profile_ownerships`

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`executive_id`	`uuid fk -> executives.id`	Required
`user_id`	`uuid`	Supabase auth user id
`status`	`text`	Example: `ACTIVE`, `REVOKED`, `PENDING`
`verified_at`	`timestamptz nullable`	Claim verification timestamp
`created_at`	`timestamptz`	Audit
`ended_at`	`timestamptz nullable`	Audit

Clay Ingestion Tables

These keep source synchronization clean and auditable.

`ingestion_runs`

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`source`	`text`	Example: `clay`
`entity_type`	`text`	Example: `executive`, `job`
`status`	`enum`	`PENDING`, `SUCCEEDED`, `FAILED`, `SKIPPED`
`started_at`	`timestamptz`	Audit
`finished_at`	`timestamptz nullable`	Audit
`metrics`	`jsonb nullable`	Counts, warnings, timings
`created_at`	`timestamptz`	Audit

`ingestion_records`

Column	Type	Notes
`id`	`uuid pk`	Internal primary key
`run_id`	`uuid fk -> ingestion_runs.id`	Required
`entity_type`	`text`	`executive`, `job`, etc.
`source_record_id`	`text nullable`	Clay row id
`target_id`	`uuid nullable`	Canonical row id
`action`	`text`	`INSERT`, `UPDATE`, `SKIP`, `DELETE`, `ERROR`
`status`	`enum`	`PENDING`, `SUCCEEDED`, `FAILED`, `SKIPPED`
`error_message`	`text nullable`	Failure details
`raw_payload`	`jsonb nullable`	Raw input row
`normalized_payload`	`jsonb nullable`	Post-transform row
`created_at`	`timestamptz`	Audit
`updated_at`	`timestamptz`	Audit

Minimum Clay Support by Entity

This section is the reverse-engineering checklist. It shows what Clay must provide for a useful first import.

Executive Minimum Viable Fields

External unique record id
First name
Last name
Public name or ability to derive one
Headline
Role
Primary location
At least one industry
Years of experience or ability to approximate
Availability
Optional hourly or monthly rates
Bio
Optional LinkedIn URL
Optional website URL

Executive Nice-to-Have Fields

Photo URL
Specialties
Experience timeline
Education
Certifications
Reviews
Case studies
Last confirmed date
Trust or verification inputs
Featured ranking signal

Executive Fields We Can Derive In-App

slug
full_name
claim_status defaulting to UNCLAIMED
review_count_cached
avg_rating_cached
highlight_outcome fallback
similarExecutives

Job Minimum Viable Fields

External unique record id
Title
Company name
Role
At least one industry
Location display string
Summary
Description
Apply URL
Posted date
Employment type

Job Nice-to-Have Fields

Structured location
Explicit remote type
Responsibilities
Requirements
Structured compensation min and max
Compensation period
Curated or reviewed timestamp
Community topic hint

Job Fields We Can Derive In-App

slug
compensation_summary from structured compensation
remote_type from location text if missing
Related jobs

Fields That Probably Do Not Belong in Clay

These are better owned by the app or ops layer.

Publish status
Editorial review status
Featured flags unless editorially managed in Clay
Claim requests
Profile ownership records
Verification badge decisions
Ingestion run metadata
Search stats and rollups

Proposed Canonical JSON Shape for an Executive

{
  "slug": "sarah-chen-fractional-cfo",
  "firstName": "Sarah",
  "lastName": "Chen",
  "fullName": "Sarah Chen",
  "headline": "Fractional CFO for Series A-C SaaS and Fintech teams",
  "bio": "Sarah helps founders move from reporting to real financial control.",
  "roleSlug": "fractional-cfo",
  "locationSlug": "austin-tx",
  "industrySlugs": ["saas", "fintech"],
  "specialtySlugs": ["forecasting", "fundraising"],
  "yearsExperience": 18,
  "availability": "AVAILABLE",
  "remoteAvailable": true,
  "travelWillingness": "NATIONAL",
  "minimumEngagement": "3 months",
  "linkedinUrl": "https://www.linkedin.com/in/sarah-chen-fractional-cfo",
  "websiteUrl": "https://sarahchen.example.com",
  "rates": {
    "hourlyMin": 275,
    "hourlyMax": 420,
    "monthlyMin": 9000,
    "monthlyMax": 18000
  },
  "claimStatus": "UNCLAIMED",
  "featured": false,
  "highlightOutcome": "Improved forecasting confidence before board meetings.",
  "experience": [],
  "education": [],
  "certifications": [],
  "reviews": [],
  "caseStudies": [],
  "verificationBadges": []
}

Proposed Canonical JSON Shape for a Job

{
  "slug": "series-b-saas-fractional-cfo-remote-us",
  "title": "Fractional CFO (Series B SaaS)",
  "companyName": "Northbound Analytics",
  "locationSummary": "Remote (US)",
  "primaryLocationSlug": null,
  "roleSlug": "fractional-cfo",
  "industrySlugs": ["saas", "ai"],
  "summary": "Own forecasting, board reporting, and fundraising readiness.",
  "description": "Northbound Analytics is hiring a part-time fractional CFO...",
  "responsibilities": [
    "Lead board and investor financial narratives."
  ],
  "requirements": [
    "10+ years in senior finance leadership."
  ],
  "applyUrl": "https://example.com/jobs/series-b-saas-fractional-cfo",
  "postedAt": "2026-02-20T00:00:00.000Z",
  "employmentType": "PART_TIME",
  "remoteType": "REMOTE",
  "compensationSummary": "$12,000-$18,000/month",
  "compMinAmount": 12000,
  "compMaxAmount": 18000,
  "compPeriod": "month",
  "compCurrency": "USD",
  "curatedAt": null,
  "communityTopicHint": null
}

Recommended First Implementation Scope

Implement taxonomy tables first
Implement executives, executive_industries, executive_specialties
Implement jobs and job_industries
Add experience, reviews, case_studies, and verifications after the first Clay mapping pass
Keep raw_payload on both executives and jobs from day one
Keep ingestion_runs and ingestion_records from day one

Questions to Answer Before Building

Will Clay provide normalized roles and industries, or do we need mapping tables?
Will Clay provide one row per executive or one row per executive-role-market combination?
Will jobs be curated manually after import, or should Clay control publish state?
Do we want responsibilities and requirements as arrays in Clay, or as multi-row related records?
Do we want to trust Clay compensation values enough to support sorting and filtering?
Do we want to store executive trust and verification fields in Clay, or keep that purely operational in the app?

Bottom Line

Clay should be treated as the source of many content fields, but not the final app schema.

The canonical model should stay normalized and app-oriented:

taxonomy tables for shared lookups
one core executives table plus child tables for rich profile data
one core jobs table plus child tables for structured job bullets
separate trust, ownership, and ingestion tables
derived search and market pages built from query logic, not stored as primary records