← back to Astek Landing
scripts/scrape-astek.js
264 lines
#!/usr/bin/env node
/**
* Workstream A — Astek catalog scrape (INTERNAL DATA ONLY, no Shopify).
* Feed-first: paginate https://www.astek.com/products.json?limit=250&page=N
* PostgreSQL-FIRST: raw snapshot -> astek_catalog in dw_unified -> register DW SKUs.
*
* Cost: $0 (local HTTP). No Browserbase.
*/
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
const VENDOR = 'Astek';
const VENDOR_CODE = 'astek'; // existing canonical vendor_registry row
const DW_PREFIX = 'DWPX'; // pre-assigned to Astek in vendor_registry (compliant: P,X allowed; 0 SKUs minted)
const SKU_RANGE_START = 400000; // Astek block (kept distinct from legacy ranges)
const FEED = 'https://www.astek.com/products.json';
const RAW_OUT = path.join(__dirname, '..', 'data', 'astek-raw.json');
const PW = (() => {
try {
const env = fs.readFileSync(require('os').homedir() + '/Projects/secrets-manager/.env', 'utf8');
const m = env.match(/^DW_ADMIN_DB_PASSWORD=(.*)$/m);
return m ? m[1].replace(/^["']|["']$/g, '').trim() : (process.env.PGPASSWORD || '');
} catch { return process.env.PGPASSWORD || ''; }
})();
const pool = new Pool({
host: '127.0.0.1', port: 5432, user: 'dw_admin', database: 'dw_unified', password: PW,
});
const BANNED = s => (s || '')
.replace(/\bWallpapers\b/gi, 'Wallcoverings')
.replace(/\bWallpaper\b/gi, 'Wallcovering');
function toTitleCase(str) {
const small = new Set(['a','an','the','and','but','or','for','nor','on','at','to','from','by','in','of','with']);
return (str || '').split(/\s+/).map((w, i) => {
const lw = w.toLowerCase();
if (i !== 0 && small.has(lw)) return lw;
return w.charAt(0).toUpperCase() + w.slice(1);
}).join(' ').trim();
}
// Parse specs out of the body_html prose / spec block.
function parseSpecs(html) {
const text = (html || '').replace(/<[^>]+>/g, ' ').replace(/&/g, '&').replace(/ /g, ' ').replace(/\s+/g, ' ');
const grab = (re) => { const m = text.match(re); return m ? m[1].trim() : null; };
return {
width: grab(/(?:Roll\s*Width|Width)[:\s]*([0-9][0-9.\s\/"]*(?:in(?:ches)?|"|cm|mm)?)/i),
length: grab(/(?:Roll\s*Length|Length)[:\s]*([0-9][0-9.\s\/"]*(?:ft|feet|yd|yard|in(?:ches)?|m)?)/i),
repeat: grab(/(?:Pattern\s*Repeat|Repeat)[:\s]*([0-9][0-9.\s\/"]*(?:in(?:ches)?|"|cm)?)/i),
material: grab(/(?:Material|Substrate|Type|Content)[:\s]*([A-Za-z][A-Za-z0-9 ,\/\-]{2,40})/i),
match: grab(/(?:Match)[:\s]*([A-Za-z][A-Za-z ]{2,30})/i),
};
}
const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124 Safari/537.36';
// Shopify/CF occasionally returns a transient 503 on the products.json feed
// (seen 2026-07-13). Retry with backoff so the monthly unattended job doesn't
// die on a single blip. Raises only after all attempts are exhausted.
async function fetchFeedPage(url, attempts = 4) {
let lastStatus = 0;
for (let i = 0; i < attempts; i++) {
const res = await fetch(url, { headers: { 'User-Agent': UA } });
if (res.ok) return res;
lastStatus = res.status;
if (i < attempts - 1) await new Promise(r => setTimeout(r, 1500 * (i + 1))); // 1.5s, 3s, 4.5s
}
throw new Error(`feed HTTP ${lastStatus} after ${attempts} attempts`);
}
async function fetchAllProducts() {
let page = 1, all = [];
while (true) {
const url = `${FEED}?limit=250&page=${page}`;
const res = await fetchFeedPage(url);
const json = await res.json();
const products = json.products || [];
if (products.length === 0) break;
all.push(...products);
process.stdout.write(`\r fetched page ${page} (total ${all.length}) `);
page++;
if (page > 40) break; // hard safety cap
await new Promise(r => setTimeout(r, 120)); // polite
}
process.stdout.write('\n');
return all;
}
async function ensureSchema(client) {
await client.query(`
CREATE TABLE IF NOT EXISTS astek_catalog (
id BIGSERIAL PRIMARY KEY,
dw_sku TEXT UNIQUE,
mfr_sku TEXT NOT NULL,
shopify_product_id BIGINT,
handle TEXT,
pattern_name TEXT,
color_name TEXT,
title TEXT, -- Pattern Color | Astek (title-cased, banned-word clean)
vendor TEXT DEFAULT 'Astek',
product_type TEXT,
body_html TEXT,
description_text TEXT,
price NUMERIC,
cost NUMERIC,
width TEXT,
length TEXT,
repeat TEXT,
material TEXT,
match_type TEXT,
color_tags TEXT[],
style_tags TEXT[],
all_images TEXT[],
image_url TEXT, -- primary image
variant_image TEXT,
product_url TEXT,
raw JSONB,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (mfr_sku)
);
CREATE INDEX IF NOT EXISTS astek_catalog_pattern_idx ON astek_catalog (pattern_name);
CREATE INDEX IF NOT EXISTS astek_catalog_handle_idx ON astek_catalog (handle);
`);
}
// registry helpers (inline; mirror lib/sku-registry semantics but with our pooled client)
async function checkDup(client, mfrSku) {
const r = await client.query(
`SELECT dw_sku FROM dw_sku_registry WHERE vendor_prefix=$1 AND mfr_sku=$2`, [DW_PREFIX, mfrSku]);
return r.rows[0]?.dw_sku || null;
}
async function nextSkuNum(client) {
const r = await client.query(
`SELECT MAX(CAST(SUBSTRING(dw_sku FROM '[0-9]+$') AS INTEGER)) mx FROM dw_sku_registry WHERE vendor_prefix=$1`,
[DW_PREFIX]);
return r.rows[0]?.mx ? (r.rows[0].mx + 1) : SKU_RANGE_START;
}
async function main() {
console.log(`\n[Astek scrape] prefix=${DW_PREFIX} range_start=${SKU_RANGE_START}`);
console.log('Fetching products.json feed ($0 local HTTP)...');
const products = await fetchAllProducts();
console.log(`Products: ${products.length}`);
fs.writeFileSync(RAW_OUT, JSON.stringify({ scraped_at: new Date().toISOString(), count: products.length, products }, null, 0));
console.log(`Raw snapshot -> ${RAW_OUT}`);
const client = await pool.connect();
let rows = 0, variants = 0, images = 0, registered = 0, dups = 0, skipped = 0;
try {
await ensureSchema(client);
// Seed the sku counter once, then increment locally to avoid N round-trips.
let counter = await nextSkuNum(client);
for (const p of products) {
const specs = parseSpecs(p.body_html);
const descText = BANNED((p.body_html || '').replace(/<[^>]+>/g, ' ').replace(/&/g, '&').replace(/ /g, ' ').replace(/\s+/g, ' ').trim());
const patternName = BANNED(toTitleCase((p.title || '').replace(/\s*Wallcovering\s*$/i, '').replace(/\s*Wallpaper\s*$/i, '').trim()));
const colorTags = (p.tags || []).filter(t => /^color_/.test(t)).map(t => t.replace(/^color_/, ''));
// ONLY style_ tags are real styles — feature_/design_ carry ops junk (instock, pricing, color)
const styleTags = (p.tags || []).filter(t => /^style_/.test(t)).map(t => t.replace(/^style_/, ''));
const allImages = (p.images || []).map(i => i.src);
images += allImages.length;
// Map variant image ids -> src
const imgById = {};
for (const im of (p.images || [])) for (const vid of (im.variant_ids || [])) imgById[vid] = im.src;
const vlist = (p.variants || []);
for (const v of vlist) {
variants++;
// MFR SKU: prefer clean variant SKU; fall back to handle+position.
let mfrSku = (v.sku && v.sku.trim()) ? v.sku.trim() : `${p.handle}-${v.position || v.id}`;
// Real color name from option/variant title (NEVER "Unknown").
let colorName = (v.option1 && v.option1.trim()) || (v.title && v.title.trim()) || '';
if (/^unknown$/i.test(colorName)) colorName = '';
// Title: Pattern Color | Astek (fallback order: color -> mfr sku -> none; never "Unknown")
let titleColor = colorName ? toTitleCase(colorName) : '';
let title = titleColor ? `${patternName} ${titleColor} | Astek` : `${patternName} | Astek`;
if (!patternName) { // last-resort pattern fallback: mfr sku
title = `${mfrSku}${titleColor ? ' ' + titleColor : ''} | Astek`;
}
title = BANNED(title);
const priceNum = (v.price != null && parseFloat(v.price) > 0) ? parseFloat(v.price) : null;
const variantImg = imgById[v.id] || v.featured_image?.src || allImages[0] || null;
// ---- PostgreSQL FIRST: register DW SKU ----
// dw_sku_registry.mfr_sku is varchar(50); some Astek feed skus jam color tags in
// and overflow it — register those under a deterministic hash-truncated key while
// astek_catalog (TEXT) keeps the full sku.
const regSku = mfrSku.length <= 50
? mfrSku
: mfrSku.slice(0, 41) + '~' + require('crypto').createHash('sha1').update(mfrSku).digest('hex').slice(0, 8);
let dwSku = await checkDup(client, regSku);
if (dwSku) { dups++; }
else {
dwSku = `${DW_PREFIX}-${counter}`;
try {
await client.query(
`INSERT INTO dw_sku_registry (dw_sku, vendor_prefix, vendor_name, mfr_sku) VALUES ($1,$2,$3,$4)`,
[dwSku, DW_PREFIX, VENDOR, regSku]);
registered++; counter++;
} catch (e) {
if (e.code === '23505') { dwSku = await checkDup(client, regSku); dups++; }
else { console.error('reg err', mfrSku, e.message); skipped++; continue; }
}
}
// ---- upsert catalog row ----
await client.query(`
INSERT INTO astek_catalog
(dw_sku, mfr_sku, handle, pattern_name, color_name, title, product_type,
body_html, description_text, price, cost, width, length, repeat, material, match_type,
color_tags, style_tags, all_images, image_url, variant_image, product_url, raw, updated_at)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,NOW())
ON CONFLICT (mfr_sku) DO UPDATE SET
dw_sku=EXCLUDED.dw_sku, pattern_name=EXCLUDED.pattern_name, color_name=EXCLUDED.color_name,
title=EXCLUDED.title, body_html=EXCLUDED.body_html, description_text=EXCLUDED.description_text,
price=EXCLUDED.price, width=EXCLUDED.width, length=EXCLUDED.length, repeat=EXCLUDED.repeat,
material=EXCLUDED.material, match_type=EXCLUDED.match_type, color_tags=EXCLUDED.color_tags,
style_tags=EXCLUDED.style_tags, all_images=EXCLUDED.all_images, image_url=EXCLUDED.image_url,
variant_image=EXCLUDED.variant_image, product_url=EXCLUDED.product_url, raw=EXCLUDED.raw,
updated_at=NOW()
`, [
dwSku, mfrSku, p.handle, patternName, colorName ? toTitleCase(colorName) : null, title, p.product_type,
p.body_html, descText, priceNum, null, specs.width, specs.length, specs.repeat, specs.material, specs.match,
colorTags, styleTags, allImages, allImages[0] || null, variantImg,
`https://www.astek.com/products/${p.handle}`, JSON.stringify(v),
]);
rows++;
}
}
// update the existing canonical Astek vendor_registry row (internal, skip_shopify true)
await client.query(`
UPDATE vendor_registry
SET sku_prefix=$1, sku_range_start=$2, catalog_table='astek_catalog',
skip_shopify=true, is_active=true,
notes='INTERNAL ONLY — astek.designerwallcoverings.com landing; deliberately NOT on Shopify',
updated_at=NOW()
WHERE vendor_code=$3
`, [DW_PREFIX + '-', SKU_RANGE_START, VENDOR_CODE]).catch(e => console.warn('vendor_registry update skipped:', e.message));
} finally {
client.release();
}
const tot = await pool.query('SELECT COUNT(*) c, COUNT(DISTINCT pattern_name) p FROM astek_catalog');
console.log(`\n=== DONE ===`);
console.log(`catalog rows (variants): ${rows} | distinct patterns: ${tot.rows[0].p}`);
console.log(`variants seen: ${variants} | images seen: ${images}`);
console.log(`DW SKUs newly registered: ${registered} | dupes reused: ${dups} | skipped: ${skipped}`);
console.log(`astek_catalog total rows now: ${tot.rows[0].c}`);
await pool.end();
}
main().catch(e => { console.error('FATAL', e); process.exit(1); });