Add agent_identity_id to ai_governance_sessions

Hitesh Raghuvanshirequested to merge
630711-ai-governance-sessions-agent-identity-id into master

What does this MR do and why?

Adds a nullable agent_identity_id column to ai_governance_sessions, with a partial index on non-null values and a foreign key to ai_agent_identities that sets it to null when the identity is deleted.

It links each session to the registered agent install, so the AI Governance dashboard can count external agents per machine.

  • Matches duo_workflows_workflows.agent_identity_id, which uses the same foreign key.
  • The matching ClickHouse column is handled separately.

References

Screenshots or screen recordings

Not applicable. Database and model change only.

Database

Click to expand Migration command output 
### up

bundle exec rake db:migrate:up_all VERSION=20260924115359
main: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9849
main: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: migrating =========
main: -- add_column(:ai_governance_sessions, :agent_identity_id, :bigint)
main:    -> 0.0609s
main: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: migrated (0.0702s) 
main: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9849
ci: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9858
ci: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: migrating =========
ci: -- add_column(:ai_governance_sessions, :agent_identity_id, :bigint)
ci:    -> 0.0400s
ci: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: migrated (0.0527s) 
ci: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9858
sec: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9863
sec: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: migrating =========
sec: -- add_column(:ai_governance_sessions, :agent_identity_id, :bigint)
sec:    -> 0.0027s
sec: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: migrated (0.0133s) 
sec: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9863

bundle exec rake db:migrate:up_all VERSION=20260924115423
main: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 11016
main: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: migrating ====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0720s
main: -- index_exists?(:ai_governance_sessions, :agent_identity_id, {:name=>"index_ai_governance_sessions_on_agent_identity_id", :where=>"agent_identity_id IS NOT NULL", :algorithm=>:concurrently})
main:    -> 0.0085s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0553s
main: -- add_index(:ai_governance_sessions, :agent_identity_id, {:name=>"index_ai_governance_sessions_on_agent_identity_id", :where=>"agent_identity_id IS NOT NULL", :algorithm=>:concurrently})
main:    -> 0.0130s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: migrated (0.1866s) 
main: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 11016
ci: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 11017
ci: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: migrating ====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- index_exists?(:ai_governance_sessions, :agent_identity_id, {:name=>"index_ai_governance_sessions_on_agent_identity_id", :where=>"agent_identity_id IS NOT NULL", :algorithm=>:concurrently})
ci:    -> 0.0078s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- add_index(:ai_governance_sessions, :agent_identity_id, {:name=>"index_ai_governance_sessions_on_agent_identity_id", :where=>"agent_identity_id IS NOT NULL", :algorithm=>:concurrently})
ci:    -> 0.0073s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: migrated (0.0819s) 
ci: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 11017
sec: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 11020
sec: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: migrating ====
sec: -- transaction_open?(nil)
sec:    -> 0.0000s
sec: -- view_exists?(:postgres_partitions)
sec:    -> 0.0008s
sec: -- index_exists?(:ai_governance_sessions, :agent_identity_id, {:name=>"index_ai_governance_sessions_on_agent_identity_id", :where=>"agent_identity_id IS NOT NULL", :algorithm=>:concurrently})
sec:    -> 0.0048s
sec: -- execute("SET statement_timeout TO 0")
sec:    -> 0.0005s
sec: -- add_index(:ai_governance_sessions, :agent_identity_id, {:name=>"index_ai_governance_sessions_on_agent_identity_id", :where=>"agent_identity_id IS NOT NULL", :algorithm=>:concurrently})
sec:    -> 0.0058s
sec: -- execute("RESET statement_timeout")
sec:    -> 0.0003s
sec: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: migrated (0.0410s) 
sec: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 11020

bundle exec rake db:migrate:up_all VERSION=20260925110319
main: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59779
main: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: migrating =======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE ai_agent_identities, ai_governance_sessions IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0006s
main: -- execute("ALTER TABLE ai_governance_sessions ADD CONSTRAINT fk_4090b6612a FOREIGN KEY (agent_identity_id) REFERENCES ai_agent_identities (id) ON DELETE SET NULL NOT VALID;")
main:    -> 0.0029s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- execute("ALTER TABLE ai_governance_sessions VALIDATE CONSTRAINT fk_4090b6612a;")
main:    -> 0.0032s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: migrated (0.1483s) 
main: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59779
ci: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59780
ci: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: migrating =======
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE ai_agent_identities, ai_governance_sessions IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE ai_governance_sessions ADD CONSTRAINT fk_4090b6612a FOREIGN KEY (agent_identity_id) REFERENCES ai_agent_identities (id) ON DELETE SET NULL NOT VALID;")
ci:    -> 0.0029s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE ai_governance_sessions VALIDATE CONSTRAINT fk_4090b6612a;")
ci:    -> 0.0032s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: migrated (0.0339s) 
ci: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59780
sec: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59782
sec: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: migrating =======
sec: -- transaction_open?(nil)
sec:    -> 0.0000s
sec: -- transaction_open?(nil)
sec:    -> 0.0000s
sec: -- execute("LOCK TABLE ai_agent_identities, ai_governance_sessions IN SHARE ROW EXCLUSIVE MODE")
sec:    -> 0.0004s
sec: -- execute("ALTER TABLE ai_governance_sessions ADD CONSTRAINT fk_4090b6612a FOREIGN KEY (agent_identity_id) REFERENCES ai_agent_identities (id) ON DELETE SET NULL NOT VALID;")
sec:    -> 0.0023s
sec: -- execute("SET statement_timeout TO 0")
sec:    -> 0.0003s
sec: -- execute("ALTER TABLE ai_governance_sessions VALIDATE CONSTRAINT fk_4090b6612a;")
sec:    -> 0.0029s
sec: -- execute("RESET statement_timeout")
sec:    -> 0.0004s
sec: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: migrated (0.0309s) 
sec: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59782

### down

bundle exec rake db:migrate:down_all VERSION=20260925110319
main: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 58999
main: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: reverting =======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:ai_governance_sessions, {:column=>:agent_identity_id})
main:    -> 0.0033s
main: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: reverted (0.1587s) 
main: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 58999
ci: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59004
ci: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: reverting =======
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:ai_governance_sessions, {:column=>:agent_identity_id})
ci:    -> 0.0025s
ci: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: reverted (0.0611s) 
ci: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59004
sec: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59006
sec: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: reverting =======
sec: -- transaction_open?(nil)
sec:    -> 0.0000s
sec: -- remove_foreign_key(:ai_governance_sessions, {:column=>:agent_identity_id})
sec:    -> 0.0024s
sec: == 20260925110319 AddFkAgentIdentityIdToAiGovernanceSessions: reverted (0.0410s) 
sec: == [advisory_lock_connection] object_id: 165000, pg_backend_pid: 59006

bundle exec rake db:migrate:down_all VERSION=20260924115423
main: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9025
main: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: reverting ====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0459s
main: -- index_name_exists?(:ai_governance_sessions, "index_ai_governance_sessions_on_agent_identity_id")
main:    -> 0.0014s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:ai_governance_sessions, {:algorithm=>:concurrently, :name=>"index_ai_governance_sessions_on_agent_identity_id"})
main:    -> 0.0033s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: reverted (0.0744s) 
main: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9025
ci: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9026
ci: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: reverting ====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_name_exists?(:ai_governance_sessions, "index_ai_governance_sessions_on_agent_identity_id")
ci:    -> 0.0018s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- remove_index(:ai_governance_sessions, {:algorithm=>:concurrently, :name=>"index_ai_governance_sessions_on_agent_identity_id"})
ci:    -> 0.0033s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: reverted (0.0586s) 
ci: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9026
sec: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9028
sec: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: reverting ====
sec: -- transaction_open?(nil)
sec:    -> 0.0000s
sec: -- view_exists?(:postgres_partitions)
sec:    -> 0.0004s
sec: -- index_name_exists?(:ai_governance_sessions, "index_ai_governance_sessions_on_agent_identity_id")
sec:    -> 0.0016s
sec: -- execute("SET statement_timeout TO 0")
sec:    -> 0.0003s
sec: -- remove_index(:ai_governance_sessions, {:algorithm=>:concurrently, :name=>"index_ai_governance_sessions_on_agent_identity_id"})
sec:    -> 0.0023s
sec: -- execute("RESET statement_timeout")
sec:    -> 0.0003s
sec: == 20260924115423 AddIndexOnAiGovernanceSessionsAgentIdentityId: reverted (0.0228s) 
sec: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9028

bundle exec rake db:migrate:down_all VERSION=20260924115359
main: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9511
main: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: reverting =========
main: -- remove_column(:ai_governance_sessions, :agent_identity_id, :bigint)
main:    -> 0.0468s
main: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: reverted (0.0576s) 
main: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9511
ci: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9512
ci: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: reverting =========
ci: -- remove_column(:ai_governance_sessions, :agent_identity_id, :bigint)
ci:    -> 0.0023s
ci: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: reverted (0.0134s) 
ci: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9512
sec: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9514
sec: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: reverting =========
sec: -- remove_column(:ai_governance_sessions, :agent_identity_id, :bigint)
sec:    -> 0.0025s
sec: == 20260924115359 AddAgentIdentityIdToAiGovernanceSessions: reverted (0.0195s) 
sec: == [advisory_lock_connection] object_id: 164960, pg_backend_pid: 9514

How to set up and validate locally

  1. Run bin/rails db:migrate.

  2. In a Rails console, check the association:

    Ai::Governance::Session.reflect_on_association(:agent_identity)

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Hitesh Raghuvanshi

Merge request reports