`sql/seed-crawl-schedule.sql`

55 lines
-- ============================================================================
-- DRAFT — ONE-TIME SEED of crawl_cron + staggered next_scheduled_crawl
-- across all ACTIVE DW vendors, for the rolling nightly recrawl wave.
--
-- *** GATED: writes canonical vendor_registry scheduling rows. Steve runs this. ***
--     psql -d dw_unified -f sql/seed-crawl-schedule.sql
--
-- What it does:
--   * Orders active vendors by priority (HIGH>MEDIUM>LOW) then stalest-first
--     (last_product_update ASC NULLS FIRST), then largest catalog first.
--   * Assigns each vendor a "night offset" = floor(rank / :batch), so ~:batch
--     vendors come DUE per night, spreading the full sweep across the wave.
--   * Stamps crawl_cron = '0 2 * * *' (participates in the 2am nightly wave; the
--     dispatcher decides who is actually due via next_scheduled_crawl).
--   * next_scheduled_crawl = tonight 02:00 + (offset days).
--
-- Idempotent: re-running re-stamps the same deterministic schedule. It does NOT
-- touch last_product_update or any catalog data. Reversible: to undo, set
-- next_scheduled_crawl = NULL (dispatcher then treats all as due, ordered as above).
--
-- Tunable: change the divisor (10) to match the dispatcher batch size.
-- ============================================================================
BEGIN;

WITH ordered AS (
  SELECT id,
         (row_number() OVER (
            ORDER BY CASE crawl_priority
                       WHEN 'HIGH' THEN 0 WHEN 'MEDIUM' THEN 1 WHEN 'LOW' THEN 2 ELSE 3 END,
                     last_product_update ASC NULLS FIRST,
                     total_products DESC
         ) - 1) AS rn
    FROM vendor_registry
   WHERE is_active
     AND vendor_code <> 'wallquest'   -- owned by com.steve.wallquest-refresh (weekly)
)
UPDATE vendor_registry v
   SET crawl_cron = '0 2 * * *',
       next_scheduled_crawl = date_trunc('day', now()) + interval '2 hours'
                              + ((o.rn / 10)::int) * interval '1 day',
       updated_at = now()
  FROM ordered o
 WHERE v.id = o.id;

-- WallQuest keeps its own weekly cadence; do not enrol it in the nightly wave.
UPDATE vendor_registry
   SET crawl_cron = '0 3 * * 0'   -- Sunday 3am, matches its dedicated job
 WHERE vendor_code = 'wallquest' AND is_active;

-- Report the resulting first week of the wave (sanity view):
--   SELECT date_trunc('day', next_scheduled_crawl) d, count(*)
--     FROM vendor_registry WHERE is_active GROUP BY 1 ORDER BY 1 LIMIT 10;

COMMIT;