Acknowledging ADR-0010 (provider externals at Platform) as the right architecture for Revenue's Square integration; flagging one operational case where the unique constraint as written would collide (Person merges where both Persons hold Square customer_ids in the same org collapse onto a single (person, org, provider, env) tuple), with two solution shapes proposed; accepting action items 6 and 7 against Revenue's Postgres migration timeline
Why
Platform's 2026-05-02 memo asks Revenue to acknowledge or push back on ADR-0010 (Platform-owned canonical mapping for provider externals) and specifically flags whether Revenue's Square integration has ever hit a case where one Sguild org legitimately has two customer_ids for the same human. Revenue accepts the architectural decision as written and signs on to the two action items it owns. Revenue does have one operational concern about the unique constraint that does not match Platform's framing of "no Person appears twice per (org, provider, env)": Person merges produce exactly that case in Sguild's current data, and the v1 schema needs to handle it. Two solution shapes are proposed; both work for Revenue's needs and the choice is Platform's call. This memo also accepts §6.1 and §6.4 consumer responsibilities and notes one operational point about the migration timeline.
What
Architecture: accepted
The Platform-owned canonical mapping is the right shape for Revenue's Square integration. The boundary as ADR-0010 describes it (Platform owns (provider, external_id) → person_id, Revenue owns Square-specific business data: payment-method tokens, billing addresses, transaction references, idempotency keys, etc.) matches the architecture Revenue would have asked for if Platform had not proposed it. Revenue's preferred posture has always been "Platform is the identity surface; Revenue does not duplicate identity infrastructure," and this ADR codifies that posture for the externals case.
Specifically endorsing the four key shape decisions:
Provider names are data, not schema. Adding Quo, Stripe, or any future provider is a row insertion plus an application-layer enum extension, not a schema migration on person_externals or on Person. This is the property that makes provider swaps tractable.
Provider-specific business data lives in the consuming domain, not in person_externals's metadata JSONB. Revenue holds Square's payment-method tokens, billing address shapes, transaction histories, idempotency keys, and webhook-replay state in Revenue-owned tables, keyed off the external_id Platform's table holds. The discipline to keep metadata narrow (account-version stamps, location_id when relevant, nothing query-target-shaped) is the right one.
Reverse lookup as the hot-path endpoint matches Revenue's webhook architecture exactly. Revenue's Square webhook handlers today do an Airtable lookup (Client Externals → Client → Person); the Postgres-era version becomes one HTTP call to Platform's /identity/v1/externals/lookup per inbound webhook. Revenue does not maintain a parallel mapping; §6.1's prohibition is exactly what Revenue wants enforced.
Soft-delete via retired_at is preserved indefinitely. Provider-swap audit trail is load-bearing for cross-provider migration debugging (Revenue has scoped a future Square-to-Stripe path conditional on Square's pricing changes, and the audit trail will matter when that path activates).
One operational case the unique constraint does not handle: Person merges
Platform's memo asks specifically whether one Sguild org has ever legitimately had two Square customer_ids for the same human. The framing focuses on legitimate multi-account business cases (a Person with both a personal and business Square account at the same Sguild org, for example); Revenue does not have any Persons with that pattern today, so on that specific reading the answer is no.
But there is a different case that produces the same database shape: Person merges. Sguild's identity service per coordination/contracts/identity/person-resolution-semantics.md supports merging two canonical Persons into one when post-mint signal arrives confirming they are the same human. Today's Airtable era hits this case operationally (an operator notices two Person records for the same human, merges them through the operator workflow), and the Postgres-era identity service per ADR-0003 will hit it programmatically through the three-tier resolution rules.
When two Persons (each holding a Square customer_id in the same Sguild org and environment) merge, the canonical Person ends up with two externals that map to the same (person_id, organization_id, provider, provider_environment) tuple:
Pre-merge state:
(person_A, org_X, square, production)→external_A(person_B, org_X, square, production)→external_B
Post-merge target state if Person B merges into Person A:
- Both externals now logically belong to person_A (the canonical Person owns the credit accounts, orders, ledger history, etc. that both pre-merge Persons accumulated)
- Re-keying external_B from person_B to person_A produces a row at
(person_A, org_X, square, production)→external_Bthat collides with the existing external_A row on the unique constraint
The unique constraint as currently written in the sub-spec (UNIQUE (person_id, organization_id, provider, provider_environment), table-level, not partial) would block the re-key. The merge would either fail or produce an unenforced cleanup that future migration scripts have to discover.
Two solution shapes Revenue can see, both of which work for Revenue's needs:
Shape A: partial unique constraint, retire-then-re-key on merge. Make the constraint UNIQUE (person_id, organization_id, provider, provider_environment) WHERE retired_at IS NULL. The merge handler retires one of the externals (typically the non-canonical Person's external; could be a policy choice such as "older created_at wins" matching the Person merge rule per person-resolution-semantics.md §3.3) before re-keying the survivor to the canonical Person. The retired row stays in the table for audit purposes; it does not participate in the active uniqueness check.
This is more flexible because it allows the historical record of both externals to coexist on the canonical Person. A future audit query "what Square customer_ids has Person A ever had at this org?" returns both rows, with one marked retired and one active.
Shape B: keep the table-level UNIQUE; merge handler retires the duplicate before re-key. Same operational behavior (one of the externals gets retired during merge), but the constraint stays non-partial and the merge handler has a hard requirement to do the retire BEFORE the re-key. The retired row gets a marker (e.g., a different (provider_environment) value or a renamed column) that lets it stay in the table without conflicting.
This is more disciplined because the schema enforces the invariant strictly; the merge handler cannot accidentally produce a state where two active externals coexist for the same tuple.
Revenue's preference is shape A (partial constraint), because the historical-record-coexistence property is useful for audit and the schema-level enforcement of "only one active at a time" is the actual business rule. But shape B works too if Platform prefers strict table-level uniqueness; Revenue does not have a hard preference between them. The choice is Platform's call.
If Platform sees a third shape Revenue is missing (e.g., the merge process collapses both externals into one by calling Square's customer-merge API rather than retiring on Sguild's side), name it. Revenue is open to that path if Square actually exposes a customer-merge primitive at the time the merge case fires; today's Square API does not, but the API has changed before.
The action item: address the merge case in v1, not as a v1.1 amendment. The schema as currently drafted in person-externals.md would block merges in production; the constraint shape needs to land before Identity Contract v1 ships.
Action items 6 and 7: accepted
Revenue takes ownership of action items 6 and 7 against Revenue's Postgres migration timeline.
Action item 6 (migrate Square-specific business data into Revenue-owned tables keyed off external_id): scoped as part of Revenue's Postgres migration. The current Airtable shape co-locates the Square customer_id mapping with payment-method-on-file token references and billing address fragments on Client Externals records. Revenue's migration splits the Square business data (tokens, addresses, etc.) into Revenue-owned Postgres tables (payment_method_on_file, external_account, etc. per coordination/domains/revenue.md's notable-directories list) and reads external_id via the reverse-lookup endpoint at webhook time.
Action item 7 (switch Square webhook handlers to Platform's reverse-lookup endpoint): scoped as part of the Postgres cutover. The cutover is the same change-window: Airtable webhooks-to-Airtable-lookup flips to Postgres-webhooks-to-Platform-API. Revenue does not plan a transition period where some webhooks use the old path and some use the new; the cutover is atomic per organization.
Timing: Revenue's Postgres migration is not on the Q2 2026 rock list (the Q2 rocks are Auth SDK adoption, ledger reconciliation parity, refund flow rebuild, automated reconciliation per coordination/domains/revenue.md). The migration lands in Q3 or Q4 depending on rock-list scoping. Revenue commits to the action items against the migration timeline whenever it falls; the commitment is on the architectural shape, not the calendar date.
If Platform's Identity Contract v1 ships ahead of Revenue's migration (per the 2026-06-15 target in ADR-0010 action item 4), Revenue's pre-Postgres webhook handlers can call the Platform endpoint as a forward-looking integration test, but the production path stays on Airtable until cutover. Revenue does not see this as a problem; flagging so the dependency direction is on the record.
§6.1 and §6.4 consumer responsibilities: accepted
§6.1 (resolve external IDs through Platform's reverse-lookup; do not maintain own mapping). Accepted. Revenue does not plan to maintain a (provider, external_id) → person_id cache or table beyond the §6.3 short-term hot-path in-process cache (60-second TTL keyed by request, per the sub-spec's recommendation). The Square webhook handler's request-to-response cycle is short enough that even short-term caching adds little value; Revenue may not implement caching at all in v1 of the integration and revisit if the latency budget tightens.
§6.4 (org-scoped reverse-lookup calls). Accepted. Revenue's Square webhook handler resolves organization_id from the webhook's payload (Square's webhook carries merchant_id which Sguild maps to organization_id via the Provider Accounts table) before calling the reverse-lookup. Cross-org reverse-lookups are nonsensical from Revenue's standpoint and the 404 behavior is what Revenue would expect.
Sub-spec details Revenue is comfortable with
The schema field set, the pex_ prefix, the two API endpoints, the partial index on (organization_id, provider, external_id) WHERE retired_at IS NULL for the reverse-lookup hot path, the JSONB metadata column with the discipline-not-business-data framing, the soft-delete-only producer responsibility (§7.4), and the additive provider enum (§7.5) all read correctly. The 95th-percentile <50ms latency target at the Platform service layer (§7.3) is acceptable; consumer-perceived latency includes network round trip, which Revenue layers against by keeping webhook handlers single-call rather than chaining identity resolutions.
Sandbox vs production via provider_environment is the right place for environment-multiplexing. Revenue's Square integration today uses production-only; the provider_environment column makes future sandbox-test integrations possible without colliding with production rows.
One framing observation, not a pushback
ADR-0010 names the trigger "If a provider lands that requires multi-row representation per (person, org) tuple" as a future revisit. The merge case Revenue is flagging is not actually that trigger; it is a different case (multi-row from merge history, not from concurrent legitimate accounts). Revenue's reading is that the merge case is in scope for v1 because the merge process is part of the identity service's day-one operation, while the multi-account business case is a future revisit. Surfacing the framing distinction so the trigger description in the ADR's "trigger to revisit" section can be amended if Platform agrees (or the merge case can be added as a separate trigger; either is fine).
Asks
Platform: pick a shape (A partial constraint or B retire-before-re-key with non-partial) for handling the Person merge case before Identity Contract v1 ships. Either works for Revenue. If a third shape is preferable that Revenue is not seeing, name it. The constraint as currently drafted does not handle the case and would block merges in production.
Platform: acknowledge action items 6 and 7 are Revenue's, dated against Revenue's Postgres migration (Q3 or Q4 2026 depending on rock scoping, not Q2). If Platform's Identity Contract v1 timing creates an external dependency Revenue should plan against differently, name it.
Sales: Revenue has no asks. The Quo/SMS-consuming-domain question is between Sales and Platform; Revenue is acknowledging the architecture, not weighing in on which domain consumes Quo.
Soft response window 2026-05-15 to align with Platform's stated window.
What this memo does NOT change
The substantive architectural decision in ADR-0010 is accepted as written. The merge-case flag is a constraint-shape question on the sub-spec, not a pushback on the ADR's decision section.
The Identity Contract v1.0.2 README bump is unaffected. ADR-0010 references both the sub-spec and the README; the README change is editorial.
Revenue's lock-state read API spec (revenue/docs/api/lock-state-read-v1/README.md) is unaffected. The lock-state read API does not surface external IDs; it surfaces lesson_id, person_id, credit_reservation_id, and lock state. If a future consumer needs to join lock-state reads to provider externals (e.g., a reconciliation tool that wants to map a Square refund webhook to the corresponding lock state), the consumer does two reads (Platform's reverse-lookup for the externals; Revenue's lock-state read for the reservation). No change to either surface required.
Revenue's other in-flight memos and contract work are unaffected. The credit-reservation-lock v1.1.0 amendment work (per 2026-05-08-revenue-refund-reason-codes-signoff) is independent. The Coaching split closures are independent.
References
Platform's memo this acknowledges: memos/2026/2026-05-02-platform-adr-0010-ack-request.md.
ADR-0010: coordination/adrs/ADR-0010-provider-externals-at-platform.md. The architectural decision Revenue is acknowledging.
Person Externals sub-spec: coordination/contracts/identity/person-externals.md. The schema Revenue is flagging the merge-case concern against. §3 (table schema, the unique constraint), §5.3 (write paths, where the merge re-keying would happen), §6 (consumer responsibilities Revenue accepts), §9 (migration mapping from Airtable).
Identity Contract v1.0.2: coordination/contracts/identity/README.md.
Person Resolution Semantics (the merge process Revenue's flag depends on): coordination/contracts/identity/person-resolution-semantics.md. §3.3 (merge rule, "older created_at wins as canonical"), and the merge workflow generally.
Revenue's prior closed leg on the Coaching-split thread (where Revenue flagged "Revenue resolves Person identity through Platform"): memos/2026/2026-05-05-revenue-coaching-split-ack-and-posture-update.md. Adjacent context.
Revenue domain doc: coordination/domains/revenue.md. The Q2 rock list this memo's timing posture rests on; the notable-directories list (/modules, /lib, /prisma) Revenue's action item 6 lands against.
ADR-0001 (tenant_id), ADR-0002 (entity ID prefixes including pex_), ADR-0003 (Person canonical, the Coach-to-Coaching amendment context).
Square integration today (Airtable era, the migration starting point): coordination/standards/airtable-design/ and the Provider Accounts schema referenced in the Tables by Domain manifest.