Database Decision

Decision date: 2026-03-15 Status: Approved — Aurora PostgreSQL Serverless v2

What is Aurora PostgreSQL?

Aurora is AWS's managed version of PostgreSQL.

Normally if you want PostgreSQL you have two options:

Self-hosted — you rent a server, install PostgreSQL yourself, handle backups, updates, crashes, scaling. A lot of ops work.
RDS PostgreSQL — AWS manages the server for you, but it's still a fixed-size machine you pay for 24/7.

Aurora is AWS's premium managed option. They took PostgreSQL, rewrote the storage layer to be more reliable and faster, and added automatic backups, auto-scaling, automatic failover, and zero maintenance overhead.

From a developer perspective — it's just PostgreSQL. Same SQL, same drivers, same ORM. You'd never know it's Aurora unless you looked at the AWS console.

	Who manages it	Cost	Reliability
Self-hosted PostgreSQL	You	Cheapest	You're responsible
RDS PostgreSQL	AWS	Medium	Good
Aurora PostgreSQL	AWS	Slightly more	Best

Serverless v2 means the cluster auto-scales based on actual load — measured in ACUs (Aurora Capacity Units). It scales down to near-zero when idle, so you pay essentially nothing during off-hours or in dev/staging environments. This is called scale-to-zero.

Context

Starlight needs a primary database to back all core domains: patients, appointments, medical records, billing, tasks, CRM, and accounting. The stack is TypeScript + AWS (SST/Lambda + API Gateway), mirroring the VibesWire baseline architecture.

The database choice must support:

Complex relational data (patients → appointments → billing → records)
Rich cross-domain queries ("all overdue invoices for active patients this month")
HIPAA-eligible infrastructure (real PHI will be stored)
Low cost at single-practice launch scale (Dr. Prajapati's practice, 17 patients)
Multi-tenant SaaS growth path (multiple practices in the future)

Options Evaluated

Option 1: DynamoDB

AWS-native NoSQL key-value store. Already used in VibesWire.


HIPAA	✅ Supported via AWS BAA
Cost at launch	✅ ~$0/month (permanent free tier)
Scale to zero	✅ Yes
Query flexibility	❌ Poor — access patterns must be defined upfront
Relational data	❌ No joins
Schema enforcement	❌ None

Why it doesn't fit Starlight: DynamoDB excels at simple, high-volume, predictable access patterns (e.g., VibesWire's news cache). Starlight's domain is deeply relational — a patient has appointments, appointments have notes and billing codes, billing links to insurance. DynamoDB requires pre-defining every query at table-design time, which is extremely difficult for a product that is still evolving. Getting it wrong means expensive table redesigns later.

Additionally: no joins means you either duplicate data everywhere (gets out of sync) or make multiple round-trips to the DB per request.

Option 2: DocumentDB

AWS-managed document database with MongoDB-compatible API.


HIPAA	✅ Supported via AWS BAA
Cost at launch	❌ ~$200+/month minimum (no scale-to-zero)
Scale to zero	❌ No
Query flexibility	⚠️ Medium
Relational data	⚠️ Partial — `$lookup` aggregations, not true joins
Schema enforcement	❌ None

Why it doesn't fit Starlight:

No scale-to-zero — you pay $200+/month regardless of actual usage. Expensive while the practice has only 17 patients.
Incomplete MongoDB compatibility — DocumentDB markets itself as "MongoDB-compatible" but many features don't work or behave differently. Developers hit subtle bugs and waste time debugging AWS-specific quirks.
Document model wrong for our data — medical records, patients, appointments, billing are all related entities. No true joins means duplicating data everywhere or using slow $lookup aggregations.
No schema enforcement — flexible schema sounds good early on but is dangerous for medical data. Nothing stops saving a patient record without a required field, or storing a date as a string in one record and a number in another.
Hard to query ad-hoc — as Deepak and Dr. Prajapati discover they need new reports ("all kids due for vaccines in the next 30 days who haven't been billed"), SQL makes these trivial. DocumentDB aggregation pipelines for the same query are complex and hard to maintain.

Option 3: MongoDB Atlas

Fully managed MongoDB hosted on AWS (not AWS-native).


HIPAA	✅ Supported on M10+ clusters via BAA
Cost at launch	❌ ~$57-200/month minimum
Scale to zero	❌ No
Query flexibility	⚠️ Good, but still a document store
Relational data	⚠️ Partial — same limitations as DocumentDB
Schema enforcement	❌ None

Why it doesn't fit Starlight: External vendor alongside AWS — adds operational complexity and a separate billing relationship. More expensive than Aurora Serverless at low volume. And despite being more capable than DocumentDB, it's still a document store: the Starlight data model is fundamentally relational, and document DBs require denormalization hacks that accumulate technical debt as the product grows.

Option 4: Aurora PostgreSQL Serverless v2 ✅ Chosen


HIPAA	✅ Supported via AWS BAA
Cost at launch	✅ ~$0-5/month dev, ~$20-30/month prod
Scale to zero	✅ Yes
Query flexibility	✅ Full PostgreSQL — joins, CTEs, window functions, full-text search
Relational data	✅ Native — foreign keys, cascades, constraints enforced at DB level
Schema enforcement	✅ Strong — migrations enforce structure

Decision Summary Table

	DynamoDB	DocumentDB	MongoDB Atlas	Aurora PostgreSQL
Relational queries	❌	⚠️	⚠️	✅
Scale to zero	✅	❌	❌	✅
Cost at launch	✅ Free	❌ $200+/mo	❌ $57+/mo	✅ ~$30-50/mo
HIPAA eligible	✅	✅	✅	✅
AWS-native	✅	✅	❌	✅
Schema enforcement	❌	❌	❌	✅

Architecture

Connection: RDS Data API

Lambda functions connect to Aurora via the RDS Data API — an HTTPS endpoint Aurora exposes. This avoids the need for a VPC + NAT Gateway setup.

Why this matters: Normally, Lambda needs to be inside the same VPC (Virtual Private Cloud — a private network on AWS) as Aurora to connect to it. But Lambdas inside a VPC lose internet access by default, requiring a NAT Gateway (~$30-40/month extra) to call external APIs.

With RDS Data API, Lambda talks to Aurora over HTTPS from outside the VPC — no VPC config, no NAT Gateway, full internet access preserved.

Lambda (outside VPC) → RDS Data API (HTTPS) → Aurora PostgreSQL

Trade-off: RDS Data API has slightly higher latency per query than a direct connection (HTTP overhead vs persistent TCP). Fine at our current scale. If performance becomes a concern as Starlight grows, migrating to a direct VPC connection is straightforward — especially if the DB layer is properly abstracted (see below).

ORM: Drizzle

Drizzle ORM — TypeScript-native, generates zero-overhead SQL, no runtime binary (unlike Prisma which ships a query engine). Schema defined in TypeScript, migrations generated by Drizzle Kit and checked into git.

Migrations run as a one-off SST deploy hook — never inside Lambda handlers.

SST Config

const db = new sst.aws.Aurora("StarlightDB", {
  engine: "postgresql",
  serverless: true,
  dataApi: true,  // enables RDS Data API — no VPC needed
})

Cost Estimate

Environment	Config	Estimated Monthly Cost
Dev	Aurora Serverless v2, scales to 0	~$0–5
Staging	Aurora Serverless v2, light usage	~$10–20
Production	Aurora Serverless v2 + Data API	~$20–30
Total		~$30–55/month

No NAT Gateway needed = $30-40/month saved vs VPC approach.

Senior Engineering Note

Aurora PostgreSQL Serverless v2 + RDS Data API is a solid, production-grade choice for this stage. But one thing to be aware of:

RDS Data API has a known performance limitation — each query goes through an HTTP request/response cycle instead of a persistent TCP connection. For a dashboard making 10-15 queries per page load, this is noticeable at scale.

This is fine for:

Early stage with light traffic ✅
Dr. Prajapati's practice at current scale ✅
Dev and staging environments ✅

As Starlight grows, we may eventually migrate to a direct VPC connection. To make this painless, all DB access should go through a service/repository layer — never directly in route handlers:

// All DB access goes through this — easy to swap connection method later
import { db } from '@/lib/db'

// Route handler uses the service layer, not db directly
import { getPatientsForPractice } from '@/services/patients'

This way, changing from Data API to direct connection is a config change, not a rewrite.

HIPAA Notes

Before storing any real PHI:

Sign AWS Business Associate Agreement (BAA) — free, done via AWS console under Account Settings
Enable Aurora encryption at rest (default on new clusters)
Enable CloudTrail for audit logging
Restrict security groups — Aurora should only accept connections via Data API, never exposed to public internet

Schema Design

All tables include practice_id (multi-tenancy from day one), created_at, and updated_at.

Patients

patients (
  id               uuid PRIMARY KEY,
  practice_id      uuid NOT NULL,
  first_name       text NOT NULL,
  last_name        text NOT NULL,
  date_of_birth    date NOT NULL,
  gender           text,
  parent_guardian  text,
  email            text,
  phone            text,
  address          jsonb,
  insurance_provider    text,
  insurance_id          text,
  insurance_group       text,
  family_id        uuid,
  status           text NOT NULL,   -- active | prospect
  pipeline_stage   text,            -- inquiry | meet-and-greet | scheduled-visit | ready-to-enroll
  fee_override     numeric,
  referred_by      text,
  notes            text,
  is_active        boolean DEFAULT true
)

Subscriptions & Billing Plans

subscription_plans (
  id           uuid PRIMARY KEY,
  practice_id  uuid NOT NULL,
  name         text NOT NULL,    -- Orion, Lyra, Sirius, Pegasus, Polaris
  age_min      integer,
  age_max      integer,
  monthly_fee  numeric NOT NULL
)

patient_subscriptions (
  id           uuid PRIMARY KEY,
  practice_id  uuid NOT NULL,
  patient_id   uuid REFERENCES patients,
  plan_id      uuid REFERENCES subscription_plans,
  started_at   date NOT NULL,
  ended_at     date,
  status       text NOT NULL     -- active | paused | cancelled
)

Appointments & Calendar

providers (
  id           uuid PRIMARY KEY,
  practice_id  uuid NOT NULL,
  name         text NOT NULL,
  specialty    text,
  email        text,
  color        text
)

appointments (
  id               uuid PRIMARY KEY,
  practice_id      uuid NOT NULL,
  patient_id       uuid REFERENCES patients,
  provider_id      uuid REFERENCES providers,
  start_time       timestamptz NOT NULL,
  end_time         timestamptz NOT NULL,
  status           text NOT NULL,    -- scheduled | confirmed | completed | cancelled | no_show
  appointment_type text NOT NULL,    -- checkup | followup | sick_visit | vaccination | wellness
  chief_complaint  text,
  notes            text
)

Medical Records

medical_records (
  id               uuid PRIMARY KEY,
  practice_id      uuid NOT NULL,
  patient_id       uuid REFERENCES patients,
  provider_id      uuid REFERENCES providers,
  appointment_id   uuid REFERENCES appointments,
  record_type      text NOT NULL,    -- soap_note | lab_result | prescription | vaccination
  content          jsonb NOT NULL,   -- flexible per record_type
  recorded_at      timestamptz NOT NULL
)

diagnoses (
  id                uuid PRIMARY KEY,
  practice_id       uuid NOT NULL,
  patient_id        uuid REFERENCES patients,
  medical_record_id uuid REFERENCES medical_records,
  icd10_code        text NOT NULL,
  description       text NOT NULL,
  status            text NOT NULL,   -- active | resolved | chronic
  diagnosed_at      date NOT NULL
)

Tasks & CRM

tasks (
  id           uuid PRIMARY KEY,
  practice_id  uuid NOT NULL,
  patient_id   uuid REFERENCES patients,
  assigned_to  uuid REFERENCES users,
  title        text NOT NULL,
  description  text,
  due_date     date,
  status       text NOT NULL,    -- open | in_progress | done
  priority     text NOT NULL     -- low | medium | high
)

contacts (
  id           uuid PRIMARY KEY,
  practice_id  uuid NOT NULL,
  type         text NOT NULL,    -- referral_doctor | insurance | vendor | other
  name         text NOT NULL,
  email        text,
  phone        text,
  notes        text
)

Invoices & Payments

invoices (
  id               uuid PRIMARY KEY,
  practice_id      uuid NOT NULL,
  patient_id       uuid REFERENCES patients,
  appointment_id   uuid REFERENCES appointments,
  status           text NOT NULL,    -- draft | sent | paid | overdue | void
  due_date         date,
  paid_at          timestamptz,
  total_amount     numeric NOT NULL
)

invoice_line_items (
  id           uuid PRIMARY KEY,
  invoice_id   uuid REFERENCES invoices,
  cpt_code     text,
  description  text NOT NULL,
  quantity     integer NOT NULL DEFAULT 1,
  unit_price   numeric NOT NULL,
  amount       numeric NOT NULL
)

payments (
  id               uuid PRIMARY KEY,
  practice_id      uuid NOT NULL,
  invoice_id       uuid REFERENCES invoices,
  patient_id       uuid REFERENCES patients,
  amount           numeric NOT NULL,
  payment_method   text,
  paid_at          timestamptz NOT NULL,
  reference_number text
)

Users & Auth

users (
  id               uuid PRIMARY KEY,
  practice_id      uuid NOT NULL,
  email            text NOT NULL UNIQUE,
  role             text NOT NULL,    -- admin | provider | staff
  external_auth_id text,             -- Cognito/Auth0 sub claim
  is_active        boolean DEFAULT true
)

Key Indexes

CREATE INDEX idx_appointments_practice_start ON appointments(practice_id, start_time);
CREATE INDEX idx_appointments_patient ON appointments(patient_id);
CREATE INDEX idx_patients_name ON patients(practice_id, last_name, first_name);
CREATE INDEX idx_records_patient ON medical_records(patient_id, recorded_at DESC);
CREATE INDEX idx_invoices_status ON invoices(practice_id, status, due_date);
CREATE INDEX idx_tasks_assigned ON tasks(practice_id, assigned_to, status);

What is Aurora PostgreSQL?​

Context​

Options Evaluated​

Option 1: DynamoDB​

Option 2: DocumentDB​

Option 3: MongoDB Atlas​

Option 4: Aurora PostgreSQL Serverless v2 ✅ Chosen​

Decision Summary Table​

Architecture​

Connection: RDS Data API​

ORM: Drizzle​

SST Config​

Cost Estimate​

Senior Engineering Note​

HIPAA Notes​

Schema Design​

Patients​

Subscriptions & Billing Plans​

Appointments & Calendar​

Medical Records​

Tasks & CRM​

Invoices & Payments​

Users & Auth​

Key Indexes​