Database

SyncAD uses PostgreSQL 15 with Drizzle ORM as the ORM. Schema definitions live in packages/db/src/schema.ts and are the single source of truth for all school databases.

Schema Architecture

Central DB Schema (Key Tables)

Table	Purpose
School	School registration, databaseName, subdomain, settings
User	User accounts (phone, email, password hash)
EmployeeUserSchool	Links users to schools with role assignment
Role	Role definitions (SCHOOL_ADMIN, TEACHER, etc.)
Module	Module names (attendance, exam, fee, ...)
Role_Module_Permission	Per-role, per-module read/write/delete flags
Session	Active sessions
RefreshToken	JWT refresh token registry

School DB Schema (Core Tables)

Table	Purpose
Student	Student records linked to user account
StudentClass	Student-class mappings
Attendance	Daily attendance records
Exam	Exam definitions
Result	Student exam results
FeeStructure	Fee heads and amounts
FeeLedger	Double-entry fee transactions
Bus	Bus fleet
BusRoute	Route definitions
BusStop	Stop master
Trip	Trip instances
TripStop	Stop order within a trip
TripStudentMark	Boarding status per student per trip
Announcement	School announcements
LeaveApplication	Leave requests
Message	Messaging threads
LibraryBookIssue	Book issue/return records

Drizzle Schema Example

// packages/db/src/schema.ts
import {
  pgTable,
  text,
  uuid,
  timestamp,
  boolean,
  integer,
  index,
  uniqueIndex,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const School = pgTable('School', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  databaseName: text('databaseName').notNull().unique(),
  subdomain: text('subdomain').notNull().unique(),
  district: text('district'),
  board: text('board'),
  evaluationMode: boolean('evaluationMode').default(false),
  createdAt: timestamp('createdAt').defaultNow(),
});

export const Student = pgTable('Student', {
  id: uuid('id').primaryKey().defaultRandom(),
  schoolId: uuid('schoolId').notNull(),
  userId: uuid('userId'),  // linked user account (optional)
  admissionNo: text('admissionNo').notNull(),
  firstName: text('firstName', { length: 100 }).notNull(),
  lastName: text('lastName', { length: 100 }),
  classId: uuid('classId'),
  divisionId: uuid('divisionId'),
  // ...
}, (table) => ({
  schoolIdIdx: index('Student_schoolId_idx').on(table.schoolId),
  classIdx: index('Student_class_idx').on(table.classId),
}));

export const Attendance = pgTable('Attendance', {
  id: uuid('id').primaryKey().defaultRandom(),
  schoolId: uuid('schoolId').notNull(),
  studentId: uuid('studentId').notNull(),
  date: timestamp('date').notNull(),
  status: text('status', { length: 20 }).notNull(), // PRESENT, ABSENT, LATE, LEAVE
  markedBy: uuid('markedBy'),  // teacher userId
  // ...
}, (table) => ({
  schoolDateIdx: index('Attendance_school_date_idx').on(table.schoolId, table.date),
  studentIdx: index('Attendance_student_idx').on(table.studentId),
}));

// Relations
export const schoolRelations = relations(School, ({ many }) => ({
  users: many(EmployeeUserSchool),
}));

export const studentRelations = relations(Student, ({ one, many }) => ({
  school: one(School, { fields: [Student.schoolId], references: [School.id] }),
  attendance: many(Attendance),
  results: many(Result),
}));

Client Factory

// packages/db/src/client.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

let centralPool: Pool;

export function getCentralPool(): Pool {
  if (!centralPool) {
    centralPool = new Pool({
      connectionString: process.env.DATABASE_URL,
      max: 10,
    });
  }
  return centralPool;
}

export function getCentralDb() {
  return drizzle(getCentralPool(), { schema: centralSchema });
}

export async function getSchoolDb(schoolId: string) {
  const school = await getCentralDb().query.School.findFirst({
    where: eq(School.id, schoolId),
    columns: { databaseName: true },
  });

  if (!school) throw new UnauthorizedException();

  const schoolPool = new Pool({
    connectionString: `${process.env.DATABASE_URL.replace('/syncad_central', `/${school.databaseName}`)}`,
    max: 10,
  });

  return drizzle(schoolPool, { schema: schoolSchema });
}

Migrations

Drizzle Kit Config

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Running Migrations

# Push schema (development — creates/alters tables)
pnpm --filter @syncad/db drizzle-kit push

# Generate migration files (production)
pnpm --filter @syncad/db drizzle-kit generate

# Apply migrations (production)
pnpm --filter @syncad/db drizzle-kit migrate

Idempotent Migrations

All school DB migrations run via school-db-migrator which executes CREATE TABLE IF NOT EXISTS statements. This ensures:

1. First run: creates tables
2. Subsequent runs: no-op (safe to re-run)
3. New school provisioning: applies all migrations fresh

-- Migration file example: 001_initial_schema.sql
CREATE TABLE IF NOT EXISTS "Student" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "schoolId" uuid NOT NULL,
  "admissionNo" varchar(50) NOT NULL,
  "firstName" varchar(100) NOT NULL,
  "lastName" varchar(100),
  "classId" uuid,
  "divisionId" uuid,
  "createdAt" timestamptz DEFAULT now(),
  "updatedAt" timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "Student_schoolId_idx" ON "Student" ("schoolId");
CREATE UNIQUE INDEX IF NOT EXISTS "Student_admissionNo_schoolId_idx" ON "Student" ("admissionNo", "schoolId");

Indexing Strategy

Table	Key Indexes
Student	(schoolId), (classId), (admissionNo, schoolId)
Attendance	(schoolId, date), (studentId)
Result	(schoolId, examId), (studentId)
FeeLedger	(schoolId, studentId), (schoolId, date)
Trip	(schoolId, busId, date)
TripStudentMark	(tripId, studentId)

Double-Entry Bookkeeping (Fee Ledger)

Fee transactions use double-entry accounting:

export const FeeLedger = pgTable('FeeLedger', {
  id: uuid('id').primaryKey().defaultRandom(),
  schoolId: uuid('schoolId').notNull(),
  studentId: uuid('studentId').notNull(),
  academicYear: text('academicYear').notNull(),
  voucherNo: text('voucherNo').notNull(),
  date: timestamp('date').notNull(),
  // Double-entry: amount is positive for debit, negative for credit
  amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
  // Account: FEE_RECEIVABLE, CASH, BANK, FEE_DISCOUNT, etc.
  account: text('account').notNull(),
  // DR or CR
  entryType: text('entryType', { length: 2 }).notNull(), // DR | CR
  narration: text('narration'),
});

For each fee payment:

• DR entry: FEE_RECEIVABLE (reduces what student owes)
• CR entry: CASH or BANK (records money received)