`db/schema.sql`

65 lines
-- patterndesignlab schema — OWNED content only. Separate DB from dw_unified.

CREATE TABLE IF NOT EXISTS designers (
  id         SERIAL PRIMARY KEY,
  slug       TEXT UNIQUE NOT NULL,
  name       TEXT NOT NULL,
  bio        TEXT,
  country    TEXT,
  avatar     TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS designs (
  id            TEXT PRIMARY KEY,             -- stable id lifted from source catalog
  slug          TEXT UNIQUE NOT NULL,
  title         TEXT NOT NULL,
  designer_id   INTEGER REFERENCES designers(id),
  category      TEXT,
  kind          TEXT,                         -- seamless_tile | mural | mural_panel
  style         TEXT,                         -- Heritage / Moody Floral / Mid-Century ...
  motif         TEXT,                         -- floral / botanical / animal / geometric / stripe / mural
  colorway      TEXT,                         -- Greige / Chamois / Oxblood ...
  technique     TEXT,                         -- digital / block-print / painterly / flocked
  seamless      BOOLEAN NOT NULL DEFAULT true,
  dominant_hex  TEXT,
  tags          TEXT[] NOT NULL DEFAULT '{}',
  img           TEXT NOT NULL,
  room_img      TEXT,
  style_line    TEXT,
  provenance    TEXT,
  license_tiers JSONB NOT NULL DEFAULT '[]',
  price_min     NUMERIC,                      -- cheapest tier, for price sort/filter
  price_max     NUMERIC,
  pricing_status TEXT,
  source_id     TEXT,
  content_class TEXT,                          -- credible | novelty (curation aid)
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  search_tsv    TSVECTOR
);

-- Full-text over title + tags + style + colorway + motif
CREATE OR REPLACE FUNCTION pdl_designs_tsv(t TEXT, tg TEXT[], st TEXT, cw TEXT, mo TEXT)
RETURNS TSVECTOR LANGUAGE sql IMMUTABLE AS $$
  SELECT setweight(to_tsvector('english', coalesce(t,'')), 'A')
      || setweight(to_tsvector('english', array_to_string(coalesce(tg,'{}'),' ')), 'B')
      || setweight(to_tsvector('english', coalesce(st,'')||' '||coalesce(cw,'')||' '||coalesce(mo,'')), 'C');
$$;

CREATE INDEX IF NOT EXISTS designs_tsv_idx      ON designs USING GIN (search_tsv);
CREATE INDEX IF NOT EXISTS designs_style_idx    ON designs (style);
CREATE INDEX IF NOT EXISTS designs_colorway_idx ON designs (colorway);
CREATE INDEX IF NOT EXISTS designs_motif_idx    ON designs (motif);
CREATE INDEX IF NOT EXISTS designs_designer_idx ON designs (designer_id);
CREATE INDEX IF NOT EXISTS designs_created_idx  ON designs (created_at DESC);

-- Inquiry / checkout event log (mirrors pattern-vault's license-events)
CREATE TABLE IF NOT EXISTS license_events (
  id         SERIAL PRIMARY KEY,
  design_id  TEXT,
  tier       TEXT,
  email      TEXT,
  kind       TEXT,   -- inquiry | checkout_start
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);