Binding the same owner material to a second session fails with a primary-key violation (material id is globally unique)

[hackdion]

Bug Description

An uploaded owner material can only ever be bound to one agent session. Binding the same material to a second session raises a duplicate-key error and the request answers HTTP 500, which makes an owner-level material library unusable for more than one course.

bindOwnerMaterialsToSession in lib/server/agent-runtime/session-materials.ts decides whether to create a row by looking it up with (sessionId, id):

if (!(await store.getMaterial(sessionId, id))) {
  await store.createMaterial(sessionId, { id, ... });   // id = owner-side material id
}

but agent_session_materials has a primary key on id alone — verified against a live database with pg_indexes, and visible in the pinned DDL in packages/@openmaic/storage/src/material/pg.ts:

CREATE TABLE IF NOT EXISTS agent_session_materials (
  id            TEXT PRIMARY KEY,          -- globally unique
  session_id    TEXT NOT NULL REFERENCES agent_sessions(id) ON DELETE CASCADE,
  ...

So the de-duplication key and the actual uniqueness constraint disagree: the code asks "is this material bound to this session?" while the schema enforces "this material exists at most once, anywhere".

This blocks any workflow that reuses one upload across sessions — e.g. building a multi-part course series from the same textbook, or re-running a failed session with the same source material — which is the natural way to use an owner-level library.

Steps to Reproduce

1. Upload one file: POST /api/materials → materialId = mat_X.
2. Create session A with materialIds: ["mat_X"] → 202, binding succeeds.
3. Create session B with materialIds: ["mat_X"].
4. Observe HTTP 500 and:

[agent-runtime] request failed under an anonymous owner error:
duplicate key value violates unique constraint "agent_session_materials_pkey"
detail: 'Key (id)=(mat_X) already exists.'
table: 'agent_session_materials'
constraint: 'agent_session_materials_pkey'

(Reproduced repeatedly; the sessions were created through the workbench with the same uploaded PDF.)

Expected Behavior

One of:

• binding is idempotent per session, so mat_X may be attached to sessions A and B (the row is session-scoped, and every read already goes through getMaterial(sessionId, id)), or
• reuse is rejected with a clear 4xx explaining that a material can only be attached once.

A 500 from an unhandled primary-key violation is the one outcome that is definitely not intended.

Actual Behavior

HTTP 500 with duplicate key value violates unique constraint "agent_session_materials_pkey"; the session row is left behind by the soft-delete path.

Deployment Method

Local / self-hosted next start on a standalone build (output: 'standalone'), with the embedded PostgreSQL 16.11 managed by the repo.

OpenMAIC Version

1.0.2 (commit 98765db). Also present in 1.0.0 (commit 1e10f60); no existing issue found.

Affected Area

Storage / persistence (agent session materials) — plus agent runtime request handling.

Browser

Chrome (latest) — though the failure is server-side.

Operating System

macOS 27.0 (arm64), Node v24.18.1.

Relevant Logs

See the duplicate key value violates unique constraint block above.

Additional Context / Suggested Fix — needs a decision

Two directions with different blast radius, so this probably needs a maintainer's call before a PR:

1. Make the row session-scoped — primary key (session_id, id), or keep a surrogate row id with a unique (session_id, material_id). Cleanest for an owner-level library, but requires auditing every getMaterial(sessionId, id) caller plus the extraction pipeline, asset principals, and anything else that assumes the material id is globally unique.
2. Keep it global, make the contract explicit — generate a fresh session-side id at bind time and record the owner material id in a separate column; return a documented 4xx on reuse. Closer to the current code shape and to the extractor/derived-material model.

Option 1 matches what the calling code already assumes; option 2 is the smaller change.

Workaround we are using meanwhile: upload a separate copy of each file per session. The copies are hard-linked on disk (so almost no extra space) but each gets a new materialId, which means the per-owner material count limit (MATERIALS_MAX_COUNT_PER_OWNER, default 100) has to be raised to cover the duplicates. It works, but it is clearly a workaround rather than a fix.