Database Design

Overview

Design efficient database schemas for real estate applications.

Recommended Schema

Leads Table

CREATE TABLE leads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  status TEXT DEFAULT 'new',
  source TEXT,
  property_address TEXT,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_leads_status ON leads(status);
CREATE INDEX idx_leads_created ON leads(created_at DESC);

Properties Table

CREATE TABLE properties (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID REFERENCES leads(id),
  address TEXT NOT NULL,
  city TEXT,
  state TEXT,
  zip TEXT,
  bedrooms INT,
  bathrooms DECIMAL,
  sqft INT,
  year_built INT,
  estimated_value DECIMAL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Offers Table

CREATE TABLE offers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID REFERENCES leads(id),
  property_id UUID REFERENCES properties(id),
  amount DECIMAL NOT NULL,
  status TEXT DEFAULT 'pending',
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Supabase Setup

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_KEY!
);

// Query leads with properties
const { data: leads } = await supabase
  .from('leads')
  .select(

    ,

    properties (),
    offers (*)
  )
  .eq('status', 'active')
  .order('created_at', { ascending: false });