# SQLStyle complete documentation Canonical public documentation corpus generated from the same Markdown sources as sqlstyle.com/docs. Service status: available through sqlstyle.com. --- # Getting started Source: https://sqlstyle.com/docs/getting-started Markdown: https://sqlstyle.com/docs/getting-started.md The SQLStyle SDK opens a local database while SQLStyle coordinates the writer, live replicas, and recovery. > SQLStyle does not publish an uptime commitment or claim a formal compliance certification. Keep an independent recovery path for critical data. ## 1. Create and authenticate an account Create an account at [sqlstyle.com/account](https://sqlstyle.com/account). Then authenticate the CLI: ```sh sqlstyle auth login sqlstyle auth status ``` Browser login stores an account-scoped CLI credential in the selected local credential store. The web dashboard handles signup and billing; database, storage-profile, API-key, restore, and analytics workflows remain in the CLI. ## 2. Create service resources Create a non-secret storage profile: ```sh sqlstyle storage profiles create \ --name production \ --provider aws_s3 \ --bucket app-dbh \ --region us-east-1 \ --access-key-id "$AWS_ACCESS_KEY_ID" ``` Create a logical database and a database-scoped runtime key: ```sh sqlstyle database app/main create --write-allowance 100 \ --storage-profile-id sqlstyle keys create \ --database app/main \ --label application-runtime ``` The key command prints the secret once by default. Store it in the application's secret manager. ## 3. Ship the runtime and SDK A Node.js release is expected to pair the SDK package with the SQLStyle runtime: ```text app/ node_modules/@sqlstyle/database/ vendor/sqlstyle/ include/sqlstyle.h lib/libsqlstyle.so.1 ``` The SQLStyle artifact supplies ABI v2 through its uniquely named library. Keep the runtime and SDK on the same release-qualified version set. ## 4. Open the database ```js import { DatabaseSync } from "@sqlstyle/database"; const db = new DatabaseSync("app.db", { sqlstyle: { name: "app/main", apiKey: process.env.SQLSTYLE_API_KEY, storage: { secretAccessKey: process.env.SQLSTYLE_STORAGE_SECRET_ACCESS_KEY, }, }, }); try { console.log(db.prepare("select authority from sqlstyle_status").get()); } finally { db.close(); } ``` `DatabaseSync` follows the synchronous `node:sqlite` shape. SQLStyle adds managed database configuration while statements and rows remain ordinary SQLite operations. ## 5. Check write authority Query the SQL surface through the returned connection: ```sql SELECT authority FROM sqlstyle_status; ``` Only `held` admits writes. Treat `unheld` as read-only. ## Next steps - Start with the [architecture section](/docs/architecture) before designing a topology. - Learn how [Application Stored Procedures](/docs/application-stored-procedures) follow the current primary. - Configure [object storage](/docs/object-storage) for portable recovery. - Review [status and SQL surfaces](/docs/status-and-sql-surfaces). - Use [restore and recovery](/docs/restore-and-recovery) before relying on a backup policy. --- # Architecture overview Source: https://sqlstyle.com/docs/architecture Markdown: https://sqlstyle.com/docs/architecture.md Each SQLStyle node has a SQLite database on local disk. Opening that database starts the per-database runtime inside the application process; SQL queries do not go through a SQLStyle proxy or the hosted controller. If several processes open the same database on one machine, they coordinate as one node and elect one local process to run capture and network services. SQLStyle ships with the Bedrock database core: patched SQLite with WAL2. Its capture adapter publishes committed pages as SQLStyle transaction events, so replication and backup share one implementation after capture. ## Capture creates one physical history The primary's database core publishes a committed WAL position while it still holds the native writer lock. Capture reads the corresponding pages, encodes each as either a compact difference from the preceding database view or a complete LZ4-compressed page, and publishes the records to a bounded shared-memory ring. That ring feeds two paths: ```text Bedrock build | v WAL page capture | v shared capture ring / \ v v replication DBH history | | v v replicas local catalog -> object storage ``` Replication sends captured transactions directly from the primary to each replica. DBH generation persists the same physical history into append-only segments. Local DBH extends replica catch-up beyond the ring; uploaded DBH and full snapshots form the recovery history used by restore. The capture path also protects its source. Per-WAL requested and completed frontiers prevent checkpoint, restart, truncation, or WAL reuse from destroying frames before their ring records have been published. ## Data and coordination cross different boundaries | Boundary | What crosses it | | --- | --- | | Database core to capture | Committed WAL positions and page data | | Primary to replica | Captured transactions and, when required, an rsync snapshot | | Node to object storage | Full snapshots and incremental DBH objects | | Controller to node | Primary grants, topology, health decisions, and accepted recovery state | Application queries, replication pages, and backup objects never pass through the controller. Object-storage credentials also remain with the application runtime and recovery tools. ## Authority is independent of local ownership The controller grants one node primary authority and associates the grant with a fencing token. A newer token supersedes every earlier token, so a former primary cannot resume writes merely because its local database is recent or its process is still running. The same lineage governs recovery. Objects uploaded by a former primary may remain in customer-owned storage, but stale authority cannot make those objects part of the accepted recovery chain. During failover, an eligible node must cover the controller's required transaction before it can receive the next primary grant. Local process ownership is narrower. A capture host may be replaced by another process on the same machine while the existing controller grant remains valid; that handoff preserves authority but cannot create or extend it. ## Read the architecture by subsystem 1. [Database core and capture](/docs/database-cores-and-capture) explains the common capture contract, WAL-retention fence, page encodings, ring publication, and DBH handoff. 2. [Node capabilities](/docs/node-roles) explains the independent controller permissions, capture policy, and capture-loss action. 3. [Replication and catch-up](/docs/replication) covers live delivery, replica acknowledgements, local DBH catch-up, ring pressure, and rsync replacement. 4. [Backup and recovery history](/docs/backup-and-recovery-history) follows DBH from a sealed local file through object storage, controller acceptance, retention, and restore planning. 5. [Multi-process operation](/docs/multi-process) describes local ownership, presence, same-boot capture-owner replacement, and terminal cleanup. 6. [Standard, Permissive, and Lightspeed integrity modes](/docs/lightspeed) defines per-connection synchronous policy and clean-close proof before local reuse. 7. [Primary authority and failover](/docs/primary-and-failover) explains direct health evidence, controller confirmation, candidate recovery, and token transfer. --- # Database core and capture Source: https://sqlstyle.com/docs/database-cores-and-capture Markdown: https://sqlstyle.com/docs/database-cores-and-capture.md SQLStyle ships with the Bedrock build, which uses patched SQLite with WAL2. The database core is fixed by the artifact you install, not selected per database connection. The Bedrock adapter defines how SQLStyle observes a commit, pins the preceding database view, and controls checkpoint or WAL2 reuse. It exposes one boundary to the capture ring, DBH format, replication protocol, and rsync implementation. ## Commits publish work; capture performs it While holding the native writer lock, the capture adapter maps the database core's WAL position to a SQLStyle transaction, publishes the requested capture frontier, and wakes the capture worker. This path does not compress pages, write DBH files, or wait for a network operation. Capture reads the WAL2 frames described by that frontier. Each published record carries the source page, SQLStyle transaction, commit boundary, database size at commit, and the encoding used for the page body. A transaction becomes available downstream without requiring the database core to implement replication or backup separately. ## WAL reuse waits for capture completion SQLStyle maintains requested and completed capture frontiers for each WAL slot. A requested frontier identifies source frames that capture still needs. The matching completed frontier advances only after those frames are represented by release-published records in the capture ring. Checkpoint, WAL restart, truncation, generation changes, and slot reuse must observe that completion before destroying the covered bytes. This is the fence that closes the interval between SQLite publishing a commit in its WAL index and SQLStyle finishing physical capture. If capture-host ownership changes during that interval, the successor validates the WAL identities, ring tail, and published frontiers before repairing or continuing progress. It fences the database when those sources do not describe one continuous history. ## Page differences require an exact predecessor For a transaction `T`, capture attempts to read each changed page from the database view at `T - 1`. It compares that page with the new WAL frame and emits a compact sequence of changed byte ranges when the result is expected to be smaller than a complete LZ4 page. The predecessor is a correctness dependency. If the required view is busy, missing, belongs to a reused WAL generation, or otherwise cannot be proven, capture uses a self-contained LZ4 page for the transaction. It does not wait or retry in the capture path, and it never constructs a difference against an uncertain page. Both encodings carry the same transaction metadata. Replication can send them without re-encoding, and DBH can persist them with the page's encoding recorded in the file. ## The ring has one shared publication edge Encoded records enter a fixed-size circular buffer in the database's shared memory. The producer's reservation and write positions remain private to the capture host. A release-published ring edge is the shared statement that a complete record, including its source trailer, is available to readers. Scratch bytes written before that publication are irrelevant after a crash. A same-boot successor starts from the published edge, validates the last complete record, and walks backward to the retained DBH boundary to reconstruct the WAL and transaction positions it inherits. DBH generation and attached live replicas consume published records for different purposes. DBH provides the required local history consumer; replication cursors are revocable and are covered in [replication and catch-up](/docs/replication). ## DBH makes the ring suffix recoverable Two DBH workers alternate at transaction boundaries. One appends records from the ring to the active segment while the other finalizes the preceding segment. The active worker flushes the visible bytes and publishes a checksummed DBH prefix before retiring the corresponding ring records. The ring edge and DBH prefix are exact same-boot handoff evidence. A replacement capture host resumes from those boundaries instead of inferring a transaction from the newest database page. Finalized DBH segments then enter the durable catalog and backup path described in [backup and recovery history](/docs/backup-and-recovery-history). --- # Replication and catch-up Source: https://sqlstyle.com/docs/replication Markdown: https://sqlstyle.com/docs/replication.md Physical replication connects each replica directly to the current primary. The controller supplies topology and authority state, but it does not relay the page stream. The replica writes the received transactions into the SQLite database on its own disk and serves reads from that local database. ## Acknowledgements track committed replica state A replication session is bound to the database, both node identities, the current primary token, and a session generation. It begins with the transaction after the replica's last authenticated local commit. For each page, the replica either applies a difference to the expected predecessor or decompresses a self-contained LZ4 payload. It commits the pages with the primary's transaction boundary and database size, then acknowledges the transaction. Progress from a replaced session, a stale primary token, or a position that does not end at a commit cannot advance the replica's durable frontier. The acknowledgement is not part of the primary's commit. A primary transaction may return before any replica has made it durable; replication durability is a later fact recorded by the session. ## Catch-up uses ring, DBH, then rsync The primary satisfies a replica's next required transaction from three sources, in order: 1. Retained capture-ring records for the current live tail. 2. Local DBH segments for older transactions still present on the primary. 3. One rsync replacement when neither local source contains the required point. The transition from DBH to live capture uses a short overlap. Capture pins the live edge, the replica commits everything before that edge, and only then does the session release its DBH claim and attach to live delivery. The two sources do not need a shared sequence number, but the handoff cannot leave a transaction gap. Uploaded DBH objects are not fetched into a live replication session. Object storage remains a restore and promotion-recovery source; replica catch-up depends only on history local to the current primary before it falls back to rsync. ## Slow replicas lose their live cursor The capture ring is bounded, and a live replica does not hold a ring position while it is catching up through DBH or rsync. If a new reservation would overtake an attached replica's cursor, capture revokes that attachment, retires any locally borrowed transport buffers, and reclaims the ring space. The session then resumes from its last acknowledged transaction through DBH. Ring reclamation does not wait for a remote acknowledgement or for the replica to complete its catch-up. If the required DBH has also expired, the session requires snapshot replacement. ## Rsync installs an exact replacement image For a missing or history-stale replica, the primary pins an exact SQLite read snapshot and runs the existing sqlite3-rsync protocol over the replication session. The replica stages the snapshot transaction before replacement and publishes it as durable only after SQLite commits the new image. Streaming then resumes at the transaction after the snapshot. A session performs at most one automatic rsync replacement; subsequent progress uses DBH and live capture. A lost final acknowledgement may cause the primary to retry session work, but it cannot make the replica discard a replacement image SQLite already committed. ## A primary change creates new sessions When authority moves, the replacement primary creates sessions bound to its new token and recovered state. The former primary's connections and session generations cannot publish progress into that lineage. A replica may become the replacement only when it is promotion-capable and covers the controller's required recovery transaction. ## Trusted-network mode removes transport protection SQLStyle's trusted-network transport disables encryption and authentication for post-handshake QUIC 1-RTT application traffic. It provides no confidentiality, integrity, or peer authentication for replication records and must be confined to a private, access-controlled network in which on-path traffic manipulation is an accepted risk. Deployments crossing a public, shared, or otherwise untrusted network must use SQLStyle's TLS transport mode with the configured CA, peer certificate, and controller-published database certificate fingerprint. That mode retains encrypted and authenticated 1-RTT traffic. --- # Backup and recovery history Source: https://sqlstyle.com/docs/backup-and-recovery-history Markdown: https://sqlstyle.com/docs/backup-and-recovery-history.md SQLStyle's backup path begins with the same captured page records used for replication. DBH workers persist those records locally, and a separate storage owner validates, catalogs, and uploads finalized history to customer-owned S3-compatible storage. Network storage is not part of the database commit path. ## DBH becomes durable before it becomes remote The active DBH worker appends complete transactions from the capture ring. At a segment boundary, the second worker takes over ring consumption while the first finalizes the preceding file. Rotation occurs only between transactions. The storage owner hashes and syncs the finalized bytes, then records the exact local artifact in the durable catalog. This `sealed local` state is the boundary between DBH continuity and remote upload. Losing the only valid copy threatens continuity; an unavailable object store does not. Failed uploads remain cataloged and retry with bounded backoff while capture and later DBH rotation continue. Local DBH has two uses before upload: finalized segments extend replica catch-up beyond the capture ring, while the active prefix gives a same-host capture successor an exact continuation boundary. That active prefix is same-boot handoff state, not machine-crash durability. Live replication never downloads an uploaded DBH object to recreate local catch-up history. ## The catalog advances only through continuous history Each DBH object identifies its database, authority token, transaction range, size, and checksum. Uploads may finish out of transaction order, but the catalog's uploaded transaction advances only through the highest continuous prefix. A later segment in the bucket cannot conceal a missing earlier range. On successful upload, the catalog atomically changes the local record to uploaded before the file becomes eligible for local eviction. Inventory refresh reconciles remote presence, size, checksums, and object metadata without holding the catalog lock during network I/O. ## Full snapshots provide recovery bases Incremental DBH needs a compatible full snapshot from which replay can begin. SQLStyle takes an exact SQLite read view and stores it either as a full DBH snapshot or as a SQLite snapshot object. Scheduled and manual snapshots use the same serialized snapshot path. If neither the local nor uploaded copy of an incremental segment validates, the catalog records a lineage break and schedules a new full snapshot. Capture and incremental upload may continue while that snapshot is produced, but restore does not bridge the missing range by assumption. ## The controller fences authoritative storage progress Object storage can contain valid bytes beyond the recovery point the controller has published. This is expected: normal uploads do not require a controller write for every segment, and a former primary may finish an upload after its token has become stale. Before a restore or other non-primary reader selects authoritative objects, it requests an object-storage read fence from the controller. If the current primary is connected, the controller asks it for the cached highest contiguous uploaded transaction for the current token. That request does not flush SQLite, seal DBH, create a snapshot, start an upload, or pause writes. The controller accepts the response only from the current holder, durably advances its recovery frontier, and returns the fenced value to the reader. Advancing that frontier counts as one [controller-state write](/docs/controller-state-writes). Returning the frontier already held by the controller does not. If authority changes while the request is in flight, the stale response cannot advance the frontier. If no primary is connected, the controller returns the last durable frontier it already holds. Once a reader has received a fenced frontier, later recovery and promotion may advance it but cannot roll it back. Objects beyond that frontier remain speculative. Recovery may validate and propose a later continuous frontier, but the data becomes authoritative only through the controller's recovery transition and the next fencing token. ## Restore builds one verified chain Restore combines the controller's accepted frontier with the object inventory. It selects an exact snapshot base and continuous DBH replay range, then validates object identity, size, checksum, transaction coverage, and commit boundaries before materializing SQLite. Latest restore may begin from a raw SQLite snapshot or a full DBH snapshot. Point-in-time restore uses a DBH snapshot base so replay can stop at the selected commit timestamp. Neither path trusts an object merely because its key or modification time makes it appear newest. ## Retention preserves recovery plans, not just ages The retention schedule selects recovery intervals, but deletion is governed by recoverability. SQLStyle waits for a newer uploaded snapshot generation, removes candidate objects from a trial inventory, and reruns the recovery obligations before deleting anything. Shared DBH ranges remain protected when any retained plan still needs them. Connected replica watermarks and active catch-up leases add local-history floors, preventing retention or eager eviction from removing DBH still required by a live session. See [object storage](/docs/object-storage) for configuration and [point-in-time recovery](/docs/restore-and-recovery) for inventory, verification, and restore commands. --- # Application Stored Procedures Source: https://sqlstyle.com/docs/application-stored-procedures Markdown: https://sqlstyle.com/docs/application-stored-procedures.md An **Application Stored Procedure (ASP)** is a named, typed database operation whose handler is application code and whose execution follows the database primary. The logic remains ordinary Go or Rust. Nothing is installed into SQLite or deployed as a separate service. ## Why use an ASP? A request can arrive at any application instance, while a database write must run against the current primary. Without an ASP, the application needs its own primary discovery and RPC path, or a separate service that all writes pass through. An ASP makes that boundary part of the database SDK. The application defines the operation by giving the SDK its name, payload and result types, and handler. The Go SDK returns a typed function; the Rust SDK returns a typed `Procedure`. Application code invokes the Go function or calls `Procedure.call` in Rust. ## How a call runs When the application invokes a generated ASP call, the SDK reads its current view of primary authority and chooses one of two paths. ### Local primary The SDK passes the typed payload directly to the handler. No payload or result serialization is involved. ### Replica The SDK serializes the payload and sends a named invocation over SQLStyle's primary stream. At the primary, the receiving SDK resolves the name to the handler, runs it, and serializes the result back. ## Serialization The payload and result are application-defined native types. Go and Rust infer them from the handler and use named-map MessagePack by default; domain types do not implement SQLStyle-specific serialization methods. Named-map MessagePack is the recommended general-purpose format in the managed Go and Rust SDKs. Go uses `msgpack` field tags when present and otherwise reuses `json` field tags; Rust uses ordinary Serde `Serialize` and `Deserialize` implementations. Stable field names plus optional or defaulted new fields support additive compatibility across mixed application versions. Rust also offers Postcard for fixed schemas. Both SDKs retain JSON when a readable wire format matters. Go's JSON selection follows `encoding/json` tags and custom marshaling interfaces. Its options-bearing definition chooses one encoding for both the payload and result. Go's `Binary` selection calls the standard `encoding.BinaryAppender` and `encoding.BinaryUnmarshaler` interfaces directly. `CustomEncoding` retains the typed serializer contract for generated and handwritten formats. Remote append encoders receive a reusable SDK-owned destination. ### Select another encoding #### Go ```go addTodo, err := sqlstyle.DefineIASPWithOptions( config, "todos.add-json", sqlstyle.ProcedureOptions{Encoding: sqlstyle.JSON}, handler, ) ``` #### Rust ```rust let options = sqlstyle::ProcedureOptions::new( sqlstyle::JsonSerializer, sqlstyle::JsonSerializer, sqlstyle::JsonSerializer, ); let add_todo = sqlstyle::define_iasp_with_options( sqlstyle::drivers::Rusqlite, config, "todos.add-json", options, handler, )?; ``` ## When no primary is available If no writable primary can accept the invocation, the SDK waits for authority to change instead of failing immediately. One wait budget covers primary discovery, write admission, and retries after a transient busy or unavailable response. The default budget is eight seconds. If a primary becomes ready within the budget, the invocation continues. Otherwise the SDK returns its primary-unavailable error. A zero budget fails immediately; in Go, context cancellation or an earlier deadline ends the wait sooner. Set the budget in the [SDK open configuration](#primary-wait-timeout-configuration). It is not a shared JSON5 configuration field. ### Primary wait timeout configuration #### Go ```go wait := 3 * time.Second // wait := time.Duration(0) // Fail immediately. config.Options.PrimaryWaitTimeout = &wait ``` #### Rust ```rust use std::time::Duration; config.options.primary_wait_timeout = Duration::from_secs(3); // config.options.primary_wait_timeout = Duration::ZERO; // Fail immediately. ``` ## Example ### Go ```go type AddTodo struct { Title string `json:"title"` } type AddTodoResult struct { ID int64 `json:"id"` } addTodo, err := sqlstyle.DefineIASP( config, "todos.add", func(ctx context.Context, tx *sql.Tx, payload AddTodo) (AddTodoResult, error) { result, err := tx.ExecContext(ctx, "INSERT INTO todos(title) VALUES (?)", payload.Title) if err != nil { return AddTodoResult{}, err } id, err := result.LastInsertId() return AddTodoResult{ID: id}, err }, ) if err != nil { return err } result, err := addTodo(ctx, AddTodo{Title: "ship it"}) id := result.ID ``` ### Rust ```rust #[derive(serde::Serialize, serde::Deserialize)] struct AddTodo { title: String } #[derive(serde::Serialize, serde::Deserialize)] struct AddTodoResult { id: i64 } let add_todo = sqlstyle::define_iasp( sqlstyle::drivers::Rusqlite, config.clone(), "todos.add", |transaction, payload: &AddTodo| -> sqlstyle::asp::Result { transaction.execute( "INSERT INTO todos(title) VALUES (?1)", [&payload.title], )?; Ok(AddTodoResult { id: transaction.last_insert_rowid() }) }, )?; let result = add_todo.call(&AddTodo { title: "ship it".into(), })?; let id = result.id; ``` Defining a procedure stores its configuration but opens no database connection. All procedures for that database share an on-demand pool, which defaults to eight connections, no minimum idle connections, and a 30-second idle timeout. When the final ordinary application connection closes, SQLStyle stops new procedure admission and waits up to 20 seconds for admitted handlers. It then interrupts remaining database work and waits for every handler to return before finishing shutdown. A later call recreates the procedure pool from `Config`. ## ASP or IASP Choose based on who owns the transaction and duplicate handling. - **IASP:** SQLStyle owns the transaction and makes remote execution idempotent. The handler must not commit or roll back. - **ASP:** Your application owns the transaction and duplicate handling. Use it when the operation already has an idempotency key or duplicate policy. --- # Multi-process operation Source: https://sqlstyle.com/docs/multi-process Markdown: https://sqlstyle.com/docs/multi-process.md An ordinary SQLStyle database open starts the internal work required by that database. Applications do not initialize a separate runtime or send SQL through a local daemon. When several operating-system processes open the same database file, every process still uses SQLite directly and the group presents one node to the controller. Cross-process coordination lives in a per-database shared memory. It contains the capture ring, WAL and DBH progress, lifecycle state, and a compact copy of the current controller grant. ## One process owns capture for each database The shared memory reserves one open-file-description lock for the **capture-host lease**. Exactly one process holds that lease for a database. That process owns the services that require one local coordinator: the controller session, WAL capture, DBH workers, replication connections, and terminal shutdown. Other processes remain full SQLite clients. Their commits publish the same shared capture predicates, and the capture host reads the resulting WAL state. They do not create another controller participant or another DBH history. Capture ownership is per database rather than per process. One process can host capture for database A while acting as an ordinary client of database B. ## Presence covers every activity that can touch SQLite A second shared memory lock carries shared **presence leases**. Each user connection, open in progress, and independent internal task holds its own lease before it enters SQLite and until it can no longer hold SQLite locks or publish work. Presence acquisition is also a validation point. Before opening SQLite, the process confirms that the shared memory session, capture-host identity, and authority state still match. Recovery and terminal shutdown take presence exclusively, which proves that no application process can retain usable database state while raw bytes are inspected or final state is committed. A client-only process simply releases its own presence when it closes. It does not become the terminal coordinator because its last local connection ended. ## Capture ownership survives process exit, not machine loss The operating system releases the capture-host lease when the owning process's final descriptor closes, including after a crash. A surviving application process can then acquire the lease and adopt the same-boot state already published in the shared memory. The successor validates the database identity and controller record, loads the published ring edge, checks the complete ring tail, reconciles the WAL/WAL2 requested and completed frontiers, and resumes DBH from its checksummed prefix. It recreates process-local threads and network connections, but does not discard valid ring or DBH progress merely because their original process exited. This is a same-host, same-boot handoff. The ring and active DBH prefix are not the machine-crash durability layer. After a reboot or complete host loss, recovery depends on the synced SQLite and WAL state, finalized DBH and catalog records, replicas, and object storage. Any malformed ring tail, incompatible WAL evidence, or gap between the published frontiers leaves access fenced for recovery. Mutable settings do not block attachment. Existing processes follow the active owner's shared capture, codec, integrity, and latency policy. Each process keeps its latest successfully normalized settings as the desired configuration for a future owner generation. A ring-capacity request takes effect only when the shared-memory file is recreated with no live mappings. ## Local ownership cannot extend primary authority Acquiring the capture-host lease identifies the process that runs local services; it does not grant writes. A same-node successor may preserve an existing primary grant only when the shared controller identity and token still match and the absolute grant deadline has not expired. Every other case closes writer admission before reconnecting to the controller. No shared memory lock, process takeover, or local database state can mint a fencing token or extend its deadline. ## Capture-loss recovery covers the last-process exit A supervised `sqlstyle run --config FILE --database NAME` entry with `capture: "off"` and `on_capture_loss: "recover"` is recovery-only. While an application process owns capture, it waits without taking ordinary capture ownership. If that owner exits and no application process remains to finish shutdown, one recovery process acquires exclusive terminal presence. The recovery process keeps application admission fenced, validates the ring, WAL, DBH, catalog, and authority evidence, and continues the interrupted local shutdown or recovery. It may establish the required replica, object-storage, or successor coverage before releasing authority, but it never exposes a writer-facing runtime or gains authority from local lock ownership. [Integrity modes](/docs/lightspeed) describes the clean-state and external coverage transition the companion completes. [Primary authority and failover](/docs/primary-and-failover) distinguishes this local process handoff from a change of primary node. --- # Standard, Permissive, and Lightspeed integrity modes Source: https://sqlstyle.com/docs/lightspeed Markdown: https://sqlstyle.com/docs/lightspeed.md # Lightspeed: OFF by default, proof before reuse. SQLStyle has three integrity modes. Standard starts each managed user connection at `synchronous=NORMAL` and restricts later changes to `NORMAL` or `FULL`. Permissive does not override the database core's connection default and leaves every supported synchronous value available. The supported Bedrock build defaults to `FULL`. Internal live-replication connections use a separate policy: Standard and Permissive replicas apply at `NORMAL`, while Lightspeed replicas apply at `OFF`. Standard and Permissive can admit an unproven local image after SQLite recovery while queuing a full snapshot. Lightspeed seals a fully checkpointed clean close with SHA-256; if the required proof cannot be established on restart, SQLStyle recovers from authoritative state or stays fenced. ## Compare the three modes | Behavior | Standard | Permissive | Lightspeed | | --- | --- | --- | --- | | New managed user connection | `synchronous=NORMAL` | Bedrock default (`FULL`) | `synchronous=OFF` | | Connection may select later | `NORMAL` or `FULL` | Any value supported by the database core | Any value supported by the database core | | Live physical or logical replica apply | `synchronous=NORMAL` | `synchronous=NORMAL` | `synchronous=OFF` | | Live concurrent WAL checkpoint | `synchronous=NORMAL` | `synchronous=NORMAL` | `synchronous=OFF` | | Unknown local integrity after an enrolled Standard or Permissive cycle | Admit after SQLite recovery and refresh a full snapshot asynchronously | Admit after SQLite recovery and refresh a full snapshot asynchronously | Recover from authoritative state or remain fenced | | Unknown local integrity after an enrolled Lightspeed cycle | Recover from authoritative state or remain fenced | Recover from authoritative state or remain fenced | Recover from authoritative state or remain fenced | | Matching clean-close SHA-256 | Admit and allow the requested mode | Admit and allow the requested mode | Admit and allow the requested mode | | Commit durability | SQLite `NORMAL` or `FULL` semantics selected by the connection | Depends on the value selected by that connection | Depends on the value selected by that connection | The mode defines user-connection initialization and restrictions, internal live-replication and concurrent-checkpoint synchronization, and local-image admission. It is not a database-wide lock on `PRAGMA synchronous`. A user connection may change its own setting within the selected policy, and that change does not alter the database’s integrity mode, internal replica applier, or live checkpoint worker. SQLStyle’s terminal maintenance and lifecycle-metadata connections use `FULL` when establishing durable evidence. ## Permissive removes the synchronous guardrail Permissive is useful when an application needs the database core's native connection default and the freedom to choose any supported synchronous value. The Bedrock build currently starts those connections at `FULL`; Permissive itself issues no initialization PRAGMA. The available later values include `OFF` and `EXTRA`. It also changes the failure envelope. If a Permissive connection selects `OFF`, returns commits, and then ends without a valid clean seal, a later Standard or Permissive cycle may admit that dirty local image after SQLite recovery and queue a full snapshot. Recent `OFF` commits may be missing after a host failure. Choose Permissive only when that combination is acceptable; choose Lightspeed when an unproven enrolled image must be recovered or fenced. ## The clean-close seal Before application or internal database writes begin, SQLStyle records a dirty cycle and its actual mode with `FULL` synchronization. A graceful terminal shutdown then fences new opens and writes, drains processes and capture work, forces a truncating checkpoint on a `FULL` maintenance connection, closes handles, syncs the main file and directory, and verifies that rollback, WAL, and WAL2 shared-memory files are absent or empty. Only then does SQLStyle hash the complete main database file with SHA-256 and commit the clean record. Failure before that final record leaves the cycle dirty. Header inspection and `quick_check` are not substitutes for this seal. `quick_check` may still validate a backup artifact, but it does not prove that an enrolled image is safe to reuse under the configured policy. On restart, a matching clean seal allows every transition among Standard, Permissive, and Lightspeed. Unknown proof after a Standard or Permissive cycle can be admitted under either of those modes while a full snapshot refreshes in the background. Requesting Lightspeed requires authoritative recovery. Unknown proof after a Lightspeed cycle requires recovery regardless of the newly requested mode. A looser connection policy cannot erase stricter provenance. ## First enrollment is a trust anchor A database with no lifecycle ledger is being enrolled for the first time. SQLStyle explicitly trusts that image, records the requested cycle as dirty, and admits it in any mode after authority and frontier checks. If the database is nonempty, SQLStyle queues an exact transaction-consistent full snapshot. Standard and Permissive admission do not wait for that upload; failures remain observable and retryable. By default, the ledger is stored in a runtime database beside the main database. For `data/app.sqlite3`, the path is: ```text data/app.runtime.sqlstyle ``` `runtime.path` can set another location. Deleting the runtime database resets the trust anchor. That is an explicit operator decision, not a repair technique. An existing malformed, version-1, or otherwise unsupported ledger fails clearly and is never treated as a new enrollment. ## Agreement is local to one database image The active runtime owner publishes the integrity mode in shared memory. Another process may attach with a different requested mode, but its open connections follow the active owner's coherent policy. Its successfully normalized setting becomes the desired policy if that process later starts the next owner generation. Terminal cleanup reads the durable integrity ledger rather than treating shared memory as durable identity. Replication does not extend that agreement across nodes. Each replica advertises its configured mode, the primary replies with its own, and all nine Standard, Permissive, and Lightspeed pairs can stream physical or logical data and perform replacement. Each node keeps its local user-connection, apply, and image-admission policy, so you can change modes as a rolling node-local configuration update. Promotion does not copy or change another node's mode. When a Lightspeed primary streams to a Standard or Permissive replica, SQLStyle logs one warning for that physical or logical stream. The primary can return `OFF` commits faster than the replica's `NORMAL` applier can make them durable, so a busy primary may outpace the replica. SQLStyle does not inspect user connections when making this observation. A validated live shared memory may support same-boot process takeover. If that evidence cannot be reused, cold-start ledger rules apply. ## Configure and inspect the mode `integrity_mode` is an inheritable database setting: ```json5 { "version": 1, "defaults": { "integrity_mode": "standard" }, "databases": { "app/main": { "path": "data/app.sqlite3", "integrity_mode": "permissive" } } } ``` The default is `standard`. Unmanaged databases accept the setting for configuration parity but ignore it. Inspect the active mode through the same managed connection: ```sql SELECT authority, lifecycle, integrity_mode FROM sqlstyle_status; ``` ## Integrity is not durability or freshness The SHA-256 seal proves byte identity for one cleanly closed local image. It does not prove that the image contains the latest cluster transaction. SQLStyle still reconciles authority, lineage, and transaction frontiers before admission. Likewise, Lightspeed and Permissive do not make `synchronous=OFF` commits power-loss durable. Recent `OFF` commits may be lost after host failure. Replicas and object storage can provide recovery sources, but a returned commit does not automatically wait for either one. Lightspeed’s promise is narrower: SQLStyle will not blindly reuse an enrolled local image whose required clean byte seal cannot be proven. --- # Primary authority and failover Source: https://sqlstyle.com/docs/primary-and-failover Markdown: https://sqlstyle.com/docs/primary-and-failover.md SQLStyle separates evidence of failure from authority to replace a writer. A capture owner can determine that its direct connection to the holder has stopped responding, but that observation alone cannot distinguish a failed primary from a network partition. Only the hosted controller can end the current grant and issue the next fencing token. There are no controller roles. [Node capabilities](/docs/node-roles) configure voting, storage work, promotion, capture contention, and capture-loss cleanup independently. ## A fencing token identifies the current writer Every primary grant contains a token newer than the token issued to the previous primary. The runtime admits writes only while its local authority state matches the current grant and its deadline remains valid. A recent database is evidence of recoverable state, not permission to write. If a former primary reconnects after replacement, its token cannot reopen writer admission, advance current replication sessions, or publish recovery progress for the new lineage. ## Direct peer streams detect loss first Each holder-to-capture-owner connection has a dedicated bidirectional health stream bound to the database, both node identities, and the current primary token. Both ends ping once per second. Five seconds without a successful round trip marks that peer unhealthy in the local view. A capture owner then treats holder health as unknown and may report a concern for the exact token and topology it observed. It does not declare the primary dead or start an election. If direct health recovers before the controller confirms the failure, the concern is cleared. A concern is counted only when the reporting connection has `vote: true` and its local token-bound channel has previously completed a round trip to that holder. A nonvoter can report an advisory route failure, which may prompt an immediate holder heartbeat but cannot create, refresh, join, or accelerate a fast-fence incident. ## The controller challenges the primary The controller maintains its own heartbeat for the current grant. The first concern for a token creates a short incident, records the holder's current heartbeat, asks the primary to heartbeat immediately, and tells the reporter to retry after 250 milliseconds. For the current topology: - `C` is the current holder plus every other connected connection with `vote: true`; - `R` is the number of matching reports from those connected voters; and - `H` is `1` when the challenged primary heartbeat has not advanced, otherwise `0`. The controller ends the grant on the fast path only when: ```text 2(R + H) > C ``` The counts are derived from current connections, not retained in the incident. If topology changes, the incident is discarded. Zero-capability ASP sessions do not appear in topology and cannot affect the calculation. If the condition is not met, the controller returns the remaining health window and retains the grant; the ordinary holder-heartbeat timeout remains the fallback. Once the controller has confirmed the holder unhealthy for a token, a later peer ping cannot revive that grant. ## The primary fences itself during suspected isolation The primary evaluates the same direct-health topology from the other side. If the missing vote-capable peers together with the primary constitute a strict majority of current voters, it asks the controller to confirm its own heartbeat. If the runtime cannot queue and confirm that challenge within 300 milliseconds, it closes local writer admission. The controller grant remains allocated during this local fence, so a later confirmation for the same token and topology can restore writes without issuing another token. A topology or authority change starts a new decision. This rule prevents a primary that may be isolated from a majority of the deployment from continuing to accept writes solely because its previous lease deadline has not yet expired. ## Candidate selection starts from a required transaction An unexpected control-channel close first reserves the existing token for a short reconnect window. The same node may resume that grant if it reconnects in time. A holder timeout already confirmed by the health path ends the grant immediately instead of waiting for that reservation. When replacement is required, the controller publishes the minimum transaction the next primary must recover. Connected `promotable` runtimes evaluate their local image and object-storage state and report a frontier only for the matching token. A runtime offers after the decision delay only when its image is usable and its frontier covers the requirement. If the selected candidate still needs to materialize the required state, the controller places it in recovery. The candidate validates and restores a continuous frontier, reports the recovered transaction, and remains read-only until the controller durably issues the next token. Connections without `promotable` never offer, and duplicate or racing offers cannot bypass the controller's current durable state. ## Process takeover does not require node failover A process crash on the primary host may leave the node and its controller grant intact. Another local process can acquire capture ownership and preserve that grant only while its identity, token, and absolute deadline still validate. This transition recreates process-local services; it does not select a new primary. See [multi-process operation](/docs/multi-process). ## Planned handoff changes holder and token atomically For a requested handoff, the controller reserves one connected `promotable` target and asks the current primary to drain and release. It then updates the holder and token in one durable transition rather than publishing an intermediate state with no primary. The replacement admits writes only after it receives the new grant. The former primary's drained state and earlier token remain fenced. ## Authority also fences backup history A former primary can finish uploading an object after failover, but object arrival does not advance the recovery state readers may trust. The controller accepts storage progress only for the current token or through an explicit recovery transition. [Backup and recovery history](/docs/backup-and-recovery-history) describes that read fence and the distinction between uploaded and authoritative history. --- # Node capabilities Source: https://sqlstyle.com/docs/node-roles Markdown: https://sqlstyle.com/docs/node-roles.md SQLStyle does not assign node roles. Each managed connection declares five independent properties: ```json5 capabilities: { controller: { vote: true, storage: true, promotable: true, }, runtime: { capture: "on", on_capture_loss: "none", }, } ``` These defaults produce regular SDK behavior. ## Controller capabilities - `vote` allows counted health concerns. A concern is counted only after the local token-bound peer-health channel has previously reached the current holder. - `storage` allows storage-floor probes and storage-derived analytics work when the local image is ready. - `promotable` allows authority acquisition, offers, and handoff candidacy. The runtime still requires a usable image and sufficient frontier. The three values form one immutable mask for an authenticated WebSocket. Changing the mask requires reconnecting. A zero-capability connection can read topology and use ASP transport but does not appear as a topology node. ## Runtime capabilities `capture` controls capture-lock ownership: - `on` uses ordinary contention. - `priority` takes priority intent before contending. - `off` never attempts ordinary capture ownership. `on_capture_loss: "recover"` performs terminal cleanup after capture-owner loss and exclusive presence. It requires `capture: "off"`. The default is `none`. Controller permissions are independent of runtime capture. For example, a capture-disabled connection can still vote, answer storage operations, or route ASP calls if those controller capabilities are enabled. ## Common combinations Terms such as regular, host-like, companion-like, witness-like, and ASP-client-like are explanatory shorthand only. They do not appear in config, the protocol, persistence, or runtime state. The controller identifies the current writer solely from the authoritative lease record. Local write admission additionally requires the capture lock, a usable image, and the writer-admission gate. --- # Object storage Source: https://sqlstyle.com/docs/object-storage Markdown: https://sqlstyle.com/docs/object-storage.md SQLStyle writes full snapshots and incremental recovery history to your S3-compatible bucket. Your application data stays in infrastructure you control. See [backup and recovery history](/docs/backup-and-recovery-history) for the capture, catalog, upload, and controller-fencing path behind these objects. ## Create a storage profile The hosted CLI stores the complete non-secret provider configuration. The authenticated SQLStyle account and selected project are attached automatically; the profile also stores the provider, bucket, region, endpoint, access-key ID, retention policy, and provider-derived checksum mode. ```sh sqlstyle storage profiles create \ --name production \ --provider aws_s3 \ --bucket app-dbh \ --region us-east-1 \ --access-key-id "$AWS_ACCESS_KEY_ID" \ --retention 15m/1h/4h/24h ``` The built-in provider keys are `aws_s3`, `cloudflare_r2`, `tigris`, and `backblaze_b2`. An account administrator can configure additional provider keys for other S3-compatible services. Some providers identify the provider account through the endpoint. For example, a Cloudflare R2 profile stores the Cloudflare account ID as part of its endpoint: ```sh sqlstyle storage profiles create \ --name production-r2 \ --provider cloudflare_r2 \ --bucket app-dbh \ --endpoint "https://${CLOUDFLARE_ACCOUNT_ID}.r2.cloudflarestorage.com" \ --access-key-id "$R2_ACCESS_KEY_ID" \ --retention 15m/1h/4h/24h ``` The CLI's optional `--account-id` selector refers to the SQLStyle product account. It is not an AWS, Cloudflare, Tigris, or Backblaze account identifier. Normally the authenticated CLI account supplies it automatically. ## Keep the secret local The secret access key always remains a runtime or toolbox input: ```sh export SQLSTYLE_STORAGE_SECRET_ACCESS_KEY='...' ``` The secret is not uploaded to SQLStyle servers. Treat it as an application deployment secret and scope it to the required bucket and operations. Hosted releases contain the SQLStyle backend address; it is not application configuration. The database-scoped API key and database name select the hosted profile, which supplies defaults for the storage configuration: ```js import { DatabaseSync } from "@sqlstyle/database"; const db = new DatabaseSync("app.db", { sqlstyle: { name: "app/main", apiKey: process.env.SQLSTYLE_API_KEY, storage: { secretAccessKey: process.env.SQLSTYLE_STORAGE_SECRET_ACCESS_KEY, }, }, }); ``` Provider, account namespace, database ID, bucket, access-key ID, region, endpoint, retention, and checksum mode are fetched from the profile. Any field set in `storage` overrides the corresponding profile value. The secret has no profile value and must always be supplied locally. ## Retention syntax Retention is an optional slash-separated sequence of increasing intervals: ```text 15m/1h/4h/24h ``` Supported units are seconds (`s`), minutes (`m`), hours (`h`), and days (`d`). The final interval is the cutoff. Retention is recovery-plan aware. It does not delete objects merely because they are old: - objects beyond the recovery state accepted by SQLStyle are not selected for a normal restore; - deletion waits for a surviving verified recovery plan; - connected replica watermarks can prevent removal of required local history; - manual deletion is a preview unless explicitly executed. ## S3 behavior The storage implementation: - retries PUT and multipart operations; - uses multipart upload for large objects; - validates size and checksum during reads; - paginates object listings; - uses conditional deletion when safely removing a selected object. ## Validate recovery Do not treat upload success alone as a restore test. Use the restore CLI: ```sh sqlstyle database app/main recovery status sqlstyle database app/main recovery verify --mode plan sqlstyle database app/main recovery verify --mode checksum ``` See [restore and recovery](/docs/restore-and-recovery) for restore targets and verification modes. --- # Point-in-time recovery Source: https://sqlstyle.com/docs/restore-and-recovery Markdown: https://sqlstyle.com/docs/restore-and-recovery.md SQLStyle writes full snapshots and incremental history from the application process to your S3-compatible object storage. A snapshot establishes a recovery base; incremental history records the transactions that follow it. Restore planning combines the recovery state accepted by SQLStyle with the objects in your storage. It selects an exact recovery chain without trusting a stale local database catalog. [Backup and recovery history](/docs/backup-and-recovery-history) explains how DBH segments, snapshots, and the controller's accepted frontier are produced. ## How recovery history is accepted Only the instance with current primary authority may advance the accepted recovery state. If authority moves during failover, fencing prevents a late upload or report from the former primary from extending that state. The objects remain in storage you control. SQLStyle records which snapshot and incremental history are authoritative, then verifies sizes, checksums, and transaction coverage before materializing a database. ## Latest and point-in-time recovery Latest recovery selects the newest complete chain accepted by SQLStyle. Point-in-time recovery starts from a compatible snapshot and replays history only through the requested commit timestamp. Both modes use the same inventory and verification commands. Selecting a time does not require the original application instance or its local database files. ## Inspect recovery state ```sh export SQLSTYLE_API_KEY='...' export SQLSTYLE_STORAGE_SECRET_ACCESS_KEY='...' sqlstyle database app/main recovery status sqlstyle database app/main recovery inventory ``` `status` reports authoritative and recoverable frontiers, recovery-point information, and issues. `inventory` lists candidate snapshot and DBH objects. ## Verify before restore ```sh sqlstyle database app/main recovery verify \ --mode plan sqlstyle database app/main recovery verify \ --mode checksum ``` `--mode` accepts `plan`, `checksum`, or `full` and defaults to `plan`. ## Restore the latest accepted state ```sh sqlstyle database app/main restore --latest ``` Without `--output`, SQLStyle writes `app_main.sqlite` in the current directory. It replaces `/` and `\` with `_` when that produces a portable filename. Names containing spaces, Unicode, or reserved or hidden components use unpadded URL-safe Base64 instead. SQLStyle never overwrites an existing file. Choose another destination when needed: ```sh sqlstyle database app/main restore \ --output ./restored.sqlite ``` ## Point-in-time restore ```sh sqlstyle database app/main restore \ --at \ --output ./restored.sqlite ``` Latest restore can begin from a raw SQLite snapshot or a DBH snapshot. Point-in-time restore requires a DBH snapshot base so replay can stop at the requested commit timestamp. ## Progress and automation Use `--progress auto|plain|json|none`. Progress is written to standard error; the final table or JSON result remains on standard output. Use `--out json` or `--json` for scripts. Keep the database-scoped API key and object-storage secret out of command history by using environment variables or key files. ## Safe deletion Manual deletion is a preview unless `--execute` is provided: ```sh sqlstyle database app/main recovery delete \ --object ``` Execution rejects the entire requested batch unless every selected object is superseded by a surviving verified recovery plan. There is no force-bypass flag. --- # Status and SQL surfaces Source: https://sqlstyle.com/docs/status-and-sql-surfaces Markdown: https://sqlstyle.com/docs/status-and-sql-surfaces.md Inspect SQLStyle through the SQLite connection your application already has. These tables and functions report authority, nodes, backups, and durability waits. This reference describes the table names, column schemas, scalar-function overloads, SQLite result codes, and direct-only restrictions in the supported Bedrock build. Logical replication remains a separate capability and does not change this SQL surface. ## Authority status ```sql SELECT authority, integrity_mode FROM sqlstyle_status; ``` `authority` is `held` or `unheld`. The live lease, capture lock, usable image, and writer-admission facts still determine whether a write is accepted. `integrity_mode` is `standard`, `lightspeed`, or `permissive` and reports the database policy published by the active runtime owner. The status surface intentionally does not expose transitional controller state as an application contract. ## Topology ```sql SELECT * FROM sqlstyle_topology; ``` Topology describes the controller-backed view available to the runtime. Treat it as operational information, not as independent permission to write. ## Backup history ```sql SELECT rowid, * FROM sqlstyle_backups ORDER BY rowid; ``` `sqlstyle_backups` projects successful retained records from the durable backup catalog. Its implicit `rowid` is the persisted catalog row identifier. Request a backup from a managed connection with object storage enabled: ```sql SELECT sqlstyle_backup(); SELECT sqlstyle_incremental_backup(); ``` These functions synchronously coordinate capture and upload, then wait until the controller has accepted the uploaded transaction frontier. The wait is bounded and interruptible. They must be used as standalone statements on a direct managed connection. ## DBH capture statistics ```sql SELECT * FROM sqlstyle_dbh_stats; ``` The single row exposes cumulative and maximum timing, byte, seal, failure, and handoff counters for the local DBH workers. A managed database without active capture reports zero-valued counters rather than changing the schema. ## Durability waits Wait for a local transaction frontier when an application workflow requires it: ```sql SELECT sqlstyle_wait_for_txn(primary_token, txn, timeout_ms); ``` Use `primary_token = 0` for a local-frontier wait. A nonzero token makes the wait lineage-safe across primary changes. A zero transaction succeeds immediately, and a zero timeout waits without a deadline. Run waits and authority requests as standalone statements outside a transaction. They return `SQLITE_MISUSE` in an active transaction, preventing a wait from retaining locks needed by the operation it is waiting for. Transaction zero requires no wait and succeeds immediately even inside a transaction. Read the transaction assigned to the calling connection's latest successful commit: ```sql PRAGMA sqlstyle_last_commit_txn; ``` The value is connection-local. It is zero before the first successful commit and on unmanaged connections. Wait for a required number of replicas: ```sql SELECT sqlstyle_wait_for_replica_watermark( txn, replica_count, timeout_ms ); ``` The transaction is a SQLStyle transaction frontier and `replica_count` must be positive. A positive timeout is bounded in milliseconds; zero waits without a deadline. Pass `NULL` for `txn` to use the calling connection's most recently committed SQLStyle transaction. Wait for the local authority state to change: ```sql SELECT sqlstyle_wait_for_authority_change(observed_authority, timeout_ms); ``` ## Live read snapshots Pin the calling connection's current committed snapshot: ```sql SELECT sqlstyle_read_begin(); SELECT * FROM orders; ROLLBACK; ``` `sqlstyle_read_begin()` starts a read transaction and returns its SQLStyle transaction number. On another connection to the same managed database, join that snapshot by binding the returned number: ```sql SELECT sqlstyle_read_begin_at(?1); SELECT * FROM orders; ROLLBACK; ``` `sqlstyle_read_begin_at()` returns `1`. At least one reader must keep the snapshot open while another connection joins it. After its last reader ends the transaction, joining returns `SQLITE_ERROR_SNAPSHOT`; this API does not restore historical data. End each reader with ordinary `COMMIT`, `ROLLBACK`, or connection closure. To observe the current readable transaction without retaining a snapshot: ```sql SELECT sqlstyle_current_read_txn(); ``` All three functions require a standalone statement on a managed connection outside a transaction. Snapshot numbers are non-negative SQLite integers. The functions are `SQLITE_DIRECTONLY` and cannot run through a view or trigger. ## Requesting authority ```sql SELECT sqlstyle_request_authority(timeout_ms); ``` This asks the controller to move writer authority to the requesting connection and waits for the grant. It returns immediately when the local runtime already holds authority. Authority movement is fenced by the controller. A request is not permission to write until `sqlstyle_status.authority` is `held`. ## Shutdown observation Shutdown operation handles remain a native API because they must survive the calling connection closing. Begin shutdown while a connection is alive, close the application's connections, then wait on the operation for resource release. A statement running on the database cannot wait for every connection to close while retaining its own connection. --- # What consumes controller-state writes Source: https://sqlstyle.com/docs/controller-state-writes Markdown: https://sqlstyle.com/docs/controller-state-writes.md SQLStyle counts a controller-state write when an action changes coordination data held by the hosted controller. The allowance is shared by every controller in the account for the current usage period. One action can consume more than one write. Database creation performs several controller updates, and account-level changes can apply to more than one controller. SQL transactions and replication traffic do not consume the allowance. ## Database and account actions | Action | Controller-state writes | | --- | --- | | Create a database | Normally **4** writes to provision its controller. | | Retry failed database provisioning | Successful controller updates performed by the retry count again. The total depends on where the earlier attempt failed. | | Enable an analytics collection | No additional controller-state writes; analytics uses the application database. | | Create a database-scoped key | **1** write for the database controller. | | Revoke a database-scoped key | **1** write for each controller from which the key is removed. Revoking an already-revoked key does not add another write. | | Enable or disable database TLS | **1** write for each active application or analytics controller in the account. Reapplying the current setting does not count. | | Delete a database | **0** writes. SQLStyle revokes its database-scoped keys in the backend, records existing usage, and deletes its controllers wholesale. Earlier usage remains counted. | Creating storage profiles, listing resources, reading status, and viewing account usage do not write controller state. ## Application and recovery actions | Action | Controller-state writes | | --- | --- | | Open an application instance with `promotable: true` | Before requesting primary authority, the instance checks the authoritative recovery frontier. That check consumes **1** write only when it publishes newer uploaded progress. Assigning the instance as the first primary consumes **1** more. | | Release or transfer primary authority | A completed release or direct handoff consumes **1** write. | | Restart the current primary | If the controller records the disconnect and later resumes the same primary, each persisted authority transition consumes a write. | | Run `sqlstyle database NAME restore` or a `recovery` command | The command consumes **1** write only when its fenced read advances the accepted recovery frontier. | | Run `sqlstyle analytics export` or `view` with a database source | The export consumes **1** write only when its fenced read advances the analytics database's accepted recovery frontier. | The [object-storage read fence](/docs/backup-and-recovery-history#the-controller-fences-authoritative-storage-progress) used by these actions is conditional. If no primary is live, or the primary's uploaded frontier is not newer than the value already held by the controller, the controller returns the existing value without a write. ## Automatic failover Failover can consume several writes without a dashboard or CLI action. The controller can write when it places the current primary in recovery, reserves a replacement, and completes the replacement's promotion. Heartbeats and health reports do not count on their own; the authority changes they cause do. ## What remains unmetered These operations do not consume controller-state writes: - SQLite reads, writes, transactions, checkpoints, and Application Stored Procedure calls - capture, replication, acknowledgements, DBH catch-up, and rsync replacement - DBH generation, snapshots, and object-storage uploads - established controller connections, topology messages, heartbeats, and health reports that do not change primary authority - controller status, usage collection, and usage enforcement For example, a new database normally uses **4** writes during provisioning, creating its first runtime key adds **1**, and assigning the first application instance as primary adds **1**. Normal SQL and replication traffic after that does not move the counter. --- # CLI reference Source: https://sqlstyle.com/docs/cli-reference Markdown: https://sqlstyle.com/docs/cli-reference.md Create a controller with an explicit allocation, for example `sqlstyle database app/main create --write-allowance 1000`. Forking also requires `--write-allowance`. The account must have enough unallocated controller writes. Use the web dashboard for signup and billing. Use the CLI to manage databases, storage, keys, recovery, and analytics. ## Command groups | Command | Purpose | | --- | --- | | `sqlstyle auth` | Sign in, inspect, or remove the local account key | | `sqlstyle credential` | Inspect and test the selected local credential store | | `sqlstyle toolbox` | Enable and use the optional encrypted database organizer | | `sqlstyle database` | Create, list, delete, and restore logical databases | | `sqlstyle storage profiles` | Create and list non-secret object-storage profiles | | `sqlstyle keys` | Create, list, and delete database-scoped API keys | | `sqlstyle database NAME recovery` | Inspect, verify, and safely delete backup objects | | `sqlstyle analytics` | Download or view centralized analytics | | `sqlstyle convert` | Convert an offline database between Bedrock and SQLite | | `sqlstyle version` | Print the CLI version and bundled database core | Append `--help` to a group or subcommand for its current flags. ## Encrypted Toolbox organizer Toolbox creates no internal database or configuration until explicitly enabled: ```sh sqlstyle toolbox setup --config devstack.json5 sqlstyle toolbox import bootstrap.export --secret-file secret sqlstyle toolbox status sqlstyle toolbox sync sqlstyle toolbox dump sqlstyle toolbox dump --include databases,storage-usage,files --database app/main --refresh sqlstyle toolbox dump --include storage-costs --database app/main --config database.json5 ``` When enabled, database, key, recovery, and remote analytics operations require a successful Devstack synchronization and are recorded in the encrypted catalog. Use `sqlstyle toolbox database credentials NAME` to manage saved controller and object-storage access. `toolbox dump` emits one JSON document with `databases` and `errors` arrays. It includes database details and cached storage usage by default. Select data with `--include databases,storage-usage,files,storage-costs`; explicit selections replace the defaults. Repeat `--database NAME` to select several saved databases. `--refresh` scans the selected databases' storage and saves complete inventories. Without it, usage and file records come from the saved scan; Toolbox still synchronizes its catalog. File paths are relative to the reported storage prefix, and file records include byte size, ETag, and modification time. Storage credentials and API-key values are omitted. Missing cached measurements have `scan.status` set to `not_cached`; failed reads return structured errors and a nonzero exit status. `storage-costs` reads fresh analytics using a saved controller key and `--config FILE` (or `SQLSTYLE_CONFIG`). The configuration supplies each database's inline analytics, storage access, and encryption keys. It reports current UTC calendar-month request/read and capacity costs, projected month-end totals, and coverage under `storage.costs`. Amounts use integer nano-USD; unavailable estimates are null with a reason. These are provider list-price estimates, excluding discounts, credits and taxes. `--refresh` applies to usage and file inventories; costs always use a fresh analytics snapshot. ## Offline database conversion Each toolbox bundles `sqlstyle` and a sibling `sqlite3` linked to the same database core. Convert a database in place, or write a new database without changing the source: ```sh sqlstyle convert bedrock app.db sqlstyle convert sqlite app.db --output app-sqlite.db ``` Bedrock uses WAL2, and SQLite output uses WAL without a SQLStyle database identity marker. Conversion requires the database to be offline and refuses existing output or companion paths. ## Authentication ```sh sqlstyle auth login sqlstyle auth status sqlstyle auth logout ``` Browser login returns an account-scoped credential to the waiting CLI. The credential is stored locally; it is not exposed to frontend JavaScript. ## Databases ```sh sqlstyle database app/main create --write-allowance 1000 sqlstyle database list sqlstyle database ls sqlstyle database app/main delete ``` Creation also accepts account, project, and storage-profile selectors. `list` and `ls` select the list operation when used alone or with list flags. When followed by an action, either word is a database name: `sqlstyle database ls create` creates a database named `ls`. Restore the latest accepted state to the current directory: ```sh sqlstyle database app/main restore ``` For `app/main`, the default output is `app_main.sqlite`. Use `--output PATH` to select another destination. SQLStyle refuses to replace an existing file. Recovery inspection and maintenance are scoped to the same database name: ```sh sqlstyle database app/main recovery status sqlstyle database app/main recovery inventory sqlstyle database app/main recovery verify --mode plan sqlstyle database app/main recovery delete --object ``` ## Storage profiles ```sh sqlstyle storage profiles create \ --name production \ --provider aws_s3 \ --bucket app-dbh \ --region us-east-1 \ --access-key-id "$AWS_ACCESS_KEY_ID" sqlstyle storage profiles list ``` Profiles contain the complete non-secret provider configuration: the SQLStyle account and project association, provider, bucket, region, endpoint, access-key ID, retention, and checksum mode. Supply only the secret access key locally to runtime, restore, or analytics commands. For providers such as Cloudflare R2, the provider account ID is encoded in the stored endpoint; `--account-id` selects a SQLStyle account. ## Database-scoped keys ```sh sqlstyle keys create \ --database app/main \ --label application-runtime sqlstyle keys list --database app/main sqlstyle keys delete --database app/main --key-id ``` When scopes are omitted, key creation uses the default controller-connect, database-read, and config-read scopes. ## Structured output Resource commands generally support `--out table|json`; some accept `--json` as a shortcut. Key creation defaults to secret-only output and can emit environment format where supported. For automation, keep progress on standard error and final JSON on standard output.