Add Anthropic Compliance transcript walk state table

Hitesh Raghuvanshirequested to merge
628485-anthropic-compliance-session-table into master

What does this MR do and why?

Adds ai_compliance_anthropic_sessions, which holds transcript walk state for each Claude Code session the Anthropic Compliance API ingests: the newest message id read (message_watermark) and when the current walk started (walk_started_at).

Each row belongs to exactly one ai_governance_sessions row through a unique ai_governance_session_id. The transcript job looks rows up by that id, and rows are deleted with their governance session, integration, or namespace. The model fills namespace_id from the integration and checks that it matches both the integration's and the governance session's namespace.

Session deduplication is handled on ai_governance_sessions, so there is no unique index on session_xid. The column is kept so a stalled walk can be diagnosed without a join.

Nothing writes to the table yet. The transcript worker (https://gitlab.com/gitlab-org/gitlab/-/work_items/623178) will create a row the first time it walks a session and update it on later walks. The session creation service (https://gitlab.com/gitlab-org/gitlab/-/work_items/623175) only creates the ai_governance_sessions row. Pruning is https://gitlab.com/gitlab-org/gitlab/-/work_items/628488.

Migration layout

The table is created with its three indexes and no foreign keys. Each of the three foreign keys (namespaces, ai_compliance_anthropic_integrations, ai_governance_sessions) is then added in its own migration, so no single migration locks more than one table. All three cascade on delete.

References

How to set up and validate locally

  1. bundle exec rails db:migrate
  2. bundle exec rspec ee/spec/models/ai/compliance/anthropic/session_spec.rb

New table: ai_compliance_anthropic_sessions

Answers to the database review guide for new tables. Volume is based on the design document estimate, which is not measured yet. Real volume is being measured in https://gitlab.com/gitlab-org/gitlab/-/work_items/623183.

Assumptions

  • One row per Claude Code session the transcript job walks. The row is created on the first walk.
  • One large customer (1,000 developers, 10 sessions per developer per day) = 10,000 new rows a day.
  • The cron runs every 6 hours. Each session is walked about 2 times a day.
  • Rows are kept for 30 days from creation. Pruning is tracked in https://gitlab.com/gitlab-org/gitlab/-/work_items/628488. Until it ships (enforcing: false), rows only leave when their governance session, integration, or namespace is deleted.
  • About 260 bytes per row including the four indexes (three plus the primary key). (Guessed: 4 bigints, 3 timestamps, two ~32-character text ids, tuple header.)
  • Number of large customers using it: guessed at 3 after 3 months, 10 after 6 months, 25 after 12 months.

Expected growth

3 months 6 months 12 months
Large customers (guessed) 3 10 25
Rows with 30-day pruning (customers × 10,000 × 30) 0.9 M 3 M 7.5 M
Size with pruning (rows × 260 B) ~0.2 GB ~0.8 GB ~2 GB
Rows if pruning has not shipped (customers × 10,000 × days live) 2.7 M 18 M 91 M

With pruning, the table stays under the small size class. Without pruning, it would reach about 24 GB at 12 months, so the pruning follow-up needs to ship before adoption grows.

Reads and writes per hour (one large customer)

Operation Per day Per hour (average) How
Insert 10,000 ~420 First walk of a session
Update ~40,000 ~1,700 2 walks × 2 updates (set walk_started_at at the start; set message_watermark and clear walk_started_at at the end)
Read by ai_governance_session_id ~20,000 to 40,000 ~830 to 1,700 Start and end of each walk

Multiply by the number of customers. At 25 customers this is roughly 10,000 inserts, 42,000 updates and up to 42,000 reads an hour. Load comes in bursts after each 6-hour cron run, so the peak is several times the hourly average. (The burst factor is a guess; transcript workers use concurrency_limit.)

Update pattern

  • Insert once, then update on every walk. Only walk_started_at, message_watermark and updated_at change. None of them is indexed, so these updates can be HOT updates (updates that don't touch indexes).
  • Rows are deleted by cascade from ai_governance_sessions, ai_compliance_anthropic_integrations and namespaces, and later by 30-day pruning.

Availability risk

  • No web request reads this table. Only the Anthropic Compliance transcript Sidekiq worker uses it (added in a follow-up MR, behind a feature flag). The AI Governance page reads ai_governance_sessions and ClickHouse.
  • If the table is unavailable, transcript jobs fail and Sidekiq retries them. The next 6-hour run walks the sessions again. Nothing is lost while the outage is shorter than the 12-hour list window.
  • If a row is lost, the next walk has no watermark and reads the whole transcript again. Events are deduplicated by cloud_event_id, so no duplicate rows are stored, but the whole session is sent to the customer's audit event stream (SIEM) a second time.

Migration output

Up 
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 45068
main: == 20261008154500 CreateAiComplianceAnthropicSessions: migrating ==============
main: -- create_table(:ai_compliance_anthropic_sessions)
main: -- quote_column_name(:session_xid)
main:    -> 0.0000s
main: -- quote_column_name(:message_watermark)
main:    -> 0.0000s
main:    -> 0.0556s
main: == 20261008154500 CreateAiComplianceAnthropicSessions: migrated (0.0661s) =====
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 45068

main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 45246
main: == 20261008154504 AddFkAiComplianceAnthropicSessionsToNamespaces: migrating ===
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE namespaces, ai_compliance_anthropic_sessions IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0004s
main: -- execute("ALTER TABLE ai_compliance_anthropic_sessions ADD CONSTRAINT fk_bcc0d20c6c FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0039s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE ai_compliance_anthropic_sessions VALIDATE CONSTRAINT fk_bcc0d20c6c;")
main:    -> 0.0158s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20261008154504 AddFkAiComplianceAnthropicSessionsToNamespaces: migrated (0.0949s) 
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 45246

main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 45465
main: == 20261008154508 AddFkAiComplianceAnthropicSessionsToIntegrations: migrating =
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE ai_compliance_anthropic_integrations, ai_compliance_anthropic_sessions IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0003s
main: -- execute("ALTER TABLE ai_compliance_anthropic_sessions ADD CONSTRAINT fk_2b8b35da36 FOREIGN KEY (anthropic_integration_id) REFERENCES ai_compliance_anthropic_integrations (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0015s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE ai_compliance_anthropic_sessions VALIDATE CONSTRAINT fk_2b8b35da36;")
main:    -> 0.0021s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20261008154508 AddFkAiComplianceAnthropicSessionsToIntegrations: migrated (0.0712s) 
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 45465

main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 45786
main: == 20261008154512 AddFkAiComplianceAnthropicSessionsToGovernanceSessions: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE ai_governance_sessions, ai_compliance_anthropic_sessions IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0004s
main: -- execute("ALTER TABLE ai_compliance_anthropic_sessions ADD CONSTRAINT fk_a6ee74a7af FOREIGN KEY (ai_governance_session_id) REFERENCES ai_governance_sessions (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0016s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE ai_compliance_anthropic_sessions VALIDATE CONSTRAINT fk_a6ee74a7af;")
main:    -> 0.0040s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20261008154512 AddFkAiComplianceAnthropicSessionsToGovernanceSessions: migrated (0.0729s) 
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 45786
Down 
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 43997
main: == 20261008154512 AddFkAiComplianceAnthropicSessionsToGovernanceSessions: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:ai_compliance_anthropic_sessions, {:column=>:ai_governance_session_id})
main:    -> 0.0049s
main: == 20261008154512 AddFkAiComplianceAnthropicSessionsToGovernanceSessions: reverted (0.0978s) 
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 43997

main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 44234
main: == 20261008154508 AddFkAiComplianceAnthropicSessionsToIntegrations: reverting =
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:ai_compliance_anthropic_sessions, {:column=>:anthropic_integration_id})
main:    -> 0.0022s
main: == 20261008154508 AddFkAiComplianceAnthropicSessionsToIntegrations: reverted (0.0687s) 
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 44234

main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 44436
main: == 20261008154504 AddFkAiComplianceAnthropicSessionsToNamespaces: reverting ===
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:ai_compliance_anthropic_sessions, {:column=>:namespace_id})
main:    -> 0.0038s
main: == 20261008154504 AddFkAiComplianceAnthropicSessionsToNamespaces: reverted (0.0766s) 
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 44436

main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 44655
main: == 20261008154500 CreateAiComplianceAnthropicSessions: reverting ==============
main: -- drop_table(:ai_compliance_anthropic_sessions)
main:    -> 0.0389s
main: == 20261008154500 CreateAiComplianceAnthropicSessions: reverted (0.0488s) =====
main: == [advisory_lock_connection] object_id: 166040, pg_backend_pid: 44655

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Edited by Hitesh Raghuvanshi

Merge request reports