Research — database analysis for oxFlow

Question

What database stack should oxFlow run on, given (a) a 5-layer estimating hierarchy with snapshot-divergence semantics, (b) a dozen-ish concurrent users single-tenant, (c) NZ data residency and latency, (d) per-PR preview environments from the BMAD workflow, and (e) migration pull from Oxcon’s current Workbench ERP?

TL;DR

Recommended: Neon (serverless Postgres) in ap-southeast-2 Sydney, plus AWS S3 in the same region for publisher outputs and file uploads. Postgres fits the relational + hierarchical + JSONB + MVCC-concurrent workload cleanly; Neon’s copy-on-write branching maps onto the 361 per-PR preview workflow; the Workbench migration path is a REST-API pull (Workbench exposes OpenAPI over its AWS-hosted SQL Server). If a bundled auth/storage experience is preferred, Supabase is a viable swap — same Postgres underneath, a little more opinion baked in.

Not recommended: DynamoDB / Mongo / Firestore (wrong shape for an inherently relational estimating domain), PlanetScale (Sydney region not confirmed at time of writing), Turso / Cloudflare D1 (SQLite-based — wrong concurrency + capacity profile for this use case).

Approach

Three parallel research agents, each with web access:

Agent A — technology deep-dive. Priced, compared, and checked compliance posture on 13 candidate stores across managed Postgres, NoSQL/document, and object storage.
Agent B — existing-platform stack survey. Reviewed what comparable estimating / construction platforms run on (Procore, Autodesk, CostX, Candy, Benchmark, Cubit, ConEst, B2W, STACK, Bluebeam, ConstructConnect, Databuild, Beams, Sage 300 CRE) and did a focused deep-dive on Workbench itself as the migration source.
Agent C — fit-for-oxFlow analysis. Reasoned across nine architectural dimensions (hierarchical shape, versioning, write contention, document-shaped content, file storage, Workbench migration path, branching, data residency, right-sizing).

Outputs cross-referenced; recommendation below is opinionated and grounded in the oxFlow concept map and business rules.

Findings

Candidate comparison matrix

Candidate	Tier	Sydney region	Branching	Pricing floor	Compliance	Fit
Neon	Managed Postgres (serverless)	Yes (ap-southeast-2)	✅ copy-on-write, Git-like	~$5/mo min, usage-based	SOC 2, ISO 27001, HIPAA	✅ recommended
Supabase	Managed Postgres + auth/storage	Yes	✅ ephemeral per-PR	$25/mo base + compute	SOC 2, HIPAA; no ISO yet	✅ strong alternative
AWS RDS Postgres	Managed Postgres	Yes	❌ snapshot-restore only	~$68/mo single-AZ t4g.medium	Full AWS (SOC/ISO/PCI/HIPAA)	🟡 boring, adequate
AWS Aurora Postgres Serverless v2	Managed Postgres (elastic)	Yes	❌ (fast clone ≈ manual)	~$45/mo 0.5 ACU floor	Full AWS	🟡 overkill
PlanetScale Postgres	Managed Postgres (Vitess)	Not confirmed	✅ deploy requests	$39/mo Scaler Pro	SOC 2, ISO 27001	❌ region risk
Turso	Edge SQLite	N/A	❌	$4.99/mo dev	SOC 2 in progress	❌ wrong shape
Cloudflare D1	Edge SQLite	N/A	❌	$5/mo Workers Paid	Inherits CF SOC/ISO	❌ 10 GB cap, concurrency ceiling
DynamoDB	NoSQL (key-value + doc)	Yes	❌	Usage-based, ~free at rest	Full AWS	❌ no joins, wrong model
MongoDB Atlas	Document	Yes	❌ cluster-per-env	~$60-110/mo M10	SOC 2, ISO 27001, HIPAA	❌ no relational payoff
Firestore	Document (serverless)	Yes (australia-southeast1)	❌	Free tier, then read-heavy	GCP SOC/ISO	❌ read-cost punishes reporting
AWS S3	Object	Yes	N/A	$0.025/GB-mo Sydney	Full AWS	✅ recommended for files
Cloudflare R2	Object	Global	N/A	$0.015/GB-mo, zero egress	SOC 2, ISO 27001	🟡 strong if publisher PDFs hit WAN hard
Google Cloud Storage	Object	Yes (australia-southeast1)	N/A	$0.023/GB-mo	GCP SOC/ISO	❌ cross-cloud unless DB on GCP too

Legend: ✅ recommended · 🟡 viable alternative · ❌ not recommended.

Tier 1 — Relational / Postgres-flavoured managed

Neon. Serverless Postgres with copy-on-write branches that map 1:1 onto the BMAD per-PR preview workflow. Storage ~$0.35/GB-mo and compute ~$0.16/CU-hour post the Databricks acquisition; branches cost nothing until they diverge, which makes ephemeral preview DBs effectively free for a small team. PITR is plan-gated (6h Free, 7d Launch, 30d Scale). SOC 2 Type 2, SOC 3, ISO 27001, ISO 27701, HIPAA (Scale plan, added Sep 2025), GDPR/CCPA. Full Postgres semantics — logical replication from Workbench’s SQL Server is not native but possible via Debezium or a bespoke API-pull ETL.

Supabase. Same Postgres underneath, bundled with hosted Auth (M365 OIDC is straightforward), Storage (S3-compatible), Realtime, and Edge Functions. $25/mo base + $10 compute credit on Pro; 8 GB DB included. Branching is ephemeral per-PR with 5-minute idle auto-pause. SOC 2 + HIPAA; no public ISO 27001 cert yet. Sydney region available. Reduces the “wire in auth + storage + realtime” surface area at the cost of a bit more vendor opinion.

AWS RDS Postgres. The boring, safe choice. Sydney ap-southeast-2 gp3 storage ~$0.138/GB-month; db.t4g.medium single-AZ ~$68/month 24×7; db.m5.large Multi-AZ ~$280/month. Backup storage free up to DB size, PITR up to 35 days. No native branching — per-PR preview DBs become a script-based snapshot-restore exercise. Full AWS compliance stack. IAM auth + Secrets Manager + DMS for Workbench ETL. Fine, but every part of the developer experience around branches is manual.

AWS Aurora Postgres Serverless v2. “RDS but elastic.” 0.5-ACU floor pre-2024; can scale to zero from 2024 onwards. ~$0.14/ACU-hour Sydney, storage $0.11/GB-month, I/O $0.22 per million. No native branching; fast-clone is a copy-on-write storage trick but not tied to Git. Makes sense if the team decides to be 100% AWS-native; weaker dev experience than Neon.

PlanetScale Postgres. GA October 2025. Headline feature is deploy requests + non-blocking online schema migrations — genuinely best-in-class for schema-change velocity. But — Sydney region not confirmed in the current regions list at time of writing, and that’s a material issue for an NZ-only tenant. Scaler Pro starts $39/mo; 10 GB storage included then $1.50/GB-month. Vitess sharding is capability oxFlow won’t use.

Turso / Cloudflare D1. SQLite-backed. Strong for edge read replication but the wrong concurrency and capacity profile for a multi-user estimating tool with concurrent adjudication. D1 has a hard 10 GB per-database cap that would bite quickly across multi-tender worksheet history. Neither has an acceptable branching-in-production story for oxFlow’s BMAD workflow.

Tier 2 — NoSQL / document / wide-column

DynamoDB. Sydney ~$1.25 per million writes / $0.25 per million reads on-demand; storage $0.25/GB-month. PITR up to 35 days. The fundamental issue is shape — Tender → Estimate → Heading → Item → Worksheet is deeply relational, reporting needs cross-entity queries, and DynamoDB has no joins and limits transactions to 100 items / 4 MB. Adjudication with concurrent editors can be done with condition expressions, but nothing about this domain wants a key-value store.

MongoDB Atlas. M10 ~~$0.08–0.15/hour on AWS Sydney (~~$60–110/month). Multi-document ACID since 4.0. Document model maps reasonably to the worksheet canvas, but the relational reporting oxFlow needs (categorisation slice-and-dice, estimate viewer filters by highest-qty resource) suffers. No native per-PR branching.

Firestore. Free tier: 1 GiB, 50K reads/day. Paid: $0.06 per 100K reads, $0.18 per 100K writes. Read-heavy cost model punishes reporting and dashboards — exactly the workload oxFlow’s estimate viewer and live dashboard will generate. Sydney available. No.

Tier 3 — Object storage (publisher output, program uploads, attachments)

AWS S3. Sydney ~$0.025/GB-month (slightly above US $0.023); PUT/COPY/POST $0.0055 per 1K, GET $0.00044 per 1K; egress to internet $0.114/GB. Lifecycle to Standard-IA ($0.0138/GB), Glacier Instant ($0.005), Glacier Deep Archive ($0.002). Presigned URLs, versioning, Object Lock, server-side encryption. Safe default.

Cloudflare R2. $0.015/GB-month (~40% cheaper than S3 Sydney) and — critically — zero egress fees. Class A ops $4.50/million, Class B $0.36/million. S3-compatible API. Material for publisher-delivering PDFs to Oxcon clients; if the publisher gets heavy external download traffic, R2 is the cheaper long-run bet. Acceptable alternative to S3.

Google Cloud Storage. $0.023/GB-month australia-southeast1. Only compelling if the DB also lands on GCP (AlloyDB, Firestore); otherwise cross-cloud egress dominates.

Existing-platform survey — what do comparable products run on?

A summary (full version with citations in Agent B output — see References):

Procore — PostgreSQL on AWS/Rackspace, Ruby on Rails monolith with 600+ tables. Third-party data access via Procore Data Extract product + REST; customer SQL Server replication via marketplace connectors.
Autodesk Construction Cloud / BIM 360 — AWS multi-region (US East, Ireland, Australia, UK, Canada, India, Germany, Japan). Specific engines not public.
CostX (RIB / Exactal) — Microsoft SQL Server. Cloud tier backed by Azure Zone-Redundant Storage.
Benchmark Estimating (what oxFlow replaces) — AWS (EC2, Cognito, WAF, ALB, API Gateway, S3); DB engine not publicly named.
ConEst — explicitly SQL Server (2016–2022 Enterprise/Standard/Express) on .NET, client/server on-prem.
B2W Software (Trimble) — “B2W Cloud” with SQL Server strongly suggested by the .NET ecosystem.
Bluebeam Studio — AWS EC2 + S3 + SQL Server AlwaysOn. Multi-AZ with Australian region.
ConstructConnect — the GCP outlier: Cloud SQL, AlloyDB, BigQuery, Firestore. Terraform + GitLab CI/CD. Confirmed via job listings.
Sage 300 CRE — Actian Pervasive legacy, ships a SQL Replicator to Microsoft SQL Server since v18.
Cubit (Buildsoft) / Candy (RIB) / Databuild / Beams — desktop or file-based; engine not publicly documented.

Patterns:

Legacy desktop estimators are universally SQL Server or embedded/Pervasive.
Modern SaaS entrants split: Postgres-on-AWS (Procore, Benchmark Cloud, Autodesk, STACK) vs GCP-native (ConstructConnect).
Dual-tier deployment (on-prem + vendor-hosted) is the NZ/AU norm — regional customers expect the option to self-host.
APIs are the new DB — every modern entrant exposes REST/JSON with OpenAPI.
Data residency drives per-country regions (Bluebeam, Autodesk).

Workbench — the migration source

Workbench is the flagship construction / project-services ERP in NZ, owned by Workbench International. It’s the system oxFlow will pull migration data from for Oxcon.

Data tier — ASP.NET MVC + HTML5, Microsoft SQL Server as the authoritative data store, Service-Oriented Architecture. Confirmed via workbenchcentral.com/technology.
Deployment — dual. On-prem (self-hosted) or Workbench-hosted. Same product.
Cloud hosting — Workbench-hosted tenants run on AWS, with SQL Server on Amazon RDS for SQL Server, Multi-AZ, 99.99% SLA, CloudFront CDN in front. Confirmed via the Workbench cloud blog post. AWS region not named publicly; Sydney ap-southeast-2 is the likely region for AU/NZ tenants — speculation.
Integration surface — JSON REST API over most functions, Swagger / OpenAPI documented, business rules enforced through the API (not just DB writes). On-prem customers can reach SQL Server directly via ODBC / linked server / SSIS.
Reporting — Sharperlight integration.
Modernisation — nothing public. The stack remains ASP.NET MVC + SQL Server as of the latest public tech page.

Migration implication. Workbench exposes an OpenAPI-documented REST surface, which is the simplest and most auditable pull path. For one-shot migration + periodic sync of reference data (activity codes, work centres), a scheduled API pull is simpler, coupling-light, and survives Workbench schema changes. For bulk historical backfill, direct T-SQL against SQL Server is viable if Oxcon’s Workbench instance grants read-only access. Either path lands neatly in Postgres via a thin ETL.

Fit-for-oxFlow — nine architectural dimensions

Condensed from Agent C’s full writeup; longer reasoning in the agent output (see sources).

Hierarchical writes and reads (Headings 5-deep, Items 5-deep, nested worksheets). Adjacency list (parent_id) + Postgres ltree path column with a GiST index is the right shape. Recursive CTEs cover occasional deep aggregation. Nested-set and graph DBs are overkill / wrong-shape at this depth. (PostgreSQL ltree, WITH queries)
Snapshot / versioning (snapshot divergence on resource/recipe usage). Bitemporal SCD Type 2 on source catalogues + immutable snapshot rows referenced by worksheet lines. The anomaly-review query becomes a join between the snapshot row and the currently-valid source row. Event sourcing is overkill for ~12 users. (SCD2 pattern)
Hot write paths (concurrent adjudication + concurrent worksheet editing). Postgres MVCC handles baseline trivially — readers never block writers. Add a version integer for optimistic concurrency on worksheet lines and adjudication rows; reserve SELECT … FOR UPDATE for the adjudication “claim” action. (Postgres MVCC, explicit locking)
Document-shaped content (content blocks, adjudication matrices, publisher output). JSONB in Postgres with GIN indexes on frequently-queried keys. Small, tightly coupled to relational entities, mostly-read. Adding Mongo doubles ops surface for no benefit. (MongoDB vs Postgres JSONB eval)
File storage (publisher PDFs, .mpp uploads, attachments). Object storage with presigned URLs for uploads and downloads — app servers never proxy bytes. Short-lived URLs (≤1h download, ≤15min upload). DB holds only key + hash + metadata. (AWS presigned URL docs)
Workbench migration path. API pull for ongoing reference-data sync; one-time bulk export + crosswalk table for historical estimates. The real risk is semantic mapping — Benchmark’s flat code lists don’t map cleanly to oxFlow’s 5-layer Heading/Item tree; plan for an explicit crosswalk table + manual adjudication for ambiguous cases.
Branching / preview environments. BMAD with per-branch previews wants ephemeral DBs per PR. Neon’s copy-on-write branches create full production-like copies in seconds. Supabase branching is schema-only by default. RDS and Aurora require snapshot/restore scripting. This is a material dev-velocity dimension. (Neon branching, Supabase branching)
Data residency. AWS now has a live NZ Auckland region (ap-southeast-6) with ~22ms latency from NZ clients vs ~75ms to Sydney (ap-southeast-2) — roughly 3× faster. For interactive worksheet editing this is noticeable. Prefer ap-southeast-6 if the chosen vendor offers it; Sydney ap-southeast-2 is an acceptable fallback. Anything further (US/EU) is not. (Latency comparison)
Right-sizing. The dominant risk at 12 users single-tenant is over-engineering. DynamoDB global tables, multi-region Aurora, enterprise Mongo clusters, Kafka event sourcing all solve problems oxFlow doesn’t have. A single-region managed Postgres (2–4 vCPU, 8–16 GB RAM) plus one object storage bucket, with multi-AZ standby and 7–30 day PITR, covers the workload with headroom.

System shape — how the recommended pieces fit

flowchart LR
    Workbench[(Workbench<br/>RDS SQL Server<br/>AWS)] -- REST API pull --> ETL[ETL job<br/>scheduled sync]
    Xero[(Xero)] -- Companies --> ETL
    M365[(M365)] -- Users --> ETL
    ETL --> Neon[(Neon Postgres<br/>ap-southeast-2)]
    Neon <--> App[oxFlow<br/>Next.js / API]
    App -- presigned URL --> S3[(S3 bucket<br/>publisher PDFs<br/>MPP uploads<br/>attachments)]
    Neon -- logical replication? --> Neon_Preview[(Neon branch<br/>per PR)]
    App --> Users([Estimators · Lead estimators · Admins])

Conclusion / recommendation

Use Neon (serverless Postgres) in ap-southeast-2 (Sydney), with AWS S3 in the same region for publisher outputs and uploads.

Why:

Shape matches the domain. The entity model is deeply relational with a bounded tree depth, a clear snapshot-divergence pattern, small semi-structured blobs (content blocks, adjudication matrices, publisher output), and concurrent-editor write patterns. Postgres with ltree + JSONB + MVCC optimistic concurrency covers every one of these without a second store.
Branching matches the BMAD workflow. Neon’s copy-on-write branches make per-PR preview DBs effectively free. None of RDS / Aurora / PlanetScale-in-Sydney / Supabase-data-branching match this today.
Workbench migration is unobstructed. Workbench runs SQL Server on AWS with a Swagger/OpenAPI REST surface. A small ETL worker pulling reference data on schedule, plus one-shot bulk backfill via direct SQL where permitted, lands cleanly in Postgres. No architecture gymnastics.
Right-sized. Neon’s usage-based pricing and pay-per-branch economics scale down gracefully to a dozen-user load; we’re not paying for always-on enterprise capacity.
Compliance posture is adequate. SOC 2 Type 2, SOC 3, ISO 27001, ISO 27701, HIPAA (Scale plan). Oxcon is NZ single-tenant — no higher bar is required.
S3 is the safe default for files because AWS Sydney is the shortest-path object store for an AWS-adjacent stack, and presigned URLs mean app servers never hold binaries in memory.

When to revisit this:

If Neon ships an Auckland (ap-southeast-6) region, move — ~3× latency improvement for interactive editors.
If Oxcon ever wants multi-tenant (other NZ contractors), re-evaluate the object-storage cost-per-tenant story against Cloudflare R2 (zero egress).
If publisher output becomes a material external-traffic source, move object storage to R2.

This recommendation does not preclude hosting the oxFlow application on AWS — Neon runs inside AWS, the S3 bucket is AWS, and an AWS application tier (Fluid Compute on Vercel, ECS, or App Runner) is a fine adjacency. The choice here is the data tier, not the app tier.

Alternatives considered

Supabase (instead of Neon). Same Postgres, bundled auth/storage/realtime. Materially reduces “wire it up” surface area and is a strong choice if the team wants fewer moving parts. Downsides: a bit more vendor opinion, no ISO 27001 yet, ephemeral branching is schema-only by default.
AWS RDS Postgres. The safe, boring, decade-proven choice. No branching, so per-PR preview DBs become a scripted snapshot-restore exercise — meaningfully slower iteration. Fine if the team weighs “nobody ever got fired for RDS” over BMAD velocity.
AWS Aurora Postgres Serverless v2. If the team decides AWS-native matters (e.g., RDS → Aurora → other AWS services all in one account/plane), Aurora Serverless v2 is reasonable. 0.5-ACU floor is real money at idle; fast-clone is not Git-native. Pick RDS or Aurora based on whether elastic capacity matters more than dev UX.
Cloudflare R2 (instead of S3). Zero egress is a real long-term lever, especially if publisher PDFs become a client-facing download volume. Marginal rework to swap later — both are S3-compatible APIs.
DynamoDB + S3 (“everything’s AWS”). Rejected — wrong shape for a relational estimating domain.
MongoDB Atlas. Rejected — no relational payoff vs Postgres + JSONB.
Firestore / D1 / Turso. Rejected — wrong concurrency/capacity/cost profile.

oxFlow Wiki

Explorer

Research — database analysis for oxFlow

Question

TL;DR

Approach

Findings

Candidate comparison matrix

Tier 1 — Relational / Postgres-flavoured managed

Tier 2 — NoSQL / document / wide-column

Tier 3 — Object storage (publisher output, program uploads, attachments)

Existing-platform survey — what do comparable products run on?

Workbench — the migration source

Fit-for-oxFlow — nine architectural dimensions

System shape — how the recommended pieces fit

Conclusion / recommendation

Alternatives considered

References

Vendor and pricing

Workbench and comparable platforms

Architecture and patterns

Repo cross-references

See also

Linked from

Graph View

Table of Contents

Backlinks