7c0caf5244
- Add AccountType, NormalBalance, PeriodStatus enums to schema - Add Account model with tenant scoping, code/name/type/normalBalance/parentId - Add AccountingPeriod model with year/month/status/closedAt/closedById - Create ISP_CHART_OF_ACCOUNTS with 28 accounts across all 5 types (1000-5000 ranges) - Create accounting-period.ts with getOpenPeriod, closePeriod, isDateInClosedPeriod - Extend TENANT_SCOPED_MODELS with account and accountingPeriod - Add full query extension blocks for account and accountingPeriod in withTenantContext - Run migration: 20260304144656_add_accounting_models
58 lines
1.9 KiB
SQL
58 lines
1.9 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "AccountType" AS ENUM ('ASSET', 'LIABILITY', 'EQUITY', 'REVENUE', 'EXPENSE');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "NormalBalance" AS ENUM ('DEBIT', 'CREDIT');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "PeriodStatus" AS ENUM ('OPEN', 'CLOSED');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Account" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"accountType" "AccountType" NOT NULL,
|
|
"normalBalance" "NormalBalance" NOT NULL,
|
|
"parentId" TEXT,
|
|
"isSystemAccount" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Account_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AccountingPeriod" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"year" INTEGER NOT NULL,
|
|
"month" INTEGER NOT NULL,
|
|
"status" "PeriodStatus" NOT NULL DEFAULT 'OPEN',
|
|
"closedAt" TIMESTAMP(3),
|
|
"closedById" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "AccountingPeriod_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Account_tenantId_idx" ON "Account"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Account_tenantId_code_key" ON "Account"("tenantId", "code");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "AccountingPeriod_tenantId_idx" ON "AccountingPeriod"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "AccountingPeriod_tenantId_year_month_key" ON "AccountingPeriod"("tenantId", "year", "month");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Account" ADD CONSTRAINT "Account_parentId_fkey" FOREIGN KEY ("parentId") REFERENCES "Account"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AccountingPeriod" ADD CONSTRAINT "AccountingPeriod_closedById_fkey" FOREIGN KEY ("closedById") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|