TwoMore

Appearance

PS11 — Platform Guardrails ​

Status: Archived Date: 2026-07-11 Blocks: B7-platform-plumbing.md (finding #10, the flagship preventive gap), cross-cutting across every block — this doc's job is not to re-fix any individual defect (those live in PS1–PS10) but to design the mechanical guard that makes each class of defect fail loudly before it ships again. Canonical companions: docs/architecture/solutions/ps1-access-control.md §3.7/Slice 9 already fully designs the SECDEF-ACL pgTAP guard (Guard 1 below reuses it verbatim — this doc generalizes the CI wiring, not the test); CLAUDE.md "Enforcement Stack" section (the 9-layer mechanical stack this doc extends); docs/best-practices/research-methodology.md (the multi-dimensional research mandate — every guard below traces to a concrete file:line, not an abstract principle).

1. Problem statement ​

The audit's 16 blocks did not surface 130 independent defects — they surfaced roughly a dozen recurring classes, each one appearing 2–4 times because the first occurrence was fixed reactively (a hotfix migration, a one-off OTA) with no mechanism stopping the same mistake from being typed again. This is the project's own stated failure mode inverted: CLAUDE.md's "PREVENTIVE" principle says every fix includes prevention, but the audit's own evidence is that this has not been happening — every SECDEF ACL fix to date (00209, 00218, 00386, 00387) was manual and reactive (B7 #10), and the ICU localeCompare freeze was fixed once (commit 030420b3, 2026-07-10) and reintroduced the very next day in three more files, found by three different audit agents who had no way to know the earlier fix existed except by reading git log.

#Recurring classEvidence (live count)SeverityCurrent guard
G1SECDEF function granted EXECUTE to anon with no internal auth guard40→5→0 unguarded instances across 4 reactive hotfixes (B7 #1–#3); 35 still anon-executable defense-in-depth violationsCRITICAL (preventive gap)None — B7 #10: "no pgTAP test, no CI step, nothing greps aclexplode"
G2REVOKE ... FROM PUBLIC used alone (the no-op-against-anon idiom)96 migrations use the broken form; 13 use the correct FROM PUBLIC, anon form (B7 #1)HIGH (root-cause idiom)None — nothing catches the anti-pattern at authoring time, only after a live ACL sweep
G3New public table ships without the explicit data-API GRANT AGENTS.md already mandates82 of 88 tables (93%) non-compliant, 7% compliance (B7 #6)HIGH (structural)None — the rule is written in AGENTS.md/CLAUDE.md prose only
G4.localeCompare( used as a sort/tiebreak comparator (Hermes routes it through ICU, ~1000× cost; caused a live 3–4s JS-thread freeze)Fixed once (030420b3, venue-directory-content.tsx); found live in 10 files, ~11 call sites across 4 more audit blocks the very next day (§2.4)MEDIUM-HIGH (recurring, 4 blocks)None — a project-memory note (feedback_hermes_localecompare_perf.md) and a code comment in one file, neither mechanically enforced
G5A SignalType is emitted, has i18n copy, or routes on tap — but not all threesession_player_forfeited: DB emitted it (00206/00310) before the TS union had it, mapper threw (scar-tissue comment, signal.entity.ts:48-53); inverse case: 4 vestigial SignalTypes with zero DB emitters (B5 #12); push-tap navigation reads data.<field> at the wrong nesting level for ~12 of 13 categories (U1 #1, CRITICAL, unrelated mechanism, same "signal pipeline has no completeness check" root class)HIGH (recurring, 3 distinct completeness gaps)None — signal-routes.test.ts unit-tests routing logic per hand-picked case, not enum completeness
G6A mutation/query hook + RPC is fully built, exported from @twomore/app, and never called from any screenReferee-transfer: 5 hooks, exported, 0 consumers (U4 #3, B4 #9); join-request approval UI: server fully built + hardened across 5 migrations, 0 consumers (U2 #1, B1 #8); club_alerts: cron still writes it daily, 0 UI consumers since v2 inception (B5 #13, B7 #11)MEDIUM-HIGH (recurring, 3 instances, "half-built screen" is a standing owner complaint)None — no dead-export detector anywhere in the repo (ts-prune/knip absent from package.json, verified)
G7RLS policy has USING but no WITH CHECK (or a GRANT wider than its RLS), so a raw PATCH/INSERT bypasses the RPC's side effectsclub_join_request (fixed, 00329); guest_applications_admin_update (P5, open); venue_corrections (P3, open) — PS1 §2.7 names this "the same class, a third time"HIGH (recurring, 3 instances, 1 fixed reactively)None — no lint/test asserts RLS policies with FOR UPDATE/INSERT always pair USING with WITH CHECK

Deliberately excluded from PS11: the individual fixes themselves (all live in PS1/PS4/PS6/PS8) — this doc only designs the guard that keeps a fixed class fixed. PS12 (engineering streamline) is explicitly sequenced after every other PS ships; PS11's guards should land as each underlying fix lands, not held for PS12, since a guard with nothing yet to protect is still worth having (it locks in the next author's correct behavior immediately).

2. Research ​

2.1 What "prevention" already means in this repo — extend, don't invent ​

The enforcement stack (CLAUDE.md "Enforcement Stack", verified live) already has five mechanical layers doing exactly this job for other classes: TypeScript strict, ArchUnitTS (16 fitness rules, packages/app/src/config/__tests__/architecture-archunit.test.ts), 52 @twomore/eslint-plugin rules + a shrinking ratchet baseline (eslint.ratchet.config.mjs, 4 rules / 84 waivers today), DB constraints, and two purpose-built migration-diff linters (scripts/lint-pi-schema-changes.mjs, scripts/lint-agents-md.mjs) wired into both .husky/pre-push and .github/workflows/ci.yml. Every guard below is a new instance of one of these four existing mechanisms — no new infrastructure category is introduced. This matters because introducing a 5th kind of gate (e.g. a bespoke Python static analyzer) would itself be the next "single source of truth" violation the audit would flag in a future pass.

Mapping each recurring class to the mechanism that already fits it:

ClassMechanismPrecedent already in this repo
G1 (live DB ACL state)pgTAP test, DB-livesupabase/tests/*.test.sql (18 files exist, 00001–00025)
G2 (migration source text, pre-merge)grep-based CI/pre-push linter, no DB neededscripts/lint-pi-schema-changes.mjs (diff-scoped migration grep)
G3 (live DB ACL state, same shape as G1)pgTAP testsame as G1
G4 (JS/TS source pattern)ESLint custom rule + ratchetpackages/eslint-plugin/rules/no-inline-date-format.js (identical shape: ban a method call by AST match, scoped by filename substring)
G5 (cross-artifact completeness: TS enum ↔ i18n keys ↔ SQL literal ↔ route switch)Node script, run as a Jest test + standalone CLIscripts/lint-pi-schema-changes.mjs's "diff the DB-schema surface against a registry" shape, generalized to 4 registries instead of 1
G6 (dead export detection)knip, industry-standard, zero bespoke codenone yet — new dependency, evaluated below
G7 (RLS policy shape, live DB state)pgTAP test, same query shape as G1/G3same as G1

2.2 G1/G3/G7 — pgTAP is the correct mechanism, and PS1 already wrote the flagship instance ​

pgTAP (https://pgtap.org, the standard Postgres TAP-test extension, already a transitive Supabase CLI dependency — supabase test db runs it) is the only mechanism that can assert on live ACL state (pg_proc.proacl, pg_class.relacl, pg_policies) rather than migration source text — this matters because ACL state is the product of Postgres's default-privilege resolution (§2.1 of PS1), not something a text grep over a single migration file can compute (a function's live ACL is the accumulated effect of every CREATE OR REPLACE, GRANT, and REVOKE across its entire migration history, plus the default-ACL bucket state at creation time). PS1 §3.7 already writes the canonical G1 test:

sql
-- supabase/tests/secdef_acl_hygiene.test.sql
SELECT is(
  (SELECT count(*)::int FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
   CROSS JOIN LATERAL aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) acl
   JOIN pg_roles r ON r.oid = acl.grantee
   WHERE p.prosecdef AND n.nspname = 'public' AND r.rolname = 'anon'
     AND acl.privilege_type = 'EXECUTE'
     AND p.proname NOT IN ('get_club_discovery_summaries', 'get_public_share_preview', 'is_public_discoverable_club')),
  0,
  'no unexpected anon-EXECUTE SECDEF functions in public'
);

PS11 does not re-design this query — PS1 owns it. What PS11 adds is the thing that was actually missing per B7 #10 and re-confirmed independently in docs/release-evidence.md:678-681 ("the committed pgTAP tests are NOT wired into CI/pre-push... manual supabase test db runs"): a CI job that runs supabase test db. This is the single highest-leverage line item in this entire doc — wiring one job activates:

  • The 18 pgTAP test files already committed and currently dormant (00001–00025, covering RLS on clubs/sessions/matches/dues, join-request/RSVP/leave-cascade triggers, tiebreak-ELO, rate limits — real regression coverage sitting inert today).
  • PS1's new G1 test (§3.7/Slice 9) the moment it lands.
  • Every G3 and G7 test this doc adds (below) — same job, same command, zero marginal CI infrastructure cost per additional test file.

G3 (explicit per-table GRANT) is the identical query shape one level up the object hierarchy — pg_class.relacl instead of pg_proc.proacl — asserting the post-PS1-§3.9-sweep state holds:

sql
-- supabase/tests/table_grant_hygiene.test.sql
SELECT is(
  (SELECT count(*)::int FROM pg_class c
   WHERE c.relnamespace = 'public'::regnamespace AND c.relkind = 'r'
     AND EXISTS (SELECT 1 FROM aclexplode(c.relacl) a JOIN pg_roles r ON r.oid = a.grantee
                 WHERE r.rolname = 'anon'
                   AND a.privilege_type IN ('TRUNCATE', 'REFERENCES', 'TRIGGER'))),
  0,
  'no public table grants anon TRUNCATE/REFERENCES/TRIGGER (the non-RLS-filtered slice of the default grant)'
);

Scoped to TRUNCATE/REFERENCES/TRIGGER specifically (not the full default grant) because PS1 §2.4 already established that SELECT/INSERT/UPDATE/DELETE are RLS-gated on 86/88 tables and PS1 §3.9's sweep is additive/defense-in-depth for those — a strict zero-tolerance test on the full grant would need a maintained allowlist that drifts every time a legitimate new table ships, whereas TRUNCATE/REFERENCES/TRIGGER genuinely has zero legitimate client use case (confirmed B7 finding #6: "not exposed by PostgREST's REST/RPC surface... that slice of the over-broad grant is currently inert" — inert is not the same as harmless, and this is the part of the grant a future policy/RLS change cannot backstop).

G7 (RLS WITH CHECK completeness) generalizes 00329's own fix pattern into a standing test — find every RLS policy governing INSERT/UPDATE and assert none of them has a NULL/absent with_check while its table also carries a GRANT wider than the policy's qual:

sql
-- supabase/tests/rls_with_check_completeness.test.sql
SELECT is(
  (SELECT count(*)::int FROM pg_policies
   WHERE schemaname = 'public' AND cmd IN ('INSERT', 'UPDATE', 'ALL')
     AND with_check IS NULL
     AND policyname NOT IN ('guest_applications_admin_update_rpc_only', /* USING(false), see PS1 §3.5 — no WITH CHECK needed, zero rows ever pass USING */ ...)),
  <baseline count, allowlisted per PS1's remediation>,
  'every INSERT/UPDATE RLS policy either has an explicit WITH CHECK or is a USING(false) RPC-only lockdown'
);

This is a ratcheted count assertion, not a zero assertion — unlike G1/G3 where the post-PS1-fix target is a hard zero, some USING-only policies are legitimate today (a policy scoped USING (false) needs no WITH CHECK, since zero rows ever pass the USING clause to check against). The test locks in "no new under-constrained policy" the same way eslint.ratchet.config.mjs locks in "no new violation of an already-ratcheted rule" — the baseline count is set once, PS1's fixes lower it, and any future migration that raises it fails CI.

2.3 G2 — the anti-pattern is greppable in migration source, before any DB exists ​

B7's own methodology already proves this is a pure text-pattern problem, not a live-DB one — the audit found the 96-vs-13 split with two grep invocations against supabase/migrations/*.sql, zero database involved:

sh
grep -rE "REVOKE (ALL|EXECUTE) ON FUNCTION public\.[a-z_]+\([^)]*\) FROM PUBLIC;" supabase/migrations/*.sql | wc -l   # 96
grep -rE "REVOKE (ALL|EXECUTE) ON FUNCTION public\.[a-z_]+\([^)]*\) FROM (PUBLIC, ?anon|PUBLIC, ?anon, ?authenticated)" supabase/migrations/*.sql | wc -l  # 13

This is architecturally identical to scripts/lint-pi-schema-changes.mjs's job: scan the migration files touched in the current diff (not the whole history — old migrations are grandfathered, matching that script's own stated design philosophy of being "forward-looking"), detect a regex pattern, fail with an actionable message pointing at the fix. No new script category — literally the same script, one more detection rule.

2.4 G4 — confirming the ESLint-rule shape and the actual blast radius before designing the rule ​

no-inline-date-format.js (§2.1 table) is the closest existing template: it bans a specific method-call pattern (Date getters), scoped by filename substring to the Hermes-executed presentation/features tree, exempting test files. .localeCompare( fits the identical shape — simpler, actually, since it needs no TemplateLiteral-ancestor walk (any CallExpression whose callee property is localeCompare is a violation, full stop — there is no legitimate formatting vs. non-formatting distinction to draw the way there was for Date getters).

Repo-wide grep (excluding node_modules, build output in apps/*/dist and apps/web/.next, and __tests__) confirms the actual current blast radius, gathered directly rather than trusting the audit's per-block counts verbatim (multi-dimensional research mandate — cross-validate):

packages/features/clubs/src/shared/club-card-model.ts        (U6 #3, 2 call sites)
packages/features/clubs/src/shared/club-card-summary.ts       (U6 #3, 3 call sites)
packages/features/records/src/records-history-screen.tsx      (U5 #5, 2 call sites)
packages/features/records/src/record-detail-screen.tsx        (U5 #5, 2 call sites)
packages/app/src/presentation/hooks/queries/use-club-attendance-summary.ts  (U2 #5, 1 call site)
packages/app/src/domain/rules/signal-resolver.ts               (not audit-flagged — found by this research pass)
packages/app/src/presentation/utils/session-sort.ts            (not audit-flagged — found by this research pass)
packages/app/src/domain/rules/user-state.rules.ts               (not audit-flagged — found by this research pass)
packages/features/sessions/src/scorecard/draws-tab.tsx          (not audit-flagged — found by this research pass)
packages/app/src/presentation/components/sessions/session-match-detail.tsx (not audit-flagged — found by this research pass)

10 files, ~14 call sites — larger than any single audit block reported, because each block only greps its own scope. Every single call site compares either an ISO-8601 date/time string, a UUID, a courtNumber integer tiebreak, or (one case, session-match-detail.tsx:139) a displayName — and even the displayName case is bounded to a single match's participant list (≤4 players), so no call site anywhere in the repo has both (a) unbounded/large N and (b) a genuine Korean-collation requirement that would justify the ICU cost. Confirms the rule can be a blanket ban with zero carve-outs needed in the rule logic itself (unlike G1's allowlist, which is structural). One file (venue-directory-content.tsx) contains the string localeCompare only inside a comment (the postmortem note explaining why the file avoids it) — grepped directly to confirm zero actual CallExpression there, so the new rule produces zero false positives on the one file that already did this correctly.

2.5 G5 — three distinct completeness axes, one registry-diff shape ​

The SignalType pipeline (packages/app/src/domain/entities/signal.entity.ts) has three independent artifacts that must stay in lockstep, and the audit found a live gap on each axis:

  1. DB emitter ↔ TS union. signals_emit(p_type := '<literal>', ...) is called from ~30 migration trigger bodies with a bare string literal (no shared enum between SQL and TS — confirmed via signals_emit's own signature, p_type TEXT, 00107_signals_system.sql:437). signal.entity.ts:48-53 carries a live scar from this exact gap: a comment reading "drift rescue: DB emits this via forfeit_session/forfeit_auto_substitute (migrations 00206/00310) but it was never added to the TS union — client mapper.assertEnum threw on read." This was caught by a production crash, not a test.
  2. TS union ↔ i18n copy. packages/app/src/config/i18n/{ko,en}/signals.ts key every signal.<category>.<type>.title/.body string by hand; nothing asserts every SignalType has a title key. B5 #12 found the inverse direction of the same gap: 4 SignalType union members with zero DB emitter (i18n copy exists, ready, unreachable — the "orphaned i18n subtree" shape).
  3. Category/type ↔ route. getSignalRoute (signal-routes.ts) is a hand-written if (category === ...) chain; signal-routes.test.ts unit-tests hand-picked cases, not exhaustiveness. U1 #1 (CRITICAL) is a different mechanism on the same axis — the push payload nests IDs under context while use-notification-handler.ts destructures them at the top level, so 12 of 13 categories silently no-op on tap. A completeness test cannot catch a payload-shape mismatch between two hand-written pieces of code the way it can catch a missing enum member, but the fix (PS8's job) and the guard (this doc's job, §3.5) are still the same family: assert every SignalType/category combination has a reachable destination, forcing the next reviewer to look at exactly the two functions that must agree.

This is the same "registry-diff" shape lint-pi-schema-changes.mjs already implements for one axis (migration schema ↔ purpose_catalog row) — G5 generalizes it to three axes, all resolvable statically (parse the TS union via the TypeScript compiler API or a regex over the literal-type array, parse the i18n key sets as plain object literals, grep migrations for p_type := / positional first-arg literals) with no DB required, so it runs as a fast Jest test rather than a pgTAP test — the artifacts being compared are all source-tree, not live-DB state.

2.6 G6 — evaluate knip against the custom-script alternative (prefer-libraries-over-custom) ​

Project convention (feedback_prefer_libraries_over_custom.md, referenced in CLAUDE.md) is to evaluate a mature library before hand-rolling. Two real options for "hook exported, never imported":

  • ts-prune — unmaintained since 2023 per its own README (superseded-by notice points to knip); simple, single-purpose (unused-export listing only).
  • knip (https://knip.dev) — actively maintained, purpose-built for exactly this (monorepo-aware, understands Yarn workspaces + package.json exports fields, has built-in React/Next.js/Expo plugins so it doesn't false-positive on framework-conventional exports like route files). Config-driven allowlist (ignoreExportsUsedInFile, entry/project globs) handles this repo's actual shape: packages/app/src/index.ts is a deliberate barrel (everything it exports is "used" only by re-export, which is correct and must not be flagged), while a hook file exported from that barrel but imported by zero feature package is the real target.

Recommendation: knip, not a bespoke script. This is the one guard in this doc that introduces a new dependency rather than reusing an existing mechanism — justified because dead- export detection genuinely requires cross-package call-graph analysis (which package imports which barrel export), which is exactly the kind of problem a hand-rolled grep script gets wrong in both directions (misses re-exports, false-positives on intentionally-public API surface) in a way a purpose-built tool does not. Config lives at knip.json (or knip.config.ts), entry points = each packages/features/*/src/index.ts + apps/*/app/** (Expo Router/Next.js file-based routes) + test files; the two live instances (referee-transfer hooks, join-request UI — the backend/hooks side of it) are exactly what a first knip run against current main would surface immediately, which is itself the acceptance test for wiring it in.

3. Solution design ​

Seven guards, each named by the mechanism + the class it locks. All wire into the existing 9-layer enforcement stack (CLAUDE.md "Enforcement Stack") rather than adding a 10th kind.

3.1 Guard 1 — supabase test db in CI (unlocks G1, G3, G7, and 18 dormant tests for free) ​

Mechanism: new CI job in .github/workflows/ci.yml, parallel to the existing drift job (same pattern: separate job, own runs-on, doesn't block the main check job's critical path).

yaml
pgtap:
  name: pgTAP — DB regression suite
  runs-on: ubuntu-latest
  timeout-minutes: 10
  steps:
    - uses: actions/checkout@v4
    - uses: supabase/setup-cli@v1
      with: { version: latest }
    - run: supabase db start
    - run: supabase test db

supabase test db spins up the local stack (already the project's own dev/reset flow — npx supabase db reset is a documented CLAUDE.md command, so this reuses infra every contributor already has), applies all 303+ migrations, then runs every supabase/tests/*.test.sql file via pgTAP. This is the mechanism, full stop — the 18 existing dormant files plus PS1's G1 test plus this doc's G3/G7 tests all activate the moment this one job exists. No further per-test CI wiring is ever needed again; this is a one-time, permanent unlock.

Verification: intentionally break one assertion locally (e.g. GRANT EXECUTE ON FUNCTION public.some_fn TO anon on a throwaway function), confirm supabase test db fails; revert, confirm green. Push a branch with the new job and confirm it runs in the Actions tab.

3.2 Guard 2 — migration-diff linter for the REVOKE ... FROM PUBLIC idiom (locks G2) ​

Mechanism: new script scripts/lint-secdef-revoke-idiom.mjs, structurally a copy of scripts/lint-pi-schema-changes.mjs (same diff-scoping logic: git diff --name-only <base> HEAD -- supabase/migrations/, same LINT_<X>_ALL=1 full-scan escape hatch, same exit-code contract).

Detection: any migration in the diff containing REVOKE\s+(ALL|EXECUTE)\s+ON\s+FUNCTION\s+public\.\w+\([^)]*\)\s+FROM\s+PUBLIC\s*; (B7's own regex, reused verbatim) where the same statement does not also include anon in the role list. Opt-out: a file-level -- ANON-SAFE: <reason> comment (mirrors lint-pi-schema's -- PI-EXEMPT: convention exactly) for the rare case a function is deliberately kept anon-revoked-but-PUBLIC-only for a documented reason.

Wired: yarn lint:secdef-revoke script, added to .husky/pre-push alongside lint:pi-schema, and as its own CI step (mirrors the existing "PI schema lint" step in ci.yml).

Verification: author a throwaway migration with the broken idiom, confirm the linter exits 1 with a message pointing at the corrected form (FROM PUBLIC, anon); fix it, confirm exit 0.

3.3 Guard 3 — no-localecompare-sort ESLint rule (locks G4) ​

Mechanism: packages/eslint-plugin/rules/no-localecompare-sort.js, template-matched to no-inline-date-format.js's structure (scope by filename substring, single CallExpression visitor, no AST-ancestor walk needed per §2.4's finding that no legitimate use case exists in scope today).

js
const SCOPE_SUBSTRINGS = ['packages/app/src/', 'packages/features/'];
const EXEMPT_SUBSTRINGS = ['__tests__/', '.test.ts', '.test.tsx'];

module.exports = {
  meta: {
    type: 'problem',
    docs: {
      description:
        'Hermes routes .localeCompare() through ICU (~1000x cost); caused a live 3-4s JS-thread freeze (commit 030420b3). Use a plain </> compare or a pre-sorted server query.',
    },
    schema: [],
    messages: {
      banned:
        'localeCompare() is banned in Hermes-executed code — use plain `<`/`>` comparison (ISO dates, UUIDs, and most sort keys need zero locale awareness). See feedback_hermes_localecompare_perf.md.',
    },
  },
  create(context) {
    const filename = (context.filename || context.getFilename()).replace(/\\/g, '/');
    if (!SCOPE_SUBSTRINGS.some((s) => filename.includes(s))) return {};
    if (EXEMPT_SUBSTRINGS.some((s) => filename.includes(s))) return {};
    return {
      CallExpression(node) {
        if (node.callee.type !== 'MemberExpression') return;
        if (node.callee.property.type !== 'Identifier') return;
        if (node.callee.property.name !== 'localeCompare') return;
        context.report({ node, messageId: 'banned' });
      },
    };
  },
};

Registered in packages/eslint-plugin/index.js (append to the rules map, same pattern as every other entry), enabled at error in the root flat config. The 10 existing files (§2.4) become the rule's day-1 ratchet baseline via yarn lint:ratchet (scripts/generate-eslint-ratchet.mjs) — this is the exact mechanism eslint.ratchet.config.mjs's own header describes ("new files that violate the rule are NOT exempted... to shrink the baseline: fix the violations in a listed file, then re-run and commit"). No custom escape-hatch comment syntax needed; the ratchet is the escape hatch, and it shrinks file-by-file as each is next touched (matches how the other 2 ratcheted rules — no-stylesheet-create, no-inline-style-with-vars — are already being retired per the ratchet file's own stated plan).

Verification: run yarn lint:strict — must pass immediately (ratchet absorbs the 10 known files). Add a throwaway .localeCompare( call to an 11th, non-ratcheted file — yarn lint:strict must fail. yarn lint:ratchet — must produce a diff exactly matching the 10 files in §2.4 (this is also the CI drift-check ci.yml already runs, so it's self-verifying).

3.4 Guard 4 — explicit GRANT is enforced live by Guard 1's G3 test; no separate mechanism needed ​

Originally scoped as its own migration-lint (mirroring G2's shape: detect CREATE TABLE public.* in a diffed migration with no accompanying REVOKE/GRANT block). Superseded by the G3 pgTAP test (§2.2) once Guard 1 is wired — a live-DB assertion on TRUNCATE/REFERENCES/TRIGGER grants catches every path to a non-compliant table (bulk sweep migrations, hand-written new-table migrations, a future ALTER TABLE that re-widens an already-compliant table) with one test, where a migration-diff linter would only catch the "new CREATE TABLE" path and miss the "someone widens an existing grant back open" path entirely. Keeping this as G3/pgTAP rather than adding a 6th script is itself an instance of this doc's own §2.1 principle (extend an existing mechanism, don't add a parallel one for a problem the existing one already covers).

3.5 Guard 5 — signal-completeness.test.ts (locks G5, all three axes) ​

Mechanism: new Jest test, packages/app/src/domain/entities/__tests__/signal-completeness.test.ts (co-located with the existing signal.entity.test.ts), no DB required — pure source-tree comparison, so it runs in the existing yarn test gate (yarn workspaces foreach -A run test), already in pre-push and CI. Three assertions:

ts
// Axis 1: every literal type string passed to signals_emit(...) across all migrations
// exists in the SignalType union. Parse via a regex sweep over supabase/migrations/*.sql
// for `p_type\s*:=\s*'([a-z_]+)'` and bare-positional `signals_emit\(\s*'([a-z_]+)'`, at
// build/test time (Node fs, not a DB connection) — same "parse migration source" technique
// as scripts/lint-pi-schema-changes.mjs, just consumed from a Jest test instead of a CLI script.
it('every DB-emitted signal type exists in the SignalType union', () => { ... });

// Axis 2: every SignalType has a `signal.<category>.<type>.title` key in BOTH ko/signals.ts
// and en/signals.ts (import the real modules, iterate SIGNAL_TYPES, assert key presence).
// Exemption: an explicit ALLOWLIST const for types with zero DB emitter today (documents
// the B5 #12 vestigial-type gap as a tracked, named exception instead of a silent one).
it('every SignalType has ko + en title copy (or is in the documented vestigial allowlist)', () => { ... });

// Axis 3: every SignalCategory has a non-null branch in getSignalRoute's category switch
// (exhaustiveness via a switch over the SignalCategory union with a `never`-typed default,
// OR a runtime test that calls getSignalRoute with a minimal signal for every category and
// asserts result !== undefined — 'result can be null (context-dependent), but never undefined').
it('every SignalCategory resolves through getSignalRoute (no unhandled category)', () => { ... });

Verification: run today against main — Axis 2's assertion should currently fail exactly on B5 #12's 4 vestigial types (proving the test detects the known-live gap, not a strawman); ship the test with those 4 in the documented allowlist so the suite is green on day 1, then PS5/PS8 shrink the allowlist as those types get real emitters or get deleted (owner call, not this doc's scope).

3.6 Guard 6 — knip dead-export detection (locks G6) ​

Mechanism: add knip as a devDependency at the repo root; knip.json configured with workspaces entries for packages/app (entry: src/index.ts), each packages/features/* (entry: src/index.ts), apps/mobile (entry: Expo Router convention — app/**/*.tsx), apps/web (entry: Next.js App Router convention — app/**/*.tsx, app/**/route.ts). New script "knip": "knip" in root package.json; run as a non-blocking CI step first (mirrors the existing "Dependency audit" step's continue-on-error: true 1-week-bake pattern already used in ci.yml for exactly this reason — a brand-new whole-repo analyzer needs a bake period to tune false positives before it can gate merges), flip to blocking after the bake.

Verification: first run against current main — expect it to surface the referee-transfer hooks (use-propose-referee-transfer.ts + 4 siblings) and the join-request-approval UI's dead consumer path as findings, matching U4 #3 and U2 #1/B1 #8 exactly. This is simultaneously the acceptance test for the tool (it must find the two things already independently confirmed dead) and a live worklist for whichever PS ends up deciding those features' fate (PS4/PS6, owner call per those docs — not re-litigated here).

3.7 Guard 7 — RLS WITH CHECK completeness is Guard 1's G7 test; already covered ​

No separate mechanism — §2.2's G7 pgTAP test, activated by Guard 1, is the guard. Listed here only to close the loop: all seven recurring classes resolve to exactly four new artifacts (one CI job, one migration linter, one ESLint rule, one Jest test) plus one new dependency (knip) — not seven independent gates. This compression is itself evidence the guards are hitting real shared root causes rather than being seven bespoke patches.

4. Slices ​

Ordered by leverage (activation cost vs. how much dormant/future coverage each unlocks), not by dependency — only Slice 1 is a hard prerequisite (Slices 2's G3/G7 tests and any future pgTAP test are inert without it).

#SliceLocksTypeBlast radiusVerificationDepends on
1Wire supabase test db into CI (pgtap job)G1, G3, G7 (activation) + 18 dormant testsCI config.github/workflows/ci.yml onlyNew job appears + passes in Actions; intentionally break a live pgTAP assertion locally, confirm supabase test db catches itnone
2G3 + G7 pgTAP tests (table_grant_hygiene, rls_with_check_completeness)G3, G7migration-adjacent SQL test filessupabase/tests/*.test.sql, 2 new filesRuns green under Slice 1's job once PS1's §3.9/§3.5 fixes land; red beforeSlice 1; PS1 Slices 5, 7, 11 for a green baseline (test itself can land first and start red, documenting the gap, per this doc's own "guard before fix is still worth having" stance)
3PS1's G1 test (secdef_acl_hygiene.test.sql)G1migration-adjacent SQL test file1 file (already fully specified, PS1 §3.7)PS1's own Slice 9 verificationSlice 1; owned by PS1, cross-referenced not re-planned
4no-localecompare-sort ESLint rule + ratchet baselineG4eslint-pluginpackages/eslint-plugin/rules/no-localecompare-sort.js, index.js, eslint.ratchet.config.mjs (10-file baseline), 1 new rule test fileyarn lint:strict green with ratchet; throwaway violation in an 11th file fails; yarn lint:ratchet diff matches expected 10 filesnone
5scripts/lint-secdef-revoke-idiom.mjs + pre-push/CI wiringG2Node script + .husky/pre-push + ci.yml1 new script, 2 wiring diffsThrowaway migration with broken idiom fails; corrected idiom passes; full-repo LINT_ALL=1 run reports ~96 pre-existing (informational only — diff-scoped by default, matches lint-pi-schema's own grandfathering stance)none
6signal-completeness.test.ts (3 axes)G5Jest test1 new test file, 1 documented allowlist constGreen on day 1 (4 known vestigial types allowlisted); red if the allowlist is deleted without the underlying gap being closednone
7knip wired as non-blocking CI stepG6new devDependency, knip.json, ci.yml stepRepo-wide (read-only analysis, zero source changes)First run surfaces referee-transfer hooks + join-request UI dead-consumer path (matches U4 #3 / U2 #1 independently)none

Total: 7 slices, 1 CI-config change, 2 new pgTAP files (+1 owned by PS1), 1 new ESLint rule + ratchet entry, 1 new lint script + 2 wiring diffs, 1 new Jest test, 1 new dependency. Slice 1 is the single highest-leverage item in the whole doc — it is a ~15-line CI job that turns 18 already- written, currently-inert tests into an enforced gate with no other code change, and every subsequent pgTAP-based slice (2, 3, and any future PS's own pgTAP guard) is free once it lands.

Markdown remains the source of truth. Run yarn docs:check before handoff.