Skip to main content

ADR-004: Use PostgreSQL

Status​

Accepted

Date​

2026-07-20

Context​

Chilarai requires a primary data store capable of supporting:

  • Multi-tenant architecture
  • School administration
  • Student records
  • Admissions workflows
  • Attendance management
  • Fee management
  • Academic structures
  • Reporting and analytics
  • Future integrations

The data model is expected to contain highly relational business entities with strong consistency requirements.

The database platform must support long-term growth while remaining affordable and operationally simple during the startup phase.

Decision Drivers​

The selected database should:

  • Support transactional consistency
  • Handle highly relational data models
  • Be open source
  • Have strong community adoption
  • Support cloud-native deployments
  • Scale from small to large workloads
  • Integrate well with NestJS and TypeScript tooling
  • Support reporting and analytical workloads
  • Minimize operational complexity
  • Avoid vendor lock-in

Alternatives Considered​

Option 1: MySQL​

Advantages​

  • Mature ecosystem
  • Broad hosting support
  • Strong community adoption
  • Familiar to many developers

Disadvantages​

  • Less sophisticated feature set compared to PostgreSQL
  • Weaker support for advanced data types
  • Less flexibility for future analytical requirements

Assessment​

A viable option but provides fewer capabilities than PostgreSQL without offering significant advantages for the project.

Option 2: Microsoft SQL Server​

Advantages​

  • Enterprise-grade capabilities
  • Strong tooling
  • Excellent reporting ecosystem
  • Mature operational model

Disadvantages​

  • Licensing costs
  • Increased operational overhead
  • Greater dependency on Microsoft-specific infrastructure

Assessment​

An excellent enterprise platform, but unnecessary for the current stage of the product.

Option 3: MongoDB​

Advantages​

  • Flexible document model
  • Rapid schema evolution
  • Good developer experience

Disadvantages​

  • Less suitable for highly relational data
  • Increased complexity when implementing relational business rules
  • More difficult reporting and transactional workflows

Assessment​

The Chilarai domain is fundamentally relational and therefore does not naturally align with a document-oriented database.

Option 4: PostgreSQL​

Advantages​

  • Open source
  • Strong ACID compliance
  • Excellent relational capabilities
  • Mature ecosystem
  • Cloud provider support
  • JSON support when needed
  • Strong reporting capabilities
  • Proven scalability

Disadvantages​

  • Requires schema design discipline
  • Slightly steeper learning curve than some alternatives
  • Database tuning becomes important as scale increases

Assessment​

Provides the strongest overall combination of capabilities, flexibility, maturity, and cost effectiveness.

Decision​

Use PostgreSQL as the primary database platform for Chilarai.

All business-critical transactional data will be stored in PostgreSQL.

Rationale​

The Chilarai domain is highly relational.

Examples include:

  • School -> Academic Year
  • School -> Classes
  • Student -> Admissions
  • Student -> Attendance
  • Student -> Fees
  • User -> Roles
  • Tenant -> Schools

These relationships benefit from:

  • Referential integrity
  • Transactions
  • Foreign keys
  • Strong consistency guarantees

PostgreSQL provides these capabilities while remaining open source and cloud portable.

The platform also supports JSON data when flexibility is required, allowing relational and semi-structured data to coexist within the same system.

Compared to SQL Server, PostgreSQL provides lower cost and reduced vendor dependency.

Compared to MongoDB, PostgreSQL provides a more natural fit for the business domain.

Consequences​

Positive Consequences​

  • Strong data integrity
  • Excellent support for relational models
  • Lower infrastructure costs
  • Cloud portability
  • Mature tooling ecosystem
  • Large talent pool
  • Long-term scalability

Negative Consequences​

  • Requires careful schema design
  • Future migrations must be managed carefully
  • Query optimization becomes important as data volume increases

Follow-up Actions​

  • Define tenant isolation strategy
  • Establish migration strategy
  • Evaluate ORM options
  • Define backup and recovery procedures
  • Create initial domain schema