mini-prog/backend/migrations/0003_home.sql

BEGIN;

CREATE TABLE cards (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_public_id TEXT NOT NULL UNIQUE,
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  entry_channel_id UUID REFERENCES entry_channels(id) ON DELETE SET NULL,
  card_type TEXT NOT NULL CHECK (card_type IN ('event', 'html', 'notice')),
  title TEXT NOT NULL,
  subtitle TEXT,
  cover_url TEXT,
  event_id UUID REFERENCES events(id) ON DELETE SET NULL,
  html_url TEXT,
  display_slot TEXT NOT NULL DEFAULT 'home_primary',
  display_priority INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  starts_at TIMESTAMPTZ,
  ends_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX cards_tenant_id_idx ON cards(tenant_id);
CREATE INDEX cards_entry_channel_id_idx ON cards(entry_channel_id);
CREATE INDEX cards_event_id_idx ON cards(event_id);
CREATE INDEX cards_display_idx ON cards(display_slot, status, display_priority DESC);

CREATE TRIGGER cards_set_updated_at
BEFORE UPDATE ON cards
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

COMMIT;