`migrations/009_breed_dogpark_avatars.sql`

28 lines
-- Project: Animals — suggested avatars for the dog park, per breed.
--
-- For each breed we surface 3 hand-picked avatar slots (1=primary, 2,3=alts).
-- A user joining the dog park can pick one of the 3 instead of uploading
-- their own photo. Picks come from the existing breed_images gallery so
-- everything is PD/CC0/CC-BY/CC-BY-SA with full attribution captured.
--
-- The picker (src/enrich/pick_dogpark_avatars.js) refreshes this table after
-- each weekly image topup. Curators can also pin a slot manually by setting
-- pinned=TRUE — the picker leaves pinned rows alone.

BEGIN;

CREATE TABLE IF NOT EXISTS breed_dogpark_avatars (
  id          BIGSERIAL PRIMARY KEY,
  breed_id    BIGINT NOT NULL REFERENCES breeds(id) ON DELETE CASCADE,
  slot        SMALLINT NOT NULL CHECK (slot BETWEEN 1 AND 3),
  image_id    BIGINT NOT NULL REFERENCES breed_images(id) ON DELETE CASCADE,
  pinned      BOOLEAN NOT NULL DEFAULT FALSE,
  picked_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_dogpark_avatars_breed_slot
  ON breed_dogpark_avatars(breed_id, slot);
CREATE INDEX IF NOT EXISTS idx_dogpark_avatars_breed
  ON breed_dogpark_avatars(breed_id);

COMMIT;