bug: OceanBase identity collation collapses case-variant scope_id and source_id

[thunguo]

Describe the bug

On OceanBase, identity columns (scope_id, source_id, and the rest of the shared schema) are declared as dialect-neutral String(...) with no collation. They inherit the server default utf8mb4_general_ci (case-insensitive and accent-insensitive).

The Runtime treats these keys as opaque and byte-exact. SQLite compares them with BINARY semantics, so the SQLite test matrix never sees this. On OceanBase the same identities collide:

1. Writing Memory under scope_id="Alpha" and listing scope_id="alpha" returns the other Scope's entries (cross-scope leak).
2. Capturing source_id="Turn-1" then source_id="turn-1" with different content is rejected as 409 source_conflict, so the second turn never enters the Source journal.

Steps to reproduce

From a clean checkout of this repository:

uv sync

docker rm -f pc-collation-repro 2>/dev/null
docker run -d --name pc-collation-repro \
  -p 127.0.0.1:2881:2881 \
  -e MODE=slim \
  -e OB_DATABASE=powercontext \
  -e OB_TENANT_PASSWORD=powercontext-e2e \
  --ulimit nofile=65536:65536 \
  ghcr.io/oceanbase/oceanbase-ce:4.3.5.6-106000012026040916

until docker exec pc-collation-repro obclient \
      -h127.0.0.1 -P2881 -uroot@test -ppowercontext-e2e -Dpowercontext \
      -e 'SELECT 1' >/dev/null 2>&1; do
  sleep 5
done

export POWERCONTEXT_TEST_OCEANBASE_URL='mysql+aoceanbase://root%40test:powercontext-e2e@127.0.0.1:2881/powercontext?charset=utf8mb4'
uv run python repro_identity_collation.py

First bootstrap is usually 30–60s. The username must be URL-encoded as root%40test. Save this file next to the commands as repro_identity_collation.py:

import asyncio
import os

import httpx
from pydantic import SecretStr

from powercontext.builtin.runtime.config import OceanBaseConfig
from powercontext.client import PowerContextClient
from powercontext.client.errors import ServerResponseError
from powercontext.http import (
    CaptureContentSourceRequest,
    ListMemoryEntriesRequest,
    RememberMemoryRequest,
)
from powercontext.server.factory import create_server_app
from powercontext.server.settings import McpConfig, ServerSettings

SECRET = "Rotate the production signing key every ninety days."


async def main() -> None:
    app = create_server_app(
        settings=ServerSettings(
            database=OceanBaseConfig(url=SecretStr(os.environ["POWERCONTEXT_TEST_OCEANBASE_URL"])),
            mcp=McpConfig(enabled=False),
        )
    )
    failed = False
    async with (
        app.router.lifespan_context(app),
        httpx.AsyncClient(transport=httpx.ASGITransport(app=app), base_url="http://x") as http,
    ):
        client = PowerContextClient("http://x", http_client=http)

        await client.remember_memory(
            RememberMemoryRequest(scope_id="Alpha", kind="fact", text=SECRET)
        )
        leaked = await client.list_memory_entries(ListMemoryEntriesRequest(scope_id="alpha"))
        print(f"list Memory for scope 'alpha' after writing 'Alpha': {len(leaked.entries)} entries")
        for entry in leaked.entries:
            print(f"  leaked: {entry.text}")
        if leaked.entries:
            failed = True

        await client.capture_content_source(
            CaptureContentSourceRequest(scope_id="s", source_id="Turn-1", content="uppercase turn")
        )
        try:
            await client.capture_content_source(
                CaptureContentSourceRequest(scope_id="s", source_id="turn-1", content="lowercase turn")
            )
        except ServerResponseError as error:
            print(f"capture source_id='turn-1' after 'Turn-1': HTTP {error.status_code} {error.code}")
            failed = True
        else:
            print("capture source_id='turn-1' after 'Turn-1': accepted")

    raise SystemExit(1 if failed else 0)


if __name__ == "__main__":
    asyncio.run(main())

Expected behavior

Exit code 0.

list Memory for scope 'alpha' after writing 'Alpha': 0 entries
capture source_id='turn-1' after 'Turn-1': accepted

Alpha and alpha are distinct Scopes. Turn-1 and turn-1 are distinct Sources.

Actual behavior

Exit Code 1.

wrote to scope 'Alpha', read from scope 'alpha' -> 1 entries
  LEAKED: Rotate the production signing key every ninety days.
Traceback (most recent call last):
  File "/Users/tew/PythonProject/demo/main.py", line 36, in <module>
    asyncio.run(main())
    ~~~~~~~~~~~^^^^^^^^
  File "/opt/homebrew/Cellar/python@3.14/3.14.3_1/Frameworks/Python.framework/Versions/3.14/lib/python3.14/asyncio/runners.py", line 204, in run
    return runner.run(main)
           ~~~~~~~~~~^^^^^^
  File "/opt/homebrew/Cellar/python@3.14/3.14.3_1/Frameworks/Python.framework/Versions/3.14/lib/python3.14/asyncio/runners.py", line 127, in run
    return self._loop.run_until_complete(task)
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "/opt/homebrew/Cellar/python@3.14/3.14.3_1/Frameworks/Python.framework/Versions/3.14/lib/python3.14/asyncio/base_events.py", line 719, in run_until_complete
    return future.result()
           ~~~~~~~~~~~~~^^
  File "/Users/tew/PythonProject/demo/main.py", line 32, in main
    assert not leaked.entries, "scope isolation was breached"
           ^^^^^^^^^^^^^^^^^^
AssertionError: scope isolation was breached
❯ .venv/bin/python main.py
list Memory for scope 'alpha' after writing 'Alpha': 1 entries
  leaked: Rotate the production signing key every ninety days.
PowerContext application operation failed
Traceback (most recent call last):
  File "/Users/tew/PythonProject/demo/.venv/lib/python3.14/site-packages/powercontext/server/app.py", line 1258, in observed_endpoint
    result = await endpoint(*args, **kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tew/PythonProject/demo/.venv/lib/python3.14/site-packages/powercontext/server/app.py", line 919, in capture_content_source
    result = await application.sources.for_scope(request.scope_id).capture(mapping.capture_request(request))
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tew/PythonProject/demo/.venv/lib/python3.14/site-packages/powercontext/builtin/runtime/application.py", line 155, in capture
    source, sequence = await context.sources.capture(
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ...<5 lines>...
    )
    ^
  File "/Users/tew/PythonProject/demo/.venv/lib/python3.14/site-packages/powercontext/builtin/context.py", line 28, in capture
    source = await self.add(resolved)
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tew/PythonProject/demo/.venv/lib/python3.14/site-packages/powercontext/context.py", line 45, in add
    stored = await self.store.add(source)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tew/PythonProject/demo/.venv/lib/python3.14/site-packages/powercontext/builtin/runtime/relational.py", line 514, in add
    raise SourceConflictError("identity", error.identity) from None
powercontext.errors.SourceConflictError: duplicate Source identity: ('s', SourceRef(source_type='content', source_id='turn-1'))
capture source_id='turn-1' after 'Turn-1': HTTP 409 source_conflict

SQLite does not reproduce this. The same script against sqlite+aiosqlite returns 0 entries and accepts the second capture.

Environment

• PowerContext version: 0.0.1 (git 2186dbe)
• Python version: 3.14
• OS: macOS 15.2
• OceanBase: ghcr.io/oceanbase/oceanbase-ce:4.3.5.6-106000012026040916 (MODE=slim)

Are you willing to submit a PR to fix this bug?

• Yes, I would like to submit a PR.

[thunguo]

Belike as follow:

❯ docker exec pc-p0-ob-oceanbase-1 sh -c 'obclient -h127.0.0.1 -P2881 -uroot@test -p"$OB_TENANT_PASSWORD" -D"$OB_DATABASE" -e "
select
  _utf8mb4 0x416C706861 = _utf8mb4 0x616C706861 as Alpha_eq_alpha, -- 'Alpha' = 'alpha'
  _utf8mb4 0x636166C3A9 = _utf8mb4 0x63616665 as cafe_acute_eq_cafe, -- 'café'  = 'cafe'
  @@collation_server as coll;
"'
Alpha_eq_alpha	 cafe_acute_eq_cafe	     coll
1	      1	      utf8mb4_general_ci