Cfn Data

CFN Data Skill (MegaPlan DAG Level 4)

Purpose: Design the data layer forward. Schema, indexes, RLS, migration up/down, lifecycle, concurrency, and privacy are authored here at plan time, not traced backward by cfn-plan-review after the build is wrong. This phase also produces the field-bindings table that cfn-ux reads to pick the correct control for every field (the source-of-truth that kills the dropdown-class bug).

Phase: data — DAG level 4. Conditional on the db build flag. Agent: database-architect.

Why forward, not backward: Review-time dependency tracing finds problems after the schema is poured. Designing the schema, its access policy, and its lifecycle up front means the implementer cannot wire a table with no RLS, an unscoped delete, or a migration that cannot roll back. Design is cheaper than catch.

When to Use

• The spec sets the db build flag (touches a table, schema, or persisted state).
• Auto-invoked by cfn-megaplan at level 4, after spec and decide return.
• Standalone for forward data-layer design of a new feature that persists anything.

Skip only when: no new or changed persisted state (pure compute, pure UI, read-only against an existing locked schema with no new query pattern).

Input

Required:

• planning/<slug>/SPEC_<slug>.md — entities, success criteria, query patterns, NFRs.
• planning/<slug>/DECISIONS_<slug>.md — storage/tradeoff decisions already locked by cfn-decide.

Refuse to run if SPEC_<slug>.md is missing or in draft status with unresolved [OPEN] gaps on the data model.

Orchestrator contract

cfn-megaplan passes:

Tier: <mvp|beta|enterprise>   Directive: <full|light>
Include extras: <...>   Omit: <drops>
Floor (forced on, never skip): rls, auth_boundaries, secrets_handling, no_unscoped_delete, pii_if_present

Tier resolution for this phase:

Floor override is absolute. A light (mvp) run still authors an RLS policy per new table, names the auth boundary, and routes secrets correctly. pii_if_present forces the privacy section on even at mvp when the data is personal or financial. No tier knob and no user downgrade disables a floor item.

Security FLOOR (never scales down)

These hold at every tier. They come from the user's global CLAUDE.md and are non-negotiable.

1. RLS per new table, in the same migration. Every CREATE TABLE is followed by ENABLE ROW LEVEL SECURITY and at least one explicit policy. Default-deny: no permissive policy means no access. Author an explicit allow for each role that needs it.
2. No unscoped DELETE/TRUNCATE. Every delete carries a WHERE that targets only the intended rows. Test cleanup identifies its rows by marker convention (test URLs contain example.com, slugs start with test-workspace-, emails match test-%@integration.test). Never TRUNCATE and never disable FK checks (session_replication_role) to dodge cleanup ordering.
3. Explicit schema qualification. Every table reference is schema.table (e.g. public.users). Never rely on search_path.
4. Reversible migrations. Every migration has a down direction, OR documents in one line why it is irreversible (data-destroying backfill, type narrowing with loss).
5. Schema-sync close-out. The plan ends with ~/.claude/skills/supabase-schema-sync/execute.sh so agent context reflects the new schema.
6. PII is a floor item. If the data is personal or financial, retention policy + access controls are forced on even at mvp.

Protocol

Step 1: Schema design

For every entity that persists, define:

• Table name, schema-qualified (public.enrollments).
• Columns: name, type, nullable, default, constraint. Cite the spec criterion each column serves.
• Primary key and foreign keys (schema-qualified target, on-delete behavior: CASCADE / RESTRICT / SET NULL, justified).
• Check constraints, uniqueness, enums. Prefer a DB-level constraint over app-level validation (build-ladder rung 4: native platform feature).
• Enum columns: trace every value through all consumers (DB constraint, serializer, API handler, UI renderer). A new enum value here is a code change in the same commit.

Pull the actual current schema before designing against it. Do not design from memory. Use ./.claude/skills/db-query/execute.sh or the db-query SKILL to dump existing tables this feature touches.

Step 2: Field-bindings table (the cfn-ux feed)

This is the load-bearing output of this phase. cfn-ux reads it to derive the control for every user-editable field; it cannot guess control types correctly without it.

Completeness rule: Emit a row for EVERY column of every new/changed table except surrogate PKs and audit columns (id, created_at, updated_at). Set UI access to editable/readonly/none; each none needs a one-line justification. Over-inclusion is free; omission is the dropdown bug.

Closed vocabulary (binding kinds): the canonical nine tokens are FK | enum | lookup | boolean | date | timestamp | free-text | numeric | multi-FK. These nine tokens are the closed vocabulary; emit no other value. cfn-ux matches the Binding kind cell byte-for-byte against its derivation map; an unmatched token routes back here as a cfn-data defect.

Binding kinds and the control they imply downstream:

Table format (required in the artifact, 8 columns, pinned):

| Field | Type | Binding kind | Source table/enum | Required | Options/rows (count or est.) | Range/length | UI access |
| course_id | uuid | FK | public.courses | yes | 12 rows | - | editable |
| status | text | enum | enrollment_status | yes | 4 values | - | readonly |
| notes | text | free-text | - | no | - | <=500 chars | editable |

Options/rows for FK/lookup: run a COUNT(*) via db-query or state the spec estimate; never leave blank. Range/length: copy the CHECK/length constraint from Step 1; - only if genuinely unconstrained.

Consumer (cfn-ux section 1) matches this table byte-for-byte; unmatched values route back as producer defects.

Step 3: Index design

For every index, name the query pattern from the spec that justifies it. No speculative indexes.

• Index FKs used in joins or filtered lookups.
• Index columns in WHERE, ORDER BY, and GROUP BY on hot paths named in the spec.
• Composite indexes ordered by selectivity; state the query they serve.
• Partial / unique indexes where the constraint or query warrants.

| Index                          | Columns              | Serves query                          |
|--------------------------------|----------------------|---------------------------------------|
| idx_enrollments_course         | course_id            | list enrollments for a course (SPEC AC-4) |
| uq_enrollments_user_course     | user_id, course_id   | enforce one enrollment per user/course |

Step 4: RLS + auth boundary (FLOOR)

For each new table, author the policy in the migration:

• The role set comes from SPEC §1a Actor Inventory, verbatim. Its Actor names are the Principal/role values below and its Trust boundary column names where each principal's identity is established. Do not invent a role here: cfn-arch §6 builds its AuthZ matrix from the same §1a table at L5, and a role set invented independently at L4 does not have to agree with it. An actor that needs data access but has no policy — or a policy naming a principal absent from §1a — is a defect; route back to cfn-spec. §1a is required whenever db: yes precisely so this floor has a source.
• State the auth boundary. Who is the principal? (auth.uid(), a service role, a tenant claim.) Where is identity established (§1a Trust boundary; cross-reference SPEC NFRs and cfn-arch AuthN)?
• Default-deny. ENABLE ROW LEVEL SECURITY with no policy = no access. List each policy as an explicit allow.
• One policy per operation/role (SELECT/INSERT/UPDATE/DELETE). Name the USING and WITH CHECK predicate.
• Every §1a actor is accounted for per table: an explicit allow policy, or an explicit note that it has no access to that table. An anonymous actor in §1a with no stated policy is the exact path to an accidental permissive read.
• Service-role writes that bypass RLS must be named explicitly and justified. §1a Kind = service enumerates the candidates; a service actor bypassing RLS with no justification here is a floor violation.

Emit the per-operation policy table (pinned shape, required in the artifact):

| Table | Operation | Principal/role | USING | WITH CHECK |

Completeness rule: every new table x each of SELECT/INSERT/UPDATE/DELETE gets a row; intentionally-disallowed operations get an explicit (no policy - default-deny) row; a missing row is a floor violation.

Step 5: Migration up / down

• Up: ordered so FK targets exist before referencing tables. CREATE TABLE -> constraints -> indexes -> ENABLE RLS -> policies, per table.

• Down: exact reverse, or a one-line note on why it is irreversible.

• In-flight data during cutover: if the migration changes an existing table, state what happens to existing rows mid-deploy. Backfill order, default for the new column, whether the old code path still works against the new schema (expand/contract). A non-null column added to a populated table needs a default or a backfill step, not a bare ADD COLUMN NOT NULL.

• Migration filename: NNNN_descriptive_name.sql.

• Rehearsal invocation (reversible migrations): name the exact command that proves up+down round-trips, verbatim:

  CFN_SCRATCH_DATABASE_URL=<scratch> ./.claude/skills/cfn-migration-rehearsal/execute.sh --up <NNNN.up.sql> --down <NNNN.down.sql>
  

  cfn-ops Phase 6 and cfn-test-plan Phase 3 cite this line verbatim as executable rollback evidence. An irreversible migration names its one-line reason instead of this command; it is never rehearsed.

Step 6: Concurrency / idempotency (beta+ extra — G13, G47)

Drop at mvp unless the concurrency is inherent to the feature. Otherwise design:

• Double-submit / retries: idempotency key (natural unique constraint or explicit key column) so a retried write does not duplicate.
• Ordering: does write order matter? If so, name the ordering guarantee (sequence, timestamp, version column).
• Locks: optimistic (version column, compare-and-set) vs pessimistic (SELECT ... FOR UPDATE). State which and why. Prefer optimistic unless contention is proven.
• Race windows: name the check-then-act gaps and how a constraint or lock closes each.

Emit the pinned concurrency-control table (required in the artifact; one row per control):

| CC-id | Mechanism (idempotency-key|unique-constraint|optimistic-lock|pessimistic-lock|ordering) | Entity (schema.table) | Race scenario closed (double-submit|parallel-write|retry-refire|check-then-act) | Enforcement (exact DDL/stmt from §1/§5) | Expected conflict behavior (decidable: 409 DUPLICATE, second write rejected, idempotent 200) |

No-concurrency builds emit a single N/A: <reason> row. The Expected conflict behavior cell is a decidable claim about what the loser of the race observes (409 DUPLICATE, second write rejected, idempotent 200), never "handled safely".

Self-check: every CC row's Enforcement object must already appear in §1 (schema) or §5 (migration). A control citing a constraint or lock statement absent from §1/§5 is an incomplete artifact; fix it before returning.

CC-id is the greppable token cfn-test-plan consumes to drive one race-closing test per row, and the key behind Bar A coverage counters cc_total/cc_mapped.

Step 7: Data lifecycle (beta+ extra — G19)

Drop at mvp. Otherwise design the full lifecycle:

• Seed: reference/lookup rows the feature needs to function on day one.
• Backfill: how existing rows acquire values for new columns. Batched, idempotent, resumable.
• Retention: how long rows live, what triggers expiry.
• Cleanup: scoped deletes only (WHERE targeting expired/test rows). Name the marker convention for test data. Never unscoped.

Step 8: Privacy / PII / compliance (G22 — FLOOR if pii flag)

Forced on when the data is personal or financial, regardless of tier.

• Classify: list every column that is PII or financial.
• Retention policy: how long PII is kept, deletion trigger (account close, legal window).
• Access controls: which roles read PII, masking/redaction at the boundary, audit of access.
• Enterprise compliance extra: legal retention windows, right-to-erasure path, data-residency note.

Step 9: Multi-tenancy isolation (enterprise extra — G33)

Drop below enterprise. Otherwise:

• Isolation model: row-level (tenant_id column + RLS predicate) vs schema-per-tenant vs database-per-tenant. State which and why.
• Tenant key: where the tenant id comes from (JWT claim, session), how RLS enforces it on every table.
• Cross-tenant leak prevention: every tenant-scoped table's RLS predicate includes the tenant key. No query path bypasses it.

Step 10: Schema-sync (FLOOR close-out)

The plan's final data step:

~/.claude/skills/supabase-schema-sync/execute.sh

Run after the migration applies so the db-query skill and agent context reflect the new schema.

Output

Artifact location. Every artifact of one plan lives in that plan's own directory, planning/<slug>/. Under /cfn-megaplan, /cfn-megaplan-lite, or /cfn-spa-plan the orchestrator hands you the exact path plus a Plan dir: line — write there, and read the input paths it gives you verbatim. Invoked standalone, read with $HOME/.claude/skills/cfn-megaplan/lib/plan-paths.sh resolve <slug> <basename> (per-plan dir first, legacy flat planning/ second) and write to planning/<slug>/. Never split one plan across two locations.

Write to: planning/<slug>/DATA_<slug>.md

Template:

# Data Layer: <task>

**Date:** <YYYY-MM-DD>
**Spec:** planning/<slug>/SPEC_<slug>.md
**Decisions:** planning/<slug>/DECISIONS_<slug>.md
**Tier:** mvp | beta | enterprise   **Build flags:** db pii?
**Status:** draft | reviewed | locked

## 1. Schema
### Table: public.<name>
- Columns (name, type, null, default, constraint)
- PK / FKs (target, on-delete)
- Constraints / enums

## 2. Field bindings (cfn-ux feed)
| Field | Type | Binding kind | Source table/enum | Required | Options/rows (count or est.) | Range/length | UI access |
(every column of every new/changed table except surrogate PKs and audit columns; Binding kind from the closed nine-token vocabulary. Consumer, cfn-ux section 1, matches this table byte-for-byte; unmatched values route back as producer defects.)

## 3. Indexes
| Index | Columns | Serves query |

## 4. RLS + auth boundary
- Principal / auth boundary
| Table | Operation | Principal/role | USING | WITH CHECK |
(every new table x SELECT/INSERT/UPDATE/DELETE; disallowed operations get an explicit `(no policy - default-deny)` row; a missing row is a floor violation)

## 5. Migration
- Up (ordered)
- Down (or irreversible-why)
- In-flight data during cutover
- Filename: NNNN_*.sql
- Rehearsal invocation (reversible; cited verbatim by cfn-ops Phase 6 + cfn-test-plan Phase 3):
  `CFN_SCRATCH_DATABASE_URL=<scratch> ./.claude/skills/cfn-migration-rehearsal/execute.sh --up <NNNN.up.sql> --down <NNNN.down.sql>`

## 6. Concurrency / idempotency   (beta+; mvp: dropped unless inherent)
| CC-id | Mechanism (idempotency-key|unique-constraint|optimistic-lock|pessimistic-lock|ordering) | Entity (schema.table) | Race scenario closed (double-submit|parallel-write|retry-refire|check-then-act) | Enforcement (exact DDL/stmt from §1/§5) | Expected conflict behavior (decidable: 409 DUPLICATE, second write rejected, idempotent 200) |
(no-concurrency builds emit a single `N/A: <reason>` row; every CC Enforcement object appears in §1 or §5; CC-id is the greppable token cfn-test-plan consumes and the Bar A `cc_total/cc_mapped` key)

## 7. Lifecycle   (beta+; mvp: dropped)
- Seed / backfill / retention / scoped cleanup

## 8. Privacy / PII / compliance   (floor if PII present)
- Classified columns / retention / access controls

## 9. Multi-tenancy   (enterprise only)

## 10. Schema-sync
~/.claude/skills/supabase-schema-sync/execute.sh

Example (excerpt)

-- 0042_create_enrollments.sql  (up)
CREATE TABLE public.enrollments (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     uuid NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  course_id   uuid NOT NULL REFERENCES public.courses(id) ON DELETE RESTRICT,
  status      text NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending','active','completed','cancelled')),
  starts_on   date NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX uq_enrollments_user_course
  ON public.enrollments (user_id, course_id);          -- one enrollment per user/course
CREATE INDEX idx_enrollments_course
  ON public.enrollments (course_id);                   -- SPEC AC-4: list by course

-- RLS (FLOOR): default-deny, owner-scoped
ALTER TABLE public.enrollments ENABLE ROW LEVEL SECURITY;
CREATE POLICY enrollments_select_own ON public.enrollments
  FOR SELECT USING (user_id = auth.uid());
CREATE POLICY enrollments_insert_own ON public.enrollments
  FOR INSERT WITH CHECK (user_id = auth.uid());

-- 0042_create_enrollments.sql  (down)
DROP TABLE IF EXISTS public.enrollments;   -- indexes + policies drop with the table

RLS per-operation table (note the two explicit default-deny rows):

| Table              | Operation | Principal/role             | USING                | WITH CHECK           |
|--------------------|-----------|----------------------------|----------------------|----------------------|
| public.enrollments | SELECT    | authenticated (auth.uid()) | user_id = auth.uid() | -                    |
| public.enrollments | INSERT    | authenticated (auth.uid()) | -                    | user_id = auth.uid() |
| public.enrollments | UPDATE    | (no policy - default-deny) | -                    | -                    |
| public.enrollments | DELETE    | (no policy - default-deny) | -                    | -                    |

Field bindings emitted for cfn-ux (8 columns; every non-audit, non-PK column of the new table appears):

| Field      | Type | Binding kind | Source table/enum        | Required | Options/rows (count or est.) | Range/length | UI access |
|------------|------|--------------|--------------------------|----------|------------------------------|--------------|-----------|
| user_id    | uuid | FK           | public.users             | yes      | n/a (server-set)             | -            | none (set from auth.uid(); never user-picked) |
| course_id  | uuid | FK           | public.courses           | yes      | 12 rows (COUNT via db-query) | -            | editable  |
| status     | text | enum         | enrollment_status (CHECK)| yes      | 4 values                     | -            | readonly  |
| starts_on  | date | date         | -                        | yes      | -                            | within course schedule window | editable |

Scoped test cleanup (never unscoped):

DELETE FROM public.enrollments
WHERE user_id IN (SELECT id FROM public.users WHERE email LIKE 'test-%@integration.test');

Self-check (before returning)

Before returning: every new table appears in sections 1, 2, 3, 4 AND 5; counts must match. A table present in section 1 (schema) but missing from field bindings, indexes, the RLS per-operation table, or the migration is an incomplete artifact; fix it before returning.

Return to orchestrator

• Artifact path: planning/<slug>/DATA_<slug>.md
• 3-line summary (tables created, RLS posture, migration reversibility)
• [OPEN] items needing a user decision (ambiguous retention window, unclear tenant key, FK on-delete ambiguity)

Review Mode (audit implemented code)

Invoked as cfn-data --review [<migration-dir>|<model-path>|--live]. Reads the SHIPPED schema instead of designing forward, then audits it against the same floor and emits the real field-bindings table for cfn-ux --review to consume.

No SPEC/DECISIONS required. Schema is the input. Source priority: --live (pull actual schema via ./.claude/skills/db-query/execute.sh or the supabase-schema-sync output — read-only, never mutate) > migration files > ORM models.

Steps

1. Dump the real schema. Every table in scope: columns (type, nullable, default), constraints, FKs (both directions), indexes, RLS state + policies, triggers.
2. Floor audit (these are violations, not suggestions):
   • RLS — any table with no ENABLE ROW LEVEL SECURITY + policy. HIGH.
   • Unscoped delete risk — any function/trigger/migration with DELETE/TRUNCATE lacking a row-targeting WHERE, or session_replication_role='replica'. HIGH.
   • PII unguarded — columns matching email/name/phone/address/token with no stated handling. HIGH if present.
   • Schema-qualification — queries relying on search_path default instead of explicit schema. MED.
3. Integrity audit: FK columns without an FK constraint; nullable columns the app treats as non-null at the boundary; aggregate results (MAX/COUNT) consumed without null/cast handling. MED.
4. Index audit: query patterns (from the calling code, if provided) with no supporting index; unused indexes. LOW/MED.
5. Migration reversibility: migrations with no down / rollback. MED.
6. Emit real field-bindings. Same table shape as forward Step 2 — but derived from the actual schema. This is the source of truth cfn-ux --review reads so the UI audit is not guessing.

Output

Write planning/<slug>/AUDIT_DATA_<slug>.md: a floor/integrity findings table (object | issue | severity | fix) PLUS the recovered field-bindings table (so the trio chains). Empty findings = PASS, state it.

| object | issue | severity | fix |
|--------|-------|----------|-----|
| public.bookings | no RLS policy | HIGH | enable RLS + owner policy in a migration |
| fn cleanup_old() | DELETE without WHERE | HIGH | scope to test/expired rows only |

Read-only: this mode never writes to the database and never proposes a destructive migration without the global DELETE/TRUNCATE approval protocol.

Anti-Patterns

• Table without RLS. A CREATE TABLE with no ENABLE ROW LEVEL SECURITY + policy in the same migration. Floor violation.
• Unscoped DELETE/TRUNCATE. Any delete without a WHERE that targets only intended rows. Wipes production. Disabling FK checks to dodge cleanup ordering is the same sin.
• search_path reliance. Bare users instead of public.users. Schema must be explicit on every reference.
• Irreversible migration with no note. A migration lacking a down direction and lacking the one-line reason it cannot have one.
• Tracing deps at review instead of designing here. Leaving schema/lifecycle/concurrency for cfn-plan-review to discover backward. This phase exists to design them forward.
• Field omitted from the bindings table. A UI-touched field not listed means cfn-ux guesses the control. That is the dropdown bug.
• New enum value in the type only. Adding a value without tracing it through DB constraint, serializer, API handler, and UI renderer.
• Speculative index. An index with no named query from the spec to justify it.
• ADD COLUMN NOT NULL on a populated table with no default or backfill step.

Related

• Upstream: cfn-spec (entities, query patterns), cfn-decide (storage decisions)
• Orchestrator: cfn-megaplan (level 4, conditional on db)
• Downstream: cfn-ux (reads field bindings), cfn-arch (consumes schema), /write-plan (synthesizes), cfn-plan-review (Bar B)
• Floor close-out: ~/.claude/skills/supabase-schema-sync/execute.sh
• DB access: ./.claude/skills/db-query/execute.sh
• Backlog: docs/PLANNING_PIPELINE_GAPS.md (G05, G13, G19, G22, G33)