Skip to content

storage.md

Latest commit

 

History

History
715 lines (624 loc) · 36.8 KB

storage.md

File metadata and controls

715 lines (624 loc) · 36.8 KB
Raw

Storage — single DuckDB store, CRDT persistence, audit/rebuild

The storage layer carries three concerns:

  1. Canonical markdown — the source of truth; everything else is derivable from it.
  2. The single per-tenant DuckDB store — relational metadata + the typed link graph + block-level retrieval (HNSW via the vss extension, BM25 via the fts extension on the same blocks table) + the Loro CRDT op log + the frontmatter index.
  3. External attached data — read-only DuckLake catalogs mounted as external.ducklake for the origin axis, via DuckDB's ATTACH mechanism. (Spec concept, not yet built — do not confuse with the DuckLake storage backend below, which is a different, shipped feature: escurel's own derived index living in DuckLake, not a foreign read-only source.)

All three sit behind one trait, LaneStore, so the filesystem-backed default and the S3-backed variant share the upper layers verbatim. The Rust port preserves the 28 end-to-end assertions and the spike outcomes T1–T6 from the Python prototype as the acceptance baseline for indexer behaviour.

Per-tenant directory layout

${ESCUREL_DATA_DIR}/tenants/<tenant_id>/
├── manifest.toml              # tenant metadata, quotas, embedding provider
├── markdown/                  # canonical source
│   ├── skills/
│   │   ├── customer.md
│   │   ├── meeting.md         # event-typed skill
│   │   └── escurel.md         # the mandatory meta-skill
│   └── instances/
│       ├── customer/
│       │   └── acme-corp.md
│       └── meeting/           # event instances live here, not in a separate "events/"
│           └── 2026-04-12-acme-qbr.md
├── escurel.duckdb             # single DuckDB file: pages, links, blocks
│                              # (with vss + fts indexes), crdt_ops,
│                              # crdt_snapshots,
│                              # external_credentials (sql_view secrets)
├── blobs/                     # document-backend canonical originals (sha256-keyed)
│   ├── inbox/                 # deposited-but-not-yet-processed uploads
│   └── <sha256>               # ingested originals (PDF/DOCX/…); chunks derive from these
├── external/                  # DuckLake / Iceberg / Delta catalog mount-points
│   └── ducklake.config        # ATTACH parameters per attached catalog
└── cache/
    ├── embeddings/            # warm cache for re-embed parallel
    └── compacted/             # staging for `compact_lanes`

This layout is the export format: tenant_export produces a deterministic tarball of the above directory minus cache/. The blobs/ subtree is part of the canonical corpus (a document instance's chunks are re-derivable from its blob, but the blob itself is not), so it is included in the export. The external_credentials table holds sql_view secrets and is exported with the DuckDB file. tenant_import is the inverse.

Under the S3 LaneStore the same tree maps to S3 keys via:

s3://<bucket>/<prefix>/tenants/<tenant_id>/
├── manifest.toml
├── markdown/skills/customer.md
├── markdown/instances/customer/acme-corp.md
├── escurel.duckdb
└── external/ducklake.config

The cache/ subtree is per-node and never lives on S3 (model weights cache and embedding-recompute staging are local). Spool (spool/<tenant_id>/) is similarly local and never synced — see the S3-backend-unavailable row in the crash-recovery summary.

The LaneStore trait

#[async_trait]
pub trait LaneStore: Send + Sync + 'static {
    /// Plain byte read; used for markdown files and small artefacts.
    async fn read(&self, key: &Key) -> Result<Bytes>;

    /// Atomic write-then-publish. Returns the new content version.
    async fn write(&self, key: &Key, body: Bytes) -> Result<Version>;

    /// Enumerate keys under a prefix. Used by audit + tenant_export.
    async fn list(&self, prefix: &Key) -> Result<Vec<Key>>;

    /// Used by `compact_lanes` and tenant_delete.
    async fn delete(&self, key: &Key) -> Result<()>;

    /// Object-store URL form, suitable for handing to DuckDB
    /// (`httpfs`) without copying through this process.
    fn url(&self, key: &Key) -> Result<Url>;

    // Content-addressed blobs for the Document/RAG backend ride on the
    // primitives above (see §"Instance backends" below):
    //   put_blob(tenant, body, max_bytes) -> BlobId, put_inbox_blob,
    //   get_blob, get_inbox_blob, promote_inbox_blob, delete_blob,
    //   list_blobs.
}

// **Not yet implemented.** A streaming `open_writer(key) ->
// AsyncWrite` handle (a `tokio::fs::File` for FS, a drop-flushing
// multipart writer for S3) is intentionally absent from the trait
// until a caller actually needs it; today all writes go through the
// buffered `write`.

Key is a tenant-scoped relative path (Key::new(tenant_id, "markdown/skills/customer.md")); the implementation maps it to a filesystem path or an S3 object key.

Three implementations ship:

  • FsStore. ${ESCUREL_DATA_DIR}/tenants/<tenant>/<rest>. Writes go to <rest>.tmp and rename(2) to publish (atomic on POSIX same-filesystem). url() returns file://....

  • S3Store. Backed by the official aws-sdk-s3 crate (not object_store), with an explicit endpoint override, static credentials and forced path-style addressing — no ambient AWS credential chain is consulted. Keys are S3 paths under a configured prefix. url() returns s3://..., consumed by DuckDB's httpfs extension when reading or by DuckLake when an external catalog is attached. Writes are a single atomic PutObject (S3 is atomic at the object level, so no temp-then-rename dance is needed and no multipart is used); the returned Version is the object's version-id, falling back to its etag. delete() HEADs first because S3 DeleteObject is idempotent and would otherwise not honour the trait's NotFound contract. The S3 backend assumes only basic S3 semantics (GET/PUT/DELETE/list/multipart-upload, path-style addressing, no STS, no presigned URLs, no AWS-S3-Tables-or-Vectors specific APIs). Verified backends: AWS S3, MinIO, Hetzner Object Storage. Critical: the S3 endpoint hostname returned by S3Store::url() must equal the hostname the LaneStore is configured against — DuckDB httpfs honours the secret's ENDPOINT field literally, so a hostname mismatch between the LaneStore config and the DuckDB ATTACH/secret produces silent unsigned PUTs and 403s on writes. On cattle nodes, /etc/hosts rewrites are not an acceptable workaround; configure the LaneStore against the object-store hostname directly.

  • GcsStore. Backed by the official google-cloud-storage crate. Same object layout as S3Store ({prefix}/tenants/{tenant}/{path}), so one bucket is readable by either backend and a migration is a copy rather than a rewrite. url() returns gs://.... Credentials are Application Default Credentials: the metadata server / workload identity on GCP, or a service-account key file (ESCUREL_STORAGE_GCS_CREDENTIALS_PATH) off it. Writes are a single atomic object write; the Version is the object's generation. Unlike S3, delete() on a missing key is already a 404, so no HEAD-first dance is needed.

    Residency note: the DataZoo substrate's SPEC §5 keeps app/customer data on Hetzner Object Storage and only recovery/integrity-critical data on GCP, so GcsStore is a portability backend for GCP-hosted deployments — substrate tenant lanes must not be pointed at it.

DuckDB happily operates against an object-store URL via httpfs; we do not need to round-trip data through the server process. The S3 and GCS backends are gated behind the s3 and gcs Cargo features respectively; the published container image and the release binaries build both.

Adding a backend

Implement the five required LaneStore methods (read, write, list, delete, url) and override backend() and size(); the blob layer rides on the defaults. Then run the shared contract — crates/escurel-storage/tests/conformance/ — against it. Backend-specific behaviour (URL scheme, client construction, atomicity mechanism) stays in the per-backend test file; everything the trait owes any backend belongs in the shared suite so the implementations cannot drift apart.

Indexer

The indexer is the Rust port of the Python prototype's ~430-LOC script. It has two entry points — update_page (steady state) and rebuild (recovery).

update_page(tenant, page_id, content)

Per-tenant write lock held throughout. Steps:

  1. Parse markdown. Frontmatter, body, blocks (^blk-... anchors auto-synthesised if absent).
  2. Parse wikilinks using the regex-plus-code-region-stripping parser (do not use a markdown AST library — they fragment text on [). Wikilinks are extracted from the body and from frontmatter field values (e.g. about:, derived_from:, primary_sponsor:); a frontmatter value that YAML parses as a nested flow sequence (about: [[skill::id]]) is rendered back to its raw [[…]] markup before parsing. Frontmatter links carry their originating field in links.src_field (frontmatter.<key>); body links leave it NULL. This makes a relationship an instance declares only in frontmatter (e.g. an event whose about: points at its entity) reachable via neighbours.
  3. Validate (the four index-time checks plus required-frontmatter check; events get the extra check that at: parses as RFC 3339).
  4. Embed new/changed blocks (candle EmbeddingGemma).
  5. One DuckDB transaction. Upsert into pages; delete + insert into links (with link_skill and link_version populated); delete + insert into blocks for changed blocks (populating dense_vec, body, denormalised skill/page_type/at_ts); insert any new crdt_ops rows from a live session that closed in this call; refresh the vss HNSW index for the affected rows (PRAGMA hnsw_compact_index or per-row update — see "VSS index maintenance" below); refresh the fts index (PRAGMA refresh_fts_index('blocks') per the FTS extension semantics). Commit.
  6. Publish the markdown file via LaneStore::write — write-then-rename, so the markdown file appears only after the DuckDB commit succeeds. On commit failure the markdown stays at the previous version.
  7. Emit issues (warnings) and return.

rebuild(tenant, scope=None)

Per-tenant write lock held throughout. Drops the affected rows for scope (one page, one skill's instances or the whole tenant) inside a DuckDB transaction and runs update_page for every markdown file in canonical order. Cost on the prototype was approximately 32 ms per page; the single-store path is at least as fast because the write is one DuckDB transaction. Streams progress events to the admin client.

audit(tenant, scope=None)

Read-only. Per-tenant read lock; concurrent reads are fine. Compares two sets: markdown files on disk and page rows in DuckDB. Returns the two asymmetric differences as the audit admin response. audit also reconciles the external backends: managed vw_ views ⟂ sql_view backend_refs (no orphan views) and blobs/ ⟂ document backend_refs / inbox ⟂ ingest Events (no orphan blobs).

Instance backends — storage & indexing

The InstanceBackend seam (markdown | sql_view | document) is a BackendRegistry keyed by skill id on AppState (next to the Indexer, not on it — MarkdownBackend holds an Arc<Indexer>, so putting the registry on the indexer would cycle). Each backend holds an Arc<Indexer> and delegates; the indexer's read/search/write methods stay put. Markdown is bit-identical to pre-feature behaviour.

sql_view. create_instance runs under the per-tenant write lock: INSTALL/LOAD the connector, ATTACH … (READ_ONLY) (the engine rejects write-back — no app-level enforcement needed), CREATE VIEW vw_<deterministic>, then a SELECT … LIMIT 0 to capture a source_schema_fingerprint stored in the overlay's backend_ref. Secrets resolve from the external_credentials table (DuckDB CREATE SECRET), never from markdown. rebuild re-ATTACHes + re-CREATE VIEWs from each backend_ref; validate_bindings re-probes and fingerprint-compares, marking drift binding_degraded (reads fail closed). Only vw_-prefixed managed views are ever projected, and source identifiers / filter fragments pass injection guards. ERPL is unsigned, so its connection is opened with allow_unsigned_extensions only behind an admin opt-in.

document. LaneStore gains a content-addressed area — put_blob(tenant, body, max_bytes) -> BlobId(sha256), put_inbox_blob, get_blob, get_inbox_blob, promote_inbox_blob, delete_blob, list_blobs (default-impl'd on the trait over read/write/list, so FsStore

  • S3 get them for free) — backing blobs/ and blobs/inbox/. max_bytes is the per-blob size quota (oversize bodies are rejected before any write); delete_blob is used by rebuild to reclaim orphan blobs. Ingestion is two-phase to keep the write lock short: the DocumentIngestWorker extracts off the lock (an Extractor: PlainTextExtractor for text/*; KreuzbergExtractor for PDF/DOCX/PPTX/XLSX, compiled in by default — see platform.md), then materialises under a brief lock — chunks become N blocks rows under one page_id, embedded via the Embedder, structurally identical to a markdown page-with-blocks for retrieval/ACL. A DocumentProcessor seam lets a future LLM-driven processor swap in without touching intake/materialise (v1 is deterministic). rebuild re-extracts → re-chunks → re-embeds from the retained blob; the FTS reindex is debounced/batched (dense HNSW is live on append, only BM25 lags).

DuckLake storage backend (writer/reader replicas)

Shipped feature (ADR-0009, design summary docs/notes/2026-07-18-ducklake-architecture.md) — distinct from the read-only external.ducklake origin-axis concept above, which remains unimplemented. This backend lets the derived index (the "single per-tenant DuckDB store" described earlier in this doc) run as stateless read replicas sharing one corpus, instead of one single-writer DuckDB file.

Selected via ESCUREL_INDEX_BACKEND=single-file (default, today's behaviour, byte-identical) or ducklake. When ducklake, ESCUREL_ROLE=writer (default) keeps a local single-file DuckDB as before and additionally attaches a DuckLake catalog — Postgres for metadata (ESCUREL_DUCKLAKE_CATALOG_DSN), Parquet on GCS/S3/local dir for data (ESCUREL_DUCKLAKE_DATA_PATH) — to publish snapshots on demand or on a timer. ESCUREL_ROLE=reader has no local DuckDB file: it adopts the latest published snapshot into an in-memory DuckDB at boot and polls for new ones every ESCUREL_SNAPSHOT_REFRESH_SECS (default 30s), hot-swapping the serving Indexer with no restart and no torn in-flight requests.

ESCUREL_ROLE=writer is single-instance, and enforced. Two writers against one catalog each publish their own snapshot of the whole lake and prune parquet the other just committed — acknowledged writes silently disappear over the following minutes (#371). A writer therefore takes a single-writer lease at boot: a Postgres advisory lock on the catalog database, held on a dedicated session for the process lifetime. A second writer finding it held fails its boot loudly; the lock releases with the holder's session (clean stop or crash alike), so the Kamal STOP-FIRST redeploy hands over without operator action. ESCUREL_WRITER_LEASE=off disables the guard for operators who guarantee a single writer some other way (the lease client speaks plain TCP, so a TLS-only catalog needs this). HA is one writer + N readers, never N writers.

pages/links/blocks (+ the group_members/external_endpoints/ pack_subscriptions registries) are the shared corpus mirrored into the lake; external_credentials never leaves the writer. Three per-user data classes that need strong consistency instead of eventual — chat_messages, events, and crdt_ops/crdt_snapshots — are re-homed to shared Postgres tables in the same Cloud SQL database (chat_pg/events_pg/crdt_pg), attached read-write from every replica including readers; see the design note for the full per-table breakdown and the CRDT scope boundary (durable storage is shared across replicas; live cross-replica session failover is not).

Routing chat and events to the lake instead

chat_messages and events can each be pointed at a table in the lake rather than the catalog's database — ESCUREL_CHAT_BACKEND / ESCUREL_EVENTS_BACKEND = postgres (default) | ducklake, set independently. The surface, row shape, ordering ((ts, msg_id) with ULID tiebreakers) and cursor format are identical; only where the payload physically lives changes. That matters when the catalog and the object store fall under different data-residency rules: on the lake, the payload follows DATA_PATH.

crdt_ops/crdt_snapshots have no lake variant and stay on Postgres.

Three properties the Postgres tables get from the schema, and how the lake variant keeps them (all measured — see docs/notes/discovered/2026-07-25-ducklake-multiwriter-and-compaction.md):

  • Every replica writes. The lake is attached a SECOND time, read-write, under its own alias, alongside the corpus attach — which stays READ_ONLY on a reader. Concurrent read-write attaches from independent processes lose no writes. So readers still serve append_message / capture_event, exactly as with the Postgres variant.
  • Cross-replica read-your-writes. Appends commit per call, so a separate replica sees a row immediately — no publish/adopt cycle. This is why appends are NOT batched into a flush window: batching would have cut the file count but delayed visibility by the flush interval, breaking the property the surface exists to provide.
  • capture_event idempotency. DuckLake enforces no PRIMARY KEY and has no ON CONFLICT, so first-writer-wins is enforced by an anti-join in the INSERT instead. Note this is weaker than a PK: two simultaneous captures of one id could both insert. The runner's own SQLite ledger is what makes exactly-one-run-per-event a hard invariant.

The cost, stated plainly. Data inlining must stay off (inlined rows live in the catalog, which defeats the point), so every append writes its own Parquet object. ducklake's own compaction calls (ducklake_merge_adjacent_files, ducklake_rewrite_data_files, both overloads) are silent no-ops on this shape. What works is the pattern publish_lake already uses for the corpus — CREATE OR REPLACE TABLE t AS SELECT * FROM t — followed by snapshot expiry and cleanup, which is what actually frees the superseded objects. Measured: 40 files → 41 after the rewrite → 1 after GC.

The publish task runs that pair periodically. Because it is what bounds object growth, a lake-backed surface makes the task non-optional: when ESCUREL_SNAPSHOT_PUBLISH_SECS is unset it defaults to 300s instead of staying disabled. An explicit 0 still disables it — the operator's retention math (ESCUREL_SNAPSHOT_KEEP × interval) is never overridden silently; the server logs a warning that appends will not be compacted.

Volume ceiling. Object count between compactions is (compaction interval × append rate); at the documented 120 writes/min per-tenant quota and the 300s default, that is ≤ ~600 objects. Compaction itself is O(rows) — it rewrites the whole table — so retention (delete_chat_history(before_ts)) is what bounds compaction cost, and the interval is the knob trading object count against rewrite volume. A deployment with a chat firehose, or one that never prunes history, should stay on the Postgres variant: the spec's answer for multi-million-row event volume is still an external read-only lake attached for the origin axis, not this operational table.

DuckDB schema

-- Pages: one row per markdown file.
CREATE TABLE pages (
  page_id        VARCHAR PRIMARY KEY,    -- ULID
  slug           VARCHAR,                 -- mutable, indexed but not unique
  skill          VARCHAR NOT NULL,
  page_type      VARCHAR NOT NULL,       -- 'skill' | 'instance'
  frontmatter    JSON NOT NULL,
  body_hash      VARCHAR NOT NULL,       -- WHAT changed (audit); see last_written_by for WHO
  at_ts          TIMESTAMP,              -- mirrored from frontmatter.at (NULL for non-events)
  last_written_by VARCHAR,               -- server-stamped principal of the LAST write (#357/CR-6);
                                         -- the verified token subject, never a caller-supplied
                                         -- field. NULL for pages last written before the column
                                         -- existed. Read back via `expand.page.last_written_by`.
  created_at     TIMESTAMP NOT NULL,
  updated_at     TIMESTAMP NOT NULL
);
CREATE INDEX pages_slug      ON pages(slug);
CREATE INDEX pages_skill     ON pages(skill);
CREATE INDEX pages_skill_at  ON pages(skill, at_ts);   -- event-log scan support

-- Links: one row per wikilink occurrence.
CREATE TABLE links (
  src_page     VARCHAR NOT NULL,
  src_anchor   VARCHAR,
  src_field    VARCHAR,                  -- `frontmatter.<key>` if the link came from a frontmatter value; NULL for body links
  dst_page     VARCHAR NOT NULL,
  dst_anchor   VARCHAR,
  link_skill   VARCHAR NOT NULL,         -- the skill segment of the typed link
  link_version VARCHAR,                  -- the @version segment (NULL if unpinned)
  -- dst_anchor is NOT NULL DEFAULT '' in the live schema: DuckDB
  -- primary keys forbid NULL columns, so "no anchor" is stored as ''
  -- and readers project it back to NULL.
  PRIMARY KEY (src_page, src_anchor, dst_page, dst_anchor, link_skill)
);
CREATE INDEX links_dst_skill ON links(dst_page, link_skill);   -- backlinks
CREATE INDEX links_src_skill ON links(src_page, link_skill);   -- forward links

-- Blocks: one row per markdown block.
CREATE TABLE blocks (
  block_id    VARCHAR PRIMARY KEY,        -- "<page_id>:<anchor>"
  page_id     VARCHAR NOT NULL,
  anchor      VARCHAR,
  ordinal     INT,
  body        VARCHAR NOT NULL,           -- the block's markdown text, VERBATIM (display + provenance)
  context     VARCHAR,                    -- structural situating prefix "[title › headings › p.N]" (GH #216);
                                          -- concatenated with body only at embed/FTS/rerank time; NULL for
                                          -- ordinary page blocks and contextualize=off
  dense_vec   FLOAT[768],                 -- EmbeddingGemma default; vss HNSW-indexed; embeds context+"\n"+body
  -- denormalised for filtered retrieval (single-SQL push-down):
  skill       VARCHAR,
  page_type   VARCHAR,
  at_ts       TIMESTAMP                   -- mirrored from frontmatter.at if present
);
CREATE INDEX blocks_page    ON blocks(page_id);
CREATE INDEX blocks_skill   ON blocks(skill);
CREATE INDEX blocks_at      ON blocks(at_ts);

-- vss + fts indexes are created via extension DDL after the table:
INSTALL vss; LOAD vss;
CREATE INDEX hnsw_blocks_vec ON blocks USING HNSW (dense_vec)
  WITH (metric = 'cosine', ef_construction = 128, ef_search = 64, M = 16);
INSTALL fts; LOAD fts;
PRAGMA create_fts_index('blocks', 'block_id', 'body', 'context',
                        stemmer = 'porter', stopwords = 'english',
                        ignore = '(\.|[^a-z])+', lower = 1);

Beyond the tables above, later migrations add a few additive tables this section does not detail: chat_messages, group_members, external_credentials, and external_endpoints.

Write attribution

pages.last_written_by and crdt_ops.principal (escurel#357 / CR-6) are server-owned: the gateway writes the subject it verified on the call and ignores every caller-supplied field of any name, exactly as capture_event does for events.provenance.captured_by. A caller cannot assert its own authorship on any of the three.

Both columns are NULLable, deliberately. They are added to already- populated tables via ADD COLUMN IF NOT EXISTS, which NOT NULL forbids; and a NOT NULL DEFAULT '<sentinel>' would attribute every historical row to a principal that did not write it. NULL means "written before the gateway recorded who", which is the truth.

last_written_by is last-writer-wins, like updated_at — it is not a history. It survives rebuild, which re-derives pages from the markdown lane: the lane stores content and no principal, so the rebuild carries the recorded writer across the truncate rather than blanking every page. A per-write history (the issue's "fuller" shape — a separate write-audit table) is not built.

The at_ts column on pages plus the pages_skill_at composite index is the event-log scan support: list_instances('meeting', filter={at: {">=": "2026-04-01"}}, order_by='at desc') becomes SELECT * FROM pages WHERE skill='meeting' AND at_ts >= '2026-04-01' ORDER BY at_ts DESC LIMIT 50 — index-served, sub-millisecond even at 100 k event instances per skill.

The denormalised skill, page_type and at_ts columns on blocks make filtered vector search a single SQL statement: a query of the form "return the top-10 closest blocks to vector V whose skill is meeting and whose at_ts is at least 2026-04-01" expresses as one SQL with a vss_search() call against dense_vec joined with the relational predicates.

Every other filterable frontmatter key (status, tier, risk, etc.) is matched directly over the canonical pages.frontmatter JSON — list_instances filters with json_extract_string(frontmatter, '$.<key>') = ? rather than consulting a separate index. This needs no schema migration when a new skill adds a new field, at the cost of a scan over the skill-filtered pages rows rather than an index seek.

CRDT persistence

The Loro engine — the in-memory adapter and LiveDoc actor — persists into two DuckDB tables that share the per-tenant store and the per-write transaction:

CREATE TABLE crdt_ops (
  page_id       VARCHAR NOT NULL,
  op_id         VARCHAR NOT NULL,        -- Loro op id (HLC-ordered)
  hlc           BIGINT NOT NULL,         -- the HLC value for monotonic sort
  parent_op_id  VARCHAR,                 -- chain parent (NULL for genesis)
  op_bytes      BLOB NOT NULL,           -- raw Loro op
  applied_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  principal     VARCHAR,                 -- server-stamped author of THIS op (#357/CR-6).
                                         -- NOT the Loro peer id inside op_bytes: a peer id
                                         -- identifies a device, so two people editing from one
                                         -- browser tab share it. Read back via `list_op_authors`.
  PRIMARY KEY (page_id, op_id)
);
CREATE INDEX crdt_ops_page_hlc ON crdt_ops(page_id, hlc);

CREATE TABLE crdt_snapshots (
  page_id        VARCHAR NOT NULL,
  snapshot_hlc   BIGINT NOT NULL,        -- the HLC at which the snapshot was taken
  snapshot_bytes BLOB NOT NULL,          -- raw Loro export_snapshot
  taken_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (page_id, snapshot_hlc)
);

-- events: the global inbox / event store (M7 — Event-sourcing surface).
-- An event is the dynamic input; `label_skill` links to the SKILL that
-- knows how to process it, `instance_page_id` to the INSTANCE it belongs
-- to once an (external) agent has processed it. `status='inbox'` until
-- assigned. Events are NOT pages and are not in the `links` graph; their
-- surface is capture_event / list_inbox / list_events / assign_event.
CREATE TABLE events (
  event_id          VARCHAR PRIMARY KEY,
  at_ts             TIMESTAMP,                        -- event time (`at` is a DuckDB keyword)
  source            VARCHAR NOT NULL DEFAULT '',      -- ingest source, e.g. gmail / meet
  mime              VARCHAR NOT NULL DEFAULT '',      -- content type, e.g. message/rfc822
  label_skill       VARCHAR NOT NULL DEFAULT '',      -- skill id: how to process this event type
  instance_page_id  VARCHAR,                          -- assigned instance (NULL = inbox)
  status            VARCHAR NOT NULL DEFAULT 'inbox', -- 'inbox' | 'processed'
  title             VARCHAR NOT NULL DEFAULT '',
  body              VARCHAR NOT NULL DEFAULT '',
  provenance        JSON,
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX events_status_at   ON events(status, at_ts);          -- the inbox view
CREATE INDEX events_instance_at ON events(instance_page_id, at_ts);-- an instance's event history

Demo seeding (M7). seed_from_dir (the ESCUREL_SEED_DIR bootstrap) loads, alongside the markdown pages, two optional files in the seed dir: events.json (an array of events captured into the inbox; an entry with status: "processed" and an instance is also assigned to that instance's event history) and history.json (an array of {page_id, states:[{taken_at, markdown}]} CRDT snapshot timelines). Both are idempotent (skipped if events/snapshots already exist), so the demo's event stream + per-instance state history is real backend data, not a frontend fixture.

The LiveDoc actor for a page is one Tokio task that accepts ops from the open session(s), feeds them to the in-memory Loro engine and on each accepted op runs a write transaction that inserts the op row. The transaction is small (one row, no index rebuild) and commits in under a millisecond on the local FS backend.

Snapshots are taken on session close (commit=true) and periodically during long-lived sessions (default every 256 ops or 60 s). After a snapshot inserts into crdt_snapshots, the ops with hlc <= snapshot.snapshot_hlc for the same page are eligible for compaction (see "Compaction" below).

Query-time historical state (M7). Beyond crash recovery, crdt_snapshots is also a read-time API: expand(page_id, as_of = T) loads the snapshot with the greatest taken_at <= T, materializes its "body" text container back to markdown (escurel_crdt::body_from_snapshot), and re-parses it — returning the instance's frontmatter+body as it was at T (the projection of its events up to T). A page with no snapshot at-or-before T falls through to the current-state path (the at_ts birth filter), so this is additive. Snapshot histories can be authored server-side via Indexer::seed_snapshot_history (deterministic Loro export, escurel_crdt::snapshot_bytes_from_markdown) — how the demo gives an instance a real state-over-time history.

On server restart, opening a page that has CRDT rows replays the latest crdt_snapshots row for the page, then any crdt_ops rows with hlc > snapshot_hlc, yielding the exact state at crash. If the markdown file on disk is newer than the snapshot's reflected state (external edit happened), the two-stage reconciler runs: snapshot wins for cited instances; external edit wins for new pages and uncited pages.

CRDT state is not part of tenant_export — the canonical markdown is. On import into a different server, all live sessions reset to the markdown head. This is by design: CRDT state is a runtime concern, not a corpus artefact.

Event volume — scaling beyond ~1 M events

The single DuckDB store clears 100 k instances at typed-backlink 5.21 ms p95 and extrapolates to 1 M at ~13 ms p95 for the backlink path. The vector + skill-filter path is targeted at ≤ 50 ms p95 at 100 k, pending the pre-deployment gate in ../adr/0001-duckdb-only-storage.md. Above ~1 M events — multi-million-event corpora such as a chat-message firehose or a CDC stream of CRM activity — the recommended path is:

  1. Author one [[table::messages]] skill instance whose body describes the schema and the catalog/schema/name fields.
  2. Land the events in an external DuckLake (or Iceberg) table.
  3. Attach the catalog read-only via attach_external.
  4. Author one or more [[query::*]] instances that wrap typical timeline queries (messages-for-customer, recent-incidents-affecting-project).
  5. Agents reach individual events through run_stored_query rather than through list_instances.

The origin-axis path is the volume escape hatch for events. Markdown is reserved for the events that have curation or authoring value (meetings, key decisions, postmortems); bulk event streams stay where they came from. Both look identical to the agent — the dispatcher hides the lane.

VSS index maintenance

The HNSW index on blocks.dense_vec is built with INSTALL vss; LOAD vss; plus CREATE INDEX … USING HNSW after the table is populated. The extension's current update semantics expect the writer to refresh the index after batched modifications; the indexer issues PRAGMA hnsw_compact_index at the end of every write transaction that touched blocks. Cost characterisation is the load-bearing item of ../adr/0001-duckdb-only-storage.md's pre-deployment gate.

HNSW persistence model

The HNSW index lives inside the DuckDB file alongside the blocks table. Opening an intact DuckDB file via DuckDB.Open() loads the existing index as-is; the index is not reconstructed per open. After a crash mid-write the DuckDB transaction is rolled back (no partial index state), so on the next open the index is consistent with the committed blocks rows without further work.

If the DuckDB file is missing but the canonical markdown is intact on the LaneStore — the cattle-node-loss case, where a host was recreated and the data Volume reattaches to a survivor with no local DuckDB state — the server detects the missing file on first tenant access and runs rebuild(tenant) automatically before serving the first request. Cost ~32 ms/page: a 1000-page tenant rebuilds in ~32 s, a 10 000-page tenant in ~5 min. Transparent to agent callers except for the first-request latency on that tenant.

This recovery property is what makes the per-tenant escurel.duckdb file cattle rather than pet: canonical markdown on the LaneStore is the source of truth, the DuckDB file (including the HNSW and FTS indexes) is a rebuildable derivative.

Compaction

DuckDB compaction is implicit (CHECKPOINT runs after the write transaction completes); file rewrites happen during the regular write path. A compact_lanes admin endpoint forces a CHECKPOINT plus a VACUUM plus a PRAGMA hnsw_compact_index for any tenant whose store size grows above a configurable watermark. The crdt_ops table also benefits from periodic truncation: ops older than the most recent crdt_snapshots row for the same page are eligible for deletion, controlled by crdt.ops.retain_post_snapshot (default keep the most recent 1024 ops per page for one-step replay margin).

For S3 backends, compaction also coalesces many small DuckDB checkpoint files into fewer larger ones (vendored behaviour).

Crash recovery summary

failure recovery
Process killed mid-write DuckDB rolls back the transaction; pages, links, blocks (with vss/fts updates), crdt_ops all revert together; markdown file is left at the previous version because the rename happens only after commit
Process killed after DuckDB commit, before markdown rename audit reports markdown_not_in_duckdb for the new page (DuckDB has it, markdown does not yet); rebuild reconciles by re-writing the markdown from the DuckDB row, or admin can re-run update_page from a re-submitted source
Markdown rename(2) crashed mid-write The pre-existing markdown is intact (.tmp orphans cleaned on startup)
External edit mid-session (live mode) Two-stage reconciler: for cited pages the CRDT snapshot wins; for new or uncited pages the external edit wins
DuckDB file corruption (rare) Auto-suspend tenant (status: suspended_corrupt); admin runs rebuild --tenant <id> to recreate from canonical markdown
vss or fts index corruption PRAGMA drop_index plus rebuild — the index is derivable from blocks.dense_vec and blocks.body without re-embedding
S3 backend timeout Local spool under ${ESCUREL_DATA_DIR}/spool/<tenant>/ — host-local, not synced to the LaneStore; queue flushes on reconnect. On a host recreate the previous host's spool is lost; the markdown source-of-truth is preserved (writes only enter the spool after a successful DuckDB commit per the row above), so recovery is a client re-submit
Cattle node destroyed; escurel.duckdb gone; markdown intact on LaneStore First request to the tenant triggers automatic rebuild from canonical markdown on the LaneStore (~32 ms/page; ~32 s for 1000 pages); transparent to agent except for one-time first-request latency

The two recovery primitives (audit, rebuild) are the full playbook. Operators do not need to know the internal storage mechanics to recover, and the playbook is bounded because there is one index to reconcile.