Compare commits
2
Commits
| Author | SHA1 | Date | |
|---|---|---|---|
 Dikshant PradhanandClaude Opus 4.8
|
8afec653d8 | ||
|
Dikshant PradhanandClaude Opus 4.8
|
b68c61f672 |
@@ -29,6 +29,8 @@ The API service handles all memory operations (retain, recall, reflect).
|
||||
|
||||
If not provided, the server uses embedded `pg0` — convenient for development but not recommended for production.
|
||||
|
||||
To run against Oracle Database 23ai instead, set `HINDSIGHT_API_DATABASE_BACKEND=oracle` and use an `oracle+oracledb://…` URL. See the [Oracle Database guide](./oracle) for full setup instructions.
|
||||
|
||||
The `DATABASE_SCHEMA` setting allows you to use a custom PostgreSQL schema instead of the default `public` schema. This is useful for:
|
||||
- Multi-database setups where you want Hindsight tables in a dedicated schema
|
||||
- Hosting platforms (e.g., Supabase) where `public` schema is reserved or shared
|
||||
|
||||
@@ -0,0 +1,231 @@
|
||||
# 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](./storage) for the
|
||||
rationale. Oracle and PostgreSQL are configured independently; you pick one per
|
||||
deployment.
|
||||
:::
|
||||
|
||||
## Requirements
|
||||
|
||||
| Requirement | Details |
|
||||
|-------------|---------|
|
||||
| Oracle Database | **23ai** (23.4+). [Oracle Database Free 23ai](https://www.oracle.com/database/free/) works for development. |
|
||||
| `VECTOR` type | Used for embeddings. Requires the schema to live in an **ASSM tablespace** (see below). |
|
||||
| Oracle Text | Full-text search uses Oracle Text indexes. The schema user needs the `CTXAPP` role. |
|
||||
| Driver | [`python-oracledb`](https://python-oracledb.readthedocs.io/) ≥ 2.5.0, running in **thin mode** — pure Python, no Oracle Instant Client required. |
|
||||
|
||||
:::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:
|
||||
|
||||
```bash
|
||||
# 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:
|
||||
|
||||
```bash
|
||||
# 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. Once the script
|
||||
prints the connection URL, export the two variables it shows, run migrations,
|
||||
and start the API (see the steps below). 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:
|
||||
|
||||
```sql
|
||||
-- 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
|
||||
```
|
||||
|
||||
| Part | Example | Notes |
|
||||
|------|---------|-------|
|
||||
| `USER` / `PASSWORD` | `hindsight` / `s3cret` | The schema user from step 1. URL-encode reserved characters (`@`, `/`, `:`) in the password. |
|
||||
| `HOST:PORT` | `db.internal:1521` | The listener host and port (Oracle default is `1521`). |
|
||||
| `SERVICE_NAME` | `FREEPDB1` | The **service name** of your pluggable database (not the SID). `FREEPDB1` for Oracle Free. |
|
||||
|
||||
Example:
|
||||
|
||||
```
|
||||
oracle+oracledb://hindsight:[email protected]:1521/ORCLPDB1
|
||||
```
|
||||
|
||||
:::warning Connection support: Easy Connect only
|
||||
Hindsight builds the Oracle connection from the URL as a plain
|
||||
`host:port/service_name` descriptor. **Wallet-based mTLS, TLS/TCPS, and TNS
|
||||
aliases or full connect descriptors are not currently supported** by the
|
||||
connection layer. In practice:
|
||||
|
||||
- **Oracle Autonomous Database** and other services that require a wallet /
|
||||
mTLS are not supported as-is — connect to a database reachable over a direct
|
||||
`host:port/service` listener.
|
||||
- The driver does not negotiate TLS itself, so 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:
|
||||
|
||||
```bash
|
||||
export HINDSIGHT_API_DATABASE_BACKEND=oracle
|
||||
export HINDSIGHT_API_DATABASE_URL='oracle+oracledb://hindsight:[email protected]:1521/ORCLPDB1'
|
||||
```
|
||||
|
||||
`HINDSIGHT_API_DATABASE_BACKEND` defaults to `postgresql`; set it to `oracle` to
|
||||
select the Oracle backend. See [Configuration → Database](./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:
|
||||
|
||||
```bash
|
||||
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](#limitations-vs-postgresql).)
|
||||
|
||||
### 5. Start the API
|
||||
|
||||
```bash
|
||||
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](./configuration) page:
|
||||
|
||||
| Variable | Purpose |
|
||||
|----------|---------|
|
||||
| `HINDSIGHT_API_DATABASE_BACKEND` | `postgresql` (default) or `oracle`. |
|
||||
| `HINDSIGHT_API_DATABASE_URL` | `oracle+oracledb://…` connection URL. |
|
||||
| `HINDSIGHT_API_RUN_MIGRATIONS_ON_STARTUP` | Auto-apply migrations when the API boots (default `true`). |
|
||||
|
||||
## 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](#quick-start-local-oracle) 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
|
||||
|
||||
| Symptom | Cause / Fix |
|
||||
|---------|-------------|
|
||||
| `python-oracledb is required for Oracle backend` | The driver isn't installed. Run `pip install oracledb` (or install the `[oracle]` extra). |
|
||||
| Migration errors when creating embedding/`VECTOR` columns | The schema user's default tablespace is not ASSM (often the `SYSTEM` tablespace). Recreate the user in an ASSM tablespace as shown above. |
|
||||
| Full-text search errors / missing Oracle Text index | The schema user is missing the `CTXAPP` role. Run `GRANT CTXAPP TO <user>;`. |
|
||||
| `ORA-12514` / service not found | The URL uses a SID or wrong service name. Use the pluggable database **service name** (e.g. `FREEPDB1`), not the SID. |
|
||||
| Login works manually but fails from Hindsight | A reserved character in the password isn't URL-encoded. Encode `@ / : ?` in the `DATABASE_URL`. |
|
||||
|
||||
## See also
|
||||
|
||||
- [Storage](./storage) — why PostgreSQL is the default, and how Oracle fits in
|
||||
- [Configuration](./configuration#database) — all database environment variables
|
||||
- [Installation](./installation) — packaging and deployment options
|
||||
- [Admin CLI](./admin-cli) — administrative commands (PostgreSQL-only data operations)
|
||||
@@ -42,6 +42,8 @@ By building on PostgreSQL, we keep the system simple:
|
||||
|
||||
For enterprise deployments, Hindsight also supports Oracle AI Database with full feature parity. All memory operations—retain, recall, and reflect—work identically on Oracle, making it a drop-in option for organizations that standardize on Oracle infrastructure.
|
||||
|
||||
See the [Oracle Database guide](./oracle) for setup: prerequisites, provisioning, connection URLs, migrations, and the differences from PostgreSQL.
|
||||
|
||||
## Development with pg0
|
||||
|
||||
For local development, Hindsight uses **[pg0](https://github.com/vectorize-io/pg0)**—an embedded PostgreSQL distribution.
|
||||
|
||||
@@ -232,6 +232,12 @@ const sidebars: SidebarsConfig = {
|
||||
label: 'Admin CLI',
|
||||
customProps: { icon: 'lu-terminal' },
|
||||
},
|
||||
{
|
||||
type: 'doc',
|
||||
id: 'developer/oracle',
|
||||
label: 'Oracle Database',
|
||||
customProps: { icon: 'lu-database' },
|
||||
},
|
||||
{
|
||||
type: 'doc',
|
||||
id: 'developer/extensions',
|
||||
|
||||
@@ -29,6 +29,8 @@ The API service handles all memory operations (retain, recall, reflect).
|
||||
|
||||
If not provided, the server uses embedded `pg0` — convenient for development but not recommended for production.
|
||||
|
||||
To run against Oracle Database 23ai instead, set `HINDSIGHT_API_DATABASE_BACKEND=oracle` and use an `oracle+oracledb://…` URL. See the [Oracle Database guide](./oracle) for full setup instructions.
|
||||
|
||||
The `DATABASE_SCHEMA` setting allows you to use a custom PostgreSQL schema instead of the default `public` schema. This is useful for:
|
||||
- Multi-database setups where you want Hindsight tables in a dedicated schema
|
||||
- Hosting platforms (e.g., Supabase) where `public` schema is reserved or shared
|
||||
|
||||
@@ -0,0 +1,231 @@
|
||||
# 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](./storage) for the
|
||||
rationale. Oracle and PostgreSQL are configured independently; you pick one per
|
||||
deployment.
|
||||
:::
|
||||
|
||||
## Requirements
|
||||
|
||||
| Requirement | Details |
|
||||
|-------------|---------|
|
||||
| Oracle Database | **23ai** (23.4+). [Oracle Database Free 23ai](https://www.oracle.com/database/free/) works for development. |
|
||||
| `VECTOR` type | Used for embeddings. Requires the schema to live in an **ASSM tablespace** (see below). |
|
||||
| Oracle Text | Full-text search uses Oracle Text indexes. The schema user needs the `CTXAPP` role. |
|
||||
| Driver | [`python-oracledb`](https://python-oracledb.readthedocs.io/) ≥ 2.5.0, running in **thin mode** — pure Python, no Oracle Instant Client required. |
|
||||
|
||||
:::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:
|
||||
|
||||
```bash
|
||||
# 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:
|
||||
|
||||
```bash
|
||||
# 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. Once the script
|
||||
prints the connection URL, export the two variables it shows, run migrations,
|
||||
and start the API (see the steps below). 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:
|
||||
|
||||
```sql
|
||||
-- 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
|
||||
```
|
||||
|
||||
| Part | Example | Notes |
|
||||
|------|---------|-------|
|
||||
| `USER` / `PASSWORD` | `hindsight` / `s3cret` | The schema user from step 1. URL-encode reserved characters (`@`, `/`, `:`) in the password. |
|
||||
| `HOST:PORT` | `db.internal:1521` | The listener host and port (Oracle default is `1521`). |
|
||||
| `SERVICE_NAME` | `FREEPDB1` | The **service name** of your pluggable database (not the SID). `FREEPDB1` for Oracle Free. |
|
||||
|
||||
Example:
|
||||
|
||||
```
|
||||
oracle+oracledb://hindsight:[email protected]:1521/ORCLPDB1
|
||||
```
|
||||
|
||||
:::warning Connection support: Easy Connect only
|
||||
Hindsight builds the Oracle connection from the URL as a plain
|
||||
`host:port/service_name` descriptor. **Wallet-based mTLS, TLS/TCPS, and TNS
|
||||
aliases or full connect descriptors are not currently supported** by the
|
||||
connection layer. In practice:
|
||||
|
||||
- **Oracle Autonomous Database** and other services that require a wallet /
|
||||
mTLS are not supported as-is — connect to a database reachable over a direct
|
||||
`host:port/service` listener.
|
||||
- The driver does not negotiate TLS itself, so 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:
|
||||
|
||||
```bash
|
||||
export HINDSIGHT_API_DATABASE_BACKEND=oracle
|
||||
export HINDSIGHT_API_DATABASE_URL='oracle+oracledb://hindsight:[email protected]:1521/ORCLPDB1'
|
||||
```
|
||||
|
||||
`HINDSIGHT_API_DATABASE_BACKEND` defaults to `postgresql`; set it to `oracle` to
|
||||
select the Oracle backend. See [Configuration → Database](./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:
|
||||
|
||||
```bash
|
||||
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](#limitations-vs-postgresql).)
|
||||
|
||||
### 5. Start the API
|
||||
|
||||
```bash
|
||||
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](./configuration) page:
|
||||
|
||||
| Variable | Purpose |
|
||||
|----------|---------|
|
||||
| `HINDSIGHT_API_DATABASE_BACKEND` | `postgresql` (default) or `oracle`. |
|
||||
| `HINDSIGHT_API_DATABASE_URL` | `oracle+oracledb://…` connection URL. |
|
||||
| `HINDSIGHT_API_RUN_MIGRATIONS_ON_STARTUP` | Auto-apply migrations when the API boots (default `true`). |
|
||||
|
||||
## 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](#quick-start-local-oracle) 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
|
||||
|
||||
| Symptom | Cause / Fix |
|
||||
|---------|-------------|
|
||||
| `python-oracledb is required for Oracle backend` | The driver isn't installed. Run `pip install oracledb` (or install the `[oracle]` extra). |
|
||||
| Migration errors when creating embedding/`VECTOR` columns | The schema user's default tablespace is not ASSM (often the `SYSTEM` tablespace). Recreate the user in an ASSM tablespace as shown above. |
|
||||
| Full-text search errors / missing Oracle Text index | The schema user is missing the `CTXAPP` role. Run `GRANT CTXAPP TO <user>;`. |
|
||||
| `ORA-12514` / service not found | The URL uses a SID or wrong service name. Use the pluggable database **service name** (e.g. `FREEPDB1`), not the SID. |
|
||||
| Login works manually but fails from Hindsight | A reserved character in the password isn't URL-encoded. Encode `@ / : ?` in the `DATABASE_URL`. |
|
||||
|
||||
## See also
|
||||
|
||||
- [Storage](./storage) — why PostgreSQL is the default, and how Oracle fits in
|
||||
- [Configuration](./configuration#database) — all database environment variables
|
||||
- [Installation](./installation) — packaging and deployment options
|
||||
- [Admin CLI](./admin-cli) — administrative commands (PostgreSQL-only data operations)
|
||||
@@ -42,6 +42,8 @@ By building on PostgreSQL, we keep the system simple:
|
||||
|
||||
For enterprise deployments, Hindsight also supports Oracle AI Database with full feature parity. All memory operations—retain, recall, and reflect—work identically on Oracle, making it a drop-in option for organizations that standardize on Oracle infrastructure.
|
||||
|
||||
See the [Oracle Database guide](./oracle) for setup: prerequisites, provisioning, connection URLs, migrations, and the differences from PostgreSQL.
|
||||
|
||||
## Development with pg0
|
||||
|
||||
For local development, Hindsight uses **[pg0](https://github.com/vectorize-io/pg0)**—an embedded PostgreSQL distribution.
|
||||
|
||||
@@ -295,6 +295,14 @@
|
||||
"icon": "lu-terminal"
|
||||
}
|
||||
},
|
||||
{
|
||||
"type": "doc",
|
||||
"id": "developer/oracle",
|
||||
"label": "Oracle Database",
|
||||
"customProps": {
|
||||
"icon": "lu-database"
|
||||
}
|
||||
},
|
||||
{
|
||||
"type": "doc",
|
||||
"id": "developer/extensions",
|
||||
|
||||
@@ -29,6 +29,8 @@ The API service handles all memory operations (retain, recall, reflect).
|
||||
|
||||
If not provided, the server uses embedded `pg0` — convenient for development but not recommended for production.
|
||||
|
||||
To run against Oracle Database 23ai instead, set `HINDSIGHT_API_DATABASE_BACKEND=oracle` and use an `oracle+oracledb://…` URL. See the [Oracle Database guide](./oracle) for full setup instructions.
|
||||
|
||||
The `DATABASE_SCHEMA` setting allows you to use a custom PostgreSQL schema instead of the default `public` schema. This is useful for:
|
||||
- Multi-database setups where you want Hindsight tables in a dedicated schema
|
||||
- Hosting platforms (e.g., Supabase) where `public` schema is reserved or shared
|
||||
|
||||
@@ -0,0 +1,231 @@
|
||||
# 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](./storage) for the
|
||||
rationale. Oracle and PostgreSQL are configured independently; you pick one per
|
||||
deployment.
|
||||
:::
|
||||
|
||||
## Requirements
|
||||
|
||||
| Requirement | Details |
|
||||
|-------------|---------|
|
||||
| Oracle Database | **23ai** (23.4+). [Oracle Database Free 23ai](https://www.oracle.com/database/free/) works for development. |
|
||||
| `VECTOR` type | Used for embeddings. Requires the schema to live in an **ASSM tablespace** (see below). |
|
||||
| Oracle Text | Full-text search uses Oracle Text indexes. The schema user needs the `CTXAPP` role. |
|
||||
| Driver | [`python-oracledb`](https://python-oracledb.readthedocs.io/) ≥ 2.5.0, running in **thin mode** — pure Python, no Oracle Instant Client required. |
|
||||
|
||||
:::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:
|
||||
|
||||
```bash
|
||||
# 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:
|
||||
|
||||
```bash
|
||||
# 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. Once the script
|
||||
prints the connection URL, export the two variables it shows, run migrations,
|
||||
and start the API (see the steps below). 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:
|
||||
|
||||
```sql
|
||||
-- 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
|
||||
```
|
||||
|
||||
| Part | Example | Notes |
|
||||
|------|---------|-------|
|
||||
| `USER` / `PASSWORD` | `hindsight` / `s3cret` | The schema user from step 1. URL-encode reserved characters (`@`, `/`, `:`) in the password. |
|
||||
| `HOST:PORT` | `db.internal:1521` | The listener host and port (Oracle default is `1521`). |
|
||||
| `SERVICE_NAME` | `FREEPDB1` | The **service name** of your pluggable database (not the SID). `FREEPDB1` for Oracle Free. |
|
||||
|
||||
Example:
|
||||
|
||||
```
|
||||
oracle+oracledb://hindsight:s3cret@db.internal:1521/ORCLPDB1
|
||||
```
|
||||
|
||||
:::warning Connection support: Easy Connect only
|
||||
Hindsight builds the Oracle connection from the URL as a plain
|
||||
`host:port/service_name` descriptor. **Wallet-based mTLS, TLS/TCPS, and TNS
|
||||
aliases or full connect descriptors are not currently supported** by the
|
||||
connection layer. In practice:
|
||||
|
||||
- **Oracle Autonomous Database** and other services that require a wallet /
|
||||
mTLS are not supported as-is — connect to a database reachable over a direct
|
||||
`host:port/service` listener.
|
||||
- The driver does not negotiate TLS itself, so 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:
|
||||
|
||||
```bash
|
||||
export HINDSIGHT_API_DATABASE_BACKEND=oracle
|
||||
export HINDSIGHT_API_DATABASE_URL='oracle+oracledb://hindsight:s3cret@db.internal:1521/ORCLPDB1'
|
||||
```
|
||||
|
||||
`HINDSIGHT_API_DATABASE_BACKEND` defaults to `postgresql`; set it to `oracle` to
|
||||
select the Oracle backend. See [Configuration → Database](./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:
|
||||
|
||||
```bash
|
||||
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](#limitations-vs-postgresql).)
|
||||
|
||||
### 5. Start the API
|
||||
|
||||
```bash
|
||||
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](./configuration) page:
|
||||
|
||||
| Variable | Purpose |
|
||||
|----------|---------|
|
||||
| `HINDSIGHT_API_DATABASE_BACKEND` | `postgresql` (default) or `oracle`. |
|
||||
| `HINDSIGHT_API_DATABASE_URL` | `oracle+oracledb://…` connection URL. |
|
||||
| `HINDSIGHT_API_RUN_MIGRATIONS_ON_STARTUP` | Auto-apply migrations when the API boots (default `true`). |
|
||||
|
||||
## 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](#quick-start-local-oracle) 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
|
||||
|
||||
| Symptom | Cause / Fix |
|
||||
|---------|-------------|
|
||||
| `python-oracledb is required for Oracle backend` | The driver isn't installed. Run `pip install oracledb` (or install the `[oracle]` extra). |
|
||||
| Migration errors when creating embedding/`VECTOR` columns | The schema user's default tablespace is not ASSM (often the `SYSTEM` tablespace). Recreate the user in an ASSM tablespace as shown above. |
|
||||
| Full-text search errors / missing Oracle Text index | The schema user is missing the `CTXAPP` role. Run `GRANT CTXAPP TO <user>;`. |
|
||||
| `ORA-12514` / service not found | The URL uses a SID or wrong service name. Use the pluggable database **service name** (e.g. `FREEPDB1`), not the SID. |
|
||||
| Login works manually but fails from Hindsight | A reserved character in the password isn't URL-encoded. Encode `@ / : ?` in the `DATABASE_URL`. |
|
||||
|
||||
## See also
|
||||
|
||||
- [Storage](./storage) — why PostgreSQL is the default, and how Oracle fits in
|
||||
- [Configuration](./configuration#database) — all database environment variables
|
||||
- [Installation](./installation) — packaging and deployment options
|
||||
- [Admin CLI](./admin-cli) — administrative commands (PostgreSQL-only data operations)
|
||||
@@ -42,6 +42,8 @@ By building on PostgreSQL, we keep the system simple:
|
||||
|
||||
For enterprise deployments, Hindsight also supports Oracle AI Database with full feature parity. All memory operations—retain, recall, and reflect—work identically on Oracle, making it a drop-in option for organizations that standardize on Oracle infrastructure.
|
||||
|
||||
See the [Oracle Database guide](./oracle) for setup: prerequisites, provisioning, connection URLs, migrations, and the differences from PostgreSQL.
|
||||
|
||||
## Development with pg0
|
||||
|
||||
For local development, Hindsight uses **[pg0](https://github.com/vectorize-io/pg0)**—an embedded PostgreSQL distribution.
|
||||
|
||||
Reference in New Issue
Block a user