Roadmap & Build Sequence

Purpose & scope

This page turns the architecture into a build schedule. It synthesizes the recommended rollout and the build-vs-buy shopping list into an ordered set of phases, each with concrete deliverables, the specs it draws on, the gate it must pass, and a Definition of Done. It is the document an engineering lead uses to sequence work and to decide what is in scope for the next milestone.

It does not redefine component internals — those live in the per-component specs (Engine Core, Storage Interface, Object-Storage Backend, and the rest). This page is the schedule and the critical path; the component pages are the blueprints.

Guiding sequencing principle

The phases are ordered so that the storage seam is a configuration value, not a rebuild. The engine speaks to a single narrow Storage trait; whether that trait is satisfied by a local file or by an LSM-on-S3 backend is decided by the connection string at engine_open time (file://… vs s3://…). Because of that seam, each phase only adds a backend, a listener, or a control loop — it never reworks what came before.

• MUST keep the Storage trait stable from Phase 1 onward; adding the ObjectStorage backend in Phase 2 MUST NOT change the trait signature the engine calls.
• MUST select the storage backend by connection-string scheme at open time, so the same compiled library embeds against a file or against S3 with no rebuild.
• MUST treat server mode as the embedded library wrapped in a listener; the engine binary built in Phase 1 MUST be the same engine linked into engine-server in Phase 3.
• SHOULD deliver each phase to a state where it is independently demonstrable and usable, not merely a checkpoint toward a later phase.
• MUST NOT fold a later phase's concern (e.g. branching, idle-stop) into an earlier phase as a shortcut; doing so couples layers the seam is meant to keep separate.

Phase 1 — Embedded library first

embedded zero infra Ship the engine as an in-process library bound into Bun via bun:ffi, backed by LocalFileStorage (a plain .db file, no network). This is the fastest route to a working demo: a Bun process opens the library, runs SQL, and persists to a local file with function-call latency. No object store, no server, no controller.

Deliverables

• MUST produce libengine.a / libengine.so / libengine.dylib — the embeddable static and dynamic libraries.
• MUST publish engine.h, the C-ABI header that is the stable boundary every runtime binds to (engine_open, engine_query, engine_close).
• MUST ship the @twilldb/bun thin TypeScript wrapper over the raw bun:ffi symbols for ergonomic embedded use.
• MUST implement LocalFileStorage as the first concrete Storage impl, satisfying the trait the engine already calls.
• SHOULD include the local cache (shared-buffer page cache) so the hot path is in-process even before disaggregation arrives.

Interface frozen in this phase

// engine.h — the stable C ABI every runtime binds to. Frozen in Phase 1.
typedef void* engine_handle;

// url selects the storage backend by scheme: "file://..." (P1) or "s3://..." (P2).
engine_handle engine_open(const char* url);
const char*   engine_query(engine_handle h, const char* sql);   // returns serialized rows
void          engine_close(engine_handle h);

import { dlopen, FFIType, suffix } from "bun:ffi";

const { symbols: db } = dlopen(`libengine.${suffix}`, {
  engine_open:  { args: [FFIType.cstring], returns: FFIType.ptr },
  engine_query: { args: [FFIType.ptr, FFIType.cstring], returns: FFIType.cstring },
  engine_close: { args: [FFIType.ptr], returns: FFIType.void },
});

const h = db.engine_open(Buffer.from("file://./local.db\0"));   // P1: pure embedded
const rows = db.engine_query(h, Buffer.from("select 1 as n\0"));

Specs it draws on

Engine Core (parser → plan → executor, MVCC, WAL) · Storage Interface (the trait + LocalFileStorage) · Local Cache (in-process page cache) · Bun Integration (bun:ffi + @twilldb/bun).

Exit criteria & gate

• MUST open a file:// database from Bun via FFI, run DDL + DML + queries, and read back correct results across a process restart (file persistence).
• MUST pass a basic correctness gate: a SQL conformance smoke suite plus MVCC snapshot-isolation checks (a reader sees a consistent snapshot across a concurrent committed write).
• SHOULD demonstrate the @twilldb/bun wrapper end-to-end in a sample Bun app with no native build step beyond the prebuilt library.

Phase 2 — Add the ObjectStorage backend

embedded scale-to-zero Add a second Storage impl, ObjectStorage, and the database becomes disaggregated and scale-to-zero — while staying embedded. Nothing about the engine or the FFI surface changes: you flip the connection string from file:// to s3://, and the same code path now durably bottoms out on object storage.

Deliverables

• MUST implement ObjectStorage: an LSM page store that writes layers to S3/R2, versioned by LSN (in-memory layer → delta layers → image layers), with compaction and GC past the PITR window.
• MUST implement the commit log as an ordered append log whose durability bottoms out on S3 conditional writes (compare-and-swap) — atomic ordered appends and single-writer fencing without a separate Raft/Paxos cluster.
• MUST wire the local cache (now load-bearing) to keep S3's hundreds-of-ms latency off the read hot path; without it every read is a network call.
• SHOULD support any S3-compatible durability tier (AWS S3, Cloudflare R2, MinIO) selected by the connection string / config.

Specs it draws on

Storage Interface (the trait the new impl satisfies) · Object-Storage Backend (LSM page store + S3-CAS commit log) · Local Cache (now mandatory, not optional).

Exit criteria & gates

• MUST open an s3:// database from the same binary as Phase 1, with no recompile, and pass the Phase 1 correctness suite against the object-storage backend.
• MUST pass Benchmark Experiment 1 (single-commit latency floor, p50/p99/p999, same-region vs cross-region) — establishing the worst-case sequential-commit ceiling.
• MUST pass Benchmark Experiment 2 (group-commit throughput curve) — proving batching lifts the plateau well above the Exp-1 ceiling (W1 beaten).
• MUST pass Benchmark Experiment 4 (crash safety / durability) unconditionally: every acked commit survives kill -9 at adversarial points, no torn or half state, no acked-write loss.

Phase 3 — Add engine-server + pgwire

server multi-client Wrap the same engine library in a wire-protocol listener (engine-server) speaking a subset of the Postgres wire protocol. This unlocks remote / server mode for multi-client access and for any tool that expects to talk to Postgres. Because the engine is wire-compatible, you inherit PostgREST and Bun's built-in Bun.sql client for free — no bespoke REST layer, no bespoke driver.

Deliverables

• MUST ship engine-server: the Phase-1 engine library linked into a network listener; the listener is the only difference from the embedded binary.
• MUST implement a pgwire subset sufficient for Bun.sql, pgbench, and PostgREST — simple + extended query, auth handshake, parameter binding, row description, and error responses.
• SHOULD provide pooler guidance: place PgBouncer / pgcat in transaction mode in front of the server to absorb serverless connection bursts.
• MAY document a NAPI native-addon path as an alternative client binding that works in Bun and Node from one package.

import { SQL } from "bun";
const sql = new SQL("postgres://user@host:5432/mydb");   // P3: same engine, network listener
const rows = await sql`select 1 as n`;

Specs it draws on

Server Mode & Wire Protocol (the listener + pgwire subset + pooler) · Bun Integration (Bun.sql client path) · Capabilities (PostgREST attaches in front of server mode for free).

Exit criteria & gates

• MUST serve a connection from Bun.sql and from pgbench with no client-side adapter beyond a standard Postgres client.
• MUST re-run Experiment 2 (group-commit curve) in server mode and confirm the plateau holds end-to-end through the wire protocol and pooler.
• MUST run Experiment 3 (write-contention wall) in server mode to confirm the red-quadrant detector behaves identically over the wire (same-row writers flatten; N separate DBs scale linearly).
• SHOULD demonstrate PostgREST pointed directly at engine-server, generating a REST API with no REST code written.

Phase 4 — Add the controller

scale-to-zero instant clones Add the lifecycle controller that cold-starts the engine on first connection, tears it down when idle (true scale-to-zero), and creates a branch as a new LSN pointer over shared immutable layers (copy-on-write → instant clones). This is what delivers the "dozens of tools, each on its own branch off one base" goal: many branches with near-zero marginal storage until they diverge.

Deliverables

• MUST implement the controller's lifecycle state machine: cold-start on first connection, run, idle-detect, stop; only object-storage bytes bill at rest.
• MUST implement branch operations: branch = a new LSN pointer over shared immutable layers (copy-on-write), with near-zero marginal storage until divergence.
• MUST implement single-writer fencing via the commit log's CAS token, so exactly one writer per database is authoritative across cold-start / restart transitions.
• SHOULD expose idle-timeout and keep-warm knobs tuned from Experiment 5's cold-read distributions.

Specs it draws on

Lifecycle & Controller (state machine, branch-on-LSN, CAS fencing).

Exit criteria & gates

• MUST scale to zero on idle and cold-start on the next connection, with durable state intact and the CAS fence correctly re-acquired by exactly one writer.
• MUST create a branch from a base in O(pointer) time, with the branch's writes invisible to the base and vice versa, and storage growing only on divergence.
• MUST pass Experiment 5 (cold read after scale-to-zero): warm vs forced-cold read distributions (p50/p99) characterized to set idle-timeout and keep-warm policy.
• MUST pass a thundering-herd test: N concurrent cold starts (the Exp-5 extension) bounded to a documented spin-up saturation point without correctness loss.

Phase 5 (optional) — Capabilities

optional With the core platform shipped, grow capability by the build-in vs compose rule: storage/execution capabilities go into the engine, interface/service capabilities are composed around it.

• MAY build vector search in-core: a vector type plus an HNSW access method behind the same Storage trait the B-trees use — so it inherits scale-to-zero, branching, and S3-backing for free (an agent can branch its memory).
• MAY compose better-auth (in-process library writing to the embedded DB), PostgREST (REST layer in front of server mode), and DuckDB (OLAP engine over shared S3 Parquet/Iceberg) — none welded into the core.
• SHOULD treat the HNSW cold-traversal case as the nastiest cache scenario in the engine: keep the working-set graph resident in memory, S3 as the cold floor only.

Full detail in Capabilities: Build-in vs Compose.

Phase 6 — SQL surface completeness Implemented (6A–6E)

frontend only seam unchanged With the core platform shipped, grow the hand-written SQL frontend (sql.rs → exec.rs) to cover the common, OLTP-shaped majority of the PostgreSQL and SQLite surface — so the SQL an application actually issues against an OLTP database parses and runs the same on Twill. This is surface-completeness work, not a PostgreSQL re-implementation and not byte-for-byte dialect parity. Every change is additive frontend work (lexer/parser, AST, executor, with catalog.rs and conn.rs for the few stateful items), so the storage seam never moves.

Invariants every stage preserves

• MUST leave the Storage trait unchanged — STORAGE_TRAIT_VERSION stays 2, the C1–C8 conformance suite stays green, branching and scale-to-zero unaffected.
• MUST keep the C ABI frozen — SQL flows through the existing engine_prepare / engine_exec / result-set path; ENGINE_ABI_VERSION stays 3 unless a stage genuinely needs a new export (called out per stage).
• MUST leave MVCC, single-writer, group commit and durability untouched: new read shapes run against the same snapshot, new write shapes emit the same WalOp batch at the same commit LSN, nothing acks before durable.
• MUST NOT mis-parse — anything still unsupported after a stage returns a clear ENGINE_ERR_SQL, never silently ignored or mis-executed.

Deliverables — five independently-shippable stages

• 6A Expression & single-table completeness — CASE, CAST/::, IN/BETWEEN, ||, OFFSET, NULLS FIRST/LAST, RETURNING, upsert (ON CONFLICT) and INSERT … SELECT. The cheapest, highest-coverage wins; no new execution model, only richer expressions and result shapes over the existing single-table scan.
• 6B Multi-table & aggregation — joins, GROUP BY/HAVING, subqueries, set operations, views & non-recursive CTEs. The executor gains a small relational layer (nested-loop join, hash-group) above the per-table MVCC scan. The single largest stage.
• 6C Function library — a curated OLTP-common set of string, math, date/time, UUID and JSON scalar/aggregate functions; non-deterministic ones (now(), random(), gen_random_uuid()) evaluate once at execution so replay reproduces the stored value exactly.
• 6D Constraints, schema evolution & savepoints — CHECK/DEFAULT/secondary UNIQUE, AUTOINCREMENT/SERIAL, ALTER TABLE, and SAVEPOINT/ROLLBACK TO (new catalog facts are additive WAL ops; DDL stays autocommit-only).
• 6E Dialect shims — placeholder forms ($1/:name → ?), identifier quoting, LIKE vs ILIKE, isolation-level pinning and session no-ops — concentrated at the pgwire boundary so the engine core stays one dialect.

Deliberate non-goals

Kept rejected with a clean ENGINE_ERR_SQL: OLAP-shaped query features (window functions, GROUPING SETS/ROLLUP/CUBE, LATERAL, recursive CTEs — compose DuckDB); the procedural & extensibility surface (PL/pgSQL, CREATE FUNCTION/TRIGGER/EXTENSION, roles/GRANT, LISTEN/NOTIFY); exact-decimal numerics (NUMERIC(p,s) maps to f64); PostgreSQL's structured type system (arrays, composite types, ranges, enums); and storage-engine-conflicting statements (ATTACH/DETACH, tablespaces).

Specs it draws on

SQL Compatibility & Mapping (the stage-by-stage gap map and dialect decisions) · Engine Core (the parser → executor the stages extend) · Server Mode & Wire Protocol (where the 6E dialect shims live).

Exit criteria & gate

• MUST ship each stage with engine tests that would fail without it, the C-ABI surface re-checked in tests/ffi.rs, and a green bun test after cargo build -p twill-engine --release.
• MUST keep the C1–C8 storage conformance suite, the Phase-4 branching battery, and the Experiment-4 crash gate green at every stage (no stage touches the seam or the commit/recovery path).
• SHOULD grow a checked-in compatibility corpus of representative PostgreSQL- and SQLite-flavoured statements with expected results, run as the regression gate as stages land.

Phase 7 — Row-level security & session authorization Implemented (spec 17)

frontend + executor implemented seam unchanged Supabase-style row-level security, resolving the open authorization question carried over from Capabilities. RLS is split along the build-in-vs-compose seam: the per-row enforcement predicate is an execution capability built into the engine, while JWT verification, identity, and policy authoring stay composed around it. Enforcement is the database's job; identity is the boundary's job — exactly the division Postgres+Supabase use. Like Phase 6 it is additive sql.rs→exec.rs growth (reusing 6E's SET handling and the Phase-3 per-statement principal) plus backward-compatible catalog facts, so the storage seam never moves; unlike Phase 6 it adds a security guarantee, so it carries its own gate. The full design is in Row-Level Security.

Why enforcement must be in-core

A predicate enforced above the engine only protects the one path through that layer; the moment any other client speaks to the engine — a second pg-wire tool, a direct embedded FFI call, a branch opened for debugging — the filter is gone and every row is visible. Row-level security is only a security property if it is enforced at the lowest common chokepoint every query passes through. In this architecture that chokepoint is the executor, not any composed layer above it — the same reasoning that put MVCC visibility in the executor rather than a client library. Composed identity is fine (every path must still present a principal); composed enforcement is not.

Invariants the phase preserves

• MUST build only enforcement (the per-row predicate + the session-principal read) into the engine; JWT verification, secret handling, role management, and policy-authoring UX stay composed (better-auth / the wire boundary), never the engine core.
• MUST leave the Storage trait unchanged — STORAGE_TRAIT_VERSION is untouched, the C1–C8 conformance suite and the branching battery stay green; policies are additive WAL catalog facts, not a side file.
• SHOULD keep the C ABI frozen at ENGINE_ABI_VERSION 3 by flowing the session context through the existing 6E SET path rather than a new hot-path export.
• MUST NOT rely on a composed layer for enforcement; a policy some client path can skip is not a guarantee.

Deliverables

• MUST add a per-connection, per-transaction session context — a small bag of claims plus an active role, set via the existing SET path and readable inside the executor through tiny auth.uid()/auth.role()/auth.claim() accessors; scoped to the connection, never process-global, and treated as already-trusted opaque input the engine never re-verifies.
• MUST implement policy DDL — ALTER TABLE … ENABLE ROW LEVEL SECURITY and CREATE POLICY … FOR … TO … USING (…) WITH CHECK (…) — persisted as an additive CreatePolicy WAL fact (predicate AST + command + roles), exposed via Connection::catalog and rebuilt by replay, autocommit-only like other DDL.
• MUST implement executor enforcement: AND the matching policies' USING predicate into every read of a protected table — base scans, join inputs, subqueries, and the row set feeding RETURNING — over the same MVCC snapshot, and reject any write failing WITH CHECK before its WAL op is emitted, so a denied write never becomes durable.
• SHOULD default-deny once RLS is enabled and no policy grants access, matching Postgres semantics (enable = "deny all" until a policy opens a hole).
• MAY provide a privileged owner/maintenance bypass (compaction, replay), gated explicitly and off by default.

Specs it draws on

Row-Level Security (Proposal) (the placement, design, and normative requirements) · Capabilities (the deciding rule and the open authorization question this resolves) · Server Mode & Wire Protocol ("auth per-connection, authorization per-statement" — the principal the executor reads) · Engine Core (the parser → executor the predicate extends, over the same MVCC snapshot).

Exit criteria & gate

• MUST pass a security-focused bypass battery: an unfiltered-access attempt across every client path (FFI, pgwire, branch) returns only authorized rows; denied writes never reach the WAL; enforcement is identical in embedded and server mode.
• MUST show policies branch and PITR-restore with the database: a branch inherits then independently diverges its parent's policies with no pages copied, and point-in-time recovery restores the policy set in effect at an LSN.
• MUST demonstrate unmodified PostgREST + a composed identity layer (better-auth or a JWT shim) delivering the Supabase experience end-to-end: one REST surface, safe for untrusted clients, filtered by engine-enforced policies — with FK-embedding sub-queries filtered by the embedded table's policies too.
• SHOULD keep STORAGE_TRAIT_VERSION at 2 and the C ABI frozen, proving (as Phase 6 did) the capability is pure frontend/executor growth over an unmoved seam, with engine tests that would fail without it and a green bun test after a release rebuild.

Build-vs-buy shopping list

Per slot: whether to build from scratch, and the existing piece to start from (2026). The two build slots — the storage trait and the Bun binding — are the load-bearing original work; almost everything else is inherited or adapted.

Critical path & inter-phase dependencies

The phases are strictly serial on their backbone, but a few items can be developed in parallel once their prerequisite seam exists.

• MUST complete Phase 1's frozen Storage trait and engine.h ABI before Phase 2 begins; the ObjectStorage impl targets the trait, and the FFI surface is reused unchanged.
• MUST complete Phase 2's durable commit path (Exp 4 green) before Phase 3; server mode must not be the first place durability is exercised under load.
• MUST complete Phase 2's CAS commit log before Phase 4; the controller's single-writer fencing is the CAS token, so fencing cannot exist until the log does.
• SHOULD allow the pgwire subset (Phase 3) and the LSM page store (Phase 2) to be built by separate workstreams once the trait is frozen, since they touch disjoint surfaces.
• MAY start the controller's branch-on-LSN design (Phase 4) during Phase 2, because branching is a property of the LSN-versioned immutable layers built there.

P1 libengine + engine.h + LocalFileStorage   [freeze Storage trait + C ABI]
       │
       ▼
P2 ObjectStorage (LSM page store + S3-CAS log) [Exp 1, 2, 4]
       │                         │
       ▼                         ▼
P3 engine-server + pgwire   P4 controller (idle-stop, branch, CAS fence)
   [Exp 2/3 server mode]        [Exp 5 + thundering-herd]
       │                         │
       └───────────┬─────────────┘
                   ▼
P5 (optional) vector-in-core · compose better-auth / PostgREST / DuckDB
P6 SQL surface completeness — 6A–6E   [frontend only; parallel to P5, seam unchanged]
P7 (proposed) row-level security        [frontend + executor; builds on 6E SET + P3 principal]

Per-phase Definition of Done

A phase is Done only when its deliverables ship, its gate experiments are green, and the artifact below is demonstrable end-to-end.

Phase 1 DoD

Prebuilt libengine.* + engine.h + @twilldb/bun bind from a sample Bun app; file:// DB persists across restart; basic-correctness + MVCC snapshot suite green. C ABI declared frozen.

Phase 2 DoD

Same binary opens an s3:// DB with no recompile; correctness suite green on object storage; Exp 1, Exp 2, and Exp 4 all pass — Exp 4 unconditionally (no acked-write loss under kill -9).

Phase 3 DoD

engine-server serves Bun.sql and pgbench over the pgwire subset; Exp 2 and Exp 3 reproduce in server mode through the pooler; PostgREST demonstrated against the server with no REST code written.

Phase 4 DoD

Engine scales to zero on idle and cold-starts with the CAS fence re-acquired by exactly one writer; branch created in O(pointer) time with copy-on-write isolation; Exp 5 distributions captured; thundering-herd (N concurrent cold starts) bounded without correctness loss.

Phase 5 DoD (optional)

Vector type + HNSW index behind the Storage trait, branching with the DB; better-auth / PostgREST / DuckDB composed against the shared object-storage floor without modifying the engine core.

Phase 6 DoD

Stages 6A–6E land independently, each with engine tests that would fail without it and a green bun test after a release rebuild; the C1–C8 conformance suite, the branching battery, and the Exp 4 crash gate stay green throughout; the compatibility corpus passes as the regression gate. STORAGE_TRAIT_VERSION stays 2 and the C ABI stays frozen.

Phase 7 DoD (proposed)

Session-context primitive, CREATE POLICY / ENABLE ROW LEVEL SECURITY DDL, and executor enforcement ship as additive frontend/executor work plus the CreatePolicy WAL fact; the bypass battery proves every client path (FFI, pgwire, branch) returns only authorized rows and denied writes never reach the WAL; policies branch and PITR-restore with the database; unmodified PostgREST + a composed identity layer deliver the Supabase experience end-to-end. STORAGE_TRAIT_VERSION stays 2 and the C ABI stays frozen.

Milestone table

Failure modes & sequencing risks

• MUST NOT let the Storage trait drift after Phase 1; a trait change forces a rewrite of LocalFileStorage and breaks the "config not rebuild" guarantee the whole sequence rests on.
• MUST NOT ship Phase 2 with a commit path that acks before the S3-CAS append is durable; this is the acked-write-loss failure Exp 4 exists to catch.
• SHOULD guard against Phase 3 quietly forking the engine: if engine-server embeds a divergent build, the "same engine, two front-ends" invariant is lost and bugs split across paths.
• SHOULD treat the cold-start tail (Phase 4) as a first-class metric, not an afterthought; scale-to-zero means caches start cold after every idle period, and the thundering-herd case can saturate spin-up.
• MAY see object-storage-native OLTP maturity move under the project (SlateDB, S3-CAS log designs, libSQL rewrite are the active frontier); pin dependency versions and re-run the gate suite on upgrades.

Open questions & risks

• MAY need to decide NAPI-vs-FFI for the Bun binding earlier than Phase 3 if a single Bun+Node package is a hard requirement, since it changes the artifact shipped from Phase 1.
• MAY want to bring branch-on-LSN forward into Phase 2 (it is a property of the immutable LSN-versioned layers) if instant clones are needed before full controller automation.
• SHOULD define how much of the pgwire surface is "subset enough": the minimum for Bun.sql / PostgREST / pgbench may be smaller than full Postgres compatibility, and the line should be explicit before Phase 3.
• MAY need a WASM build track (Cloudflare Workers + R2) as a parallel deliverable; per Deployment Targets this is a port, not a recompile, and is not on the native critical path above.
• MUST keep the per-tool go/no-go decision (from the benchmark plan) attached to the rollout, so write-heavy hot-row outliers are routed to coupled Postgres rather than blocking a phase gate.

Related specifications