> ## Documentation Index
> Fetch the complete documentation index at: https://supaschema.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Hints

> How to approve reviewed destructive changes, renames, grants, and RLS policy-column intent without weakening fail-closed planning.

`supaschema` fails closed when intent cannot be proven from the SQL parse tree. Hints are the explicit, reviewable way to state supported source intent.

Use hints for four cases:

* a destructive change that a human already reviewed;
* a rename that would otherwise look like drop plus create;
* roles allowed by the free grant least-privilege rule;
* table columns required by the free RLS policy-column rule.

<Warning>
  Do not commit broad destructive approval. Use exact object keys, not `"*"`, in
  shared config.
</Warning>

## Destructive changes

A blocked plan looks like this:

```text theme={null}
ERROR SUPA_PLAN_DESTRUCTIVE_HINT_REQUIRED [table:app.legacy_imports]: drop of table requires an explicit destructive-change hint
  hint: Add "table:app.legacy_imports" to hints.destructive only after reviewing the migration.
```

Recovery:

1. Review the rendered `-- BLOCKED` section.
2. Confirm the drop is intended.
3. Add the exact object key.

```json theme={null}
{
  "hints": {
    "destructive": ["table:app.legacy_imports"]
  }
}
```

`"*"` approves all destructive changes for one run; prefer exact keys in committed config.

Dropped grants render as `REVOKE`, and dropped default privileges render as the reverse `ALTER DEFAULT PRIVILEGES ... REVOKE`, but both still require the destructive hint first.

## Grant and RLS safety

`hints.allowedGrantees` declares the roles that grant/default-privilege diagnostics accept:

```json theme={null}
{
  "hints": {
    "allowedGrantees": ["anon", "authenticated", "service_role"]
  }
}
```

`hints.requiredPolicyColumns` declares table columns that must appear in the effective RLS predicate for each configured table:

```json theme={null}
{
  "hints": {
    "requiredPolicyColumns": {
      "public.accounts": ["tenant_id"]
    }
  }
}
```

For `SELECT` and `DELETE`, the required-column rule reads `USING`. For `INSERT`, it reads `WITH CHECK`. For `UPDATE` and `ALL`, required-column matching uses `WITH CHECK` when present and otherwise reads PostgreSQL's `USING` fallback, while the RLS safety pack still warns when an update-capable policy omits an explicit `WITH CHECK`.

## Column drops and type changes

When only columns change, the hinted plan renders data-preserving column ALTERs instead of replacing the table (`SUPA_PLAN_COLUMN_ALTER_HINT_REQUIRED`):

* removed columns render `ALTER TABLE ... DROP COLUMN IF EXISTS`,
* type changes render `ALTER COLUMN ... TYPE <type> USING <column>::<type>`,
* identity, generated-expression, `NOT NULL`, and `DEFAULT` changes render without a destructive hint.

Type changes also emit `SUPA_PLAN_COLUMN_TYPE_USING_REVIEW` and add an inline SQL comment because the rendered `USING <column>::<type>` expression is only a default identity cast. Replace it when PostgreSQL needs a custom conversion.

Identity changes emit `SUPA_PLAN_COLUMN_IDENTITY_REVIEW` because PostgreSQL identity actions affect future generated values. Generated-expression changes emit `SUPA_PLAN_COLUMN_GENERATED_REVIEW` because stored generated values can be rewritten and statistics may need refresh. Table-constraint changes render as separate guarded constraint operations. A hinted table **replace** drops and recreates the table — it is not data-preserving; prefer the column lane or hand-author the migration.

If a routine, view, policy, or trigger depends on a column being dropped or type-changed, the dependent object must be dropped or replaced before the table alter. If the source still references the changed column, the planner blocks with `SUPA_PLAN_COLUMN_DEPENDENT_REWRITE_REQUIRED`.

## Routine dependencies

supaschema extracts routine dependencies from SQL-standard function bodies, SQL string bodies, and common static PL/pgSQL statements such as `SELECT`, `PERFORM`, DML, `RETURN QUERY`, `RETURN (SELECT ...)`, cursor queries, and `FOR ... IN query` loops.

Dynamic SQL (`EXECUTE`), partially parsed PL/pgSQL, and unsupported routine languages fail closed when the same plan changes relations or types. Supaschema no longer accepts config-supplied routine dependency hints for this path because they are not structural proof. Rewrite the routine so dependencies are statically extractable, include the routine rewrite in the same plan, split the relation/type change, or use a reviewed explicit migration.

## Incompatible view and routine replacements

PostgreSQL rejects `CREATE OR REPLACE` when a view drops/renames/reorders output columns, or when supaschema cannot prove the output columns (`SUPA_PLAN_VIEW_REPLACE_INCOMPATIBLE`). A routine replacement also blocks when the return type or OUT parameters change (`SUPA_PLAN_ROUTINE_RETURN_TYPE_CHANGED`). Hinting the object key renders a guarded `DROP ... IF EXISTS` followed by the new definition. Dependent objects (views over views) must be hinted and recreated in the same plan.

## Renames

Without a hint, a rename diffs as drop+create (destructive, blocked). Declare the rename instead:

```json theme={null}
{
  "hints": {
    "renames": [{ "from": "table:app.accounts", "to": "table:app.customers" }]
  }
}
```

The rendered SQL is a guarded `DO` block: it raises if both names exist, renames if only the old name exists, and is a no-op if the rename already happened. Rename hints:

* must keep the same object kind (`SUPA_PLAN_RENAME_KIND_MISMATCH`),
* must stay in the same schema (`SUPA_PLAN_RENAME_SET_SCHEMA_UNSUPPORTED`),
* support schemas, tables, sequences, indexes, functions, procedures, views, and materialized views (`SUPA_PLAN_RENAME_UNSUPPORTED` otherwise),
* always carry a `SUPA_PLAN_RENAME_VERIFY_REQUIRED` warning — run `supaschema verify`.

## Object keys

Keys follow `kind:schema.name`, with `(signature)` for functions/procedures and `:table` for table-scoped objects:

```text theme={null}
table:app.accounts
function:app.greeting()
policy:app.accounts_select:accounts
index:app.accounts_email_idx:accounts
grant:grant:table:app.accounts:authenticated
```

`supaschema plan` prints every operation key; copy keys from there rather than constructing them by hand.

## Enum reordering and removal

Enum value removal/reordering has no in-place ALTER in PostgreSQL. The hinted plan replaces the type (drop+create), which fails while columns depend on it. The safe hand-authored recipe is:

```sql theme={null}
CREATE TYPE app.mood_next AS ENUM ('happy', 'curious');
ALTER TABLE app.entries
  ALTER COLUMN current TYPE app.mood_next
  USING current::text::app.mood_next;
DROP TYPE IF EXISTS app.mood;
ALTER TYPE app.mood_next RENAME TO mood;
```

Validate it with `supaschema check` and `supaschema verify` like any hand-authored migration.

## Changes that require source intent

Data backfills have no broad hint lane because the package must not invent row values, predicates, or conversion expressions. Put that intent in an explicit reviewed migration or another checked source-intent artifact, then validate it with `supaschema check` and `supaschema verify`.

When one table change drops columns and adds columns in the same transition, supaschema treats it as a possible storage rewrite. If the configured migration corpus has no reviewed DML or `DO` intent, planning blocks with `SUPA_PLAN_DATA_TRANSITION_REQUIRED` even when the destructive table key is hinted.

`CASCADE` drops have no hint lane by design.