Data Modeling for Venture Capital

-- Investors table (the firms)
CREATE TABLE investors (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL, -- "Sequoia Capital"
  type TEXT, -- VC, Angel, Corporate
  headquarters TEXT,
  focus_areas TEXT[]
);

-- Funds table (specific funds within firms)
CREATE TABLE funds (
  id UUID PRIMARY KEY,
  investor_id UUID REFERENCES investors(id),
  name TEXT NOT NULL, -- "Sequoia Capital Fund XIV"
  vintage_year INTEGER,
  fund_size NUMERIC,
  status TEXT -- Active, Deployed, Closed
);

-- Funding round participations (which funds invested in which rounds)
CREATE TABLE funding_round_participants (
  id UUID PRIMARY KEY,
  funding_round_id UUID REFERENCES funding_rounds(id),
  fund_id UUID REFERENCES funds(id), -- Links to specific fund, not just investor
  amount_invested NUMERIC,
  is_lead BOOLEAN
);

-- Companies table
CREATE TABLE companies (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  website TEXT,
  description TEXT,
  founded_date DATE,
  location TEXT,
  sector TEXT,
  stage TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

-- Deals table
CREATE TABLE deals (
  id UUID PRIMARY KEY,
  company_id UUID REFERENCES companies(id), -- Foreign key to company
  deal_name TEXT, -- "Stripe Series A"
  status TEXT, -- Sourced, Meeting, Diligence, IC, Term Sheet, Closed, Passed
  source TEXT, -- How we found this company (warm intro, Harmonic, conference)
  lead_investor UUID REFERENCES people(id), -- Who at our fund is leading
  initial_contact_date DATE,
  last_activity_date DATE,
  investment_amount NUMERIC,
  ownership_percentage NUMERIC,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

-- Roles table (separate from people and companies)
CREATE TABLE roles (
  id UUID PRIMARY KEY,
  person_id UUID REFERENCES people(id),
  company_id UUID REFERENCES companies(id),
  title TEXT,
  role_type TEXT, -- Employee, Founder, Advisor, Board Member
  start_date DATE,
  end_date DATE, -- NULL if current
  is_current BOOLEAN,
  created_at TIMESTAMP
);

-- Education table (separate from people)
CREATE TABLE education (
  id UUID PRIMARY KEY,
  person_id UUID REFERENCES people(id),
  institution TEXT,
  degree TEXT, -- BS, MS, MBA, PhD
  field_of_study TEXT,
  start_date DATE,
  end_date DATE,
  created_at TIMESTAMP
);

-- Append-only company data from PitchBook
CREATE TABLE pitchbook_company_changes (
  id UUID PRIMARY KEY,
  company_id UUID,
  fetched_at TIMESTAMP, -- When we got this data
  raw_json JSONB, -- Raw response from PitchBook API
  name TEXT,
  description TEXT,
  employee_count INTEGER,
  funding_total NUMERIC,
  -- ... other fields
  created_at TIMESTAMP
);

-- dbt model: models/intermediate/pitchbook_companies.sql
-- Gets the latest record per company from append-only staging data

SELECT DISTINCT ON (company_id)
  company_id,
  name,
  description,
  employee_count,
  funding_total,
  fetched_at
FROM {{ ref('pitchbook_company_changes') }}
ORDER BY company_id, fetched_at DESC

-- models/staging/pitchbook/stg_pitchbook__company_changes.sql
-- Takes raw JSON from PitchBook API, extracts fields

SELECT
  raw_json->>'id' AS external_id,
  (raw_json->>'name')::text AS name,
  (raw_json->>'description')::text AS description,
  (raw_json->>'employeeCount')::integer AS employee_count,
  (raw_json->>'fundingTotal')::numeric AS funding_total,
  fetched_at,
  created_at
FROM raw_pitchbook_data

-- models/intermediate/pitchbook_companies.sql
SELECT DISTINCT ON (external_id)
  external_id,
  name,
  description,
  employee_count,
  funding_total,
  fetched_at AS pitchbook_updated_at
FROM {{ ref('stg_pitchbook__company_changes') }}
ORDER BY external_id, fetched_at DESC

-- models/marts/companies_enriched.sql
-- Combines multiple sources, choosing best value for each field

SELECT
  c.id,
  COALESCE(pb.name, li.name, c.name) AS name,
  pb.description AS description, -- PitchBook best for descriptions
  li.employee_count AS employee_count, -- LinkedIn best for headcount
  pb.funding_total AS funding_total, -- PitchBook best for funding
  c.website,
  c.sector
FROM companies c
LEFT JOIN pitchbook_companies pb ON c.pitchbook_id = pb.external_id
LEFT JOIN linkedin_companies li ON c.linkedin_id = li.external_id

-- models/marts/companies_enriched.sql
-- Keeps separate columns for each source

SELECT
  c.id,
  c.name,
  pb.description AS description_pitchbook,
  li.description AS description_linkedin,
  pb.employee_count AS employee_count_pitchbook,
  li.employee_count AS employee_count_linkedin,
  pb.funding_total AS funding_total_pitchbook,
  pb.updated_at AS pitchbook_updated_at,
  li.updated_at AS linkedin_updated_at
FROM companies c
LEFT JOIN pitchbook_companies pb ON c.pitchbook_id = pb.external_id
LEFT JOIN linkedin_companies li ON c.linkedin_id = li.external_id