pub const MIGRATION_V5: &str = "-- Migration-owner only. Permanently binds one Dex source identity to one\n-- verified ATProto DID and its host-scoped deterministic internal UUID.\nBEGIN;\nDO $$\nBEGIN\n IF (SELECT count(*) FROM federate_session.schema_version WHERE version = 4) <> 1\n THEN RAISE EXCEPTION \'expected federate session schema v4\'; END IF;\nEND $$;\nCREATE TABLE federate_session.identity_bindings (\n host text NOT NULL,\n profile text NOT NULL CHECK (profile = \'federate-session-v1\'),\n issuer text NOT NULL CHECK (length(issuer) BETWEEN 1 AND 2048),\n source_subject text NOT NULL CHECK (length(source_subject) BETWEEN 1 AND 256),\n atproto_did text NOT NULL CHECK (length(atproto_did) BETWEEN 7 AND 255),\n account_id text NOT NULL CHECK (length(account_id) BETWEEN 1 AND 256),\n first_seen_at bigint NOT NULL CHECK (first_seen_at >= 0),\n PRIMARY KEY (host, profile, issuer, source_subject),\n UNIQUE (host, profile, atproto_did),\n UNIQUE (host, profile, account_id),\n FOREIGN KEY (host, profile)\n REFERENCES federate_session.profile_state(host, profile)\n);\n-- A non-null DID was already verified and persisted in v4. If it disagrees\n-- with the session subject, do not silently omit the row and backfill from a\n-- consistent subset: later admissions could make the omitted session appear\n-- to match a binding. Preserve only genuinely unverified NULL-DID history.\nDO $$\nBEGIN\n IF EXISTS (\n SELECT 1\n FROM federate_session.sessions s\n JOIN federate_session.replay r ON (r.host=s.host AND r.sid=s.sid)\n WHERE r.source_atproto_did IS NOT NULL\n AND s.subject IS DISTINCT FROM r.source_atproto_did\n ) THEN\n RAISE EXCEPTION \'v5 refuses inconsistent verified-DID session history\';\n END IF;\nEND $$;\n-- Preserve consistent v4 bindings, but reject any history that would require\n-- choosing which account or DID was canonical. Legacy rows without a verified\n-- DID remain unusable as primary records and are intentionally not guessed.\nINSERT INTO federate_session.identity_bindings\n (host, profile, issuer, source_subject, atproto_did, account_id, first_seen_at)\nSELECT s.host, s.profile, r.issuer, r.source_subject, r.source_atproto_did,\n s.account_id, min(s.created_at)\nFROM federate_session.sessions s\nJOIN federate_session.replay r ON (r.host=s.host AND r.sid=s.sid)\nWHERE r.source_atproto_did IS NOT NULL\n AND s.subject = r.source_atproto_did\nGROUP BY s.host, s.profile, r.issuer, r.source_subject,\n r.source_atproto_did, s.account_id;\nREVOKE ALL ON federate_session.identity_bindings FROM PUBLIC;\nALTER TABLE federate_session.schema_version\n DROP CONSTRAINT schema_version_version_check;\nUPDATE federate_session.schema_version SET version = 5 WHERE version = 4;\nALTER TABLE federate_session.schema_version\n ADD CONSTRAINT schema_version_version_check CHECK (version = 5);\nCOMMIT;\n";