Multi-Tenancy

SyncAD uses a database-per-school multi-tenancy model. Each school has its own isolated PostgreSQL database. The API routes requests to the correct database based on the authenticated user's school.

Database Layout

┌─────────────────────────────────────────────────────────────┐
│                   Central Database                          │
│  (syncad_central)                                         │
│                                                             │
│  School · User · EmployeeUserSchool ·                       │
│  Role · Module · Role_Module_Permission ·                   │
│  Session · RefreshToken                                     │
└─────────────────────────────────────────────────────────────┘
         │                                    │
         │ 1:1                              │ 1:1
         ▼                                    ▼
┌─────────────────┐                ┌─────────────────────────┐
│  School DB 1    │                │      School DB 2        │
│ (school_001)    │                │    (school_002)        │
│                 │                │                        │
│ Student         │                │ Student                 │
│ Attendance      │                │ Attendance              │
│ Exam            │                │ Exam                    │
│ FeeLedger       │                │ FeeLedger               │
│ ...50+ tables   │                │ ...50+ tables          │
└─────────────────┘                └─────────────────────────┘

Routing — getDbFromSchoolId()

Every API request carries a JWT. The schoolId claim in the JWT identifies which school's database to use.

// packages/db/src/helpers.ts

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

  if (!school) {
    throw new UnauthorizedException('School not found');
  }

  return createDbClient(school.databaseName);
}

export function createDbClient(databaseName: string) {
  return drizzle(pool, { schema, databaseSchema: databaseName });
}

Usage in a NestJS Controller

@Injectable()
export class StudentService {
  async getStudents(@CurrentUser() user: JwtPayload) {
    const db = await getDbFromSchoolId(user.schoolId);
    return db.query.Student.findMany({
      where: eq(Student.schoolId, user.schoolId),
    });
  }
}

JWT Payload

interface JwtPayload {
  sub: string;        // userId
  role: UserRole;      // PARENT | TEACHER | DRIVER | SCHOOL_ADMIN | SUPER_ADMIN
  schoolId: string;    // maps to databaseName in School table
  iat: number;
  exp: number;
}

School Onboarding (SCS Service)

When a new school is provisioned via the SCS Go service:

1. API: POST /school with { schoolName, district, board, ... }
2. SCS creates school_{uuid} database in PostgreSQL
3. SCS runs school-db-migrator with idempotent migrations
4. SCS creates Route53 DNS record: {slug}.syncad.in
5. SCS issues Let's Encrypt SSL for the subdomain
6. SCS writes { id, slug, databaseName, subdomain } to central School table

// apps/scs/internal/provision.go (simplified)
func ProvisionSchool(req CreateSchoolRequest) (*School, error) {
    dbName := fmt.Sprintf("school_%s", uuid.New().String())

    // 1. Create isolated DB
    _, err := pgxpool.Exec(ctx, fmt.Sprintf("CREATE DATABASE %s", dbName))
    if err != nil { return nil, err }

    // 2. Run migrations
    err = migrator.Run(dbName, migrations)
    if err != nil { return nil, err }

    // 3. Create DNS record
    err = route53.CreateSubdomain(slug, dbName)
    if err != nil { return nil, err }

    // 4. Insert into central DB
    school := &School{
        ID:           uuid.New().String(),
        Name:         req.Name,
        DatabaseName: dbName,
        Subdomain:    fmt.Sprintf("%s.syncad.in", slug),
    }
    return centralDb.CreateSchool(school), nil
}

Idempotent Migrations

All migrations use CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS to ensure they can be run multiple times safely — critical when provisioning new schools.

-- Example migration
CREATE TABLE IF NOT EXISTS "Student" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "schoolId" uuid NOT NULL,
  "userId" uuid,
  "firstName" varchar(100) NOT NULL,
  ...
);

CREATE INDEX IF NOT EXISTS "Student_schoolId_idx" ON "Student" ("schoolId");

Migration tool: pnpm drizzle-kit push via school-db-migrator app.

Schema Sharing

The schema definition in packages/db/src/schema.ts defines the canonical structure. Every school DB is created from the same schema — no per-school schema customization.

// packages/db/src/schema.ts
export const Student = sqliteTable('Student', {
  id: text('id').primaryKey(),
  schoolId: text('schoolId').notNull(),
  firstName: text('firstName', { length: 100 }).notNull(),
  // ...
}, (table) => ({
  schoolIdIdx: index('Student_schoolId_idx').on(table.schoolId),
}));

Security Considerations

Concern	Mitigation
Cross-school data access	schoolId enforced in every query WHERE clause
JWT forgery	Signed with HS256 / RS256, verified at gateway
School enumeration	schoolId is UUID v4, not sequential
DB credential exposure	Each school DB has scoped credentials (via RDS IAM)
Migration injection	Migrations are versioned files, not dynamic SQL