spb/airiskindex Public

1-- CreateTable2CREATE TABLE "Occupation" (3    "code" TEXT NOT NULL,4    "title" TEXT NOT NULL,5    "description" TEXT,6    "escoUri" TEXT,7    "romeCode" TEXT,8    "crosswalkNote" TEXT,9    "medianWageCents" INTEGER,10    "wageCurrency" TEXT NOT NULL DEFAULT 'USD',11    "employment" INTEGER,1213    CONSTRAINT "Occupation_pkey" PRIMARY KEY ("code")14);1516-- CreateTable17CREATE TABLE "Task" (18    "id" TEXT NOT NULL,19    "occupationCode" TEXT NOT NULL,20    "statement" TEXT NOT NULL,21    "importance" DOUBLE PRECISION,2223    CONSTRAINT "Task_pkey" PRIMARY KEY ("id")24);2526-- CreateTable27CREATE TABLE "TaskRating" (28    "id" TEXT NOT NULL,29    "taskId" TEXT NOT NULL,30    "dimension" TEXT NOT NULL,31    "model" TEXT NOT NULL,32    "promptVersion" TEXT NOT NULL,33    "sampleIndex" INTEGER NOT NULL DEFAULT 0,34    "rating" INTEGER NOT NULL,35    "rationale" TEXT,36    "rawResponse" JSONB NOT NULL,37    "ratedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,3839    CONSTRAINT "TaskRating_pkey" PRIMARY KEY ("id")40);4142-- CreateTable43CREATE TABLE "ExpertOverride" (44    "id" TEXT NOT NULL,45    "taskId" TEXT NOT NULL,46    "dimension" TEXT NOT NULL,47    "ratingLow" INTEGER NOT NULL,48    "ratingMid" DOUBLE PRECISION NOT NULL,49    "ratingHigh" INTEGER NOT NULL,50    "reviewer" TEXT NOT NULL,51    "note" TEXT,52    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,5354    CONSTRAINT "ExpertOverride_pkey" PRIMARY KEY ("id")55);5657-- CreateTable58CREATE TABLE "ScoreRun" (59    "id" TEXT NOT NULL,60    "indexVersion" TEXT NOT NULL,61    "raterPromptVersion" TEXT NOT NULL,62    "raterModels" TEXT[],63    "notes" TEXT,64    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,6566    CONSTRAINT "ScoreRun_pkey" PRIMARY KEY ("id")67);6869-- CreateTable70CREATE TABLE "OccupationScore" (71    "id" TEXT NOT NULL,72    "runId" TEXT NOT NULL,73    "occupationCode" TEXT NOT NULL,74    "substitutionLow" DOUBLE PRECISION NOT NULL,75    "substitution" DOUBLE PRECISION NOT NULL,76    "substitutionHigh" DOUBLE PRECISION NOT NULL,77    "exposureLow" DOUBLE PRECISION NOT NULL,78    "exposure" DOUBLE PRECISION NOT NULL,79    "exposureHigh" DOUBLE PRECISION NOT NULL,80    "augmentationLow" DOUBLE PRECISION NOT NULL,81    "augmentation" DOUBLE PRECISION NOT NULL,82    "augmentationHigh" DOUBLE PRECISION NOT NULL,83    "highlyExposedTaskShare" DOUBLE PRECISION NOT NULL,8485    CONSTRAINT "OccupationScore_pkey" PRIMARY KEY ("id")86);8788-- CreateTable89CREATE TABLE "TaskScore" (90    "id" TEXT NOT NULL,91    "runId" TEXT NOT NULL,92    "taskId" TEXT NOT NULL,93    "substitutionLow" DOUBLE PRECISION NOT NULL,94    "substitution" DOUBLE PRECISION NOT NULL,95    "substitutionHigh" DOUBLE PRECISION NOT NULL,96    "exposureLow" DOUBLE PRECISION NOT NULL,97    "exposure" DOUBLE PRECISION NOT NULL,98    "exposureHigh" DOUBLE PRECISION NOT NULL,99    "augmentationLow" DOUBLE PRECISION NOT NULL,100    "augmentation" DOUBLE PRECISION NOT NULL,101    "augmentationHigh" DOUBLE PRECISION NOT NULL,102103    CONSTRAINT "TaskScore_pkey" PRIMARY KEY ("id")104);105106-- CreateTable107CREATE TABLE "ApiKey" (108    "id" TEXT NOT NULL,109    "hashedKey" TEXT NOT NULL,110    "name" TEXT NOT NULL,111    "rateLimitPerMin" INTEGER NOT NULL DEFAULT 600,112    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,113114    CONSTRAINT "ApiKey_pkey" PRIMARY KEY ("id")115);116117-- CreateIndex118CREATE INDEX "Task_occupationCode_idx" ON "Task"("occupationCode");119120-- CreateIndex121CREATE INDEX "TaskRating_taskId_dimension_idx" ON "TaskRating"("taskId", "dimension");122123-- CreateIndex124CREATE INDEX "TaskRating_promptVersion_idx" ON "TaskRating"("promptVersion");125126-- CreateIndex127CREATE UNIQUE INDEX "TaskRating_taskId_dimension_model_promptVersion_sampleIndex_key" ON "TaskRating"("taskId", "dimension", "model", "promptVersion", "sampleIndex");128129-- CreateIndex130CREATE UNIQUE INDEX "ExpertOverride_taskId_dimension_key" ON "ExpertOverride"("taskId", "dimension");131132-- CreateIndex133CREATE INDEX "ScoreRun_indexVersion_idx" ON "ScoreRun"("indexVersion");134135-- CreateIndex136CREATE INDEX "OccupationScore_occupationCode_idx" ON "OccupationScore"("occupationCode");137138-- CreateIndex139CREATE UNIQUE INDEX "OccupationScore_runId_occupationCode_key" ON "OccupationScore"("runId", "occupationCode");140141-- CreateIndex142CREATE INDEX "TaskScore_taskId_idx" ON "TaskScore"("taskId");143144-- CreateIndex145CREATE UNIQUE INDEX "TaskScore_runId_taskId_key" ON "TaskScore"("runId", "taskId");146147-- CreateIndex148CREATE UNIQUE INDEX "ApiKey_hashedKey_key" ON "ApiKey"("hashedKey");149150-- AddForeignKey151ALTER TABLE "Task" ADD CONSTRAINT "Task_occupationCode_fkey" FOREIGN KEY ("occupationCode") REFERENCES "Occupation"("code") ON DELETE RESTRICT ON UPDATE CASCADE;152153-- AddForeignKey154ALTER TABLE "TaskRating" ADD CONSTRAINT "TaskRating_taskId_fkey" FOREIGN KEY ("taskId") REFERENCES "Task"("id") ON DELETE RESTRICT ON UPDATE CASCADE;155156-- AddForeignKey157ALTER TABLE "ExpertOverride" ADD CONSTRAINT "ExpertOverride_taskId_fkey" FOREIGN KEY ("taskId") REFERENCES "Task"("id") ON DELETE RESTRICT ON UPDATE CASCADE;158159-- AddForeignKey160ALTER TABLE "OccupationScore" ADD CONSTRAINT "OccupationScore_runId_fkey" FOREIGN KEY ("runId") REFERENCES "ScoreRun"("id") ON DELETE RESTRICT ON UPDATE CASCADE;161162-- AddForeignKey163ALTER TABLE "OccupationScore" ADD CONSTRAINT "OccupationScore_occupationCode_fkey" FOREIGN KEY ("occupationCode") REFERENCES "Occupation"("code") ON DELETE RESTRICT ON UPDATE CASCADE;164165-- AddForeignKey166ALTER TABLE "TaskScore" ADD CONSTRAINT "TaskScore_runId_fkey" FOREIGN KEY ("runId") REFERENCES "ScoreRun"("id") ON DELETE RESTRICT ON UPDATE CASCADE;167168-- AddForeignKey169ALTER TABLE "TaskScore" ADD CONSTRAINT "TaskScore_taskId_fkey" FOREIGN KEY ("taskId") REFERENCES "Task"("id") ON DELETE RESTRICT ON UPDATE CASCADE;170