ADR-0027 — Feature Flag Env Scoping: per-env rows vs single row with override

Status: Accepted Date: 2026-04-28 UTC Refs: #146, console-feature-flags.md, ADR-0024

Context

Feature flags need to support different values in prod and staging. This is not hypothetical: several flags (console_env_switcher_banner, console_env_gate, enforce_cf_origin) are explicitly intended to be enabled on staging for soaking before being enabled on prod.

Two modeling approaches:

1. Per-env rows — console_feature_flags has one row per (flag_key, env) pair. A flag that is on in staging and off in prod has two rows.
2. Single row with staging override — console_feature_flags has one row per flag_key with a primary value and an optional staging_override column. If staging_override IS NOT NULL, staging reads the override; otherwise staging reads the primary value.

Decision

Option 1: per-env rows. Each (flag_key, env) pair is an independent row.

The UNIQUE (flag_key, env) constraint is the primary key semantic. Reads and writes are keyed on both dimensions. The UI shows two columns per flag (prod value, staging value) rather than a single value with an override indicator.

Consequences

Positive: - Uniform data model: every flag has at most two rows (one per env). No special-casing in SQL. - Reads are a single indexed lookup on (flag_key, env) — O(1), cache-friendly. - Audit trail is clean: each flip event records which env was changed. No ambiguity about "which value changed." - Env isolation is absolute: a bug in staging flag handling cannot affect a prod flag row. - Adding a third environment (e.g., preview) in the future requires no schema change — just a new env CHECK value and a new row.

Negative: - A flag that has the same value in both envs requires two rows (or zero rows, using YAML default). This is minor storage overhead. - The UI must clearly indicate per-env values. A single global "on/off" toggle per flag is not sufficient. The UI table design must show two environment columns (see §4.4 of the design doc).

Alternatives Considered

Option 2: single row with staging_override column

Would allow a flag to have a single "primary" value (used for prod) and an optional staging-specific override.

Rejected for the following reasons: - Asymmetric model: prod is implicitly the "canonical" value and staging is a "deviation." This bakes in a prod-first assumption that conflicts with the staging-first soak pattern (flags go on in staging first, then promoted to prod). - Complicates the read path: SELECT COALESCE(staging_override, value) WHERE env='staging' — conditional logic in a hot path. - Audit trail is less clear: a single row with two columns means a flip to staging_override looks different in the audit log than a flip to value. Operators must understand which column was touched to interpret the log. - Adding a third environment would require adding a new column rather than a new row — a schema change.