0xn0l

📂 /home/brightbrai/.trash/api.9/migrations

✎ EDIT: 01_schema.sql ← BACK

-- Academy ERP SQLite Schema

PRAGMA foreign_keys = OFF;

CREATE TABLE IF NOT EXISTS "User" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "email" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "passwordHash" TEXT NOT NULL,
  "role" TEXT NOT NULL DEFAULT 'ADMIN',
  "isActive" INTEGER NOT NULL DEFAULT 1,
  "lastLoginAt" TEXT DEFAULT (datetime('now')),
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS "Account" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "code" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "type" TEXT NOT NULL,
  "subtype" TEXT NOT NULL,
  "isGroup" INTEGER NOT NULL DEFAULT 0,
  "parentId" TEXT,
  "isActive" INTEGER NOT NULL DEFAULT 1,
  "openingBalance" REAL NOT NULL DEFAULT 0,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "type_idx" ("type"),
  INDEX "parentId_idx" ("parentId")
);

CREATE TABLE IF NOT EXISTS "Student" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "studentNumber" TEXT NOT NULL,
  "registrationNumber" TEXT,
  "firstName" TEXT NOT NULL,
  "lastName" TEXT NOT NULL,
  "dateOfBirth" TEXT DEFAULT (datetime('now')),
  "gender" TEXT,
  "country" TEXT NOT NULL DEFAULT 'Saudi Arabia',
  "city" TEXT,
  "passportNumber" TEXT,
  "guardianName" TEXT,
  "guardianPhone" TEXT,
  "guardianEmail" TEXT,
  "guardianRelation" TEXT,
  "address" TEXT,
  "phone" TEXT,
  "email" TEXT,
  "course" TEXT,
  "className" TEXT,
  "section" TEXT,
  "batch" TEXT,
  "admissionDate" TEXT NOT NULL DEFAULT (datetime('now')),
  "status" TEXT NOT NULL DEFAULT 'ACTIVE',
  "discountPercent" REAL NOT NULL DEFAULT 0,
  "scholarshipPercent" REAL NOT NULL DEFAULT 0,
  "notes" TEXT,
  "receivableAccountId" TEXT,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "status_idx" ("status"),
  INDEX "className_idx" ("className"),
  INDEX "batch_idx" ("batch")
);

CREATE TABLE IF NOT EXISTS "FeeHead" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "name" TEXT NOT NULL,
  "code" TEXT NOT NULL,
  "description" TEXT,
  "defaultAmount" REAL NOT NULL DEFAULT 0,
  "isRecurring" INTEGER NOT NULL DEFAULT 1,
  "incomeAccountId" TEXT,
  "isActive" INTEGER NOT NULL DEFAULT 1,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS "FeeStructure" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "studentId" TEXT NOT NULL,
  "feeHeadId" TEXT NOT NULL,
  "amount" REAL NOT NULL DEFAULT 0,
  "dueDayOfMonth" INTEGER NOT NULL DEFAULT 5,
  "startDate" TEXT NOT NULL DEFAULT (datetime('now')),
  "endDate" TEXT DEFAULT (datetime('now')),
  "isActive" INTEGER NOT NULL DEFAULT 1,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "studentId_idx" ("studentId"),
  INDEX "feeHeadId_idx" ("feeHeadId")
);

CREATE TABLE IF NOT EXISTS "Invoice" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "invoiceNumber" TEXT NOT NULL,
  "studentId" TEXT NOT NULL,
  "invoiceDate" TEXT NOT NULL DEFAULT (datetime('now')),
  "dueDate" TEXT NOT NULL DEFAULT (datetime('now')),
  "period" TEXT,
  "subtotal" REAL NOT NULL DEFAULT 0,
  "discountPercent" REAL NOT NULL DEFAULT 0,
  "discountAmount" REAL NOT NULL DEFAULT 0,
  "taxAmount" REAL NOT NULL DEFAULT 0,
  "totalAmount" REAL NOT NULL DEFAULT 0,
  "paidAmount" REAL NOT NULL DEFAULT 0,
  "balanceDue" REAL NOT NULL DEFAULT 0,
  "status" TEXT NOT NULL DEFAULT 'UNPAID',
  "type" TEXT NOT NULL DEFAULT 'INVOICE',
  "notes" TEXT,
  "reversalOfId" TEXT,
  "createdById" TEXT NOT NULL,
  "journalEntryId" TEXT,
  "cancelledAt" TEXT DEFAULT (datetime('now')),
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "studentId_idx" ("studentId"),
  INDEX "status_idx" ("status"),
  INDEX "period_idx" ("period"),
  INDEX "invoiceDate_idx" ("invoiceDate")
);

CREATE TABLE IF NOT EXISTS "InvoiceLine" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "invoiceId" TEXT NOT NULL,
  "feeHeadId" TEXT,
  "description" TEXT NOT NULL,
  "quantity" REAL NOT NULL DEFAULT 1,
  "unitPrice" REAL NOT NULL DEFAULT 0,
  "amount" REAL NOT NULL DEFAULT 0,
  "createdAt" TEXT DEFAULT (datetime('now')),
  INDEX "invoiceId_idx" ("invoiceId")
);

CREATE TABLE IF NOT EXISTS "Payment" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "receiptNumber" TEXT NOT NULL,
  "invoiceId" TEXT,
  "studentId" TEXT,
  "paymentDate" TEXT NOT NULL DEFAULT (datetime('now')),
  "amount" REAL NOT NULL DEFAULT 0,
  "paymentMethod" TEXT NOT NULL,
  "reference" TEXT,
  "bankAccountId" TEXT,
  "description" TEXT,
  "status" TEXT NOT NULL DEFAULT 'COMPLETED',
  "receivedById" TEXT NOT NULL,
  "journalEntryId" TEXT,
  "reversedAt" TEXT DEFAULT (datetime('now')),
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "invoiceId_idx" ("invoiceId"),
  INDEX "studentId_idx" ("studentId"),
  INDEX "paymentDate_idx" ("paymentDate"),
  INDEX "paymentMethod_idx" ("paymentMethod")
);

CREATE TABLE IF NOT EXISTS "JournalEntry" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "entryNumber" TEXT NOT NULL,
  "entryDate" TEXT NOT NULL DEFAULT (datetime('now')),
  "description" TEXT NOT NULL,
  "reference" TEXT,
  "source" TEXT NOT NULL DEFAULT 'MANUAL',
  "posted" INTEGER NOT NULL DEFAULT 1,
  "postedById" TEXT NOT NULL,
  "reversalOfId" TEXT,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "entryDate_idx" ("entryDate"),
  INDEX "source_idx" ("source")
);

CREATE TABLE IF NOT EXISTS "JournalLine" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "journalEntryId" TEXT NOT NULL,
  "accountId" TEXT NOT NULL,
  "debit" REAL NOT NULL DEFAULT 0,
  "credit" REAL NOT NULL DEFAULT 0,
  "description" TEXT,
  "createdAt" TEXT DEFAULT (datetime('now')),
  INDEX "journalEntryId_idx" ("journalEntryId"),
  INDEX "accountId_idx" ("accountId")
);

CREATE TABLE IF NOT EXISTS "Expense" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "expenseNumber" TEXT NOT NULL,
  "expenseDate" TEXT NOT NULL DEFAULT (datetime('now')),
  "category" TEXT NOT NULL,
  "vendorId" TEXT,
  "description" TEXT NOT NULL,
  "amount" REAL NOT NULL DEFAULT 0,
  "paymentMethod" TEXT NOT NULL DEFAULT 'CASH',
  "bankAccountId" TEXT,
  "expenseAccountId" TEXT,
  "paidById" TEXT NOT NULL,
  "journalEntryId" TEXT,
  "status" TEXT NOT NULL DEFAULT 'POSTED',
  "attachmentPath" TEXT,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "expenseDate_idx" ("expenseDate"),
  INDEX "category_idx" ("category")
);

CREATE TABLE IF NOT EXISTS "Vendor" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "name" TEXT NOT NULL,
  "code" TEXT NOT NULL,
  "contact" TEXT,
  "email" TEXT,
  "phone" TEXT,
  "address" TEXT,
  "taxNumber" TEXT,
  "payableAccountId" TEXT,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS "BankAccount" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "accountName" TEXT NOT NULL,
  "bankName" TEXT NOT NULL,
  "accountNumber" TEXT NOT NULL,
  "iban" TEXT,
  "currency" TEXT NOT NULL DEFAULT 'PKR',
  "balance" REAL NOT NULL DEFAULT 0,
  "ledgerAccountId" TEXT,
  "isActive" INTEGER NOT NULL DEFAULT 1,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS "Employee" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "employeeCode" TEXT NOT NULL,
  "firstName" TEXT NOT NULL,
  "lastName" TEXT NOT NULL,
  "email" TEXT,
  "phone" TEXT,
  "cnic" TEXT,
  "dateOfBirth" TEXT DEFAULT (datetime('now')),
  "gender" TEXT,
  "maritalStatus" TEXT,
  "nationality" TEXT,
  "religion" TEXT,
  "bloodGroup" TEXT,
  "address" TEXT,
  "city" TEXT,
  "country" TEXT,
  "emergencyName" TEXT,
  "emergencyRelation" TEXT,
  "emergencyPhone" TEXT,
  "designation" TEXT NOT NULL,
  "department" TEXT,
  "employmentType" TEXT NOT NULL DEFAULT 'FULL_TIME',
  "joiningDate" TEXT NOT NULL DEFAULT (datetime('now')),
  "leavingDate" TEXT DEFAULT (datetime('now')),
  "status" TEXT NOT NULL DEFAULT 'ACTIVE',
  "qualification" TEXT,
  "experienceYears" INTEGER NOT NULL DEFAULT 0,
  "previousEmployer" TEXT,
  "bankName" TEXT,
  "bankAccountNumber" TEXT,
  "iban" TEXT,
  "basicSalary" REAL NOT NULL DEFAULT 0,
  "hraAllowance" REAL NOT NULL DEFAULT 0,
  "transportAllowance" REAL NOT NULL DEFAULT 0,
  "medicalAllowance" REAL NOT NULL DEFAULT 0,
  "otherAllowance" REAL NOT NULL DEFAULT 0,
  "taxPercent" REAL NOT NULL DEFAULT 0,
  "taxMode" TEXT NOT NULL DEFAULT 'PERCENT',
  "taxFixedAmount" REAL NOT NULL DEFAULT 0,
  "taxFormula" TEXT,
  "pfPercent" REAL NOT NULL DEFAULT 0,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "status_idx" ("status"),
  INDEX "department_idx" ("department"),
  INDEX "employmentType_idx" ("employmentType")
);

CREATE TABLE IF NOT EXISTS "Payroll" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "employeeId" TEXT NOT NULL,
  "payPeriod" TEXT NOT NULL,
  "payDate" TEXT NOT NULL DEFAULT (datetime('now')),
  "basicSalary" REAL NOT NULL DEFAULT 0,
  "hraAllowance" REAL NOT NULL DEFAULT 0,
  "transportAllowance" REAL NOT NULL DEFAULT 0,
  "medicalAllowance" REAL NOT NULL DEFAULT 0,
  "otherAllowance" REAL NOT NULL DEFAULT 0,
  "bonus" REAL NOT NULL DEFAULT 0,
  "grossPay" REAL NOT NULL DEFAULT 0,
  "tax" REAL NOT NULL DEFAULT 0,
  "providentFund" REAL NOT NULL DEFAULT 0,
  "loanDeduction" REAL NOT NULL DEFAULT 0,
  "otherDeduction" REAL NOT NULL DEFAULT 0,
  "totalDeductions" REAL NOT NULL DEFAULT 0,
  "netPay" REAL NOT NULL DEFAULT 0,
  "paymentMethod" TEXT NOT NULL DEFAULT 'BANK_TRANSFER',
  "bankAccountId" TEXT,
  "status" TEXT NOT NULL DEFAULT 'POSTED',
  "journalEntryId" TEXT,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "employeeId_idx" ("employeeId"),
  INDEX "payPeriod_idx" ("payPeriod"),
  INDEX "status_idx" ("status")
);

CREATE TABLE IF NOT EXISTS "TaxSlab" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "name" TEXT NOT NULL,
  "minIncome" REAL NOT NULL DEFAULT 0,
  "maxIncome" REAL DEFAULT 0,
  "ratePercent" REAL NOT NULL DEFAULT 0,
  "fixedAmount" REAL NOT NULL DEFAULT 0,
  "isActive" INTEGER NOT NULL DEFAULT 1,
  "sortOrder" INTEGER NOT NULL DEFAULT 0,
  "createdAt" TEXT DEFAULT (datetime('now')),
  "updatedAt" TEXT DEFAULT (datetime('now')),
  INDEX "name_idx" ("name"),
  INDEX "sortOrder_idx" ("sortOrder")
);

CREATE TABLE IF NOT EXISTS "AuditLog" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "userId" TEXT,
  "action" TEXT NOT NULL,
  "entity" TEXT NOT NULL,
  "entityId" TEXT,
  "description" TEXT NOT NULL,
  "oldValue" TEXT,
  "newValue" TEXT,
  "ipAddress" TEXT,
  "userAgent" TEXT,
  "createdAt" TEXT DEFAULT (datetime('now')),
  INDEX "userId_idx" ("userId"),
  INDEX "entity_idx" ("entity"),
  INDEX "action_idx" ("action"),
  INDEX "createdAt_idx" ("createdAt")
);

CREATE TABLE IF NOT EXISTS "Setting" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "key" TEXT NOT NULL,
  "value" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "Notification" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "type" TEXT NOT NULL,
  "channel" TEXT NOT NULL,
  "recipient" TEXT NOT NULL,
  "message" TEXT NOT NULL,
  "status" TEXT NOT NULL DEFAULT 'PENDING',
  "sentAt" TEXT DEFAULT (datetime('now')),
  "createdAt" TEXT DEFAULT (datetime('now')),
  INDEX "status_idx" ("status")
);

0xn0l - 0011000001111000011011100011000001101100