CREATE TABLE IF NOT EXISTS leads (
id INTEGER PRIMARY KEY AUTOINCREMENT,
-- Basic Info
name TEXT NOT NULL,
email TEXT,
phone TEXT,
message TEXT,
-- Routing Metadata
domain TEXT, -- e.g., 'northsidepoint.com', 'atlantajunkhaulers.com'
location TEXT, -- e.g., 'atlanta', 'marietta'
source TEXT, -- e.g., 'contact-form', 'landing-page', 'call'
status TEXT DEFAULT 'new', -- e.g., 'new', 'contacted', 'qualified', 'closed'
service_key TEXT,
city_key TEXT,
-- Request Metadata
path TEXT, -- The specific URL path
referral TEXT, -- The referrer URL
ip_address TEXT, -- Client IP
user_agent TEXT, -- Client Browser/Device
-- Extensible Data
metadata TEXT, -- JSON blob for any service-specific extra fields
-- Timestamps
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
– Providers table schema CREATE TABLE IF NOT EXISTS providers ( id INTEGER PRIMARY KEY AUTOINCREMENT, company_name TEXT NOT NULL, service TEXT NOT NULL, city TEXT NOT NULL,
website TEXT,
email TEXT,
phone TEXT,
contact_form_url TEXT,
outreach_status TEXT DEFAULT 'new',
response_status TEXT DEFAULT 'none',
active INTEGER DEFAULT 0,
sourcing_score INTEGER DEFAULT 0,
notes TEXT,
last_outreach_at DATETIME,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
This is my schema. I have two workers lead-admin and lead-router. Right now, lead admin is CRUD that sits on top of this database and also is responsible for using resend for provider outreach emails. Nothing automated all button clicks. I have a sourcing script that I run manually to scrape google maps data for a specific location and qualify leads. The lead router project is responsible for handling incoming calls from twilio and incoming website form requests. I am able to assign a single provider per niche per city. The relation is in this table:
CREATE TABLE IF NOT EXISTS assets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
domain TEXT NOT NULL UNIQUE,
service TEXT NOT NULL,
city TEXT NOT NULL,
phone TEXT,
email TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
Right now I have reached out to 20 providers in greenville. I have two that I can assign. I will turn on paid traffic to google ads at $5 a day. Right now when a lead comes in, I have it directly routing to my email by default. I can then manually deliver the lead to the provider. My site builder is a project called static-site-factory. Templated sites using json to power the content. I have set up service pages, location pages, and submitted to google search console.