Skip to main content

MIGRATION_V5

Constant MIGRATION_V5 

Source
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";