`db/migrations/006_equipment_and_metrics.sql`

38 lines
-- 006 · Equipment Fleet (UX idea #4) + Acceptance Rate cache (UX idea #7)
--
-- Two unique-UX adds for the luxury trade:
--   - installers.equipment JSONB → ladder/scaffold/lift/table/dust capacity
--   - acceptance-rate is computed from bookings table on read, no schema add,
--     but partial index on status speeds up the rolling-365d aggregation.
--
-- Idempotent. Apply with:
--   psql -d national_paper_hangers -f db/migrations/006_equipment_and_metrics.sql

BEGIN;

-- Equipment Fleet — structured studio-capacity disclosure for trade buyers.
-- Shape (any field optional):
--   {
--     "max_reach_ft": 22,
--     "lift_type": "scaffold" | "extension_ladder" | "scissor_lift" | "boom_lift",
--     "paper_table": "none" | "folding" | "dedicated_60" | "dedicated_72_plus",
--     "dust_extraction": true,
--     "vehicle": "van" | "box_truck" | "trailer",
--     "notes": "free text — e.g. 'Genie GS-1932 lift available on request'"
--   }
ALTER TABLE installers
  ADD COLUMN IF NOT EXISTS equipment JSONB;

-- Partial GIN for "filter by lift_type" or "max_reach >= N" trade-buyer queries.
CREATE INDEX IF NOT EXISTS idx_installers_equipment
  ON installers USING GIN (equipment)
  WHERE equipment IS NOT NULL;

-- Acceptance-rate aggregation index — rolling 365-day window per installer,
-- only on the statuses that count toward the metric.
CREATE INDEX IF NOT EXISTS idx_bookings_acceptance_metric
  ON bookings (installer_id, created_at, status)
  WHERE status IN ('confirmed', 'completed', 'declined');

COMMIT;