Cameron Burke Real Estate SaaS Platform¶

-- Users and Authentication
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(20),
    company_name VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    last_login_at TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT true,
    stripe_customer_id VARCHAR(255),
    subscription_tier VARCHAR(50) DEFAULT 'free'
);

CREATE TABLE user_preferences (
    user_id UUID PRIMARY KEY REFERENCES users(id),
    default_market VARCHAR(100),
    target_coc_return DECIMAL(5,2) DEFAULT 15.00,
    min_cash_flow_per_door DECIMAL(10,2) DEFAULT 100.00,
    max_arv DECIMAL(12,2),
    max_ltv_percent DECIMAL(5,2) DEFAULT 70.00,
    min_flip_profit DECIMAL(12,2) DEFAULT 25000.00,
    preferred_strategies JSONB DEFAULT '["flip", "brrrr", "rental"]',
    notification_settings JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Subscriptions (Stripe Integration)
CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    stripe_subscription_id VARCHAR(255) UNIQUE,
    tier VARCHAR(50) NOT NULL, -- 'free', 'solo', 'pro', 'team'
    status VARCHAR(50) NOT NULL, -- 'active', 'canceled', 'past_due', 'trialing'
    current_period_start TIMESTAMPTZ,
    current_period_end TIMESTAMPTZ,
    cancel_at_period_end BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Properties
CREATE TABLE properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zip_code VARCHAR(10) NOT NULL,
    county VARCHAR(100),
    property_type VARCHAR(50), -- 'sfr', 'duplex', 'triplex', 'quad', 'multi', 'land'
    bedrooms INTEGER,
    bathrooms DECIMAL(3,1),
    sqft INTEGER,
    lot_size_sqft INTEGER,
    year_built INTEGER,
    parcel_number VARCHAR(50),
    latitude DECIMAL(10,7),
    longitude DECIMAL(10,7),
    current_value_estimate DECIMAL(12,2),
    arv_estimate DECIMAL(12,2),
    status VARCHAR(50) DEFAULT 'prospect', -- 'prospect', 'under_contract', 'owned', 'sold', 'archived'
    source VARCHAR(100), -- 'mls', 'wholesaler', 'direct_mail', 'driving_for_dollars', 'facebook', 'craigslist'
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Deals (Core Transaction Tracking)
CREATE TABLE deals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    property_id UUID REFERENCES properties(id),
    deal_name VARCHAR(255),
    strategy VARCHAR(50) NOT NULL, -- 'flip', 'brrrr', 'rental', 'wholesale', 'subject_to', 'seller_finance'
    status VARCHAR(50) DEFAULT 'analyzing', -- 'analyzing', 'offer_sent', 'under_contract', 'in_rehab', 'listed', 'sold', 'holding', 'dead'

    -- Purchase Info
    asking_price DECIMAL(12,2),
    offer_price DECIMAL(12,2),
    purchase_price DECIMAL(12,2),
    purchase_date DATE,

    -- ARV & Rehab
    arv DECIMAL(12,2),
    rehab_estimate DECIMAL(12,2),
    rehab_actual DECIMAL(12,2),

    -- Costs (70% Rule Components)
    buying_closing_costs DECIMAL(12,2), -- BSCCC
    holding_costs DECIMAL(12,2), -- HOLDCO
    selling_closing_costs DECIMAL(12,2), -- SSCC

    -- Calculated Fields
    mao DECIMAL(12,2), -- Maximum Allowable Offer
    all_in_cost DECIMAL(12,2),
    expected_profit DECIMAL(12,2),
    actual_profit DECIMAL(12,2),

    -- Sale Info (for flips/wholesales)
    list_price DECIMAL(12,2),
    sale_price DECIMAL(12,2),
    sale_date DATE,
    days_on_market INTEGER,

    -- Rental Info (for BRRRR/rentals)
    monthly_rent DECIMAL(10,2),
    refinance_amount DECIMAL(12,2),
    cash_left_in_deal DECIMAL(12,2),

    -- AI Scoring
    deal_score_id UUID,

    -- Metadata
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Deal Analyses (Multiple scenarios per deal)
CREATE TABLE deal_analyses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deal_id UUID REFERENCES deals(id),
    analysis_name VARCHAR(100) DEFAULT 'Primary',

    -- Input Assumptions
    purchase_price DECIMAL(12,2),
    arv DECIMAL(12,2),
    rehab_cost DECIMAL(12,2),

    -- Flip Analysis
    gross_profit DECIMAL(12,2),
    total_costs DECIMAL(12,2),
    net_profit DECIMAL(12,2),
    roi_percent DECIMAL(6,2),

    -- Rental Analysis
    monthly_rent DECIMAL(10,2),
    vacancy_rate DECIMAL(5,2) DEFAULT 5.00,
    property_mgmt_rate DECIMAL(5,2) DEFAULT 10.00,
    capex_reserve_rate DECIMAL(5,2) DEFAULT 5.00,
    insurance_monthly DECIMAL(10,2),
    taxes_monthly DECIMAL(10,2),
    hoa_monthly DECIMAL(10,2) DEFAULT 0,
    utilities_monthly DECIMAL(10,2) DEFAULT 0,

    -- Calculated Rental Metrics
    gross_monthly_income DECIMAL(10,2),
    operating_expenses DECIMAL(10,2),
    noi_monthly DECIMAL(10,2),
    noi_annual DECIMAL(12,2),
    cap_rate DECIMAL(5,2),
    cash_flow_monthly DECIMAL(10,2),
    cash_on_cash_return DECIMAL(5,2),

    -- BRRRR Specific
    refinance_ltv DECIMAL(5,2) DEFAULT 75.00,
    refinance_rate DECIMAL(5,3),
    refinance_term_years INTEGER DEFAULT 30,
    refinance_amount DECIMAL(12,2),
    cash_out DECIMAL(12,2),
    cash_left_in DECIMAL(12,2),

    -- Financing
    down_payment_percent DECIMAL(5,2),
    loan_amount DECIMAL(12,2),
    interest_rate DECIMAL(5,3),
    loan_term_years INTEGER,
    monthly_piti DECIMAL(10,2),

    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- AI Deal Scoring
CREATE TABLE deal_scores (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deal_id UUID UNIQUE REFERENCES deals(id),
    overall_score VARCHAR(10) NOT NULL, -- 'green', 'yellow', 'red'
    confidence_level DECIMAL(5,2), -- 0-100%

    -- Category Scores
    arv_score VARCHAR(10),
    rehab_score VARCHAR(10),
    market_score VARCHAR(10),
    profit_score VARCHAR(10),
    risk_score VARCHAR(10),

    -- JSON breakdown
    score_breakdown JSONB,

    calculated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Score Explanations (Links to educational content)
CREATE TABLE score_explanations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deal_score_id UUID REFERENCES deal_scores(id),
    category VARCHAR(50), -- 'arv', 'rehab', 'market', 'profit', 'risk'
    score VARCHAR(10),
    explanation TEXT NOT NULL,
    recommendation TEXT,
    video_module_id UUID REFERENCES video_modules(id),
    video_timestamp_seconds INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Scope of Work
CREATE TABLE scope_of_work (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deal_id UUID REFERENCES deals(id),
    property_id UUID REFERENCES properties(id),
    version INTEGER DEFAULT 1,
    status VARCHAR(50) DEFAULT 'draft', -- 'draft', 'sent_to_contractor', 'approved', 'in_progress', 'completed'
    total_estimate DECIMAL(12,2),
    total_actual DECIMAL(12,2),
    estimated_days INTEGER,
    actual_days INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- SOW Line Items
CREATE TABLE sow_line_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sow_id UUID REFERENCES scope_of_work(id),
    category VARCHAR(100) NOT NULL, -- 'roofing', 'hvac', 'plumbing', 'electrical', 'flooring', etc.
    subcategory VARCHAR(100),
    description TEXT NOT NULL,
    quantity DECIMAL(10,2),
    unit VARCHAR(50), -- 'sqft', 'each', 'linear_ft', 'lump_sum'
    unit_price DECIMAL(10,2),
    estimated_cost DECIMAL(10,2),
    actual_cost DECIMAL(10,2),
    contractor_id UUID REFERENCES contacts(id),
    status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'in_progress', 'completed', 'skipped'
    notes TEXT,
    sort_order INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Pricing Database (Cameron's OK market prices as baseline)
CREATE TABLE pricing_database (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    category VARCHAR(100) NOT NULL,
    subcategory VARCHAR(100),
    item_name VARCHAR(255) NOT NULL,
    description TEXT,
    unit VARCHAR(50),
    min_price DECIMAL(10,2),
    max_price DECIMAL(10,2),
    typical_price DECIMAL(10,2),
    market VARCHAR(100) DEFAULT 'oklahoma', -- Base market, users can add their own
    user_id UUID REFERENCES users(id), -- NULL for system defaults
    source VARCHAR(100), -- 'cameron_methodology', 'user_input', 'system'
    last_updated TIMESTAMPTZ DEFAULT NOW(),
    is_active BOOLEAN DEFAULT true,
    UNIQUE(category, subcategory, item_name, market, user_id)
);

-- Contacts (Agents, Wholesalers, Contractors, Lenders)
CREATE TABLE contacts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    contact_type VARCHAR(50) NOT NULL, -- 'agent', 'wholesaler', 'contractor', 'lender', 'property_manager', 'seller', 'buyer', 'other'
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    company_name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    secondary_phone VARCHAR(20),
    address TEXT,

    -- Contractor-specific
    contractor_specialty JSONB, -- ['roofing', 'hvac', 'plumbing']
    license_number VARCHAR(100),
    insurance_expiry DATE,

    -- Agent-specific
    mls_id VARCHAR(50),
    brokerage VARCHAR(255),

    -- Wholesaler-specific
    avg_deal_fee DECIMAL(10,2),
    markets_served JSONB,

    -- Lender-specific
    lender_type VARCHAR(50), -- 'hard_money', 'private', 'bank', 'dscr'
    max_ltv DECIMAL(5,2),
    typical_rate DECIMAL(5,3),
    min_credit_score INTEGER,

    -- Tracking
    source VARCHAR(100), -- 'facebook', 'referral', 'mls_listing', 'investor_lift'
    tags JSONB,
    notes TEXT,
    rating INTEGER CHECK (rating >= 1 AND rating <= 5),
    status VARCHAR(50) DEFAULT 'active', -- 'active', 'dormant', 'dnc'
    last_contact_date DATE,
    next_followup_date DATE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Contact Interactions Log
CREATE TABLE contact_interactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    contact_id UUID REFERENCES contacts(id),
    user_id UUID REFERENCES users(id),
    interaction_type VARCHAR(50), -- 'call', 'text', 'email', 'meeting', 'deal_sent', 'deal_closed'
    direction VARCHAR(10), -- 'inbound', 'outbound'
    subject VARCHAR(255),
    content TEXT,
    outcome VARCHAR(100),
    deal_id UUID REFERENCES deals(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Rental Portfolio
CREATE TABLE portfolios (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    name VARCHAR(255) DEFAULT 'My Portfolio',
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE rental_properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portfolio_id UUID REFERENCES portfolios(id),
    property_id UUID REFERENCES properties(id),
    acquisition_date DATE,
    acquisition_price DECIMAL(12,2),
    current_value DECIMAL(12,2),
    mortgage_balance DECIMAL(12,2),
    equity DECIMAL(12,2),
    monthly_rent DECIMAL(10,2),
    monthly_mortgage DECIMAL(10,2),
    monthly_expenses DECIMAL(10,2),
    monthly_cash_flow DECIMAL(10,2),
    annual_noi DECIMAL(12,2),
    cap_rate DECIMAL(5,2),
    coc_return DECIMAL(5,2),
    status VARCHAR(50) DEFAULT 'active', -- 'active', 'vacant', 'renovating', 'for_sale'
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rental_property_id UUID REFERENCES rental_properties(id),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    lease_start DATE,
    lease_end DATE,
    monthly_rent DECIMAL(10,2),
    security_deposit DECIMAL(10,2),
    status VARCHAR(50) DEFAULT 'active', -- 'active', 'notice_given', 'eviction', 'past'
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE income_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rental_property_id UUID REFERENCES rental_properties(id),
    tenant_id UUID REFERENCES tenants(id),
    record_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    income_type VARCHAR(50), -- 'rent', 'late_fee', 'pet_deposit', 'application_fee', 'other'
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE expense_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rental_property_id UUID REFERENCES rental_properties(id),
    record_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    expense_type VARCHAR(50), -- 'mortgage', 'insurance', 'taxes', 'repairs', 'capex', 'utilities', 'hoa', 'property_mgmt', 'other'
    description TEXT,
    contractor_id UUID REFERENCES contacts(id),
    is_recurring BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Capital Tracking (Cameron's Capital Management Stack)
CREATE TABLE capital_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    record_date DATE NOT NULL,
    total_capital DECIMAL(14,2),
    available_capital DECIMAL(14,2),
    deployed_capital DECIMAL(14,2),
    pending_capital DECIMAL(14,2), -- Committed but not yet deployed
    reserved_capital DECIMAL(14,2), -- Emergency/reserves
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE capital_deployments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    capital_record_id UUID REFERENCES capital_records(id),
    deal_id UUID REFERENCES deals(id),
    description VARCHAR(255),
    amount DECIMAL(12,2) NOT NULL,
    deployment_type VARCHAR(50), -- 'purchase', 'rehab', 'holding', 'refinance_out'
    deployed_date DATE,
    expected_return_date DATE,
    actual_return_date DATE,
    return_amount DECIMAL(12,2),
    status VARCHAR(50) DEFAULT 'deployed', -- 'pending', 'deployed', 'returned', 'lost'
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Comparable Sales
CREATE TABLE comps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID REFERENCES properties(id),
    deal_id UUID REFERENCES deals(id),
    address VARCHAR(255) NOT NULL,
    city VARCHAR(100),
    state VARCHAR(2),
    zip_code VARCHAR(10),

    -- Property Details
    bedrooms INTEGER,
    bathrooms DECIMAL(3,1),
    sqft INTEGER,
    lot_size_sqft INTEGER,
    year_built INTEGER,
    property_type VARCHAR(50),

    -- Sale Info
    sale_price DECIMAL(12,2),
    sale_date DATE,
    price_per_sqft DECIMAL(10,2),
    days_on_market INTEGER,

    -- Condition
    condition VARCHAR(50), -- 'as_is', 'dated', 'average', 'updated', 'fully_renovated'
    comp_quality VARCHAR(50), -- 'excellent', 'good', 'fair', 'stretch'

    -- Distance/Relevance
    distance_miles DECIMAL(5,2),
    in_same_subdivision BOOLEAN DEFAULT false,

    -- Adjustments (for manual comp analysis)
    location_adjustment DECIMAL(12,2) DEFAULT 0,
    condition_adjustment DECIMAL(12,2) DEFAULT 0,
    size_adjustment DECIMAL(12,2) DEFAULT 0,
    age_adjustment DECIMAL(12,2) DEFAULT 0,
    adjusted_price DECIMAL(12,2),

    -- Status
    comp_type VARCHAR(20), -- 'sold', 'pending', 'active'
    source VARCHAR(50), -- 'mls', 'zillow', 'redfin', 'manual'
    mls_number VARCHAR(50),

    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Market Analysis Data
CREATE TABLE market_data (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    market_name VARCHAR(255) NOT NULL, -- 'Oklahoma City, OK' or zip code
    data_date DATE NOT NULL,

    -- Supply Metrics
    active_listings INTEGER,
    pending_listings INTEGER,
    monthly_sales INTEGER,
    months_of_inventory DECIMAL(4,1), -- MOI

    -- Price Metrics
    median_list_price DECIMAL(12,2),
    median_sale_price DECIMAL(12,2),
    avg_price_per_sqft DECIMAL(10,2),

    -- Time Metrics
    avg_dom INTEGER, -- Days on Market
    median_dom INTEGER,

    -- Trends
    yoy_price_change DECIMAL(5,2), -- Year over year %
    mom_price_change DECIMAL(5,2), -- Month over month %

    source VARCHAR(50),
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(market_name, data_date)
);

-- Cameron's Methodology Content (for RAG)
CREATE TABLE methodology_content (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255) NOT NULL,
    content_type VARCHAR(50), -- 'module', 'worksheet', 'template', 'coaching_call'
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE video_modules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    methodology_id UUID REFERENCES methodology_content(id),
    title VARCHAR(255) NOT NULL,
    module_number INTEGER,
    description TEXT,
    duration_seconds INTEGER,
    video_url VARCHAR(500),
    thumbnail_url VARCHAR(500),
    transcript TEXT,

    -- RAG Embeddings
    embedding_id VARCHAR(255), -- Reference to vector store

    -- Topics for linking to deal scores
    topics JSONB, -- ['arv', 'comps', 'rehab_estimation', 'financing']

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE worksheets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    methodology_id UUID REFERENCES methodology_content(id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    template_url VARCHAR(500),
    topics JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- RAG Embeddings Index
CREATE TABLE rag_embeddings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_type VARCHAR(50), -- 'video_transcript', 'worksheet', 'coaching_call'
    source_id UUID,
    chunk_index INTEGER,
    content TEXT NOT NULL,
    embedding vector(768), -- Using pgvector
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable pgvector
CREATE EXTENSION IF NOT EXISTS vector;
CREATE INDEX ON rag_embeddings USING ivfflat (embedding vector_cosine_ops);

-- Property Inspections
CREATE TABLE inspections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID REFERENCES properties(id),
    deal_id UUID REFERENCES deals(id),
    inspection_date DATE,
    inspector_name VARCHAR(255),
    inspection_type VARCHAR(50), -- 'initial_walkthrough', 'formal_inspection', 'rehab_progress', 'final'
    overall_condition VARCHAR(50), -- 'good', 'fair', 'poor', 'gut_job'
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE inspection_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    inspection_id UUID REFERENCES inspections(id),
    category VARCHAR(100), -- 'roof', 'hvac', 'plumbing', 'electrical', 'foundation', 'interior', 'exterior'
    item_name VARCHAR(255),
    condition VARCHAR(50), -- 'good', 'fair', 'replace', 'repair', 'critical'
    estimated_cost DECIMAL(10,2),
    notes TEXT,
    sort_order INTEGER
);

-- Property Photos
CREATE TABLE photos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID REFERENCES properties(id),
    deal_id UUID REFERENCES deals(id),
    inspection_id UUID REFERENCES inspections(id),
    photo_url VARCHAR(500) NOT NULL,
    thumbnail_url VARCHAR(500),
    category VARCHAR(100), -- 'exterior', 'kitchen', 'bathroom', 'bedroom', 'living', 'basement', 'roof', 'damage'
    caption TEXT,
    photo_date DATE,
    is_before BOOLEAN, -- For before/after tracking
    sort_order INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Contracts
CREATE TABLE contracts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deal_id UUID REFERENCES deals(id),
    contract_type VARCHAR(50), -- 'purchase', 'wholesale_ab', 'wholesale_bc', 'listing', 'contractor', 'lease'
    status VARCHAR(50) DEFAULT 'draft', -- 'draft', 'sent', 'signed', 'executed', 'expired', 'cancelled'

    -- Key Dates
    effective_date DATE,
    due_diligence_end DATE,
    closing_date DATE,
    expiration_date DATE,

    -- Financials
    contract_price DECIMAL(12,2),
    earnest_money DECIMAL(10,2),
    assignment_fee DECIMAL(10,2), -- For wholesale deals

    -- Parties
    buyer_id UUID REFERENCES contacts(id),
    seller_id UUID REFERENCES contacts(id),

    -- Documents
    document_url VARCHAR(500),
    signed_document_url VARCHAR(500),

    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Deal Timeline/Activity Log
CREATE TABLE timeline_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deal_id UUID REFERENCES deals(id),
    event_date TIMESTAMPTZ DEFAULT NOW(),
    event_type VARCHAR(100), -- 'status_change', 'offer_sent', 'inspection', 'rehab_start', 'listed', 'showing', 'offer_received', 'closed'
    title VARCHAR(255),
    description TEXT,
    created_by UUID REFERENCES users(id),
    metadata JSONB
);

-- Deal Expenses (Actuals)
CREATE TABLE expenses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deal_id UUID REFERENCES deals(id),
    expense_date DATE NOT NULL,
    category VARCHAR(100), -- 'acquisition', 'rehab', 'holding', 'selling', 'other'
    subcategory VARCHAR(100),
    description TEXT,
    amount DECIMAL(12,2) NOT NULL,
    vendor_id UUID REFERENCES contacts(id),
    receipt_url VARCHAR(500),
    is_billable BOOLEAN DEFAULT true,
    sow_line_item_id UUID REFERENCES sow_line_items(id),
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

React 18 + TypeScript
├── Next.js 14 (App Router)
├── TailwindCSS + shadcn/ui
├── React Query (data fetching)
├── Zustand (state management)
├── React Hook Form + Zod
└── Recharts (analytics)

Python FastAPI
├── SQLAlchemy 2.0 (ORM)
├── Alembic (migrations)
├── Pydantic v2 (validation)
├── Celery + Redis (background jobs)
└── pgvector (RAG embeddings)

PostgreSQL 16
├── pgvector extension (embeddings)
├── pg_cron (scheduled jobs)
└── Row-Level Security (multi-tenant)

RAG Pipeline
├── LM Studio (local embeddings - nomic-embed-text-v1.5, 768d)
├── Qdrant Cloud (vector storage - existing infrastructure)
├── Claude API (deal analysis, explanations)
└── Whisper (video transcriptions)

Cloud Services
├── Vercel (frontend hosting)
├── Railway or Render (backend)
├── Supabase or Neon (PostgreSQL)
├── Cloudflare R2 (file storage)
├── Stripe (payments)
└── Resend (transactional email)

// Stripe Product IDs
const PRODUCTS = {
  solo: 'prod_XXXXX',
  pro: 'prod_XXXXX',
  team: 'prod_XXXXX'
};

// Webhook Events to Handle
const WEBHOOK_EVENTS = [
  'checkout.session.completed',
  'customer.subscription.updated',
  'customer.subscription.deleted',
  'invoice.payment_failed',
  'invoice.payment_succeeded'
];

MAO = ARV × 0.7 - REHAB - BSCCC - HOLDCO - SSCC

Where:
- ARV = After Repair Value
- REHAB = Renovation costs
- BSCCC = Buying-Side Closing Costs & Carrying
- HOLDCO = Holding Costs
- SSCC = Selling-Side Closing Costs & Commissions

NOI = Gross Rent – Operating Expenses
Cap Rate = NOI ÷ Purchase Price
Cash-on-Cash = Annual Cash Flow ÷ Cash Invested
LTV = Loan Amount ÷ ARV
MOI = Active Listings ÷ Monthly Sales
DTI = Monthly Debt ÷ Monthly Income