Skip to content

ADR-002: Backend API & Database

Status

Accepted

Context

The backend must serve as the synchronisation layer between the Flutter mobile app and the web admin console. Key requirements:

  • Handle health records, video completion metrics, and curriculum data for potentially 50,000+ students across multiple districts.
  • Support offline-first sync — mobile clients buffer data locally and sync in batches when connectivity resumes.
  • Enforce row-level security — coordinators must not access data from other schools; admins must not see data beyond their district.
  • Comply with DPDP — all PII must be stored in an Indian cloud region.

Options Considered

Option Description Trade-offs
REST API + PostgreSQL HTTP API layer with relational DB Proven, auditable, strong RBAC, relational integrity for student-school-screening hierarchy
GraphQL + PostgreSQL Flexible query layer Over-fetching prevention, but higher complexity for a small team and mobile client
Firebase (NoSQL + Realtime) Fully managed Google BaaS Fast initial build, but limited RBAC for complex school-coordinator-admin hierarchies; data residency constraints in India
Supabase (REST + PostgreSQL) Open-source Firebase alternative Rapid setup, built-in auth, row-level security — viable alternative worth evaluating in Discovery

Decision

We will implement a RESTful API with a PostgreSQL database, deployed on a managed cloud platform (AWS, GCP, Azure — provider selected during Discovery based on HarvestPlus's cloud agreements and Indian region availability).

Row-level security policies in PostgreSQL will enforce school-level data isolation directly at the database layer — not just the application layer.

Rationale

  • PostgreSQL's row-level security (RLS) natively enforces the coordinator-school data boundary required by DPDP.
  • REST is simpler to maintain for a small team than GraphQL and easier to consume from Flutter's http/dio clients.
  • Relational integrity is essential for tracking the student → screening → recommendation → module-completion chain.
  • Supabase remains on the shortlist for Discovery evaluation as it packages PostgreSQL + RLS + REST + Auth into a single managed service.

Consequences

Positive

  • Relational model handles the student/school/district hierarchy cleanly.
  • PostgreSQL RLS enforces data isolation without application-level workarounds.
  • Provider-agnostic: same schema and API work on AWS RDS, GCP Cloud SQL, or Supabase.
  • JWT-based auth integrates cleanly with any OAuth2 IdP (see ADR-005).

Negative

  • REST requires more explicit endpoint design than GraphQL (no self-documenting schema).
  • Connection pooling must be managed carefully in serverless deployments (PgBouncer or built-in pooling required).
  • Managed PostgreSQL on any cloud provider incurs ongoing running costs (outside Iteration 1 fixed fee).