`migrations/004_business_articles.sql`

42 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 L5liGadvThkoMt9pgBBbmPZUEnOvCinn0z0sWK0dFxf9E4hHYXj7v0oVMnJqBYP

CREATE TABLE IF NOT EXISTS business_articles (
    id bigint NOT NULL,
    business_id bigint NOT NULL,
    url text NOT NULL,
    title text,
    publisher text,
    published_at timestamp with time zone,
    excerpt text,
    source text DEFAULT 'exa'::text NOT NULL,
    raw_json jsonb,
    created_at timestamp with time zone DEFAULT now()
);

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

ALTER SEQUENCE business_articles_id_seq OWNED BY business_articles.id;

ALTER TABLE ONLY business_articles ALTER COLUMN id SET DEFAULT nextval('business_articles_id_seq'::regclass);

ALTER TABLE ONLY business_articles
    ADD CONSTRAINT business_articles_pkey PRIMARY KEY (id);

CREATE INDEX IF NOT EXISTS idx_article_business ON business_articles USING btree (business_id);

CREATE UNIQUE INDEX IF NOT EXISTS uq_article_biz_url ON business_articles USING btree (business_id, url);

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

\unrestrict L5liGadvThkoMt9pgBBbmPZUEnOvCinn0z0sWK0dFxf9E4hHYXj7v0oVMnJqBYP