mono
2f7a491666
Source-only publication of reviewed commit 64e96a7f423673dc29ad56485be9fc79bce8af1c.
Passed in isolation: focused frontend/backend tests, race checks, TypeScript typecheck, lint, Go compilation, Flowgram build, and deterministic SDK build/tests.
Not accepted as a production release: Next.js export/native assembly remains blocked by environment memory limits; browser interaction and MySQL staging acceptance remain unverified. No production server, database/schema, runtime configuration, deployment, release tag, or workflow execution was performed.
Expected source tree: 0a4a7528e9.
48 lines
2.6 KiB
SQL
48 lines
2.6 KiB
SQL
-- READ-ONLY preflight after the additive identity_scope column is present.
|
|
-- Run against an operator-approved backup/staging copy, not an implicit production
|
|
-- connection. Compatible with SQLite and MySQL. This file never changes data.
|
|
-- Results contain customer identifiers; keep them private and do not commit them.
|
|
|
|
-- 1. Any result is an ambiguous key. Runtime lookup fails closed for these keys,
|
|
-- including disabled mappings. Resolve ownership before adding a unique index.
|
|
SELECT identity_scope, external_source, external_id,
|
|
COUNT(*) AS mapping_count, COUNT(DISTINCT customer_id) AS owner_count
|
|
FROM t_customer_identity
|
|
GROUP BY identity_scope, external_source, external_id
|
|
HAVING COUNT(*) > 1;
|
|
|
|
-- 2. Inventory legacy mappings and their conversation channels. A single channel
|
|
-- is evidence to investigate, NOT proof of the issuer or ownership. Guest IDs
|
|
-- from unauthenticated exchange are never sufficient proof to recover history.
|
|
SELECT ci.id AS identity_id, ci.customer_id, ci.external_source, ci.external_id,
|
|
ci.status AS identity_status, c.channel_id AS channel_record_id,
|
|
ch.channel_id AS channel_code, COUNT(c.id) AS conversation_count
|
|
FROM t_customer_identity AS ci
|
|
LEFT JOIN t_conversation AS c ON c.customer_id = ci.customer_id
|
|
LEFT JOIN t_channel AS ch ON ch.id = c.channel_id
|
|
WHERE ci.identity_scope = ''
|
|
GROUP BY ci.id, ci.customer_id, ci.external_source, ci.external_id, ci.status,
|
|
c.channel_id, ch.channel_id
|
|
ORDER BY ci.id, c.channel_id;
|
|
|
|
-- 3. Mixed legacy/scoped mappings require explicit review of the legacy owner
|
|
-- and any new post-upgrade customer before reconciliation. Equal external_id
|
|
-- values across channels/issuers are not evidence of equal people.
|
|
SELECT legacy.id AS legacy_identity_id, legacy.customer_id AS legacy_customer_id,
|
|
scoped.id AS scoped_identity_id, scoped.customer_id AS scoped_customer_id,
|
|
scoped.identity_scope, legacy.external_source, legacy.external_id
|
|
FROM t_customer_identity AS legacy
|
|
JOIN t_customer_identity AS scoped
|
|
ON scoped.external_source = legacy.external_source
|
|
AND scoped.external_id = legacy.external_id
|
|
WHERE legacy.identity_scope = '' AND scoped.identity_scope <> ''
|
|
ORDER BY legacy.id, scoped.id;
|
|
|
|
-- 4. Scoped mappings without their unique digest require a reviewed key backfill.
|
|
-- Application-created scoped mappings always have a key. Never populate it by
|
|
-- hashing only external_id: include the scope and external source as implemented
|
|
-- by repositories.ScopedCustomerIdentityKey (JSON string array, SHA-256).
|
|
SELECT id, customer_id, identity_scope, external_source, external_id
|
|
FROM t_customer_identity
|
|
WHERE identity_scope <> '' AND scoped_key IS NULL;
|