vliaudatandClaude Opus 5
42ce09100e
Model the whole domain: settings, the reference week, dated exceptions, vacation periods, the public-holiday cache, display messages, the translation cache, the audit log, and the device tables the BYOS server needs (Device, DeviceLog, SyncState). Two modelling decisions are load-bearing and documented in the schema: - Opening times are wall-clock "HH:mm" strings in the shop timezone, never instants. Nothing is stored in UTC, which turns the March and October daylight-saving switches into non-events instead of edge cases. - VacationPeriod is a source of truth, never expanded into ScheduleException rows. The resolver reads it directly at priority rank 2, so a holiday sync can never overwrite a manual exception and editing a period leaves no orphans behind. Device access tokens are stored only as SHA-256 digests, and the image filename column holds a content hash: the firmware skips the redraw when the filename is unchanged, which is where the battery life comes from. Prisma 7 no longer accepts the connection URL in the schema file, so it moves to prisma.config.ts with the pg driver adapter. The dev Postgres service lands here rather than with the rest of the Docker work, because the migration needs a database to run against. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_012cSY9pVhZmJUKNN7wf1Myd
211 lines
6.3 KiB
SQL
211 lines
6.3 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "ExceptionReason" AS ENUM ('TEMPORARY', 'HOLIDAY', 'VACATION', 'SPECIAL_EVENT');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ExceptionSource" AS ENUM ('MANUAL', 'HOLIDAY_API');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "TranslationStatus" AS ENUM ('PENDING', 'DONE', 'MANUAL', 'ERROR');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "DeviceLogLevel" AS ENUM ('DEBUG', 'INFO', 'WARN', 'ERROR');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "settings" (
|
|
"id" TEXT NOT NULL DEFAULT 'singleton',
|
|
"shopName" TEXT NOT NULL DEFAULT 'ITA ITO',
|
|
"timezone" TEXT NOT NULL DEFAULT 'Europe/Zurich',
|
|
"countryIsoCode" TEXT NOT NULL DEFAULT 'CH',
|
|
"subdivisionCode" TEXT NOT NULL DEFAULT 'CH-GE',
|
|
"defaultClosedMessageFr" TEXT NOT NULL DEFAULT 'Fermé',
|
|
"defaultClosedMessageEn" TEXT NOT NULL DEFAULT 'Closed',
|
|
"refreshRateOpenSec" INTEGER NOT NULL DEFAULT 600,
|
|
"refreshRateClosedSec" INTEGER NOT NULL DEFAULT 7200,
|
|
"imageFormat" TEXT NOT NULL DEFAULT 'bmp',
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "settings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "weekly_schedules" (
|
|
"id" TEXT NOT NULL,
|
|
"dayOfWeek" INTEGER NOT NULL,
|
|
"isClosed" BOOLEAN NOT NULL DEFAULT false,
|
|
"slots" JSONB NOT NULL DEFAULT '[]',
|
|
|
|
CONSTRAINT "weekly_schedules_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "schedule_exceptions" (
|
|
"id" TEXT NOT NULL,
|
|
"date" DATE NOT NULL,
|
|
"isClosed" BOOLEAN NOT NULL DEFAULT true,
|
|
"slots" JSONB,
|
|
"reason" "ExceptionReason" NOT NULL DEFAULT 'TEMPORARY',
|
|
"noteFr" TEXT,
|
|
"noteEn" TEXT,
|
|
"source" "ExceptionSource" NOT NULL DEFAULT 'MANUAL',
|
|
"createdBy" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "schedule_exceptions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "vacation_periods" (
|
|
"id" TEXT NOT NULL,
|
|
"startDate" DATE NOT NULL,
|
|
"endDate" DATE NOT NULL,
|
|
"labelFr" TEXT NOT NULL,
|
|
"labelEn" TEXT,
|
|
"createdBy" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "vacation_periods_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public_holidays" (
|
|
"id" TEXT NOT NULL,
|
|
"date" DATE NOT NULL,
|
|
"nameFr" TEXT NOT NULL,
|
|
"nameEn" TEXT NOT NULL,
|
|
"nationwide" BOOLEAN NOT NULL DEFAULT true,
|
|
"subdivisionCode" TEXT NOT NULL,
|
|
"source" TEXT NOT NULL DEFAULT 'openholidaysapi.org',
|
|
"syncedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"isAutoClosed" BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
CONSTRAINT "public_holidays_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "messages" (
|
|
"id" TEXT NOT NULL,
|
|
"textFr" TEXT NOT NULL,
|
|
"textEn" TEXT,
|
|
"translationStatus" "TranslationStatus" NOT NULL DEFAULT 'PENDING',
|
|
"startsAt" TIMESTAMP(3),
|
|
"endsAt" TIMESTAMP(3),
|
|
"priority" INTEGER NOT NULL DEFAULT 0,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdBy" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "messages_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "translation_cache" (
|
|
"id" TEXT NOT NULL,
|
|
"sourceHash" TEXT NOT NULL,
|
|
"targetLang" TEXT NOT NULL DEFAULT 'en',
|
|
"sourceText" TEXT NOT NULL,
|
|
"translatedText" TEXT NOT NULL,
|
|
"model" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "translation_cache_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "audit_logs" (
|
|
"id" TEXT NOT NULL,
|
|
"userEmail" TEXT NOT NULL,
|
|
"action" TEXT NOT NULL,
|
|
"entity" TEXT NOT NULL,
|
|
"entityId" TEXT,
|
|
"diff" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "devices" (
|
|
"id" TEXT NOT NULL,
|
|
"macAddress" TEXT NOT NULL,
|
|
"friendlyId" TEXT NOT NULL,
|
|
"apiKeyHash" TEXT NOT NULL,
|
|
"label" TEXT NOT NULL DEFAULT 'Vitrine',
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"lastSeenAt" TIMESTAMP(3),
|
|
"fwVersion" TEXT,
|
|
"batteryVoltage" DOUBLE PRECISION,
|
|
"percentCharged" INTEGER,
|
|
"rssi" INTEGER,
|
|
"lastFilename" TEXT,
|
|
"lastRefreshRate" INTEGER,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "devices_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "device_logs" (
|
|
"id" TEXT NOT NULL,
|
|
"deviceId" TEXT NOT NULL,
|
|
"level" "DeviceLogLevel" NOT NULL DEFAULT 'INFO',
|
|
"message" TEXT NOT NULL,
|
|
"payload" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "device_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "sync_states" (
|
|
"key" TEXT NOT NULL,
|
|
"lastSuccessAt" TIMESTAMP(3),
|
|
"lastErrorAt" TIMESTAMP(3),
|
|
"lastError" TEXT,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "sync_states_pkey" PRIMARY KEY ("key")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "weekly_schedules_dayOfWeek_key" ON "weekly_schedules"("dayOfWeek");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "schedule_exceptions_date_key" ON "schedule_exceptions"("date");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "schedule_exceptions_date_idx" ON "schedule_exceptions"("date");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "vacation_periods_startDate_endDate_idx" ON "vacation_periods"("startDate", "endDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "public_holidays_date_idx" ON "public_holidays"("date");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "public_holidays_date_subdivisionCode_key" ON "public_holidays"("date", "subdivisionCode");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "messages_isActive_priority_idx" ON "messages"("isActive", "priority");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "translation_cache_sourceHash_targetLang_model_key" ON "translation_cache"("sourceHash", "targetLang", "model");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "audit_logs_createdAt_idx" ON "audit_logs"("createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "devices_macAddress_key" ON "devices"("macAddress");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "devices_friendlyId_key" ON "devices"("friendlyId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "device_logs_deviceId_createdAt_idx" ON "device_logs"("deviceId", "createdAt");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "device_logs" ADD CONSTRAINT "device_logs_deviceId_fkey" FOREIGN KEY ("deviceId") REFERENCES "devices"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|