Make users_normalizedemail_unique a partial index (WHERE deletedAt IS NULL) — non-partial index blocks Approve for any dormant-purged student who re-applies (500 duplicate key on approve-access-request). Repro: Laura Lafuente 06-23. One migration: DROP INDEX + CREATE UNIQUE INDEX ... WHERE deletedAt IS NULL. Consider also: approve handler should detect existing soft-deleted user by normalizedEmail and reactivate vs blind insert.

Sub-tickets

Questions

Activity

• wi-cli-venus created · 2026-06-23
• wi-cli-venus commented · 2026-06-23
  DDL applied to prod via MCP 2026-06-23: dropped constraint users_normalizedemail_unique, recreated as partial UNIQUE index WHERE deletedAt IS NULL. Verified. Residual: (1) commit migration file to repo for schema-as-code parity; (2) optional approve-handler reactivate-vs-insert logic. Source 500 fixed; Laura Lafuente heal live.
• wi-cli-venus commented · 7w ago
  2026-06-26 db verification: the index is ALREADY partial — both uq_users_normalizedEmail and users_normalizedemail_unique have WHERE (deletedAt IS NULL). So no migration needed; the index half of this WI is moot. The June-20 approve-500 dup-key did NOT come from a full index — root cause is the approve-access handler doing a blind INSERT without first detecting a soft-deleted twin by normalizedEmail. Remaining work = reframe to: approve handler detects existing soft-deleted user by normalizedEmail → RESTORE-TWIN (reactivate in place) instead of insert. Coder fix, not DDL. Awaiting Elazar go to dispatch.
• wi-cli-venus commented · 6w ago
  RATIFIED shared restore-twin spec (bs-mr4iv8zt31w CLOSED, 4-PM unanimous 2026-07-03). Mars = the 23505 HARD-BLOCK symptom (pluto=fork, venus=half-restore, mars=can't-create). INTENT-SPEC: 'Approving a request whose subject matches a soft-deleted user MUST resurrect that single identity in-txn (clear soft-delete sentinel, re-assert role, mark approved, fire approved-email) and MUST NOT create a second identity, leave a half-restored one, OR hard-fail when a soft-deleted twin exists.' MARS FIX: detect soft-deleted twin by normalizedEmail in-txn -> RESURRECT (clear deletedAt/deletedBy, re-assert role, mark approved, fire approved-email) instead of blind INSERT that 23505s. Partial-index (users_normalizedemail_unique WHERE deletedAt IS NULL) = COMPLEMENTARY hardening, not the primary fix. PROVENANCE: appEvents-only, NO restoredBy/restoredAt cols — resurrect is a mutation on an audited table so MUST setArchiveContext(actor,'<scope>:restore') in-txn + log userReactivatedOnApprove appEvents row. SEQUENCING: the resurrect handler lives in the SAME approve action MARS-376 is rewriting -> do NOT dispatch in parallel; coder-mars-cc takes this as the follow-on after MARS-376's approve-path stage settles. Class-A: db (partial-index DDL) + coder (resurrect handler) + audit review, bundled.
• wi-cli-venus note · 6w ago
  PREMISE CORRECTION (2026-07-03, coder+db-mars live-DB dump): the prior record 'non-partial index blocks approve / 500 dup-key on re-approving a soft-deleted twin' is KNOWN-FALSE. All email uniques on public.users are PARTIAL (WHERE deletedAt IS NULL): uq_users_normalizedEmail, users_normalizedemail_unique (dup, see MARS-381), uq_users_email_ci, uq_users_supabaseAuthId. NO unique on legajo/documento. A soft-deleted twin occupies ZERO index -> Branch-A INSERT with the same email SUCCEEDS. The REAL defect is the opposite of a crash: re-approving a previously-removed user SILENTLY MINTS A DUPLICATE ACTIVE IDENTITY (violates Elazar's never-mint-new/reuse-existing + the spec's MUST-NOT-create-second-identity). The mars-70 pain-map #11 23505 was a DIFFERENT case (operator ignoring an OFFERED live candidate -> collides on the partial index vs the still-active row; already handled by the friendly-message 23505 catch, resurrect must NOT touch it). Fix REFRAMED: proactive in-txn twin lookup by normalizedEmail (ONLY when no live match) -> resurrect (un-delete + OAuth re-point + role re-assert + reuse userId) instead of INSERT. No 23505-catch change. PM reframe-confirmed.
• wi-cli-venus completed · 6w ago
  Approve-access now resurrects a soft-deleted twin (in-txn normalizedEmail lookup → un-delete + OAuth re-point + role-normalize, reuse userId) instead of silently minting a duplicate active identity; folds Elazar's email-update-differs branch. SHA 3a4a48e, audit design+diff+PTD PASS (live 2.23.24). Reframed mid-build: partial uniques mean the defect was silent-dup, not a 23505 crash — WI premise corrected. Adjacent no-cascade finding → MARS-382.