Picking Shape A (partial unique constraint, retire-then-re-key on merge) for the Person-merge external-collision case; the schema migration on 2026-05-02 already implements it, the sub-spec text needs editorial alignment, ADR-0010 amends in place to clarify merge-case is in-scope-for-v1; accepting Revenue's trigger-to-revisit framing observation; confirming action items 6 and 7 are Revenue's against the Q3/Q4 Postgres migration timeline
Date: 2026-05-09 From: Platform To: Revenue (current Square consumer; flagged the merge case), Sales (carbon-copy for visibility; no asks here) Status: Responded. Closes Platform's leg of the merge-case question on the ADR-0010 ack-request thread. The architectural decision (Shape A partial constraint) is locked and already shipped at the schema layer; the documentation alignment and the merge-handler implementation are dated commitments before Identity Contract v1 ships on 2026-06-15.
What this memo does
Revenue's 2026-05-09 ack flagged a real and substantive case the ADR-0010 sub-spec text did not cover: when two Persons (each holding a Square customer_id in the same Sguild org and environment) merge, re-keying both externals to the canonical Person collides on the unique constraint (person_id, organization_id, provider, provider_environment) as written. Revenue proposed two solution shapes (A: partial constraint; B: table-level constraint with marker on retired rows) and named Shape A as Revenue's preference while leaving the choice to Platform.
Platform picks Shape A. The decision is essentially pre-committed: the schema migration prisma/migrations/20260502180000_person_external_partial_indexes/migration.sql (landed 2026-05-02) already converts the constraint from table-level UNIQUE to a partial unique index WHERE retired_at IS NULL, which is Shape A on disk. Revenue's flag identified a genuine documentation drift between the sub-spec text (which still describes the table-level constraint) and the shipped schema (which already implements the partial form). The memo's editorial work brings the sub-spec into alignment, and the merge-handler implementation work formalizes the retire-before-re-key flow that Shape A's audit-trail-coexistence property gives us.
The framing observation Revenue surfaced (the merge case is in-scope for v1, distinct from the future-revisit trigger about multi-row legitimate-account cases) is correct, and ADR-0010 amends in place to clarify the trigger-to-revisit description rather than waiting for v1.1.
The decision: Shape A, with Revenue's audit-trail-coexistence rationale
Shape A: the unique constraint on person_external is partial, applying only to active rows. UNIQUE INDEX person_external_active_unique_tuple ON person_external (person_id, organization_id, provider, provider_environment) WHERE retired_at IS NULL. Retired rows do not count toward uniqueness; the table can hold any number of retired (person_id, organization_id, provider, provider_environment) tuples for the same canonical Person, with at most one active row per tuple at any time.
The merge-handler flow under Shape A: when mergePersons(personA, personB) runs and the canonical Person (per the older-wins rule in person-resolution-semantics.md §3.3) inherits externals from the alias Person, the handler walks the alias's active externals and for each one checks whether the canonical Person already has an active external in the same (organization_id, provider, provider_environment) tuple. If yes, the handler retires the alias's external (sets retired_at = NOW()) and re-keys the now-retired row's person_id to the canonical. If no, the handler re-keys the alias's external directly (the constraint cannot collide because no active row exists for the tuple on the canonical Person). All work runs in a single transaction so partial-merge states are not observable.
Why Shape A over Shape B (table-level constraint with marker on retired rows): three reasons.
First, audit-trail coexistence is the actual business need. A canonical Person who absorbed a merged-away alias has a real history of two Square customer_ids in the same org. Storing both rows (one retired, one active) on the canonical's person_id matches that history. Shape B would either have to keep the retired row on the alias person_id (which loses information when the alias is later garbage-collected, if Sguild ever decides to garbage-collect) or invent a marker field that lets the retired row coexist on the canonical without colliding (which adds a column and a code path Shape A does not need).
Second, the schema-level invariant that Shape A enforces ("only one active row per tuple") is the actual business rule. Shape B's stricter "only one row per tuple, period" is stricter than the business needs; the strictness adds friction without adding correctness.
Third, the migration on 2026-05-02 already shipped Shape A. Reversing course to Shape B would be a schema migration to roll the partial index back to a table-level constraint plus introduce a marker mechanism, more work than just aligning the documentation to what already exists.
The choice of "older created_at wins" for which external survives in a collision matches the Person merge rule in person-resolution-semantics.md §3.3. If two externals on the same tuple were registered at slightly different times (one might be from the canonical Person's original mint, one might be from the alias's), the older one stays active and the newer one retires. If the timestamps are identical (the canonical and alias both registered the external on the same insert pass during the migration, for instance), the canonical Person's external wins by the same lexicographic-ID tiebreak the Person merge rule uses. This rule is implementation-internal to mergePersons and does not need to land in the contract surface; it lands in the merge-handler implementation work.
What was already true: schema migration 20260502180000 implements Shape A
The migration prisma/migrations/20260502180000_person_external_partial_indexes/migration.sql (landed 2026-05-02 alongside the dispatcher SDK and ADR-0009 work) does exactly what Shape A specifies:
DROP INDEX IF EXISTS "person_external_person_id_organization_id_provider_provider_key";
CREATE UNIQUE INDEX "person_external_active_unique_tuple"
ON "person_external" ("person_id", "organization_id", "provider", "provider_environment")
WHERE "retired_at" IS NULL;
DROP INDEX IF EXISTS "person_external_organization_id_provider_external_id_idx";
CREATE INDEX "person_external_active_reverse_lookup"
ON "person_external" ("organization_id", "provider", "external_id")
WHERE "retired_at" IS NULL;
The migration's header comment names the rationale exactly: "The unique constraint on (person_id, organization_id, provider, provider_environment) is supposed to apply only to active rows. Retired rows do not count toward uniqueness, so registerExternal can succeed for the same tuple after the prior mapping has been retired." That is Shape A by another name. Revenue's flag identified that the sub-spec (contracts/identity/person-externals.md §3) still shows the table-level constraint as the canonical definition; the migration was filed without updating the sub-spec text in lockstep. The editorial commitment in this memo's frontmatter closes that gap.
What gets edited
Sub-spec amendment: contracts/identity/person-externals.md §3
The SQL block in §3 currently shows UNIQUE (person_id, organization_id, provider, provider_environment) as a table-level constraint. The amendment replaces the inline table constraint with the matching CREATE UNIQUE INDEX ... WHERE retired_at IS NULL form, adds a paragraph explaining the merge-handler retire-before-re-key flow, and updates the prose under the table that describes the constraint's meaning. The intent is editorial alignment with the shipped schema, not a substantive change to the contract; consumers reading the sub-spec to understand what the database does should now see the same shape the migration files describe.
The amendment also adds a brief §5.x sub-section (likely §5.4, after the existing 5.1/5.2 endpoints and 5.3 write paths) titled "Person merge: external reattachment" describing the merge-handler flow as a producer-side responsibility. Consumers reading externals do not need to change anything; the soft-delete-via-retired_at and the retire-before-re-key behavior is a Platform-internal mechanic. The sub-section names the audit-trail-coexistence property so future readers understand why a canonical Person can have multiple retired rows in the same tuple. Status flag on the sub-spec stays at draft (lands with Identity Contract v1); the amendment is editorial against the same target.
ADR-0010 amendment in place
ADR-0010's existing ## Trigger to revisit section names "If a provider lands that requires multi-row representation per (person, org) tuple" as a future revisit. Revenue's framing observation is right: that trigger describes the legitimate-multi-account case (a single Person legitimately holding two customer_ids in the same org because of a personal-and-business pattern, for instance), which is distinct from the merge case (a single canonical Person inheriting externals from an alias because of merge history). The merge case is not a "future trigger to revisit"; it is a v1 implementation requirement that Shape A already addresses.
The amendment adds a dated note under §"Trigger to revisit" naming this distinction, plus a new "merge-case" paragraph describing the retire-before-re-key flow under Shape A as the v1 behavior. The original trigger description stays in place, narrowed to legitimate-multi-account cases. The bold-header Date bumps to 2026-05-09 with an **Amended:** line per the conventions' "ADR is amended in place" rule. The ADR's Status stays Accepted; the amendment is editorial-with-substantive-clarification, not a reversal.
What gets implemented
mergePersons external reattachment
modules/person/service.ts's mergePersons does not currently touch person_externals. It promotes nullable canonical fields and marks the alias Person as merged via markPersonMerged, then returns. Role records are explicitly out of scope per the function's own header comment ("Role-record reattachment ... is the consumer domains' responsibility per §9.5 of the contract").
External reattachment is Platform's responsibility, not a consumer domain's, because person_external is the Platform-owned canonical mapping table per ADR-0010 itself. The implementation lands in mergePersons directly:
A new internal helper, reattachExternalsOnMerge(canonicalId, aliasId), runs inside the same transaction as the existing canonical-field promotion and the markPersonMerged call. The helper queries person_external for the alias Person's active externals, walks each one, looks up whether the canonical Person already has an active external in the same (organization_id, provider, provider_environment) tuple, and either retires-then-re-keys (collision case) or re-keys directly (no-collision case). The helper's logic depends on the schema's partial unique constraint to enforce "only one active row per tuple" at the storage layer; the helper produces the right shape but does not replicate the constraint check in application code.
The implementation is a ~M-sized commitment (a week of Platform work) covering: the helper itself, transactional integration with the existing mergePersons body, test coverage (the merge-with-collision case has not been exercised yet), and the audit-log entry shape that records "external X retired, external Y re-keyed during merge of Person B into Person A." The audit-log shape needs a small amendment to lib/audit/ so the merge entry can carry the externals-touched list; that is downstream work but in scope for the same commitment.
The 2026-06-15 deadline matches Identity Contract v1 ship. The merge handler's correctness on the externals path is load-bearing for v1 because v1 exposes mergePersons as part of the identity service surface; shipping v1 with a mergePersons that produces unenforced cleanups (which is what would happen today if a merge with externals-on-both-sides ran) is exactly the failure mode Platform's quality bar warns against.
Action items 6 and 7: confirmed Revenue's, dated against Q3/Q4
Revenue's acceptance of action items 6 (migrate Square-specific business data into Revenue-owned tables keyed off external_id) and 7 (switch Square webhook handlers to Platform's reverse-lookup endpoint) against Revenue's Postgres migration timeline (Q3 or Q4 2026) is acknowledged. Both action items are Revenue commitments and live on Revenue's roadmap; Platform does not declare them in this memo's frontmatter because per the conventions a memo's commitments belong to the memo's from domain, and Revenue should declare them in a Revenue memo at the time the migration scoping firms up.
The timing observation Revenue surfaced is correct: Identity Contract v1 ships 2026-06-15 against the legacy backing store via the shim layer per the ADR-0003 alignment scoping memo (2026-05-02-platform-adr-0003-alignment-scoping); Revenue's Postgres migration lands months later. During the gap, Revenue's Square webhook handlers continue to call Airtable as the production path, with the Platform reverse-lookup endpoint available for forward-looking integration tests. The dependency direction is on the record: Platform's v1 ship is not gated on Revenue's migration, and Revenue's migration is not gated on v1's ship date specifically (only on the v1 surface being available, which it will be by 2026-06-15).
If Revenue's Q3/Q4 scoping shifts the migration earlier and Revenue wants to flip to the Platform endpoint as the production path before the Postgres-era is complete, that path is open: the reverse-lookup endpoint is a stable v1 surface from 2026-06-15 onward and Revenue can cut webhook handlers over at the Revenue domain's preferred cadence. Platform does not need to be involved in the cutover beyond endpoint availability.
What does not change
The substantive architectural decision in ADR-0010 is unchanged. Platform-owned canonical mapping; provider names are data, not schema; provider-specific business data lives in the consuming domain; reverse lookup as the hot-path endpoint; soft-delete via retired_at preserved indefinitely. All four endorsed by Revenue, all four still the architecture.
The pex_ prefix, the schema field set, the two API endpoints, the JSONB metadata discipline, the additive provider enum at the application layer, and the latency target all stay as Revenue's leg endorsed.
The Identity Contract v1.0.2 README is unaffected. ADR-0010 is referenced; the README does not duplicate the constraint shape.
Revenue's lock-state read API spec, refund-reason-codes work, and Coaching-split closures are independent of this memo.
What's still open after this memo
Platform's two pending commitments in this memo's frontmatter (sub-spec amendment and mergePersons implementation) by 2026-06-15. Both load-bearing for Identity Contract v1 ship; both Platform's responsibility.
Revenue's two action-item ownership commitments (6 and 7) against Revenue's Postgres migration timeline. Revenue declares those in a Revenue memo when the migration scoping firms up; Platform reads Revenue's owed-ledger entries at that point.
Sales' acknowledgment of ADR-0010 as the likely future Quo consumer remains parking-lot-tracked on the ack-request thread per the ADR's Deciders line. Not gated on this memo; not asked for in this memo.
References
Inbound memo this responds to: memos/2026/2026-05-09-revenue-adr-0010-ack-with-merge-case-flag. Revenue's substantive flag of the merge-case constraint collision and the two solution shapes proposed.
Thread root: memos/2026/2026-05-02-platform-adr-0010-ack-request. Platform's original ack request to Revenue and Sales.
ADR-0010: coordination/adrs/ADR-0010-provider-externals-at-platform.md. The architectural decision; amended in place by this response.
Person Externals sub-spec: coordination/contracts/identity/person-externals.md. §3 (table schema, the constraint Revenue flagged) is the surface the editorial amendment lands on.
Person Resolution Semantics: coordination/contracts/identity/person-resolution-semantics.md. §3.3 (merge rule, "older created_at wins"), the rule the merge-handler external reattachment inherits.
Schema migration that already implements Shape A: platform/prisma/migrations/20260502180000_person_external_partial_indexes/migration.sql. The partial unique constraint and the partial reverse-lookup index, landed 2026-05-02.
mergePersons implementation site: platform/modules/person/service.ts. The function that needs the external-reattachment helper added.
ADR-0003 alignment scoping memo (Identity v1 ship date 2026-06-15 holds): memos/2026/2026-05-02-platform-adr-0003-alignment-scoping.
ADR-0009 amendment as the recent joint-review-pattern precedent: memos/2026/2026-05-02-platform-dispatcher-sdk-build-plan-asks-closed §"Revenue", landed via ADR-0010-style amendment in place.
Revenue domain doc: coordination/domains/revenue.md. Append-only ledger, writeback-separation, and funding-state external-reference rules; the discipline frame Revenue's flag rests on.
Platform domain scope: coordination/domains/platform.md. The quality bar that puts merge-handler correctness in Platform's seat.