`migrations/007_business_review_sources.sql`

43 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 bLlJp3WVNsixCnZ5miDOV9yYq4ISLy8FEtzyf8UEb2JUJAJMjM6Xo3FPytvDCnV

CREATE TABLE IF NOT EXISTS business_review_sources (
    id bigint NOT NULL,
    business_id bigint NOT NULL,
    source_type text NOT NULL,
    url text NOT NULL,
    label text,
    submitted_by text,
    verified boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

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

ALTER SEQUENCE business_review_sources_id_seq OWNED BY business_review_sources.id;

ALTER TABLE ONLY business_review_sources ALTER COLUMN id SET DEFAULT nextval('business_review_sources_id_seq'::regclass);

ALTER TABLE ONLY business_review_sources
    ADD CONSTRAINT business_review_sources_pkey PRIMARY KEY (id);

CREATE INDEX IF NOT EXISTS idx_brs_business ON business_review_sources USING btree (business_id);

CREATE INDEX IF NOT EXISTS idx_brs_verified ON business_review_sources USING btree (verified) WHERE (verified = true);

CREATE UNIQUE INDEX IF NOT EXISTS uq_brs_business_url ON business_review_sources USING btree (business_id, url);

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

\unrestrict bLlJp3WVNsixCnZ5miDOV9yYq4ISLy8FEtzyf8UEb2JUJAJMjM6Xo3FPytvDCnV