docs(claude-code): document knowledge tools and subagent skill

The Claude Code integration now ships an MCP server with
agent_knowledge_* tools and a /hindsight-memory:create-agent skill
for scaffolding memory-backed subagents. Document both, plus the
new enableKnowledgeTools config flag and venv bootstrap behavior.

.env.example

@@ -2,7 +2,7 @@
 # Copy this file to .env and fill in your values
 
 # LLM Configuration (Required)
-# Supported providers: openai, groq, ollama, gemini, anthropic, lmstudio, vertexai, minimax, volcano
+# Supported providers: openai, groq, ollama, gemini, anthropic, lmstudio, vertexai, minimax, deepseek, volcano
 HINDSIGHT_API_LLM_PROVIDER=openai
 HINDSIGHT_API_LLM_API_KEY=your-api-key-here
 HINDSIGHT_API_LLM_MODEL=gpt-4o-mini
@@ -25,6 +25,11 @@ HINDSIGHT_API_LLM_BASE_URL=https://api.openai.com/v1
 # HINDSIGHT_API_LLM_API_KEY=your-minimax-api-key
 # HINDSIGHT_API_LLM_MODEL=MiniMax-M2.7
 
+# Example: DeepSeek configuration (https://api.deepseek.com)
+# HINDSIGHT_API_LLM_PROVIDER=deepseek
+# HINDSIGHT_API_LLM_API_KEY=your-deepseek-api-key
+# HINDSIGHT_API_LLM_MODEL=deepseek-v4-flash  # or deepseek-v4-pro / deepseek-chat / deepseek-reasoner
+
 # Example: LM Studio local configuration (Qwen 2.5 32B recommended)
 # HINDSIGHT_API_LLM_PROVIDER=lmstudio
 # HINDSIGHT_API_LLM_API_KEY=lmstudio

.github/workflows/deploy-docs.yml

@@ -34,7 +34,7 @@ jobs:
         env:
           UMAMI_URL: https://analytics.hindsight.vectorize.io
           UMAMI_WEBSITE_ID: ${{ secrets.UMAMI_WEBSITE_ID }}
-      - uses: actions/upload-pages-artifact@v4
+      - uses: actions/upload-pages-artifact@v5
         with:
           path: hindsight-docs/build
   deploy:

.github/workflows/perf-test.yml

@@ -0,0 +1,197 @@
+name: Performance Tests
+
+on:
+  schedule:
+    # Run daily at 06:00 UTC
+    - cron: "0 6 * * *"
+  workflow_dispatch:
+    inputs:
+      scale:
+        description: "Test scale (perf-test)"
+        type: choice
+        options:
+          - tiny
+          - small
+          - medium
+          - large
+        default: large
+      suite:
+        description: "Perf-test suite to run (blank = all)"
+        type: choice
+        options:
+          - ""
+          - retain
+          - recall
+          - recall-with-observations
+          - consolidation
+        default: ""
+      locomo_max_conversations:
+        description: "LoComo max conversations (0 = skip, blank = all)"
+        type: number
+        default: 0
+      locomo_skip:
+        description: "Skip LoComo job"
+        type: boolean
+        default: false
+      ref:
+        description: "Git ref to test (branch, tag, or SHA). Defaults to main."
+        type: string
+        default: ""
+
+concurrency:
+  group: perf-test
+  cancel-in-progress: true
+
+jobs:
+  perf-test:
+    runs-on: ubuntu-latest
+    steps:
+    - uses: actions/checkout@v6
+      with:
+        ref: ${{ inputs.ref || github.ref }}
+
+    - name: Install uv
+      uses: astral-sh/setup-uv@v7
+      with:
+        enable-cache: true
+        prune-cache: false
+
+    - name: Set up Python
+      uses: actions/setup-python@v6
+      with:
+        python-version-file: ".python-version"
+
+    - name: Cache HuggingFace models
+      uses: actions/cache@v5
+      with:
+        path: ~/.cache/huggingface
+        key: ${{ runner.os }}-huggingface-${{ hashFiles('hindsight-api-slim/pyproject.toml') }}
+        restore-keys: |
+          ${{ runner.os }}-huggingface-
+
+    - name: Pre-download models
+      working-directory: ./hindsight-api-slim
+      run: |
+        uv run --frozen --all-extras --index-strategy unsafe-best-match python -c "
+        from sentence_transformers import SentenceTransformer
+        print('Downloading embedding model...')
+        SentenceTransformer('BAAI/bge-small-en-v1.5')
+        print('Model downloaded successfully')
+        "
+
+    - name: Install hindsight-dev dependencies
+      run: |
+        cd hindsight-dev && uv sync --frozen --all-extras --index-strategy unsafe-best-match
+
+    - name: "Suite: retain"
+      if: inputs.suite == '' || inputs.suite == 'retain'
+      run: |
+        ./scripts/benchmarks/run-perf-test.sh \
+          --scale ${{ inputs.scale || 'large' }} \
+          --suite retain \
+          --output perf-results-retain.json
+
+    - name: "Suite: recall"
+      if: inputs.suite == '' || inputs.suite == 'recall'
+      run: |
+        ./scripts/benchmarks/run-perf-test.sh \
+          --scale ${{ inputs.scale || 'large' }} \
+          --suite recall \
+          --output perf-results-recall.json
+
+    - name: "Suite: recall-with-observations"
+      if: inputs.suite == '' || inputs.suite == 'recall-with-observations'
+      run: |
+        ./scripts/benchmarks/run-perf-test.sh \
+          --scale ${{ inputs.scale || 'large' }} \
+          --suite recall-with-observations \
+          --output perf-results-recall-with-observations.json
+
+    - name: "Suite: consolidation"
+      if: inputs.suite == '' || inputs.suite == 'consolidation'
+      run: |
+        ./scripts/benchmarks/run-perf-test.sh \
+          --scale ${{ inputs.scale || 'large' }} \
+          --suite consolidation \
+          --output perf-results-consolidation.json
+
+    - name: Upload perf results
+      if: always()
+      uses: actions/upload-artifact@v7
+      with:
+        name: perf-results-${{ github.sha }}
+        path: hindsight-dev/perf-results-*.json
+        retention-days: 90
+
+  locomo:
+    if: inputs.locomo_skip != true
+    runs-on: ubuntu-latest
+    env:
+      HINDSIGHT_API_LLM_PROVIDER: vertexai
+      HINDSIGHT_API_LLM_VERTEXAI_SERVICE_ACCOUNT_KEY: /tmp/gcp-credentials.json
+      HINDSIGHT_API_LLM_MODEL: google/gemini-2.5-flash-lite
+      HINDSIGHT_API_JUDGE_LLM_PROVIDER: vertexai
+      HINDSIGHT_API_JUDGE_LLM_MODEL: google/gemini-2.5-flash-lite
+      HINDSIGHT_API_ANSWER_LLM_PROVIDER: vertexai
+      HINDSIGHT_API_ANSWER_LLM_MODEL: google/gemini-2.5-flash
+    steps:
+    - uses: actions/checkout@v6
+      with:
+        ref: ${{ inputs.ref || github.ref }}
+
+    - name: Setup GCP credentials
+      run: |
+        printf '%s' '${{ secrets.GCP_VERTEXAI_CREDENTIALS }}' > /tmp/gcp-credentials.json
+        PROJECT_ID=$(jq -r '.project_id' /tmp/gcp-credentials.json)
+        echo "HINDSIGHT_API_LLM_VERTEXAI_PROJECT_ID=$PROJECT_ID" >> $GITHUB_ENV
+
+    - name: Install uv
+      uses: astral-sh/setup-uv@v7
+      with:
+        enable-cache: true
+        prune-cache: false
+
+    - name: Set up Python
+      uses: actions/setup-python@v6
+      with:
+        python-version-file: ".python-version"
+
+    - name: Cache HuggingFace models
+      uses: actions/cache@v5
+      with:
+        path: ~/.cache/huggingface
+        key: ${{ runner.os }}-huggingface-${{ hashFiles('hindsight-api-slim/pyproject.toml') }}
+        restore-keys: |
+          ${{ runner.os }}-huggingface-
+
+    - name: Pre-download models
+      working-directory: ./hindsight-api-slim
+      run: |
+        uv run --frozen --all-extras --index-strategy unsafe-best-match python -c "
+        from sentence_transformers import SentenceTransformer
+        print('Downloading embedding model...')
+        SentenceTransformer('BAAI/bge-small-en-v1.5')
+        print('Model downloaded successfully')
+        "
+
+    - name: Install hindsight-dev dependencies
+      run: |
+        cd hindsight-dev && uv sync --frozen --all-extras --index-strategy unsafe-best-match
+
+    - name: Run LoComo benchmark
+      run: |
+        MAX_CONV_ARG=""
+        if [ "${{ inputs.locomo_max_conversations }}" != "0" ] && [ -n "${{ inputs.locomo_max_conversations }}" ]; then
+          MAX_CONV_ARG="--max-conversations ${{ inputs.locomo_max_conversations }}"
+        fi
+        uv run python hindsight-dev/benchmarks/locomo/locomo_benchmark.py \
+          --wait-consolidation \
+          $MAX_CONV_ARG
+
+    - name: Upload LoComo results
+      if: always()
+      uses: actions/upload-artifact@v7
+      with:
+        name: locomo-results-${{ github.sha }}
+        path: hindsight-dev/benchmarks/locomo/results/
+        retention-days: 90

.github/workflows/release-integration.yml

@@ -81,17 +81,28 @@ jobs:
       with:
         node-version: '22'
         registry-url: 'https://registry.npmjs.org'
+        cache: 'npm'
+        cache-dependency-path: package-lock.json
 
-    # Guard: fail fast if the integration's lockfile resolves any dep from a
-    # monorepo workspace (link=true) or a relative file path. The release
-    # runner has no pre-built workspace `dist/` so `npm run build` would
-    # later fail at tsc with "Cannot find module". See:
-    # https://github.com/vectorize-io/hindsight/issues/… (0.6.0 openclaw retry)
     - name: Check integration lockfile
       if: steps.type.outputs.type == 'typescript'
       run: ./scripts/check-integration-lockfiles.sh
 
-    - name: Install dependencies
+    # Some integrations depend on workspace packages (hindsight-client,
+    # hindsight-all, hindsight-agent-sdk) via file: refs. Install from root
+    # so npm resolves them, then build the workspace deps before the integration.
+    - name: Install root workspace dependencies
+      if: steps.type.outputs.type == 'typescript'
+      run: npm ci
+
+    - name: Build workspace deps (hindsight-client, hindsight-all, hindsight-agent-sdk)
+      if: steps.type.outputs.type == 'typescript'
+      run: |
+        npm run build --workspace=hindsight-clients/typescript
+        npm run build --workspace=hindsight-all-npm
+        npm run build --workspace=hindsight-tools/hindsight-agent-sdk
+
+    - name: Install integration dependencies
       if: steps.type.outputs.type == 'typescript'
       working-directory: ./hindsight-integrations/${{ steps.info.outputs.integration }}
       run: npm ci

.github/workflows/release-tool.yml

@@ -0,0 +1,65 @@
+name: Release Tool
+
+on:
+  push:
+    tags:
+      - 'tools/**'
+
+jobs:
+  publish:
+    runs-on: ubuntu-latest
+
+    steps:
+    - uses: actions/checkout@v6
+
+    - name: Extract tool info
+      id: info
+      run: |
+        # refs/tags/tools/self-driving-agents/v0.0.1 → tool=self-driving-agents, version=0.0.1
+        TAG="${GITHUB_REF#refs/tags/}"
+        TOOL=$(echo "$TAG" | cut -d'/' -f2)
+        VERSION=$(echo "$TAG" | cut -d'/' -f3 | sed 's/^v//')
+        echo "tool=$TOOL" >> $GITHUB_OUTPUT
+        echo "version=$VERSION" >> $GITHUB_OUTPUT
+        echo "tag=$TAG" >> $GITHUB_OUTPUT
+        echo "Tool: $TOOL, Version: $VERSION"
+
+    - name: Set up Node.js
+      uses: actions/setup-node@v6
+      with:
+        node-version: '22'
+        registry-url: 'https://registry.npmjs.org'
+        cache: 'npm'
+        cache-dependency-path: package-lock.json
+
+    # Tools live under hindsight-tools/ and may depend on workspace packages
+    # (e.g. @vectorize-io/hindsight-client). Install from root so npm resolves
+    # workspace deps, then build any required workspace packages first.
+    - name: Install root workspace dependencies
+      run: npm ci
+
+    - name: Build hindsight-client (workspace dep)
+      run: npm run build --workspace=hindsight-clients/typescript
+
+    - name: Build hindsight-agent-sdk (workspace dep)
+      run: npm run build --workspace=hindsight-tools/hindsight-agent-sdk
+
+    - name: Build tool
+      run: npm run build --workspace=hindsight-tools/${{ steps.info.outputs.tool }}
+
+    - name: Publish to npm
+      working-directory: ./hindsight-tools/${{ steps.info.outputs.tool }}
+      run: |
+        set +e
+        OUTPUT=$(npm publish --access public 2>&1)
+        EXIT_CODE=$?
+        echo "$OUTPUT"
+        if [ $EXIT_CODE -ne 0 ]; then
+          if echo "$OUTPUT" | grep -q "cannot publish over"; then
+            echo "Package version already published, skipping..."
+            exit 0
+          fi
+          exit $EXIT_CODE
+        fi
+      env:
+        NODE_AUTH_TOKEN: ${{ secrets.NPM_TOKEN }}

.github/workflows/release.yml

@@ -497,6 +497,12 @@ jobs:
         name: rust-cli-hindsight-linux-amd64
         path: ./artifacts/rust-cli-linux
 
+    - name: Download Rust CLI (Linux ARM)
+      uses: actions/download-artifact@v8
+      with:
+        name: rust-cli-hindsight-linux-arm64
+        path: ./artifacts/rust-cli-linux-arm64
+
     - name: Download Rust CLI (macOS Intel)
       uses: actions/download-artifact@v8
       with:
@@ -533,6 +539,7 @@ jobs:
         cp artifacts/control-plane/*.tgz release-assets/ || true
         # Rust CLI binaries
         cp artifacts/rust-cli-linux/hindsight-linux-amd64 release-assets/ || true
+        cp artifacts/rust-cli-linux-arm64/hindsight-linux-arm64 release-assets/ || true
         cp artifacts/rust-cli-darwin-amd64/hindsight-darwin-amd64 release-assets/ || true
         cp artifacts/rust-cli-darwin-arm64/hindsight-darwin-arm64 release-assets/ || true
         # Helm chart

.prettierrc.json

@@ -0,0 +1,7 @@
+{
+  "semi": true,
+  "singleQuote": false,
+  "tabWidth": 2,
+  "trailingComma": "es5",
+  "printWidth": 100
+}

CLAUDE.md

@@ -68,8 +68,9 @@ cd hindsight-control-plane && npm run dev
 ./scripts/benchmarks/run-locomo.sh
 
 # Performance benchmarks
+./scripts/benchmarks/run-perf-test.sh                      # System perf (mock LLM + pg0)
+./scripts/benchmarks/run-perf-test.sh --scale tiny          # Quick smoke test
 ./scripts/benchmarks/run-consolidation.sh
-./scripts/benchmarks/run-retain-perf.sh --document <path>  # Requires API server running
 
 # Results viewer
 ./scripts/benchmarks/start-visualizer.sh  # View results at localhost:8001
@@ -122,12 +123,17 @@ Key tables: `banks`, `memory_units`, `documents`, `entities`, `entity_links`
 
 ### Adding Database Migrations
 
+Hindsight runs the same Alembic tree against PostgreSQL and Oracle 23ai. Each
+migration file dispatches through `run_for_dialect`, which calls either
+`_pg_upgrade` or `_oracle_upgrade` based on the live connection. A pytest lint
+(`tests/test_migration_shape.py`) fails CI if a migration omits the dispatcher.
+
 1. **Create a new migration file** in `hindsight-api-slim/hindsight_api/alembic/versions/`:
    - File name format: `<revision_id>_<description>.py` (e.g., `f1a2b3c4d5e6_add_new_index.py`)
    - Use a unique hex revision ID (12 chars)
    - Set `down_revision` to the previous migration's revision ID
 
-2. **Migration template**:
+2. **Migration template** (the `script.py.mako` template scaffolds this; fill in the bodies):
    ```python
    """Description of the migration
 
@@ -138,25 +144,58 @@ Key tables: `banks`, `memory_units`, `documents`, `entities`, `entity_links`
    from collections.abc import Sequence
    from alembic import context, op
 
+   from hindsight_api.alembic._dialect import run_for_dialect
+
    revision: str = "f1a2b3c4d5e6"
    down_revision: str | Sequence[str] | None = "<previous_revision_id>"
    branch_labels: str | Sequence[str] | None = None
    depends_on: str | Sequence[str] | None = None
 
-   def _get_schema_prefix() -> str:
-       """Get schema prefix for table names (required for multi-tenant support)."""
+
+   def _pg_schema_prefix() -> str:
+       """Schema-qualifier for raw SQL on PG (multi-tenant search_path)."""
        schema = context.config.get_main_option("target_schema")
        return f'"{schema}".' if schema else ""
 
-   def upgrade() -> None:
-       schema = _get_schema_prefix()
+
+   def _pg_upgrade() -> None:
+       schema = _pg_schema_prefix()
        op.execute(f"CREATE INDEX ... ON {schema}table_name(...)")
 
-   def downgrade() -> None:
-       schema = _get_schema_prefix()
+
+   def _pg_downgrade() -> None:
+       schema = _pg_schema_prefix()
        op.execute(f"DROP INDEX IF EXISTS {schema}index_name")
+
+
+   def _oracle_upgrade() -> None:
+       # Oracle 23ai equivalent. Use op.get_bind().exec_driver_sql for forms
+       # that Alembic core does not model (vector/text indexes, partitions).
+       op.execute("CREATE INDEX ... ON table_name(...)")
+
+
+   def _oracle_downgrade() -> None:
+       op.execute("DROP INDEX IF EXISTS index_name")
+
+
+   def upgrade() -> None:
+       run_for_dialect(pg=_pg_upgrade, oracle=_oracle_upgrade)
+
+
+   def downgrade() -> None:
+       run_for_dialect(pg=_pg_downgrade, oracle=_oracle_downgrade)
    ```
 
+   **Dialect-only migrations.** If a change genuinely doesn't apply to one
+   dialect (e.g. enabling `pg_trgm` is PG-only), omit the unused slot:
+   ```python
+   def upgrade() -> None:
+       run_for_dialect(pg=_pg_upgrade)  # oracle slot intentionally absent → no-op
+   ```
+   Make the asymmetry deliberate. Don't leave an Oracle slot empty just because
+   you didn't think about it — copy-pasting a PG migration without the Oracle
+   half is exactly how schemas drift.
+
 3. **Run migrations locally**:
    ```bash
    # Set database URL and run migrations for the base schema plus all tenants

README.md

@@ -84,6 +84,8 @@ cd docker/docker-compose
 docker compose up 
 ```
 
+> Oracle AI Database is also supported for enterprise deployments with full feature parity. See the [storage documentation](https://hindsight.vectorize.io/developer/storage) for details.
+
 
 >API: http://localhost:8888
 >UI: http://localhost:9999

deno.lock

@@ -42,6 +42,16 @@
   },
   "workspace": {
     "members": {
+      "hindsight-all-npm": {
+        "packageJson": {
+          "dependencies": [
+            "npm:@types/node@22",
+            "npm:tsup@^8.5.1",
+            "npm:typescript@^5.7.0",
+            "npm:vitest@^4.1.2"
+          ]
+        }
+      },
       "hindsight-clients/typescript": {
         "packageJson": {
           "dependencies": [
@@ -58,6 +68,7 @@
       "hindsight-control-plane": {
         "packageJson": {
           "dependencies": [
+            "npm:@chenglou/pretext@^0.0.3",
             "npm:@eslint/eslintrc@^3.3.3",
             "npm:@eslint/js@^9.39.2",
             "npm:@radix-ui/react-alert-dialog@^1.1.15",
@@ -91,11 +102,12 @@
             "npm:eslint@^9.39.1",
             "npm:[email protected]",
             "npm:next-themes@~0.4.6",
-            "npm:next@^16.1.6",
+            "npm:next@^16.1.7",
             "npm:postcss@^8.5.6",
             "npm:prettier@^3.7.4",
             "npm:react-chrono@^2.9.1",
             "npm:react-dom@^19.2.0",
+            "npm:react-is@^19.2.4",
             "npm:react-markdown@^10.1.0",
             "npm:react18-json-view@~0.2.9",
             "npm:react@^19.2.0",
@@ -133,6 +145,26 @@
             "npm:typescript@~5.6.2"
           ]
         }
+      },
+      "hindsight-tools/hindsight-agent-sdk": {
+        "packageJson": {
+          "dependencies": [
+            "npm:@vectorize-io/hindsight-client@~0.5.6",
+            "npm:typescript@^5.4.0",
+            "npm:vitest@^4.1.2"
+          ]
+        }
+      },
+      "hindsight-tools/self-driving-agents": {
+        "packageJson": {
+          "dependencies": [
+            "npm:@clack/prompts@^1.2.0",
+            "npm:@vectorize-io/hindsight-client@~0.5.6",
+            "npm:picocolors@^1.1.0",
+            "npm:typescript@^5.4.0",
+            "npm:vitest@^4.1.2"
+          ]
+        }
       }
     }
   }

helm/hindsight/Chart.yaml

@@ -2,8 +2,8 @@ apiVersion: v2
 name: hindsight
 description: Hindsight helm chart
 type: application
-version: 0.5.1
-appVersion: "0.5.1"
+version: 0.6.0
+appVersion: "0.6.0"
 keywords:
   - ai
   - memory

hindsight-all-npm/README.md

@@ -18,17 +18,17 @@ npm install @vectorize-io/hindsight-all @vectorize-io/hindsight-client
 ## Example
 
 ```ts
-import { HindsightServer, consoleLogger } from '@vectorize-io/hindsight-all';
-import { HindsightClient } from '@vectorize-io/hindsight-client';
+import { HindsightServer, consoleLogger } from "@vectorize-io/hindsight-all";
+import { HindsightClient } from "@vectorize-io/hindsight-client";
 
 const server = new HindsightServer({
-  profile: 'my-app',
+  profile: "my-app",
   port: 9077,
   env: {
-    HINDSIGHT_API_LLM_PROVIDER: 'anthropic',
+    HINDSIGHT_API_LLM_PROVIDER: "anthropic",
     HINDSIGHT_API_LLM_API_KEY: process.env.ANTHROPIC_API_KEY,
-    HINDSIGHT_API_LLM_MODEL: 'claude-sonnet-4-20250514',
-    HINDSIGHT_EMBED_DAEMON_IDLE_TIMEOUT: '0',
+    HINDSIGHT_API_LLM_MODEL: "claude-sonnet-4-20250514",
+    HINDSIGHT_EMBED_DAEMON_IDLE_TIMEOUT: "0",
   },
   logger: consoleLogger,
 });
@@ -37,11 +37,11 @@ await server.start();
 
 const client = new HindsightClient({ baseUrl: server.getBaseUrl() });
 
-await client.retain('user-123', 'User prefers dark mode and concise answers.', {
-  documentId: 'pref-2026-04-01',
+await client.retain("user-123", "User prefers dark mode and concise answers.", {
+  documentId: "pref-2026-04-01",
 });
 
-const recall = await client.recall('user-123', 'what are the user preferences?');
+const recall = await client.recall("user-123", "what are the user preferences?");
 console.log(recall.results);
 
 await server.stop();
@@ -62,7 +62,7 @@ If you're hacking on the Python `hindsight-embed` package in the same monorepo,
 
 ```ts
 new HindsightServer({
-  embedPackagePath: '/path/to/hindsight-embed',
+  embedPackagePath: "/path/to/hindsight-embed",
   // ...
 });
 ```

hindsight-all-npm/package.json

@@ -1,6 +1,6 @@
 {
   "name": "@vectorize-io/hindsight-all",
-  "version": "0.5.1",
+  "version": "0.6.0",
   "description": "Node.js programmatic lifecycle manager for Hindsight — embeds a local hindsight daemon in a Node application. Pair with @vectorize-io/hindsight-client for memory operations.",
   "main": "dist/index.js",
   "types": "dist/index.d.ts",

hindsight-all-npm/src/command.test.ts

@@ -1,32 +1,36 @@
-import { describe, it, expect } from 'vitest';
-import { getEmbedCommand } from './command.js';
+import { describe, it, expect } from "vitest";
+import { getEmbedCommand } from "./command.js";
 
-describe('getEmbedCommand', () => {
-  it('defaults to uvx hindsight-embed@latest', () => {
-    expect(getEmbedCommand()).toEqual(['uvx', 'hindsight-embed@latest']);
+describe("getEmbedCommand", () => {
+  it("defaults to uvx hindsight-embed@latest", () => {
+    expect(getEmbedCommand()).toEqual(["uvx", "hindsight-embed@latest"]);
   });
 
-  it('honours an explicit version', () => {
-    expect(getEmbedCommand({ embedVersion: '0.5.0' })).toEqual(['uvx', '[email protected]']);
+  it("honours an explicit version", () => {
+    expect(getEmbedCommand({ embedVersion: "0.5.0" })).toEqual(["uvx", "[email protected]"]);
   });
 
-  it('treats an empty version as latest', () => {
-    expect(getEmbedCommand({ embedVersion: '' })).toEqual(['uvx', 'hindsight-embed@latest']);
+  it("treats an empty version as latest", () => {
+    expect(getEmbedCommand({ embedVersion: "" })).toEqual(["uvx", "hindsight-embed@latest"]);
   });
 
-  it('uses uv run --directory when a local path is given', () => {
-    expect(getEmbedCommand({ embedPackagePath: '/abs/path' })).toEqual([
-      'uv',
-      'run',
-      '--directory',
-      '/abs/path',
-      'hindsight-embed',
+  it("uses uv run --directory when a local path is given", () => {
+    expect(getEmbedCommand({ embedPackagePath: "/abs/path" })).toEqual([
+      "uv",
+      "run",
+      "--directory",
+      "/abs/path",
+      "hindsight-embed",
     ]);
   });
 
-  it('local path takes precedence over version', () => {
-    expect(
-      getEmbedCommand({ embedPackagePath: '/abs/path', embedVersion: '0.5.0' }),
-    ).toEqual(['uv', 'run', '--directory', '/abs/path', 'hindsight-embed']);
+  it("local path takes precedence over version", () => {
+    expect(getEmbedCommand({ embedPackagePath: "/abs/path", embedVersion: "0.5.0" })).toEqual([
+      "uv",
+      "run",
+      "--directory",
+      "/abs/path",
+      "hindsight-embed",
+    ]);
   });
 });

hindsight-all-npm/src/command.ts

@@ -18,8 +18,8 @@ export interface EmbedCommandOptions {
 
 export function getEmbedCommand(opts: EmbedCommandOptions = {}): string[] {
   if (opts.embedPackagePath) {
-    return ['uv', 'run', '--directory', opts.embedPackagePath, 'hindsight-embed'];
+    return ["uv", "run", "--directory", opts.embedPackagePath, "hindsight-embed"];
   }
-  const version = opts.embedVersion && opts.embedVersion.length > 0 ? opts.embedVersion : 'latest';
-  return ['uvx', `hindsight-embed@${version}`];
+  const version = opts.embedVersion && opts.embedVersion.length > 0 ? opts.embedVersion : "latest";
+  return ["uvx", `hindsight-embed@${version}`];
 }

hindsight-all-npm/src/index.ts

@@ -1,7 +1,7 @@
-export { HindsightServer } from './server.js';
-export { getEmbedCommand } from './command.js';
-export { silentLogger, consoleLogger } from './logger.js';
+export { HindsightServer } from "./server.js";
+export { getEmbedCommand } from "./command.js";
+export { silentLogger, consoleLogger } from "./logger.js";
 
-export type { Logger } from './logger.js';
-export type { EmbedCommandOptions } from './command.js';
-export type { HindsightServerOptions } from './types.js';
+export type { Logger } from "./logger.js";
+export type { EmbedCommandOptions } from "./command.js";
+export type { HindsightServerOptions } from "./types.js";

hindsight-all-npm/src/server.test.ts

@@ -1,32 +1,32 @@
-import { describe, it, expect } from 'vitest';
-import { HindsightServer } from './server.js';
+import { describe, it, expect } from "vitest";
+import { HindsightServer } from "./server.js";
 
-describe('HindsightServer construction', () => {
-  it('defaults base URL to http://127.0.0.1:8888', () => {
+describe("HindsightServer construction", () => {
+  it("defaults base URL to http://127.0.0.1:8888", () => {
     const server = new HindsightServer();
-    expect(server.getBaseUrl()).toBe('http://127.0.0.1:8888');
-    expect(server.getProfile()).toBe('default');
+    expect(server.getBaseUrl()).toBe("http://127.0.0.1:8888");
+    expect(server.getProfile()).toBe("default");
   });
 
-  it('honours custom profile, port, and host', () => {
-    const server = new HindsightServer({ profile: 'app', port: 9077, host: '0.0.0.0' });
-    expect(server.getProfile()).toBe('app');
-    expect(server.getBaseUrl()).toBe('http://0.0.0.0:9077');
+  it("honours custom profile, port, and host", () => {
+    const server = new HindsightServer({ profile: "app", port: 9077, host: "0.0.0.0" });
+    expect(server.getProfile()).toBe("app");
+    expect(server.getBaseUrl()).toBe("http://0.0.0.0:9077");
   });
 
-  it('accepts open env pass-through without complaining about unknown keys', () => {
+  it("accepts open env pass-through without complaining about unknown keys", () => {
     const server = new HindsightServer({
       env: {
-        HINDSIGHT_API_LLM_PROVIDER: 'openai',
-        HINDSIGHT_API_LLM_MODEL: 'gpt-4o-mini',
+        HINDSIGHT_API_LLM_PROVIDER: "openai",
+        HINDSIGHT_API_LLM_MODEL: "gpt-4o-mini",
         // A field that does not exist today — should still be accepted
-        HINDSIGHT_FUTURE_FLAG: 'enabled',
+        HINDSIGHT_FUTURE_FLAG: "enabled",
       },
     });
     expect(server).toBeInstanceOf(HindsightServer);
   });
 
-  it('exposes checkHealth that returns false when no daemon is running', async () => {
+  it("exposes checkHealth that returns false when no daemon is running", async () => {
     // Random high port that nothing is listening on.
     const server = new HindsightServer({ port: 1, readyTimeoutMs: 100 });
     const healthy = await server.checkHealth();

hindsight-all-npm/src/server.ts

@@ -1,12 +1,12 @@
-import { spawn } from 'child_process';
-import { getEmbedCommand } from './command.js';
-import { silentLogger } from './logger.js';
-import type { Logger } from './logger.js';
-import type { HindsightServerOptions } from './types.js';
+import { spawn } from "child_process";
+import { getEmbedCommand } from "./command.js";
+import { silentLogger } from "./logger.js";
+import type { Logger } from "./logger.js";
+import type { HindsightServerOptions } from "./types.js";
 
 const DEFAULT_PORT = 8888;
-const DEFAULT_HOST = '127.0.0.1';
-const DEFAULT_PROFILE = 'default';
+const DEFAULT_HOST = "127.0.0.1";
+const DEFAULT_PROFILE = "default";
 const DEFAULT_READY_TIMEOUT_MS = 30_000;
 const DEFAULT_READY_POLL_INTERVAL_MS = 1_000;
 
@@ -61,7 +61,7 @@ export class HindsightServer {
     this.userEnv = opts.env ?? {};
     this.extraProfileCreateArgs = opts.extraProfileCreateArgs ?? [];
     this.extraDaemonStartArgs = opts.extraDaemonStartArgs ?? [];
-    this.platformCpuWorkaround = opts.platformCpuWorkaround ?? (process.platform === 'darwin');
+    this.platformCpuWorkaround = opts.platformCpuWorkaround ?? process.platform === "darwin";
     this.readyTimeoutMs = opts.readyTimeoutMs ?? DEFAULT_READY_TIMEOUT_MS;
     this.readyPollIntervalMs = opts.readyPollIntervalMs ?? DEFAULT_READY_POLL_INTERVAL_MS;
     this.logger = opts.logger ?? silentLogger;
@@ -100,22 +100,22 @@ export class HindsightServer {
       embedVersion: this.embedVersion,
       embedPackagePath: this.embedPackagePath,
     });
-    const args = [...baseArgs, 'daemon', '--profile', this.profile, 'stop'];
+    const args = [...baseArgs, "daemon", "--profile", this.profile, "stop"];
 
-    const child = spawn(cmd, args, { stdio: 'pipe' });
-    this.pipeOutput(child, 'daemon.stop');
+    const child = spawn(cmd, args, { stdio: "pipe" });
+    this.pipeOutput(child, "daemon.stop");
 
     await new Promise<void>((resolve) => {
       const timeout = setTimeout(() => {
         this.logger.warn(`[hindsight] daemon stop timed out after 5s`);
         resolve();
       }, 5_000);
-      child.on('exit', () => {
+      child.on("exit", () => {
         clearTimeout(timeout);
         this.logger.info(`[hindsight] daemon stopped`);
         resolve();
       });
-      child.on('error', (err) => {
+      child.on("error", (err) => {
         clearTimeout(timeout);
         this.logger.warn(`[hindsight] error stopping daemon: ${err.message}`);
         resolve();
@@ -147,9 +147,9 @@ export class HindsightServer {
   private buildEnv(): NodeJS.ProcessEnv {
     const merged: NodeJS.ProcessEnv = { ...process.env };
 
-    if (this.platformCpuWorkaround && process.platform === 'darwin') {
-      merged['HINDSIGHT_API_EMBEDDINGS_LOCAL_FORCE_CPU'] = '1';
-      merged['HINDSIGHT_API_RERANKER_LOCAL_FORCE_CPU'] = '1';
+    if (this.platformCpuWorkaround && process.platform === "darwin") {
+      merged["HINDSIGHT_API_EMBEDDINGS_LOCAL_FORCE_CPU"] = "1";
+      merged["HINDSIGHT_API_RERANKER_LOCAL_FORCE_CPU"] = "1";
     }
 
     for (const [key, value] of Object.entries(this.userEnv)) {
@@ -175,11 +175,11 @@ export class HindsightServer {
     });
     const createArgs = [
       ...baseArgs,
-      'profile',
-      'create',
+      "profile",
+      "create",
       this.profile,
-      '--merge',
-      '--port',
+      "--merge",
+      "--port",
       String(this.port),
     ];
 
@@ -189,12 +189,12 @@ export class HindsightServer {
     // host state into profile config.
     const envForProfile = this.collectProfileEnv(env);
     for (const [key, value] of Object.entries(envForProfile)) {
-      createArgs.push('--env', `${key}=${value}`);
+      createArgs.push("--env", `${key}=${value}`);
     }
 
     createArgs.push(...this.extraProfileCreateArgs);
 
-    await this.runCommand(cmd, createArgs, env, 'profile.create');
+    await this.runCommand(cmd, createArgs, env, "profile.create");
   }
 
   /** Collect only the env vars that should be written into the profile file. */
@@ -209,10 +209,10 @@ export class HindsightServer {
     }
 
     // 2. CPU workaround — only if auto-applied and not already overridden.
-    if (this.platformCpuWorkaround && process.platform === 'darwin') {
+    if (this.platformCpuWorkaround && process.platform === "darwin") {
       const cpuKeys = [
-        'HINDSIGHT_API_EMBEDDINGS_LOCAL_FORCE_CPU',
-        'HINDSIGHT_API_RERANKER_LOCAL_FORCE_CPU',
+        "HINDSIGHT_API_EMBEDDINGS_LOCAL_FORCE_CPU",
+        "HINDSIGHT_API_RERANKER_LOCAL_FORCE_CPU",
       ];
       for (const key of cpuKeys) {
         if (!(key in out) && env[key] !== undefined) {
@@ -231,14 +231,14 @@ export class HindsightServer {
     });
     const args = [
       ...baseArgs,
-      'daemon',
-      '--profile',
+      "daemon",
+      "--profile",
       this.profile,
-      'start',
+      "start",
       ...this.extraDaemonStartArgs,
     ];
 
-    await this.runCommand(cmd, args, env, 'daemon.start');
+    await this.runCommand(cmd, args, env, "daemon.start");
   }
 
   /**
@@ -249,34 +249,34 @@ export class HindsightServer {
     cmd: string,
     args: string[],
     env: NodeJS.ProcessEnv,
-    label: string,
+    label: string
   ): Promise<void> {
-    const child = spawn(cmd, args, { stdio: 'pipe', env });
-    let output = '';
-    child.stdout?.on('data', (data: Buffer) => {
+    const child = spawn(cmd, args, { stdio: "pipe", env });
+    let output = "";
+    child.stdout?.on("data", (data: Buffer) => {
       const text = data.toString();
       output += text;
-      for (const line of text.trimEnd().split('\n')) {
+      for (const line of text.trimEnd().split("\n")) {
         if (line) this.logger.info(`[hindsight:${label}] ${line}`);
       }
     });
-    child.stderr?.on('data', (data: Buffer) => {
+    child.stderr?.on("data", (data: Buffer) => {
       const text = data.toString();
       output += text;
-      for (const line of text.trimEnd().split('\n')) {
+      for (const line of text.trimEnd().split("\n")) {
         if (line) this.logger.warn(`[hindsight:${label}] ${line}`);
       }
     });
 
     await new Promise<void>((resolve, reject) => {
-      child.on('exit', (code) => {
+      child.on("exit", (code) => {
         if (code === 0) {
           resolve();
         } else {
           reject(new Error(`${label} failed with code ${code}: ${output.trim()}`));
         }
       });
-      child.on('error', (err) => {
+      child.on("error", (err) => {
         reject(new Error(`${label} failed to spawn: ${err.message}`, { cause: err }));
       });
     });
@@ -284,13 +284,13 @@ export class HindsightServer {
 
   /** Stream a spawned child's stdout/stderr through the logger without blocking. */
   private pipeOutput(child: ReturnType<typeof spawn>, label: string): void {
-    child.stdout?.on('data', (data: Buffer) => {
-      for (const line of data.toString().trimEnd().split('\n')) {
+    child.stdout?.on("data", (data: Buffer) => {
+      for (const line of data.toString().trimEnd().split("\n")) {
         if (line) this.logger.info(`[hindsight:${label}] ${line}`);
       }
     });
-    child.stderr?.on('data', (data: Buffer) => {
-      for (const line of data.toString().trimEnd().split('\n')) {
+    child.stderr?.on("data", (data: Buffer) => {
+      for (const line of data.toString().trimEnd().split("\n")) {
         if (line) this.logger.warn(`[hindsight:${label}] ${line}`);
       }
     });
@@ -316,7 +316,7 @@ export class HindsightServer {
       await new Promise((resolve) => setTimeout(resolve, this.readyPollIntervalMs));
     }
     throw new Error(
-      `Hindsight daemon did not become ready within ${this.readyTimeoutMs}ms at ${this.baseUrl}`,
+      `Hindsight daemon did not become ready within ${this.readyTimeoutMs}ms at ${this.baseUrl}`
     );
   }
 }

hindsight-all-npm/src/types.ts

@@ -1,4 +1,4 @@
-import type { Logger } from './logger.js';
+import type { Logger } from "./logger.js";
 
 /**
  * Options for {@link HindsightServer}.

hindsight-all-npm/tsup.config.ts

@@ -1,10 +1,10 @@
-import { defineConfig } from 'tsup';
+import { defineConfig } from "tsup";
 
 export default defineConfig({
-  entry: ['src/index.ts'],
-  format: ['esm'],
+  entry: ["src/index.ts"],
+  format: ["esm"],
   dts: true,
-  outDir: 'dist',
+  outDir: "dist",
   clean: true,
   sourcemap: true,
   bundle: true,

hindsight-all-npm/vitest.config.ts

@@ -1,8 +1,8 @@
-import { defineConfig } from 'vitest/config';
+import { defineConfig } from "vitest/config";
 
 export default defineConfig({
   test: {
-    include: ['src/**/*.test.ts'],
-    environment: 'node',
+    include: ["src/**/*.test.ts"],
+    environment: "node",
   },
 });

hindsight-all-slim/pyproject.toml

@@ -4,7 +4,7 @@ build-backend = "setuptools.build_meta"
 
 [project]
 name = "hindsight-all-slim"
-version = "0.5.1"
+version = "0.6.0"
 description = "Hindsight: Agent Memory That Works Like Human Memory - Slim All-in-One Bundle"
 readme = "README.md"
 requires-python = ">=3.11"

hindsight-all/hindsight/embedded.py

@@ -71,7 +71,7 @@ class HindsightEmbedded:
         llm_model: Model name to use
         llm_base_url: Optional custom base URL for LLM API
         database_url: Optional database URL override (default: profile-specific pg0)
-        idle_timeout: Seconds before daemon auto-exits when idle (default: 300)
+        idle_timeout: Seconds before daemon auto-exits when idle (default: 0, disabled)
         log_level: Daemon log level (default: "info")
         ui: Whether to start the control plane web UI alongside the daemon (default: False)
         ui_port: Port for the UI. Defaults to daemon_port + 10000.
@@ -86,7 +86,7 @@ class HindsightEmbedded:
         llm_model: str = "openai/gpt-oss-120b",
         llm_base_url: Optional[str] = None,
         database_url: Optional[str] = None,
-        idle_timeout: int = 300,
+        idle_timeout: int = 0,
         log_level: str = "info",
         ui: bool = False,
         ui_port: Optional[int] = None,
@@ -102,7 +102,7 @@ class HindsightEmbedded:
             llm_model: Model name to use
             llm_base_url: Optional custom base URL for LLM API
             database_url: Optional database URL override
-            idle_timeout: Seconds before daemon auto-exits when idle
+            idle_timeout: Seconds before daemon auto-exits when idle (0 = disabled)
             log_level: Daemon log level
             ui: Whether to start the control plane web UI alongside the daemon
             ui_port: Port for the UI (defaults to daemon_port + 10000)
@@ -142,14 +142,37 @@ class HindsightEmbedded:
         self._memories_api: Optional[MemoriesAPI] = None
 
     def _ensure_started(self):
-        """Ensure daemon is running (thread-safe)."""
+        """Ensure daemon is running (thread-safe), restarting if crashed."""
         if self._started and self._client is not None:
-            return
+            if self._manager.is_running(self.profile):
+                return
+            # Daemon crashed — reset state and fall through to restart
+            logger.warning(
+                "Daemon for profile '%s' is no longer responsive, restarting...",
+                self.profile,
+            )
+            try:
+                self._client.close()
+            except Exception:
+                logger.debug("Error closing stale client", exc_info=True)
+            self._client = None
+            self._started = False
 
         with self._lock:
             # Double-check after acquiring lock
             if self._started and self._client is not None:
-                return
+                if self._manager.is_running(self.profile):
+                    return
+                logger.warning(
+                    "Daemon for profile '%s' is no longer responsive (lock path), restarting...",
+                    self.profile,
+                )
+                try:
+                    self._client.close()
+                except Exception:
+                    logger.debug("Error closing stale client", exc_info=True)
+                self._client = None
+                self._started = False
 
             if self._closed:
                 raise RuntimeError(
@@ -253,23 +276,10 @@ class HindsightEmbedded:
         This allows HindsightEmbedded to expose all HindsightClient methods
         without manually wrapping each one.
         """
-        # Ensure server is started before proxying
+        # Ensure server is started (and restart if crashed) before proxying
         self._ensure_started()
 
-        # Get the attribute from the underlying client
-        attr = getattr(self._client, name)
-
-        # If it's a callable, wrap it to ensure server is started
-        # (shouldn't be needed since _ensure_started already called, but defensive)
-        if callable(attr):
-
-            def wrapper(*args, **kwargs):
-                self._ensure_started()
-                return attr(*args, **kwargs)
-
-            return wrapper
-
-        return attr
+        return getattr(self._client, name)
 
     def __enter__(self):
         """Context manager entry - ensures server is started."""
@@ -394,11 +404,8 @@ class HindsightEmbedded:
         """
         Get the underlying Hindsight client for direct access.
 
-        WARNING: Using this property directly means daemon restarts won't be
-        handled automatically. Prefer using the API namespaces (banks, mental_models,
-        directives, memories) or direct method calls on HindsightEmbedded instead.
-
-        Ensures daemon is started before returning the client.
+        Ensures daemon is started (and restarts it if it has crashed) before
+        returning the client.
 
         Returns:
             Hindsight: The underlying client instance
@@ -409,9 +416,8 @@ class HindsightEmbedded:
 
             embedded = HindsightEmbedded(profile="myapp", ...)
 
-            # Direct access (not recommended - daemon crashes won't be handled)
             client = embedded.client
-            banks = client.list_banks()  # If daemon crashes, this will fail
+            banks = client.list_banks()
             ```
         """
         self._ensure_started()
@@ -425,8 +431,13 @@ class HindsightEmbedded:
 
     @property
     def is_running(self) -> bool:
-        """Check if the client is initialized."""
-        return self._started and not self._closed and self._client is not None
+        """Check if the client is initialized and the daemon is responsive."""
+        return (
+            self._started
+            and not self._closed
+            and self._client is not None
+            and self._manager.is_running(self.profile)
+        )
 
     @property
     def ui_url(self) -> str:

hindsight-all/pyproject.toml

@@ -4,7 +4,7 @@ build-backend = "hatchling.build"
 
 [project]
 name = "hindsight-all"
-version = "0.5.1"
+version = "0.6.0"
 description = "Hindsight: Agent Memory That Works Like Human Memory - All-in-One Bundle"
 readme = "README.md"
 requires-python = ">=3.11"

hindsight-all/tests/test_embedded.py

@@ -401,3 +401,42 @@ def test_embedded_ui_flag(llm_config):
 
     finally:
         client.close()
+
+
+def test_embedded_daemon_crash_recovery(llm_config):
+    """
+    Test that HindsightEmbedded recovers when the daemon crashes.
+
+    Simulates a crash by stopping the daemon, then verifies
+    that the next operation transparently restarts it.
+    """
+    profile = f"test_crash_{uuid.uuid4().hex[:8]}"
+    bank_id = f"bank_{uuid.uuid4().hex[:8]}"
+
+    client = HindsightEmbedded(profile=profile, log_level="info", **llm_config)
+
+    try:
+        # Start daemon and store a memory
+        result = client.retain(bank_id=bank_id, content="Before crash")
+        assert result.success, "Initial retain should succeed"
+        assert client.is_running, "Daemon should be running"
+
+        original_url = client.url
+
+        # Simulate daemon crash by stopping it
+        client._manager.stop(client.profile)
+        assert not client._manager.is_running(client.profile), (
+            "Daemon should be stopped after simulated crash"
+        )
+
+        # Next operation should transparently restart the daemon
+        result2 = client.retain(bank_id=bank_id, content="After crash recovery")
+        assert result2.success, "Retain after crash recovery should succeed"
+        assert client.is_running, "Daemon should be running again after recovery"
+
+        # Verify recall still works
+        recall_result = client.recall(bank_id=bank_id, query="crash")
+        assert isinstance(recall_result.results, list), "Recall should return results"
+
+    finally:
+        client.close()

hindsight-api-slim/hindsight_api/__init__.py

@@ -46,4 +46,4 @@ __all__ = [
     "RemoteTEICrossEncoder",
     "LLMConfig",
 ]
-__version__ = "0.5.1"
+__version__ = "0.6.0"

hindsight-api-slim/hindsight_api/admin/cli.py

@@ -1,5 +1,7 @@
-"""
-Hindsight Admin CLI - backup and restore operations.
+"""PostgreSQL-only admin utilities (backup, restore, migration, worker management).
+
+Not supported on Oracle backends. Uses asyncpg.connect() directly, binary COPY,
+TRUNCATE CASCADE, and REFRESH MATERIALIZED VIEW — all inherently PG-specific.
 """
 
 import asyncio
@@ -15,15 +17,10 @@ import asyncpg
 import typer
 
 from ..config import DEFAULT_DATABASE_SCHEMA, HindsightConfig
+from ..engine.schema import fq_table_explicit as _fq_table
 from ..extensions import TenantExtension, load_extension
 from ..pg0 import parse_pg0_url, resolve_database_url
 
-
-def _fq_table(table: str, schema: str) -> str:
-    """Get fully-qualified table name with schema prefix."""
-    return f"{schema}.{table}"
-
-
 # Setup logging
 logging.basicConfig(
     level=logging.INFO,
@@ -375,6 +372,140 @@ def decommission_worker(
         typer.echo(f"No tasks found for worker '{worker_id}'")
 
 
+async def _decommission_all_workers(db_url: str, schema: str = "public") -> list[dict[str, Any]]:
+    """Release all processing tasks from all workers, setting them back to pending status."""
+    is_pg0, instance_name, _ = parse_pg0_url(db_url)
+    if is_pg0:
+        typer.echo(f"Starting embedded PostgreSQL (instance: {instance_name})...")
+    resolved_url = await resolve_database_url(db_url)
+
+    conn = await asyncpg.connect(resolved_url)
+    try:
+        table = _fq_table("async_operations", schema)
+        rows = await conn.fetch(
+            f"""
+            UPDATE {table}
+            SET status = 'pending', worker_id = NULL, claimed_at = NULL, updated_at = now()
+            WHERE status = 'processing'
+            RETURNING operation_id, worker_id, operation_type
+            """,
+        )
+        return [dict(r) for r in rows]
+    finally:
+        await conn.close()
+
+
+@app.command(name="decommission-workers")
+def decommission_workers(
+    schema: str = typer.Option("public", "--schema", "-s", help="Database schema"),
+    yes: bool = typer.Option(False, "--yes", "-y", help="Skip confirmation prompt"),
+):
+    """Release all processing tasks from all workers (sets status back to pending).
+
+    Use this command to recover from situations where one or more workers have crashed
+    or been removed without graceful shutdown. All tasks currently in 'processing' status
+    will be released back to the queue regardless of which worker owns them.
+    """
+    config = HindsightConfig.from_env()
+
+    if not config.database_url:
+        typer.echo("Error: Database URL not configured.", err=True)
+        typer.echo("Set HINDSIGHT_API_DATABASE_URL environment variable.", err=True)
+        raise typer.Exit(1)
+
+    if not yes:
+        typer.confirm(
+            "This will release ALL processing tasks from ALL workers back to pending. Continue?",
+            abort=True,
+        )
+
+    typer.echo(f"Decommissioning all workers (schema: {schema})...")
+
+    released = asyncio.run(_decommission_all_workers(config.database_url, schema))
+
+    if released:
+        # Group by worker_id for summary
+        by_worker: dict[str, int] = {}
+        for row in released:
+            wid = row["worker_id"] or "unknown"
+            by_worker[wid] = by_worker.get(wid, 0) + 1
+
+        typer.echo(f"Released {len(released)} task(s):")
+        for wid, count in by_worker.items():
+            typer.echo(f"  {wid}: {count} task(s)")
+    else:
+        typer.echo("No processing tasks found")
+
+
+async def _worker_status(db_url: str, schema: str = "public") -> list[dict[str, Any]]:
+    """Get all processing tasks grouped by worker with their last update time."""
+    is_pg0, instance_name, _ = parse_pg0_url(db_url)
+    if is_pg0:
+        typer.echo(f"Starting embedded PostgreSQL (instance: {instance_name})...")
+    resolved_url = await resolve_database_url(db_url)
+
+    conn = await asyncpg.connect(resolved_url)
+    try:
+        table = _fq_table("async_operations", schema)
+        rows = await conn.fetch(
+            f"""
+            SELECT worker_id, operation_id, operation_type, bank_id,
+                   claimed_at, updated_at,
+                   now() - claimed_at AS running_for,
+                   now() - updated_at AS last_update_ago
+            FROM {table}
+            WHERE status = 'processing'
+            ORDER BY worker_id, claimed_at
+            """,
+        )
+        return [dict(r) for r in rows]
+    finally:
+        await conn.close()
+
+
+@app.command(name="worker-status")
+def worker_status(
+    schema: str = typer.Option("public", "--schema", "-s", help="Database schema"),
+):
+    """Show all currently processing tasks grouped by worker.
+
+    Displays each worker's active tasks with operation type, bank, how long
+    the task has been running, and when it was last updated. Useful for
+    identifying dead workers with orphaned tasks.
+    """
+    config = HindsightConfig.from_env()
+
+    if not config.database_url:
+        typer.echo("Error: Database URL not configured.", err=True)
+        typer.echo("Set HINDSIGHT_API_DATABASE_URL environment variable.", err=True)
+        raise typer.Exit(1)
+
+    rows = asyncio.run(_worker_status(config.database_url, schema))
+
+    if not rows:
+        typer.echo("No processing tasks found")
+        return
+
+    # Group by worker_id
+    by_worker: dict[str, list[dict[str, Any]]] = {}
+    for row in rows:
+        wid = row["worker_id"] or "unknown"
+        by_worker.setdefault(wid, []).append(row)
+
+    typer.echo(f"Processing tasks across {len(by_worker)} worker(s):\n")
+    for wid, tasks in by_worker.items():
+        typer.echo(f"Worker: {wid} ({len(tasks)} task(s))")
+        for task in tasks:
+            op_id = str(task["operation_id"])[:8]
+            running_for = task["running_for"]
+            last_update = task["last_update_ago"]
+            typer.echo(
+                f"  {op_id}  {task['operation_type']:<20s} bank={task['bank_id']}"
+                f"  running={running_for}  last_update={last_update} ago"
+            )
+        typer.echo("")
+
+
 def main():
     app()
 

hindsight-api-slim/hindsight_api/alembic/_dialect.py

@@ -0,0 +1,42 @@
+"""Dialect dispatcher for Alembic migrations.
+
+Each migration file declares a ``_pg_upgrade``/``_oracle_upgrade`` (and matching
+downgrades) function and routes ``upgrade()``/``downgrade()`` through
+``run_for_dialect``. The helper inspects the live connection's dialect name and
+runs the matching function — or no-ops if the migration doesn't apply to the
+current backend.
+
+Use ``None`` (or omit the kwarg) when a migration intentionally has no effect
+on a dialect; the helper treats it as a no-op.
+"""
+
+from __future__ import annotations
+
+from collections.abc import Callable
+
+from alembic import op
+
+DialectFn = Callable[[], None]
+_SUPPORTED = ("postgresql", "oracle")
+
+
+def run_for_dialect(
+    *,
+    pg: DialectFn | None = None,
+    oracle: DialectFn | None = None,
+) -> None:
+    """Dispatch to the function matching the current bind's dialect.
+
+    Args:
+        pg: Function to run when the active bind is PostgreSQL.
+        oracle: Function to run when the active bind is Oracle.
+
+    Unrecognized dialects raise; an explicit ``None`` for the active dialect
+    is a no-op (the migration deliberately does nothing here).
+    """
+    name = op.get_bind().dialect.name
+    if name not in _SUPPORTED:
+        raise RuntimeError(f"Unsupported dialect for migration dispatch: {name!r}. Expected one of {_SUPPORTED}.")
+    fn = {"postgresql": pg, "oracle": oracle}[name]
+    if fn is not None:
+        fn()

hindsight-api-slim/hindsight_api/alembic/env.py

@@ -1,28 +1,37 @@
 """
-Alembic environment configuration for SQLAlchemy with pgvector.
-Uses synchronous psycopg2 driver for migrations to avoid pgbouncer issues.
+Alembic environment for Hindsight.
+
+Supports two dialects:
+
+* PostgreSQL (sync psycopg2 driver) — default; uses ``search_path`` for
+  multi-tenant schema isolation and forces read-write transactions to work
+  around Supabase's read-only-by-default sessions.
+* Oracle 23ai (``oracledb`` driver) — uses ``CURRENT_SCHEMA`` for tenant
+  isolation; no equivalent of ``search_path`` or read-only session quirks.
+
+Each migration file dispatches its DDL through ``alembic._dialect.run_for_dialect``
+so a single revision tree serves both backends.
 """
 
 import logging
 import os
 from pathlib import Path
+from urllib.parse import parse_qsl, urlencode, urlsplit, urlunsplit
 
 from alembic import context
 from dotenv import load_dotenv
-from sqlalchemy import engine_from_config, pool
+from sqlalchemy import Connection, engine_from_config, pool
+from sqlalchemy.engine import Engine
 
-# Import your models here
+from hindsight_api.db_url import is_oracle_url, to_libpq_url
 from hindsight_api.models import Base
 
 
-# Load environment variables based on HINDSIGHT_API_DATABASE_URL env var or default to local
-def load_env():
-    """Load environment variables from .env"""
-    # Check if HINDSIGHT_API_DATABASE_URL is already set (e.g., by CI/CD)
+def load_env() -> None:
+    """Load environment variables from .env (skipped if already configured)."""
     if os.getenv("HINDSIGHT_API_DATABASE_URL"):
         return
 
-    # Look for .env file in the parent directory (root of the workspace)
     root_dir = Path(__file__).parent.parent.parent
     env_file = root_dir / ".env"
 
@@ -32,30 +41,45 @@ def load_env():
 
 load_env()
 
-# this is the Alembic Config object, which provides
-# access to the values within the .ini file in use.
 config = context.config
-
-# Note: We don't call fileConfig() here to avoid overriding the application's logging configuration.
-# Alembic will use the existing logging configuration from the application.
-
-# add your model's MetaData object here
-# for 'autogenerate' support
 target_metadata = Base.metadata
 
-# other values from the config, defined by the needs of env.py,
-# can be acquired:
-# my_important_option = config.get_main_option("my_important_option")
-# ... etc.
+
+def _normalize_oracle_url(url: str) -> str:
+    """Coerce an Oracle URL into the SQLAlchemy form the oracledb dialect expects.
+
+    Two issues to handle:
+
+    1. Force the ``oracle+oracledb`` driver — bare ``oracle://`` defaults to
+       cx_Oracle.
+    2. Map a path-style service to ``?service_name=...``. SQLAlchemy's oracledb
+       dialect treats the URL path as a *SID* (legacy), but Oracle Free /
+       Autonomous DB only register a service name. Without this rewrite we get
+       ``DPY-6003: SID "FREEPDB1" is not registered`` even though the listener
+       is happy to accept the same name as a service.
+    """
+    parts = urlsplit(url)
+    if not parts.scheme.startswith("oracle"):
+        return url
+
+    new_scheme = "oracle+oracledb" if parts.scheme == "oracle" else parts.scheme
+    service = parts.path.lstrip("/")
+    new_query = parts.query
+    new_path = parts.path
+
+    # Promote /SERVICE to ?service_name=SERVICE unless the caller already
+    # supplied an explicit ?sid= or ?service_name=.
+    if service and "service_name=" not in new_query and "sid=" not in new_query:
+        params = [(k, v) for k, v in parse_qsl(new_query, keep_blank_values=True)]
+        params.append(("service_name", service))
+        new_query = urlencode(params)
+        new_path = ""
+
+    return urlunsplit((new_scheme, parts.netloc, new_path, new_query, parts.fragment))
 
 
 def get_database_url() -> str:
-    """
-    Get and process the database URL from config or environment.
-
-    Returns the URL with the correct driver (psycopg2) for migrations.
-    """
-    # Get database URL from config (set programmatically) or environment
+    """Resolve the migration URL from Alembic config or env, normalizing per-dialect."""
     database_url = config.get_main_option("sqlalchemy.url")
     if not database_url:
         database_url = os.getenv("HINDSIGHT_API_DATABASE_URL")
@@ -65,30 +89,18 @@ def get_database_url() -> str:
                 "Set HINDSIGHT_API_DATABASE_URL environment variable or pass database_url to run_migrations()."
             )
 
-    # For migrations, use psycopg2 (sync driver) to avoid pgbouncer prepared statement issues
-    if database_url.startswith("postgresql+asyncpg://"):
-        database_url = database_url.replace("postgresql+asyncpg://", "postgresql://", 1)
-    elif database_url.startswith("postgres+asyncpg://"):
-        database_url = database_url.replace("postgres+asyncpg://", "postgresql://", 1)
+    if is_oracle_url(database_url):
+        database_url = _normalize_oracle_url(database_url)
+    else:
+        # PG: convert SQLAlchemy-style asyncpg URLs and ?ssl= params to libpq form
+        # for the sync engine used during migrations.
+        database_url = to_libpq_url(database_url)
 
-    # Update config with processed URL for engine_from_config to use
     config.set_main_option("sqlalchemy.url", database_url)
-
     return database_url
 
 
 def run_migrations_offline() -> None:
-    """Run migrations in 'offline' mode.
-
-    This configures the context with just a URL
-    and not an Engine, though an Engine is acceptable
-    here as well.  By skipping the Engine creation
-    we don't even need a DBAPI to be available.
-
-    Calls to context.execute() here emit the given string to the
-    script output.
-
-    """
     logging.info("running offline")
     database_url = get_database_url()
 
@@ -103,14 +115,40 @@ def run_migrations_offline() -> None:
         context.run_migrations()
 
 
-def run_migrations_online() -> None:
-    """Run migrations in 'online' mode with synchronous engine."""
+def _configure_pg_session(engine: Engine, connection: Connection, target_schema: str | None) -> None:
+    """PG-only: ensure the session is RW (Supabase) and bind ``search_path``."""
     from sqlalchemy import event, text
 
-    get_database_url()  # Process and set the database URL in config
+    @event.listens_for(engine, "connect")
+    def set_read_write_mode(dbapi_connection, connection_record):
+        cursor = dbapi_connection.cursor()
+        cursor.execute("SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE")
+        if target_schema:
+            cursor.execute(f'CREATE SCHEMA IF NOT EXISTS "{target_schema}"')
+            cursor.execute(f'SET search_path TO "{target_schema}", public')
+        cursor.close()
 
-    # Check if we're targeting a specific schema (for multi-tenant isolation)
+    connection.execute(text("SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE"))
+    if target_schema:
+        connection.execute(text(f'CREATE SCHEMA IF NOT EXISTS "{target_schema}"'))
+        connection.execute(text(f'SET search_path TO "{target_schema}", public'))
+    connection.commit()
+
+
+def _configure_oracle_session(connection: Connection, target_schema: str | None) -> None:
+    """Oracle: switch the session's default schema; tolerate DDL contention."""
+    from sqlalchemy import text
+
+    # Wait up to 30s for DDL locks instead of failing immediately (ORA-00054).
+    connection.execute(text("ALTER SESSION SET DDL_LOCK_TIMEOUT = 30"))
+    if target_schema:
+        connection.execute(text(f'ALTER SESSION SET CURRENT_SCHEMA = "{target_schema}"'))
+
+
+def run_migrations_online() -> None:
+    database_url = get_database_url()
     target_schema = config.get_main_option("target_schema")
+    is_oracle = is_oracle_url(database_url)
 
     connectable = engine_from_config(
         config.get_section(config.config_ini_section, {}),
@@ -118,37 +156,19 @@ def run_migrations_online() -> None:
         poolclass=pool.NullPool,
     )
 
-    # Add event listener to ensure connection is in read-write mode
-    # This is needed for Supabase which may start connections in read-only mode
-    @event.listens_for(connectable, "connect")
-    def set_read_write_mode(dbapi_connection, connection_record):
-        cursor = dbapi_connection.cursor()
-        cursor.execute("SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE")
-        # If targeting a specific schema, set search_path
-        # Include public in search_path for access to shared extensions (pgvector)
-        if target_schema:
-            cursor.execute(f'CREATE SCHEMA IF NOT EXISTS "{target_schema}"')
-            cursor.execute(f'SET search_path TO "{target_schema}", public')
-        cursor.close()
-
     with connectable.connect() as connection:
-        # Also explicitly set read-write mode on this connection
-        connection.execute(text("SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE"))
+        if is_oracle:
+            _configure_oracle_session(connection, target_schema)
+        else:
+            _configure_pg_session(connectable, connection, target_schema)
 
-        # If targeting a specific schema, set search_path
-        # Include public in search_path for access to shared extensions (pgvector)
-        if target_schema:
-            connection.execute(text(f'CREATE SCHEMA IF NOT EXISTS "{target_schema}"'))
-            connection.execute(text(f'SET search_path TO "{target_schema}", public'))
-
-        connection.commit()  # Commit the SET command
-
-        # Configure context with version_table_schema if using a specific schema
         context_opts = {
             "connection": connection,
             "target_metadata": target_metadata,
         }
-        if target_schema:
+        if target_schema and not is_oracle:
+            # Oracle has no equivalent of PG's per-schema version table; the
+            # ``alembic_version`` table lives in CURRENT_SCHEMA implicitly.
             context_opts["version_table_schema"] = target_schema
 
         context.configure(**context_opts)
@@ -156,7 +176,12 @@ def run_migrations_online() -> None:
         with context.begin_transaction():
             context.run_migrations()
 
-        # Explicit commit to ensure changes are persisted (especially for Supabase)
+        # Always commit. PG needs it for the explicit RW-mode SET to persist;
+        # Oracle needs it because each DDL auto-commits but the trailing
+        # ``UPDATE alembic_version`` is plain DML that would otherwise stay in
+        # an open transaction and roll back when the connection closes —
+        # producing the "schema is created but the version row is one revision
+        # behind" failure mode.
         connection.commit()
 
 

hindsight-api-slim/hindsight_api/alembic/script.py.mako

@@ -11,6 +11,8 @@ from alembic import op
 import sqlalchemy as sa
 ${imports if imports else ""}
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = ${repr(up_revision)}
 down_revision: Union[str, Sequence[str], None] = ${repr(down_revision)}
@@ -18,11 +20,27 @@ branch_labels: Union[str, Sequence[str], None] = ${repr(branch_labels)}
 depends_on: Union[str, Sequence[str], None] = ${repr(depends_on)}
 
 
-def upgrade() -> None:
-    """Upgrade schema."""
+def _pg_upgrade() -> None:
+    """PostgreSQL upgrade. Set to ``None`` below if this migration is Oracle-only."""
     ${upgrades if upgrades else "pass"}
 
 
-def downgrade() -> None:
-    """Downgrade schema."""
+def _pg_downgrade() -> None:
     ${downgrades if downgrades else "pass"}
+
+
+def _oracle_upgrade() -> None:
+    """Oracle upgrade. Set to ``None`` below if this migration is Postgres-only."""
+    pass
+
+
+def _oracle_downgrade() -> None:
+    pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade, oracle=_oracle_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade, oracle=_oracle_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/2eee35aa3cfc_case_insensitive_entities_trgm_index.py

@@ -4,8 +4,8 @@ The previous GIN trigram index on canonical_name was case-sensitive, causing
 "Alice" and "alice" to have different trigram sets. This recreates it on
 LOWER(canonical_name) so the % operator matches case-insensitively.
 
-Revision ID: d6e7f8a9b0c1
-Revises: c5d6e7f8a9b0
+Revision ID: 2eee35aa3cfc
+Revises: d6e7f8a9b0c1
 Create Date: 2026-03-31
 """
 
@@ -13,8 +13,10 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
-revision: str = "d6e7f8a9b0c1"
-down_revision: str | Sequence[str] | None = "c5d6e7f8a9b0"
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "2eee35aa3cfc"
+down_revision: str | Sequence[str] | None = "d6e7f8a9b0c1"
 branch_labels: str | Sequence[str] | None = None
 depends_on: str | Sequence[str] | None = None
 
@@ -24,7 +26,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     # Drop the old case-sensitive trigram index
     op.execute("DROP INDEX IF EXISTS entities_canonical_name_trgm_idx")
@@ -35,7 +37,7 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     op.execute("DROP INDEX IF EXISTS entities_canonical_name_lower_trgm_idx")
     schema = _get_schema_prefix()
     # Restore original case-sensitive index
@@ -43,3 +45,11 @@ def downgrade() -> None:
         f"CREATE INDEX IF NOT EXISTS entities_canonical_name_trgm_idx "
         f"ON {schema}entities USING GIN (canonical_name gin_trgm_ops)"
     )
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/5a366d414dce_initial_schema.py

@@ -15,6 +15,8 @@ from pgvector.sqlalchemy import Vector
 from sqlalchemy import text
 from sqlalchemy.dialects import postgresql
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "5a366d414dce"
 down_revision: str | Sequence[str] | None = None
@@ -112,7 +114,7 @@ def _detect_text_search_extension() -> str:
         )
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Upgrade schema - create all tables from scratch."""
 
     # Note: pgvector extension is installed globally BEFORE migrations run
@@ -463,7 +465,7 @@ def upgrade() -> None:
     op.create_index("idx_unit_entities_entity", "unit_entities", ["entity_id"])
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Downgrade schema - drop all tables."""
 
     # Drop tables in reverse dependency order
@@ -523,3 +525,11 @@ def downgrade() -> None:
     # Drop extensions (optional - comment out if you want to keep them)
     # op.execute('DROP EXTENSION IF EXISTS vector')
     # op.execute('DROP EXTENSION IF EXISTS "uuid-ossp"')
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/8c6fa6f7230b_merge_v053_divergent_heads.py

@@ -0,0 +1,50 @@
+"""Merge divergent migration heads for v0.5.3
+
+v0.5.3 shipped with two migration heads that were never unified:
+
+  * ``c4x5y6z7a8b9`` — delta-refresh chain
+    (``add_last_refreshed_source_query`` ->
+     ``add_structured_content_to_mental_models`` ->
+     ``backsweep_orphan_observations_v2``)
+
+  * ``h3i4j5k6l7m8`` — per-bank vector indexes / audit log chain
+    (the ``merge_heads_and_add_unit_entities_index`` subtree)
+
+Both fork from ``z1u2v3w4x5y6``. Upgrades from v0.5.2 still succeed — the
+walker applies the three c4x5 revisions and leaves the database stamped at
+both heads — but the result is a split DAG: ``alembic upgrade head``
+(singular) is ambiguous, and any future migration has to pick one head as
+its parent, orphaning the other.
+
+This revision linearises the DAG into a single head. It has no schema
+effect.
+
+Revision ID: 8c6fa6f7230b
+Revises: c4x5y6z7a8b9, h3i4j5k6l7m8
+Create Date: 2026-04-18
+"""
+
+from collections.abc import Sequence
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "8c6fa6f7230b"
+down_revision: str | Sequence[str] | None = ("c4x5y6z7a8b9", "h3i4j5k6l7m8")
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _pg_upgrade() -> None:
+    pass
+
+
+def _pg_downgrade() -> None:
+    pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/9f8e7d6c5b4a_memory_links_deferrable_fk.py

@@ -0,0 +1,128 @@
+"""Make memory_links.from_unit_id and memory_links.to_unit_id FKs deferrable.
+
+Revision ID: 9f8e7d6c5b4a
+Revises: o1a2b3c4d5e6
+Create Date: 2026-05-03
+
+Background
+----------
+Concurrent retain (which INSERTs into ``memory_links``) and any code path
+that DELETEs a row whose deletion cascades into ``memory_links`` (e.g.
+delta-retain superseding chunks, which CASCADEs chunks → memory_units →
+memory_links) can deadlock under sustained single-tenant write load.
+
+The deadlock cycle:
+
+* Tx A: ``DELETE FROM chunks WHERE chunk_id = ANY(...)``
+  → CASCADE acquires row locks on memory_units, then on memory_links rows
+    where ``to_unit_id`` matches the deleted units.
+* Tx B: ``INSERT INTO memory_links (...)`` referencing one of the same
+  memory_units rows.
+  → The immediate FK check takes ``FOR KEY SHARE`` on those memory_units
+    rows.
+
+The two transactions take row locks on the same memory_units rows in
+opposite orders depending on which side started first. PostgreSQL detects
+the cycle and aborts one transaction; the loser is killed mid-batch, the
+winner continues. Workers then retry, but under sustained write load the
+pattern repeats.
+
+Fix
+---
+Make both ``memory_links → memory_units`` FKs (``from_unit_id`` and
+``to_unit_id``) ``DEFERRABLE INITIALLY DEFERRED``. This pushes the FK
+check from INSERT time to COMMIT time:
+
+* INSERT no longer takes ``FOR KEY SHARE`` on the memory_units row → no
+  contention with the cascading DELETE's row lock.
+* At COMMIT the engine validates referential integrity in one shot. If a
+  cascade-DELETE has since removed the referenced unit, the INSERT
+  transaction commits OR fails with a clean FK violation (sqlstate
+  23503) instead of a deadlock (sqlstate 40P01).
+
+The ``WHERE EXISTS`` filter already in ``_bulk_insert_links`` continues to
+filter out the typical "stale unit_id" case at INSERT time; the deferred
+FK is only the backstop for the narrow race window between the EXISTS
+probe and COMMIT. ``ON DELETE CASCADE`` semantics are unchanged — only
+the *timing* of the constraint check moves.
+
+The ``entity_id`` FK on ``memory_links`` is not changed; entities are not
+involved in the observed deadlock cycle and leaving the constraint
+immediate keeps the error message specific when an entity row is missing.
+"""
+
+from collections.abc import Sequence
+
+from alembic import context, op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "9f8e7d6c5b4a"
+down_revision: str | Sequence[str] | None = "o1a2b3c4d5e6"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _get_schema_prefix() -> str:
+    """Schema-qualifier for raw SQL on PG (multi-tenant search_path)."""
+    schema = context.config.get_main_option("target_schema")
+    return f'"{schema}".' if schema else ""
+
+
+# The two FK constraints installed by the initial schema migration
+# (5a366d414dce_initial_schema), mapped to the column they constrain.
+# They reference memory_units(id) with ON DELETE CASCADE — that
+# semantics is preserved; only the deferral attribute changes.
+_FK_COLUMNS: dict[str, str] = {
+    "fk_memory_links_from_unit_id_memory_units": "from_unit_id",
+    "fk_memory_links_to_unit_id_memory_units": "to_unit_id",
+}
+
+
+def _pg_upgrade() -> None:
+    schema = _get_schema_prefix()
+    # PostgreSQL doesn't allow altering the deferrability of an existing
+    # constraint with ALTER CONSTRAINT — the constraint must be dropped
+    # and recreated. DROP IF EXISTS makes the migration safe to re-run
+    # on schemas where the constraint was already recreated.
+    for fk_name, column in _FK_COLUMNS.items():
+        op.execute(f"ALTER TABLE {schema}memory_links DROP CONSTRAINT IF EXISTS {fk_name}")
+        op.execute(
+            f"""
+            ALTER TABLE {schema}memory_links
+                ADD CONSTRAINT {fk_name}
+                FOREIGN KEY ({column})
+                REFERENCES {schema}memory_units (id)
+                ON DELETE CASCADE
+                DEFERRABLE INITIALLY DEFERRED
+            """
+        )
+
+
+def _pg_downgrade() -> None:
+    schema = _get_schema_prefix()
+    # Revert to the default (NOT DEFERRABLE) form so a downgrade actually
+    # restores the prior schema state, even though that re-introduces the
+    # deadlock window.
+    for fk_name, column in _FK_COLUMNS.items():
+        op.execute(f"ALTER TABLE {schema}memory_links DROP CONSTRAINT IF EXISTS {fk_name}")
+        op.execute(
+            f"""
+            ALTER TABLE {schema}memory_links
+                ADD CONSTRAINT {fk_name}
+                FOREIGN KEY ({column})
+                REFERENCES {schema}memory_units (id)
+                ON DELETE CASCADE
+            """
+        )
+
+
+def upgrade() -> None:
+    # PG-only: Oracle's deferrable-FK semantics differ and the deadlock
+    # cycle was only observed on PostgreSQL. Oracle slot intentionally
+    # absent → no-op there.
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/a1b2c3d4e5f6_add_file_storage_table.py

@@ -15,6 +15,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "a1b2c3d4e5f6"
 down_revision: str | Sequence[str] | None = "y0t1u2v3w4x5"
 branch_labels: str | Sequence[str] | None = None
@@ -27,7 +29,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Create file_storage table for BYTEA storage."""
     schema = _get_schema_prefix()
 
@@ -52,7 +54,7 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove file_storage table and related columns."""
     schema = _get_schema_prefix()
 
@@ -68,3 +70,11 @@ def downgrade() -> None:
 
     # Drop file_storage table
     op.execute(f"DROP TABLE IF EXISTS {schema}file_storage")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/a2b3c4d5e6f7_add_text_signals_column.py

@@ -18,6 +18,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "a2b3c4d5e6f7"
 down_revision: str | Sequence[str] | None = "aa2b3c4d5e6f"
 branch_labels: str | Sequence[str] | None = None
@@ -33,7 +35,7 @@ def _detect_text_search_extension() -> str:
     return os.getenv("HINDSIGHT_API_TEXT_SEARCH_EXTENSION", "native").lower()
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     table = f"{schema}memory_units"
     text_search_ext = _detect_text_search_extension()
@@ -65,7 +67,7 @@ def upgrade() -> None:
     # pg_textsearch: no change — index operates on the base `text` column only
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     table = f"{schema}memory_units"
     text_search_ext = _detect_text_search_extension()
@@ -86,3 +88,11 @@ def downgrade() -> None:
         """)
 
     op.execute(f"ALTER TABLE {table} DROP COLUMN IF EXISTS text_signals")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/a2b3c4d5e6f8_add_gin_index_source_memory_ids.py

@@ -24,6 +24,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "a2b3c4d5e6f8"
 down_revision: str | Sequence[str] | None = "f7g8h9i0j1k2"
 branch_labels: str | Sequence[str] | None = None
@@ -35,7 +37,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     # CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
@@ -48,7 +50,15 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute("COMMIT")
     op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {schema}idx_memory_units_source_memory_ids")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/a2v3w4x5y6z7_add_last_refreshed_source_query.py

@@ -0,0 +1,48 @@
+"""Add last_refreshed_source_query column to mental_models
+
+Revision ID: a2v3w4x5y6z7
+Revises: z1u2v3w4x5y6
+Create Date: 2026-04-15
+
+Tracks the source_query that was used during the most recent refresh.
+Used by delta-mode refresh to detect when the query has changed: if it has,
+delta mode falls back to a full regeneration because the surgical-edit
+assumption (same topic, new facts) no longer holds.
+"""
+
+from collections.abc import Sequence
+
+from alembic import context, op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "a2v3w4x5y6z7"
+down_revision: str | Sequence[str] | None = "z1u2v3w4x5y6"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _get_schema_prefix() -> str:
+    schema = context.config.get_main_option("target_schema")
+    return f'"{schema}".' if schema else ""
+
+
+def _pg_upgrade() -> None:
+    schema = _get_schema_prefix()
+    op.execute(f"""
+        ALTER TABLE {schema}mental_models
+        ADD COLUMN IF NOT EXISTS last_refreshed_source_query TEXT
+    """)
+
+
+def _pg_downgrade() -> None:
+    schema = _get_schema_prefix()
+    op.execute(f"ALTER TABLE {schema}mental_models DROP COLUMN IF EXISTS last_refreshed_source_query")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/a3b4c5d6e7f8_add_consolidation_failed_at_to_memory_units.py

@@ -13,6 +13,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "a3b4c5d6e7f8"
 down_revision: str | Sequence[str] | None = "g7h8i9j0k1l2"
 branch_labels: str | Sequence[str] | None = None
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     op.execute(
@@ -45,8 +47,16 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
 
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_memory_units_consolidation_failed")
     op.execute(f"ALTER TABLE {schema}memory_units DROP COLUMN IF EXISTS consolidation_failed_at")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/a4b5c6d7e8f9_fix_per_bank_vector_index_type.py

@@ -1,7 +1,7 @@
 """Fix per-bank vector indexes to match configured extension
 
 Revision ID: a4b5c6d7e8f9
-Revises: d6e7f8a9b0c1
+Revises: 2eee35aa3cfc
 Create Date: 2026-04-01
 
 Migration d5e6f7a8b9c0 hardcoded HNSW when creating per-bank partial vector
@@ -20,8 +20,10 @@ from collections.abc import Sequence
 from alembic import context, op
 from sqlalchemy import text
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "a4b5c6d7e8f9"
-down_revision: str | Sequence[str] | None = "d6e7f8a9b0c1"
+down_revision: str | Sequence[str] | None = "2eee35aa3cfc"
 branch_labels: str | Sequence[str] | None = None
 depends_on: str | Sequence[str] | None = None
 
@@ -58,7 +60,7 @@ def _vector_index_using_clause() -> str:
         return "USING hnsw (embedding vector_cosine_ops)"
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     target = _target_index_type()
     if target is None:
         # pgvector — indexes are already HNSW, nothing to fix
@@ -113,7 +115,7 @@ def upgrade() -> None:
             )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     # Downgrade recreates indexes as HNSW (the original hardcoded behavior)
     target = _target_index_type()
     if target is None:
@@ -140,3 +142,11 @@ def downgrade() -> None:
                     f"WHERE fact_type = '{ft}' AND bank_id = '{escaped_bank_id}'"
                 )
             )
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/aa2b3c4d5e6f_nullable_event_date.py

@@ -12,6 +12,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "aa2b3c4d5e6f"
 down_revision: str | Sequence[str] | None = "z1u2v3w4x5y6"
 branch_labels: str | Sequence[str] | None = None
@@ -24,13 +26,21 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}memory_units ALTER COLUMN event_date DROP NOT NULL")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     # Backfill NULLs with now() before restoring the NOT NULL constraint
     op.execute(f"UPDATE {schema}memory_units SET event_date = now() WHERE event_date IS NULL")
     op.execute(f"ALTER TABLE {schema}memory_units ALTER COLUMN event_date SET NOT NULL")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/b3c4d5e6f7a8_add_content_hash_to_chunks.py

@@ -9,6 +9,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "b3c4d5e6f7a8"
 down_revision: str | Sequence[str] | None = "a3b4c5d6e7f8"
 branch_labels: str | Sequence[str] | None = None
@@ -21,12 +23,20 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     # Add content_hash column to chunks table for delta comparison
     op.execute(f"ALTER TABLE {schema}chunks ADD COLUMN IF NOT EXISTS content_hash TEXT")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}chunks DROP COLUMN IF EXISTS content_hash")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/b3c4d5e6f7g8_add_temporal_date_indexes.py

@@ -22,6 +22,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "b3c4d5e6f7g8"
 down_revision: str | Sequence[str] | None = "c1a2b3d4e5f6"
 branch_labels: str | Sequence[str] | None = None
@@ -33,7 +35,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     # Partial index on occurred_start (covers "occurred_start BETWEEN $4 AND $5")
     op.execute("COMMIT")
@@ -58,7 +60,7 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute("COMMIT")
     op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {schema}idx_memory_units_bank_mentioned_at")
@@ -66,3 +68,11 @@ def downgrade() -> None:
     op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {schema}idx_memory_units_bank_occurred_end")
     op.execute("COMMIT")
     op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {schema}idx_memory_units_bank_occurred_start")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/b3w4x5y6z7a8_add_structured_content_to_mental_models.py

@@ -0,0 +1,54 @@
+"""Add structured_content JSONB column to mental_models
+
+Revision ID: b3w4x5y6z7a8
+Revises: a2v3w4x5y6z7
+Create Date: 2026-04-16
+
+Stores the structured representation of a mental model document (sections,
+blocks). The plain ``content`` column remains the rendered markdown shown to
+users. ``structured_content`` is the source of truth for delta-mode refreshes:
+each refresh applies a list of typed operations to the structured doc, then
+re-renders to markdown — so unchanged sections come through byte-identical
+without an LLM round-trip.
+
+Nullable: existing markdown-only mental models continue to work in full mode;
+the column is populated lazily the first time a model is refreshed in delta
+mode.
+"""
+
+from collections.abc import Sequence
+
+from alembic import context, op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "b3w4x5y6z7a8"
+down_revision: str | Sequence[str] | None = "a2v3w4x5y6z7"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _get_schema_prefix() -> str:
+    schema = context.config.get_main_option("target_schema")
+    return f'"{schema}".' if schema else ""
+
+
+def _pg_upgrade() -> None:
+    schema = _get_schema_prefix()
+    op.execute(f"""
+        ALTER TABLE {schema}mental_models
+        ADD COLUMN IF NOT EXISTS structured_content JSONB
+    """)
+
+
+def _pg_downgrade() -> None:
+    schema = _get_schema_prefix()
+    op.execute(f"ALTER TABLE {schema}mental_models DROP COLUMN IF EXISTS structured_content")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/b4c5d6e7f8a9_backfill_observation_scopes.py

@@ -14,6 +14,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "b4c5d6e7f8a9"
 down_revision: str | Sequence[str] | None = "a2b3c4d5e6f7"
 branch_labels: str | Sequence[str] | None = None
@@ -25,10 +27,18 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}memory_units ADD COLUMN IF NOT EXISTS observation_scopes JSONB")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     pass  # intentionally no-op — safe to leave the column in place
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/b5d4e3f2a1c9_backfill_entity_cooccurrences_event_time.py

@@ -0,0 +1,91 @@
+"""Backfill entity_cooccurrences.last_cooccurred from memory_units event time
+
+Revision ID: b5d4e3f2a1c9
+Revises: o1a2b3c4d5e6
+Create Date: 2026-04-24
+
+The writer path in `entity_resolver.link_units_to_entities_batch` historically
+stamped `entity_cooccurrences.last_cooccurred` with `datetime.now(UTC)` at
+flush time, ignoring the source memory unit's event date. For normal online
+retains that's fine (now ≈ event time), but for any corpus that was
+backfilled in a single session — migrating from another memory system, for
+example — every co-occurrence collapsed to the import moment, which hid the
+underlying knowledge timeline from the dashboard's entity graph recency heat
+and from any downstream consumer of the column.
+
+The writer is fixed in the same change set to propagate the unit's event_date;
+this migration repairs historical rows by reading the true event time off
+`unit_entities × memory_units` (falling back to `created_at` when
+`mentioned_at` / `occurred_start` are NULL, so rows never regress).
+
+Oracle slot is intentionally absent: the Oracle baseline (`o1a2b3c4d5e6`)
+landed days before this fix, so any Oracle deployment runs the corrected
+writer against an effectively empty `entity_cooccurrences` — there is no
+historical residue on Oracle to repair. PG-only matches the asymmetry of
+the data, not negligence.
+"""
+
+from collections.abc import Sequence
+
+from alembic import context, op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "b5d4e3f2a1c9"
+down_revision: str | Sequence[str] | None = "o1a2b3c4d5e6"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _get_schema_prefix() -> str:
+    """Get schema prefix for table names (required for multi-tenant support)."""
+    schema = context.config.get_main_option("target_schema")
+    return f'"{schema}".' if schema else ""
+
+
+def _pg_upgrade() -> None:
+    schema = _get_schema_prefix()
+    # Recompute last_cooccurred from the true event time per entity pair.
+    # COALESCE picks the first non-null of mentioned_at / occurred_start /
+    # created_at so banks without event-time metadata still see a sane value
+    # (equivalent to the pre-fix behaviour) instead of NULL.
+    #
+    # The self-join on `unit_entities` is O(k²) per memory_unit in the number
+    # of distinct entities mentioned (k). For typical units k is small (single
+    # digits), but a bank with units containing hundreds of entities and tens
+    # of millions of co-occurrence rows may want to run this off-hours — the
+    # whole UPDATE is one statement, so it locks every targeted ec row for
+    # the duration. The migration is one-time; subsequent online writes
+    # already carry event time via the writer fix.
+    op.execute(
+        f"""
+        UPDATE {schema}entity_cooccurrences ec
+        SET last_cooccurred = sub.event_time
+        FROM (
+            SELECT
+                LEAST(ue1.entity_id, ue2.entity_id) AS e1,
+                GREATEST(ue1.entity_id, ue2.entity_id) AS e2,
+                MAX(COALESCE(mu.mentioned_at, mu.occurred_start, mu.created_at)) AS event_time
+            FROM {schema}memory_units mu
+            JOIN {schema}unit_entities ue1 ON ue1.unit_id = mu.id
+            JOIN {schema}unit_entities ue2 ON ue2.unit_id = mu.id AND ue1.entity_id <> ue2.entity_id
+            GROUP BY 1, 2
+        ) sub
+        WHERE ec.entity_id_1 = sub.e1 AND ec.entity_id_2 = sub.e2
+        """
+    )
+
+
+def _pg_downgrade() -> None:
+    # No-op: the previous column value was `now()` at the time of write and
+    # isn't recoverable. Rolling back the code is sufficient — new writes will
+    # revert to the old behaviour for subsequent retains.
+    pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)  # oracle slot intentionally absent — see header
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/b7c4d8e9f1a2_add_chunks_table.py

@@ -12,6 +12,8 @@ import sqlalchemy as sa
 from alembic import op
 from sqlalchemy.dialects import postgresql
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "b7c4d8e9f1a2"
 down_revision: str | Sequence[str] | None = "5a366d414dce"
@@ -19,7 +21,7 @@ branch_labels: str | Sequence[str] | None = None
 depends_on: str | Sequence[str] | None = None
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add chunks table and link memory_units to chunks."""
 
     # Create chunks table with single text PK (bank_id_document_id_chunk_index)
@@ -56,7 +58,7 @@ def upgrade() -> None:
     op.create_index("idx_memory_units_chunk_id", "memory_units", ["chunk_id"])
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove chunks table and chunk_id from memory_units."""
 
     # Drop index and foreign key from memory_units
@@ -68,3 +70,11 @@ def downgrade() -> None:
     op.drop_index("idx_chunks_bank_id", table_name="chunks")
     op.drop_index("idx_chunks_document_id", table_name="chunks")
     op.drop_table("chunks")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/c1a2b3d4e5f6_enable_pg_trgm_and_entities_trgm_index.py

@@ -14,6 +14,8 @@ from collections.abc import Sequence
 import sqlalchemy as sa
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "c1a2b3d4e5f6"
 down_revision: str | Sequence[str] | None = "b4c5d6e7f8a9"
 branch_labels: str | Sequence[str] | None = None
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     # pg_trgm ships with most PostgreSQL installations as a contrib module.
     # It enables fast similarity lookups via GIN indexes, used for entity name matching.
     # On managed services (e.g. Azure Flexible Server), the extension may not be
@@ -52,8 +54,16 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute("COMMIT")
     op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {schema}entities_canonical_name_trgm_idx")
     # Note: not dropping pg_trgm extension as other indexes may depend on it
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/c2d3e4f5g6h7_add_audit_log_table.py

@@ -14,6 +14,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "c2d3e4f5g6h7"
 down_revision: str | Sequence[str] | None = ("a3b4c5d6e7f8", "c8e5f2a3b4d1")
 branch_labels: str | Sequence[str] | None = None
@@ -26,7 +28,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     op.execute(
@@ -52,10 +54,18 @@ def upgrade() -> None:
     op.execute(f"CREATE INDEX IF NOT EXISTS idx_audit_log_started ON {schema}audit_log (started_at DESC)")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
 
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_audit_log_started")
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_audit_log_bank_started")
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_audit_log_action_started")
     op.execute(f"DROP TABLE IF EXISTS {schema}audit_log")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/c3d4e5f6g7h8_add_history_to_mental_models.py

@@ -9,6 +9,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "c3d4e5f6g7h8"
 down_revision: str | Sequence[str] | None = ("a2b3c4d5e6f7", "a2b3c4d5e6f8")
 branch_labels: str | Sequence[str] | None = None
@@ -20,11 +22,19 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}mental_models ADD COLUMN IF NOT EXISTS history JSONB DEFAULT '[]'::jsonb")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}mental_models DROP COLUMN IF EXISTS history")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/c4x5y6z7a8b9_backsweep_orphan_observations_v2.py

@@ -0,0 +1,76 @@
+"""backsweep_orphan_observations_v2
+
+Re-run of Pass 2 from migration ``g7h8i9j0k1l2_backsweep_orphan_observations``
+to sweep observations that became orphaned between then and now.
+
+Why we need it again:
+  ``fact_storage.handle_document_tracking`` (the retain/upsert path) deleted
+  the existing document via the FK cascade — which removes the source
+  ``memory_units`` — but never invalidated the observations derived from
+  them.  Only the explicit ``MemoryEngine.delete_document`` API called
+  ``_delete_stale_observations_for_memories``.  Every document re-ingest
+  therefore left orphan observations whose ``source_memory_ids`` arrays
+  pointed at IDs that no longer existed in ``memory_units``.
+
+  ``handle_document_tracking`` now calls the same cleanup helper before the
+  cascade, so no new orphans will accumulate going forward.  This migration
+  cleans up the historical residue.
+
+Identical to Pass 2 of g7h8i9j0k1l2.  Pass 1 (memory_units whose bank is
+gone) is intentionally not re-run; that scenario has no fresh source.
+
+Revision ID: c4x5y6z7a8b9
+Revises: b3w4x5y6z7a8
+Create Date: 2026-04-16
+"""
+
+from collections.abc import Sequence
+
+from alembic import context, op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "c4x5y6z7a8b9"
+down_revision: str | Sequence[str] | None = "b3w4x5y6z7a8"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _get_schema_prefix() -> str:
+    schema = context.config.get_main_option("target_schema")
+    return f'"{schema}".' if schema else ""
+
+
+def _pg_upgrade() -> None:
+    schema = _get_schema_prefix()
+    mu = f"{schema}memory_units"
+
+    # Delete observations whose every source_memory_id refers to a now-deleted
+    # memory_unit (or the array is empty).  Observations with at least one
+    # surviving source are left alone — the consolidation engine will refresh
+    # their text on the next pass.
+    op.execute(
+        f"""
+        DELETE FROM {mu} orphan
+        WHERE orphan.fact_type = 'observation'
+          AND NOT EXISTS (
+              SELECT 1
+              FROM {mu} src
+              WHERE src.id = ANY(orphan.source_memory_ids)
+                AND src.bank_id = orphan.bank_id
+          )
+        """
+    )
+
+
+def _pg_downgrade() -> None:
+    # Deleted rows cannot be restored.
+    pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/c5d6e7f8a9b0_add_bank_id_to_memory_links.py

@@ -13,6 +13,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "c5d6e7f8a9b0"
 down_revision: str | Sequence[str] | None = "b3c4d5e6f7a8"
 branch_labels: str | Sequence[str] | None = None
@@ -24,7 +26,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     # 1. Add nullable column
@@ -43,6 +45,14 @@ def upgrade() -> None:
     op.execute(f"ALTER TABLE {schema}memory_links ALTER COLUMN bank_id SET NOT NULL")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}memory_links DROP COLUMN IF EXISTS bank_id")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/c8e5f2a3b4d1_add_retain_params_to_documents.py

@@ -12,6 +12,8 @@ import sqlalchemy as sa
 from alembic import op
 from sqlalchemy.dialects import postgresql
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "c8e5f2a3b4d1"
 down_revision: str | Sequence[str] | None = "b7c4d8e9f1a2"
@@ -19,7 +21,7 @@ branch_labels: str | Sequence[str] | None = None
 depends_on: str | Sequence[str] | None = None
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add retain_params JSONB column to documents table."""
 
     # Add retain_params column to store parameters passed during retain
@@ -29,7 +31,7 @@ def upgrade() -> None:
     op.create_index("idx_documents_retain_params", "documents", ["retain_params"], postgresql_using="gin")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove retain_params column from documents table."""
 
     # Drop index
@@ -37,3 +39,11 @@ def downgrade() -> None:
 
     # Drop column
     op.drop_column("documents", "retain_params")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/d2e3f4a5b6c7_add_memory_links_expansion_indexes.py

@@ -34,6 +34,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "d2e3f4a5b6c7"
 down_revision: str | Sequence[str] | None = "b3c4d5e6f7g8"
 branch_labels: str | Sequence[str] | None = None
@@ -45,7 +47,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     # CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
@@ -75,9 +77,17 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute("COMMIT")
     op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {schema}idx_memory_links_entity_covering")
     op.execute("COMMIT")
     op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {schema}idx_memory_links_to_type_weight")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/d4e5f6g7h8i9_gin_source_memory_ids_fastupdate_off.py

@@ -18,6 +18,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "d4e5f6g7h8i9"
 down_revision: str | Sequence[str] | None = "d5e6f7a8b9c0"
 branch_labels: str | Sequence[str] | None = None
@@ -29,7 +31,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     # DROP + CREATE CONCURRENTLY must run outside a transaction block.
     op.execute("COMMIT")
@@ -42,7 +44,7 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute("COMMIT")
     op.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {schema}idx_memory_units_source_memory_ids")
@@ -51,3 +53,11 @@ def downgrade() -> None:
         f"ON {schema}memory_units USING GIN (source_memory_ids) "
         f"WHERE source_memory_ids IS NOT NULL"
     )
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/d5e6f7a8b9c0_add_bank_internal_id_and_per_bank_hnsw.py

@@ -25,6 +25,8 @@ from collections.abc import Sequence
 from alembic import context, op
 from sqlalchemy import text
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "d5e6f7a8b9c0"
 down_revision: str | Sequence[str] | None = "c3d4e5f6g7h8"
 branch_labels: str | Sequence[str] | None = None
@@ -53,7 +55,7 @@ def _vector_index_using_clause() -> str:
         return "USING hnsw (embedding vector_cosine_ops)"
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     # 1. Add internal_id column to banks
@@ -96,7 +98,7 @@ def upgrade() -> None:
             )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
 
     # Drop per-bank HNSW indexes (iterate existing banks)
@@ -137,3 +139,11 @@ def downgrade() -> None:
     # Drop internal_id column
     op.execute(f"ALTER TABLE {schema}banks DROP CONSTRAINT IF EXISTS banks_internal_id_unique")
     op.execute(f"ALTER TABLE {schema}banks DROP COLUMN IF EXISTS internal_id")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/d5y6z7a8b9c0_backfill_mental_models_subtype.py

@@ -0,0 +1,67 @@
+"""Backfill mental_models.subtype for databases that ran h3c4d5e6f7g8 before the fix
+
+Migration h3c4d5e6f7g8 used CREATE TABLE IF NOT EXISTS to create the
+mental_models table with a subtype column. But on databases where the table
+already existed (from the reflections -> mental_models rename chain), the
+CREATE was a no-op and subtype was never added. A fix was later added to
+h3c4d5e6f7g8 (Step 4b), but databases that had already run the migration
+never re-execute it. This migration adds the missing columns idempotently.
+
+Revision ID: d5y6z7a8b9c0
+Revises: 8c6fa6f7230b
+Create Date: 2026-04-18
+"""
+
+from collections.abc import Sequence
+
+from alembic import context, op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "d5y6z7a8b9c0"
+down_revision: str | Sequence[str] | None = "8c6fa6f7230b"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _get_schema_prefix() -> str:
+    schema = context.config.get_main_option("target_schema")
+    return f'"{schema}".' if schema else ""
+
+
+def _pg_upgrade() -> None:
+    schema = _get_schema_prefix()
+
+    # Add columns that h3c4d5e6f7g8 intended to create but missed when
+    # the table already existed from the reflections rename chain.
+    for col_ddl in [
+        "subtype VARCHAR(32) NOT NULL DEFAULT 'structural'",
+        "description TEXT NOT NULL DEFAULT ''",
+        "entity_id UUID",
+        "observations JSONB DEFAULT '{\"observations\": []}'::jsonb",
+        "links VARCHAR[]",
+        "last_updated TIMESTAMP WITH TIME ZONE",
+    ]:
+        op.execute(f"ALTER TABLE {schema}mental_models ADD COLUMN IF NOT EXISTS {col_ddl}")
+
+    # Ensure the CHECK constraint exists
+    op.execute(f"ALTER TABLE {schema}mental_models DROP CONSTRAINT IF EXISTS ck_mental_models_subtype")
+    op.execute(f"""
+        ALTER TABLE {schema}mental_models
+        ADD CONSTRAINT ck_mental_models_subtype CHECK (subtype IN ('structural', 'emergent', 'pinned', 'learned'))
+    """)
+
+    op.execute(f"CREATE INDEX IF NOT EXISTS idx_mental_models_subtype ON {schema}mental_models(bank_id, subtype)")
+
+
+def _pg_downgrade() -> None:
+    # No-op: these columns are part of the intended schema
+    pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/d6e7f8a9b0c1_drop_documents_metadata_column.py

@@ -0,0 +1,49 @@
+"""Drop unused metadata column from documents table
+
+Revision ID: d6e7f8a9b0c1
+Revises: c2d3e4f5g6h7, c5d6e7f8a9b0
+Create Date: 2026-03-30
+
+The metadata column on documents was always stored as an empty dict {}.
+Actual document metadata is stored inside retain_params.metadata.
+
+This migration was originally shipped in v0.4.22, then its file was deleted
+in v0.5.0 (and its revision ID accidentally reused by 2eee35aa3cfc).
+Restoring the file so that databases stamped at this revision can upgrade
+cleanly to v0.5.x+.
+"""
+
+from collections.abc import Sequence
+
+from alembic import context, op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "d6e7f8a9b0c1"
+down_revision: str | Sequence[str] | None = ("c2d3e4f5g6h7", "c5d6e7f8a9b0")
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _get_schema_prefix() -> str:
+    """Get schema prefix for table names (required for multi-tenant support)."""
+    schema = context.config.get_main_option("target_schema")
+    return f'"{schema}".' if schema else ""
+
+
+def _pg_upgrade() -> None:
+    schema = _get_schema_prefix()
+    op.execute(f"ALTER TABLE {schema}documents DROP COLUMN IF EXISTS metadata")
+
+
+def _pg_downgrade() -> None:
+    schema = _get_schema_prefix()
+    op.execute(f"ALTER TABLE {schema}documents ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{{}}'")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/d9f6a3b4c5e2_rename_bank_to_interactions.py

@@ -8,6 +8,8 @@ Create Date: 2024-12-04 15:00:00.000000
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision = "d9f6a3b4c5e2"
 down_revision = "c8e5f2a3b4d1"
@@ -21,7 +23,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade():
+def _pg_upgrade():
     schema = _get_schema_prefix()
 
     # Drop old check constraint FIRST (before updating data)
@@ -38,7 +40,7 @@ def upgrade():
     )
 
 
-def downgrade():
+def _pg_downgrade():
     schema = _get_schema_prefix()
 
     # Drop new check constraint FIRST
@@ -51,3 +53,11 @@ def downgrade():
     op.create_check_constraint(
         "memory_units_fact_type_check", "memory_units", "fact_type IN ('world', 'bank', 'opinion', 'observation')"
     )
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/e0a1b2c3d4e5_disposition_to_3_traits.py

@@ -14,6 +14,8 @@ from collections.abc import Sequence
 import sqlalchemy as sa
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "e0a1b2c3d4e5"
 down_revision: str | Sequence[str] | None = "rename_personality"
@@ -33,7 +35,7 @@ def _get_target_schema() -> str:
     return schema if schema else "public"
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Convert Big Five disposition to 3-trait disposition."""
     conn = op.get_bind()
     schema = _get_schema_prefix()
@@ -75,7 +77,7 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Convert back to Big Five disposition."""
     conn = op.get_bind()
     schema = _get_schema_prefix()
@@ -109,3 +111,11 @@ def downgrade() -> None:
         ALTER COLUMN disposition SET DEFAULT '{{"openness": 0.5, "conscientiousness": 0.5, "extraversion": 0.5, "agreeableness": 0.5, "neuroticism": 0.5, "bias_strength": 0.5}}'::jsonb
     """)
     )
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/e4f5a6b7c8d9_add_webhooks_tables.py

@@ -12,6 +12,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "e4f5a6b7c8d9"
 down_revision: str | Sequence[str] | None = "d2e3f4a5b6c7"
 branch_labels: str | Sequence[str] | None = None
@@ -23,7 +25,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     op.execute(
@@ -54,9 +56,17 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_async_operations_status_retry")
     op.execute(f"ALTER TABLE {schema}async_operations DROP COLUMN IF EXISTS next_retry_at")
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_webhooks_bank_id")
     op.execute(f"DROP TABLE IF EXISTS {schema}webhooks")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/e5f6g7h8i9j0_cascade_delete_ops_on_bank_delete.py

@@ -13,6 +13,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "e5f6g7h8i9j0"
 down_revision: str | Sequence[str] | None = "d4e5f6g7h8i9"
 branch_labels: str | Sequence[str] | None = None
@@ -24,7 +26,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     # Remove orphaned async_operations rows whose bank no longer exists
@@ -67,7 +69,15 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}async_operations DROP CONSTRAINT IF EXISTS fk_async_operations_bank_id")
     op.execute(f"ALTER TABLE {schema}webhooks DROP CONSTRAINT IF EXISTS fk_webhooks_bank_id")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/f1a2b3c4d5e6_add_memory_links_composite_index.py

@@ -12,6 +12,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "f1a2b3c4d5e6"
 down_revision: str | Sequence[str] | None = "e0a1b2c3d4e5"
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add composite index for efficient graph retrieval edge loading."""
     schema = _get_schema_prefix()
     # Create composite index for efficient top-k per (from_node, link_type) queries
@@ -38,7 +40,15 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove the composite index."""
     schema = _get_schema_prefix()
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_memory_links_from_type_weight")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/f6g7h8i9j0k1_chunk_fk_cascade_delete.py

@@ -10,6 +10,8 @@ from collections.abc import Sequence
 
 from alembic import op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "f6g7h8i9j0k1"
 down_revision: str | Sequence[str] | None = "e5f6g7h8i9j0"
@@ -17,7 +19,7 @@ branch_labels: str | Sequence[str] | None = None
 depends_on: str | Sequence[str] | None = None
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Change memory_units.chunk_id FK from SET NULL to CASCADE.
 
     When a document is deleted the CASCADE reaches chunks first; with SET NULL
@@ -49,9 +51,17 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Revert to SET NULL behaviour."""
     op.drop_constraint("memory_units_chunk_fkey", "memory_units", type_="foreignkey")
     op.create_foreign_key(
         "memory_units_chunk_fkey", "memory_units", "chunks", ["chunk_id"], ["chunk_id"], ondelete="SET NULL"
     )
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/f7g8h9i0j1k2_add_webhook_http_config.py

@@ -12,6 +12,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "f7g8h9i0j1k2"
 down_revision: str | Sequence[str] | None = "e4f5a6b7c8d9"
 branch_labels: str | Sequence[str] | None = None
@@ -23,11 +25,19 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}webhooks ADD COLUMN IF NOT EXISTS http_config JSONB NOT NULL DEFAULT '{{}}'")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}webhooks DROP COLUMN IF EXISTS http_config")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/g2a3b4c5d6e7_add_tags_column.py

@@ -12,6 +12,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "g2a3b4c5d6e7"
 down_revision: str | Sequence[str] | None = "f1a2b3c4d5e6"
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add tags column to memory_units and documents tables."""
     schema = _get_schema_prefix()
 
@@ -39,10 +41,18 @@ def upgrade() -> None:
     op.execute(f"ALTER TABLE {schema}documents ADD COLUMN IF NOT EXISTS tags VARCHAR[] NOT NULL DEFAULT '{{}}'")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove tags columns and index."""
     schema = _get_schema_prefix()
 
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_memory_units_tags")
     op.execute(f"ALTER TABLE {schema}memory_units DROP COLUMN IF EXISTS tags")
     op.execute(f"ALTER TABLE {schema}documents DROP COLUMN IF EXISTS tags")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/g2h3i4j5k6l7_remove_opinion_fact_type.py

@@ -13,6 +13,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "g2h3i4j5k6l7"
 down_revision: str | Sequence[str] | None = "f1a2b3c4d5e6"
@@ -26,7 +28,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     # 1. Delete any remaining opinion rows
@@ -49,7 +51,7 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
 
     # Restore confidence_score column
@@ -81,3 +83,11 @@ def downgrade() -> None:
         f"CREATE INDEX idx_memory_units_opinion_date ON {schema}memory_units "
         f"(bank_id, event_date DESC) WHERE fact_type = 'opinion'"
     )
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/g7h8i9j0k1l2_backsweep_orphan_observations.py

@@ -22,6 +22,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "g7h8i9j0k1l2"
 down_revision: str | Sequence[str] | None = "f6g7h8i9j0k1"
 branch_labels: str | Sequence[str] | None = None
@@ -33,7 +35,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     mu = f"{schema}memory_units"
     banks = f"{schema}banks"
@@ -66,6 +68,14 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     # Deleted rows cannot be restored.
     pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/h3c4d5e6f7g8_mental_models_v4.py

@@ -17,6 +17,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "h3c4d5e6f7g8"
 down_revision: str | Sequence[str] | None = "g2a3b4c5d6e7"
@@ -30,7 +32,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Apply mental models v4 changes."""
     schema = _get_schema_prefix()
 
@@ -85,6 +87,26 @@ def upgrade() -> None:
         )
     """)
 
+    # Step 4b: If the table already existed (from reflections rename chain),
+    # it won't have the v4 columns. Add them idempotently so the migration
+    # works regardless of whether CREATE TABLE above was a no-op.
+    for col_ddl in [
+        "subtype VARCHAR(32) NOT NULL DEFAULT 'directive'",
+        "description TEXT NOT NULL DEFAULT ''",
+        "entity_id UUID",
+        "observations JSONB DEFAULT '{\"observations\": []}'::jsonb",
+        "links VARCHAR[]",
+        "last_updated TIMESTAMP WITH TIME ZONE",
+    ]:
+        op.execute(f"ALTER TABLE {schema}mental_models ADD COLUMN IF NOT EXISTS {col_ddl}")
+
+    # Ensure the subtype CHECK constraint exists (may not if table was renamed)
+    op.execute(f"ALTER TABLE {schema}mental_models DROP CONSTRAINT IF EXISTS ck_mental_models_subtype")
+    op.execute(f"""
+        ALTER TABLE {schema}mental_models
+        ADD CONSTRAINT ck_mental_models_subtype CHECK (subtype IN ('structural', 'emergent', 'pinned', 'learned'))
+    """)
+
     # Step 5: Create indexes for efficient queries (if not exist)
     op.execute(f"CREATE INDEX IF NOT EXISTS idx_mental_models_bank_id ON {schema}mental_models(bank_id)")
     op.execute(f"CREATE INDEX IF NOT EXISTS idx_mental_models_subtype ON {schema}mental_models(bank_id, subtype)")
@@ -93,7 +115,7 @@ def upgrade() -> None:
     op.execute(f"CREATE INDEX IF NOT EXISTS idx_mental_models_tags ON {schema}mental_models USING GIN(tags)")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Revert mental models v4 changes."""
     schema = _get_schema_prefix()
 
@@ -110,3 +132,11 @@ def downgrade() -> None:
     op.execute(f"ALTER TABLE {schema}banks DROP COLUMN IF EXISTS mission")
 
     # Note: Cannot restore deleted observations - they are lost on downgrade
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/h3i4j5k6l7m8_merge_heads_and_add_unit_entities_index.py

@@ -1,7 +1,7 @@
 """Merge 3 migration heads and add unit_entities composite index
 
 Revision ID: h3i4j5k6l7m8
-Revises: a4b5c6d7e8f9, c2d3e4f5g6h7, g2h3i4j5k6l7
+Revises: a4b5c6d7e8f9, g2h3i4j5k6l7
 Create Date: 2026-04-07
 
 Merges three unmerged migration heads into one, and adds a composite index
@@ -13,8 +13,10 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "h3i4j5k6l7m8"
-down_revision: str | Sequence[str] | None = ("a4b5c6d7e8f9", "c2d3e4f5g6h7", "g2h3i4j5k6l7")
+down_revision: str | Sequence[str] | None = ("a4b5c6d7e8f9", "g2h3i4j5k6l7")
 branch_labels: str | Sequence[str] | None = None
 depends_on: str | Sequence[str] | None = None
 
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     # Composite index enables index-only scans for entity_id -> unit_id lookups
     op.execute(
@@ -35,8 +37,16 @@ def upgrade() -> None:
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_unit_entities_entity")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_unit_entities_entity_unit")
     # Restore the single-column index
     op.execute(f"CREATE INDEX IF NOT EXISTS idx_unit_entities_entity ON {schema}unit_entities (entity_id)")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/i4d5e6f7g8h9_delete_opinions.py

@@ -13,6 +13,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "i4d5e6f7g8h9"
 down_revision: str | Sequence[str] | None = "h3c4d5e6f7g8"
@@ -26,7 +28,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Delete opinion memory_units."""
     schema = _get_schema_prefix()
 
@@ -35,7 +37,15 @@ def upgrade() -> None:
     op.execute(f"DELETE FROM {schema}memory_units WHERE fact_type = 'opinion'")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Cannot restore deleted opinions."""
     # Note: Cannot restore deleted opinions - they are lost on downgrade
     pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/i4j5k6l7m8n9_add_cancelled_status_to_async_operations.py

@@ -0,0 +1,49 @@
+"""Add 'cancelled' to async_operations status check constraint
+
+Revision ID: i4j5k6l7m8n9
+Revises: d5y6z7a8b9c0
+Create Date: 2026-04-23
+"""
+
+from collections.abc import Sequence
+
+from alembic import context, op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "i4j5k6l7m8n9"
+down_revision: str | Sequence[str] | None = "d5y6z7a8b9c0"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _get_schema_prefix() -> str:
+    """Get schema prefix for table names (required for multi-tenant support)."""
+    schema = context.config.get_main_option("target_schema")
+    return f'"{schema}".' if schema else ""
+
+
+def _pg_upgrade() -> None:
+    schema = _get_schema_prefix()
+    op.execute(f"ALTER TABLE {schema}async_operations DROP CONSTRAINT IF EXISTS async_operations_status_check")
+    op.execute(
+        f"ALTER TABLE {schema}async_operations ADD CONSTRAINT async_operations_status_check "
+        f"CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'cancelled'))"
+    )
+
+
+def _pg_downgrade() -> None:
+    schema = _get_schema_prefix()
+    op.execute(f"ALTER TABLE {schema}async_operations DROP CONSTRAINT IF EXISTS async_operations_status_check")
+    op.execute(
+        f"ALTER TABLE {schema}async_operations ADD CONSTRAINT async_operations_status_check "
+        f"CHECK (status IN ('pending', 'processing', 'completed', 'failed'))"
+    )
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/j5e6f7g8h9i0_mental_model_versions.py

@@ -15,6 +15,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "j5e6f7g8h9i0"
 down_revision: str | Sequence[str] | None = "i4d5e6f7g8h9"
@@ -28,7 +30,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Create mental_model_versions table and add version tracking."""
     schema = _get_schema_prefix()
 
@@ -81,7 +83,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove mental_model_versions table and version column."""
     schema = _get_schema_prefix()
 
@@ -93,3 +95,11 @@ def downgrade() -> None:
 
     # Remove version column from mental_models
     op.execute(f"ALTER TABLE {schema}mental_models DROP COLUMN IF EXISTS version")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/k6f7g8h9i0j1_add_directive_subtype.py

@@ -12,6 +12,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "k6f7g8h9i0j1"
 down_revision: str | Sequence[str] | None = "j5e6f7g8h9i0"
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add 'directive' to mental_models subtype constraint."""
     schema = _get_schema_prefix()
 
@@ -40,7 +42,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove 'directive' from mental_models subtype constraint."""
     schema = _get_schema_prefix()
 
@@ -56,3 +58,11 @@ def downgrade() -> None:
         ADD CONSTRAINT ck_mental_models_subtype
         CHECK (subtype IN ('structural', 'emergent', 'pinned', 'learned'))
     """)
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/k6l7m8n9o0p1_create_observation_sources_table.py

@@ -0,0 +1,38 @@
+"""No-op: observation_sources table is Oracle-only
+
+Originally created the observation_sources junction table for all backends,
+but PG uses native array ops on the source_memory_ids column (faster at scale).
+Oracle creates this table in the o1a2b3c4d5e6 baseline migration instead.
+
+Kept as a no-op to preserve the Alembic revision chain.
+
+Revision ID: k6l7m8n9o0p1
+Revises: i4j5k6l7m8n9
+Create Date: 2026-04-24
+"""
+
+from collections.abc import Sequence
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "k6l7m8n9o0p1"
+down_revision: str | Sequence[str] | None = "i4j5k6l7m8n9"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _pg_upgrade() -> None:
+    # PG uses source_memory_ids[] array on memory_units — no junction table.
+    pass
+
+
+def _pg_downgrade() -> None:
+    pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/l7g8h9i0j1k2_add_worker_columns.py

@@ -17,6 +17,8 @@ import sqlalchemy as sa
 from alembic import context, op
 from sqlalchemy.dialects import postgresql
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "l7g8h9i0j1k2"
 down_revision: str | Sequence[str] | None = "k6f7g8h9i0j1"
@@ -30,7 +32,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add worker columns to async_operations."""
     schema = _get_schema_prefix()
 
@@ -78,7 +80,7 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove worker columns from async_operations."""
     schema = _get_schema_prefix()
 
@@ -107,3 +109,11 @@ def downgrade() -> None:
         "worker_id",
         schema=context.config.get_main_option("target_schema") or None,
     )
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/m3rg3h3ad5f6_merge_deferrable_fk_and_cooccurrence_backfill.py

@@ -0,0 +1,31 @@
+"""Merge divergent heads from deferrable FK and cooccurrence backfill
+
+Revision ID: m3rg3h3ad5f6
+Revises: 9f8e7d6c5b4a, b5d4e3f2a1c9
+Create Date: 2026-05-04
+"""
+
+from collections.abc import Sequence
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "m3rg3h3ad5f6"
+down_revision: tuple[str, ...] = ("9f8e7d6c5b4a", "b5d4e3f2a1c9")
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _pg_upgrade() -> None:
+    pass
+
+
+def _pg_downgrade() -> None:
+    pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/m8h9i0j1k2l3_mental_model_id_to_text.py

@@ -12,6 +12,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "m8h9i0j1k2l3"
 down_revision: str | Sequence[str] | None = "l7g8h9i0j1k2"
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Change mental_models.id from VARCHAR(64) to TEXT."""
     schema = _get_schema_prefix()
 
@@ -33,9 +35,17 @@ def upgrade() -> None:
     op.execute(f"ALTER TABLE {schema}mental_models ALTER COLUMN id TYPE TEXT")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Revert mental_models.id from TEXT to VARCHAR(64)."""
     schema = _get_schema_prefix()
 
     # Note: This may fail if any id values exceed 64 characters
     op.execute(f"ALTER TABLE {schema}mental_models ALTER COLUMN id TYPE VARCHAR(64)")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/n9i0j1k2l3m4_learnings_and_pinned_reflections.py

@@ -16,6 +16,8 @@ from collections.abc import Sequence
 from alembic import context, op
 from sqlalchemy import text
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "n9i0j1k2l3m4"
 down_revision: str | Sequence[str] | None = "m8h9i0j1k2l3"
@@ -119,7 +121,7 @@ def _detect_text_search_extension() -> str:
         )
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Create learnings and pinned_reflections tables."""
     schema = _get_schema_prefix()
 
@@ -304,7 +306,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Drop learnings and pinned_reflections tables."""
     schema = _get_schema_prefix()
 
@@ -315,3 +317,11 @@ def downgrade() -> None:
     # Remove columns from banks
     op.execute(f"ALTER TABLE {schema}banks DROP COLUMN IF EXISTS last_consolidated_at")
     op.execute(f"ALTER TABLE {schema}banks DROP COLUMN IF EXISTS mission_changed_at")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/o0j1k2l3m4n5_migrate_mental_models_data.py

@@ -16,6 +16,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "o0j1k2l3m4n5"
 down_revision: str | Sequence[str] | None = "n9i0j1k2l3m4"
@@ -29,7 +31,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Migrate data and clean up old mental models."""
     schema = _get_schema_prefix()
 
@@ -80,15 +82,17 @@ def upgrade() -> None:
     # 4. Drop the mental_model_versions table (no longer used)
     op.execute(f"DROP TABLE IF EXISTS {schema}mental_model_versions CASCADE")
 
-    # 5. Drop old constraints and add new one that only allows 'directive'
+    # 5. Drop old constraints and add new one that allows current subtypes.
+    # 'pinned' is still used by the code for user-created mental models;
+    # 'directive' is used for system directives.
     op.execute(f"ALTER TABLE {schema}mental_models DROP CONSTRAINT IF EXISTS ck_mental_models_subtype")
     op.execute(f"""
         ALTER TABLE {schema}mental_models
-        ADD CONSTRAINT ck_mental_models_subtype CHECK (subtype = 'directive')
+        ADD CONSTRAINT ck_mental_models_subtype CHECK (subtype IN ('directive', 'pinned'))
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Reverse the migration (data migration is one-way, so this just removes constraints)."""
     schema = _get_schema_prefix()
 
@@ -111,3 +115,11 @@ def downgrade() -> None:
     )
 
     # Note: Data migration cannot be reversed - pinned_reflections and learnings data remains
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/o1a2b3c4d5e6_oracle_baseline.py

@@ -0,0 +1,445 @@
+"""oracle_baseline
+
+Brings a fresh Oracle 23ai database up to the current schema in a single step.
+PostgreSQL is a no-op here — the prior 59 revisions already build the PG schema
+incrementally; this revision just closes the loop so both dialects share a
+single head from this point on.
+
+After this migration ships, *every* new revision must fill both the ``_pg_*``
+and ``_oracle_*`` slots (or explicitly leave one ``None``); a CI check enforces
+that.
+
+Tables mirror the PostgreSQL schema but use Oracle-native types:
+  - UUID                       -> RAW(16) DEFAULT SYS_GUID()
+  - TEXT / large VARCHAR        -> CLOB
+  - JSONB                       -> CLOB with IS JSON CHECK
+  - BOOLEAN                     -> NUMBER(1)
+  - FLOAT                       -> BINARY_DOUBLE
+  - VARCHAR[]                   -> CLOB (JSON array stored as string)
+  - BYTEA                       -> BLOB
+  - vector(384)                 -> VECTOR(384, FLOAT32) (Oracle 23ai native)
+
+Revision ID: o1a2b3c4d5e6
+Revises: k6l7m8n9o0p1
+Create Date: 2026-04-29
+"""
+
+from collections.abc import Sequence
+
+from alembic import op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "o1a2b3c4d5e6"
+down_revision: str | Sequence[str] | None = "k6l7m8n9o0p1"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+# ---------------------------------------------------------------------------
+# Tables — created in dependency order
+# ---------------------------------------------------------------------------
+
+_TABLES: tuple[str, ...] = (
+    """
+    CREATE TABLE IF NOT EXISTS banks (
+        bank_id           VARCHAR2(256)  NOT NULL,
+        internal_id       RAW(16)        DEFAULT SYS_GUID() NOT NULL,
+        name              VARCHAR2(512),
+        disposition       CLOB           DEFAULT '{"skepticism":3,"literalism":3,"empathy":3}' NOT NULL
+                                         CONSTRAINT banks_disposition_json CHECK (disposition IS JSON),
+        mission           CLOB,
+        personality       CLOB           DEFAULT '{}' NOT NULL
+                                         CONSTRAINT banks_personality_json CHECK (personality IS JSON),
+        config            CLOB           DEFAULT '{}' NOT NULL
+                                         CONSTRAINT banks_config_json CHECK (config IS JSON),
+        created_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        updated_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        CONSTRAINT pk_banks PRIMARY KEY (bank_id),
+        CONSTRAINT banks_internal_id_unique UNIQUE (internal_id)
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS documents (
+        id                VARCHAR2(512)  NOT NULL,
+        bank_id           VARCHAR2(256)  NOT NULL,
+        original_text     CLOB,
+        content_hash      VARCHAR2(128),
+        metadata          CLOB           DEFAULT '{}' NOT NULL
+                                         CONSTRAINT docs_metadata_json CHECK (metadata IS JSON),
+        retain_params     CLOB           CONSTRAINT docs_retain_params_json CHECK (retain_params IS JSON OR retain_params IS NULL),
+        file_storage_key  VARCHAR2(512),
+        file_original_name VARCHAR2(512),
+        file_content_type VARCHAR2(256),
+        tags              CLOB           DEFAULT '[]' NOT NULL,
+        created_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        updated_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        CONSTRAINT pk_documents PRIMARY KEY (id, bank_id),
+        CONSTRAINT fk_documents_bank FOREIGN KEY (bank_id) REFERENCES banks(bank_id) ON DELETE CASCADE
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS chunks (
+        chunk_id          VARCHAR2(512)  NOT NULL,
+        document_id       VARCHAR2(512)  NOT NULL,
+        bank_id           VARCHAR2(256)  NOT NULL,
+        chunk_index       NUMBER(10)     NOT NULL,
+        chunk_text        CLOB           NOT NULL,
+        content_hash      VARCHAR2(128),
+        created_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        CONSTRAINT pk_chunks PRIMARY KEY (chunk_id),
+        CONSTRAINT fk_chunks_document FOREIGN KEY (document_id, bank_id)
+            REFERENCES documents(id, bank_id) ON DELETE CASCADE
+    )
+    """,
+    # memory_units uses automatic list partitioning on bank_id at create time —
+    # no post-create ALTER required (we used to do that for legacy installs).
+    """
+    CREATE TABLE IF NOT EXISTS memory_units (
+        id                RAW(16)        DEFAULT SYS_GUID() NOT NULL,
+        bank_id           VARCHAR2(256)  NOT NULL,
+        document_id       VARCHAR2(512),
+        chunk_id          VARCHAR2(512),
+        text              CLOB           NOT NULL,
+        embedding         VECTOR(384, FLOAT32),
+        context           CLOB,
+        event_date        TIMESTAMP WITH TIME ZONE NOT NULL,
+        occurred_start    TIMESTAMP WITH TIME ZONE,
+        occurred_end      TIMESTAMP WITH TIME ZONE,
+        mentioned_at      TIMESTAMP WITH TIME ZONE,
+        fact_type         VARCHAR2(64)   DEFAULT 'world' NOT NULL,
+        confidence_score  BINARY_DOUBLE,
+        access_count      NUMBER(10)     DEFAULT 0 NOT NULL,
+        consolidated_at   TIMESTAMP WITH TIME ZONE,
+        observation_scopes CLOB          CONSTRAINT mu_obs_scopes_json CHECK (observation_scopes IS JSON OR observation_scopes IS NULL),
+        tags              CLOB           DEFAULT '[]' NOT NULL,
+        metadata          CLOB           DEFAULT '{}' NOT NULL
+                                         CONSTRAINT mu_metadata_json CHECK (metadata IS JSON),
+        proof_count       NUMBER(10)     DEFAULT 1,
+        source_memory_ids CLOB,
+        history           CLOB           DEFAULT '[]'
+                                         CONSTRAINT mu_history_json CHECK (history IS JSON OR history IS NULL),
+        text_signals      CLOB,
+        consolidation_failed_at TIMESTAMP WITH TIME ZONE,
+        search_vector     CLOB,
+        created_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        updated_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        CONSTRAINT pk_memory_units PRIMARY KEY (id),
+        CONSTRAINT fk_mu_document FOREIGN KEY (document_id, bank_id)
+            REFERENCES documents(id, bank_id) ON DELETE CASCADE,
+        CONSTRAINT fk_mu_chunk FOREIGN KEY (chunk_id)
+            REFERENCES chunks(chunk_id) ON DELETE SET NULL,
+        CONSTRAINT chk_mu_fact_type CHECK (fact_type IN ('world', 'experience', 'observation')),
+        CONSTRAINT chk_mu_confidence CHECK (
+            confidence_score IS NULL
+            OR (confidence_score >= 0.0 AND confidence_score <= 1.0)
+        )
+    )
+    PARTITION BY LIST (bank_id) AUTOMATIC
+    (PARTITION p_default VALUES ('__default__'))
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS entities (
+        id                RAW(16)        DEFAULT SYS_GUID() NOT NULL,
+        bank_id           VARCHAR2(256)  NOT NULL,
+        canonical_name    VARCHAR2(512)  NOT NULL,
+        metadata          CLOB           DEFAULT '{}' NOT NULL
+                                         CONSTRAINT ent_metadata_json CHECK (metadata IS JSON),
+        first_seen        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        last_seen         TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        mention_count     NUMBER(10)     DEFAULT 1 NOT NULL,
+        CONSTRAINT pk_entities PRIMARY KEY (id)
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS unit_entities (
+        unit_id           RAW(16)        NOT NULL,
+        entity_id         RAW(16)        NOT NULL,
+        CONSTRAINT pk_unit_entities PRIMARY KEY (unit_id, entity_id),
+        CONSTRAINT fk_ue_unit FOREIGN KEY (unit_id) REFERENCES memory_units(id) ON DELETE CASCADE,
+        CONSTRAINT fk_ue_entity FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS entity_cooccurrences (
+        entity_id_1       RAW(16)        NOT NULL,
+        entity_id_2       RAW(16)        NOT NULL,
+        cooccurrence_count NUMBER(10)    DEFAULT 1 NOT NULL,
+        last_cooccurred   TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        CONSTRAINT pk_entity_cooccurrences PRIMARY KEY (entity_id_1, entity_id_2),
+        CONSTRAINT fk_ec_entity1 FOREIGN KEY (entity_id_1) REFERENCES entities(id) ON DELETE CASCADE,
+        CONSTRAINT fk_ec_entity2 FOREIGN KEY (entity_id_2) REFERENCES entities(id) ON DELETE CASCADE
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS memory_links (
+        from_unit_id      RAW(16)        NOT NULL,
+        to_unit_id        RAW(16)        NOT NULL,
+        link_type         VARCHAR2(64)   NOT NULL,
+        entity_id         RAW(16),
+        bank_id           VARCHAR2(256),
+        weight            BINARY_DOUBLE  DEFAULT 1.0 NOT NULL,
+        source_memory_ids CLOB,
+        created_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        CONSTRAINT fk_ml_from FOREIGN KEY (from_unit_id) REFERENCES memory_units(id) ON DELETE CASCADE,
+        CONSTRAINT fk_ml_to FOREIGN KEY (to_unit_id) REFERENCES memory_units(id) ON DELETE CASCADE,
+        CONSTRAINT fk_ml_entity FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE,
+        CONSTRAINT chk_ml_link_type CHECK (
+            link_type IN ('temporal', 'semantic', 'entity', 'causes', 'caused_by', 'enables', 'prevents')
+        ),
+        CONSTRAINT chk_ml_weight CHECK (weight >= 0.0 AND weight <= 1.0)
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS mental_models (
+        id                VARCHAR2(256)  NOT NULL,
+        bank_id           VARCHAR2(256)  NOT NULL,
+        subtype           VARCHAR2(32)   NOT NULL,
+        name              VARCHAR2(256)  NOT NULL,
+        description       CLOB           NOT NULL,
+        source_query      CLOB,
+        content           CLOB,
+        embedding         VECTOR(384, FLOAT32),
+        entity_id         RAW(16),
+        observations      CLOB           DEFAULT '{"observations":[]}' NOT NULL
+                                         CONSTRAINT mm_obs_json CHECK (observations IS JSON),
+        links             CLOB,
+        tags              CLOB           DEFAULT '[]' NOT NULL,
+        max_tokens        NUMBER(10)     DEFAULT 2048 NOT NULL,
+        "trigger"         CLOB           DEFAULT '{"refresh_after_consolidation":false}' NOT NULL
+                                         CONSTRAINT mm_trigger_json CHECK ("trigger" IS JSON),
+        structured_content CLOB          CONSTRAINT mm_sc_json CHECK (structured_content IS JSON OR structured_content IS NULL),
+        last_refreshed_source_query CLOB,
+        reflect_response  CLOB           CONSTRAINT mm_reflect_resp_json CHECK (reflect_response IS JSON OR reflect_response IS NULL),
+        history           CLOB           DEFAULT '[]' NOT NULL
+                                         CONSTRAINT mm_history_json CHECK (history IS JSON),
+        last_refreshed_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        last_updated      TIMESTAMP WITH TIME ZONE,
+        created_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        CONSTRAINT pk_mental_models PRIMARY KEY (id, bank_id),
+        CONSTRAINT fk_mm_bank FOREIGN KEY (bank_id) REFERENCES banks(bank_id) ON DELETE CASCADE,
+        CONSTRAINT fk_mm_entity FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE SET NULL,
+        CONSTRAINT chk_mm_subtype CHECK (subtype IN ('directive', 'pinned'))
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS directives (
+        id                RAW(16)        DEFAULT SYS_GUID() NOT NULL,
+        bank_id           VARCHAR2(256)  NOT NULL,
+        name              VARCHAR2(256)  NOT NULL,
+        content           CLOB           NOT NULL,
+        priority          NUMBER(10)     DEFAULT 0 NOT NULL,
+        is_active         NUMBER(1)      DEFAULT 1 NOT NULL,
+        tags              CLOB           DEFAULT '[]' NOT NULL,
+        created_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        updated_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        CONSTRAINT pk_directives PRIMARY KEY (id),
+        CONSTRAINT fk_dir_bank FOREIGN KEY (bank_id) REFERENCES banks(bank_id) ON DELETE CASCADE
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS async_operations (
+        operation_id      RAW(16)        DEFAULT SYS_GUID() NOT NULL,
+        bank_id           VARCHAR2(256)  NOT NULL,
+        operation_type    VARCHAR2(128)  NOT NULL,
+        status            VARCHAR2(32)   DEFAULT 'pending' NOT NULL,
+        worker_id         VARCHAR2(256),
+        claimed_at        TIMESTAMP WITH TIME ZONE,
+        retry_count       NUMBER(10)     DEFAULT 0 NOT NULL,
+        next_retry_at     TIMESTAMP WITH TIME ZONE,
+        task_payload      CLOB           CONSTRAINT ao_payload_json CHECK (task_payload IS JSON OR task_payload IS NULL),
+        result_metadata   CLOB           DEFAULT '{}' NOT NULL
+                                         CONSTRAINT ao_result_json CHECK (result_metadata IS JSON),
+        error_message     CLOB,
+        created_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        updated_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        completed_at      TIMESTAMP WITH TIME ZONE,
+        CONSTRAINT pk_async_operations PRIMARY KEY (operation_id),
+        CONSTRAINT fk_ao_bank FOREIGN KEY (bank_id) REFERENCES banks(bank_id) ON DELETE CASCADE,
+        CONSTRAINT chk_ao_status CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'cancelled'))
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS webhooks (
+        id                RAW(16)        DEFAULT SYS_GUID() NOT NULL,
+        bank_id           VARCHAR2(256)  NOT NULL,
+        url               VARCHAR2(2048) NOT NULL,
+        secret            VARCHAR2(512),
+        event_types       CLOB           DEFAULT '[]' NOT NULL,
+        http_config       CLOB           DEFAULT '{}' NOT NULL
+                                         CONSTRAINT wh_http_config_json CHECK (http_config IS JSON),
+        enabled           NUMBER(1)      DEFAULT 1 NOT NULL,
+        created_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        updated_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        CONSTRAINT pk_webhooks PRIMARY KEY (id),
+        CONSTRAINT fk_wh_bank FOREIGN KEY (bank_id) REFERENCES banks(bank_id) ON DELETE CASCADE
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS file_storage (
+        storage_key       VARCHAR2(512)  NOT NULL,
+        data              BLOB           NOT NULL,
+        CONSTRAINT pk_file_storage PRIMARY KEY (storage_key)
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS audit_log (
+        id                RAW(16)        DEFAULT SYS_GUID() NOT NULL,
+        action            VARCHAR2(128)  NOT NULL,
+        transport         VARCHAR2(64)   NOT NULL,
+        bank_id           VARCHAR2(256),
+        started_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
+        ended_at          TIMESTAMP WITH TIME ZONE,
+        request           CLOB           CONSTRAINT al_request_json CHECK (request IS JSON OR request IS NULL),
+        response          CLOB           CONSTRAINT al_response_json CHECK (response IS JSON OR response IS NULL),
+        metadata          CLOB           DEFAULT '{}' NOT NULL
+                                         CONSTRAINT al_metadata_json CHECK (metadata IS JSON),
+        CONSTRAINT pk_audit_log PRIMARY KEY (id)
+    )
+    """,
+    """
+    CREATE TABLE IF NOT EXISTS observation_sources (
+        observation_id    RAW(16)        NOT NULL,
+        source_id         RAW(16)        NOT NULL,
+        CONSTRAINT pk_observation_sources PRIMARY KEY (observation_id, source_id),
+        CONSTRAINT fk_obs_src_observation FOREIGN KEY (observation_id)
+            REFERENCES memory_units(id) ON DELETE CASCADE
+    )
+    """,
+)
+
+
+# ---------------------------------------------------------------------------
+# B-tree indexes
+# ---------------------------------------------------------------------------
+
+_INDEXES: tuple[str, ...] = (
+    # documents
+    "CREATE INDEX idx_docs_bank_id ON documents(bank_id)",
+    "CREATE INDEX idx_docs_content_hash ON documents(content_hash)",
+    # chunks
+    "CREATE INDEX idx_chunks_document_id ON chunks(document_id)",
+    "CREATE INDEX idx_chunks_bank_id ON chunks(bank_id)",
+    # memory_units
+    "CREATE INDEX idx_mu_bank_id ON memory_units(bank_id)",
+    "CREATE INDEX idx_mu_document_id ON memory_units(document_id)",
+    "CREATE INDEX idx_mu_chunk_id ON memory_units(chunk_id)",
+    "CREATE INDEX idx_mu_event_date ON memory_units(event_date DESC)",
+    "CREATE INDEX idx_mu_bank_date ON memory_units(bank_id, event_date DESC)",
+    "CREATE INDEX idx_mu_access_count ON memory_units(access_count DESC)",
+    "CREATE INDEX idx_mu_fact_type ON memory_units(fact_type)",
+    "CREATE INDEX idx_mu_bank_fact_type ON memory_units(bank_id, fact_type)",
+    "CREATE INDEX idx_mu_bank_type_date ON memory_units(bank_id, fact_type, event_date DESC)",
+    # entities
+    "CREATE INDEX idx_ent_bank_id ON entities(bank_id)",
+    "CREATE INDEX idx_ent_canonical_name ON entities(canonical_name)",
+    "CREATE INDEX idx_ent_bank_name ON entities(bank_id, canonical_name)",
+    "CREATE UNIQUE INDEX idx_ent_bank_lower_name ON entities(bank_id, LOWER(canonical_name))",
+    # unit_entities
+    "CREATE INDEX idx_ue_unit ON unit_entities(unit_id)",
+    "CREATE INDEX idx_ue_entity ON unit_entities(entity_id)",
+    # entity_cooccurrences
+    "CREATE INDEX idx_ec_entity1 ON entity_cooccurrences(entity_id_1)",
+    "CREATE INDEX idx_ec_entity2 ON entity_cooccurrences(entity_id_2)",
+    "CREATE INDEX idx_ec_count ON entity_cooccurrences(cooccurrence_count DESC)",
+    # memory_links — function-based unique index uses NVL with the nil UUID raw
+    # to handle nullable entity_id (matches PG idx_memory_links_unique).
+    "CREATE UNIQUE INDEX idx_memory_links_unique ON memory_links("
+    "from_unit_id, to_unit_id, link_type, "
+    "NVL(entity_id, HEXTORAW('00000000000000000000000000000000')))",
+    "CREATE INDEX idx_ml_from_unit ON memory_links(from_unit_id)",
+    "CREATE INDEX idx_ml_to_unit ON memory_links(to_unit_id)",
+    "CREATE INDEX idx_ml_entity ON memory_links(entity_id)",
+    "CREATE INDEX idx_ml_link_type ON memory_links(link_type)",
+    "CREATE INDEX idx_ml_bank_id ON memory_links(bank_id)",
+    # directives
+    "CREATE INDEX idx_dir_bank_id ON directives(bank_id)",
+    "CREATE INDEX idx_dir_bank_active ON directives(bank_id, is_active)",
+    # mental_models
+    "CREATE INDEX idx_mm_bank_id ON mental_models(bank_id)",
+    "CREATE INDEX idx_mm_subtype ON mental_models(bank_id, subtype)",
+    "CREATE INDEX idx_mm_entity_id ON mental_models(entity_id)",
+    # async_operations
+    "CREATE INDEX idx_ao_bank_id ON async_operations(bank_id)",
+    "CREATE INDEX idx_ao_status ON async_operations(status)",
+    "CREATE INDEX idx_ao_bank_status ON async_operations(bank_id, status)",
+    "CREATE INDEX idx_ao_status_retry ON async_operations(status, next_retry_at)",
+    # webhooks
+    "CREATE INDEX idx_wh_bank_id ON webhooks(bank_id)",
+    # audit_log
+    "CREATE INDEX idx_al_action_started ON audit_log(action, started_at DESC)",
+    "CREATE INDEX idx_al_bank_started ON audit_log(bank_id, started_at DESC)",
+    "CREATE INDEX idx_al_started ON audit_log(started_at DESC)",
+    # observation_sources
+    "CREATE INDEX idx_obs_sources_source_id ON observation_sources(source_id, observation_id)",
+)
+
+_VECTOR_INDEX = (
+    "CREATE VECTOR INDEX idx_mu_embedding_hnsw ON memory_units(embedding) "
+    "ORGANIZATION NEIGHBOR PARTITIONS "
+    "DISTANCE COSINE "
+    "WITH TARGET ACCURACY 95"
+)
+
+# Oracle Text (CTXSYS.CONTEXT) — ``SYNC (ON COMMIT)`` makes it auto-update
+# without a maintenance job. Doubled single quotes for the embedded literal.
+_TEXT_INDEX = (
+    "BEGIN "
+    "EXECUTE IMMEDIATE '"
+    "CREATE INDEX idx_mu_content_text ON memory_units(text) "
+    "INDEXTYPE IS CTXSYS.CONTEXT "
+    "PARAMETERS (''SYNC (ON COMMIT)'')"
+    "'; "
+    "EXCEPTION WHEN OTHERS THEN "
+    "IF SQLCODE = -955 THEN NULL; ELSE RAISE; END IF; "
+    "END;"
+)
+
+
+def _execute_ignoring_955(sql: str) -> None:
+    """Run a CREATE statement and swallow ORA-00955 (object already exists).
+
+    Wraps the statement in PL/SQL so the exception handler runs server-side —
+    no round-trip cost for the common case.
+    """
+    block = (
+        "BEGIN "
+        "EXECUTE IMMEDIATE :stmt; "
+        "EXCEPTION WHEN OTHERS THEN "
+        "IF SQLCODE = -955 THEN NULL; ELSE RAISE; END IF; "
+        "END;"
+    )
+    op.get_bind().exec_driver_sql(block, {"stmt": sql.strip()})
+
+
+def _oracle_upgrade() -> None:
+    bind = op.get_bind()
+    # Tolerate concurrent DDL instead of failing immediately (ORA-00054).
+    bind.exec_driver_sql("ALTER SESSION SET DDL_LOCK_TIMEOUT = 30")
+
+    for ddl in _TABLES:
+        _execute_ignoring_955(ddl)
+
+    for idx in _INDEXES:
+        _execute_ignoring_955(idx)
+
+    # Hindsight on Oracle requires 23ai with VECTOR support (ASSM tablespace)
+    # and the CTXSYS package for full-text. Both index creations must succeed
+    # — the migration fails hard if either feature is unavailable, by design.
+    # We only swallow ORA-00955 (object already exists) so reruns are safe.
+    _execute_ignoring_955(_VECTOR_INDEX)
+    bind.exec_driver_sql(_TEXT_INDEX)
+
+
+def _oracle_downgrade() -> None:
+    # Baseline downgrades aren't supported — dropping every table here would
+    # destroy customer data. Use point-in-time recovery instead.
+    raise NotImplementedError("Cannot downgrade past the Oracle baseline.")
+
+
+def upgrade() -> None:
+    run_for_dialect(oracle=_oracle_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(oracle=_oracle_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/p1k2l3m4n5o6_new_knowledge_architecture.py

@@ -21,6 +21,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "p1k2l3m4n5o6"
 down_revision: str | Sequence[str] | None = "o0j1k2l3m4n5"
@@ -34,7 +36,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Implement new knowledge architecture."""
     schema = _get_schema_prefix()
 
@@ -126,7 +128,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Reverse the migration."""
     schema = _get_schema_prefix()
 
@@ -192,3 +194,11 @@ def downgrade() -> None:
     """)
 
     # Note: mental_models table recreation is complex and would need separate handling
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/q2l3m4n5o6p7_fix_mental_model_fact_type.py

@@ -12,6 +12,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "q2l3m4n5o6p7"
 down_revision: str | Sequence[str] | None = "p1k2l3m4n5o6"
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add 'mental_model' to the fact_type check constraint."""
     schema = _get_schema_prefix()
 
@@ -38,7 +40,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove 'mental_model' from the fact_type check constraint."""
     schema = _get_schema_prefix()
 
@@ -48,3 +50,11 @@ def downgrade() -> None:
         ADD CONSTRAINT memory_units_fact_type_check
         CHECK (fact_type IN ('world', 'experience', 'opinion', 'observation'))
     """)
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/r3m4n5o6p7q8_add_reflect_response_to_reflections.py

@@ -14,6 +14,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "r3m4n5o6p7q8"
 down_revision: str | Sequence[str] | None = "q2l3m4n5o6p7"
 branch_labels: str | Sequence[str] | None = None
@@ -26,7 +28,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add reflect_response JSONB column to reflections."""
     schema = _get_schema_prefix()
 
@@ -37,7 +39,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove reflect_response column from reflections."""
     schema = _get_schema_prefix()
 
@@ -45,3 +47,11 @@ def downgrade() -> None:
         ALTER TABLE {schema}reflections
         DROP COLUMN IF EXISTS reflect_response
     """)
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/rename_personality_to_disposition.py

@@ -12,6 +12,8 @@ import sqlalchemy as sa
 from alembic import context, op
 from sqlalchemy.dialects import postgresql
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 # revision identifiers, used by Alembic.
 revision: str = "rename_personality"
 down_revision: str | Sequence[str] | None = "d9f6a3b4c5e2"
@@ -25,7 +27,7 @@ def _get_target_schema() -> str:
     return schema if schema else "public"
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Rename personality column to disposition in banks table (if it exists)."""
     conn = op.get_bind()
     target_schema = _get_target_schema()
@@ -69,7 +71,7 @@ def upgrade() -> None:
     # else: disposition already exists, nothing to do
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Revert disposition column back to personality."""
     conn = op.get_bind()
     target_schema = _get_target_schema()
@@ -83,3 +85,11 @@ def downgrade() -> None:
     )
     if result.fetchone():
         op.alter_column("banks", "disposition", new_column_name="personality")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/s4n5o6p7q8r9_add_consolidated_at_to_memory_units.py

@@ -13,6 +13,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "s4n5o6p7q8r9"
 down_revision: str | Sequence[str] | None = "r3m4n5o6p7q8"
 branch_labels: str | Sequence[str] | None = None
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
 
     # Add consolidated_at column to memory_units
@@ -46,8 +48,16 @@ def upgrade() -> None:
     )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
 
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_memory_units_unconsolidated")
     op.execute(f"ALTER TABLE {schema}memory_units DROP COLUMN IF EXISTS consolidated_at")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/t5o6p7q8r9s0_rename_mental_models_to_observations.py

@@ -17,6 +17,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "t5o6p7q8r9s0"
 down_revision: str | Sequence[str] | None = "s4n5o6p7q8r9"
 branch_labels: str | Sequence[str] | None = None
@@ -29,7 +31,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Rename mental_model -> observation and reflections -> mental_models."""
     schema = _get_schema_prefix()
 
@@ -86,7 +88,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Reverse: observation -> mental_model and mental_models -> reflections."""
     schema = _get_schema_prefix()
 
@@ -132,3 +134,11 @@ def downgrade() -> None:
         ON {schema}memory_units(bank_id, fact_type)
         WHERE fact_type = 'mental_model'
     """)
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/u6p7q8r9s0t1_mental_models_text_id.py

@@ -12,6 +12,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "u6p7q8r9s0t1"
 down_revision: str | Sequence[str] | None = "t5o6p7q8r9s0"
 branch_labels: str | Sequence[str] | None = None
@@ -24,7 +26,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Change mental_models.id from UUID to TEXT."""
     schema = _get_schema_prefix()
 
@@ -33,9 +35,17 @@ def upgrade() -> None:
     op.execute(f"ALTER TABLE {schema}mental_models ALTER COLUMN id TYPE TEXT USING id::TEXT")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Revert mental_models.id from TEXT to UUID."""
     schema = _get_schema_prefix()
 
     # Note: This will fail if any id values are not valid UUIDs
     op.execute(f"ALTER TABLE {schema}mental_models ALTER COLUMN id TYPE UUID USING id::UUID")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/v7q8r9s0t1u2_add_max_tokens_to_mental_models.py

@@ -13,6 +13,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "v7q8r9s0t1u2"
 down_revision: str | Sequence[str] | None = "u6p7q8r9s0t1"
 branch_labels: str | Sequence[str] | None = None
@@ -25,7 +27,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add max_tokens and trigger columns to mental_models."""
     schema = _get_schema_prefix()
 
@@ -42,9 +44,17 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove max_tokens and trigger columns from mental_models."""
     schema = _get_schema_prefix()
 
     op.execute(f"ALTER TABLE {schema}mental_models DROP COLUMN IF EXISTS max_tokens")
     op.execute(f"ALTER TABLE {schema}mental_models DROP COLUMN IF EXISTS trigger")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/w8r9s0t1u2v3_fix_mental_models_pk_isolation.py

@@ -15,6 +15,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "w8r9s0t1u2v3"
 down_revision: str | Sequence[str] | None = "v7q8r9s0t1u2"
 branch_labels: str | Sequence[str] | None = None
@@ -27,7 +29,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Change mental_models primary key from (id) to (bank_id, id) for proper bank isolation."""
     schema = _get_schema_prefix()
 
@@ -45,7 +47,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Revert mental_models primary key from (bank_id, id) to (id)."""
     schema = _get_schema_prefix()
 
@@ -58,3 +60,11 @@ def downgrade() -> None:
         ALTER TABLE {schema}mental_models
         ADD CONSTRAINT mental_models_pkey PRIMARY KEY (id)
     """)
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/x9s0t1u2v3w4_add_bank_config_column.py

@@ -20,6 +20,8 @@ import sqlalchemy as sa
 from alembic import context, op
 from sqlalchemy.dialects.postgresql import JSONB
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "x9s0t1u2v3w4"
 down_revision: str | Sequence[str] | None = "w8r9s0t1u2v3"
 branch_labels: str | Sequence[str] | None = None
@@ -32,7 +34,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add config JSONB column to banks table with GIN index."""
     schema = _get_schema_prefix()
 
@@ -50,7 +52,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove config column and index from banks table."""
     schema = _get_schema_prefix()
 
@@ -62,3 +64,11 @@ def downgrade() -> None:
         ALTER TABLE {schema}banks
         DROP COLUMN IF EXISTS config
     """)
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/y0t1u2v3w4x5_add_result_metadata_gin_index.py

@@ -17,6 +17,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "y0t1u2v3w4x5"
 down_revision: str | Sequence[str] | None = "x9s0t1u2v3w4"
 branch_labels: str | Sequence[str] | None = None
@@ -29,7 +31,7 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Add GIN index on result_metadata for efficient parent_operation_id queries."""
     schema = _get_schema_prefix()
 
@@ -41,9 +43,17 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Remove GIN index on result_metadata."""
     schema = _get_schema_prefix()
 
     # Drop index
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_async_operations_result_metadata")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/alembic/versions/z1u2v3w4x5y6_add_observation_tags_to_memory_units.py

@@ -13,6 +13,8 @@ from collections.abc import Sequence
 
 from alembic import context, op
 
+from hindsight_api.alembic._dialect import run_for_dialect
+
 revision: str = "z1u2v3w4x5y6"
 down_revision: str | Sequence[str] | None = "a1b2c3d4e5f6"
 branch_labels: str | Sequence[str] | None = None
@@ -25,11 +27,19 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}memory_units ADD COLUMN IF NOT EXISTS observation_scopes JSONB")
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
     op.execute(f"ALTER TABLE {schema}memory_units DROP COLUMN IF EXISTS observation_scopes")
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
+def downgrade() -> None:
+    run_for_dialect(pg=_pg_downgrade)

hindsight-api-slim/hindsight_api/api/mcp.py

@@ -8,6 +8,7 @@ from contextvars import ContextVar
 from fastmcp import FastMCP
 
 from hindsight_api import MemoryEngine
+from hindsight_api import __version__ as HINDSIGHT_VERSION
 from hindsight_api.config import _get_raw_config
 from hindsight_api.engine.memory_engine import _current_schema
 from hindsight_api.extensions import MCPExtension, load_extension
@@ -89,7 +90,7 @@ def create_mcp_server(memory: MemoryEngine, multi_bank: bool = True) -> FastMCP:
     Returns:
         Configured FastMCP server instance
     """
-    mcp = FastMCP("hindsight-mcp-server")
+    mcp = FastMCP("hindsight-mcp-server", version=HINDSIGHT_VERSION)
 
     global_config = _get_raw_config()
 
@@ -111,7 +112,6 @@ def create_mcp_server(memory: MemoryEngine, multi_bank: bool = True) -> FastMCP:
             "delete_directive",
             "list_memories",
             "get_memory",
-            "delete_memory",
             "list_documents",
             "get_document",
             "delete_document",

hindsight-api-slim/hindsight_api/config.py

@@ -10,10 +10,12 @@ import os
 import sys
 from dataclasses import dataclass, field, fields
 from datetime import datetime, timezone
-from typing import Any
+from typing import Any, Literal
 
 from dotenv import find_dotenv, load_dotenv
 
+from .utils import mask_network_location
+
 # Load .env file, searching current and parent directories (overrides existing env vars)
 load_dotenv(find_dotenv(usecwd=True), override=True)
 
@@ -117,6 +119,7 @@ def normalize_config_dict(config: dict[str, Any]) -> dict[str, Any]:
 
 
 # Environment variable names
+ENV_DATABASE_BACKEND = "HINDSIGHT_API_DATABASE_BACKEND"
 ENV_DATABASE_URL = "HINDSIGHT_API_DATABASE_URL"
 ENV_MIGRATION_DATABASE_URL = "HINDSIGHT_API_MIGRATION_DATABASE_URL"
 ENV_DATABASE_SCHEMA = "HINDSIGHT_API_DATABASE_SCHEMA"
@@ -132,11 +135,15 @@ ENV_LLM_TIMEOUT = "HINDSIGHT_API_LLM_TIMEOUT"
 ENV_LLM_GROQ_SERVICE_TIER = "HINDSIGHT_API_LLM_GROQ_SERVICE_TIER"
 ENV_LLM_OPENAI_SERVICE_TIER = "HINDSIGHT_API_LLM_OPENAI_SERVICE_TIER"
 ENV_LLM_EXTRA_BODY = "HINDSIGHT_API_LLM_EXTRA_BODY"
+ENV_LLM_DEFAULT_HEADERS = "HINDSIGHT_API_LLM_DEFAULT_HEADERS"
 
 # Defaults for service tiers
 DEFAULT_LLM_GROQ_SERVICE_TIER = "auto"  # "on_demand", "flex", or "auto"
 DEFAULT_LLM_OPENAI_SERVICE_TIER = None  # None (default) or "flex" (50% cheaper)
 DEFAULT_LLM_EXTRA_BODY = None  # None = no extra body params; JSON dict merged into OpenAI extra_body
+DEFAULT_LLM_DEFAULT_HEADERS = (
+    None  # None = no extra headers; JSON dict passed as default_headers to provider SDK clients
+)
 
 # Per-operation LLM configuration (optional, falls back to global LLM config)
 ENV_RETAIN_LLM_PROVIDER = "HINDSIGHT_API_RETAIN_LLM_PROVIDER"
@@ -177,11 +184,13 @@ ENV_EMBEDDINGS_TEI_URL = "HINDSIGHT_API_EMBEDDINGS_TEI_URL"
 ENV_EMBEDDINGS_OPENAI_API_KEY = "HINDSIGHT_API_EMBEDDINGS_OPENAI_API_KEY"
 ENV_EMBEDDINGS_OPENAI_MODEL = "HINDSIGHT_API_EMBEDDINGS_OPENAI_MODEL"
 ENV_EMBEDDINGS_OPENAI_BASE_URL = "HINDSIGHT_API_EMBEDDINGS_OPENAI_BASE_URL"
+ENV_EMBEDDINGS_OPENAI_BATCH_SIZE = "HINDSIGHT_API_EMBEDDINGS_OPENAI_BATCH_SIZE"
 
 # Gemini/Vertex AI embeddings configuration
 ENV_EMBEDDINGS_GEMINI_API_KEY = "HINDSIGHT_API_EMBEDDINGS_GEMINI_API_KEY"
 ENV_EMBEDDINGS_GEMINI_MODEL = "HINDSIGHT_API_EMBEDDINGS_GEMINI_MODEL"
 ENV_EMBEDDINGS_GEMINI_OUTPUT_DIMENSIONALITY = "HINDSIGHT_API_EMBEDDINGS_GEMINI_OUTPUT_DIMENSIONALITY"
+ENV_EMBEDDINGS_GEMINI_FORCE_IPV4 = "HINDSIGHT_API_EMBEDDINGS_GEMINI_FORCE_IPV4"
 ENV_EMBEDDINGS_VERTEXAI_PROJECT_ID = "HINDSIGHT_API_EMBEDDINGS_VERTEXAI_PROJECT_ID"
 ENV_EMBEDDINGS_VERTEXAI_REGION = "HINDSIGHT_API_EMBEDDINGS_VERTEXAI_REGION"
 ENV_EMBEDDINGS_VERTEXAI_SERVICE_ACCOUNT_KEY = "HINDSIGHT_API_EMBEDDINGS_VERTEXAI_SERVICE_ACCOUNT_KEY"
@@ -190,6 +199,7 @@ ENV_EMBEDDINGS_VERTEXAI_SERVICE_ACCOUNT_KEY = "HINDSIGHT_API_EMBEDDINGS_VERTEXAI
 ENV_EMBEDDINGS_COHERE_API_KEY = "HINDSIGHT_API_EMBEDDINGS_COHERE_API_KEY"
 ENV_EMBEDDINGS_COHERE_MODEL = "HINDSIGHT_API_EMBEDDINGS_COHERE_MODEL"
 ENV_EMBEDDINGS_COHERE_BASE_URL = "HINDSIGHT_API_EMBEDDINGS_COHERE_BASE_URL"
+ENV_EMBEDDINGS_COHERE_OUTPUT_DIMENSIONS = "HINDSIGHT_API_EMBEDDINGS_COHERE_OUTPUT_DIMENSIONS"
 ENV_RERANKER_COHERE_API_KEY = "HINDSIGHT_API_RERANKER_COHERE_API_KEY"
 ENV_RERANKER_COHERE_MODEL = "HINDSIGHT_API_RERANKER_COHERE_MODEL"
 ENV_RERANKER_COHERE_BASE_URL = "HINDSIGHT_API_RERANKER_COHERE_BASE_URL"
@@ -238,9 +248,11 @@ ENV_RERANKER_LOCAL_BATCH_SIZE = "HINDSIGHT_API_RERANKER_LOCAL_BATCH_SIZE"
 ENV_RERANKER_TEI_URL = "HINDSIGHT_API_RERANKER_TEI_URL"
 ENV_RERANKER_TEI_BATCH_SIZE = "HINDSIGHT_API_RERANKER_TEI_BATCH_SIZE"
 ENV_RERANKER_TEI_MAX_CONCURRENT = "HINDSIGHT_API_RERANKER_TEI_MAX_CONCURRENT"
+ENV_RERANKER_TEI_HTTP_TIMEOUT = "HINDSIGHT_API_RERANKER_TEI_HTTP_TIMEOUT"
 ENV_RERANKER_MAX_CANDIDATES = "HINDSIGHT_API_RERANKER_MAX_CANDIDATES"
 ENV_RERANKER_FLASHRANK_MODEL = "HINDSIGHT_API_RERANKER_FLASHRANK_MODEL"
 ENV_RERANKER_FLASHRANK_CACHE_DIR = "HINDSIGHT_API_RERANKER_FLASHRANK_CACHE_DIR"
+ENV_RERANKER_FLASHRANK_CPU_MEM_ARENA = "HINDSIGHT_API_RERANKER_FLASHRANK_CPU_MEM_ARENA"
 
 # ZeroEntropy configuration (reranker only)
 ENV_RERANKER_ZEROENTROPY_API_KEY = "HINDSIGHT_API_RERANKER_ZEROENTROPY_API_KEY"
@@ -265,6 +277,7 @@ ENV_PORT = "HINDSIGHT_API_PORT"
 ENV_BASE_PATH = "HINDSIGHT_API_BASE_PATH"
 ENV_LOG_LEVEL = "HINDSIGHT_API_LOG_LEVEL"
 ENV_LOG_FORMAT = "HINDSIGHT_API_LOG_FORMAT"
+ENV_LOG_JSON_FIELDS = "HINDSIGHT_API_LOG_JSON_FIELDS"
 ENV_WORKERS = "HINDSIGHT_API_WORKERS"
 ENV_MCP_ENABLED = "HINDSIGHT_API_MCP_ENABLED"
 ENV_MCP_ENABLED_TOOLS = "HINDSIGHT_API_MCP_ENABLED_TOOLS"
@@ -325,6 +338,7 @@ ENV_FILE_PARSER = "HINDSIGHT_API_FILE_PARSER"
 ENV_FILE_PARSER_ALLOWLIST = "HINDSIGHT_API_FILE_PARSER_ALLOWLIST"
 ENV_FILE_PARSER_IRIS_TOKEN = "HINDSIGHT_API_FILE_PARSER_IRIS_TOKEN"
 ENV_FILE_PARSER_IRIS_ORG_ID = "HINDSIGHT_API_FILE_PARSER_IRIS_ORG_ID"
+ENV_FILE_PARSER_LLAMA_PARSE_API_KEY = "HINDSIGHT_API_FILE_PARSER_LLAMA_PARSE_API_KEY"
 ENV_FILE_CONVERSION_MAX_BATCH_SIZE_MB = "HINDSIGHT_API_FILE_CONVERSION_MAX_BATCH_SIZE_MB"
 ENV_FILE_CONVERSION_MAX_BATCH_SIZE = "HINDSIGHT_API_FILE_CONVERSION_MAX_BATCH_SIZE"
 ENV_ENABLE_FILE_UPLOAD_API = "HINDSIGHT_API_ENABLE_FILE_UPLOAD_API"
@@ -333,12 +347,15 @@ ENV_FILE_DELETE_AFTER_RETAIN = "HINDSIGHT_API_FILE_DELETE_AFTER_RETAIN"
 # Observations settings (consolidated knowledge from facts)
 ENV_ENABLE_OBSERVATIONS = "HINDSIGHT_API_ENABLE_OBSERVATIONS"
 ENV_CONSOLIDATION_BATCH_SIZE = "HINDSIGHT_API_CONSOLIDATION_BATCH_SIZE"
+ENV_CONSOLIDATION_MAX_MEMORIES_PER_ROUND = "HINDSIGHT_API_CONSOLIDATION_MAX_MEMORIES_PER_ROUND"
 ENV_CONSOLIDATION_LLM_BATCH_SIZE = "HINDSIGHT_API_CONSOLIDATION_LLM_BATCH_SIZE"
 ENV_CONSOLIDATION_MAX_TOKENS = "HINDSIGHT_API_CONSOLIDATION_MAX_TOKENS"
 ENV_CONSOLIDATION_SOURCE_FACTS_MAX_TOKENS = "HINDSIGHT_API_CONSOLIDATION_SOURCE_FACTS_MAX_TOKENS"
 ENV_CONSOLIDATION_SOURCE_FACTS_MAX_TOKENS_PER_OBSERVATION = (
     "HINDSIGHT_API_CONSOLIDATION_SOURCE_FACTS_MAX_TOKENS_PER_OBSERVATION"
 )
+ENV_CONSOLIDATION_RECALL_BUDGET = "HINDSIGHT_API_CONSOLIDATION_RECALL_BUDGET"
+ENV_CONSOLIDATION_MAX_ATTEMPTS = "HINDSIGHT_API_CONSOLIDATION_MAX_ATTEMPTS"
 ENV_OBSERVATIONS_MISSION = "HINDSIGHT_API_OBSERVATIONS_MISSION"
 ENV_MAX_OBSERVATIONS_PER_SCOPE = "HINDSIGHT_API_MAX_OBSERVATIONS_PER_SCOPE"
 ENV_ENABLE_OBSERVATION_HISTORY = "HINDSIGHT_API_ENABLE_OBSERVATION_HISTORY"
@@ -370,6 +387,7 @@ ENV_DB_POOL_MIN_SIZE = "HINDSIGHT_API_DB_POOL_MIN_SIZE"
 ENV_DB_POOL_MAX_SIZE = "HINDSIGHT_API_DB_POOL_MAX_SIZE"
 ENV_DB_COMMAND_TIMEOUT = "HINDSIGHT_API_DB_COMMAND_TIMEOUT"
 ENV_DB_ACQUIRE_TIMEOUT = "HINDSIGHT_API_DB_ACQUIRE_TIMEOUT"
+ENV_DB_STATEMENT_TIMEOUT = "HINDSIGHT_API_DB_STATEMENT_TIMEOUT"
 
 # Worker configuration (distributed task processing)
 ENV_WORKER_ENABLED = "HINDSIGHT_API_WORKER_ENABLED"
@@ -378,7 +396,18 @@ ENV_WORKER_POLL_INTERVAL_MS = "HINDSIGHT_API_WORKER_POLL_INTERVAL_MS"
 ENV_WORKER_MAX_RETRIES = "HINDSIGHT_API_WORKER_MAX_RETRIES"
 ENV_WORKER_HTTP_PORT = "HINDSIGHT_API_WORKER_HTTP_PORT"
 ENV_WORKER_MAX_SLOTS = "HINDSIGHT_API_WORKER_MAX_SLOTS"
-ENV_WORKER_CONSOLIDATION_MAX_SLOTS = "HINDSIGHT_API_WORKER_CONSOLIDATION_MAX_SLOTS"
+
+# Per-operation-type slot reservations. Each entry maps an operation_type
+# (as stored in async_operations.operation_type) to its env var and default.
+# Adding a new operation type here is the ONLY change needed to make it
+# reservable via env var — config fields, from_env(), and the
+# worker_slot_reservations property all derive from this dict.
+WORKER_SLOT_RESERVATION_TYPES: dict[str, tuple[str, int]] = {
+    "consolidation": ("HINDSIGHT_API_WORKER_CONSOLIDATION_MAX_SLOTS", 2),
+    "retain": ("HINDSIGHT_API_WORKER_RETAIN_MAX_SLOTS", 0),
+    "file_convert_retain": ("HINDSIGHT_API_WORKER_FILE_CONVERT_RETAIN_MAX_SLOTS", 0),
+    "refresh_mental_model": ("HINDSIGHT_API_WORKER_REFRESH_MENTAL_MODEL_MAX_SLOTS", 0),
+}
 ENV_RETAIN_MAX_CONCURRENT = "HINDSIGHT_API_RETAIN_MAX_CONCURRENT"
 
 # Reflect agent settings
@@ -387,6 +416,20 @@ ENV_REFLECT_MAX_CONTEXT_TOKENS = "HINDSIGHT_API_REFLECT_MAX_CONTEXT_TOKENS"
 ENV_REFLECT_WALL_TIMEOUT = "HINDSIGHT_API_REFLECT_WALL_TIMEOUT"
 ENV_REFLECT_MISSION = "HINDSIGHT_API_REFLECT_MISSION"
 ENV_REFLECT_SOURCE_FACTS_MAX_TOKENS = "HINDSIGHT_API_REFLECT_SOURCE_FACTS_MAX_TOKENS"
+ENV_RECALL_INCLUDE_CHUNKS = "HINDSIGHT_API_RECALL_INCLUDE_CHUNKS"
+ENV_RECALL_MAX_TOKENS = "HINDSIGHT_API_RECALL_MAX_TOKENS"
+ENV_RECALL_CHUNKS_MAX_TOKENS = "HINDSIGHT_API_RECALL_CHUNKS_MAX_TOKENS"
+
+# Recall budget mapping (budget enum -> thinking_budget integer)
+ENV_RECALL_BUDGET_FUNCTION = "HINDSIGHT_API_RECALL_BUDGET_FUNCTION"
+ENV_RECALL_BUDGET_FIXED_LOW = "HINDSIGHT_API_RECALL_BUDGET_FIXED_LOW"
+ENV_RECALL_BUDGET_FIXED_MID = "HINDSIGHT_API_RECALL_BUDGET_FIXED_MID"
+ENV_RECALL_BUDGET_FIXED_HIGH = "HINDSIGHT_API_RECALL_BUDGET_FIXED_HIGH"
+ENV_RECALL_BUDGET_ADAPTIVE_LOW = "HINDSIGHT_API_RECALL_BUDGET_ADAPTIVE_LOW"
+ENV_RECALL_BUDGET_ADAPTIVE_MID = "HINDSIGHT_API_RECALL_BUDGET_ADAPTIVE_MID"
+ENV_RECALL_BUDGET_ADAPTIVE_HIGH = "HINDSIGHT_API_RECALL_BUDGET_ADAPTIVE_HIGH"
+ENV_RECALL_BUDGET_MIN = "HINDSIGHT_API_RECALL_BUDGET_MIN"
+ENV_RECALL_BUDGET_MAX = "HINDSIGHT_API_RECALL_BUDGET_MAX"
 
 # Audit log settings
 ENV_AUDIT_LOG_ENABLED = "HINDSIGHT_API_AUDIT_LOG_ENABLED"
@@ -399,6 +442,7 @@ ENV_DISPOSITION_LITERALISM = "HINDSIGHT_API_DISPOSITION_LITERALISM"
 ENV_DISPOSITION_EMPATHY = "HINDSIGHT_API_DISPOSITION_EMPATHY"
 
 # Default values
+DEFAULT_DATABASE_BACKEND = "postgresql"
 DEFAULT_DATABASE_URL = "pg0"
 DEFAULT_DATABASE_SCHEMA = "public"
 DEFAULT_LLM_PROVIDER = "openai"
@@ -410,11 +454,12 @@ PROVIDER_DEFAULT_MODELS = {
     "gemini": "gemini-2.5-flash",
     "groq": "openai/gpt-oss-120b",
     "minimax": "MiniMax-M2.7",
+    "deepseek": "deepseek-v4-flash",
     "ollama": "gemma3:12b",
     "llamacpp": "gemma-4-e2b-it",
     "lmstudio": "local-model",
     "vertexai": "google/gemini-2.5-flash-lite",
-    "openai-codex": "gpt-5.2-codex",
+    "openai-codex": "gpt-5.4-mini",
     "claude-code": "claude-sonnet-4-5-20250929",
     "mock": "mock-model",
     "none": "none",
@@ -432,7 +477,7 @@ DEFAULT_LLAMACPP_NO_GRAMMAR = False  # True = disable JSON grammar enforcement (
 DEFAULT_LLAMACPP_EXTRA_ARGS = None  # Space-separated extra CLI args for llama.cpp server
 
 DEFAULT_LLM_MAX_CONCURRENT = 32
-DEFAULT_LLM_MAX_RETRIES = 10  # Max retry attempts for LLM API calls
+DEFAULT_LLM_MAX_RETRIES = 3  # Max retry attempts for LLM API calls
 DEFAULT_LLM_INITIAL_BACKOFF = 1.0  # Initial backoff in seconds for retry exponential backoff
 DEFAULT_LLM_MAX_BACKOFF = 60.0  # Max backoff cap in seconds for retry exponential backoff
 DEFAULT_LLM_TIMEOUT = 120.0  # seconds
@@ -450,8 +495,10 @@ DEFAULT_EMBEDDINGS_LOCAL_MODEL = "BAAI/bge-small-en-v1.5"
 DEFAULT_EMBEDDINGS_LOCAL_FORCE_CPU = False  # Force CPU mode for local embeddings (avoids MPS/XPC issues on macOS)
 DEFAULT_EMBEDDINGS_LOCAL_TRUST_REMOTE_CODE = False  # Security: disabled by default, required for some models
 DEFAULT_EMBEDDINGS_OPENAI_MODEL = "text-embedding-3-small"
+DEFAULT_EMBEDDINGS_OPENAI_BATCH_SIZE = 100
 DEFAULT_EMBEDDINGS_GEMINI_MODEL = "gemini-embedding-001"
 DEFAULT_EMBEDDINGS_GEMINI_OUTPUT_DIMENSIONALITY = 768
+DEFAULT_EMBEDDINGS_GEMINI_FORCE_IPV4 = False
 DEFAULT_EMBEDDING_DIMENSION = 384
 
 DEFAULT_RERANKER_PROVIDER = "local"
@@ -466,9 +513,11 @@ DEFAULT_RERANKER_LOCAL_BUCKET_BATCHING = False  # Length-sorted bucket batching:
 DEFAULT_RERANKER_LOCAL_BATCH_SIZE = 32  # Batch size for local reranker predict() calls
 DEFAULT_RERANKER_TEI_BATCH_SIZE = 128
 DEFAULT_RERANKER_TEI_MAX_CONCURRENT = 8
+DEFAULT_RERANKER_TEI_HTTP_TIMEOUT = 30.0  # HTTP timeout for TEI reranker requests (seconds)
 DEFAULT_RERANKER_MAX_CANDIDATES = 300
 DEFAULT_RERANKER_FLASHRANK_MODEL = "ms-marco-MiniLM-L-12-v2"  # Best balance of speed and quality
 DEFAULT_RERANKER_FLASHRANK_CACHE_DIR = None  # Use default cache directory
+DEFAULT_RERANKER_FLASHRANK_CPU_MEM_ARENA = False  # Disable ONNX CPU memory arena to bound RSS
 
 DEFAULT_EMBEDDINGS_COHERE_MODEL = "embed-english-v3.0"
 DEFAULT_RERANKER_COHERE_MODEL = "rerank-english-v3.0"
@@ -551,12 +600,17 @@ DEFAULT_FILE_DELETE_AFTER_RETAIN = True  # Delete file bytes after retain (saves
 DEFAULT_ENABLE_OBSERVATIONS = True  # Observations enabled by default
 DEFAULT_ENABLE_OBSERVATION_HISTORY = True  # Observation history tracking enabled by default
 DEFAULT_ENABLE_MENTAL_MODEL_HISTORY = True  # Mental model history tracking enabled by default
+DEFAULT_CONSOLIDATION_MAX_ATTEMPTS = 3  # Outer retry attempts for consolidation LLM batch calls
 DEFAULT_CONSOLIDATION_BATCH_SIZE = 50  # Memories to load per batch (internal memory optimization)
+DEFAULT_CONSOLIDATION_MAX_MEMORIES_PER_ROUND = (
+    100  # Max memories per consolidation round (0 = unlimited). Limits how long one bank holds a worker slot.
+)
 DEFAULT_CONSOLIDATION_LLM_BATCH_SIZE = 8  # Facts per LLM call (1 = no batching; >1 = batch mode)
 DEFAULT_CONSOLIDATION_MAX_TOKENS = 512  # Max tokens for recall when finding related observations
+DEFAULT_CONSOLIDATION_RECALL_BUDGET = "low"  # Budget level for consolidation recall (low/mid/high)
 DEFAULT_CONSOLIDATION_SOURCE_FACTS_MAX_TOKENS = (
-    -1
-)  # Total token budget for source facts in consolidation recall (-1 = unlimited)
+    4096  # Total token budget for source facts in consolidation recall (-1 = unlimited)
+)
 DEFAULT_CONSOLIDATION_SOURCE_FACTS_MAX_TOKENS_PER_OBSERVATION = (
     256  # Max tokens of source facts per observation in consolidation prompt (-1 = unlimited)
 )
@@ -571,6 +625,7 @@ DEFAULT_DB_POOL_MIN_SIZE = 5
 DEFAULT_DB_POOL_MAX_SIZE = 100
 DEFAULT_DB_COMMAND_TIMEOUT = 60  # seconds
 DEFAULT_DB_ACQUIRE_TIMEOUT = 30  # seconds
+DEFAULT_DB_STATEMENT_TIMEOUT = 600  # seconds (Postgres statement_timeout applied on every pool connection; 0 disables)
 
 # Worker configuration (distributed task processing)
 DEFAULT_WORKER_ENABLED = True  # API runs worker by default (standalone mode)
@@ -579,7 +634,6 @@ DEFAULT_WORKER_POLL_INTERVAL_MS = 500  # Poll database every 500ms
 DEFAULT_WORKER_MAX_RETRIES = 3  # Max retries before marking task failed
 DEFAULT_WORKER_HTTP_PORT = 8889  # HTTP port for worker metrics/health
 DEFAULT_WORKER_MAX_SLOTS = 10  # Total concurrent tasks per worker
-DEFAULT_WORKER_CONSOLIDATION_MAX_SLOTS = 2  # Max concurrent consolidation tasks per worker
 DEFAULT_RETAIN_MAX_CONCURRENT = 4  # Max concurrent retain DB phases (HNSW reads + writes). Limits I/O contention.
 
 # Reflect agent settings
@@ -587,6 +641,25 @@ DEFAULT_REFLECT_MAX_ITERATIONS = 10  # Max tool call iterations before forcing r
 DEFAULT_REFLECT_MAX_CONTEXT_TOKENS = 100_000  # Max accumulated context tokens before forcing final prompt
 DEFAULT_REFLECT_WALL_TIMEOUT = 300  # Wall-clock timeout in seconds for the entire reflect operation (5 minutes)
 DEFAULT_REFLECT_SOURCE_FACTS_MAX_TOKENS = -1  # Token budget for source facts in search_observations (-1 = disabled)
+DEFAULT_RECALL_INCLUDE_CHUNKS = True  # Whether internal recall (e.g. mental model refresh) returns raw chunks
+DEFAULT_RECALL_MAX_TOKENS = 2048  # Token budget for facts returned by internal recall
+DEFAULT_RECALL_CHUNKS_MAX_TOKENS = 1000  # Token budget for raw chunks returned by internal recall
+
+# Recall budget mapping
+# "fixed": thinking_budget = recall_budget_fixed_<level> (preserves legacy behavior)
+# "adaptive": thinking_budget = round(max_tokens * recall_budget_adaptive_<level>),
+#             clamped to [recall_budget_min, recall_budget_max]
+RECALL_BUDGET_FUNCTIONS = ("fixed", "adaptive")
+DEFAULT_RECALL_BUDGET_FUNCTION = "fixed"
+DEFAULT_RECALL_BUDGET_FIXED_LOW = 100
+DEFAULT_RECALL_BUDGET_FIXED_MID = 300
+DEFAULT_RECALL_BUDGET_FIXED_HIGH = 1000
+# Adaptive defaults chosen to roughly match fixed defaults at max_tokens=4096
+DEFAULT_RECALL_BUDGET_ADAPTIVE_LOW = 0.025
+DEFAULT_RECALL_BUDGET_ADAPTIVE_MID = 0.075
+DEFAULT_RECALL_BUDGET_ADAPTIVE_HIGH = 0.25
+DEFAULT_RECALL_BUDGET_MIN = 20  # Floor for the adaptive function
+DEFAULT_RECALL_BUDGET_MAX = 2000  # Ceiling for the adaptive function
 
 # Disposition defaults (None = not set, fall back to bank DB value or 3)
 DEFAULT_DISPOSITION_SKEPTICISM = None
@@ -649,6 +722,10 @@ class JsonFormatter(logging.Formatter):
         logging.CRITICAL: "CRITICAL",
     }
 
+    def __init__(self, allowed_fields: frozenset[str] | None = None):
+        super().__init__()
+        self._allowed_fields = allowed_fields
+
     def format(self, record: logging.LogRecord) -> str:
         log_entry = {
             "severity": self.SEVERITY_MAP.get(record.levelno, "DEFAULT"),
@@ -657,10 +734,20 @@ class JsonFormatter(logging.Formatter):
             "logger": record.name,
         }
 
+        # Lazy import to avoid circular dependency (engine imports from config).
+        from hindsight_api.engine.memory_engine import _current_schema
+
+        tenant = _current_schema.get()
+        if tenant:
+            log_entry["tenant"] = tenant
+
         # Add exception info if present
         if record.exc_info:
             log_entry["exception"] = self.formatException(record.exc_info)
 
+        if self._allowed_fields is not None:
+            log_entry = {k: v for k, v in log_entry.items() if k in self._allowed_fields}
+
         return json.dumps(log_entry)
 
 
@@ -669,6 +756,25 @@ def _parse_str_list(value: str) -> list[str]:
     return [v.strip() for v in value.split(",") if v.strip()]
 
 
+def _parse_positive_int(name: str, raw: str | None, default: int) -> int:
+    """
+    Parse an env var that must be a positive integer (>= 1).
+
+    Falls back to ``default`` when unset/empty. Raises ValueError on non-integer
+    or non-positive values so misconfiguration fails fast instead of triggering
+    infinite loops or zero-step range() calls downstream.
+    """
+    if raw is None or raw == "":
+        return default
+    try:
+        parsed = int(raw)
+    except ValueError as e:
+        raise ValueError(f"{name} must be an integer, got {raw!r}") from e
+    if parsed < 1:
+        raise ValueError(f"{name} must be >= 1, got {parsed}")
+    return parsed
+
+
 def _validate_extraction_mode(mode: str) -> str:
     """Validate and normalize extraction mode."""
     mode_lower = mode.lower()
@@ -681,6 +787,18 @@ def _validate_extraction_mode(mode: str) -> str:
     return mode_lower
 
 
+def _validate_recall_budget_function(function: str) -> str:
+    """Validate and normalize recall budget function."""
+    function_lower = function.lower()
+    if function_lower not in RECALL_BUDGET_FUNCTIONS:
+        logger.warning(
+            f"Invalid recall budget function '{function}', must be one of {RECALL_BUDGET_FUNCTIONS}. "
+            f"Defaulting to '{DEFAULT_RECALL_BUDGET_FUNCTION}'."
+        )
+        return DEFAULT_RECALL_BUDGET_FUNCTION
+    return function_lower
+
+
 def _get_default_model_for_provider(provider: str) -> str:
     """Get the default model for a given provider."""
     return PROVIDER_DEFAULT_MODELS.get(provider.lower(), DEFAULT_LLM_MODEL)
@@ -711,6 +829,7 @@ class HindsightConfig:
     """Configuration container for Hindsight API."""
 
     # Database
+    database_backend: Literal["postgresql", "oracle"]
     database_url: str
     migration_database_url: str | None
     database_schema: str
@@ -732,6 +851,9 @@ class HindsightConfig:
     llm_extra_body: (
         dict | None
     )  # Extra body params merged into OpenAI-compatible API calls (e.g. {"chat_template_kwargs": {"enable_thinking": true}})
+    llm_default_headers: (
+        dict | None
+    )  # Custom headers passed as default_headers to provider SDK clients (e.g. {"X-Component-Id": "hindsight"} for proxies / request tracing)
 
     # Vertex AI configuration
     llm_vertexai_project_id: str | None
@@ -790,6 +912,7 @@ class HindsightConfig:
     embeddings_cohere_api_key: str | None
     embeddings_cohere_model: str
     embeddings_cohere_base_url: str | None
+    embeddings_cohere_output_dimensions: int | None
     embeddings_openrouter_api_key: str | None
     embeddings_openrouter_model: str
     embeddings_litellm_api_base: str
@@ -804,6 +927,7 @@ class HindsightConfig:
     embeddings_gemini_api_key: str | None
     embeddings_gemini_model: str
     embeddings_gemini_output_dimensionality: int | None
+    embeddings_gemini_force_ipv4: bool
     embeddings_vertexai_project_id: str | None
     embeddings_vertexai_region: str | None
     embeddings_vertexai_service_account_key: str | None
@@ -820,6 +944,7 @@ class HindsightConfig:
     reranker_tei_url: str | None
     reranker_tei_batch_size: int
     reranker_tei_max_concurrent: int
+    reranker_tei_http_timeout: float
     reranker_max_candidates: int
     reranker_cohere_api_key: str | None
     reranker_cohere_model: str
@@ -849,6 +974,7 @@ class HindsightConfig:
     base_path: str
     log_level: str
     log_format: str
+    log_json_fields: list[str] | None  # None = all fields; explicit list = allowlist
     mcp_enabled: bool
     mcp_enabled_tools: list[str] | None  # None = all tools; explicit list = allowlist
     mcp_stateless: bool  # True = stateless HTTP (POST-only); False = stateful (supports GET/SSE)
@@ -897,6 +1023,7 @@ class HindsightConfig:
     file_parser_allowlist: list[str] | None  # Parsers clients may request (None = all registered)
     file_parser_iris_token: str | None  # Vectorize API token for iris parser (VECTORIZE_TOKEN)
     file_parser_iris_org_id: str | None  # Vectorize org ID for iris parser (VECTORIZE_ORG_ID)
+    file_parser_llama_parse_api_key: str | None  # LlamaCloud API key for llama_parse parser
     file_conversion_max_batch_size_mb: int  # Max total batch size in MB (all files combined)
     file_conversion_max_batch_size: int  # Max files per request
     enable_file_upload_api: bool
@@ -907,10 +1034,13 @@ class HindsightConfig:
     enable_observation_history: bool
     enable_mental_model_history: bool
     consolidation_batch_size: int
+    consolidation_max_memories_per_round: int
     consolidation_llm_batch_size: int
     consolidation_max_tokens: int
+    consolidation_recall_budget: str
     consolidation_source_facts_max_tokens: int
     consolidation_source_facts_max_tokens_per_observation: int
+    consolidation_max_attempts: int
     observations_mission: str | None
     max_observations_per_scope: int
 
@@ -925,6 +1055,25 @@ class HindsightConfig:
     reflect_mission: str | None
     reflect_source_facts_max_tokens: int
 
+    # Recall settings (used by internal recall, e.g. during mental model refresh)
+    recall_include_chunks: bool
+    recall_max_tokens: int
+    recall_chunks_max_tokens: int
+
+    # Recall budget mapping: how the Budget enum (LOW/MID/HIGH) maps to thinking_budget integer.
+    # function="fixed": use the recall_budget_fixed_* values directly (legacy behavior).
+    # function="adaptive": compute round(max_tokens * recall_budget_adaptive_*),
+    #                      clamped to [recall_budget_min, recall_budget_max].
+    recall_budget_function: str
+    recall_budget_fixed_low: int
+    recall_budget_fixed_mid: int
+    recall_budget_fixed_high: int
+    recall_budget_adaptive_low: float
+    recall_budget_adaptive_mid: float
+    recall_budget_adaptive_high: float
+    recall_budget_min: int
+    recall_budget_max: int
+
     # Disposition settings (hierarchical - can be overridden per bank; None = fall back to DB)
     disposition_skepticism: int | None
     disposition_literalism: int | None
@@ -942,6 +1091,7 @@ class HindsightConfig:
     db_pool_max_size: int
     db_command_timeout: int
     db_acquire_timeout: int
+    db_statement_timeout: int
 
     # Worker configuration (distributed task processing)
     worker_enabled: bool
@@ -950,7 +1100,7 @@ class HindsightConfig:
     worker_max_retries: int
     worker_http_port: int
     worker_max_slots: int
-    worker_consolidation_max_slots: int
+    worker_slot_reservations: dict[str, int]
     retain_max_concurrent: int
 
     # Reflect agent settings
@@ -977,6 +1127,10 @@ class HindsightConfig:
     webhook_event_types: list[str]  # Event types to deliver globally
     webhook_delivery_poll_interval_seconds: int  # How often the delivery worker polls
 
+    # Defaulted fields (source-compatible additions — existing direct constructor callers keep working).
+    # Keep at the end of the dataclass; Python forbids non-default fields after default fields.
+    embeddings_openai_batch_size: int = DEFAULT_EMBEDDINGS_OPENAI_BATCH_SIZE
+
     # Class-level sets for configuration categorization
 
     # CREDENTIAL_FIELDS: Never exposed via API, never configurable per-tenant/bank
@@ -1009,6 +1163,7 @@ class HindsightConfig:
         "file_storage_azure_account_key",
         # File parser credentials
         "file_parser_iris_token",
+        "file_parser_llama_parse_api_key",
     }
 
     # CONFIGURABLE_FIELDS: Safe behavioral settings that can be customized per-tenant/bank
@@ -1031,6 +1186,7 @@ class HindsightConfig:
         # Consolidation settings
         "enable_observations",
         "consolidation_llm_batch_size",
+        "consolidation_max_memories_per_round",
         "consolidation_source_facts_max_tokens",
         "consolidation_source_facts_max_tokens_per_observation",
         "observations_mission",
@@ -1038,6 +1194,20 @@ class HindsightConfig:
         # Reflect settings
         "reflect_mission",
         "reflect_source_facts_max_tokens",
+        # Recall settings (used by internal recall, e.g. mental model refresh)
+        "recall_include_chunks",
+        "recall_max_tokens",
+        "recall_chunks_max_tokens",
+        # Recall budget mapping (Budget enum -> thinking_budget integer)
+        "recall_budget_function",
+        "recall_budget_fixed_low",
+        "recall_budget_fixed_mid",
+        "recall_budget_fixed_high",
+        "recall_budget_adaptive_low",
+        "recall_budget_adaptive_mid",
+        "recall_budget_adaptive_high",
+        "recall_budget_min",
+        "recall_budget_max",
         # Disposition settings
         "disposition_skepticism",
         "disposition_literalism",
@@ -1144,6 +1314,40 @@ class HindsightConfig:
                 f"provider: {self.retain_llm_provider or self.llm_provider})"
             )
 
+        # Warn if local ML dependencies are missing when configured.
+        # Don't hard-fail here — the actual ImportError fires at model init time
+        # with a clear message. This early warning catches it before startup proceeds.
+        if self.embeddings_provider == "local" or self.reranker_provider == "local":
+            try:
+                import importlib
+
+                importlib.import_module("sentence_transformers")
+            except ImportError:
+                missing = []
+                if self.embeddings_provider == "local":
+                    missing.append("embeddings")
+                if self.reranker_provider == "local":
+                    missing.append("reranker")
+                logger.warning(
+                    "Local ML provider configured for %s, but 'sentence-transformers' "
+                    "is not installed. The API will fail at startup. Either:\n"
+                    "  1. Install local ML deps: pip install hindsight-api[local-ml]\n"
+                    "  2. Use a remote provider instead:\n"
+                    "     HINDSIGHT_API_EMBEDDINGS_PROVIDER=openai (or gemini, tei)\n"
+                    "     HINDSIGHT_API_RERANKER_PROVIDER=none (or tei)",
+                    " and ".join(missing),
+                )
+
+        # Validate that sum of per-operation slot reservations does not exceed max_slots
+        total_reserved = sum(self.worker_slot_reservations.values())
+        if total_reserved > self.worker_max_slots:
+            reservation_details = ", ".join(f"{k}={v}" for k, v in self.worker_slot_reservations.items() if v > 0)
+            raise ValueError(
+                f"Sum of per-operation slot reservations ({total_reserved}: {reservation_details}) "
+                f"exceeds worker_max_slots ({self.worker_max_slots}). "
+                f"Reduce reservations or increase HINDSIGHT_API_WORKER_MAX_SLOTS."
+            )
+
     @classmethod
     def from_env(cls) -> "HindsightConfig":
         """Create configuration from environment variables."""
@@ -1153,6 +1357,7 @@ class HindsightConfig:
 
         config = cls(
             # Database
+            database_backend=os.getenv(ENV_DATABASE_BACKEND, DEFAULT_DATABASE_BACKEND).lower(),
             database_url=os.getenv(ENV_DATABASE_URL, DEFAULT_DATABASE_URL),
             migration_database_url=os.getenv(ENV_MIGRATION_DATABASE_URL) or None,
             database_schema=os.getenv(ENV_DATABASE_SCHEMA, DEFAULT_DATABASE_SCHEMA),
@@ -1171,6 +1376,7 @@ class HindsightConfig:
             llm_groq_service_tier=os.getenv(ENV_LLM_GROQ_SERVICE_TIER, DEFAULT_LLM_GROQ_SERVICE_TIER),
             llm_openai_service_tier=os.getenv(ENV_LLM_OPENAI_SERVICE_TIER, DEFAULT_LLM_OPENAI_SERVICE_TIER),
             llm_extra_body=json.loads(os.getenv(ENV_LLM_EXTRA_BODY, "null")),
+            llm_default_headers=json.loads(os.getenv(ENV_LLM_DEFAULT_HEADERS, "null")),
             # Vertex AI
             llm_vertexai_project_id=os.getenv(ENV_LLM_VERTEXAI_PROJECT_ID) or DEFAULT_LLM_VERTEXAI_PROJECT_ID,
             llm_vertexai_region=os.getenv(ENV_LLM_VERTEXAI_REGION, DEFAULT_LLM_VERTEXAI_REGION),
@@ -1270,10 +1476,18 @@ class HindsightConfig:
             in ("true", "1"),
             embeddings_tei_url=os.getenv(ENV_EMBEDDINGS_TEI_URL),
             embeddings_openai_base_url=os.getenv(ENV_EMBEDDINGS_OPENAI_BASE_URL) or None,
+            embeddings_openai_batch_size=_parse_positive_int(
+                ENV_EMBEDDINGS_OPENAI_BATCH_SIZE,
+                os.getenv(ENV_EMBEDDINGS_OPENAI_BATCH_SIZE),
+                DEFAULT_EMBEDDINGS_OPENAI_BATCH_SIZE,
+            ),
             # Cohere embeddings (with backward-compatible fallback to shared API key)
             embeddings_cohere_api_key=os.getenv(ENV_EMBEDDINGS_COHERE_API_KEY) or os.getenv(ENV_COHERE_API_KEY),
             embeddings_cohere_model=os.getenv(ENV_EMBEDDINGS_COHERE_MODEL, DEFAULT_EMBEDDINGS_COHERE_MODEL),
             embeddings_cohere_base_url=os.getenv(ENV_EMBEDDINGS_COHERE_BASE_URL) or None,
+            embeddings_cohere_output_dimensions=int(v)
+            if (v := os.getenv(ENV_EMBEDDINGS_COHERE_OUTPUT_DIMENSIONS))
+            else None,
             # OpenRouter embeddings (with fallback to shared OpenRouter key, then LLM key)
             embeddings_openrouter_api_key=os.getenv(ENV_EMBEDDINGS_OPENROUTER_API_KEY)
             or os.getenv(ENV_OPENROUTER_API_KEY)
@@ -1305,6 +1519,11 @@ class HindsightConfig:
                     str(DEFAULT_EMBEDDINGS_GEMINI_OUTPUT_DIMENSIONALITY),
                 )
             ),
+            embeddings_gemini_force_ipv4=os.getenv(
+                ENV_EMBEDDINGS_GEMINI_FORCE_IPV4,
+                str(DEFAULT_EMBEDDINGS_GEMINI_FORCE_IPV4),
+            ).lower()
+            in ("true", "1"),
             embeddings_vertexai_project_id=os.getenv(ENV_EMBEDDINGS_VERTEXAI_PROJECT_ID)
             or os.getenv(ENV_LLM_VERTEXAI_PROJECT_ID),
             embeddings_vertexai_region=os.getenv(ENV_EMBEDDINGS_VERTEXAI_REGION) or os.getenv(ENV_LLM_VERTEXAI_REGION),
@@ -1338,6 +1557,9 @@ class HindsightConfig:
             reranker_tei_max_concurrent=int(
                 os.getenv(ENV_RERANKER_TEI_MAX_CONCURRENT, str(DEFAULT_RERANKER_TEI_MAX_CONCURRENT))
             ),
+            reranker_tei_http_timeout=float(
+                os.getenv(ENV_RERANKER_TEI_HTTP_TIMEOUT, str(DEFAULT_RERANKER_TEI_HTTP_TIMEOUT))
+            ),
             reranker_max_candidates=int(os.getenv(ENV_RERANKER_MAX_CANDIDATES, str(DEFAULT_RERANKER_MAX_CANDIDATES))),
             # Cohere reranker (with backward-compatible fallback to shared API key)
             reranker_cohere_api_key=os.getenv(ENV_RERANKER_COHERE_API_KEY) or os.getenv(ENV_COHERE_API_KEY),
@@ -1382,6 +1604,7 @@ class HindsightConfig:
             base_path=os.getenv(ENV_BASE_PATH, DEFAULT_BASE_PATH),
             log_level=os.getenv(ENV_LOG_LEVEL, DEFAULT_LOG_LEVEL),
             log_format=os.getenv(ENV_LOG_FORMAT, DEFAULT_LOG_FORMAT).lower(),
+            log_json_fields=_parse_str_list(os.getenv(ENV_LOG_JSON_FIELDS, "")) or None,
             mcp_enabled=os.getenv(ENV_MCP_ENABLED, str(DEFAULT_MCP_ENABLED)).lower() == "true",
             mcp_enabled_tools=[t.strip() for t in os.getenv(ENV_MCP_ENABLED_TOOLS).split(",") if t.strip()]
             if os.getenv(ENV_MCP_ENABLED_TOOLS)
@@ -1449,6 +1672,7 @@ class HindsightConfig:
             else None,
             file_parser_iris_token=os.getenv(ENV_FILE_PARSER_IRIS_TOKEN) or None,
             file_parser_iris_org_id=os.getenv(ENV_FILE_PARSER_IRIS_ORG_ID) or None,
+            file_parser_llama_parse_api_key=os.getenv(ENV_FILE_PARSER_LLAMA_PARSE_API_KEY) or None,
             file_conversion_max_batch_size_mb=int(
                 os.getenv(ENV_FILE_CONVERSION_MAX_BATCH_SIZE_MB, str(DEFAULT_FILE_CONVERSION_MAX_BATCH_SIZE_MB))
             ),
@@ -1474,12 +1698,19 @@ class HindsightConfig:
             consolidation_batch_size=int(
                 os.getenv(ENV_CONSOLIDATION_BATCH_SIZE, str(DEFAULT_CONSOLIDATION_BATCH_SIZE))
             ),
+            consolidation_max_memories_per_round=int(
+                os.getenv(
+                    ENV_CONSOLIDATION_MAX_MEMORIES_PER_ROUND,
+                    str(DEFAULT_CONSOLIDATION_MAX_MEMORIES_PER_ROUND),
+                )
+            ),
             consolidation_llm_batch_size=int(
                 os.getenv(ENV_CONSOLIDATION_LLM_BATCH_SIZE, str(DEFAULT_CONSOLIDATION_LLM_BATCH_SIZE))
             ),
             consolidation_max_tokens=int(
                 os.getenv(ENV_CONSOLIDATION_MAX_TOKENS, str(DEFAULT_CONSOLIDATION_MAX_TOKENS))
             ),
+            consolidation_recall_budget=os.getenv(ENV_CONSOLIDATION_RECALL_BUDGET, DEFAULT_CONSOLIDATION_RECALL_BUDGET),
             consolidation_source_facts_max_tokens=int(
                 os.getenv(ENV_CONSOLIDATION_SOURCE_FACTS_MAX_TOKENS, str(DEFAULT_CONSOLIDATION_SOURCE_FACTS_MAX_TOKENS))
             ),
@@ -1489,6 +1720,9 @@ class HindsightConfig:
                     str(DEFAULT_CONSOLIDATION_SOURCE_FACTS_MAX_TOKENS_PER_OBSERVATION),
                 )
             ),
+            consolidation_max_attempts=int(
+                os.getenv(ENV_CONSOLIDATION_MAX_ATTEMPTS, str(DEFAULT_CONSOLIDATION_MAX_ATTEMPTS))
+            ),
             observations_mission=os.getenv(ENV_OBSERVATIONS_MISSION) or DEFAULT_OBSERVATIONS_MISSION,
             max_observations_per_scope=int(
                 os.getenv(ENV_MAX_OBSERVATIONS_PER_SCOPE, str(DEFAULT_MAX_OBSERVATIONS_PER_SCOPE))
@@ -1502,6 +1736,7 @@ class HindsightConfig:
             db_pool_max_size=int(os.getenv(ENV_DB_POOL_MAX_SIZE, str(DEFAULT_DB_POOL_MAX_SIZE))),
             db_command_timeout=int(os.getenv(ENV_DB_COMMAND_TIMEOUT, str(DEFAULT_DB_COMMAND_TIMEOUT))),
             db_acquire_timeout=int(os.getenv(ENV_DB_ACQUIRE_TIMEOUT, str(DEFAULT_DB_ACQUIRE_TIMEOUT))),
+            db_statement_timeout=int(os.getenv(ENV_DB_STATEMENT_TIMEOUT, str(DEFAULT_DB_STATEMENT_TIMEOUT))),
             # Worker configuration
             worker_enabled=os.getenv(ENV_WORKER_ENABLED, str(DEFAULT_WORKER_ENABLED)).lower() == "true",
             worker_id=os.getenv(ENV_WORKER_ID) or DEFAULT_WORKER_ID,
@@ -1509,9 +1744,11 @@ class HindsightConfig:
             worker_max_retries=int(os.getenv(ENV_WORKER_MAX_RETRIES, str(DEFAULT_WORKER_MAX_RETRIES))),
             worker_http_port=int(os.getenv(ENV_WORKER_HTTP_PORT, str(DEFAULT_WORKER_HTTP_PORT))),
             worker_max_slots=int(os.getenv(ENV_WORKER_MAX_SLOTS, str(DEFAULT_WORKER_MAX_SLOTS))),
-            worker_consolidation_max_slots=int(
-                os.getenv(ENV_WORKER_CONSOLIDATION_MAX_SLOTS, str(DEFAULT_WORKER_CONSOLIDATION_MAX_SLOTS))
-            ),
+            worker_slot_reservations={
+                op_type: int(os.getenv(env_var, str(default)))
+                for op_type, (env_var, default) in WORKER_SLOT_RESERVATION_TYPES.items()
+                if int(os.getenv(env_var, str(default))) > 0
+            },
             retain_max_concurrent=int(os.getenv(ENV_RETAIN_MAX_CONCURRENT, str(DEFAULT_RETAIN_MAX_CONCURRENT))),
             # Reflect agent settings
             reflect_max_iterations=int(os.getenv(ENV_REFLECT_MAX_ITERATIONS, str(DEFAULT_REFLECT_MAX_ITERATIONS))),
@@ -1523,6 +1760,31 @@ class HindsightConfig:
             reflect_source_facts_max_tokens=int(
                 os.getenv(ENV_REFLECT_SOURCE_FACTS_MAX_TOKENS, str(DEFAULT_REFLECT_SOURCE_FACTS_MAX_TOKENS))
             ),
+            recall_include_chunks=os.getenv(ENV_RECALL_INCLUDE_CHUNKS, str(DEFAULT_RECALL_INCLUDE_CHUNKS)).lower()
+            in ("true", "1", "yes"),
+            recall_max_tokens=int(os.getenv(ENV_RECALL_MAX_TOKENS, str(DEFAULT_RECALL_MAX_TOKENS))),
+            recall_chunks_max_tokens=int(
+                os.getenv(ENV_RECALL_CHUNKS_MAX_TOKENS, str(DEFAULT_RECALL_CHUNKS_MAX_TOKENS))
+            ),
+            recall_budget_function=_validate_recall_budget_function(
+                os.getenv(ENV_RECALL_BUDGET_FUNCTION, DEFAULT_RECALL_BUDGET_FUNCTION)
+            ),
+            recall_budget_fixed_low=int(os.getenv(ENV_RECALL_BUDGET_FIXED_LOW, str(DEFAULT_RECALL_BUDGET_FIXED_LOW))),
+            recall_budget_fixed_mid=int(os.getenv(ENV_RECALL_BUDGET_FIXED_MID, str(DEFAULT_RECALL_BUDGET_FIXED_MID))),
+            recall_budget_fixed_high=int(
+                os.getenv(ENV_RECALL_BUDGET_FIXED_HIGH, str(DEFAULT_RECALL_BUDGET_FIXED_HIGH))
+            ),
+            recall_budget_adaptive_low=float(
+                os.getenv(ENV_RECALL_BUDGET_ADAPTIVE_LOW, str(DEFAULT_RECALL_BUDGET_ADAPTIVE_LOW))
+            ),
+            recall_budget_adaptive_mid=float(
+                os.getenv(ENV_RECALL_BUDGET_ADAPTIVE_MID, str(DEFAULT_RECALL_BUDGET_ADAPTIVE_MID))
+            ),
+            recall_budget_adaptive_high=float(
+                os.getenv(ENV_RECALL_BUDGET_ADAPTIVE_HIGH, str(DEFAULT_RECALL_BUDGET_ADAPTIVE_HIGH))
+            ),
+            recall_budget_min=int(os.getenv(ENV_RECALL_BUDGET_MIN, str(DEFAULT_RECALL_BUDGET_MIN))),
+            recall_budget_max=int(os.getenv(ENV_RECALL_BUDGET_MAX, str(DEFAULT_RECALL_BUDGET_MAX))),
             # Disposition settings (None = fall back to DB value)
             disposition_skepticism=int(os.getenv(ENV_DISPOSITION_SKEPTICISM))
             if os.getenv(ENV_DISPOSITION_SKEPTICISM)
@@ -1613,7 +1875,8 @@ class HindsightConfig:
         handler.setLevel(self.get_python_log_level())
 
         if self.log_format == "json":
-            handler.setFormatter(JsonFormatter())
+            allowed = frozenset(self.log_json_fields) if self.log_json_fields else None
+            handler.setFormatter(JsonFormatter(allowed_fields=allowed))
         else:
             handler.setFormatter(logging.Formatter("%(asctime)s - %(levelname)s - %(name)s - %(message)s"))
 
@@ -1624,9 +1887,9 @@ class HindsightConfig:
 
     def log_config(self) -> None:
         """Log the current configuration (without sensitive values)."""
-        logger.info(f"Database: {self.database_url} (schema: {self.database_schema})")
+        logger.info(f"Database: {mask_network_location(self.database_url)} (schema: {self.database_schema})")
         if self.migration_database_url:
-            logger.info(f"Migration database: {self.migration_database_url}")
+            logger.info(f"Migration database: {mask_network_location(self.migration_database_url)}")
         logger.info(f"LLM: provider={self.llm_provider}, model={self.llm_model}")
         if self.retain_llm_provider or self.retain_llm_model:
             retain_provider = self.retain_llm_provider or self.llm_provider

hindsight-api-slim/hindsight_api/config_resolver.py

@@ -11,30 +11,36 @@ multiple API servers.
 import json
 import logging
 from dataclasses import asdict, replace
-from typing import Any
+from typing import TYPE_CHECKING, Any
 
-import asyncpg
-
-from hindsight_api.config import HindsightConfig, _get_raw_config, normalize_config_dict
+from hindsight_api.config import (
+    RECALL_BUDGET_FUNCTIONS,
+    HindsightConfig,
+    _get_raw_config,
+    normalize_config_dict,
+)
 from hindsight_api.engine.memory_engine import fq_table
 from hindsight_api.extensions.tenant import TenantExtension
 from hindsight_api.models import RequestContext
 
+if TYPE_CHECKING:
+    from hindsight_api.engine.db.base import DatabaseBackend
+
 logger = logging.getLogger(__name__)
 
 
 class ConfigResolver:
     """Resolves hierarchical configuration with tenant/bank overrides."""
 
-    def __init__(self, pool: asyncpg.Pool, tenant_extension: TenantExtension | None = None):
+    def __init__(self, backend: "DatabaseBackend", tenant_extension: TenantExtension | None = None):
         """
         Initialize config resolver.
 
         Args:
-            pool: Database connection pool
+            backend: Database backend for connection acquisition
             tenant_extension: Optional tenant extension for tenant-level config and permissions
         """
-        self.pool = pool
+        self._backend = backend
         self.tenant_extension = tenant_extension
         self._global_config = _get_raw_config()
         self._configurable_fields = HindsightConfig.get_configurable_fields()
@@ -148,7 +154,7 @@ class ConfigResolver:
             Dict of config overrides (only configurable fields, normalized keys)
         """
         try:
-            async with self.pool.acquire() as conn:
+            async with self._backend.acquire() as conn:
                 row = await conn.fetchrow(
                     f"""
                     SELECT config FROM {fq_table("banks")} WHERE bank_id = $1
@@ -256,8 +262,11 @@ class ConfigResolver:
                     "Strategy names must not be empty strings. Remove entries with empty names before saving."
                 )
 
+        # Validate recall budget fields
+        _validate_recall_budget_updates(normalized_updates)
+
         # Merge with existing config (JSONB || operator)
-        async with self.pool.acquire() as conn:
+        async with self._backend.acquire() as conn:
             await conn.execute(
                 f"""
                 UPDATE {fq_table("banks")}
@@ -278,7 +287,7 @@ class ConfigResolver:
         Args:
             bank_id: Bank identifier
         """
-        async with self.pool.acquire() as conn:
+        async with self._backend.acquire() as conn:
             await conn.execute(
                 f"""
                 UPDATE {fq_table("banks")}
@@ -292,6 +301,53 @@ class ConfigResolver:
         logger.info(f"Reset bank config for {bank_id} to defaults")
 
 
+_RECALL_BUDGET_FIXED_KEYS = (
+    "recall_budget_fixed_low",
+    "recall_budget_fixed_mid",
+    "recall_budget_fixed_high",
+)
+_RECALL_BUDGET_ADAPTIVE_KEYS = (
+    "recall_budget_adaptive_low",
+    "recall_budget_adaptive_mid",
+    "recall_budget_adaptive_high",
+)
+
+
+def _validate_recall_budget_updates(updates: dict[str, Any]) -> None:
+    """Validate recall budget config updates. Raises ValueError on invalid input."""
+    if "recall_budget_function" in updates:
+        function = updates["recall_budget_function"]
+        if not isinstance(function, str) or function.lower() not in RECALL_BUDGET_FUNCTIONS:
+            raise ValueError(
+                f"recall_budget_function must be one of {sorted(RECALL_BUDGET_FUNCTIONS)}, got {function!r}"
+            )
+
+    for key in _RECALL_BUDGET_FIXED_KEYS:
+        if key in updates:
+            value = updates[key]
+            if not isinstance(value, int) or isinstance(value, bool) or value < 1:
+                raise ValueError(f"{key} must be a positive integer, got {value!r}")
+
+    for key in _RECALL_BUDGET_ADAPTIVE_KEYS:
+        if key in updates:
+            value = updates[key]
+            if isinstance(value, bool) or not isinstance(value, (int, float)) or value <= 0:
+                raise ValueError(f"{key} must be a positive number, got {value!r}")
+
+    for key in ("recall_budget_min", "recall_budget_max"):
+        if key in updates:
+            value = updates[key]
+            if not isinstance(value, int) or isinstance(value, bool) or value < 1:
+                raise ValueError(f"{key} must be a positive integer, got {value!r}")
+
+    if "recall_budget_min" in updates and "recall_budget_max" in updates:
+        if updates["recall_budget_min"] > updates["recall_budget_max"]:
+            raise ValueError(
+                f"recall_budget_min ({updates['recall_budget_min']}) must be <= "
+                f"recall_budget_max ({updates['recall_budget_max']})"
+            )
+
+
 def apply_strategy(config: HindsightConfig, strategy_name: str) -> HindsightConfig:
     """
     Apply a named retain strategy's overrides on top of a resolved config.

hindsight-api-slim/hindsight_api/daemon.py

@@ -63,7 +63,17 @@ def daemonize():
     Fork the current process into a background daemon.
 
     Uses double-fork technique to properly detach from terminal.
+
+    On Windows there is no fork model: the spawning parent is expected to
+    detach us via `CREATE_NEW_PROCESS_GROUP | DETACHED_PROCESS` and to
+    redirect stdout/stderr to HINDSIGHT_API_DAEMON_LOG before exec. We
+    still ensure the log directory exists so that any file handlers set
+    up by the calling app have a valid target.
     """
+    if sys.platform == "win32":
+        DAEMON_LOG_PATH.parent.mkdir(parents=True, exist_ok=True)
+        return
+
     # First fork - detach from parent
     try:
         pid = os.fork()

hindsight-api-slim/hindsight_api/db_url.py

@@ -0,0 +1,64 @@
+"""Database URL normalization.
+
+Hindsight accepts SQLAlchemy-style URLs like ``postgresql+asyncpg://...?ssl=require``
+for its async engine, but the same string cannot be handed directly to synchronous
+SQLAlchemy (psycopg2) or to :func:`asyncpg.create_pool`, which both expect a
+libpq-compatible URL (``postgresql://...?sslmode=require``).
+
+:func:`to_libpq_url` performs that translation. It is idempotent and safe to
+apply to URLs that are already libpq-compatible, to the ``pg0`` embedded-PG
+marker, or to any non-PostgreSQL string (returned unchanged).
+"""
+
+from __future__ import annotations
+
+from urllib.parse import parse_qsl, urlencode, urlsplit, urlunsplit
+
+_ASYNCPG_SCHEMES = ("postgresql+asyncpg", "postgres+asyncpg")
+_POSTGRES_SCHEMES = ("postgresql", "postgres") + _ASYNCPG_SCHEMES
+
+
+def is_oracle_url(url: str) -> bool:
+    """True if ``url`` is an Oracle SQLAlchemy URL (``oracle`` or ``oracle+oracledb``)."""
+    if not url or "://" not in url:
+        return False
+    return urlsplit(url).scheme.startswith("oracle")
+
+
+def to_libpq_url(url: str) -> str:
+    """Normalize a PostgreSQL URL for libpq-style consumers.
+
+    Accepts a SQLAlchemy URL (``postgresql+asyncpg://...``) or a plain libpq
+    URL and returns a form suitable for:
+
+    - :func:`sqlalchemy.create_engine` (sync / psycopg2)
+    - :func:`asyncpg.create_pool`
+
+    Transformations:
+
+    - ``postgresql+asyncpg`` / ``postgres+asyncpg`` / ``postgres`` → ``postgresql``
+    - Query param ``ssl=<mode>`` → ``sslmode=<mode>`` (SQLAlchemy's asyncpg
+      dialect uses ``ssl=``; libpq uses ``sslmode=``)
+
+    Any non-PostgreSQL input (e.g. the ``pg0`` embedded-PG marker, a sqlite
+    URL, an empty string) is returned unchanged. Already-normalized URLs are
+    returned unchanged.
+    """
+    if not url or "://" not in url:
+        return url
+
+    parts = urlsplit(url)
+    if parts.scheme not in _POSTGRES_SCHEMES:
+        return url
+
+    new_scheme = "postgresql"
+
+    new_query_pairs = [
+        ("sslmode", v) if k == "ssl" else (k, v) for k, v in parse_qsl(parts.query, keep_blank_values=True)
+    ]
+    new_query = urlencode(new_query_pairs)
+
+    if new_scheme == parts.scheme and new_query == parts.query:
+        return url
+
+    return urlunsplit((new_scheme, parts.netloc, parts.path, new_query, parts.fragment))