837b7f1979
- Add JournalEntryStatus and JournalEntrySource enums to Prisma schema - Add JournalEntry model with maker-checker fields, self-referential reversal relation, and audit timestamps - Add JournalEntryLine model with debit/credit Decimal(15,2) fields - Update Account model with journalEntryLines back-relation - Update User model with createdJournalEntries and approvedJournalEntries back-relations - Run migration: 20260304150817_add_journal_entry_models - Add journalEntry and journalEntryLine to TENANT_SCOPED_MODELS in prisma-tenant.ts - Create JournalEntryService with createEntry, approveEntry, reverseEntry, getAccountBalance, getTrialBalance - Enforce debit=credit balance using integer cents comparison (avoids float issues) - SYSTEM source entries auto-posted; MANUAL entries start as DRAFT for maker-checker
80 lines
3.0 KiB
SQL
80 lines
3.0 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "JournalEntryStatus" AS ENUM ('DRAFT', 'PENDING_APPROVAL', 'APPROVED', 'POSTED', 'REVERSED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "JournalEntrySource" AS ENUM ('SYSTEM', 'MANUAL');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "JournalEntry" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"entryNumber" TEXT NOT NULL,
|
|
"date" TIMESTAMP(3) NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"source" "JournalEntrySource" NOT NULL,
|
|
"status" "JournalEntryStatus" NOT NULL DEFAULT 'DRAFT',
|
|
"referenceType" TEXT,
|
|
"referenceId" TEXT,
|
|
"reversesEntryId" TEXT,
|
|
"createdById" TEXT NOT NULL,
|
|
"approvedById" TEXT,
|
|
"approvedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "JournalEntry_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "JournalEntryLine" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"journalEntryId" TEXT NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
"debit" DECIMAL(15,2) NOT NULL DEFAULT 0,
|
|
"credit" DECIMAL(15,2) NOT NULL DEFAULT 0,
|
|
"description" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "JournalEntryLine_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "JournalEntry_reversesEntryId_key" ON "JournalEntry"("reversesEntryId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "JournalEntry_tenantId_idx" ON "JournalEntry"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "JournalEntry_tenantId_date_idx" ON "JournalEntry"("tenantId", "date");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "JournalEntry_referenceType_referenceId_idx" ON "JournalEntry"("referenceType", "referenceId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "JournalEntry_tenantId_entryNumber_key" ON "JournalEntry"("tenantId", "entryNumber");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "JournalEntryLine_tenantId_idx" ON "JournalEntryLine"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "JournalEntryLine_accountId_idx" ON "JournalEntryLine"("accountId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "JournalEntryLine_journalEntryId_idx" ON "JournalEntryLine"("journalEntryId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "JournalEntry" ADD CONSTRAINT "JournalEntry_reversesEntryId_fkey" FOREIGN KEY ("reversesEntryId") REFERENCES "JournalEntry"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "JournalEntry" ADD CONSTRAINT "JournalEntry_createdById_fkey" FOREIGN KEY ("createdById") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "JournalEntry" ADD CONSTRAINT "JournalEntry_approvedById_fkey" FOREIGN KEY ("approvedById") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "JournalEntryLine" ADD CONSTRAINT "JournalEntryLine_journalEntryId_fkey" FOREIGN KEY ("journalEntryId") REFERENCES "JournalEntry"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "JournalEntryLine" ADD CONSTRAINT "JournalEntryLine_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "Account"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|