← back to Ventura Corridor
db/migrations/001_init.sql
126 lines
-- Ventura Blvd corridor — initial schema.
-- Local-only PG. Mirrors lawyer/doctor pattern: organizations as anchor, plus
-- audit/score tables, plus a corridor_segments table for the 17-mile polyline.
BEGIN;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
-- ─── Businesses ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS businesses (
id BIGSERIAL PRIMARY KEY,
source TEXT NOT NULL, -- 'here' | 'osm' | 'la_county' | 'manual'
source_id TEXT NOT NULL, -- the upstream ID (HERE ID, OSM way/node id, etc.)
name TEXT NOT NULL,
category TEXT, -- normalized top-level (food, retail, services, ...)
category_raw TEXT, -- upstream label, before normalization
category_naics TEXT, -- best-effort NAICS bucket if derivable
address TEXT,
street_number TEXT, -- parsed for matching to corridor segments
street_name TEXT, -- normalized "VENTURA BLVD" if on the corridor
city TEXT, -- "Studio City", "Sherman Oaks", ...
zip TEXT,
lat DOUBLE PRECISION,
lng DOUBLE PRECISION,
phone TEXT,
website TEXT, -- canonical URL after normalization
on_corridor BOOLEAN NOT NULL DEFAULT FALSE, -- true iff geocoded onto Ventura Blvd
corridor_block INTEGER, -- 100-block address (e.g., 12100 → 12100)
segment_id INTEGER, -- FK to corridor_segments below
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
raw JSONB, -- the upstream row, kept for audit
UNIQUE (source, source_id)
);
CREATE INDEX IF NOT EXISTS idx_businesses_corridor ON businesses (on_corridor) WHERE on_corridor;
CREATE INDEX IF NOT EXISTS idx_businesses_category ON businesses (category) WHERE on_corridor;
CREATE INDEX IF NOT EXISTS idx_businesses_geo ON businesses (lat, lng) WHERE lat IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_businesses_name_trgm ON businesses USING gin (name gin_trgm_ops);
-- ─── Corridor segments (17-mi Ventura Blvd polyline, ~1km tiles) ────────
CREATE TABLE IF NOT EXISTS corridor_segments (
id SERIAL PRIMARY KEY,
ord INTEGER NOT NULL, -- 1..N, west→east
city TEXT, -- which city the segment falls in
west_lat DOUBLE PRECISION NOT NULL,
west_lng DOUBLE PRECISION NOT NULL,
east_lat DOUBLE PRECISION NOT NULL,
east_lng DOUBLE PRECISION NOT NULL,
block_low INTEGER, -- e.g., 22000 (Calabasas end)
block_high INTEGER -- e.g., 23999
);
CREATE INDEX IF NOT EXISTS idx_corridor_segments_ord ON corridor_segments (ord);
-- ─── Front-page audits (one row per crawl pass) ─────────────────────────
CREATE TABLE IF NOT EXISTS front_page_audits (
id BIGSERIAL PRIMARY KEY,
business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
audited_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
fetched_url TEXT, -- where we ended up (after redirects)
status_code INTEGER,
http_protocol TEXT, -- 'http' | 'https'
ttfb_ms INTEGER,
load_ms INTEGER,
bytes_html INTEGER,
bytes_total INTEGER,
has_https BOOLEAN,
has_viewport BOOLEAN, -- mobile-viewport meta tag
has_title BOOLEAN,
title_text TEXT,
meta_desc TEXT,
has_h1 BOOLEAN,
h1_text TEXT,
schema_org_count INTEGER, -- # of <script type="application/ld+json"> blocks
schema_org_types TEXT[], -- e.g., ['Organization','LocalBusiness']
og_image TEXT, -- og:image URL if present
alt_coverage NUMERIC(4,2), -- 0.00–1.00 fraction of <img> with non-empty alt
word_count INTEGER,
outbound_links INTEGER,
internal_links INTEGER,
screenshot_path TEXT, -- local file relative to data/screenshots/
error_message TEXT,
raw_html_path TEXT, -- relative to data/raw/
raw_lighthouse JSONB -- if Lighthouse runs ever wired in
);
CREATE INDEX IF NOT EXISTS idx_audits_business ON front_page_audits (business_id, audited_at DESC);
-- ─── SEO scores (one row per audit pass) ────────────────────────────────
-- 12 signals, weighted, mirrors lawyer/doctor methodology v1.0.
CREATE TABLE IF NOT EXISTS seo_scores (
id BIGSERIAL PRIMARY KEY,
audit_id BIGINT NOT NULL REFERENCES front_page_audits(id) ON DELETE CASCADE,
business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
scored_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
total INTEGER NOT NULL, -- 0..100
signals JSONB NOT NULL, -- {has_https: 10, has_viewport: 8, ...}
tier TEXT NOT NULL, -- 'A' | 'B' | 'C' | 'D'
category_rank INTEGER, -- rank within category at score time
category_n INTEGER -- size of competitor cohort
);
CREATE INDEX IF NOT EXISTS idx_seo_scores_business ON seo_scores (business_id, scored_at DESC);
CREATE INDEX IF NOT EXISTS idx_seo_scores_total ON seo_scores (total DESC);
-- ─── Ingest run log ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ingest_runs (
id BIGSERIAL PRIMARY KEY,
source TEXT NOT NULL,
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
finished_at TIMESTAMPTZ,
rows_in INTEGER,
rows_new INTEGER,
rows_updated INTEGER,
error_message TEXT,
notes TEXT
);
-- ─── Migrations ledger ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS schema_migrations (
filename TEXT PRIMARY KEY,
applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
INSERT INTO schema_migrations (filename) VALUES ('001_init.sql')
ON CONFLICT (filename) DO NOTHING;
COMMIT;