undrop-guard

A pure SQL classifier: statement text in, a verdict out — safe, risky, destructive, or unknown, where unknown is treated as destructive by every caller. It is the open-source enforcement core behind Undrop, a database safety net that sits between an AI coding agent and a Supabase/Postgres database and blocks destructive statements pending human approval.

You don't have to trust a hosted service to trust this package: read this file, read the ~1,500 lines of TypeScript in src/, and read the 349-case public corpus that ships with it. That is the whole audit surface.

import { classify } from "undrop-guard";

const verdict = await classify("DELETE FROM orders");
// {
//   verdict: "destructive",
//   statements: [
//     {
//       class: "destructive",
//       stmtType: "DeleteStmt",
//       findings: [{ code: "DELETE_NO_WHERE", ... }],
//       relations: [{ kind: "relation", schema: "public", name: "orders" }],
//       fingerprint: "…sha256…",
//       sql: "DELETE FROM orders",
//     },
//   ],
// }

classify(sql, policy?) never throws on string input. Malformed SQL and unrecognized syntax both resolve to an unknown verdict rather than a rejected promise, so a caller's try/catch can never become an accidental fail-open path.

An optional second argument lets you downgrade specific destructive statements to risky by relation pattern — for example, to stop blocking drops of your own _tmp_* scratch tables:

const verdict = await classify("DROP TABLE _tmp_import", [
  {
    action: "downgrade",
    match: { relation: { schema: "public", namePrefix: "_tmp_" } },
    to: "risky",
  },
]);
// verdict.verdict === "risky"

Policy can only ever soften a destructive call to risky — it can never touch unknown, and it can never widen anything. See src/types.ts for the full GuardPolicy shape.

Install

npm install undrop-guard

Requires Node ≥20 (for globalThis.crypto.subtle) or a browser. No native build step — the SQL parser is Postgres's own grammar compiled to WASM.

The four-class taxonomy

One exhaustive switch over the parsed statement's tag. Any tag not explicitly handled falls through to unknown — that default arm is the safety property this package is built around: new or unrecognized syntax fails closed instead of executing.

• safe — executes immediately. Reads, inserts, UPDATE/DELETE with a real (non-tautological) WHERE, additive DDL (CREATE TABLE, indexes, schemas, functions/views without REPLACE), transaction control, and a narrow set of GUC/session statements verified to carry no privilege-escalation surface (SHOW, RESET ALL).
• risky — allowed, but flagged as something a caller should snapshot first: code-object replacement (CREATE OR REPLACE FUNCTION/VIEW, triggers), lossy column type changes, constraint changes, index drops, REVOKE, renames, enabling row-level security on a table that has no policies yet (denies all rows to app roles — app breakage without data loss).
• destructive — blocked; requires human approval; a snapshot is taken at block time. DROP TABLE/SCHEMA/VIEW/..., TRUNCATE, UPDATE/DELETE with no WHERE or a tautological one (WHERE 1=1, WHERE true, and every disguised form Postgres itself would coerce to a boolean, e.g. WHERE 'true'), dropping a column, disabling row-level security, altering or dropping a policy, SET ROLE/SET SESSION AUTHORIZATION and their RESET forms.
• unknown — cannot be statically verified safe, so it is treated as destructive. DO/EXECUTE blocks, server-side COPY PROGRAM, two-phase commit, and — critically — every statement type this package hasn't been explicitly taught, including any new syntax a future Postgres version adds.

This summary is illustrative, not the contract. The actual, exhaustive mapping lives in src/taxonomy.ts (one file, read top to bottom) and is pinned case-by-case by corpus/corpus.jsonl — those two are what to audit, not this README's prose.

The purity guarantee

This package has exactly one runtime dependency: libpg-query (Postgres's own parser, compiled to WASM), pinned to the pg17 line. Beyond that:

• No filesystem access, no network calls, no environment variables.
• No ambient state — no clock reads, no globals that affect a verdict.
• No database connection, ever. Classification is purely static; it never looks at your schema, your data, or your catalog.
• Every recursive walk (tautology folding, nested-statement descent, fingerprinting) is depth-bounded, so pathologically nested-but-valid SQL fails closed to unknown instead of crashing the process.
• A dedicated test (tests/purity.test.ts) asserts the package's runtime export surface and that package.json's dependencies field contains nothing but libpg-query.

The same input always produces the same verdict. Nothing outside the SQL text — not your role, not the time of day, not a prior call — can change the answer.

Run the corpus yourself

The corpus is the enforceable contract (see corpus/corpus.jsonl, which ships in this package) — not the implementation, and not this README. It is authored independently of the classifier so the author of the code isn't grading its own homework.

The published package ships corpus/corpus.jsonl but not the test runner (that lives in the source repo's tests/). Point your own script at the installed copy:

import { readFileSync } from "node:fs";
import { createRequire } from "node:module";
import { dirname, join } from "node:path";
import { classify } from "undrop-guard";

// "undrop-guard/package.json" is an explicit export, so this resolves
// correctly under Node's package-encapsulated resolution even though
// "corpus/" itself isn't a declared export target.
const require = createRequire(import.meta.url);
const packageRoot = dirname(require.resolve("undrop-guard/package.json"));
const corpusPath = join(packageRoot, "corpus", "corpus.jsonl");

const cases = readFileSync(corpusPath, "utf8")
  .split("\n")
  .filter(Boolean)
  .map((line) => JSON.parse(line));

let failed = 0;
for (const c of cases) {
  const verdict = await classify(c.sql, c.policy);
  if (verdict.verdict !== c.expect.class) {
    failed++;
    console.error(`${c.id}: expected ${c.expect.class}, got ${verdict.verdict}`);
  }
}
console.log(`${cases.length - failed}/${cases.length} corpus cases passed`);

Or clone the source repository and run the full suite, including the adversarial and boundary cases this snippet skips:

git clone https://github.com/RayAdrian/undrop.git
cd undrop/packages/guard
pnpm install && pnpm test

What this classifier does not catch

Documented on purpose — a classifier that hides its blind spots is less trustworthy than one that states them:

• Function bodies are opaque. A safe SELECT my_function() can execute arbitrary destructive DML hidden inside a user-defined function created earlier. Whether my_function is a harmless builtin or a Trojan-horsed DELETE FROM orders is a catalog fact, and this package has no catalog access by design. Function creation is classified (risky/safe depending on REPLACE); function invocation is not inspected. Closing this requires a stateful caller tracking what a function actually contains — out of scope for a pure, static classifier.
• Narrow tautology folding, not general predicate analysis. The WHERE-clause folder catches constants, boolean logic, simple comparisons, self-joins, and Postgres's own boolean-string coercion rules (WHERE 'true' is caught). It deliberately does not attempt arithmetic (WHERE 1+1=2 passes), function calls (WHERE now() > ... passes), subqueries, BETWEEN, IN-lists, CASE/COALESCE, or cross-column reasoning. Over-blocking real predicates would kill adoption faster than under-blocking rare tautologies costs safety — the corpus pins every one of these escapes as allowed-by-design, not a bug.
• Dynamic SQL is opaque, not analyzed. DO blocks, EXECUTE, and stored-procedure CALL are all unknown (blocked) rather than partially interpreted — a constructed string is not statically classifiable, so this package doesn't try.
• No catalog, ever, means no row-count or table-size awareness. A destructive verdict tells you what kind of statement this is, never how much data it would touch — that requires a live connection, which this package deliberately never opens.

If you're deciding whether to trust this in front of a production database, read src/taxonomy.ts and corpus/corpus.jsonl yourself rather than taking this list as exhaustive — it names the residuals we know about and thought worth calling out, not a formal proof of completeness.

License

MIT — see LICENSE. Free to use, audit, fork, and vendor forever; the corpus and classifier are not going behind a paywall.