# Database-backed OKF bundles

Knowledge can read its authoritative Open Knowledge Format bundle from a local folder, SQLite, or PostgreSQL. An SQL backend projects a set of tables or views into a virtual directory tree. Each row supplies the relative path and original bytes of one OKF file. Relative Markdown links, frontmatter, per-path chunking rules, graph edges, and evidence locators use the resulting logical paths. The backend has no OS mount or intermediate materialized folder. Supplemental `sources` can still be enrolled alongside the bundle and continue to use their separate connector namespaces. [OKF specification](https://github.com/GoogleCloudPlatform/open-knowledge-format/blob/main/SPEC.md)

The YAML keys distinguish three roles.

| Setting | Role |
| --- | --- |
| `source.path` or `source.backend` | Choose the authoritative OKF bundle. Use one of these settings. |
| `sources` | Enroll supplemental records. A normal SQL connector can generate documents from business rows. |
| `storage.database` | SQLite or SQLCipher index, or the local state file for PostgreSQL serving. |
| `storage.postgres` | Optional shared PostgreSQL index and worker coordinator. The upstream source connection is independent. |

## Map files to rows

The backend needs a logical root `index.md`. A pages mapping can return `index.md`, `log.md`, and paths such as `calibration/sensor.md` from one table or view. Additional mappings can supply media and other files. `prefix: assets` changes a row path `drawing.png` to `assets/drawing.png`. Files link to that path using standard OKF relative links.

Each mapping uses a table/view name and one path column and content column. Use a restricted view for joins, per-bundle or per-tenant filtering, type conversions, and layout transforms. The reader accepts portable bundle-relative paths up to 1,024 bytes. It rejects traversal, reserved namespace collisions, duplicate paths, case collisions, and file/directory conflicts. Mappings use literal ASCII SQL identifiers. Markdown and binary asset contents are read as UTF-8 text or raw bytes respectively. Set `content_type: bytes` for SQLite BLOB or PostgreSQL BYTEA. Knowledge indexes the file types supported by its extraction pipeline. Diagnostics can resolve links to other files present in the inventory.

### PostgreSQL

Use a dedicated login with only the schema access and SELECT grants needed for the mapped views or tables. Require verified TLS. Put the PEM trust roots in `connection.ca_file`. The server certificate must cover the configured host name. Use a password reference backed by an environment variable or a private file. Path values in the sample are relative to the configuration file. Use the `postgres` Cargo feature for custom builds. Default builds and the platform build script include it. [Sample PostgreSQL configuration](https://github.com/tsok-org/source-code/blob/main/apps/knowledge/configs/okf-postgres.yml)

A read-only, repeatable-read transaction reads all mappings in one consistent snapshot. If a mapping fails, a bound is exceeded, or the root is absent or declares an unsupported format version, Knowledge preserves the previous publication. The local index is bound to the upstream database, projection, and PostgreSQL login. To intentionally switch to another projection or login, use a new index. Rotating credentials, trust roots, polling cadence, or notification settings keeps the same binding. [PostgreSQL transaction isolation](https://www.postgresql.org/docs/current/transaction-iso.html)

Configure a `watch_channel` for prompt refreshes. Send an empty notification from the writer's transaction after it changes mapped rows, or install a statement-level trigger that does so. Put no content or secrets in the notification payload. Knowledge ignores payloads and coalesces bursts using its normal change batching. After connecting or reconnecting it registers the listener first and then schedules a catch-up snapshot. Periodic scans continue as a recovery path. The listener uses a dedicated session, which needs a direct PostgreSQL or session-pooled connection. [LISTEN](https://www.postgresql.org/docs/current/sql-listen.html) · [NOTIFY](https://www.postgresql.org/docs/current/sql-notify.html)

```sql
BEGIN;
UPDATE publishing.pages SET markdown = $document$---
type: Procedure
---
Review the sensor calibration.$document$
WHERE relative_path = 'calibration/sensor.md';
SELECT pg_notify('okf_updated', '');
COMMIT;
```

### SQLite

The SQLite backend reads an upstream SQLite database through a separate read-only connection and polls it at `indexing.scan_interval_ms`. All mappings read from the main schema inside one transaction. The index file and source file must be distinct.

```yaml
source:
  strict_metadata: true
  backend:
    type: sqlite
    path: ./notebook.sqlite
    mappings:
      - table: okf_pages
        path_column: path
        content_column: body
      - table: okf_assets
        path_column: path
        content_column: payload
        content_type: bytes
        prefix: assets
storage:
  database: ./.okf/index.sqlite
```

## Operations

- `knowledge validate-config` checks settings and reports the selected OKF backend. `knowledge check` reads the bundle and reports OKF diagnostics without opening the index.
- `knowledge index` refreshes the configured bundle and runs jobs. `knowledge watch` and `knowledge serve` with `server.watch: true` keep it current. `watch --poll` uses periodic reconciliation without native notifications. An upstream outage leaves the existing index available and the watcher retries.
- Rust applications can use `Knowledge::reconcile().await` and `Knowledge::watch_until`. The synchronous `scan` remains the folder API. HTTP reconciliation uses the configured backend.
- Database-backed OKF is read-only through Knowledge. The upstream application owns changes. Existing folder authoring still uses `source.path`. A Knowledge backup includes retained indexed bytes, and an explicit `restore <database> <root>` can materialize those files as a folder bundle with a separate restored index.

Database snapshots are bounded by `source.backend.limits` and the normal `indexing.max_file_bytes`, `max_scan_files`, `max_scan_entries`, and `max_scan_bytes` settings. The database defaults are 30 seconds and 64 MiB of content. A completed snapshot stages only changed versions for processing. Snapshots still read the mapped contents, so large or heavily edited bundles will benefit from durable change capture or a versioned manifest protocol. [PostgreSQL index and worker design](https://github.com/tsok-org/source-code/blob/main/apps/knowledge/docs/postgres-index-design.md)

## Reproduce the tests

```sh
cargo test --locked --no-default-features --features postgres,mcp --test bundle
cargo build --locked --no-default-features --features postgres
python3 scripts/check-postgres-bundle.py "$(command -v knowledge)" "$(pg_config --bindir)"
```

The PostgreSQL fixture creates a temporary local cluster with TLS, a private CA, a restricted role, text and byte mappings, and a notification channel. It checks normal reads, certificate and hostname failures, notification-triggered indexing, missed changes after reconnect, polling without notifications, exact stored binary bytes, and retention on a failed snapshot. It requires PostgreSQL server binaries, OpenSSL, and Python 3 on a Unix test host. The Rust suite also verifies relative-link and version-ID parity with a folder bundle, HTTP reconcile, and write protection.
