Harden Edge de-dup with a partial unique index (close connectNodes TOCTOU)

[CuriouslyCory]

Context

PR #24 introduced the Edge model with service-level de-duplication (ADR-0005). The de-dup check in connectNodes uses findFirst to test for a duplicate active Edge, then creates it—but under READ COMMITTED isolation, a concurrent double-submit can slip two identical Edges in the window between the check and the write.

Why deferred

A database partial unique index is the only correct fix:

CREATE UNIQUE INDEX ... ON "Edge"(canvasNodeId, sourceId, targetId) WHERE "deletedAt" IS NULL

This allows soft-delete-then-recreate (a plain @@unique would wrongly block it) and closes the race. However, Prisma cannot express partial unique indexes declaratively—it requires switching from db:push to prisma migrate + raw SQL, a workflow change beyond the current slice.

The realistic trigger is narrow (one owner double-submitting the same Edge within milliseconds) and the blast radius (a duplicate active Edge) is recoverable via soft-delete.

Resolution

1. Switch to prisma migrate workflow.
2. Add the composite index: CREATE UNIQUE INDEX "idx_edge_dedup" ON "Edge"(canvasNodeId, sourceId, targetId) WHERE "deletedAt" IS NULL.
3. In connectNodes, catch Prisma P2002 (unique constraint violation) and map it to ConflictError.
4. Update ADR-0005's deferral note to point at this issue.

The non-unique composite index from PR #24 (@@index([canvasNodeId, sourceId, targetId, deletedAt])) is the fast-path precursor for the findFirst query.

[CuriouslyCory]

Realigned against #33 / docs/plans/flow-routed-connections.md.

Still valid. Edge shape is unchanged in the master plan ("Zero changes to today's Edge shape"), so the proposed partial unique index on (canvasNodeId, sourceId, targetId) WHERE "deletedAt" IS NULL stands as written.

New context — second Edge writer. Slice 3 (#36) introduces routeFlow as the only other service permitted to write an Edge (the gated ADR-0005 exception, ADR-0011). The index applies to those inner Edges too, which is desirable — routeFlow gets the same TOCTOU guard for free.

Acceptance criteria should expand. Add:

• routeFlow's inner-Edge insert path (Slice 3: Refinement routing via boundary proxy palette #36) catches P2002 and maps to ConflictError symmetrically with connectNodes.
• Regression test: two concurrent routeFlow calls refining the same outer Edge with two distinct Flows over the same (sourceId → targetId) interior pair must converge on one inner Edge with two FlowRoutes pointing at it — not two Edges, not one rejection. The plan's model (Edge = pipe, Flows ride it, FlowRoute.innerEdgeId is a plain FK with no uniqueness) supports inner-Edge sharing; routeFlow should find-or-create the inner Edge, not insert blindly.

One concern. The cross-scope exception in invariant 4 means an inner Edge can have one endpoint whose parentId !== canvasNodeId (the boundary proxy side). The unique-index tuple is purely (canvasNodeId, sourceId, targetId) — it does not care about parentId, so the index remains correct under the exception. No data-shape concern; only the find-or-create coordination above.

Sequencing: this can ship before or after #36, but if #36 lands first, its routeFlow writer needs the P2002 mapping retrofitted when this index lands. Easier if #25 lands first (or alongside #36) so the writer is born race-safe.

[CuriouslyCory]

Implemented on branch `feat/edge-dedup-partial-unique-index` (PR pending).

What landed:

• Partial unique index `idx_edge_dedup` on `(canvasNodeId, sourceId, targetId) NULLS NOT DISTINCT WHERE "deletedAt" IS NULL` via raw SQL migration; legacy 4-column composite dropped.
• `connectNodes` and `restoreNode` catch P2002 narrowed to the constraint (via new `isEdgeDedupCollision` helper in `src/server/architecture/prisma-errors.ts`) and map to `ConflictError` with structured `details.conflictingEdgeIds`. The fast-path findFirst now produces the same shape so the contract is symmetric across both paths.
• `ConflictError` extended additively with optional `details: ConflictErrorDetails`; tRPC `errorFormatter` extended to flow `ArchitectureError.details` → `error.data.archDetails` on the client.
• Repo moved from `prisma db push` to `prisma migrate` (workflow: `migrate diff` for authoring, `migrate deploy` for applying — no shadow DB required); `db:push` removed from `package.json`; test global-setup switched to `migrate deploy`.
• Documentation: new ADR-0010 (wide-scope, names the canonical "service-primary + partial-unique-backstop + named P2002 catch" pattern with preconditions); amendments on ADR-0005 + CONTEXT.md; renumbered reserved ADR numbers in `docs/plans/flow-routed-connections.md` (0010→0011, 0011→0012, 0012→0013).

Two surprises caught during implementation (now baked into the slice and ADR-0010):

1. `NULLS NOT DISTINCT` is load-bearing — without it, two root-Canvas duplicates (canvasNodeId = NULL) would both pass.
2. Prisma 7 + `@prisma/adapter-pg` reports P2002 with a completely different shape than the legacy query engine (`meta.driverAdapterError.cause.originalMessage` / `constraint.fields` instead of `meta.target`). The helper handles both shapes.

Verification: `pnpm check` clean, 92/92 tests passing. `restoreNode` rich-diagnostic regression test deferred until `routeFlow` (#36) lands — the conflict state is unreachable through valid service calls today (cascading-delete sweeps endpoints alongside their incident edges).