← back to Vintage Wallpaper Archive
initial scaffold + full ingest of vintagewallpaperarchive.com (494 products, internal-only, owned_by_steve=false)
bc5ec6eff656f6c1b5d1d0c4258c796487a2b024 · 2026-05-13 10:52:49 -0700 · Steve Abrams
Files touched
A .env.exampleA .gitignoreA package-lock.jsonA package.jsonA scripts/ingest.jsA sql/001_schema.sql
Diff
commit bc5ec6eff656f6c1b5d1d0c4258c796487a2b024
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed May 13 10:52:49 2026 -0700
initial scaffold + full ingest of vintagewallpaperarchive.com (494 products, internal-only, owned_by_steve=false)
---
.env.example | 4 +
.gitignore | 10 ++
package-lock.json | 174 +++++++++++++++++++++++++++++++++++
package.json | 14 +++
scripts/ingest.js | 262 +++++++++++++++++++++++++++++++++++++++++++++++++++++
sql/001_schema.sql | 94 +++++++++++++++++++
6 files changed, 558 insertions(+)
diff --git a/.env.example b/.env.example
new file mode 100644
index 0000000..f631d90
--- /dev/null
+++ b/.env.example
@@ -0,0 +1,4 @@
+DATABASE_URL=postgresql:///vintage_wallpaper_archive?host=/tmp&user=stevestudio2
+SHOPIFY_ORIGIN=https://vintagewallpaperarchive.com
+# Set DRY_RUN=1 to fetch and log without writing to PG
+# DRY_RUN=0
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..8d47336
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,10 @@
+node_modules/
+.env
+.env.*
+!.env.example
+data/raw/
+logs/*.log
+*.log
+.DS_Store
+dist/
+build/
diff --git a/package-lock.json b/package-lock.json
new file mode 100644
index 0000000..ab6005c
--- /dev/null
+++ b/package-lock.json
@@ -0,0 +1,174 @@
+{
+ "name": "vintage-wallpaper-archive",
+ "version": "0.1.0",
+ "lockfileVersion": 3,
+ "requires": true,
+ "packages": {
+ "": {
+ "name": "vintage-wallpaper-archive",
+ "version": "0.1.0",
+ "dependencies": {
+ "dotenv": "^16.4.7",
+ "pg": "^8.13.1"
+ }
+ },
+ "node_modules/dotenv": {
+ "version": "16.6.1",
+ "resolved": "https://registry.npmjs.org/dotenv/-/dotenv-16.6.1.tgz",
+ "integrity": "sha512-uBq4egWHTcTt33a72vpSG0z3HnPuIl6NqYcTrKEg2azoEyl2hpW0zqlxysq2pK9HlDIHyHyakeYaYnSAwd8bow==",
+ "license": "BSD-2-Clause",
+ "engines": {
+ "node": ">=12"
+ },
+ "funding": {
+ "url": "https://dotenvx.com"
+ }
+ },
+ "node_modules/pg": {
+ "version": "8.20.0",
+ "resolved": "https://registry.npmjs.org/pg/-/pg-8.20.0.tgz",
+ "integrity": "sha512-ldhMxz2r8fl/6QkXnBD3CR9/xg694oT6DZQ2s6c/RI28OjtSOpxnPrUCGOBJ46RCUxcWdx3p6kw/xnDHjKvaRA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-connection-string": "^2.12.0",
+ "pg-pool": "^3.13.0",
+ "pg-protocol": "^1.13.0",
+ "pg-types": "2.2.0",
+ "pgpass": "1.0.5"
+ },
+ "engines": {
+ "node": ">= 16.0.0"
+ },
+ "optionalDependencies": {
+ "pg-cloudflare": "^1.3.0"
+ },
+ "peerDependencies": {
+ "pg-native": ">=3.0.1"
+ },
+ "peerDependenciesMeta": {
+ "pg-native": {
+ "optional": true
+ }
+ }
+ },
+ "node_modules/pg-cloudflare": {
+ "version": "1.3.0",
+ "resolved": "https://registry.npmjs.org/pg-cloudflare/-/pg-cloudflare-1.3.0.tgz",
+ "integrity": "sha512-6lswVVSztmHiRtD6I8hw4qP/nDm1EJbKMRhf3HCYaqud7frGysPv7FYJ5noZQdhQtN2xJnimfMtvQq21pdbzyQ==",
+ "license": "MIT",
+ "optional": true
+ },
+ "node_modules/pg-connection-string": {
+ "version": "2.12.0",
+ "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.12.0.tgz",
+ "integrity": "sha512-U7qg+bpswf3Cs5xLzRqbXbQl85ng0mfSV/J0nnA31MCLgvEaAo7CIhmeyrmJpOr7o+zm0rXK+hNnT5l9RHkCkQ==",
+ "license": "MIT"
+ },
+ "node_modules/pg-int8": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/pg-int8/-/pg-int8-1.0.1.tgz",
+ "integrity": "sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw==",
+ "license": "ISC",
+ "engines": {
+ "node": ">=4.0.0"
+ }
+ },
+ "node_modules/pg-pool": {
+ "version": "3.13.0",
+ "resolved": "https://registry.npmjs.org/pg-pool/-/pg-pool-3.13.0.tgz",
+ "integrity": "sha512-gB+R+Xud1gLFuRD/QgOIgGOBE2KCQPaPwkzBBGC9oG69pHTkhQeIuejVIk3/cnDyX39av2AxomQiyPT13WKHQA==",
+ "license": "MIT",
+ "peerDependencies": {
+ "pg": ">=8.0"
+ }
+ },
+ "node_modules/pg-protocol": {
+ "version": "1.13.0",
+ "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.13.0.tgz",
+ "integrity": "sha512-zzdvXfS6v89r6v7OcFCHfHlyG/wvry1ALxZo4LqgUoy7W9xhBDMaqOuMiF3qEV45VqsN6rdlcehHrfDtlCPc8w==",
+ "license": "MIT"
+ },
+ "node_modules/pg-types": {
+ "version": "2.2.0",
+ "resolved": "https://registry.npmjs.org/pg-types/-/pg-types-2.2.0.tgz",
+ "integrity": "sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-int8": "1.0.1",
+ "postgres-array": "~2.0.0",
+ "postgres-bytea": "~1.0.0",
+ "postgres-date": "~1.0.4",
+ "postgres-interval": "^1.1.0"
+ },
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/pgpass": {
+ "version": "1.0.5",
+ "resolved": "https://registry.npmjs.org/pgpass/-/pgpass-1.0.5.tgz",
+ "integrity": "sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug==",
+ "license": "MIT",
+ "dependencies": {
+ "split2": "^4.1.0"
+ }
+ },
+ "node_modules/postgres-array": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/postgres-array/-/postgres-array-2.0.0.tgz",
+ "integrity": "sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/postgres-bytea": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/postgres-bytea/-/postgres-bytea-1.0.1.tgz",
+ "integrity": "sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-date": {
+ "version": "1.0.7",
+ "resolved": "https://registry.npmjs.org/postgres-date/-/postgres-date-1.0.7.tgz",
+ "integrity": "sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-interval": {
+ "version": "1.2.0",
+ "resolved": "https://registry.npmjs.org/postgres-interval/-/postgres-interval-1.2.0.tgz",
+ "integrity": "sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ==",
+ "license": "MIT",
+ "dependencies": {
+ "xtend": "^4.0.0"
+ },
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/split2": {
+ "version": "4.2.0",
+ "resolved": "https://registry.npmjs.org/split2/-/split2-4.2.0.tgz",
+ "integrity": "sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg==",
+ "license": "ISC",
+ "engines": {
+ "node": ">= 10.x"
+ }
+ },
+ "node_modules/xtend": {
+ "version": "4.0.2",
+ "resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
+ "integrity": "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.4"
+ }
+ }
+ }
+}
diff --git a/package.json b/package.json
new file mode 100644
index 0000000..113f9ba
--- /dev/null
+++ b/package.json
@@ -0,0 +1,14 @@
+{
+ "name": "vintage-wallpaper-archive",
+ "version": "0.1.0",
+ "private": true,
+ "description": "Internal-only ingest of vintagewallpaperarchive.com (Hannah's Treasures) into a separate PG db. No public site, no Shopify push.",
+ "scripts": {
+ "ingest": "node scripts/ingest.js",
+ "ingest:dry": "DRY_RUN=1 node scripts/ingest.js"
+ },
+ "dependencies": {
+ "pg": "^8.13.1",
+ "dotenv": "^16.4.7"
+ }
+}
diff --git a/scripts/ingest.js b/scripts/ingest.js
new file mode 100644
index 0000000..fc23db5
--- /dev/null
+++ b/scripts/ingest.js
@@ -0,0 +1,262 @@
+#!/usr/bin/env node
+'use strict';
+
+// Ingest vintagewallpaperarchive.com (Shopify) into the local PG db.
+// Internal only. No public site. No Shopify push. Images stay third-party-owned.
+
+require('dotenv').config();
+const { Client } = require('pg');
+const crypto = require('crypto');
+
+const DATABASE_URL = process.env.DATABASE_URL
+ || 'postgresql:///vintage_wallpaper_archive?host=/tmp&user=stevestudio2';
+const ORIGIN = process.env.SHOPIFY_ORIGIN || 'https://vintagewallpaperarchive.com';
+const PAGE_LIMIT = 250; // Shopify max
+const REQUEST_GAP_MS = 1200; // ~0.83 req/sec — under Shopify's 2/sec limit
+const USER_AGENT =
+ 'Mozilla/5.0 (compatible; DW-archive-onboard/0.1; +mailto:steve@designerwallcoverings.com)';
+const DRY_RUN = /^(1|true|yes)$/i.test(process.env.DRY_RUN || '');
+
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+
+function stripHtml(html) {
+ if (!html) return null;
+ return html
+ .replace(/<[^>]+>/g, ' ')
+ .replace(/ /g, ' ')
+ .replace(/&/g, '&')
+ .replace(/\s+/g, ' ')
+ .trim();
+}
+
+function parseEra(text) {
+ if (!text) return { era_year: null, era_decade: null };
+ // "1940s", "1923", "early 1900s"
+ const decadeMatch = text.match(/\b(18|19|20)(\d)0s\b/);
+ if (decadeMatch) {
+ const decade = parseInt(decadeMatch[1] + decadeMatch[2] + '0', 10);
+ return { era_year: decade, era_decade: decade };
+ }
+ const yearMatch = text.match(/\b(18\d{2}|19\d{2}|20[0-2]\d)\b/);
+ if (yearMatch) {
+ const year = parseInt(yearMatch[1], 10);
+ return { era_year: year, era_decade: Math.floor(year / 10) * 10 };
+ }
+ return { era_year: null, era_decade: null };
+}
+
+function parseWidthAndRepeat(text) {
+ if (!text) return { width: null, repeat: null, match: null };
+ const widthMatch = text.match(/(\d+(?:\.\d+)?)\s*(?:inches|inch|in\.|")\s*(?:wide|width)/i)
+ || text.match(/pattern is\s*(\d+(?:\.\d+)?)\s*inches\s*wide/i);
+ const repeatMatch = text.match(/repeat\s*is\s*(\d+(?:\.\d+)?)\s*inches/i)
+ || text.match(/(\d+(?:\.\d+)?)\s*(?:inch|in\.|")\s*repeat/i);
+ let match = null;
+ if (/straight\s*across/i.test(text)) match = 'straight across';
+ else if (/half\s*drop/i.test(text)) match = 'half drop';
+ else if (/random/i.test(text) && /match/i.test(text)) match = 'random';
+ return {
+ width: widthMatch ? parseFloat(widthMatch[1]) : null,
+ repeat: repeatMatch ? parseFloat(repeatMatch[1]) : null,
+ match,
+ };
+}
+
+async function fetchPage(page) {
+ const url = `${ORIGIN}/products.json?limit=${PAGE_LIMIT}&page=${page}`;
+ const r = await fetch(url, { headers: { 'User-Agent': USER_AGENT, accept: 'application/json' } });
+ if (!r.ok) throw new Error(`HTTP ${r.status} on page ${page}`);
+ const j = await r.json();
+ return j.products || [];
+}
+
+async function upsertProduct(client, p, runId) {
+ const bodyText = stripHtml(p.body_html);
+ const era = parseEra(`${p.title || ''} ${bodyText || ''} ${(p.tags || []).join(' ')}`);
+ const wr = parseWidthAndRepeat(bodyText);
+ const tags = typeof p.tags === 'string'
+ ? p.tags.split(',').map((s) => s.trim()).filter(Boolean)
+ : (Array.isArray(p.tags) ? p.tags : []);
+
+ await client.query(
+ `INSERT INTO products (
+ shopify_product_id, handle, title, vendor, product_type,
+ body_html, body_text, tags, options,
+ published_at, created_at_remote, updated_at_remote,
+ pattern_width_inches, pattern_repeat_inches, era_year, era_decade, match_type,
+ source, source_url, owned_by_steve, image_license, ok_for_commercial_use,
+ raw_payload, ingest_run_id
+ ) VALUES (
+ $1,$2,$3,$4,$5, $6,$7,$8,$9, $10,$11,$12, $13,$14,$15,$16,$17,
+ $18,$19,$20,$21,$22, $23,$24
+ )
+ ON CONFLICT (shopify_product_id) DO UPDATE SET
+ handle = EXCLUDED.handle,
+ title = EXCLUDED.title,
+ vendor = EXCLUDED.vendor,
+ product_type = EXCLUDED.product_type,
+ body_html = EXCLUDED.body_html,
+ body_text = EXCLUDED.body_text,
+ tags = EXCLUDED.tags,
+ options = EXCLUDED.options,
+ published_at = EXCLUDED.published_at,
+ updated_at_remote = EXCLUDED.updated_at_remote,
+ pattern_width_inches = EXCLUDED.pattern_width_inches,
+ pattern_repeat_inches = EXCLUDED.pattern_repeat_inches,
+ era_year = EXCLUDED.era_year,
+ era_decade = EXCLUDED.era_decade,
+ match_type = EXCLUDED.match_type,
+ raw_payload = EXCLUDED.raw_payload,
+ ingest_run_id = EXCLUDED.ingest_run_id,
+ ingested_at = NOW()`,
+ [
+ p.id, p.handle, p.title, p.vendor, p.product_type,
+ p.body_html, bodyText, tags, JSON.stringify(p.options || []),
+ p.published_at, p.created_at, p.updated_at,
+ wr.width, wr.repeat, era.era_year, era.era_decade, wr.match,
+ 'vintage-wallpaper-archive-shopify-public',
+ `${ORIGIN}/products/${p.handle}`,
+ false, // owned_by_steve
+ 'unknown_third_party', // image_license
+ false, // ok_for_commercial_use
+ JSON.stringify(p),
+ runId,
+ ]
+ );
+}
+
+async function upsertVariants(client, p) {
+ for (const v of (p.variants || [])) {
+ await client.query(
+ `INSERT INTO variants (
+ shopify_variant_id, shopify_product_id, sku, title, position,
+ price, compare_at_price, available, taxable, requires_shipping,
+ option1, option2, option3, grams,
+ created_at_remote, updated_at_remote
+ ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
+ ON CONFLICT (shopify_variant_id) DO UPDATE SET
+ sku = EXCLUDED.sku,
+ title = EXCLUDED.title,
+ price = EXCLUDED.price,
+ compare_at_price = EXCLUDED.compare_at_price,
+ available = EXCLUDED.available,
+ option1 = EXCLUDED.option1,
+ option2 = EXCLUDED.option2,
+ option3 = EXCLUDED.option3,
+ updated_at_remote = EXCLUDED.updated_at_remote,
+ ingested_at = NOW()`,
+ [
+ v.id, p.id, v.sku, v.title, v.position,
+ v.price ? parseFloat(v.price) : null,
+ v.compare_at_price ? parseFloat(v.compare_at_price) : null,
+ v.available, v.taxable, v.requires_shipping,
+ v.option1, v.option2, v.option3, v.grams,
+ v.created_at, v.updated_at,
+ ]
+ );
+ }
+}
+
+async function upsertImages(client, p) {
+ for (const img of (p.images || [])) {
+ await client.query(
+ `INSERT INTO images (
+ shopify_image_id, shopify_product_id, position, src, alt,
+ width, height, variant_ids, created_at_remote, updated_at_remote
+ ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
+ ON CONFLICT (shopify_image_id) DO UPDATE SET
+ position = EXCLUDED.position,
+ src = EXCLUDED.src,
+ alt = EXCLUDED.alt,
+ width = EXCLUDED.width,
+ height = EXCLUDED.height,
+ variant_ids = EXCLUDED.variant_ids,
+ updated_at_remote = EXCLUDED.updated_at_remote,
+ ingested_at = NOW()`,
+ [
+ img.id, p.id, img.position, img.src, img.alt,
+ img.width, img.height, img.variant_ids || [],
+ img.created_at, img.updated_at,
+ ]
+ );
+ }
+}
+
+async function main() {
+ const runId = crypto.randomBytes(6).toString('hex');
+ console.log(`[ingest] run_id=${runId} dry=${DRY_RUN} origin=${ORIGIN}`);
+
+ const client = new Client({ connectionString: DATABASE_URL });
+ await client.connect();
+
+ let pagesFetched = 0;
+ let productsSeen = 0;
+ let productsUpserted = 0;
+ let variantsUpserted = 0;
+ let imagesUpserted = 0;
+ let errors = 0;
+
+ if (!DRY_RUN) {
+ await client.query(
+ 'INSERT INTO ingest_runs (run_id, notes) VALUES ($1, $2)',
+ [runId, 'vintagewallpaperarchive.com /products.json paginate']
+ );
+ }
+
+ try {
+ for (let page = 1; page <= 50; page++) {
+ const products = await fetchPage(page);
+ pagesFetched++;
+ if (products.length === 0) {
+ console.log(`[ingest] page ${page} empty — done`);
+ break;
+ }
+ console.log(`[ingest] page ${page}: ${products.length} products`);
+ for (const p of products) {
+ productsSeen++;
+ if (DRY_RUN) {
+ console.log(` - ${p.handle} "${p.title}" variants=${p.variants?.length || 0} images=${p.images?.length || 0}`);
+ continue;
+ }
+ try {
+ await client.query('BEGIN');
+ await upsertProduct(client, p, runId);
+ await upsertVariants(client, p);
+ await upsertImages(client, p);
+ await client.query('COMMIT');
+ productsUpserted++;
+ variantsUpserted += (p.variants || []).length;
+ imagesUpserted += (p.images || []).length;
+ } catch (err) {
+ await client.query('ROLLBACK').catch(() => {});
+ errors++;
+ console.error(`[ingest] ERROR on ${p.handle}: ${err.message}`);
+ }
+ }
+ await sleep(REQUEST_GAP_MS);
+ }
+ } finally {
+ if (!DRY_RUN) {
+ await client.query(
+ `UPDATE ingest_runs SET
+ finished_at = NOW(),
+ pages_fetched = $2,
+ products_seen = $3,
+ products_upserted = $4,
+ variants_upserted = $5,
+ images_upserted = $6,
+ errors = $7
+ WHERE run_id = $1`,
+ [runId, pagesFetched, productsSeen, productsUpserted, variantsUpserted, imagesUpserted, errors]
+ );
+ }
+ await client.end();
+ }
+
+ console.log(`[ingest] DONE pages=${pagesFetched} seen=${productsSeen} upserted=${productsUpserted} variants=${variantsUpserted} images=${imagesUpserted} errors=${errors}`);
+}
+
+main().catch((e) => {
+ console.error('[ingest] FATAL', e);
+ process.exit(1);
+});
diff --git a/sql/001_schema.sql b/sql/001_schema.sql
new file mode 100644
index 0000000..17a31e9
--- /dev/null
+++ b/sql/001_schema.sql
@@ -0,0 +1,94 @@
+-- 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);
(oldest)
·
back to Vintage Wallpaper Archive
·
chore: broaden .gitignore to cover .bak/.pre- snapshot files 4d9ffdb →