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