`migrations/008_enrichment_jobs.sql`

46 lines
-- Captured 2026-05-04 from local DB schema drift before fresh-deploy reproducibility.
-- Idempotent — re-running on a populated DB is a no-op.

\restrict EuPDbXhVYSFsBy8xkV84IyraZU2Um5iZibL59Mv2ilaibBuyeo5tz803Pdv31kL

CREATE TABLE IF NOT EXISTS enrichment_jobs (
    id bigint NOT NULL,
    business_id bigint NOT NULL,
    kind text NOT NULL,
    status text DEFAULT 'pending'::text NOT NULL,
    found_url text,
    found_handle text,
    hit_count integer,
    raw_json jsonb,
    attempted_at timestamp with time zone,
    next_attempt timestamp with time zone DEFAULT now(),
    attempts integer DEFAULT 0 NOT NULL,
    source text DEFAULT 'exa'::text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

CREATE SEQUENCE IF NOT EXISTS enrichment_jobs_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE enrichment_jobs_id_seq OWNED BY enrichment_jobs.id;

ALTER TABLE ONLY enrichment_jobs ALTER COLUMN id SET DEFAULT nextval('enrichment_jobs_id_seq'::regclass);

ALTER TABLE ONLY enrichment_jobs
    ADD CONSTRAINT enrichment_jobs_pkey PRIMARY KEY (id);

CREATE INDEX IF NOT EXISTS idx_enrich_status ON enrichment_jobs USING btree (status, next_attempt) WHERE (status = 'pending'::text);

CREATE UNIQUE INDEX IF NOT EXISTS uq_enrich_biz_kind ON enrichment_jobs USING btree (business_id, kind);

ALTER TABLE ONLY enrichment_jobs
    ADD CONSTRAINT enrichment_jobs_business_id_fkey FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE;

\unrestrict EuPDbXhVYSFsBy8xkV84IyraZU2Um5iZibL59Mv2ilaibBuyeo5tz803Pdv31kL