schema generator: emit UNIQUE NULLS NOT DISTINCT on PG for indexes derived from nullable conflict columns

[willgriffin]

Context

#1246 surfaced a class of bugs where `conflictColumns` that include nullable columns (e.g. `tenant_id` on `@TenantScoped({ mode: 'optional' })` models) silently fail to match under `ON CONFLICT(...)` because SQL treats NULL as distinct. The smrt-core fix at commit 82d7618 is a tactical workaround in `planPersistenceWrite()`. The proper fix has two upstream layers:

1. `@happyvertical/sql` handles NULL-aware upsert transparently → tracked at happyvertical/sdk#1025
2. smrt-core's schema generator (this issue) emits PG-native `UNIQUE NULLS NOT DISTINCT` indexes when conflict columns are nullable, so the trap doesn't exist at all on PG 15+.

What to build

In `packages/core/src/schema/generator.ts`, when generating the UNIQUE index for a model's `conflictColumns`:

• Inspect each conflict column's field decorator to see if it's nullable
• If ANY conflict column is nullable AND the target dialect is Postgres 15+: emit `CREATE UNIQUE INDEX ... ON ... USING btree (...) NULLS NOT DISTINCT`
• If older PG: fall back to a partial unique index OR rely on the `@happyvertical/sql` query-time fix (sdk#1025) — document the choice in the generator
• If SQLite: no schema-level fix exists (SQLite has no `NULLS NOT DISTINCT`); rely on the sql-layer fix from sdk#1025

The dialect detection should already exist in the schema generator (PG / SQLite paths diverge for other reasons).

Why this matters

With the schema-level fix, the SMRT framework's upsert behavior on PG becomes self-consistent: ON CONFLICT works as users intuitively expect, no application-layer probe needed. Pairs naturally with the sdk#1025 fix for non-PG / older-PG cases.

Acceptance criteria

• PG 15+ schema generator emits `UNIQUE NULLS NOT DISTINCT` for indexes derived from nullable conflict columns
• PG < 15 documented behavior (partial index, or rely on sdk#1025)
• SQLite generator unchanged (no schema-level fix available)
• Test coverage in `packages/core/tests/schema/` for the new generator behavior
• Existing test suite stays green — schema migrations on existing tables are non-destructive (the migration tool either tolerates the index rebuild or the new attribute is additive)
• CLAUDE.md update describing the canonical "optional tenancy + per-tenant unique natural key" pattern and that the framework handles NULL semantics for it

Blocked by

None — this can land independently of sdk#1025. They're complementary; this fixes PG at the schema layer, sdk#1025 fixes everywhere at the query layer.

Follow-up

Once both this issue AND sdk#1025 land, the tactical workaround in `packages/core/src/object.ts` (commit 82d7618, the `planPersistenceWrite` NULL probe) can be removed. Tracked as a separate issue.

[willgriffin]

Re-evaluation after sdk#1026 merged

happyvertical/sdk#1026 (@happyvertical/sql@0.74.0) ended up doing more than just the query-layer fix:

• The new postgres adapter detects native PG15 NULLS NOT DISTINCT indexes at runtime via a cached pg_index.indnullsnotdistinct lookup (see hasNativeNullsNotDistinctIndex() in packages/sql/src/postgres.ts).
• When such an index is present, it uses the standard ON CONFLICT ... DO UPDATE path with no runtime overhead.
• When absent (PG < 15, or PG 15+ without the index attribute set), it falls back to advisory-lock transactional probe-then-write.

So the upstream already rewards smrt-side schema generation that emits NULLS NOT DISTINCT — it's the fast path. But correctness no longer requires it.

Recommendation: keep this ticket open, demote priority

Reasons to still do it:

• Performance on PG 15+: removes the per-upsert hasNativeNullsNotDistinctIndex probe (cached, but still costs a query the first time per table+columns combo per process).
• Self-documenting schema: external tools (DBeaver, pgdump, migrations from other consumers) see the index attribute and behave consistently.
• Architectural cleanliness: the bug class is structurally absent rather than papered over at the query layer.

Reasons it's no longer urgent:

• End-to-end correctness is already achieved by sdk#1026 across all dialects.
• The PG advisory-lock fallback handles concurrency safely.
• The original blocker for smrt-core: remove planPersistenceWrite NULL-conflict workaround once upstream sdk + schema fixes land #1261 (the smrt-core workaround removal) only needs sdk#1026, not this.

Suggested re-labeling: type: enhancement rather than type: bug. Move out of the #1245 epic critical-path. Acceptance criteria still valid as written.

Note on the original "Blocked by" framing in #1261

#1261's "Blocked by" list currently mentions both sdk#1025 and #1260. Now that sdk#1026 makes #1260 a perf/cleanliness optimization rather than a correctness prerequisite, #1261 is only blocked by the catalog bump to @happyvertical/sql ^0.74.0. I've commented on #1261 noting this.