josh
7c0d422228
Ground-up TypeScript rewrite of the Vector hardware parts inventory
system. Ships the full roadmap (Phases 0-8) in one initial commit:
- pnpm + Turbo monorepo: apps/{api,web,e2e}, packages/{db,shared,ui,config}
- Express 5 + Prisma 5 + zod validation + JWT w/ refresh-token rotation
- React 19 + Vite + shadcn/ui + TanStack Query/Table + nuqs URL state
- Repair/RMA, tags, bulk ops, saved views, CSV audit export
- Analytics dashboard on Recharts + EOL tracking
- Signed webhook subscriptions (HMAC-SHA256) with in-process emitter
- Vitest unit tests (shared schemas, api services/helpers) + Playwright skeleton
- Gitea Actions CI (lint, typecheck, test+coverage, build) + Renovate
Deferred follow-ups: Postgres cutover (data-migration script ready),
BullMQ worker for webhook delivery, @react-pdf PDF export, CSV import wizard.
174 lines
6.1 KiB
SQL
174 lines
6.1 KiB
SQL
-- AlterTable
|
|
ALTER TABLE "Manufacturer" ADD COLUMN "eolDate" DATETIME;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Category" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Tag" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"color" TEXT,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "PartTag" (
|
|
"partId" TEXT NOT NULL,
|
|
"tagId" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
PRIMARY KEY ("partId", "tagId"),
|
|
CONSTRAINT "PartTag_partId_fkey" FOREIGN KEY ("partId") REFERENCES "Part" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "PartTag_tagId_fkey" FOREIGN KEY ("tagId") REFERENCES "Tag" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Host" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"location" TEXT,
|
|
"notes" TEXT,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "RepairJob" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"partId" TEXT NOT NULL,
|
|
"hostId" TEXT,
|
|
"assigneeId" TEXT,
|
|
"status" TEXT NOT NULL DEFAULT 'PENDING',
|
|
"openedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"closedAt" DATETIME,
|
|
"notes" TEXT,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "RepairJob_partId_fkey" FOREIGN KEY ("partId") REFERENCES "Part" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "RepairJob_hostId_fkey" FOREIGN KEY ("hostId") REFERENCES "Host" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT "RepairJob_assigneeId_fkey" FOREIGN KEY ("assigneeId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "WebhookSubscription" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"url" TEXT NOT NULL,
|
|
"secret" TEXT NOT NULL,
|
|
"events" TEXT NOT NULL,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SavedView" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"resource" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"filterJson" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "SavedView_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CsvImportJob" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT,
|
|
"resource" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'PENDING',
|
|
"filename" TEXT NOT NULL,
|
|
"stagedRows" INTEGER NOT NULL DEFAULT 0,
|
|
"errors" TEXT,
|
|
"startedAt" DATETIME,
|
|
"finishedAt" DATETIME,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "CsvImportJob_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- RedefineTables
|
|
PRAGMA defer_foreign_keys=ON;
|
|
PRAGMA foreign_keys=OFF;
|
|
CREATE TABLE "new_Part" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"serialNumber" TEXT NOT NULL,
|
|
"mpn" TEXT NOT NULL,
|
|
"manufacturerId" TEXT NOT NULL,
|
|
"price" REAL,
|
|
"state" TEXT NOT NULL DEFAULT 'SPARE',
|
|
"binId" TEXT,
|
|
"categoryId" TEXT,
|
|
"replacementPartId" TEXT,
|
|
"notes" TEXT,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "Part_manufacturerId_fkey" FOREIGN KEY ("manufacturerId") REFERENCES "Manufacturer" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "Part_binId_fkey" FOREIGN KEY ("binId") REFERENCES "Bin" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT "Part_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "Category" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT "Part_replacementPartId_fkey" FOREIGN KEY ("replacementPartId") REFERENCES "Part" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_Part" ("binId", "createdAt", "id", "manufacturerId", "mpn", "notes", "price", "serialNumber", "state", "updatedAt") SELECT "binId", "createdAt", "id", "manufacturerId", "mpn", "notes", "price", "serialNumber", "state", "updatedAt" FROM "Part";
|
|
DROP TABLE "Part";
|
|
ALTER TABLE "new_Part" RENAME TO "Part";
|
|
CREATE UNIQUE INDEX "Part_serialNumber_key" ON "Part"("serialNumber");
|
|
CREATE INDEX "Part_state_idx" ON "Part"("state");
|
|
CREATE INDEX "Part_binId_idx" ON "Part"("binId");
|
|
CREATE INDEX "Part_manufacturerId_idx" ON "Part"("manufacturerId");
|
|
CREATE INDEX "Part_mpn_idx" ON "Part"("mpn");
|
|
CREATE INDEX "Part_categoryId_idx" ON "Part"("categoryId");
|
|
CREATE INDEX "Part_replacementPartId_idx" ON "Part"("replacementPartId");
|
|
PRAGMA foreign_keys=ON;
|
|
PRAGMA defer_foreign_keys=OFF;
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Category_name_key" ON "Category"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Tag_name_key" ON "Tag"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "PartTag_tagId_idx" ON "PartTag"("tagId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Host_name_key" ON "Host"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RepairJob_partId_idx" ON "RepairJob"("partId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RepairJob_status_idx" ON "RepairJob"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RepairJob_hostId_idx" ON "RepairJob"("hostId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RepairJob_assigneeId_idx" ON "RepairJob"("assigneeId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RepairJob_status_openedAt_idx" ON "RepairJob"("status", "openedAt" DESC);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "WebhookSubscription_active_idx" ON "WebhookSubscription"("active");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "SavedView_userId_idx" ON "SavedView"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "SavedView_userId_resource_name_key" ON "SavedView"("userId", "resource", "name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CsvImportJob_userId_idx" ON "CsvImportJob"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CsvImportJob_status_idx" ON "CsvImportJob"("status");
|