Allow govern_policies rows without a namespace

What does this MR do and why?

First of four MRs splitting !248000 (closed), which had grown to 27 files across a schema migration, a model, the policy store gem, and an EE adapter, with no single reviewer owning all of it. This one carries the schema change alone.

MakeNamespaceIdNullableOnGovernPolicies makes govern_policies.namespace_id nullable, because a governance policy may be owned by an organization rather than by a top-level group. organization_id stays the sharding key in both cases, so nothing about Cells tenancy changes.

The index work follows from that. Postgres treats NULLs as distinct, so a single (organization_id, namespace_id, name) unique index would stop constraining organization-owned policies against each other entirely. It is replaced by two partial unique indexes, one per ownership form:

• unique_govern_policies_org_namespace_and_name, on (organization_id, namespace_id, name) WHERE namespace_id IS NOT NULL
• unique_govern_policies_org_and_name_without_namespace, on (organization_id, name) WHERE namespace_id IS NULL

Govern::Policy is deliberately untouched here. It keeps belongs_to :namespace, optional: false, so the model stays stricter than the column until the next MR in the stack relaxes it. That is a safe intermediate state: nothing can write a null namespace in between.

Design decisions

The composite index is in this migration rather than the next one. Both replacement unique indexes are partial, so neither covers the organization_id foreign key, and spec/db/schema_spec.rb fails on "all foreign keys are indexed". The composite (organization_id, trigger_type, lifecycle_state, id) index restores that coverage, still leads with the sharding key, and is the index the per-trigger evaluation read in the next MR will use. Adding it here avoids creating a placeholder single-column index now and dropping it immediately afterwards.

down is only reversible while every row still has a namespace, because restoring the NOT NULL constraint fails once an organization-owned policy exists. The table ships dark behind the policy store experiment gate and has no production rows, so nothing depends on that path today.

The unique index this migration drops is safe to drop. It was created by !247753 (merged) in this same milestone, on a table that has never been written to, and the two partial indexes replacing it cover the same rows between them. Nothing has had the chance to depend on it.

How to set up and validate locally

1. Migrate the sec database

bin/rails db:migrate:up:sec VERSION=20260806120000

2. Confirm the column is nullable and the indexes are in place

bin/rails dbconsole --database sec -p

\d govern_policies

Verify namespace_id is no longer marked not null, and that unique_govern_policies_org_namespace_and_name, unique_govern_policies_org_and_name_without_namespace and index_govern_policies_on_org_trigger_lifecycle_and_id are all listed.

3. Confirm each partial index constrains only its own ownership form. The model still requires a namespace at this point in the stack, so insert directly. On the rails console:

organization_id = Organizations::Organization.first.id
namespace_id = Group.first.id

insert_policy = ->(name, namespace) do
  Govern::Policy.connection.execute(<<~SQL)
    INSERT INTO govern_policies (organization_id, namespace_id, name, trigger_type, created_at, updated_at)
    VALUES (#{organization_id}, #{namespace || 'NULL'}, '#{name}', 0, now(), now())
  SQL
end

insert_policy.call('shared name', nil)          # organization-owned
insert_policy.call('shared name', namespace_id) # group-owned, same name

Verify both succeed, because the two ownership forms are constrained by different indexes and do not collide.

4. Verify the organization-owned index still rejects a duplicate

insert_policy.call('shared name', nil)
# ActiveRecord::RecordNotUnique: duplicate key value violates unique constraint
# "unique_govern_policies_org_and_name_without_namespace"

5. Clean up and roll back

Govern::Policy.where(name: 'shared name').delete_all

bin/rails db:migrate:down:sec VERSION=20260806120000

Verify the rollback succeeds, and that \d govern_policies shows namespace_id back to not null with the single non-partial unique index restored.

References

• Work item https://gitlab.com/gitlab-org/gitlab/-/work_items/604367
• Split out of !248000 (closed) (open)