-- Account administration (ADR-0182 section 6, 2026-10-27): bans, an admin logout and time -- played. A ban is an append-only row; the newest row per account decides. A banned account -- gets no grant (SQLSTATE EVL20, which the broker reports as account_banned), and banning -- also revokes its grants, so a live session closes at its next control renewal. Session -- intervals are recorded by the session-control functions the regions already call: a claim -- opens one, a renewal moves its last-seen time, a release closes it. A session that is -- never released ends at its last renewal. CREATE TABLE evolution.account_bans ( sequence bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, account_id uuid NOT NULL, banned boolean NOT NULL, author_subject text NOT NULL CHECK (octet_length(author_subject) BETWEEN 1 AND 255), reason text NOT NULL DEFAULT '' CHECK (octet_length(reason) <= 500), created_at timestamptz NOT NULL DEFAULT clock_timestamp() ); CREATE INDEX account_bans_by_account ON evolution.account_bans(account_id, sequence); CREATE TRIGGER account_bans_immutable BEFORE UPDATE OR DELETE ON evolution.account_bans FOR EACH ROW EXECUTE FUNCTION evolution.refuse_history_rewrite(); CREATE TABLE evolution.session_intervals ( world_id evolution.u64 NOT NULL, account_id uuid NOT NULL, generation evolution.u64 NOT NULL, region_id evolution.u64 NOT NULL, started_at timestamptz NOT NULL, last_seen timestamptz NOT NULL, ended_at timestamptz, PRIMARY KEY (world_id, account_id, generation) ); CREATE FUNCTION evolution.account_banned(p_account uuid) RETURNS boolean LANGUAGE sql STABLE SET search_path = pg_catalog, pg_temp AS $$ SELECT coalesce((SELECT b.banned FROM evolution.account_bans b WHERE b.account_id = p_account ORDER BY b.sequence DESC LIMIT 1), false) $$; REVOKE ALL ON FUNCTION evolution.account_banned(uuid) FROM PUBLIC; -- Raises the account's accepted serial past every grant issued and expires its control, as -- revoke_account_grants does for a Keycloak identity. CREATE FUNCTION evolution.revoke_account_by_id(p_world evolution.u64, p_account uuid) RETURNS boolean LANGUAGE plpgsql SET search_path = pg_catalog, pg_temp AS $$ DECLARE r evolution.account_identities%ROWTYPE; BEGIN SELECT * INTO r FROM evolution.account_identities x WHERE x.account_id = p_account FOR UPDATE; IF NOT FOUND THEN UPDATE evolution.account_controls a SET expires_at = CASE WHEN a.region_id IS NULL THEN NULL ELSE least(a.expires_at, clock_timestamp()) END WHERE a.world_id = p_world AND a.account_id = p_account; RETURN FOUND; END IF; INSERT INTO evolution.account_controls AS a (world_id, account_id, serial, generation) VALUES (p_world, r.account_id, greatest(r.last_serial, 1), 1) ON CONFLICT (world_id, account_id) DO UPDATE SET serial = greatest(a.serial, EXCLUDED.serial), expires_at = CASE WHEN a.region_id IS NULL THEN NULL ELSE least(a.expires_at, clock_timestamp()) END; RETURN true; END $$; REVOKE ALL ON FUNCTION evolution.revoke_account_by_id(evolution.u64, uuid) FROM PUBLIC; -- Admin: ban or lift a ban (recorded with who and why); a ban also logs the account out. CREATE FUNCTION evolution.admin_set_account_ban_v1(p_world evolution.u64, p_account uuid, p_banned boolean, p_subject text, p_reason text) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ BEGIN IF p_world IS NULL OR p_world = 0 OR p_account IS NULL OR p_banned IS NULL THEN RAISE EXCEPTION 'invalid ban' USING ERRCODE = '22023'; END IF; INSERT INTO evolution.account_bans(account_id, banned, author_subject, reason) VALUES (p_account, p_banned, p_subject, coalesce(p_reason, '')); IF p_banned THEN PERFORM evolution.revoke_account_by_id(p_world, p_account); END IF; RETURN p_banned; END $$; REVOKE ALL ON FUNCTION evolution.admin_set_account_ban_v1(evolution.u64, uuid, boolean, text, text) FROM PUBLIC; -- Admin: end the account's sessions now (its next login needs a new grant). CREATE FUNCTION evolution.admin_logout_account_v1(p_world evolution.u64, p_account uuid) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ BEGIN IF p_world IS NULL OR p_world = 0 OR p_account IS NULL THEN RAISE EXCEPTION 'invalid account' USING ERRCODE = '22023'; END IF; RETURN evolution.revoke_account_by_id(p_world, p_account); END $$; REVOKE ALL ON FUNCTION evolution.admin_logout_account_v1(evolution.u64, uuid) FROM PUBLIC; -- Admin: each account's ban state, time played and last session activity in the world. CREATE FUNCTION evolution.admin_account_status_v1(p_world evolution.u64) RETURNS TABLE (account_id uuid, banned boolean, played_seconds bigint, last_seen timestamptz, sessions bigint) LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ WITH accounts AS ( SELECT i.account_id FROM evolution.account_identities i UNION SELECT s.account_id FROM evolution.session_intervals s WHERE s.world_id = p_world UNION SELECT b.account_id FROM evolution.account_bans b ) SELECT a.account_id, evolution.account_banned(a.account_id), coalesce((SELECT floor(sum(extract(epoch FROM coalesce(s.ended_at, s.last_seen) - s.started_at)))::bigint FROM evolution.session_intervals s WHERE s.world_id = p_world AND s.account_id = a.account_id), 0), (SELECT max(coalesce(s.ended_at, s.last_seen)) FROM evolution.session_intervals s WHERE s.world_id = p_world AND s.account_id = a.account_id), (SELECT count(*) FROM evolution.session_intervals s WHERE s.world_id = p_world AND s.account_id = a.account_id) FROM accounts a LIMIT 10000 $$; REVOKE ALL ON FUNCTION evolution.admin_account_status_v1(evolution.u64) FROM PUBLIC; -- Admin: one account's ban history, newest first. CREATE FUNCTION evolution.admin_account_bans_v1(p_account uuid) RETURNS TABLE (banned boolean, author_subject text, reason text, created_at timestamptz) LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ SELECT b.banned, b.author_subject, b.reason, b.created_at FROM evolution.account_bans b WHERE b.account_id = p_account ORDER BY b.sequence DESC LIMIT 50 $$; REVOKE ALL ON FUNCTION evolution.admin_account_bans_v1(uuid) FROM PUBLIC; -- A banned account gets no grant. ALTER FUNCTION evolution.issue_account_grant(evolution.u64, text, text) RENAME TO issue_account_grant_v63; REVOKE ALL ON FUNCTION evolution.issue_account_grant_v63(evolution.u64, text, text) FROM PUBLIC, :"runtime_role"; CREATE FUNCTION evolution.issue_account_grant(p_world evolution.u64, p_issuer text, p_subject text) RETURNS TABLE(grant_account uuid, grant_serial evolution.u64, home_region evolution.u64) LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE known uuid; BEGIN SELECT x.account_id INTO known FROM evolution.account_identities x WHERE x.issuer = p_issuer AND x.subject = p_subject; IF known IS NOT NULL AND evolution.account_banned(known) THEN RAISE EXCEPTION 'account banned' USING ERRCODE = 'EVL20'; END IF; RETURN QUERY SELECT * FROM evolution.issue_account_grant_v63(p_world, p_issuer, p_subject); END $$; REVOKE ALL ON FUNCTION evolution.issue_account_grant(evolution.u64, text, text) FROM PUBLIC; GRANT EXECUTE ON FUNCTION evolution.issue_account_grant(evolution.u64, text, text) TO :"runtime_role"; -- Session intervals from the control functions (signatures unchanged). ALTER FUNCTION evolution.claim_account_control(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64, evolution.u64, integer) RENAME TO claim_account_control_v63; REVOKE ALL ON FUNCTION evolution.claim_account_control_v63(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64, evolution.u64, integer) FROM PUBLIC, :"runtime_role"; CREATE FUNCTION evolution.claim_account_control(p_world evolution.u64, p_region evolution.u64, p_holder uuid, p_epoch evolution.u64, p_account uuid, p_serial evolution.u64, p_session evolution.u64, p_duration_ms integer) RETURNS TABLE (control_generation evolution.u64, wait_ms integer, granted_ms integer) LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE r record; BEGIN FOR r IN SELECT * FROM evolution.claim_account_control_v63(p_world, p_region, p_holder, p_epoch, p_account, p_serial, p_session, p_duration_ms) LOOP IF r.granted_ms > 0 THEN INSERT INTO evolution.session_intervals(world_id, account_id, generation, region_id, started_at, last_seen) VALUES (p_world, p_account, r.control_generation, p_region, clock_timestamp(), clock_timestamp()) ON CONFLICT (world_id, account_id, generation) DO NOTHING; END IF; control_generation := r.control_generation; wait_ms := r.wait_ms; granted_ms := r.granted_ms; RETURN NEXT; END LOOP; END $$; REVOKE ALL ON FUNCTION evolution.claim_account_control(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64, evolution.u64, integer) FROM PUBLIC; GRANT EXECUTE ON FUNCTION evolution.claim_account_control(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64, evolution.u64, integer) TO :"runtime_role"; ALTER FUNCTION evolution.renew_account_control(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64, integer) RENAME TO renew_account_control_v63; REVOKE ALL ON FUNCTION evolution.renew_account_control_v63(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64, integer) FROM PUBLIC, :"runtime_role"; CREATE FUNCTION evolution.renew_account_control(p_world evolution.u64, p_region evolution.u64, p_holder uuid, p_epoch evolution.u64, p_account uuid, p_generation evolution.u64, p_duration_ms integer) RETURNS integer LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE granted integer; BEGIN granted := evolution.renew_account_control_v63(p_world, p_region, p_holder, p_epoch, p_account, p_generation, p_duration_ms); UPDATE evolution.session_intervals s SET last_seen = clock_timestamp() WHERE s.world_id = p_world AND s.account_id = p_account AND s.generation = p_generation AND s.ended_at IS NULL; RETURN granted; END $$; REVOKE ALL ON FUNCTION evolution.renew_account_control(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64, integer) FROM PUBLIC; GRANT EXECUTE ON FUNCTION evolution.renew_account_control(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64, integer) TO :"runtime_role"; ALTER FUNCTION evolution.release_account_control(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64) RENAME TO release_account_control_v63; REVOKE ALL ON FUNCTION evolution.release_account_control_v63(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64) FROM PUBLIC, :"runtime_role"; CREATE FUNCTION evolution.release_account_control(p_world evolution.u64, p_region evolution.u64, p_holder uuid, p_epoch evolution.u64, p_account uuid, p_generation evolution.u64) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE released boolean; BEGIN released := evolution.release_account_control_v63(p_world, p_region, p_holder, p_epoch, p_account, p_generation); UPDATE evolution.session_intervals s SET last_seen = clock_timestamp(), ended_at = clock_timestamp() WHERE s.world_id = p_world AND s.account_id = p_account AND s.generation = p_generation AND s.ended_at IS NULL; RETURN released; END $$; REVOKE ALL ON FUNCTION evolution.release_account_control(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64) FROM PUBLIC; GRANT EXECUTE ON FUNCTION evolution.release_account_control(evolution.u64, evolution.u64, uuid, evolution.u64, uuid, evolution.u64) TO :"runtime_role"; INSERT INTO evolution.schema_migrations(version,sha256) VALUES(64,:'migration_sha_64');