Backfill duo_secret_detection_fp_enabled to false
What does this MR do and why?
duo_secret_detection_fp_enabled was added to project_settings in 18.10 with default: true, null: false. Postgres applied that default to every existing row at migration time, so all pre-existing projects were set to true on upgrade. In 18.11 the default was changed to false via change_column_default, but that only affects new rows. Existing rows kept true, and there was no backfill.
The result: any instance that upgraded through 18.10 has all pre-existing projects set to true. Once the feature is enabled at the group level, those projects are included without anyone making a project-level choice, which defeats the per-project opt-in.
This MR queues a batched background migration that resets the column to false for rows created before the 18.11 default change ran. Rows created afterwards already default to false, so a true value there is a deliberate opt-in and is left untouched. We also leave a row alone when the Secret Detection False Positive Detection foundational flow (secrets_fp_detection/v1) is enabled, since enabling that flow is a deliberate opt-in.
Query plan
A row is reset only when the secret-FP foundational flow is not enabled on the project itself, read from the project's own materialised enabled_foundational_flows rows (catalog item ai_catalog_items.foundational_flow_reference = 'secrets_fp_detection/v1', not soft-deleted). Enabling the flow at a group cascades a per-project row to every descendant, so a genuine opt-in carries its own row, and the check stays a single-table NOT EXISTS with no projects/namespaces join. The accepted gap and the earlier recursive resolution we moved away from are covered under "Known simplifications" and in the folded attempts below.
Batching uses the cursor strategy (cursor :project_id), and the sub-batch is wrapped in a MATERIALIZED CTE (batch) so the plan stays stable across batches.
WITH batch AS MATERIALIZED (
SELECT "project_settings"."project_id"
FROM "project_settings"
WHERE "project_settings"."project_id" >= 1
AND "project_settings"."project_id" <= 5000
ORDER BY "project_settings"."project_id" ASC
LIMIT 5000
),
fp_items AS MATERIALIZED (
SELECT id FROM ai_catalog_items
WHERE foundational_flow_reference = 'secrets_fp_detection/v1'
AND deleted_at IS NULL
)
UPDATE project_settings ps
SET duo_secret_detection_fp_enabled = FALSE
WHERE ps.project_id IN (SELECT project_id FROM batch)
AND ps.duo_secret_detection_fp_enabled = TRUE
AND ps.created_at < '2026-04-13 07:44:00+01:00'
AND NOT EXISTS (
SELECT 1
FROM enabled_foundational_flows efp
WHERE efp.project_id = ps.project_id
AND efp.catalog_item_id IN (SELECT id FROM fp_items)
)Database Lab plan
Run on gitlab-production-main (warm clone), sub-batch project_id 1..5000 (sub_batch_size). Report: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/53376/commands/155391
- Time: 1.809 s total (planning 3.249 ms, execution 1.806 s)
- Shared buffers: 48,126 hits (~376 MiB), 3,091 reads, 3,062 dirtied, 30 written
- WAL: 10,726 records, 3,027 fpi, ~24.6 MB
ModifyTable on public.project_settings ps (cost=140.40..10453.81 rows=0 width=0) (actual time=1805.489..1805.496 rows=0 loops=1)
Buffers: shared hit=48126 read=3091 dirtied=3062 written=30
WAL: records=10726 fpi=3027 bytes=24602363
CTE batch
-> Limit (cost=0.56..73.06 rows=2800 width=4) (actual time=0.043..96.190 rows=2456 loops=1)
Buffers: shared hit=147 read=112 dirtied=54
WAL: records=82 fpi=54 bytes=438732
-> Index Only Scan using project_settings_pkey on public.project_settings (cost=0.56..73.06 rows=2800 width=4) (actual time=0.041..95.962 rows=2456 loops=1)
Index Cond: ((project_settings.project_id >= 1) AND (project_settings.project_id <= 5000))
Heap Fetches: 120
Buffers: shared hit=147 read=112 dirtied=54
WAL: records=82 fpi=54 bytes=438732
CTE fp_items
-> Index Scan using index_ai_catalog_items_on_foundational_flow_reference on public.ai_catalog_items (cost=0.29..3.30 rows=1 width=8) (actual time=2.340..2.341 rows=1 loops=1)
Index Cond: (ai_catalog_items.foundational_flow_reference = 'secrets_fp_detection/v1'::text)
Filter: (ai_catalog_items.deleted_at IS NULL)
Rows Removed by Filter: 0
Buffers: shared read=3
-> Nested Loop Anti Join (cost=64.03..10377.45 rows=2525 width=73) (actual time=101.653..1219.853 rows=2456 loops=1)
Buffers: shared hit=17828 read=2085 dirtied=106
WAL: records=134 fpi=106 bytes=859628
-> Nested Loop (cost=63.56..10087.00 rows=2558 width=38) (actual time=99.212..1201.452 rows=2456 loops=1)
Buffers: shared hit=10457 read=2082 dirtied=106
WAL: records=134 fpi=106 bytes=859628
-> HashAggregate (cost=63.00..91.00 rows=2800 width=32) (actual time=98.610..100.921 rows=2456 loops=1)
Group Key: batch.project_id
Buffers: shared hit=147 read=112 dirtied=54
WAL: records=82 fpi=54 bytes=438732
-> CTE Scan on batch (cost=0.00..56.00 rows=2800 width=32) (actual time=0.061..97.329 rows=2456 loops=1)
Buffers: shared hit=147 read=112 dirtied=54
WAL: records=82 fpi=54 bytes=438732
-> Index Scan using project_settings_pkey on public.project_settings ps (cost=0.56..3.57 rows=1 width=10) (actual time=0.447..0.447 rows=1 loops=2456)
Index Cond: (ps.project_id = batch.project_id)
Filter: (ps.duo_secret_detection_fp_enabled AND (ps.created_at < '2026-04-13 06:44:00+00'::timestamp with time zone))
Rows Removed by Filter: 0
Buffers: shared hit=10310 read=1970 dirtied=52
WAL: records=52 fpi=52 bytes=420896
-> Hash Semi Join (cost=0.47..0.57 rows=1 width=46) (actual time=0.006..0.006 rows=0 loops=2456)
Hash Cond: (efp.catalog_item_id = fp_items.id)
Buffers: shared hit=7371 read=3
-> Index Scan using index_enabled_foundational_flows_on_project_id on public.enabled_foundational_flows efp (cost=0.43..0.52 rows=5 width=22) (actual time=0.004..0.004 rows=0 loops=2456)
Index Cond: (efp.project_id = ps.project_id)
Buffers: shared hit=7371
-> Hash (cost=0.02..0.02 rows=1 width=40) (actual time=2.358..2.359 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared read=3
-> CTE Scan on fp_items (cost=0.00..0.02 rows=1 width=40) (actual time=2.349..2.351 rows=1 loops=1)
Buffers: shared read=3
Settings: effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4'Earlier query attempts (superseded, kept for reference, no need to read unless curious)
These resolved enablement via the full application read path (the project's own rows, else a recursive walk up the namespace ancestors). They are correct but slower on production-scale data; the cost sat in the projects to namespaces base join and the per-row ancestor resolution, not the write. The materialised-only check above replaced them.
1. Correlated subquery: 4.233 s (session 53110/154848, sub-batch 1..5000, ~2,456 rows)
UPDATE project_settings ps
SET duo_secret_detection_fp_enabled = FALSE
FROM projects p
JOIN namespaces grp ON grp.id = p.namespace_id
WHERE ps.project_id = p.id
AND ps.project_id IN (SELECT "project_settings"."project_id" FROM "project_settings" WHERE "project_settings"."project_id" BETWEEN 1 AND 38 AND "project_settings"."project_id" >= 1)
AND ps.duo_secret_detection_fp_enabled = TRUE
AND ps.created_at < '2026-04-10 12:00:00+00:00'
AND COALESCE(
CASE
WHEN EXISTS (SELECT 1 FROM enabled_foundational_flows efp WHERE efp.project_id = p.id)
THEN EXISTS (
SELECT 1 FROM enabled_foundational_flows efp
JOIN ai_catalog_items ci ON ci.id = efp.catalog_item_id
WHERE efp.project_id = p.id
AND ci.foundational_flow_reference = 'secrets_fp_detection/v1'
AND ci.deleted_at IS NULL
)
ELSE NULL
END,
(
SELECT EXISTS (
SELECT 1 FROM enabled_foundational_flows efn
JOIN ai_catalog_items ci ON ci.id = efn.catalog_item_id
WHERE efn.namespace_id = deciding.namespace_id
AND ci.foundational_flow_reference = 'secrets_fp_detection/v1'
AND ci.deleted_at IS NULL
)
FROM (
SELECT anc.namespace_id
FROM unnest(grp.traversal_ids) WITH ORDINALITY AS anc(namespace_id, ord)
WHERE EXISTS (SELECT 1 FROM enabled_foundational_flows efn2 WHERE efn2.namespace_id = anc.namespace_id)
ORDER BY anc.ord DESC LIMIT 1
) AS deciding
),
FALSE
) = FALSE;2. Stacked MATERIALIZED CTEs: 5.525 s (session 53188/155084, sub-batch 10000..15000, ~2,829 rows). Buffers 112,433 hit / 7,860 read / 3,752 dirtied / 8 written; WAL 12,850 records, ~30.14 MB.
WITH base_batch AS MATERIALIZED (
SELECT "project_settings"."project_id" FROM "project_settings" WHERE "project_settings"."project_id" BETWEEN 10000 AND 15000 AND "project_settings"."project_id" >= 10000 LIMIT 5000
),
batch AS MATERIALIZED (
SELECT ps.project_id, p.namespace_id, grp.traversal_ids
FROM project_settings ps
JOIN projects p ON p.id = ps.project_id
JOIN namespaces grp ON grp.id = p.namespace_id
WHERE ps.project_id IN (SELECT project_id FROM base_batch)
AND ps.duo_secret_detection_fp_enabled = TRUE
AND ps.created_at < '2026-04-13 07:44:00+01:00'
),
fp_items AS MATERIALIZED (
SELECT id FROM ai_catalog_items
WHERE foundational_flow_reference = 'secrets_fp_detection/v1'
AND deleted_at IS NULL
),
project_scope AS MATERIALIZED (
SELECT b.project_id, bool_or(fi.id IS NOT NULL) AS has_secret_fp
FROM batch b
JOIN enabled_foundational_flows efp ON efp.project_id = b.project_id
LEFT JOIN fp_items fi ON fi.id = efp.catalog_item_id
GROUP BY b.project_id
),
namespace_scope AS MATERIALIZED (
SELECT b.project_id, decided.has_secret_fp
FROM batch b
LEFT JOIN project_scope ps ON ps.project_id = b.project_id
CROSS JOIN LATERAL (
SELECT bool_or(fi.id IS NOT NULL) AS has_secret_fp
FROM unnest(b.traversal_ids) WITH ORDINALITY AS anc(namespace_id, ord)
JOIN enabled_foundational_flows efn ON efn.namespace_id = anc.namespace_id
LEFT JOIN fp_items fi ON fi.id = efn.catalog_item_id
WHERE anc.ord = (
SELECT anc2.ord
FROM unnest(b.traversal_ids) WITH ORDINALITY AS anc2(namespace_id, ord)
WHERE EXISTS (
SELECT 1 FROM enabled_foundational_flows e WHERE e.namespace_id = anc2.namespace_id
)
ORDER BY anc2.ord DESC
LIMIT 1
)
) AS decided
WHERE ps.project_id IS NULL
),
preserved AS MATERIALIZED (
SELECT project_id FROM project_scope WHERE has_secret_fp
UNION
SELECT project_id FROM namespace_scope WHERE has_secret_fp
)
UPDATE project_settings ps
SET duo_secret_detection_fp_enabled = FALSE
WHERE ps.project_id IN (SELECT project_id FROM batch)
AND ps.project_id NOT IN (SELECT project_id FROM preserved)Stacked CTEs: full Database Lab plan (session 53188)
ModifyTable on public.project_settings ps (cost=39014.00..172412.83 rows=0 width=0) (actual time=5524.021..5524.053 rows=0 loops=1)
Buffers: shared hit=112433 read=7860 dirtied=3752 written=8
WAL: records=12850 fpi=3740 bytes=30138416
CTE base_batch
-> Limit (cost=0.56..83.48 rows=2952 width=4) (actual time=1.198..58.970 rows=2834 loops=1)
Buffers: shared hit=136 read=76 dirtied=38
WAL: records=55 fpi=38 bytes=308776
-> Index Only Scan using project_settings_pkey on public.project_settings (cost=0.56..83.48 rows=2952 width=4) (actual time=1.197..58.688 rows=2834 loops=1)
Index Cond: ((project_settings.project_id >= 10000) AND (project_settings.project_id <= 15000) AND (project_settings.project_id >= 10000))
Heap Fetches: 102
Buffers: shared hit=136 read=76 dirtied=38
WAL: records=55 fpi=38 bytes=308776
CTE batch
-> Nested Loop (cost=68.12..14208.06 rows=2708 width=36) (actual time=67.467..4171.277 rows=2834 loops=1)
Buffers: shared hit=37147 read=5577 dirtied=104
WAL: records=121 fpi=104 bytes=833130
-> Nested Loop (cost=67.55..12446.44 rows=2708 width=8) (actual time=65.060..2959.879 rows=2834 loops=1)
Buffers: shared hit=24508 read=4046 dirtied=91
WAL: records=108 fpi=91 bytes=729473
-> Nested Loop (cost=66.98..10635.48 rows=2952 width=12) (actual time=65.023..2093.412 rows=2834 loops=1)
Buffers: shared hit=11806 read=2578 dirtied=58
WAL: records=75 fpi=58 bytes=462304
-> HashAggregate (cost=66.42..95.94 rows=2952 width=4) (actual time=60.975..63.480 rows=2834 loops=1)
Group Key: base_batch.project_id
Buffers: shared hit=136 read=76 dirtied=38
WAL: records=55 fpi=38 bytes=308776
-> CTE Scan on base_batch (cost=0.00..59.04 rows=2952 width=4) (actual time=1.200..59.812 rows=2834 loops=1)
Buffers: shared hit=136 read=76 dirtied=38
WAL: records=55 fpi=38 bytes=308776
-> Index Scan using projects_pkey on public.projects p (cost=0.56..3.57 rows=1 width=8) (actual time=0.715..0.715 rows=1 loops=2834)
Index Cond: (p.id = base_batch.project_id)
Buffers: shared hit=11669 read=2502 dirtied=19
WAL: records=19 fpi=19 bytes=146059
-> Index Scan using project_settings_pkey on public.project_settings ps_1 (cost=0.56..0.61 rows=1 width=4) (actual time=0.304..0.304 rows=1 loops=2834)
Index Cond: (ps_1.project_id = p.id)
Filter: (ps_1.duo_secret_detection_fp_enabled AND (ps_1.created_at < '2026-04-13 06:44:00+00'::timestamp with time zone))
Rows Removed by Filter: 0
Buffers: shared hit=12702 read=1468 dirtied=33
WAL: records=33 fpi=33 bytes=267169
-> Index Scan using namespaces_pkey on public.namespaces grp (cost=0.57..0.65 rows=1 width=32) (actual time=0.426..0.426 rows=1 loops=2834)
Index Cond: (grp.id = p.namespace_id)
Buffers: shared hit=12639 read=1531 dirtied=13
WAL: records=13 fpi=13 bytes=103657
CTE fp_items
-> Index Scan using index_ai_catalog_items_on_foundational_flow_reference on public.ai_catalog_items (cost=0.29..3.30 rows=1 width=8) (actual time=2.482..2.484 rows=1 loops=1)
Index Cond: (ai_catalog_items.foundational_flow_reference = 'secrets_fp_detection/v1'::text)
Filter: (ai_catalog_items.deleted_at IS NULL)
Rows Removed by Filter: 0
Buffers: shared read=3
CTE project_scope
-> HashAggregate (cost=5662.46..5689.54 rows=2708 width=5) (actual time=5.887..5.905 rows=5 loops=1)
Group Key: b.project_id
Buffers: shared hit=8525 read=3
-> Hash Left Join (cost=0.47..5590.30 rows=14432 width=12) (actual time=3.290..5.869 rows=40 loops=1)
Hash Cond: (efp.catalog_item_id = fi.id)
Buffers: shared hit=8525 read=3
-> Nested Loop (cost=0.43..5521.72 rows=14432 width=12) (actual time=0.772..3.336 rows=40 loops=1)
Buffers: shared hit=8525
-> CTE Scan on batch b (cost=0.00..54.16 rows=2708 width=4) (actual time=0.000..0.200 rows=2834 loops=1)
-> Index Only Scan using idx_enabled_flows_on_project_catalog_item on public.enabled_foundational_flows efp (cost=0.43..1.97 rows=5 width=16) (actual time=0.001..0.001 rows=0 loops=2834)
Index Cond: (efp.project_id = b.project_id)
Heap Fetches: 0
Buffers: shared hit=8525
-> Hash (cost=0.02..0.02 rows=1 width=8) (actual time=2.496..2.498 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared read=3
-> CTE Scan on fp_items fi (cost=0.00..0.02 rows=1 width=8) (actual time=2.488..2.490 rows=1 loops=1)
Buffers: shared read=3
CTE namespace_scope
-> Nested Loop (cost=101.75..18805.64 rows=1354 width=5) (actual time=0.777..19.605 rows=2829 loops=1)
Buffers: shared hit=8496 read=6
-> Hash Right Anti Join (cost=88.01..179.41 rows=1354 width=36) (actual time=0.695..1.006 rows=2829 loops=1)
Hash Cond: (ps_2.project_id = b_1.project_id)
-> CTE Scan on project_scope ps_2 (cost=0.00..54.16 rows=2708 width=4) (actual time=0.000..0.001 rows=5 loops=1)
-> Hash (cost=54.16..54.16 rows=2708 width=36) (actual time=0.647..0.648 rows=2834 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 201kB
-> CTE Scan on batch b_1 (cost=0.00..54.16 rows=2708 width=36) (actual time=0.001..0.218 rows=2834 loops=1)
-> Aggregate (cost=13.74..13.75 rows=1 width=1) (actual time=0.006..0.006 rows=1 loops=2829)
Buffers: shared hit=8496 read=6
InitPlan 5
-> Limit (cost=9.93..9.94 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=2829)
Buffers: shared hit=8496 read=6
-> Sort (cost=9.93..9.96 rows=10 width=8) (actual time=0.005..0.005 rows=0 loops=2829)
Sort Key: anc2.ord DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=8496 read=6
-> Nested Loop Semi Join (cost=0.43..9.88 rows=10 width=8) (actual time=0.004..0.004 rows=0 loops=2829)
Buffers: shared hit=8493 read=6
-> Function Scan on unnest anc2 (cost=0.00..0.10 rows=10 width=12) (actual time=0.001..0.001 rows=1 loops=2829)
-> Index Only Scan using idx_enabled_flows_on_namespace_catalog_item on public.enabled_foundational_flows e (cost=0.43..2.18 rows=6 width=8) (actual time=0.003..0.003 rows=0 loops=2833)
Index Cond: (e.namespace_id = anc2.namespace_id)
Heap Fetches: 0
Buffers: shared hit=8493 read=6
-> Hash Left Join (cost=0.46..3.78 rows=6 width=8) (actual time=0.006..0.006 rows=0 loops=2829)
Hash Cond: (efn.catalog_item_id = fi_1.id)
Buffers: shared hit=8496 read=6
-> Nested Loop (cost=0.43..3.72 rows=6 width=8) (actual time=0.006..0.006 rows=0 loops=2829)
Buffers: shared hit=8496 read=6
-> Function Scan on unnest anc (cost=0.00..0.13 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=2829)
Filter: (anc.ord = (InitPlan 5).col1)
Rows Removed by Filter: 1
Buffers: shared hit=8496 read=6
-> Index Only Scan using idx_enabled_flows_on_namespace_catalog_item on public.enabled_foundational_flows efn (cost=0.43..3.53 rows=6 width=16) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (efn.namespace_id = anc.namespace_id)
Heap Fetches: 0
-> Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
-> CTE Scan on fp_items fi_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
CTE preserved
-> HashAggregate (cost=96.47..116.78 rows=2031 width=4) (actual time=26.213..26.233 rows=5 loops=1)
Group Key: project_scope.project_id
Buffers: shared hit=17021 read=9
-> Append (cost=0.00..91.40 rows=2031 width=4) (actual time=5.892..26.203 rows=5 loops=1)
Buffers: shared hit=17021 read=9
-> CTE Scan on project_scope (cost=0.00..54.16 rows=1354 width=4) (actual time=5.890..5.905 rows=5 loops=1)
Filter: project_scope.has_secret_fp
Rows Removed by Filter: 0
Buffers: shared hit=8525 read=3
-> CTE Scan on namespace_scope (cost=0.00..27.08 rows=677 width=4) (actual time=20.291..20.292 rows=0 loops=1)
Filter: namespace_scope.has_secret_fp
Rows Removed by Filter: 2829
Buffers: shared hit=8496 read=6
-> Nested Loop (cost=107.19..133506.02 rows=1354 width=35) (actual time=4209.448..4229.052 rows=2829 loops=1)
Buffers: shared hit=68338 read=5586 dirtied=104
WAL: records=121 fpi=104 bytes=833130
-> HashAggregate (cost=60.93..88.01 rows=2708 width=32) (actual time=4183.126..4184.985 rows=2834 loops=1)
Group Key: batch.project_id
Buffers: shared hit=37147 read=5577 dirtied=104
WAL: records=121 fpi=104 bytes=833130
-> CTE Scan on batch (cost=0.00..54.16 rows=2708 width=32) (actual time=67.486..4178.397 rows=2834 loops=1)
Buffers: shared hit=37147 read=5577 dirtied=104
WAL: records=121 fpi=104 bytes=833130
-> Index Scan using project_settings_pkey on public.project_settings ps (cost=46.26..49.27 rows=1 width=10) (actual time=0.015..0.015 rows=1 loops=2834)
Index Cond: (ps.project_id = batch.project_id)
Filter: (NOT (ANY (ps.project_id = (hashed SubPlan 8).col1)))
Rows Removed by Filter: 0
Buffers: shared hit=31191 read=9
SubPlan 8
-> CTE Scan on preserved (cost=0.00..40.62 rows=2031 width=4) (actual time=26.214..26.233 rows=5 loops=1)
Buffers: shared hit=17021 read=9
Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'3. Sparse-driven preserved set: 8.756 s (slowest) (session 53188/155126, sub-batch 10000..15000, ~2.00 GiB buffer hits). Drove the preserved set off the flow tables via the namespaces.traversal_ids GIN index. It backfired: enabled_foundational_flows holds ~1.37M rows across ~343k namespaces, so building one array of all flow namespaces and overlapping it per candidate cost ~7.8 s and matched zero inherited projects.
WITH fp_items AS MATERIALIZED (
SELECT id FROM ai_catalog_items
WHERE foundational_flow_reference = 'secrets_fp_detection/v1'
AND deleted_at IS NULL
),
batch AS MATERIALIZED (
SELECT project_id
FROM project_settings
WHERE project_id IN (SELECT "project_settings"."project_id" FROM "project_settings" WHERE "project_settings"."project_id" BETWEEN 10000 AND 15000 AND "project_settings"."project_id" >= 10000 LIMIT 5000)
AND duo_secret_detection_fp_enabled = TRUE
AND created_at < '2026-04-13 07:44:00+01:00'
),
own_scope AS MATERIALIZED (
SELECT efp.project_id,
bool_or(efp.catalog_item_id IN (SELECT id FROM fp_items)) AS has_secret_fp
FROM enabled_foundational_flows efp
WHERE efp.project_id IN (SELECT project_id FROM batch)
GROUP BY efp.project_id
),
flow_namespaces AS MATERIALIZED (
SELECT DISTINCT namespace_id
FROM enabled_foundational_flows
WHERE namespace_id IS NOT NULL
),
inherit_candidates AS MATERIALIZED (
SELECT p.id AS project_id, grp.traversal_ids
FROM namespaces grp
JOIN projects p ON p.namespace_id = grp.id
-- traversal_ids is integer[] on gitlab.com but bigint[] in newer schemas; cast both
-- sides to int[] so the operator resolves and the integer[] GIN index still applies.
WHERE grp.traversal_ids::int[] && (SELECT array_agg(namespace_id)::int[] FROM flow_namespaces)
AND p.id IN (SELECT project_id FROM batch)
AND p.id NOT IN (SELECT project_id FROM own_scope)
),
inherit_scope AS MATERIALIZED (
SELECT ic.project_id, decided.has_secret_fp
FROM inherit_candidates ic
CROSS JOIN LATERAL (
SELECT bool_or(efn.catalog_item_id IN (SELECT id FROM fp_items)) AS has_secret_fp
FROM unnest(ic.traversal_ids) WITH ORDINALITY AS anc(namespace_id, ord)
JOIN enabled_foundational_flows efn ON efn.namespace_id = anc.namespace_id
WHERE anc.ord = (
SELECT anc2.ord
FROM unnest(ic.traversal_ids) WITH ORDINALITY AS anc2(namespace_id, ord)
WHERE EXISTS (
SELECT 1 FROM enabled_foundational_flows e WHERE e.namespace_id = anc2.namespace_id
)
ORDER BY anc2.ord DESC
LIMIT 1
)
) AS decided
),
preserved AS MATERIALIZED (
SELECT project_id FROM own_scope WHERE has_secret_fp
UNION
SELECT project_id FROM inherit_scope WHERE has_secret_fp
)
UPDATE project_settings ps
SET duo_secret_detection_fp_enabled = FALSE
WHERE ps.project_id IN (SELECT project_id FROM batch)
AND ps.project_id NOT IN (SELECT project_id FROM preserved)Known simplifications / notes
- Reads only the project's own materialised
enabled_foundational_flowsrows, not the project to ancestor walk the app uses. Enabling the flow at a group cascades a per-project row to every descendant, so genuine opt-ins are covered. The accepted gap is a project enabled only via an ancestor whose cascade is stale (no re-sync on transfer; create-time sync gated on the umbrella; sub-minute async worker lag). Sampling 18,442 candidate projects across several id ranges found 0 in that gap, inside the ~1% we agreed to accept. - A row whose catalog item is missing/soft-deleted or not the secret-FP flow → not enabled → reset (folded into the membership
EXISTS).
References
How to set up and validate locally
The opt-in signal is the Secret Detection False Positive Detection foundational flow (secrets_fp_detection/v1) materialised as the project's own row in enabled_foundational_flows. Set up one project with that row and one without, both pre-cutoff with the flag on.
-
In the rails console (wrap in a rolled-back transaction so nothing persists):
cutoff = Time.zone.parse(Gitlab::BackgroundMigration::UpdateDuoSecretDetectionFpEnabledToFalse::DEFAULT_CHANGED_AT) before = cutoff - 10.days org = Organizations::Organization.first # catalog item the flow toggle points at (seeded per org; create if missing in GDK) item = Ai::Catalog::Item.find_by(organization_id: org.id, foundational_flow_reference: 'secrets_fp_detection/v1') || Ai::Catalog::Item.new(organization_id: org.id, item_type: :flow, name: 'Secret FP', description: 'x', foundational_flow_reference: 'secrets_fp_detection/v1') .tap { |i| i.save!(validate: false) } control, opted = Project.where(organization_id: org.id).first(2) [control, opted].each do |p| ProjectSetting.where(project_id: p.id) .update_all(duo_secret_detection_fp_enabled: true, created_at: before) end # materialise the flow on the opted-in project (its own row) Ai::Catalog::EnabledFoundationalFlow.create!(project_id: opted.id, catalog_item_id: item.id)In real usage the flow is enabled through the UI at Group → Settings → GitLab Duo → Flows → "Secret Detection False Positive Detection"; the group toggle cascades a per-project
enabled_foundational_flowsrow down to each project, which is the row this migration reads. -
Run the background migration to completion (
db:migratealone only enqueues it):bundle exec rake db:migrate -
Confirm the outcome:
control.project_setting.reload.duo_secret_detection_fp_enabled # => false (reset; no flow) opted.project_setting.reload.duo_secret_detection_fp_enabled # => true (preserved; flow materialised on the project)A project with
created_atat or after2026-04-13 07:44:00 BST(2026-04-13 06:44:00 UTC) also keeps itstruevalue.
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist.
- Database review (bulk
UPDATEwith join + cascade subquery, new background migration). - Attach Database Lab query plan for the sub-batch UPDATE.
Edited by Vasyl Pedak