140 lines
4.3 KiB
SQL
140 lines
4.3 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"email" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
"firstName" TEXT NOT NULL,
|
|
"lastName" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Role" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Permission" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"key" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "UserRole" (
|
|
"userId" TEXT NOT NULL,
|
|
"roleId" TEXT NOT NULL,
|
|
"assignedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"assignedBy" TEXT,
|
|
|
|
PRIMARY KEY ("userId", "roleId"),
|
|
CONSTRAINT "UserRole_roleId_fkey" FOREIGN KEY ("roleId") REFERENCES "Role" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "UserRole_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "RolePermission" (
|
|
"roleId" TEXT NOT NULL,
|
|
"permissionId" TEXT NOT NULL,
|
|
"grantedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
PRIMARY KEY ("roleId", "permissionId"),
|
|
CONSTRAINT "RolePermission_permissionId_fkey" FOREIGN KEY ("permissionId") REFERENCES "Permission" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "RolePermission_roleId_fkey" FOREIGN KEY ("roleId") REFERENCES "Role" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CompanyProfile" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"companyName" TEXT NOT NULL,
|
|
"legalName" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"phone" TEXT NOT NULL,
|
|
"website" TEXT NOT NULL,
|
|
"taxId" TEXT NOT NULL,
|
|
"addressLine1" TEXT NOT NULL,
|
|
"addressLine2" TEXT NOT NULL,
|
|
"city" TEXT NOT NULL,
|
|
"state" TEXT NOT NULL,
|
|
"postalCode" TEXT NOT NULL,
|
|
"country" TEXT NOT NULL,
|
|
"primaryColor" TEXT NOT NULL DEFAULT '#185ADB',
|
|
"accentColor" TEXT NOT NULL DEFAULT '#00A6A6',
|
|
"surfaceColor" TEXT NOT NULL DEFAULT '#F4F7FB',
|
|
"fontFamily" TEXT NOT NULL DEFAULT 'Manrope',
|
|
"logoFileId" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "CompanyProfile_logoFileId_fkey" FOREIGN KEY ("logoFileId") REFERENCES "FileAttachment" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "FileAttachment" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"originalName" TEXT NOT NULL,
|
|
"storedName" TEXT NOT NULL,
|
|
"mimeType" TEXT NOT NULL,
|
|
"sizeBytes" INTEGER NOT NULL,
|
|
"relativePath" TEXT NOT NULL,
|
|
"ownerType" TEXT NOT NULL,
|
|
"ownerId" TEXT NOT NULL,
|
|
"createdById" TEXT,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Customer" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"phone" TEXT NOT NULL,
|
|
"addressLine1" TEXT NOT NULL,
|
|
"addressLine2" TEXT NOT NULL,
|
|
"city" TEXT NOT NULL,
|
|
"state" TEXT NOT NULL,
|
|
"postalCode" TEXT NOT NULL,
|
|
"country" TEXT NOT NULL,
|
|
"notes" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Vendor" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"phone" TEXT NOT NULL,
|
|
"addressLine1" TEXT NOT NULL,
|
|
"addressLine2" TEXT NOT NULL,
|
|
"city" TEXT NOT NULL,
|
|
"state" TEXT NOT NULL,
|
|
"postalCode" TEXT NOT NULL,
|
|
"country" TEXT NOT NULL,
|
|
"notes" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Role_name_key" ON "Role"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Permission_key_key" ON "Permission"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "CompanyProfile_logoFileId_key" ON "CompanyProfile"("logoFileId");
|