f917fb8014
- Server: Express.js with 13 API route files (auth, regulations, contacts, calendar, truck stops, bridges, weigh stations, alerts, load board, escort locator, orders, documents, contributions) - Database: PostgreSQL with Prisma ORM, 15 models covering all modules - Auth: JWT + bcrypt with role-based access control (driver/carrier/escort/admin) - Geospatial: Haversine distance filtering on truck stops, bridges, escorts - Seed script: Imports all existing mock data (51 states, contacts, equipment, truck stops, bridges, weigh stations, alerts, seasonal restrictions) - Frontend: All 10 data-driven pages now fetch from /api instead of mock-data.js - API client (api.js): Compatibility layer that transforms API responses to match existing frontend rendering code, minimizing page-level changes Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
309 lines
9.9 KiB
SQL
309 lines
9.9 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "states" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"abbr" VARCHAR(2) NOT NULL,
|
|
"lat" DOUBLE PRECISION NOT NULL,
|
|
"lng" DOUBLE PRECISION NOT NULL,
|
|
|
|
CONSTRAINT "states_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "regulations" (
|
|
"id" TEXT NOT NULL,
|
|
"stateId" TEXT NOT NULL,
|
|
"permitWidth" TEXT NOT NULL,
|
|
"permitHeight" TEXT NOT NULL,
|
|
"permitLength" TEXT NOT NULL,
|
|
"permitWeight" TEXT NOT NULL,
|
|
"escortWidth" TEXT NOT NULL,
|
|
"escortHeight" TEXT NOT NULL,
|
|
"escortLength" TEXT NOT NULL,
|
|
"escortWeight" TEXT NOT NULL,
|
|
"travelRestrictions" TEXT NOT NULL,
|
|
"holidays" TEXT NOT NULL,
|
|
"agency" TEXT NOT NULL,
|
|
"url" TEXT NOT NULL,
|
|
"notes" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "regulations_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "equipment_requirements" (
|
|
"id" TEXT NOT NULL,
|
|
"stateId" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"certification" TEXT NOT NULL DEFAULT '',
|
|
"vehicle" TEXT NOT NULL DEFAULT '',
|
|
"signs" TEXT NOT NULL DEFAULT '',
|
|
"lights" TEXT NOT NULL DEFAULT '',
|
|
"heightPole" TEXT NOT NULL DEFAULT '',
|
|
"flags" TEXT NOT NULL DEFAULT '',
|
|
"safetyGear" TEXT NOT NULL DEFAULT '',
|
|
"communication" TEXT NOT NULL DEFAULT '',
|
|
|
|
CONSTRAINT "equipment_requirements_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "contacts" (
|
|
"id" TEXT NOT NULL,
|
|
"stateId" TEXT NOT NULL,
|
|
"permitPhone" TEXT NOT NULL,
|
|
"policePhone" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"hours" TEXT NOT NULL,
|
|
"portalUrl" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "contacts_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "seasonal_restrictions" (
|
|
"id" TEXT NOT NULL,
|
|
"stateId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"startMonth" INTEGER NOT NULL,
|
|
"endMonth" INTEGER NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "seasonal_restrictions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "truck_stops" (
|
|
"id" TEXT NOT NULL,
|
|
"stateId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"lat" DOUBLE PRECISION NOT NULL,
|
|
"lng" DOUBLE PRECISION NOT NULL,
|
|
"address" TEXT NOT NULL,
|
|
"hasOversizeParking" BOOLEAN NOT NULL DEFAULT false,
|
|
"entranceHeight" TEXT NOT NULL DEFAULT '',
|
|
"entranceWidth" TEXT NOT NULL DEFAULT '',
|
|
"lotSqFt" INTEGER,
|
|
"facilities" JSONB NOT NULL DEFAULT '[]',
|
|
"satelliteUrl" TEXT NOT NULL DEFAULT '',
|
|
"phone" TEXT NOT NULL DEFAULT '',
|
|
|
|
CONSTRAINT "truck_stops_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "bridges" (
|
|
"id" TEXT NOT NULL,
|
|
"stateId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"lat" DOUBLE PRECISION NOT NULL,
|
|
"lng" DOUBLE PRECISION NOT NULL,
|
|
"route" TEXT NOT NULL,
|
|
"heightClearance" DOUBLE PRECISION NOT NULL,
|
|
"widthClearance" DOUBLE PRECISION,
|
|
"weightLimit" DOUBLE PRECISION,
|
|
"lastVerified" TIMESTAMP(3),
|
|
|
|
CONSTRAINT "bridges_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "weigh_stations" (
|
|
"id" TEXT NOT NULL,
|
|
"stateId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"lat" DOUBLE PRECISION NOT NULL,
|
|
"lng" DOUBLE PRECISION NOT NULL,
|
|
"direction" TEXT NOT NULL,
|
|
"route" TEXT NOT NULL,
|
|
"prePass" BOOLEAN NOT NULL DEFAULT false,
|
|
"hours" TEXT NOT NULL DEFAULT '',
|
|
"currentStatus" TEXT NOT NULL DEFAULT 'unknown',
|
|
"lastStatusUpdate" TIMESTAMP(3),
|
|
"lastStatusUserId" TEXT,
|
|
|
|
CONSTRAINT "weigh_stations_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "users" (
|
|
"id" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"role" TEXT NOT NULL DEFAULT 'driver',
|
|
"tier" TEXT NOT NULL DEFAULT 'free',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "users_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "escort_profiles" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"lat" DOUBLE PRECISION NOT NULL,
|
|
"lng" DOUBLE PRECISION NOT NULL,
|
|
"radiusMiles" INTEGER NOT NULL DEFAULT 100,
|
|
"certifications" JSONB NOT NULL DEFAULT '[]',
|
|
"vehicleType" TEXT NOT NULL DEFAULT '',
|
|
"availability" TEXT NOT NULL DEFAULT 'available',
|
|
"rating" DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
"ratingCount" INTEGER NOT NULL DEFAULT 0,
|
|
"phone" TEXT NOT NULL DEFAULT '',
|
|
"bio" TEXT NOT NULL DEFAULT '',
|
|
|
|
CONSTRAINT "escort_profiles_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "loads" (
|
|
"id" TEXT NOT NULL,
|
|
"posterId" TEXT NOT NULL,
|
|
"origin" TEXT NOT NULL,
|
|
"destination" TEXT NOT NULL,
|
|
"pickupDate" TIMESTAMP(3) NOT NULL,
|
|
"width" TEXT NOT NULL,
|
|
"height" TEXT NOT NULL,
|
|
"length" TEXT NOT NULL,
|
|
"weight" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL DEFAULT '',
|
|
"escortsNeeded" INTEGER NOT NULL DEFAULT 1,
|
|
"status" TEXT NOT NULL DEFAULT 'open',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "loads_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "orders" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"origin" TEXT NOT NULL,
|
|
"destination" TEXT NOT NULL,
|
|
"pickupDate" TIMESTAMP(3) NOT NULL,
|
|
"width" TEXT NOT NULL DEFAULT '',
|
|
"height" TEXT NOT NULL DEFAULT '',
|
|
"length" TEXT NOT NULL DEFAULT '',
|
|
"weight" TEXT NOT NULL DEFAULT '',
|
|
"loadType" TEXT NOT NULL DEFAULT '',
|
|
"escortsNeeded" INTEGER NOT NULL DEFAULT 1,
|
|
"status" TEXT NOT NULL DEFAULT 'pending',
|
|
"notes" TEXT NOT NULL DEFAULT '',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "orders_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "documents" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"filename" TEXT NOT NULL,
|
|
"filepath" TEXT NOT NULL,
|
|
"mimeType" TEXT NOT NULL DEFAULT '',
|
|
"sizeBytes" INTEGER NOT NULL DEFAULT 0,
|
|
"expiresAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "documents_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "contributions" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"entityType" TEXT NOT NULL,
|
|
"entityId" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"truckStopId" TEXT,
|
|
"weighStationId" TEXT,
|
|
|
|
CONSTRAINT "contributions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "alerts" (
|
|
"id" TEXT NOT NULL,
|
|
"stateId" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"route" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"severity" TEXT NOT NULL DEFAULT 'info',
|
|
"startsAt" TIMESTAMP(3) NOT NULL,
|
|
"endsAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "alerts_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "states_name_key" ON "states"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "states_abbr_key" ON "states"("abbr");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "regulations_stateId_key" ON "regulations"("stateId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "contacts_stateId_key" ON "contacts"("stateId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "escort_profiles_userId_key" ON "escort_profiles"("userId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "regulations" ADD CONSTRAINT "regulations_stateId_fkey" FOREIGN KEY ("stateId") REFERENCES "states"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "equipment_requirements" ADD CONSTRAINT "equipment_requirements_stateId_fkey" FOREIGN KEY ("stateId") REFERENCES "states"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "contacts" ADD CONSTRAINT "contacts_stateId_fkey" FOREIGN KEY ("stateId") REFERENCES "states"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "seasonal_restrictions" ADD CONSTRAINT "seasonal_restrictions_stateId_fkey" FOREIGN KEY ("stateId") REFERENCES "states"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "truck_stops" ADD CONSTRAINT "truck_stops_stateId_fkey" FOREIGN KEY ("stateId") REFERENCES "states"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "bridges" ADD CONSTRAINT "bridges_stateId_fkey" FOREIGN KEY ("stateId") REFERENCES "states"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "weigh_stations" ADD CONSTRAINT "weigh_stations_stateId_fkey" FOREIGN KEY ("stateId") REFERENCES "states"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "escort_profiles" ADD CONSTRAINT "escort_profiles_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "loads" ADD CONSTRAINT "loads_posterId_fkey" FOREIGN KEY ("posterId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "orders" ADD CONSTRAINT "orders_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "documents" ADD CONSTRAINT "documents_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "contributions" ADD CONSTRAINT "contributions_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "contributions" ADD CONSTRAINT "contributions_truckStopId_fkey" FOREIGN KEY ("truckStopId") REFERENCES "truck_stops"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "contributions" ADD CONSTRAINT "contributions_weighStationId_fkey" FOREIGN KEY ("weighStationId") REFERENCES "weigh_stations"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "alerts" ADD CONSTRAINT "alerts_stateId_fkey" FOREIGN KEY ("stateId") REFERENCES "states"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|