← back to Animals
migrations/004_breed_images_and_events.sql
84 lines
-- Project: Animals — breed image library + events table.
--
-- breed_images: many-per-breed gallery sourced from Wikimedia Commons
-- (and later: USDA, NIH, AKC parent-club photos with explicit permission).
-- Every row stores the license + author + commons URL so we can render
-- attribution per image. STRICT: only PD / CC0 / CC-BY / CC-BY-SA accepted.
--
-- events: dog shows, cat shows, horse shows, adoption fairs, vaccination
-- clinics. Geo-tagged so the map view can plot them. Time-stamped so the
-- calendar view can group by date.
BEGIN;
CREATE TABLE IF NOT EXISTS breed_images (
id BIGSERIAL PRIMARY KEY,
breed_id BIGINT NOT NULL REFERENCES breeds(id) ON DELETE CASCADE,
source TEXT NOT NULL, -- 'wikimedia_commons','usda','nih','owner_upload','manual'
source_url TEXT NOT NULL, -- the descriptionurl on Commons / page on the source
image_url TEXT NOT NULL, -- direct media URL (use this in <img src>)
thumb_url TEXT, -- 320px CDN thumb
filename TEXT, -- e.g., 'Labrador_Retriever_portrait.jpg'
title TEXT, -- human-readable title from Commons
author TEXT, -- "Photographer Name" (from extmetadata)
author_url TEXT,
license_short TEXT NOT NULL, -- 'PD','CC0','CC-BY-2.0','CC-BY-SA-3.0', etc.
license_url TEXT, -- url to the license terms
width INTEGER,
height INTEGER,
is_hero BOOLEAN NOT NULL DEFAULT FALSE,
category_match TEXT, -- the Commons category we found this in
fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_breed_images_breed ON breed_images(breed_id);
CREATE INDEX IF NOT EXISTS idx_breed_images_hero ON breed_images(breed_id) WHERE is_hero;
CREATE INDEX IF NOT EXISTS idx_breed_images_source ON breed_images(source);
CREATE UNIQUE INDEX IF NOT EXISTS idx_breed_images_unique ON breed_images(breed_id, image_url);
-- Events: dog shows, cat shows, horse shows, adoption fairs, vaccine clinics
CREATE TABLE IF NOT EXISTS events (
id BIGSERIAL PRIMARY KEY,
kind TEXT NOT NULL CHECK (kind IN (
'dog_show','cat_show','horse_show','adoption_event',
'vaccine_clinic','breed_meet','agility_trial','obedience_trial',
'rally_trial','field_trial','herding_trial','tracking_test',
'lure_coursing','barn_hunt','dock_diving','flyball','other'
)),
title TEXT NOT NULL,
organizer TEXT, -- 'Sacramento Kennel Club'
sponsor_org TEXT, -- 'AKC' / 'UKC' / 'CFA' etc.
venue TEXT,
address TEXT,
city TEXT,
state TEXT,
zip TEXT,
latitude NUMERIC(10,7),
longitude NUMERIC(10,7),
start_at TIMESTAMPTZ NOT NULL, -- earliest day's open
end_at TIMESTAMPTZ, -- last day's close
url TEXT, -- registration / details
source_url TEXT, -- where we scraped it
source TEXT NOT NULL, -- 'akc' / 'ukc' / 'cfa' / 'manual' etc.
description TEXT,
breeds_focus TEXT[], -- e.g. ['golden_retriever','labrador_retriever'] (slugs) or [] for all-breed
status TEXT NOT NULL DEFAULT 'scheduled'
CHECK (status IN ('scheduled','cancelled','postponed','completed')),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_events_kind ON events(kind);
CREATE INDEX IF NOT EXISTS idx_events_state ON events(state);
CREATE INDEX IF NOT EXISTS idx_events_start ON events(start_at);
CREATE INDEX IF NOT EXISTS idx_events_latlng ON events(latitude, longitude);
CREATE INDEX IF NOT EXISTS idx_events_source ON events(source);
CREATE UNIQUE INDEX IF NOT EXISTS idx_events_dedupe ON events(source, source_url, start_at);
-- bumper for events
CREATE OR REPLACE FUNCTION bump_events_updated() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_events_updated BEFORE UPDATE ON events
FOR EACH ROW EXECUTE FUNCTION bump_events_updated();
COMMIT;