`db/migrations/001_schema.sql`

66 lines
-- wallco.ai — PG schema (dw_unified)
-- Two tables:
--   pd_source_designs   — public-domain / CC0 pattern source corpus (Smithsonian, Wikimedia, LACMA, IA, V&A CC0)
--   spoon_all_designs   — generated wallco.ai designs (AI-original + variants), tracks provenance

CREATE TABLE IF NOT EXISTS pd_source_designs (
  id                BIGSERIAL PRIMARY KEY,
  source            TEXT NOT NULL,             -- 'smithsonian' | 'wikimedia' | 'lacma' | 'ia' | 'va_cc0'
  source_id         TEXT NOT NULL,             -- ID at source (e.g. 'edanmdm:nmah_1234')
  title             TEXT,
  creator           TEXT,
  date_created      TEXT,                      -- free-text; PD spans centuries, hard to normalize
  license           TEXT NOT NULL,             -- 'CC0' | 'PD' | 'PDMark' (must be one of these for inclusion)
  source_url        TEXT NOT NULL,             -- canonical source page
  image_url         TEXT NOT NULL,             -- high-res image URL
  local_path        TEXT,                      -- ~/Projects/wallco-ai/data/images/<source>/<id>.jpg once downloaded
  width_px          INTEGER,
  height_px         INTEGER,
  bytes             BIGINT,
  raw_metadata      JSONB,                     -- whatever the source API returned
  tags              TEXT[],                    -- source-provided tags
  motifs            TEXT[],                    -- LLM-extracted motifs (e.g. 'peony', 'scroll', 'damask')
  dominant_hex      TEXT,
  palette           JSONB,                     -- [{hex, pct}, ...]
  category          TEXT,                      -- 'floral' | 'damask' | 'geometric' | 'art-deco' | etc.
  ingested_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (source, source_id)
);
CREATE INDEX IF NOT EXISTS idx_pd_source_designs_source     ON pd_source_designs(source);
CREATE INDEX IF NOT EXISTS idx_pd_source_designs_category   ON pd_source_designs(category);
CREATE INDEX IF NOT EXISTS idx_pd_source_designs_dominant   ON pd_source_designs(dominant_hex);
CREATE INDEX IF NOT EXISTS idx_pd_source_designs_motifs     ON pd_source_designs USING GIN(motifs);
CREATE INDEX IF NOT EXISTS idx_pd_source_designs_tags       ON pd_source_designs USING GIN(tags);


CREATE TABLE IF NOT EXISTS spoon_all_designs (
  id                BIGSERIAL PRIMARY KEY,
  brand             TEXT NOT NULL DEFAULT 'wallco.ai',
  kind              TEXT NOT NULL CHECK (kind IN ('seamless_tile','mural','mural_panel')),
  width_in          NUMERIC(8,2),
  height_in         NUMERIC(8,2),
  panels            INTEGER,                   -- mural: how many panels; null for seamless tile
  generator         TEXT,                      -- 'sdxl-text' | 'sdxl-img2img' | 'comfy' | 'replicate' | 'stub'
  prompt            TEXT,                      -- text prompt used (always recorded for provenance)
  negative_prompt   TEXT,
  seed              BIGINT,
  steps             INTEGER,
  cfg_scale         NUMERIC(5,2),
  pd_source_ids     BIGINT[],                  -- which pd_source_designs rows fed this generation (if any)
  image_url         TEXT,                      -- public CDN/serve URL (when deployed)
  local_path        TEXT,                      -- ~/Projects/wallco-ai/data/generated/<id>.png
  dominant_hex      TEXT,
  palette           JSONB,
  motifs            TEXT[],                    -- LLM-extracted
  tags              TEXT[],
  category          TEXT,
  is_published      BOOLEAN NOT NULL DEFAULT FALSE,
  notes             TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_spoon_all_kind       ON spoon_all_designs(kind);
CREATE INDEX IF NOT EXISTS idx_spoon_all_category   ON spoon_all_designs(category);
CREATE INDEX IF NOT EXISTS idx_spoon_all_motifs     ON spoon_all_designs USING GIN(motifs);
CREATE INDEX IF NOT EXISTS idx_spoon_all_tags       ON spoon_all_designs USING GIN(tags);
CREATE INDEX IF NOT EXISTS idx_spoon_all_published  ON spoon_all_designs(is_published);