`migrations/005_business_interviews.sql`

37 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 pFcf0Jc9pmQ7hl19eCeUFdKF7xuq2zJJZaRAE3SlTUAgcCmHh7SoilZtgDnHVib

CREATE TABLE IF NOT EXISTS business_interviews (
    id bigint NOT NULL,
    business_id bigint,
    qa_json jsonb DEFAULT '{}'::jsonb NOT NULL,
    recorded_by text,
    recorded_at timestamp with time zone DEFAULT now()
);

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

ALTER SEQUENCE business_interviews_id_seq OWNED BY business_interviews.id;

ALTER TABLE ONLY business_interviews ALTER COLUMN id SET DEFAULT nextval('business_interviews_id_seq'::regclass);

ALTER TABLE ONLY business_interviews
    ADD CONSTRAINT business_interviews_pkey PRIMARY KEY (id);

CREATE INDEX IF NOT EXISTS idx_interviews_business ON business_interviews USING btree (business_id);

CREATE INDEX IF NOT EXISTS idx_interviews_recorded ON business_interviews USING btree (business_id, recorded_at DESC);

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

\unrestrict pFcf0Jc9pmQ7hl19eCeUFdKF7xuq2zJJZaRAE3SlTUAgcCmHh7SoilZtgDnHVib