Persistence

Elowen uses PostgreSQL as its only runtime database. The elowen-db workspace package in packages/db owns the driver, core SQL, migrations, fourteen typed repositories and generic plugin database adapter. Core code receives repositories and awaits every database operation. Cross-package runtime imports use public package exports, including elowen-plugin-shared/projectExecution for reserved project slugs; source-relative paths cannot survive the package's dist/src layout. HTTP routes, DTOs, ownership rules, replay bytes and usage accounting retain their existing contracts.

This page describes the PostgreSQL persistence architecture. The package's detailed rules live beside it: module.md for pools, binding, transactions, migrations and the test harness, schema.md for column, JSON, trigger and identity rules, concurrency.md for lock protocols and the C5-NNN invariant labels, and plugin-db.md for the plugin adapter. Repository signatures are the typed interfaces in packages/db/src/repositories/.

Process lifetime

Pool acquisition failures carry the shared HostConditionError classification through DatabaseConnectionError, also behind the protected configuration wrapper; the problem collector reports them once they repeat. Query/programming failures after acquisition are not reclassified. HTTP database diagnostics retain the typed failure separately from their rendered original cause. Transcript, turn admission and process-state publication already pass native errors to the logger. Plugin interval and Sandbox lease-cancellation diagnostics now retain the same original failure in logger metadata rather than only its text. These connection conditions remain in local logs, while untyped SQL/invariant failures and mixed aggregates remain reportable. Config SQL sanitization preserves the audience without retaining sensitive driver diagnostics in a cause.

The daemon, each subagent runner and each background child own one pool. Daemon pools have four connections; runners and workers have two. Startup reads the connection URL and explicit instance root from protected instance configuration. Credentials never enter argv, logs, statement maps or HTTP responses. TCP uses certificate-verified TLS; Unix sockets need no TLS. All application connections use transaction-pooling PgBouncer.

The daemon alone checks the server floor and runs versioned migrations before serving. Runners and background children use already migrated data. Supported servers are PostgreSQL 16 or newer with vector >=0.7, pg_trgm and preloaded pg_stat_statements. Local provisioning installs PostgreSQL 18 and PgBouncer in an owned socket-only cluster; external provisioning verifies the existing service without editing it. The instance root is independent of the connection URL. The shared path resolvers accept a read-only partial environment: callers with an explicit instance root need no unrelated process keys such as Next.js's required NODE_ENV.

Owned-host resource policy belongs to CLI provisioning and proven-release updates, not migrations or daemon startup. One renderer and root-recorded approved budget drive fresh and already-owned convergence. Apply checks actual settings, sources and pending restart, reloads only the owned cluster/pool and never restarts them automatically. External/shared settings remain operator-controlled. Both parameter logging limits are zero in the owned profile; literal SQL and arbitrary server error text are not redacted. Table-local vacuum/analyze reloptions remain versioned schema policy for every hosting mode.

Close the process pool after persistence, queued reporting and shutdown work drain. Attachment sweep and session export retain their existing child process for filesystem traversal and rendering CPU work; their children own and close their pools. Runtime callers use the repository registry directly. Boot recovery and channel settlement import the existing stable syntheticRestartResultId function directly from elowen-db.

The config factory owns one committed public snapshot. config.committed():ConfigSnapshot synchronously returns a defensive copy with revision; async get/snapshot and writes publish coherent current/newer views only after outer commit. Delayed reads never rewind it, rollback never publishes, and another process's change appears on the next async read. Only necessarily synchronous runtime/health readers use committed(); fresh reads still await get. Unreadable settings retain closed defaults, unreadable health and their original stored bytes.

Fresh runtime assembly uses config.brainSnapshot() when it needs public configuration and stored provider credentials. One document read returns them separately and publishes only the redacted public view after commit. brainConfigFromElowen and transaction-bound conversational/account model projections consume this pair. Equal-revision byte changes remain visible on every invocation. Plugin config/settings snapshots reuse the document's already-read revision while retaining coherent vault projection and unreadable refusal.

Journal and delegation share one assembly-owned boot identity cell across every SQL binding. The delegation factory also retains one onSubagentRunSettled(parentSessionId, toolCallId) observer across transaction bindings. Every accepted terminal run transition queues that identity with the bound executor's afterCommit; the callback reads through the process repository, never a released transaction. Rollback and repeated terminal writes emit nothing. Observer failures propagate after commit without replaying the mutation; runner IPC publication errors are logged and reconnect reads repair client projections from durable state. Delegation cancellation may persist terminal error progress before child helpers unwind; late progress cannot revive that call and late helper results cannot wake the stopped child. Successful foreground terminal progress and atomic result completion still wait for helpers to finish and their results to be processed. Journal persists typed decisions and raw data; delegated authority, visible-message/ancestry projection and filesystem work remain in their existing core owners. Plan intents and artifact deletion debt commit with reference revocation before files are touched. Core managedArtifacts drains intents under the instance-root kernel lock outside SQL, then acknowledges them; delete/clear/account cleanup uses captured immutable namespaces only after commit. Whole-session DB deletion drains before id reuse; range-clear drains remain in the background. Explicit instanceRoot is mandatory for core plan/file operations. Exact signatures are in packages/db/src/repositories/journal.ts and delegation.ts.

journal.copyJournalBranch receives the existing pure core ancestry callback, returning { seeds, buildModeEntryJson }. SessionFactory.seedForkBranch is its production consumer: core supplies journalContextSeeds and a complete hidden Build mode entry rendered by chatEventMessage({ v: 1, kind: 'mode', mode: 'build' }, false), with identity and timestamp captured before SQL. Publication inserts this initializer in the same transaction only when the copy is nonempty, the target has a parent and its copied current mode is not Build. It uses the existing insertion writer before a provisional tail, preserving that tail's active leaf and tool ordering; without a pending tail it appends to the copied leaf. Missing required initializer JSON fails the whole copy. A Build source, empty copy or nondelegated target adds no initializer. Peer forkSession retains its inherited mode. The callback remains free of IO and SQL; there is no renderer in the database package. The returned count covers copied ancestry and context, excluding the initializer.

Finished-dream prefix retirement stays an internal journal composition. Its private candidate reader and delegation archiving import the same idle-child SQL and returned-call proof from delegation/retentionEligibility; raw tool results are parsed in JavaScript rather than decoded by SQL. journal.reclaimInheritedForkPrefix selects by updated_at and id, protects parent and child on one serializable executor, then detaches at most 128 roots and 256 KiB per call, allowing one indivisible oversized root. It preserves descendants' inherited metadata and usage, and publishes a journal-only deletion marker with below_request_seq=0 only after all own roots detach. Ordinary terminal delegates remain continuable and ineligible; closed prefixes drain through the existing journal/request retirement path.

transcript/sessionVisibility.ts owns the single SQL predicate for unspoken identities, shared by transcript.sessionListing, transcript.unspokenSessionIds, account conversation counts and message search. Model/thinking initialization alone does not publish a conversation. Accepted native user messages and custom_message records with pending != 2 count as content, including slash commands, explicit mode/project choices and plan decisions. A stored goal in any status, a checkpointed send in brain_paused_queue, a durable view wait, a parked session or a durably queued conversation append (brain_conversation_appends) also counts, even with an empty journal. Listing and deletion use that same predicate; a draft goal must remain visible and must survive switch-away or teardown. Explicitly named organizational roots and deliberately cleared conversations retain their existing visibility. The physical has_entries projection only accelerates the zero-entry case; a positive value is not evidence of a user message. Listing and search use indexed scalar headers without parsing journal bodies. Runtime addressing and explicit ownership reads keep seeing an unspoken identity so another terminal can resume it while composing. Existing anonymous shells are hidden immediately by this predicate and need no data migration. Execution refs, work-dir binding and plan-transfer intents written by ordinary opening are runtime identity, not content by themselves. Actual plan authoring follows the durable Plan-mode choice or a user turn. Pending composer attachments live in the client; resumable uploads live in UploadSessions memory and publish project-owned files, not session-owned journal state. Deleting an unused identity does not delete those project files.

Service lifecycle uses journal.selectActiveConversation for protected empty-session pruning. Selection preserves both conversations' goals; their continuation lifetime belongs to GoalLoopService. The active pointer remains the existing process-local LiveSessionRegistry map, published only after commit and a fresh client-claim check. journal.setProjectExecution now accepts a validated workDir slice so cwd, execution and plan debt share its session-locked transaction. delegation.settleInterruptedChild reads recovery facts after child protection, persists the core-compiled partial journal and acknowledges only exact durably folded result receipts on that same executor. If the child session vanished before the protection was acquired, settlement rejects before invoking the compiler; an absent transcript cannot become an empty-conversation terminal verdict. Each receipt names resultId, journalSessionId and journalEntryId; delegation verifies the child-scoped entry against this call's inserted rows and prepared payload bytes before acknowledgement. It takes the existing internal journal.lockJournalSessions(boundSql,[childSessionId]) protection, with no public lock API or duplicate lock SQL. Canonical projection stays in core, with no PI/provider/file effects inside retryable SQL.

Monthly spend reset is one DB-only usage.resetMonthBaseline(userId,{fromDay,toDayExclusive}) operation. It locks the payer, sums the provider ledger after lock acquisition and writes the baseline in the same READ COMMITTED transaction. It rejects older bound snapshots, keeps the exclusive UTC-month boundary and existing USD-to-microusd rounding, and fails for a missing account. Core computes only the time boundaries; no public lock or caller-side sum/write composition is added.

Provider-request debug persistence receives CoreProviderRequestInputs once through assembly: canonical channel/subagent prefixes from core sessionId and the existing fromRegistryProvider function from shared/execs. Row classification and parameterized SQL prefix filters share those values, and request projections call the same injected converter. The package keeps no copied session vocabulary or provider namespace logic; root and transaction bindings retain the inputs.

ProviderRequests.start keeps its durable writing intent, pending-capture pin and final manifest publication on the existing staged protocol. PayloadStore.storeSegments runs on the bound capture or response transaction: one typed UNNEST writable CTE inserts missing segments and upgrades only V1 rows whose v2_referenced is still zero. Only keys returned by those writes contribute first-reference bytes. Only upgrades and conflicting keys need the separate batch SELECT that compares payload::text and V2 display values; rows inserted by this batch already contain its prepared bytes. Conflicts committed after the write statement's snapshot remain visible to that SELECT, and a mismatch rolls back the whole batch. Preflight and post-intent checks share a capture-local proof keyed by session, segment, exact prepared bytes and row identity: a previously verified row needs no second TOAST payload read, while a deleted/reinserted key is verified again. Proofs never survive into another request. SQL returns equality results rather than transferring stored payloads. Chain nodes use one UNNEST INSERT per 128-row batch and account only inserted rows. Segment batches retain the 128-row and 256-KiB limits with one oversized item allowed to progress. Preflight key/collision checks and debug metadata use UNNEST joins; metadata is mapped back to the original reference order, retaining duplicates and missing-key errors.

Provider diagnostic reads retain their coherent read transaction. PayloadStore.chainRefs traverses scalar metadata in one recursive statement per nonempty input/tool chain, bounded by declared count and stopping expansion on count mismatch. Typed validation still rejects missing nodes, early ends, count mismatches and excess tails. segmentTexts reads ordinal LEFT JOIN batches of at most 128 references as payload::text, preserving duplicates, order, missing-row errors and stored text. Debug reads select the byte-bounded page before hydration; reconstruction reuses references from its metadata/byte preflight. Parsing remains standard JSON.parse and replay key order remains options, system, input, tools.

Provider retention keeps C5-247–249 owner/session locks, SERIALIZABLE attempt/diagnostic boundaries, live-reference discovery and staged draining. Each request, chain or segment DELETE RETURNING supplies its exact physical-byte charge to additive stored_bytes subtraction in the same writable CTE and transaction. Empty steps neither update nor recompute the summary. Row/byte budgets and one oversized-item allowance stay unchanged. Planning still reloads and validates the full reference graph for each relevant batch; no plan or reference fact survives a commit. This does not repair previously inconsistent summaries.

Repositories and transactions

Full transcript.sessionListing snapshots return a per-session usage map of { tokens, cost }, summed together from the existing brain_usage_session_totals for each owner's current reset epoch. Both owned and managed lists use this one batched query, not journal payloads or per-row usage reads. Refs mode returns an empty map and still skips usage readiness/enrichment. BrainStatusService forwards the totals to both conversation list routes; the snapshot, accounting-kind inclusion and token sorting semantics stay unchanged.

repositories/types.ts owns the raw JournalSessionExpectation fence beside BrainSessionRow. Journal and computer-prerequisite contracts import that one leaf declaration; prerequisites never import the journal repository aggregate. The public elowen-db export and the journal contract's existing type projection retain the same identity and fields. This ownership adds no runtime code, query, lock or transaction boundary.

The repositories are config, users, projects, journal, transcript, attachments, search, delegation, usage, providerRequests, providerConnections, memory, events and maintenance. Their authoritative types, domain error constructors and the config repository's UNREADABLE_PLUGIN_SECRET identity are exported by elowen-db. HTTP routes import those same values to classify repository failures and mask unreadable secrets, preserving 400/409 responses and secret-state flags. Pure exports include account/CLI defaults, terminal/navigation/alert validators, digest bounds and the single strict model-grant codec. Account and preset writes use writeModelGrantColumn; malformed or widening grant lists fail before persistence. Historical spelling conversion belongs to the importer. Nested properties preserve existing ownership, including users.apiTokens, users.presets, config.problems and search.vectors. No core caller receives a statement or driver object.

await createCoreDatabase(options) first migrates the schema when the caller is the authoritative daemon (an empty database from a fresh install included), then initializes the single committed public config snapshot before returning and assembles the complete factories once per process. Each create<Name>Factory(context) captures its process-owned observers, caches, pins or boot identity once and returns a binding function. Rebinding to a transaction changes only its SqlExecutor. context.bind(sql) exposes all fourteen repositories on that same executor. CoreDatabaseOptions requires config construction inputs; createConfigFactory receives CoreConfigRepositoryContext, which extends the existing bind-only context. Daemon wiring supplies explicit vault key/keyPath and allowKeyInitialization, model-reference and account-default declaration accessors, strict secret-manifest discovery and the authoritative preparation flag. These are process-owned inputs retained across binding. The structural types and minimal construction contract are in module.md.

await db.transaction(async tx => {
  await tx.repositories.journal.setTitle(sessionId, title);
  await tx.repositories.journal.setLastWriter(sessionId, userId);
});

The callback receives tx.repositories; callbacks already typed as Repositories receive that inner shape directly. All callback promises finish before commit. Escaping to outer repositories would acquire another connection. Internal sql.atomic starts a transaction on the root executor or joins its bound connection. A joined operation retains the outer isolation. Composite read-only snapshot requests inherit even READ COMMITTED, where statements can see different snapshots and the caller owns consistency. Callers requiring a coherent view must open their outer transaction at REPEATABLE READ or SERIALIZABLE. Explicit stronger write-isolation requests still fail; no nested call silently upgrades or downgrades a transaction. Captured handles reject after completion.

READ COMMITTED is the default. Coherent multi-statement readers, including export authorization/transcript, selected memory scores/bodies and session listings, use {isolation:'repeatable read',readOnly:true} when standalone. A read inside a stronger write operation shares its existing snapshot. No session SET, session advisory lock, LISTEN/NOTIFY or cross-transaction temporary table is permitted.

The concurrency inventory names each invariant and its row lock, unique/CAS statement, advisory transaction lock or SERIALIZABLE boundary. Lock stable owner rows before dependent reads, in settings, sorted users, sorted projects, sorted sessions, domain-row order. Every writer participating in the invariant uses the same protocol. An absent-row predicate needs its specified constraint/advisory protection. Provider admission requires payerUserId:number|null in AdmissionGuard. A user principal locks its user row after settings and before connection state/guard reads; a missing user row refuses admission. An explicit instance principal supplies null and has no user-row lock or per-user spend read, while settings-then-connection locking and per-connection spend checks remain. Dispatch uses RoutingPrincipal, so sessionless user work still supplies a numeric payer and absent identity never becomes instance work. Checks use settled spend only and never reserve future cost.

SERIALIZABLE operations explicitly set maxAttempts:3 and retry the whole database callback only on 40001 or 40P01. Pure prepared inputs may be reused; network, filesystem, PI mutations and notifications stay outside the retry. Stale business revisions remain conflicts. sql.afterCommit queues notifications until the outer commit, drops them on rollback or retry, releases the connection, then awaits them in order. An observer failure propagates without retrying committed writes.

Maintenance construction

CoreDatabaseOptions.maintenance supplies CoreMaintenanceInputs to createMaintenanceFactory through CoreMaintenanceRepositoryContext. The core-owned sampleHostMetrics(history) callback adapts src/hostMetrics/sampler.ts to the async events.hostResources repository, retains one private delta reader and performs sampling/persistence before returning ProblemHostResources. The root daemon scheduler invokes it outside SQL retry callbacks, once a minute without a boot sample. OS sampling and reporting projection remain outside packages/db.

The same inputs carry the existing isEvictable and vitality functions from src/brain/memoryVitality.ts as memoryVitality. Memory eviction reuses them for its paged prefilter and the locked current-row autoEvict evaluation, preserving one formula, one sweep time/config snapshot and the rolling deletion budget. Both maintenance operation inputs remain undefined. The module contract specifies lifecycle and ownership.

CoreDatabaseOptions.memory.categoryIcons supplies the canonical allowlist and defaultIcon from src/shared/categoryIconNames.ts through CoreMemoryRepositoryContext. Memory retains those inputs across binds and removes its copied constants/exports; the web mirror continues matching the core vocabulary. Category deletion and assignment serialize through repository row locks, not a memories.category_id foreign key.

The existing core event-retention tick also calls maintenance.maintain('digests', undefined, now), expiring dashboard digest and run rows before yesterday's UTC day key for every account, including inactive accounts. It returns the combined deleted-row count, commits bounded run-then-digest batches and leaves retained payload bytes, retry attempts and C5-302 run counters unchanged. A pass handles at most 128 batches of 128 rows per table; additional backlog waits for the next hourly tick. Digest claims no longer run a separate pruning path.

Project lifecycle and live execution

Journal admission serializes with deletion entry: session creation, delegated-scope promotion and prepared plan moves lock users, all named execution Projects, session identities/rows, then domain rows. Both plan endpoints participate for every target kind. Missing or deleting targets reject before writes or migration-marker consumption. Running delegated calls/workflows and workflow resume recheck parent/child authority under the same protocol. Boot claims omit unavailable targets without blocking unrelated recovery; recovery-attempt admission rechecks after claim before spending an attempt. These fences remain effective while blocked deletion retains its Project row. Terminal settlement and history cleanup can drain.

Core ProjectService calls projects.beginDeletion(id, {requestedByUserId, deleteFiles, paths, files}) for every execution kind and projects.finishDeletion(id) only after verified external cleanup. Both join the existing transaction. Migration 0015_project_deletions stores the immutable delete_files choice, confirmed paths, private core-owned canonical files receipts, ordered participants, acknowledged steps and a named blocked owner; pre-existing managed deletions require scope confirmation. Plugin declarations persist even when a plugin is disabled or absent. Finalization requires every captured acknowledgement. A false file choice omits the files participant, not a no-op acknowledgement; service and managed-storage participants remain mandatory. Neither repeated admission nor retry changes that choice. Existing migrated operations start with false and no working-directory file participant. Plugins cannot begin or finish core deletion through host stores; their fulfilled registerProjectRemoved handler acknowledges their own idempotent step. The final SQL cleanup rechecks lifecycle and the last-project invariant, prunes preset references, removes memberships and rebinds eligible ordinary sessions. For every execution kind, it consumes parked turns, paused queues, platform envelopes, pending result wakes and native prerequisite claims before rebinding; conversation history and usage/audit records remain. Ordinary chatbot-owned sessions clear execution_ref without selecting another Project or changing the account; only human non-administrators require a replacement Project. Delegated sessions whose byte-preserving delegated_access.projectRef names the removed Project retain their historical scope but become failed, with no restart continuation. Open subagent calls and workflows owned by affected sessions, or workflows whose nodes name affected delegates, become terminal; workflow generation and recovery-admission invalidation fence stale progress and restart claims. The cleanup uses the existing delegation state codecs, not SQL payload decoding, and leaves completed workflow output intact. With no derived delegated Project column, scope discovery scans delegated session payloads; workflow inspection is limited to affected sessions and their direct parents. Malformed stored scope/state aborts the transaction rather than silently skipping cleanup. Shared Project memory categories (user_id = 0) lose their memories and cascading hash/vector rows; personal memories in Project categories survive with category_id = NULL. External runtime cleanup and stopping live producers stay outside retryable SQL and must finish before finalization.

Users awaits the projects-owned internal provisionAccount(sql,userId,choice) export from packages/db/src/projects after the account insert, using its account-creation/provisioning callback's bound executor. The choice is Exclude<UsersCreateProjectsInput,undefined>. Account creation, personal managed project or selected memberships, and initial order commit together. This helper opens no pool/transaction and is not a public repository method.

BrainService registers one async callback with await projects.onExecutionRebound(async (sessionId,userId,ref) => { ... }). Registration replaces the process-local callback slot retained by createProjectsFactory across transaction bindings; it performs no SQL. With no listener, project mutations still complete normally. finishDeletion enqueues concrete ordinary-session rebinds with the same bound executor's afterCommit; delegated sessions are terminalized without rebinding or a rebind notification, and an administrator null-ref reset of a host or managed Project still emits no notification. Removing a COMPUTER Project is different: the session's recorded directory is a path on that machine, so the rebind also clears brain_sessions.work_dir, and every affected ordinary session is enqueued with the REMOVED computer's own {kind:'local',projectId} reference — the one signal that tells a vanished computer from a destination, on which BrainService records where the conversation runs now. Notifications run after the outermost commit and connection release, are discarded on rollback/retry, and await the listener. A listener failure cannot replay committed SQL. Brain re-reads the stored reference through transcript.getProjectExecution before updating its live projection, in the listener installed in src/brain/brainService.ts. The exact signatures and ownership are in packages/db/src/repositories/projects.ts.

The public paths includes every participant's confirmation scope; deletionFiles(id) returns private typed source/canonical receipts and preservation reasons, validated on write and read from byte-preserving JSON text. Missing canonical identity remains an unresolved offline obligation; it never authorizes removal. Remote deletion never receives plugin-owned host paths. Explicit retry preserves previously captured core paths and adds newly confirmed locations, rather than losing the original directory after device metadata changes. For paired computers, acknowledgements cover only the confirmed working directories. The shared ~/.elowen plans, tool results and workflow results, including historical leftovers, deliberately remain; users may remove their contents manually after checking other Projects' needs. No per-target namespace or remote inventory is introduced. See the remote cleanup scope.

Local plans and artifact lifetime

Daemon composition installs the live managed Sandbox resolver before sessions start. Maintenance waits for plugin reconciliation, service settlement and recovery dispatch before its first plan and artifact deletion sweeps, then repeats them hourly. Runner composition binds the same seam to its own registry for turn work. Pending intents retain their existing retry and acknowledgement authority. Project removal settles plan intents whose source or destination names that Project, regardless of execution kind, in its existing C5-098 transaction after user/project/session locks and before Project deletion. It also removes workflow_artifact_deletions rows targeting that project_id; other targets and later moves in the same conversation chain survive. Device-addressed artifact intents retain their existing revocation/grant sweep semantics. Migration 0007_orphaned_local_plan_operations settles existing local intents whose source or destination Project row is absent; other execution kinds and existing Projects' intents remain. journal.setProjectExecution fences a concurrent removal by locking users, all named Project rows, then session identities/rows; it raises the exported ExecutionTargetUnavailableError for any missing or deleting Project before queuing an intent or changing the session.

Core discovers host copies for managed and local sessions through queuePlanMigration(db, instanceRoot); journal commits validated candidates and its once-only brain_plan_migration.local_completed marker together under an advisory transaction lock. A source that already completed managed migration schedules only local destinations. Plan IO runs through drainPlanOperations outside SQL under the existing instance lock. Dependencies are ordered by source and destination plan location, with all host Projects sharing the same instance path. An offline computer deletion therefore does not delay the independent host deletion for that conversation. settlePlanOperation({status:'deferred'}) retains the intent and clears last_error for typed LOCAL_TARGET_OFFLINE; maintenance treats this as normal pending work and the authenticated CLI connection callback drains it again on reconnect. A background drain that finds the instance lock busy publishes a request generation instead of timing out; the current holder runs one further full pass for the coalesced requests and checks again across lock release. The SQL intent queue remains authoritative, with no new scheduler or timer. Unexpected failures keep their causes and fail the sweep. Conversation state and artifact-reference changes commit before file work and do not wait for device reachability. Source and destination authority, account and paths survive session deletion; the table stores no plan body. Remote deletions must finish before a later plan read so a clear cannot resurrect stale bytes.

Delegation snapshots the local target, contribution account, home and platform beside the workflow in byte-preserving brain_workflows.local_artifact_context. This location survives pairing-token revocation and grants no independent read authority. Artifact deletion intents carry local_target_id separately from managed project_id. The baseline retains ordinary NULL-distinct uniqueness on (path,project_id), so existing host duplicates keep their row identities. New intent writes deduplicate the full nullable tuple through the existing atomic repository operation: a transaction advisory lock precedes its null-safe existence check. Settlement removes all matching historical occurrences. Last-holder checks and later sweeps match the captured target as well as path and content identity. transcript.pendingArtifactDeletions projects revocation from the device alone, because one device is one token and sharing never copies it, so an intent is revoked only once no token names its target. The workflow snapshot reads device metadata through the computer Project's creator, whose account owns the token, while the artifact owner stays the acting member. A disconnected target keeps its intent; a revoked target relinquishes it without contacting a provider or deleting a host path. A late workflow snapshot after revocation keeps the captured local_artifact_context even once the conversation was rebound off the deleted Project, because the capture is the only record of that device. A computer artifact's intent is therefore recorded inside the history-removal transaction even while a grant still shares its path, and pendingArtifactDeletions withholds that intent until the grant is cleared, matching a live grant by its device and a grant whose own Project is gone by path.

Durable computer prerequisites

Migration 0011_computer_prerequisites.sql retains offline obligations against their native conversation, delegated call, workflow node or plan operation. The prerequisite stores identity, original Project, authority and lifecycle generation, not another copy of input/results. Offline waiting does not fail over to the host or discard queued input; explicit cancellation, changed scope/target and removal invalidate the native continuation. Plans remain native intents outside session lifetime, with file I/O under the existing kernel lock and outside SQL transactions.

Journal and delegation share the assembly boot identity and bound transaction. Claim CAS prevents duplicate owners; boot claims and offline wakes spend no recovery attempt. Actual dispatch admission charges the native budget. Workflow siblings share one admission epoch; only explicit terminal WorkflowResume reserves running and advances the lifecycle/reset budget atomically. Its advanceWorkflowGeneration({parentSessionId,toolCallId,workflowId}) has no expected-generation input, while existing upsertWorkflowRun snapshot writes still require the exact native generation as their third argument.

Retained input transfers item by item: its stable admissionId is the user echo identity, while PI allocates a separate native journalEntryId. Append that native user entry and transfer the exact queue admission/sequence/generation plus native receipt in one db.transaction; rollback retains the input. The physical admission_entry_id column in migration 0011 is unchanged. Coherent status projections use the targeted SELECT-only journal.computerPrerequisiteForSession(sessionId) within their read snapshot; waiting/claimed pre-dispatch work is visible, admitted/stale/terminal work is not. No second status table, all-Project scan or snapshot write is needed. The exact API, schema and lock/admission rules are in module.md, schema.md and concurrency.md. Conversation dispatch uses beginConversationRecoveryAttempt with native {reference,generation} and exactly one of {claim} for local computer work or {expectedAttempt} for non-local/HOST work. The latter locks the current session, rejects stored local execution including delegated scope, and compares its exact park generation/attempt count. Both require a current park, budget below 3 and working activity or a retained platform envelope. Admission increments only the existing park_attempts; boot ownership/wake spends nothing. The first provider preflight owns invocation, never an early sweep or a fabricated computer wait.

ParkedConversationRecovery defers an already-final claimed local owner's activity settlement to its existing bound transaction. With no completed queued replay it retains the original turn/activity fence; a real completed replay replaces it with the last replay's TurnCompletion.activity receipt, captured under the runner's send/admission lock after opened.begin() and before execution. assertComputerPrerequisiteClaim retains native ownership locks while recovery checks the generation, park, exact current working turn/activity sequence against that captured fence and absence of retained input, pending delivery, a platform envelope or an unanswered view. Canonical withoutTrailingChatEvents plus finishedAnswerOf must prove a surviving settled final assistant answer. The transaction settles the exact wait before the existing settleSessionActivity CAS, then clears the park; a refused protected CAS throws and park-clear failure rolls all writes back. Activity notification runs after commit. Settlement adds no provider admission or budget reset; genuine no-dispatch completion leaves dispatch_admitted and park_attempts unchanged, while actual queued dispatch uses the existing admission/budget owner. Ordinary metadata activity setters still refuse active pre-dispatch waits.

The existing delegation updateClaimedRunProgress(parent,call,raw,claim?), completeRecoveredRun(parent,call,raw,claim?) and markRecoveryRequired(parent,call,reason,raw,claim?) accept an optional trailing ComputerPrerequisiteClaim. Without it, only the existing same-boot recovering path is eligible. With it, native running or recovering is allowed only for this boot's exact row_id === claim.generation and delegated parent/tool-call/child relation, held prerequisite id/version/boot, authority fingerprint, Project and current lifecycle. Internal lockDelegatedComputerPrerequisiteClaim reuses the canonical locked claim; validation and write retain native/prerequisite locks in one transaction, not an earlier detached assertion or fake boot transition. Successful completion preserves the unprocessed-helper check. Error completion cannot consume helpers and instead retires orphaned deliveries with discardOrphanedDeliveries in the same transaction. Both atomically enqueue the result, terminalize the run, clear boot ownership and settle its wait. Failure atomically enqueues an ordinary error result that wakes the parent, sets recovery_required and cancels the wait. Stale repark/reclaim tokens refuse all three writes. Injected terminal settlement failure rolls run, inbox, wait and notification back together.

ChannelSessionService.abortTree uses cancelSubagentRun(parent, result) for explicit Stop rather than the recovery-claim completion path. This error-only transition locks the parent/child relation and call, cancels any nonterminal lifecycle across boot ownership, clears ownership, enqueues one parent result and cancels the exact computer prerequisite. It uses the same terminal writer as ordinary completion; already-terminal calls are unchanged. Pending helper results are retired through the existing orphan predicate only when no call for their recipient remains open. Ordinary completion still requires a running call owned by the current boot; recovery completion still requires its original claim. After the owner's targeted Stop returns from this teardown, DelegatedSessionService.stopForOwner schedules the shared parent result drain; the terminal writer itself starts no turn. Successful completion still requires every helper to be processed.

Repository implementation does not itself migrate every core recovery consumer.

Restart pause checkpoints validate the complete queued input before DELETE and write nonempty queues with one typed UNNEST INSERT. Checkpoint and take retain the same session protection and atomic ordered consumption. Boot delegation reconciliation reuses protected parent journal facts only within its current claim transaction; claim ownership and supersession remain unchanged.

Conversation names and pins

The journal metadata owner persists brain_sessions.pinned_at and title_manual through migration 0006_conversation_pins.sql. setSessionPinned(id, userId, pinned) is a single owner-checked UPDATE; it preserves pin time on repeated pins and clears it on unpin, without moving conversation activity. TranscriptReads.sessionListing selects pinned_at in its existing query and the owned service DTO exposes pinnedAt: string | null. The history panel and CLI therefore need no additional read. Core refuses channel or foreign metadata targets with the existing owned-user-session predicate.

Explicit names use journal.renameSession, which marks title_manual = 1. The automatic titler's compare-and-set also checks this flag, so even a manual name equal to the provisional title wins. TurnAdmission.preparePreflight writes an optional first-message name on the caller's admission transaction, together with the user entry, and only while the conversation still has no title. It applies the existing rename normalization through normalizeConversationTitle: collapsed whitespace, a 120-code-unit cap and refusal of an empty title. Omitting the name keeps provisional and background automatic titling.

Transcript composition in core

BrainStatusService.readSnapshot binds durable status, listing, history and stream-snapshot reads to one read-only repeatable-read transaction. Configuration and policy readers are prepared before SQL and rebound with the repositories. Status and stream snapshots enrich live execution metadata and process telemetry only after releasing that transaction: computer-target resolution and process ownership checks use services backed by the root pool. The fixed-session SSE consumer keeps the same snapshot shape; execution metadata is omitted when includeExecution is false and unwired process telemetry returns an empty list.

Transcript authority getters return raw parent_session_id/execution_ref/delegated_access columns, preserving JSON text for the canonical core project-execution and delegated-scope parsers. hasDelegatedAccess now returns that raw row for one sessionId; core performs the supplied-scope normalization/equality check. Missing or corrupt authority never becomes host execution.

Session listings return raw latest-turn rows and workMode rather than a computed plan. Full listings also read unanswered brain_view_waits for all listed session ids in one query and group the original JSON text into each control's viewWaits. Refs listings do not read waits. BrainService forwards those rows through its snapshot adapter into ElicitationRegistry.pendingForSession; even an empty supplied array suppresses a per-session query. Live questions and approvals retain precedence, and persisted views use the same parser and errors as individual status reads. Personal and administrative listing DTOs remain unchanged. delegation.recoveringWorkflowIds(parentSessionIds) reads all listed workflow parents in one query on that snapshot and returns a map including empty arrays for requested parents without current-boot running claims; the single-workflow liveness path requests a one-parent batch. Service composes the existing DTO through brain/messageView's pendingSubmittedPlan. Transcript paging returns bounded raw BrainEntryRow chunks; service applies canonical rendering, pending/echo rules, tool pairing and visible lookahead within one shared snapshot. API receives the final service DTO. packages/db/src/repositories/transcript.ts declares the exact types.

artifactStillReferenced remains a persistence predicate using the store-owned workflow codec relocated once into the package delegation domain. Its persisted metadata validators move with it; core keeps message rendering, project/scope policy and tool-result selection. No package imports src/ or duplicates a core parser. All runtime callers use the package copy; the old store codec is removed.

Problem outbox operations begin with await tx.repositories.config.problems.lockSettings() and keep all outbox/reporter reads/writes on that same transaction. The lock lasts through outer commit; HTTP is outside. Absent settings uses normal fresh initialization; existing unreadable bytes stay untouched.

Journal, projections and bytes

Forward schema migrations own the journal parent access path (0008) and selective table-local autovacuum policy (0010) on both owned and external databases. They do not modify cluster globals or restart services. The scalar (session_id,parent_entry_id) B-tree supports descendant repair and orphan lookup without payload decoding. Index rollout requires a scheduled transactional write-blocking window; later policy rollback uses another forward migration. Journal tables use vacuum 500/0.05, analyze 500/0.02 and insert vacuum 1000/0.05; provider segment, chain and request tables use vacuum/analyze 500/0.02 without insert overrides. Other tables retain their existing options.

The journal remains the replay authority. Store entry_json, request segments and other hashed/replayed payloads as PostgreSQL json using their original string; select them as ::text. Never reconstruct stored bytes through pg's object parser, jsonb or a parse/stringify roundtrip. Intentional journal edits preserve typed property insertion order and change only their requested fields.

Real data contains escaped NUL in journal and request JSON. PostgreSQL json accepts it, but native JSON extraction can fail on an unrelated field. Every byte-sensitive payload is therefore parsed with standard JSON.parse and typed validation inside the owning repository. Journal writes supply entry_id, parent_entry_id, entry_type, is_meta and message_role explicitly. The two generated attachment flags inspect undecoded JSON text only.

Structural ids with actual U+0000 fail explicitly with row/field diagnostics. Derived attachment filename candidates map NUL to U+FFFD and remain subject to canonical authorization; NUL cannot be an operating-system filename. U+0000 elsewhere in payloads is supported. Derived search/display prose maps it to U+FFFD, while original payloads, replay and hashes remain unchanged. Search applies the same mapping to candidate terms and checks matches against original text. Remaining jsonb metadata has a separate explicit NUL validation rule. The complete per-column policy is in the schema contract.

A journal mutation updates branch projections, attachment refs, search text and usage facts on its bound transaction. For initial spawn capture and captures without fresh rows, the PI persistence projector passes its expected active leaf as the fourth argument to journal.appendJournalEntries; the writer compares it with committed state under session protection before selecting the captured leaf. A stale capture retains new journal rows and usage, but keeps the newer committed branch and publishes its actual leaf. Initial capture expects the first new entry's parent, or the restored leaf when every entry already exists. Repeated captures expect their unchanged leaf. Subsequent genuine native appends and explicit branch-selection callers omit the expectation. Projection repair reads committed rows on that same executor. Attachment and search internal projection helpers are implemented once by their repository owners and invoked by journal; their reads use the projected rows directly, with no readiness state. Set-based deletion repairs the surviving subtree once, rather than once per deleted row. Presence and usage-total SQL triggers do no payload decoding. Usage rows, origin counters and provider counters remain separate accounting authorities.

Journal append prepares payload bytes and validated headers once, batches identity/parent/leaf checks and retired-collision reads, and reuses the prepared headers at the canonical insertion SQL. Existing per-entry effects, projection repair, transactional sequence allocation and expected-leaf comparison remain unchanged. Newly inserted identities become available as parents only in input order; there is no separate append-only projection path.

Paid compaction resolves the active origin pin's payer, otherwise the durable session owner, before calling journal.appendJournalEntries with payerUserId as its fifth argument. The existing lockJournalSessions protocol locks sorted owner and payer accounts before session protection or usage projection rows; the journal fact and accounting callback still share one transaction. attachments.recordSharedAttachment uses the same protocol before checking session existence or duplicate references, preserving the user-before-session order used by mark-read and append.

prepareBrainJournalProjection stages JavaScript-parsed structural inputs in a transaction-local table with native identity and parent indexes. Source payload reads are ordered by session and sequence and bounded to 128 rows and 256 KiB, allowing one indivisible oversized entry. The same SQL inheritance and compaction-cut projector serves staged inputs and live descendant repairs; it never decodes journal JSON in PostgreSQL. The staging table and source cursor are removed before readiness publication.

Explicit bigint identity columns replace live rowid cursors. Journal seq is a transactional per-session allocation rather than a PostgreSQL sequence; aborted allocations do not introduce a new journal gap. Existing rewind/insertion semantics still apply.

PI's synchronous append and event subscription contracts remain immutable. Brain-rest owns one SessionPersistenceProjector with synchronous observe(event) capture and async flush(). SessionFactory returns that same handle and LiveBrain.persistence stores it; runtime guards and the service drain it before provider/tool execution, admission, terminal publication and teardown. pause() synchronously seals capture, exposes the process-lifetime paused promise, then awaits the existing queue, the factory's provider-request drain and any compaction accounting already admitted before the seal. The recorder's single captureWrites queue includes request startup and its multi-statement store.start(), response metadata and terminal writes. Its drain awaits admitted database operations, never the process-lifetime pause promise; onPayload rechecks the seal after capture before returning a dispatchable payload. Ordinary projector flush() remains a captured-watermark drain for checkpoint publication; it does not reopen the session. Event reduction ignores later PI events, and provider/tool dispatch and completion suspend on paused until process exit, preserving the committed transcript and park marker for boot recovery without awaiting the whole turn. A write failure latches the existing persistence refusal and prevents dispatch. Neither an async PI subscriber nor a second queue substitutes for this boundary.

The persistence latch reports brain.transcript_write_failed once for its first failure. Repeated refusal of the same cause does not report another write; distinct secondary causes remain local diagnostics. PersistenceRefusalError carries the accepted-turn failure to the HTTP settlement boundary, which still publishes the failed outcome but does not report a second brain.turn_failed. The guarded model stream marks its synthetic assistant error with persistenceRefused:true; terminal provider classification uses that origin rather than error prose, so a refused local write cannot become provider.request_failed. Actual provider and lifecycle-capture failures retain their own reporting.

Search and vectors

Conversation candidate text is stored in brain_search_rows.message_text with a pg_trgm GIN index. Canonical shown-text extraction, literal-match authority, visibility and branch checks remain. The supplied production measurement was about 38 MB of user/assistant text in 3,689 rows on a 6.1 GB source database; this is text size, not a PostgreSQL index/TOAST estimate.

search.searchMessages(userId, query, limit = 50) accepts a personal user id or explicit null for the managed instance universe. The existing /brain/search consumer resolves that value through resolveUserReadScope: missing or malformed scope remains caller-only, and only an administrator may request scope=instance. Authorization precedes even the unavailable-engine empty response. A personal search excludes channel sessions regardless of owner; instance search uses the managed listing's fork_copy_pending and emptyShell predicates across every account, including channel and cron conversations. This read grants no metadata-write or continuation authority.

Search keeps user/assistant shown text, excludes discarded and meta entries, and validates each candidate against the active journal branch in one repeatable-read snapshot. Queries shorter than two trimmed characters return no hits. Results remain newest-first by message timestamp and journal row identity, with 50 hits by default and at most 500 literal candidate matches considered; this is a bounded search, not an exhaustive conversation count. Journal mutations project personal and channel shown text through the same searchMessage parser; the SQL read owns scope selection. Instance candidate reads use projected text for indexed channels and personal conversations; only channel entries missing a sidecar fall back to original journal text. Channel candidates must also contain the literal query in canonical shown text before consuming the 500-candidate budget, including short queries that use raw candidates, so JSON metadata cannot crowd out older genuine hits. Personal raw-JSON literal authority and active-branch exclusions remain unchanged. No migration, backfill, new transcript authority or browser-side transcript fan-out is required.

Memory embeddings use untyped halfvec storage with positive-width checks and support up to 16000 dimensions. Search and recall compute exact cosine scores. There are no approximate vector indexes, activation hooks or index-specific configuration limits. Score and body hydration share a snapshot; ownership, vector space and current body hash are mandatory. The old in-memory memory-vector cache and revision table disappear. Real-memory acceptance revalidates the existing semantic threshold after fp16 rounding.

memory.searchActiveEmbeddings(userId, active, queryVector, minimumSimilarity, limit, sharedCategoryIds?) serves MemoryService.findSimilar and the curator's MemoryService.searchSemantic. It computes every eligible cosine exactly in SQL, applies the inclusive threshold, orders by similarity, update time and id descending, and limits before hydrating selected bodies in the same statement snapshot. Results contain only rows and scores. Zero vectors score zero; current body hashes, embedding-space identity and live shared grants remain mandatory. Semantic search retains keyword fallback. GET /memory lists accessible rows with status, kind and category filters; its unused q search path is removed. It only uses the shared MemoryService for vitalityOf, while curator semantic search stays available. Full recall continues through scoreActiveEmbeddings with all scoring metadata but no vectors in its result or sort. MemoryService keeps the exact importance/vitality blend and stable tie order, then calls loadEmbeddingVectors for bounded batches in weighted rank order as packing needs them. The batch uses the same access, freshness and space checks in the same repeatable-read snapshot. Rejected candidates can advance to later batches, so no similarity-only top-k truncation changes budgets, exclusions or paraphrase comparison. Recall accounting groups UNNEST input ids by multiplicity, updates each accessible memory once and inserts one usage event per accepted occurrence in the same SERIALIZABLE statement.

The content-addressed semantic store is separate: semantic_vectors holds packed Float32 bytea for docs/api/palette keyed by corpus, space and text_hash, and semantic_vector_state holds each corpus revision. It preserves the existing bounded ranking/cache owner. Its insert/delete triggers invalidate only the affected corpus. Retired docs_search_chunks, docs_search_meta and search_vectors are absent.

Memory maintenance admission uses memory.maintenance.create(userId, job): Promise<boolean>. One INSERT with the existing partial unique owner/operation constraint, ON CONFLICT DO NOTHING RETURNING, decides the winner; its writable CTE also retains the existing receipt cleanup. A losing attempt changes nothing. MemoryMaintenanceService.startReindex and startRecategorize return {job, started}: only the winner runs work, while a loser reads the accepted receipt and reports that it is already running. API routes expose only job, retaining the existing response shape. Memory tools and API catch the canonical MemoryMergeConflictError exported by elowen-db.

Account presets

ServerDeps.services carries the operation services constructed once by buildBrainCore through createCoreServices; HTTP never builds a second Project, preset, Microsoft or memory service. Repository ownership remains the process database. API fixtures use the same factory and can omit optional stores; preset routes still return 503 without user settings. Preset changes from HTTP and Microsoft provisioning reach the same post-commit reapplyAccountSettings callback on the live brain.

users.presets owns preset rows, unique names, revision CAS, grant materialization, assignment and detachment. AccountPresetService retains validation and ProjectService runtime preparation. It then rechecks preset, role and membership snapshots through one bound SERIALIZABLE transaction, adds required memberships before committing removals, and preserves the last-active-project exception. Runtime effects already completed are not repeated by a SQL retry. Reapplication happens after commit.

Preset settings retain owner -presetId, instance defaults owner 0 and positive account ids. Resolution remains personal, preset, instance, code, with whole behavior blobs replacing rather than merging. Chatbots cannot attach. Manual grants detach atomically; deleting a preset removes negative-owner settings/revisions and clears its configured new-account pointer. Project/model deletion prunes affected preset grants/projects in its owning transaction. Live authorization still uses materialized account columns.

Stored settings bytes

settings.data and user_plugin_config.data are text; user_settings.value remains text. Config parses the original strings with JSON.parse and domain validation. Invalid JSON, arrays, invalid model values and throwing accessors retain the singleton's typed unreadable health, closed runtime defaults, refused writers and skipped boot writers without changing bytes/revision. Only explicit reset replaces an unreadable singleton. Per-account JSON settings retain unreadable-key guards and explicit named resets. Plugin-slice reads and maintenance preserve malformed rows; explicit set/setDefaults may replace them under revision CAS.

The migration engine copies all three columns as byte-sensitive text, including corrupt JSON, and the verifier compares exact bytes/hashes rather than parsing them. No json/jsonb conversion may normalize or reject this supported persisted state. packages/db/tests/config/configStoreUnreadable.test.ts is the real singleton consumer contract.

Config.update records problem-reporting consent through the events-owned recordAdminSetting input containing old/new values, ProblemConsentActor and resulting revision. It awaits the transaction-bound events method after the settings write. Settings, consent audit, vault/cascade changes and disabled-reporting outbox cleanup commit or roll back together under C5-018.

Interactive view waits keep the structured view document as byte-preserving json, while answer is nullable text containing the exact user action string. SQL NULL means unanswered; JSON-looking answers such as 'null' remain text. The journal write/CAS and readers never parse or reserialize answer, and migration verifies its original bytes. The id/session_id columns remain text identifiers.

Account TOTP cryptography

packages/db/src/users/totp.ts is the pure internal owner used by users.accountSecurity for enrollment and all OTP-consuming transitions. It has no database, clock, brand resolution, persistence or notification side effects. Its four exports are generateTotpSecret():string, totpCode(secret,atMs):string, verifyTotpCode(secret,code,atMs,lastAcceptedStep):number|null and buildTotpUri(secret,issuer,account):string; it is not a plugin API or a configurable MFA framework.

Generation uses 20 cryptographically random bytes and exactly 32 uppercase, unpadded RFC 4648 Base32 characters. Malformed stored secrets throw a content-free TypeError rather than becoming an ordinary wrong-code result. RFC 6238 uses HMAC-SHA1, six digits, T0=0 and 30-second steps. The moving counter is an unsigned eight-byte big-endian BigInt, not a bitwise timestamp. Callers supply authoritative Unix milliseconds as a nonnegative safe integer; invalid time or persisted high-water marks throw RangeError.

Verification accepts exactly six ASCII digits, including leading zeroes; it does not trim or normalize input. It evaluates current−1, current and current+1 with fixed-length timingSafeEqual comparisons and no first-match exit, including already-consumed steps. Only the nonexistent negative counter at the Unix epoch is omitted. The result is the highest matching step strictly above lastAcceptedStep, or null for refusal. The transactional caller must lock the account, load its global high-water mark and atomically persist the returned step with its authorized mutation/token/audit. The helper alone does not prevent concurrent replay or rate-limit guesses. Future-step acceptance and clock rollback never relax the mark.

For example, enrollment calls generateTotpSecret() and buildTotpUri(secret,resolvedIssuer,username); confirmation calls verifyTotpCode(secret,code,serverTime,lastAcceptedStep) before committing activation. The issuer comes from resolved server branding, not request host/client input. URI generation uses URL, encoded label components and URLSearchParams, explicitly setting SHA1, six digits and period 30. Both labels replace colons and Unicode control/format characters with spaces, trim outer whitespace and keep at most 120 Unicode code points without splitting surrogate pairs; empty normalized labels throw. The normalized issuer prefix and issuer query value are identical. These are display-only labels: full account identity remains in its owning row, and renaming does not rotate a secret.

The work is bounded to one 20-byte generation, one HMAC for code presentation or at most three HMACs for verification. Nothing subscribes or runs in the background. Neither secrets, codes nor provisioning URIs belong in logs, diagnostics or durable public projections. packages/db/tests/users/totp.test.ts covers the published SHA-1 vectors (eight-digit presentation exists only in tests), production six digits, boundaries, large counters, real adjacent-step collisions, replay, strict input/Base32 and URI encoding. Algorithm and provisioning references are RFC 6238 and the Authenticator key URI format.

Encrypted plugin configuration

config.pluginSecrets and config.pluginConfigSecrets keep the existing vault, typed corruption errors and manifest-authorized secret projection. Configuration and vault changes share one bound revision transaction. No declared secret falls back to plaintext. Masked settings snapshots retain secretsSet and secretsUnreadable; corrupt rows remain replaceable/clearable without making runtime reads succeed.

Authoritative preparation retains atomic migration of existing declared plaintext JSON values, including disabled manifests and populated accounts; conflicts abort and only affected revisions advance. Discovery publication waits for the outer commit. Only a daemon with explicit allowKeyInitialization may initialize key/canary state, and only an authoritative daemon performs plaintext-secret preparation. Runners/workers and migration-disabled daemon wiring pass both flags false and retain typed unavailable readiness for absent or mismatched vault state. Strict secret-manifest discovery includes disabled plugins, propagates errors and caches only a successful scan; it is separate from best-effort model/default discovery. Initial boot preparation follows successful ordinary discovery, while subsequent secret access still requires a successful strict scan. A runner reads the prepared encrypted values and never performs authoritative migration. Secret namespace factories are async; their returned bags retain plugin/account scope and the existing value bound. Credential-refresh withLock retains its existing cross-process external-IO lock and is not a long-lived retried SQL transaction.

Users.delete removes vault rows within its SQL cascade, then invokes the internal CoreUsersRepositoryContext.forgetCorruptRowsForUser hook through boundSql.afterCommit. Assembly supplies that exact synchronous hook from the callable CoreConfigRepositoryFactory, retaining the same process-local corruption index used by readiness. Rollback/retried attempts retain the index, and the hook performs no SQL or filesystem IO. It is internal composition, not a public secret-store operation.

The baseline's partial idx_auth_tokens_advisor_user enforces one advisor token per account.

Plugin configuration writes

Missing or blank declared secret fields preserve a value, null clears it, and stale revisions return masked conflicts. Bulk replacement validates all declared values and preserves its existing full-slice semantics; PATCH preserves omissions. Vault publication, non-secret JSON and revision CAS commit together. Failed writes do not request restart. Account defaults retain user 0 and never inherit declared secrets or apply to chatbot accounts; clearing own values retains the revision row and encrypted secrets.

Settled delegated results

Owner and delegated-parent result delivery read transcript.settledResultIds(sessionId, resultIds): Promise<Set<string>>. Only settled, undiscarded custom_message journal entries carrying details.resultId prove delivery, including entries before compaction. Live PI context and queued steering messages are not delivery evidence. Empty candidate lists perform no query; provisional and abandoned entries prove neither delivery nor consumption.

The session persistence projector treats an ingested subagent-result custom message with a string details.resultId as accepted external input: that entry is settled immediately, while the surrounding assistant/tool work remains provisional until its own settlement. In the same database transaction as the fresh journal append, it reads delegation.pendingSubagentResults through the bound repositories and calls the existing bound acknowledgeSubagentResult for matching subagent and whole-workflow results, including failed recovery. Payloads are decoded in TypeScript, never with SQL JSON extraction. A rollback leaves both the journal and inbox unchanged. Repeated captures and respawn snapshots do not acknowledge or publish the same result again.

LiveSessionSpawner passes its optional onResultDelivered?: OnResultDelivered dependency through SessionSpec to createSessionPersistenceProjector. The callback receives (sessionId, results: { resultId; toolCallId; kind: 'subagent' | 'workflow' }[]) after commit and connection release, once for the acknowledgements won by that append transaction. BrainService uses it to publish result-delivery events; daemon and runner PI sessions share this persistence path. The existing ordered flush waits for the callback before the next provider request. With no callback, durable acknowledgement still happens, but no result-delivery event is emitted here. A callback failure propagates through the existing persistence failure latch without undoing the committed result or retrying publication.

Recovery failures use the same commit-time acknowledgement and publication. Existing payloads carrying requiresUserAction: true need no migration: pendingSubagentResults and normalizeSubagentResult project only known fields, so the obsolete key is ignored and the row delivers as an ordinary error. Results already recorded are acknowledged once by the recovery drain without resending; a remaining owner batch starts at most one parent answer. A genuinely parked unanswered owner continues through the existing parked-result continuation.

Activity rollups

usage_by_origin alone owns origin-attributed spend and activity. usage_by_provider_day includes paid provider requests such as embeddings and need not agree with origin totals. Conversation usage derives from journal projection and its reduced session/descendant totals. These three authorities remain separate, with no invented cross-counter repair or journal fallback for global spend. Voice receipt dedupe, cumulative checkpoints and counter writes commit together; duplicate receipts add nothing.

Conversation deletion and retention use the DELETE-only transition trigger from 0009_usage_delete_transition.sql. A second-row probe skips aggregation for empty/single-row deletes; multirow statements group normalized usage by session, epoch and accounting kind. One MERGE action updates a surviving bucket or removes an emptied one, without payload decoding or historical totals rewrite. Session deletion, retirement and rewind keep their source/sidecar transaction and sorted owner/session locks. INSERT/UPDATE results remain identical and their unused DELETE branch is removed.

The usage_by_origin key includes target_id, with the empty string for host, managed and non-conversation work. Each settled local turn records its computer and terminal error count. Failure-only settlements add one error without another turn or token charge. Redaction retains the target dimension. usage.origins.targetDays supplies device usage; executor dispatch records only command/file-edit counts in cli_device_activity_days, whose rows follow token/account deletion. Revoking a device retains its origin spend.

Provider usage has one daemon-owned schedule. Subscription network reads and quota persistence finish there; routes read the last account-keyed snapshot. Poller changes and successful account spend writes publish the existing content-free provider-usage invalidation, which creates no event-feed row. continuesPastCap survives validated quota snapshots so available Codex credits are not hidden by a capped plan window.

Plugins

The host checks reads:['db'] before await ctx.db(). src/daemon/brainCore.ts binds the process-owned database directly through createPluginDb from elowen-db; packages/db/src/pluginDb/index.ts owns the only generic plugin database implementation. The generic package adapter supplies schema-qualified query/one/run, transaction, migrate and appliedVersion. Canonical names are plugin_ plus the exact validated manifest name, quoted as SQL identifiers. There is no prefix collision from folding hyphens. Plugin handles expose no synchronous statement API.

Plugin transactions expose tx.host, containing the existing capability-gated projects, userProjects, usersRead and conversationsRead facets on the same executor as plugin SQL. The host keeps one canonical facade builder and all existing per-call identity/ownership policy. Filesystem-backed pluginsRead remains outside. The root handle has no host accessor; root stores access inside a transaction throws, including previously captured facades/methods, through one package-owned async transaction scope. Bound SQL and host methods expire when the callback finishes. Sandbox beginDeletion/finishDeletion and its own rows therefore share one outer commit or rollback, including deferred failure; runtime/storage IO remains outside. The plugin contract fixes the generic package types, specialized core host type, construction binding, capability/lifetime guards and owning tests.

Daemon migration steps and their plugin_migrations ledger rows commit atomically under a transaction advisory lock. A runner's migrate is the existing logged skip; it cannot create or drop a schema. Uninstall authorization belongs to the host, while the package drops the exact schema and ledger rows together. See the plugin contract.

Schema changes, migration and retention

Migration 0001 is the PostgreSQL baseline. Later changes add contiguous numbered SQL files. The runner checks exact filename/checksum history and executes each version in its own transaction under pg_advisory_xact_lock. Drift and unknown versions fail. Never edit a shipped migration or run migration work from a runner.

Retention archives are gzip JSONL containing original entry strings. Maintenance writes and verifies the archive before retiring bodies and committing markers in bounded transactions. Before session rehydration or a peer fork, core reads maintenance.journalArchive(sessionId) and calls maintenance's standalone readJournalArchive(identity) outside every SQL transaction. The reader returns verified original JSON strings keyed by entry id, their sequences and the observed marker identity, retaining full PostgreSQL archived_at precision. restoreArchivedJournal(sessionId,prepared) and journal.forkSession(sourceId,newId,prepared) require those prepared bodies or explicit null for an observed absent marker. SQL locks and rechecks that exact marker, rejects stale preparation with ArchiveUnavailableError, validates all originals before any replacement and commits all bounded restore batches with marker removal. Fork invokes the shared internal restoreArchivedJournal(boundSql,context,sourceId,prepared) helper from maintenance/restore before inserting the target, then copies the full active branch in the same transaction. It shares maintenance's marker validation, exact-byte restoration and bounded pages rather than maintaining a second archive codec or restoration path. A conflict rolls everything back; no filesystem read occurs inside the SQL callback. packages/db/src/repositories/maintenance.ts and journal.ts declare the signatures. The retention command operates in place.

Provider admission across the synchronous PI callback

The native preflightResult callback records only its disposition. Session's SessionProviderPreflight.run scopes an awaited guard at the existing ModelRuntime.streamSimple seam: drain committed persistence, await service's durable preparation, then call the inner runtime. A rejected guard never reaches transport and its original error is rethrown after PI settles.

Service's preparePreflight and turn-context prepareProviderPreflight await SQL on bound repositories and return local publication closures. The owning READ COMMITTED transaction commits before acceptance/recall publication. Preparation failures roll it back and prevent provider dispatch. Pending delegated results do not gate provider admission; their settled journal receipt is the sole delivery authority.

Diagnostics and verification

The package times queries and pool acquisition separately. Queries over 250 ms log a stable fingerprint, caller, duration and failure flag without SQL values. A protected local statement map holds SQL text and never enters telemetry. /health exposes database pool, wait, recent slow-query and connection-error information; writerLock is removed.

db.measurements() exposes bounded process-lifetime query aggregates for explicit caller labels, outer transaction-attempt durations and separate user-lock acquisition/post-acquisition hold intervals. Unknown query callers share other; no SQL, parameters, payloads or identities enter the snapshot. Hold starts at the first successful nonempty tagged query result and ends after actual outer commit/rollback and connection release, not at a nested atomic return. Retries and rollback remain distinct from commit; afterCommit is excluded. Pool waiting remains a separate health metric. Health/HTTP stays unchanged, with no route or automatic telemetry added. One private exact-text fingerprint per fixed caller avoids repeated hashing without caching dynamic labels. The isolated driver benchmark and delayed competing-transaction regression are the consumers; these intervals neither identify server blockers nor promise production durability timings.

The test harness creates its own PostgreSQL 18 cluster with a private Unix socket and no TCP listener, migrates a template through the real runner, and clones one database per worker. Before each test, TRUNCATE RESTART IDENTITY resets application tables and preserves the migration ledger. Tests needing seed rows explicitly initialize their fixtures after reset. Schema-changing tests and tests needing independent databases use fresh template clones named with a UUID, so Vitest pool-id reuse between files cannot collide with an earlier clone. The setup registers owned pool/clone cleanup through each test's onTestFinished finalizer, which runs after suite teardown even when a nested afterEach throws; a parent afterEach is insufficient because Vitest skips it after that failure. File teardown also closes handles left by file setup. Cleanup errors remain test failures, and borrowed core/database handles must still be closed by their owning fixture before the clone is dropped. The harness neither raises max_connections nor retries allocation/cleanup.

Run focused package/repository tests inside the repository isolation boundary with at most two Vitest workers and the heavy-command gate. The earlier 85-table benchmark measured 27.3247 ms mean and 51.6575 ms maximum over 900 resets, about 24.59 seconds total. It is a tmpfs/fsync-off test budget. Package typecheck, typed repository compilation, focused ESLint and migration regressions precede the final integration gates.