> ## Documentation Index
> Fetch the complete documentation index at: https://ventstream.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# SurrealDB sink

> Materialize CDC streams as SurrealDB records — documents, joins, and KNN-searchable vectors.

The SurrealDB sink writes each change as a full-replace upsert on a
native record id, so your tables in SurrealDB mirror the source — flat
rows or composed documents with embedded joins — and stay consistent
through updates, deletes, primary-key changes, and truncates. SurrealDB
commits synchronously: a confirmed write is durable, with no task queue
to poll. Requires SurrealDB **3.x**.

## 1. Provision the target (one-time)

VentStream never needs root. In [Surrealist](https://surrealist.app)
(or any client with admin authority — for a local instance,
`surreal sql -u root -p root` works), create the namespace, database,
and a database-scoped user:

```sql theme={"dark"}
DEFINE NAMESPACE IF NOT EXISTS production;
USE NS production;
DEFINE DATABASE IF NOT EXISTS app;
USE DB app;
DEFINE USER ventstream ON DATABASE PASSWORD '<generate one>' ROLES OWNER;
```

Those are the credentials the sink runs with: enough to write records
and define table-level indexes, nothing more. Scoped users authenticate
via `/signin`; the sink handles tokens and refresh automatically. If the
database is missing at startup, the sink fails with exactly this DDL in
the error message. (For a throwaway local instance you can instead set
`auto_create_database: true` and connect with root — dev only.)

## 2. A complete pipeline

The sink is one half of a pipeline file; any
[source](/docs/connectors/overview) provides the other. A minimal, complete
Postgres → SurrealDB config:

```yaml theme={"dark"}
# ventstream.yaml
schema_version: 1
roles: [cdc]

source:
  kind: postgres
  postgres:
    host_ref: env:VS_PG_HOST
    user_ref: env:VS_PG_USER
    password_ref: env:VS_PG_PASSWORD
    database_ref: env:VS_PG_DATABASE
    publication_ref: env:VS_PG_PUBLICATION   # CREATE PUBLICATION … FOR TABLE …
    slot_ref: env:VS_PG_SLOT                 # created by the engine
    bootstrap:
      mode: snapshot

sink:
  kind: surrealdb
  surrealdb:
    endpoint_ref: env:VS_SURREAL_ENDPOINT    # http(s)://host:8000
    namespace: production
    database: app
    username_ref: env:VS_SURREAL_USERNAME
    password_ref: env:VS_SURREAL_PASSWORD

runtime:
  health_listen: 127.0.0.1:4043
  dlq_path: ./ventstream-state/dlq.jsonl
```

`*_ref: env:NAME` resolves each value from the environment at startup,
so no secret lives in the file. Export them and run the engine:

```bash theme={"dark"}
export VS_PG_HOST=127.0.0.1 VS_PG_USER=app VS_PG_PASSWORD=… \
       VS_PG_DATABASE=shop VS_PG_PUBLICATION=vs_pub VS_PG_SLOT=vs_slot \
       VS_SURREAL_ENDPOINT=http://127.0.0.1:8000 \
       VS_SURREAL_USERNAME=ventstream VS_SURREAL_PASSWORD=…

VS_ENGINE_CONFIG=ventstream.yaml ventstream
```

The engine snapshots every published table into SurrealDB, then tails
the WAL. **Each published table becomes a SurrealDB table named after
its relation** — `public.orders` lands in table `orders` — and
`table_prefix: "pg-"` would make that `pg-orders`. Verify in Surrealist:

```sql theme={"dark"}
SELECT count() FROM orders GROUP ALL;
LIVE SELECT * FROM orders;   -- watch changes stream in
```

For **composed documents** (an order carrying its customer and line
items), add a joins spec exactly as in the
[Postgres source guide](/docs/connectors/sources/postgres) — everything
upstream of the sink, including joins and schema-drift handling, is
identical for every sink.

## Record identity

VentStream's deterministic doc id `table:["pk",…]` maps directly onto
SurrealDB's array record ids — `orders:['299']`, composite keys included
— so upserts are idempotent and deletes always find their record. The
canonical id is stamped on every document as `_vs_id`. A source column
named `id` is preserved as `source_id` (SurrealDB reserves `id` for the
record id itself).

## Vector search

Declare embedding fields to make them KNN-searchable — the sink ensures
the HNSW index at startup:

```yaml theme={"dark"}
    vector_indexes:
      - table: orders
        field: embedding
        dimension: 384
        distance: cosine     # cosine | euclidean | manhattan
```

Embedding arrays flow through documents unchanged; query with
`WHERE embedding <|10,40|> $vec`.

## Large joined tables

For 1:many joined pipelines, declare the embedded join paths so child
deletes filter a flat materialized field instead of evaluating a
per-row closure (\~5x cheaper today, index-ready in future SurrealDB
versions):

```yaml theme={"dark"}
    lookup_fields:
      - table: orders
        field: items.item_id
```

## Semantics to know

* **Concurrent writers converge.** SurrealDB uses optimistic
  transactions; the sink classifies conflicts as transient and retries
  the ordered tail — replays are idempotent, so external writers hammering
  the same records cost retries, not corruption.
* **Full-replace materialization.** Documents are replaced, not merged:
  fields written by other applications on the same records are overwritten
  on the next sync. Give VentStream its own tables.
* **`table_routing.mode: fixed`** funnels every relation into one named
  table (records keep fully-qualified ids so relations can't collide);
  a TRUNCATE of any relation then clears that shared table — prefer the
  default per-relation routing unless you need this.

## Troubleshooting

* `namespace/database is not provisioned` — run the DDL from step 1, or
  set `auto_create_database: true` with elevated credentials (dev only).
* `HTTP 401` at startup — wrong credentials, or the user was defined at
  a different scope than the configured namespace/database.
* Writes retry with `transient statement failure` — normal under write
  contention; sustained retries mean another process is hammering the
  same records.