217 lines
10 KiB
PL/PgSQL
217 lines
10 KiB
PL/PgSQL
-- =============================================================================
|
|
-- OEM Vehicle Stock Crawler - PostgreSQL Database Schema
|
|
-- =============================================================================
|
|
|
|
-- Enable UUID extension if not already available
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- Function to automatically update 'updated_at' column
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- =============================================================================
|
|
-- 1. Table: crawl_runs (Execution telemetry & audit log)
|
|
-- =============================================================================
|
|
CREATE TABLE IF NOT EXISTS crawl_runs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
brand VARCHAR(50) NOT NULL,
|
|
market_country VARCHAR(2) NOT NULL DEFAULT 'DE',
|
|
crawler_name VARCHAR(100) NOT NULL,
|
|
crawler_version VARCHAR(20),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'PENDING'
|
|
CHECK (status IN ('PENDING', 'RUNNING', 'COMPLETED', 'FAILED', 'PARTIAL')),
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
finished_at TIMESTAMPTZ,
|
|
duration_ms INTEGER,
|
|
items_discovered INTEGER NOT NULL DEFAULT 0,
|
|
items_inserted INTEGER NOT NULL DEFAULT 0,
|
|
items_updated INTEGER NOT NULL DEFAULT 0,
|
|
items_unmodified INTEGER NOT NULL DEFAULT 0,
|
|
items_delisted INTEGER NOT NULL DEFAULT 0,
|
|
items_failed INTEGER NOT NULL DEFAULT 0,
|
|
error_summary TEXT,
|
|
config_snapshot JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_crawl_runs_brand_status
|
|
ON crawl_runs(brand, status, started_at DESC);
|
|
|
|
-- =============================================================================
|
|
-- 2. Table: vehicles (Master Vehicle Stock Inventory)
|
|
-- =============================================================================
|
|
CREATE TABLE IF NOT EXISTS vehicles (
|
|
-- Identifiers
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
brand VARCHAR(50) NOT NULL,
|
|
oem_vehicle_id VARCHAR(100) NOT NULL,
|
|
vin VARCHAR(17),
|
|
|
|
-- Model & Classification
|
|
series VARCHAR(100) NOT NULL,
|
|
model_name VARCHAR(255) NOT NULL,
|
|
trim_line VARCHAR(100),
|
|
body_type VARCHAR(50),
|
|
condition VARCHAR(30) NOT NULL DEFAULT 'new'
|
|
CHECK (condition IN ('new', 'used', 'demonstration', 'day_registration', 'certified_pre_owned')),
|
|
model_year SMALLINT,
|
|
first_registration_date DATE,
|
|
previous_owners_count SMALLINT,
|
|
|
|
-- Powertrain & Technical Specifications
|
|
fuel_type VARCHAR(30) NOT NULL DEFAULT 'petrol'
|
|
CHECK (fuel_type IN ('bev', 'phev', 'petrol', 'diesel', 'mhev', 'fcev', 'cng', 'lpg', 'hybrid_other')),
|
|
transmission VARCHAR(30)
|
|
CHECK (transmission IN ('automatic', 'manual', 'direct_drive', 'dual_clutch', 'cvt', 'other')),
|
|
drivetrain VARCHAR(20)
|
|
CHECK (drivetrain IN ('awd', 'rwd', 'fwd', '4x4', 'other')),
|
|
power_kw INTEGER,
|
|
power_hp INTEGER,
|
|
battery_capacity NUMERIC(6,2),
|
|
battery_capacity_unit VARCHAR(10)
|
|
CHECK (battery_capacity_unit IS NULL OR battery_capacity_unit IN ('kwh', 'ah')),
|
|
battery_capacity_gross_kwh NUMERIC(6,2),
|
|
battery_capacity_net_kwh NUMERIC(6,2),
|
|
charging_power_ac_kw NUMERIC(5,2),
|
|
charging_power_dc_kw NUMERIC(5,2),
|
|
charging_time_dc_minutes SMALLINT,
|
|
electric_range_wltp_km INTEGER,
|
|
consumption_combined NUMERIC(5,2),
|
|
co2_emissions_g_km NUMERIC(5,1),
|
|
mileage_km INTEGER NOT NULL DEFAULT 0,
|
|
|
|
-- Colors & Upholstery
|
|
exterior_color VARCHAR(100),
|
|
exterior_color_code VARCHAR(50),
|
|
exterior_color_generic VARCHAR(50),
|
|
interior_color VARCHAR(100),
|
|
interior_material VARCHAR(50),
|
|
|
|
-- Pricing, Financing & Availability
|
|
currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
|
|
base_price_gross NUMERIC(12,2),
|
|
options_price_gross NUMERIC(12,2),
|
|
price_gross NUMERIC(12,2) NOT NULL,
|
|
price_net NUMERIC(12,2),
|
|
msrp_gross NUMERIC(12,2),
|
|
discount_percentage NUMERIC(5,2),
|
|
vat_deductible BOOLEAN,
|
|
monthly_rate NUMERIC(10,2),
|
|
leasing_details JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
status VARCHAR(30) NOT NULL DEFAULT 'in_stock'
|
|
CHECK (status IN ('in_stock', 'in_transit', 'on_order', 'reserved', 'sold', 'delisted')),
|
|
availability_date DATE,
|
|
delivery_time_days INTEGER,
|
|
|
|
-- Location, Dealer & Media
|
|
dealer_name VARCHAR(255),
|
|
dealer_id VARCHAR(100),
|
|
location_city VARCHAR(100),
|
|
location_postal_code VARCHAR(20),
|
|
location_country VARCHAR(2) NOT NULL DEFAULT 'DE',
|
|
latitude NUMERIC(9,6),
|
|
longitude NUMERIC(9,6),
|
|
vehicle_url TEXT,
|
|
image_urls JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
thumbnail_url TEXT,
|
|
|
|
-- Timestamps, Audit & Change Tracking
|
|
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
delisted_at TIMESTAMPTZ,
|
|
last_crawl_run_id UUID REFERENCES crawl_runs(id) ON DELETE SET NULL,
|
|
content_hash VARCHAR(64) NOT NULL,
|
|
raw_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
raw_payload_path VARCHAR(512),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Compound uniqueness constraint to avoid duplicate vehicles per OEM per country
|
|
CONSTRAINT uq_vehicles_brand_market_oem_id UNIQUE (brand, location_country, oem_vehicle_id)
|
|
);
|
|
|
|
-- Triggers
|
|
DROP TRIGGER IF EXISTS trg_vehicles_updated_at ON vehicles;
|
|
CREATE TRIGGER trg_vehicles_updated_at
|
|
BEFORE UPDATE ON vehicles
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Indexes on vehicles
|
|
CREATE INDEX IF NOT EXISTS idx_vehicles_brand_series
|
|
ON vehicles(brand, series);
|
|
CREATE INDEX IF NOT EXISTS idx_vehicles_fuel_transmission
|
|
ON vehicles(fuel_type, transmission);
|
|
CREATE INDEX IF NOT EXISTS idx_vehicles_price_gross
|
|
ON vehicles(price_gross);
|
|
CREATE INDEX IF NOT EXISTS idx_vehicles_status_last_seen
|
|
ON vehicles(status, last_seen_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_vehicles_vin
|
|
ON vehicles(vin) WHERE vin IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_vehicles_location
|
|
ON vehicles(location_country, location_city);
|
|
|
|
-- =============================================================================
|
|
-- 3. Table: vehicle_options (Normalized Equipment & Option Pricing)
|
|
-- =============================================================================
|
|
CREATE TABLE IF NOT EXISTS vehicle_options (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE CASCADE,
|
|
option_code VARCHAR(50),
|
|
name VARCHAR(255) NOT NULL,
|
|
category VARCHAR(100),
|
|
price_gross NUMERIC(10,2),
|
|
price_net NUMERIC(10,2),
|
|
is_standard BOOLEAN NOT NULL DEFAULT FALSE,
|
|
description TEXT,
|
|
raw_data JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_vehicle_options_vehicle_id
|
|
ON vehicle_options(vehicle_id);
|
|
CREATE INDEX IF NOT EXISTS idx_vehicle_options_code
|
|
ON vehicle_options(option_code);
|
|
CREATE INDEX IF NOT EXISTS idx_vehicle_options_category
|
|
ON vehicle_options(category);
|
|
|
|
-- =============================================================================
|
|
-- 4. Table: vehicle_price_history (Historical Price & Status Snapshots)
|
|
-- =============================================================================
|
|
CREATE TABLE IF NOT EXISTS vehicle_price_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE CASCADE,
|
|
crawl_run_id UUID NOT NULL REFERENCES crawl_runs(id) ON DELETE CASCADE,
|
|
price_gross NUMERIC(12,2) NOT NULL,
|
|
price_net NUMERIC(12,2),
|
|
monthly_rate NUMERIC(10,2),
|
|
status VARCHAR(30) NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_price_history_vehicle_recorded
|
|
ON vehicle_price_history(vehicle_id, recorded_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_price_history_crawl_run
|
|
ON vehicle_price_history(crawl_run_id);
|
|
|
|
-- =============================================================================
|
|
-- 5. Table: crawl_logs (Granular Diagnostic & Error Logging)
|
|
-- =============================================================================
|
|
CREATE TABLE IF NOT EXISTS crawl_logs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
crawl_run_id UUID NOT NULL REFERENCES crawl_runs(id) ON DELETE CASCADE,
|
|
vehicle_id UUID REFERENCES vehicles(id) ON DELETE SET NULL,
|
|
level VARCHAR(10) NOT NULL CHECK (level IN ('DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL')),
|
|
stage VARCHAR(50) NOT NULL CHECK (stage IN ('FETCH', 'PARSE', 'VALIDATE', 'PERSIST', 'DELIST_CHECK', 'GENERAL')),
|
|
message TEXT NOT NULL,
|
|
error_context JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_crawl_logs_run_level
|
|
ON crawl_logs(crawl_run_id, level, created_at DESC);
|