Technical architecture
A small, sturdy stack for a planning system of record
NexTwelve runs on Cloudflare Workers in front of Postgres. One API is the only way to change the plan: it checks who may change what, writes the cells and records the audit trail in a single database transaction.
Overview
Three tiers. Clients talk only to the Worker; the Worker is the only thing that talks to the database and to file storage.
Clients
Cloudflare
Data and identity
Dashed outline: planned. Everything else is built and tested.
Components
| Layer | Technology | Responsibility |
|---|---|---|
| API | Cloudflare Workers, Hono, TypeScript | Every request: session check, input validation, reads and writes in one transaction each. The same code runs in Node for tests and scripts. |
| Database | Postgres 16 (PlanetScale Postgres or any managed Postgres) | The plan itself. Schema epm, row-level security, and the database functions that perform every write. |
| Connections | Cloudflare Hyperdrive | Keeps connections to Postgres warm near the database. Caching is off so every read sees the latest committed values. |
| Files | Cloudflare R2 | Import files, reference workbooks and snapshots, under one prefix per tenant. |
| Scheduled jobs | Cron Triggers | Nightly snapshots and retention; hourly cleanup of expired demo copies. Authorized by a job key whose hash is the only copy in the database. |
| Identity | Google sign-in | A Google ID token is exchanged for a signed 15-minute session token. Invitations, roles and access live in NexTwelve. |
| Web app | HTML, CSS and plain JavaScript | No framework and no build step; served as static assets with a strict Content Security Policy. |
| Bot check | Cloudflare Turnstile (optional) | In front of "Try the live demo" only. |
Data model
Applications, cubes and dimensions
An application holds dimensions and cubes. Every cube has Scenario, Version, Years, Period, Entity and Account; Department and up to eight custom dimensions (Product, Channel and so on) are optional.
Members and hierarchies
Members form hierarchies in which each member adds to, subtracts from or is ignored by its parent. Deploying checks the hierarchies and rebuilds a closure table of every ancestor and descendant, so any total is one join away.
Cells
Only entered or loaded cells are stored, one row each, keyed by a 16-slot coordinate: cube, the six fixed dimensions, Department and eight custom slots. Values are numeric(24,6): exact decimals, never floating point.
History
An audit row for every changed cell (old and new value), a transaction record in a SHA-256 hash chain, and versioned members, forms and reports with the moment each change took effect.
How a change is saved
Grid saves, imports, undo and restores all take this path. There is no other way to write a cell: the API's database role has no direct write access to cells, audit or access tables.
- The client sends the changed cells with its session tokenAlong with the values it read, so the server can tell whether someone else changed them since.
- The Worker opens one transaction and sets the tenant and userFrom here on, Postgres itself confines every query to that tenant.
apply_changeschecks everything again, inside the databaseRole, coordinates, write access to each member, open plan windows, and conflicts with changes made meanwhile.- Cells, audit rows and the chain entry are written togetherOne audit row per changed cell; one transaction record linked to the previous one by a SHA-256 hash.
- Commit, or nothing at allTotals are computed on read from the closure table, so they are never stale. Undo and point-in-time restore replay old values from the audit trail.
Security
| Layer | How it is enforced |
|---|---|
| Identity | Google ID tokens checked for signature, issuer, audience and expiry. Users match on Google's stable ID; invitations bind by verified email; a Workspace domain can admit its own users. |
| Tenant isolation | Row-level security on every tenant table, forced so that database functions are bound by it too. The API connects as a role that owns nothing and cannot bypass it. |
| Roles | Service administrator per tenant; application administrator, power user, planner and viewer per application. Checked in the API and again in the database. |
| Member-level access | Read and write grants on secured dimensions, to users or groups. The closest grant wins and an explicit "none" denies. Forms and reports show only what a person may read. |
| Writes | Only through apply_changes, which re-checks role, access and plan windows for every cell. |
| Audit | Append-only cell audit and a per-application hash chain that can be re-verified at any time. |
| Phones | Paired by a one-time QR code (two minutes) that the user approves on the computer by typing the number the phone shows. The phone's session may call only read routes, its database transactions are read-only, and it ends when revoked from either side or after 8 hours. |
| Files and snapshots | Stored under the tenant's prefix; snapshot files carry SHA-256 checksums kept in the database, and a restore refuses any file that does not match. |
Backup and recovery
Point-in-time restore
Put a slice of data (cube, scenarios, versions, any members) back to how it was at a moment, in place or into another version. Preview first; up to 100,000 changed cells per restore.
Metadata restore
Put members, hierarchies, forms and reports back to a moment or a snapshot. Deleted members return with their history; members added since that hold data are kept.
Snapshots
A consistent copy of an application in R2: manual, nightly (newest 30, plus one a month for 12 months) and automatically before snapshot and metadata restores. Restore in place or into a new application.
Every restore is itself an audited change that can be undone.
Public demo
A private copy per visitor
- Each visitor gets a tenant of their own, loaded from a stored template in a few seconds
- Three people in every copy: administrator, regional planner, viewer
- Isolated by the same row-level security as real tenants
- Removed with its files when it expires (24 hours) or when the visitor starts over
Guard rails
- No inviting people, changing roles or creating applications
- 1 MB per request, 16 MB of uploads and 20 snapshots per copy
- Limits per network on new copies per hour and live copies at once, plus an overall cap
- An optional Turnstile check before a copy starts
Core MVP requirements
What the minimum viable product had to do, and how each requirement is met and verified.
| ID | Requirement | How it is met and verified | Status |
|---|---|---|---|
| F1 | Model a business: dimensions, hierarchies, cubes | Six fixed dimensions, optional Department, eight custom; validated deploy; metadata test suites | Met |
| F2 | Enter plan data in forms | Saved forms, page choices, up to three dimensions per axis, paste, conflict detection; grid and form suites | Met |
| F3 | Report and export | Reports with suppression, scaling and rounding; Excel and CSV output; report suites | Met |
| F4 | Load actuals from the general ledger | CSV and Excel imports, mapping rules, crosswalk workbooks, dry runs, drill-back; import suites | Met |
| F5 | Control who sees and changes what | Roles, member-level grants, plan windows; security and isolation suites | Met |
| F6 | Audit every change and undo it | Cell audit, hash chain with verification, undo by transaction; grid and concurrency suites | Met |
| F7 | Restore data and metadata to a point in time | Slice restore and metadata restore with previews; restore and metadata-restore suites | Met |
| F8 | Back up applications | Manual, nightly and pre-restore snapshots with retention and checksums; snapshot suites | Met |
| F9 | Edit form and report definitions in Excel | Export and import as Excel or CSV, checked row by row before anything changes; definitions suites | Met |
| F10 | Plan in Google Sheets | Sheets add-on on the same API | Phase 1 |
| N1 | Tenant isolation that does not depend on the API | Forced row-level security; tests read every tenant table as another tenant and as no tenant | Met |
| N2 | Exact amounts | Decimal storage and decimal text in snapshots, exports and reports; precision suites | Met |
| N3 | One audited write path | The API role cannot write cells, audit or access tables directly; checked by the isolation suite | Met |
| N4 | Responsive at sample-company scale | 20,000-cell saves in seconds on a populated application, asserted in tests; a demo copy starts in a few seconds | Met |
| N5 | Recoverable | Nightly snapshots plus point-in-time restore from the audit trail; tampered and missing files are refused | Met |
| N6 | Hard to break | 500+ tests against a real Postgres, about 390 of them adversarial | Met |
| N7 | Self-hostable | Open source; runs locally with Docker and Wrangler, deploys to your own Cloudflare account and Postgres | Met |
Limits
| What | Limit |
|---|---|
| Cells in one grid, form or report | 50,000 |
| Dimensions on a form or report axis | 3 |
| Custom dimensions per cube | 8 |
| Amount precision | 18 digits before the decimal point, 6 after |
| Changed cells per restore | 100,000 |
| Uploaded file | 25 MB (1 MB in the demo) |
| Mapping rules per import format | 50,000 |
| Definitions import | 5,000 definitions and 100,000 layout rows per file |
| Session | 15 minutes (a demo session lasts as long as its copy) |
| Snapshot retention | Nightly: newest 30 plus newest per month for 12 months. Before a restore: 90 days. Manual: kept. |
Read the code, run it yourself
The schema, the API, the browser app and every test are in the repository.