Data Access Architecture

Purpose

This document defines how Chilarai accesses PostgreSQL data. It complements the logical data model and does not duplicate the complete TypeScript database schema.

The TypeScript Drizzle schema is the source of truth for tables, columns, constraints, indexes, and relations. This document records the conventions and boundaries that are not fully expressed by a schema file.

Technology

• PostgreSQL is the primary database.
• Drizzle ORM provides typed schema definitions and query construction.
• pg provides PostgreSQL connectivity and connection pooling.
• Drizzle Kit generates and applies versioned migrations.

The application does not require Prisma, Prisma Accelerate, or another hosted ORM service.

Schema Organization

Database schemas are stored centrally and organized by business module:

api/src/database/
  client.ts
  schema/
    identity/
    school/
    student/
    academic/
    admission/
    attendance/
    staff/
    fees/
    assessment/
  migrations/

The central schema entry point exposes module schemas to Drizzle Kit. Each module remains the owner of its tables and persistence rules.

Database schemas are not domain entities. Domain and application layers must not depend directly on Drizzle.

Module Ownership

Database schema folders follow the established backend modules:

Identity Module  -> User, SchoolMembership
School Module    -> School, AcademicYear
Admission Module -> Admission
Student Module   -> Student, StudentEnrollment
Academic Module  -> Class, Section, Subject, ClassSubject,
                    SectionTeacherAssignment, SubjectTeacherAssignment
Staff Module     -> Staff
Attendance Module -> Attendance, AttendanceEvent
Fees Module      -> FeeStructure, FeeComponent, StudentFee, FeeAdjustment,
                    FeeCharge, Payment
Assessment Module -> Assessment, AssessmentComponent, AssessmentResult

Each module owns the definitions and persistence rules for its entities. A module may reference another module's identifiers through an approved contract, but must not modify another module's tables directly.

Layering

Domain
  -> Application services
  -> Repository contracts
  -> Drizzle repositories
  -> Drizzle schema
  -> pg connection pool
  -> PostgreSQL

Repositories translate between database records and domain objects. Controllers must not contain database queries.

Naming Conventions

PostgreSQL uses plural snake_case names:

student_enrollments
  id
  student_id
  academic_year_id
  created_at
  updated_at

TypeScript properties may use camelCase, with explicit Drizzle mappings:

studentEnrollment.studentId
studentEnrollment.createdAt

All identifiers use UUID. All timestamps use PostgreSQL timestamptz.

Tenant Scoping

Tenant isolation is enforced at the School boundary.

• Tenant and School have a 1:1 relationship.
• User is global and connects to Schools through School Membership.
• School-owned tables include a required school_id.
• Repositories require the active School context for school-owned operations.
• Normal queries must include the applicable School scope.
• Backend authorization and repository behavior enforce isolation; frontend checks are not security controls.

Audit and Archival

Business records use the following audit fields:

created_at
created_by
updated_at
updated_by
archived_at
archived_by
archive_reason

Creation and update audit fields are required. System actions identify the system actor explicitly.

archived_at is the archival state. A separate is_archived flag is not used. Normal queries exclude records where archived_at is not null.

archive_reason is nullable for active records but required when a record is archived. Archived records are retained rather than physically deleted.

Transactions

Application services own transaction boundaries.

A business workflow that changes multiple records uses one transaction:

Application service
  -> Begin transaction
  -> Repository operation
  -> Repository operation
  -> Commit or rollback

Repositories use the transaction context supplied by the application service. They must not open independent transactions for steps in the same workflow.

Migrations

Schema changes follow this workflow:

Update TypeScript schema
  -> Generate migration with Drizzle Kit
  -> Review generated SQL
  -> Commit schema and migration together
  -> Validate in CI
  -> Apply during deployment

Versioned migrations are required for shared environments and production. Direct schema push is limited to local experimentation. Applied migrations are immutable. Risky changes, including data transformations and column renames, require explicit manual SQL and review.

Queries and Reporting

Drizzle query builders are the default for operational queries. Parameterized SQL is allowed for complex reporting, analytics, and PostgreSQL-specific features.

Reporting queries belong in repository implementations. Reporting code may read data owned by other modules through approved repositories, views, or read models, but it must not modify another module's data. A dedicated Reporting module may be introduced later if reporting becomes a substantial capability.

Performance work should begin with indexes, query design, and EXPLAIN ANALYZE. Views, materialized views, summary tables, read replicas, and separate analytics stores are introduced only when demonstrated workload requires them.

Connection Management

The API uses one shared pg connection pool per application process.

Pool size, connection timeout, and idle timeout are configured through environment variables. A new pool is not created per request or per repository. The application closes the pool during graceful shutdown.

External poolers or managed pooling services may be introduced later if the deployment model or scale requires them.