Skip to main content

ADR-001: RDS PostgreSQL over a Data-Lake Model

Status: Accepted | Date: 2026-03-13

Context​

The monorepo operates a Medallion-architecture data lake on S3 for analytics. When starting SA3, a decision was required on whether to extend the data lake or introduce a dedicated transactional database. Key constraints:

  • School management data is highly relational (students, classes, subjects, assessments, scores, reports) with mandatory FK integrity.
  • ACID transactions are required for score submission, role revocation, and report finalization.
  • Online transactional workloads dominate -- not batch analytical scans.
  • Bounded record volume: up to ~2,000 students and ~150 staff.

Decision​

SA3 uses RDS PostgreSQL 15 in a private VPC subnet (eu-west-3) as its sole operational database. Prisma 5 is the ORM layer with a dedicated schema at projects/sa3/prisma/schema.prisma.

Rationale​

  1. Referential integrity -- 28 entities with mandatory FK relationships. Object storage cannot enforce these.
  2. ACID transactions -- atomic multi-table commits for score submission with audit logs, role revocation, report finalization.
  3. Point-query patterns -- O(1) row lookups by index for teacher score entry and admin lookups.
  4. Prisma 5 is the workspace ORM standard, minimising new tooling.
  5. Cost proportional -- db.t3.small at ~$0.034/hr is right-sized for a single school.
  6. Clean separation -- analytical reporting can be added later via EventBridge export to S3.

Consequences​

Positive: Full relational integrity, ACID transactions, sub-millisecond point lookups, Prisma migrations for versioned schema evolution, RDS automated snapshots.

Negative: Single RDS instance is a single point of failure (mitigated by daily snapshots, Multi-AZ planned). VPC connector adds ~$0.125/hr. Schema migrations require controlled deployment coordination.

Alternatives Rejected​

  • S3 + Glue + Athena -- no FK enforcement, no ACID, seconds-level query latency
  • DynamoDB -- highly relational schema requires extensive data duplication; no Prisma support
  • PlanetScale / Neon -- violates AWS-only constraint; data residency cannot be guaranteed