---
name: snappy-data-hygiene
role: Schema design review — enforce the canonical/derived taxonomy before any storage is added or modified
loaded-by: PreToolUse hook (auto-injected when "snappy-data-hygiene" appears in a Task prompt) or on schema-touching requests
Triggers on: add column, add table, add settings key, schema change, data model, add field, KV config, settings bag, denormalize
---

# snappy-data-hygiene — Agent Loader

You are the upstream gate on schema changes. This file is load-bearing and self-sufficient. You may not read other files in this skill directory unless explicitly handed off — every rule you need is here.

Owner: Robert Boulos.

## The one question

Before a single column, table, settings key, or stored field is added or modified:

> **"Is this value canonical or derived? If derived, what's the formula, and does the schema make it impossible to store it inconsistently with that formula?"**

If you cannot answer cleanly, STOP and surface the ambiguity to the user. Do not proceed to implementation.

## The taxonomy (memorize)

Every piece of data in any system is exactly one of these:

| Kind | Definition | Storage | Mutated by |
|---|---|---|---|
| **CANONICAL** | A fact or choice nothing else determines | A stored column / row | A user action, external event, or explicit write |
| **DERIVED** | Fully determined by canonical values via a formula | NOT stored — computed at read | The formula alone, never directly |

If a value is "sometimes stored, sometimes computed" — that's a latent bug. It WILL diverge. Pick one.

Common disguises for derived values that people mistakenly store:
- Any column labeled `*_cached`, `*_computed`, `*_summary`, `*_count` without a refresh mechanism
- `full_name` when `first_name + last_name` exist
- `total_price` when line items + tax rate exist
- Composed prompt text when the inputs (persona + scene + history) are stored elsewhere
- Any "settings" key whose value is actually a function of other settings
- Denormalized display strings that depend on foreign keys

## Red flags that trigger this skill

Invoke this skill when you see ANY of the following in a request or codebase:

1. **"Let's add a column for X"** where X is described in terms of other columns.
2. **"Store it so we don't have to recompute"** (caching without a refresh contract).
3. **A key-value "settings" / "config" / "meta" table** with stringly-typed keys. These bags are structurally anti-normal — every new key is a potential 3NF violation that the database will not warn you about.
4. **The same value computed differently in multiple places.** Pick the canonical source or pick ONE formula location. Not both.
5. **Read paths that reference stored derived values without going through a helper.** Each bypass is a future divergence.
6. **Write paths that accept ANY key (e.g. `PUT /settings {key, value}`) without validating which keys are allowed to be stored.** The door is open to all kinds of illegal state.

## Academic grounding (1-sentence version)

This is **Codd's 3rd Normal Form (1971)**: no non-key column should depend on another non-key column. Storing a derived value alongside its inputs IS a transitive dependency, and it IS the entire reason you have drift.

If you want to sound smart, call it "eliminating transitive dependencies." If you want to sound like Robert, call it "don't store what you can compute."

## The diagnostic flow

When reviewing a proposed schema change, run this flow:

1. **List every field.** For each, classify: canonical or derived?
2. **If derived**, state the formula in one sentence (e.g. `image_style = vibes[active_vibe_id].image_style`).
3. **Check the enforcement level**:
   - Best: the storage layer makes it impossible to write the derived value (Postgres `GENERATED ALWAYS AS ... STORED`, a view, a function, no column at all).
   - Middle: the write API rejects the derived key at the boundary (our `agent_settings` PUT guard pattern).
   - Worst: it's just a convention and reviewers are trusted to spot it. This will fail eventually.
4. **Check the read paths**: does every reader call the same composer/formula, or do some read raw?
5. **Check the write surface**: can a caller write the derived value directly, bypassing the formula? If yes, that's your next bug.

If any of 3/4/5 come back worst-case, you have a 3NF violation in progress.

## Storage enforcement tools (by backend)

| Backend | Derived-value enforcement |
|---|---|
| **PostgreSQL** | `GENERATED ALWAYS AS (<expr>) STORED` columns. Views. Materialized views with refresh contracts. |
| **Xano** | No native generated columns. Enforce in app code: one function owns composition (e.g. `Config/resolve_scene`), PUT endpoints reject derived-key writes with a precondition error naming the composer. |
| **MySQL** | `GENERATED ALWAYS AS (<expr>) STORED` / `VIRTUAL`. |
| **Datomic** | By construction — no stored derived state. Everything non-fact is a query. |
| **dbt / modern data stack** | Contract-based models + `tests:` block. Lineage graphs surface transitive dependencies. |
| **Any ORM (Prisma, SQLAlchemy, Drizzle)** | Computed/virtual fields if supported. Otherwise, expose derived values only through model methods, never as writable columns. |

For Xano specifically, see the retroactive playbook at `~/.claude/projects/*/memory/funnel-reject-playbook.md` when you find an existing violation.

## The KV-bag warning (hard rule)

Key-value settings tables (`agent_settings`, `config`, `meta`, `preferences`) are **structurally anti-normal**. The moment you create one, you've opted out of schema-level enforcement because the database just sees strings.

Rules when a KV bag exists or is proposed:
1. **Before adding any new key**, apply the canonical/derived test. If derived, do NOT add it — add the inputs as keys and put the formula in a composer function.
2. **Every KV bag must have a write guard** that validates keys against an allow-list. No wildcard PUT endpoints.
3. **Every KV bag must have a single reader helper** that scopes by whatever owner dimension applies (user_id, companion_id, tenant_id). Bare `db.get where key=X` patterns leak across owners.
4. **Prefer proper schemas over KV bags** when the key domain is known. The bag is only appropriate when keys are genuinely user-defined and unbounded.

## API module

```typescript
import { classifyField, reviewSchema, getReviewPrompt } from "../snappy-data-hygiene/api.ts";
```

Or CLI:
```bash
npx tsx ~/.claude/skills/snappy-data-hygiene/api.ts prompt                                # returns canonical review prompt text
npx tsx ~/.claude/skills/snappy-data-hygiene/api.ts classify "field_name" "table/context" # classifies a field as canonical/derived/ambiguous
npx tsx ~/.claude/skills/snappy-data-hygiene/api.ts review path/to/schema.json            # reviews a proposed schema for violations
```

### API functions

| Function | Purpose |
|----------|---------|
| `classifyField(name, context)` | Returns `{ classification: "canonical" \| "derived" \| "ambiguous", reasoning, formula? }`. Heuristic-based; ambiguous cases must go to human. |
| `reviewSchema(schema)` | Static review of a proposed schema (array of `{name, type, description}`). Returns violations list — transitive dependencies, unscoped KV patterns, unenforced derivations. |
| `getReviewPrompt()` | Returns the canonical review prompt for injection into another agent's context. |

## Produce-output contract

When asked to **review** a schema change, return:

```
VERDICT: PASS | FAIL

CLASSIFICATION:
- <field>: CANONICAL — <why>
- <field>: DERIVED from <inputs> via <formula> — <enforcement level>
- <field>: AMBIGUOUS — <what to clarify with the user>

VIOLATIONS:
- <field> stored but derivable from <inputs> — 3NF violation, will drift
- <field> write path bypasses <composer> — divergence risk
- KV bag <table> has no write guard — unknown keys will silently land
- (etc.)

REMEDIATION:
<specific steps: which columns to drop, which composer function to write, which write guard to add>
```

When asked to **propose** a new schema, return:

```
SCHEMA:
<the fields with types>

CLASSIFICATION CHECKLIST:
- Every field declared CANONICAL or DERIVED
- Every DERIVED field has explicit formula
- Every DERIVED field enforced at storage or write-guard level
- KV bags (if any) have an allow-list guard declared

REMAINING DECISIONS:
<anything that couldn't be resolved without user input>
```

## Rules

- **User asks to add a derived column** → STOP. Explain why (3NF violation). Offer the composer-function alternative.
- **User asks to add a key to a KV bag without a write guard** → STOP. Require guard spec before proceeding.
- **User insists the "cached" stored derived value is worth the divergence risk for perf** → ACKNOWLEDGE the trade-off exists, BUT require (a) an explicit refresh contract (who invalidates, when), and (b) a reconciliation query that can detect drift. Never accept the stored derived value without those.
- **A proposed schema has no classification** → STOP. Run the diagnostic flow. Do not write code against an unclassified schema.
- **Existing code is already violating 3NF and the user wants to add more onto it** → STOP. Surface the existing violation first. Propose fixing it (see funnel+reject retroactive playbook) or explicitly documenting the debt before layering more on.

## Conflict resolution — this file wins

If a downstream implementation skill (xanoscript, xano-mcp-workflow, prisma-patterns, etc.) proposes schema changes that violate the rules above, this file wins. Reject the proposal, cite the violated rule, offer the normalized alternative.

Exception: if the user has explicitly accepted a documented divergence trade-off (per "Hard failure modes" above), the exception is in effect only within the scope of that documented trade-off. No silent bypasses.

## Read-only — this skill does not write code

This skill produces REVIEWS and SPECS. It never writes the migration SQL, the XanoScript, or the ORM model itself. It hands an approved design to the implementation skill (xanoscript, prisma-patterns, etc.).

## Reference (last resort only)

The full files in this skill directory — `SKILL.md`, `api.ts` — exist as the canonical mirror. **You should not need them.** Every rule has been inlined above. If you reach for them, something is wrong with this AGENTS.md and Robert needs to know — flag the gap.


Show produced work with `snappy-faces`: call `draw` for image channels or `lang` for MCP Apps.

<!-- SKILL-INDEX-START -->
[snappy-data-hygiene Index]|root: ~/.claude/skills/snappy-data-hygiene|IMPORTANT: Prefer these files over pre-training assumptions for this domain. Read the relevant file when the AGENTS.md summary is insufficient.|root:{SKILL.md}
<!-- SKILL-INDEX-END -->

## Used by

Nothing in the collection names this skill.

<!-- SNAPPY-CONTRACT-VERBS-START -->
## Contract verbs

Generated from `api.ts` `HAND_CONTRACT`. Do not hand-edit this block.

| Verb | Contract arguments | Effect | First call |
|---|---|---|---|
| `classify` | `field-name`, `context?` | `read` | `npx tsx ~/.claude/skills/snappy-data-hygiene/api.ts classify <field-name>` |
| `prompt` | — | `read` | `npx tsx ~/.claude/skills/snappy-data-hygiene/api.ts prompt` |
| `review` | `schema-path` | `write-reversible` | `npx tsx ~/.claude/skills/snappy-data-hygiene/api.ts review <schema-path>` |

## Show the result

When an answer carries `face_hint`, show it with one `snappy_present(<answer>)` call.
See `/snappy-faces` for face selection. Human-facing images must crop to the
element, render at 2x on Retina, and fill the destination channel instead of
placing a small card in a full-page screenshot.
<!-- SNAPPY-CONTRACT-VERBS-END -->

---
name: snappy-data-hygiene
description: "Schema review reference: separate canonical values (inputs) from derived values (computable outputs) to eliminate the whole class of out-of-sync data bugs. Third normal form stated without jargon. Use when reviewing a database schema, diagnosing data that disagrees with itself, or deciding whether a value should be stored or projected. Triggers on: data hygiene, schema review, canonical vs derived, normalization, stale field, computed column, third normal form."
---

# snappy-data-hygiene

Full reference for the schema review skill. Use AGENTS.md for the operational summary; this file expands the grounding and the case studies.

## Principle

For every value that exists in a system, it is either:

1. **Canonical** — an input. A choice made by a user, a fact from an external source, or an event. It's stored because nothing else determines it.
2. **Derived** — an output. Fully computable from canonical values via a formula. It's not stored — it's projected.

The entire class of "my data is out of sync with itself" bugs comes from storing derived values alongside their inputs. This is E.F. Codd's 3rd Normal Form (1971) stated without jargon: **no non-key column should depend on another non-key column**.

A schema that mixes canonical and derived storage is an untyped schema. The database doesn't know which columns are inputs and which are outputs, so it has no way to enforce consistency. Drift becomes inevitable — it's not a matter of "if" but of "when."

## Why this matters architecturally

Most "refactor the data model" work in real codebases is cleanup after 3NF violations have landed and compounded. The bug isn't in the refactor — the bug is in having allowed the violation in the first place.

Preventing the class of bugs requires changing the *question asked at design time*:

| Old question | New question |
|---|---|
| "What columns do I need?" | "Which of these are canonical, which are derived, and what enforces the distinction?" |
| "Where should I store this value?" | "Should this be stored at all, or computed?" |
| "What fields go in this settings row?" | "Does this key belong here, or does it derive from inputs that are already stored?" |

The answer to the new question ~70% of the time is: fewer columns than you first drafted. The remaining canonical fields become obvious, and the derivation becomes a named function.

## Why key-value settings bags are structurally dangerous

A KV bag (table with schema `{key, value, owner_id}` where `key` is a free string) is an opt-out from schema-level type checking. Every new "key" is an undeclared column. The database has no way to enforce:

- Which keys are allowed (typo `"mdoel"` lands successfully next to `"model"`)
- Which keys are canonical vs. derived (you can freely store `sum_of_line_items` next to the line items)
- Which keys are scoped to which owner (a single row can bleed across tenants if read paths don't explicitly scope)
- Which values are valid for a given key (the column is `text`, everything is a string)

KV bags look cheap at the start (one table handles everything, no schema migrations for new settings) and become one of the most expensive tables in the codebase as they accumulate 3NF violations, stringly-typed values, and implicit ownership semantics.

**Rules for when a KV bag is acceptable:**
1. The key domain is genuinely user-defined (e.g. user-authored custom metadata tags). Not "settings that we, the developers, keep adding to."
2. Values are treated uniformly (e.g. all strings, no type-specific read logic). If your read code does `if key == "model" then parse as URL` you don't have a KV bag, you have a badly-typed proper schema.
3. There's a write guard validating keys against an allow-list. No wildcard PUTs.

**Rules for when a KV bag is NOT acceptable (use proper tables instead):**
1. The key domain is fixed and known at design time (`model_chat`, `model_vision`, `model_npc` — these are a `model_config` table with three columns, not three KV rows).
2. Values have type-specific semantics (URL, enum, JSON-shaped). Type them.
3. You find yourself adding new keys every sprint. That's a schema growing organically without migrations — it will rot.

## The diagnostic flow (expanded)

### Step 1 — Classify every field

Lay out every column / key / field in the proposed change. For each:

- **Canonical**: "This is input data. Changing it is a user action or external event."
- **Derived**: "This is output data. Given the inputs, there is exactly one correct value."
- **Ambiguous**: "I can't tell without asking the user."

Ambiguous is a real answer and requires escalation. Don't guess.

### Step 2 — State the formula (for derived fields)

For each derived field, write the formula in one sentence:

- `full_name = first_name + " " + last_name`
- `total_price = sum(line_items.price) + tax_rate * sum(line_items.price)`
- `image_style = vibes[active_vibe_id].image_style`
- `chain_count = count(posts WHERE root_post_id = <this>)`

If you can't state the formula cleanly, one of three things is true:
1. The field isn't actually derived — it's canonical and requires a real user input.
2. The field is derived from too many inputs to express — reconsider whether those inputs should be aggregated first (they probably should).
3. The "formula" is actually "whatever someone happened to write last time" — that's not a formula, that's chaos.

### Step 3 — Pick the enforcement level

Ranked best to worst:

1. **Impossible to misstore**: the column doesn't exist as storage. It's a view, a generated column, or a stored function. Example: Postgres `image_style GENERATED ALWAYS AS (compute_from_vibe(active_vibe_id)) STORED`.
2. **Rejected at the write boundary**: the storage accepts writes, but the write API has a guard that rejects writes to this field with a precondition error. Example: `PUT /settings` rejects `key: "image_style"` with a 400 naming the composer.
3. **Single reader convention**: the storage is writable, but every reader goes through a named function. New readers are expected to follow the convention. Works until someone doesn't.
4. **Comment convention**: "// don't write this directly, use the helper." Works until it doesn't. Which is fast.

Aim for 1 or 2. Never settle for 4.

### Step 4 — Sweep the read paths

For each derived field, grep for every read site. Does each go through the composer, or does some read the stored column raw?

If some readers bypass the composer, either:
- The composer isn't the canonical read path (fix: make it so, with a guard), OR
- The stored value is treated as canonical by those readers (fix: classify honestly — maybe it IS canonical and the "derivation" is a lie you tell yourself).

### Step 5 — Sweep the write paths

For each derived field, find every write path. Is there one? There shouldn't be.

If there is (e.g. legacy migration, admin tool, KV bag PUT), add a rejection at the boundary. Every write to a derived value is a bug with a delayed fuse.

## What to do when you find an existing violation

Follow the retroactive playbook (project-specific):
- `~/.claude/projects/*/memory/funnel-reject-playbook.md`

Summary: build the composer, reject at the boundary, sweep callers, purge stale state, verify invariants.

This skill does not teach you HOW to do that retrofit — it teaches you how to recognize the violation and how to prevent the next one. The mechanics of cleanup are specific to the backend and live in the project memory.

## Case study — agent_settings (April 2026)

**Violation**: A key-value settings table stored 9 derived text chunks (`image_style`, `narrative_mode`, `content_mode`, `image_prompt_prefix`, `group_photo_prompt`, `scene_ref_urls`, `agent_directive`, `auto_grok_prompt`, and one more) all computable from two canonical inputs (`active_env_id`, `active_vibe_id`) via lookups into an `environments` table and a `vibes` table.

**Symptoms**:
- Per-companion override rows leaked into global reads because read paths used `db.get where key=X` without owner scoping. Companion A's custom directive appeared in Companion B's posts.
- Picker changes failed silently when one of the 9 writes missed. UI showed "hotwife vibe" selected while the backend was still composing from "solo."
- User-typed text could land in any of the 9 fields, poisoning downstream prompts with companion names.

**Classification after review**:
- `active_env_id`: CANONICAL (user's choice)
- `active_vibe_id`: CANONICAL (user's choice)
- `agent_directive`: DERIVED from `(env, vibe)` OR CANONICAL if user overrides (this was the ambiguous case — resolved by making the default derived and allowing a documented override path)
- All 7 others: DERIVED (pure lookups into env/vibe tables)

**Fix**:
1. Built `Config/resolve_scene(companion_id?)` — the one composer.
2. Hardened `PUT /settings` to reject the 7 derived keys with an `ERROR_CODE_INPUT_ERROR` naming the composer.
3. Swept 14+ read sites to use `resolve_scene` instead of raw `db.get`.
4. Purged 12 stale derived rows.
5. Frontend now writes only the 2 IDs.

**Lesson**: if the original schema had asked "is this canonical or derived?" for each of the 9 fields before they were added, 7 of them would never have existed, 1 would have been classified as genuinely derived, and 1 would have been explicitly documented as "canonical override path for user customization." The bug class is impossible under that schema.

## Case study — Agent/call_persona (April 2026)

Slightly different shape — not a storage 3NF violation, but the same pattern of "one authoritative function owns the concern, bypass must be impossible."

**Violation**: 6+ functions each building LLM prompts and calling OpenRouter with their own audit / filter / model-selection logic. Divergent implementations meant some paths skipped the banned-phrase filter, some skipped the audit log, some used the wrong model.

**Classification**:
- LLM API call parameters: DERIVED from `(companion_id, purpose, context)` via a single prompt-building pipeline.
- Audit row: DERIVED from `(request, response)` — always written, always the same shape.

**Fix**: `Agent/call_persona(companion_id, purpose, ...)` owns every persona LLM call. Callers specify purpose enum. 6 call sites migrated.

**Lesson**: "multiple places doing the same mechanical work" and "stored derived values" are the same underlying problem viewed through different lenses. Both are: "there should be one place where this logic lives, and the schema/API should make bypassing it impossible."

## References

- **Codd, E.F. (1971)** — "Further Normalization of the Data Base Relational Model." The original 3NF paper.
- **Kent, W. (1983)** — "A Simple Guide to Five Normal Forms in Relational Database Theory." The readable version.
- **Fowler, M.** — "Patterns of Enterprise Application Architecture" (Event Sourcing, CQRS chapters) — the modern framing of canonical (events) vs. derived (projections).
- **Datomic architecture docs** — the clearest real-world implementation of "store facts, project views, never store projections."
- **dbt contracts + tests** — the closest practical lineage-checking tooling for application schemas.

## Self-check — when in doubt, ask

- "Would this field need to be updated if another field changed?" → derived.
- "Could two places in the code compute this differently?" → derived, and you need a composer.
- "Is there a formula I can write on one line?" → derived. Don't store it, compute it.
- "Is the 'formula' actually 'whatever someone wrote'?" → canonical, and name it explicitly as user input.
- "Could a typo in a write land silently?" → you have a KV bag or a missing write guard. Fix it before adding more.

<!-- SNAPPY-NEAR-NEIGHBOURS-START -->
## Near neighbours

These hands share enough of this one's words that a model can pick the wrong
door. Each row says what the other one is for; open that one instead when its
job is the job.

| Hand | What it is for |
|---|---|
| `snappy-database` | Snappy Database -- single source of truth for the data layer that backs every snappy-* skill |
<!-- SNAPPY-NEAR-NEIGHBOURS-END -->

#!/usr/bin/env -S npx tsx
/**
 * snappy-data-hygiene — Schema review API.
 *
 * Programmatic interface for the canonical/derived taxonomy review.
 * Use this when another skill or agent needs to classify fields,
 * review a proposed schema, or pull the canonical review prompt.
 *
 * CLI:
 *   npx tsx ~/.claude/skills/snappy-data-hygiene/api.ts prompt
 *   npx tsx ~/.claude/skills/snappy-data-hygiene/api.ts classify "field_name" "context"
 *   npx tsx ~/.claude/skills/snappy-data-hygiene/api.ts review path/to/schema.json
 */

export type Classification = "canonical" | "derived" | "ambiguous";

export interface FieldClassification {
  name: string;
  classification: Classification;
  reasoning: string;
  formula?: string;
  /** If classification is derived, what level of enforcement should exist */
  enforcement_required?: "storage-generated" | "write-guard" | "single-reader" | "unenforceable";
}

export interface SchemaField {
  name: string;
  type: string;
  description?: string;
  /** If the author classified it themselves */
  declared?: Classification;
  /** If derived, what's the formula */
  formula?: string;
}

export interface SchemaReview {
  verdict: "PASS" | "FAIL" | "NEEDS_CLARIFICATION";
  classifications: FieldClassification[];
  violations: Violation[];
  remediation: string[];
}

export interface Violation {
  kind:
    | "transitive_dependency"
    | "unscoped_kv_bag"
    | "unenforced_derivation"
    | "write_path_bypasses_composer"
    | "unclassified_field"
    | "stringly_typed_value_domain";
  field?: string;
  table?: string;
  detail: string;
  severity: "error" | "warning";
}

const DERIVED_NAME_HINTS = [
  // Classic aggregations
  "total", "sum_", "count_", "avg_", "max_", "min_",
  "_count", "_total", "_sum",
  // Head-of-chain / aggregation pointers
  "latest_", "first_of_", "last_engagement", "last_activity", "last_seen",
  // Denormalized snapshots
  "_cached", "_computed", "_summary", "_rendered", "_resolved", "_denormalized",
  // Composed identity
  "full_name", "display_name", "display_string",
  // Composed prompts / derived text
  "prompt_text", "composed_", "rendered_", "final_",
];

const KV_BAG_TABLE_HINTS = [
  "settings", "config", "meta", "preferences", "options",
  "agent_settings", "feature_flags", "kv_store",
];

/**
 * Heuristic classifier. For ambiguous cases, ALWAYS returns "ambiguous"
 * with reasoning — never guess. The human is the tiebreaker.
 */
export function classifyField(name: string, context?: string): FieldClassification {
  const lower = name.toLowerCase();
  const ctxLower = (context ?? "").toLowerCase();

  // Strong heuristics for derived
  for (const hint of DERIVED_NAME_HINTS) {
    if (lower.includes(hint)) {
      return {
        name,
        classification: "derived",
        reasoning: `Field name contains "${hint}" — matches a known derived-value pattern (aggregation, composition, or snapshot).`,
        enforcement_required: "storage-generated",
      };
    }
  }

  // ID / FK patterns are canonical-ish but could be derived in rare cases
  if (lower.endsWith("_id") || lower === "id") {
    return {
      name,
      classification: "canonical",
      reasoning: "Identifier / foreign key. These are typically canonical inputs assigned at creation time.",
    };
  }

  // Timestamps are canonical (set at event time)
  if (lower.match(/(^|_)(created|updated|deleted|modified)_at$/) || lower === "created_at" || lower === "updated_at") {
    return {
      name,
      classification: "canonical",
      reasoning: "Event timestamp. Canonical — set by the system at the moment of the event.",
    };
  }

  // "Active" flags and enums are usually canonical choices
  if (lower.startsWith("is_") || lower.startsWith("has_") || lower === "status" || lower.endsWith("_enabled")) {
    return {
      name,
      classification: "canonical",
      reasoning: "Boolean flag or status enum. Typically a direct declaration, not derived.",
    };
  }

  // KV-bag context is a red flag
  if (KV_BAG_TABLE_HINTS.some((t) => ctxLower.includes(t))) {
    return {
      name,
      classification: "ambiguous",
      reasoning: `Field lives in a key-value settings bag (${context ?? "unknown"}). These bags are anti-normal by construction — classification requires knowing the specific key's semantics. Ask the user: canonical user choice, or derived from other keys?`,
    };
  }

  return {
    name,
    classification: "ambiguous",
    reasoning: "No strong heuristic match. Human classification required — ask the user whether this value is an independent choice (canonical) or computable from other stored values (derived).",
  };
}

/**
 * Reviews a proposed schema for 3NF violations and KV-bag anti-patterns.
 * Returns VERDICT + CLASSIFICATIONS + VIOLATIONS + REMEDIATION.
 */
export function reviewSchema(params: {
  table: string;
  fields: SchemaField[];
  isKVBag?: boolean;
  hasWriteGuard?: boolean;
  scopedBy?: string[];
}): SchemaReview {
  const { table, fields, isKVBag, hasWriteGuard, scopedBy } = params;
  const classifications: FieldClassification[] = [];
  const violations: Violation[] = [];
  const remediation: string[] = [];

  for (const f of fields) {
    const classification =
      f.declared != null
        ? {
            name: f.name,
            classification: f.declared,
            reasoning: `Declared by author as ${f.declared}`,
            formula: f.formula,
            enforcement_required: f.declared === "derived" ? ("storage-generated" as const) : undefined,
          }
        : classifyField(f.name, `${table} ${f.description ?? ""}`);

    classifications.push(classification);

    if (classification.classification === "derived" && !classification.formula && !f.formula) {
      violations.push({
        kind: "unenforced_derivation",
        field: f.name,
        table,
        detail: `Field "${f.name}" is classified as derived but has no declared formula. Derived fields without an explicit formula cannot be enforced or reimplemented safely.`,
        severity: "error",
      });
      remediation.push(
        `Declare the formula for "${f.name}" in one sentence, then move it out of storage (use a generated column, view, or composer function).`
      );
    }

    if (classification.classification === "ambiguous") {
      violations.push({
        kind: "unclassified_field",
        field: f.name,
        table,
        detail: `Field "${f.name}" could not be classified heuristically. Ask the user: is this a user-controlled input (canonical), or computable from other fields (derived)?`,
        severity: "warning",
      });
    }
  }

  if (isKVBag) {
    if (!hasWriteGuard) {
      violations.push({
        kind: "unscoped_kv_bag",
        table,
        detail: `Table "${table}" is a key-value settings bag without a write guard. Any key can be written to it, including derived keys that should never be stored and typo'd keys that silently land.`,
        severity: "error",
      });
      remediation.push(
        `Add a write-boundary guard on the PUT endpoint for "${table}" that validates keys against an explicit allow-list of canonical keys. Reject derived keys with a precondition error naming the composer function.`
      );
    }
    if (!scopedBy || scopedBy.length === 0) {
      violations.push({
        kind: "unscoped_kv_bag",
        table,
        detail: `Table "${table}" is a KV bag without a declared owner scope. Bare reads (e.g. "where key = X") will leak across owners.`,
        severity: "error",
      });
      remediation.push(
        `Declare the owner scope (user_id, companion_id, tenant_id, etc.) and require every read to filter by it. Build a helper "get_effective_setting(owner, key)" and sweep call sites to use it.`
      );
    }
  }

  const hasErrors = violations.some((v) => v.severity === "error");
  const hasWarnings = violations.some((v) => v.severity === "warning");
  const verdict: SchemaReview["verdict"] = hasErrors
    ? "FAIL"
    : hasWarnings
    ? "NEEDS_CLARIFICATION"
    : "PASS";

  return { verdict, classifications, violations, remediation };
}

/**
 * Returns the canonical review prompt text for injection into another
 * agent's context. Use when you need to run the review in a sub-agent
 * or paste it into a chat.
 */
export function getReviewPrompt(): string {
  return `You are reviewing a proposed data schema change.

Before any column, table, settings key, or stored field is added or modified, answer this question:

  "Is this value canonical or derived? If derived, what's the formula, and does the schema make it impossible to store it inconsistently with that formula?"

TAXONOMY:
  CANONICAL — a fact or choice nothing else determines. Stored. Mutated by user action, event, or explicit write.
  DERIVED   — fully determined by canonical values via a formula. NOT stored. Projected/computed at read.

For EACH field in the proposed change:
  1. Classify it: CANONICAL / DERIVED / AMBIGUOUS.
  2. If DERIVED, state the formula in one sentence.
  3. State the enforcement level: storage-generated / write-guard / single-reader / unenforceable.
  4. If AMBIGUOUS, escalate to the user. Do not guess.

RED FLAGS that trigger a failure:
  - A field is classified DERIVED but is still stored as a writable column.
  - A field is DERIVED but its formula lives in multiple places (divergent implementations).
  - The schema includes a key-value settings bag (stringly-typed keys) without a write guard.
  - Read paths bypass the composer/formula function.
  - Write paths accept any key without validation.

OUTPUT FORMAT:

VERDICT: PASS | FAIL | NEEDS_CLARIFICATION

CLASSIFICATIONS:
  - <field>: <CANONICAL | DERIVED | AMBIGUOUS> — <reason>
    formula (if derived): <one-sentence formula>

VIOLATIONS:
  - <kind>: <field/table> — <detail>

REMEDIATION:
  - <specific steps>

If VERDICT is anything other than PASS, do not proceed to implementation. Surface to the user.`;
}

// ---------- CLI ----------

async function main() {
  const [cmd, ...args] = process.argv.slice(2);

  switch (cmd) {
    case "prompt":
      console.log(getReviewPrompt());
      return;

    case "classify": {
      const [name, context] = args;
      if (!name) {
        console.error("Usage: api.ts classify <field_name> [context]");
        process.exit(1);
      }
      const result = classifyField(name, context);
      console.log(JSON.stringify(result, null, 2));
      return;
    }

    case "review": {
      const [schemaPath] = args;
      if (!schemaPath) {
        console.error("Usage: api.ts review <path/to/schema.json>");
        console.error('Schema JSON: {"table": "...", "fields": [...], "isKVBag": bool?, "hasWriteGuard": bool?, "scopedBy": [...]?}');
        process.exit(1);
      }
      const fs = await import("node:fs");
      const raw = fs.readFileSync(schemaPath, "utf-8");
      const schema = JSON.parse(raw);
      const result = reviewSchema(schema);
      console.log(JSON.stringify(result, null, 2));
      if (result.verdict === "FAIL") process.exit(2);
      if (result.verdict === "NEEDS_CLARIFICATION") process.exit(3);
      return;
    }

    default:
      console.error("snappy-data-hygiene — schema review API");
      console.error("");
      console.error("Commands:");
      console.error("  prompt                               Print the canonical review prompt");
      console.error("  classify <name> [context]            Classify a single field by heuristic");
      console.error("  review <schema.json>                 Review a proposed schema");
      console.error("");
      console.error("Exit codes: 0=PASS, 2=FAIL, 3=NEEDS_CLARIFICATION");
      process.exit(1);
  }
}
/** WHAT THIS HAND ANSWERS, and what each verb does to the world.
 *  Derived from this file's own CLI dispatch by
 *  `snappy-hands/contract-derive.ts` — a verb the code does not implement is
 *  never declared here. Snappy's daemon reads it (`api.ts contract`) to
 *  validate every call, build the argument words in order, decide whether the
 *  act runs now or stages for the owner, and hand the child exactly the
 *  environment keys named in `requires` — never a value, never anything else.
 */
import { refusalTable } from "../snappy-settings/refusal-codes.ts";

export const HAND_CONTRACT = {
  skill: "snappy-data-hygiene",
  description: "Schema review reference: separate canonical values (inputs) from derived values (computable outputs) to eliminate the whole class of out-of-sync data bugs. Third normal form stated without jargon. Use when reviewing a database schema, diagnosing data that disagrees with itself, or deciding whether a value should be stored or projected. Triggers on: data hygiene, schema review, canonical vs derived, normalization, stale field, computed column, third normal form.",
  managed: false,
  requires: [] as string[],
  refusals: refusalTable("unknown_verb", "missing_argument", "not_found"),
  verbs: {
    classify: {
      args: ["field-name","context?"], effect: "read",
      class: "read", execution: "call", openWorld: false,
      annotations: { readOnlyHint: true, destructiveHint: false, idempotentHint: false, openWorldHint: false },
      inputSchema: { properties: { "field-name": { type: "string", description: "Column or field name to classify for personal data" }, context: { type: "string", description: "Surrounding table or schema name that disambiguates the field" } } },
    },
    prompt: {
      args: [], effect: "read",
      class: "read", execution: "call", openWorld: false,
      annotations: { readOnlyHint: true, destructiveHint: false, idempotentHint: false, openWorldHint: false },
    },
    review: {
      args: ["schema-path"], effect: "write-reversible",
      class: "additive-write", execution: "call", openWorld: false,
      annotations: { readOnlyHint: false, destructiveHint: false, idempotentHint: false, openWorldHint: false },
      inputSchema: { properties: { "schema-path": { type: "string", description: "Schema file whose fields are reviewed and annotated" } } },
    },
  },
} as const;

if (import.meta.url === `file://${process.argv[1]}` && process.argv[2] === "contract") {
  console.log(JSON.stringify(HAND_CONTRACT, null, 2));
  process.exit(0);
}


if (import.meta.url === `file://${process.argv[1]}`) {
  main().catch((err) => {
    console.error(err);
    process.exit(1);
  });
}

import { test } from "node:test";
import assert from "node:assert/strict";
import { HAND_CONTRACT } from "./api.ts";
import { REFUSAL_CODES, type RefusalCode } from "../snappy-settings/refusal-codes.ts";

/** RULE 33 COVERAGE, AGAINST THE ONE CLOSED TABLE ⟨lane r30, 2026-09-09⟩.
 *  This file used to restate a refusal row's own properties — that it has a
 *  `contract_slice`, that it has a `fix`, that it leaks no token — once per
 *  hand, 48 times, over rows that all come from the SAME object. Forty-eight
 *  copies of one check is the duplicate road the closed table exists to end:
 *  `snappy-settings/refusal-codes.test.ts` runs those checks ONCE over every
 *  row, and the second test below proves this hand carries THE ROW ITSELF and
 *  not a copy — an identity a drifted duplicate cannot fake. Before this, each
 *  hand hand-wrote its own row, and the wording had already drifted: the
 *  inline `unknown_verb` said "Call one of the verbs named in
 *  HAND_CONTRACT.verbs" while the closed table says "Call one of the verbs the
 *  contract declares; the refusal lists them."
 *
 *  DECLARED stays a literal list, deliberately. It is this hand's coverage
 *  manifest and it is what rule 33's lint reads out of the test SOURCE to ask
 *  "did a person look at this code" — deriving it from Object.keys would make
 *  the test pass for a hand with no refusals at all. `satisfies readonly
 *  RefusalCode[]` makes the compiler refuse a name the one table does not
 *  have. It NAMES codes; it no longer DEFINES them. */
const DECLARED = [
  "unknown_verb",
  "missing_argument",
  "not_found",
] as const satisfies readonly RefusalCode[];

test("snappy-data-hygiene: the refusal table declares exactly the codes this test names", () => {
  assert.deepEqual(Object.keys(HAND_CONTRACT.refusals ?? {}).sort(), [...DECLARED].sort());
});

test("snappy-data-hygiene: every declared refusal is the one closed table's own row, never a copy", () => {
  const declared = Object.entries(HAND_CONTRACT.refusals ?? {});
  assert.ok(declared.length > 0, "HAND_CONTRACT.refusals is empty");
  for (const [code, row] of declared) {
    assert.ok(code in REFUSAL_CODES, `${code} is not a row of snappy-settings/refusal-codes.ts`);
    assert.equal(row, REFUSAL_CODES[code as RefusalCode], `${code} is a copy of the closed table's row, not the row itself`);
  }
});