`db/migrations/021_installer_service_areas.sql`

34 lines
-- 021 · Structured service-area coverage per installer
--
-- Today: every installer has `service_radius_miles` (single number, default 50)
-- and lat/lng. That works for "radius around the studio" but breaks for the
-- real-world case where a studio in Manhattan services all 5 boroughs +
-- specific Hudson Valley towns, OR explicitly excludes far-out areas.
--
-- These columns let the studio list (a) counties they service, (b) specific
-- towns/cities they service, (c) zips they service. Additive to the existing
-- radius: a match on ANY surface (radius OR county OR town OR zip) counts as
-- "this studio services your location." Empty arrays = radius-only behavior,
-- which preserves the current default for the 524 seeded studios.
--
-- Reversible:
--   ALTER TABLE installers
--     DROP COLUMN service_counties,
--     DROP COLUMN service_towns,
--     DROP COLUMN service_zips;

BEGIN;

ALTER TABLE installers
  ADD COLUMN IF NOT EXISTS service_counties TEXT[] DEFAULT ARRAY[]::TEXT[],
  ADD COLUMN IF NOT EXISTS service_towns    TEXT[] DEFAULT ARRAY[]::TEXT[],
  ADD COLUMN IF NOT EXISTS service_zips     TEXT[] DEFAULT ARRAY[]::TEXT[];

-- GIN indexes so the @> / && operators stay cheap when /near-me filters
-- "which installers list this county/town/zip" on every search.
CREATE INDEX IF NOT EXISTS idx_installers_service_counties ON installers USING gin (service_counties);
CREATE INDEX IF NOT EXISTS idx_installers_service_towns    ON installers USING gin (service_towns);
CREATE INDEX IF NOT EXISTS idx_installers_service_zips     ON installers USING gin (service_zips);

COMMIT;