`sql/001_schema.sql`

108 lines
-- patternbank_archive schema
-- Source: https://patternbank.com (commercial pattern marketplace)
-- Internal-use only. See LICENSE_NOTES.md for compliance record.
-- Do NOT expose any of this data on a public route.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE IF NOT EXISTS designers (
  designer_slug           TEXT PRIMARY KEY,             -- patternbank.com/<slug>
  display_name            TEXT,
  profile_url             TEXT,
  bio                     TEXT,
  country                 TEXT,
  pattern_count_remote    INT,                          -- self-reported on profile
  raw_payload             JSONB,
  ingested_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS patterns (
  pattern_id              TEXT PRIMARY KEY,             -- patternbank's own id from URL
  slug                    TEXT NOT NULL,
  title                   TEXT,
  designer_slug           TEXT REFERENCES designers(designer_slug),
  designer_display_name   TEXT,
  source_url              TEXT NOT NULL,
  category                TEXT,                         -- e.g. floral, abstract, animal
  subcategory             TEXT,
  tags                    TEXT[],
  colors_hex              TEXT[],                       -- swatches surfaced on PDP
  description             TEXT,
  license_label           TEXT,                         -- "Premium", "Studio", "Free", etc.
  price_usd               NUMERIC,
  is_seamless             BOOLEAN,
  added_at_remote         TIMESTAMPTZ,
  -- compliance / provenance
  source                  TEXT NOT NULL DEFAULT 'patternbank-headless',
  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,
  ok_for_ai_training      BOOLEAN NOT NULL DEFAULT FALSE,   -- robots Content-Signal=ai-train:no
  -- forensic
  raw_payload             JSONB NOT NULL,
  ingested_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ingest_run_id           TEXT
);

CREATE INDEX IF NOT EXISTS idx_patterns_designer    ON patterns (designer_slug);
CREATE INDEX IF NOT EXISTS idx_patterns_category    ON patterns (category);
CREATE INDEX IF NOT EXISTS idx_patterns_tags_gin    ON patterns USING GIN (tags);
CREATE INDEX IF NOT EXISTS idx_patterns_colors_gin  ON patterns USING GIN (colors_hex);
CREATE INDEX IF NOT EXISTS idx_patterns_title_trgm  ON patterns USING GIN (title gin_trgm_ops);

CREATE TABLE IF NOT EXISTS pattern_images (
  image_id                BIGSERIAL PRIMARY KEY,
  pattern_id              TEXT NOT NULL REFERENCES patterns(pattern_id) ON DELETE CASCADE,
  position                INT,                          -- 0 = primary tile, 1+ = applied/sample views
  remote_url              TEXT NOT NULL,
  local_path              TEXT,                         -- data/images/<id>/<n>.jpg if downloaded
  width                   INT,
  height                  INT,
  bytes                   BIGINT,
  sha256                  TEXT,                         -- dedupe + integrity
  content_type            TEXT,
  is_thumbnail            BOOLEAN DEFAULT FALSE,
  ingested_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_images_pattern ON pattern_images (pattern_id);
CREATE INDEX IF NOT EXISTS idx_images_sha     ON pattern_images (sha256);

CREATE TABLE IF NOT EXISTS categories (
  category_slug           TEXT PRIMARY KEY,
  parent_slug             TEXT REFERENCES categories(category_slug),
  display_name            TEXT,
  url                     TEXT,
  pattern_count_remote    INT,
  ingested_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ingest_runs (
  run_id                  TEXT PRIMARY KEY,
  started_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  finished_at             TIMESTAMPTZ,
  mode                    TEXT,                         -- 'discover' | 'pdp' | 'images'
  pages_fetched           INT NOT NULL DEFAULT 0,
  patterns_seen           INT NOT NULL DEFAULT 0,
  patterns_upserted       INT NOT NULL DEFAULT 0,
  images_upserted         INT NOT NULL DEFAULT 0,
  images_downloaded       INT NOT NULL DEFAULT 0,
  bytes_downloaded        BIGINT NOT NULL DEFAULT 0,
  errors                  INT NOT NULL DEFAULT 0,
  notes                   TEXT
);

CREATE TABLE IF NOT EXISTS crawl_queue (
  url                     TEXT PRIMARY KEY,
  kind                    TEXT NOT NULL,                -- 'list' | 'pdp' | 'image'
  priority                INT NOT NULL DEFAULT 100,
  attempts                INT NOT NULL DEFAULT 0,
  last_attempt_at         TIMESTAMPTZ,
  last_status             INT,
  last_error              TEXT,
  done                    BOOLEAN NOT NULL DEFAULT FALSE,
  enqueued_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_queue_pending ON crawl_queue (kind, done, priority) WHERE done = FALSE;