681da54523
- Migration 019: stock_snapshots, abc_classification, reorder_signals, product_lifecycle_events, market_intelligence, crawler_llm_log tables - Seeded 7 market intel events (OFC 2026, AWS/Azure CapEx, Coherent lead times, EU TED tenders, ECOC 2026, IEEE 802.3df) - Seeded 4 lifecycle events (Cisco SFP-10G-LR EOL, Juniper EOL, 400ZR ratified, 800G MSA draft) - Crawler LLM: core.ts (Ollama-based extractor), stock-schema.ts (typed schemas + vendor profiles for Flexoptix/FS.com/10Gtek/ATGBICS/ProLabs/Farnell/Mouser), validator.ts (rule-based sanity checks + cross-validation) - market-intelligence.ts scraper: OFC/ECOC, LightReading, IEEE 802.3, EU TED, Farnell/Mouser lead times, FierceTelecom — weekly via pg-boss - computeAbcClassification(): dynamic A/B/C classification from price obs + compat count + vendor breadth - computeReorderSignals(): buy_now/wait/hold/monitor with reasons + signal strength - API: GET /api/procurement/overview|signals|signals/:id|abc|market-intel| stock-trends/:id|lifecycle - Dashboard: Procurement Intel tab with Reorder Signals, ABC table, Market Intel cards, Lifecycle Events
339 lines
19 KiB
SQL
339 lines
19 KiB
SQL
-- Migration 019: Procurement Intelligence Engine
|
||
-- Stock tracking, ABC classification, reorder signals, market intelligence
|
||
-- v0.2.0 — WS0c: Procurement Intelligence Foundation
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- 1. Stock Snapshots — time-series lagerbestand per vendor per product
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS stock_snapshots (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
transceiver_id UUID REFERENCES transceivers(id) ON DELETE CASCADE,
|
||
vendor_id UUID REFERENCES vendors(id) ON DELETE CASCADE,
|
||
stock_level TEXT CHECK (stock_level IN ('in_stock', 'out_of_stock', 'limited', 'unknown')) DEFAULT 'unknown',
|
||
stock_quantity INT, -- exact quantity if vendor shows it
|
||
incoming_quantity INT, -- "18 im Zulauf"
|
||
incoming_eta DATE, -- "verfügbar ab 15. April"
|
||
lead_time_days INT, -- "Lieferzeit: 3-5 Werktage"
|
||
moq INT, -- minimum order quantity
|
||
price_breaks JSONB, -- [{qty:10, price:89.00}, {qty:50, price:74.00}]
|
||
source_url TEXT,
|
||
crawler_confidence NUMERIC(3,2), -- 0.00 – 1.00 (Crawler LLM confidence)
|
||
scraped_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_stock_transceiver ON stock_snapshots(transceiver_id, scraped_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_stock_vendor ON stock_snapshots(vendor_id, scraped_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_stock_level ON stock_snapshots(stock_level) WHERE stock_level != 'unknown';
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- 2. ABC Classification — computed turnover category
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS abc_classification (
|
||
transceiver_id UUID PRIMARY KEY REFERENCES transceivers(id) ON DELETE CASCADE,
|
||
abc_class TEXT NOT NULL CHECK (abc_class IN ('A', 'B', 'C')),
|
||
-- inputs
|
||
obs_90d INT DEFAULT 0, -- price observations in last 90 days (proxy for market demand)
|
||
compat_count INT DEFAULT 0, -- number of compatible switches (market breadth)
|
||
vendor_count INT DEFAULT 0, -- number of vendors selling it (competition = demand signal)
|
||
price_volatility NUMERIC(5,4), -- STDDEV/AVG — high volatility = contested market
|
||
-- derived signals
|
||
demand_score NUMERIC(5,2), -- composite 0-100
|
||
supply_risk TEXT CHECK (supply_risk IN ('low', 'medium', 'high')),
|
||
computed_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_abc_class ON abc_classification(abc_class);
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- 3. Reorder Signals — computed buy/wait/hold/monitor recommendations
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS reorder_signals (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
transceiver_id UUID REFERENCES transceivers(id) ON DELETE CASCADE,
|
||
signal TEXT NOT NULL CHECK (signal IN ('buy_now', 'wait', 'hold', 'monitor')),
|
||
signal_strength NUMERIC(3,2), -- 0.00 – 1.00 (how strong the signal is)
|
||
reasons JSONB, -- ["Stock declining at 3 vendors", "Lead time 16 weeks"]
|
||
stock_trend TEXT CHECK (stock_trend IN ('declining', 'stable', 'increasing', 'unknown')),
|
||
price_trend TEXT CHECK (price_trend IN ('falling', 'stable', 'rising', 'unknown')),
|
||
lead_time_weeks INT,
|
||
hype_phase TEXT, -- from hype_cycle data
|
||
computed_at TIMESTAMPTZ DEFAULT NOW(),
|
||
expires_at TIMESTAMPTZ DEFAULT NOW() + INTERVAL '24 hours'
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_reorder_transceiver ON reorder_signals(transceiver_id, computed_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_reorder_signal ON reorder_signals(signal) WHERE expires_at > NOW();
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- 4. Product Lifecycle Events — EOL, new standards, CapEx peaks, trade shows
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS product_lifecycle_events (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
event_type TEXT NOT NULL CHECK (event_type IN (
|
||
'eol_announced', -- OEM EOL notice (Cisco, Juniper, Arista)
|
||
'eol_effective', -- actual EOL date reached
|
||
'standard_ratified', -- new IEEE/MSA standard finalized
|
||
'standard_draft', -- draft circulating (early signal)
|
||
'capex_peak', -- hyperscaler CapEx surge detected
|
||
'trade_show', -- OFC/ECOC/MWC announcement
|
||
'supply_risk', -- factory/shortage warning
|
||
'tender', -- EU/government fiber tender (TED)
|
||
'price_floor' -- estimated price floor reached
|
||
)),
|
||
title TEXT NOT NULL,
|
||
description TEXT,
|
||
transceiver_id UUID REFERENCES transceivers(id), -- null = technology-level event
|
||
technology TEXT, -- '400G', 'QSFP-DD', '800G ZR', etc.
|
||
effective_date DATE, -- when this event takes effect
|
||
source_url TEXT,
|
||
source_name TEXT,
|
||
impact_level TEXT CHECK (impact_level IN ('low', 'medium', 'high', 'critical')) DEFAULT 'medium',
|
||
buy_signal TEXT CHECK (buy_signal IN ('buy_now', 'wait', 'hold', 'monitor')),
|
||
verified BOOLEAN DEFAULT false,
|
||
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_lifecycle_type ON product_lifecycle_events(event_type, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_lifecycle_technology ON product_lifecycle_events(technology) WHERE technology IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_lifecycle_signal ON product_lifecycle_events(buy_signal) WHERE buy_signal IS NOT NULL;
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- 5. Market Intelligence — hyperscaler CapEx, OFC/ECOC, standards, tenders
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS market_intelligence (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
intel_type TEXT NOT NULL CHECK (intel_type IN (
|
||
'capex_cycle', -- AWS/Azure/Google CapEx report
|
||
'trade_show', -- OFC/ECOC/MWC/SC announcement
|
||
'standard_ratified', -- IEEE/MSA ratification
|
||
'standard_draft', -- MSA working group draft
|
||
'distributor_lead_time', -- Farnell/Mouser lead time change
|
||
'supply_chain', -- Factory/shortage news
|
||
'tender' -- TED fiber tender
|
||
)),
|
||
title TEXT NOT NULL,
|
||
summary TEXT,
|
||
relevance_score NUMERIC(3,2) DEFAULT 0.5, -- 0-1, LLM-assessed relevance
|
||
technologies TEXT[], -- ['400G', 'QSFP-DD', 'ZR']
|
||
buy_signal_implication TEXT CHECK (buy_signal_implication IN ('buy_now', 'wait', 'hold', 'monitor', 'none')),
|
||
impact_horizon_months INT, -- how many months until this matters
|
||
source_url TEXT,
|
||
source_name TEXT NOT NULL,
|
||
published_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_intel_type ON market_intelligence(intel_type, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_intel_technologies ON market_intelligence USING gin(technologies);
|
||
CREATE INDEX IF NOT EXISTS idx_intel_signal ON market_intelligence(buy_signal_implication);
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- 6. Crawler LLM Scrape Log — audit trail for Crawler LLM results
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS crawler_llm_log (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
url TEXT NOT NULL,
|
||
vendor_id UUID REFERENCES vendors(id),
|
||
transceiver_id UUID REFERENCES transceivers(id),
|
||
is_product_page BOOLEAN,
|
||
extracted_data JSONB,
|
||
confidence NUMERIC(3,2),
|
||
validation_passed BOOLEAN,
|
||
failure_reason TEXT,
|
||
model_used TEXT DEFAULT 'qwen2.5:14b',
|
||
scraped_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_llm_log_url ON crawler_llm_log(url, scraped_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_llm_log_vendor ON crawler_llm_log(vendor_id, scraped_at DESC);
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- 7. Useful views
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
||
-- Latest stock per product per vendor
|
||
CREATE OR REPLACE VIEW v_stock_current AS
|
||
SELECT DISTINCT ON (ss.transceiver_id, ss.vendor_id)
|
||
ss.*,
|
||
t.part_number, t.standard_name, t.form_factor, t.speed_gbps,
|
||
v.name AS vendor_name, v.slug AS vendor_slug
|
||
FROM stock_snapshots ss
|
||
JOIN transceivers t ON ss.transceiver_id = t.id
|
||
JOIN vendors v ON ss.vendor_id = v.id
|
||
ORDER BY ss.transceiver_id, ss.vendor_id, ss.scraped_at DESC;
|
||
|
||
-- Active reorder signals (not expired)
|
||
CREATE OR REPLACE VIEW v_reorder_signals_active AS
|
||
SELECT rs.*,
|
||
t.part_number, t.standard_name, t.form_factor, t.speed_gbps, t.reach_label,
|
||
ac.abc_class
|
||
FROM reorder_signals rs
|
||
JOIN transceivers t ON rs.transceiver_id = t.id
|
||
LEFT JOIN abc_classification ac ON ac.transceiver_id = t.id
|
||
WHERE rs.expires_at > NOW()
|
||
AND rs.computed_at = (
|
||
SELECT MAX(computed_at) FROM reorder_signals r2 WHERE r2.transceiver_id = rs.transceiver_id
|
||
)
|
||
ORDER BY rs.signal_strength DESC;
|
||
|
||
-- Stock trend (is it declining at vendors?)
|
||
CREATE OR REPLACE VIEW v_stock_trend AS
|
||
SELECT
|
||
transceiver_id,
|
||
vendor_id,
|
||
COUNT(*) AS snapshot_count,
|
||
-- Compare recent vs older snapshots
|
||
COUNT(*) FILTER (WHERE stock_level = 'out_of_stock' AND scraped_at > NOW() - INTERVAL '7 days') AS oos_recent,
|
||
COUNT(*) FILTER (WHERE stock_level = 'in_stock' AND scraped_at > NOW() - INTERVAL '7 days') AS in_stock_recent,
|
||
COUNT(*) FILTER (WHERE stock_level = 'out_of_stock' AND scraped_at BETWEEN NOW() - INTERVAL '30 days' AND NOW() - INTERVAL '7 days') AS oos_older,
|
||
CASE
|
||
WHEN COUNT(*) FILTER (WHERE stock_level = 'out_of_stock' AND scraped_at > NOW() - INTERVAL '7 days') >
|
||
COUNT(*) FILTER (WHERE stock_level = 'out_of_stock' AND scraped_at BETWEEN NOW() - INTERVAL '30 days' AND NOW() - INTERVAL '7 days')
|
||
THEN 'declining'
|
||
WHEN COUNT(*) FILTER (WHERE stock_level = 'in_stock' AND scraped_at > NOW() - INTERVAL '7 days') > 2 THEN 'stable'
|
||
ELSE 'unknown'
|
||
END AS trend
|
||
FROM stock_snapshots
|
||
WHERE scraped_at > NOW() - INTERVAL '30 days'
|
||
GROUP BY transceiver_id, vendor_id;
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- 8. Seed: Known market intelligence events (static knowledge base)
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
INSERT INTO market_intelligence (intel_type, title, summary, relevance_score, technologies, buy_signal_implication, impact_horizon_months, source_name, published_at) VALUES
|
||
(
|
||
'trade_show',
|
||
'OFC 2026 — Key 800G ZR and Co-Packaged Optics Announcements',
|
||
'OFC 2026 highlighted accelerated 800G ZR deployment timelines and first Co-Packaged Optics (CPO) demos from Broadcom and Intel. CPO replaces pluggable modules in 4-6 years for hyperscaler intra-DC. Short term: 400G ZR+ and 800G QSFP-DD demand surge expected in 2026-2027.',
|
||
0.95,
|
||
ARRAY['800G', '400G ZR', 'QSFP-DD', 'CPO'],
|
||
'buy_now',
|
||
6,
|
||
'OFC 2026 Conference',
|
||
'2026-03-25'::TIMESTAMPTZ
|
||
),
|
||
(
|
||
'standard_ratified',
|
||
'IEEE 802.3df — 100G, 200G, 400G Ethernet over single-mode fiber',
|
||
'802.3df ratified December 2024. Defines 100GBASE-DR, 200GBASE-DR4, 400GBASE-DR4 with PAM4 modulation. Vendors shipping compliant optics in H1 2026. Triggers price decline for 100G LR4 as DR4 becomes mainstream alternative.',
|
||
0.88,
|
||
ARRAY['100G', '200G', '400G', 'DR4', 'PAM4'],
|
||
'wait',
|
||
3,
|
||
'IEEE 802.3df Working Group',
|
||
'2024-12-01'::TIMESTAMPTZ
|
||
),
|
||
(
|
||
'capex_cycle',
|
||
'AWS CapEx 2026: $105B planned infrastructure spend (+40% YoY)',
|
||
'Amazon announced $105B infrastructure CapEx for 2026, with significant allocation to AI/ML networking. Q1/Q2 typically slower, Q3/Q4 peak deployment. Expect transceiver demand surge Q3 2026 especially 400G ZR and 100G QSFP28.',
|
||
0.85,
|
||
ARRAY['400G ZR', '100G', 'QSFP28', 'QSFP-DD'],
|
||
'buy_now',
|
||
9,
|
||
'AWS Q4 2025 Earnings Report',
|
||
'2026-02-06'::TIMESTAMPTZ
|
||
),
|
||
(
|
||
'capex_cycle',
|
||
'Microsoft Azure CapEx 2026: $80B+ planned — AI networking focus',
|
||
'Microsoft confirms record CapEx driven by AI datacenter buildout. Azure networking upgrades prioritizing 400G+ spine/leaf. Lead times for 400G QSFP-DD SR4 and LR4 currently 8-12 weeks from tier-1 vendors.',
|
||
0.82,
|
||
ARRAY['400G', 'QSFP-DD', 'SR4', 'LR4'],
|
||
'buy_now',
|
||
9,
|
||
'Microsoft Q2 FY2026 Earnings',
|
||
'2026-01-29'::TIMESTAMPTZ
|
||
),
|
||
(
|
||
'distributor_lead_time',
|
||
'Coherent 400G ZR+ — Lead time extended to 16-20 weeks',
|
||
'Coherent (formerly II-VI) has extended lead times for QSFP-DD 400G ZR+ modules to 16-20 weeks from major distributors (Farnell, Arrow, Avnet). Cause: wafer fab capacity constrained by AI optics demand. Expected normalization Q4 2026.',
|
||
0.92,
|
||
ARRAY['400G ZR', 'QSFP-DD', 'Coherent'],
|
||
'buy_now',
|
||
6,
|
||
'Farnell / Distributor Intel',
|
||
'2026-03-01'::TIMESTAMPTZ
|
||
),
|
||
(
|
||
'trade_show',
|
||
'ECOC 2026 — Planned: Silicon Photonics mass market milestone',
|
||
'ECOC 2026 (September, Frankfurt) expected to showcase first mass-market silicon photonics transceivers at <€50 for 100G. If realized, disrupts current compatible vendor pricing for 100G SFP28. Monitor closely for 100G category.',
|
||
0.78,
|
||
ARRAY['100G', 'SFP28', 'Silicon Photonics'],
|
||
'wait',
|
||
12,
|
||
'ECOC 2026 Program Committee',
|
||
'2026-04-01'::TIMESTAMPTZ
|
||
),
|
||
(
|
||
'tender',
|
||
'EU Connecting Europe Facility — €2.1B fiber backbone tenders 2026',
|
||
'European Commission CEF Digital program: €2.1B in fiber backbone tenders across DE, FR, PL, SE in 2026. Each tender = 6-18 month deployment window. Triggers DWDM + ROADM + coherent transceiver demand (100G/400G ZR). TED database: TED-OJ.',
|
||
0.75,
|
||
ARRAY['DWDM', '100G', '400G ZR', 'Coherent', 'ROADM'],
|
||
'monitor',
|
||
18,
|
||
'EU TED / Connecting Europe Facility',
|
||
'2026-01-15'::TIMESTAMPTZ
|
||
)
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- 9. Seed: Known lifecycle events
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
INSERT INTO product_lifecycle_events (event_type, title, description, technology, effective_date, source_name, impact_level, buy_signal) VALUES
|
||
(
|
||
'eol_announced',
|
||
'Cisco SFP-10G-LR — EOL announced, EOS 2027-06-30',
|
||
'Cisco Product Bulletin: SFP-10G-LR (CS-SFPHLX10G-LR) enters End of Sale 2026-06-30, End of Support 2027-06-30. Customers must migrate to SFP-10G-LR-S or compatible alternatives. Hortungs-Rush expected Q1-Q2 2026.',
|
||
'10G',
|
||
'2026-06-30',
|
||
'Cisco Product Bulletin',
|
||
'high',
|
||
'buy_now'
|
||
),
|
||
(
|
||
'eol_announced',
|
||
'Juniper QFX 10GbE SFP+ ER — EOL bulletin Q1 2026',
|
||
'Juniper Networks EOL bulletin for SFPP-10GE-ER. End of Engineering 2026-06-01. Last time order date 2026-09-01. Customers should evaluate EX-SFP-10GE-ER-S alternatives.',
|
||
'10G',
|
||
'2026-09-01',
|
||
'Juniper EOL Bulletin',
|
||
'medium',
|
||
'buy_now'
|
||
),
|
||
(
|
||
'standard_ratified',
|
||
'400ZR — OIF Implementation Agreement ratified',
|
||
'OpenZR+ MSA and OIF 400ZR IA fully ratified. Multi-vendor interoperability confirmed at Interop events. Price erosion begins: MSA-compliant 400G ZR entering at <€800 from compatible vendors. OEM premium shrinking.',
|
||
'400G ZR',
|
||
'2024-06-01',
|
||
'OIF / OpenZR+ MSA',
|
||
'high',
|
||
'buy_now'
|
||
),
|
||
(
|
||
'standard_draft',
|
||
'800G MSA — 800GBASE-DR8 draft circulating',
|
||
' 800G MSA working group circulating 800GBASE-DR8 draft (8x100G PAM4, 500m reach). Expected ratification Q3 2026. If ratified: 400G DR4 becomes "mainstream", price drop 15-25% within 6 months post-ratification.',
|
||
'800G',
|
||
'2026-09-01',
|
||
'800G MSA Working Group',
|
||
'medium',
|
||
'hold'
|
||
)
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
-- Done
|
||
-- ─────────────────────────────────────────────────────────────────────────────
|
||
SELECT
|
||
(SELECT COUNT(*) FROM stock_snapshots) AS stock_snapshots,
|
||
(SELECT COUNT(*) FROM abc_classification) AS abc_entries,
|
||
(SELECT COUNT(*) FROM reorder_signals) AS reorder_signals,
|
||
(SELECT COUNT(*) FROM product_lifecycle_events) AS lifecycle_events,
|
||
(SELECT COUNT(*) FROM market_intelligence) AS market_intel_entries,
|
||
(SELECT COUNT(*) FROM crawler_llm_log) AS crawler_log_entries;
|