9b5e568e72
Fixes: - Negotiate route: added auth check (only shipper or bidder can negotiate) - Negotiate route: added notification to other party - All payment views: removed /100 division (amounts stored in rupees, not paise) - Migration 006: updated platform_config seed values to rupees - Migration 007: added current_lat/current_lng columns to vehicles table - Added bulk-parser route to marketplace.js - Added Bulk WhatsApp Parser link to portal sidebar Seed Data: - scripts/seed-demo.js: 5 shippers, 5 drivers, 8 loads, sample bids - Idempotent: skips if data already exists
28 lines
1.3 KiB
SQL
28 lines
1.3 KiB
SQL
-- ============================================================
|
|
-- FreightDesk — Migration 007: Driver Location Tracking
|
|
-- GPS location history for real-time tracking
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS vehicle_locations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE CASCADE,
|
|
lat DECIMAL(10,8) NOT NULL,
|
|
lng DECIMAL(11,8) NOT NULL,
|
|
accuracy DECIMAL(8,2),
|
|
heading DECIMAL(6,2),
|
|
speed DECIMAL(6,2),
|
|
recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_vehicle_locations_vehicle ON vehicle_locations(vehicle_id);
|
|
CREATE INDEX IF NOT EXISTS idx_vehicle_locations_time ON vehicle_locations(recorded_at);
|
|
CREATE INDEX IF NOT EXISTS idx_vehicle_locations_vehicle_time ON vehicle_locations(vehicle_id, recorded_at DESC);
|
|
|
|
-- Add current location columns to vehicles
|
|
ALTER TABLE vehicles ADD COLUMN IF NOT EXISTS current_lat DECIMAL(10,8);
|
|
ALTER TABLE vehicles ADD COLUMN IF NOT EXISTS current_lng DECIMAL(11,8);
|
|
|
|
-- Enable PostGIS-like functionality with btree_gist for spatial queries
|
|
-- (In production, use PostGIS extension)
|
|
CREATE INDEX IF NOT EXISTS idx_vehicles_location ON vehicles(current_lat, current_lng)
|
|
WHERE current_lat IS NOT NULL AND current_lng IS NOT NULL;
|