388 lines
13 KiB
SQL
388 lines
13 KiB
SQL
-- CreateSchema
|
|
CREATE SCHEMA IF NOT EXISTS "public";
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "Role" AS ENUM ('admin', 'manager', 'superadmin');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "PaymentMethod" AS ENUM ('cash', 'payconiq', 'other');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "StockMovementType" AS ENUM ('add', 'count');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ActorType" AS ENUM ('user', 'device', 'system');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "TerminalEventType" AS ENUM ('tab_opened', 'tab_renamed', 'tab_deleted', 'line_added', 'line_removed', 'settled', 'stock_set', 'product_created', 'product_updated', 'product_archived', 'product_restored');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Venue" (
|
|
"id" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"cashPaymentEmails" TEXT[] DEFAULT ARRAY[]::TEXT[],
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Venue_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID,
|
|
"email" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"role" "Role" NOT NULL DEFAULT 'manager',
|
|
"sessionsRevokedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "RefreshToken" (
|
|
"id" UUID NOT NULL,
|
|
"userId" UUID NOT NULL,
|
|
"tokenHash" TEXT NOT NULL,
|
|
"expiresAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"revokedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "RefreshToken_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Device" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"tokenHash" TEXT NOT NULL,
|
|
"tokenPrefix" TEXT NOT NULL,
|
|
"tokenExpiresAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"lastSeenAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"revokedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Device_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "RegistrationCode" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"expiresAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"usedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "RegistrationCode_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Customer" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"nameKey" TEXT NOT NULL,
|
|
"email" TEXT,
|
|
"position" INTEGER NOT NULL DEFAULT 0,
|
|
"tabOpenedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Customer_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Tab" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"customerName" TEXT NOT NULL,
|
|
"openedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"version" INTEGER NOT NULL DEFAULT 0,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "Tab_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Settlement" (
|
|
"id" UUID NOT NULL,
|
|
"sequence" SERIAL NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"tabId" UUID NOT NULL,
|
|
"customerName" TEXT NOT NULL,
|
|
"settledAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"paymentMethod" "PaymentMethod" NOT NULL,
|
|
"amountInCents" INTEGER NOT NULL,
|
|
"deviceId" UUID,
|
|
"settledByUserId" UUID,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Settlement_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TabUnit" (
|
|
"id" TEXT NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"tabId" UUID,
|
|
"settlementId" UUID,
|
|
"lineId" TEXT NOT NULL,
|
|
"productId" UUID,
|
|
"productName" TEXT NOT NULL,
|
|
"unitPriceInCents" INTEGER NOT NULL,
|
|
"orderId" TEXT,
|
|
"addedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"removedAt" TIMESTAMPTZ(3),
|
|
|
|
CONSTRAINT "TabUnit_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Product" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"category" TEXT,
|
|
"priceInCents" INTEGER NOT NULL,
|
|
"buyPriceInCents" INTEGER,
|
|
"stock" INTEGER,
|
|
"countedAt" TIMESTAMPTZ(3),
|
|
"lowStockThreshold" INTEGER,
|
|
"desiredStock" INTEGER,
|
|
"imageId" TEXT,
|
|
"archivedAt" TIMESTAMPTZ(3),
|
|
"fieldUpdatedAt" JSONB NOT NULL DEFAULT '{}',
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "Product_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ProductImage" (
|
|
"id" TEXT NOT NULL,
|
|
"contentType" TEXT NOT NULL,
|
|
"width" INTEGER NOT NULL,
|
|
"height" INTEGER NOT NULL,
|
|
"bytes" INTEGER NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "ProductImage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "StockMovement" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"productId" UUID NOT NULL,
|
|
"type" "StockMovementType" NOT NULL,
|
|
"quantity" INTEGER NOT NULL,
|
|
"stockAfter" INTEGER NOT NULL,
|
|
"note" TEXT,
|
|
"userId" UUID,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "StockMovement_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TabEvent" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"deviceId" UUID NOT NULL,
|
|
"type" "TerminalEventType" NOT NULL,
|
|
"payload" JSONB NOT NULL,
|
|
"occurredAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"receivedAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "TabEvent_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Reminder" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID NOT NULL,
|
|
"customerId" UUID,
|
|
"customerName" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"tabId" UUID NOT NULL,
|
|
"amountInCents" INTEGER NOT NULL,
|
|
"sentAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Reminder_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AuditLog" (
|
|
"id" UUID NOT NULL,
|
|
"venueId" UUID,
|
|
"actorType" "ActorType" NOT NULL,
|
|
"actorId" UUID,
|
|
"action" TEXT NOT NULL,
|
|
"details" JSONB,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "AuditLog_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "User_venueId_idx" ON "User"("venueId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "RefreshToken_tokenHash_key" ON "RefreshToken"("tokenHash");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RefreshToken_userId_idx" ON "RefreshToken"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Device_tokenHash_key" ON "Device"("tokenHash");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Device_venueId_revokedAt_idx" ON "Device"("venueId", "revokedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "RegistrationCode_code_key" ON "RegistrationCode"("code");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RegistrationCode_venueId_idx" ON "RegistrationCode"("venueId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Customer_venueId_nameKey_key" ON "Customer"("venueId", "nameKey");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Tab_venueId_idx" ON "Tab"("venueId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Settlement_sequence_key" ON "Settlement"("sequence");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Settlement_venueId_sequence_idx" ON "Settlement"("venueId", "sequence");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Settlement_venueId_settledAt_idx" ON "Settlement"("venueId", "settledAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TabUnit_tabId_idx" ON "TabUnit"("tabId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TabUnit_settlementId_idx" ON "TabUnit"("settlementId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TabUnit_venueId_addedAt_idx" ON "TabUnit"("venueId", "addedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TabUnit_productId_idx" ON "TabUnit"("productId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Product_venueId_idx" ON "Product"("venueId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "StockMovement_venueId_createdAt_idx" ON "StockMovement"("venueId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "StockMovement_productId_createdAt_idx" ON "StockMovement"("productId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TabEvent_venueId_occurredAt_idx" ON "TabEvent"("venueId", "occurredAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TabEvent_receivedAt_idx" ON "TabEvent"("receivedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Reminder_venueId_sentAt_idx" ON "Reminder"("venueId", "sentAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Reminder_customerId_idx" ON "Reminder"("customerId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "AuditLog_venueId_createdAt_idx" ON "AuditLog"("venueId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "AuditLog_createdAt_idx" ON "AuditLog"("createdAt");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "User" ADD CONSTRAINT "User_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "RefreshToken" ADD CONSTRAINT "RefreshToken_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Device" ADD CONSTRAINT "Device_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "RegistrationCode" ADD CONSTRAINT "RegistrationCode_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Customer" ADD CONSTRAINT "Customer_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Tab" ADD CONSTRAINT "Tab_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Settlement" ADD CONSTRAINT "Settlement_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Settlement" ADD CONSTRAINT "Settlement_deviceId_fkey" FOREIGN KEY ("deviceId") REFERENCES "Device"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Settlement" ADD CONSTRAINT "Settlement_settledByUserId_fkey" FOREIGN KEY ("settledByUserId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TabUnit" ADD CONSTRAINT "TabUnit_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TabUnit" ADD CONSTRAINT "TabUnit_tabId_fkey" FOREIGN KEY ("tabId") REFERENCES "Tab"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TabUnit" ADD CONSTRAINT "TabUnit_settlementId_fkey" FOREIGN KEY ("settlementId") REFERENCES "Settlement"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TabUnit" ADD CONSTRAINT "TabUnit_productId_fkey" FOREIGN KEY ("productId") REFERENCES "Product"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Product" ADD CONSTRAINT "Product_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Product" ADD CONSTRAINT "Product_imageId_fkey" FOREIGN KEY ("imageId") REFERENCES "ProductImage"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "StockMovement" ADD CONSTRAINT "StockMovement_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "StockMovement" ADD CONSTRAINT "StockMovement_productId_fkey" FOREIGN KEY ("productId") REFERENCES "Product"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "StockMovement" ADD CONSTRAINT "StockMovement_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TabEvent" ADD CONSTRAINT "TabEvent_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TabEvent" ADD CONSTRAINT "TabEvent_deviceId_fkey" FOREIGN KEY ("deviceId") REFERENCES "Device"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Reminder" ADD CONSTRAINT "Reminder_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Reminder" ADD CONSTRAINT "Reminder_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AuditLog" ADD CONSTRAINT "AuditLog_venueId_fkey" FOREIGN KEY ("venueId") REFERENCES "Venue"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
|
|
-- A drink is on an open tab or in a settlement, never both or neither
|
|
ALTER TABLE "TabUnit" ADD CONSTRAINT "TabUnit_tab_or_settlement" CHECK (("tabId" IS NULL) <> ("settlementId" IS NULL));
|
|
|
|
-- A superadmin is above the venues; everyone else belongs to one
|
|
ALTER TABLE "User" ADD CONSTRAINT "User_superadmin_has_no_venue" CHECK (("role" = 'superadmin') = ("venueId" IS NULL));
|
|
|
|
-- Amounts and counts that can't be negative (stock can: a sale is never blocked)
|
|
ALTER TABLE "Settlement" ADD CONSTRAINT "Settlement_amount_positive" CHECK ("amountInCents" >= 0);
|
|
ALTER TABLE "TabUnit" ADD CONSTRAINT "TabUnit_price_positive" CHECK ("unitPriceInCents" >= 0);
|
|
ALTER TABLE "Product" ADD CONSTRAINT "Product_prices_positive" CHECK ("priceInCents" >= 0 AND ("buyPriceInCents" IS NULL OR "buyPriceInCents" >= 0));
|