SQLite storage and disaster recovery

• Status: Specification v0.5 — September 2026. The SQL and model tests are executable; database commands, services, migrations, and hardware recovery are not implemented.
• Authority: This document owns SQLite schemas, connection policy, reconstruction records, and database maintenance. STATE-AND-RECOVERY owns artifact identity, trust, privilege, and the transaction state machine. KEY-RUNBOOK owns signing authority and compromise response.

1. Ownership and authority

SQLite stores projections of durable decisions and verified objects. It does not establish trust, authorize privileged effects, or replace recovery journals. Each owner has its own files and reconstruction stream. The offline root Pi keeps its existing ceremony records; it gains no database. Farm agents exchange the existing signed job/result protocol and never open the coordinator's database.

Diagram source

flowchart LR
  U[Prefix owner] --> C[Client state]
  U --> K[Disposable client cache]
  T[Authoritative trust records] --> K
  J[Prefix records and journals] --> C
  H[Authorized system helper] --> S[Protected system state]
  R[Protected trust and journals] --> H
  C -. untrusted plan input .-> H
  A[Agent job and result protocol] --> F[Farm coordinator]
  E[Retained plans and gate evidence] --> F
  F --> P[Publisher]
  P --> N[Release signer]
  N --> P
  O[Offline root ceremonies] --> N
  O --> P

Arrows show data flow, not inherited authority. The helper authenticates a complete closure independently. The publisher and signer independently enforce authorization, gates, expected base, and environment. Their private keys and credentials remain outside SQLite and outside database backup sets.

1.1 Files, owners, and creation

<system> is /Library/Application Support/aslice/system. <coordinator>, <publisher>, and <signer> are absolute local service-state roots selected during provisioning and recorded in owner-controlled configuration. They must be distinct directories, even on a shared machine. A database path is never a network share.

Directories use owner-only access (0700), database and sidecar files 0600, with equivalent ACL restrictions. Root-controlled paths have no unprivileged-writable ancestor. Check ownership, regular-file type, canonical location, and symlink traversal before opening any database or sidecar. Database inspection does not grant another role's permissions. Root launches the fixed helper vocabulary, never arbitrary user SQL in the privileged process.

For protected inspection, the helper's fixed export operation produces only the authorized diagnostic projection in a private inspection directory for the caller. The caller runs SQL in an unprivileged, resource-limited inspection process. Service operators similarly query owner-approved exports; they do not submit SQL over the job/result or signing protocols. Exports retain role/instance/head identity and their authorization scope, and are removed after inspection.

The paths above are logical active slots. Versioned copies live under the same owner's db/versions/<migration-id>/; activation of the slot is journaled. Cache versions live below cache/db/versions/. Schema creation alone does not initialize an installation: the owner must insert database_identity before activation.

1.2 Identity and reconstruction

The client-cache and coordinator schemas have user_version = 3; the other four roles remain at 2. Their application_id values, in table order above, are 1095977985 through 1095977990. database_identity binds the role, schema version, immutable 32-lowercase-hex instance identifier, and stable owner identity. For clients the instance is the prefix identity; protected state has a separate root identity. Services retain their provisioned identity across recovery. Owner identity binds the provisioned principal and host/prefix record, not a UID alone. Restoring onto a new host requires an authorized ownership mapping recorded in the restore journal. Copying a database never creates a new prefix identity.

None of these sources can regenerate lost unpublished payload bytes or missing before-images. A retained manifest identifies missing bytes; it is not a backup of them. Git plus build results cannot reconstruct quarantine decisions or signing reservations.

2. Schema conventions

The six SQL companions contain the complete DDL: columns, types, keys, constraints, indexes, and views. Run a companion only against an empty staging database. All tables are STRICT. Primary and foreign keys deliberately include repository and environment wherever a relation is scoped; a dev row cannot stand in for prod. Repository strings are existing repository namespaces, resolved against retained trust identity bindings; environments are dev, staging, and prod. Reusing a namespace for a different trust identity requires explicit recovery, never an SQL rename. Artifact identities remain sha256: plus 64 lowercase hex digits; build_id remains 64 lowercase hex digits, as defined by STATE-AND-RECOVERY §1. These schemas add no fields to public manifests, indexes, locks, or TUF metadata.

Every role contains five common tables:

objects is a reference registry, not a blob store. Resolve digests through the owner's fixed content-addressed object roots; no row supplies an arbitrary filename. Store exact signed bytes, large manifests, backups, recipes, solver inputs/results, and evidence outside SQLite. Hash and measure bytes again when consumed. Kind is descriptive; the consumer validates the required format and semantic role. A digest may serve multiple uses. Length and checksum do not authenticate a source.

SQL enforces structural invariants. Owner code additionally validates canonical manifest hashes, package names against existing schemas, complete closures, DAG acyclicity, required gate sets, monotonic reservations, state transitions, signatures, and compare-and-swap conditions. Foreign keys cannot prove those properties. No writer uses INSERT OR REPLACE to hide a conflicting identity. An identical retry compares the complete immutable record; different bytes at the same identity stop replay and retain both records as evidence.

3. Client state

Diagram source

erDiagram
  scopes ||--o{ artifacts : contains
  artifacts ||--o{ bindings : consumes
  artifacts ||--o{ bindings : provides
  profiles ||--o{ generations : retains
  generations ||--o{ members : includes
  artifacts ||--o{ members : identifies
  generations ||--o| active_generations : activates
  profiles ||--o{ requests : records
  requests ||--o| holds : holds
  profiles ||--o{ runtime_defaults : selects
  profiles ||--o{ profile_priorities : prefers
  artifacts ||--o{ gc_roots : retains
  objects ||--o{ history : explains

Bindings join exact artifacts, including explicit cross-repository dependencies. Each binding retains its dependency repository, environment, and qualified name; there is no inferred cross-environment fallback. Manifests, rather than matching build_id values, authorize the binding.

User choices describe current intent independently of generation manifests. Rollback applies the recorded target managed state under existing command semantics; it does not erase later history. before_digest and after_digest may be equal for a no-op and null for a failed operation without an established state. The history sequence identifies its terminal record, not wall-clock ordering.

An operation that stops with needs-attention retains that history entry. Its later authorized resolution has a new operation ID and references the earlier record; it does not replace the earlier outcome.

Representative query (also exercised by the tests):

SELECT profile, repository, name, artifact_id, on_request
FROM installed ORDER BY profile, repository, name;

4. Disposable client cache

Diagram source

erDiagram
  scopes ||--o{ snapshots : scopes
  snapshots ||--o| current_snapshots : selects
  snapshots ||--o{ packages : indexes
  solve_cache ||--o{ solve_inputs : binds
  snapshots ||--o{ solve_inputs : supplies
  scopes ||--o| trust_projections : projects

All cache rows are disposable; routine cleanup protects current snapshots and active work under §10.2. No cached verdict permits stale metadata, stale policy, or offline use without the retained verification receipts required by STATE-AND-RECOVERY §8. Search uses the indexed name column; FTS is not a schema dependency.

SELECT repository, environment, name, summary
FROM search_packages WHERE name LIKE 'python%' ORDER BY repository, environment, name;

5. Protected system state

Diagram source

erDiagram
  closures ||--o{ closure_members : contains
  scopes ||--o{ closure_members : scopes
  prefixes ||--o{ prefix_references : retains
  closures ||--o{ prefix_references : referenced
  prefixes ||--o{ effects : owns
  closures ||--o{ effects : executes
  effects ||--o{ services : registers
  objects ||--o{ recovery_receipts : proves

Effect and service rows describe the current managed footprint. Historical fingerprints and declaration versions remain in durable records after replacement or removal. Before-image objects include file metadata and an explicit absent-file marker when appropriate. Resolved backup retention follows existing policy; unresolved operations retain all before-images. No new database changes privileged consent or system-volume requirements.

SELECT label, prefix_id, closure_digest, state FROM managed_services ORDER BY label;

6. Farm coordinator

Diagram source

erDiagram
  plans ||--o{ jobs : schedules
  job_kinds ||--o{ jobs : classifies
  jobs ||--o{ job_dependencies : depends
  jobs ||--o{ job_requirements : requires
  workers ||--o{ worker_capabilities : proves
  workers ||--o{ attempts : runs
  jobs ||--o{ attempts : retries
  attempts ||--o| results : returns
  jobs ||--o{ quarantine : decides
  jobs ||--o{ gate_evidence : evaluates

| attempts | Dispatch/heartbeat/expiry serialize under the coordinator writer. One active attempt per job; tokens are unique and expiry uses the coordinator's clock. Record attempt and lease before dispatch. After recovery mark every inherited active lease expired before redispatch; never trust restored clock-relative liveness | | results | Result admission authenticates worker/attempt/job bindings and inserts once per job-manifest hash. Byte-identical duplicates return the retained result. Conflicting duplicates are retained in evidence and quarantined, never overwrite the accepted result. Expired attempts can supply evidence but cannot complete a replacement lease | | quarantine, gate_evidence | Gate processing writes evidence references and append-only quarantine decisions together. Only an authorized release decision clears a hold. Required gates derive from retained policy, so absent rows mean pending. Signed job results alone do not establish a passing gate |

Compact scheduling, attempts, result identities, quarantine decisions, and gate receipts survive indefinitely in records. Old SQL jobs may be archived only behind a verified checkpoint; unresolved jobs and evidence remain available. Build logs and payloads follow their existing retention policy. The coordinator's success is not publisher authorization.

SELECT job_digest, attempt, worker_id FROM lease_status
WHERE expires_at <= :now ORDER BY job_digest;

7. Publisher

Diagram source

erDiagram
  scopes ||--o{ candidates : authorizes
  candidates ||--o{ promotions : source_and_destination
  candidates ||--o| publication_queue : queues
  scopes ||--o| fencing : serializes
  candidates ||--o{ timestamp_reservations : reserves
  timestamp_reservations ||--o| activations : publishes
  activations ||--o| acknowledgements : reconciles

All candidate bindings, reservations, fence epochs, promotions, and publication evidence are retained indefinitely in compact records. SQL history may move behind verified checkpoints but active queue/base and all version high-water projections remain available. Current and in-flight immutable public objects follow repository retention. Compare returned signer state with retained public records on every recovery; uncertain latest state stops publication.

SELECT repository, environment, queue_sequence, state, snapshot_digest
FROM publication_status ORDER BY repository, environment, queue_sequence;

8. Release signer

Diagram source

erDiagram
  scopes ||--o{ candidates : scopes
  candidates ||--o{ reservations : reserves
  reservations ||--o| signed_metadata : signs
  candidates ||--o| signing_outcomes : concludes
  candidates ||--o{ slice_signatures : retains
  candidates ||--o| publication_acknowledgements : observes

Keep every reservation, candidate binding, outcome, exact signed object, and acknowledgement indefinitely, including rejected and signed-but-unpublished work. Private key operations remain in the existing signer boundary. No key blob, unlock secret, or key-backup path is a column. Root signing remains offline and is not represented by this database.

SELECT repository, environment, metadata_role, version
FROM version_high_water ORDER BY repository, environment, metadata_role;

9. Durable records and replay

Each durable role has an owner-controlled records/ stream beside db/; existing transaction journals and trust/ remain authoritative in their own locations. Records describe logical state transitions, never raw SQL. The cache has no stream. An independent recovery set outside the active prefix retains reconstruction records, trust evidence, transaction receipts, and manifests, with protected copies for privileged evidence and an export for disk-loss recovery. Application Support directories are the approved location; §9.3 defines the ordered multi-copy protocol. The adjacent stream alone cannot satisfy prefix-loss recovery. See STATE-AND-RECOVERY §5.1. Protect streams with the same ownership boundary as their database and retain compact history indefinitely. Checkpoints accelerate replay; they do not authorize discarding compact records.

A record is canonical RFC 8785 JSON with these fields: format (1), role, instance_id, sequence (positive safe integer), previous (prior record digest, null only for sequence 1), operation_id (32 lowercase hex digits), phase, command, occurred_at (UTC epoch seconds), scope (repository/environment or null for an instance-wide operation), before, after (logical state digests or null), changes, and evidence. changes is an ordered array of typed domain operations whose before/after references bind complete canonical values, with null as the explicit absence/deletion marker; evidence is a sorted, duplicate-free array of digest/byte-length references. Record identity is SHA-256 of the canonical bytes, stored separately from the bytes themselves. Names are <sequence>-<digest-hex>.json. Initialize the stream with owner/instance identity and empty state before admitting mutations.

Domain operations cover generation/member/binding changes, request marking and removal, holds, defaults, profile priorities, GC references, effect/service and prefix registration, farm enrollment/plan/attempt/result/gate/quarantine changes, candidate/promotion/queue/fence changes, reservations, signing outcomes, and activation/acknowledgement receipts. Each kind has a versioned decoder and validates against its owning contract; unknown kinds or formats stop replay. Trust changes reference independently authenticated trust records rather than embedding a grant in an ordinary choice record. Session selections and project files are not replayed.

The writer serializes stream allocation under the role lock. It writes a temporary record, flushes it, renames to its final name without replacement, flushes the directory, and advances a flushed stream-head record containing sequence/digest. Validate both the head and all finalized tail files: a crash between rename and head update may leave a valid contiguous tail to recover. Duplicate sequence with different bytes, gaps, broken predecessors, unsupported formats, or an operation with incompatible terminal outcomes produce needs-attention. Checksums detect damage; independently retained heads/backup manifests detect a truncated tail. Local checksums alone cannot prove that an attacker did not rewrite user-owned state.

9.1 Commit boundaries

Diagram source

sequenceDiagram
  participant W as Owner writer
  participant J as Durable records and journal
  participant F as Pointers and external effects
  participant D as SQLite projection
  W->>J: Prepared intent and verified before-images, flush
  W->>F: Apply under expected fingerprints, activate pointers
  W->>D: BEGIN IMMEDIATE, project tentative after-state, COMMIT
  W->>F: Reconcile managed state
  W->>J: Committed terminal record, flush
  W->>D: History and replay head in one transaction
  W->>F: Bounded service health checks, retain mutation ownership

Preserve prefix-then-system lock ordering for operations touching both owners. Service roles serialize through their own owner locks and existing publication protocol; there is no transaction spanning WAL databases. Cross-owner journals bind the same operation and participant receipts. Missing participants prevent success.

The durable terminal record is the commit decision. A choice-only operation uses the same prepared/terminal discipline without generation or pointer changes. Before returning success, both terminal record and final SQL projection must be durable. SQL may temporarily lead or lag the record stream; every opener checks for an unresolved journal or head mismatch before exposing shim/installed state. Do not expose tentative state as committed. A mismatch blocks affected selections, not every runtime: established unaffected selections and verified closures remain usable under STATE-AND-RECOVERY §5.2. Keep session/project/default precedence and fail promptly if required state is unavailable; no implicit fallback is introduced. The separately retained committed execution catalog and affected-selection gates supply that read path independently of SQLite; §9.3 orders its durable publication. Diagnostic reads report mismatches. If the terminal commit is absent, recovery reverses effects and tentative choices under the existing fingerprint rules, except for explicit finalization of a verified protected-volume pending-reboot intent under SYSTEM-VOLUMES §4; committed operations reconcile their after-state. SQL rollback alone does not undo filesystem changes.

Reservations and fence allocations are special irreversible decisions: their flushed allocation records consume the number even if the enclosing operation never commits. Append these before any signature or writer handoff. An abandoned candidate records consumption; replay never releases its versions. Quarantine holds are similarly retained until an explicit authorized release record. Missing gate evidence remains pending.

Contention and cancellation follow §10.1, including committed operations whose SQL projection remains incomplete.

9.2 Replay and checkpoints

Replay verifies identity, format, sequence, predecessor, checksum, byte length, and trusted external references before applying a record. It applies one complete logical transition and its head in one SQL transaction. Replaying an already applied sequence with the same digest is a no-op; another digest is a conflict. Require the transition's before-state to match the reconstructed state. Terminal history inserts once by operation ID. A prepared operation without a terminal decision is unresolved, not silently dropped. Rebuild into staging and run recovery against real pointers and fingerprints before activation.

A checkpoint binds role/instance/schema, manager and SQLite build identities, sequence/head digest, logical projection digest, checkpoint object digest/length, and the retained-record range it covers. Authenticate its provenance through the owner's independently retained backup inventory; a checkpoint supplied by an untrusted database is not evidence. Verify its prefix records and replay its suffix. Unknown or conflicting high-water provenance forbids signer/publisher resumption. No replay reconstructs a TOFU decision or lowers retained trust versions.

9.3 Independent records and durable copy updates

The outside-prefix owner-controlled stream is the transaction decision history; the adjacent prefix stream is a mirror, and SQLite is a projection. A transaction has one operation ID, one ordered history, and one terminal decision. Protected participants keep their own root-controlled receipts bound to that ID and history; a user record cannot confer protected authority. Prefix initialization binds the recovery location and owner identity. Independently retained checkpoints and export inventories establish known heads; a stream's self-reported head alone cannot prove that history was not truncated or rewritten.

Each finalized record uses the temporary-file, flush, no-replacement rename, and directory-flush sequence in §9. A head advances only after its referenced records and required objects are durable. Validate finalized tails as well as heads after interruption. Apply the following order under effective mutation ownership:

1. Allocate the operation and sequence from the validated outside-prefix history. Inventory the actual base and required participants. Persist complete intent, authorization references, before-images, object inventory, and the affected execution-catalog gate outside the prefix. Persist required privileged evidence under the protected owner and obtain its bound preparation receipt. Mirror preparation into the prefix. No live write precedes these required flushes.
2. Apply each journaled effect under its expected fingerprint. Persist completion receipts before advancing progress. The protected helper independently verifies authorization and actual state and writes its own receipt before acknowledging a privileged step. A lost acknowledgement is resolved by querying that receipt and inspecting state; it does not authorize repeating an external effect blindly.
3. Reconcile pointers and effects, record the tentative projection, and prepare the next execution catalog. Require durable receipts from every participating owner showing the verified after-state and prepared commit boundary. These receipts are not a terminal commit by themselves. Flush the single commit decision in the outside-prefix history, binding all participant receipts, state and catalog digests. This is the commit point; a crash before it invokes pre-commit reversal (with the explicit protected-volume finalization exception in §9.1), and a crash after it preserves the commit. Unknown or inaccessible decision evidence permits neither guessing rollback nor declaring success.
4. Mirror the decision into the prefix, acknowledge it in protected records through the helper, and reconcile the final projection/head and catalog selection. The helper accepts only a decision matching its own authorized intent and receipts; user-supplied state never substitutes for protected evidence. Clear affected catalog gates only after their state agrees. Do not report full completion until required mirrors, participant acknowledgements, and projections are durable. Mirror failure after the commit point reports committed with recovery pending.
5. Run bounded service checks while retaining mutation ownership. Append check outcomes and saved recovery choices without changing the commit decision. Report health failure or incomplete verification explicitly. Release ownership only after helpers stop writing and the operation reaches a safe stopping point.

An unambiguous verified contiguous suffix repairs a lagging copy automatically. Compare owner/instance, sequence, predecessor, operation, object digests, known independent heads, and protected counterpart receipts. Never choose by timestamp, filename ordering, or copy majority. A gap, incompatible terminal decisions, missing required evidence, or an unexplained newer head preserves both copies and blocks the affected repair. If the outside-prefix copy is lost, a surviving mirror is usable only after those same independent checks establish the decision history. Without them, use guided evidence recovery; do not promote a surviving file merely because it is readable.

Exports capture the same coordinated boundary as §11, including the committed execution catalog, unresolved gates and choices, required before-images, and protected components. Finalize the verified inventory last. A partial export lists missing participants and is not a complete recovery set. Local redundancy does not establish resistance to disk loss or compromise of the owner account.

The storage layout, canonical JSON record/receipt schemas, flush obligations, and export validation are specified in STATE-AND-RECOVERY §10.2. Per-filesystem adapters, record/receipt decoders, and crash-injection evidence must pass the release gates before the protocol can be described as implemented or durable on hardware.

10. SQLite connection and migration policy

Bundle a pinned SQLite 3.51.3 source release, or an explicitly reviewed newer fixed release, with the manager and service builds. The release manifest must pin the source checksum, sqlite_source_id(), compiler options, and VFS build; those build artifacts remain implementation work. Do not link legacy macOS system SQLite. SQLite's WAL documentation identifies the WAL-reset corruption fix in 3.51.3 and backports; the project baseline uses the fixed mainline. See the offline SQLite account.

Minimum schema features are STRICT tables (3.37.0), foreign keys, recursive CTEs, partial indexes, and the online backup API. Production readers must use the pinned fixed build as well as understand the current role/schema version; accepting STRICT syntax alone is not sufficient. Schema-test Python may use another SQLite build and reports its version; that is not a production qualification. Older managers may open only their retained compatible database copy. Unknown application IDs, schema versions, or identity mismatches fail before domain queries; db schema may show the shipped schema without opening a damaged database.

Every writer connection checks journal_mode=WAL, enables foreign_keys=ON, trusted_schema=OFF, and uses prepared statements with bound data. Durable roles use synchronous=FULL; only the cache may use NORMAL. On macOS enable fullfsync=ON and checkpoint_fullfsync=ON and verify the selected VFS and filesystem's behavior in platform acceptance. Unknown/ignored required settings are a refusal. Durability still depends on the storage stack. The PRAGMA reference explains the FULL/NORMAL distinction; defaults are not a project configuration policy.

Use short BEGIN IMMEDIATE write transactions after acquiring domain locks. Use the shared lock-wait allowance as specified in §10.1; expiry reports db_busy and resolves the durable phase before releasing ownership. Replan on a stale base; never repeat external effects merely because SQL returned busy. Surface disk-full and I/O errors, stop mutations/GC, preserve sidecars and journals, and determine the durable phase through recovery.

Autocheckpoint at 1,000 pages; attempt PASSIVE checkpoint after a batch and on idle. Inspect WAL growth and busy readers. Owner maintenance can request RESTART/TRUNCATE only after readers drain; it must report an incomplete checkpoint. Query readers have a 5-second deadline and 10,000-row/16-MiB output limit. Do not delete WAL/SHM files manually or copy only the live main file. These choices follow the WAL concurrency and checkpoint rules.

Inspection opens an owner-produced consistent snapshot read-only, never marks a live database immutable, and enables defensive mode, query-only, disabled trusted schema, and disabled extension loading. Prepare exactly one statement with no non-whitespace tail. The authorizer permits SELECT/READ/RECURSIVE and an explicit side-effect-free built-in function allowlist; deny ATTACH/DETACH, all PRAGMAs, transactions, writes, DDL, virtual-table creation, and extension/file/network functions. Apply SQL length, memory, expression-depth, and execution limits. db schema and db check use separate fixed statements. sqlite3_stmt_readonly() is an additional check, not the authorization policy. Use the SQLite security guidance and authorizer API; runtime enforcement and adversarial tests remain required.

10.1 Contention and safe stopping

Busy interactive commands display the known owner and offer wait or exit. Unattended commands wait only with --wait; otherwise they return contention promptly. A configured timeout alone does not authorize waiting. After waiting or recovery, revalidate the base, trust, and plan; materially changed operations require fresh confirmation. Prefix ownership spans preparation through post-commit service checks, including surviving helpers until they stop writing. The approved waiting/stopping command surface and protocol are in STATE-AND-RECOVERY §10.2.

Authorized foreground waits use db.lock_timeout = "30s"; the common --lock-timeout DURATION option overrides it for one invocation. Accept a nonnegative integer followed by ms, s, or m; reject other forms and overflow. 0s tries once without waiting. One operation controller counts cumulative lock-wait time across prefix, system, service-owner, and SQLite waits. Useful work does not consume this allowance, and entering another layer does not reset it. Use monotonic elapsed time and cancellable backoff starting at 10 ms, doubling to a 250 ms cap and clipped to the remaining allowance. Preserve prefix-then-system ordering. After one second of cumulative waiting, print a stderr waiting message; update every five seconds thereafter with role, operation, elapsed wait, and known owner identity. Say unknown when the owner cannot be established; a PID alone does not establish authority. JSON output remains one object, with progress on stderr.

Owner-lock contention and SQLite BUSY consume the same allowance. SQLite may return busy without invoking its busy handler, so the operation controller handles every result; a connection-local timeout cannot implement this contract. Enable extended result codes. For BUSY_SNAPSHOT, end the stale read transaction and revalidate/replan before any effects; after effects begin, enter recovery instead. For internal LOCKED conflicts, finalize conflicting statements or end the local transaction and diagnose the connection error; do not sleep and retry blindly. Disable shared-cache connections. Finalize statements and end read transactions promptly. Runtime shims fail promptly when required state is unavailable and do not inherit foreground mutation waits. See the SQLite contention evidence.

Normal commands bypass a busy disposable cache when authenticated inputs are available: verify receipts, freshness, policy, and complete solver inputs as usual, work in memory, and skip cache writes. Without those inputs, report unavailable verified data; contention never authorizes stale or unverified use. Explicit cache inspection or maintenance reports contention rather than substituting another data source.

Timeout and cancellation are resolved by durable phase, not by the last SQL call:

Recovery receives one separate, bounded 30-second cumulative lock-wait allowance, including resolution of prepared intent; it is not renewed by retries or repeated cancellation. Cancellation requests a safe stopping point, not abandonment of effects. The initiating user or an authenticated administrator may request stopping; the controller must quiesce helpers before rollback or manual repair. If recovery cannot finish, retain journals, backups, and GC roots, report needs-attention, and block conflicting mutations. Working packages and external repair tools remain available. A committed operation cannot be described as rolled back merely because cancellation arrived late.

Contention with no unresolved recovery exits 4; recovery-required outcomes exit 1; safely completed cancellation exits 130. Optional maintenance after completed work preserves exit 0. Diagnostics in data expose phase (unprepared, prepared, effects, committed, or complete), committed (boolean), recovery_required (boolean), and retry_safe (boolean). Retry is safe only after resolution and base revalidation; committed work must not be resubmitted as a fresh mutation.

10.2 Automatic maintenance and cleanup

Run maintenance after successful mutations and in existing service idle loops. Add no client daemon or launchd job, and never request elevation solely for housekeeping. Attempt owner and SQL locks without waiting, yield to foreground work, and share a 100 ms work budget across tasks. Check a monotonic deadline between tasks and batches and use an SQL progress handler to interrupt work. This limits scheduling and interruptible SQL, not the wall-clock duration of storage synchronization. An interrupted batch rolls back; completed batches remain. Optional maintenance failures do not undo command success; report corruption or I/O failures separately and block later mutations when database health is uncertain.

Attempt cleanup hourly, optimization daily, and PRAGMA quick_check weekly when due. Keep PASSIVE checkpoint attempts after batches and on idle; readers that prevent completion leave checkpoint work deferred. Rotate due tasks by oldest attempt (null first, then task name) so a cleanup backlog cannot starve another task. A deferred or interrupted task remains due; each invocation tries it at most once. Use bounded PRAGMA optimize with the pinned build's analysis limit and the same progress deadline. Full integrity and external-reference checks belong to db check; a quick check is not proof of reference or trust validity.

maintenance_tasks records UTC epoch seconds for last attempt and last success, outcome, and deferred reason. Success advances only after the whole task completes; a partial checkpoint or cleanup backlog is deferred. Bookkeeping is disposable, excluded from logical-state digests and authoritative replay. Reconstruction seeds all tasks due. If locks prevent writing an attempt, retain that observation in memory and report it; flush it on the next writable opportunity, without waiting solely to record a deferral. A backward clock jump clears future bookkeeping times and makes tasks due rather than suppressing them indefinitely. These local records are not telemetry.

Cleanup transactions delete at most 100 rows total, including dependent rows. Large snapshot projections are removed in bounded child batches while owner coordination protects current snapshots and active work; parent rows survive until all references are gone. No cascading delete may evade the batch bound. Before removing children, set the solve or snapshot's cleanup_pending marker under exclusive owner coordination. Marked entries cannot be selected as current, reused by solves, or exposed by search, even after interruption. The search view excludes marked snapshots; owner code enforces the other selection rules. Complete authenticated reimport may clear the marker atomically. This keeps a partially removed projection from appearing complete between batches. Cache inserted_at and accessed_at are UTC epoch seconds; initialize them on authenticated insertion and coalesce access updates during writable operations. Inspection never writes access times. In-memory active-work pins protect the owner's readers even when timestamps have not been flushed. Other processes hold shared owner coordination while consuming cache rows; cleanup requires exclusive coordination and defers if those readers remain. Revalidate eligibility under the lock. Access timestamps never move backward; retain future timestamps after a clock correction until their age can be established conservatively.

10.3 Manual maintenance, compaction, and schema 2

aslice db maintain [--dry-run] runs eligible cleanup, optimization, lightweight checks, and checkpoint work for the selected role. It uses the foreground lock allowance and cancellable bounded batches, without the automatic 100 ms total budget. It reports remaining/deferred work rather than claiming completion. --dry-run inspects and reports eligibility and work estimates without writes, including bookkeeping. It grants no authority to remove protected references.

auto_vacuum=NONE is explicit in every schema. Cleanup removes eligible rows; deleted pages remain reusable inside the file. Checkpointing transfers WAL pages, optimization updates planner information, and compaction reclaims database-file space. None substitutes for another. Only aslice db compact [--dry-run] requests full compaction; automatic maintenance never vacuums.

Compaction uses the versioned-copy mechanism below. Under owner coordination, capture a consistent backup, vacuum the staging copy with no open statements or transaction, validate logical equivalence, identity, replay head, integrity, foreign keys, and external references, then journal activation. Keep the owner boundary through activation; drain/reopen readers against the selected version. Preserve the original database and known-good recovery entry point. Before work, report snapshot size, vacuum workspace, retained-original size, and free-space requirements per filesystem. Reserve space for the snapshot plus up to twice its size for vacuum workspace, in addition to the retained original and measured sidecars/journal margin; refuse insufficient space. --dry-run reports the estimate without staging or activation. Failure before activation leaves the active file intact; interruption during activation enters existing journal recovery. No filename or modification time decides which copy is active. See the VACUUM evidence.

The historical schema-1-to-2 transition advanced all six roles without changing application IDs or public formats. Migrate through a validated copy, never by editing the active file. Rebuild database_identity with its version-2 constraint, preserve instance/owner and all domain rows, and add seeded maintenance tasks. Cache lifecycle timestamps start at migration time (not fabricated historical access); rebuild current-snapshot references from authenticated current inputs, refusing activation if these cannot be established. Unknown cache rows may instead be discarded and reimported after verification. Compare logical state excluding maintenance bookkeeping and cache lifecycle fields; validate every retained reference and both version markers.

Diagram source

sequenceDiagram
  participant S as Known-good supervisor
  participant O as Old manager and database
  participant N as Versioned staging copy
  participant J as Recovery journal
  S->>O: Lock and capture coordinated boundary
  O->>N: SQLite backup API snapshot
  S->>N: Migrate or compact, validate, run manager health checks
  S->>J: Prepare activation with old and new identities
  S->>N: Activate database and manager pair
  S->>J: Reconcile and commit
  Note over O,N: Retain old pair and recovery entry point

Migrations run on a versioned copy, including migrations that appear additive. Validate integrity, foreign keys, semantic equivalence, replay cursor, object references, and manager health before journaled activation. No destructive in-place migration is permitted. Failure preserves the old pair. Downgrade after new writes requires an explicit compatible replay/conversion path; selecting an old snapshot must not erase newer choices, reservations, or trust. Without that path refuse.

10.4 Security and scheduler projection version 3

Client-cache version 3 adds advisory_assessments keyed by repository/environment, snapshot, advisory, artifact, and component (empty component means whole artifact). Rows retain repository identity, advisory object digest, expiry, and vulnerability status for active-closure queries. Recompute remediation from current holds, platform and available candidates; it is not repository authority. Clear dependent assessment rows before snapshot eviction. Cache rows cannot authorize an advisory: reverify the retained TUF target, freshness, scope, and assessment evidence.

Coordinator version 3 names the former kind table job_kinds and the jobs column kind. The scheduling projection independently records priority, continuous ready time, authenticated authorization, supersession, and cancellation receipts. worker_inventory binds enrolled worker IDs to physical builder IDs and retained capability evidence. Query these with job requirements, dependencies, attempts and gate evidence under BUILD-INFRA. Scheduler rows cannot grant security priority, qualify hardware, or waive a gate without authenticated evidence.

Reject unsupported role/schema versions before reading domain rows or admitting mutation. Do not reinterpret old lanes as priorities. Reconstruct client-cache 3 from currently authenticated snapshots and advisory targets in a new file. Reconstruct coordinator 3 from retained plans, job kinds, authorization, readiness, lease history, capability and gate receipts. Missing priority/readiness/physical identity evidence keeps jobs blocked pending explicit classification or qualification; do not fabricate timestamps, consent, or independent builders. Expire inherited leases, reconcile completed and cancelled attempts, then activate the verified copy under the existing recovery procedure. Retain the old file as evidence.

The schema-1-to-2 migration above remains a historical regression contract; tests/fixtures/database-v2 freezes its DDL. It is not an automatic 2-to-3 migration. The remaining roles need no new persistent tables: promotion and selections are retained authenticated records, addressed through their existing object projections.

11. Backup sets and restore

A backup set is a consistent SQLite snapshot plus the durable material required to interpret it. Use SQLite's online backup API into a new staging file; check every API result and require completion. Raw copying of a live main file is unsupported. A local snapshot supports operator-error recovery; disk/site loss requires independently verified copies on separate storage and at a separate location, following the never-lose set.

11.1 Coordinated boundaries

A standalone role backup is still useful but must name missing participants. It cannot claim whole-prefix or signing recovery completeness. Quiesce at a resolved boundary where possible. If an operation cannot resolve, retain its complete prepared intent, phase receipts, and before-images and mark the set recovery-required. Never wait for another owner while holding locks in reverse order.

The version-1 canonical JSON backup manifest binds set_id, creation time, status, each role/instance/owner, application/schema IDs, manager/SQLite source identities, database digest/length, stream sequence/head and retained ranges, trust checkpoints, active pointer fingerprints, all required object digests/lengths, participant boundary IDs, and any unresolved operations or missing components. Include restore tool identities and compatible manager/database pairs. Verify all objects before flushing and finalizing the manifest; store its digest in the independent backup inventory. Interrupted copies have no finalized manifest and are not restorable sets. Encrypt private administrative records in transit/storage as required by their owner; database backup contains no private keys. Key backup follows the separate runbook.

11.2 Ordered restore procedure

1. Stop the affected writers and GC/dispatch/publication. Preserve damaged files, WAL/SHM, journals, configuration, and pointers as evidence. Record incident and selected backup identities; do not overwrite originals.
2. Authenticate the backup manifest against the independent inventory. Establish the latest retained heads from live records, other copies, and counterpart receipts. A stale backup is usable only with a continuous verified suffix to the known latest state. If latest state cannot be established, refuse automatic restore rather than treating absence as an empty history.
3. Restore into a new owner-controlled staging directory. Check permissions, symlinks, role/instance/schema, manager compatibility, manifest checksums/lengths, integrity_check, foreign_key_check, all object references, trust provenance, record continuity, and domain invariants. Refuse mixed repository/environment identities and missing required participants. Preserve the prior active pair.
4. Replay the verified suffix and reconstruct missing projections. Reconcile incomplete transactions against actual filesystem pointers and fingerprints, including protected effects. Conflicting external edits or missing before-images produce needs-attention; never overwrite them to make the backup match.
5. Produce a preview binding set digest, current heads, target identities, ownership mapping, affected paths, effects, and unresolved issues. Explicit confirmation authorizes exactly that preview. Reacquire locks and revalidate its base; any change invalidates the confirmation.
6. Journal activation of the validated database/manager pair and any reconciled effects, reconcile, and commit before service health checks. Manager/database validation remains a pre-commit gate. Keep the prior pair until recovery policy permits retirement. After interruption, aslice recover resumes from the journal and inspects pointers; it never guesses from the newest filename.
7. Verify clients/services, GC roots, farm pending gates, and current publication state as applicable. Take and independently verify a new complete backup set before resuming signing/publication after disaster recovery.

SQLite salvage can recover altered, deleted, or constraint-violating rows. Treat its output only as evidence for independent comparison and reconstruction, never as an automatically trusted restore.

12. Recovery decisions and failure matrix

Diagram source

flowchart TD
  A[Stop affected mutations and preserve evidence] --> B{Only cache lost?}
  B -- yes --> C[Rebuild from authenticated inputs; retain trust]
  B -- no --> D{Trusted records and latest head intact?}
  D -- yes --> E[Rebuild or restore staging and replay]
  D -- no --> F{Independent complete backup and suffix?}
  F -- yes --> E
  F -- no --> G[Needs attention; authority recovery if required]
  E --> H{Fingerprints and before-images sufficient?}
  H -- no --> G
  H -- yes --> I[Inspect journals and validate evidence]
  I --> K{Durable commit exists?}
  K -- yes --> L[Reconcile forward; retain commit]
  K -- no --> M[Fingerprint-checked reversal]
  L --> J[Preview, confirm restore, journal activation]
  M --> J
  L -- recovery wait exhausted --> G
  M -- recovery wait exhausted --> G

Reconstruction through recover follows the same validation gates as restore. Automatic crash reconciliation uses already-authorized transaction intent. Explicit backup restore requires the confirmation in §11.2; neither path weakens consent for protected effects or trust rebootstrap.

Interactive recovery offers recover and continue once; unattended use requires explicit authorization. Resume saved recovery progress automatically when durable evidence and actual state agree. Inventory files, services, registrations, permissions, and transaction progress before grouping actual conflicts by repository, package, or service. Persist recommended choices, validated current-state acceptance, displaced-content references, and accepted deviations in recovery records. These are additional record/decoder requirements, not implemented SQL schema changes. Manual repair waits for helper quiescence and preserves a mutation gate across exits and reboots while external repair tools remain usable.

A damaged-prefix rebuild recovers selections separately from executable authority. Verify artifacts independently, prepare beside the original, and review missing artifacts, verified replacements, and omissions together. Isolated verified closures may be usable before normal activation or affected privileged integration is allowed. Report repaired, usable with listed unresolved repairs, or replacement prepared but activation blocked. The authoritative workflow and recovery mechanisms are in STATE-AND-RECOVERY §5.1 and STATE-AND-RECOVERY §10.2.

For timestamp state that is unavailable or untrustworthy, preserve the existing KEY-RUNBOOK requirement to replace the timestamp key through a root update. Establish safe version continuity as part of that recovery; new keys do not waive client rollback protection.

13. Command and output contracts

The specified command family is aslice-db(1). --role accepts exactly the six roles in §1.1 and defaults to client-state. --prefix selects a client instance; --instance selects a configured service or system instance. Reject selectors that conflict with the role. Paths supplied by an untrusted caller cannot select a different owner's database.

aslice recover --role ROLE reconstructs a missing projection or resolves an interrupted operation using retained records. It defaults to client state, discovers required protected participants, and uses their authorization boundary. Cache rebuild never resets trust. check offers this command when records are intact; otherwise it identifies the missing evidence and applicable runbook. Service roles are operator interfaces, not new client access to farm accounts.

--json produces one object with format: 1, command, role, instance_id, status (ok, refused, needs-attention, or error), data, and errors. Errors have stable code, message, remedy, and optional path. List has null role/instance and an array of per-instance entries in data. Query data contains ordered columns, positional rows, and truncated; SQL null maps to JSON null, BLOB to an object with base64 bytes, and integers outside JSON's safe range to decimal strings. Duplicate column names are preserved by positional rows. On a limit, discard partial rows, set truncated: true, and return failure. Human output uses tables and stderr diagnostics; scripts consume JSON. No command reports a failed check or partial backup as success.

Exit codes: 0 success, 1 check failure/needs-attention/I/O failure, 2 usage or unsafe query/unsupported schema, 3 authorization/confirmation refusal, 4 busy or changed base without unresolved recovery; 130 safely completed cancellation. Recovery-required outcomes use 1 even after timeout or cancellation. Stable error codes include db_busy, db_identity_mismatch, db_schema_unsupported, db_query_denied, db_query_limit, db_corrupt, db_records_incomplete, db_stale_backup, db_evidence_missing, db_external_conflict, db_confirmation_required, and db_io_error.

14. Verification and acceptance

Run python -m unittest discover -s tests -p test_database.py. The suite creates temporary databases from all six companions, checks valid fixtures and rejected identities/relations/reservations, exercises views and representative queries, and models record replay, lease expiry, duplicate results, stale restore, and migration copies. Multi-connection tests exercise writer contention, stale snapshots, local statement conflicts, and incomplete checkpoints with active readers. Deterministic models cover cumulative wait/cancellation budgets, durable-phase outcomes, cache bypass verification, fair maintenance scheduling, cleanup bounds and retention, schema-1-to-2 copies, and compaction with interruption and space refusal. Model helpers live only in tests; they are not database runtime services.

The current models do not implement the guided recovery, explicit waiting, helper-ownership, post-commit service-check, or isolated-replacement contracts added in this revision. Passing them validates their existing modeled scope only. The additional pending runtime scenarios and UX targets are specified in STATE-AND-RECOVERY §10.1, including lost trust, disagreeing copies, working access, grouped conflicts, manual repair, reviewed activation, repeated interruptions, and durable-transition failures.

Before shipping, implement and test the domain decoders, connection authorizer, service fencing adapter, backup coordinator, restore supervisor, and actual CLI. Inject failures at every journal/SQL/fsync/pointer/signature/activation boundary; test disk-full, I/O errors, malicious schema and SQL, long readers, corrupt records, lost acknowledgements, and independently restored backups. Complete HFS+/APFS and macOS 10.11–12 power-loss drills and separate Pi/service-host drills with the pinned build. Rendering diagrams, passing schema tests, and SQLite's upstream guarantees do not satisfy these platform and security acceptance gates.

History

Source document