maaz519
cd1eef0098
- SevaOpportunity + SevaEntry + GamificationEvent models + migration - GamificationService: multi-signal point table (helpful answer +10, seva +20, event +5, FAQ +15, welcome +3, profile +5, business +5), idempotent award via unique(userId,type,refId), lifetime + time-decayed "recent" score, leaderboard - SevaModule: admin CRUD + mark-entry-complete (awards SEVA_COMPLETED points) - Member endpoints in MyController: GET /my/seva, POST /my/seva/:id/signup|cancel - PROFILE_COMPLETED auto-awarded on profile update (name+hometown+interest) - /my/seva page: points hero with per-type breakdown, open opportunities w/ signup, leaderboard (respects directoryVisible), my seva history - Member + admin BFF routes; Seva & Points nav item - Register GamificationModule + SevaModule; add SEVA_* audit actions Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
60 lines
3.1 KiB
SQL
60 lines
3.1 KiB
SQL
CREATE TYPE "SevaStatus" AS ENUM ('OPEN', 'CLOSED');
|
|
CREATE TYPE "SevaEntryStatus" AS ENUM ('SIGNED_UP', 'COMPLETED', 'CANCELLED');
|
|
CREATE TYPE "GamificationEventType" AS ENUM ('HELPFUL_ANSWER', 'SEVA_COMPLETED', 'EVENT_ATTENDANCE', 'FAQ_APPROVED', 'WELCOME', 'PROFILE_COMPLETED', 'BUSINESS_ADDED');
|
|
|
|
CREATE TABLE "SevaOpportunity" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"location" TEXT,
|
|
"slots" INTEGER,
|
|
"startsAt" TIMESTAMP(3),
|
|
"pointsAward" INTEGER NOT NULL DEFAULT 20,
|
|
"status" "SevaStatus" NOT NULL DEFAULT 'OPEN',
|
|
"createdBy" TEXT NOT NULL,
|
|
"isPublished" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "SevaOpportunity_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "SevaEntry" (
|
|
"id" TEXT NOT NULL,
|
|
"opportunityId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"status" "SevaEntryStatus" NOT NULL DEFAULT 'SIGNED_UP',
|
|
"hours" DOUBLE PRECISION,
|
|
"note" TEXT,
|
|
"completedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "SevaEntry_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "GamificationEvent" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"type" "GamificationEventType" NOT NULL,
|
|
"points" INTEGER NOT NULL,
|
|
"refType" TEXT,
|
|
"refId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "GamificationEvent_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "SevaOpportunity_tenantId_status_idx" ON "SevaOpportunity"("tenantId", "status");
|
|
CREATE INDEX "SevaOpportunity_tenantId_isPublished_idx" ON "SevaOpportunity"("tenantId", "isPublished");
|
|
CREATE UNIQUE INDEX "SevaEntry_opportunityId_userId_key" ON "SevaEntry"("opportunityId", "userId");
|
|
CREATE INDEX "SevaEntry_userId_idx" ON "SevaEntry"("userId");
|
|
CREATE INDEX "GamificationEvent_tenantId_userId_idx" ON "GamificationEvent"("tenantId", "userId");
|
|
CREATE INDEX "GamificationEvent_userId_createdAt_idx" ON "GamificationEvent"("userId", "createdAt");
|
|
CREATE UNIQUE INDEX "GamificationEvent_userId_type_refId_key" ON "GamificationEvent"("userId", "type", "refId");
|
|
|
|
ALTER TABLE "SevaOpportunity" ADD CONSTRAINT "SevaOpportunity_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "SevaEntry" ADD CONSTRAINT "SevaEntry_opportunityId_fkey" FOREIGN KEY ("opportunityId") REFERENCES "SevaOpportunity"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "SevaEntry" ADD CONSTRAINT "SevaEntry_userId_fkey" FOREIGN KEY ("userId") REFERENCES "TowerUser"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "GamificationEvent" ADD CONSTRAINT "GamificationEvent_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "GamificationEvent" ADD CONSTRAINT "GamificationEvent_userId_fkey" FOREIGN KEY ("userId") REFERENCES "TowerUser"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|