a742f701e7
- InventoryService: registerItem, recordMovement, getStockLevels, getItemMovements, listItems - RECEIVED movements auto-post JE (DR 1200 Equipment Inventory, CR 2010 AP) - Stock levels derived from movement aggregation (no mutable quantity column) - All 5 movement types validated with type-specific rules - API routes: POST/GET items, GET item detail, POST/GET movements, GET stock-levels - CASL: OFFICE_STAFF gets manage Inventory permission - Migration applied: add_inventory_models (4 enums, 2 tables) - 13 tests: registration, all movement types, stock derivation, JE posting, history Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
78 lines
2.6 KiB
SQL
78 lines
2.6 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "ItemTrackingType" AS ENUM ('SERIALIZED', 'BATCH');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ItemCondition" AS ENUM ('NEW', 'REFURBISHED', 'USED', 'DAMAGED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "MovementType" AS ENUM ('RECEIVED', 'ISSUED', 'RETURNED', 'DISPOSED', 'TRANSFERRED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "LocationType" AS ENUM ('WAREHOUSE', 'TECHNICIAN', 'SUBSCRIBER');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "InventoryItem" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"itemType" TEXT NOT NULL,
|
|
"model" TEXT,
|
|
"serialNumber" TEXT,
|
|
"trackingType" "ItemTrackingType" NOT NULL,
|
|
"purchaseCost" DECIMAL(10,2),
|
|
"purchaseDate" TIMESTAMP(3),
|
|
"warrantyExpiry" TIMESTAMP(3),
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "InventoryItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "StockMovement" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"inventoryItemId" TEXT NOT NULL,
|
|
"movementType" "MovementType" NOT NULL,
|
|
"quantity" INTEGER NOT NULL DEFAULT 1,
|
|
"condition" "ItemCondition",
|
|
"fromLocationType" "LocationType",
|
|
"fromLocationId" TEXT,
|
|
"toLocationType" "LocationType",
|
|
"toLocationId" TEXT,
|
|
"notes" TEXT,
|
|
"journalEntryId" TEXT,
|
|
"performedById" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "StockMovement_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "InventoryItem_tenantId_idx" ON "InventoryItem"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "InventoryItem_tenantId_itemType_idx" ON "InventoryItem"("tenantId", "itemType");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "InventoryItem_tenantId_trackingType_idx" ON "InventoryItem"("tenantId", "trackingType");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "InventoryItem_tenantId_serialNumber_key" ON "InventoryItem"("tenantId", "serialNumber");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "StockMovement_tenantId_idx" ON "StockMovement"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "StockMovement_inventoryItemId_idx" ON "StockMovement"("inventoryItemId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "StockMovement_tenantId_movementType_idx" ON "StockMovement"("tenantId", "movementType");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "StockMovement" ADD CONSTRAINT "StockMovement_inventoryItemId_fkey" FOREIGN KEY ("inventoryItemId") REFERENCES "InventoryItem"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "StockMovement" ADD CONSTRAINT "StockMovement_performedById_fkey" FOREIGN KEY ("performedById") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|