feature_grants: add an exactly-one-scope XOR CHECK (QRS-1018)

Raised 2026-09-04. Follows the architecture change protocol.

DO NOT IMPLEMENT. The rule is already enforced, and the proposed CHECK would break the table.

QRS-1018 asked for check (num_nonnulls(archetype_key, industry_key, plan_key, group_id, workspace_id, member_user_id, user_id) = 1). Measured against the live table, that CHECK fails to apply: nine rows would violate it. Had it been applied to an empty table it would have made platform-wide grants and workspace-member grants permanently unrepresentable.

The exactly-one rule the row was written to protect is enforced already, by feature_grants_scope_target_matches_kind, which is strictly stronger.

1 · Existing architecture

public.feature_grants (migration 20260808140000_v2_features_and_grants.sql:184) carries a polymorphic scope: a scope_kind discriminator plus seven nullable FK columns, one per kind of thing a grant can target. The local stack at head 20260904090822 holds 55 rows — 46 plan-scoped and 9 platform-scoped.

scope_kind is itself an FK to feature_grant_scopes, so it cannot hold a value outside the vocabulary. That matters for §2: it means a CASE over scope_kind is total.

2 · Actual data model and relationships

All seven scope columns are nullable; scope_kind, feature_key, axis, effect, on_exceed, reason, effective_from and created_at are NOT NULL. Every scope column is a real foreign key with ON DELETE CASCADE, except created_by (no action) and scope_kind (RESTRICT).

The constraint QRS-1018 believed absent exists. feature_grants_scope_target_matches_kind is a CASE over scope_kind that states, per kind, exactly which columns must be NOT NULL and asserts every other is NULL, with ELSE false so an unrecognised kind is refused outright.

Two of its eight branches are the reason the proposed XOR is wrong:

scope_kind	non-null scope columns	num_nonnulls(...)
platform	none — a platform-wide grant targets nothing in particular	0
workspace_member	workspace_id and member_user_id	2
every other kind	exactly one	1

3 · Current implementation

resolve_features and get_my_features read grants through scope_kind; nothing anywhere counts non-null columns, so no consumer depends on the shape the proposal assumed.

The source of the error is a comment. 20260808140000:189 reads:

POLYMORPHIC SCOPE AS EXACTLY-ONE-NON-NULL FK COLUMNS, not scope_id text + a validation trigger.

That phrase is not the rule the table implements — platform sets zero and workspace_member sets two. A reader who takes it literally goes looking for a num_nonnulls = 1 CHECK, does not find one, and concludes the rule is unenforced. That is exactly the trip QRS-1018 made.

4 · Impact analysis

Measured with psql against the live local table:

rows with num_nonnulls(seven scope columns) <> 1 : 9
  scope_kind=platform  9 rows  num_nonnulls=0
  scope_kind=plan     46 rows  num_nonnulls=1

So the proposed CHECK does not merely fail to help — it fails to apply. And on a table that happened to be empty it would have silently removed two legitimate grant shapes: platform-wide grants, which are how a feature is turned on for everyone, and workspace-member grants, which are how an org admin grants a capability to one employee.

5 · Gaps

Search space stated, because an absence proves nothing without one: every constraint on public.feature_grants was enumerated from pg_constraint (contype in c, f) on the local stack, not grepped from migrations. There is no enforcement gap. The gap is documentation: a column comment that states a rule the table does not implement.

6 · Alternatives evaluated

Option	Verdict
Add num_nonnulls(...) = 1 as QRS-1018 specifies	Rejected — measured to fail against 9 existing rows and to make workspace_member unrepresentable
Replace the CASE constraint with a num_nonnulls form plus per-kind exceptions	Rejected — strictly weaker: it stops tying each column to its scope_kind, and loses the ELSE false that refuses an unknown kind
Correct the comment; pin the existing constraint with assertions	Accepted

7 · Proposed change

No structural change. A COMMENT ON correcting the column comment to describe the rule the table actually implements, plus pgTAP assertions pinning feature_grants_scope_target_matches_kind — including the platform (zero) and workspace_member (two) branches, which are the two that make the naive reading wrong.

The assertions matter more than the comment: a comment can drift again, and the whole cost of this row was that nobody had a failing test to contradict it.

8 · Validation against all four customer types

Customer type	Verdict	Why, with citations
Consumer	safe	The consumer biodata caps use scope_kind=user, whose branch already requires user_id NOT NULL and all six others NULL (measured, pg_constraint)
Solo / SMB merchant	safe	46 of the 55 live rows are plan-scoped and already satisfy the existing constraint
Car dealership	safe	Unaffected: no structural change, and workspace_group / workspace branches are untouched
Enterprise	safe	The workspace_member branch — the one the rejected change would have destroyed — is the enterprise per-employee grant path

9 · Production-risk assessment

Low, for the accepted change: a comment and test assertions alter no data, no structure and no behaviour. Rollback is reverting the comment.

Recorded separately because it is the useful half: the rejected change carried severe risk. It would have failed on Dev at apply time, which is the good outcome; the bad outcome was available too, since on a freshly reset database it would have applied cleanly and quietly removed two grant shapes nobody would have missed until an enterprise customer needed one.

10 · Final recommendation

do_not_implement. Close QRS-1018 as already-enforced, correct the misleading comment, and pin the real constraint with tests.

⚠ The transferable lesson is not about this table. A tracker row is a claim, and this one was written from a code comment rather than from the schema. The comment was itself imprecise, so the row inherited its imprecision and then dressed it as a measurement — and the prescription that followed would have been actively harmful. The protocol caught it only because step 4 requires running the proposed predicate against real rows before recommending it.