bunker-admin
a77306fac2
Phase 1-14 complete: - Unified Express.js API (TypeScript, Prisma ORM, PostgreSQL 16) - React Admin GUI (Vite + Ant Design + Zustand) - JWT auth with refresh tokens - Influence: Campaigns, Representatives, Responses, Email Queue - Map: Locations, Cuts, Shifts, Canvassing System - NAR data import infrastructure (2025 format) - Listmonk newsletter integration - Landing page builder (GrapesJS) - MkDocs + Code Server integration - Volunteer portal with GPS tracking - Monitoring stack (Prometheus, Grafana, Alertmanager) - Pangolin tunnel integration Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>
60 lines
2.3 KiB
SQL
60 lines
2.3 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "TrackPointEvent" AS ENUM ('LOCATION_ADDED', 'VISIT_RECORDED', 'SESSION_STARTED', 'SESSION_ENDED');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "tracking_sessions" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"canvassSessionId" TEXT,
|
|
"startedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"endedAt" TIMESTAMP(3),
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"totalPoints" INTEGER NOT NULL DEFAULT 0,
|
|
"totalDistanceM" DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
"lastLatitude" DECIMAL(10,8),
|
|
"lastLongitude" DECIMAL(11,8),
|
|
"lastRecordedAt" TIMESTAMP(3),
|
|
|
|
CONSTRAINT "tracking_sessions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "track_points" (
|
|
"id" TEXT NOT NULL,
|
|
"trackingSessionId" TEXT NOT NULL,
|
|
"latitude" DECIMAL(10,8) NOT NULL,
|
|
"longitude" DECIMAL(11,8) NOT NULL,
|
|
"accuracy" DOUBLE PRECISION,
|
|
"recordedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"eventType" "TrackPointEvent",
|
|
|
|
CONSTRAINT "track_points_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "tracking_sessions_canvassSessionId_key" ON "tracking_sessions"("canvassSessionId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "tracking_sessions_userId_idx" ON "tracking_sessions"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "tracking_sessions_isActive_idx" ON "tracking_sessions"("isActive");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "tracking_sessions_isActive_lastRecordedAt_idx" ON "tracking_sessions"("isActive", "lastRecordedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "track_points_trackingSessionId_recordedAt_idx" ON "track_points"("trackingSessionId", "recordedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "track_points_recordedAt_idx" ON "track_points"("recordedAt");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "tracking_sessions" ADD CONSTRAINT "tracking_sessions_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "tracking_sessions" ADD CONSTRAINT "tracking_sessions_canvassSessionId_fkey" FOREIGN KEY ("canvassSessionId") REFERENCES "canvass_sessions"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "track_points" ADD CONSTRAINT "track_points_trackingSessionId_fkey" FOREIGN KEY ("trackingSessionId") REFERENCES "tracking_sessions"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|