/hindsight-docs

Oracle Database

Hindsight uses PostgreSQL as its default storage backend, but it also runs on Oracle Database 23ai for organizations that standardize on Oracle infrastructure. All memory operations — retain, recall, and reflect — work the same way on Oracle; the backend is selected with a single environment variable.

This guide covers everything needed to run Hindsight against Oracle: the prerequisites, the driver, a local quick start, provisioning a production database, running migrations, and the handful of behavioural differences from PostgreSQL.

:::info When to use Oracle Oracle is the right choice when your organization already runs Oracle and needs Hindsight to live inside that footprint. For everything else, the default PostgreSQL backend is simpler to operate — see Storage for the rationale. Oracle and PostgreSQL are configured independently; you pick one per deployment. :::

Requirements

:::warning The schema must use an ASSM tablespace Oracle's SYSTEM tablespace uses manual segment space management (MSSM), which does not support VECTOR columns. Create the Hindsight user in a tablespace with Automatic Segment Space Management (ASSM) — otherwise migrations fail when they create embedding columns. The provisioning SQL below does this for you. :::

Install the driver

The Oracle driver is an optional extra — it is not bundled with the default packages. Install it alongside Hindsight:

# With the packaged extra
pip install "hindsight-api-slim[oracle]"

# Or add the driver to an existing install (e.g. the full hindsight-api package)
pip install hindsight-api oracledb

If the driver is missing at startup, Hindsight fails with: python-oracledb is required for Oracle backend. Install it with: pip install oracledb.

Quick start (local Oracle)

The fastest way to try Hindsight on Oracle is the bundled helper script, which starts a local Oracle Database Free 23ai container, provisions the test user with the correct tablespace and grants, and prints a ready-to-use connection URL:

# Start Oracle Free in Docker and bootstrap the hindsight_test user
./scripts/dev/start-oracle.sh

# ...prints:
#   export HINDSIGHT_API_DATABASE_BACKEND=oracle
#   export HINDSIGHT_API_DATABASE_URL='oracle+oracledb://hindsight_test:hindsight_test@localhost:1521/FREEPDB1'

# Stop and remove the container when done
./scripts/dev/stop-oracle.sh

A cold start takes 60–120s while the database initializes. If the first run reports a provisioning error, the database was still starting up — just re-run ./scripts/dev/start-oracle.sh once the container is healthy (it is idempotent).

Once the script prints the connection URL, export the variables it shows, and also set the schema to the Oracle user it created:

export HINDSIGHT_API_DATABASE_SCHEMA=HINDSIGHT_TEST

Then run migrations and start the API (see the steps below). Setting the schema is required on Oracle — see step 3. This is the same setup Hindsight's CI uses to test the Oracle backend.

Production setup

1. Provision the schema user

Connect to your pluggable database as a privileged user (for example SYSTEM) and create a dedicated tablespace and user for Hindsight. The tablespace must use ASSM so VECTOR columns are supported:

-- ASSM tablespace (required for VECTOR columns). Size to your data volume.
CREATE BIGFILE TABLESPACE hindsight_ts
    DATAFILE 'hindsight_ts.dbf' SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

-- Dedicated schema user
CREATE USER hindsight IDENTIFIED BY "<strong-password>"
    DEFAULT TABLESPACE hindsight_ts
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON hindsight_ts;

-- Object privileges Hindsight's migrations need
GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE TO hindsight;

-- Oracle Text (full-text search indexes)
GRANT CTXAPP TO hindsight;

:::note Least privilege CONNECT and RESOURCE cover the basics; the explicit CREATE TABLE / SEQUENCE / VIEW / PROCEDURE grants and CTXAPP are what the schema migrations require. No DBA role is needed. On a managed service where CREATE TABLESPACE is not available directly, provision the schema through the platform's admin tooling — the requirements are unchanged: an ASSM default tablespace (needed for VECTOR columns) plus the CTXAPP role. :::

2. Build the connection URL

Hindsight uses SQLAlchemy-style URLs. The Oracle form is:

oracle+oracledb://USER:PASSWORD@HOST:PORT/SERVICE_NAME

Example:

oracle+oracledb://hindsight:s3cret@db.internal:1521/ORCLPDB1

Full connect descriptors and TNS aliases

The form above is Easy Connect, which can only express host:port/service. When you need more — Autonomous Database over TCPS, retry_count, ssl_server_dn_match, or a TNS alias from a tnsnames.ora — leave the host empty and pass the descriptor as a dsn query parameter:

oracle+oracledb://USER:PASSWORD@/?dsn=DESCRIPTOR_OR_TNS_ALIAS

The descriptor has to be percent-encoded, like the password. For example, the Autonomous Database descriptor

(description=(retry_count=20)(address=(protocol=tcps)(port=1522)(host=adb.example.com))(connect_data=(service_name=abc_low))(security=(ssl_server_dn_match=yes)))

becomes

export HINDSIGHT_API_DATABASE_URL="oracle+oracledb://hindsight:s3cret@/?dsn=$(python3 -c 'import urllib.parse,sys;print(urllib.parse.quote(sys.stdin.read().strip()))' <<< '(description=(retry_count=20)(address=(protocol=tcps)(port=1522)(host=adb.example.com))(connect_data=(service_name=abc_low))(security=(ssl_server_dn_match=yes)))')"

:::warning Wallet-based mTLS is still not supported Hindsight passes no wallet directory or wallet password to the driver, so connections that require a wallet (mTLS) do not work. TCPS with ssl_server_dn_match does. Otherwise secure the connection at the network layer (private networking, VPN, or a TLS-terminating proxy). :::

3. Configure Hindsight

Point Hindsight at Oracle with two environment variables:

export HINDSIGHT_API_DATABASE_BACKEND=oracle
export HINDSIGHT_API_DATABASE_URL='oracle+oracledb://hindsight:s3cret@db.internal:1521/ORCLPDB1'
export HINDSIGHT_API_DATABASE_SCHEMA=HINDSIGHT   # the Oracle user from step 1

HINDSIGHT_API_DATABASE_BACKEND defaults to postgresql; set it to oracle to select the Oracle backend.

:::warning Set DATABASE_SCHEMA to your Oracle user HINDSIGHT_API_DATABASE_SCHEMA defaults to public — a PostgreSQL concept. On Oracle a schema is a user, there is no public schema, and leaving the default makes migrations fail with ORA-01435: user does not exist. Set it to the schema user you created in step 1, spelled exactly as Oracle stores it — uppercase (e.g. HINDSIGHT) unless you created the user with a quoted lower-case name. :::

See Configuration → Database for the full list of database variables.

4. Run migrations

Hindsight runs the same schema migrations on Oracle as on PostgreSQL. By default the API applies them automatically on startup (HINDSIGHT_API_RUN_MIGRATIONS_ON_STARTUP=true). To run them explicitly — for example in a controlled deploy step — use:

hindsight-admin run-db-migration

This routes through the dialect-aware migration runner and creates the Oracle schema. (Unlike the admin CLI's data-movement commands, run-db-migration is fully supported on Oracle — see Limitations.)

:::warning Migrate with your runtime embedding dimension The embedding VECTOR columns are sized to the dimension of the configured embeddings model. Run migrations with the same embeddings provider/model you will serve with — otherwise the column dimension won't match the vectors the API produces and retain fails with ORA-51803: Vector dimension count must match… (for example, a schema built for a 384-dim local model rejects the 1536-dim vectors from OpenAI text-embedding-3-small). If you change the embeddings model later, re-run migrations with --embedding-dimension <N> to resize the columns. :::

5. Start the API

hindsight-api

On startup Hindsight logs the resolved database (with credentials masked); it should show your Oracle host and confirm the Oracle backend is active.

Configuration reference

Oracle-relevant settings, all documented in full on the Configuration page:

Limitations vs PostgreSQL

Memory operations behave identically on Oracle, but a few operational and internal details differ:

• Admin CLI data commands are PostgreSQL-only. hindsight-admin backup, restore, bank export/import, and worker-status use asyncpg binary COPY and TRUNCATE, which are PostgreSQL-specific and not available on Oracle. Schema migrations (run-db-migration) are supported on Oracle.
• No embedded database. The pg0 embedded PostgreSQL used for zero-config local development has no Oracle equivalent — Oracle always requires a running instance (use the quick-start script locally).
• Consolidation reconciliation is skipped. The similarity-based near-duplicate reconciliation pass in consolidation (HINDSIGHT_API_CONSOLIDATION_DEDUP_THRESHOLD) is a PostgreSQL-only path; consolidation still runs on Oracle, without that extra reconciliation step.
• Entity resolution uses Oracle fuzzy matching. Fuzzy entity lookup during retain uses Oracle's text matching rather than PostgreSQL's pg_trgm trigram matching. Behaviour is equivalent; the underlying mechanism differs.

Troubleshooting

See also

• Storage — why PostgreSQL is the default, and how Oracle fits in
• Configuration — all database environment variables
• Installation — packaging and deployment options
• Admin CLI — administrative commands (PostgreSQL-only data operations)