Lakebase as Graph DB — OntoBricks Reference

OntoBricks ships with one built-in graph database engine: Lakebase Postgres. This document covers everything you need to provision, configure, and operate it — from the API gotchas that trip up fresh installs to the Postgres schema layout and troubleshooting runbook.

For the developer guide on adding a new engine, see docs/graphdb-integration.md.

Table of Contents

1. Architecture overview
2. Prerequisites
3. Provisioning the Lakebase project
4. Configuring the engine in the app
5. Write modes
6. Postgres schema layout
7. Scripts reference
8. Permissions bootstrap
9. Knowledge Graph build — step by step
10. Troubleshooting

1. Architecture overview

┌─────────────────────────────────────────────────┐
│  OntoBricks (FastAPI)                           │
│                                                 │
│  GraphDBFactory.create(engine="lakebase")       │
│    └─ LakebaseFlatStore                          │
│              │                                  │
│              │  COPY FROM STDIN / INSERT         │
│              ▼                                  │
│  ┌─────────────────────────────────────────┐   │
│  │  Lakebase Postgres (App-bound)          │   │
│  │  schema: ontobricks_graph               │   │
│  │    g_<domain>_v<n>           (app_managed)  │
│  │    g_<domain>_v<n>_sync      (synced table) │
│  │    g_<domain>_v<n>__app      (companion)    │
│  │    g_<domain>_v<n>           (UNION VIEW)   │
│  └─────────────────────────────────────────┘   │
│              │                                  │
│              │  Lakeflow snapshot pipeline       │
│              ▼                                  │
│  ┌─────────────────────────────────────────┐   │
│  │  Unity Catalog                          │   │
│  │  <catalog>.<schema>.<domain>_vN_sync    │   │
│  │  (registered synced table)              │   │
│  └─────────────────────────────────────────┘   │
└─────────────────────────────────────────────────┘

OntoBricks maintains two complementary storage layers:

The Graph DB engine is selected per domain under Domain → Information → Knowledge Graph (defaults to lakebase). Engine connection settings remain workspace-global under Settings → Back end.

2. Prerequisites

2.1 — Databricks workspace requirements

2.2 — Python dependencies

Lakebase support is an optional extra since v0.4.0:

# Local development
uv sync --frozen --extra lakebase

# Or pip
pip install ".[lakebase]"

This installs psycopg[binary]>=3.2.0 and psycopg-pool>=3.2.0. The deployed app.yaml already includes --extra lakebase in the startup command.

2.3 — Critical API distinction

Lakebase has two project-creation APIs with different capabilities:

The Databricks UI "New project" button calls the new API and produces a project that is incompatible with POST /api/2.0/database/synced_tables (used by the Knowledge Graph managed_synced build mode). Always use scripts/bootstrap/setup-lakebase.sh to provision the project.

3. Provisioning the Lakebase project

3.1 — Create with setup-lakebase.sh

Run once per workspace before the first deploy:

./scripts/bootstrap/setup-lakebase.sh --name ontobricks-demo --capacity CU_2

The script:

1. Checks whether the instance name already exists via GET /api/2.0/database/instances.
2. Creates it via POST /api/2.0/database/instances (synced-tables-compatible).
3. Polls until the instance reaches AVAILABLE.
4. Resolves the branch endpoint and creates the Postgres database.
5. Prints the db-… resource id — copy this into scripts/deploy.config.sh.

All options:

3.1b — One-click provisioning from Settings (in-app alternative)

Admins can provision a graph DB end-to-end from the UI instead of running the two scripts by hand. In Settings → Lakebase → Connection tab there is a "Create graph DB from scratch" card: fill in the instance/project name, compute capacity, branch, Postgres database, graph schema, and the MCP app name, then click Create graph DB. The action runs as an async job (a progress bar + per-step log update live, polling GET /tasks/{id} like a Knowledge Graph build) and performs the same flow as scripts/bootstrap/setup-lakebase.sh + scripts/bootstrap/lakebase-perms.sh:

1. Create the Lakebase instance (via the synced-tables-compatible /api/2.0/database/instances API) and wait for AVAILABLE.
2. Create the Postgres database and the graph schema.
3. Grant CAN_USE on the project and USAGE/CREATE/DML on the schema to the app and MCP service principals; optionally grant ALL_PRIVILEGES on the configured UC catalog (managed-sync only).

On success the chosen project/branch/database/schema are written into graph_engine_config, so the Connection pickers reflect the new target.

Permission model (unchanged — only automated). The button runs as the app's own service principal, not a human. It therefore needs the SP to be allowed to create Lakebase instances; if it is not, the job fails on the first step with a clear message. Schema grants to the MCP SP are best-effort and surfaced as warnings when the MCP Postgres role does not exist yet. In those cases the shell scripts (POST /api/2.0/database/instances as a human owner) remain the documented fallback — re-run scripts/bootstrap/lakebase-perms.sh after the apps have connected once.

3.2 — After the script

Set the project + Postgres datname in scripts/deploy.config.sh (deploy.sh resolves the db-… segment automatically):

DEFAULT_LAKEBASE_PROJECT="<project-name>"
DEFAULT_LAKEBASE_DATABASE="<postgres-datname>"
# Optional override:
# LAKEBASE_DATABASE_RESOURCE_SEGMENT="db-xxxx-xxxxxxxxxx"

You can also look it up at any time:

databricks postgres list-databases \
  "projects/<project-name>/branches/production" -o json \
  | python3 -c "import sys,json; [print(d['name']) for d in json.load(sys.stdin)]"

3.3 — Name reservation gotcha

If you delete a Lakebase project from the new /postgres/projects API, the name may remain ghost-reserved in the /database/instances namespace for an extended period (15 minutes to hours). If setup-lakebase.sh fails with Instance name is not unique, choose a different name and update deploy.config.sh.

4. Configuring the engine in the app

4.1 — UI configuration

Go to Settings → Graph DB → Engine Configuration and enter a JSON object:

{
  "schema": "ontobricks_graph",    // Postgres schema for graph tables (default)
  "database": "ontobricks_demo",   // Postgres database (overrides PGDATABASE)
  "sync_mode": "app_managed"       // or "managed_synced" — see §5
}

The engine selector should show lakebase. If it shows empty, reload the page.

4.2 — All graph_engine_config.lakebase keys

graph_engine_config is nested per backend::

{
  "lakebase":  { ... keys below ... },
  "neo4j":     { "uri": "...", "database": "neo4j", ... },
  "lakehouse": { "warehouse_id": "..." }
}

Flat legacy blobs are still accepted and rewritten to this shape on Save. Lakehouse SQL calls resolve lakehouse.warehouse_id (then fall back to the global warehouse).

4.3 — Environment variables (deployed app)

The Apps runtime auto-injects these when the postgres resource is bound in databricks.yml:

The Postgres password is never stored. LakebaseAuth mints a short-lived JWT via POST /api/2.0/postgres/credentials on every connection open.

For local development (no Apps resource injection), set these in .env:

LAKEBASE_PROJECT=ontobricks-demo2   # Autoscaling project name
LAKEBASE_BRANCH=production          # Branch to connect to
LAKEBASE_DATABASE=ontobricks_demo   # Postgres datname
LAKEBASE_SCHEMA=ontobricks_registry # Registry schema
PGUSER=you@example.com              # Your Databricks login email

4.4 — deploy.config.sh variables

These drive the DAB deployment (edit before make deploy):

deploy.config.sh is registry-scoped. The graph DB schema/database are configured in-app (Settings → Graph DB → graph_engine_config) and may live in a different Lakebase project — they are not deploy vars.

5. Write modes

5.1 — app_managed (default)

The FastAPI process streams R2RML rows from the SQL Warehouse and writes them directly into the *_sync table via COPY FROM STDIN. The same 3-object layout used by managed_synced is created: a *_sync bulk-data table, a *__app companion for reasoning/cohort writes, and a union view for readers.

R2RML view (SQL Warehouse)
    │  iter_rows batches
    ▼
LakebaseFlatStore.bulk_load_into_sync
    │  COPY FROM STDIN → g_<domain>_v<n>_sync  (app-owned)
    ▼
g_<domain>_v<n>  (UNION VIEW over _sync + __app)

• Simple setup — no Lakeflow pipelines required.
• App process is on the hot path for large graphs.
• Reasoning / cohort writes always go to *__app (consistent with managed_synced).
• Suitable for most use cases.

5.2 — managed_synced (Lakeflow)

A Databricks Lakeflow snapshot pipeline keeps a Postgres synced table in lock-step with the R2RML Delta view. The app only orchestrates; bulk movement happens entirely on the Databricks side.

R2RML view (Unity Catalog)
    │  Lakeflow snapshot pipeline
    ▼
g_<domain>_v<n>_sync  (Postgres, Lakeflow-owned, read-only)

App writes (reasoning, cohort):
g_<domain>_v<n>__app  (Postgres, app-owned, writable)

Readers:
g_<domain>_v<n>  (UNION VIEW over _sync + __app)

Enable it with:

{ "sync_mode": "managed_synced" }

When to use managed_synced:

• Source tables are very large (millions of triples).
• You want Databricks lineage on the synced table.
• The app process should not be the bottleneck during builds.

Additional requirements for managed_synced:

• The Lakebase project must be provisioned via scripts/bootstrap/setup-lakebase.sh (provisioned instance, not autoscaling-only) — see §2.3.
• The app SP needs CAN_USE on the Lakebase database instance — applied by scripts/bootstrap/lakebase-perms.sh.
• The UC schema for the synced table must exist before the first build (CREATE SCHEMA IF NOT EXISTS is run automatically by the build pipeline).

6. Postgres schema layout

6.1 — Schemas

OntoBricks uses up to three Postgres schemas in the same Lakebase project:

6.2 — Objects per graph version

Both app_managed and managed_synced use the same 3-object layout per domain version. The difference is who writes to the *_sync table.

All SPARQL queries and graph traversal operations target the back-compat name g_<domain>_v<n> — no downstream code is aware of which mode is active.

6.3 — Drop cascade (both modes)

LakebaseFlatStore.drop_table(name) removes all three objects in order:

1. DROP VIEW IF EXISTS g_<domain>_v<n> (UNION view)
2. DROP TABLE IF EXISTS g_<domain>_v<n>__app (companion)
3. app_managed: DROP TABLE IF EXISTS g_<domain>_v<n>_sync (sync table) managed_synced: SyncedTableManager.delete(uc_name, purge_data=True) — removes the UC synced-table registration and the underlying Postgres _sync table

6.4 — Long literal objects (object_hash)

Postgres btree indexes (including the composite primary key) cannot index TEXT values longer than ~2704 bytes. OntoBricks therefore stores the full object literal for reads/deletes but keys uniqueness on a generated object_hash column (digest(object, 'sha256') via the pgcrypto extension).

pgcrypto must live in public. Pooled connections run SET search_path TO "<graph_schema>", public, and a bare CREATE EXTENSION installs into the first search_path entry — i.e. the graph schema. Since IF NOT EXISTS is then a permanent no-op, changing the graph schema strands digest() out of reach and every Build fails with function digest(text, unknown) does not exist.

Two places keep this correct, both idempotent:

• ensure_pgcrypto (app) installs into public and relocates a stranded extension via ALTER EXTENSION pgcrypto SET SCHEMA public. The app owns any extension it created, so it can self-heal.
• make bootstrap-lakebase Step 1b does the same as the admin, and warns (without aborting) when the extension is app-owned and cannot be relocated.

The extension is per database — the graph database is often a different Lakebase project/database from the registry (graph_engine_config.database), so fixing one does not fix the other.

• app_managed: *_sync and *__app tables are created with PRIMARY KEY (subject, predicate, object_hash).
• managed_synced: the Delta warehouse VIEW exposes object_hash (sha2(object, 256)) and Lakeflow uses primary_key_columns = [subject, predicate, object_hash].

Graphs created before this layout need a full Knowledge Graph rebuild (drop

• recreate) to pick up the new schema.

7. Scripts reference

scripts/bootstrap/setup-lakebase.sh

Provisions a Lakebase project via POST /api/2.0/database/instances (synced-tables-compatible).

# Basic usage
./scripts/bootstrap/setup-lakebase.sh --name my-project --capacity CU_2

# Dry-run to preview
./scripts/bootstrap/setup-lakebase.sh --name my-project --dry-run

# Custom profile
./scripts/bootstrap/setup-lakebase.sh --name my-project --profile prod-workspace

Outputs: prints the db-… resource id (informational). Set DEFAULT_LAKEBASE_PROJECT / DEFAULT_LAKEBASE_DATABASE in deploy.config.sh; optional override LAKEBASE_DATABASE_RESOURCE_SEGMENT.

scripts/bootstrap/lakebase-perms.sh

Grants the app service principals the Postgres and control-plane permissions they need to operate. Idempotent — safe to run repeatedly.

# Registry schema
scripts/bootstrap/lakebase-perms.sh \
  -i ontobricks-demo2 -b production \
  -d ontobricks_demo -s ontobricks_registry \
  -a ontobricks-030 -a mcp-ontobricks

# Graph DB schema (run after first Build)
scripts/bootstrap/lakebase-perms.sh \
  -i ontobricks-demo2 -b production \
  -d ontobricks_demo -s ontobricks_graph \
  -a ontobricks-030 -a mcp-ontobricks

# Sync schema (managed_synced only — run after first Lakeflow snapshot)
scripts/bootstrap/lakebase-perms.sh \
  -i ontobricks-demo2 -b production \
  -d ontobricks_demo -s ontobricks \
  -a ontobricks-030 -a mcp-ontobricks

make deploy (via scripts/deploy.sh) grants the registry schema automatically. The graph and sync schemas are granted by the in-app "Create graph DB" flow or by running the commands above manually — they are not deploy vars, since the graph DB may live in a different Lakebase project.

What each run grants:

scripts/deploy.sh

Full deploy pipeline (called by make deploy). On the dev-lakebase target it:

1. Renders app.yaml from app.yaml.template + deploy.config.sh.
2. Validates the DAB bundle.
3. Deploys both apps.
4. Starts the main app.
5. Bootstraps app self-permissions (bootstrap-app-permissions.sh).
6. Runs bootstrap-lakebase-perms.sh for all configured schemas.

make deploy                    # dev-lakebase target (default)
make bootstrap-lakebase        # run only the Lakebase grants
make deploy-volume             # dev target (volume-only, no Lakebase binding)

8. Permissions bootstrap

8.1 — Order of operations

Lakebase schemas are created lazily (by app actions), so grants must follow creation:

Step 1:  make deploy
         → CAN_USE on instance applied immediately (before schema exists)
Step 2:  Open app → Settings → Registry → Initialize
         → Creates 'ontobricks_registry' schema in Postgres
Step 3:  make bootstrap-lakebase  (or make deploy again — idempotent)
         → Applies USAGE + DML on 'ontobricks_registry' schema
Step 4:  Build a Knowledge Graph (Settings → Knowledge Graph → Build)
         → Creates 'ontobricks_graph' schema (first build)
Step 5:  make bootstrap-lakebase  (or make deploy again)
         → Applies USAGE + DML on 'ontobricks_graph' schema
Step 6:  (managed_synced only) First Lakeflow snapshot completes
         → Creates the sync schema automatically
Step 7:  make bootstrap-lakebase  (or make deploy again)
         → Applies USAGE + DML on sync schema

make deploy is always safe to re-run — it skips schemas that don't exist yet and prints an informational message instead of failing.

8.2 — Verify permissions

Check what the SP has been granted from a SQL editor or psql:

-- From psql (connected as human admin)
\dn+                                     -- list schemas + ACLs
\dp ontobricks_graph.*                   -- table-level ACLs
SELECT * FROM information_schema.role_table_grants WHERE grantee = '<sp-client-id>';

Check CAN_USE on the instance (Databricks CLI):

databricks permissions get database-instances/<instance-id> -o json

9. Knowledge Graph build — step by step

This section describes what the Lakebase engine does during a Build for the managed_synced mode. For app_managed, steps 3–6 are replaced by direct COPY FROM STDIN ingestion.

Build logs are streamed to the UI and to the application log. Look for lines tagged [DT-BUILD <id>] for per-step context.

10. Troubleshooting

Database instance is not found

Failed to create synced table …: Database instance is not found.

Cause: The Lakebase project was created via the new autoscaling API (/postgres/projects) which is not listed in /database/instances. The Synced Tables API only accepts provisioned instance names.

Fix:

1. Delete the old project from the UI.
2. Re-create it with scripts/bootstrap/setup-lakebase.sh.
3. Update DEFAULT_LAKEBASE_PROJECT / DEFAULT_LAKEBASE_DATABASE in deploy.config.sh (optional: LAKEBASE_DATABASE_RESOURCE_SEGMENT).
4. make deploy.

If the name is reserved (Instance name is not unique), choose a different name — deleted names can stay ghost-reserved for hours.

The user is not authorized … assign 'Can Use' or 'Can Manage'

Failed to create synced table …:
The user is not authorized … assign cf06ae08… 'Can Use' or 'Can Manage'
for Database instance 6b981581-…

Cause: The app service principal lacks CAN_USE on the Lakebase instance.

Fix:

make bootstrap-lakebase
# or manually:
databricks permissions update database-instances/<instance-id> \
  --json '{"access_control_list":[{"service_principal_name":"<sp-id>","permission_level":"CAN_USE"}]}'

"<view>" is not a view

Failed: Could not create Lakebase union view after sync: "<name>" is not a view.

Cause: An object with the union view's name already exists as a TABLE (e.g. left over from an app_managed build on the same version). CREATE OR REPLACE VIEW cannot replace an existing table in PostgreSQL.

Fix (automatic since v0.4.1): The ensure_union_view function now auto-detects and drops the conflicting table before creating the view. If you see this error on an older deployment, drop the table manually:

-- From psql connected to the Lakebase database
DROP TABLE IF EXISTS ontobricks_graph."g_<domain>_vN" CASCADE;

Then retry the build.

_sync table '…_b' not found in Postgres

Failed: Could not create Lakebase union view after sync:
_sync table 'cust360auto_v4_sync_b' not found in Postgres.

Cause: The Lakeflow snapshot pipeline ran against the wrong branch (e.g. production instead of demo). The _sync table therefore landed in a different Lakebase branch schema.

Fix:

1. In Settings → Graph DB, verify database matches the Postgres datname for your branch.
2. In deploy.config.sh, verify DEFAULT_LAKEBASE_BRANCH points to the branch configured in the app settings.
3. make deploy and rebuild.

Lakebase connection failed: database "ontobricks_registry" does not exist

Cause: The registry database or schema has not been created yet. This is expected on a fresh deployment.

Fix (in order):

1. Open the deployed app.
2. Go to Settings → Registry → Initialize.
3. After initialisation, run make bootstrap-lakebase (or make deploy).

logicalDatabaseName must be defined when creating synced table in a standard catalog

Cause: The graph_engine_config.database key is missing or the PGDATABASE env var was not injected by the Apps runtime (postgres resource not bound).

Fix:

1. Confirm the postgres resource binding in the Databricks Apps UI.
2. Add "database": "<datname>" to the graph_engine_config JSON.
3. Restart the app.

Must specify either database instance name or both database project and branch

Cause: The synced table registration call is missing both the instance name and the project+branch pair. This means lakebase_project or lakebase_database_resource_segment is empty in deploy.config.sh.

Fix:

1. Confirm DEFAULT_LAKEBASE_PROJECT / DEFAULT_LAKEBASE_DATABASE in deploy.config.sh.
2. If auto-resolve fails, set LAKEBASE_DATABASE_RESOURCE_SEGMENT=db-xxxx-xxxxxxxxxx (from databricks postgres list-databases "projects/<project>/branches/<branch>" -o json).
3. make deploy.

Build uses app_managed even though managed_synced is configured in Settings

Symptom: The build log shows sync_mode=app_managed and iterates triples through the app process. The Settings → Graph DB page correctly displays the saved config with "sync_mode": "managed_synced".

Cause (fixed in v0.6.0): On cold start, if the Lakebase store was briefly unavailable (typical the first few seconds after the app container started), the GlobalConfigService in-memory cache was populated with the empty template (_empty()). Subsequent reads within the 5-minute cache TTL returned the empty config, so the build resolved sync_mode as missing and silently fell back to app_managed. The Settings page was not affected because it always forces a fresh read.

Fix: Upgrade to v0.6.0 — _resolve_lakebase_mode now bypasses the cache via force=True. No configuration change needed; simply redeploy.

Settings → Graph DB: catalog list is empty

The UC catalog dropdown uses the configured SQL Warehouse. If the warehouse is not yet saved to the global config, the dropdown falls back to DATABRICKS_SQL_WAREHOUSE_ID from the environment. If all fallbacks fail, the "Configure a SQL warehouse first" message appears.

Fix:

1. Go to Settings → Databricks and save the SQL Warehouse.
2. If it still fails, verify DATABRICKS_SQL_WAREHOUSE_ID / DATABRICKS_SQL_WAREHOUSE_ID_DEFAULT are set in the Apps resource binding or .env.

Delete asset button does nothing (Settings → Graph DB)

Cause: window.confirm() is suppressed inside the Databricks Apps iframe.

Fix (applied since v0.4.1): The delete flow uses a Bootstrap modal instead of window.confirm(). If you see this on an older deployment, make deploy to pick up the fix.

Quick-start checklist (Lakebase Graph DB)

[ ] 1. Create Lakebase project:
        ./scripts/bootstrap/setup-lakebase.sh --name <name> --capacity CU_2
[ ] 2. Set DEFAULT_LAKEBASE_PROJECT / DEFAULT_LAKEBASE_DATABASE in deploy.config.sh
        (optional override: LAKEBASE_DATABASE_RESOURCE_SEGMENT="db-xxxx-xxxxxxxxxx")
[ ] 3. Set DEFAULT_LAKEBASE_BRANCH (and DEFAULT_LAKEBASE_SCHEMA if needed) in deploy.config.sh
[ ] 4. make deploy
[ ] 5. Bind resources in Databricks Apps UI (sql-warehouse, volume, postgres)
[ ] 6. Open app → Settings → Registry → Initialize
[ ] 7. make bootstrap-lakebase  (or make deploy — idempotent)
[ ] 8. Open Settings → Graph DB
        - Engine: lakebase
        - Config: { "sync_mode": "app_managed" }  (or "managed_synced")
[ ] 9. Build your first Knowledge Graph
[ ] 10. make bootstrap-lakebase again (grants on ontobricks_graph schema)