ADR-0001: Supabase for Backend Database and API¶

Status: Accepted Date: 2025-01-15 (Estimated - actual decision date) Deciders: GetCimple Team (Alice, Bob, Charlie) Tags: architecture, database, backend, api

Context¶

GetCimple requires a backend database and API layer for our cybersecurity compliance SaaS. As a 3-person startup building an MVP, we need:

Requirements: - PostgreSQL database (proven, reliable, supports complex queries) - Multi-tenant architecture with Row Level Security (RLS) - Auto-generated REST/GraphQL APIs to accelerate development - Australian data residency for compliance - Real-time subscriptions for collaborative features - Authentication integration - Generous free tier for MVP development

Constraints: - Limited backend development capacity (3-person team) - Must launch MVP quickly (avoid custom API development) - Australian data sovereignty required (ACSC Essential 8, Privacy Act 1988) - Budget constraints (startup-friendly pricing) - Must support ACSC Essential 8 compliance features

Options Considered¶

Option A: Supabase (PostgreSQL BaaS)¶

Description: Backend-as-a-Service built on PostgreSQL with auto-generated APIs, authentication, real-time subscriptions, and storage.

Pros: - ✅ PostgreSQL with Row Level Security (perfect for multi-tenancy) - ✅ Auto-generated REST and GraphQL APIs (saves weeks of development) - ✅ Sydney region available (ap-southeast-2) for Australian data residency - ✅ Built-in authentication (can integrate with Kinde) - ✅ Real-time subscriptions via WebSocket - ✅ Generous free tier: 500MB database, 2GB bandwidth, 50,000 monthly active users - ✅ TypeScript client SDK with excellent DX - ✅ Open source (can self-host if needed later) - ✅ Storage for evidence files - ✅ Excellent documentation and community

Cons: - ❌ Vendor lock-in (though PostgreSQL export is possible) - ❌ Less control than self-hosted database - ❌ Pricing scales with usage (could get expensive at scale) - ❌ RLS policies can be complex for advanced use cases

Estimated Effort: 1-2 days setup and configuration

Option B: Self-Hosted PostgreSQL + Custom API¶

Description: Deploy PostgreSQL on AWS RDS (Sydney) or DigitalOcean (Australia) and build custom REST API with Node.js/Express.

Pros: - ✅ Full control over database and API - ✅ No vendor lock-in - ✅ Can optimize exactly for our needs - ✅ Potentially cheaper at large scale

Cons: - ❌ 2-4 weeks to build REST API endpoints - ❌ Ongoing maintenance burden (backups, updates, security patches) - ❌ Need to build authentication integration - ❌ Need to implement RLS or equivalent multi-tenancy - ❌ DevOps overhead for 3-person team - ❌ No real-time subscriptions without significant work (WebSocket server) - ❌ Slower time-to-market

Estimated Effort: 3-4 weeks initial development + ongoing maintenance

Option C: Firebase/Firestore¶

Description: Google's BaaS with NoSQL database and real-time features.

Pros: - ✅ Real-time by default - ✅ Auto-scaling - ✅ Google Cloud Platform (Sydney region available) - ✅ Generous free tier

Cons: - ❌ NoSQL (poor fit for relational compliance data) - ❌ Complex queries difficult (no SQL) - ❌ Multi-tenant security rules complex - ❌ Different data modeling paradigm (learning curve) - ❌ Vendor lock-in stronger than Supabase (no standard export)

Estimated Effort: 2-3 weeks (learning curve for NoSQL modeling)

Decision¶

We chose: Option A - Supabase

Rationale: 1. Fastest time-to-market: Auto-generated APIs save 2-4 weeks of backend development, critical for MVP launch 2. Australian data residency: Sydney region (ap-southeast-2) satisfies ACSC Essential 8 and Privacy Act requirements 3. PostgreSQL perfect fit: Relational model suits compliance data (policies, assessments, evidence, audit trails) 4. Multi-tenancy built-in: Row Level Security provides secure tenant isolation without custom code 5. Real-time capabilities: Built-in subscriptions support collaborative features (e.g., shared assessment dashboards) 6. Startup-friendly: Free tier supports MVP development and early customer onboarding 7. Reduced DevOps burden: Managed service lets 3-person team focus on product, not infrastructure

Key Trade-offs Accepted: - We're accepting potential vendor lock-in because PostgreSQL export provides reasonable exit strategy - We're deferring cost optimization to post-MVP when we have revenue and usage data - We're trusting Supabase for database reliability (they have 99.9% SLA)

Consequences¶

Positive¶

✅ Development velocity: Estimated 3-4 weeks saved on backend API development
✅ Australian compliance: Data residency requirement immediately satisfied
✅ Security by default: RLS enforces tenant isolation at database level
✅ TypeScript integration: Strong typing from database schema to frontend
✅ Scalability: Handles MVP → 1000s of users without architecture changes
✅ Real-time features: Enables collaborative compliance assessments
✅ Focus on product: Team can build features, not infrastructure

Negative¶

⚠️ Vendor dependency: Changing database providers later requires migration effort
⚠️ Cost scaling: Need to monitor usage and optimize queries to control costs
⚠️ RLS complexity: Advanced multi-tenant scenarios may require complex policies
⚠️ Limited control: Cannot fine-tune PostgreSQL configuration or extensions

Risks¶

Risk	Likelihood	Impact	Mitigation
Supabase pricing becomes prohibitive at scale	MEDIUM	HIGH	Monitor usage metrics monthly; design efficient queries; have PostgreSQL export plan ready
RLS policies become unmaintainable	LOW	MEDIUM	Keep policies simple; document thoroughly; use Supabase policy templates
Supabase outage affects production	LOW	HIGH	Monitor Supabase status page; implement graceful degradation; maintain recent database backups
Sydney region becomes unavailable	LOW	HIGH	Supabase has multi-region failover; maintain backup strategy; acceptable downtime for MVP

Compliance Note¶

ACSC Essential 8 Impact: - Relevant Controls: Not directly applicable (Supabase is infrastructure choice) - Data Residency: ✅ Satisfies Australian data sovereignty via Sydney region (ap-southeast-2) - Security: Supabase provides encryption at rest (AES-256) and in transit (TLS)

Australian Data Residency: - Data Storage: Supabase Sydney region (ap-southeast-2) - Data Processing: PostgreSQL processing occurs in Sydney datacenter - Guarantees: Supabase contracts specify data residency, SOC 2 Type II certified - Compliance: Meets Privacy Act 1988 requirements for Australian data storage

Audit Trail: - Supabase audit logs track all database schema changes - Application-level audit trails implemented via triggers/functions

Implementation Notes¶

Prerequisites: - Supabase account created - Project configured with Sydney region - Environment variables set (SUPABASE_URL, SUPABASE_ANON_KEY)

Integration Points: - Kinde Auth: JWT tokens validated via Supabase functions - Cloudflare Workers: API calls via Supabase client library - React Frontend: Direct connection via @supabase/supabase-js - File Storage: Using Supabase Storage for evidence files (alternative: Cloudflare R2)

Monitoring: - Track query performance via Supabase dashboard - Monitor API request counts and latency - Set alerts for error rate > 1% or latency > 500ms

Documentation Updates Needed: - ✅ Database schema documented in database-design-mvp.md - ✅ API design in api-design-mvp.md - ✅ Deployment configuration in deployment-mvp.md

Revisit¶

Revisit By: 2026-01-01 or after 500 active users (whichever comes first) Blast Radius: HIGH - Affects all backend data storage, API layer, authentication integration

Conditions for Revisit: - Monthly database costs exceed $500/month consistently - RLS policies become unmaintainable (>20 policies, frequent bugs) - Performance issues that cannot be resolved with query optimization - Supabase announces end-of-life or significant pricing changes - Regulatory requirements change (e.g., new data residency rules)

Next Review: 2025-07-01 (6-month check-in on usage and costs)

References¶

Version History¶

Version	Date	Author	Changes
1.0	2025-10-20	Claude (AI Assistant)	Initial ADR capturing historical decision

ADR Template Version: 1.0 Created: 2025-10-20 Decision Date: ~2025-01-15 (estimated from documentation dates)