Der Architecture Decision Record Generator

ADR-007: Use PostgreSQL as the Primary Data Store for Helios SaaS Platform

Date: 2026-06-03
Status: Accepted

Context

Helios is a B2B SaaS platform for project portfolio management, currently serving 52,000 daily active users across 1,400 enterprise customers. We are running on a Node.js/Express backend, React frontend, deployed on AWS ECS with an RDS MySQL 5.7 instance as our primary store. MySQL 5.7 reaches end-of-life in October 2026 and we must migrate. Simultaneously, our product roadmap calls for highly flexible project metadata schemas — customers increasingly want custom fields, nested attributes, and dynamic reporting dimensions that fight our current rigid schema. We evaluated whether this is the right moment to switch to a document database (MongoDB) rather than upgrading to MySQL 8 or migrating to PostgreSQL.

Decision

We will migrate from MySQL 5.7 to PostgreSQL 16 as our primary relational store. PostgreSQL's JSONB column type gives us the schema flexibility we need for custom fields without surrendering ACID transactions, mature tooling, or the relational model our reporting layer depends on.

Decision Drivers

• EOL forcing function: MySQL 5.7 support ends October 2026; inaction is not an option.
• Custom metadata flexibility: Customers require 10-200 dynamic custom fields per project entity; current rigid schema requires schema migrations for every new field type.
• Reporting integrity: Finance and PMO teams run complex multi-table JOINs and window functions for cross-project analytics; losing ACID and JOIN semantics would require rebuilding the entire reporting layer.
• Operational familiarity: 4 of 5 backend engineers have PostgreSQL production experience; 0 have MongoDB production experience beyond toy projects.
• AWS RDS support: PostgreSQL 16 is fully supported on Amazon RDS with automated failover; no infrastructure re-architecture required.

Alternatives Considered

Option A: PostgreSQL 16 ✅ (Selected)

Pros: Full ACID compliance with row-level locking; JSONB with GIN indexes allows sub-5ms queries on custom field data at our scale; pg_trgm extension supports full-text search without Elasticsearch for our current needs; logical replication to read replicas is production-tested; pgvector available for future AI features; RDS Proxy reduces connection overhead from our 800-instance ECS fleet.

Cons: JSONB schema validation is application-enforced, not database-enforced (unlike SQL CHECK constraints on typed columns); migration from MySQL requires careful handling of MySQL-specific functions (GROUP_CONCAT → STRING_AGG, etc.) and implicit type coercions; no native horizontal write sharding without Citus.

Why selected: The JSONB approach directly solves our custom fields problem while preserving JOIN semantics for reporting. pgAdmin and AWS RDS tooling are mature. The migration path from MySQL via pgloader is well-documented, and we can run both databases in parallel during the cutover window.

Option B: MongoDB 7.0 (Atlas)

Pros: Native document model eliminates the custom fields schema problem entirely; horizontal sharding is built-in; Atlas Search provides Lucene-backed full-text search; Change Streams simplify real-time notification pipelines.

Cons: No multi-document ACID transactions across collections without significant performance penalty (MongoDB 4.0+ has them but they degrade throughput 3-5x at volume); our reporting queries require cross-collection $lookup aggregations that are slower and more brittle than SQL JOINs; rewriting 340+ SQL queries and the Sequelize ORM layer is estimated at 8-12 weeks of engineering; Atlas pricing at our data volume is ~2.3x our current RDS cost; zero production MongoDB experience on the team means we absorb operational risk simultaneously with a major migration.

Why not selected: The JOIN-heavy reporting layer is a first-class product feature, not legacy debt we can eliminate. Rewriting it in aggregation pipelines would take longer than the MySQL EOL deadline and introduce regression risk in our highest-revenue feature set.

Option C: MySQL 8.0 (In-Place Upgrade)

Pros: Minimal migration risk — in-place upgrade path is well-tested; zero query rewriting; team knows MySQL deeply; RDS supports MySQL 8.0 with automated minor version upgrades through 2030+.

Cons: MySQL 8.0 JSON type does not support GIN-equivalent indexes (only generated columns with functional indexes — significantly more verbose and less flexible than PostgreSQL JSONB); custom fields problem is not solved, only deferred; MySQL's window function implementation has known edge-case bugs in versions before 8.0.26; we would be back in the same position when MySQL 8.0 reaches EOL.

Why not selected: MySQL 8.0 solves the EOL problem but not the schema flexibility problem. We would be choosing a shorter runway over a harder migration that solves both problems at once.

Consequences

Positive

• Custom fields for project entities can be stored as JSONB without schema migrations; new field types ship in hours instead of days.
• Reporting queries retain full SQL expressiveness; no rewrite of the analytics layer required.
• pgvector support positions us for AI-powered project similarity and recommendation features on the roadmap for Q3 2027.
• Estimated 15-20% query performance improvement on complex reporting workloads based on internal benchmarks (PostgreSQL's planner handles our LEFT OUTER JOIN patterns better than MySQL 5.7).
• Migration eliminates technical debt: MySQL-specific SQL quirks (implicit GROUP BY, lenient type casting) are replaced with standard SQL.

Negative

• One-time migration engineering cost: estimated 6 weeks for a team of 2 engineers (schema translation, query rewriting, pgloader ETL pipeline, parallel run validation, cutover playbook).
• All 340+ queries and stored procedures must be audited; approximately 40 contain MySQL-specific functions requiring rewriting.
• Custom field validation logic moves to the application layer (Zod schemas in our Node.js service) — this is enforced by code review discipline, not the database itself.
• PostgreSQL connection model (process-per-connection) requires RDS Proxy to handle our ECS connection count; adds ~$180/month in infrastructure cost.

Devil's Advocate

The strongest argument against this decision is that we are solving the custom fields problem with a workaround rather than the right tool. PostgreSQL JSONB is powerful, but we are essentially bolting a document store onto a relational database and will spend the next two years writing application-layer validation code that a proper document database would handle structurally. MongoDB's document model is correct for entities with 10-200 dynamic attributes — PostgreSQL JSONB becomes unwieldy when customers start querying across custom fields in reports, and we will end up with partial indexes on JSONB paths that are fragile and hard to maintain. The 8-12 week rewrite estimate for the reporting layer may be pessimistic; if it is actually 4-6 weeks, the MongoDB option pays for itself within a year through reduced schema maintenance overhead. We may be choosing the comfortable option over the correct one.

Review Date

Revisit this decision in 18 months (December 2027) or earlier if: (a) JSONB query performance degrades below our 200ms p95 SLA as data volume grows past 500GB, (b) custom field reporting complexity requires more than 3 partial indexes per entity type, or (c) a major product pivot makes the reporting layer less central to the product value proposition. At that point, evaluate whether a hybrid architecture (PostgreSQL for relational data + a dedicated document store for project metadata) makes sense.