`sql/001_schema.sql`

95 lines
-- vintage_wallpaper_archive schema
-- Source: https://vintagewallpaperarchive.com (Hannah's Treasures, Shopify-hosted)
-- Internal-use only. Images and copy are © Hannah's Treasures.
-- Do NOT push to public site or Shopify without ownership clearance.

CREATE TABLE IF NOT EXISTS products (
  shopify_product_id      BIGINT PRIMARY KEY,
  handle                  TEXT NOT NULL UNIQUE,
  title                   TEXT NOT NULL,
  vendor                  TEXT,
  product_type            TEXT,
  body_html               TEXT,
  body_text               TEXT,         -- stripped of HTML for fulltext search
  tags                    TEXT[],
  options                 JSONB,        -- [{name, position, values:[]}]
  published_at            TIMESTAMPTZ,
  created_at_remote       TIMESTAMPTZ,
  updated_at_remote       TIMESTAMPTZ,
  -- normalized fields parsed from body_html (best-effort)
  pattern_width_inches    NUMERIC,
  pattern_repeat_inches   NUMERIC,
  era_year                INT,          -- e.g. 1940 from "1940s scenic"
  era_decade              INT,          -- e.g. 1940 (decade rounded)
  match_type              TEXT,         -- "straight across", "half drop", "random"
  -- provenance + compliance
  source                  TEXT NOT NULL DEFAULT 'vintage-wallpaper-archive-shopify-public',
  source_url              TEXT,
  owned_by_steve          BOOLEAN NOT NULL DEFAULT FALSE,
  image_license           TEXT NOT NULL DEFAULT 'unknown_third_party',
  ok_for_commercial_use   BOOLEAN NOT NULL DEFAULT FALSE,
  -- forensic completeness
  raw_payload             JSONB NOT NULL,
  ingested_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ingest_run_id           TEXT
);

CREATE INDEX IF NOT EXISTS idx_products_handle    ON products (handle);
CREATE INDEX IF NOT EXISTS idx_products_era_year  ON products (era_year);
CREATE INDEX IF NOT EXISTS idx_products_tags_gin  ON products USING GIN (tags);
-- idx_products_body_trgm is created after pg_trgm extension at bottom of file

CREATE TABLE IF NOT EXISTS variants (
  shopify_variant_id      BIGINT PRIMARY KEY,
  shopify_product_id      BIGINT NOT NULL REFERENCES products(shopify_product_id) ON DELETE CASCADE,
  sku                     TEXT,
  title                   TEXT,
  position                INT,
  price                   NUMERIC,
  compare_at_price        NUMERIC,
  available               BOOLEAN,
  taxable                 BOOLEAN,
  requires_shipping       BOOLEAN,
  option1                 TEXT,
  option2                 TEXT,
  option3                 TEXT,
  grams                   INT,
  created_at_remote       TIMESTAMPTZ,
  updated_at_remote       TIMESTAMPTZ,
  ingested_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_variants_product ON variants (shopify_product_id);
CREATE INDEX IF NOT EXISTS idx_variants_sku     ON variants (sku);

CREATE TABLE IF NOT EXISTS images (
  shopify_image_id        BIGINT PRIMARY KEY,
  shopify_product_id      BIGINT NOT NULL REFERENCES products(shopify_product_id) ON DELETE CASCADE,
  position                INT,
  src                     TEXT NOT NULL,
  alt                     TEXT,
  width                   INT,
  height                  INT,
  variant_ids             BIGINT[],
  created_at_remote       TIMESTAMPTZ,
  updated_at_remote       TIMESTAMPTZ,
  ingested_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_images_product ON images (shopify_product_id);

CREATE TABLE IF NOT EXISTS ingest_runs (
  run_id                  TEXT PRIMARY KEY,
  started_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  finished_at             TIMESTAMPTZ,
  pages_fetched           INT NOT NULL DEFAULT 0,
  products_seen           INT NOT NULL DEFAULT 0,
  products_upserted       INT NOT NULL DEFAULT 0,
  variants_upserted       INT NOT NULL DEFAULT 0,
  images_upserted         INT NOT NULL DEFAULT 0,
  errors                  INT NOT NULL DEFAULT 0,
  notes                   TEXT
);

-- pg_trgm for fuzzy body_text search (must come before any gin_trgm_ops index)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX IF NOT EXISTS idx_products_body_trgm ON products USING GIN (body_text gin_trgm_ops);