ReferenceAsk
Remy Reference/Architecture/Architecture/App Databases
10Architecture

App Databases

The file an app's data lives in, the pod that owns it while it is in use, and where the durable copy ends up at rest.

Each Remy app has its own database: a SQLite file, durable in object storage in the workspace's region, owned by one app database pod at a time while it is in use. There is no shared schema, no cross-tenant query surface, and no row-level security to misconfigure. This chapter covers the file, the ownership model that makes it fast, the flush model that makes it durable, and how the platform keeps it in the workspace's chosen region.


#One File Per Database

An app's tables are defined as TypeScript files. At build time the platform parses them, diffs them against the live schema, and applies DDL to a clone before promoting. See Build and Deploy. The result is a SQLite database file whose tables are exactly the app's tables plus the system columns the platform adds to every table: an id, created and updated timestamps, and the identity of the last writer, set from the auth context of the method that wrote the row.

The file is the isolation unit. An app's queries run against its file and no other. A method's queries arrive as one batch that is one transaction, and SQL cannot open or end a transaction of its own or name another file. A method reaches its database through the SDK, which sends SQL to the platform with the execution's hook token; the platform resolves the token to a release and an environment, and the environment's release id is the data-plane key that names the file. A live request, a preview, and a dev session each name different files.

The file is also the portable unit. Exporting a database is downloading it.


#Ownership

The app database role is a Deployment of several pods. At any moment, each database has at most one owner pod, recorded in Redis at a key for that database with the owner's address and a TTL. The owner holds the SQLite connection open for the life of its ownership; reads and writes hit that connection directly.

When a query arrives at a pod:

  1. It re-checks ownership against its own address. The caller's lookup is a routing hint; this check is the correctness gate.
  2. If it owns the database, it runs the query.
  3. If nobody owns it, it claims ownership atomically and hydrates the file from the store.
  4. If another pod owns it, it proxies the request to that pod.
  5. If the proxy is refused because the owner is gone, it force-releases and claims. A proxy that times out while the owner still answers is a timeout, never a takeover, since the request may have run there.

Owners refresh their TTLs from a loop of their own that never waits on a transfer, and a release or a takeover removes a claim only while it still names the pod it is about. A pod that dies without releasing loses its ownership when the TTL expires, and a reverse index of what each pod owned lets the surviving pods reclaim its databases without scanning the keyspace. Every live pod also registers in a Redis hash with a heartbeat and writes its load beside it, so a caller sends a fresh claim to the quieter of two live pods.

SQLite execution runs in a worker-thread pool inside the pod, so the connection layer never blocks the request thread. A database is open on one worker at a time, placed on the quietest worker when the pod first sees it. A statement cannot be interrupted, so the pool has many more workers than cores (32). A long statement holds only its own worker, and the kernel shares the cores between workers, so a short request beside it is not kept waiting.

A call has one deadline: two minutes on the pod for a query or batch, its wait for a turn included. After that the pod answers that it timed out, and the caller waits a little longer, so the answer it hears is the pod's. A call is sent again only when it provably never ran (the connection was never made, or a pod refused it before running anything), because a statement cannot be stopped once it starts and a second send of a write would apply it twice.


#Flushing

Every write schedules a flush. After two seconds of quiet the file is uploaded to the store; a ten-second ceiling guarantees that sustained writes still flush regularly. A database is held warm for an hour after its last query, then closed: written home, connection closed, ownership given up, disk freed. A close whose upload fails keeps the database open and dirty, and tries again.

Rollouts drain cleanly. On shutdown a pod marks itself draining, so no new claim comes to it, and refuses every request with an answer that callers send again elsewhere. It keeps listening, because a refused connection is how a caller recognizes a dead owner, whose databases it takes. The pod then closes every database once its writes are uploaded, giving up each claim as it goes, so the next pod hydrates the final flush, and only then stops listening and deregisters. Its claims and its registry entry stay fresh until the drain is done, so a slow final flush is never handed to another pod, and if the pod is killed mid-drain, the surviving pods' sweep reclaims what it held.


#The Working-Copy Model

A workspace can choose a storage region, and its app's database lives at rest in that region's bucket. Compute runs in one region. For a database whose home is elsewhere, a flush every two seconds across regions would be slow and costly, so the platform keeps a working copy.

TierWhereRole
Hot working setThe owner pod's memory and local diskEvery read and write
Working copyThe compute region's bucketEvery debounced flush lands here while the database is hot
Durable copyThe home region's bucketThe authoritative at-rest copy

The working copy is always at least as fresh as the durable copy. While a database is continuously hot, it is checkpointed to the durable copy at least hourly. When it goes cold, it is finalized: the durable copy is written and the working copy deleted. A sweep finalizes anything a dying pod left behind. Nothing lives only in the compute region for more than about an hour past its last write. A database whose home is the compute region has no working copy at all; working and durable are the same object.

The durable object is created exactly once, when the database is created, and a missing object on read is an error rather than an empty file. That invariant is what makes a misplaced file fail loudly at first touch instead of silently serving empty tables.


#Durability and Recovery

In our cloud, the private bucket that holds database files is versioned, so recent prior versions of a file remain available after an overwrite. The bucket is encrypted at rest and blocks all public access. Reads are HTTPS only.

A release build never applies DDL to the live file. It clones the file, applies the schema change to the clone, and promotes the clone on success. The previous release's file is left in place, so a promotion that fails leaves live serving exactly as it was.


#Snapshots for Shadow Runs

A jewel run must see the world as it was when the human's invocation began. Before dispatching such a method, the platform snapshots each of the release's databases on its owner pod into a local copy, and every query tagged with that run's snapshot id is served from the snapshot instead of the live handle. Snapshots are local only, never flushed, and disposed when the run settles. See Self-Hosted Models and Jewels.

What This Gives an App
Its own database, structurally separate from every other app's.
Reads and writes against an open connection on one pod, not a hydrate per query.
Durability within seconds of every write, and clean handoff on every rollout.
Its data at rest in the region its workspace chose.
Export as a file, and schema changes that never touch live data until they have succeeded.