211 lines
8.4 KiB
SQL
211 lines
8.4 KiB
SQL
-- CreateTable
|
|
CREATE TABLE `User` (
|
|
`id` VARCHAR(191) NOT NULL,
|
|
`firstName` VARCHAR(191) NOT NULL,
|
|
`login` VARCHAR(191) NOT NULL,
|
|
`passwordHash` VARCHAR(191) NOT NULL,
|
|
`color` VARCHAR(191) NOT NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
UNIQUE INDEX `User_login_key`(`login`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Setting` (
|
|
`id` INTEGER NOT NULL DEFAULT 1,
|
|
`defaultSplit` JSON NOT NULL,
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Category` (
|
|
`id` VARCHAR(191) NOT NULL,
|
|
`name` VARCHAR(191) NOT NULL,
|
|
`emoji` VARCHAR(191) NOT NULL,
|
|
`kind` ENUM('INCOME', 'EXPENSE') NOT NULL,
|
|
`archived` BOOLEAN NOT NULL DEFAULT false,
|
|
`sortOrder` INTEGER NOT NULL DEFAULT 0,
|
|
|
|
UNIQUE INDEX `Category_name_kind_key`(`name`, `kind`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Operation` (
|
|
`id` VARCHAR(191) NOT NULL,
|
|
`kind` ENUM('INCOME', 'EXPENSE') NOT NULL,
|
|
`amount` DECIMAL(12, 2) NOT NULL,
|
|
`date` DATE NOT NULL,
|
|
`label` VARCHAR(191) NOT NULL,
|
|
`note` TEXT NULL,
|
|
`categoryId` VARCHAR(191) NOT NULL,
|
|
`accountUserId` VARCHAR(191) NOT NULL,
|
|
`status` ENUM('PLANNED', 'ACTUAL', 'SKIPPED') NOT NULL,
|
|
`recurringId` VARCHAR(191) NULL,
|
|
`occurrenceDate` DATE NULL,
|
|
`allocatedToRecurringId` VARCHAR(191) NULL,
|
|
`allocatedToOperationId` VARCHAR(191) NULL,
|
|
`allocationMode` ENUM('REDUCE_SHARED', 'BENEFICIARY_ONLY', 'CUSTOM') NULL,
|
|
`createdById` VARCHAR(191) NOT NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
INDEX `Operation_date_idx`(`date`),
|
|
UNIQUE INDEX `Operation_recurringId_occurrenceDate_key`(`recurringId`, `occurrenceDate`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `OperationSplit` (
|
|
`operationId` VARCHAR(191) NOT NULL,
|
|
`userId` VARCHAR(191) NOT NULL,
|
|
`basisPoints` INTEGER NOT NULL,
|
|
|
|
PRIMARY KEY (`operationId`, `userId`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Recurring` (
|
|
`id` VARCHAR(191) NOT NULL,
|
|
`kind` ENUM('INCOME', 'EXPENSE') NOT NULL,
|
|
`amount` DECIMAL(12, 2) NOT NULL,
|
|
`label` VARCHAR(191) NOT NULL,
|
|
`categoryId` VARCHAR(191) NOT NULL,
|
|
`accountUserId` VARCHAR(191) NOT NULL,
|
|
`frequency` ENUM('MONTHLY', 'QUARTERLY', 'YEARLY') NOT NULL,
|
|
`dayOfMonth` INTEGER NOT NULL,
|
|
`startDate` DATE NOT NULL,
|
|
`endDate` DATE NULL,
|
|
`paused` BOOLEAN NOT NULL DEFAULT false,
|
|
`loanId` VARCHAR(191) NULL,
|
|
`allocatedToRecurringId` VARCHAR(191) NULL,
|
|
`allocationMode` ENUM('REDUCE_SHARED', 'BENEFICIARY_ONLY', 'CUSTOM') NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
UNIQUE INDEX `Recurring_loanId_key`(`loanId`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `RecurringSplit` (
|
|
`recurringId` VARCHAR(191) NOT NULL,
|
|
`userId` VARCHAR(191) NOT NULL,
|
|
`basisPoints` INTEGER NOT NULL,
|
|
|
|
PRIMARY KEY (`recurringId`, `userId`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Settlement` (
|
|
`id` VARCHAR(191) NOT NULL,
|
|
`fromUserId` VARCHAR(191) NOT NULL,
|
|
`toUserId` VARCHAR(191) NOT NULL,
|
|
`amount` DECIMAL(12, 2) NOT NULL,
|
|
`date` DATE NOT NULL,
|
|
`note` VARCHAR(191) NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Budget` (
|
|
`id` VARCHAR(191) NOT NULL,
|
|
`categoryId` VARCHAR(191) NOT NULL,
|
|
`monthlyAmount` DECIMAL(12, 2) NOT NULL,
|
|
`startMonth` CHAR(7) NOT NULL,
|
|
`endMonth` CHAR(7) NULL,
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Loan` (
|
|
`id` VARCHAR(191) NOT NULL,
|
|
`name` VARCHAR(191) NOT NULL,
|
|
`lender` VARCHAR(191) NULL,
|
|
`principal` DECIMAL(12, 2) NOT NULL,
|
|
`annualRate` DECIMAL(6, 3) NOT NULL,
|
|
`durationMonths` INTEGER NOT NULL,
|
|
`startDate` DATE NOT NULL,
|
|
`insuranceMonthly` DECIMAL(12, 2) NOT NULL DEFAULT 0,
|
|
`accountUserId` VARCHAR(191) NOT NULL,
|
|
`paymentOverride` DECIMAL(12, 2) NULL,
|
|
`referenceDate` DATE NULL,
|
|
`referenceRemainingCapital` DECIMAL(12, 2) NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `LoanSplit` (
|
|
`loanId` VARCHAR(191) NOT NULL,
|
|
`userId` VARCHAR(191) NOT NULL,
|
|
`basisPoints` INTEGER NOT NULL,
|
|
|
|
PRIMARY KEY (`loanId`, `userId`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Operation` ADD CONSTRAINT `Operation_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `Category`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Operation` ADD CONSTRAINT `Operation_accountUserId_fkey` FOREIGN KEY (`accountUserId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Operation` ADD CONSTRAINT `Operation_recurringId_fkey` FOREIGN KEY (`recurringId`) REFERENCES `Recurring`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Operation` ADD CONSTRAINT `Operation_allocatedToRecurringId_fkey` FOREIGN KEY (`allocatedToRecurringId`) REFERENCES `Recurring`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Operation` ADD CONSTRAINT `Operation_allocatedToOperationId_fkey` FOREIGN KEY (`allocatedToOperationId`) REFERENCES `Operation`(`id`) ON DELETE SET NULL ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Operation` ADD CONSTRAINT `Operation_createdById_fkey` FOREIGN KEY (`createdById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `OperationSplit` ADD CONSTRAINT `OperationSplit_operationId_fkey` FOREIGN KEY (`operationId`) REFERENCES `Operation`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `OperationSplit` ADD CONSTRAINT `OperationSplit_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Recurring` ADD CONSTRAINT `Recurring_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `Category`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Recurring` ADD CONSTRAINT `Recurring_accountUserId_fkey` FOREIGN KEY (`accountUserId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Recurring` ADD CONSTRAINT `Recurring_loanId_fkey` FOREIGN KEY (`loanId`) REFERENCES `Loan`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Recurring` ADD CONSTRAINT `Recurring_allocatedToRecurringId_fkey` FOREIGN KEY (`allocatedToRecurringId`) REFERENCES `Recurring`(`id`) ON DELETE SET NULL ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `RecurringSplit` ADD CONSTRAINT `RecurringSplit_recurringId_fkey` FOREIGN KEY (`recurringId`) REFERENCES `Recurring`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `RecurringSplit` ADD CONSTRAINT `RecurringSplit_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Settlement` ADD CONSTRAINT `Settlement_fromUserId_fkey` FOREIGN KEY (`fromUserId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Settlement` ADD CONSTRAINT `Settlement_toUserId_fkey` FOREIGN KEY (`toUserId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Budget` ADD CONSTRAINT `Budget_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `Category`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Loan` ADD CONSTRAINT `Loan_accountUserId_fkey` FOREIGN KEY (`accountUserId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `LoanSplit` ADD CONSTRAINT `LoanSplit_loanId_fkey` FOREIGN KEY (`loanId`) REFERENCES `Loan`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `LoanSplit` ADD CONSTRAINT `LoanSplit_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|