`db/migrations/019_user_contributions.sql`

77 lines
-- Slice 1 of PLAN-geo-archival.md (2026-05-01)
-- Adds moderation + contributor tracking to place_event,
-- plus a new user_image table for user-submitted photos.
--
-- Auth model: stayclaim has NO users table. Identity is delegated to
-- Site Factory (claimmyaddress.com) via session token. We store
-- contributor_email (mirroring address_submission's pattern) rather
-- than a cross-DB user_id FK we couldn't enforce.

BEGIN;

-- 1. Augment place_event for user-submitted memories / facts
ALTER TABLE place_event ADD COLUMN IF NOT EXISTS contributor_email   text;
ALTER TABLE place_event ADD COLUMN IF NOT EXISTS moderation_status   text NOT NULL DEFAULT 'APPROVED'
  CHECK (moderation_status IN ('PENDING','APPROVED','REJECTED'));
ALTER TABLE place_event ADD COLUMN IF NOT EXISTS moderation_reason   text;
ALTER TABLE place_event ADD COLUMN IF NOT EXISTS moderated_at        timestamptz;
ALTER TABLE place_event ADD COLUMN IF NOT EXISTS moderator_email     text;

-- Existing rows: backfilled to APPROVED (they were always public_visible).
-- New user-submitted rows MUST insert with moderation_status='PENDING' and
-- public_visible=false until approved; the API enforces this, not the schema.

CREATE INDEX IF NOT EXISTS idx_place_event_pending
  ON place_event (moderation_status)
  WHERE moderation_status = 'PENDING';

-- 2. New user_image table — user-submitted photos.
-- Distinct from pd_film_image (which is rights='public-domain' only).
-- All rows here are tier C (community-submitted) by definition.
CREATE TABLE IF NOT EXISTS user_image (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  listing_id          uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
  contributor_email   text NOT NULL,
  caption             text,
  image_url           text NOT NULL,                         -- CF R2 URL once uploaded
  thumb_url           text,
  width               int,
  height              int,
  source_tier         char(1) NOT NULL DEFAULT 'C'
    CHECK (source_tier = 'C'),                                -- always C; row-level lock
  source_label        text NOT NULL,                          -- e.g. "Submitted by Steve A."
  rights              text NOT NULL DEFAULT 'user-submitted', -- not PD
  rights_url          text,
  moderation_status   text NOT NULL DEFAULT 'PENDING'
    CHECK (moderation_status IN ('PENDING','APPROVED','REJECTED')),
  moderation_reason   text,
  moderated_at        timestamptz,
  moderator_email     text,
  uploaded_at         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_user_image_listing_approved
  ON user_image (listing_id)
  WHERE moderation_status = 'APPROVED';
CREATE INDEX IF NOT EXISTS idx_user_image_pending
  ON user_image (moderation_status)
  WHERE moderation_status = 'PENDING';
CREATE INDEX IF NOT EXISTS idx_user_image_contributor
  ON user_image (contributor_email);

COMMIT;

-- ROLLBACK plan (if needed):
-- BEGIN;
-- DROP INDEX IF EXISTS idx_user_image_contributor;
-- DROP INDEX IF EXISTS idx_user_image_pending;
-- DROP INDEX IF EXISTS idx_user_image_listing_approved;
-- DROP TABLE IF EXISTS user_image;
-- DROP INDEX IF EXISTS idx_place_event_pending;
-- ALTER TABLE place_event DROP COLUMN IF EXISTS moderator_email;
-- ALTER TABLE place_event DROP COLUMN IF EXISTS moderated_at;
-- ALTER TABLE place_event DROP COLUMN IF EXISTS moderation_reason;
-- ALTER TABLE place_event DROP COLUMN IF EXISTS moderation_status;
-- ALTER TABLE place_event DROP COLUMN IF EXISTS contributor_email;
-- COMMIT;