← back to Watches

database/schema.sql

504 lines

-- ============================================================================
-- OMEGA WATCH PRICE HISTORY - PostgreSQL Database Schema
-- ============================================================================
-- Purpose: Production-grade relational database for watch price tracking
-- Features: Normalization, constraints, indices, partitioning, audit trails
-- ============================================================================

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For full-text search optimization

-- ============================================================================
-- DOMAIN TYPES & ENUMS
-- ============================================================================

-- Custom domain types for data validation
CREATE DOMAIN positive_price AS NUMERIC(12,2)
    CHECK (VALUE >= 0);

CREATE DOMAIN positive_integer AS INTEGER
    CHECK (VALUE >= 0);

CREATE DOMAIN watch_year AS INTEGER
    CHECK (VALUE >= 1848 AND VALUE <= EXTRACT(YEAR FROM CURRENT_DATE) + 1);

-- Enums for controlled vocabularies
CREATE TYPE watch_series_enum AS ENUM (
    'Speedmaster',
    'Seamaster',
    'Constellation',
    'De Ville',
    'Railmaster',
    'Flightmaster',
    'Cosmic',
    'Genève',
    'Specialty'
);

CREATE TYPE condition_enum AS ENUM (
    'new',
    'excellent',
    'good',
    'fair',
    'vintage',
    'prototype'
);

CREATE TYPE case_material_enum AS ENUM (
    'stainless_steel',
    'gold_18k',
    'gold_14k',
    'platinum',
    'titanium',
    'ceramic',
    'bronze',
    'two_tone',
    'canopus_gold',
    'sedna_gold'
);

-- ============================================================================
-- CORE TABLES
-- ============================================================================

-- Watches Table - Main entity with business rules
CREATE TABLE watches (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    slug VARCHAR(255) UNIQUE NOT NULL,
    model VARCHAR(255) NOT NULL,
    series watch_series_enum NOT NULL,
    reference VARCHAR(100) NOT NULL,
    year_introduced watch_year NOT NULL,
    description TEXT,
    detailed_description TEXT,
    image_url VARCHAR(500),

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_active BOOLEAN DEFAULT true,
    is_limited_edition BOOLEAN DEFAULT false,
    limited_edition_count INTEGER,

    -- Full-text search column (auto-updated via trigger)
    search_vector tsvector,

    -- Constraints
    CONSTRAINT valid_slug CHECK (slug ~* '^[a-z0-9-]+$'),
    CONSTRAINT valid_limited_edition CHECK (
        (is_limited_edition = false AND limited_edition_count IS NULL) OR
        (is_limited_edition = true AND limited_edition_count > 0)
    )
);

-- Create GIN index for full-text search
CREATE INDEX idx_watches_search ON watches USING GIN(search_vector);
CREATE INDEX idx_watches_series ON watches(series);
CREATE INDEX idx_watches_year ON watches(year_introduced);
CREATE INDEX idx_watches_active ON watches(is_active) WHERE is_active = true;

-- Specifications Table - One-to-one relationship with watches
CREATE TABLE specifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    watch_id UUID NOT NULL UNIQUE REFERENCES watches(id) ON DELETE CASCADE,

    -- Case specifications
    case_size VARCHAR(50),
    case_material case_material_enum,
    case_thickness VARCHAR(50),
    lug_width VARCHAR(50),

    -- Movement specifications
    movement_calibre VARCHAR(100),
    movement_type VARCHAR(100),
    power_reserve VARCHAR(50),

    -- Water resistance and crystal
    water_resistance VARCHAR(50),
    crystal_type VARCHAR(50),

    -- Aesthetic properties
    dial_color VARCHAR(100),
    bezel_type VARCHAR(100),

    -- Physical properties
    weight VARCHAR(50),

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_specifications_watch_id ON specifications(watch_id);
CREATE INDEX idx_specifications_material ON specifications(case_material);

-- Features Table - Many-to-many relationship
CREATE TABLE features (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) UNIQUE NOT NULL,
    category VARCHAR(50), -- e.g., 'complication', 'functionality', 'certification'
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_features_category ON features(category);

-- Watch Features Junction Table
CREATE TABLE watch_features (
    watch_id UUID REFERENCES watches(id) ON DELETE CASCADE,
    feature_id UUID REFERENCES features(id) ON DELETE CASCADE,
    PRIMARY KEY (watch_id, feature_id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_watch_features_watch ON watch_features(watch_id);
CREATE INDEX idx_watch_features_feature ON watch_features(feature_id);

-- ============================================================================
-- PRICE HISTORY - PARTITIONED TABLE FOR SCALABILITY
-- ============================================================================

-- Main price history table (partitioned by year)
CREATE TABLE price_history (
    id UUID DEFAULT uuid_generate_v4(),
    watch_id UUID NOT NULL REFERENCES watches(id) ON DELETE CASCADE,
    year watch_year NOT NULL,
    price positive_price NOT NULL,
    condition condition_enum NOT NULL DEFAULT 'new',
    source VARCHAR(255),
    notes TEXT,
    currency CHAR(3) DEFAULT 'USD',

    -- Market context
    auction_house VARCHAR(100),
    lot_number VARCHAR(50),

    -- Data quality
    is_estimate BOOLEAN DEFAULT false,
    confidence_level INTEGER CHECK (confidence_level >= 1 AND confidence_level <= 5),

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Constraints
    CONSTRAINT valid_currency CHECK (currency ~* '^[A-Z]{3}$'),
    PRIMARY KEY (id, year)
) PARTITION BY RANGE (year);

-- Create partitions for different decades (1950-2030)
CREATE TABLE price_history_1950_1979 PARTITION OF price_history
    FOR VALUES FROM (1950) TO (1980);

CREATE TABLE price_history_1980_1999 PARTITION OF price_history
    FOR VALUES FROM (1980) TO (2000);

CREATE TABLE price_history_2000_2019 PARTITION OF price_history
    FOR VALUES FROM (2000) TO (2020);

CREATE TABLE price_history_2020_2030 PARTITION OF price_history
    FOR VALUES FROM (2020) TO (2031);

-- Indices on partitioned table
CREATE INDEX idx_price_history_watch_year ON price_history(watch_id, year);
CREATE INDEX idx_price_history_price ON price_history(price);
CREATE INDEX idx_price_history_condition ON price_history(condition);

-- ============================================================================
-- ANALYTICS & AGGREGATIONS
-- ============================================================================

-- Materialized view for watch statistics (fast reads)
CREATE MATERIALIZED VIEW watch_statistics AS
SELECT
    w.id,
    w.slug,
    w.model,
    w.series,
    COUNT(DISTINCT ph.id) as price_point_count,
    MIN(ph.year) as earliest_price_year,
    MAX(ph.year) as latest_price_year,
    MIN(ph.price) as lowest_price,
    MAX(ph.price) as highest_price,
    AVG(ph.price) as average_price,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.price) as median_price,

    -- Calculate appreciation (latest price / earliest price)
    CASE
        WHEN MIN(ph.price) > 0 THEN
            ((MAX(ph.price) - MIN(ph.price)) / MIN(ph.price) * 100)
        ELSE NULL
    END as appreciation_percentage,

    -- Annualized return
    CASE
        WHEN MIN(ph.price) > 0 AND (MAX(ph.year) - MIN(ph.year)) > 0 THEN
            (POWER(MAX(ph.price) / MIN(ph.price), 1.0 / (MAX(ph.year) - MIN(ph.year))) - 1) * 100
        ELSE NULL
    END as annualized_return_percentage,

    -- Last updated timestamp
    NOW() as calculated_at
FROM watches w
LEFT JOIN price_history ph ON w.id = ph.watch_id
WHERE w.is_active = true
GROUP BY w.id, w.slug, w.model, w.series;

-- Index for fast lookups
CREATE UNIQUE INDEX idx_watch_statistics_id ON watch_statistics(id);
CREATE INDEX idx_watch_statistics_appreciation ON watch_statistics(appreciation_percentage DESC NULLS LAST);
CREATE INDEX idx_watch_statistics_series ON watch_statistics(series);

-- ============================================================================
-- USER ACTIVITY TRACKING
-- ============================================================================

-- User watchlists
CREATE TABLE user_watchlists (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id VARCHAR(255) NOT NULL, -- Would be FK to users table in production
    watch_id UUID NOT NULL REFERENCES watches(id) ON DELETE CASCADE,
    notes TEXT,
    target_price positive_price,
    alert_enabled BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(user_id, watch_id)
);

CREATE INDEX idx_watchlists_user ON user_watchlists(user_id);
CREATE INDEX idx_watchlists_watch ON user_watchlists(watch_id);
CREATE INDEX idx_watchlists_alerts ON user_watchlists(alert_enabled) WHERE alert_enabled = true;

-- View tracking (for trending calculations)
CREATE TABLE watch_views (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    watch_id UUID NOT NULL REFERENCES watches(id) ON DELETE CASCADE,
    session_id VARCHAR(255),
    user_agent TEXT,
    ip_address INET,
    viewed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Partition by month for efficient queries
CREATE INDEX idx_watch_views_watch_date ON watch_views(watch_id, viewed_at DESC);
CREATE INDEX idx_watch_views_date ON watch_views(viewed_at DESC);

-- ============================================================================
-- AUDIT TRAIL
-- ============================================================================

-- Generic audit log for all table changes
CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    table_name VARCHAR(100) NOT NULL,
    record_id UUID NOT NULL,
    operation VARCHAR(10) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values JSONB,
    new_values JSONB,
    changed_by VARCHAR(255),
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_audit_log_table_record ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_log_date ON audit_log(changed_at DESC);

-- ============================================================================
-- DATA SOURCES & METADATA
-- ============================================================================

-- Track data sources for provenance
CREATE TABLE data_sources (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) UNIQUE NOT NULL,
    type VARCHAR(50), -- 'auction', 'dealer', 'catalog', 'estimate'
    url VARCHAR(500),
    reliability_score INTEGER CHECK (reliability_score >= 1 AND reliability_score <= 5),
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Link price history to sources
CREATE TABLE price_history_sources (
    price_history_id UUID NOT NULL,
    data_source_id UUID NOT NULL REFERENCES data_sources(id),
    PRIMARY KEY (price_history_id, data_source_id)
);

-- ============================================================================
-- TRIGGERS FOR AUTOMATION
-- ============================================================================

-- Auto-update timestamps
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER watches_update_timestamp
    BEFORE UPDATE ON watches
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER specifications_update_timestamp
    BEFORE UPDATE ON specifications
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- Auto-update full-text search vector
CREATE OR REPLACE FUNCTION update_watch_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.model, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.series::text, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.reference, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'C') ||
        setweight(to_tsvector('english', COALESCE(NEW.detailed_description, '')), 'D');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER watches_search_vector_update
    BEFORE INSERT OR UPDATE ON watches
    FOR EACH ROW
    EXECUTE FUNCTION update_watch_search_vector();

-- Audit trail trigger
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, operation, old_values, changed_by)
        VALUES (TG_TABLE_NAME, OLD.id, TG_OP, row_to_json(OLD), current_user);
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_by)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, row_to_json(OLD), row_to_json(NEW), current_user);
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, operation, new_values, changed_by)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, row_to_json(NEW), current_user);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Apply audit triggers to key tables
CREATE TRIGGER watches_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON watches
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

CREATE TRIGGER price_history_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON price_history
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- ============================================================================
-- FUNCTIONS & STORED PROCEDURES
-- ============================================================================

-- Function to refresh materialized view
CREATE OR REPLACE FUNCTION refresh_watch_statistics()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY watch_statistics;
END;
$$ LANGUAGE plpgsql;

-- Function to get trending watches (most viewed in last N days)
CREATE OR REPLACE FUNCTION get_trending_watches(days_back INTEGER DEFAULT 7, limit_count INTEGER DEFAULT 10)
RETURNS TABLE (
    watch_id UUID,
    model VARCHAR(255),
    view_count BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        w.id,
        w.model,
        COUNT(*) as view_count
    FROM watches w
    INNER JOIN watch_views wv ON w.id = wv.watch_id
    WHERE wv.viewed_at >= NOW() - (days_back || ' days')::INTERVAL
    GROUP BY w.id, w.model
    ORDER BY view_count DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

-- Function to calculate price appreciation between two years
CREATE OR REPLACE FUNCTION calculate_appreciation(
    p_watch_id UUID,
    p_start_year INTEGER,
    p_end_year INTEGER
)
RETURNS TABLE (
    start_price NUMERIC,
    end_price NUMERIC,
    appreciation_pct NUMERIC,
    annualized_return NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    WITH price_points AS (
        SELECT
            MIN(CASE WHEN year = p_start_year THEN price END) as start_price,
            MAX(CASE WHEN year = p_end_year THEN price END) as end_price
        FROM price_history
        WHERE watch_id = p_watch_id
    )
    SELECT
        pp.start_price,
        pp.end_price,
        CASE
            WHEN pp.start_price > 0 THEN
                ((pp.end_price - pp.start_price) / pp.start_price * 100)
            ELSE NULL
        END as appreciation_pct,
        CASE
            WHEN pp.start_price > 0 AND (p_end_year - p_start_year) > 0 THEN
                (POWER(pp.end_price / pp.start_price, 1.0 / (p_end_year - p_start_year)) - 1) * 100
            ELSE NULL
        END as annualized_return
    FROM price_points pp;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- INITIAL DATA SOURCES
-- ============================================================================

INSERT INTO data_sources (name, type, reliability_score, description) VALUES
    ('Christies Speedmaster 50 Auction', 'auction', 5, 'Major auction house - highly reliable'),
    ('Sothebys Omega Collection', 'auction', 5, 'Major auction house - highly reliable'),
    ('ChronoCentric Historical Database', 'catalog', 4, 'Historical price database'),
    ('Chrono24 Market Data', 'dealer', 4, 'Leading watch marketplace'),
    ('WatchCharts Index', 'estimate', 4, 'Market index and analytics'),
    ('Vintage Omega Forums', 'community', 3, 'Community-sourced data'),
    ('Historical Catalogs', 'catalog', 5, 'Official manufacturer catalogs');

-- ============================================================================
-- PERFORMANCE OPTIMIZATION
-- ============================================================================

-- Analyze tables for query planner
ANALYZE watches;
ANALYZE price_history;
ANALYZE specifications;

-- Grant permissions (adjust as needed)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO omega_app_user;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO omega_app_user;

COMMENT ON DATABASE omega_watches IS 'Omega Watch Price History Tracking System';
COMMENT ON TABLE watches IS 'Main watch catalog with core information';
COMMENT ON TABLE price_history IS 'Historical price tracking (partitioned by year)';
COMMENT ON TABLE specifications IS 'Technical specifications for watches';
COMMENT ON MATERIALIZED VIEW watch_statistics IS 'Pre-calculated statistics for performance';