Merge branch 'main' into docs-chat-cloud-first

* docs(nemoclaw): prioritize Hindsight Cloud with callout banners

* feat(nemoclaw): default --api-url to Hindsight Cloud, make it optional

.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, zai, 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,16 @@ 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: z.ai configuration (Zhipu GLM series, https://z.ai)
+# HINDSIGHT_API_LLM_PROVIDER=zai
+# HINDSIGHT_API_LLM_API_KEY=your-zai-api-key
+# HINDSIGHT_API_LLM_MODEL=glm-4.5-flash  # or glm-4.5-air for the paid tier
+
 # Example: LM Studio local configuration (Qwen 2.5 32B recommended)
 # HINDSIGHT_API_LLM_PROVIDER=lmstudio
 # HINDSIGHT_API_LLM_API_KEY=lmstudio
@@ -44,6 +54,7 @@ HINDSIGHT_API_LOG_LEVEL=info
 
 # Database (Optional - uses embedded pg0 by default)
 # HINDSIGHT_API_DATABASE_URL=postgresql://user:pass@host:5432/db
+# HINDSIGHT_API_READ_DATABASE_URL=  # Optional read-replica URL. When set, recall queries (semantic, BM25, graph, temporal) flow through a separate pool against this URL, offloading the primary. Typically points to a read-only endpoint (CNPG's <cluster>-ro service or Aurora reader endpoint).
 # HINDSIGHT_API_MIGRATION_DATABASE_URL=  # Direct PostgreSQL URL for migrations (bypasses PgBouncer). Falls back to DATABASE_URL.
 # HINDSIGHT_API_DATABASE_SCHEMA=public  # PostgreSQL schema name (default: public)
 
@@ -54,12 +65,25 @@ HINDSIGHT_API_LOG_LEVEL=info
 # HINDSIGHT_API_VECTOR_EXTENSION=pgvectorscale  # Auto-detects pg_diskann on Azure
 
 # Embeddings Configuration (Optional - uses local by default)
-# Provider: "local" (default) or "tei" (HuggingFace Text Embeddings Inference)
+# Provider: "local" (default), "tei", "openai", "cohere", "google", "openrouter", "litellm", or "litellm-sdk"
 # HINDSIGHT_API_EMBEDDINGS_PROVIDER=local
 # For local provider:
 # HINDSIGHT_API_EMBEDDINGS_LOCAL_MODEL=BAAI/bge-small-en-v1.5
+# Optional for China network / restricted HF access:
+# HF_ENDPOINT=https://hf-mirror.com
 # For TEI provider:
 # HINDSIGHT_API_EMBEDDINGS_TEI_URL=http://localhost:8080
+# For OpenAI-compatible embeddings:
+# HINDSIGHT_API_EMBEDDINGS_OPENAI_API_KEY=sk-xxxx
+# HINDSIGHT_API_EMBEDDINGS_OPENAI_MODEL=text-embedding-3-small
+# HINDSIGHT_API_EMBEDDINGS_OPENAI_BASE_URL=https://api.openai.com/v1
+#
+# IMPORTANT: Embedding keys require provider-specific names:
+# HINDSIGHT_API_EMBEDDINGS_{PROVIDER}_{PARAMETER}
+# (for example, HINDSIGHT_API_EMBEDDINGS_OPENAI_MODEL).
+#
+# DeepSeek note: DeepSeek is supported for LLM calls, but not for embeddings.
+# If using DeepSeek as LLM provider, keep embeddings on local/openai/cohere/google/etc.
 
 # Reranker Configuration (Optional - uses local by default)
 # Provider: "local" (default) or "tei" (HuggingFace Text Embeddings Inference)
@@ -83,3 +107,15 @@ HINDSIGHT_API_LOG_LEVEL=info
 # Custom service name and environment (optional, defaults: hindsight-api, development)
 # HINDSIGHT_API_OTEL_SERVICE_NAME=hindsight-production
 # HINDSIGHT_API_OTEL_DEPLOYMENT_ENVIRONMENT=production
+
+# -----------------------------------------------------------------------------
+# Control Plane (Optional)
+# -----------------------------------------------------------------------------
+
+# Dataplane API URL - where the CP proxies requests to
+# HINDSIGHT_CP_DATAPLANE_API_URL=http://localhost:8888
+
+# Optional: Require a shared access key to view the Control Plane UI.
+# When set, visitors see a login page and must enter the key before
+# accessing the dashboard or any /api/* routes (except /api/health).
+# HINDSIGHT_CP_ACCESS_KEY=your-shared-secret-key

.github/workflows/perf-test.yml

@@ -22,11 +22,13 @@ on:
           - ""
           - retain
           - recall
+          - recall-with-observations
+          - consolidation
+        default: ""
+      locomo_conversations:
+        description: "LoComo conversation IDs (space-separated). Blank = curated set (conv-26 conv-30 conv-43)."
+        type: string
         default: ""
-      locomo_max_conversations:
-        description: "LoComo max conversations (0 = skip, blank = all)"
-        type: number
-        default: 0
       locomo_skip:
         description: "Skip LoComo job"
         type: boolean
@@ -81,7 +83,7 @@ jobs:
       run: |
         cd hindsight-dev && uv sync --frozen --all-extras --index-strategy unsafe-best-match
 
-    - name: Run perf tests
+    - name: Run perf-test
       run: |
         SUITE_ARG=""
         if [ -n "${{ inputs.suite }}" ]; then
@@ -94,12 +96,23 @@ jobs:
 
     - name: Upload perf results
       if: always()
-      uses: actions/upload-artifact@v4
+      uses: actions/upload-artifact@v7
       with:
         name: perf-results-${{ github.sha }}
         path: hindsight-dev/perf-results.json
         retention-days: 90
 
+    # Publish enriched results (perf JSON + commit metadata) to the dashboard
+    # repo's gh-pages branch. The static site at
+    #   https://vectorize-io.github.io/hindsight-continuous-performance-monitor/
+    # reads data/index.json + data/<run>.json and renders charts client-side.
+    - name: Publish to dashboard
+      if: github.event_name == 'schedule' || github.event_name == 'push' || github.event_name == 'workflow_dispatch'
+      env:
+        PERF_DASHBOARD_TOKEN: ${{ secrets.PERF_DASHBOARD_TOKEN }}
+        GH_TOKEN: ${{ secrets.GITHUB_TOKEN }}
+      run: ./scripts/benchmarks/publish-perf-results.sh hindsight-dev/perf-results.json
+
   locomo:
     if: inputs.locomo_skip != true
     runs-on: ubuntu-latest
@@ -110,7 +123,7 @@ jobs:
       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-3.1-pro-preview
+      HINDSIGHT_API_ANSWER_LLM_MODEL: google/gemini-2.5-flash
     steps:
     - uses: actions/checkout@v6
       with:
@@ -156,19 +169,32 @@ jobs:
         cd hindsight-dev && uv sync --frozen --all-extras --index-strategy unsafe-best-match
 
     - name: Run LoComo benchmark
+      # Curated 3-conversation subset (best/middle/worst by accuracy on the
+      # last successful full run): conv-26 (best), conv-30 (middle), conv-43
+      # (worst). Excludes conv-44, the bank with the largest unconsolidated
+      # set that has been pushing scheduled runs over the per-bank
+      # _wait_for_consolidation timeout. Override via workflow_dispatch with
+      # the locomo_conversations input.
       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 }}"
+        CONVERSATIONS="${{ inputs.locomo_conversations }}"
+        if [ -z "$CONVERSATIONS" ]; then
+          CONVERSATIONS="conv-26 conv-30 conv-43"
         fi
         uv run python hindsight-dev/benchmarks/locomo/locomo_benchmark.py \
           --wait-consolidation \
-          $MAX_CONV_ARG
+          --conversation $CONVERSATIONS
 
     - name: Upload LoComo results
       if: always()
-      uses: actions/upload-artifact@v4
+      uses: actions/upload-artifact@v7
       with:
         name: locomo-results-${{ github.sha }}
         path: hindsight-dev/benchmarks/locomo/results/
         retention-days: 90
+
+    - name: Publish LoComo to dashboard
+      if: success() && (github.event_name == 'schedule' || github.event_name == 'push' || github.event_name == 'workflow_dispatch')
+      env:
+        PERF_DASHBOARD_TOKEN: ${{ secrets.PERF_DASHBOARD_TOKEN }}
+        GH_TOKEN: ${{ secrets.GITHUB_TOKEN }}
+      run: ./scripts/benchmarks/publish-locomo-results.sh hindsight-dev/benchmarks/locomo/results/benchmark_results.json

.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
@@ -106,7 +117,7 @@ jobs:
       working-directory: ./hindsight-integrations/${{ steps.info.outputs.integration }}
       run: |
         set +e
-        OUTPUT=$(npm publish --access public 2>&1)
+        OUTPUT=$(npm publish --access public --provenance 2>&1)
         EXIT_CODE=$?
         echo "$OUTPUT"
         if [ $EXIT_CODE -ne 0 ]; then

.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

@@ -314,6 +314,7 @@ jobs:
     permissions:
       contents: read
       packages: write
+      id-token: write
     strategy:
       matrix:
         include:
@@ -410,6 +411,7 @@ jobs:
 
     # Build multi-platform and push to release tags
     - name: Build and push release images
+      id: build
       uses: docker/build-push-action@v7
       with:
         context: .
@@ -421,6 +423,31 @@ jobs:
         tags: ${{ steps.meta.outputs.tags }}
         labels: ${{ steps.meta.outputs.labels }}
 
+    - name: Install cosign
+      uses: sigstore/cosign-installer@v3
+
+    - name: Sign published images
+      env:
+        TAGS: ${{ steps.meta.outputs.tags }}
+        DIGEST: ${{ steps.build.outputs.digest }}
+      run: |
+        set -euo pipefail
+        refs=()
+        while IFS= read -r tag; do
+          [[ -z "${tag}" ]] && continue
+          refs+=("${tag}@${DIGEST}")
+        done <<< "${TAGS}"
+        cosign sign --yes "${refs[@]}"
+
+    - name: Verify signature on primary tag
+      env:
+        IMAGE: ghcr.io/${{ github.repository_owner }}/${{ matrix.image_name }}
+        DIGEST: ${{ steps.build.outputs.digest }}
+      run: |
+        cosign verify "${IMAGE}@${DIGEST}" \
+          --certificate-identity-regexp "^https://github\.com/${{ github.repository }}/\.github/workflows/release\.yml@.*" \
+          --certificate-oidc-issuer https://token.actions.githubusercontent.com
+
   release-helm-chart:
     runs-on: ubuntu-latest
     permissions:
@@ -497,6 +524,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 +566,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

.github/workflows/sign-images.yml

@@ -0,0 +1,71 @@
+name: Sign published images
+
+on:
+  workflow_dispatch:
+    inputs:
+      version:
+        description: 'Version to sign (without leading v, e.g. 0.6.0)'
+        required: true
+        type: string
+        default: '0.6.0'
+
+permissions:
+  contents: read
+  packages: write
+  id-token: write
+
+jobs:
+  sign:
+    name: Sign ${{ matrix.image }}:${{ inputs.version }}${{ matrix.suffix }}
+    runs-on: ubuntu-latest
+    strategy:
+      fail-fast: false
+      matrix:
+        include:
+          - { image: hindsight-api, suffix: '' }
+          - { image: hindsight-api, suffix: '-slim' }
+          - { image: hindsight-control-plane, suffix: '' }
+          - { image: hindsight, suffix: '' }
+          - { image: hindsight, suffix: '-slim' }
+
+    steps:
+      - name: Set up Docker Buildx
+        uses: docker/setup-buildx-action@v4
+
+      - name: Log in to GHCR
+        uses: docker/login-action@v4
+        with:
+          registry: ghcr.io
+          username: ${{ github.actor }}
+          password: ${{ secrets.GITHUB_TOKEN }}
+
+      - name: Install cosign
+        uses: sigstore/cosign-installer@v3
+
+      - name: Resolve image digest
+        id: resolve
+        env:
+          IMAGE: ghcr.io/${{ github.repository_owner }}/${{ matrix.image }}
+          TAG: ${{ inputs.version }}${{ matrix.suffix }}
+        run: |
+          set -euo pipefail
+          DIGEST=$(docker buildx imagetools inspect "${IMAGE}:${TAG}" --format '{{json .Manifest.Digest}}' | tr -d '"')
+          if [[ -z "${DIGEST}" || "${DIGEST}" != sha256:* ]]; then
+            echo "Failed to resolve digest for ${IMAGE}:${TAG} (got: ${DIGEST})" >&2
+            exit 1
+          fi
+          echo "Resolved ${IMAGE}:${TAG} -> ${DIGEST}"
+          echo "ref=${IMAGE}@${DIGEST}" >> "$GITHUB_OUTPUT"
+
+      - name: Sign image
+        env:
+          REF: ${{ steps.resolve.outputs.ref }}
+        run: cosign sign --yes "${REF}"
+
+      - name: Verify signature
+        env:
+          REF: ${{ steps.resolve.outputs.ref }}
+        run: |
+          cosign verify "${REF}" \
+            --certificate-identity-regexp "^https://github\.com/${{ github.repository }}/\.github/workflows/sign-images\.yml@.*" \
+            --certificate-oidc-issuer https://token.actions.githubusercontent.com

CLAUDE.md

@@ -123,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
 
@@ -139,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

@@ -30,7 +30,7 @@ It eliminates the shortcomings of alternative techniques such as RAG and knowled
 
 Hindsight is the most accurate agent memory system ever tested according to benchmark performance. It has achieved state-of-the-art performance on the LongMemEval benchmark, widely used to assess memory system performance across a variety of conversational AI scenarios. The current reported performance of Hindsight and other agent memory solutions as of January 2026 is shown here:
 
-![Overview](./hindsight-docs/static/img/hindsight-bench.jpg)
+![Overview](./hindsight-docs/static/img/hindsight-benchmarks.png)
 
 The benchmark performance data for Hindsight has been independently reproduced by research collaborators at the Virginia Tech [Sanghani Center for Artificial Intelligence and Data Analytics](https://sanghani.cs.vt.edu/) and The Washington Post. Other scores are self-reported by software vendors.
 
@@ -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"
+          ]
+        }
       }
     }
   }

docker/docker-compose/alloydb/docker-compose.yaml

@@ -0,0 +1,90 @@
+name: hindsight
+# Docker Compose file for Hindsight with AlloyDB Omni and ScaNN
+# Uses Google's free AlloyDB Omni container image: https://hub.docker.com/r/google/alloydbomni
+#
+# Usage:
+# docker compose -f docker/docker-compose/alloydb/docker-compose.yaml up -d
+#
+# Make sure to set the required environment variables before running:
+# - HINDSIGHT_DB_PASSWORD: password for the AlloyDB Omni/PostgreSQL user
+# - Configure LLM provider variables as needed (see the hindsight service below)
+#
+# Optional environment variables with defaults:
+# - HINDSIGHT_VERSION: Hindsight application version (default: latest)
+# - HINDSIGHT_DB_VERSION: AlloyDB Omni image tag (default: 17)
+# - HINDSIGHT_DB_USER: database user (default: hindsight_user)
+# - HINDSIGHT_DB_NAME: database name (default: hindsight_db)
+
+services:
+  db:
+    image: google/alloydbomni:${HINDSIGHT_DB_VERSION:-17}
+    container_name: hindsight-db-alloydb
+    restart: always
+    ports:
+      - "5438:5432"
+    environment:
+      POSTGRES_USER: ${HINDSIGHT_DB_USER:-hindsight_user}
+      POSTGRES_PASSWORD: ${HINDSIGHT_DB_PASSWORD:-hindsight_password}
+      POSTGRES_DB: ${HINDSIGHT_DB_NAME:-hindsight_db}
+    volumes:
+      - alloydb_data:/var/lib/postgresql/data
+    networks:
+      - hindsight-net
+
+  alloydb-init:
+    image: google/alloydbomni:${HINDSIGHT_DB_VERSION:-17}
+    depends_on:
+      - db
+    environment:
+      - PGPASSWORD=${HINDSIGHT_DB_PASSWORD:-hindsight_password}
+    command:
+      - bash
+      - -c
+      - |
+        echo 'Waiting for AlloyDB Omni to be ready...'
+        until pg_isready -h hindsight-db-alloydb -p 5432 -U ${HINDSIGHT_DB_USER:-hindsight_user}; do
+          echo 'AlloyDB Omni is unavailable - sleeping'
+          sleep 2
+        done
+        echo 'AlloyDB Omni is ready - creating ${HINDSIGHT_DB_NAME:-hindsight_db} database'
+        psql -h hindsight-db-alloydb -p 5432 -U ${HINDSIGHT_DB_USER:-hindsight_user} -c 'CREATE DATABASE ${HINDSIGHT_DB_NAME:-hindsight_db};' 2>/dev/null || echo 'Database already exists'
+        echo 'Creating vector and alloydb_scann extensions'
+        psql -h hindsight-db-alloydb -p 5432 -U ${HINDSIGHT_DB_USER:-hindsight_user} -d ${HINDSIGHT_DB_NAME:-hindsight_db} -c 'CREATE EXTENSION IF NOT EXISTS vector;'
+        psql -h hindsight-db-alloydb -p 5432 -U ${HINDSIGHT_DB_USER:-hindsight_user} -d ${HINDSIGHT_DB_NAME:-hindsight_db} -c 'CREATE EXTENSION IF NOT EXISTS alloydb_scann CASCADE;'
+        echo 'Database and extensions created successfully'
+    restart: "no"
+    networks:
+      - hindsight-net
+
+  hindsight:
+    image: ghcr.io/vectorize-io/hindsight:${HINDSIGHT_VERSION:-latest}
+    container_name: hindsight-app
+    ports:
+      - "8888:8888"
+      - "9999:9999"
+    environment:
+      # LLM Configuration
+      HINDSIGHT_API_LLM_PROVIDER: ${HINDSIGHT_API_LLM_PROVIDER:-openai}
+      HINDSIGHT_API_LLM_API_KEY: ${OPENAI_API_KEY:-your-api-key}
+
+      # Database Configuration
+      HINDSIGHT_API_DATABASE_URL: postgresql://${HINDSIGHT_DB_USER:-hindsight_user}:${HINDSIGHT_DB_PASSWORD:-hindsight_password}@db:5432/${HINDSIGHT_DB_NAME:-hindsight_db}
+
+      # Vector and Text Search Extensions
+      HINDSIGHT_API_VECTOR_EXTENSION: scann
+      HINDSIGHT_API_TEXT_SEARCH_EXTENSION: native
+
+    depends_on:
+      db:
+        condition: service_started
+      alloydb-init:
+        condition: service_completed_successfully
+    networks:
+      - hindsight-net
+
+networks:
+  hindsight-net:
+    driver: bridge
+
+volumes:
+  alloydb_data:

docker/docker-compose/custom-models/Dockerfile

@@ -0,0 +1,34 @@
+# Example: custom Hindsight image with non-default local models baked in.
+#
+# Use this pattern in production when you run a non-default embedder or
+# reranker. Baking models into the image removes the runtime dependency on
+# HuggingFace and lets the container registry handle caching per node, so
+# you don't need a model-cache PVC.
+#
+# Built on top of the slim image so only the deps and models you actually
+# use end up in the final image.
+FROM ghcr.io/vectorize-io/hindsight:latest-slim
+
+# Install the local-ml deps required to load sentence-transformers /
+# cross-encoder models at runtime. Pinned ranges mirror hindsight-api-slim's
+# `local-ml` extra in hindsight-api-slim/pyproject.toml. Use `uv pip
+# install` against the image's venv explicitly: the slim image's venv was
+# created by `uv sync` and does not ship its own `pip`, so a bare
+# `pip install` would fall back to user site-packages and not be visible
+# to the runtime python.
+RUN uv pip install --python /app/api/.venv/bin/python --no-cache \
+    'sentence-transformers>=3.3.0' \
+    'transformers>=4.53.0' \
+    'torch>=2.6.0'
+
+# Pre-download the models you want to use. Replace these with your own.
+# The defaults bundled in the full image are BAAI/bge-small-en-v1.5 and
+# cross-encoder/ms-marco-MiniLM-L-6-v2; here we pick multilingual variants
+# as a concrete non-default example.
+ARG EMBEDDER=sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2
+ARG RERANKER=cross-encoder/mmarco-mMiniLMv2-L12-H384-v1
+ENV HF_HUB_DOWNLOAD_TIMEOUT=600
+RUN python -c "\
+from sentence_transformers import SentenceTransformer, CrossEncoder; \
+SentenceTransformer('${EMBEDDER}'); \
+CrossEncoder('${RERANKER}')"

docker/docker-compose/custom-models/README.md

@@ -0,0 +1,81 @@
+# Hindsight with Custom Local Models
+
+Example Docker Compose setup that builds a Hindsight image with **non-default
+local embedder and reranker models baked in at build time**.
+
+This is the recommended pattern for production when you use a non-default
+local model: the container registry caches model layers per node, pod
+startup is deterministic, and you don't need a model-cache PVC (or any
+runtime dependency on HuggingFace).
+
+## When to use this
+
+- You override `HINDSIGHT_API_EMBEDDINGS_LOCAL_MODEL` or
+  `HINDSIGHT_API_RERANKER_LOCAL_MODEL` to a non-default model.
+- You want pod startup to be deterministic and offline-capable.
+- You'd otherwise reach for a Helm `modelCache` PVC just to avoid
+  re-downloading models.
+
+If you're using the **default** local models, the published full image
+(`ghcr.io/vectorize-io/hindsight:latest`) already bakes them in — you don't
+need this example.
+
+If you're using **external** providers (TEI, OpenAI, Cohere, ...) for
+embeddings and reranking, use the slim image directly — no models are
+needed in the image.
+
+## Quick start
+
+```bash
+export OPENAI_API_KEY=sk-xxx
+
+docker compose -f docker/docker-compose/custom-models/docker-compose.yaml up --build
+```
+
+- API: http://localhost:8888
+- Control Plane: http://localhost:9999
+
+## Using your own models
+
+Override the build args to bake different models:
+
+```bash
+docker compose -f docker/docker-compose/custom-models/docker-compose.yaml build \
+  --build-arg EMBEDDER=your-org/your-embedder \
+  --build-arg RERANKER=your-org/your-reranker
+```
+
+Then update the matching `HINDSIGHT_API_EMBEDDINGS_LOCAL_MODEL` and
+`HINDSIGHT_API_RERANKER_LOCAL_MODEL` values in `docker-compose.yaml` so the
+runtime points at the same model IDs.
+
+## Verifying the models are baked in
+
+`docker-compose.yaml` sets `HF_HUB_OFFLINE=1` and `TRANSFORMERS_OFFLINE=1`
+so that any attempt to download a model at runtime fails loudly instead of
+silently re-downloading. If the container starts and serves recall queries
+with these set, the models are correctly baked in.
+
+You can also inspect the image directly:
+
+```bash
+docker run --rm --entrypoint sh hindsight-custom-models-hindsight \
+  -c 'ls ~/.cache/huggingface/hub/'
+```
+
+## Why not a model-cache PVC?
+
+The Helm chart exposes an optional `api.persistence.modelCache` PVC for
+caching downloaded models across pod restarts. Compared to baking models
+into the image:
+
+- A PVC adds storage cost — one PVC per worker replica with
+  `volumeClaimTemplates`.
+- `ReadWriteOnce` (the default) pins pods to a node.
+- The PVC needs lifecycle management on `helm uninstall` / `helm upgrade`
+  — without `helm.sh/resource-policy: keep` it is deleted on uninstall;
+  with it, storage keeps billing forever until manually cleaned up.
+- Pod startup still depends on HuggingFace being reachable on first run.
+
+Image layers, by contrast, are pulled once per node and cached for free by
+the container runtime, with no orphaned-storage cleanup story.

docker/docker-compose/custom-models/docker-compose.yaml

@@ -0,0 +1,44 @@
+name: hindsight-custom-models
+# Example: run a custom Hindsight image with non-default local models baked
+# in at build time, so pod startup does not depend on HuggingFace at runtime.
+#
+# Quick start:
+#   export OPENAI_API_KEY=sk-xxx
+#   docker compose -f docker/docker-compose/custom-models/docker-compose.yaml up --build
+#
+# Required environment variables:
+# - OPENAI_API_KEY (or configure another LLM provider via HINDSIGHT_API_LLM_*)
+
+services:
+  hindsight:
+    build:
+      context: .
+      dockerfile: Dockerfile
+      # Override at build time to bake different models:
+      #   docker compose build --build-arg EMBEDDER=your-org/your-embedder
+      args:
+        EMBEDDER: sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2
+        RERANKER: cross-encoder/mmarco-mMiniLMv2-L12-H384-v1
+    container_name: hindsight-custom-models
+    ports:
+      - "8888:8888"
+      - "9999:9999"
+    environment:
+      HINDSIGHT_API_LLM_PROVIDER: ${HINDSIGHT_API_LLM_PROVIDER:-openai}
+      HINDSIGHT_API_LLM_API_KEY: ${OPENAI_API_KEY:-your-api-key}
+
+      # Point Hindsight at the models baked into the image above.
+      HINDSIGHT_API_EMBEDDINGS_PROVIDER: local
+      HINDSIGHT_API_EMBEDDINGS_LOCAL_MODEL: sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2
+      HINDSIGHT_API_RERANKER_PROVIDER: local
+      HINDSIGHT_API_RERANKER_LOCAL_MODEL: cross-encoder/mmarco-mMiniLMv2-L12-H384-v1
+
+      # Fail fast if a model is missing from the image instead of silently
+      # falling back to a HuggingFace download at runtime.
+      HF_HUB_OFFLINE: "1"
+      TRANSFORMERS_OFFLINE: "1"
+    volumes:
+      - pg_data:/home/hindsight/.pg0
+
+volumes:
+  pg_data:

docker/docker-compose/nginx/docker-compose.yml

@@ -51,6 +51,8 @@ services:
 
       # Control Plane config
       HINDSIGHT_CP_DATAPLANE_API_URL: http://localhost:8888
+      # Optional: Require a shared access key for Control Plane UI access
+      # HINDSIGHT_CP_ACCESS_KEY: your-secret-key
     volumes:
       # Persist embedded pg0 database
       - hindsight_data:/app/data

docker/standalone/Dockerfile

@@ -172,6 +172,10 @@ USER hindsight
 # "Permission denied" error when mounting a fresh root-owned volume.
 RUN mkdir -p /home/hindsight/.pg0
 
+# Make /home/hindsight traversable when running with --user UID:GID overrides
+# (default 0700 blocks traversal by non-owner UIDs needed for bind-mount ownership matching)
+RUN chmod 755 /home/hindsight
+
 ENV PATH="/app/api/.venv/bin:${PATH}"
 
 # Pre-download tiktoken encoding (ALWAYS - required for token counting even in air-gapped envs)
@@ -328,6 +332,10 @@ USER hindsight
 # "Permission denied" error when mounting a fresh root-owned volume.
 RUN mkdir -p /home/hindsight/.pg0
 
+# Make /home/hindsight traversable when running with --user UID:GID overrides
+# (default 0700 blocks traversal by non-owner UIDs needed for bind-mount ownership matching)
+RUN chmod 755 /home/hindsight
+
 ENV PATH="/app/api/.venv/bin:${PATH}"
 
 # Pre-download tiktoken encoding (ALWAYS - required for token counting even in air-gapped envs)

helm/hindsight/Chart.yaml

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

helm/hindsight/values.yaml

@@ -70,6 +70,12 @@ api:
   # Persistent volume for local model cache (reranker, embeddings)
   # Models are downloaded to /home/hindsight/.cache on first use.
   # Without persistence, models are re-downloaded on every pod restart.
+  #
+  # For production, prefer baking models into a custom image instead of
+  # enabling this PVC: image layers are pulled once per node and cached
+  # for free, while a PVC adds storage cost, pins pods to a node
+  # (ReadWriteOnce), and needs lifecycle management on uninstall/upgrade.
+  # See docs: developer/installation#bundling-custom-models-in-a-custom-image
   persistence:
     modelCache:
       enabled: false
@@ -168,7 +174,10 @@ worker:
   # affinity: {}
 
   # Persistent volume for local model cache (reranker, embeddings)
-  # Uses volumeClaimTemplates since worker is a StatefulSet.
+  # Uses volumeClaimTemplates since worker is a StatefulSet — one PVC per
+  # replica. For production, prefer baking models into a custom image; see
+  # api.persistence.modelCache above and docs:
+  # developer/installation#bundling-custom-models-in-a-custom-image
   persistence:
     modelCache:
       enabled: false

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.4",
+  "version": "0.6.2",
   "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,12 +4,12 @@ build-backend = "setuptools.build_meta"
 
 [project]
 name = "hindsight-all-slim"
-version = "0.5.4"
+version = "0.6.2"
 description = "Hindsight: Agent Memory That Works Like Human Memory - Slim All-in-One Bundle"
 readme = "README.md"
 requires-python = ">=3.11"
 dependencies = [
-    "hindsight-api-slim>=0.4.17",
+    "hindsight-api-slim==0.6.2",
     "hindsight-client>=0.0.7",
     "hindsight-embed>=0.1.0",
 ]

hindsight-all/pyproject.toml

@@ -4,12 +4,12 @@ build-backend = "hatchling.build"
 
 [project]
 name = "hindsight-all"
-version = "0.5.4"
+version = "0.6.2"
 description = "Hindsight: Agent Memory That Works Like Human Memory - All-in-One Bundle"
 readme = "README.md"
 requires-python = ">=3.11"
 dependencies = [
-    "hindsight-api-slim[all]>=0.4.17",
+    "hindsight-api-slim[all]==0.6.2",
     "hindsight-client>=0.0.7",
     "hindsight-embed>=0.1.0",
 ]
@@ -21,7 +21,7 @@ hindsight-embed = { workspace = true }
 
 [project.optional-dependencies]
 local-llm = [
-    "hindsight-api-slim[local-llm]>=0.4.17",
+    "hindsight-api-slim[local-llm]==0.6.2",
 ]
 test = [
     "pytest>=7.0.0",

hindsight-api-slim/hindsight_api/__init__.py

@@ -46,4 +46,4 @@ __all__ = [
     "RemoteTEICrossEncoder",
     "LLMConfig",
 ]
-__version__ = "0.5.4"
+__version__ = "0.6.2"

hindsight-api-slim/hindsight_api/_vector_index.py

@@ -0,0 +1,170 @@
+"""Shared PostgreSQL vector-extension dispatch helpers."""
+
+from __future__ import annotations
+
+import logging
+
+from sqlalchemy import text
+from sqlalchemy.engine import Connection
+
+logger = logging.getLogger(__name__)
+
+# Extensions a user can set via HINDSIGHT_API_VECTOR_EXTENSION.
+CONFIGURABLE_EXTENSIONS = ("pgvector", "pgvectorscale", "vchord", "scann")
+
+# Extensions detect_vector_extension() can return. pg_diskann is a runtime-only
+# resolution from a configured "pgvectorscale" backend on Azure (uses a different
+# WITH clause), never a value the user sets directly.
+RESOLVED_EXTENSIONS = (*CONFIGURABLE_EXTENSIONS, "pg_diskann")
+
+# Backwards-compatible alias for older imports.
+VALID_EXTENSIONS = CONFIGURABLE_EXTENSIONS
+
+SCANN_MIN_ROWS_FOR_AUTO_INDEX = 10_000
+
+
+_EXTENSION_NAMES = {
+    "pgvector": "vector",
+    "pgvectorscale": "vectorscale",
+    "vchord": "vchord",
+    "scann": "alloydb_scann",
+}
+
+_INDEX_USING_CLAUSES = {
+    "pgvector": "USING hnsw (embedding vector_cosine_ops)",
+    "pgvectorscale": "USING diskann (embedding vector_cosine_ops) WITH (num_neighbors = 50)",
+    "pg_diskann": "USING diskann (embedding vector_cosine_ops) WITH (max_neighbors = 50)",
+    "vchord": "USING vchordrq (embedding vector_l2_ops)",
+    "scann": "USING scann (embedding cosine) WITH (mode = 'AUTO')",
+}
+
+_INDEX_TYPE_KEYWORDS = {
+    "pgvector": "hnsw",
+    "pgvectorscale": "diskann",
+    "pg_diskann": "diskann",
+    "vchord": "vchordrq",
+    "scann": "scann",
+}
+
+_EXTENSION_INSTALL_SQL = {
+    "pgvector": ("CREATE EXTENSION IF NOT EXISTS vector",),
+    "pgvectorscale": (
+        "CREATE EXTENSION IF NOT EXISTS vector",
+        "CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE",
+    ),
+    "vchord": ("CREATE EXTENSION IF NOT EXISTS vchord CASCADE",),
+    "scann": (
+        "CREATE EXTENSION IF NOT EXISTS vector",
+        "CREATE EXTENSION IF NOT EXISTS alloydb_scann CASCADE",
+    ),
+}
+
+_INSTALL_HINTS = {
+    "pgvector": "CREATE EXTENSION vector;",
+    "pgvectorscale": "CREATE EXTENSION vector; then CREATE EXTENSION vectorscale CASCADE; (or pg_diskann on Azure)",
+    "vchord": "CREATE EXTENSION vchord CASCADE;",
+    "scann": "CREATE EXTENSION vector; then CREATE EXTENSION alloydb_scann CASCADE;",
+}
+
+
+def validate_extension(name: str) -> str:
+    """Return a normalized configurable vector extension name or raise.
+
+    Used at the user-facing config boundary; pg_diskann is rejected here because
+    it is a detection-time alias, never a value the user sets directly.
+    """
+    ext = name.lower()
+    if ext not in CONFIGURABLE_EXTENSIONS:
+        valid = ", ".join(CONFIGURABLE_EXTENSIONS)
+        raise ValueError(f"Invalid vector_extension: {name}. Must be one of: {valid}")
+    return ext
+
+
+def _normalize_resolved(name: str) -> str:
+    """Normalize either a user-configurable or detect-time extension name."""
+    ext = name.lower()
+    if ext not in RESOLVED_EXTENSIONS:
+        valid = ", ".join(RESOLVED_EXTENSIONS)
+        raise ValueError(f"Unknown vector extension: {name}. Must be one of: {valid}")
+    return ext
+
+
+def pg_extension_name(ext: str) -> str:
+    """Return the PostgreSQL extension name for a configured vector backend."""
+    return _EXTENSION_NAMES[validate_extension(ext)]
+
+
+def index_using_clause(ext: str) -> str:
+    """Return the CREATE INDEX USING clause for the vector backend."""
+    return _INDEX_USING_CLAUSES[_normalize_resolved(ext)]
+
+
+def index_type_keyword(ext: str) -> str:
+    """Return the keyword that identifies this index type in pg_indexes.indexdef."""
+    return _INDEX_TYPE_KEYWORDS[_normalize_resolved(ext)]
+
+
+def minimum_rows_for_index(ext: str) -> int:
+    """Return the minimum populated embedding rows before creating this index type."""
+    return SCANN_MIN_ROWS_FOR_AUTO_INDEX if _normalize_resolved(ext) == "scann" else 0
+
+
+def should_defer_index_creation(ext: str, row_count: int) -> bool:
+    """Return True when index creation should wait for more embeddings."""
+    minimum_rows = minimum_rows_for_index(ext)
+    return minimum_rows > 0 and row_count < minimum_rows
+
+
+def uses_per_bank_vector_indexes(ext: str) -> bool:
+    """Return whether the backend should create per-bank partial vector indexes."""
+    return _normalize_resolved(ext) != "scann"
+
+
+def bootstrap_extension(conn: Connection, ext: str) -> None:
+    """Install the configured vector extension and any prerequisites if possible."""
+    normalized = validate_extension(ext)
+    for statement in _EXTENSION_INSTALL_SQL[normalized]:
+        conn.execute(text(statement))
+
+
+def detect_vector_extension(conn: Connection, vector_extension: str = "pgvector") -> str:
+    """Validate the configured vector extension exists and return the index backend."""
+    configured_ext = validate_extension(vector_extension)
+
+    if configured_ext == "pgvectorscale":
+        pgvector_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vector'")).scalar()
+        if not pgvector_check:
+            raise RuntimeError(
+                "DiskANN (pgvectorscale/pg_diskann) requires pgvector to be installed. "
+                f"Install it with: {_INSTALL_HINTS['pgvectorscale']}"
+            )
+
+        vectorscale_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vectorscale'")).scalar()
+        pg_diskann_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'pg_diskann'")).scalar()
+
+        if vectorscale_check:
+            logger.debug("Using vector extension: pgvectorscale (DiskANN)")
+            return "pgvectorscale"
+        if pg_diskann_check:
+            logger.debug("Using vector extension: pg_diskann (Azure DiskANN)")
+            return "pg_diskann"
+
+        raise RuntimeError(
+            "Configured vector extension 'pgvectorscale' not found. Install either:\n"
+            "  - pgvectorscale (open source): CREATE EXTENSION vectorscale CASCADE;\n"
+            "  - pg_diskann (Azure): CREATE EXTENSION pg_diskann CASCADE;"
+        )
+
+    extension_name = pg_extension_name(configured_ext)
+    extension_check = conn.execute(
+        text("SELECT 1 FROM pg_extension WHERE extname = :extension_name"),
+        {"extension_name": extension_name},
+    ).scalar()
+    if not extension_check:
+        raise RuntimeError(
+            f"Configured vector extension '{configured_ext}' not found. "
+            f"Install it with: {_INSTALL_HINTS[configured_ext]}"
+        )
+
+    logger.debug("Using configured vector extension: %s", configured_ext)
+    return configured_ext

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,

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

@@ -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 = "2eee35aa3cfc"
 down_revision: str | Sequence[str] | None = "d6e7f8a9b0c1"
 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()
     # 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
@@ -24,52 +26,67 @@ depends_on: str | Sequence[str] | None = None
 
 def _detect_vector_extension() -> str:
     """
-    Detect or validate vector extension: 'pgvector', 'vchord', or 'pgvectorscale'.
+    Detect or validate vector extension for this immutable migration revision.
     Respects HINDSIGHT_API_VECTOR_EXTENSION env var if set.
     """
     conn = op.get_bind()
     vector_extension = os.getenv("HINDSIGHT_API_VECTOR_EXTENSION", "pgvector").lower()
 
-    # Validate configured extension is installed
     if vector_extension == "pgvectorscale":
-        # pgvectorscale/DiskANN requires pgvector
         pgvector_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vector'")).scalar()
         if not pgvector_check:
             raise RuntimeError(
                 "DiskANN requires pgvector. Install with: CREATE EXTENSION vector; then vectorscale or pg_diskann CASCADE;"
             )
-        # Check for either vectorscale (open source) or pg_diskann (Azure)
         vectorscale_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vectorscale'")).scalar()
         pg_diskann_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'pg_diskann'")).scalar()
 
         if vectorscale_check:
             return "pgvectorscale"
-        elif pg_diskann_check:
+        if pg_diskann_check:
             return "pg_diskann"
-        else:
-            raise RuntimeError(
-                "Configured vector extension 'pgvectorscale' not found. Install either:\n"
-                "  - pgvectorscale: CREATE EXTENSION vectorscale CASCADE;\n"
-                "  - pg_diskann (Azure): CREATE EXTENSION pg_diskann CASCADE;"
-            )
-    elif vector_extension == "vchord":
+        raise RuntimeError(
+            "Configured vector extension 'pgvectorscale' not found. Install either:\n"
+            "  - pgvectorscale: CREATE EXTENSION vectorscale CASCADE;\n"
+            "  - pg_diskann (Azure): CREATE EXTENSION pg_diskann CASCADE;"
+        )
+    if vector_extension == "vchord":
         vchord_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vchord'")).scalar()
         if not vchord_check:
             raise RuntimeError(
                 "Configured vector extension 'vchord' not found. Install it with: CREATE EXTENSION vchord CASCADE;"
             )
         return "vchord"
-    elif vector_extension == "pgvector":
+    if vector_extension == "scann":
+        scann_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'alloydb_scann'")).scalar()
+        if not scann_check:
+            raise RuntimeError(
+                "Configured vector extension 'scann' not found. Install it with: CREATE EXTENSION alloydb_scann CASCADE;"
+            )
+        return "scann"
+    if vector_extension == "pgvector":
         pgvector_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vector'")).scalar()
         if not pgvector_check:
             raise RuntimeError(
                 "Configured vector extension 'pgvector' not found. Install it with: CREATE EXTENSION vector;"
             )
         return "pgvector"
-    else:
-        raise ValueError(
-            f"Invalid HINDSIGHT_API_VECTOR_EXTENSION: {vector_extension}. Must be 'pgvector', 'vchord', or 'pgvectorscale'"
-        )
+    raise ValueError(
+        "Invalid HINDSIGHT_API_VECTOR_EXTENSION: "
+        f"{vector_extension}. Must be 'pgvector', 'vchord', 'pgvectorscale', or 'scann'"
+    )
+
+
+def _vector_index_using_clause(ext: str) -> str:
+    if ext == "pgvectorscale":
+        return "USING diskann (embedding vector_cosine_ops) WITH (num_neighbors = 50)"
+    if ext == "pg_diskann":
+        return "USING diskann (embedding vector_cosine_ops) WITH (max_neighbors = 50)"
+    if ext == "vchord":
+        return "USING vchordrq (embedding vector_l2_ops)"
+    if ext == "scann":
+        return "USING scann (embedding cosine) WITH (mode = 'AUTO')"
+    return "USING hnsw (embedding vector_cosine_ops)"
 
 
 def _detect_text_search_extension() -> str:
@@ -112,7 +129,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
@@ -311,36 +328,11 @@ def upgrade() -> None:
     )
     # Create vector index - conditional based on available extension
     vector_ext = _detect_vector_extension()
-
-    if vector_ext == "pgvectorscale":
-        # Use DiskANN index for pgvectorscale (disk-based, scalable)
-        op.execute("""
+    if vector_ext != "scann":
+        op.execute(f"""
             CREATE INDEX idx_memory_units_embedding ON memory_units
-            USING diskann (embedding vector_cosine_ops)
-            WITH (num_neighbors = 50)
+            {_vector_index_using_clause(vector_ext)}
         """)
-    elif vector_ext == "pg_diskann":
-        # Use DiskANN index for pg_diskann (Azure)
-        op.execute("""
-            CREATE INDEX idx_memory_units_embedding ON memory_units
-            USING diskann (embedding vector_cosine_ops)
-            WITH (max_neighbors = 50)
-        """)
-    elif vector_ext == "vchord":
-        # Use vchordrq index for vchord (supports high-dimensional embeddings)
-        op.execute("""
-            CREATE INDEX idx_memory_units_embedding ON memory_units
-            USING vchordrq (embedding vector_l2_ops)
-        """)
-    else:  # pgvector
-        # Use HNSW index for pgvector
-        op.create_index(
-            "idx_memory_units_embedding",
-            "memory_units",
-            ["embedding"],
-            postgresql_using="hnsw",
-            postgresql_ops={"embedding": "vector_cosine_ops"},
-        )
 
     # Create full-text search index on search_vector
     # Index type depends on text search backend
@@ -463,7 +455,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 +515,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/86f7a033d372_repair_mental_models_subtype_at_head.py

@@ -0,0 +1,117 @@
+"""Repair mental_models.subtype on databases stuck at m3rg3h3ad5f6
+
+Three production deployments reported `column "subtype" of relation
+"mental_models" does not exist` on `create_mental_model` even after their
+container reported `Database migrations completed successfully` and
+`alembic_version` advanced to `m3rg3h3ad5f6` (see issue #1553, #1553#1
+confirmations from @4Lienau and @khanhduyvt0101).
+
+Both `h3c4d5e6f7g8_mental_models_v4` and `d5y6z7a8b9c0_backfill_mental_models_subtype`
+were meant to ensure `subtype` exists, but on databases that came through the
+`reflections -> mental_models` rename chain *and* whose alembic_version
+advanced past `d5y6z7a8b9c0` along an alternate path during the divergent-heads
+reorganization, neither column-add actually fired. The result is a head-tagged
+database with a v3-shaped `mental_models` table missing six columns:
+``subtype``, ``description``, ``entity_id``, ``observations``, ``links``,
+``last_updated``.
+
+This migration sits at the current head (`m3rg3h3ad5f6`) so every affected
+deployment will pick it up on next container start. It mirrors the column-add
+block from `d5y6z7a8b9c0_backfill_mental_models_subtype` using
+``ADD COLUMN IF NOT EXISTS`` so it is a no-op on databases where the columns
+are already present.
+
+Revision ID: 86f7a033d372
+Revises: m3rg3h3ad5f6
+Create Date: 2026-05-14
+"""
+
+from collections.abc import Sequence
+
+from alembic import context, op
+
+from hindsight_api.alembic._dialect import run_for_dialect
+
+revision: str = "86f7a033d372"
+down_revision: str | Sequence[str] | None = "m3rg3h3ad5f6"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+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 _pg_upgrade() -> None:
+    """Idempotently ensure mental_models has the v4 column set.
+
+    Safe to re-apply on databases that already received the columns via
+    `h3c4d5e6f7g8_mental_models_v4` or `d5y6z7a8b9c0_backfill_mental_models_subtype` —
+    every column-add uses ``IF NOT EXISTS`` and the constraint is recreated
+    from scratch with the canonical v4 allowlist.
+    """
+    schema = _pg_schema_prefix()
+    bare_schema = schema.strip(".").strip('"') if schema else ""
+    schema_clause = f"AND table_schema = '{bare_schema}'" if bare_schema else ""
+
+    # Wrapped in a DO block so the existence check skips databases that
+    # predate the reflections -> mental_models rename chain (no table to
+    # repair). On those, every ALTER below would error.
+    op.execute(
+        f"""
+        DO $$
+        BEGIN
+            IF EXISTS (
+                SELECT 1 FROM information_schema.tables
+                WHERE table_name = 'mental_models'
+                  {schema_clause}
+            ) THEN
+                -- Add the six v4 columns idempotently.
+                ALTER TABLE {schema}mental_models
+                    ADD COLUMN IF NOT EXISTS subtype VARCHAR(32) NOT NULL DEFAULT 'structural';
+                ALTER TABLE {schema}mental_models
+                    ADD COLUMN IF NOT EXISTS description TEXT NOT NULL DEFAULT '';
+                ALTER TABLE {schema}mental_models
+                    ADD COLUMN IF NOT EXISTS entity_id UUID;
+                ALTER TABLE {schema}mental_models
+                    ADD COLUMN IF NOT EXISTS observations JSONB DEFAULT '{{"observations": []}}'::jsonb;
+                ALTER TABLE {schema}mental_models
+                    ADD COLUMN IF NOT EXISTS links VARCHAR[];
+                ALTER TABLE {schema}mental_models
+                    ADD COLUMN IF NOT EXISTS last_updated TIMESTAMP WITH TIME ZONE;
+
+                -- Recreate the CHECK constraint with the canonical v4 allowlist.
+                -- Existing rows with subtype = 'directive' (possible on databases
+                -- that ran the o0j1k2l3m4n5 directive-only path) are rewritten to
+                -- 'structural' first so the constraint add succeeds.
+                UPDATE {schema}mental_models SET subtype = 'structural' WHERE subtype = 'directive';
+
+                ALTER TABLE {schema}mental_models DROP CONSTRAINT IF EXISTS ck_mental_models_subtype;
+                ALTER TABLE {schema}mental_models
+                    ADD CONSTRAINT ck_mental_models_subtype
+                    CHECK (subtype IN ('structural', 'emergent', 'pinned', 'learned'));
+
+                CREATE INDEX IF NOT EXISTS idx_mental_models_subtype
+                    ON {schema}mental_models(bank_id, subtype);
+            END IF;
+        END$$;
+        """
+    )
+
+
+def _pg_downgrade() -> None:
+    """No-op: dropping these columns would corrupt v4 application code."""
+    pass
+
+
+def upgrade() -> None:
+    # PG-only: Oracle's baseline (o1a2b3c4d5e6) creates mental_models with its
+    # own subtype shape (chk_mm_subtype IN ('directive', 'pinned')) and a
+    # different table topology, so this PG-shaped repair does not apply.
+    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

@@ -26,15 +26,25 @@ 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 upgrade() -> None:
+def _pg_upgrade() -> None:
     pass
 
 
+def _pg_downgrade() -> None:
+    pass
+
+
+def upgrade() -> None:
+    run_for_dialect(pg=_pg_upgrade)
+
+
 def downgrade() -> None:
-    pass
+    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

@@ -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 = "a2v3w4x5y6z7"
 down_revision: str | Sequence[str] | None = "z1u2v3w4x5y6"
 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(f"""
         ALTER TABLE {schema}mental_models
@@ -33,6 +35,14 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+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

@@ -11,7 +11,8 @@ was configured.
 
 This migration detects the mismatch and recreates the affected indexes with
 the correct type. Skipped entirely when the configured extension is pgvector
-(the default), since those indexes are already correct.
+(the default) or scann. ScaNN uses global vector indexes because empty or tiny
+per-bank indexes cannot be built safely on AlloyDB.
 """
 
 import os
@@ -20,6 +21,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 = "a4b5c6d7e8f9"
 down_revision: str | Sequence[str] | None = "2eee35aa3cfc"
 branch_labels: str | Sequence[str] | None = None
@@ -37,39 +40,47 @@ def _get_schema_prefix() -> str:
     return f'"{schema}".' if schema else ""
 
 
-def _target_index_type() -> str | None:
-    """Return the target index type, or None if pgvector (no fix needed)."""
-    ext = os.getenv("HINDSIGHT_API_VECTOR_EXTENSION", "pgvector").lower()
+def _validate_extension(name: str) -> str:
+    ext = name.lower()
+    if ext not in {"pgvector", "pgvectorscale", "vchord", "scann"}:
+        raise ValueError(
+            f"Invalid HINDSIGHT_API_VECTOR_EXTENSION: {ext}. Must be 'pgvector', 'vchord', 'pgvectorscale', or 'scann'"
+        )
+    return ext
+
+
+def _index_type_keyword(ext: str) -> str:
     if ext == "pgvectorscale":
         return "diskann"
-    elif ext == "vchord":
+    if ext == "vchord":
         return "vchordrq"
-    return None
+    if ext == "scann":
+        return "scann"
+    return "hnsw"
 
 
-def _vector_index_using_clause() -> str:
-    """Return the USING clause based on the configured vector extension."""
-    ext = os.getenv("HINDSIGHT_API_VECTOR_EXTENSION", "pgvector").lower()
+def _vector_index_using_clause(ext: str) -> str:
     if ext == "pgvectorscale":
         return "USING diskann (embedding vector_cosine_ops) WITH (num_neighbors = 50)"
-    elif ext == "vchord":
+    if ext == "vchord":
         return "USING vchordrq (embedding vector_l2_ops)"
-    else:
-        return "USING hnsw (embedding vector_cosine_ops)"
+    if ext == "scann":
+        return "USING scann (embedding cosine) WITH (mode = 'AUTO')"
+    return "USING hnsw (embedding vector_cosine_ops)"
 
 
-def upgrade() -> None:
-    target = _target_index_type()
-    if target is None:
-        # pgvector — indexes are already HNSW, nothing to fix
+def _pg_upgrade() -> None:
+    ext = _validate_extension(os.getenv("HINDSIGHT_API_VECTOR_EXTENSION", "pgvector"))
+    if ext in {"pgvector", "scann"}:
         return
+    target = _index_type_keyword(ext)
 
     bind = op.get_bind()
     schema_name = context.config.get_main_option("target_schema")
     schema = _get_schema_prefix()
     table_ref = f'"{schema_name}".memory_units' if schema_name else "memory_units"
     banks_ref = f'"{schema_name}".banks' if schema_name else "banks"
-    using_clause = _vector_index_using_clause()
+    using_clause = _vector_index_using_clause(ext)
     pg_schema = schema_name or "public"
 
     rows = bind.execute(text(f"SELECT bank_id, internal_id FROM {banks_ref}")).fetchall()  # noqa: S608
@@ -113,10 +124,10 @@ 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:
+    ext = _validate_extension(os.getenv("HINDSIGHT_API_VECTOR_EXTENSION", "pgvector"))
+    if ext in {"pgvector", "scann"}:
         return
 
     bind = op.get_bind()
@@ -140,3 +151,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

@@ -20,6 +20,8 @@ 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
@@ -31,7 +33,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(f"""
         ALTER TABLE {schema}mental_models
@@ -39,6 +41,14 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+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

@@ -28,6 +28,8 @@ 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
@@ -39,7 +41,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"
 
@@ -61,6 +63,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/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

@@ -6,10 +6,11 @@ Create Date: 2026-03-11
 
 This migration:
 1. Adds internal_id UUID column to banks (stable identifier for index naming)
-2. Drops the global vector index (competes with per-bank partial indexes)
-3. Creates per-(bank_id, fact_type) partial vector indexes for all existing banks
-   using the configured vector extension (HNSW for pgvector, DiskANN for
-   pgvectorscale, vchordrq for vchord).
+2. For non-ScaNN backends, drops the global vector index (competes with
+   per-bank partial indexes)
+3. For non-ScaNN backends, creates per-(bank_id, fact_type) partial vector
+   indexes for all existing banks using the configured vector extension
+   (HNSW for pgvector, DiskANN for pgvectorscale, vchordrq for vchord).
    (new banks get indexes created at bank-creation time via bank_utils.create_bank_vector_indexes)
 
 Why per-(bank, fact_type) indexes:
@@ -17,6 +18,8 @@ Why per-(bank, fact_type) indexes:
   clause, because the idx_memory_units_bank_id B-tree index always wins at planning time.
 - Per-(bank, fact_type) partial indexes have both predicates matching → planner selects them.
 - The global vector index competes for larger partitions (world, observation) and must be dropped.
+- AlloyDB ScaNN uses global vector indexes with filtered vector search instead
+  because empty or tiny per-bank indexes cannot be built safely.
 """
 
 import os
@@ -25,6 +28,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
@@ -37,23 +42,31 @@ _FACT_TYPES: dict[str, str] = {
 }
 
 
+def _configured_vector_extension() -> str:
+    ext = os.getenv("HINDSIGHT_API_VECTOR_EXTENSION", "pgvector").lower()
+    if ext not in {"pgvector", "pgvectorscale", "vchord", "scann"}:
+        raise ValueError(
+            f"Invalid HINDSIGHT_API_VECTOR_EXTENSION: {ext}. Must be 'pgvector', 'vchord', 'pgvectorscale', or 'scann'"
+        )
+    return ext
+
+
+def _vector_index_using_clause(ext: str) -> str:
+    if ext == "pgvectorscale":
+        return "USING diskann (embedding vector_cosine_ops) WITH (num_neighbors = 50)"
+    if ext == "vchord":
+        return "USING vchordrq (embedding vector_l2_ops)"
+    if ext == "scann":
+        return "USING scann (embedding cosine) WITH (mode = 'AUTO')"
+    return "USING hnsw (embedding vector_cosine_ops)"
+
+
 def _get_schema_prefix() -> str:
     schema = context.config.get_main_option("target_schema")
     return f'"{schema}".' if schema else ""
 
 
-def _vector_index_using_clause() -> str:
-    """Return the USING clause based on the configured vector extension."""
-    ext = os.getenv("HINDSIGHT_API_VECTOR_EXTENSION", "pgvector").lower()
-    if ext == "pgvectorscale":
-        return "USING diskann (embedding vector_cosine_ops) WITH (num_neighbors = 50)"
-    elif ext == "vchord":
-        return "USING vchordrq (embedding vector_l2_ops)"
-    else:
-        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
@@ -62,6 +75,14 @@ def upgrade() -> None:
     )
     op.execute(f"ALTER TABLE {schema}banks ADD CONSTRAINT banks_internal_id_unique UNIQUE (internal_id)")
 
+    ext = _configured_vector_extension()
+    if ext == "scann":
+        # ScaNN should keep/use a global vector index. Per-bank partial indexes
+        # are created while banks are empty and can fail AlloyDB's ScaNN build
+        # requirements, so this migration leaves vector index reconciliation to
+        # runtime ensure_vector_extension once enough rows exist.
+        return
+
     # 2. Drop any fact_type-only partial indexes that may exist from prior migrations
     #    (bank_id B-tree always wins over them when bank_id is in the WHERE clause)
     op.execute(f"DROP INDEX IF EXISTS {schema}idx_mu_emb_world")
@@ -77,7 +98,7 @@ def upgrade() -> None:
     schema_name = context.config.get_main_option("target_schema")
     table_ref = f'"{schema_name}".memory_units' if schema_name else "memory_units"
     banks_ref = f'"{schema_name}".banks' if schema_name else "banks"
-    using_clause = _vector_index_using_clause()
+    using_clause = _vector_index_using_clause(ext)
 
     rows = bind.execute(text(f"SELECT bank_id, internal_id FROM {banks_ref}")).fetchall()  # noqa: S608
     for row in rows:
@@ -96,7 +117,7 @@ def upgrade() -> None:
             )
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     schema = _get_schema_prefix()
 
     # Drop per-bank HNSW indexes (iterate existing banks)
@@ -107,7 +128,7 @@ def downgrade() -> None:
     rows = bind.execute(text(f"SELECT internal_id FROM {banks_ref}")).fetchall()  # noqa: S608
     for row in rows:
         internal_id = str(row[0]).replace("-", "")[:16]
-        for ft_short in _HNSW_FACT_TYPES.values():
+        for ft_short in _FACT_TYPES.values():
             idx_name = f"idx_mu_emb_{ft_short}_{internal_id}"
             bind.execute(text(f"DROP INDEX IF EXISTS {schema}{idx_name}"))
 
@@ -137,3 +158,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

@@ -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 = "d6e7f8a9b0c1"
 down_revision: str | Sequence[str] | None = ("c2d3e4f5g6h7", "c5d6e7f8a9b0")
 branch_labels: str | Sequence[str] | None = None
@@ -29,11 +31,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}documents DROP COLUMN IF EXISTS metadata")
 
 
-def downgrade() -> None:
+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

@@ -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 = "h3i4j5k6l7m8"
 down_revision: str | Sequence[str] | None = ("a4b5c6d7e8f9", "g2h3i4j5k6l7")
 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()
     # 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"
@@ -30,53 +32,65 @@ def _get_schema_prefix() -> str:
 
 
 def _detect_vector_extension() -> str:
-    """
-    Detect or validate vector extension: 'pgvector', 'vchord', or 'pgvectorscale'.
-    Respects HINDSIGHT_API_VECTOR_EXTENSION env var if set.
-    """
+    """Detect or validate vector extension for this immutable migration revision."""
     conn = op.get_bind()
     vector_extension = os.getenv("HINDSIGHT_API_VECTOR_EXTENSION", "pgvector").lower()
 
-    # Validate configured extension is installed
     if vector_extension == "pgvectorscale":
-        # pgvectorscale/DiskANN requires pgvector
         pgvector_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vector'")).scalar()
         if not pgvector_check:
             raise RuntimeError(
                 "DiskANN requires pgvector. Install with: CREATE EXTENSION vector; then vectorscale or pg_diskann CASCADE;"
             )
-        # Check for either vectorscale (open source) or pg_diskann (Azure)
         vectorscale_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vectorscale'")).scalar()
         pg_diskann_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'pg_diskann'")).scalar()
 
         if vectorscale_check:
             return "pgvectorscale"
-        elif pg_diskann_check:
+        if pg_diskann_check:
             return "pg_diskann"
-        else:
-            raise RuntimeError(
-                "Configured vector extension 'pgvectorscale' not found. Install either:\n"
-                "  - pgvectorscale: CREATE EXTENSION vectorscale CASCADE;\n"
-                "  - pg_diskann (Azure): CREATE EXTENSION pg_diskann CASCADE;"
-            )
-    elif vector_extension == "vchord":
+        raise RuntimeError(
+            "Configured vector extension 'pgvectorscale' not found. Install either:\n"
+            "  - pgvectorscale: CREATE EXTENSION vectorscale CASCADE;\n"
+            "  - pg_diskann (Azure): CREATE EXTENSION pg_diskann CASCADE;"
+        )
+    if vector_extension == "vchord":
         vchord_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vchord'")).scalar()
         if not vchord_check:
             raise RuntimeError(
                 "Configured vector extension 'vchord' not found. Install it with: CREATE EXTENSION vchord CASCADE;"
             )
         return "vchord"
-    elif vector_extension == "pgvector":
+    if vector_extension == "scann":
+        scann_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'alloydb_scann'")).scalar()
+        if not scann_check:
+            raise RuntimeError(
+                "Configured vector extension 'scann' not found. Install it with: CREATE EXTENSION alloydb_scann CASCADE;"
+            )
+        return "scann"
+    if vector_extension == "pgvector":
         pgvector_check = conn.execute(text("SELECT 1 FROM pg_extension WHERE extname = 'vector'")).scalar()
         if not pgvector_check:
             raise RuntimeError(
                 "Configured vector extension 'pgvector' not found. Install it with: CREATE EXTENSION vector;"
             )
         return "pgvector"
-    else:
-        raise ValueError(
-            f"Invalid HINDSIGHT_API_VECTOR_EXTENSION: {vector_extension}. Must be 'pgvector', 'vchord', or 'pgvectorscale'"
-        )
+    raise ValueError(
+        "Invalid HINDSIGHT_API_VECTOR_EXTENSION: "
+        f"{vector_extension}. Must be 'pgvector', 'vchord', 'pgvectorscale', or 'scann'"
+    )
+
+
+def _vector_index_using_clause(ext: str) -> str:
+    if ext == "pgvectorscale":
+        return "USING diskann (embedding vector_cosine_ops) WITH (num_neighbors = 50)"
+    if ext == "pg_diskann":
+        return "USING diskann (embedding vector_cosine_ops) WITH (max_neighbors = 50)"
+    if ext == "vchord":
+        return "USING vchordrq (embedding vector_l2_ops)"
+    if ext == "scann":
+        return "USING scann (embedding cosine) WITH (mode = 'AUTO')"
+    return "USING hnsw (embedding vector_cosine_ops)"
 
 
 def _detect_text_search_extension() -> str:
@@ -119,7 +133,7 @@ def _detect_text_search_extension() -> str:
         )
 
 
-def upgrade() -> None:
+def _pg_upgrade() -> None:
     """Create learnings and pinned_reflections tables."""
     schema = _get_schema_prefix()
 
@@ -156,28 +170,12 @@ def upgrade() -> None:
     # Indexes for learnings
     op.execute(f"CREATE INDEX idx_learnings_bank_id ON {schema}learnings(bank_id)")
 
-    # Create vector index based on detected extension
-    if vector_ext == "pgvectorscale":
+    # Create vector index based on detected extension. ScaNN is deferred because
+    # this table is empty during migration and AlloyDB rejects empty ScaNN builds.
+    if vector_ext != "scann":
         op.execute(f"""
             CREATE INDEX idx_learnings_embedding ON {schema}learnings
-            USING diskann (embedding vector_cosine_ops)
-            WITH (num_neighbors = 50)
-        """)
-    elif vector_ext == "pg_diskann":
-        op.execute(f"""
-            CREATE INDEX idx_learnings_embedding ON {schema}learnings
-            USING diskann (embedding vector_cosine_ops)
-            WITH (max_neighbors = 50)
-        """)
-    elif vector_ext == "vchord":
-        op.execute(f"""
-            CREATE INDEX idx_learnings_embedding ON {schema}learnings
-            USING vchordrq (embedding vector_l2_ops)
-        """)
-    else:  # pgvector
-        op.execute(f"""
-            CREATE INDEX idx_learnings_embedding ON {schema}learnings
-            USING hnsw (embedding vector_cosine_ops)
+            {_vector_index_using_clause(vector_ext)}
         """)
 
     op.execute(f"CREATE INDEX idx_learnings_tags ON {schema}learnings USING GIN(tags)")
@@ -235,28 +233,12 @@ def upgrade() -> None:
     # Indexes for pinned_reflections
     op.execute(f"CREATE INDEX idx_pinned_reflections_bank_id ON {schema}pinned_reflections(bank_id)")
 
-    # Create vector index based on detected extension
-    if vector_ext == "pgvectorscale":
+    # Create vector index based on detected extension. ScaNN is deferred because
+    # this table is empty during migration and AlloyDB rejects empty ScaNN builds.
+    if vector_ext != "scann":
         op.execute(f"""
             CREATE INDEX idx_pinned_reflections_embedding ON {schema}pinned_reflections
-            USING diskann (embedding vector_cosine_ops)
-            WITH (num_neighbors = 50)
-        """)
-    elif vector_ext == "pg_diskann":
-        op.execute(f"""
-            CREATE INDEX idx_pinned_reflections_embedding ON {schema}pinned_reflections
-            USING diskann (embedding vector_cosine_ops)
-            WITH (max_neighbors = 50)
-        """)
-    elif vector_ext == "vchord":
-        op.execute(f"""
-            CREATE INDEX idx_pinned_reflections_embedding ON {schema}pinned_reflections
-            USING vchordrq (embedding vector_l2_ops)
-        """)
-    else:  # pgvector
-        op.execute(f"""
-            CREATE INDEX idx_pinned_reflections_embedding ON {schema}pinned_reflections
-            USING hnsw (embedding vector_cosine_ops)
+            {_vector_index_using_clause(vector_ext)}
         """)
 
     op.execute(f"CREATE INDEX idx_pinned_reflections_tags ON {schema}pinned_reflections USING GIN(tags)")
@@ -304,7 +286,7 @@ def upgrade() -> None:
     """)
 
 
-def downgrade() -> None:
+def _pg_downgrade() -> None:
     """Drop learnings and pinned_reflections tables."""
     schema = _get_schema_prefix()
 
@@ -315,3 +297,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)