91 lines
3.1 KiB
SQL
91 lines
3.1 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "billing_settings" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"autoGenerate" BOOLEAN NOT NULL DEFAULT true,
|
|
"gracePeriodDays" INTEGER NOT NULL DEFAULT 7,
|
|
"dueDateOffsetDays" INTEGER NOT NULL DEFAULT 15,
|
|
"lateFeePercent" DECIMAL(5,2) NOT NULL DEFAULT 0,
|
|
"invoicePrefix" TEXT NOT NULL DEFAULT 'INV',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "billing_settings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "chart_of_accounts" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"parentId" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"isSystem" BOOLEAN NOT NULL DEFAULT false,
|
|
"balance" DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "chart_of_accounts_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "journal_entries" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"entryDate" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"description" TEXT NOT NULL,
|
|
"reference" TEXT,
|
|
"sourceType" TEXT,
|
|
"sourceId" TEXT,
|
|
"createdById" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "journal_entries_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "journal_lines" (
|
|
"id" TEXT NOT NULL,
|
|
"journalEntryId" TEXT NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
"debit" DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
"credit" DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "journal_lines_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "billing_settings_tenantId_key" ON "billing_settings"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "chart_of_accounts_tenantId_idx" ON "chart_of_accounts"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "chart_of_accounts_tenantId_type_idx" ON "chart_of_accounts"("tenantId", "type");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "chart_of_accounts_tenantId_code_key" ON "chart_of_accounts"("tenantId", "code");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "journal_entries_tenantId_idx" ON "journal_entries"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "journal_entries_tenantId_sourceType_sourceId_idx" ON "journal_entries"("tenantId", "sourceType", "sourceId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "journal_lines_journalEntryId_idx" ON "journal_lines"("journalEntryId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "journal_lines_accountId_idx" ON "journal_lines"("accountId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "chart_of_accounts" ADD CONSTRAINT "chart_of_accounts_parentId_fkey" FOREIGN KEY ("parentId") REFERENCES "chart_of_accounts"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "journal_lines" ADD CONSTRAINT "journal_lines_journalEntryId_fkey" FOREIGN KEY ("journalEntryId") REFERENCES "journal_entries"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "journal_lines" ADD CONSTRAINT "journal_lines_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "chart_of_accounts"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|