ksv741
e6f58ef0ee
Store exact RUB balances, atomic transfers, linked refunds and versioned edits independently of T-Bank. Add migration, HTTP and durability verification.
70 lines
2.8 KiB
SQL
70 lines
2.8 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "BankCard" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"bankName" TEXT,
|
|
"lastFour" TEXT,
|
|
"openingBalanceKopecks" BIGINT NOT NULL,
|
|
"openingDate" TEXT NOT NULL,
|
|
"version" INTEGER NOT NULL DEFAULT 1,
|
|
"archivedAt" DATETIME,
|
|
"createFingerprint" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "BankCard_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ExpenseCategory" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"nameKey" TEXT NOT NULL,
|
|
"seedKey" TEXT,
|
|
"version" INTEGER NOT NULL DEFAULT 1,
|
|
CONSTRAINT "ExpenseCategory_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "BankCardOperation" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"kind" TEXT NOT NULL,
|
|
"cardId" TEXT NOT NULL,
|
|
"targetCardId" TEXT,
|
|
"categoryId" TEXT,
|
|
"originalExpenseId" TEXT,
|
|
"amountKopecks" BIGINT NOT NULL,
|
|
"date" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"version" INTEGER NOT NULL DEFAULT 1,
|
|
"deletedAt" DATETIME,
|
|
"createFingerprint" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "BankCardOperation_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "BankCardOperation_cardId_fkey" FOREIGN KEY ("cardId") REFERENCES "BankCard" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "BankCardOperation_targetCardId_fkey" FOREIGN KEY ("targetCardId") REFERENCES "BankCard" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "BankCardOperation_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "ExpenseCategory" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "BankCardOperation_originalExpenseId_fkey" FOREIGN KEY ("originalExpenseId") REFERENCES "BankCardOperation" ("id") ON DELETE RESTRICT ON UPDATE NO ACTION
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "BankCard_userId_idx" ON "BankCard"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ExpenseCategory_userId_nameKey_key" ON "ExpenseCategory"("userId", "nameKey");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ExpenseCategory_userId_seedKey_key" ON "ExpenseCategory"("userId", "seedKey");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "BankCardOperation_userId_cardId_date_idx" ON "BankCardOperation"("userId", "cardId", "date");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "BankCardOperation_userId_targetCardId_date_idx" ON "BankCardOperation"("userId", "targetCardId", "date");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "BankCardOperation_originalExpenseId_idx" ON "BankCardOperation"("originalExpenseId");
|